Mysql как составить запрос

Время на прочтение
5 мин

Количество просмотров 1.1M

Введение

Язык SQL очень прочно влился в жизнь бизнес-аналитиков и требования к кандидатам благодаря простоте, удобству и распространенности. Из собственного опыта могу сказать, что наиболее часто SQL используется для формирования выгрузок, витрин (с последующим построением отчетов на основе этих витрин) и администрирования баз данных. И поскольку повседневная работа аналитика неизбежно связана с выгрузками данных и витринами, навык написания SQL запросов может стать фактором, из-за которого кандидат или получит преимущество, или будет отсеян. Печальная новость в том, что не каждый может рассчитывать получить его на студенческой скамье. Хорошая новость в том, что в изучении SQL нет ничего сложного, это быстро, а синтаксис запросов прост и понятен. Особенно это касается тех, кому уже доводилось сталкиваться с более сложными языками.

Обучение SQL запросам я разделил на три части. Эта часть посвящена базовому синтаксису, который используется в 80-90% случаев. Следующие две части будут посвящены подзапросам, Join’ам и специальным операторам. Цель гайдов: быстро и на практике отработать синтаксис SQL, чтобы добавить его к арсеналу навыков.

Практика

Введение в синтаксис будет рассмотрено на примере открытой базы данных, предназначенной специально для практики SQL. Чтобы твое обучение прошло максимально эффективно, открой ссылку ниже в новой вкладке и сразу запускай приведенные примеры, это позволит тебе лучше закрепить материал и самостоятельно поработать с синтаксисом.

Кликнуть здесь

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

Структура sql-запросов

Общая структура запроса выглядит следующим образом:

SELECT ('столбцы или * для выбора всех столбцов; обязательно')
FROM ('таблица; обязательно')
WHERE ('условие/фильтрация, например, city = 'Moscow'; необязательно')
GROUP BY ('столбец, по которому хотим сгруппировать данные; необязательно')
HAVING ('условие/фильтрация на уровне сгруппированных данных; необязательно')
ORDER BY ('столбец, по которому хотим отсортировать вывод; необязательно')

Разберем структуру. Для удобства текущий изучаемый элемент в запроса выделяется CAPS’ом.

SELECT, FROM

SELECT, FROM — обязательные элементы запроса, которые определяют выбранные столбцы, их порядок и источник данных.

Выбрать все (обозначается как *) из таблицы Customers:

SELECT * FROM Customers

Выбрать столбцы CustomerID, CustomerName из таблицы Customers:

SELECT CustomerID, CustomerName FROM Customers

WHERE

WHERE — необязательный элемент запроса, который используется, когда нужно отфильтровать данные по нужному условию. Очень часто внутри элемента where используются IN / NOT IN для фильтрации столбца по нескольким значениям, AND / OR для фильтрации таблицы по нескольким столбцам.

Фильтрация по одному условию и одному значению:

select * from Customers
WHERE City = 'London'

Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение):

select * from Customers
where City IN ('London', 'Berlin')

select * from Customers
where City NOT IN ('Madrid', 'Berlin','Bern')

Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям:

select * from Customers
where Country = 'Germany' AND City not in ('Berlin', 'Aachen') AND CustomerID > 15

select * from Customers
where City in ('London', 'Berlin') OR CustomerID > 4

GROUP BY

GROUP BY — необязательный элемент запроса, с помощью которого можно задать агрегацию по нужному столбцу (например, если нужно узнать какое количество клиентов живет в каждом из городов).

При использовании GROUP BY обязательно:

1. перечень столбцов, по которым делается разрез, был одинаковым внутри SELECT и внутри GROUP BY,
2. агрегатные функции (SUM, AVG, COUNT, MAX, MIN) должны быть также указаны внутри SELECT с указанием столбца, к которому такая функция применяется.

Группировка количества клиентов по городу:

select City, count(CustomerID) from Customers
GROUP BY City

Группировка количества клиентов по стране и городу:

select Country, City, count(CustomerID) from Customers
GROUP BY Country, City

Группировка продаж по ID товара с разными агрегатными функциями: количество заказов с данным товаром и количество проданных штук товара:

select ProductID, COUNT(OrderID), SUM(Quantity) from OrderDetails
GROUP BY ProductID

Группировка продаж с фильтрацией исходной таблицы. В данном случае на выходе будет таблица с количеством клиентов по городам Германии:

select City, count(CustomerID) from Customers
WHERE Country = 'Germany'
GROUP BY City

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

select City, count(CustomerID) AS Number_of_clients from Customers
group by City

HAVING

HAVING — необязательный элемент запроса, который отвечает за фильтрацию на уровне сгруппированных данных (по сути, WHERE, но только на уровень выше).

Фильтрация агрегированной таблицы с количеством клиентов по городам, в данном случае оставляем в выгрузке только те города, в которых не менее 5 клиентов:

select City, count(CustomerID) from Customers
group by City
HAVING count(CustomerID) >= 5 

В случае с переименованным столбцом внутри HAVING можно указать как и саму агрегирующую конструкцию count(CustomerID), так и новое название столбца number_of_clients:

select City, count(CustomerID) as number_of_clients from Customers
group by City
HAVING number_of_clients >= 5

Пример запроса, содержащего WHERE и HAVING. В данном запросе сначала фильтруется исходная таблица по пользователям, рассчитывается количество клиентов по городам и остаются только те города, где количество клиентов не менее 5:

select City, count(CustomerID) as number_of_clients from Customers
WHERE CustomerName not in ('Around the Horn','Drachenblut Delikatessend')
group by City
HAVING number_of_clients >= 5

ORDER BY

ORDER BY — необязательный элемент запроса, который отвечает за сортировку таблицы.

Простой пример сортировки по одному столбцу. В данном запросе осуществляется сортировка по городу, который указал клиент:

select * from Customers
ORDER BY City

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

select * from Customers
ORDER BY Country, City

По умолчанию сортировка происходит по возрастанию для чисел и в алфавитном порядке для текстовых значений. Если нужна обратная сортировка, то в конструкции ORDER BY после названия столбца надо добавить DESC:

select * from Customers
order by CustomerID DESC

Обратная сортировка по одному столбцу и сортировка по умолчанию по второму:

select * from Customers
order by Country DESC, City

JOIN

JOIN — необязательный элемент, используется для объединения таблиц по ключу, который присутствует в обеих таблицах. Перед ключом ставится оператор ON.

Запрос, в котором соединяем таблицы Order и Customer по ключу CustomerID, при этом перед названиям столбца ключа добавляется название таблицы через точку:

select * from Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID

Нередко может возникать ситуация, когда надо промэппить одну таблицу значениями из другой. В зависимости от задачи, могут использоваться разные типы присоединений. INNER JOIN — пересечение, RIGHT/LEFT JOIN для мэппинга одной таблицы знаениями из другой,

select * from Orders
join Customers on Orders.CustomerID = Customers.CustomerID
where Customers.CustomerID >10

Внутри всего запроса JOIN встраивается после элемента from до элемента where, пример запроса:

Другие типы JOIN’ов можно увидеть на замечательной картинке ниже:

В следующей части подробнее поговорим о типах JOIN’ов и вложенных запросах.

При возникновении вопросов/пожеланий, всегда прошу обращаться!

Обновлено: 21.01.2022
Опубликовано: 14.06.2017

Тематические термины: MariaDB, MySQL, SQL

В инструкции мы рассмотрим пример запросов MySQL/MariaDB, которые будут интересны новичкам или опытным пользователям в качестве шпаргалки.

Все запросы, за исключением выборки, несут потенциальную опасность для данных, которые хранятся в базе. Перед началом работы с рабочей базой, сделайте резервную копию.

Простые примеры использования SELECT

Синтаксис:

> SELECT <fields1> FROM <table> [JOIN <table2>] [ WHERE <conditions> ORDER BY <fields2> LIMIT <count> ]

* где fields1 — поля для выборки через запятую, также можно указать все поля знаком *; table — имя таблицы, из которой вытаскиваем данные; conditions — условия выборки; fields2 — поле или поля через запятую, по которым выполнить сортировку; count — количество строк для выгрузки.
* запрос в квадратных скобках не является обязательным для выборки данных.

1. Обычная выборка данных

> SELECT * FROM users

* в данном примере мы получаем список всех записей из таблицы users.

2. Выборка данных с объединением двух таблиц (JOIN)

SELECT u.name, r.* FROM users u JOIN users_rights r ON r.user_id=u.id

* в данном примере идет выборка данных с объединением таблиц users и users_rights. Объединяются они по полям user_id (в таблице users_rights) и id (users). Извлекается поле name из первой таблицы и все поля из второй.

3. Выборка с интервалом по времени и/или дате

Варианты запросов и примеров могут быть самые разные. Мы рассмотрим те, с которыми чаще всего приходилось иметь дело автору.

а) известна точка начала и определенный временной интервал:

> SELECT * FROM users WHERE date >= DATE_SUB(NOW(), INTERVAL 1 HOUR)

* будут выбраны данные за последний час (поле date).

б) известны дата начала и дата окончания:

> SELECT * FROM users WHERE date >= ‘2017-10-25’ AND date <= ‘2017-11-25’

* выбираем данные в промежутке между 25.10.2017 и 25.11.2017.

в) известны даты начала и окончания + время:

> SELECT * FROM users WHERE DATE(date) BETWEEN ‘2018-03-25 00:15:00’ AND ‘2018-04-25 15:33:09’;

* выбираем данные в промежутке между 25.03.2018 0 часов 15 минут и 25.04.2018 15 часов 33 минуты и 9 секунд.

г) вытаскиваем данные за определенные месяц и год:

> SELECT * FROM study WHERE MONTH(date) = 4 AND YEAR(date) = 2018

* извлечем данные, где в поле date присутствуют значения для апреля 2018 года.

д) текущая дата минут год:

> SELECT * FROM study WHERE date < (CURDATE() — INTERVAL 1 YEAR)

* мы получим данные, которые имеют в колонке date дату, старше одного года.

4. Выборка максимального, минимального и среднего значения

> SELECT max(area), min(area), avg(area) FROM country

* max — максимальное значение; min — минимальное; avg — среднее.

5. Использование длины строки

> SELECT * FROM users WHERE CHAR_LENGTH(name) = 5;

* данный запрос должен показать всех пользователей, имя которых состоит из 5 символов.

6. Использование лимитов (LIMIT)

Применяется для ограничения количества выводимых результатов. Синтаксис:

<основной запрос> LIMIT [<число2>,] <число1>

 * где число1 — сколько результатов вернуть; число2 — сколько результатов пропустить, необязательный параметр — если его не писать, то отсчет начнется с первой строки.

а) извлечь максимум 15 строк:

> SELECT * FROM users LIMIT 15;

б) выбрать строки с 16 по 25 (запрос со смещением):

> SELECT * FROM users LIMIT 15, 10;

* 15 строк пропускаем, 10 извлекаем.

Примеры более сложных запросов или используемых редко

1. Объединение с группировкой выбранных данных в одну строку (GROUP_CONCAT)

> SELECT GROUP_CONCAT(DISTINCT CONVERT(id USING  ‘utf8’)  SEPARATOR ‘, ‘) as ids FROM users

* из таблицы users извлекаются данные по полю id, все они помещаются в одну строку, значения разделяются запятыми.

2. Группировка данных по двум и более полям

> SELECT * FROM users GROUP BY CONCAT(title, ‘::’, birth)

* итого, в данном примере мы сделаем выгрузку данных из таблицы users и сгруппируем их по полям title и birth. Перед группировкой мы делаем объединение полей в одну строку с разделителем ::.

3. Объединение результатов из двух и более таблиц (UNION)

а) простой вариант использования UNION:

> (SELECT id, fio, address, ‘Пользователи’ as type FROM users)
UNION 
(SELECT id, fio, address, ‘Покупатели’ as type FROM customers)

* в данном примере идет выборка данных из таблиц users и customers.

б) если нам нужно использовать WHERE после UNION, запрос будет сложнее:

> SELECT * FROM (
   (SELECT id, fio, address, ‘Пользователи’ as type FROM users)
UNION 
   (SELECT id, fio, address, ‘Покупатели’ as type FROM customers)
) as U
WHERE U.address LIKE «Садовая%»

4. Выборка средних значений, сгруппированных за каждый час

SELECT avg(temperature), DATE_FORMAT(datetimeupdate, ‘%Y-%m-%d %H’) as hour_datetime FROM archive GROUP BY DATE_FORMAT(datetimeupdate, ‘%Y-%m-%d %H’)

* здесь мы извлекаем среднее значение поля temperature из таблицы archive и группируем по полю datetimeupdate (с разделением времени за каждый час).

5. Использование операторов IF и CASE

Данные операторы позволяют определять исход запроса исходя из условия.

а) выбрать пол мужской или женский:

SELECT IF(sex = ‘m’, ‘мужчина’, ‘женщина’) as sex FROM people

* в данном примере мы возвращаем слово «мужчина», если поле sex равно ‘m‘, иначе — «женщина».

б) заменяем идентификатор времени года более понятным человеку значением:

