sql_for_beginner.md 40 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 unitPrice BETWEEN 100 AND 500;[NOT] IN— позволяет узнать входит (не входит) ли проверяемое значение столбца в список определённых значений.Выведем имена членов семьи, чей статус равен «father» или «mother»:
SELECT memberName FROM FamilyMembers WHERE `status` IN ('father', 'mother');[NOT] LIKE шаблон [ESCAPE символ]— позволяет узнать соответствует ли строка определённому шаблону. ESCAPE символ - это так называемый трафаретный символ, вместо которого может быть что один (_) или несколько (%) любых символов.Например, выведем всех людей с фамилией «Quincey»:
SELECT memberName FROM FamilyMembers WHERE memberName LIKE '% Quincey';
Трафаретные символы
В шаблоне разрешается использовать два трафаретных символа:
- символ подчеркивания (
_), который можно применять вместо любого единичного символа в проверяемом значении - символ процента (
%) заменяет последовательность любых символов (число символов в последовательности может быть от 0 и более) в проверяемом значении.
Шаблон | Описание
:--:|--
never% | Сопоставляется любым строкам, начинающимся на «never».
%ing | Сопоставляется любым строкам, заканчивающимся на «ing».
_ing | Сопоставляется строкам, имеющим длину 4 символа, при этом 3 последних обязательно должны быть «ing». Например, слова «sing» и «wing».
ESCAPE-символ
ESCAPE-символ используется для экранирования трафаретных символов. В случае если вам нужно найти строки, содержащие проценты (а процент — это зарезервированный символ), вы можете использовать ESCAPE-символ.
Например, вы хотите получить идентификаторы задач, прогресс которых равен 3%:
SELECT
jobId
FROM
Jobs
WHERE
progress LIKE '3!%'
ESCAPE '!';
Если бы мы не экранировали трафаретный символ, то в выборку попало бы всё, что начинается на 3.
Логические операторы
Логические операторы необходимы для связывания нескольких условий ограничения строк.
- Оператор NOT — меняет значение специального оператора на противоположный
- Оператор OR — общее значение выражения истинно, если хотя бы одно из них истинно
- Оператор AND — общее значение выражения истинно, если они оба истинны
- Оператор XOR — общее значение выражения истинно, если один и только один аргумент является истинным
Выведем все полёты, которые были совершены на самолёте «Boeing», но, при этом, вылет был не из Лондона:
SELECT
*
FROM
Trip
WHERE
plane = 'Boeing' AND NOT townFrom = '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 (goodId, goodName, `type`)
VALUES (5, 'Table', 2);
-- заполняются все поля в таблице
INSERT INTO Goods
VALUES (5, 'Table', 2);
INSERT INTO Goods
SELECT
goodId, goodName, `type`
FROM
Goods
where
`type` = 2;
Первичный ключ при добавлении новой записи
Следует помнить, что первичный ключ таблицы является уникальным значением и добавление уже существующего значения приведет к ошибке.
При добавлении новой записи с уникальными индексами выбор такого уникального значения может оказаться непростой задачей. Решением может быть дополнительный запрос, направленный на выявление максимального значения первичного ключа для генерации нового уникального значения.
INSERT INTO Goods
SELECT
COUNT(*) + 1, 'Table', 2
FROM
Goods;
В SQL введен механизм его автоматической генерации. Для этого достаточно снабдить первичный ключ goodId атрибутом AUTO_INCREMENT. Тогда при создании новой записи в качестве значения goodId достаточно передать NULL или 0 — поле автоматически получит значение, равное максимальному значению столбца goodId, плюс единица.
CREATE TABLE Goods (
goodId INT NOT NULL AUTO_INCREMENT
...
);
INSERT INTO Goods
VALUES (NULL, 'Table', 2);
Редактирование данных, команда UPDATE
Команда UPDATE применяется для обновления уже имеющихся строк. Она имеет следующий формальный синтаксис:
UPDATE имя_таблицы
SET столбец1 = значение1, столбец2 = значение2, ... столбецN = значениеN
[WHERE условие_обновления]
Например, увеличим у всех товаров цену на 3000:
UPDATE Products
SET Price = Price + 3000;
Однако при выполнении данного запроса в MySQL Workbench мы можем столкнуться с ошибкой "You are using safe update..."
Ошибка говорит о том, что мы находимся в безопасном режиме. И чтобы его отключить, в MySQL Workbench надо перейти в меню Edit -> Preferences и в открывшемся окне перейти к пункту SQL Editor:
В открывшейся вкладке в самом низу надо снять флажок с поля "Safe Updates (reject UPDATEs and DELETEs with no restrictions)" и затем сохранить изменения, нажав на кнопку OK. После этого надо переподключиться к серверу.
Используем выражение WHERE и изменим название производителя с "Samsung" на "Samsung Inc.":
UPDATE Products
SET Manufacturer = 'Samsung Inc.'
WHERE Manufacturer = 'Samsung';
Также можно обновлять сразу несколько столбцов:
UPDATE Products
SET Manufacturer = 'Samsung',
ProductCount = ProductCount + 3
WHERE Manufacturer = 'Samsung Inc.';
При обновлении вместо конкретных значений и выражений мы можем использовать ключевые слова DEFAULT и NULL для установки соответственно значения по умолчанию или NULL:
UPDATE Products
SET ProductCount= DEFAULT
WHERE Manufacturer = 'Huawei';
Удаление данных, команда DELETE
Команда DELETE удаляет данные из БД. Она имеет следующий формальный синтаксис:
DELETE FROM имя_таблицы
[WHERE условие_удаления]
Например, удалим строки, у которых производитель - Huawei:
DELETE FROM Products
WHERE Manufacturer='Huawei';
Или удалим все товары, производителем которых является Apple и которые имеют цену меньше 60000:
DELETE FROM Products
WHERE Manufacturer='Apple' AND Price < 60000;
Если необходимо вовсе удалить все строки вне зависимости от условия, то условие можно не указывать:
DELETE FROM Products;
| Создание ER-диаграммы в среде MySQL Workbench | Содержание | Представления (View) |