Конспект по изучению SQL (курс)

---

Содержание:

• Урок 1 - Отношение
• Урок 2 - Выборка данных
• Урок 3 - Запросы, групповые операции
• Урок 4 - Вложеннын запросы
• Урок 5 - Запросы корректировки данных
• Урок 6 - Связи между таблицами
• Урок 7 - Запросы на выборку, соединение таблиц
• Урок 8 - Запросы корректировки, соединение таблиц
• Урок 9 - Продвинутые темы
• Различные функции и операторы
• Ограничения в SQL

---

Урок 1 - Отношение

• Основные принципы реляционных баз данных
• Создание таблицы
• Вставка записи в таблицу

Базовые понятия

Основные принципы реляционных баз данных:

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

Терминология реляционных баз данных на примере таблицы Сотрудник:

• отношение – это структура данных целиком, набор записей (в обычном понимании – таблица), в примере – это Сотрудник;
• кортеж – это каждая строка, содержащая данные (более распространенный термин – запись), например, <001, Борин С.А, 234-01-23, программист>, все кортежи в отношении должны быть различны;
• мощность – число кортежей в таблице (проще говоря, число записей), в данном случае 3, мощность отношения может быть любой (от 0 до бесконечности), порядок следования кортежей - неважен;
• атрибут – это столбец в таблице (более распространенный термин – поле), в примере – Табельный номер, Фамилия И.О., Телефон, Должность
• размерность – это число атрибутов в таблице, в данном случае – 4;
• размерность отношения должна быть больше 0, порядок следования атрибутов существенен;
• домен атрибута – это допустимые значения (неповторяющиеся), которые можно занести в поле, например для атрибута Должность домен – {инженер, программист}.

Правила по выбору имени таблицы:

• может включать латинские буквы, цифры и знак подчеркивания, должно начинаться с буквы;
• имя должно быть уникальным в пределах базы данных.

Рекомендации:

• чтобы имя было существительным в единственном числе;
• имя должно быть понятным и соответствовать тому объекту, который оно описывает;
• имя должно быть как можно короче, максимум до 10 символов.

Правила по выбору имени поля:

• может включать латинские буквы, цифры и знак подчеркивания, должно начинаться с буквы;
• имя поля должно быть уникальным в пределах таблицы.

Рекомендации:

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

Рекомендации по именованию ключевых полей:

• имя должно состоять из двух частей: начинаться с названия таблицы, которой поле принадлежит, затем через подчеркивание необходимо указать id (например, employee_id).

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

Для создания таблицы используется SQL-запрос. В нем указывается какая таблица создается, из каких атрибутов(полей) она состоит и какой тип данных имеет каждое поле, при необходимости указывается описание полей (ключевое поле и т.д.). Его структура:

• ключевые слова : CREATE TABLE
• имя создаваемой таблицы;
• открывающая круглая скобка «(»;
• название поля и его описание, которое включает тип поля и другие необязательные характеристики;
• запятая;
• название поля и его описание;
• ...
• закрывающая скобка «)».

Запрос:

CREATE TABLE book(
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(50),
    author VARCHAR(30),
    price DECIMAL(8,2),
    amount INT
);

Вставка записи в таблицу

Для занесения новой записи в таблицу используется SQL запрос, в котором указывается в какую таблицу, в какие поля заносить новые значения. Структура запроса:

• ключевые слова INSERT INTO (ключевое слово INTO можно пропустить);
• имя таблицы, в которую добавляется запись;
• открывающая круглая скобка «(»;
• список полей через запятую, в которые следует занести новые данные;
• закрывающая скобка «)»;
• ключевое слово VALUES;
• открывающая круглая скобка «(»;
• список значений через запятую, которые заносятся в соответствующие поля, при этом текстовые значения заключаются в кавычки, числовые значения записываются без кавычек, в качестве разделителя целой и дробной части используется точка;
• закрывающая скобка «)».

Note: новые значения нельзя добавлять в поля, описанные как PRIMARY KEY AUTO_INCREMENT

Запрос:

INSERT INTO book (title, author, price, amount)
VALUES ('Мастер и Маргарита', 'Булгаков М.А.', 670.99, 3);

Запрос:

INSERT INTO book (title, author, price, amount)
VALUES ('Белая гвардия',     'Булгаков М.А.',    540.50, 5),
       ('Идиот',             'Достоевский Ф.М.', 460.00, 10),
       ('Братья Карамазовы', 'Достоевский Ф.М.', 799.01, 2);

---

Урок 2 - Выборка данных

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

Выборка всех данных из таблицы

Для того чтобы отобрать все данные из таблицы используется SQL запрос следующей структуры:

• ключевое слово SELECT;
• символ «*»;
• ключевое слово FROM;
• имя таблицы.

Запрос:

SELECT * FROM book;

Выборка отдельных столбцов

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

• ключевое слово SELECT;
• список столбцов таблицы через запятую;
• ключевое слово FROM;
• имя таблицы.

Запрос:

SELECT title, amount FROM book;

Присвоение новых имен столбцам при формировании выборки

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

• ключевое слово SELECT;
• имя столбца;
• ключевое слово AS;
• новое название столбца (можно русскими буквами), выводимое в результате запроса, но это должно быть одно слово, если название состоит из двух слов – соединяйте их подчеркиванием, например, Количество_книг;
• запятая;
• имя столбца;
• ....
• ключевое слово FROM;
• имя таблицы.

Запрос:

SELECT title AS Название, amount FROM book;

Выборка данных с созданием вычисляемого столбца

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

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

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

Запросы:

SELECT title, author, price, amount, 
    price * amount AS total 
FROM book;

SELECT title, amount, 
    cast((amount * '1.65') AS DECIMAL(5,2)) AS pack
FROM book;

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

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

Запрос:

SELECT title, author, amount,
    ROUND(price * 0.7, 2) AS new_price
FROM book;

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

В SQL реализована возможность заносить в поле значение в зависимости от условия. Для этого используется функция IF():

IF(логическое_выражение, выражение_1, выражение_2)

Функция вычисляет логическое_выражение, если оно истина – в поле заносится значение выражения_1, в противном случае – значение выражения_2. Все три параметра IF() являются обязательными.

Допускается использование вложенных функций, вместо выражения_1 и/или выражения_2 может стоять новая функция IF().

Запрос:

SELECT title, amount, price,
    ROUND(IF(amount < 4, price * 0.5, IF(amount < 11, price * 0.7, price * 0.9)), 2) AS sale,
    IF(amount < 4, 'скидка 50%', IF(amount < 11, 'скидка 30%', 'скидка 10%')) AS Ваша_скидка
FROM book;

Выборка данных по условию

С помощью запросов можно включать в итоговую выборку не все строки исходной таблицы, а только те, которые отвечают некоторому условию. Для этого после указания таблицы, откуда выбираются данные, задается ключевое слово WHERE и логическое выражение, от результата которого зависит будет ли включена строка в выборку или нет. Если условие – истина, то строка(запись) включается в выборку, если ложь – нет.

Логическое выражение может включать операторы сравнения (равно «=», не равно «<>», больше «>», меньше «<», больше или равно«>=», меньше или равно «<=») и выражения, допустимые в SQL.

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

Запрос:

SELECT title, author, price * amount AS total
FROM book
WHERE price * amount > 4000;

Выборка данных, логические операции

Логическое выражение после ключевого слова WHERE кроме операторов сравнения и выражений может включать логические операции (И «and», ИЛИ «or», НЕ «not») и круглые скобки, изменяющие приоритеты выполнения операций.

Приоритеты операций:

1. круглые скобки
2. умножение (*), деление (/)
3. сложение (+), вычитание (-)
4. операторы сравнения (=, >, <, >=, <=, <>)
5. NOT
6. AND
7. OR

Запрос:

SELECT title, author, price, amount
FROM book
WHERE (price < 500 OR price > 600) AND (price * amount >= 5000);

Выборка данных, операторы BETWEEN, IN

Логическое выражение после ключевого слова WHERE может включать операторы BETWEEN и IN. Приоритет у этих операторов такой же как у операторов сравнения, то есть они выполняются раньше, чем NOT, AND, OR.

Оператор BETWEEN позволяет отобрать данные, относящиеся к некоторому интервалу, включая его границы.

Запрос:

SELECT title, amount 
FROM book
WHERE amount BETWEEN 5 AND 14;

/*Эквивалентно запросу:*/
SELECT title, amount 
FROM book
WHERE amount >= 5 AND amount <=14;

Запрос:

SELECT title, price 
FROM book
WHERE author IN ('Булгаков М.А.', 'Достоевский Ф.М.');

/*Эквивалентно запросу:*/
SELECT title, price 
FROM book
WHERE author = 'Булгаков М.А.' OR author = 'Достоевский Ф.М.';

Выборка данных с сортировкой

При выборке можно указывать столбец или несколько столбцов, по которым необходимо отсортировать отобранные строки. Для этого используются ключевые слова ORDER BY, после которых задаются имена столбцов. При этом строки сортируются по первому столбцу. Если указан второй столбец, сортировка осуществляется только для тех строк, у которых значения первого столбца одинаковы. По умолчанию ORDER BY выполняет сортировку по возрастанию. Чтобы управлять направлением сортировки вручную, после имени столбца указывается ключевое слово ASC (по возрастанию) или DESC (по убыванию).

Столбцы после ключевого слова ORDER BY можно задавать:

• названием столбца;
• номером столбца;
• именем столбца (указанным после AS);
• функция RAND() для случайной сортировки.

Запрос:

SELECT author, title, amount AS Количество
FROM book
WHERE price < 750
ORDER BY author, Количество DESC;

Выборка данных, оператор LIKE

Оператор LIKE используется для сравнения строк. В отличие от операторов отношения равно (=) и не равно (<>), LIKE позволяет сравнивать строки не на полное совпадение (не совпадение), а в соответствии с шаблоном. Шаблон может включать обычные символы и символы-шаблоны. При сравнении с шаблоном, его обычные символы должны в точности совпадать с символами, указанными в строке. Символы-шаблоны могут совпадать с произвольными элементами символьной строки.

Запрос:

SELECT title FROM book 
WHERE title LIKE "______%";
/* эквивалентные условия 
title LIKE "%______"
title LIKE "%______%"
*/

---

Урок 3 - Запросы, групповые операции

• Выбор уникальных элементов столбца
• Выборка данных, групповые функции SUM и COUNT
• Выборка данных, групповые функции MIN, MAX и AVG
• Выборка данных c вычислением, групповые функции
• Вычисления по таблице целиком
• Выборка данных по условию, групповые функции
• Выборка данных по условию, групповые функции, WHERE и HAVING

Выбор уникальных элементов столбца

Чтобы отобрать уникальные элементы некоторого столбца используется ключевое слово DISTINCT, которое размещается сразу после SELECT.

Запрос:

SELECT DISTINCT author
FROM book;

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

Запрос:

SELECT author
FROM book
GROUP BY author;

Выборка данных, групповые функции SUM и COUNT

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

SELECT author,
       SUM(amount),
       COUNT(amount)
FROM book
GROUP BY author;

1. В таблице book определяются строки, в которых в столбце author одинаковые значения:

Получили 3 различные группы:

• группа I объединяет две записи, у которых в столбце author значение Булгаков М.А.;
• группа II объединяет три записи, у которых в столбце author значение Достоевский Ф.М.;
• группа III объединяет одну запись, у которой в столбце author значение Есенин С.А.

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

3. Дальше можно выполнить вычисления над элементами КАЖДОЙ группы в отдельности, например, посчитать общее количество экземпляров книг каждого автора. Для этого используется групповая функция SUM(), а в скобках указывается столбец, по которому нужно выполнить суммирование (в нашем случае amount):

4. Также можно посчитать, сколько записей относится к группе. Для этого используется функция COUNT(), в скобках можно указать ЛЮБОЙ столбец из группы, если группа не содержит пустых значений:

COUNT(*) — подсчитывает все записи, относящиеся к группе, в том числе и со значением NULL; COUNT(имя_столбца)— возвращает количество записей конкретного столбца (только NOT NULL), относящихся к группе; Если столбец указан в SELECT БЕЗ применения групповой функции, то он обязательно должен быть указан и в GROUP BY. Иначе получим ошибку.

Запрос

SELECT author AS Автор,
       COUNT(DISTINCT title) AS Различных_книг, /*Подсчет количества уникальных книг одного автора*/
       SUM(amount) AS Количество_экземпляров
FROM book
GROUP BY author;

Выборка данных, групповые функции MIN, MAX и AVG

К групповым функциям SQL относятся: MIN(), MAX() и AVG(), которые вычисляют минимальное, максимальное и среднее значение элементов столбца, относящихся к группе.

Запрос

SELECT author,
       MIN(price) AS Минимальная_цена,
       MAX(price) AS Максимальная_цена,
       AVG(price) AS Средняя_цена
FROM book
GROUP BY author;

Выборка данных c вычислением, групповые функции

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

Запрос

SELECT author,
       ROUND(SUM(price * amount),2) AS Стоимость,
       ROUND(SUM(price * amount) * 0.18 / 1.18, 2) AS НДС,
       ROUND(SUM(price * amount) / 1.18, 2) AS Стоимость_без_НДС
FROM book
GROUP BY author;

Вычисления по таблице целиком

Групповые функции позволяют вычислять итоговые значения по всей таблице. Например, можно посчитать общее количество книг на складе, вычислить суммарную стоимость и пр. Для этого после ключевого слова SELECT указывается групповая функция для выражения или имени столбца, а ключевые слова GROUP BY опускаются. Результатом таких запросов является единственная строка с вычисленными по таблице значениями.

Запрос

SELECT SUM(amount) AS Количество, 
       SUM(price * amount) AS Стоимость
FROM book;

Выборка данных по условию, групповые функции

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

Запрос

SELECT author,
       MIN(price) AS Минимальная_цена, 
       MAX(price) AS Максимальная_цена