SELECT CASE season_id WHEN 1 THEN ‘зима’ WHEN 2 THEN ‘весна’ WHEN 3 THEN ‘лето’ WHEN 4 THEN ‘осень’ ELSE ‘неправильный идентификатор времени года’ END as season FROM ` seasons

* в данном примере мы используем оператор CASE. Если 1, то вернем слово «зима», если 2 — «весна» и так далее.

Вставка (INSERT)

Синтаксис 1:

> INSERT INTO <table> (<fields>) VALUES (<values>)

Синтаксис 2:

> INSERT INTO <table> VALUES (<values>)

* где table — имя таблицы, в которую заносим данные; fields — перечисление полей через запятую; values — перечисление значений через запятую.
* первый вариант позволит сделать вставку только по перечисленным полям — остальные получат значения по умолчанию. Второй вариант потребует вставки для всех полей.

1. Вставка нескольких строк одним запросом:

> INSERT INTO cities (`name`, `country`) VALUES (‘Москва’, ‘Россия’), (‘Париж’, ‘Франция’), (‘Фунафути’ ,’Тувалу’);

* в данном примере мы одним SQL-запросом добавим 3 записи.

2. Вставка из другой таблицы (копирование строк, INSERT + SELECT):

Синтаксис при копировании строк из одной таблицы в другую выглядит так:

> INSERT INTO <table1> SELECT * FROM <table2> WHERE <условие для select>;

* где table1 — куда копируем; table2 — откуда копируем.

а) скопировать все без разбора:

> INSERT INTO cities-new SELECT * FROM cities;

* в данном примере мы скопируем все строки из таблицы cities в таблицу cities-new.

б) скопировать определенные столбцы строк с условием:

> INSERT INTO cities-new (`name`, `country`) SELECT `name`, `country` FROM cities WHERE name LIKE ‘М%’;

* извлекаем все записи из таблицы cities, названия которых начинаются на «М» и заносим в таблицу cities-new.

в) копирование с обновлением повторяющихся ключей.

Если копировать таблицы несколько раз, то может возникнуть проблема повторения первичного ключа. В базах данных значения таких ключей должны быть уникальными и при попытке вставить повтор мы получим ошибку «Duplicate entry ‘xxx’ for key ‘PRIMARY’». Чтобы новые строки вставить, а повторяющиеся обновить (если есть изменения), используем «ON DUPLICATE KEY UPDATE»:

> INSERT INTO cities-new SELECT * FROM cities ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `country`=VALUES(`country`);

* в данном примере, как и в предыдущих, мы копируем данные из таблицы cities в таблицу cities-new. Но при совпадении значений первичного ключа мы будем обновлять поля name и country.

Обновление (UPDATE)

Синтаксис:

> UPDATE <table> SET <field>='<value>’ WHERE <conditions>

* где table — имя таблицы; field — поле, для которого будем менять значение; value — новое значение; conditions — условие (без него делать update опасно — можно заменить все данные во всей таблице).

Обновление с использованием замены (REPLACE):

UPDATE <table> SET <field> = REPLACE(<field>, ‘<что меняем>’, ‘<на что>’);

Примеры:

UPDATE cities SET name = REPLACE(name, ‘Масква’, ‘Москва’);

UPDATE cities SET name = REPLACE(name, ‘Масква’, ‘Москва’) WHERE country = ‘Россия’;

UPDATE cities SET name = REPLACE(name, ‘Ма’, ‘Мо’) WHERE name = ‘Масква’;

Если мы хотим перестраховаться, результат замены можно сначала проверить с помощью SELECT:

SELECT REPLACE(name, ‘Ма’, ‘Мо’) FROM cities WHERE name = ‘Масква’;

Удаление (DELETE)

Синтаксис:

> DELETE FROM <table> WHERE <conditions>

* где table — имя таблицы; conditions — условие (как и в случае с UPDATE, использовать DELETE без условия опасно — СУБД не запросит подтверждения, а просто удалит все данные).

Более сложный вариант — удаление данных с объединением таблиц. Запрос будет такого вида:

> DELETE u FROM users u JOIN users_rights r ON r.user_id=u.id WHERE r.admin=’1′

* в данном примере мы удалим записи только из таблицы users (u), которые при объединении с таблицей users_rights будут соответствовать условию r.admin=’1′.

Создание таблицы

Синтаксис:

> CREATE TABLE <table> (<field1> <options1>, <field2> <options2>) <table options>

Пример:

> CREATE TABLE IF NOT EXISTS `users_rights` (
  `id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `rights` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

* где table — имя таблицы (в примере users_rights); field1, field2 — имя полей (в примере создается 3 поля — id, user_id, rights); options1, options2 — параметры поля (в примере int(10) unsigned NOT NULL); table options — общие параметры таблицы (в примере ENGINE=InnoDB DEFAULT CHARSET=utf8).

Использование запросов в PHP

Подключаемся к базе данных:

mysql_connect (‘localhost’, ‘login’, ‘password’) or die («MySQL connect error»);
mysql_select_db (‘db_name’);
mysql_query(«SET NAMES ‘utf8′»);

* где подключение выполняется к базе на локальном сервере (localhost); учетные данные для подключения — login и password (соответственно, логин и пароль); в качестве базы используется db_name; используемая кодировка UTF-8.

Также можно создать постоянное подключение:

mysql_pconnect (‘localhost’, ‘login’, ‘password’) or die («MySQL connect error»);

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

Завершить подключение:

mysql_close();

* в PHP выполняется автоматически, кроме постоянных подключений (mysql_pconnect).

Запрос к MySQL (Mariadb) в PHP делается функцией mysql_query(), а извлечение данных из запроса — mysql_fetch_array():

$result = mysql_query(«SELECT * FROM users»);
while ($mass = mysql_fetch_array($result)) {
    echo $mass[name] . ‘<br>’;
}

* в данном примере выполнен запрос к таблице users. Результат запроса помещен в переменную $result. Далее используется цикл while, каждая итерация которого извлекает массив данных и помещает его в переменную $mass — в каждой итерации мы работаем с одной строкой базы данных.

Используемая функция mysql_fetch_array() возвращает ассоциативный массив, с которым удобно работать, но есть еще альтернатива — mysql_fetch_row(), которая возвращает обычный нумерованный массив.

Экранирование

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

Например:

> SELECT * FROM producrions WHERE kpd = ‘100%’

* если выполнить такой запрос без экранирования, знак %, будет восприниматься как любое количество символов после 100.

Использование переменных

Пременные задаются с помощью знака собаки, например:

> SET @number = 101;

* мы создали переменную number со значением 101.

Теперь можно применить переменную в запросе, например:

> INSERT INTO users (`user_number`, `user_name`) VALUES (@number, CONCAT(‘Пользователь ‘, @number));

👉 Система управления базами данных (СУБД) — это отдельная программа, которая работает как сервер, независимо от PHP.

Создавать свои базы данных, таблицы и наполнять их данными можно прямо из этой же программы, но для выполнения этих операций прежде придётся познакомиться с ещё одним языком программирования — SQL.

SQL или Structured Query Language (язык структурированных запросов) — язык программирования, предназначенный для управления данными в СУБД. Все современные СУБД поддерживают SQL.

На языке SQL выражаются все действия, которые можно провести с данными: от записи и чтения данных, до администрирования самого сервера СУБД. Для повседневной работы совсем не обязательно знать весь этот язык; достаточно ознакомиться лишь с основными понятиями синтаксиса и ключевыми словами.

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

Язык SQL — это в первую очередь язык запросов, а кроме того он очень похож на естественный язык. Каждый раз, когда требуется прочитать или записать любую информацию в БД, требуется составить корректный запрос. Такой запрос должен быть выражен в терминах SQL.

Например, чтобы вывести на экран все записи из таблицы города, составим такой запрос:

ПРОЧИТАТЬ всё ИЗ ТАБЛИЦЫ 'города'

Если перевести этот запрос на язык SQL, то корректным результатом будет:

SELECT * FROM 'cities'

Теперь напишем запрос на добавление в таблицу города нового города:

ВСТАВЬ В ТАБЛИЦУ 'города' ЗНАЧЕНИЯ 'имя города' = 'Санкт-Петербург'

Перевод на SQL:

INSERT INTO 'cities' SET 'name' = 'Санкт-Петербург'

Эта команда создаст в таблице города новую запись, где полю имя города будет присвоено значение Санкт-Петербург.

С помощью SQL можно не только добавлять и читать данные, но и:

• удалять и обновлять записи в таблицах;
• создавать и редактировать сами таблицы;
• производить операции над данными: считать сумму, получать самое большое или малое значение, и так далее;
• настраивать работу сервера СУБД.

