лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
ALL есть TRUE, если подзапрос не возвращает строк: 1. SELECT DISTINCT maker 2. FROM Product Pr1 3. WHERE type = ALL 4. ( SELECT type FROM Product 5. EXCEPT 6. SELECT type FROM Product Pr2 7. WHERE Pr2.maker = Pr1.maker 8. ) ; Для искомых производителей список типов в предикате ALL будет пуст (предикат равен TRUE). В остальных случаях он будет содержать типы моделей, отсутствующие у производителя из внешнего запроса, поэтому операция сравнения (равенство "=") для всех его моделей даст FALSE. Существование Не должно существовать такого типа продукции, которого бы не было у искомого производителя. 1. SELECT DISTINCT maker 2. FROM Product Pr1 3. WHERE NOT EXISTS 4. ( SELECT type 5. FROM Product 6. WHERE type NOT IN 7. ( SELECT type 8. FROM Product Pr2 9. WHERE Pr1.maker = Pr2.maker 10. ) 11. ) ; Кроме первого варианта с группировкой все остальные решения используют коррелирующие подзапросы для определения множества типов моделей производителя из основного запроса. Следует также отметить, что решение с группировкой не подойдет для случая, когда требуется выполнить деление не на все множество имеющихся типов, а на некоторое их подмножество. Например, если требуется найти производителей, у которых множество типов включает в себя (или совпадает) множество типов, определяемое некоторыми критериями. Другие же приемы можно адаптировать для решения подобной задачи. Использование ключевых слов SOME (ANY) и ALL с предикатами сравнения 1. <выражение> <оператор сравнения> SOME | ANY ( <подзапрос> ) SOME и ANY являются синонимами, то есть может использоваться любое из них. Результатом подзапроса является один столбец величин. Если хотя бы для одного значения V, получаемого из подзапроса, результат операции "<значение выражения> < оператор сравнения > V" равняется TRUE, то предикат ANY также равняется TRUE. 1. <выражение> <оператор сравнения> ALL ( <подзапрос> ) Исполняется так же, как и ANY, однако значение предиката ALL будет истинным, если для всех значений V, получаемых из подзапроса, предикат "<значение выражения> <оператор сравнения> V" дает TRUE. Пример 5.8.1. Найти поставщиков компьютеров, моделей которых нет в продаже (то есть модели этих поставщиков отсутствуют в таблице PC) 1. SELECT DISTINCT maker 2. FROM Product 3. WHERE type = 'pc' AND 4. NOT model = ANY ( SELECT model 5. FROM PC 6. ) ; Оказалось, что только у поставщика Е есть модели, отсутствующие в продаже: Maker E Рассмотрим подробно этот пример. Предикат 1. model = ANY ( SELECT model 2. FROM PC 3. ) ; вернет значение TRUE, если модель, определяемая полем model основного запроса, найдется в списке моделей таблицы РС (возвращаемом подзапросом). Поскольку предикат используется в запросе с отрицанием NOT, то значение TRUE будет получено, если модели не окажется в списке. Этот предикат проверяется для каждой записи основного запроса, которыми являются все модели ПК (предикат type = 'pc') из таблицы Product. Результирующий набор состоит из одного столбца — имени производителя. Чтобы один производитель не выводился несколько раз (что может случиться, если он производит несколько моделей, отсутствующих в таблице РС), используется служебное слово DISTINCT, исключающее дубликаты. Пример 5.8.2. Найти модели и цены портативных компьютеров, стоимость которых превышает стоимость любого ПК 1. SELECT DISTINCT model, price 2. FROM Laptop 3. WHERE price > ALL ( SELECT price 4. FROM PC 5. ) ; model Price 1298 1050 1750 1200 1752 1150 Приведем формальные правила оценки истинности предикатов, использующих параметры ANY|SOME и ALL. Если определен параметр ALL или SOME и все результаты сравнения значения выражения и каждого значения, полученного из подзапроса, являются TRUE, истинностное значение равно TRUE. Если результат выполнения подзапроса не содержит строк и определен параметр ALL, результат равен TRUE. Если же определен параметр SOME, результат равен FALSE. Если определен параметр ALL и результат сравнения значения выражения хотя бы с одним значением, полученным из подзапроса, является FALSE, истинностное значение равно FALSE. Если определен параметр SOME и хотя бы один результат сравнения значения выражения и значения, полученного из подзапроса, является TRUE, истинностное значение равно TRUE. Если определен параметр SOME и каждое сравнение значения выражения и значений, полученных из подзапроса, равно FALSE, истинностное значение тоже равно FALSE. В любом другом случае результат будет равен UNKNOWN. Еще раз о подзапросах Заметим, что в общем случае запрос возвращает множество значений. Поэтому использование подзапроса в предложении WHERE без предикатов EXISTS, IN, ALL и ANY, которые дают булево значение, может привести к ошибке времени выполнения запроса. Пример 5.8.3 Найти модели и цены ПК, стоимость которых превышает минимальную стоимость портативных компьютеров: 1. SELECT DISTINCT model, price 2. FROM PC 3. WHERE price > ( SELECT MIN ( price ) 4. FROM Laptop 5. ) ; Этот запрос вполне корректен, так как скалярное значение price сравнивается с подзапросом, который возвращает единственное значение. В результате получим четыре модели ПК: model price 1121 850 1233 950 1233 970 1233 980 Однако, если в ответ на вопрос «найти модели и цены ПК, стоимость которых совпадает со стоимостью портативных компьютеров» написать следующий запрос 1. SELECT DISTINCT model, price 2. FROM PC 3. WHERE price = ( SELECT price 4. FROM Laptop 5. ) ; то при выполнении последнего мы можем получить такое сообщение об ошибке: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. («Подзапрос вернул более одного значения. Это не допускается в тех случаях, когда подзапрос следует после =, !=, <, <=, >, >= или когда подзапрос используется в качестве выражения».) Эта ошибка будет возникать при сравнении скалярного значения с подзапросом, который возвращает более одного значения. Подзапросы, в свою очередь, также могут содержать вложенные запросы. С другой стороны, подзапрос, возвращающий множество строк и содержащий несколько столбцов, вполне естественно может использоваться в предложении FROM. Это, например, позволяет ограничить набор столбцов и/или строк при выполнении операции соединения таблиц. Пример 5.8.4 Вывести производителя, тип, модель и частоту процессора для Портативных компьютеров, частота процессора которых превышает 600 МГц. Этот запрос может быть сформулирован, например, следующим образом: 1. SELECT prod.maker, lap.* 2. FROM ( SELECT 'laptop' AS type, model, speed 3. FROM laptop 4. WHERE speed > 600 5. ) AS lap INNER JOIN 6. ( SELECT maker, model 7. FROM product 8. ) AS prod ON lap.model = prod.model; В результате получим: maker type model speed B laptop 1750 750 A laptop 1752 750 Наконец, подзапросы могут присутствовать в предложении SELECT. Это иногда позволяет весьма компактно сформулировать запрос. Пример 5.8.5 Найти разницу между средними значениями цены портативных компьютеров и ПК, то есть насколько в среднем портативный компьютер стоит дороже, чем ПК. Здесь вообще можно обойтись одним предложением SELECT в основном запросе: 1. SELECT ( SELECT AVG ( price ) 2. FROM Laptop 3. ) - 4. ( SELECT AVG ( price ) 5. FROM PC 6. ) AS dif_price; В результате получим dif_price 328.3333 Преобразование типов и оператор CAST В реализациях языка SQL может быть выполнено неявное преобразование типов. Так, например, в SQL Server и Sybase ASE.Transact- SQL при сравнении или комбинировании значений типов smallint и int, данные типа smallint неявно преобразуются к типу int. Подробно о явном и неявном преобразовании типов в SQL Server можно прочитать в BOL. Пример 5.9.1. Вывести среднюю цену портативных компьютеров с предваряющим текстом «средняя цена = ». Попытка выполнить запрос 1. SELECT 'Средняя цена = ' + AVG ( price ) 2. FROM Laptop; приведет к сообщению об ошибке: Implicit conversion from data type varchar to money is not allowed. Use the CONVERT_:_1._SELECTCONVERT(NUMERIC(6,2),AVG(launched*1.0))2._FROM_Ships;_Функция_CONVERT'>CONVERT function to run this query. («Не допускается неявное преобразование типа varchar к типу money. Используйте для выполнения этого запроса функцию CONVERT».) Это сообщение означает, что система не может выполнить неявное преобразование типа varchar к типу money. В подобных ситуациях может помочь явное преобразование типов. При этом, как указано в сообщении об ошибке, можно воспользоваться функцией CONVERT. Однако эта функция не стандартизована, поэтому в целях переносимости рекомендуется использовать стандартное выражение CAST. С него и начнем. Итак, если переписать наш запрос в виде 1. SELECT 'Средняя цена = ' + CAST ( AVG ( price ) AS CHAR ( 15 )) 2. FROM Laptop; в результате получим то, что требовалось: 1. Средняя цена = 1003.33 Мы использовали выражение явного преобразования типов CAST для приведения среднего значения цены к строковому представлению. Синтаксис выражения CAST очень простой 1. CAST ( <выражение> AS <тип данных> ) Внимание: Следует иметь в виду, во-первых, что не любые преобразования типов возможны (стандарт содержит таблицу допустимых преобразований типов данных). Во-вторых, результат функции CAST для значения выражения, равного NULL, тоже будет NULL. Рассмотрим еще один пример. Пример 5.9.2 Определить средний год спуска на воду кораблей из таблицы Ships. Запрос: 1. SELECT AVG ( launched ) 2. FROM Ships; даст результат 1926. В принципе все правильно, так как мы получили в результате то, что просили — год. Однако среднее арифметическое будет составлять примерно 1926,9091. Тут следует напомнить, что агрегатные функции (за исключением функции COUNT, которая всегда возвращает целое число) наследуют тип данных обрабатываемых значений. Поскольку поле launched — целочисленное, мы и получили среднее значение с отброшенной дробной частью (заметьте — не округленное). А если нас интересует результат с заданной точностью, скажем, до двух десятичных знаков? Применение выражения CAST к среднему значению ничего не даст по указанной выше причине. Действительно, 1. SELECT CAST ( AVG ( launched ) AS NUMERIC ( 6 , 2 )) 2. FROM Ships; вернет значение 1926.00. Следовательно, CAST нужно применить к аргументу агрегатной функции: 1. SELECT AVG ( CAST ( launched AS NUMERIC ( 6 , 2 ))) 2. FROM Ships; Результат — 1926.90909. Опять не то. Причина состоит в том, что при вычислении среднего значения было выполнено неявное преобразование типа. Сделаем еще один шаг: 1. SELECT CAST ( AVG ( CAST ( launched AS NUMERIC ( 6 , 2 ))) AS NUMERIC ( 6 , 2 )) 2. FROM Ships; В результате получим то, что нужно — 1926.91. Однако это решение выглядит очень громоздко. Заставим неявное преобразование типа поработать на нас: 1. SELECT CAST ( AVG ( launched* 1.0 ) AS NUMERIC ( 6 , 2 )) 2. FROM Ships; Теперь мы использовали неявное преобразование целочисленного аргумента к точному числовому типу (EXACT NUMERIC), умножив его на вещественную единицу, после чего применили явное приведения типа результата агрегатной функции. Аналогичные преобразования типа можно выполнить с помощью функции SQL Server CONVERT: 1. SELECT CONVERT ( NUMERIC ( 6 , 2 ) , AVG ( launched* 1.0 )) 2. FROM Ships; Функция CONVERT имеет следующий синтаксис: 1. CONVERT ( <тип_данных [( <длина> )] >, <выражение> [ , <стиль> ]) Основное отличие функции CONVERT от функции CAST состоит в том, что первая позволяет форматировать данные (например, темпоральные данные типа datetime) при преобразовании их к символьному типу и указывать формат при обратном преобразовании. Разные целочисленные значения необязательного аргумента стиль соответствуют различным типам форматов. Рассмотрим следующий пример 1. SELECT CONVERT ( char ( 25 ) , CONVERT ( datetime, '20030722' )) ; Здесь мы преобразуем строковое представление даты к типу datetime, после чего выполняем обратное преобразование, чтобы продемонстрировать результат форматирования. Поскольку значение аргумента стиль не задано используется значение по умолчанию (0 или 100). В результате получим: Jul 22 2003 12:00AM Ниже приведены некоторые другие значения аргумента стиль и результат, полученный на приведенном выше примере. Заметим, что увеличение значения стиль на 100 приводит к четырехзначному отображению года. 1 07/22/03 11 03/07/22 3 22/07/03 121 2003-07-22 00:00:00.000 Перечень всех возможных значений аргумента стиль можно посмотреть в BOL. Есть одна особенность использования оператора CAST в SQL Server, связанная с преобразованием числа к его строковому представлению. Что произойдет, если число символов в числе превышает размер строки? Например, 1. SELECT CAST ( 1234.6 AS VARCHAR ( 5 )) ; Следует ожидать, что мы получим сообщение об ошибке. Правильно, вот это сообщение: Arithmetic overflow error converting numeric to data type varchar. («Ошибка арифметического переполнения при преобразовании числа к типу данных VARCHAR».) Естественно, что мы будем ожидать того же сообщения и при выполнении следующего оператора: 1. SELECT CAST ( 123456 AS VARCHAR ( 5 )) ; Но нет. В результате мы получим символ «*» вместо сообщения об ошибке. Мы не беремся судить, с чем это связано, однако, однажды мы столкнулись с проблемой диагностики ошибки в коде, в котором впоследствии выполнялось обратное преобразование к числовому типу. В нашем простейшем примере это будет выглядеть так: 1. SELECT CAST ( CAST ( 123456 AS VARCHAR ( 5 )) AS INT ) ; Вот тут-то мы и получаем ошибку: Syntax error converting the varchar value '*' to a column of data type int. («Ошибка синтаксиса при преобразовании значения «*» к типу данных INT».) Примечание: Функция SQL Server и Sybase ASE.Transact-SQL CONVERT ведет себя аналогичным образом. Преобразование типа money Денежный тип данных не является стандартным. В SQL Server имеется два денежных типа: money: диапазон значений от –922,337,203,685,477.5808 до 922,337,203,685,477.5807 smallmoney: диапазон значений от -214 748,3648 до 214 748,3647 Точность обоих типов одна десятитысячная. Константу типа money можно задать с помощью префикса $, или же использовать преобразование типов, например: 1. SELECT 1.2 num, $ 1.2 mn1, CAST ( 1.2 AS MONEY ) mn2; num mn1 mn2 1.2 1,20 1,20 Обратите внимание на запятую в качестве разделителя "рублей" и "копеек" - не точка! Преобразование к целому типу для чисел и денег выполняется по разному: в первом случае дробная часть отбрасывается, во втором происходит округление. 1. SELECT CAST ( 1.75 AS INT ) int_num, CAST ( $ 1.75 AS INT ) int_mon; int_num int_mon 1 2 Деньги таки, их просто так терять нельзя! Оператор CASE Пусть требуется вывести список всех моделей ПК с указанием их цены. При этом если модель отсутствует в продаже (ее нет в таблице РС), то вместо цены вывести текст «Нет в наличии». Список всех моделей ПК с ценами можно получить с помощью запроса: 1. SELECT DISTINCT Product.model, price 2. FROM Product LEFT JOIN 3. PC ON Product.model = PC.model 4. WHERE product.type = 'pc' ; В результирующем наборе отсутствующая цена будет заменена NULL- значением: |