Группировки и выборки

WHERE — оператор для определения условий выборки

SELECT * FROM table WHERE condition

IN — Оператор IN является сокращением для нескольких OR условий.

SELECT * FROM CustomersWHERE Country IN ('Germany', 'France', 'UK');

BETWEEN — выбирает значения в заданном диапазоне. Является инклюзивным: начальное и конечное значения включены. 

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

LIKE — поиск указанного шаблона в столбце

• Знак процента % представляет ноль, один или несколько символов.
• Знак подчеркивания _ представляет собой один, отдельный символ.

SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

ESCAPE-символ — используется для экранирования специальных символов (% и \). В случае если вам нужно найти строки, вы можете использовать ESCAPE-символ.

SELECT job_id FROM Jobs WHERE progress LIKE '3!%' ESCAPE '!';

CONCAT — позволяет соединять строки.

SELECT CONCAT('SQL', 'Server');
--Результат: 'SQLServer'

SUBSTRING — позволяют извлекать подстроку из строки

SUBSTRING( string, start_position, length );
SELECT SUBSTRING('MS SQL Server', 1, 4);
--Результат: 'MS S'

REPLACE — заменяет последовательность символов в строке другим набором символов, и не чувствительна к регистру

REPLACE( string1, string_to_replace, replacement_string )
SELECT REPLACE('Ms SQL Server', 'S', 'z');
--Результат: 'Mz zQL zerver' (И большая S и мальнькая s будут заменены на z)

INSTR — Осуществляет поиск подстроки в строке, возвращая позицию её первого символа. При этом отсчёт начинается с единицы, а не нуля, как в большинстве языков программирования.

SELECT INSTR('sql-academy', 'academy') AS idx;

IF() — возвращает значение, если условие имеет значение ИСТИНА, или другое значение, если условие имеет значение ЛОЖЬ.

SELECT IF(500<1000, 5, 10);

CASE — проходит через условия и возвращает значение, когда выполняется первое условие

SELECT OrderID, Quantity,
  CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
  END 
  AS QuantityText
  FROM OrderDetails;

COUNT — возвращает количество возращенных запросом строк.

SELECT COUNT(атрибут или *) FROM таблица;

NULL значения в ячейках не будут учтены при подсчете.

Можно считать только уникальные значения

SELECT COUNT(DISTINCT department) AS "Unique departments" FROM employees WHERE salary > 48000;

GROUP BY — можно использовать в SELECT для сбора данных по нескольким записям и группировки результатов одного или нескольких столбцов

SELECT expression1, expression2, ... expression_n,
aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

/* Пример – использования функции SUM */
SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department;

/* Пример – использование функции COUNT */
SELECT department, COUNT(*) AS "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

/* Пример – использование функции MIN */
SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department;

HAVING — используется в сочетании с GROUP BY, чтобы ограничить группы возвращаемых строк, только тех, чьи условия истинны

SELECT expression1, expression2, ... expression_n,
aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;

Пример — Использование функции SUM

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;

Пример — Использование функции COUNT

SELECT department, COUNT(*) AS "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;

Пример — Использование функции MIN

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) > 35000;

DISTINCT — используется для удаления дубликатов из результирующего набора

SELECT DISTINCT expressions
FROM tables
[WHERE conditions];

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

Пример — поиск уникальных значений в столбце

SELECT DISTINCT state FROM suppliers ORDER BY state;

ALIAS — временные имена для столбцов или таблиц

SELECT department, MAX(salary) AS highest
FROM employees
GROUP BY department;

• MySQL ALIASES можно использовать для создания временного имени для столбцов или таблиц.
• COLUMN ALIASES используются для упрощения чтения столбцов в вашем результирующем наборе.
• TABLE ALIASES используются для сокращения вашего SQL-кода, чтобы упростить его чтение или когда вы выполняете самосоединение (то есть: перечисление одной и той же таблицы более одного раза в операторе FROM).

Subqueries (подзапросы) — это запрос в запросе

SELECT * 
FROM all_tables tabs
WHERE tabs.table_name IN (SELECT cols.table_name
                          FROM all_tab_columns cols
                          WHERE cols.column_name = 'SUPPLIER_ID');

Подзапрос и оператор ALL

С помощью оператора ALL мы можем сравнивать отдельное значение с каждым значением в наборе, полученным подзапросом. При этом данное условие вернёт TRUE, только если все сравнения отдельного значения со значениями в наборе вернут TRUE.

Например, нижеприведённый синтетический запрос проверяет для всех ли жилых помещений выполняется условие, что оно дешевле чем 200.

SELECT 200 > ALL(SELECT price FROM Rooms)

Подзапрос и оператор ANY

Условное выражение с ANY имеет схожее поведение, но оно возвращает TRUE, если хотя бы одно сравнение отдельного значения со значением в наборе вернёт TRUE.

SELECT * FROM Users WHERE id = ANY (
    SELECT DISTINCT owner_id FROM Rooms WHERE price >= 150
)

Сравнение по нескольким столбцам

SELECT * FROM Reservations   //сравнивается целиком
    WHERE (room_id, price) IN (SELECT id, price FROM Rooms);

Коррелированные подзапросы

Все предыдущие рассматриваемые подзапросы были некоррелированные (независимые). Они могли выполняться автономно от основного запроса и мы могли посмотреть, что они возвращают перед тем, как их результат будет использоваться в основном запросе. Коррелированные же подзапросы ссылаются на один или несколько столбцов основного запроса

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

SELECT FamilyMembers.member_name,
(SELECT MAX(unit_price) FROM Payments WHERE Payments.family_member = FamilyMembers.member_id) 
AS good_price
FROM FamilyMembers