MySQL

Существует множество различных реляционных СУБД. Самая известная СУБД — это Microsoft Access, входящая в состав офисного пакета приложений Microsoft Office. Нет никаких препятствий для использования в качестве СУБД MS Access, но для задач веб-программирования гораздо лучше подходит альтернативная программа — MySQL.

В отличие от MS Access, MySQL абсолютно бесплатна, может работать на серверах с Linux, обладает гораздо большей производительностью и безопасностью, что делает её идеальным кандидатом на роль базы данных в веб-разработке.

Подавляющее большинство сайтов и приложений на PHP используют в качестве СУБД именно MySQL.

Установка

Последняя версия MySQL доступна для загрузки по ссылке: https://dev.mysql.com/downloads/mysql/. На этой странице следует выбрать MySQL Installer for Windows и нажать на кнопку Download для загрузки.

В процессе установки запомните директорию, куда вы устанавливаете MySQL (скрывается под ссылкой Advanced options). На шаге Accounts and Roles установщик попросит придумать пароль для доступа к БД (MySQL Root Password) — обязательно запомните или запишите этот пароль — он вам ещё понадобится.

Если для своей работы вы используете программную среду OpenServer, то этот раздел можно смело пропустить, так как в состав OpenServer уже входит свежая версия MySQL.

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

По умолчанию, если вы не устанавливали дополнительные программы, у MySQL нет графического интерфейса пользователя. Это значит, что единственный способ работы с ней — это использование командной строки.

1. Откройте командную строку (Выполнить — cmd.exe).
2. Перейдите в каталог с установленной MySQL: cd /d <каталог установки>/bin.
3. Выполните: mysql -uroot -p.
4. Введите пароль, заданный при установке.

Если вы всё выполнили верно, то в командной строке запустится клиент для работы с MySQL (вы поймете это по строке приглашения «mysql>»). С этого момента можно вводить любые SQL запросы, но каждый запрос обязательно должен заканчиваться точкой с запятой ;.

Оператор SQL create database: создание новой базы данных

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

Начать следует с создания новой базы данных для нашего сайта. Новая БД в MySQL создаётся простой командой:

CREATE DATABASE <имя базы данных>

После этого MySQL создаст для нас новую БД, в которой будет происходить вся дальнейшая работа. Это важно: после создания БД её невозможно будет переименовать, а только удалить и создать заново. По этой причине крайне внимательно подойдите к выбору имени для базы данных.

create table

Зачем нужен:создание таблиц

Создав новую БД, сообщим MySQL, что теперь мы собираемся работать именно с ней. Выбор активной БД выполняется командой:

USE <имя базы>;

Пришло время создать первые таблицы! Для ведения дневника по всем правилам, понадобится создать три таблицы: города (cities), пользователи (users) и записи о погоде weather_log. В подразделе «Запись» этой главы описано, как должна выглядеть структура таблицы weather_log. Переведём это описание на язык SQL:

CREATE TABLE weather_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  city_id INT,
  day DATE,
  temperature INT,
  cloud TINYINT DEFAULT 0
);

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

Теперь создадим таблицу городов:

CREATE TABLE cities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name CHAR(128)
)

MySQL может показать созданную таблицу, если попросить об этом командой:

SHOW COLUMNS FROM weather_log

В ответе будут перечислены все поля таблицы, их тип и другие характеристики.

Что такое первичный ключ

В примере с созданием новой таблицы при перечислении необходимых полей первым полем идёт id INT AUTO_INCREMENT PRIMARY KEY. Это поле называется первичным ключом. Обязательно создавать первичный ключ в каждой таблице.

👉 Первичный ключ — это особенное поле, в котором сохраняется уникальный идентификатор записи.

Первичный ключ нужен, чтобы у программиста и базы данных всегда была возможность однозначно обратиться к одной конкретной записи для её чтения, обновления или удаления. Если назначить поле первичным ключом, то БД будет следить за тем, чтобы значение в этом поле больше не повторялось в таблице.

А если ещё и добавить аттрибут AUTO_INCREMENT, то MySQL при добавлении новых записей будет заполнять это поле сама. AUTO_INCREMENT будет играть роль счётчика — каждая новая запись в таблице получит значение на единицу больше максимального существующего значения.

insert into

Зачем нужен: добавление записи в таблицу

Начнём с добавления новых данных в таблицу. Для добавления записи используется следующий синтаксис:

insert into <название таблицы> set <имя столбца1> = <значение2>, <имя столбца2> = <значение2>...

В начале добавим город в таблицу городов:

insert into cities set name = 'Санкт-Петербург'

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

Теперь создадим запись о погоде за сегодня.

При определении таблицы weather_log мы решили ссылаться на город, путём записи в поле city_id идентификатора города из таблицы cities. Так как мы только что добавили новый город, ничего не мешает использовать его идентификатор в записи о погоде.

Идентификатором города будет первичный ключ, который также был определён в качестве первого поля таблицы. Нумерация этого поля начинается с единицы, значит первая добавленная запись имеет идентификатор 1. Зная это, запрос на добавление записи о погоде в Санкт-Петербурге за третье сентября 2017 года выглядит так:

INSERT INTO weather_log SET city_id = 1, day = '2017-09-03', temperature = 5, cloud = 1;

select. Чтение информации из БД

Для вывода информации из БД используются запросы типа SELECT.

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

SELECT <перечисление полей> FROM <имя таблицы>

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

SELECT id, name FROM cities

Все погодные записи:

SELECT id, day, city_id, temperature, cloud FROM weather_log

Вместо перечисления всех столбцов можно использовать знак звездочки — *.

Оператор update: обновление информации в БД

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

Предположим, что при добавлении погодной записи пользователь ошибся и ввёл неверную дату. Чтобы исправить эту ошибку, нужно использовать оператор обновления — UPDATE. Запрос с этим оператором позволяет обновить значение одного или нескольких полей в существующей записи. Выглядит он так:

UPDATE <имя таблицы> SET <имя столбца1> = <значение2>, <имя столбца2> = <значение2>... WHERE <имя столбца> = <значение>

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

В качестве такого условия лучше всего использовать первичный идентификатор записи. Поэтому, прежде чем выполнять запрос обновления, нужно выполнить запрос на чтение информации из таблицы, чтобы узнать, под каким идентификатором сохранилась ошибочная запись. Допустим, этот идентификатор — единица, а правильная дата — седьмое декабря 2022 года.

Запрос на обновление:

UPDATE weather_log SET day = '2022-12-07' WHERE id = 1

Оператор join: объединение записей из двух таблиц

В нашей таблице для хранения погодного дневника город сохраняется как идентификатор, поэтому при обычном чтении данных из этой таблицы вместо названия города стоит непонятное число. Чтобы подставить на место числа действительное значение, а конкретнее — название города, в SQL существуют операторы объединения — JOIN. Поддержка операторов объединения и позволяет базе данных называться реляционной.

Поменяем запрос на показ погодных записей, чтобы он объединял две таблицы, а в поле города показывалось его название, а не идентификатор:

SELECT day, cities.name, temperature, cloud FROM weather_log JOIN cities ON weather_log.city_id = cities.id

Важно усвоить три самых главных момента:

