sql_for_beginner.md 36 KB
| Создание ER-диаграммы в среде MySQL Workbench | Содержание | Создание базы данных. Импорт данных. | ||
| Оператор | Описание |
|---|---|
| \= | Оператор равенство |
| <\=> | Оператор эквивалентность Аналогичный оператору равенства, с одним лишь исключением: в отличие от него, оператор эквивалентности вернет ИСТИНУ при сравнении NULL <=> NULL |
| <> или != |
Оператор неравенство |
| < | Оператор меньше |
| <\= | Оператор меньше или равно |
| > | Оператор больше |
| >\= | Оператор больше или равно |
Специальные операторы
IS [NOT] NULL— позволяет узнать равно ли проверяемое значение NULL.Для примера выведем всех членов семьи, у которых статус в семье не равен NULL:
SELECT * FROM FamilyMembers WHERE status IS NOT NULL;[NOT] BETWEEN min AND max— позволяет узнать расположено ли проверяемое значение столбца в интервале между min и max.Выведем все данные о покупках с ценой от 100 до 500 рублей из таблицы Payments:
SELECT * FROM Payments WHERE unit_price BETWEEN 100 AND 500;[NOT] IN— позволяет узнать входит ли проверяемое значение столбца в список определённых значений.Выведем имена членов семьи, чей статус равен «father» или «mother»:
SELECT member_name FROM FamilyMembers WHERE status IN ('father', 'mother');[NOT] LIKE шаблон [ESCAPE символ]— позволяет узнать соответствует ли строка определённому шаблону.Например, выведем всех людей с фамилией «Quincey»:
SELECT member_name FROM FamilyMembers WHERE member_name LIKE '% Quincey';
Трафаретные символы
В шаблоне разрешается использовать два трафаретных символа:
- символ подчеркивания (_), который можно применять вместо любого единичного символа в проверяемом значении
- символ процента (%) заменяет последовательность любых символов (число символов в последовательности может быть от 0 и более) в проверяемом значении.
Шаблон | Описание :--:|-- never% | Сопоставляется любым строкам, начинающимся на «never». %ing | Сопоставляется любым строкам, заканчивающимся на «ing». _ing | Сопоставляется строкам, имеющим длину 4 символа, при этом 3 последних обязательно должны быть «ing». Например, слова «sing» и «wing».
ESCAPE-символ
ESCAPE-символ используется для экранирования трафаретных символов. В случае если вам нужно найти строки, содержащие проценты (а процент — это зарезервированный символ), вы можете использовать ESCAPE-символ.
Например, вы хотите получить идентификаторы задач, прогресс которых равен 3%:
SELECT
job_id
FROM
Jobs
WHERE
progress LIKE '3!%'
ESCAPE '!';
Если бы мы не экранировали трафаретный символ, то в выборку попало бы всё, что начинается на 3.
Логические операторы
Логические операторы необходимы для связывания нескольких условий ограничения строк.
- Оператор NOT — меняет значение специального оператора на противоположный
- Оператор OR — общее значение выражения истинно, если хотя бы одно из них истинно
- Оператор AND — общее значение выражения истинно, если они оба истинны
- Оператор XOR — общее значение выражения истинно, если один и только один аргумент является истинным
Выведем все полёты, которые были совершены на самолёте «Boeing», но, при этом, вылет был не из Лондона:
SELECT
*
FROM
Trip
WHERE
plane = 'Boeing' AND NOT town_from = 'London';
Выборка сводных данных (из двух и более таблиц)
При формировании сводной выборки данные беруться из нескольких таблиц. В операторе FROM исходные таблицы перечисляются через запятую. Также им могут быть присвоены алиасы. Синтаксис запроса выглядит следующийм образом:
SELECT
[ALIAS | TABLE].Название_поля1, [ALIAS | TABLE].Название_поля2,...
FROM
Table1 [ALIAS],
Table2 [ALIAS]
...
При выборке сводных таблиц нужно учитывать, что исходные таблицы перемножаются. Т.е. если на входе у нас были таблицы:
id | Name :-:|-- 1 | Иванов 2 | Петров
| id | Name | Phone |
|---|---|---|
| 1 | Иванов | 322223 |
| 2 | Петров | 111111 |
То при простом запросе без условий
SELECT a.*, b.*
FROM
Table1 a, Table2 b
Получим примерно следующее:
| id | Name | id2 | Name2 | Phone |
|---|---|---|---|---|
| 1 | Иванов | 1 | Иванов | 322223 |
| 1 | Иванов | 2 | Петров | 111111 |
| 2 | Петров | 1 | Иванов | 322223 |
| 2 | Петров | 2 | Петров | 111111 |
Чтобы выбрать уникальные значения, нам нужно использовать оператор WHERE для связи этих таблиц
SELECT a.*, b.Phone
FROM
Table1 a, Table2 b
WHERE
a.Name=b.Name
| id | Name | Phone |
|---|---|---|
| 1 | Иванов | 322223 |
| 2 | Петров | 111111 |
Сводные выборки нужны при импорте данных в базу. Сначала вы выделяете из таблиц импорта словари. А потом из таблиц импорта и словарей формируете запрос INSERT ... SELECT для записи данных в основную таблицу.
Вложенные SQL запросы
Вложенный запрос — это запрос на выборку, который используется внутри инструкции SELECT, INSERT, UPDATE или DELETE или внутри другого вложенного запроса. Подзапрос может быть использован везде, где разрешены выражения.
Пример структуры вложенного запроса
SELECT поля_таблиц
FROM список_таблиц
WHERE конкретное_поле IN (
SELECT поле_таблицы FROM таблица
)
Здесь, SELECT поля_таблиц FROM список_таблиц WHERE конкретное_поле IN (...) — внешний запрос, а SELECT поле_таблицы FROM таблица — вложенный (внутренний) запрос.
Каждый вложенный запрос, в свою очередь, может содержать один или несколько вложенных запросов. Количество вложенных запросов в инструкции не ограничено.
Подзапрос может содержать все стандартные инструкции, разрешённые для использования в обычном SQL-запросе: DISTINCT, GROUP BY, LIMIT, ORDER BY, объединения таблиц, запросов и т.д.
Подзапрос может возвращать скаляр (одно значение), одну строку, один столбец или таблицу (одну или несколько строк из одного или нескольких столбцов). Они называются скалярными, столбцовыми, строковыми и табличными подзапросами.
Подзапрос как скалярный операнд
Скалярный подзапрос — запрос, возвращающий единственное скалярное значение (строку, число и т.д.).
Следующий простейший запрос демонстрирует вывод единственного значения (названия компании). В таком виде он не имеет большого смысла, однако ваши запросы могут быть намного сложнее.
SELECT (
SELECT
name
FROM
company
LIMIT 1
);
Таким же образом можно использовать скалярные подзапросы для фильтрации строк с помощью WHERE, используя операторы сравнения.
SELECT
*
FROM
FamilyMembers
WHERE
birthday = (
SELECT
MAX(birthday)
FROM
FamilyMembers
);
С помощью данного запроса возможно получить самого младшего члена семьи. Здесь используется подзапрос для получения максимальной даты рождения, которая затем используется для фильтрации строк.
Подзапросы с ANY, IN, ALL
ANY — ключевое слово, которое должно следовать за операцией сравнения (>, <, <>, = и т.д.), возвращающее TRUE, если хотя бы одно из значений столбца подзапроса удовлетворяет обозначенному условию.
SELECT поля_таблицы_1
FROM таблица_1
WHERE поле_таблицы_1 <= ANY (SELECT поле_таблицы_2 FROM таблица_2);
ALL — ключевое слово, которое должно следовать за операцией сравнения, возвращающее TRUE, если все значения столбца подзапроса удовлетворяет обозначенному условию.
SELECT поля_таблицы_1
FROM таблица_1
WHERE поле_таблицы_1 > ALL (SELECT поле_таблицы_2 FROM таблица_2);
IN — ключевое слово, являющееся псевдонимом ключевому слову ANY с оператором сравнения = (эквивалентность), либо <> ALL для NOT IN. Например, следующие запросы равнозначны:
...
WHERE поле_таблицы_1 = ANY (SELECT поле_таблицы_2 FROM таблица_2);
...
WHERE поле_таблицы_1 IN (SELECT поле_таблицы_2 FROM таблица_2);
Строковые подзапросы
Строковый подзапрос — это подзапрос, возвращающий единственную строку с более чем одной колонкой. Например, следующий запрос получает в подзапросе единственную строку, после чего по порядку попарно сравнивает полученные значения со значениями во внешнем запросе.
SELECT поля_таблицы_1
FROM таблица_1
WHERE (первое_поле_таблицы_1, второе_поле_таблицы_1) =
(
SELECT первое_поле_таблицы_2, второе_поле_таблицы_2
FROM таблица_2
WHERE id = 10
);
Данную конструкцию удобно использовать для замены логических операторов. Так, следующие два запроса полностью эквивалентны:
SELECT поля_таблицы_1 FROM таблица_1 WHERE (первое_поле_таблицы_1, второе_поле_таблицы_1) = (1, 1);
SELECT поля_таблицы_1 FROM таблица_1 WHERE первое_поле_таблицы_1 = 1 AND второе_поле_таблицы_1 = 1;
Связанные подзапросы
Связанным подзапросом является подзапрос, который содержит ссылку на таблицу, которая была объявлена во внешнем запросе. Здесь вложенный запрос ссылается на внешюю таблицу "таблица_1":
SELECT поля_таблицы_1 FROM таблица_1
WHERE поле_таблицы_1 IN
(
SELECT поле_таблицы_2 FROM таблица_2
WHERE таблица_2.поле_таблицы_2 = таблица_1.поле_таблицы_1
);
Подзапросы как производные таблицы
Производная таблица — выражение, которое генерирует временную таблицу в предложении FROM, которая работает так же, как и обычные таблицы, которые вы указываете через запятую. Так выглядит общий синтаксис запроса с использованием производных таблиц:
SELECT поля_таблицы_1 FROM (подзапрос) [AS] псевдоним_производной_таблицы
Обратите внимание на то, что для производной таблицы обязательно должен указываться её псевдоним, для того, чтобы имелась возможность обратиться к ней в других частях запроса.
Обработка вложенных запросов
Вложенные подзапросы обрабатываются «снизу вверх». То есть сначала обрабатывается вложенный запрос самого нижнего уровня. Далее значения, полученные по результату его выполнения, передаются и используются при реализации подзапроса более высокого уровня и т.д.
Добавление данных, оператор INSERT
Для добавления новых записей в таблицу предназначен оператор INSERT.
Общая структура запроса с оператором INSERT
INSERT INTO имя_таблицы [(поле_таблицы, ...)]
VALUES (значение_поля_таблицы, ...)
| SELECT поле_таблицы, ... FROM имя_таблицы ...
В описанной структуре запроса необязательные параметры указаны в квадратных скобках. Вертикальной чертой обозначен альтернативный синтаксис.
Значения можно вставлять перечислением с помощью слова values, перечислив их в круглых скобках через запятую или c помощью оператора select. Таким образом, добавить новые записей можно следующими способами:
INSERT INTO Goods (good_id, good_name, type)
VALUES (5, 'Table', 2);
INSERT INTO Goods VALUES (5, 'Table', 2);
INSERT INTO Goods
SELECT
good_id, good_name, type
FROM
Goods
where
good_name = 2;
Первичный ключ при добавлении новой записи
Следует помнить, что первичный ключ таблицы является уникальным значением и добавление уже существующего значения приведет к ошибке.
При добавлении новой записи с уникальными индексами выбор такого уникального значения может оказаться непростой задачей. Решением может быть дополнительный запрос, направленный на выявление максимального значения первичного ключа для генерации нового уникального значения.
INSERT INTO Goods
SELECT
COUNT(*) + 1, 'Table', 2
FROM
Goods;
В SQL введен механизм его автоматической генерации. Для этого достаточно снабдить первичный ключ good_id атрибутом AUTO_INCREMENT. Тогда при создании новой записи в качестве значения good_id достаточно передать NULL или 0 — поле автоматически получит значение, равное максимальному значению столбца good_id, плюс единица.
CREATE TABLE Goods (
good_id INT NOT NULL AUTO_INCREMENT
...
);
INSERT INTO Goods VALUES (NULL, 'Table', 2);
Пример
Теперь, зная синткасис команд INSERT и SELECT, можем разобраться как создать из исходного набора данных словари и загрузить данные в БД с учетом внешних ключей
Допустим есть список агентов (данные полученные от заказчика в виде CSV-файла), у которых есть поля название, тип и т.д. (далее по тексту я её называю таблица импорта)
В структуре БД поле "тип агента" создано как внешний ключ на таблицу типов
Заполнение словарей
Для добавления "типов агентов" в таблицу AgentType мы будем использовать альтернативный синтаксис INSERT ... SELECT
Пишем инструкцию SELECT, которая выбирает уникальные записи из таблицы импорта:
SELECT DISTINCT Тип_агента FROM agents_import- Ключевое слово DISTINCT относится только к тому полю, перед которым написано. В нашем случае выбирает уникальные названия типов агентов.
- Откуда брать поле Image в предметной области не написано и в исходных данных его нет. Но т.к. в целевой таблице это поле не обязательное, то можно его пропустить
Этот запрос можно выполнить отдельно, чтобы проверить что получится
После отладки запроса SELECT перед ним допишем запрос INSERT:
INSERT INTO AgentType (Title) SELECT DISTINCT Тип_агента FROM agents_import- Поле ID можно пропустить, оно автоинкрементное и создастся само
- Количество и порядок вставляемых полей (Title) должно быть равным количеству выбираемых полей (Тип_агента)
Если в таблице есть обязательные поля, а нем неоткуда взять для них данные, то мы можем в SELECT вставить фиксированные значения (в примере пустая строка):
```sql INSERT INTO AgentType (Title, Image) SELECT DISTINCT Тип_агента, '' -- ^^ FROM agents_import ```
Заполнение основной таблицы
Тоже сначала пишем SELECT запрос, чтобы проверить те ли данные получаются
напоминаю, что порядок и количество выбираемых и вставляемых полей должны быть одинаковыми
в поле AgentTypeID мы должны вставить ID соответсвующей записи из таблицы AgentType, поэтому выборка у нас из двух таблиц и чтобы не писать перед каждым полем полные названия таблиц мы присваиваем им алиасы
SELECT asi.Наименование_агента, att.ID, asi.Юридический_адрес, asi.ИНН, asi.КПП, asi.Директор, asi.Телефон_агента, asi.Электронная_почта_агента, asi.Логотип_агента, asi.Приоритет FROM agents_import asi, AgentType att WHERE asi.Тип_агента=att.Title
Т.е. мы выбираем перечисленные поля из таблицы agents_import и добавляем к ним ID агента у которого совпадает название.
При выборке из нескольких таблиц исходные данные перемножаются. Т.е. если мы не заполним перед этой выборкой словарь, то
100 * 0 = пустая выборка.Если же мы не укажем условие WHERE, то выберутся, к примеру,
100 * 10 = 1000записей (каждый агент будет в каждой категории). Поэтому важно, чтобы условие WHERE выбирало уникальные значения.Естественно, количество внешних ключей в таблице может быть больше одного, в таком случае в секции FROM перечисляем все используемые словари и в секции WHERE перечисляем условия для всех таблиц объединив их логическим выражением AND
```sql ... WHERE a.Name=b.Name AND a.Title=c.Title AND a.Price=d.Price ... ```где алиасы b, c, d - словарные таблицы, а алиас "а" - таблица импорта
Написав и проверив работу выборки (она должна возвращать столько же записей, сколько в таблице импорта) дописываем команду вставки данных:
INSERT INTO Agent (Title, AgentTypeID, Address, INN, KPP, DirectorName, Phone, Email, Logo, Priority) SELECT asi.Наименование_агента, att.ID, asi.Юридический_адрес, asi.ИНН, asi.КПП, asi.Директор, asi.Телефон_агента, asi.Электронная_почта_агента, asi.Логотип_агента, asi.Приоритет FROM agents_import asi, AgentType att WHERE asi.Тип_агента=att.Title
| Создание ER-диаграммы в среде MySQL Workbench | Содержание | Представления (View) |