Вспоминаем SQL

Тут все просто, первое что надо помнить что если у вас есть таблица students например:

если вы хотите просто вывести все что есть то используете

SELECT *
FROM students

Если вы хотите вывести только конкретные столбцы то надо вместо звездочки прописать что имено вам нужно, например если я хочу только имя отчество то напишу так:

SELECT first_name, patronymic
FROM students

если надо отфильтровать по какому-нибудь признаку, например вывести всех девушек, то надо добавить условие WHERE, в котором указать признак по которому фильтровать

SELECT first_name, patronymic
FROM students
WHERE gender = 1

если надо результат отфильтровать то добавляем опертор ORDER BY в котором указываем по какому столбцу фильтровать. Например так

SELECT first_name, patronymic
FROM students
WHERE gender = 1
ORDER BY first_name

кстати не обязательно писать название столбца можно просто его указать, наприимер у меня в этом запросе отчество – второй столбец. Если я хочу упорядочить по отчеству то могу написать так:

SELECT first_name, patronymic
FROM students
WHERE gender = 1
ORDER BY 2

Для того чтобы склеить две таблицы по внешнему ключу надо использовать оператор join. Например, мы можем вот так подклеить таблицу групп к таблице студентов

SELECT *
FROM students s
JOIN groups g ON s.group_id = g.id

Тут обратите внимание что мы используем альясы для названия таблиц, то есть после students я пишу букву s и теперь могу обращаться к полям таблицы студентов через s, например, s.group_id

То же самое делаю для таблицы групп.

В качестве бонуса эта нам так же дает возможность выводить значение полей с одинаковыми именами. Например, поле id есть и в таблице студентов, и в таблице групп

И если я напишу

SELECT id
FROM students s
JOIN groups g ON s.group_id = g.id

то получу ошибку

но если я воспользуюсь альясом, то смогу явно указать из какой таблице я хочу взять поле

SELECT s.id
FROM students s
JOIN groups g ON s.group_id = g.id

В sql есть возможность преобразовывать значения полей на лету. Например, проверять значения поля на некоторое соответствие и в зависимости от результат выводить другое значение. Для этого используется оператор CASE, работает он так

то есть я вместо названия столбца, открываю конструкцию CASE, внутри прописываю ситуацию которую проверяю через WHEN, при необходимости указываю ELSE и обязательно завершаю все это дело END, ну и для полного счастья придумываю название столбца через as id_info

Один из основополагающих механизмов в SQL – это группировка или как её еще называют агрегация.

То есть когда вы объединяете записи по какому-то признаку. И вычисляется для группы какой-то общий параметр

Для того чтобы сработала группировка достаточно добавить агрегирующую функцию в SELECT

Базовые агрегирующие функции это:

• count – подсчитывает количество элементов в группе
• sum – суммирует значение поля в группе
• min – находит минимальные значение поля в группе
• max – находит максимальное значение поля в группе
• avg – находит среднее значение поля в группе

Если, например, написать

SELECT count(*)
FROM students

то просто получим количество строк в таблице

если же в качестве параметра передать конкретное поле, то получим количество строк с ненулевым значением в этом поле

SELECT count(patronymic)
FROM students

похоже у какого-то студента не заполнено поле отчества, попробуйте найти его

Можно, например, подсчитать количество уникальных значений в столбце если добавить слово distinct. Например

SELECT count(distinct first_name)
FROM students

то есть получается у нас в среднем каждое имя встречается 2 раза

Вообще на самом деле редко когда требуется агрегация по всей таблице, как правило бьют таблицу на подгруппы ко какому-то полю. Например я могу указать в качестве поля – пол.

SELECT count(distinct first_name)
FROM students
GROUP BY gender

как только вы добавили поле в GROUP BY вы можете смело его добавить в SELECT, вот так:

SELECT gender, count(distinct first_name)
FROM students
GROUP BY gender

можно даже сделать вычислимое поле

SELECT 
	CASE WHEN gender = 1 THEN 'Ж' ELSE 'М' END as [пол]
	, count(distinct first_name)
FROM students
GROUP BY gender

можно добавить еще одну агрегирующую функцию, например, min

SELECT 
	CASE WHEN gender = 1 THEN 'Ж' ELSE 'М' END as [пол]
	, count(distinct first_name)
	, min(first_name)
FROM students
GROUP BY gender

получим такое:

можно конечно задать вопрос, почему Александра – это самое маленькое имя.