1. При чтении из объединённых таблиц, в перечислении полей после SELECT нужно явно указывать в поле имени также имя таблицы, с которой производится объединение.
2. Всегда есть основная таблица (тб1), из которой читается большинство полей, и присоединяемая (тб2), имя которой определяется после оператора JOIN.
3. Помимо указания имени второй таблицы, обязательно следует указать условие, по которому будет происходить объединение. В этом примере таким условием будет соответствие идентификатора города из тб1 (weather_log.city_id) первичному ключу города из тб2 (cities.id).

---

«Доктайп» — журнал о фронтенде. Читайте, слушайте и учитесь с нами.

ТелеграмПодкастБесплатные учебники

Вступление

Базы данных являются ключевым компонентом многих веб-сайтов и приложений и лежат в основе хранения и обмена данными в Интернете. Одним из наиболее важных аспектов управления базой данных является практика извлечения данных из базы данных, будь то на разовой основе или частью процесса, который был закодирован в приложении. Существует несколько способов получения информации из базы данных, но один из наиболее часто используемых методов выполняется путем отправки запросов через командную строку.

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

В этом руководстве мы обсудим основной синтаксис SQL-запросов, а также некоторые из наиболее часто используемых функций и операторов. Мы также будем практиковаться в создании запросов SQL с использованием некоторых примеров данных в базе данных MySQL.

MySQL — это система управления реляционными базами данных с открытым исходным кодом. MySQL, одна из наиболее распространенных баз данных SQL, отдает приоритет скорости, надежности и удобству использования. Как правило, она соответствует стандарту ANSI SQL, хотя в некоторых случаях MySQL выполняет операции не так, как признанный стандарт.

Подготовка окружения

В целом, команды и концепции, представленные в этом руководстве, могут использоваться в любой операционной системе на базе Linux, на которой работает любое программное обеспечение базы данных SQL. Тем не менее, он был написан специально для сервера Ubuntu 18.04. Для настройки вам понадобится следующее:

• Сервер с Ubuntu 18.04
• Установленный на сервере MySQL

Если сервер создается в NetAngels, то при создании сервера мы рекомендуем выбрать Ubuntu 18.04 Bionic LAMP

Создание образца базы данных

Прежде чем мы сможем начать делать запросы в SQL, мы сначала создадим базу данных и пару таблиц, а затем заполним эти таблицы некоторыми примерами данных. Это позволит вам получить практический опыт, когда вы начнете делать запросы позже.

Для примера базы данных, которую мы будем использовать в этом руководстве, представьте следующий сценарий:

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

Теперь, что эта традиция продолжается некоторое время, вы решили начать отслеживать записи с этих турниров. Кроме того, чтобы упростить планирование обедов, вы решаете создать запись о днях рождения ваших друзей и их любимых блюдах, сторонах и десертах. Вместо того чтобы хранить эту информацию в физической книге, вы решаете использовать свои навыки работы с базами данных, записав ее в базу данных MySQL.

Если вы создали сервер в NetAngels на основе образа Ubuntu 18.04 Bionic LAMP, то откройте приглашение MySQL выполнив от пользователя root команду:

mysql

Примечание: Если зайти в MySQL таким образом не удается, то для аутентификации с использованием пароля используйте команду:

mysql -u root -p

Затем создайте базу данных, запустив:

CREATE DATABASE `birthdays`;

Затем выберите эту базу данных, набрав:

USE birthdays;

Затем создайте две таблицы в этой базе данных. Мы будем использовать первую таблицу, чтобы отслеживать записи ваших друзей в боулинге. Следующая команда создаст таблицу под названием «tourneys» со столбцами для «name» каждого из ваших друзей, количества турниров, которые они выиграли («wins»), их лучший результат за все время и каков размер обувь для боулинга, которую они носят (размер):

CREATE TABLE tourneys (
name varchar(30),
wins real,
best real,
size real
);

Как только вы запустите команду CREATE TABLE и заполните ее заголовками столбцов, вы получите следующий вывод:

Query OK, 0 rows affected (0.00 sec)

Заполните таблицу ‘tourneys’ некоторыми примерами данных:

INSERT INTO tourneys (name, wins, best, size)
VALUES ('Dolly', '7', '245', '8.5'),
('Etta', '4', '283', '9'),
('Irma', '9', '266', '7'),
('Barbara', '2', '197', '7.5'),
('Gladys', '13', '273', '8');

Вы получите такой вывод:

Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

После этого создайте еще одну таблицу в той же базе данных, которую мы будем использовать для хранения информации о любимых блюдах ваших друзей на день рождения. Следующая команда создает таблицу с именем dinners и столбцами для«имя» каждого из ваших друзей, их «дата рождения», их любимое «блюдо», их любимое «гарнир» и их любимый «десерт»:

CREATE TABLE dinners (
name varchar(30),
birthdate date,
entree varchar(30),
side varchar(30),
dessert varchar(30)
);

Аналогично для этой таблицы вы получите отзыв, подтверждающий успешное выполнение команды:

Query OK, 0 rows affected (0.01 sec)

Заполните эту таблицу также некоторыми примерами данных:

INSERT INTO dinners (name, birthdate, entree, side, dessert)
VALUES ('Dolly', '1946-01-19', 'steak', 'salad', 'cake'),
('Etta', '1938-01-25', 'chicken', 'fries', 'ice cream'),
('Irma', '1941-02-18', 'tofu', 'fries', 'cake'),
('Barbara', '1948-12-25', 'tofu', 'salad', 'ice cream'),
('Gladys', '1944-05-28', 'steak', 'fries', 'ice cream');

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Как только эта команда завершится успешно, вы закончили настройку базы данных. Далее мы рассмотрим основную структуру команд запросов SELECT.

Понимание операторов SELECT

Как упоминалось во введении, SQL-запросы почти всегда начинаются с оператора SELECT. SELECT используется в запросах, чтобы указать, какие столбцы из таблицы должны быть возвращены в наборе результатов. Запросы также почти всегда включают FROM, который используется для указания таблицы, к которой будет обращаться оператор.

Как правило, SQL-запросы следуют этому синтаксису:

SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply;

Например, следующий оператор вернет весь столбец name из таблицыdinners:

SELECT name FROM dinners;

+---------+
| name    |
+---------+
| Dolly   |
| Etta    |
| Irma    |
| Barbara |
| Gladys  |
+---------+
5 rows in set (0.00 sec)

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

SELECT name, birthdate FROM dinners;

+---------+------------+
| name    | birthdate  |
+---------+------------+
| Dolly   | 1946-01-19 |
| Etta    | 1938-01-25 |
| Irma    | 1941-02-18 |
| Barbara | 1948-12-25 |
| Gladys  | 1944-05-28 |
+---------+------------+
5 rows in set (0.00 sec)

Вместо того, чтобы называть конкретный столбец или набор столбцов, вы можете следовать за оператором SELECT со звездочкой (*), которая служит заполнителем, представляющим все столбцы в таблице. Следующая команда возвращает каждый столбец из таблицы tourneys:

SELECT * FROM tourneys;

+---------+------+------+------+
| name    | wins | best | size |
+---------+------+------+------+
| Dolly   |    7 |  245 |  8.5 |
| Etta    |    4 |  283 |    9 |
| Irma    |    9 |  266 |    7 |
| Barbara |    2 |  197 |  7.5 |
| Gladys  |   13 |  273 |    8 |
+---------+------+------+------+
5 rows in set (0.00 sec)