FROM book
GROUP BY author
HAVING SUM(price * amount) > 5000 
ORDER BY Минимальная_цена DESC;

Выборка данных по условию, групповые функции, WHERE и HAVING

WHERE и HAVING могут использоваться в одном запросе. При этом необходимо учитывать порядок выполнения SQL запроса на выборку на СЕРВЕРЕ:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

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

Важно! Порядок ВЫПОЛНЕНИЯ запросов - это не порядок ЗАПИСИ ключевых слов в запросе на выборку. Порядок ВЫПОЛНЕНИЯ нужен для того, чтобы понять, почему, например, в WHERE нельзя использовать имена выражений из SELECT. Просто SELECT выполняется компилятором позже, чем WHERE, поэтому ему неизвестно, какое там выражение написано.

MySQL: FROM => WHERE = SELECT = GROUP BY = HAVING = ORDER BY = LIMIT.
PostgreSQL: FROM => WHERE = GROUP BY = HAVING = SELECT = DISTINCT = ORDER BY = LIMIT.

Запрос

SELECT author, SUM(price * amount) AS Стоимость
FROM book
WHERE title <> 'Идиот' AND title <> 'Белая гвардия'
GROUP BY author
HAVING SUM(price * amount) > 5000
ORDER BY Стоимость DESC;

---

Урок 4 - Вложеннын запросы

• Вложенный запрос, возвращающий одно значение
• Использование вложенного запроса в выражении)
• Вложенный запрос, оператор IN
• Вложенный запрос, операторы ANY и ALL
• Вложенный запрос после SELECT

SQL позволяет создавать вложенные запросы. Вложенный запрос (подзапрос, внутренний запрос) – это запрос внутри другого запроса SQL. Вложенный запрос используется для выборки данных, которые будут использоваться в условии отбора записей основного запроса. Его применяют для:

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

Вложенный запрос имеет следующие компоненты:

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

Вложенные запросы могут включаться в WHERE или HAVING так (в квадратных скобках указаны необязательные элементы, через | – один из элементов):

• WHERE | HAVING выражение оператор_сравнения (вложенный запрос);
• WHERE | HAVING выражение, включающее вложенный запрос;
• WHERE | HAVING выражение [NOT] IN (вложенный запрос);
• WHERE | HAVING выражение оператор_сравнения ANY | ALL (вложенный запрос).

Также вложенные запросы могут вставляться в основной запрос после ключевого слова SELECT.

Вложенный запрос, возвращающий одно значение

Вложенный запрос, возвращающий одно значение, может использоваться в условии отбора записей WHERE как обычное значение совместно с операциями =, <>, >=, <=, >, <.

Запрос

SELECT title, author, price, amount
FROM book
WHERE price = (
         SELECT MIN(price) 
         FROM book
      );

Использование вложенного запроса в выражении

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

Запрос

SELECT title, author, amount 
FROM book
WHERE ABS(amount - (SELECT AVG(amount) FROM book)) > 3;

Вложенный запрос, оператор IN

Вложенный запрос может возвращать несколько значений одного столбца. Тогда его можно использовать в разделе WHERE совместно с оператором IN.

WHERE имя_столбца IN (вложенный запрос, возвращающий один столбец)

Оператор IN определяет, совпадает ли значение столбца с одним из значений, содержащихся во вложенном запросе. При этом логическое выражение после WHERE получает значение истина. Оператор NOT IN выполняет обратное действие – выражение истинно, если значение столбца не содержится во вложенном запросе.

Запрос

SELECT title, author, amount, price
FROM book
WHERE author IN (
        SELECT author 
        FROM book 
        GROUP BY author 
        HAVING SUM(amount) >= 12
      );

Вложенный запрос, операторы ANY и ALL

Вложенный запрос, возвращающий несколько значений одного столбца, можно использовать для отбора записей с помощью операторов ANY и ALL совместно с операциями отношения (=, <>, <=, >=, <, >).

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

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

• amount > ANY (10, 12) эквивалентно amount > 10
• amount < ANY (10, 12) эквивалентно amount < 12
• amount = ANY (10, 12) эквивалентно (amount = 10) OR (amount = 12), а также amount IN (10, 12)
• amount <> ANY (10, 12) вернет все записи с любым значением amount, так как эквивалентно (amount <> 10) OR (amount <> 12)

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

• amount > ALL (10, 12) эквивалентно amount > 12
• amount < ALL (10, 12) эквивалентно amount < 10
• amount = ALL (10, 12) не вернет ни одной записи, так как эквивалентно (amount = 10) AND (amount = 12)
• amount <> ALL (10, 12) вернет все записи кроме тех, в которых amount равно 10 или 12

Важно! Операторы ALL и ANY можно использовать только с вложенными запросами. В примерах выше (10, 12) приводится как результат вложенного запроса просто для того, чтобы показать как эти операторы работают. В запросах так записывать нельзя.

Запрос

SELECT title, author, amount, price
FROM book
WHERE amount < ANY (
        SELECT AVG(amount) 
        FROM book 
        GROUP BY author 
      );

Вложенный запрос после SELECT

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

Запрос

SELECT title, author, amount,
    ((SELECT MAX(amount) FROM book) - amount) AS Заказ
FROM book
WHERE amount <> (SELECT MAX(amount) FROM book)

---

Урок 5 - Запросы корректировки данных

• Добавление записей из другой таблицы
• Добавление записей, вложенные запросы
• Запросы на обновление
• Запросы на обновление нескольких столбцов
• Запросы на обновление нескольких таблиц
• Запросы на удаление
• Запросы на создание таблицы

Добавление записей из другой таблицы

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

Правила соответствия между полями таблицы и вставляемыми значениями из запроса:

1. количество полей в таблице и количество полей в запросе должны совпадать;
2. должно существовать прямое соответствие между позицией одного и того же элемента в обоих списках, поэтому первый столбец запроса должен относиться к первому столбцу в списке столбцов таблицы, второй – ко второму столбцу и т.д.
3. типы столбцов запроса должны быть совместимы с типами данных соответствующих столбцов таблицы (целое число можно занести в поле типа DECIMAL, обратная операция – недопустима).

Запрос

INSERT INTO book (title, author, price, amount) 
SELECT title, author, price, amount 
FROM supply;

Добавление записей, вложенные запросы

В запросах на добавление можно использовать вложенные запросы.

Запрос

INSERT INTO book (title, author, price, amount) 
SELECT title, author, price, amount 
FROM supply
WHERE title NOT IN (
        SELECT title 
        FROM book
      );

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

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

UPDATE таблица SET поле = выражение

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

Запрос

UPDATE book 
SET price = 0.7 * price 
WHERE amount < 5;

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

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

UPDATE таблица SET поле1 = выражение1, поле2 = выражение2

Запрос

UPDATE book 
SET amount = amount - buy,
    buy = 0;

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

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

• для столбцов, имеющих одинаковые имена, необходимо указывать имя таблицы, к которой они относятся, например, book.price – столбец price из таблицы book, supply.price – столбец price из таблицы supply;
• все таблицы, используемые в запросе, нужно перечислить после ключевого слова UPDATE;
• в запросе обязательно условие WHERE, в котором указывается условие при котором обновляются данные.

