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 перечисляются данные, вставляемые в поля столбцов. Например:

INSERT INTO sections (_id, name) VALUES (1, 'information');

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

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

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

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

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

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

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

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

Оператор SELECT

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

SELECT * FROM <table_name>;

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

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

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

sqlite> SELECT * FROM sections;
1|information
2|Digital Systems
3|Boolean Algebra
sqlite> SELECT * FROM pages;
1|What is Information|information|1|1
2|Amount of Information|amount-information|1|2
sqlite> .mode
current output mode: list
sqlite> .mode csv
sqlite> SELECT * FROM pages;
1,"What is Information",information,1,1
2,"Amount of Information",amount-information,1,2
sqlite>

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

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

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

sqlite> .mode list
sqlite> .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). Примеры:

SELECT * FROM pages WHERE _id == 3;
SELECT * FROM pages WHERE theme == 2 AND num == 100;
SELECT * FROM pages WHERE theme <= 2;
SELECT _id, title FROM pages WHERE _id BETWEEN 2 AND 8;
SELECT _id, title FROM pages WHERE _id IN (1,2);
SELECT _id, title FROM pages WHERE _id NOT IN (1,3);

ORDER BY

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

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

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