WHERE используется в запросах для фильтрации записей, которые удовлетворяют указанному условию, и любые строки, которые не удовлетворяют этому условию, исключаются из результата. Предложение WHERE обычно соответствует следующему синтаксису:

. . . WHERE column_name comparison_operator value

Оператор сравнения в предложении WHERE определяет способ сравнения указанного столбца со значением. Вот некоторые распространенные операторы сравнения SQL:

Оператор	Что он делает
=	тесты для равенства
!=	тесты для неравенства
<	тесты для меньше, чем
>	тесты для больше
<=	тесты для менее чем или равный к
>=	тесты для больше чем или равный к
BETWEEN	проверяет лежит ли в заданном диапазоне
IN	проверяет содержатся ли строки в наборе значений
EXISTS	тесты на соответствие строки существует при заданных условиях
LIKE	проверяет совпадает ли значение с указанной строкой
IS NULL	тесты для `NULL` значения
IS NOT NULL	тесты для всех других значений, чем `NULL`

Например, если вы хотите найти размер обуви Ирмы, вы можете использовать следующий запрос:

SELECT size FROM tourneys WHERE name = 'Irma';

+------+
| size |
+------+
|    7 |
+------+
1 row in set (0.00 sec)

SQL допускает использование подстановочных знаков, и это особенно удобно при использовании в предложениях WHERE. Знаки процента (%) представляют ноль или более неизвестных символов, а подчеркивания (_) представляют один неизвестный символ. Они полезны, если вы пытаетесь найти конкретную запись в таблице, но не уверены, что эта запись. Чтобы проиллюстрировать это, скажем, что вы забыли любимое блюдо нескольких своих друзей, но вы уверены, что это конкретное блюдо начинается с буквы “t”. Вы можете найти его имя, выполнив следующий запрос:

SELECT entree FROM dinners WHERE entree LIKE 't%';

+--------+
| entree |
+--------+
| tofu   |
| tofu   |
+--------+
2 rows in set (0.00 sec)

Основываясь на вышеприведенном выводе, мы видим, что блюдо — это тофу.

Могут быть случаи, когда вы работаете с базами данных, в которых есть столбцы или таблицы с относительно длинными или трудно читаемыми именами. В этих случаях вы можете сделать эти имена более читабельными, создав псевдоним с ключевым словом AS. Псевдонимы, созданные с помощью AS, являются временными и существуют только на время запроса, для которого они созданы:

SELECT name AS n, birthdate AS b, dessert AS d FROM dinners;

+---------+------------+-----------+
| n       | b          | d         |
+---------+------------+-----------+
| Dolly   | 1946-01-19 | cake      |
| Etta    | 1938-01-25 | ice cream |
| Irma    | 1941-02-18 | cake      |
| Barbara | 1948-12-25 | ice cream |
| Gladys  | 1944-05-28 | ice cream |
+---------+------------+-----------+
5 rows in set (0.00 sec)

Здесь мы сказали SQL отображать столбец name какn, столбец birthdate какb, а столбец sert как d.

Примеры, которые мы рассмотрели до этого момента, включают в себя некоторые из наиболее часто используемых ключевых слов и предложений в запросах SQL. Они полезны для базовых запросов, но они бесполезны, если вы пытаетесь выполнить вычисление или получить скалярное значение (одно значение, а не набор из нескольких различных значений) на основе ваших данных. Это где агрегатные функции вступают в игру.

Агрегатные функции

Часто при работе с данными необязательно просматривать сами данные. Скорее, вам нужна информация о данных. Синтаксис SQL включает в себя ряд функций, которые позволяют интерпретировать или выполнять вычисления для ваших данных, просто выполнив запрос «SELECT». Они известны как aggregate functions.

Функция COUNT считает и возвращает количество строк, соответствующих определенным критериям. Например, если вы хотите узнать, сколько ваших друзей предпочитают тофу для своего дня рождения, вы можете выполнить этот запрос:

SELECT COUNT(entree) FROM dinners WHERE entree = 'tofu';

+---------------+
| COUNT(entree) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

Функция AVG возвращает среднее (среднее) значение столбца. Используя наш пример таблицы, вы можете найти средний лучший результат среди ваших друзей с помощью этого запроса:

SELECT AVG(best) FROM tourneys;

+-----------+
| AVG(best) |
+-----------+
|     252.8 |
+-----------+
1 row in set (0.00 sec)

SUM используется для поиска общей суммы данного столбца. Например, если вы хотите посмотреть, сколько игр вы и ваши друзья играли в боулинг за эти годы, вы можете выполнить этот запрос:

SELECT SUM(wins) FROM tourneys;

+-----------+
| SUM(wins) |
+-----------+
|        35 |
+-----------+
1 row in set (0.00 sec)

Обратите внимание, что функции AVG иSUM будут работать правильно только при использовании с числовыми данными. Если вы попытаетесь использовать их для нечисловых данных, это приведет к ошибке или просто к «0», в зависимости от того, какую СУБД вы используете:

SELECT SUM(entree) FROM dinners;

+-------------+
| SUM(entree) |
+-------------+
|           0 |
+-------------+
1 row in set, 5 warnings (0.00 sec)

MIN используется для поиска наименьшего значения в указанном столбце. Вы можете использовать этот запрос, чтобы увидеть, какой худший общий рекорд в боулинге (с точки зрения количества побед):

SELECT MIN(wins) FROM tourneys;

+-----------+
| MIN(wins) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

Аналогично, MAX используется для поиска наибольшего числового значения в данном столбце. Следующий запрос покажет лучший общий результат в боулинге:

SELECT MAX(wins) FROM tourneys;

+-----------+
| MAX(wins) |
+-----------+
|        13 |
+-----------+
1 row in set (0.00 sec)

В отличие от SUM иAVG, функции MIN иMAX могут использоваться как для числовых, так и для буквенных типов данных. При запуске в столбце, содержащем строковые значения, функция MIN будет отображать первое значение в алфавитном порядке:

SELECT MIN(name) FROM dinners;

+-----------+
| MIN(name) |
+-----------+
| Barbara   |
+-----------+
1 row in set (0.00 sec)

Аналогично, при запуске в столбце, содержащем строковые значения, функция MAX покажет последнее значение в алфавитном порядке:

SELECT MAX(name) FROM dinners;

+-----------+
| MAX(name) |
+-----------+
| Irma      |
+-----------+
1 row in set (0.00 sec)

Агрегатные функции умеют больше того, что было описано в этом разделе. Они особенно полезны при использовании с предложением GROUP BY, которое рассматривается в следующем разделе, а также с несколькими другими предложениями запроса, которые влияют на сортировку наборов результатов.

Управление запросами

В дополнение к предложениям FROM иWHERE, есть несколько других предложений, которые используются для манипулирования результатами запроса SELECT. В этом разделе мы объясним и предоставим примеры для некоторых из наиболее часто используемых предложений запросов.

Одним из наиболее часто используемых предложений запроса, помимо FROM иWHERE, является предложение GROUP BY. Обычно он используется, когда вы выполняете статистическую функцию для одного столбца, но в отношении сопоставления значений в другом.

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