Запрос

UPDATE book, supply 
SET book.amount = book.amount + supply.amount
WHERE book.title = supply.title AND book.author = supply.author;

Запросы на удаление

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

DELETE FROM таблица;

Этот запрос удаляет все записи из указанной после FROM таблицы.

Запрос

DELETE FROM supply 
WHERE title IN (
        SELECT title 
        FROM book
      );

Запросы на создание таблицы

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

CREATE TABLE имя_таблицы AS
SELECT ...

Запрос

CREATE TABLE ordering AS
SELECT author, title,
    (
        SELECT ROUND(AVG(amount)) 
        FROM book
    ) AS amount
FROM book
WHERE amount < 4;

SELECT * FROM ordering;

---

Урок 6 - Связи между таблицами

• Связь «один ко многим»
• Связь «многие ко многим»
• Создание таблицы с внешними ключами
• Действия при удалении записи главной таблицы

Связь «один ко многим»

Рассмотрим таблицу book

В этой таблице фамилии авторов повторяются для нескольких книг. А что, если придется вместо инициалов для каждого автора хранить его полное имя и отчество? Тогда, если в таблице содержится информация о 50 книгах Достоевского, придется 50 раз исправлять «Ф.М.» на «Федор Михайлович». При этом, если в некоторых записях использовать «Фёдор Михайлович» (c буквой ё), то мы вообще получим двух разных авторов...

Чтобы устранить эту проблему в реляционных базах данных создается новая таблица author, в которой перечисляются все различные авторы, а затем эта таблица связывается с таблицей book. При этом такая связь называется «один ко многим», таблица author называется главной, таблица book – связанной или подчиненной.

Связь «один ко многим» имеет место, когда одной записи главной таблицы соответствует несколько записей связанной таблицы, а каждой записи связанной таблицы соответствует только одна запись главной таблицы. Обозначается это так:

Этапы реализации связи «один ко многим» на следующем примере:

Один автор может написать несколько книг, а одна книга написана только одним автором. Для каждой книги известны ее количество и цена.

1. Создать таблицу author, в которую включить уникальных авторов книг, хранящихся на складе:

2. Обе таблицы должны содержать первичный ключ, в таблице book он уже есть, в таблицу author добавим ключ author_id:

3. Включить в таблицу book связанный столбец (внешний ключ, FOREIGN KEY), соответствующий по имени и типу ключевому столбцу главной таблицы (в нашем случае это столбец author_id). Для наглядности связь на схеме обозначается стрелкой от ключевого столбца главной таблицы к внешнему ключу связной таблицы:

Связь «многие ко многим»

На предыдущем шаге мы реализовали связь «один ко многим» для книг и авторов. Она означает, что каждый автор написал несколько книг, но каждую книгу написал только один автор. На самом деле, это не совсем верное утверждение. Например, книга «12 стульев» написана двумя авторами Ильфом И.А. и Петровым Е.П. С другой стороны, эти авторы написали и другие книги, например «Золотой теленок».

Для соединения таких таблиц используется связь «многие ко многим».

Связь «многие ко многим» имеет место когда каждой записи одной таблицы соответствует несколько записей во второй, и наоборот, каждой записи второй таблицы соответствует несколько записей в первой. Обозначается это так:

Этапы реализации связи «многие ко многим» на следующем примере:

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

1. Создать таблицу author, в которую включить уникальных авторов книг, хранящихся на складе:

2. В обеих таблицах необходимо определить первичный ключ, в нашем случае в таблице book он уже есть, поэтому достаточно включить первичный ключ author_id в таблицу author:

3. Создать новую таблицу-связку, состоящую из двух столбцов, соответствующих по имени и типу ключевым столбцам исходных таблиц. Каждый из этих столбцов является внешним ключом (FOREIGN KEY) и связан с ключевым столбцом каждой таблицы. Для наглядности связи на схеме обозначаются стрелкой от ключевого столбца исходной таблицы к внешнему ключу связной таблицы.

4. Дальше необходимо определиться с первичным ключом таблицы-связки. Можно сделать два ключевых столбца, тогда все записи в этой таблице должны быть уникальными, то есть не повторяться. Для связи автор-книга этот вариант подходит. Но в некоторых случаях записи в таблице-связке могут повторяться, например, если мы будем продавать книги покупателям (один человек может купить несколько книг, а одну и ту же книгу могут купить несколько человек). Тогда в таблицу-связку включают дополнительные столбцы для идентификации записей, например, дату продажи, также в таблицу-связку добавляют первичный ключ. Мы воспользуемся вторым способом:

Создание таблицы с внешними ключами

При создании зависимой таблицы (таблицы, которая содержит внешние ключи) необходимо учитывать, что:

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

FOREIGN KEY (связанное_поле_зависимой_таблицы)
REFERENCES главная_таблица (связанное_поле_главной_таблицы)

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

Запрос

CREATE TABLE book (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(50),
    author_id INT NOT NULL,
    price DECIMAL(8,2),
    amount INT,
    FOREIGN KEY (author_id)  REFERENCES author (author_id)
);

Действия при удалении записи главной таблицы

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

• CASCADE: автоматически удаляет строки из зависимой таблицы при удалении связанных строк в главной таблице;
• SET NULL: при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL (В этом случае столбец внешнего ключа должен поддерживать установку NULL);
• SET DEFAULT похоже на SET NULL за тем исключением, что значение внешнего ключа устанавливается не в NULL, а в значение по умолчанию для данного столбца;
• RESTRICT: отклоняет удаление строк в главной таблице при наличии связанных строк в зависимой таблице.

При удалении автора из таблицы author, необходимо удалить все записи о книгах из таблицы book, написанные этим автором

Запрос

CREATE TABLE book (
    book_id INT PRIMARY KEY AUTO_INCREMENT, 
    title VARCHAR(50), 
    author_id INT NOT NULL, 
    price DECIMAL(8,2), 
    amount INT, 
    FOREIGN KEY (author_id)  REFERENCES author (author_id) ON DELETE CASCADE
);

---

Урок 7 - Запросы на выборку, соединение таблиц

• Соединение INNER JOIN
• Внешнее соединение LEFT и RIGHT OUTER JOIN
• Перекрестное соединение CROSS JOIN
• Запросы на выборку из нескольких таблиц
• Оператор USING

Соединение INNER JOIN

Оператор внутреннего соединения INNER JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным.

SELECT
 ...
FROM
    таблица_1 INNER JOIN таблица_2
    ON условие
...

Результат запроса формируется так:

• каждая строка одной таблицы сопоставляется с каждой строкой второй таблицы;
• для полученной «соединённой» строки проверяется условие соединения;
• если условие истинно, в таблицу результата добавляется соответствующая «соединённая» строка.

Внешнее соединение LEFT и RIGHT OUTER JOIN

Оператор внешнего соединения LEFT OUTER JOIN (можно использовать LEFT JOIN) соединяет две таблицы. Порядок таблиц для оператора важен, поскольку оператор не является симметричным.

SELECT
 ...
