лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
Предикаты сравнения Предикат сравнения представляет собой два выражения, соединяемых оператором сравнения. Имеется шесть традиционных операторов сравнения: =, >, <, >=, <=, <>. Данные типа NUMERIC (числа) сравниваются в соответствии с их алгебраическим значением. Данные типа CHARACTER STRING (символьные строки) сравниваются в соответствии с их алфавитной последовательностью. Если а1а2…аn и в1 в…вn — две последовательности символов, то первая «меньше» второй, если а1 < в1, или а1 = в1 и а2 < в2 и т. д. Считается также, что а1а2…аn < в1в2…вm, если n < m и а1а2…аn = в1в2…вn, то есть если первая строка является префиксом второй. Например, ‘folder’ < ‘for’, так как первые две буквы этих строк совпадают, а третья буква строки ‘folder’ предшествует третьей букве строки ‘for’. Также справедливо неравенство ‘bar’ < ‘barber’, поскольку первая строка является префиксом второй. Данные типа DATETIME (дата/время) сравниваются в хронологическом порядке. Данные типа INTERVAL (временной интервал) преобразуются в соответствующие типы, а затем сравниваются как обычные числовые значения типа NUMERIC. Пример 5.2.1 Получить информацию о компьютерах, имеющих частоту процессора не менее 500 МГц и цену ниже $800: 1. SELECT * 2. FROM PC 3. WHERE speed >= 500 AND 4. price < 800 ; Запрос возвращает следующие данные code model speed ram hd cd price 1 1232 500 64 5 12x 600 3 1233 500 64 5 12x 600 7 1232 500 32 10 12x 400 10 1260 500 32 10 12x 350 Пример 5.2.2 Получить информацию обо всех принтерах, которые не являются матричными и стоят меньше $300: 1. SELECT * 2. FROM printer 3. WHERE NOT ( type = 'matrix' ) AND 4. price < 300 ; Результат выполнения запроса: code model color type price 2 1433 y Jet 270 3 1434 y Jet 290 Предикат BETWEEN Синтаксис: 1. BETWEEN ::= 2. <Проверяемое выражение> [ NOT ] BETWEEN 3. <Начальное выражение> AND <Конечное выражение> Предикат BETWEEN проверяет, попадают ли значения проверяемого выражения в диапазон, задаваемый пограничными выражениями, соединяемыми служебным словом AND. Естественно, как и для предиката сравнения, выражения в предикате BETWEEN должны быть совместимы по типам. Предикат 1. exp1 BETWEEN exp2 AND exp3 равносилен предикату 1. exp1 >= exp2 AND exp1 <= exp3 А предикат 1. exp1 NOT BETWEEN exp2 AND exp3 равносилен предикату 1. NOT ( exp1 BETWEEN exp2 AND exp3 ) Если значение предиката exp1 BETWEEN exp2 AND exp3 равно TRUE, в общем случае это отнюдь не означает, что значение предиката exp1 BETWEEN exp3 AND exp2 тоже будет TRUE, так как первый будет интерпретироваться как предикат: 1. exp1 >= exp2 AND exp1 <= exp3 а второй как: 1. exp1 >= exp3 AND exp1 <= exp2 Пример 5.2.3 Найти модель и частоту процессора компьютеров стоимостью от $400 до $600: 1. SELECT model, speed 2. FROM PC 3. WHERE price BETWEEN 400 AND 600 ; model speed 1232 500 1233 500 1232 500 Предикат IN Синтаксис: 1. IN ::= 2. <Проверяемое выражение> [ NOT ] IN ( <подзапрос> ) 3. | ( <выражение для вычисления значения>,... ) Предикат IN определяет, будет ли значение проверяемого выражения обнаружено в наборе значений, который либо явно определен, либо получен с помощью табличного подзапроса. Здесь табличный подзапрос это обычный оператор SELECT, который создает одну или несколько строк для одного столбца, совместимого по типу данных со значением проверяемого выражения. Если целевой объект эквивалентен хотя бы одному из указанных в предложении IN значений, истинностное значение предиката IN будет равно TRUE. Если для каждого значения Х в предложении IN целевой объект <> X, истинностное значение будет равно FALSE. Если подзапрос выполняется, и результат не содержит ни одной строки (пустая таблица), предикат принимает значение FALSE. Когда не соблюдается ни одно из упомянутых выше условий, значение предиката равно UNKNOWN. Пример 5.2.4 Найти модель, частоту процессора и объем жесткого диска тех компьютеров, которые комплектуются накопителями 10 или 20 Гбайт: 1. SELECT model, speed, hd 2. FROM PC 3. WHERE hd IN ( 10 , 20 ) ; model speed hd 1233 750 20 1232 500 10 1232 450 10 1260 500 10 1233 800 20 Пример 5.2.5 Найти модель, частоту процессора и объем жесткого диска компьютеров, которые комплектуются накопителями 10 Гбайт или 20 Гбайт и выпускаются производителем А: 1. SELECT model, speed, hd 2. FROM PC 3. WHERE hd IN ( 10 , 20 ) AND 4. model IN ( SELECT model 5. FROM product 6. WHERE maker = 'A' 7. ) ; model speed hd 1233 750 20 1232 500 10 1232 450 10 1233 800 20 Переименование столбцов и вычисления в результирующем наборе Имена столбцов, указанные в предложении SELECT, можно переименовать. Это делает результаты более читабельными, поскольку имена полей в таблицах часто сокращают с целью упрощения набора. Ключевое слово AS, используемое для переименования, согласно стандарту можно и опустить, так как оно неявно подразумевается. Например, запрос: 1. SELECT ram AS Mb, hd Gb 2. FROM PC 3. WHERE cd = '24x' ; переименует столбец ram в Mb (мегабайты), а столбец hd в Gb (гигабайты). Этот запрос возвратит объемы оперативной памяти и жесткого диска для тех компьютеров, которые имеют 24-скоростной CD-ROM: Mb Gb 64 8 32 10 Переименование особенно желательно при использовании в предложении SELECT выражений для вычисления значения. Эти выражения позволяют получать данные, которые не находятся непосредственно в таблицах. Если выражение содержит имена столбцов таблицы, указанной в предложении FROM, то выражение подсчитывается для каждой строки выходных данных. Так, например, чтобы вывести объем оперативной памяти в килобайтах, можно написать: 1. SELECT ram* 1024 AS Kb, hd Gb 2. FROM PC 3. WHERE cd = '24x' ; Теперь будет получен следующий результат: Kb Gb 65536 8 32768 10 Иногда бывает необходимо выводить поясняющую информацию рядом с соответствующим значением. Это можно сделать, добавив строковое выражение как дополнительный столбец. Например, запрос: 1. SELECT ram, 'Mb' AS ram_units, hd, 'Gb' AS hd_units 2. FROM PC 3. WHERE cd = '24x' ; даст следующий результат: ram ram_units hd hd_units 64 Mb 8 Gb 32 Mb 10 Gb Если же явно не указать имя для выражения, то будет принят способ именования по умолчанию, который зависит от используемой СУБД. Так, в MS Access будут использованы имена типа выражение1 и т. д., а выходной столбец в MS SQL Server вообще не будет иметь заголовка. Согласно стандарту могут использоваться имена с ограничителями (delimited identifier), при этом в качестве ограничителя применяется символ двойной кавычки ("). Такой прием допускает присутствие в именах специальных символов и зарезервированных слов. Например, запрос 1. SELECT 'SELECT' "SELECT" ; выведет значение выражения (в данном случае символьную константу 'SELECT') в столбце с именем SELECT. Т.е. мы используем зарезервированное слово в качестве имени столбца. Без этого компилятор (SQL Server) не сможет корректно выполнить разбор подобного запроса 1. SELECT 'SELECT' SELECT ; и выдаст такую ошибку: Incorrect syntax near 'SELECT'. (некорректный синтаксис возле 'SELECT') Помимо стандартного ограничителя, различные СУБД допускают использование своих собственных. Например, в SQL Server наш запрос можно написать так: 1. SELECT 'SELECT' [ SELECT ] ; В то же время, стандартный ограничитель используется параллельно, но не везде он принят настройками по умолчанию. В MSSQL настройку, отвечающую за имена с ограничителями, можно изменить с помощью оператора 1. SET QUOTED_IDENTIFIER { ON | OFF } При этом стандартное поведение (ON) принято по умолчанию. MySQL Запрос 1. SELECT 'SELECT' "SELECT" ; даст в результате выполнения SELECTSELECT Это я объясняю тем, что настройки по умолчанию допускают использование символа (") в качестве символа (') для ограничителей строковой константы. Поэтому две строки просто сливаются в одну. Однако если написать так 1. SELECT 'SELECT' AS "SELECT" ; или так 1. SELECT 'SELECT' `SELECT` ; то мы получим требуемый результат. Чтобы запретить использование двойной кавычки в качестве одинарной, можно поменять настройки на стандартные. Следующий оператор изменит настройку, о которой идет речь: 1. SET GLOBAL sql_mode= 'ANSI_QUOTES' ; а этот все настройки сделает стандартными: 1. SET GLOBAL sql_mode= 'ANSI' ; После этого запрос 1. SELECT 'SELECT' "SELECT" ; даст SELECT SELECT Oracle и PostgreSQL Эти СУБД ведут себя стандартно. Следует лишь отметить, что поскольку Oracle требует присутствия предложения FROM в запросе, наш оператор следует написать так: 1. SELECT 'SELECT' "SELECT" FROM dual; Предикат LIKE Синтаксис: 1. LIKE ::= 2. <Выражение для вычисления значения строки> 3. [ NOT ] LIKE <Выражение для вычисления значения строки> 4. [ ESCAPE <символ> ] Предикат LIKE сравнивает строку, указанную в первом выражении, для вычисления значения строки, называемого проверяемым значением, с образцом, который определен во втором выражении для вычисления значения строки. В образце разрешается использовать два трафаретных символа: символ подчеркивания (_), который можно применять вместо любого единичного символа в проверяемом значении; символ процента (%) заменяет последовательность любых символов (число символов в последовательности может быть от 0 и более) в проверяемом значении. Если проверяемое значение соответствует образцу с учетом трафаретных символов, то значение предиката равно TRUE. Ниже приводится несколько примеров написания образцов. Образец Описание 'abc%' Любые строки, которые начинаются с букв «abc» 'abc_' Строки длиной строго 4 символа, причем первыми символами строки должны быть «abc» '%z' Любая последовательность символов, которая обязательно заканчивается символом «z» '%Rostov%' Любая последовательность символов, содержащая слово «Rostov» в любой позиции строки '% % %' Текст, содержащий не менее 2-х пробелов, например, "World Wide Web" Пример 5.4.1 Найти все корабли, имена классов которых заканчиваются на букву ‘о’ 1. SELECT * 2. FROM Ships 3. WHERE class LIKE '%o' ; Результатом выполнения запроса будет следующая таблица: name class launched Haruna Kongo 1916 Hiei Kongo 1914 Kirishima Kongo 1915 Kongo Kongo 1913 Musashi Yamato 1942 Yamato Yamato 1941 Пример 5.4.2 Найти все корабли, имена классов которых заканчиваются на букву ‘о’, но не на ‘go’ 1. SELECT * 2. FROM Ships 3. WHERE class NOT LIKE '%go' AND 4. class LIKE '%o' ; Name Class launched Musashi Yamato 1942 Yamato Yamato 1941 Если искомая строка содержит трафаретный символ, то следует задать управляющий символ в предложении ESCAPE. Этот управляющий символ должен использоваться в образце перед трафаретным символом, сообщая о том, что последний следует трактовать как обычный символ. Например, если в некотором поле следует отыскать все значения, содержащие символ «_», то шаблон ‘%_%’ приведет к тому, что будут возвращены все записи из таблицы. В данном случае шаблон следует записать следующим образом: 1. '%#_%' ESCAPE '#' Для проверки значения на соответствие строке «25%» можно воспользоваться таким предикатом: 1. LIKE '25|%' ESCAPE '|' Истинностное значение предиката LIKE присваивается в соответствии со следующими правилами: если либо проверяемое значение, либо образец, либо управляющий символ есть NULL, истинностное значение равно UNKNOWN; в противном случае, если проверяемое значение и образец имеют нулевую длину, истинностное значение равно TRUE; в противном случае, если проверяемое значение соответствует шаблону, то предикат LIKE равен TRUE; если не соблюдается ни одно из перечисленных выше условий, предикат LIKE равен FALSE. Предикат LIKE и регулярные выражения Предикат LIKE в его стандартной редакции не поддерживает регулярных выражений, хотя ряд реализаций (в частности, Oracle) допускает их использование, расширяя возможности стандарта. В SQL Server 2005/2008 использование регулярных выражений возможно через CLR, т.е. посредством языков Visual Studio, которые могут использоваться для написания хранимых процедур и функций. Однако в Transact-SQL, помимо стандартных символов-шаблонов ("%" и "_"), существует еще пара символов, которые делают этот предикат LIKE более гибким инструментом. Этими символами являются: [ ] - одиночный символ из набора символов (например, [zxy]) или диапазона ([a-z]), указанных в квадратных скобках. При этом можно перечислить сразу несколько диапазонов (например, [0-9a-z]); ^ - который в сочетании с квадратными скобками исключает из поискового образца символы из набора или диапазона. Поясним использование этих символов на примерах. 1. SELECT * FROM 2. ( 3. SELECT '5%' name UNION ALL 4. SELECT '55' UNION ALL 5. SELECT '5%%' UNION ALL 6. SELECT '3%%' UNION ALL 7. SELECT 'a5%%' UNION ALL 8. SELECT 'abc' UNION ALL 9. SELECT 'abc 5% cde' UNION ALL 10. SELECT '5c2e' UNION ALL 11. SELECT 'C2H5OH' UNION ALL 12. SELECT 'C25OH' UNION ALL 13. SELECT 'C54OH' 14. ) x 15. /* 1 */ 16. --WHERE name LIKE'5%' -- начинается с 5 17. /* 2 */ 18. --WHERE name LIKE '5[%]' -- 5% 19. /* 3 */ 20. --WHERE name LIKE '5|%' ESCAPE '|'-- 5% 21. /* 4 */ 22. --WHERE name LIKE '%5|%%' ESCAPE '|' -- 5% в любом месте строки 23. /* 5 */ 24. --WHERE name LIKE '[0-9][a-zA-Z]%' -- первая цифра, вторая буква 25. /* 6 */ 26. --WHERE name LIKE '[a-z][0-9]%' -- первая буква, вторая цифра 27. /* 7 */ 28. --WHERE name LIKE '[^0-9]%' -- начинается не на цифру. 29. /* 8 */ 30. --WHERE name LIKE '%[02468]%' -- содержит четную цифру. 31. /* 9 */ 32. --WHERE name LIKE '%[02468][13579]%' -- комбинация четная-нечетная. В данном запросе генерируются некоторые данные, для поиска в которых используется предикат LIKE. Девять примеров - это соответственно 9 закомментированных предложений WHERE. Для проверки результатов выполнения запроса на сайте предварительно снимите комментарий с одной из строк, начинающихся с WHERE. Тем, кто не может использовать сайт, приведу здесь результаты выполнения этих примеров. 1. Все строки, которые начинаются с 5: name 5% 55 5%% 5c2e 2. Поиск строки '5%'. Символ в скобках воспринимается как обычный единичный символ: name 5% 3. Другое решение, аналогичное второму, но использующее ESCAPE-символ, указывающий, что знак % следует воспринимать как обычный символ. 4. Поиск подстроки '5%', находящейся в любом месте строки: name 5% 5%% a5%% abc 5% cde 5. Поиск строки, у которой первый символ является цифрой, а второй - буквой: name 5c2e 6. Поиск строки, у которой первый символ является буквой, а второй - цифрой. Вариант для регистронезависимого сравнения: name a5%% C2H5OH C25OH C54OH |