SQL — DDL и DML

Раздаточный материал Смотреть урок

Словарь

English	Русский
SQL/ˌes kjuː ˈel/	SQL
Data Definition Language/ˈdeɪtə ˌdefɪˈnɪʃn ˈlæŋɡwɪdʒ/	Язык определения данных
Data Manipulation Language/ˈdeɪtə məˌnɪpjʊˈleɪʃn ˈlæŋɡwɪdʒ/	Язык манипулирования данными
INNER JOIN/ˈɪnə dʒɔɪn/	INNER JOIN
aggregate functions/ˈæɡrɪɡeɪt ˈfʌŋkʃnz/	агрегатные функции

DDL: создание структуры

CREATE DATABASE Shop;

CREATE TABLE CUSTOMER (
  CustomerID INTEGER,
  Name VARCHAR(50),
  Town VARCHAR(30),
  Joined DATE,
  Active BOOLEAN,
  PRIMARY KEY (CustomerID)
);

ALTER TABLE CUSTOMER ADD Email VARCHAR(100);

Типы данных по учебной программе: CHARACTER (фиксированное количество символов), VARCHAR(n) (до n символов), BOOLEAN, INTEGER, REAL, DATE, TIME.
PRIMARY KEY (field) называет ключ; ALTER TABLE … ADD добавляет атрибут к существующей таблице.

Разобранный пример: две таблицы с внешним ключом

Example

Напишите SQL для создания таблицы ORDERS, где OrderID является первичным ключом, CustomerID — внешним ключом, ссылающимся на CUSTOMER, и добавьте столбец OrderDate.

CREATE TABLE ORDERS (
  OrderID INTEGER,
  CustomerID INTEGER,
  OrderDate DATE,
  PRIMARY KEY (OrderID),
  FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID)
);

Баллы: CREATE TABLE с именем; каждый атрибут с подходящим типом; PRIMARY KEY; FOREIGN KEY … REFERENCES, указывающий имя таблицы и ее поля.

DML: запрос данных с помощью SELECT

SELECT Name, Town
FROM CUSTOMER
WHERE Town = 'London'
ORDER BY Name ASC;

SELECT перечисляет поля для вывода (* для всех), FROM называет таблицу, WHERE оставляет только строки, удовлетворяющие условию, ORDER BY сортирует результат, ASC или DESC.
Строки заключаются в одинарные кавычки; числа — нет. Операции сравнения: =, <, >, <=, >=, <>; условия объединяются операторами AND, OR, NOT; LIKE 'A%' совпадает с текстом, начинающимся с A; BETWEEN 10 AND 20 задает диапазон.

Запрос SELECT возвращает только строки, соответствующие условию

Только строки, прошедшие проверку WHERE, попадают в результат

Разобранный пример: напишите запрос

Example

Напишите SQL-скрипт для вывода имен и адресов электронной почты всех активных клиентов в Манчестере, в алфавитном порядке по имени.

SELECT Name, Email
FROM CUSTOMER
WHERE Town = 'Manchester' AND Active = TRUE
ORDER BY Name;

Один балл за каждое: правильные поля после SELECT; правильная таблица после FROM; WHERE с обоими условиями и строкой в одинарных кавычках; ORDER BY Name. Используйте точно те имена таблиц и полей, которые даны в вопросе.

Две таблицы: INNER JOIN

SELECT CUSTOMER.Name, ORDERS.OrderDate
FROM CUSTOMER INNER JOIN ORDERS
  ON CUSTOMER.CustomerID = ORDERS.CustomerID
WHERE ORDERS.OrderDate >= '2024-01-01';

INNER JOIN объединяет строки двух таблиц, где внешний ключ в одной совпадает с первичным ключом в другой, указанными в klauze ON.
Префиксы поля таблицей, если то же имя встречается в обеих. В программе требуется выполнение запросов не более чем к двум таблицам.

Оператор JOIN сопоставляет ключ каждого заказа с соответствующей строкой клиента и «сшивает» две таблицы в единый объединённый набор результатов.

Агрегаты и GROUP BY

SELECT CustomerID, COUNT(*) AS NumOrders
FROM ORDERS
GROUP BY CustomerID;

SELECT AVG(Price) FROM PRODUCT;
SELECT SUM(Quantity) FROM ORDER_LINE WHERE OrderID = 1042;

Агрегатные функции сводят множество строк к одному значению: COUNT количество строк, SUM сумма, AVG среднее значение.
GROUP BY создаёт одну итоговую строку для каждого значения поля: количество заказов для каждого клиента. Без него агрегатная функция обобщает всю таблицу.

Изменение данных: INSERT, UPDATE, DELETE

INSERT INTO CUSTOMER (CustomerID, Name, Town, Joined, Active)
VALUES (101, 'Ada Lovelace', 'London', '2024-03-01', TRUE);

UPDATE CUSTOMER SET Town = 'Bristol' WHERE CustomerID = 101;

DELETE FROM CUSTOMER WHERE CustomerID = 101;

INSERT INTO … VALUES добавляет строку: перечислите поля, затем значения в том же порядке. UPDATE … SET … WHERE изменяет совпадающие строки. DELETE FROM … WHERE удаляет их.
Всегда указывайте для UPDATE и DELETE условие WHERE, иначе изменения затронут все строки в таблице.

Разбор примера: прочитайте оператор

Example

SELECT Name
FROM CUSTOMER INNER JOIN ORDERS
  ON CUSTOMER.CustomerID = ORDERS.CustomerID
WHERE OrderDate = '2024-05-01'
ORDER BY Name DESC;

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

Следующий →

Полные конспекты для повторения: Базы данных