# SQL от Тигоря
## Select
[Тут картиночки](https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm)
Простейший селект (все остальное не обязательное):
```sql
SELECT * FROM TABLE_NAME
```
селект средней сложности:
```sql=
WITH
QUERY_1 AS (
SELECT ...
), ...
QUERY_R AS (
SELECT ...
)
SELECT DISTINCT
COL_NAME_1, ... , COL_NAME_N
FROM TABLE_NAME_1, ..., TABLE_NAME_M
WHERE CONDITION
GROUP BY COL_NAME_I, ..., COL_NAME_K
HAVING CONDITION
ORDER BY COL_NAME_U, ..., COL_NAME_V DESC
LIMIT L
OFFSET O
;
```
Как читается:
FROM **откуда** берем данные -> WHERE **как** отбираем -> WITH **что используем** для отбора данных -> SELECT **что выбираем** -> GROUP BY **как группируем** для отбора -> HAVING **какие значения после применения агрегатов берем** -> ORDER BY **как сортируем** -> LIMIT сколько берем -> OFFSET начиная с какой строки
А теперь по порядку:
- **WITH** - это что-то типа view или переменной. `WITH ИМЯ AS (Запрос)`. Результаты запроса не выполняются сразу, он будет вызван только в тот момент, когда мы обращаемся к этому имени, поэтому он может быть оптимизирован транслятором SQL (много сложных слов - читай как: результат запроса на самом деле не формируется и нигде не хранится, поэтому эта штука быстрая). Если нужно несколько WITH, пишем так:
```sql=
WITH QUERY_1 AS (
SELECT ...
), ...
QUERY_R AS (
SELECT ...
)
...
SELECT
```
- **DISTINCT** - позволяет выбрать уникальные значения (считаются по всем столбцам). Например:
```sql
SELECT * FROM TABLE_NAME
```
| Column 1 | Column 2 | Column 3 |
| -------- | -------- | -------- |
| 1 | A | B |
| 1 | A | B |
| 2 | A | B |
| 3 | C | D |
```sql
SELECT DISTINCT * FROM TABLE_NAME
```
| Column 1 | Column 2 | Column 3 |
| -------- | -------- | -------- |
| 1 | A | B |
| 2 | A | B |
| 3 | C | D |
- **COL_NAME_1, ... , COL_NAME_N** - то, что мы хотим получить в качестве столбцов в итоговой таблице. Это может быть:
- Имя столбца одной из таблиц, которые указаны в FROM, также можно указать с псевдонимом, который будет использоваться далее. Например:
```sql
SELECT some_shit AS "Pretty thing" FROM TABLE_NAME
```
- Агрегатная функция
```sql
SELECT COUNT(*) FROM STUDENTS
-- посчитаем количество всех студентов
```
- Агрегатная функция от столбца (это разные вещи)
```sql
SELECT MAX(AGE) FROM STUDENTS
-- посчитаем возраст самого старшего из всех студентов
```
- оконная функция (про это будет позже)
- Обычная функция от столбца или выражение, например:
```sql
SELECT TO_DATE(DATE_FIELD), CASH*1000 FROM TABLE_NAME
```
- Строка (ВАЖНО: одинарные кавычки) или число - получится столбец в котором ничего не будет кроме этой строки
```sql
SELECT NAME, 'its a string', 1 FROM TABLE_NAME
```
| Column 1 | Column 2 | Column 3 |
| -------- | -------- | -------- |
| Tigor | its a string | 1 |
| Not tigor | its a string | 1 |
- CASE WHEN THEN ELSE END - это набор условий. Если что-то, то одно, иначе другое. Например:
```sql
SELECT
CASE WHEN DAY < 6
THEN 'Workday',
ELSE 'Weekend'
END
FROM DAYS_TABLE
```
```sql
SELECT
CASE
WHEN DAY = 1 THEN 'Monday',
...
WHEN DAY = 7 THEN 'Sunday'
END
FROM DAYS_TABLE
```
- **FROM TABLE_NAME_1, ..., TABLE_NAME_M** - откуда хотим брать данные. Это может быть:
- Имя таблицы, также можно указать с псевдонимом:
```sql
SELECT * FROM LONG_TABLE_NAME TAB
```
- JOIN (будет потом)
- Имя сохраненного запроса из WITH
- На самом деле почти все что угодно тк в оракле все есть таблица
- **WHERE CONDITION** - условия для отбора строк из таблиц которые указаны во FROM. Внутри может быть какое-нибудь дикое условие, в котором могут тоже быть селекты и прочее. Есть специальные операторы, которые можно использовать в WHERE:
- AND: дальше таблица, как рассчитать EXP_1 AND EXP_2
| AND | TRUE | FALSE |NULL |
| -------- | -------- | -------- |-------- |
| **TRUE** | TRUE | FALSE |NULL |
| **FALSE** | FALSE | FALSE |FALSE |
|**NULL** | NULL | FALSE |NULL |
- OR: дальше таблица, как рассчитать EXP_1 OR EXP_2
| OR | TRUE | FALSE |NULL |
| -------- | -------- | -------- |-------- |
| **TRUE** | TRUE | TRUE |NULL |
| **FALSE** | TRUE | FALSE |NULL |
|**NULL** | TRUE | NULL |NULL |
- IN: принадлежность множеству
```sql
SELECT * FROM TABLE_NAME
WHERE SMTH IN ('A', 'B', 'C')
-- это TRUE когда SMTH = 'A' OR SMTH = 'B' OR SMTH = 'C'
```
```sql
SELECT * FROM TABLE_NAME
WHERE SMTH NOT IN ('A', 'B', 'C')
-- это TRUE когда SMTH != 'A' AND SMTH != 'B' AND SMTH != 'C'
```
```sql
SELECT * FROM TABLE_NAME
WHERE SMTH IN (SELECT ...)
-- это TRUE когда SMTH есть в таблице,
-- которая возвращается из второго селекта
-- Таблица должна возвращать один столбец
```
- BETWEEN A AND B - проверяет принадлежность диапазону
```sql
SELECT NAME, TRUE AS IS_LEGAL FROM LITTLE_GIRLS
WHERE AGE BETWEEN 18 and 25
-- это TRUE когда AGE >= 18 AND AGE <= 25
```
- IS (NOT) NULL проверка NULL/не NULL
```sql
SELECT NAME AS WORKER FROM STUDENT
WHERE WORK_PLACE IS NOT NULL
```
- LIKE - помогает определить похожесть. Редко кто использует, дичь та еще
```sql
SELECT NAME FROM NAMES
WHERE NAME LIKE 'Alex%'
```
| Column 1 |
| -------- |
| Alex |
| Alexey |
| Alexandr |
| Alexandra |
- **GROUP BY COL_NAME_I, ..., COL_NAME_K** - позволяет сгруппировать строки из запроса. Зачем? Чтобы что-то посчитать (а точнее агрегатную функцию) для каждой группы. Без агрегата не работает. Подробнее позже.
```sql
SELECT CLASS, COUNT(*) FROM STUDENTS
GROUP BY CLASS
```
| Column 1 | Column 2 |
| -------- | -------- |
| М80-201М-19 | 10 |
| М80-202М-19 | 3 |
| М80-203М-19 | 54 |
| М80-204М-19 | 5 |
- **HAVING** - позволяет отобрать строки после применения агрегата. Зачем? Ну например, чтобы отсечь группы где студентов меньше 10
```sql
SELECT CLASS, COUNT(*) FROM STUDENTS
GROUP BY CLASS
HAVING COUNT(*) < 10
```
| Column 1 | Column 2 |
| -------- | -------- |
| М80-201М-19 | 10 |
| М80-203М-19 | 54 |
- **ORDER BY COL_NAME_U, ..., COL_NAME_V DESC** - сортировка. По умолчанию по возрастанию (ASC), если добавить в конец DESC, будет по убыванию. А еще можно писать номер столбцов вместо имени столбца. А можно для каждого указывать по возрастанию или нет - в конце пример
```sql
SELECT NAME FROM TABLE_NAME
ORDER BY NAME
```
| Column 1 |
| -------- |
| Igor |
| Tigor |
| Zigor |
```sql
SELECT NAME FROM TABLE_NAME
ORDER BY NAME DESC
```
| Column 1 |
| -------- |
| Zigor |
| Tigor |
| Igor |
```sql
SELECT NAME, AGE, MONEY FROM TABLE_NAME
ORDER BY 2 ASC, 3 DECS
```
| Column 1 | Column 2 | Column 3 |
| -------- | -------- | -------- |
| Tigor | 0 | 10 |
| Another tigor | 0 | 5 |
| Not tigor | 10 | 1 |
- **LIMIT L .. OFFSET O** - позволяет указать лимит выдачи и начиная с какого-то места. Работает в самом конце после сортировки. Например для примера выше:
```sql
SELECT NAME, AGE, MONEY FROM TABLE_NAME
ORDER BY 2 ASC, 3 DECS
LIMIT 1 -- выбираем одну строку
```
| Column 1 | Column 2 | Column 3 |
| -------- | -------- | -------- |
| Tigor | 0 | 10 |
```sql
SELECT NAME, AGE, MONEY FROM TABLE_NAME
ORDER BY 2 ASC, 3 DECS
OFFSET 1 -- пропускаем первую строку
```
| Column 1 | Column 2 | Column 3 |
| -------- | -------- | -------- |
| Another tigor | 0 | 5 |
| Not tigor | 10 | 1 |
```sql
SELECT NAME, AGE, MONEY FROM TABLE_NAME
ORDER BY 2 ASC, 3 DECS
LIMIT 1 OFFSET 1 -- выбираем одну строку, пропустив первую
```
| Column 1 | Column 2 | Column 3 |
| -------- | -------- | -------- |
| Another tigor | 0 | 5 |
## Запросы с группировкой
Часто требуется посчитать что-то используя группировку. Для этого можно использовать агрегатные функции + group by + having
```sql=
SELECT COL_NAME_1, ... , AGGREGATE_FUN_1(COL_NAME_N),...
FROM TABLE_NAME_1, ..., TABLE_NAME_M
WHERE CONDITION
GROUP BY COL_NAME_I, ..., COL_NAME_K
HAVING CONDITION
```
Как это работает: выбираем строки из таблиц из `FROM` -> отбираем их по условию из `WHERE` -> группируем строки по полям, которые указаны в `GROUP BY` (важно: не получится использовать группировку по разным полям в одном запросе - дальше будет пример) -> считаем для них агрегатные функции в `SELECT` -> отбираем нужные значения с помощью `HAVING`
Ограничения:
пусть есть таблица вида:
| Department | Class | Student | Year |
| -------- | -------- | -------- |-------- |
|8 факультет | М80-204М-19 | Золотарев Игорь | 2019
|..|..|..|..|
- нельзя сделать группировку по разным полям в одном запросе (например хотим посчитать одновременно количество студентов на каждом факультете и узнать год, когда был принят первый студент в группе - пример херовый, надеюсь, поймешь)
```sql
-- это не правильно, мы получим количество студентов
-- в каждой группе факультета и год принятия первого
-- студента в каждой группе на факультете
SELECT DEPARTMENT, CLASS, COUNT(STUDENT), MIN(YEAR)
FROM STUDENTS
GROUP BY DEPARTMENT, CLASS
```
как надо:
```sql
SELECT DEPARTMENT, CLASS, COUNT, MIN
FROM
(SELECT DEPARTMENT, CLASS, COUNT(STUDENT) AS COUNT,
FROM STUDENTS
GROUP BY DEPARTMENT) T1
INNER JOIN
(SELECT DEPARTMENT, CLASS, MIN(YEAR)
FROM STUDENTS
GROUP BY CLASS) T2
ON T1.DEPARTMENT = T2.DEPARTMENT AND T1.CLASS = T2.CLASS
```
- в `SELECT` обязательно должны быть все поля, которые указаны в `GROUP BY` (иначе не понятно, какое значение агрегатной функции принадлежит какой группе)
```sql
-- это не будет работать
SELECT COUNT(STUDENT), MIN(YEAR)
FROM STUDENTS
GROUP BY DEPARTMENT, CLASS
```
- в рамках одного запроса можно группировать только по полям, которые есть в таблицах (то есть рассчетные поля в `SELECT` не могут участвовать в группировке, например, по `CASE WHEN` не получится группировать, нужно писать второй запрос)
```sql
SELECT COUNT(STUDENT),
CASE YEAR > 2020
THEN 'CURRENT_YEAR'
ELSE 'PAST_TIMES'
END AS WHICH_YEAR
FROM STUDENTS
GROUP BY DEPARTMENT, CLASS, WHICH_YEAR -- это не будет работать
```
- в HAVING нельзя указать псевдонимы столбцов
```sql
SELECT DEPARTMENT, CLASS, COUNT(STUDENT) AS CNT
FROM STUDENTS
GROUP BY DEPARTMENT, CLASS
HAVING CNT > 10 -- это не будет работать
```
так правильно:
```sql
SELECT DEPARTMENT, CLASS, COUNT(STUDENT) AS CNT
FROM STUDENTS
GROUP BY DEPARTMENT, CLASS
HAVING COUNT(STUDENT) > 10
```
Какие есть агрегатные функции:
- `MIN(COL)`
- `MAX(COL)`
- `AVG(COL)` - среднее
- `SUM(COL)`
- `MEDIAN(COL)`
- `COUNT(COL)` - вернет количество уникальных значений для данного столбца в указанной группе
- `COUNT(*)` - вернет количество записей в группе (в т.ч. не уникальные)
- `VARIANCE(COL)` - дисперсия
и [другие](https://docs.oracle.com/database/121/SQLRF/functions003.htm#SQLRF20035)
**Все агрегатные функции, кроме `COUNT` работают со значениями, которые приводятся к числовым - числа, логические значения и даты (т.к. дата представляется в оракле как набор чисел)**
Sign in with Wallet
Connect another wallet