Групування записів

Створимо ще одну таблицюorders, у якій зберігається інформація про замовлення: nz – номер замовлення, date – дата замовлення, nk – номер клієнта, nt – номер товару, quantity- кількістьодиниць замовленого товару, amount – вартість замовлення.

CREATE TABLE orders
( nz INT NOT NULL,
date DATE NOT NULL,
nk INT NOT NULL,
nt INT NOT NULL,
quantity DOUBLE(9,2) NOT NULL,
amount DOUBLE(9,2) NOT NULL );

Якщо в різних таблицях є поля з однаковими іменами, то в багатотабличних запитах перед ім'ям поля потрібно вказувати ім'я таблиці, наприклад, cliеnts.nk, orders.nk.

Оператор SELECT дозволяє групувати значення в запитах. Наприклад, клієнт Козлов (nk=1) кілька разів замовляв якийсь товар. Виходить, його номер зустрічається в таблиці orders кілька разів. Інший клієнт також міг зробити кілька замовлень. Ми можемо згрупувати всі записи по полю nk (номер клієнта), а потім вивести суму замовлення кожного клієнта. Псевдонім стовпця для загальної суми замовлення – totalsum.

SELECT clients.fio, SUM(orders.amount) AS totalsum
FROM clients, orders
WHERE clients.nk = orders. nk
GROUP BY orders.nk;

Групування виконує оператор GROUP BY, що є частиною оператора SELECT. Оператор GROUP BY можна обмежити за допомогою HAVING. HAVING можна вважати аналогом WHERE, але тільки для GROUP BY:

HAVING <умова>

Наприклад, нас цікавлять тільки клієнти, які замовили товарів на загальну суму, що перевищує 1500:

SELECT clients.fio, SUM(ORDERS.AMOUNT) AS totalsum
FROM clients, orders
WHERE clients.nk = orders.nk
GROUP BY orders.nk
HAVING totalsum > 1500;

Ключі

Припустимо, що хтось додав у таблицю clients запис:

2 Волков В.В. Волі 7 Донецьк 0112233

У той же час, до цього номер 2 був закріплений за Барановим. Номер клієнта повинен бути унікальним. Щоб уникнути такої плутанини, необхідно оголосити поле nk як первинний ключ:

ALTER TABLE clients ADD PRIMARY KEY (nk);

Як первинний ключ не можна використовувати поле, що допускає значення NULL. Створити первинний ключ можна й простіше - при побудові таблиці в такий спосіб:

CREATE TABLE clients
( nk int NOT NULL,
fio varchar(50) NOT NULL,
addr varchar(55) NOT NULL,
city varchar(20) NOT NULL,
phone varchar(8) NOT NULL,
gr INT NOT NULL,
PRIMARY KEY (nk) );

Таблиця orders містить відомості про замовлення. По полю nk у цій таблиці ідентифікується замовник. Припустимо, що в таблицю orders хтось увів значення, якого немає в таблиці clients. Щоб не допустити подібної ситуації, варто використовувати запит для створення зовнішнього ключа:

ALTER TABLE orders ADD FOREIGN KEY(nk) REFERENCES clients;

Такий зв'язок називається декларативною цілісністю бази даних. Команда ALTER використовується не тільки для додавання ключів, але й для реорганізації таблиці в цілому. Наприклад, можна додати в таблицю нове поле email:

ALTER TABLE clients ADD email VARCHAR(20) NULL;

Зверніть увагу, що ви не можете додати нове поле зі значенням NOT NULL у таблицю, у якій уже є дані.

За допомогою ALTER можна ввести список обмежень на значення, що вводяться. Наприклад, якщо компанія працює тільки із клієнтами Києва, Донецька і Запоріжжя, то доцільно ввести список припустимих значень для таблиці clients:

ALTER TABLE clients
ADD CONSTRAINT INVALID_STATE SHECK (city IN ('Київ', 'Донецьк', 'Запоріжжя' ));