SELECT COUNT(name), entree FROM dinners GROUP BY entree;

+-------------+---------+
| COUNT(name) | entree  |
+-------------+---------+
|           1 | chicken |
|           2 | steak   |
|           2 | tofu    |
+-------------+---------+
3 rows in set (0.00 sec)

Предложение ORDER BY используется для сортировки результатов запроса. По умолчанию числовые значения сортируются в порядке возрастания, а текстовые значения сортируются в алфавитном порядке. Чтобы проиллюстрировать это, следующий запрос перечисляет столбцы name иbirthdate, но сортирует результаты по дате рождения:

SELECT name, birthdate FROM dinners ORDER BY birthdate;

+---------+------------+
| name    | birthdate  |
+---------+------------+
| Etta    | 1938-01-25 |
| Irma    | 1941-02-18 |
| Gladys  | 1944-05-28 |
| Dolly   | 1946-01-19 |
| Barbara | 1948-12-25 |
+---------+------------+
5 rows in set (0.00 sec)

Обратите внимание, что поведение по умолчанию ORDER BY состоит в сортировке набора результатов в порядке возрастания. Чтобы изменить это и отсортировать набор результатов в порядке убывания, закройте запрос с помощью DESC:

SELECT name, birthdate FROM dinners ORDER BY birthdate DESC;

+---------+------------+
| name    | birthdate  |
+---------+------------+
| Barbara | 1948-12-25 |
| Dolly   | 1946-01-19 |
| Gladys  | 1944-05-28 |
| Irma    | 1941-02-18 |
| Etta    | 1938-01-25 |
+---------+------------+
5 rows in set (0.00 sec)

Как уже упоминалось ранее, предложение WHERE используется для фильтрации результатов на основе определенных условий. Однако, если вы используете предложение WHERE с агрегатной функцией, оно вернет ошибку, как в случае со следующей попыткой выяснить, какие стороны являются фаворитами по крайней мере трех ваших друзей:

SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3;

ERROR 1111 (HY000): Invalid use of group function

Предложение HAVING было добавлено в SQL для обеспечения функциональности, аналогичной функциональности предложения WHERE, а также совместимости с агрегатными функциями. Полезно думать о разнице между этими двумя пунктами как о том, что WHERE применяется к отдельным записям, в то время какHAVING применяется к групповым записям. С этой целью каждый раз, когда вы вводите предложение HAVING, также должно присутствовать предложениеGROUP BY.

Следующий пример — еще одна попытка найти, какие гарниры являются фаворитами как минимум трех ваших друзей, хотя этот вернет результат без ошибок:

SELECT COUNT(name), side FROM dinners GROUP BY side HAVING COUNT(name) >= 3;

+-------------+-------+
| COUNT(name) | side  |
+-------------+-------+
|           3 | fries |
+-------------+-------+
1 row in set (0.00 sec)

Агрегатные функции полезны для суммирования результатов определенного столбца в данной таблице. Однако во многих случаях необходимо запросить содержимое более чем одной таблицы. Мы рассмотрим несколько способов сделать это в следующем разделе.

Запрос нескольких таблиц

Чаще всего база данных содержит несколько таблиц, каждая из которых содержит разные наборы данных. SQL предоставляет несколько разных способов выполнения одного запроса для нескольких таблиц.

Предложение JOIN может использоваться для объединения строк из двух или более таблиц в результате запроса. Это достигается путем нахождения связанного столбца между таблицами и соответствующей сортировки результатов в выходных данных.

Операторы SELECT, которые включают предложениеJOIN, обычно следуют этому синтаксису:

SELECT table1.column1, table2.column2
FROM table1
JOIN table2 ON table1.related_column=table2.related_column;

Обратите внимание, что поскольку предложения JOIN сравнивают содержимое нескольких таблиц, в предыдущем примере указывается, из какой таблицы выбрать каждый столбец, предшествуя имени столбца с именем таблицы и точкой. Вы можете указать, из какой таблицы должен быть выбран столбец, например, для любого запроса, хотя это не обязательно при выборе из одной таблицы, как мы делали в предыдущих разделах. Давайте рассмотрим пример, используя наш образец Dата.

Представьте, что вы хотите купить каждому из ваших друзей пару ботинок для боулинга в качестве подарка на день рождения. Поскольку информация о датах рождения и размерах обуви ваших друзей хранится в отдельных таблицах, вы можете запросить обе таблицы по отдельности, а затем сравнить результаты для каждой из них. Тем не менее, с помощью предложения JOIN вы можете найти всю необходимую информацию с помощью одного запроса:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
JOIN dinners ON tourneys.name=dinners.name;

+---------+------+------------+
| name    | size | birthdate  |
+---------+------+------------+
| Dolly   |  8.5 | 1946-01-19 |
| Etta    |    9 | 1938-01-25 |
| Irma    |    7 | 1941-02-18 |
| Barbara |  7.5 | 1948-12-25 |
| Gladys  |    8 | 1944-05-28 |
+---------+------+------------+
5 rows in set (0.00 sec)

Предложение JOIN, используемое в этом примере, без каких-либо других аргументов, является предложением innerJOIN. Это означает, что он выбирает все записи, которые имеют совпадающие значения в обеих таблицах, и печатает их в наборе результатов, в то время как все несоответствующие записи исключаются. Чтобы проиллюстрировать эту идею, давайте добавим новую строку в каждую таблицу, у которой нет соответствующей записи в другой:

INSERT INTO tourneys (name, wins, best, size)
VALUES ('Bettye', '0', '193', '9');

INSERT INTO dinners (name, birthdate, entree, side, dessert)
VALUES ('Lesley', '1946-05-02', 'steak', 'salad', 'ice cream');

Затем повторно запустите предыдущий оператор SELECT с предложением JOIN:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
JOIN dinners ON tourneys.name=dinners.name;

+---------+------+------------+
| name    | size | birthdate  |
+---------+------+------------+
| Dolly   |  8.5 | 1946-01-19 |
| Etta    |    9 | 1938-01-25 |
| Irma    |    7 | 1941-02-18 |
| Barbara |  7.5 | 1948-12-25 |
| Gladys  |    8 | 1944-05-28 |
+---------+------+------------+
5 rows in set (0.00 sec)

Обратите внимание, что, поскольку в таблице «tourneys» нет записи для Лесли, а в таблице «dinners» нет записи для Бетти, эти записи отсутствуют в этих выходных данных.

Тем не менее, можно вернуть все записи из одной из таблиц, используя предложение outer JOIN. В MySQL предложения JOIN записываются какLEFT JOIN или RIGHT JOIN.

Предложение LEFT JOIN возвращает все записи из« левой »таблицы и только совпадающие записи из правой таблицы. В контексте внешних объединений левая таблица — это таблица, на которую ссылается условие FROM, а правая таблица — любая другая таблица, на которую ссылается после оператораJOIN.

Выполните предыдущий запрос еще раз, но на этот раз используйте предложение LEFT JOIN:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
LEFT JOIN dinners ON tourneys.name=dinners.name;

Эта команда будет возвращать каждую запись из левой таблицы (в данном случае, «турниры»), даже если в правой таблице нет соответствующей записи. Каждый раз, когда в правой таблице нет подходящей записи, она возвращается как NULL или просто пустое значение, в зависимости от вашей RDBMS:

