Рідкісний SQL

Вступна
Коли часто стикаєшся з будь-якою технологією, мовою програмування, стандартом, формується певна картина їх можливостей, межі, в яких вони використовуються. Так може тривати досить довго, поки на очі не трапляються приклади, які розширюють затверділі горизонти знання. Сьогодні я хотів би розповісти про таких прикладах і продемонструвати їх для мови SQL. Цікаві і рідкісні конструкції, забуті вираження, незвичні прийоми чекають Вас в цій статті. Кого зацікавив, ласкаво просимо під кат.

Нюанси
Мене часто запитують, а для кого ця стаття? Але, повірте, не завжди легко дати відповісти: з одного боку, є ніндзя розробники, яких важко чимось здивувати, а з іншого — молоді падаваны. Але одне точно можу сказати — для читача, якого цікавить SQL, який здатний доповнювати свою багату картину дрібними, але дуже цікавими деталями. У даній статті не буде кілометрових сторінок sql-запиту, максимум 1, 2 рядки і тільки те, що зустрічається на мій погляд рідко. Але так як я хочу бути до кінця відвертим, якщо Ви з sql на ти, стаття здасться нудною. Всі приклади в статті, за винятком першого і четвертого можна віднести до стандарту SQL-92.

Дані
Для того, щоб спростити нам життя, я накидав просту табличку з даними, на якій будуть випробувані ті чи інші моменти і для стислості, я буду приводити результат експерименту над ними. Всі запити я перевіряю на PostgreSql. Скрипти і таблиця з даними
CREATE TABLE goods(
id bigint NOT NULL,
name character varying(127) NOT NULL,
description character varying(255) NOT NULL,
price numeric(16,2) NOT NULL,
articul character varying(20) NOT NULL,
act_time timestamp NOT NULL,
availability boolean NOT NULL,
CONSTRAINT pk_goods PRIMARY KEY (id));

INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (1, 'Тапочки', 'М'які', 100.00, 'TR-75', {ts '2017-01-01 01:01:01.01'}, TRUE);
INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (2, 'Подушка', 'Білий', 200.00, 'PR-75', {ts '2017-01-02 02:02:02.02'}, TRUE);
INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (3, 'Ковдру', 'Пухова', 300.00, 'ZR-75', {ts '2017-01-03 03:03:03.03'}, TRUE);
INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (4, 'Наволочка', 'Сірий', 400.00, 'AR-75', {ts '2017-01-04 04:04:04.04'}, FALSE);
INSERT INTO goods (id, name, description, price, articul, act_time, availability) VALUES (5, 'Простынка', 'Шовкова', 500.00, 'BR-75', {ts '2017-01-05 05:05:05.05'}, FALSE);

id name description price articul act_time availability
1 Тапочки М'які 100.00 TR-75 2017-01-01 01:01:01.01 true
2 Подушка Біла 200.00 PR-75 2017-01-02 02:02:02.02 true
3 Ковдру Пухова 300.00 ZR-75 2017-01-03 03:03:03.03 true
4 Наволочка Сірий 400.00 AR-75 2017-01-04 04:04:04.04 false
5 Простынка Шовкова 500.00 BR-75 2017-01-05 05:05:05.05 false


Запити

1. Подвійні лапки

І перше, що в мене є — це просте запитання: Змогли б Ви навести приклад sql запиту з використанням подвійних лапок? Так, не з одинарними, подвійними?
Приклад з подвійними лапками
SELECT name "Ім'я товару" FROM goods
Ім'я товару
Тапочки
Подушка
Ковдру
Наволочка
Простынка

Я був дуже здивований, коли побачив це в перший раз. Якщо спробувати змінити подвійні лапки на одинарні, результат буде абсолютно інший!
Приклад з одинарними лапками
SELECT name 'дані' FROM goods WHERE id = 1

name
Це дані

Може здатися, що це не дуже корисний приклад для реальної розробки. Для мене це не так. Тепер я його активно використовую у всіх своїх sql-заготовках. Суть проста, коли повертаєшся через пів року sql-запиту з 40 колонок, ой як рятує 'нашенський' їх назва. Не дивлячись, що я не вказав про SQL-92, в останній редакції згадка про подвійні лапки є.

2. Псевдо таблиця. SQL-92

Трохи не точно, з точки зору термінології, але суть проста — таблиця виходить в результаті подзапроса в секції FROM. Мабуть найвідоміший факт у цій статті
Псевдо таблиця
SELECT mock.nickname "Прізвисько", (CASE WHEN mock.huff THEN 'Так' ELSE 'Немає' END) "Ображається?" FROM (SELECT name AS nickname, availability AS huff FROM goods) mock
Прізвисько Ображається?
Тапочки Так
Подушка Так
Ковдру Так
Наволочка Немає
Простынка Немає
У нашому прикладі mock — це псевдо таблиця (іноді називають віртуальною таблицею). Природно, вони призначені зовсім не для того, щоб перебрехати істинний сенс. Приклад такої.

3. Конструктор блоку даних. SQL-92

Звучить страшно, просто з-за того, що я не знайшов хорошого перекладу або тлумачення. І як завжди на прикладі легше пояснити:Приклад конструктора блоку даних
SELECT name "Ім'я товару", price "Ціна" FROM (VALUES ('Тапочки', 100.00), ('Подушка', 200.00)) AS goods(name, price)
Назва товару Ціна
Тапочки 100.00
Подушка 200.00
В секції FROM використовується ключове слово VALUES, за яким в дужках дані, рядок за рядком. Суть в тому, що ми взагалі не вибираємо дані з якоїсь таблиці, а просто створюємо їх нальоту, 'називаємо' таблицею, іменуємо колонки і далі використовуємо на свій розсуд. Ця штука виявилася дуже корисною при тестуванні різних кейсів sql-запиту, коли даних для деяких таблиць немає (у Вашій локальній БД), а писати insert лінь або іноді дуже складно, зважаючи пов'язаності таблиць і обмежень.

4. Час, Дата і Час-і-Дата

Напевно кожен стикався у запитах, з необхідністю зазначення часу, дати або дати і часу. У багатьох СУБД підтримуються літерали t, d та ts відповідно для роботи з цими типами. Але простіше пояснити на прикладі:Приклад з литералом ts
SELECT name "Ім'я товару", act_time "Точний час" FROM WHERE goods act_time = {ts '2017-01-01 01:01:01.01'}
Назва товару Точний час
Тапочки 2017-01-01 01:01:01.01
Для літералів d і t все аналогічно.
Прошу пробачення у читача, що ввів в оману, але все що сказано в пункті 4, не відноситься до мови SQL, а відноситься до можливостей попередньої обробки запитів у JDBC.

5. Заперечення. SQL-92

Всі ми знаємо про оператор NOT, але дуже часто забувають, що його можна застосовувати як до групи предикатів так і одиночної колонці:
Приклад з запереченням
SELECT id, name, availability FROM WHERE goods NOT availability
-- чи так
SELECT id, name FROM WHERE goods NOT (id = 1 OR id = 2 OR id = 3)
id name availability
4 Наволочка false
5 Простынка false


6. Порівняння блоків даних. SQL-92

В черговий раз перепрошую за термінологію. Це один з моїх улюблених прикладівПриклад порівняння блоків даних
SELECT * FROM goods WHERE name, price, availability) = ('Наволочка', 400.00, FALSE)
-- або його аналог
SELECT * FROM goods WHERE name = 'Наволочка' AND price = 400.00 AND availability = FALSE
id name description price articul act_time availability
4 Наволочка Сірий 400.00 AR-75 2017-01-04 04:04:04.04 false
Як видно з прикладу, порівняння блоків даних аналогічно порівнянні поелементно значение_1_block_1 = значение_1_block_2, значение_2_block_1 = значение_2_block_2, значение_3_block_1 = значение_3_block_2 з використанням AND між ними.

