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');

Если в таблице есть столбцы с ограничителями 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.

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

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

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;

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

sqlite> SELECT _id, title
   ...> FROM pages WHERE
   ...> _id BETWEEN 2 AND 8;
3|Amount of Information
4|Binary System
5|Octal System
6|Lows of Logic Algebra

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);
4|Binary System
5|Octal System
6|Lows of Logic Algebra

ORDER BY

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

sqlite> SELECT url, title, theme 
   ...> FROM pages
   ...> ORDER BY url ASC;
amount-information|Amount of Information|1
binary|Binary System|2
information|What is Information|1
logic-low|Lows of Logic Algebra|3
octal|Octal System|2

sqlite> SELECT url, title FROM pages
   ...> WHERE theme == 1
   ...> ORDER BY url DESC;
information|What is Information
amount-information|Amount of Information

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