По сути, это оператор, который возвращает данные. Например, это могут быть данные из таблицы, просто выражение:
// выражение
SELECT 9+5;
SELECT 6-4 AS result; //переименует столбец в result
//данные из таблицы
SELECT * FROM fruits;
SELECT price FROM fruits;
SELECT name AS fruit FROM fruits;
SELECT всегда возвращает таблицу. Даже если оператор возвращает одно число, это все равно таблица из одного ряда и одной ячейки. И все манипуляции данными происходят на уровне таблиц.
У оператора SELECT много предикатов (фильтры, уточняющие фразы). Т.е. мы уточняем, что конкретно хотим получить.
Рассмотрим основные предикаты:
- ORDER BY — по какому полю сортировать выборку.
SELECT * FROM fruits ORDER BY price; //сортировка по цене
SELECT * FROM fruits ORDER BY 1; //сортировка по первой колонке
- ASC — сортировать в порядке возрастания.
- DESC — сортировать в порядке убывания.
SELECT * FROM fruits ORDER BY price DESC;
SELECT * FROM fruits ORDER BY price ASC;
- LIMIT — ограничение числа записей.
SELECT * FROM fruits ORDER BY price LIMIT 4;
SELECT * FROM fruits ORDER BY price LIMIT 3,2; //с 3-й записи по счету две штуки
- WHERE — фильтр записи, искать что-то, где сработает фильтр. Выдаст только те записи, длякоторых выражение where истина.
SELECT price FROM fruits WHERE id=2;
SELECT * FROM fruits WHERE price>50;
SELECT * FROM fruits WHERE price BETWEEN 10 AND 100;
- DISTINCT — выбрать неповторяющиеся записи.
- ALL — выбрать все записи (стоит по умолчанию, писать не надо).
- BEETWEEN (значение между), LIKE (оператор похожести строки, он сравнивает строки), IN (указываем множество значений), IS NULL (не записано, отсутствие значения, применяют для проверки элементов).
SELECT * FROM table WHERE phone IS NULL; //проверка на незаданный номер телефона
SELECT * FROM table WHERE phone LIKE '+7%';//выбрать телефоны, начинающиеся с +7
SELECT * FROM table WHERE price IN (100,200,300);
- REGEXP — регулярные выражения (применяются для поиска по базе данных перебором всех записей).
SELECT name FROM fruits WHERE name REGEXP '[a-z]{7}';
- YEAR, MONTH — фильтр на года и месяца.
- INDEX — создание индекса. И уже по нему происходит поиск с помощью SELECT. При этом перебор записей не делается, т.е. очень быстро (быстрее, чем LIKE и REGEXP). Внимание, по умолчанию поиск трехбуквенных слов не ведется.
CREATE FULLTEXT INDEX name ON table (title,description);
SELECT * FROM table WHERE MATCH (title,description)//указываем совпадение
AGAINST ('eburg' IN NATURAL LANGUAGE MODE);//ищем совпадение по 'eburg' и в каком режиме
- REPAIR — переиндексация.
Функции агрегации (агрегаторы)
Они работают с наборами данных. Принцип работы: агрегатор проходит по множеству записей в исходной таблице, формируя из этого множества одну запись (одно значение). Т.е. агругатор собирает множество записей в одну.
Основные функции агрегации:
- AVG () — среднее значение.
- COUNT () — число записей.
- GROUP_CONCAT () — склеивание строк (агрегатор проходит через все строки и склеивает их в одну строку — конкатенирует запись).
- MIN () — минимальное значение из набора.
- MAX () — максимальное значение из набора.
- STD () — стандартное отклонение.
- SUM () — сумма всех записей.
- VARIANCE () — разброс, отклонение от среднего.
- HAVING — фильтрация данных после группировки.
SELECT COUNT(*) FROM fruits;
SELECT MAX(price) FROM fruits;
SELECT MIN(price) FROM fruits;
В каком порядке писать предикаты в запросе? Пишем их в порядке выполнения сервером:
- WHERE.
- GROUP.
- HAVING.
- ORDER.
- LIMIT.
Группировка данных
Допустим, у нас есть большая таблица, в которой часто данные какой-то колонки повторяются.
SELECT teacher, courser, SUM(length) FROM lessons
GROUP BY teacher, course;
Необязательно делать группировку по колонке в таблице. Можно это делать по любому выражению:
SELECT YEAR(lesson_date), MONTH(lesson_date), SUM(lengt)
FROM lessons
GROUP BY YEAR(lesson_date), MONTH(lesson_date); //группируем по году и месяцу
При группировке используют также предикат HAVING. Он похож на фильтр WHERE, но делает фильтрацию данных после группировки, а WHERE — перед группировкой. Т.е. по сути HAVING — это второй отбор по результатам группировки. Пример:
SELECT YEAR(lesson_date), MONTH(lesson_date), SUM(length) //выборка
FROM lessons
WHERE teacher IN(1,2,3) //первый фильтр
GROUP BY YEAR(lesson_date),MONTH(lesson_date) //группировка
HAVING SUM(length)>20; //второй фильтр
Объединение таблиц
Оператор SELECT может брать данные из нескольких таблиц одновременно.
Допустим, у нас есть несколько таблиц. Надо сделать выборку из нескольких, но встает проблема их сопоставления. И тогда применяют объединение нескольких таблиц в одну.
Известно несколько видов объединений таблиц:
- INNER JOIN — внутреннее (склеиваются все колонки всех таблиц). Копируется только то, что имеет соответствие в правой и в левой ьаблице, т.е. не все записи.
- LEFT [OUTER] JOIN — левое внешнее. Из левой таблицы в правую копируются все записи. И по возможности им ставится в соответствие правая таблица. Если соответствие не найдено — ставится NULL (данных нет).
- RIGHT [OUTER] JOIN – правое внешнее. То же самое, что в предыдущем, только копирование из правой таблицы в левую.
- FULL OUTER JOIN – полное внешнее. Копируются все записи из всех таблиц и по возможности ставятся в соответствие друг к другу.
- CROSS JOIN – кросс-объединение. Каждой записи левой таблицы ставятся в соответствие все записи правой таблицы. Здесь нет условий связи. Получается дублирование элементов.
Пример:
//INNER JOIN
SELECT teachers.name
FROM teachers
INNER JOIN lessons ON teachers.id = lessons.teacher //склеивание