FROM
    таблица_1 LEFT JOIN таблица_2
    ON условие
...

Результат запроса формируется так:

• в результат включается внутреннее соединение (INNER JOIN) первой и второй таблицы в соответствии с условием;
• затем в результат добавляются те записи первой таблицы, которые не вошли во внутреннее соединение на шаге 1, для таких записей соответствующие поля второй таблицы заполняются значениями NULL.

Соединение RIGHT JOIN действует аналогично, только в пункте 2 первая таблица меняется на вторую и наоборот.

Перекрестное соединение CROSS JOIN

Оператор перекрёстного соединения, или декартова произведения CROSS JOIN (в запросе вместо ключевых слов можно поставить запятую между таблицами) соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным. Его структура:

SELECT
 ...
FROM
    таблица_1 CROSS JOIN  таблица_2
...

/* или */
SELECT
 ...
FROM
    таблица_1, таблица_2
...

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

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

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

Пусть таблицы связаны между собой следующим образом:

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

SELECT
 ...
FROM
    first 
    INNER JOIN second ON first.first_id = second.first_id
    INNER JOIN third ON second.second_id = third.second_id
...

Если же таблицы связаны так:

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

SELECT
 ...
FROM
    first 
    INNER JOIN third ON first.first_id = third.first_id
    INNER JOIN second ON third.second_id = second.second_id 
...

В этом случае рекомендуется соединение таблиц записывать последовательно, «по кругу»: first → third → second.

Оператор USING

При описании соединения таблиц с помощью JOIN в некоторых случаях вместо ON и следующего за ним условия можно использовать оператор USING().

USING позволяет указать набор столбцов, которые есть в обеих объединяемых таблицах. Если база данных хорошо спроектирована, а каждый внешний ключ имеет такое же имя, как и соответствующий первичный ключ (например, genre.genre_id = book.genre_id), тогда можно использовать предложение USING для реализации операции JOIN. При этом после SELECT, при использовании столбцов из USING(), необязательно указывать, из какой именно таблицы берется столбец.

Вариант с ON

SELECT title, name_author, author.author_id /* явно указать таблицу - обязательно */
FROM 
    author INNER JOIN book
    ON author.author_id = book.author_id;

Вариант с USING

SELECT title, name_author, author_id /* имя таблицы, из которой берется author_id, указывать не обязательно */
FROM 
    author INNER JOIN book
    USING(author_id);

Запись условия соединения с ON является более общим случаем, так как

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

---

Урок 8 - Запросы корректировки, соединение таблиц

• Запросы на обновление, связанные таблицы
• Запросы на добавление, связанные таблицы
• Удаление записей, использование связанных таблиц

Запросы на обновление, связанные таблицы

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

UPDATE таблица_1
     ... JOIN таблица_2
     ON выражение
     ...
SET ...   
WHERE ...;

Запросы на добавление, связанные таблицы

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

INSERT INTO таблица (список_полей)
SELECT список_полей_из_других_таблиц
FROM 
    таблица_1 
    ... JOIN таблица_2 ON ...
    ...

Удаление записей, использование связанных таблиц

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

DELETE FROM таблица_1
USING 
    таблица_1 
    INNER JOIN таблица_2 ON ...
WHERE ...

Урок 9 - Продвинутые темы

• Оконные функции
• Функция INSTR
• Функция REGEXP
• Выборка данных по нескольким условиям, оператор CASE
• Табличные выражения, оператор WITH

Оконные функции

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

Оконная функция в SQL - функция, которая работает с выделенным набором строк (окном, партицией) и выполняет вычисление для этого набора строк в отдельном столбце.

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

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

название_функции(выражение) 
    OVER (
        PARTITION BY столбец_1, столбец_2, ... - это окно
        ORDER BY ... - сортировка 
        ROWS BETWEEN - границы окна
          ...
    )

Столбцы, образующие окно записываются после PARTITION BY. Окном считается совокупность записей, имеющих в столбцах, указанных после PARTITION BY, одинаковые значения. Причем все разделы OVER являются не обязательными, но обязательно нужно указать либо окно, либо сортировку. Самый простой синтаксис оконного выражения:

название_функции(выражение) 
    OVER (
        ORDER BY ...
    )

Такое оконное выражение позволяет выполнять одинаковые действия над всеми записями таблицы (здесь окно - вся таблица).

При использовании агрегирующих функций предложение GROUP BY сокращает количество строк в запросе с помощью их группировки.

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

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

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

Синтаксис оконных функций вне зависимости от их класса будет так или иначе состоять из идентичных команд.

Оконные функции можно прописывать как под командой SELECT, так и в отдельном ключевом слове WINDOW, где окну дается алиас (псевдоним), к которому можно обращаться в SELECT выборке.

Множество оконных функций можно разделять на 3 класса:

• Агрегирующие (Aggregate)
• Ранжирующие (Ranking)
• Функции смещения (Value)

Агрегирующие:

Ранжирующие:

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

• ROW_NUMBER() - функция вычисляет последовательность ранг (порядковый номер) строк внутри партиции, НЕЗАВИСИМО от того, есть ли в строках повторяющиеся значения или нет.
• RANK() - функция вычисляет ранг каждой строки внутри партиции. Если есть повторяющиеся значения, функция возвращает одинаковый ранг для таких строчек, пропуская при этом следующий числовой ранг.
• DENSE_RANK() - то же самое что и RANK, только в случае одинаковых значений DENSE_RANK не пропускает следующий числовой ранг, а идет последовательно.

Для SQL пустые NULL значения будут определяться одинаковым рангом.

Функции смещения:

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

• LAG() - функция, возвращающая предыдущее значение столбца по порядку сортировки.
• LEAD() - функция, возвращающая следующее значение столбца по порядку сортировки.

Функция INSTR

Функция INSTR() выполняет поиск подстроки (указанного текста) в строке, и возвращает позицию её первого символа. Функция INSTR() принимает 2 параметра: строку "где нужно искать", а затем строку "что нужно искать". Нумерация символов начинается с 1.

SELECT
    INSTR(поле_таблицы, что_будем_искать)
FROM
    имя_таблицы
WHERE
    условие_для_выборки;

Функция REGEXP

REGEXP (regular expression) представляет собой мощный инструмент для работы с текстом, который позволяет осуществлять поиск и сопоставление подстрок с использованием шаблонов. Он использует регулярные выражения для определения шаблонов, которые будут использоваться при поиске и сопоставлении текста.

SELECT
    *
FROM
    имя_таблицы
WHERE
    колонка REGEXP 'регулярное выражение';

Выборка данных по нескольким условиям, оператор CASE

С помощью оператора CASE можно в зависимости от нескольких условий получить один из нескольких результатов.

Оператор CASE записывается в виде:

CASE  
     WHEN логическое_выражение_1 THEN выражение_1
     WHEN логическое_выражение_2 THEN выражение_2
     ...
     ELSE выражение_else   
END

Раздел ELSE является необязательным.

Выполняется оператор CASE так:

• вычисляется логическое_выражение_1, если оно истинно, то результатом оператора является выражение_1, если ложно - выполнение оператора продолжается;
• вычисляется логическое_выражение_2, если оно истинно, то результатом оператора является выражение_2, если ложно - выполнение оператора продолжается;
• если все логические выражения оказались ложными, то результат оператора - выражение_else

CASE можно использовать в SELECT, UPDATE, DELETE, SET, WHERE, ORDER BY, HAVING - всюду, где можно использовать выражения.

Табличные выражения, оператор WITH

Табличное выражение определяется с помощью оператора WITH и является частью запроса. Его синтаксис:

WITH имя_выражения (имя_1, имя_2,...)
  AS
    (
     SELECT столбец_1, столбец_2,
     FROM 
       ... 
     )
SELECT ...
   FROM имя_выражения
   ...

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

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

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

---

Различные функции и операторы

• Оператор LIMIT
• Функция GETDATE
• Функция DATE_ADD
• Функция CURRENT_TIMESTAMP
• Функция DATEDIFF
• Функция MONTH
• Функция MONTHNAME
• Функция CONCAT
• Функция RAND
• Функция IFNULL
• Функция LEFT
• Использование временного имени таблицы (алиаса)
• Представление (VIEW)
• Хранимые процедуры
• Оператор UNION
• Оператор EXISTS
• Оператор CREATE DATABASE
• Оператор DROP DATABASE
• Оператор DROP TABLE
• Оператор ALTER TABLE
• Оператор BACKUP DATABASE
• Переменные, оператор SET

Оператор LIMIT

Для ограничения вывода записей в SQL используется оператор LIMIT, после которого указывается количество строк. Результирующая таблица будет иметь количество строк не более указанного после LIMIT. LIMIT размещается после раздела ORDER BY.

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

Запрос

SELECT *
FROM trip
ORDER BY  date_first
LIMIT 1;

Ключевое слово OFFSET используется для указания места, откуда следует выбирать строки. Например:

Запрос

SELECT first_name, last_name
FROM Customers
LIMIT 2 OFFSET 3;

Здесь мы выбираем две строки, начиная с четвертой строки. OFFSET 3 означает, что первые 3 строки исключены.

Функция GETDATE

Эта функция используется для получения текущей даты и времени.

SELECT GETDATE();

Функция DATE_ADD

Для сложения даты с числом используется функция DATE_ADD.

DATE_ADD(дата, INTERVAL число единица_измерения)

• единица_измерения (использовать прописные буквы) – это день(DAY), месяц(MONTH), неделя(WEEK) и пр.,
• число – целое число,
• дата – значение даты или даты и времени.

DATE_ADD('2020-02-02', INTERVAL 45 DAY) /* возвращает 18 марта 2020 года */
DATE_ADD('2020-02-02', INTERVAL 6 MONTH) /* возвращает 2 августа 2020 года */

Функция CURRENT_TIMESTAMP

Эта функция используется для получения текущей временной метки в системе.

SELECT CURRENT_TIMESTAMP;

Функция DATEDIFF

Для вычитания двух дат используется функция DATEDIFF(дата_1, дата_2), результатом которой является количество дней между дата_1 и дата_2. (По факту просто дата_1 - дата_2) Например,

DATEDIFF('2020-04-01', '2020-03-28') = 4
DATEDIFF('2020-05-09','2020-05-01') = 8

Функция MONTH

Для того, чтобы выделить номер месяца из даты используется функция MONTH(дата). Например,

MONTH('2020-04-12') = 4.

Функция MONTHNAME

Для того, чтобы выделить название месяца из даты используется функция MONTHNAME(дата), которая возвращает название месяца на английском языке для указанной даты. Например,

MONTHNAME('2020-04-12') = 'April'

Функция CONCAT

Для того, чтобы объединить строки из разных полей и/или со строковым литералом, нужно использовать функцию CONCAT():

CONCAT(поле_1 | литерал_1, поле_2 | литерал_2, ...)

Функция RAND

Генерация случайных чисел в интервале от 0 до 1 (не включительно) осуществляется с помощью функции RAND(). Так же функцию RAND() можно использовать для сортировки (после ORDER BY) в случайной последовательности

Функция IFNULL

Для проверки значения на NULL можно использовать функцию IFNULL().

IFNULL(expression, value_if_null)

• expression — значение для проверки как NULL;
• value_if_null — значение, возвращаемое, если выражение равно NULL.

Функция IFNULL вернёт выражение, если выражение NOT NULL, и value_if_null, если выражение равно NULL.

Функция LEFT

Чтобы выделить крайние левые n символов из строки используется функция LEFT(строка, n)

LEFT("abcde", 3) -> "abc"

Использование временного имени таблицы (алиаса)

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

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

Для присваивания псевдонима существует 2 варианта:

• с использованием ключевого слова AS

FROM fine AS f, traffic_violation AS tv

• а так же и без него

FROM fine f, traffic_violation tv

После присвоения таблице алиаса, он используется во всех разделах запроса, в котором алиас задан. Например:

WHERE f.violation = tv.violation

Запрос

SELECT  f.name, f.number_plate, f.violation, 
    IF (
        f.sum_fine = tv.sum_fine, "Стандартная сумма штрафа", 
    IF(
        f.sum_fine < tv.sum_fine, "Уменьшенная сумма штрафа", "Увеличенная сумма штрафа"
    )
  ) AS description               
FROM  fine f, traffic_violation tv
WHERE tv.violation = f.violation and f.sum_fine IS NOT Null;

Представление (VIEW)

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

Создавать представления в SQL можно с помощью команды CREATE VIEW.

CREATE VIEW us_customers AS
SELECT customer_id, first_name
FROM Customers
WHERE Country = 'USA';

/* Далее уже */
SELECT *
FROM us_customers;

Можно изменить или обновить существующее представление с помощью команды CREATE OR REPLACE VIEW.

CREATE OR REPLACE VIEW us_customers AS
SELECT *
FROM Customers
WHERE Country = 'USA';

Можно удалить представление с помощью команды DROP VIEW.

DROP VIEW us_customers;

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

Хранимые процедуры

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

Хранимая процедура создается с помощью оператора CREATE PROCEDURE, за которым следует необходимый набор SQL-команд.

/* SQL Server */
CREATE PROCEDURE us_customers AS
SELECT customer_id, first_name
FROM Customers
WHERE Country = 'USA';

/* PostgreSQL */
CREATE PROCEDURE us_customers ()
LANGUAGE SQL
AS $$
SELECT customer_id, first_name
FROM Customers
WHERE Country = 'USA';
$$;

/* MySQL */
DELIMITER //
CREATE PROCEDURE us_customers ()
BEGIN
SELECT customer_id, first_name
FROM Customers
WHERE Country = 'USA';
END //
DELIMITER ;

Теперь, если нам нужно получить всех клиентов, которые живут в США, мы можем просто вызвать хранимую процедуру, которую написали ранее.

/* SQL Server, Oracle */
EXEC us_customers;

/* PostgreSQL, MySQL */
CALL us_customers();

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

