SQL за 20 минут
Каждый уважающий себя веб-разработчик, да и не только, должет знать SQL. Хоть он и существует аж с 70-х годов прошлого века, он до сих пор очень широко используется, и без него будет сложно создать нечто серьёзное. Большинство full-stack фреймворков умеют работать с SQL. В их числе: ActiveRecord, Doctrine, Hibernate и многие другие. Несмотря на это, иногда приходится «замарать руки» и пуститься в настоящий SQL.
Именно поэтому мы подготовили короткое вступление, в котором мы пройдёмся по фундаментальным вещам в SQL. Мы настоятельно рекомендуем вам попробовать все приведённые ниже примеры самостоятельно, ведь, как известно, теория — ничто без практики.
Что ж, приступим!
Создаём таблицу
Для того, чтобы создать таблицу в SQL, используется выражение CREATE TABLE. Он принимает в качестве параметров все колонки, которые мы хотим внести, а также их типы данных.
Давайте создадим табличку с названием «Months», в которой будет три колонки:
CREATE TABLE months (id int, name varchar(10), days int);
Также, когда создаются таблицы, принято добавлять так называемый primary key. Это колонка, значения в которой уникальны. Чаще всего primary key колонкой является id, но в нашем случае это может быть и name, так как имена всех месяцев уникальны. Для более подробной информации предлагаем перейти по этой ссылке.
Ввод данных
Теперь давайте добавим пару месяцев в нашу табличку. Сделать это можно с помощью команды INSERT. Есть два разных способа использовать INSERT:
Первый способ не подразумевает указания названий колонок, а лишь принимает значения в том порядке, в котором они указаны в таблице.
INSERT INTO months VALUES (1, 'January', 31);
Первый способ короче второго, однако если в будущем мы захотим добавить дополнительные колонки, все предыдущие запросы работать не будут. Для решения данной проблемы следует использовать второй способ. Его суть в том, что перед вводом данных мы указываем названия колонок.
INSERT INTO months (id, name, days) VALUES (2, 'Febreary', 29);
В случае, если мы не укажем одну из колонок, на её место будет записано NULL или заданное значение по умолчанию, но это уже совсем другая история.
Select
Данный запрос используется в случае, если нам нужно показать данные в таблице. Наверное, самым простым примером использования SELECT будет следующий запрос:
SELECT * FROM characters
Результатом данного запроса будет таблица со всеми данными в таблице characters. Знак звёздочки (*) означает то, что мы хотим показать все столбцы из таблицы без исключений. Так как в базе данных обычно больше одной таблицы, нам необходимо указывать название таблицы, данные из которой мы хотим посмотреть. Сделать это мы можем, используя ключевое слово FROM.
Когда вам нужны лишь некоторые столбцы из таблицы, то вы можете указать их имена через запятую вместо звёздочки.
SELECT name, weapon FROM characters
Также иногда нам нужно отсортировать выводимые данные. Для этого мы используем ORDER BY «название столбца». ORDER BY имеет два модификатора: ASC (по возрастанию) (по умолчанию) и DESC (по убыванию).
SELECT name, weapon FROM "characters" ORDER BY name DESC
Where
Теперь мы знаем, как показать только конкретные столбцы, но что если мы хотим включить в вывод лишь некоторые конкретные строки? Для этого мы используем WHERE. Данное ключевое слово позволяет нам фильтровать данные по определённому условию.
В следующем запросе мы выведем только тех персонажей, которые в качестве оружия используют пистолет.
SELECT *
FROM characters
WHERE weapon = 'pistol';
И/или
Условия в WHERE могут быть написаны с использованием логических операторов (AND/OR) и математические операторы сравнения (=, <, >, <=, >=, <>).
К примеру, у нас есть табличка, в которой записаны данные о 4 самых продаваемых музыкальных альбомах всех времён. Давайте выведем только те, жанром которых является рок, а продажи которых были меньше, чем 50 миллионов копий.
SELECT *
FROM albums
WHERE genre = 'rock' AND sales_in_millions <= 50
ORDER BY released
In/Between/Like
Условия в WHERE могут быть записаны с использованием ещё нескольких команд, которыми являются:
SELECT * FROM albums WHERE genre IN ('pop','soul');
Если мы хотим вывести все альбомы, которые были выпущены в промежутке между 1975 и 1985 годом, мы можем использовать следующую запись:
SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;
Также, если мы хотим вывести все альбомы, в названии которых есть буква ‘R’, мы можем использовать следующую запись:
SELECT * FROM albums WHERE album LIKE '%R%';
Знак % означает любую последовательность символов (0 символов тоже считается за последовательность).
Если мы хотим вывести все альбомы, первая буква в названии которых — ‘R’, то запись слегка изменится:
SELECT * FROM albums WHERE album LIKE 'R%';
В SQL также есть инверсия. Для примера, попробуйте самостоятельно написать NOT перед любым логическим выражением в условии (NOT BETWEEN и так далее).
Функции
В SQL полно встроенных функций для выполнения разных операций. Мы же покажем вам только наиболее часто используемые:
SELECT MAX(released) FROM albums;
Обратите внимание, что если вы напишете запрос, в котором вам, к примеру, нужно будет вывести имя и среднее значение чего-либо, то вы получите ошибку на выводе.
Допустим, вы пишете такой запрос:
SELECT name, avg(age) FROM students;
Чтобы избежать ошибки, вам следует добавить следующую строку:
GROUP BY name
Причиной тому является, что запись avg(age) является совокупной (aggregated), и вам необходимо групировать значения по имени.
Вложенные Select
В предыдущих шагах мы изучили, как делать простые вычисления с данными. Если мы хотим использовать результат данных вычислений, то часто нам необходимо использовать так называемые вложенные запросы. Допустим, нам необходимо вывести артиста, альбом и год выпуска самого старого альбома в таблице.
Вывести эти столбцы можно, используя следующий запрос:
SELECT artist, album, released FROM albums;
Также мы знаем, как получить самый ранний год из имеющихся:
SELECT MIN(released) FROM album;
Объеденить эти запросы можно в WHERE:
SELECT artist,album,released
FROM albums
WHERE released = (
SELECT MIN(released) FROM albums
);
Присоеденение таблиц
В сложных базах данных чаще всего у нас есть несколько связанных таблиц. К примеру, у нас есть две таблицы: про видеоигры и про разработчиков.
В таблице video_games есть столбец developer_id, в данном случае он является так называемым foreign_key. Чтобы было проще понять, developer_id — это связывающее звено между двумя таблицами.
Если мы хотим вывести всю информацию об игре, включая информацию о её разработчике, нам необходимо подключить вторую таблицу. Чтобы это сделать, можно использовать INNER JOIN:
SELECT video_games.name, video_games.genre, game_developers.name, game_developers.country
FROM video_games
INNER JOIN game_developers
ON video_games.developer_id = game_developers.id;
Это, наверное, самый простой пример использования JOIN. Есть ещё несколько вариантов его использования. Для более подробной информации предлагаем перейти по этой ссылке.
Псевдонимы
Если вы взгляните на предыдущий пример, то вы заметите, что есть два столбца, названных одинаково: «name». Часто это может запутать. Решением данной проблемы являются псевдонимы. Они, к слову, помогают сделать название столбца красивее или понятнее в случае необходимости.
Чтобы присвоить столбцу псевдоним, можно использовать ключевое слово AS:
SELECT games.name, games.genre, devs.name AS developer, devs.country FROM video_games AS games
INNER JOIN game_developers AS devs
ON games.developer_id = devs.id;
Update
Зачастую нам нужно изменить данные в таблице. В SQL это делается с помощью UPDATE.
Исполльзование UPDATE включает в себя:
UPDATE tv_series
SET genre = 'drama'
WHERE name = 'Game of Thrones';
Удаление записей из таблицы
Удаление записи из таблицы через SQL — очень простая операция. Всё, что нужно — это обозначить, что именно мы хотим удалить.
DELETE FROM tv_series
WHERE id = 4;
Примечание: убедитесь, что используете WHERE, когда удаляете запись из таблицы. Иначе вы удалите все записи из таблицы, сами того не желая.
Удаление таблиц
Если мы хотим удалить все данные из таблицы, но при этом оставить саму таблицу, нам следует использовать команду TRUNCATE:
TRUNCATE TABLE table_name;
В случае, если мы хотим удалить саму таблицу, то нам следует исользовать команду DROP:
DROP TABLE table_name;
Заключение
На этой ноте мы завершаем данный SQL-туториал. Само собой, это не всё, и для полного освоения нужно ещё много изучить, однако данное вступление даст вам толчок для дальнейшего изучения.
Каждый уважающий себя веб-разработчик, да и не только, должет знать SQL. Хоть он и существует аж с 70-х годов прошлого века, он до сих пор очень широко используется, и без него будет сложно создать нечто серьёзное. Большинство full-stack фреймворков умеют работать с SQL. В их числе: ActiveRecord, Doctrine, Hibernate и многие другие. Несмотря на это, иногда приходится «замарать руки» и пуститься в настоящий SQL.
Именно поэтому мы подготовили короткое вступление, в котором мы пройдёмся по фундаментальным вещам в SQL. Мы настоятельно рекомендуем вам попробовать все приведённые ниже примеры самостоятельно, ведь, как известно, теория — ничто без практики.
Что ж, приступим!
Создаём таблицу
Для того, чтобы создать таблицу в SQL, используется выражение CREATE TABLE. Он принимает в качестве параметров все колонки, которые мы хотим внести, а также их типы данных.
Давайте создадим табличку с названием «Months», в которой будет три колонки:
- id — иными словами, порядковый номер месяца (целочисленный тип или int)
- name — название месяца (строка или varchar(10) (10 символов — максимальная длина строки))
- days — число дней в конкретном месяце (целочисленный тип или int)
CREATE TABLE months (id int, name varchar(10), days int);
Также, когда создаются таблицы, принято добавлять так называемый primary key. Это колонка, значения в которой уникальны. Чаще всего primary key колонкой является id, но в нашем случае это может быть и name, так как имена всех месяцев уникальны. Для более подробной информации предлагаем перейти по этой ссылке.
Ввод данных
Теперь давайте добавим пару месяцев в нашу табличку. Сделать это можно с помощью команды INSERT. Есть два разных способа использовать INSERT:
Первый способ не подразумевает указания названий колонок, а лишь принимает значения в том порядке, в котором они указаны в таблице.
INSERT INTO months VALUES (1, 'January', 31);
Первый способ короче второго, однако если в будущем мы захотим добавить дополнительные колонки, все предыдущие запросы работать не будут. Для решения данной проблемы следует использовать второй способ. Его суть в том, что перед вводом данных мы указываем названия колонок.
INSERT INTO months (id, name, days) VALUES (2, 'Febreary', 29);
В случае, если мы не укажем одну из колонок, на её место будет записано NULL или заданное значение по умолчанию, но это уже совсем другая история.
Select
Данный запрос используется в случае, если нам нужно показать данные в таблице. Наверное, самым простым примером использования SELECT будет следующий запрос:
SELECT * FROM characters
Результатом данного запроса будет таблица со всеми данными в таблице characters. Знак звёздочки (*) означает то, что мы хотим показать все столбцы из таблицы без исключений. Так как в базе данных обычно больше одной таблицы, нам необходимо указывать название таблицы, данные из которой мы хотим посмотреть. Сделать это мы можем, используя ключевое слово FROM.
Когда вам нужны лишь некоторые столбцы из таблицы, то вы можете указать их имена через запятую вместо звёздочки.
SELECT name, weapon FROM characters
Также иногда нам нужно отсортировать выводимые данные. Для этого мы используем ORDER BY «название столбца». ORDER BY имеет два модификатора: ASC (по возрастанию) (по умолчанию) и DESC (по убыванию).
SELECT name, weapon FROM "characters" ORDER BY name DESC
Where
Теперь мы знаем, как показать только конкретные столбцы, но что если мы хотим включить в вывод лишь некоторые конкретные строки? Для этого мы используем WHERE. Данное ключевое слово позволяет нам фильтровать данные по определённому условию.
В следующем запросе мы выведем только тех персонажей, которые в качестве оружия используют пистолет.
SELECT *
FROM characters
WHERE weapon = 'pistol';
И/или
Условия в WHERE могут быть написаны с использованием логических операторов (AND/OR) и математические операторы сравнения (=, <, >, <=, >=, <>).
К примеру, у нас есть табличка, в которой записаны данные о 4 самых продаваемых музыкальных альбомах всех времён. Давайте выведем только те, жанром которых является рок, а продажи которых были меньше, чем 50 миллионов копий.
SELECT *
FROM albums
WHERE genre = 'rock' AND sales_in_millions <= 50
ORDER BY released
In/Between/Like
Условия в WHERE могут быть записаны с использованием ещё нескольких команд, которыми являются:
- IN — сравнивает значение в столбце с несколькими возможными значениями и возвращает true, если значение совпадает хотя бы с одним из значением
- BETWEEN — проверяет, находится ли значение в каком-то промежутке
- LIKE — ищет по шаблону
SELECT * FROM albums WHERE genre IN ('pop','soul');
Если мы хотим вывести все альбомы, которые были выпущены в промежутке между 1975 и 1985 годом, мы можем использовать следующую запись:
SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;
Также, если мы хотим вывести все альбомы, в названии которых есть буква ‘R’, мы можем использовать следующую запись:
SELECT * FROM albums WHERE album LIKE '%R%';
Знак % означает любую последовательность символов (0 символов тоже считается за последовательность).
Если мы хотим вывести все альбомы, первая буква в названии которых — ‘R’, то запись слегка изменится:
SELECT * FROM albums WHERE album LIKE 'R%';
В SQL также есть инверсия. Для примера, попробуйте самостоятельно написать NOT перед любым логическим выражением в условии (NOT BETWEEN и так далее).
Функции
В SQL полно встроенных функций для выполнения разных операций. Мы же покажем вам только наиболее часто используемые:
- COUNT() — возвращает число строк
- SUM() — возвращает сумму всех полей с числовыми значениями в них
- AVG() — возвращает среднее значение среди строк
- MIN()/MAX() — возвращает минимальное/максимальное значение среди строк
SELECT MAX(released) FROM albums;
Обратите внимание, что если вы напишете запрос, в котором вам, к примеру, нужно будет вывести имя и среднее значение чего-либо, то вы получите ошибку на выводе.
Допустим, вы пишете такой запрос:
SELECT name, avg(age) FROM students;
Чтобы избежать ошибки, вам следует добавить следующую строку:
GROUP BY name
Причиной тому является, что запись avg(age) является совокупной (aggregated), и вам необходимо групировать значения по имени.
Вложенные Select
В предыдущих шагах мы изучили, как делать простые вычисления с данными. Если мы хотим использовать результат данных вычислений, то часто нам необходимо использовать так называемые вложенные запросы. Допустим, нам необходимо вывести артиста, альбом и год выпуска самого старого альбома в таблице.
Вывести эти столбцы можно, используя следующий запрос:
SELECT artist, album, released FROM albums;
Также мы знаем, как получить самый ранний год из имеющихся:
SELECT MIN(released) FROM album;
Объеденить эти запросы можно в WHERE:
SELECT artist,album,released
FROM albums
WHERE released = (
SELECT MIN(released) FROM albums
);
Присоеденение таблиц
В сложных базах данных чаще всего у нас есть несколько связанных таблиц. К примеру, у нас есть две таблицы: про видеоигры и про разработчиков.
В таблице video_games есть столбец developer_id, в данном случае он является так называемым foreign_key. Чтобы было проще понять, developer_id — это связывающее звено между двумя таблицами.
Если мы хотим вывести всю информацию об игре, включая информацию о её разработчике, нам необходимо подключить вторую таблицу. Чтобы это сделать, можно использовать INNER JOIN:
SELECT video_games.name, video_games.genre, game_developers.name, game_developers.country
FROM video_games
INNER JOIN game_developers
ON video_games.developer_id = game_developers.id;
Это, наверное, самый простой пример использования JOIN. Есть ещё несколько вариантов его использования. Для более подробной информации предлагаем перейти по этой ссылке.
Псевдонимы
Если вы взгляните на предыдущий пример, то вы заметите, что есть два столбца, названных одинаково: «name». Часто это может запутать. Решением данной проблемы являются псевдонимы. Они, к слову, помогают сделать название столбца красивее или понятнее в случае необходимости.
Чтобы присвоить столбцу псевдоним, можно использовать ключевое слово AS:
SELECT games.name, games.genre, devs.name AS developer, devs.country FROM video_games AS games
INNER JOIN game_developers AS devs
ON games.developer_id = devs.id;
Update
Зачастую нам нужно изменить данные в таблице. В SQL это делается с помощью UPDATE.
Исполльзование UPDATE включает в себя:
- выбор таблицы, в которой находится поле, которое мы хотим изменить
- запись нового значения
- использование WHERE, чтобы обозначить конкретное место в таблице
UPDATE tv_series
SET genre = 'drama'
WHERE name = 'Game of Thrones';
Удаление записей из таблицы
Удаление записи из таблицы через SQL — очень простая операция. Всё, что нужно — это обозначить, что именно мы хотим удалить.
DELETE FROM tv_series
WHERE id = 4;
Примечание: убедитесь, что используете WHERE, когда удаляете запись из таблицы. Иначе вы удалите все записи из таблицы, сами того не желая.
Удаление таблиц
Если мы хотим удалить все данные из таблицы, но при этом оставить саму таблицу, нам следует использовать команду TRUNCATE:
TRUNCATE TABLE table_name;
В случае, если мы хотим удалить саму таблицу, то нам следует исользовать команду DROP:
DROP TABLE table_name;
Заключение
На этой ноте мы завершаем данный SQL-туториал. Само собой, это не всё, и для полного освоения нужно ещё много изучить, однако данное вступление даст вам толчок для дальнейшего изучения.