6. Поглиблене вивчення мови SQL

6.1. Introduction

У цьому розділі ми розглянемо

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

Для ілюстрації різних команд ми будемо працювати з наступними таблицями, що використовуються для управління замовленнями в системі PME з розповсюдження книг:

6.1.1. таблиця CLIENTS

Вона зберігає інформацію про клієнтів системи PME:

`ID`	унікальний ідентифікатор клієнта — первинний ключ
`NOM`	ім'я клієнта
`STATUT`	I = Фізична особа, E = Підприємство, A = Державна установа
`PRENOM`	ім'я у разі фізичної особи
`CONTACT`	Прізвище контактної особи у клієнта (у разі підприємства або адміністрації)
`RUE`	Адреса клієнта — вулиця
`VILLE`	місто
`CPOSTAL`	поштовий індекс
`TELEPH`	Телефон
`DEPUIS`	З якого часу є клієнтом?
`DEBITEUR`	O (Так), якщо клієнт має заборгованість перед компанією, і N (Ні) в іншому випадку.

6.1.2. таблиця ARTICLES

У ній зберігається інформація про продані товари, в даному випадку книги. Її структура така:

`ISBN`	номер, що однозначно ідентифікує книгу (ISBN = Міжнародний стандартний номер книги) — первинний ключ
`TITRE`	Назва книги
`CODEDITEUR`	Код, що однозначно ідентифікує видавця
`AUTEUR`	Прізвище автора
`RESUME`	Анотація книги
`QTEANCOUR`	Кількість, продана за рік
`QTEANPREC`	Кількість, продана минулого року
`DERNVENTE`	Дата останнього продажу
`QTERECUE`	Кількість останньої поставки
`DERNLIV`	Дата останньої поставки
`PRIXVENTE`	Ціна продажу
`COUT`	Вартість закупівлі
`MINCDE`	Мінімальна кількість для замовлення
`MINSTOCK`	Мінімальний поріг запасів
`QTESTOCK`	Кількість на складі

Його вміст може бути таким:

6.1.3. таблиця COMMANDES

У ній зберігається інформація про замовлення, зроблені клієнтами. Її структура така:

`NOCMD`	Номер, що однозначно ідентифікує замовлення — первинний ключ
`IDCLI`	Номер клієнта, який оформив це замовлення — зовнішній ключ — артикул CLIENTS(ID)
`DATE_CMD`	Дата внесення цього замовлення
`ANNULE`	O (Так), якщо замовлення було скасовано, і N (Ні) в іншому випадку.

6.1.4. таблиця DETAILS

Вона містить детальну інформацію про замовлення, а саме номери та кількість замовлених книг. Її структура така:

`NOCMD`	Номер замовлення — зовнішній ключ, що посилається на стовпець NOCMD таблиці COMMANDES
`ISBN`	Номер замовленої книги — зовнішній ключ, що посилається на стовпець ISBN таблиці LIVRES
`QTE`	Замовлена кількість

Його вміст може бути таким:

З наведеного вище бачимо, що замовлення № 3 (NOCMD) стосується трьох книг. Це означає, що клієнт замовив три книги одночасно. Дані про цього клієнта можна знайти в таблиці [COMMANDES], де видно, що замовлення № 3 було зроблено клієнтом № 5. З таблиці [CLIENTS] ми дізнаємося, що клієнт № 5 — це компанія NetLogos із міста Сегре.

6.2. Замовлення SELECT

Тут ми пропонуємо поглибити наші знання про замовлення SELECT, представивши нові синтаксичні варіанти цього замовлення.

6.2.1. Синтаксис запиту до декількох таблиць

syntaxe

SELECT стовпець1, стовпець2, ...

FROM таблиця1, таблиця2, ..., таблицяp

WHERE condition

ORDER BY ...

action

Новизна тут полягає в тому, що стовпці стовпець1, стовпець2, ... походять з кількох таблиць таблиця1, таблиця2, ... Якщо дві таблиці мають стовпці з однаковими назвами, неоднозначність усувається за допомогою позначення tablei.colonnej. condition може стосуватися стовпців різних таблиць.

Принцип роботи

`1`	Створюється декартова таблиця з table1, table2, ..., tablep. Якщо ni — це кількість рядків у tablei, то побудована таблиця матиме n1n2...np* рядків, що містять усі стовпці різних таблиць.
`2`	До цієї таблиці застосовується condition з WHERE. Таким чином створюється нова таблиця
`3`	Вона впорядкована відповідно до режиму, зазначеного в ORDER.
`4`	Відображаються стовпці, запитувані у файлі SELECT.