/* SQL Server */
CREATE PROCEDURE ctr_customers @ctr VARCHAR(50) AS
SELECT customer_id, first_name
FROM Customers
WHERE Country = @ctr;

/* PostgreSQL */
CREATE PROCEDURE ctr_customers (ctr VARCHAR(50))
LANGUAGE SQL
AS $$
SELECT customer_id, first_name
FROM Customers
WHERE Country = ctr;
$$;

/* MySQL */
DELIMITER //
CREATE PROCEDURE ctr_customers (ctr VARCHAR(50))
BEGIN
SELECT customer_id, first_name
FROM Customers
WHERE Country = ctr;
END //
DELIMITER;

Вызов процедур

/* SQL Server */
EXEC ctr_customers 'UK';

/* PostgreSQL, MySQL */
CALL ctr_customers ('USA');

Мы можем удалить хранимые процедуры с помощью команды DROP PROCEDURE.

DROP PROCEDURE order_details;

Оператор UNION

Оператор UNION используется для объединения двух и более SQL запросов, его синтаксис:

SELECT столбец_1_1, столбец_1_2, ...
FROM 
  ...
UNION
SELECT столбец_2_1, столбец_2_2, ...
FROM 
  ...

Или

SELECT столбец_1_1, столбец_1_2, ...
FROM 
  ...
UNION ALL
SELECT столбец_2_1, столбец_2_2, ...
FROM 
  ...

Важно отметить, что каждый из SELECT должен иметь в своем запросе одинаковое количество столбцов и совместимые типы возвращаемых данных. Каждый запрос может включать разделы WHERE, GROUP BY и пр.

В результате выполнения этой конструкции будет выведена таблица, имена столбцов которой соответствуют именам столбцов в первом запросе. А в таблице результата сначала отображаются записи-результаты первого запроса, а затем второго. Если указано ключевое слово ALL, то в результат включаются все записи запросов, в противном случае - различные (уникальные при пересечении двух выюорок).

Оператор EXISTS

Оператор EXISTS выполняет внешний запрос SQL, если внутренний запрос (подзапрос) не возвращает NULL. Принцип работа оператора EXISTS:

Этот процесс повторяется для каждой строки внешнего запроса.

Рассмотрим пример. Предположим, нам нужно вывести всех клиентов, совершивших заказ. В подзапросе мы проверяем наличие совершенного заказа в клиента (по полю customer_id) и если это подтверждается, то в результате выводим идентификатор и имя клиента.

Запрос

SELECT customer_id, first_name
FROM Customers
WHERE EXISTS (
    SELECT order_id
    FROM Orders
    WHERE Orders.customer_id = Customers.customer_id
);

Мы также можем использовать оператор NOT, чтобы инвертировать работу оператора EXISTS.

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

CREATE TABLE IF NOT EXISTS ...
DROP TABLE IF EXISTS ...

Оператор CREATE DATABASE

Оператор CREATE DATABASE используется для создания базы данных.

CREATE DATABASE my_db;
CREATE DATABASE IF NOT EXISTS my_db;

SHOW DATABASES; /* Посмотреть все БД */
USE my_db; /* Переключиться между БД */

Оператор DROP DATABASE

Инструкция DROP DATABASE используется для удаления базы данных в СУБД. Также убедитесь, что у вас есть права администратора или (права) DROP для запуска этой команды.

DROP DATABASE my_db;

Оператор DROP TABLE

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

DROP TABLE my_table;
DROP TABLE IF EXISTS my_table;

Оператор ALTER TABLE

Мы можем изменить структуру таблицы с помощью команды ALTER TABLE. Мы можем:

• Добавить столбец;
• Переименовать столбец;
• Изменить столбец;
• Удалить столбец;
• Переименовать таблицу.

Мы можем добавить столбцы в таблицу с помощью команды ALTER TABLE с оператором ADD.

ALTER TABLE таблица ADD имя_столбца тип;                     - вставляет столбец после последнего
ALTER TABLE таблица ADD имя_столбца тип FIRST;               - вставляет столбец перед первым
ALTER TABLE таблица ADD имя_столбца тип AFTER имя_столбца_1; - вставляет столбец после укзанного столбца

Мы также можем добавить сразу несколько столбцов в таблицу.

ALTER TABLE Customers
ADD phone varchar(10), age int;

Мы можем переименовать столбцы в таблице с помощью команды ALTER TABLE с оператором RENAME COLUMN или CHANGE.

ALTER TABLE таблица RENAME COLUMN имя_столбца TO новое_имя_столбца;
ALTER TABLE таблица CHANGE имя_столбца новое_имя_столбца ТИП ДАННЫХ;

Мы также можем изменить тип данных столбца с помощью команды ALTER TABLE с оператором MODIFY или ALTER COLUMN.

/* SQL Server */
ALTER TABLE Customers
ALTER COLUMN age VARCHAR(2);

/* MySQL */
ALTER TABLE Customers
MODIFY COLUMN age VARCHAR(2);

/* Oracle */
ALTER TABLE Customers
MODIFY age VARCHAR(2);

/* PostgreSQL */
ALTER TABLE Customers
ALTER COLUMN age TYPE VARCHAR(2);

Мы также можем удалить столбцы в таблице с помощью команды ALTER TABLE с оператором DROP.

ALTER TABLE таблица DROP COLUMN имя_столбца; - удаляет столбец с заданным именем
ALTER TABLE таблица DROP имя_столбца;        - ключевое слово COLUMN не обязательно указывать
ALTER TABLE таблица DROP имя_столбца,
                    DROP имя_столбца_1;      - удаляет два столбца

Мы можем изменить имя таблицы с помощью команды ALTER TABLE с оператором RENAME.

ALTER TABLE Customers
RENAME TO newCustomers;

Оператор BACKUP DATABASE

Важно регулярно создавать резервные копии базы данных, чтобы данные не были потеряны в случае повреждения базы данных. В SQL мы можем создавать резервные копии БД с помощью оператора BACKUP DATABASE.

BACKUP DATABASE orders
TO DISK = 'C:\orders_backup.bak';

Здесь мы создаем файл резервной копии базы данных orders на диске C с именем orders_backup.bak.

Распространено использование расширения .bak для файлов резервных копий БД, однако это не является обязательным.

В SQL мы также можем сделать резервную копию только новых изменений по сравнению с предыдущей резервной копией, используя команду WITH DIFFERENTIAL. Мы добавляем только новые изменения в предыдущий файл резервной копии. Следовательно, эта команда работает быстрее, нежели создание резервной копии БД с нуля.

BACKUP DATABASE orders
TO DISK = 'C:\orders_backup.bak'
WITH DIFFERENTIAL;

Для восстановления файла резервной копии в системе управления базой данных используется оператор RESTORE DATABASE.

RESTORE DATABASE orders
FROM DISK = 'C:\orders_backup.bak';

Переменные, оператор SET

Переменные задаются с помощью ключевого слова SET, перед именем указывается символ @. Переменные можно использовать в запросах, кроме того в запросах их можно менять.

SET @row_num := 0;

SELECT
    *,
    (@row_num := @row_num + 1) AS str_num
FROM
    applicant_order;

