Работа с операторами SQL

План:

1. Введение
2. Базы Данных
3. Настройка таблиц
4. Ввод значений в таблицы
5. Выбор данных
6. Запросы для нескольких таблиц
7. Операторы сравнений
8. Вспомогательные символы

В предыдущих статьях мы разобрались в устройстве клиента DB Browser и изучили основные операторы для выведения простых запросов. Сегодня же мы рассмотрим математические функции, научимся объединять несколько таблиц в единую, изучим принцип работы с подзапросами и элементами таблицы, а также ознакомимся с группами языков в SQLite.

Математические функции в SQLite

Помимо вывода запросов из таблиц Вы можете использовать математические операторы для примитивных вычислений. Хоть они и не отличаются высокой продвинутостью, но для базовых действий над элементами строк вполне удобны. Более сложные функции можно вводить вручную. Математические запросы в БД выполняются очень быстро и не так сильно нагружают компьютер.

Основные функции для математических вычислений следующие:

COUNT – вывод числа строк;
MAX – максимальное значение;
MIN – минимальное значение;
SUM – сумма элементов;
AVG – подсчёт среднего значения.

Далее мы рассмотрим примеры запросов с перечисленными операторами.

SELECT COUNT (country)
FROM phonebook
WHERE country like “Italy”;

Данный запрос подсчитывает число строк (COUNT*(*country)), из таблицы phonebook, удовлетворяющих условию (WHERE  country like “Italy”). Так мы получаем число людей, которые проживают в Италии.

SELECT COUNT(DISTINCT country)
FROM phonebook;

При такой записи мы выведем количество стран, отличающихся друг от друга и неповторяющихся в дальнейшем. В данном случае запрос указал четыре строки из пяти существующих.

SELECT SUM(Age)
FROM phonebook;

Этот запрос выводит результат суммирования всех элементов указанного столбца. С нашем случае это сумма возрастов всех абонентов. Запросы с использованием оператора SUM могут применяться только для числовых значений, на строковых полях эта функция работать не будет и никакой пользы не принесёт.

SELECT AVG(Age)
FROM phonebook;

Такой запрос выведет среднее значение чисел в строках. Как и в случае с SUM, AVG применяется только к числовым значениям. Остальные функции записываются аналогично, выводя соответствующие значения. Перейдём к действиям с математическими знаками.

SELECT Age+1
FROM phonebook;

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

SELECT PhoneNumber+Age
FROM phonebook

Также на числовыми значениями можно выполнять запросы вычитания, умножения, деления и прочих вычислений.

Подробнее с математическими функциями Вы можете ознакомиться по ссылке.

Объединение таблиц

Двигаемся дальше. В этом разделе разберём принцип создания запросов с объединёнными таблицами и формирования выходных данных для использования в сложных запросах.
Ниже представлены две таблицы, с которыми мы будем работать:

users1

users2

Для простого отображения всех таблиц нужно перечислить в запросе. Однако при объединении Вам понадобится новый оператор – UNION. Так будет выглядеть запрос с его использованием:

SELECT *

FROM users1

UNION

SELECT *
FROM users2

В качестве результата будет получена новая таблица, содержащая строки из обеих указанных таблиц. Также оператор UNION можно свободно объединять с любыми условиями для строк:

SELECT *

FROM users1

WHERE Device LIKE 'PC'

UNION

SELECT *

FROM users2

WHERE Device LIKE 'PC';

Обратите внимание! Для выполнения запроса без ошибок число полей и их типы в данных таблицах не должны отличаться друг от друга.

В случае, если какой-либо столбец отсутствует, его можно заполнить в запросе, указав любое символьное значение:

SELECT ID, FirstName, LastName, Age, Device

FROM users1

WHERE Device LIKE 'Phone'

UNION

SELECT ID, FirstName, 'Not Found', Age, Device

FROM users2

WHERE Device LIKE 'Phone';

Вместо несуществующего столбца LastName в users2 мы добавляем значение ‘Not Found‘, благодаря чему число столбцов в двух таблицах совпадает.

Вы также можете упорядочить вывод элементов запроса по выбранному столбцу. Для этого достаточно в конце записи применить оператор ORDER BY. К примеру:

SELECT ID, FirstName, LastName, Age, Device

FROM users1

WHERE Device LIKE 'Phone'

UNION

SELECT ID, FirstName, 'Not Found', Age, Device

FROM users2

WHERE Device LIKE 'Phone';

ORDER BY 1

В данном случае строки будут упорядочены по первому столбцу ID.

Подзапросы

Продолжая изучать SQLite, поговорим о подзапросах. Имеются ввиду дополнительные запросы, находящиеся внутри основных запросов. Приведём пример такого запроса:

