Как сделать таблицу в sqlite
Обновлено: 03.07.2024
Рассмотрим на примере создание базы данных и таблицы SQLite в PHP 5:
Каким же должен быть SQL-запрос для создания таблицы, учитывая бестиповый характер таблиц SQLite? Все очень и очень просто — при описании полей таблицы принимаются в расчет только имена полей. Указание типов полей и их максимальной длины является необязательным и SQLite’ом не учитывается! Исключение составляет только первичный ключ (PRIMARY KEY), объявленный как INTEGER – такое поле автоматически становится автоинкрементным.
Таким образом, аналогичную таблицу можно было бы создать и таким SQL-оператором:
Выполнение запроса к базе производится функцией sqlite_query(), а проверка успешности выполнения запрос функцией sqlite_last_error().
Необязательно закрытие соединения с базой SQLite выполняется функцией sqlte_close().
Сохранение данных в таблицу SQLite
Добавим в нашу тестовую таблицу SQLite некоторые значения.
По сравнению с предыдущим сценарием создания таблицы с точки зрения PHP в представленном коде нет ничего нового. Тем не менее, стоит обратить внимание на SQL-оператор INSERT добавления данных в таблицу. В его записи нет неожиданностей. Однако, учитывая бестиповый характер таблиц SQLite возможен и следующий вариант, который не будет являться ошибочным:
В этом примере числовое значение записывается в текстовое поле, а символьное значение в целочисленное! Поскольку при создании таблицы типы полей, указанные в операторе CREATE TABLE игнорируются, SQLite позволяет вставлять данные любого типа в любые поля! Единственно, когда типы в SQLite имеют значение – это процесс сортировки или выборки данных по условиям, при этом приведение типов SQLite выполняет автоматически.
Вывод данных из таблицы SQLite
Теперь посмотрим, каким образом PHP 5 может произвести вывод данных из созданной нами таблицы.
Выполняя запрос SELECT необходимо результат сохранять в переменную, которая будет иметь ресурсный тип ($result). Вывод данных из такой переменной возможен множеством способов, однако в любом случае потребуется перебор всех записей, как это делалось и при работе с MySQL.
В данном примере используется функция sqlite_fetch_object(), возвращающая данные одной записи в виде объекта. Получить же значение одного конкретного поля из такого объекта можно, обратившись к его свойству, имя которого совпадает с именем поля.
Как это принято в PHP, функция sqlite_fetch_object() возвратит FALSE, в случае прохода по всем записям вернувшихся значений, вот почему ее использование удачно сочетается с оператором цикла while.
Для добавления возможности использования СУБД SQLite в программе на Python необходимо импортировать модуль:
После чего станут доступны API-функции этого расширения. Подробнее о них мы будем говорить на последующих занятиях. А вначале воспользуемся вот такой простой заготовкой для создания и управления БД:
Смотрите, в первую очередь мы должны вызвать метод connect, чтобы установить связь с определенной БД. В данном случае – это файл saper.db, который должен располагаться в том же каталоге, что и файл программы на Питоне. В качестве расширений этого файла, обычно, используют следующие:
*.db, *.db3, *.sqlite и *.sqlite3
Я взял первый вариант, т.к. он короткий и понятный. При выполнении команды connect файл saper.db либо будет открыт, либо будет создан, если он не существует. В результате создается (или открывается) БД с именем saper.db.
При успешном соединении с БД метод connect возвращает экземпляр объекта Connection, на который ссылается переменная con. И, далее, мы должны использовать объект Cursor для взаимодействия с БД и выполнения SQL-запросов. Например, это можно сделать с помощью метода execute, которому в качестве аргумента как раз и передается строка с SQL-запросом. Но мы пока там ничего указывать не будем. В конце программы при завершении работы с БД необходимо закрыть соединение. Это делается с помощью метода close.
Вот так в двух словах происходит взаимодействие с СУБД SQLite. Запустим программу, видим, что никаких ошибок не возникло и, кроме того, была создана БД saper.db в виде отдельного файла в каталоге с исполняемым файлом Питона.
Однако, соединяться с БД лучше все-таки через менеджер контекста:
Он автоматически сохраняет данные в БД (вызывает метод commit()) даже при возникновении ошибочных ситуаций. Поэтому, в дальнейшем мы им и будем пользоваться. Конечно, после него, когда соединение с БД уже не нужно, его нужно закрыть все той же командой close().
Давайте теперь перейдем в программу DB Browser и откроем эту БД. На экране увидим вот такое окно и, как видите, у нас пока здесь нет ни одной таблицы.
Создание и удаление таблиц
- name – строка с именем игрока;
- sex – число, пол игрока (1 – мужской; 2 – женский);
- old – число, возраст игрока;
- score – суммарное число набранных очков за все игры.
Далее, пропишем вот такой SQL-запрос:
Мы здесь указываем команду CREATE TABLE (создать таблицу), затем, имя таблицы – users и, далее, в круглых скобках через запятую перечисляем поля с указанием их типов. Названия команд и типов принято писать заглавными буквами, а имена таблиц и их полей – строчными. Мы будем придерживаться этого негласного правила.
Запустим программу, снова откроем БД в DB Browser и увидим созданную таблицу заданной структуры. Это будет наше первое хранилище данных.
Однако, смотрите, если запустить программу еще раз, то появится ошибка, т.к. мы пытаемся создать таблицу, которая уже существует. Поэтому SQL-запрос лучше записать в таком виде:
То есть, создавать таблицу только если она не существует. Теперь, запуская программу, никаких ошибок появляться не будет и, кроме того, мы точно будем уверены, что таблица users присутствует в нашей БД.
Алексей 1 22 1000
Миша 1 19 800
Федор 1 26 1100
Маша 2 18 1500
Здесь * указывает взять все поля из таблицы users. Подробнее мы еще поговорим об этом запросе, а пока я покажу следующее. Каждая таблица SQLite содержит скрытое поле rowid, хранящее уникальный идентификатор записи. Выведем его на экран с помощью запроса:
Теперь мы видим значения этого поля для каждой записи. Используя rowid, в частности, можно осуществлять связывание нескольких таблиц между собой для формирования сводного отчета. И в дальнейшем мы увидим как это делается.
Наконец, если требуется удалить таблицу, то для этого прописывается такой SQL-запрос:
Как видите, все довольно просто.
PRIMARY KEY, AUTOINCREMENT, NOT NULL и DEFAULT
При необходимости можно самостоятельно создать поле подобное rowid в любой таблице. Для этого, при создании ее структуры, указывается следующая строчка:
user_id INTEGER PRIMARY KEY AUTOINCREMENT
Здесь ограничитель PRIMARY KEY (первичный ключ) означает, что поле user_id должно содержать уникальные значения, а ограничитель AUTOINCREMENT указывает СУБД автоматически увеличивать значение user_id при добавлении новой записи.
Удалим таблицу users из БД, выполним программу и увидим, что автоматически создается еще одна вспомогательная таблица sqlite_sequence, которая будет хранить служебную информацию для поля user_id. Но, в нашей работе она не нужна, мы по-прежнему пользуемся только одной таблицей users.
Далее, если нам нужно указать, что поле обязательно должно содержать какие-либо данные, то ему следует добавить ограничитель NOT NULL, а для задания значения по умолчанию – ограничитель DEFAULT, например, так:
Теперь, при добавлении новой записи, обязательно нужно будет указывать имя игрока, но можно не указывать его пол, если предполагается мужской:
В результате получим запись:
Алексей 1 18 1000
Часто ограничители NOT NULL и DEFAULT объединяют между собой и пишут так:
Тогда у нас поле sex обязательно будет содержать значение и по умолчанию оно будет равно 1.
Вот так происходит обращение к БД и создание таблиц в SQLite. На следующем занятии мы продолжим изучение команд языка SQL.
Видео по теме
© 2022 Частичное или полное копирование информации с данного сайта для распространения на других ресурсах, в том числе и бумажных, строго запрещено. Все тексты и изображения являются собственностью сайта
В этом уроке я покажу вам, как создавать новые таблицы в базе данных SQLite с использованием PHP PDO.
Мы создадим две новые таблицы в базе данных phpsqlite, которую мы создали в предыдущем уроке . Ниже приведен код SQL, который создает таблицы проектов и задач.
Чтобы создать новую таблицу в базе данных SQLite с помощью PDO, используйте следующие шаги:
- Сначала подключитесь к базе данных SQLite, создав экземпляр класса PDO.
- Во-вторых, выполните инструкцию CREATE TABLE, вызвав метод exec () объекта PDO.
Мы будем повторно использовать класс SQLiteConnection , который мы разработали в предыдущем уроке. Следующий класс SQLiteCreateTable демонстрирует, как создавать новые таблицы в нашей базе данных phpsqlite .
Как это устроено?
Метод createTables() используется для создания таблиц в базе данных phpsqlite. Во-первых, у нас есть массив, в котором хранятся операторы CREATE TABLE . Затем мы перебираем массив и выполняем каждую инструкцию CREATE TABLE один за другим, используя метод exec() объекта PDO.
Метод getTableList() выбирает все таблицы в базе данных, запрашивая имя таблицы в таблице sqlite_master . Предикат в предложении WHERE гарантирует, что запрос возвращает только таблицы, а не представления. Вы узнаете, как запрашивать данные при использовании PDO в следующем уроке.
Теперь пришло время использовать разработанные нами классы. В файле index.php используйте следующий код:
Сначала мы создаем новый экземпляр класса SQLiteCreateTable и передаем объект PDO, который создается с помощью класса SQLiteConnection .
Во-вторых, мы вызываем метод createTables() для создания новых таблиц и метод getTableList() для запроса вновь созданных таблиц.
В-третьих, в HTML коде мы отображаем список таблиц.
В этом уроке я показал, как создавать новые таблицы, выполняя оператор CREATE TABLE с использованием PHP PDO.
Организация данных — все равно, что поддержание порядка на рабочем месте. Хорошо всегда знать, где что находится, и в случае необходимости освобождать больше места для хранения или работы.
В настоящее время мир состоит из библиотек реляционных и нереляционных баз данных, причем и у тех, и у других свои преимущества и недостатки. В этой статье сфокусируемся на рассмотрении реляционных баз данных.
Увидим, как с помощью библиотеки SQLite создаются таблицы и как минимизируется добавление неправильных данных через предоставление дополнительной информации в момент построения таблицы и после ее создания. Но прежде попытаемся дать общее понятие о том, что представляет собой эта библиотека SQLite.
SQLite
Ниже показан пример таблицы с тремя записями и тремя атрибутами, содержащими различные типы данных. Построение примера осуществлено с использованием приложения DB Browser для SQLite.
Образец таблицы, содержащей примеры атрибутов и значений.
SQL — это стандартизированный язык. Тем не менее разработчики вольны соблюдать или не соблюдать стандарты до определенной степени. Это зависит от конкретной реализации. Одной из таких реализаций и является SQLite. В отличие от MySQL или PostgreSQL, это более простая библиотека, созданная для использования локально и без необходимости внешних серверов.
Бессерверность SQLite обусловливает наличие других функциональных особенностей. Во-первых, это независимость, подразумевающая то, что SQLite не нуждается во внешних ресурсах и поэтому пригодна для использования в любой среде. Во-вторых, это нулевая конфигурация, ведь сервера нет и настраивать нечего.
А кроме того, SQLite транзакционная: все изменения либо происходят полностью, либо не происходят вообще. Транзакции должны соответствовать набору правил под названием ACID. Каждая буква этого акронима обозначает определенные требования.
Начнем создание базы данных с использования следующих команд SQLite в командной строке:
Первой командой меняем каталог в файл, в котором будет база данных, а второй создаем базу данных, активировав библиотеку sqlite3 и выбрав для нее название.
SQLite — гибкая библиотека с расширениями, которые задействуются для файла базы данных. Такие расширения, как .db , .sqlite или .sqlite3 , используются без проблем. Теперь создадим первую таблицу.
Создание таблицы
Таблицы — это структуры, которые удерживают данные, вставленные в реляционные и подобные им базы данных. Вот обобщенное представление кода для создания таблицы:
Начинаем с ввода команды CREATE TABLE , за которой следует название таблицы в table_name . В квадратные скобки заключен необязательный код. Команда IF NOT EXISTS используется для того, чтобы SQLite проверила отсутствие этого названия среди уже имеющихся таблиц, прежде чем создавать новую.
В круглых скобках указываем имена атрибутов или столбцов, их типы данных и ограничения на уровне атрибутов или таблицы. Типы данных и ограничения приводить необязательно, но лучше их указать. Так пользователь будет понимать, какая информация хранится в каждом атрибуте.
О том, что такое типы данных и ограничения, расскажем позже. А пока достаточно знать, что они существуют и определяются в момент создания таблицы.
Точка с запятой указывает SQLite, что фрагмент кода завершен и готов к обработке. Создадим новую таблицу races, которая будет содержать информацию для различных рас:
Атрибут size — это то, насколько велики в среднем по размеру представители той или иной расы, как правило они либо маленькие, либо средние. Speed — это максимальное расстояние, которое представители расы способны пробежать за один раунд сражения. Languages — это языки, на которых говорит раса, а age — это средняя продолжительность жизни представителей расы.
race_id — это число, присваиваемое расе как целочисленное свойство. К этому важному свойству мы вернемся позже, когда будем говорить об ограничениях.
Многие скажут, что многовато новой информации для того, кто никогда не играл в эту настольную игру или даже не слышал о ней. На самом деле вовсе и не нужно понимать, что означают атрибуты. Что действительно важно — это их типы данных и ограничения.
А теперь заполним таблицу. Для этого напишем оператор INSERT с тремя записями:
Посмотрите: мы передаем название таблицы, а также ее атрибуты, куда вставляем значения, за которыми идут фактические, желаемые нами значения. Вот данные, сохраняющиеся в races:
Таблица races, содержащая атрибуты и значения для рас.
Тут все просто: пишем код для создания таблицы, вставляем значения в текстовый файл и проверяем, нет ли опечаток. Если все правильно, сохраняем файл под любым именем и вводим его в командной строке вот так: .read имя файла . Представьте, что операторы CREATE TABLE и INSERT у нас выше разделены пустой строкой в текстовом файле races.txt :
sqlite>.read races.txt автоматически создаст таблицу и заполнит ее.
Теперь мы умеем создавать таблицы и вставлять в них значения. Дальше узнаем, что такое типы данных и ограничения и как они помогают идентифицировать и поддерживать информацию в таблице. Сначала рассмотрим типы данных.
Типы данных
SQLite поддерживает пять основных типов данных, которые определяются в момент создания новой таблицы: TEXT , INTEGER , REAL , GLOB и NULL . Мы видели выше, что в созданной таблице races содержатся TEXT (текстовые) и INTEGER (целочисленные) типы данных.
Вообще говоря, тип данных TEXT представляет собой текстовую информацию, заключенную в кавычки или отделенную строками. В таблице races атрибут race_name установлен для хранения данных TEXT текстового типа, а строки Aasimar, Kalashtar и Genasi заполняют этот атрибут.
Тип данных INTEGER представляет собой целые числа (положительные или отрицательные) размером от 1 до 8 байтов. Speed — хороший пример того, где использовать целые числа, потому что этот атрибут обозначает количество футов, которые персонаж способен пробежать в раунде сражения.
REAL — это тип данных, содержащий числа длиной более 8 байтов или десятичные и экспоненциальные числа, например 1,5 или 2⁴.
NULL используется для отсутствующей или неизвестной информации. Допустим, нам не удалось найти, на каких языках говорят представители расы Genasi. В этом случае на какое-то время будем сохранять это значение как NULL.
Тип данных Datetime не поддерживается SQLite, но такая информация все равно сохраняется как тип данных TEXT , REAL или INTEGER при условии корректных преобразований. Например, данные timestamp сохраняются как INTEGER .
Имейте в виду, что SQLite, в отличие от других библиотек SQL, принимает различные данные для хранения в атрибутах, тип данных которых предопределен. Например, определение атрибута как содержащего данные TEXT не помешает SQLite принимать целые числа или любые другие типы данных.
И тем не менее определение типа данных дает пользователю прозрачный намек на то, какие данные должны храниться в каждом атрибуте, позволяя лучше организовывать таблицы.
Ограничения
Ограничения помогают устанавливать границы в атрибутах, значениях по умолчанию или связях между таблицами. Начнем с последнего. Есть два ограничения, которые играют важную роль в том, что называется нормализацией. Это PRIMARY KEY (первичный ключ) и FOREIGN KEY (внешний ключ).
Прежде чем переходить к более подробному рассмотрению этих двух ограничений, создадим новую таблицу subraces, содержащую связь с races, и попробуем понять, что такое нормализация.
В таблице subraces содержится атрибут race_id, который принимает значения INTEGER (целочисленного типа данных) и ссылается на столбец race_id в таблице races с помощью строки FOREIGN KEY (race_id) REFERENCES races (race_id) .
Благодаря такой ссылке мы сохраняем информацию о подгруппах, сводя к минимуму повторы этой информации в базе данных. Посмотрите на значения в таблице subraces:
Таблица subraces, содержащая атрибуты и значения для подрас.
Без нормализации нам пришлось бы создавать таблицу типа races_subraces со следующим кодом:
И следующими значениями:
Таблица Races_subraces, содержащая значения и атрибуты для рас и подрас.
Особой разницы, возможно, и не заметно из-за не слишком большого количества атрибутов и записей. Присмотритесь, и вы увидите довольно много повторяющихся значений, а именно в атрибутах age, languages, speed, size и race_name. А представьте, сколько места экономится в больших базах данных с тысячами взаимосвязанных записей и атрибутов!
Вот как выглядят связи между расами и подрасами на схеме:
Взаимосвязь между таблицами races и subraces.
Но мы до сих пор не рассказали, что означают ON DELETE CASCADE и ON UPDATE NO ACTION .
Первое фактически означает, что если удалить, например, расу Genasi (race_id = 3) из таблицы races, то SQLite автоматически удалит и подрасы с race_id = 3 из таблицы subraces.
ON UPDATE NO ACTION необходимо потому, что если изменить любую запись в таблице races, которая связана со внешним ключом в таблице subraces, например обновив атрибут languages для Genasi, то таблицаsubraces станет совершенно пустой.
Кроме того, различные атрибуты определяют как PRIMARY KEY (первичный ключ). Это делается для создания более сложных связей между большим количеством таблиц (больше чем двух таблиц):
Заметили здесь еще одно ограничение? Да, это NOT NULL . Оно вынуждает атрибуты не принимать значения NULL, т. е. пустые значения. NOT NULL обычно используется в столбцах идентификаторов. Хотя не только в них, но в любом атрибуте, если пользователь сочтет, что значения этого атрибута не должны быть неизвестными или отсутствующими.
То, что у атрибута записи не имеется значения, не предполагает, что вместо него обязательно должен быть NULL.
Обратите внимание на строку None в столбце subrace_name таблицы races_subraces с названием расы (race_name) Калаштары. NULL здесь не имел бы смысла, потому что у Калаштаров просто нет подрас. То есть нельзя сказать, что эта информация недостающая или неизвестная.
Если пользователь решит, что у атрибута для всех записей должны быть разные значения, он должен будет использовать ограничение UNIQUE . В этом случае, если в столбец с ограничением UNIQUE попытаться вставить новую строку с повторяющимся значением, SQLite выдаст ошибку.
Посмотрите на столбец races_name в таблице races: в нем есть это ограничение и оно здесь кстати. Ведь у разных рас должны быть уникальные названия, чтобы отличать одну от другой.
UNIQUE определяется на уровне столбца (вверху) или на уровне таблицы (внизу):
Переходим к следующему ограничению CHECK . Оно позволяет пользователю определить выражение, которое не должно быть нарушено вставляемым значением.
Выберем таблицу races и убедимся, что значения, вставленные в атрибут speed, всегда состоят из двух цифр (так как у нас нет рас с большей или меньшей скоростью, чем эта). Оставим пока в покое таблицу races и создадим новую с ограничением CHECK :
Теперь попробуем вставить строку с атрибутом speed 300:
Возникнет ошибка. Почему? Потому что нарушено ограничение CHECK и эта новая строка значений не будет вставлена в таблицу.
Так же, как и UNIQUE , CHECK определяется на уровне столбца (вверху) или таблицы (внизу):
Как видите, атрибуту language не передано никакого значения. Однако, определив Common в качестве языка по умолчанию, мы получим следующий результат:
Это ограничения, принятые в SQLite. Они помогают пользователю создавать взаимосвязи между более простыми, удобными для восприятия таблицами, а также в определенной степени управлять данными перед их вставкой в таблицы.
Выберемся теперь из таблиц и посмотрим, как используется команда CREATE TRIGGER для создания границ.
Триггеры
Триггеры автоматически выполняют заданные пользователем действия всякий раз, когда строка вставляется, удаляется или обновляется в связанной с ней таблице. Они позволяют пользователю осуществлять более сложное управление значениями, поступающими в таблицы и покидающими их. Общий синтаксис создания триггера выглядит следующим образом:
Сначала создаем триггер и даем ему название. Затем решаем, когда TRIGGER сработает. Это может быть до или после любого из следующих трех событий: INSERT , UPDATE или DELETE .
Нужно указать таблицу, в которой будет действовать этот триггер, а затем условие триггера (необязательное) после оператора WHEN . Наконец, указываем логику триггера между BEGIN и END .
В зависимости от события доступ к атрибуту осуществляется с помощью ссылок OLD и NEW в виде OLD.column_name и NEW.column_name .
Для события INSERT используется NEW , потому что вставляется новая строка. Для UPDATE используется и NEW , и OLD , потому что старая строка или значение обновляется новым. Для действия DELETE используется OLD , потому что удаляется старая строка или значение.
Вернемся к таблице subraces. Значения и строки в атрибуте ability_score_increase можно эмулировать, применяя ___ +1 , с помощью трех подстановочных знаков подчеркивания.
Подстановочные знаки заменяют символы. Есть два подстановочных знака: подчеркивание _ (которое заменяет только один символ) и процент % (заменяет любое количество символов). Например, если искать таблицы с помощью команды .table , то при вводе .tables “_aces” SQLite найдет таблицу races, а при вводе .table “%aces” найдет таблицы races, subraces и races_subraces.
Разница между подстановочными знаками _ и %.
Если триггер больше не нужен, избавляемся от него, либо удаляя все таблицы, с которыми он связан, либо удаляя его самого:
С триггерами легче минимизировать ошибки. Стоит только проявить творческий подход, как тут же находятся и другие способы использования триггеров для контроля за потоком данных. Тем не менее ничто не гарантирует отсутствия ошибок, и дальше мы расскажем о том, как справляться с еще большими ошибками на уровне базы данных.
Резервное копирование баз данных
Всякий раз, выполняя какие-то действия с базой данных, следует создавать резервную копию. Ведь неправильные инструкции легко приводят к тому, что целиком удаляются строки или заменяются атрибуты, и на исправление всего этого требуется много времени.
Чтобы защититься от любых нежелательных изменений в базе данных, есть возможность сделать резервную копию и восстановить базу данных с помощью .backup backupfilename и .restore backupfilename . Название backupfilename не должно совпадать с названием базы данных.
Подведем итоги
Мы обсудили, что такое SQLite, как создавать таблицы и как правильно хранить данные. А начали все со стандартизированного языка SQL.
Кроме того, мы рассказали о преимуществах SQLite: отсутствии необходимости настраивать сервер для ее использования и правилах, которые соблюдаются здесь при совершении транзакции.
Затем перешли к созданию таблиц, определению атрибутов и установке для них типов данных и ограничений.
Мы также обсудили общие типы данных, которые используются в SQLite для хранения информации. Убедились, что SQLite не сохраняет тип данных datetime , но здесь такие данные легко сохраняются с помощью основных используемых типов данных.
Затем мы перешли к ограничениям. Они добавляют функциональность таблицам либо через нормализацию с помощью ключей, которые связывают таблицы, устанавливая значения по умолчанию или сверяя вставляемые значения с выражением, либо не допуская значения NULL.
Мы также обсудили то, как с помощью триггеров поддерживается информация в таблице благодаря более глубокому контролю того, что в ней подлежит изменению. Триггеры используются до или после момента добавления, обновления и удаления любых необходимых нам строк или значений.
Наконец, мы кратко рассказали о том, как создавать резервные копии и восстанавливать базу данных.
Читайте также: