1. 18.2.4. предикат in
2. Приклади запитів з використанням предиката in
3. 18.2.5. предикат like
4. Приклади запитів з використанням предиката like
5. 18.2.6. предикат similar
6. Приклади запитів з використанням предиката similar
7. 18.2.7. предикат exists
8. Приклади запитів з використанням предиката exists
9. 18.2.8. предикат unique
10. Приклади запитів з використанням предиката unique
11. 18.2.9. предикат overlaps
12. Приклади запитів з використанням предиката overlaps

2008 р Сергій Кузнецов

назад зміст вперед

18.2.4. предикат in

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

in_predicate :: = row_value_constructor [NOT] IN in_predicate_value in_predicate_value :: = table_subquery | (Value_expression_comma_list)

Рядок, що є першим операндом, і таблиця-другий операнд повинні бути однаковою мірою. Зокрема, якщо другий операнд є список значень, то перший операнд повинен мати ступінь 1. Типи даних відповідних стовпців операндів повинні бути сумісні.

Нехай X позначає рядок-перший операнд, а S - безліч рядків другого операнда. Позначимо через s рядок-елемент цієї множини. Тоді за визначенням умова X IN S еквівалентно булевскому висловом OR (X = s). Іншими словами, X IN S приймає значення true в тому і тільки в тому випадку, коли в безлічі S існує хоча б один елемент s, такий, що значенням предиката X = s є true. X IN S приймає значення false в тому і тільки тому випадку, коли для всіх елементів s безлічі S значенням операції порівняння X = s є false. Інакше значенням умови X IN S є unknown. Зауважимо, що для порожнього безлічі S значенням X IN S є false.

За визначенням умова X NOT IN S еквівалентно NOT (X IN S).

Приклади запитів з використанням предиката in

Приклад 18.9. Знайти номери, імена та номери відділів службовців, які працюють у відділах 15, 17 і 19.

SELECT EMP_NO, EMP_NAME, DEPT_NO FROM EMP WHERE DEPT_NO IN (15, 17, 19);

Звичайно, це формулювання запиту еквівалентна наступній формулюванні (приклад 18.9.1):

SELECT EMP_NO, EMP_NAME, DEPT_NO FROM EMP WHERE DEPT_NO = 15 OR DEPT_NO = 17 OR DEPT_NO = 19;

Приклад 18.10. Знайти номери службовців, які є керівниками відділів і отримують латку, розмір якої дорівнює розміру зарплати будь-якого керівника відділу.

SELECT EMP_NO FROM EMP WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT) AND EMP_SAL IN (SELECT EMP_SAL FROM EMP, DEPT WHERE EMP_NO = DEPT_MNG);

Запити, що містять предикат IN з підзапитом, легко переформулювати в запити з сполуками. Наприклад, запит з прикладу 18.10 еквівалентний наступному запитом з сполуками (приклад 18.10.1):

SELECT DISTINCT EMP_NO FROM EMP, EMP EMP1, DEPT WHERE EMP_NO NOT IN (SELECT DEPT_MNG FROM DEPT) AND EMP_SAL = EMP1_SAL AND EMP1.EMP_NO = DEPT.DEPT_MNG;

З приводу цієї другої формулювання слід зробити два зауваження. По-перше, як видно, ми змінили тільки ту частину умови, в якій використовувався предикат IN, і не торкнулися предикат NOT IN. Запити з предикатами NOT IN запитами з сполуками так просто не замінюються. По-друге, в розділі SELECT було додано ключове слово DISTINCT, тому що в результаті запиту в другій формулюванні для кожного службовця буде міститися стільки рядків, скільки існує керівників відділів, які отримують таку ж зарплату, що і даний службовець.

18.2.5. предикат like

Формально предикат like визначається наступними синтаксичними правилами:

like_predicate :: = source_value [NOT] LIKE pattern_value [ESCAPE escape_value] source_value :: = value_expression pattern_value :: = value_expression escape_value :: = value_expression

Всі три операнда (source_value, pattern_value і escape_value) повинні бути одного типу: або типу символьних рядків, або типу бітових рядків 139) . У першому випадку значенням останнього операнда повинна бути рядок з одного символу, у другому - рядок з 8 біт. Другий операнд, як правило, задається літералом відповідного типу. В обох випадках значення предиката дорівнює true в тому і тільки в тому випадку, коли вихідна рядок (source_value) може бути порівняна з заданим шаблоном (pattern_value).

Якщо обробляються символьні рядки, і якщо розділ ESCAPE умови відсутня, то при зіставленні шаблону з рядком проводиться спеціальна інтерпретація двох символів шаблону: символ підкреслення ( '_') позначає будь-який одиночний символ; символ відсотка ( '%') позначає послідовність довільних символів довільної довжини (довжина послідовності може бути нульовою). Якщо ж розділ ESCAPE присутній і специфицирует деякий одиночний символ x, то пари символів «x_» і «x%» представляють поодинокі символи «_» і «%» відповідно.

У разі обробки бітових рядків зіставлення шаблону з рядком проводиться вісімками сусідніх біт (октетами). Відповідно до стандарту SQL: 1999, при зіставленні шаблону з рядком проводиться спеціальна інтерпретація октетів зі значеннями X'25 'і X'5F' (коди символів підкреслення і відсотки в кодуванні ASCII). Перший октет позначає будь-який одиночний октет, а другий - послідовність довільної довжини довільних октетів (довжина може бути нульовою). У розділі ESCAPE вказується октет, який скасовує спеціальну інтерпретацію октетів X'25 'і X'5F'.

Значення предиката like є unknown, якщо значення першого або другого операндів є невизначеним. Умова x NOT LIKE y ESCAPE z еквівалентно умові NOT x LIKE y ESCAPE z.

Приклади запитів з використанням предиката like

Приклад 18.11. Знайти номери проектів, в назві яких присутні слова "next" і "step '. Слова повинні слідувати саме в такій послідовності, але слово 'next' може бути першим в назві проекту.

SELECT PRO_TITLE FROM PRO WHERE PRO_TITLE LIKE '% next% step%' OR PRO_TITLE LIKE 'Next% step%';

Це дуже невдалий запит, тому що його виконання, швидше за все, змусить СУБД переглянути всі рядки таблиці PRO і для кожного рядка виконати дві перевірки стовпчика PRO_TITLE. Можна трохи поліпшити формулювання з невеликим ризиком отримати невірну відповідь (приклад 18.11.1):

SELECT PRO_TITLE FROM PRO WHERE PRO_TITLE LIKE '% ext% step%';

Приклад 18.12. Знайти номери відділів, службовці яких є менеджерами проектів, і назва кожного з цих проектів починається з назви відділу.

SELECT DISTINCT DEPT.DEPT_NO FROM EMP, DEPT, PRO WHERE EMP.EMP_NO = PRO.PRO_MNG AND EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_TITLE LIKE DEPT.DEPT_NAME || '%';

Ось як може виглядати формулювання цього запиту, якщо використовувати вкладені підзапити (приклад 18.12.1):

SELECT DEPT.DEPT_NO FROM DEPT WHERE DEPT.DEPT_NO IN (SELECT EMP.DEPT_NO FROM EMP WHERE EMP.EMP_NO IN (SELECT PRO.PRO_MNG FROM PRO WHERE PRO.PRO_TITLE LIKE DEPT.DEPT_NAME || '%'));

Приклад 18.13. Знайти номери відділів, назви яких не починаються зі слова 'Software'.

SELECT DEPT_NO FROM DEPT WHERE DEPT_NAME NOT LIKE 'Software%';

18.2.6. предикат similar

Формально предикат similar визначається наступними синтаксичними правилами:

similar_predicate :: = source_value [NOT] SIMILAR TO pattern_value [ESCAPE escape_value] source_value :: = character_expression pattern_value :: = character_expression escape_value :: = character_expression

Всі три операнда (source_value, pattern_value і escape_value) повинні мати тип символьних рядків. Значним останнього операнда повинна бути рядок з одного символу. Другий операнд, як правило, задається літералом відповідного типу. В обох випадках значення предиката дорівнює true в тому і тільки в тому випадку, коли шаблон (pattern_value) належним чином зіставляється з вихідної рядком (source_value).

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