Ну на самом деле тут разгадка простая – это просто самое первое имя если упорядочить слова по алфавиту, поэтому оно и выдается, когда вы используете функцию min для строкового столбца.

В общем идея такая. Если вы что-то сгруппировали – то вы в запросе можете также применять сортировку. Например:

SELECT 
	CASE WHEN gender = 1 THEN 'Ж' ELSE 'М' END as [пол]
	, count(distinct first_name)
	, min(first_name)
FROM students
GROUP BY gender
ORDER BY count(distinct first_name) DESC

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

А еще в SQL есть возможность указать сколько строк вы хотитет оставить для этого в SELECT надо запихать TOP n, где вместо n подставить циферку, как-то так

SELECT TOP 100 -- пожалуйста 100 первых записей мне, ну и что что у меня их только 2, а вот найдите =О
	CASE WHEN gender = 1 THEN 'Ж' ELSE 'М' END as [пол]
	, count(distinct first_name)
	, min(first_name)
FROM students
GROUP BY gender
ORDER BY 2 DESC

Еще есть в SQL такая мега возможность как использовать результат одного запроса в другом запросе.

Допустим решил я найти самое часто встречающееся имя

SELECT TOP 1 first_name, count(*)
FROM students
GROUP BY first_name
ORDER BY count(*) DESC

и теперь хочу чтобы мне вывелся список всех групп в которых учатся Дмитрии.

Как мне это сделать?

Если бы у меня просто стояла задача вывести все группы где учатся Дмитрии, я бы написал:

SELECT g.title
FROM students s
JOIN groups g ON s.group_id = g.id
WHERE first_name = 'Дмитрий'

ну типа найди всех студентов с именем Дмитрий и выведи названия их групп. Получился бы такой список

чтобы убрать повторы можно еще DISTINCT добавить

SELECT DISTINCT g.title
FROM students s
JOIN groups g ON s.group_id = g.id
WHERE first_name = 'Дмитрий'

а теперь посмотрим как можно использовать результат запроса чтобы получить такой же результат. Для этого вместо ‘Дмитрий’ надо подставить прямо запрос которые выдавал нам самое часто встречающееся имя, ну только в скобочки его обернуть:

SELECT DISTINCT g.title
FROM students s
JOIN groups g ON s.group_id = g.id
WHERE first_name = (
	SELECT TOP 1 first_name, count(*)
	FROM students
	GROUP BY first_name
	ORDER BY count(*) DESC
)

правда в таком случае у нас случиться ошибка:

тут дело в том что наш запрос которые ищет самое популярное имя выдает два стобца. Надо просто его подправить так чтобы он выдавал только один – только first_name.

То есть получим такой запрос:

SELECT DISTINCT g.title
FROM students s
JOIN groups g ON s.group_id = g.id
WHERE first_name = (
	SELECT TOP 1 first_name
	FROM students
	GROUP BY first_name
	ORDER BY count(*) DESC
)

о получили то самое:

кстати на самом деле часто надо вывести значение которое является элементом результат подзапроса, в этом случае надо first_name = заменить на first_name in

Например вывести группы в которых учаться студенты с тремя самыми популярными именами:

SELECT DISTINCT g.title
FROM students s
JOIN groups g ON s.group_id = g.id
WHERE first_name in (
	SELECT TOP 3 first_name
	FROM students
	GROUP BY first_name
	ORDER BY count(*) DESC
)

Вообще эта задачка на функцию sum.

SUM – очень прикольная функция, она суммирует то что передается ей в качестве параметра.

Например

SELECT sum(1)
FROM students

просто на каждую строку в таблице студентов будет добавлять единицу. И по сути выдаст тоже что и count(*)

А если запихать туда sum(2)

SELECT sum(2)
FROM students

то оно уже на каждую строку будет суммировать 2

а можно, например, запихать туда какую-нибудь логику. Например, засунуть туда CASE и если id четный добавить 1, а если нечетный, то добавлять -1.

SELECT sum(CASE 
	WHEN id % 2 = 0 THEN 1
	WHEN id % 2 = 1 THEN -1
END) as [бессмыслица]
FROM students

В этом нет никакого смысла, но если развить эту идею можно решить задачку

Вычислимые выражения могут быть очень сложными. Например, никто не мешает вам делить одну агрегацию на другую:

SELECT 1.0 * sum(gender) / count(distinct first_name)
FROM students
GROUP BY group_id

Я умножаю на 1.0 слева, чтобы получить не целое число в качестве результата. Я не знаю, что значат эти цифры, но истина где-то рядом