Приклади

Використовуємо таблиці, наведені вище. Хочемо дізнатися деталі замовлень, зроблених після 25 вересня:

SQL>select details.nocmd,isbn,qte from commandes,details
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd

Зверніть увагу, що після FROM вказується назва всіх таблиць, стовпці яких використовуються в запиті. У наведеному вище прикладі всі вибрані стовпці належать до таблиці DETAILS. Однак умова посилається на таблицю COMMANDES. Тому необхідно вказати назву останньої таблиці після FROM. Операція, яка перевіряє рівність стовпців двох різних таблиць, часто називається рівним з’єднанням.

Запит SELECT можна було б також записати таким чином:

SQL> select details.nocmd,isbn,qte from commandes
    inner join details on details.nocmd=commandes.nocmd
    where commandes.datecmd>'25-sep-91'

Продовжимо наші приклади. Нам потрібен той самий результат, що й раніше, але з назвою замовленої книги, а не її номером ISBN:

SQL>select commandes.nocmd, articles.titre, details.qte
  from commandes,articles,details
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd
  and details.isbn=articles.isbn

Такий самий результат отримується за допомогою наступного запиту SQL, який є менш зрозумілим:

SQL> select details.nocmd,articles.titre,details.qte from details
    inner join commandes on details.nocmd=commandes.nocmd
    inner join articles on  details.isbn=articles.isbn
    where commandes.datecmd>'25-sep-91'

Вище наведено два внутрішні з'єднання з таблицею [DETAILS]:

одна — з таблицею [COMMANDES] для отримання доступу до дати замовлення книги
одна — з таблицею [ARTICLES], щоб отримати доступ до назви замовленої книги

Крім того, нам потрібне ім’я клієнта, який робить замовлення:

SQL>select commandes.nocmd, articles.titre, qte ,clients.nom
  from commandes,details,articles,clients
  where commandes.datecmd>'25-sep-91'
  and details.nocmd=commandes.nocmd
  and details.isbn=articles.isbn
  and commandes.idcli=clients.id

Крім того, потрібні дати замовлень та їх відображення у порядку спадання за цими датами:

SQL>select commandes.nocmd, commandes.datecmd, articles.titre, qte ,clients.nom
    from commandes,details,articles,clients
    where commandes.datecmd>'25-sep-91'
    and details.nocmd=commandes.nocmd
    and details.isbn=articles.isbn
    and commandes.idcli=clients.id
    order by commandes.datecmd descending

Ось кілька правил, яких слід дотримуватися при з'єднанні:

Після SELECT вказуються стовпці, які потрібно відобразити. Якщо стовпець існує в різних таблицях, перед ним вказується назва таблиці.
Після FROM вказуються всі таблиці, які будуть оброблені за допомогою SELECT, тобто таблиці, до яких належать стовпці, що знаходяться після SELECT та WHERE.

6.2.2. Автоматичне з'єднання

Ми хочемо дізнатися, які книги мають ціну, вищу за ціну книги «Using SQL»:

SQL>select a.titre from articles a, articles b
  where b.titre='Using SQL'
  and a.prixvente>b.prixvente

Обидві таблиці в з'єднанні тут однакові: таблиця articles. Щоб їх розрізнити, їм присвоюють псевдоніми: from articles a, articles b. Псевдонім першої таблиці називається a, а другої — b. Цю синтаксичну конструкцію можна використовувати навіть у разі, якщо таблиці різні. При використанні псевдоніма його слід застосовувати в усьому запиті SELECT замість назви таблиці, яку він позначає.

6.2.3. Зовнішнє з'єднання

Ми хочемо дізнатися, які клієнти придбали щось у вересні, із зазначенням дати замовлення. Інші клієнти відображаються без цієї дати:

SQL>select clients.nom,commandes.datecmd from clients
    left outer join commandes  on clients.id=commandes.idcli
    where datecmd between '01-sep-91' and '30-sep-91'

Дивно, що тут ми не отримуємо правильного результату. У таблиці [CLIENTS] мали б бути всі клієнти, але це не так. Якщо замислитися над принципом роботи зовнішнього з’єднання, стає зрозуміло, що клієнти, які не зробили покупку, були пов’язані з порожнім рядком таблиці COMMANDES і, отже, з порожньою датою (значення NULL у термінології SQL). Ця дата не відповідає заданій умові щодо дати, і відповідний клієнт не відображається. Спробуємо інший варіант:

SQL>select clients.nom,commandes.datecmd from clients
    left outer join commandes  on clients.id=commandes.idcli
    where (commandes.datecmd between '01-sep-91' and '30-sep-91')
        or (commandes.datecmd is null)

Цього разу ми отримуємо правильну відповідь на наше запитання.

6.2.4. Вкладені запити

syntaxe

SELECT стовпець[s] FROM таблиця[s]

WHERE вираз оператор запит

ORDER BY ...

fonctionnement

requête — це оператор SELECT, який повертає групу з 0, 1 або декількох значень. У цьому випадку маємо умову WHERE типу

вираз оператор (val1, val2, ..., vali)

expression та vali повинні бути одного типу. Якщо запит повертає лише одне значення, ми повертаємося до умови типу

вираз оператор значення

, яке нам добре знайоме. Якщо запит повертає список значень, можна використовувати такі оператори:

IN

expression IN (val1, val2, ..., vali): істинне, якщо значення expression є одним із елементів списку vali.

NOT IN

обернена до IN

ANY

повинно передувати =, !=, >, >=, <, <=

expression >= ANY (val1, val2, .., valn): істинне, якщо expression >= одному зі значень vali зі списку

ALL

повинно передувати =, !=, >, >=, <, <=

expression >= ALL (val1, val2, .., valn): істинне, якщо вираз >= усім значенням vali зі списку

EXISTS

запит: істинний, якщо requête повертає принаймні один рядок.

Приклади

Повернемося до питання, яке вже було вирішено за допомогою рівного з'єднання: відобразити назви, ціна яких перевищує ціну книги «Using SQL».

SQL>select titre from ARTICLES
    where prixvente > (select prixvente from ARTICLES where titre='Using SQL')

Це рішення здається інтуїтивнішим, ніж рішення з рівним з’єднанням. Спочатку виконується фільтрація за SELECT, а потім — друга фільтрація за отриманим результатом. Таким чином можна виконувати кілька фільтрацій поспіль.

Нам потрібно визначити назви, ціна продажу яких перевищує середню ціну продажу:

SQL> select titre from ARTICLES
    where prixvente > (select avg(prixvente) from ARTICLES)

Які клієнти замовили позиції, отримані в результаті попереднього запиту?

SQL>select distinct idcli from COMMANDES,DETAILS
    where DETAILS.isbn in
    (select isbn from ARTICLES where prixvente
        > (select avg(prixvente) from ARTICLES))
    and COMMANDES.nocmd=DETAILS.nocmd

Пояснення

у таблиці DETAILS відбираються коди ISBN, що містяться серед книг, ціна яких перевищує середню ціну книг.
у рядках, відібраних на попередньому етапі, відсутній код клієнта IDCLI. Він міститься в таблиці COMMANDES. Зв’язок між двома таблицями здійснюється за номером замовлення NOCMD, звідки випливає рівне з’єднання COMMANDES.nocmd=DETAILS.nocmd.
Один і той самий клієнт міг придбати одну з відповідних книг кілька разів, і в цьому випадку його код IDCLI зустрічатиметься кілька разів. Щоб уникнути цього, ключове слово DISTINCT розміщується після SELECT. DISTINCT загалом усуває дублікати в рядках, отриманих у результаті SELECT.
Щоб отримати ім’я клієнта, нам потрібно виконати додаткове рівне з’єднання між таблицями COMMANDES та CLIENTS, як показано в наступному запиті.

SQL> select distinct CLIENTS.nom from COMMANDES,DETAILS,CLIENTS
    where DETAILS.isbn in
    (select isbn from ARTICLES where prixvente
        > (select avg(prixvente) from ARTICLES))
    and COMMANDES.nocmd=DETAILS.nocmd
    and COMMANDES.IDCLI=CLIENTS.ID

Знайти клієнтів, які не робили замовлень з 24 вересня:

SQL>select nom from CLIENTS
    where clients.id not in
    (select distinct commandes.idcli from commandes where datecmd>='24-sep-91')

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

Так само, як і для клаузули WHERE, синтаксис


     HAVING expression opérateur requête

, з уже зазначеним обмеженням, що expression має бути одним із виразів expri у клаузулі


     GROUP BY expr1, expr2, ...

Приклади

Які обсяги продажів книг перевищують 200F?

Спочатку відобразимо кількість проданих примірників за кожним виданням:

SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    group by titre

Тепер відфільтруємо позиції:

SQL> select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    group by titre
    having titre in (select titre from ARTICLES where prixvente>200)

Можливо, було б зрозуміліше написати так:

SQL>select ARTICLES.titre,sum(qte) QTE from ARTICLES, DETAILS
    where DETAILS.isbn=ARTICLES.isbn
    and ARTICLES.prixvente>200
    group by titre

6.2.5. Взаємопов'язані запити

У випадку вкладених запитів існує батьківський запит (найзовнішній запит) та дочірній запит (найвнутрішній запит). Батьківський запит обчислюється лише після повного обчислення дочірнього запиту.

Пов’язані запити мають однаковий синтаксис із таким невеликим відхиленням: дочірній запит виконує з’єднання з таблицею батьківського запиту. У цьому випадку сукупність «батьківський запит — дочірній запит» обчислюється повторно для кожного рядка батьківської таблиці.

Приклад

Повернемося до прикладу, де нам потрібні імена клієнтів, які не робили замовлень з 24 вересня:

SQL> 
select nom from clients
    where not exists
        (select idcli from commandes
            where datecmd>='24-sep-91'
                and commandes.idcli=clients.id)

Базовий запит виконується над таблицею clients. Дочірній запит виконує з'єднання таблиць clients та commandes. Отже, маємо кореляційний запит. Для кожного рядка таблиці clients виконується дочірній запит: він шукає код клієнта id у замовленнях, зроблених після 24 вересня. Якщо його не знайде (not exists), відображається ім’я клієнта. Потім переходимо до наступного рядка таблиці clients.

6.2.6. Критерії вибору для запису SELECT

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

З'єднання

SQL>
select distinct nom from clients,commandes
    where clients.id=commandes.idcli

Вкладені запити

SQL> 
select nom from clients
    where id in (select idcli from commandes)

дають однаковий результат.

Взаємопов’язані запити

SQL>
select nom from clients
    where exists (select * from commandes where commandes.idcli=clients.id)

дають однаковий результат.

Автори Крістіан MAREE та Гай LEDANT у своїй книзі «SQL: Вступ, програмування та майстерність» пропонують кілька критеріїв вибору:

Продуктивність

Користувач не знає, як SGBD «впорається» з пошуком результатів, які він запитує. Тому лише на власному досвіді він зможе виявити, що певний спосіб запису є ефективнішим за інший. MAREE та LEDANT на основі свого досвіду стверджують, що кореляційні запити, як правило, виконуються повільніше, ніж вкладені запити або з’єднання.

Формулювання

Формулювання за допомогою вкладених запитів часто є більш зрозумілим та інтуїтивним, ніж з’єднання. Однак воно не завжди є придатним для використання. Слід звернути увагу, зокрема, на два моменти:

Таблиці, до яких належать стовпці-аргументи запиту SELECT (SELECT col1, col2, ...), повинні вказуватися після ключового слова FROM. У цьому випадку виконується декартовий добуток цих таблиць, що називається з’єднанням.

Коли запит відображає результати з однієї таблиці, а фільтрування рядків цієї таблиці вимагає звернення до іншої таблиці, можна використовувати вкладені запити.

6.3. Розширення синтаксису

З міркувань зручності ми найчастіше наводили скорочені синтаксиси різних команд. У цьому розділі ми наводимо їхні розширені синтаксиси. Вони зрозумілі самі по собі, оскільки аналогічні синтаксисам команди SELECT, яку ми детально розглядали.

INSERT

`syntaxe1`	INSERT INTO table (col1, col2, ..) VALUES (val1, val2, ...)
`syntaxe2`	INSERT INTO table (col1, col2, ..) (requête)
`explication`	Ці дві синтаксичні конструкції були представлені

DELETE

`syntaxe1`	DELETE FROM table WHERE condition
`explication`	Цей синтаксис відомий. Додамо, що умова може містити запит із синтаксисом WHERE вираз оператор (запит)

UPDATE

`syntaxe1`	UPDATE table SET col1=expr1, col2=expr2, ... WHERE condition
`explication`	Цей синтаксис уже було розглянуто. Додамо, що умова може містити запит із синтаксисом WHERE вираз оператор (запит)

`syntaxe2`	UPDATE table SET (col1, col2, ..) = запит1, (cola, colb, ..) = запит2, ... WHERE condition
`explication`	Значення, що присвоюються різним стовпцям, можуть походити із запиту.