Выражение @row_num := @row_num + 1 означает, что для каждой записи, выводимой в запрос, значение переменной @row_num увеличивается на 1. В результате получается нумерация строк запроса.

---

Ограничения в SQL

• Обзор ограничений
• Ограничение NOT NULL
• Ограничение UNIQUE
• Первичный ключ (PRIMARY KEY)
• Внешний ключ (FOREIGN KEY)
• Ограничение CHECK
• Ограничение DEFAULT
• Ограничение CREATE INDEX

Обзор ограничений

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

В SQL используются следующие ограничения:

Эти ограничения также называются ограничениями целостности данных.

Ограничение NOT NULL

Ограничение NOT NULL означает, что столбец не может хранить значения NULL.

CREATE TABLE Colleges (
    college_id INT NOT NULL,
    college_code VARCHAR(20),
    college_name VARCHAR(50)
);

Также можно удалить ограничение NOT NULL для столбца, если оно больше не нужно.

/* SQL Server */
ALTER TABLE Colleges 
ALTER COLUMN college_id INT;

/* Oracle */
ALTER TABLE Colleges 
MODIFY (college_id NULL);

/* MySQL */
ALTER TABLE Colleges 
MODIFY college_id INT;

/* PostgreSQL */
ALTER TABLE Colleges 
ALTER COLUMN college_id DROP NOT NULL;

Можно добавить ограничение NOT NULL к столбцу в существующей таблице с помощью оператора ALTER TABLE.

/* SQL Server */
ALTER TABLE Colleges 
ALTER COLUMN college_id INT NOT NULL;

/* Oracle */
ALTER TABLE Colleges 
MODIFY college_id INT NOT NULL;

/* MySQL */
ALTER TABLE Colleges 
MODIFY COLUMN college_id INT NOT NULL;

/* PostgreSQL */
ALTER TABLE Colleges 
ALTER COLUMN college_id SET NOT NULL;

Ограничение UNIQUE

Ограничение UNIQUE означает, что столбец должен иметь уникальные значения.

CREATE TABLE Colleges (
    college_id INT NOT NULL UNIQUE,
    college_code VARCHAR(20) UNIQUE,
    college_name VARCHAR(50)
);

Также можно добавить ограничение UNIQUE к существующему столбцу с помощью команды ALTER TABLE.

/* Для одного столбца 8?
ALTER TABLE Colleges
ADD UNIQUE (college_id);

/* Для нескольких столбцов */
ALTER TABLE Colleges
ADD UNIQUE UniqueCollege (college_id, college_code);

Если нужно создать индексы для уникальных значений в столбце, следует использовать ограничение CREATE UNIQUE INDEX. Например:

CREATE UNIQUE INDEX college_index
ON Colleges(college_code);

Первичный ключ (PRIMARY KEY)

В SQL ограничение PRIMARY KEY используется для уникальной идентификации строк. Ограничение PRIMARY KEY — это просто комбинация ограничений NOT NULL и UNIQUE. Это означает, что столбец не может содержать повторяющиеся значения, а также значения NULL. В таблице может быть только один первичный ключ.

CREATE TABLE Colleges (
    college_id INT,
    college_code VARCHAR(20) NOT NULL,
    college_name VARCHAR(50),
    CONSTRAINT CollegePK PRIMARY KEY (college_id)
);

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

CREATE TABLE Colleges (
    college_id INT,
    college_code VARCHAR(20),
    college_name VARCHAR(50),
    CONSTRAINT CollegePK PRIMARY KEY (college_id, college_code)
);

Также можно добавить ограничение PRIMARY KEY к столбцу в уже существующей таблице с помощью оператора ALTER TABLE.

/* Для одного столбца */
ALTER TABLE Colleges
ADD PRIMARY KEY (college_id);

/* Для нескольких столбцов */
ALTER TABLE Colleges
ADD CONSTRAINT CollegePK PRIMARY KEY (college_id, college_code);

Обычной практикой является автоматическое увеличение значения первичного ключа при вставке новой строки.

Внешний ключ (FOREIGN KEY)

Внешний ключ (FOREIGN KEY) нужен для того, чтобы связать две разные таблицы между собой. Внешний ключ может ссылаться на любой столбец в родительской таблице. Однако общепринятой практикой является ссылка внешнего ключа на первичный ключ (PRIMARY KEY) родительской таблицы. Таблица может иметь несколько внешних ключей.

/* Эта таблица не имеет внешнего ключа */
CREATE TABLE Customers (
    id INT,
    first_name VARCHAR(40),
    last_name VARCHAR(40),
    age INT,
    country VARCHAR(10),
    CONSTRAINT CustomersPK PRIMARY KEY (id)
);
 
/* Добавляем внешний ключ к полю customer_id */
/* Внешний ключ ссылается на поле id таблицы Customers */
CREATE TABLE Orders (
    order_id INT,
    item VARCHAR(40),
    amount INT,
    customer_id INT REFERENCES Customers(id),
    CONSTRAINT OrdersPK PRIMARY KEY (order_id)
);

Две главные причины использовать внешний ключ:

• Нормализация данных. FOREIGN KEY помогает нормализовать данные в нескольких таблицах и уменьшить избыточность. Это означает, что в базе данных может быть несколько таблиц, связанных друг с другом.
• Предотвращение вставки некорректных данных. Если две таблицы в базе данных связаны через поле (атрибут), использование FOREIGN KEY гарантирует, что в это поле не будут вставлены неверные данные. Это помогает устранить ошибки на уровне базы данных.

Ограничение CHECK

Ограничение CHECK используется для указания условия, которое должно быть выполнено для вставки значения в таблицу. Ограничение CHECK используется для проверки данных только при вставке.

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    amount INT CHECK (amount > 0)
);

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

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    amount INT,
    CONSTRAINT amountCK CHECK (amount > 0)
);

Ограничение DEFAULT

Ограничение DEFAULT используется для установки значений по умолчанию при попытке вставить пустое (NULL) значение в столбец.

CREATE TABLE Colleges (
    college_id INT PRIMARY KEY,
    college_code VARCHAR(20),
    college_country VARCHAR(20) DEFAULT 'Japan'
);

Ограничение CREATE INDEX

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

Индекс — это объект базы данных, создаваемый с целью повышения производительности поиска данных. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра таблицы строка за строкой может занимать много времени. Словно закладка в книге, индекс помогает быстро получить доступ к требуемым данным в таблице, согласно SQL-запросу. Таким образом, использование индексов позволяет ускорить получение данных. Увидеть разницу в скорости при малом количестве данных в таблице проблематично. Однако при большом количестве данных можно легко заметить разницу в скорости между использованием индексов и без них.

CREATE TABLE Colleges (
    college_id INT PRIMARY KEY,
    college_code VARCHAR(20) NOT NULL,
    college_name VARCHAR(50)
);

CREATE INDEX college_index
ON Colleges(college_code);

Если нужно создать индексы для уникальных значений в столбце, следует использовать ограничение CREATE UNIQUE INDEX.

CREATE UNIQUE INDEX college_index
ON Colleges(college_code);