SELECT *

FROM users1

WHERE ID =(SELECT ID

FROM users2

WHERE Device LIKE 'Tablet');

Нам требуется вывести все строки из users1, ID которых соответствует ID пользователей Tablet в users2. В первую очередь будет выполнен вложенный запрос в скобках (SELECT ID FROM users2 WHERE Device LIKE ‘Tablet‘), после чего результат будет передан внешнему запросу. В результате мы получим данные строки users1, ID которой будет равен 3.

Для стаблильного выполнения запросов с внутренними запросами следует помнить следующее: во вложенном запросе может находиться только один конкретный столбец, а не таблица полностью. Также типы данных внутреннего и внешнего запроса должны совпадать (ID user1 и ID users2 оба содержат числовые значения). В качестве конечного результата программой выводится одна строка. Чтобы избежать ошибок в виде пустого результата или вывода нескольких строк воспользуйтесь оператором DISTINCT.
Существует также запрос-исключение, выводящий несколько значений:

SELECT *

FROM users1

WHERE ID IN(SELECT ID

FROM users2

WHERE Device LIKE 'PC');

Оператор IN во внешней части запроса (WHERE ID IN) выводит все соответствующие требованию значения, избегая возможных ошибок с некорректным подсчётом строк.

Действия над полями

Любая база данных нуждается в периодической корректировке данных таблиц и строк. В клиентах по типу DB Browser достаточно перейти на нужную вкладку и внести соответствующие изменения. Однако при работе через консоль Вам понадобятся дополнительные операторы: INSERT (вставить), UPDATE (обновить) и DELETE (удалить).

С командой INSERT мы познакомились в прошлой статье. Освежим в памяти:

INSERT INTO phonebook (FirstName, LastName, Age, PhoneNumber,Country)

VALUES ('Mike', 'Stone', '23', '90134679552', 'Germany');

INSERT INTO phonebook (FirstName, LastName, Age, PhoneNumber, Country)

VALUES ('Paula', 'Bronte', '19', '79468524693', 'Italy');

Общая запись команды следующая:

INSERT INTO <таблица>
VALUES (Значение_1, Значение_2…);

Если требуется ввести значения только в некоторые столбцы, то перечислять их нужно вручную. Ввод значений также осуществляется вручную с соблюдением указанной последовательности элементов таблицы. Не забывайте об установленных типах данных в столбцах, чтобы запрос применился без ошибок. Пустые значения обозначаются при перечислении данных как NULL.

Чтобы добавить полученный из запроса результата в таблицу, Вам также необходимо применить оператор INSERT INTO:

INSERT INTO users1
SELECT *
FROM users2
WHERE Device LIKE 'PC'

В первую очередь выполнится стандартный запрос с оператором SELECT, после чего результат добавится к таблице users1. При одинаковом числе и типе столбцов в записи запрос успешно выполнится.

INSERT INTO users1 (Name, Device)
SELECT Name, Device
FROM users2
WHERE Device LIKE 'PC'

В этом случае выбрана часть столбцов, которая будет в последующем перенесена в таблицу users1. Этим же способом вы можете добавлять элементы строк из более сложных запросов.

Следующим рассмотрим UPDATE:

UPDATE users1
SET Device = 'PC'

В запросе мы обновляем таблицу users1 (UPDATE users1), после чего устанавливаем значение для всех строк столбца Device значение ‘PC‘ (SET Device LIKE ‘PC‘). Если же требуется изменить значение только одного столбца, то в запись добавляется дополнительная строка:

UPDATE users1
SET Device = 'PC'
WHERE FirstName LIKE 'Ronald'

Таким образом мы уточняем, какие строки должны быть изменены. В результате значение поля Device пользователя Ronald изменится на PC.

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

UPDATE users1
SET Device = 'PC', Age=Age+3
WHERE FirstName LIKE 'Ronald'

Как результат Device будет сменён на PC, а также увеличится значение поля Age на 3 единицы для строки, где Имя равняется ‘Ronald‘.

DELETE записывается схожим образом:

DELETE FROM users1

Запрос удаляет все существующие строки в таблице users1. Для удаления отдельных строк указываются критерии выбора.

DELETE FROM users1
WHERE Device LIKE 'Phone'

Команды групп DDL,DQL,DML,DCL,TCL

Напоследок затронем тему внутренних языков SQL. Для упрощения понимания и использования команд в языке SQLite существуют подъязыки:

• DDL – Data Definition Language (Язык определения данных)
• DQL – Data Query Language (Язык запросов данных)
• DML – Data Manipulation Language (Язык обработки данных)
• DCL – Data Control Language (Язык управления данными)
• TCL – Transaction Control Language (**Язык** **управления** **транзакциями**)