1️⃣ Основы выборки
Базовый синтаксис SQL запроса
Одна из основных функций SQL — это получение выборок данных из СУБД. Для этого в SQL используется оператор SELECT. Давайте рассмотрим несколько простых запросов с его участием.
Вывод произвольных значений
Для начала важно понимать, что через оператор SELECT можно выводить данные не только из таблиц базы данных, но и произвольные строки, числа, даты и т.д. Например, так можно вывести произвольную строку:
MySQL
SELECT "Hello world"
Вывод всех данных из таблицы
Для вывода всех полей из определённой таблицы используется символ *. Давайте взглянем на схему базы данных и выведем данные одной из таблиц
SELECT * FROM FamilyMembers
| member_id | status | member_name | birthday |
|---|---|---|---|
| 1 | father | Headley Quincey | 1960-05-13T00:00:00.000Z |
| 2 | mother | Flavia Quincey | 1963-02-16T00:00:00.000Z |
| 3 | son | Andie Quincey | 1983-06-05T00:00:00.000Z |
| 4 | daughter | Lela Quincey | 1985-06-07T00:00:00.000Z |
| 5 | daughter | Annie Quincey | 1988-04-10T00:00:00.000Z |
| 6 | father | Ernest Forrest | 1961-09-11T00:00:00.000Z |
| 7 | mother | Constance Forrest | 1968-09-06T00:00:00.000Z |
Вывод данных из определённых колонок таблицы
Если необходимо вывести информацию только по определённым столбцам таблицы, а не всю сразу, то это можно сделать, перечисляя названия столбцов через запятую:
SELECT member_id, member_name FROM FamilyMembers
| member_id | member_name |
|---|---|
| 1 | Headley Quincey |
| 2 | Flavia Quincey |
| 3 | Andie Quincey |
| 4 | Lela Quincey |
| 5 | Annie Quincey |
| 6 | Ernest Forrest |
| 7 | Constance Forrest |
Псевдонимы
В случае, если мы хотим вывести какие-то столбцы таблицы, но чтобы в итоговой выборке они были названы иначе, мы можем использовать псевдонимы (их также называют алиасами).
Их синтаксис достаточно простой, мы должны использовать оператор AS. Как в примере ниже:
SELECT member_id, member_name AS Name FROM FamilyMembers
| member_id | Name |
|---|---|
| 1 | Headley Quincey |
| 2 | Flavia Quincey |
| 3 | Andie Quincey |
| 4 | Lela Quincey |
| 5 | Annie Quincey |
| 6 | Ernest Forrest |
| 7 | Constance Forrest |
Или же можно обойтись и без него, просто написав желаемое наименование поля через пробел.
SELECT member_id, member_name Name FROM FamilyMembers
Псевдонимы могут содержать до 255 знаков (включая пробелы, цифры и специальные символы).
Литералы в SQL
На прошлом уроке выводили строку, а если говорить более формальным языком, то так называемый строковый литерал.
MySQL
SELECT "Hello world"
Литерал — это указанное явным образом фиксированное значение, например, число 12 или строка "SQL".
Основными типами литералов в MySQL являются:
- строковый
- числовой
- логический
- NULL
- литерал даты и времени
Строковые литералы
Строка — это последовательность символов, заключённых в одинарные (') или двойные (") кавычки. Например, 'это строка' и "это строка".
Строки могут содержать специальные последовательности символов, начинающиеся с "\" (экранирующий символ). Они нужны для того, чтобы СУБД придала обычным символам (буквам и другим знакам) новое особое значение. Например, последовательность "\n" буквально означает "перевод строки", а без предваряющего слеша это была бы обычная буква "n".
Числовые литералы
| Пример | |
|---|---|
| Включает в себя целые и дробные числа. Разделительный знак для дробного числа — «.» (точка). | 1, 2.9, 0.01 |
| Может иметь только целую, дробную часть или обе сразу. | .2, 1.1, 10 |
| Может быть положительным и отрицательным числом (для положительного числа совсем не обязательно указывать знак). | +1, -10, -2.2 |
| Могут быть представлены в экспоненциальном виде. | 1e3 (=1000) 1e-3 (=0.001) |
Арифметические операторы
Для числовых литералов в SQL есть все привычные нам арифметические операторы:
Литералы даты и времени
Значения даты и времени могут быть представлены в формате строки или числа.
Например, если мы хотим указать какую-то дату в запросе, то мы можем это сделать с помощью строки "1970-12-30", "19701230" или же числа 19701230. В обоих случаях эти значения будут интерпретироваться как дата «30 декабря 1970 года».
Логические литералы
Логический литерал - значения TRUE и FALSE, означающие истинность и ошибочность какого-либо утверждения. При интерпретации запроса, MySQL преобразует их в числа: TRUE и FALSE становятся 1 и 0 соответственно.
NULL
Значение NULL означает "нет данных", "нет значения". Оно нужно, чтобы отличать визуально пустые значения, такие как строка нулевой длины или "пробел", от того, когда значения вообще нет, даже пустого.
Применение функций
При составлении SQL запросов мы можем использовать встроенные функции. Например, если мы хотим вывести строку в верхнем регистре, то для этого мы можем использовать функцию UPPER.
Что такое встроенная функция?
Встроенная функция – реализованный в СУБД кусок кода, с помощью которого можно выполнять преобразования строковых, числовых и других данных в запросах.
Каждая функция принимает набор аргументов определённого типа, выполняет заложенные в неё операции и обязательно возвращает один из возможных литералов. Стоит отметить, что функции могут принимать как ноль аргументов, так и несколько.
Например, функция NOW() принимает ноль аргументов и возвращает литерал в формате даты, а LENGTH('sql-academy') принимает один строковый аргумент и возвращает числовой литерал «11».
Исключение дубликатов, DISTINCT
Чтобы при выборке избежать дублирования, есть оператор DISTINCT.
Синтаксис оператора
SELECT [DISTINCT] поля_таблиц FROM наименование_таблицы;
Условный оператор WHERE
Ситуация, когда требуется сделать выборку по определённому условию, встречается очень часто. Для этого в операторе SELECT существует оператор WHERE, после которого следуют условия для ограничения строк. Если запись удовлетворяет этому условию, то попадает в результат, иначе отбрасывается.
Общая структура запроса с оператором WHERE
SELECT [DISTINCT] поля_таблиц FROM наименование_таблицы
WHERE условие_на_ограничение_строк
[логический_оператор другое_условие_на_ограничение_строк];
Операторы IS NULL, BETWEEN, IN
Мы уже познакомились с синтаксисом оператора WHERE и операторами сравнения, но помимо них в условных запросах мы можем использовать следующие полезные операторы:
- IS NULL
- BETWEEN
- IN
Давайте рассмотрим их применение.
IS NULL
Оператор IS NULL позволяет узнать, равно ли проверяемое значение NULL, т.е. пустое ли значение.
BETWEEN
Оператор BETWEEN min AND max позволяет узнать, расположено ли проверяемое значение столбца в интервале между min и max, включая сами значения min и max. Он идентичен условию:
... WHERE field >= min AND field <= max
IN
Оператор IN позволяет узнать, входит ли проверяемое значение столбца в список определённых значений.
Оператор LIKE
Оператор LIKE используется при условных запросах, когда мы хотим узнать, соответствует ли строка определённому шаблону.
ESCAPE-символ
ESCAPE-символ используется для экранирования специальных символов (% и \). В случае если вам нужно найти строки, вы можете использовать ESCAPE-символ.
Оператор REGEXP
Оператор REGEXP (или его синоним RLIKE) в SQL используется для поиска и обработки строковых данных с помощью регулярных выражений. Регулярные выражения предоставляют мощные возможности для сложных шаблонов поиска, которые трудно реализовать с помощью оператора LIKE.
Когда использовать REGEXP вместо LIKE?
Оператор LIKE удобен для простых шаблонов поиска, таких как поиск строк, начинающихся или заканчивающихся на определённый набор символов, или содержащих определённые подстроки. Однако, если требуется более сложный и гибкий поиск, например, поиск по нескольким условиям или использование специальных символов и диапазонов, оператор REGEXP станет незаменимым инструментом.
:::info Я бы рекомендовал просто выучить REGEXP. У нас нет таких задач, где бы потребовался прирост скорости от LIKE. Поэтому просто можно использовать REGEX. Его плюс в широте использования и применении
:::
Важные нюансы
- Регистронезависимость
По умолчанию регулярные выражения в MySQL не чувствительны к регистру. Например, выражение REGEXP 'abc' найдёт строку и abc, и Abc, и ABC. 2. Специальные символы
Некоторые символы имеют особое значение в регулярных выражениях и требуют экранирования (например, ., *, +, ?, [, ], (, ), {, }, |, \). Для экранирования используется обратная косая черта \.
Сортировка, оператор ORDER BY
При выполнении SELECT запроса, строки по умолчанию возвращаются в неопределённом порядке. Фактический порядок строк в этом случае зависит от плана соединения и сканирования, а также от порядка расположения данных на диске, поэтому полагаться на него нельзя. Для упорядочивания записей используется конструкция ORDER BY.
Общая структура запроса с оператором ORDER BY
SELECT поля_таблиц FROM наименование_таблицы
WHERE ...
ORDER BY столбец_1 [ASC | DESC][, столбец_n [ASC | DESC]]
Группировка, оператор GROUP BY
Общая структура запроса с GROUP BY
SELECT [литералы, агрегатные_функции, поля_группировки]
FROM имя_таблицы
GROUP BY поля_группировки;
Для того, чтобы записи у нас образовали группы по типу жилья, мы должны после GROUP BY указать home_type, т.е. поле, по которому будет происходить группировка.
:::warning Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, т.е. при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу
:::
Агрегатные функции
В статье о группировках мы обсудили, что при использовании оператора GROUP BY мы можем использовать агрегатные функции. Давайте поговорим о них подробнее 🐳.
Агрегатная функция – это функция, которая выполняет вычисление на наборе значений и возвращает одиночное значение.
Общая структура запроса с агрегатной функцией
SELECT [литералы, агрегатные_функции, поля_группировки]
FROM имя_таблицы
GROUP BY поля_группировки;
Например, запрос с использованием агрегатной функции AVG может выглядеть так:
SELECT home_type, AVG(price) as avg_price FROM Rooms
GROUP BY home_type
Описание агрегатных функций
| Функция | Описание |
|---|---|
| SUM(поле_таблицы) | Возвращает сумму значений |
| AVG(поле_таблицы) | Возвращает среднее значение |
| COUNT(поле_таблицы) | Возвращает количество записей |
| MIN(поле_таблицы) | Возвращает минимальное значение |
| MAX(поле_таблицы) | Возвращает максимальное значение |
:::warning Агрегатные функции применяются для значений, не равных NULL. Исключением является функция COUNT(*).
:::
Оператор HAVING
Общая структура запроса с оператором HAVING
SELECT [константы, агрегатные_функции, поля_группировки]
FROM имя_таблицы
WHERE условия_на_ограничения_строк
GROUP BY поля_группировки
HAVING условие_на_ограничение_строк_после_группировки
ORDER BY условие_сортировки