regular_expression :: = regular_term | regular_expression vertical_bar regular_term regular_term :: = regular_factor | regular_term regular_factor regular_factor :: = regular_primary | regular_primary * | regular_primary + regular_primary :: = character_specifier | % | regular_character_set | (Regular_expression) character_specifier :: = non_escape_character | escape_character regular_character_set :: = _ | left_bracket character_enumeration_list right_bracket | left_bracket ^ character_enumeration_list right_bracket | left_bracket: regular_charset_id: right_bracket character_enumeration :: = character_specifier | character_specifier - character_specifier regular_charset_id :: = ALPHA | UPPER | LOWER | DIGIT | ALNUM

Оскільки в синтаксичних правилах регулярних виразів символи «|», «[» і «]», використовувані нами в якості метасимволов в BNF, є термінальними символами, вони зображені як vertical_bar, left_bracket і right_bracket відповідно.

Створюване за наведеними правилами регулярний вираз являє собою символьну рядок, що містить всі символи, які потрібно явно зіставляти з символами рядка-джерела. У рядку можуть перебувати спеціальні символи, що представляють собою замінники звичайних символів ( «%» і «_»), позначення операцій ( «|»), показники числа можливих повторень ( «*» і «+») і т. Д. При обчисленні регулярного виразу утворюються всі можливі символьні рядки, що не містять спеціальних символів і відповідні вихідного шаблоном. Тим самим, значенням предиката similar є true в тому і тільки в тому випадку, коли серед всіх символьних рядків, що генеруються по регулярному виразу pattern_value, знайдеться символьний рядок, що збігається з source_value.

Розглянемо кілька прикладів регулярних виразів.

Вираз '(This is string1) | (This is string2)' виробляє дві символьні рядки: '(This is string1)' і '(This is string2)'. У загальному випадку в круглих дужках можуть перебувати довільні регулярні вирази rexp1 і rexp2. Результатом обчислення '(rexp1) | (rexp2)' є безліч символьних рядків, що генеруються виразом rexp1, об'єднане з безліччю символьних рядків, що генеруються виразом rexp2.

Вираз 'This is string [12] *' генерує символьні рядки 'This is string', 'This is string 1', 'This is string 2', 'This is string 11', 'This is string 22', 'This is string 12 ',' This is string 22 ',' This is string 111 'і т. д. Конструкція в квадратних дужках являє собою один з варіантів визначення набору символів (regular_character_set). В даному випадку символи, що входять в який визначається набір, просто перераховуються. При обчисленні регулярного виразу в кожній з генеруються символьних рядків конструкція в квадратних дужках замінюється одним із символів відповідного набору.

Спеціальний символ «*», що стоїть після закриває квадратної дужки, є показником числа повторень. «Зірочка» означає, що в генеруються символьних рядках формального виразу, що безпосередньо передує «зірочці», може з'являтися нуль або більше разів. Використання в такій же ситуації спеціального символу «+» означає, що в генеруються символьних рядках формального виразу, що безпосередньо передує символу «плюс», може з'являтися один або більше разів.

Інша форма визначення набору символів ілюструється регулярним виразом 'This is string [: DIGIT:]'. У цьому випадку конструкція в квадратних дужках являє будь-який одиночний символ, який зображає десяткову цифру. Іншими допустимими в SQL ідентифікаторами наборів символів (regular_charset_id) є ALPHA (будь-який символ алфавіту), UPPER (будь-який символ верхнього регістру), LOWER (будь-який символ нижнього регістра) і ALNUM (будь алфавітно-цифровий символ).

Визначається набір символів може здаватися нижньої і верхньої межею діапазону значень кодів допустимих символів. Наприклад, в регулярному виразі 'This is string [3-8]' конструкція в квадратних дужках являє собою будь-який одиночний символ, який зображає цифри від 3 до 8 включно. Зауважимо, що при завданні діапазону можна використовувати будь-які символи, але потрібно, щоб значення коду символу лівої межі діапазону було не більше значення коду символу правої межі.