7. Оператори порівняння з модифікаторами ANY, SOME або ALL. SQL-92

Ось тут необхідне пояснення. Але як завжди, спочатку прикладПриклад порівняння c ALL
SELECT id, name FROM goods WHERE id > ALL (SELECT id FROM WHERE goods availability)
id name
4 Наволочка
5 Простынка
Що означає ALL в даному випадку? А означає вона те, що умові вибірки задовольняють тільки ті рядки, ідентифікатори яких (в нашому випадку це 4 і 5), більше будь-якого із знайдених значень підзапитів (1, 2 і 3). 4 більше ніж 1 та 2 і 3. 5 аналогічно. Що буде, якщо ми замінимо ALL ANY?
Приклад порівняння c ANY
SELECT id, name FROM goods WHERE id > ANY (SELECT id FROM WHERE goods availability)
id name
2 Подушка
3 Ковдру
4 Наволочка
5 Простынка
Що означає ANY в даному випадку? А означає вона те, що умові вибірки задовольняють тільки ті рядки, ідентифікатори яких (в нашому випадку це 2, 3, 4 і 5), більше хоча б одного із знайдених значень підзапитів (1, 2 і 3). Для себе я асоціював ALL з AND, ANY з OR. SOME та ANY аналоги між собою.

8. Оператори роботи з запитами/під запитами. SQL-92

Достатньо відомо, що можна об'єднати 2 запиту між собою за допомогою операторів UNION або UNION ALL. Цим часто користуються. Але існують ще 2 оператора EXCEPT та INTERSECT.
Приклад з EXCEPT
SELECT * FROM goods EXCEPT (SELECT * FROM WHERE goods availability)
id name description price articul act_time act_time
4 Наволочка Сірий 400.00 AR-75 2017-01-04 04:04:04.04 false
5 Простынка Шовкова 500.00 BR-75 2017-01-05 05:05:05.05 false
Власне з першого безлічі значень виключаються дані другого безлічі.
Приклад з INTERSECT
SELECT * FROM goods WHERE id > 2 INTERSECT (SELECT * FROM WHERE goods availability)
id name description price articul act_time act_time
3 Ковдру Пухова 300.00 ZR-75 2017-01-03 03:03:03.03 true
Власне відбувається перетин першого множини значень і другого безлічі.
На цьому все, спасибі за Вашу увагу.

Джерела
BNF Grammars for SQL-92, SQL-99 and SQL-2003
SQL Tutorial

Редакція
N1. Спасибі streetflush за конструктивну критику. Вніс статтю інформацію про те, що є стандартом мови, а що ні.
N2. Виправлено пункт 4, з пояснення про те, що ts/d/t не являюься частиною мови SQL. Дякую за уважність Melkij.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.