INSERT и SELECT – добавление и выборка данных

В теории реляционных баз данных часто фигурирует акроним CRUD, образованный от слов Create, Read, Update, Delete. Обозначает он тот факт, что данные в БД можно

  • добавлять,

  • считывать их оттуда,

  • изменять,

  • удалять.

То есть CRUD обозначает четыре базовых действия с данными, хранимыми в таблицах БД.

При этом операторы языка SQL не обязательно совпадают со словами create, read, update, delete. Так в SQL нет оператора READ, вместо него используется SELECT. Записи-строки в таблицу вставляются не с помощью CREATE, а оператором INSERT.

Оператор INSERT

С помощью оператора INSERT языка SQL выполняется вставка данных в таблицу. Синтаксис команды:

INSERT INTO <table_name> (<column_name1>, <column_name2>, ...) VALUES (<value1>, <value2>, …);

После INSERT INTO указывается имя таблицы, после в скобках перечисляются столбцы. После слова VALUES перечисляются данные, вставляемые в поля столбцов. Например:

sqlite> INSERT INTO sections (_id, name) VALUES (1, 'Information');

При этом столбцы не обязательно должны перечисляться в том же порядке, в котором задавались при создании таблицы:

sqlite> INSERT INTO sections (name, _id) VALUES ('Boolean Algebra', 3);

Однако, поскольку можно вообще не указывать имена столбцов, в этом случае при указании значений их порядок должен совпадать со схемой таблицы:

sqlite> INSERT INTO sections VALUES (2, 'Digital Systems');

INSERT INTO

Если в таблице есть столбцы с ограничителями AUTOINCREMENT и DEFAULT, то есть автозаполняемые, значения для них можно не указывать. Однако в этом случае должен быть указан перечень столбцов для остальных значений:

sqlite> INSERT INTO pages VALUES 
   ...> (1, 'What is Information', 'information', 1, 1);
sqlite> INSERT INTO pages (title, url, theme, num) VALUES 
   ...> ('Amount of Information', 'amount-information', 1, 2);

Здесь в первом случае мы вручную задаем значение для поля _id и передаем данные во все остальные поля. Поэтому можем опустить перечисление столбцов. Во втором случае СУБД будет самостоятельно заполнять поле _id. Во избежание неоднозначности мы должны перечислить остальные столбцы.

Вставка значений с автоинкрементом

Обратите внимание, в SQLite мы должны включать поддержку внешнего ключа, чтобы работал ограничитель FOREIGN KEY и не давал нам добавлять записи с номерами тем, которых нет в таблице sections.

Причина, по которой СУБД присвоила второй записи идентификатор 3, а не 2, заключается в том, что раньше в таблицу уже помещались данные, но были удалены.

Оператор SELECT

С помощью оператора SELECT осуществляется выборочный просмотр данных из таблицы. В простейшем случае оператор имеет следующий синтаксис, где вместо <table_name> указывается имя таблицы:

SELECT * FROM <table_name>;

Такая команда отображает значения всех столбцов и строк заданной таблицы. На выборку всех столбцов указывает звездочка после слова SELECT. А все строки будут выбраны потому, что после имени таблицы нет оператора WHERE языка SQL. WHERE позволяет задавать условие, согласно которому отображаются только удовлетворяющие ему строки.

Утилита sqlite3 позволяет отображать данные таблицы в различных форматах, которые задаются с помощью команды .mode, после которой указывается необходимый режим. Команда .mode без аргумента показывает текущий режим.

Обратите внимание, на скринах выше разделителями между столбцами является вертикальная черта. Это режим list. Посмотреть все доступные режимы можно с помощью команды .help .mode.

Режимы отображения в sqlite3

Чтобы отобразить заголовки используется команда .header on. Они хорошо сочетаются с режимом column. Для отключения заголовков используется .header off:

sqlite> .mode column
sqlite> .header on
sqlite> SELECT * FROM pages;
_id         title                url          theme       num       
----------  -------------------  -----------  ----------  ----------
1           What is Information  information  1           1         
3           Amount of Informati  amount-info  1           2         

Когда требуется просмотреть только отдельные столбцы, то вместо звездочки их имена перечисляются через запятую:

sqlite> SELECT title, theme FROM pages;
What is Information|1
Amount of Information|1

WHERE

Условие WHERE используется не только с оператором SELECT, также с UPDATE и DELETE. С помощью WHERE определяются строки, которые будут выбраны, обновлены или удалены. По сути это фильтр.

После ключевого слова WHERE записывается логическое выражение, которое может быть как простым (содержащим операторы = или ==, >, <, >=, <=, !=, BETWEEN), так и сложным (AND, OR, NOT, IN, NOT IN). Примеры:

sqlite> SELECT * FROM pages WHERE _id == 3;
sqlite> SELECT * FROM pages WHERE theme == 2 AND num == 100;
sqlite> SELECT * FROM pages WHERE theme <= 2;

Использование оператора WHERE

Примеры с BETWEEN и IN:

sqlite> SELECT _id, title FROM pages WHERE _id BETWEEN 3 AND 8;
3|Amount of Information
7|Binary System
8|Boolean Lows
 
sqlite> SELECT _id, title FROM pages WHERE _id IN (1,2);
1|What is Information
 
sqlite> SELECT _id, title FROM pages WHERE _id NOT IN (1,3);
7|Binary System
8|Boolean Lows

ORDER BY

При выводе данных их можно не только фильтровать с помощью WHERE, но и сортировать по возрастанию или убыванию с помощью оператора ORDER BY.

sqlite> SELECT url,title,theme FROM pages ORDER BY url ASC;
sqlite> SELECT url,title FROM pages WHERE theme == 1 ORDER BY url DESC;

ASC – сортировка от меньшего значения к большему. DESC – сортировка от большего значения к меньшему.

Сортировка - ORDER BY