Расти вверх будет только тот, кто не боится делать выбор.
Джордж Элиот
Выборка данных. SELECT
SELECT – это самая популярная конструкция в языке SQL. Она предназначена для извлечения данных. Хотя SQL предполагает некий стандарт, при реализации этой команды для конкретной СУБД имеются некоторые особенности.
В общем виде команда имеет следующую структуру:
SELECT <что извлечь> FROM <откуда> [ WHERE <условие> ][<опции> ]
Code language: HTML, XML (xml)
- <откуда> – таблица или набор данных (объединение данных из нескольких таблиц)
- <что извлечь> – описание набор данных, которые нужно получить
- <условие> – описание условия отбора данных
- <опции> – дополнительные действия (сортировка, группировка и ограничения)
При этом <что извлечь> и <условие> – это выражения, в которых используются названия полей, операторы и встроенные функции.
SELECT name, price FROM invoice
Code language: SQL (Structured Query Language) (sql)
Для того, чтобы выбрать все поля из таблицы можно указать символ “*”.
SELECT * FROM invoice
Code language: SQL (Structured Query Language) (sql)
Операторы
При записи выражений на языке SQL используются операнды (данные) и операторы. Операторы – это действия, выполняемые с операндами. Операторы бывают нескольких видов. Арифметические и побитовые операторы выдают числовой результат, а строковые операторы возвращают текст. Перед выполнением операции значения операндов приводятся к типу результата операции. Операторы могут быть использованы при составлении выражений – конечной последовательности операторов и операндов, которые могут быть вычислены для получения значения.
Таблица 1. Арифметические операторы
Оператор | Описание | Выражение |
---|---|---|
+ | Сложение чисел | a + b |
– | Вычитание чисел | a – b |
* | Умножение чисел | a * b |
/ | Деление чисел | a / b |
% | Остаток от деления | a % b |
– | Смена знака | – а |
🔨 Выбрать данные из накладной: название товара, цена, количество; сумму вычислить как произведение цены на количество:
SELECT name, price, qty, price*qty FROM invoice
Code language: SQL (Structured Query Language) (sql)
Таблица 2. Побитовые операторы
Оператор | Описание | Выражение |
---|---|---|
>> | Двоичный оператор правого сдвига. Значение левых операндов перемещается вправо на количество бит, заданных правым операндом. | a >> b |
<< | Двойной левый оператор сдвига. Значение левых операндов перемещается влево на количество бит, заданных правым операндом. | a << b |
& | Побитовое И | a & b |
| | Побитовое ИЛИ | a | b |
^ | Побитовое исключающее ИЛИ | a ^ b |
🔨 Отобразить результат логической операции побитового сложения двух масок:
SELECT mask1, mask2, mask1 | mask2 FROM pattern
Code language: SQL (Structured Query Language) (sql)
Таблица 3. Строковые операторы
Оператор | Описание | Выражение |
---|---|---|
|| | Объединение строк | a || b |
🔨 Отобразить фамилию, имя и отчество одной строкой:
SELECT name || surname || patronomic FROM person
Code language: SQL (Structured Query Language) (sql)
Таблица 4. Операторы сравнения
Оператор | Описание | Выражение |
---|---|---|
= | Равно | a = b |
== | Равно | a == b |
<> | Не равно | a <> b |
!= | Не равно | a != b |
> | Больше, чем | a > b |
>= | Больше или равно | a >= b |
< | Меньше, чем | a < b |
<= | Меньше или равно | a <= b |
!> | Не больше, чем | a !> b |
!< | Не меньше чем | a !< b |
🔨 Отобразить товары и стоимость, сравнить стоимость товаров с заданным значением (100). Для товаров, стоимость которых превышает или равна 100, получим значение 1 (логическое значение “True”).
SELECT name, price, price >= 100 FROM goods
Code language: SQL (Structured Query Language) (sql)
Фильтрация данных. WHERE
Команда SELECT выбирает указанные данные (поля) из указанного источника (таблицы). Но что, если нам нужно выбрать не все данные, а только те, которые отвечают определенным условиям? Для этого используется ключевое слово WHERE:
SELECT <данные> FROM <источник> WHERE <условие>
Code language: HTML, XML (xml)
Теперь в результирующий набор попадут только те записи, которые удовлетворяют заданному условию. Условие может быть простым (из одного логического выражения) или сложным. Также можно использовать в качестве значения выражения подзапрос.
В логическом выражении могут быть использованы операторы сравнения (таб.4) и логические операторы (таб.5).
Таблица 5. Логические операторы
Оператор | Описание | Выражение |
---|---|---|
AND | Логическое “И” | a and b |
OR | Логическое “ИЛИ” | a or b |
IN ( ) | Соответствует значению в списке | a in (b,c,d) |
NOT | Отрицает условие. Унарный оператор | not a |
BETWEEN | В пределах диапазона (включительно). Используется для чисел. | a between c and d |
IS NULL | Значение NULL | a is null |
IS NOT NULL | Значение, отличное от NULL | a is not null |
LIKE | Соответствие шаблону с использованием % и _ . Можно добавить исключение после ключевого слова ESCAPE. | a like ‘b%’ |
GLOB | Соответствие шаблону с использованием * и ?. Аналогично LIKE | a glob ‘b*’ |
EXISTS | Условие выполняется, если подзапрос возвращает хотя бы одну строку. Оператор EXISTS всегда возвращает одно из двух значений 0 (ложь) или 1 (истина). Если подзапрос возвращает одно или более значений, тогда оператор EXISTS вернет 1 (истина). Если подзапрос в выражении не возвращает ни одной строки, тогда EXISTS вернет 0 (ложь). Количество возвращаемых подзапросом столбцов не имеет значения. Также не имеет значения, есть ли в возвращаемых столбцах NULL значения. | |
MATCH | Расширение для определения пользовательской функции сравнения. | |
REGEXP | Позволяет использование регулярных выражений https://ru.wikipedia.org/wiki/Регулярные_выражения |
🔨 Отобразить товары и стоимость, стоимость товаров должна лежать в диапазоне от 100 до 200
SELECT name, price FROM goods WHERE price BETWEEN 100 AND 200
Code language: SQL (Structured Query Language) (sql)
☝ Оператор MATCH в My Visual Database не поддерживается.
Объединение данных. JOIN
Если необходимо извлечь данные из нескольких таблиц, то их необходимо объединить с помощью команды JOIN.
SELECT <данные> FROM <главный источник> [ <тип объединения> <источник данных> | ON <условие объединения> | ]
Code language: HTML, XML (xml)
Как видно из приведенной выше схемы, для каждой объединяемой таблицы можно задать свои тип и условие объединения с другой таблицей. Количество объединяемых таблиц ограничено только вашими возможностями понимания структуры запроса. Однако следует иметь ввиду, что хотя СУБД умеет оптимизировать SQL-запросы, не стоит увлекаться чрезмерной вложенностью или сложностью условий – это может снизить надежность кода по причине его плохой читаемости.
Таблица 6. Типы объединения
Ключевое слово | Тип | Описание |
---|---|---|
INNER JOIN | Пересечение | В результате окажутся только те записи, для которых четко выполняется условие объединения. |
LEFT [OUTER] JOIN | Присоединение | В результате окажутся все записи из таблицы 1 и только те записи из таблицы 2, для которых четко выполняется условие объединения. |
CROSS JOIN | Умножение | В результате окажутся сочетания всех записей из таблицы 1 со всеми записями из таблицы 2 |
🔨 Отобразить список сотрудников с указанием отдела, в котором он работает. Сотрудники без указания отдела в списке будут отсутствовать.
SELECT employee.name, dep.name
FROM employee
INNER JOIN dep ON dep.id = employee.id_dep
Code language: SQL (Structured Query Language) (sql)
🔨 Отобразить список всех сотрудников с указанием отдела, в котором он работает.
SELECT employee.name, dep.name
FROM employee
LEFT JOIN dep ON dep.id = employee.id_dep
Code language: SQL (Structured Query Language) (sql)
Если можно однозначно определить, к какой таблице относится поле, то имя таблицы перед названием поля можно не указывать. В противном случае (например, если в запросе участвуют несколько таблиц в которых есть поля с одинаковыми названиями) необходимо явно указывать, к какой таблице относится поле.
Сортировка данных. ORDER BY
Данные в запросе обычно выдаются в порядке их добавления в базу данных, но на это нельзя полагаться, так как стандарт SQL не предполагает неявной сортировки результата. Явная сортировка задается специальной командой:
SELECT <данные> FROM <источник> [ <условие отбора> ] [ <условие группировки> ] ORDER BY <сортировка> [ <ограничение выборки> ]
Сортировка может быть задана несколькими способами:
1. указанием перечня названий полей
2. указанием перечня номеров полей в запросе
После каждого поля или номера может быть указано направление сортировки:
Таблица 7. Направления сортировки
Код | Описание |
ASC | По возрастанию. Используется по умолчанию. |
DESC | По убыванию. |
Скорость выполнения сортировки зависит от того, имеется ли для указанного поля индексация или нет.
☝ My Visual Database не имеет встроенных инструментов управления индексацией.
🔨 Отобразить список всех сотрудников с указанием отдела, в котором он работает.
SELECT employee.name, dep.name FROM employee LEFT JOIN dep ON dep.id = employee.id_dep
Code language: SQL (Structured Query Language) (sql)
Объединение результатов нескольких запросов. UNION
Вы можете объединить результаты отдельных запросов при соблюдении нескольких условий:
- Количество колонок в запросах должно быть одинаковым.
- Соответствующие колонки должны иметь совместимые типы.
- Название полей результирующего запроса берутся из первого запроса.
- Группировки действуют только в пределах каждого отдельного запроса.
- Сортировка действует только для всей выборки.
SELECT <параметры выборки>
UNION [ALL]
SELECT <параметры выборки>
UNION [ALL]
…
SELECT <параметры выборки>
[ORDER BY <параметры сортировки>]
Code language: SQL (Structured Query Language) (sql)
Таблица 8. Варианты объединения
Ключевое слово | Тип | Описание |
UNION | Объединить и исключить повторяющиеся строки | В результате окажутся неповторяющиеся записи из обоих запросов |
UNION ALL | Объединить все строки | В результате окажутся все записи из обоих запросов. |
Обычно UNION используют для объединения данных из разных таблиц.
SELECT id FROM item
UNION
SELECT id FROM test
Code language: SQL (Structured Query Language) (sql)
Исключение определенных данных. EXCEPT
Вы можете исключить результат одного запроса из результата другого запроса при соблюдении нескольких условий:
- Количество колонок в запросах должно быть одинаковым.
- Соответствующие колонки должны иметь совместимые типы.
- Название полей результирующего запроса берутся из первого запроса.
SELECT <параметры выборки>
EXCEPT
SELECT <параметры выборки>
EXCEPT
…
SELECT <параметры выборки>
[ORDER BY <параметры сортировки>]
Code language: SQL (Structured Query Language) (sql)
Таблица 9. Варианты исключения
Ключевое слово | Тип | Описание |
EXCEPT | Исключить повторяющиеся строки | В результате окажутся уникальные записи из первого запроса. |
Обычно EXCEPT используют для получения данных из разных таблиц.
SELECT id FROM item
EXCEPT
SELECT id FROM test
Code language: SQL (Structured Query Language) (sql)
Удаление дубликатов. DISTINCT
Для удаления из результирующего набора повторяющихся записей необходимо после оператора SELECT добавить оператор DISTINCT:
SELECT DISTINCT <данные> FROM <источник> [<условие отбора>] [<условие группировки>] [<условие сортировки>] [<ограничение выборки>]
Code language: SQL (Structured Query Language) (sql)
Ограничение выборки. LIMIT, OFFSET
Иногда требуется ограничить количество записей, возвращаемых запросом. Например, требуется только первая запись. Или для увеличения производительности реализован постраничный просмотр данных. В этих случаях пригодится следующий синтаксис:
SELECT <данные> FROM <источник> [ <условие отбора> ] [ <условие группировки> ] [ <условие сортировки> ] LIMIT <число записей> OFFSET <смещение от начала>
Code language: SQL (Structured Query Language) (sql)
где <число записей> определяет максимальное количество записей, возвращаемое данным запросом, а <смещение от начала> – количество записей, которые нужно пропустить от начала выборки.
А теперь немного практики.