Нарешті, є ще одна можливість визначення набору символів. Відповідна конструкція дозволяє вказати, які символи із загального набору символів SQL не входять до визначається набір символів. Наприклад, регулярний вираз '_S [^ t] * ing%' генерує все символьні рядки, у яких другим символом є «S», за яким (не обов'язково безпосередньо) слід подстрока «ing», але між «S» і «ing» відсутні входження символу «t».

Як і в предикате like, символ, визначений в розділі ESCAPE, поставлений перед будь-яким спеціальним символом, скасовує спеціальну інтерпретацію цього символу.

На закінчення даного пункту повернемося до відкладеного в розділі 17.2 «Скалярні вираження» лекції 17 обговоренню функції SUBSTRING ... SIMILAR ... ESCAPE. Нагадаємо, що виклик цієї функції визначається наступним синтаксисом:

SUBSTRING (character_value_expression SIMILAR character_value_expression ESCAPE character_value_expression)

Припустимо, що в розділі ESCAPE (який повинен бути присутнім обов'язково) заданий символ «x». Тоді символьний рядок, що задається у другому операнд, повинна мати вигляд 'rexp1x "rexp2x" rexp3', де rexp1, rexp2 і rexp3 є регулярними виразами. Функція намагається розділити символьний рядок першого операнда на три розділи, перший з яких визначається шляхом зіставлення початку рядка з рядками, що генеруються rexp1, другий - шляхом зіставлення решти рядки першого операнда з rexp2 і третій - шляхом зіставлення кінця цього рядка з rexp3. Значенням функції є середня частина символьного рядка першого операнда.

Ось приклад виклику функції:

SUBSTRING ( 'This is string22' SIMILAR 'This is \ "[: ALPHA:] + \" [: DIGIT:] +' ESCAPE '\')

Результатом буде рядок 'string'.

Приклади запитів з використанням предиката similar

Приклад 18.14. Знайти номери і назви відділів, назва яких починається зі слів 'Hardware' або 'Software', а за ними (не обов'язково безпосередньо) слід послідовність десяткових цифр, яка випереджає символом підкреслення.

SELECT DEPT_NAME, DEPT_NO FROM DEPT WHERE DEPT_NAME SIMILAR TO '(HARD | SOFT) WARE% \ _ [: DIGIT:] +' ESCAPE '\';

Приклад 18.15. Знайти номери і назви проектів, назва яких не починається з послідовності цифр.

SELECT DEPT_NAME, DEPT_NO FROM DEPT WHERE DEPT_NAME SIMILAR TO '[^ 1-9] +%';

18.2.7. предикат exists

Предикат exists визначається наступним синтаксичним правилом:

exists_predicate :: = EXISTS (query_expression)

Значним умови EXISTS (query_expression) є true в тому і тільки в тому випадку, коли потужність таблиці-результату виразу запитів більше нуля, інакше значенням умови є false.

Приклади запитів з використанням предиката exists

Приклад 18.16. Знайти номери відділів, серед службовців яких є менеджери проектів.

SELECT DEPT.DEPT_NO FROM DEPT WHERE EXISTS (SELECT EMP.EMP_NO FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND EXISTS (SELECT PRO.PRO_MNG FROM PRO WHERE PRO.PRO_MNG = EMP.EMP_NO));

Цю формулювання можна спростити, позбувшись від самого вкладеного запиту (приклад 18.16.1):

SELECT DEPT.DEPT_NO FROM DEPT WHERE EXISTS (SELECT EMP.EMP_NO FROM EMP, PRO WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_MNG = EMP.EMP_NO);

Далі зауважимо, що за змістом предиката EXISTS список вибірки у вкладеному підзапит є несуттєвим, і формулювання запиту можна змінити, наприклад, наступним чином (приклад 18.16.2):

SELECT DEPT.DEPT_NO FROM DEPT WHERE EXISTS (SELECT * FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_MNG = EMP.EMP_NO);

Запити з предикатом EXISTS можна також переформулювати у вигляді запитів з предикатом порівняння (приклад 18.16.3):

SELECT DEPT.DEPT_NO FROM DEPT WHERE (SELECT COUNT (*) FROM EMP, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND PRO.PRO_MNG = EMP.EMP_NO)> = 1;

Приклад 18.17. Знайти номери відділів, розмір заробітної плати службовців яких не перевищує розмір заробітної плати керівника відділу.

SELECT DEPT.DEPT_NO FROM DEPT WHERE NOT EXISTS (SELECT * FROM EMP EMP1, EMP EMP2 WHERE EMP1.EMP_NO = DEPT.DEPT_MNG AND EMP2.DEPT_NO = DEPT.DEPT_NO AND EMP2.EMP_SAL> EMP1.EMP_SAL);

18.2.8. предикат unique

Цей предикат дозволяє сформулювати умова відсутності дублікатів в результаті запиту:

unique_predicate :: = UNIQUE (query_expression)

Результатом обчислення умови UNIQUE (query_expression) є true в тому і тільки в тому випадку, коли в таблиці-результаті вираження запитів відсутні будь-які два рядки, один з яких є дублікатом іншої. В іншому випадку значення умови є false.

Приклади запитів з використанням предиката unique

Приклад 18.18. Знайти номери відділів, службовців яких можна розрізнити по імені і даті народження.

SELECT DEPT_NO FROM DEPT WHERE UNIQUE (SELECT EMP_NAME, EMP_BDATE FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

Можлива альтернативна, але більш складна формулювання цього запиту з використанням предиката NOT EXISTS (приклад 18.18.1):

SELECT DEPT_NO FROM DEPT WHERE NOT ESISTS (SELECT * FROM EMP, EMP EMP1 WHERE EMP1.EMP_NO <> EMP.EMP_NO AND EMP.DEPT_NO = DEPT.DEPT_NO AND EMP1.DEPT_NO = DEPT.DEPT_NO AND EMP1.EMP_NAME = EMP.EMP_NAME AND ( EMP1.EMP_BDATE = EMP.EMP_BDATE OR (EMP.EMP_BDATE IS NULL AND EMP1.EMP_BDATE IS NULL)));

Якщо ж обмежитися вимогою унікальності імен службовців, то можлива наступна формулювання (приклад 18.18.2):

SELECT DEPT_NO FROM DEPT WHERE (SELECT COUNT (EMP_NAME) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO) = (SELECT COUNT (DISTINCT EMP_NAME) FROM EMP WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

18.2.9. предикат overlaps

Цей предикат служить для перевірки перекриття в часі двох подій. Умова визначається наступним синтаксисом:

overlaps_predicate :: = row_value_constructor OVERLAPS row_value_constructor

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

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

Нехай D1 і D2 - значення першого стовпчика першого і другого операндів відповідно. Якщо другий стовпець першого операнда має тип дата-час, то нехай E1 позначає його значення. Якщо другий стовпець першого операнда має тип INTERVAL, то нехай I1 - його значення, а E1 = D1 + I1. Якщо D1 є невизначеним значенням або якщо E1 <D1, то нехай S1 = E1 і T1 = D1. В іншому випадку, нехай S1 = D1 і T1 = E1. Аналогічно визначаються S2 і T2 стосовно до другого операнду. Результат умови збігається з результатом обчислення наступного булевского вираження:

(S1> S2 AND NOT (S1> = T2 AND T1> = T2)) OR (S2> S1 AND NOT (S2> = T1 AND T2> = T1)) OR (S1 = S2 AND (T1 <> T2 OR T1 = T2))

Приклади запитів з використанням предиката overlaps

Приклад 18.19. Знайти номери проектів, які виконувалися в період з 15 січня 2000 по 31 грудня 2002 р

SELECT PRO_NO FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (DATE '2000-01-15', DATE '2002-12-31');

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

SELECT PRO_TITLE FROM PRO WHERE (PRO_SDATE, PRO_DURAT) OVERLAPS (CURRENT_DATE, INTERVAL '1' YEAR);

139 У стандарті SQL: 1999 дозволяється застосовувати предикат LIKE тільки для бітових рядків типу BLOB. Бітові рядки типів BIT і BIT VARYING не допускаються.

назад зміст вперед