+---------+------+------------+
| name    | size | birthdate  |
+---------+------+------------+
| Dolly   |  8.5 | 1946-01-19 |
| Etta    |    9 | 1938-01-25 |
| Irma    |    7 | 1941-02-18 |
| Barbara |  7.5 | 1948-12-25 |
| Gladys  |    8 | 1944-05-28 |
| Bettye  |    9 | NULL       |
+---------+------+------------+
6 rows in set (0.00 sec)

Теперь выполните запрос еще раз, на этот раз с предложением RIGHT JOIN:

SELECT tourneys.name, tourneys.size, dinners.birthdate
FROM tourneys
RIGHT JOIN dinners ON tourneys.name=dinners.name;

Это вернет все записи из правой таблицы (dinners). Поскольку дата рождения Лесли записана в правой таблице, но для нее нет соответствующей строки в левой таблице, столбцы name иsize вернутся как значения NULL в этой строке:

+---------+------+------------+
| name    | size | birthdate  |
+---------+------+------------+
| Dolly   |  8.5 | 1946-01-19 |
| Etta    |    9 | 1938-01-25 |
| Irma    |    7 | 1941-02-18 |
| Barbara |  7.5 | 1948-12-25 |
| Gladys  |    8 | 1944-05-28 |
| NULL    | NULL | 1946-05-02 |
+---------+------+------------+
6 rows in set (0.00 sec)

Обратите внимание, что левые и правые объединения могут быть записаны как LEFT OUTER JOIN илиRIGHT OUTER JOIN, хотя подразумевается часть «OUTER» в предложении. Аналогично, указание INNER JOIN даст тот же результат, что и простое написаниеJOIN.

В качестве альтернативы использованию JOIN для запроса записей из нескольких таблиц, вы можете использовать предложениеUNION.

Оператор UNION работает немного иначе, чем предложениеJOIN: вместо того, чтобы печатать результаты из нескольких таблиц в виде уникальных столбцов с использованием одного оператора SELECT,UNION объединяет результаты двух операторов SELECT в один столбец.

Чтобы проиллюстрировать, запустите следующий запрос:

SELECT name FROM tourneys UNION SELECT name FROM dinners;

Этот запрос удалит все повторяющиеся записи, что является поведением по умолчанию оператора UNION:

+---------+
| name    |
+---------+
| Dolly   |
| Etta    |
| Irma    |
| Barbara |
| Gladys  |
| Bettye  |
| Lesley  |
+---------+
7 rows in set (0.00 sec)

Чтобы вернуть все записи (включая дубликаты), используйте оператор UNION ALL:

SELECT name FROM tourneys UNION ALL SELECT name FROM dinners;

+---------+
| name    |
+---------+
| Dolly   |
| Etta    |
| Irma    |
| Barbara |
| Gladys  |
| Bettye  |
| Dolly   |
| Etta    |
| Irma    |
| Barbara |
| Gladys  |
| Lesley  |
+---------+
12 rows in set (0.00 sec)

Имена и количество столбцов в таблице результатов отражают имя и количество столбцов, запрошенных первым оператором SELECT. Обратите внимание, что при использовании UNION для запроса нескольких столбцов из более чем одной таблицы каждый операторSELECT должен запрашивать одинаковое количество столбцов, соответствующие столбцы должны иметь одинаковые типы данных, а столбцы в каждом операторе SELECT должны быть в том же порядке. В следующем примере показано, что может произойти, если вы используете предложение UNION для двух операторовSELECT, которые запрашивают разное количество столбцов:

SELECT name FROM dinners UNION SELECT name, wins FROM tourneys;

ERROR 1222 (21000): The used SELECT statements have a different number of columns

Другой способ запроса нескольких таблиц — использование subqueries. Подзапросы (также известные как inner или nested query) — это запросы, заключенные в другой запрос. Это полезно в тех случаях, когда вы пытаетесь отфильтровать результаты запроса по сравнению с результатами отдельной агрегатной функции.

Чтобы проиллюстрировать эту идею, скажем, вы хотите знать, кто из ваших друзей выиграл больше матчей, чем Барбара. Вместо того, чтобы узнать, сколько матчей выиграла Барбара, а затем выполнить другой запрос, чтобы узнать, кто выиграл больше игр, вы можете рассчитать обе с помощью одного запроса:

SELECT name, wins FROM tourneys
WHERE wins > (
SELECT wins FROM tourneys WHERE name = 'Barbara'
);

+--------+------+
| name   | wins |
+--------+------+
| Dolly  |    7 |
| Etta   |    4 |
| Irma   |    9 |
| Gladys |   13 |
+--------+------+
4 rows in set (0.00 sec)

Подзапрос в этом операторе был выполнен только один раз; нужно было только найти значение из столбца wins в той же строке, что иBarbara в столбце name, а данные, возвращаемые подзапросом и внешним запросом, не зависят друг от друга. Однако существуют случаи, когда внешний запрос должен сначала прочитать каждую строку в таблице и сравнить эти значения с данными, возвращенными подзапросом, чтобы получить требуемые данные. В этом случае подзапрос называется коррелированным подзапросом.

Следующее утверждение является примером коррелированного подзапроса. Этот запрос пытается выяснить, кто из ваших друзей выиграл больше игр, чем в среднем для тех, у кого одинаковый размер обуви:

SELECT name, size FROM tourneys AS t
WHERE wins > (
SELECT AVG(wins) FROM tourneys WHERE size = t.size
);

Чтобы запрос завершился, он должен сначала собрать столбцы name иsize из внешнего запроса. Затем он сравнивает каждую строку из этого набора результатов с результатами внутреннего запроса, который определяет среднее количество побед для людей с одинаковыми размерами обуви. Поскольку у вас есть только два друга с одинаковым размером обуви, в наборе результатов может быть только одна строка:

+------+------+
| name | size |
+------+------+
| Etta |    9 |
+------+------+
1 row in set (0.00 sec)

Как упоминалось ранее, подзапросы могут использоваться для запроса результатов из нескольких таблиц. Чтобы проиллюстрировать это одним последним примером, скажем, вы хотели устроить неожиданный ужин для лучшего боулера группы. Вы можете узнать, кто из ваших друзей имеет лучший рекорд в боулинге, и вернуть любимое блюдо по следующему запросу:

SELECT name, entree, side, dessert
FROM dinners
WHERE name = (SELECT name FROM tourneys
WHERE wins = (SELECT MAX(wins) FROM tourneys));

+--------+--------+-------+-----------+
| name   | entree | side  | dessert   |
+--------+--------+-------+-----------+
| Gladys | steak  | fries | ice cream |
+--------+--------+-------+-----------+
1 row in set (0.00 sec)

Обратите внимание, что этот оператор не только включает подзапрос, но также содержит подзапрос в этом подзапросе.

Заключение

Выдача запросов является одной из наиболее часто выполняемых задач в области управления базами данных. Существует ряд инструментов администрирования баз данных, таких как phpMyAdmin или pgAdmin, которые позволяют выполнять запросы и визуализировать результаты, но с помощью операторов SELECT это все еще широко распространенный рабочий процесс, который также может предоставить вам больший контроль.

Возможно вам будет полезна наша статья про консольные команды для работы с MySQL или официальная документация по MySQL.