Каждый веб-разработчик должен знать SQL, чтобы писать запросы к базам данных. И, хотя, phpMyAdmin никто не отменял, зачастую необходимо испачкать руки, чтобы написать низкоуровневый SQL.
Именно поэтому мы подготовили краткий экскурс по основам SQL. Начнем же!
1. Создание таблицы
Для создания таблиц предназначена инструкция CREATE TABLE
. В качестве аргументов должно быть задано название столбцов, а также их типы данных.
Создадим простую таблицу по имени month. Она состоит из 3 колонок:
- id – Номер месяца в календарном году (целое число).
- name – Название месяца (строка, максимум 10 символов).
- days – Количество дней в этом месяце (целое число).
Вот как будет выглядеть соответствующий SQL запрос:
CREATE TABLE months (
id int,
name varchar(10),
days int
);
Также при создании таблиц целесообразно добавить первичный ключ для одной из колонок. Это позволит держать записи уникальными и ускорит запросы на выборку. Пусть в нашем случае уникальным будет название месяца (столбец name)
CREATE TABLE months (
id int,
name varchar(10),
days int,
PRIMARY KEY (name)
);
Строковые типы данных
Тип данных | Описание |
CHAR | Строка фиксированной длины, состоящая из 1-255 символов |
TEXT | Может хранить не более 65 535 символов |
Числовые типы данных
Тип данных | Описание |
TINYINT | Может хранить числа от -128 до 127 |
SMALLINT | Диапазон от -32 768 до 32 767 |
MEDIUMINT | Диапазон от -8 388 608 до 8 388 607 |
INT | Диапазон от -2 147 483 648 до 2 147 483 647 |
FLOAT | Число с плавающей точкой |
Дата и время
Тип данных | Описание |
DATE | Значения даты |
DATETIME | Значения даты и времени с точностью до минты |
TIME | Значения времени |
2. Вставка строк
Теперь давайте заполнять нашу таблицу months полезной информацией. Добавление записей в таблицу производится через инструкцию INSERT
. Есть два способа записи этой инструкции.
Первый способ не указать имена столбцов, куда будут вставлены данные, а указать только значения.
INSERT INTO months VALUES (1,'January',31);
Этот способ записи прост, но небезопасен, поскольку нет гарантии, что по мере расширения проекта и редактировании таблицы, столбцы будут располагаться в том же порядке, что и ранее. Безопасный (и в тоже время более громоздкий) способ записи инструкции INSERT
требует указания как значений, так и порядка следования столбцов:
INSERT INTO months (id,name,days) VALUES (2,'February',29);
Здесь первое значение в списке VALUES соответствует первому указанному имени столбца и т.д.
3. Извлечение данных из таблиц
Инструкция SELECT
- наш лучший друг, когда мы хотим получить данные из базы данных. Она используется очень часто, так что отнеситесь к этому разделу очень внимательно.
Самый простое использование инструкции SELECT
- запрос, который возвращает все столбцы и строки из таблицы (например, таблицы по имени characters):
SELECT * FROM "characters"
Символ звездочка (*
) означает, что мы хотим получить данные из всех столбцов. Так базы данных SQL обычно состоят из более чем одной таблицы, то требуется обязательно указывать ключевое слово FROM
, следом за которым через пробел должно следовать название таблицы.
Иногда мы не хотим получить данные не из всех столбцов в таблице. Для этого, вместо звездочки (*
) мы должны через запятую записать имена желаемых столбцов.
SELECT id, name FROM month
Кроме того, во многих случаях мы хотим, чтобы полученные результаты были отсортированы в определенном порядке. В SQL мы делаем это с помощью ORDER BY
. Он может принимать опциональный модификатор – ASC
(по-умолчанию) сортирующий по возрастанию или DESC
, сортирующий по убыванию:
SELECT id, name FROM month ORDER BY name DESC
При использовании ORDER BY
убедитесь, что оно будет последним в инструкции SELECT
. В противном случае будет выдано сообщение об ошибке.
4. Фильтрация данных
Вы узнали, как выбрать из базы данных с помощью SQL запроса строго определенные столбцы, но что если нам нужно получить еще и определенные строки? На помощь здесь приходит условие WHERE
, позволяющее нам фильтровать данные в зависимости от условия.
В этом запросе мы выбираем только те месяцы из таблицы month, в которых больше 30 дней с помощью оператора больше (>).
SELECT id, name FROM month WHERE days > 30
Операторы в условии WHERE
Оператор | Проверка |
= | Равенство |
<> | Неравенство |
!= | Неравенство |
< | Меньше |
<= | Меньше или равно |
!< | Не меньше |
> | Больше |
>= | Больше или равно |
!> | Не больше |
BETWEEN | Вхождение в диапазон |
IS NULL | Проверка на пустое значение |
5. Расширенная фильтрация данных. Операторы AND и OR
Ранее мы использовали фильтрацию данных с использованием одного критерия. Для более сложной фильтрации данных можно использовать операторы AND
и OR
и операторов сравнения (=,<,>,<=,>=,<>).
Здесь мы имеем таблицу, содержащую четыре самых продаваемых альбомов всех времен. Давайте выберем те из них, которые классифицируются как рок и у которых менее 50 миллионов проданных копий. Это можно легко сделать путем размещения оператора AND
между этими двумя условиями.
SELECT *
FROM albums
WHERE genre = 'рок' AND sales_in_millions <= 50
ORDER BY released
6. In/Between/Like
WHERE также поддерживает несколько специальных команд, позволяя быстро проверять наиболее часто используемые запросы. Вот они:
IN
– служит для указания диапазона условий, любое из которых может быть выполнено
BETWEEN
– проверяет, находится ли значение в указанном диапазоне
LIKE
– ищет по определенным паттернам
Например, если мы хотим выбрать альбомы с поп и соул музыкой, мы можем использовать IN("value1","value2")
.
SELECT * FROM albums WHERE genre IN ('pop','soul');
Если мы хотим получить все альбомы, изданные между 1975 и 1985годами, мы должны записать:
SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;
7. Функции
SQL напичкан с функциями, которые делают разные полезные вещи. Вот некоторые из наиболее часто используемых:
COUNT()
– возвращает количество строк
SUM()
– возвращает общую сумму числового столбца
AVG()
– возвращает среднее значение из множества значений
MIN()
/ MAX()
– получает минимальное / максимальное значение из столбца
Чтобы получить самый последний год в нашей таблице мы должны записать такой SQL запрос:
SELECT MAX(released) FROM albums;
8. Подзапросы
В предыдущем пункте мы научились делать простые расчеты с данными. Если мы хотим использовать результат от этих расчетов, нам не обойтись без вложенных запросов. Допустим, мы хотим вывести artist, album и release year для старейшего альбома в таблице.
Мы знаем, как получить эти конкретные столбцы:
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
);
9. Объединение таблиц
В более сложных базах данных существует несколько таблиц, связанных друг с другом. Например, ниже представлены две таблицы о видеоиграх (video_games) и разработчиков видеоигр (game_developers).
В таблице video_games есть колонка разработчик (developer_id), но в ней содержится целое число, а не имя разработчика. Это число представляет собой идентификатор (id) соответствующего разработчика из таблицы разработчиков игр (game_developers), связывая логически два списка, что позволяет нам использовать информацию, хранящуюся в них обоих одновременно.
Если мы хотим создать запрос, который возвращает все, что нужно знать об играх, мы можем использовать 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
. Есть несколько других вариантов, но они применимы к менее частым случаям.
10. Алиасы
Если вы посмотрите на предыдущий пример, то вы заметите, что существуют две колонки называемые name. Это сбивает с толку, так что давайте установим псевдоним одного из повторяющихся столбцов, например, name из таблицы game_developers будет называться developer.
Мы также можем сократить запрос задав псевдонимы имен таблиц: video_games назовем games, game_developers - devs:
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;
11. Обновление данных
Часто мы должны изменить данные в некоторых строках. В SQL это делается с помощью инструкции UPDATE
. Инструкция UPDATE
состоит из:
- Таблицы, в которой находится значение для замены;
- Имен столбцов и их новых значений;
- Выбранные с помощью
WHERE
строки, которые мы хотим обновить. Если этого не сделать, то изменятся все строки в таблице.
Ниже приведена таблица tv_series с сериалами с их рейтингом. Однако, в таблицу закралась маленькая ошибка: хотя сериал Игра престолов и описывается как комедия, он на самом деле ей не является. Давайте исправим это!
UPDATE tv_series SET genre = 'драма' WHERE id = 2;
12. Удаление данных
Удаление строки таблицы с помощью SQL - это очень простой процесс. Все, что вам нужно, - это выбрать таблицу и строку, которую нужно удалить. Давайте удалим из предыдущего примера последнюю строку в таблице tv_series. Делается это с помощью инструкции >DELETE
DELETE FROM tv_series WHERE id = 4
Будьте осторожными при написании инструкции DELETE
и убедитесь, что условие WHERE
присутствует, иначе все строки таблицы будут удалены!
13. Удаление таблицы
Если мы хотим, чтобы удалить все строки, но оставить саму таблицу, то воспользуйтесь командой TRUNCATE
:
TRUNCATE TABLE table_name;
В случае, когда мы на самом деле хотим, чтобы удалить и данные, и саму таблицу, то нам пригодится команда DROP
:
DROP TABLE table_name;
Будьте очень осторожны с этими командами. Их нельзя отменить!/p>
На этом мы завершаем наш учебник по SQL! Мы многое о чем не рассказали, но то, что вы уже знаете, должно быть достаточно, чтобы дать вам несколько практических навыков в вашей веб-карьере.