В вводной статье по SQLite 3 мы рассмотрели некоторые основные команды, создание базы данных и таблицы и многое другое. В этом разделе мы углубимся в изучение знакомых функций из других баз данных и функций, которые выделяют SQLite.
Предполагается, что вы прошли вводный пост, прежде чем продолжить этот, или что вы знакомы с основами SQLite 3.
ВЫБЕРИТЕ Запрос
SELECT является стандартной командой SQL и принадлежит языку запросов данных (или DQL). Это позволяет выполнять запросы к базе данных и получать необходимые записи. Результаты могут быть дополнительно отфильтрованы с использованием различных предложений, предоставляемых SQLite.
Откройте терминал и введите: sqlite3 Library.db
Это создаст базу данных с именем Library.db в текущем каталоге. Теперь давайте создадим таблицу, в которой хранится информация о пользователях библиотеки. У пользователя библиотеки должно быть имя, уникальный идентификатор, предоставленный библиотекой, возраст пользователя и дата его присоединения. Следующий SQL-запрос создаст эту таблицу:
CREATE TABLE Users ( SerialNo INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Id TEXT NOT NULL UNIQUE, Age INTEGER NOT NULL, DOJ TEXT NOT NULL );
SerialNo — это первичный ключ.
Помните: в таблице может быть любое количество
UNIQUEстолбцов, но должен быть только одинPRIMARY KEY.
Вставьте запись в таблицу, используя следующий запрос:
INSERT INTO Users ( Name, Id, Age, DOJ) VALUES ( 'Shivam', 'U123', 19, '2015-01-31' );
Примечание: SQLite не имеет другого типа данных только для дат. Чтобы сохранить даты в SQLite, вы должны ввести их в
YYYY-MM-DD, иначе они будут считаться строками.
Вы можете вставить больше записей, используя приведенную выше команду, но это было бы утомительно для большого набора данных. Мы будем использовать мета-команду .read . Выполняет SQL-запросы из указанного файла. Сохраните следующее в файл с именем newusers.sql :
BEGIN TRANSACTION; CREATE TABLE NewUsers ( SerialNo INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Id TEXT NOT NULL UNIQUE, Age INTEGER NOT NULL, DOJ TEXT NOT NULL ); INSERT INTO NewUsers VALUES (1, 'Abraham', 'U123', 19, '2015-02-28'); INSERT INTO NewUsers VALUES (2, 'Michael', 'U124', 23, '2014-12-14'); INSERT INTO NewUsers VALUES (3, 'Steve', 'U125', 35, '2012-11-20'); INSERT INTO NewUsers VALUES (4, 'Ben', 'U126', 15, '2013-05-12'); INSERT INTO NewUsers VALUES (5, 'Alice', 'U127', 17, '2013-03-01'); INSERT INTO NewUsers VALUES (6, 'Christopher', 'U128', 19, '2014-05-03'); INSERT INTO NewUsers VALUES (7, 'Elena', 'U129', 27, '2011-07-14'); INSERT INTO NewUsers VALUES (8, 'Daniel', 'U130', 43, '2010-08-14'); INSERT INTO NewUsers VALUES (9, 'Candice', 'U131', 18, '2014-02-05'); INSERT INTO NewUsers VALUES (10, 'Billy', 'U132', 20, '2015-01-24'); INSERT INTO NewUsers VALUES (11, 'Michael', 'U133', 19, '2013-02-12'); INSERT INTO NewUsers VALUES (12, 'Alice', 'U134', 25, '2010-07-14'); INSERT INTO NewUsers VALUES (13, 'Richard', 'U135', 40, '2011-12-14'); INSERT INTO NewUsers VALUES (14, 'Lester', 'U136', 18, '2014-09-05'); INSERT INTO NewUsers VALUES (15, 'Malvo', 'U137', 21, '2015-01-04'); INSERT INTO NewUsers VALUES (16, 'Alice', 'U138', 19, '2013-02-02'); INSERT INTO NewUsers VALUES (17, 'Drako', 'U139', 24, '2010-12-02'); COMMIT;
и прочитайте его из командной строки sqlite, используя:
.read newusers.sql
Если все пойдет хорошо, у вас будет таблица NewUsers с новыми записями. Используйте запрос SELECT для их отображения.
SELECT Id, Name, Age, DOJ FROM NewUsers;
По умолчанию заголовки столбцов не отображаются, а режим отображения установлен на линию. Включите отображение заголовка, введя .header ON и измените стиль режима на столбец, используя .mode column . Введите запрос SELECT еще раз.
Совет: используйте команду .show для отображения значений различных настроек. Вы также можете указать ширину столбца вручную, используя мета-команду .width 1 .
Вы можете изменить количество отображаемых столбцов и указать другое имя (псевдоним) для заголовка. Например:
SELECT Id AS 'User ID', Name, DOJ AS 'Date of Joining' FROM NewUsers;
Вы можете использовать команду .schema для отображения всех операторов SQL, которые использовались для создания базы данных. При желании вы можете указать имя таблицы для отображения схемы конкретной таблицы.
ГДЕ оговорка
Предложение WHERE указывает условие для получения результатов. Например, чтобы получить идентификаторы и имена пользователей старше 20 лет, мы можем ввести следующий запрос.
SELECT Id, Name FROM NewUsers WHERE Age >= 20;
операторы
SQLite3 предоставляет ряд операторов для оптимизации предложения WHERE. Бинарные операторы в порядке убывания их приоритета:
|| * / % + - << >> & | < <= > >= = == != <> IS IS NOT IN LIKE GLOB MATCH REGEXP AND OR
SQLite3 поддерживает четыре унарных префиксных оператора, - + ~ NOT .
Помимо них, есть некоторые другие операторы, такие как BETWEEN и EXISTS .
МЕЖДУ
Предоставляет диапазон значений (от минимального до максимального) для работы. Например, чтобы выбрать пользователей с датой присоединения между 12-30-2011 и 12-30-2014 и возрастом от 17 до 27, мы можем ввести следующий запрос:
SELECT * FROM NewUsers WHERE DOJ BETWEEN '2011-12-30' AND '2014-12-30' AND Age BETWEEN 17 AND 27;
И, ИЛИ И НЕ
Оператор AND обеспечивает пересечение двух условий, где оператор OR обеспечивает объединение двух условий. Например, чтобы выбрать пользователей с возрастом более 20 и с серийным номером больше 5, введите запрос:
SELECT * FROM NewUsers WHERE Age > 20 AND SerialNo > 5;
Теперь, чтобы выбрать пользователей с возрастом> 20 или пользователей с серийным номером> 5, введите:
SELECT * FROM NewUsers WHERE Age > 20 OR SerialNo > 5;
Оператор NOT — это просто отрицание соответствующего условия. Пользователи, у которых нет возраста от 20 до 30 лет, могут быть извлечены с помощью:
SELECT * FROM NewUsers WHERE Age NOT BETWEEN 20 AND 30;
Есть и нет
IS и IS не работают так же, как = (равно) и ! = (Не равно) соответственно.
В, а не В
Оператор IN ищет значение в списке литералов. Например, для поиска пользователей с идентификаторами библиотек U124, U127 и U129:
SELECT * FROM NewUsers WHERE Id IN ('U124', 'U127', 'U129');
Как и GLOB
LIKE выполняет сравнение сопоставления с шаблоном, используя подстановочные знаки % и _ . Подстановочный знак % соответствует нулю или более символов, а _ соответствует любому одному символу Чтобы выбрать пользователей с именами, начинающимися с «B», заканчивающимися «y» и третьим последним символом как «l», мы можем ввести:
SELECT * FROM NewUsers WHERE Name LIKE "B%l_y";
Примечание: LIKE нечувствителен к регистру, поэтому «B%» и «b%» работают одинаково при использовании с LIKE.
GLOB похож на LIKE за исключением того, что он чувствителен к регистру и использует синтаксис глобализации файлов Unix .
СУЩЕСТВУЮТ
Оператор EXISTS оценивает либо в 0, либо в 1 . Если запрос справа от EXISTS не возвращает строк, тогда EXISTS возвращает ноль, а если запрос возвращает одну или несколько строк, EXISTS возвращает одну.
SELECT EXISTS ( SELECT * FROM NewUsers WHERE Age < 10 );
Это вернет ноль, так как нет пользователей с возрастом менее 10.
|| оператор
|| Оператор объединяет указанные строки. Приведенный ниже запрос даст результат: «sitepoint.com».
SELECT 'sitepoint' || '.' || 'com';
ЗАКАЗАТЬ по пункту
Как следует из этого, предложение ORDER BY сортирует записи в порядке возрастания или убывания. Он работает с определенным столбцом и принимает ASC в порядке возрастания и DESC в порядке убывания.
Например, чтобы отсортировать все пользовательские записи в порядке возрастания имени.
SELECT * FROM NewUsers ORDER BY Name ASC;
Чтобы отсортировать записи пользователей в порядке убывания возраста и даты присоединения после 2013-05-12 , необходимо ввести следующий запрос:
SELECT * FROM NewUsers WHERE DOJ > '2013-05-12' ORDER BY Age DESC;
ОГРАНИЧЕНИЕ
Предложение LIMIT ограничивает количество результатов запроса указанным числом. Он поставляется с необязательным флагом OFFSET, который объявляет количество пропускаемых записей. Например, чтобы выбрать шестую, седьмую и восьмую записи, нам нужно установить ограничение в три и смещение в 5.
SELECT * FROM NewUsers LIMIT 3 OFFSET 5;
Примечание . Вышеуказанный запрос также можно записать так:
SELECT * FROM NewUsers LIMIT 5, 3;
Предложение GROUP BY
GROUP BY объединяет несколько записей и группирует их, используя один или несколько столбцов. Например, чтобы подсчитать количество пользователей с определенным именем, мы можем ввести запрос:
SELECT Name, COUNT(Name) FROM NewUsers GROUP BY Name;
ИМЕЮЩИЙ пункт
Если нам нужно указать условие для групп, мы не сможем использовать предложение WHERE, поскольку предложение WHERE определяет условия для столбцов. Чтобы обеспечить условия для групп, используется предложение HAVING . В приведенном выше примере давайте извлекать пользователей с одинаковыми именами.
SELECT Name, COUNT(Name) FROM NewUsers GROUP BY Name HAVING COUNT(Name) > 1;
DISTINCT
Ключевое слово DISTINCT извлекает уникальные записи. Чтобы получить уникальные имена, введите следующий запрос:
SELECT DISTINCT Name FROM NewUsers ORDER BY Name;
Присоединение и отсоединение баз данных
При работе с несколькими базами данных псевдоним может быть присоединен к каждой базе данных. Команда ATTACH DATABASE присоединяет имя к базе данных. Введите .databases . Вы получите список имен баз данных и их соответствующих файлов. Основная база данных является основной базой данных. Помимо основной , существует временная, скрытая база данных temp . Оба они не могут быть ни присоединены, ни отсоединены от псевдонима. Давайте прикрепим Library.db к псевдониму LIB .
ATTACH DATABASE 'Library.db' AS 'LIB';
Рассмотрим другую базу данных Students.db . Прикрепите его к СТЮ .
ATTACH DATABASE 'Students.db' AS 'STU';
Введите .databases . Вы получите что-то вроде этого:
Столбец seq не имеет записи 1 между 0 и 2. На самом деле это скрытая временная база данных.
Теперь вы можете выполнять запросы к обеим базам данных в одном сеансе sqlite. Чтобы выбрать первые десять записей из LIB, мы можем ввести следующее:
SELECT * FROM LIB.NewUsers WHERE 1 LIMIT 10;
Предположим, STU содержит таблицу студентов с именами студентов. Нам нужно узнать подробности о пользователях библиотеки, которые являются студентами (для этого примера рассмотрим имена как столбцы как для студентов, так и для новых пользователей). Мы можем ввести следующий запрос:
SELECT LIB.NewUsers.Id, LIB.NewUsers.Name, LIB.NewUsers.Age FROM LIB.NewUsers INNER JOIN STU.Students ON STU.Students.Name = LIB.NewUsers.Name;
Чтобы отсоединить Library.db от псевдонима LIB, введите следующее:
DETACH DATABASE 'LIB';
операции
После создания базы данных мы можем выполнять запросы для создания и изменения записей в таблицах. Один или несколько из этих запросов, которые изменяют предыдущее состояние базы данных, вызывают транзакцию . Таким образом, транзакция может включать вставку, обновление и удаление записей. Это также включает создание, модификацию и удаление таблиц. Внутри транзакции может быть несколько небольших исполнений — рассмотрим базу данных банка. Пользователь снимает деньги, статус его счета обновляется, статус банка обновляется, база данных журнала получает новую запись и т. Д. — все это должно происходить безупречно, иначе деньги будут отсутствовать в одной таблице, создавая проблему для всех.
Когда вы выполняете запросы в форме транзакции, изменения не добавляются непосредственно в базу данных. Вы должны совершить их самостоятельно. Это помогает в случае возникновения ошибки или сбоя системы во время транзакции. В такой ситуации база данных не изменит свое состояние и не останется с частичными изменениями.
SQLite — это транзакционная база данных, то есть совместимая с ACID . ACID является аббревиатурой от атомарности, согласованности, изоляции и долговечности. Эти условия обсуждаются ниже.
- валентность
-
Атомность делает транзакции неделимыми. Либо изменения фиксируются сразу, либо вся транзакция завершается неудачно.
- консистенция
-
Каждая транзакция является последовательной, то есть конечное состояние, достигнутое после транзакции, также будет действительным.
- изоляция
-
Транзакции отделены друг от друга таким образом, что при их одновременном выполнении сбой одной не повлияет на выполнение других.
- долговечность
-
После успешной фиксации транзакции изменения в базе данных будут постоянными и невосприимчивыми к любым системным сбоям.
SQLite предлагает команды для управления транзакциями. В следующей таблице перечислены команды управления.
| команда | Описание |
|---|---|
| НАЧАЛО СДЕЛКИ | Отмечает начало транзакции. |
| ROLLBACK | Стирает текущую транзакцию. Если оно используется с ключевым словом TO, выполняется откат до определенной точки сохранения. |
| COMMIT | Передает транзакцию в базу данных. Это еще один псевдоним для КОНЕЧНОЙ СДЕЛКИ. |
Помимо этого есть еще две команды: SAVEPOINT и RELEASE. SAVEPOINT похожа на BEGIN, за исключением того, что вы должны указать имя для начальной транзакции, что позволяет вам вкладывать транзакции. RELEASE удаляет предопределенную точку сохранения и объединяет именованную транзакцию со своей родительской транзакцией.
Давайте продолжим с Library.db . Начните транзакцию, введя: BEGIN TRANSACTION; , Вставьте несколько допустимых записей в таблицу:
INSERT INTO NewUsers VALUES (18, 'Frank', 'U140', 45, '2009-11-02'); INSERT INTO NewUsers VALUES (19, 'Claire', 'U141', 43, '2009-11-01');
Чтобы сохранить эти изменения, вы можете ввести COMMIT; , Или, если вы хотите отказаться от них, введите ROLLBACK; ,
Оператор SAVEPOINT запускает транзакцию с именем. Создайте точку сохранения с именем SPT1 и добавьте новую запись.
SAVEPOINT SPT1; INSERT INTO NewUsers VALUES (20, 'Ricky', 'U142', 26, '2011-07-22');
Создайте еще одну точку сохранения SPT2 и добавьте еще одну запись.
SAVEPOINT SPT2; INSERT INTO NewUsers VALUES (21, 'Sundar', 'U143', 31, '2011-09-21');
Обе эти точки сохранения являются вложенными (SPT2 в SPT1). Вы можете зафиксировать их обоих, введя:
RELEASE SAVEPOINT SPT1;
Когда RELEASE применяется к самой внешней точке сохранения из вложенных точек сохранения, он фиксирует все точки сохранения, начиная с самой последней. Таким образом, SPT2 и SPT1 оба зафиксированы.
RELEASE SAVEPOINT SPT2;выпустит SPT2. Но поскольку он является дочерним по отношению к родительской транзакции SPT1, от него зависит его судьба. Если SPT1 откатывается, то и SPT2.
Чтобы отменить оба из них, вы можете выполнить откат до крайней точки сохранения, т.е. SPT1.
ROLLBACK TO SAVEPOINT SPT1;
Режим автоматической фиксации
Если вы выполняете запросы без явного определения транзакции, каждый запрос переносится в свою собственную транзакцию, а затем выполняется. Вы можете думать об этом как о каждом запросе с определенным BEGIN TRANSACTION и оператором COMMIT. Это режим автоматической фиксации. Режим автоматической фиксации включен по умолчанию.
Если вы явно запустили транзакцию, режим автоматического принятия будет отключен. После фиксации (или отката) той же транзакции снова включается режим автоматической фиксации.
Экспорт баз данных
В начале урока мы импортировали таблицу в базу данных с помощью команды .read . Для экспорта базы данных или таблицы мы используем команду .dump . .dump отображает базу данных в текстовом формате SQL. Вы можете использовать оператор перенаправления в командной строке, чтобы сохранить дамп в файл.
sqlite3 Library.db '.dump' > LibBackup.sql
Если файл LibBackup.sql уже существует, он перезаписывается. Приведенная выше команда выведет всю базу данных в файл. При желании вы можете .dump имя таблицы в качестве аргумента .dump . Это будет экспортировать эту конкретную таблицу. Чтобы вывести NewUsers, введите:
sqlite3 Library.db '.dump NewUsers' > LibBackup.sql
Выходные результаты
По умолчанию вывод, сгенерированный посредством запроса, записывается в стандартный вывод. SQLite предоставляет команду .output для записи результатов одного или нескольких запросов в файл.
Введите .output output.txt и несколько запросов. Результаты будут записаны в output.txt хранящийся в текущем рабочем каталоге. Вы можете выйти из сеанса sqlite и просмотреть файл.
.output stdoutотображает вывод на стандартный вывод.
Вывод
Мы узнали об операторах и выражениях SQLite. Учебное пособие также включает транзакции и краткое обсуждение транзакционной природы SQLite. Я надеюсь, что вы сочли полезным; если у вас есть какие-либо вопросы, не стесняйтесь задавать их в комментариях, и я сделаю все возможное, чтобы ответить на них.
-
.widthустанавливает ширину столбца для режима столбца. Укажите ширину символов в значениях, разделенных запятыми. например..width 10, 20, 15
