лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
ram hd cd price 1 1232 500 64 5 12x 600.0 3 1233 500 64 5 12x 600.0 8 1232 450 64 8 24x 350.0 Это может оказаться полезнее, чем кажется на первый взгляд, если иметь в виду конфиденциальность информации. Например, на клиенте можно формировать запросы, которые будут оперировать такими категориями, как высоко, средне и низко оплачиваемый специалист. То есть сами критерии (оклады) будут спрятаны, скажем, в хранимой процедуре, а через параметр будет передаваться что-то типа символьной строки «средний». Предложение GROUP BY Пусть теперь мы хотим получить количество компьютеров, подходящих по RAM к каждому типу операционных систем. Тогда мы можем написать следующий запрос: 1. SELECT 2. CASE 3. WHEN ram < 64 4. THEN 'W95' 5. WHEN ram < 128 6. THEN 'W98' 7. ELSE 'W2k' 8. END Type, 9. COUNT ( * ) Qty 10. FROM PC 11. GROUP BY 12. CASE 13. WHEN ram < 64 14. THEN 'W95' 15. WHEN ram < 128 16. THEN 'W98' 17. ELSE 'W2k' 18. END ; В результате выполнения запроса получим: Type Qty W2k 5 W95 3 W98 3 Здесь мы дублируем оператор CASE в предложении SELECT, чтобы получить столбец с наименованием операционной системы. Использование оператора CASE в предложении GROUP BY позволяет в рамках одного запроса выполнять группировку по разному числу столбцов. Рассмотрим, например, следующую задачу. Для каждой комбинации скорости процессора и объема жесткого диска определить среднюю цену ПК. Для ПК со скоростью процессора менее 600 выполнять группировку только по скорости процессора. Сначала для сравнения приведем результаты группировки по одному (speed) и двум столбцам (speed, hd) соответственно. 1. SELECT speed, MAX ( hd ) max_hd, AVG ( price ) avg_price FROM pc 2. GROUP BY speed; speed max_hd avg_price 450 10 350.00 500 10 487.50 600 14 850.00 750 20 900.00 800 20 970.00 900 40 980.00 1. SELECT speed, hd max_hd, AVG ( price ) avg_price FROM pc 2. GROUP BY speed, hd 3. ORDER BY speed; speed max_hd avg_price 450 8 350.00 450 10 350.00 500 5 600.00 500 10 375.00 600 8 850.00 600 14 850.00 750 14 850.00 750 20 950.00 800 20 970.00 900 40 980.00 Поскольку в каждой группе значения hd совпадают, можно max(hd) и hd использовать равноправно. Теперь дадим решение нашей задачи: 1. SELECT speed, MAX ( hd ) max_hd, AVG ( price ) avg_price FROM pc 2. GROUP BY speed, CASE WHEN speed >= 600 THEN hd ELSE speed END 3. ORDER BY speed; speed max_hd avg_price 450 10 350.00 500 10 487.50 600 8 850.00 600 14 850.00 750 14 850.00 750 20 950.00 800 20 970.00 900 40 980.00 Когда speed >= 600, выполняется группировка по столбцам speed, hd. В противном случае группировка выглядит так: 1. GROUP BY speed, speed что эквивалентно группировке одному столбцу. Кстати, оператор CASE в последнем решении можно было написать без ELSE: 1. SELECT speed, MAX ( hd ) max_hd, AVG ( price ) avg_price FROM pc 2. GROUP BY speed, CASE WHEN speed >= 600 THEN hd END 3. ORDER BY speed; Хотя явная группировка - GROUP BY speed, NULL - будет вызывать ошибку. Предложение HAVING Пусть требуется найти красные квадраты, т.е. квадраты, окрашенные только баллонами с красной краской и суммарным количеством краски, равным 255. Речь идёт о базе данных " Окраска ". Эту задачу можно решить разными способами. Здесь мы приведём решение с использованием оператора CASE в предложении HAVING. Идея состоит в следующем. Выполняя группировку по идентификатору квадрата, будем суммировать объем краски. При этом краску красного цвета будем добавлять со знаком "+", а остальную - со знаком "-". Поскольку краски каждого цвета на квадрате не может быть больше 255 единиц, то результат, равный в точности 255 говорит о том, что вся эта краска была красного цвета, и никакой другой использовано не было. А вот и само решение: 1. SELECT b_q_id 2. FROM utb JOIN utv ON v_id=b_v_id 3. GROUP BY b_q_id 4. HAVING SUM ( CASE WHEN v_color= 'R' THEN b_vol ELSE -b_vol END ) = 255 ; Предложение ORDER BY Использование оператора CASE в предложении ORDER BY позволяет выполнить более сложную сортировку, чем та, которая допускается при использовании сортировки по набору столбцов. При сортировке по столбцу ram можно выполнить ее по возрастанию или по убыванию. Если же мы хотим вывести сначала средние модели, то есть те, которые отвечают W98, потом высшие (W2k), а уже потом низшие (W95), то можно поступить так: 1. SELECT * 2. FROM PC 3. ORDER BY CASE 4. WHEN ram <= 32 5. THEN '3-W95' 6. WHEN ram <= 64 7. THEN '1-W98' 8. ELSE '2-W2k' 9. END ; Цифры перед названием ОС проставлены в соответствии с желательным порядком сортировки. В противном случае, упорядочение текстовых значений по возрастанию будет следующим: W2k, W95, W98. Вот результат вышеприведенного запроса (заголовок столбца сортировки выделен прописными буквами): code model speed RAM hd cd price 3 1233 500 64 5 12x 600 1 1232 500 64 5 12x 600 8 1232 450 64 8 24x 350 2 1121 750 128 14 40x 850 4 1121 600 128 14 40x 850 5 1121 600 128 8 40x 850 6 1233 750 128 20 50x 950 11 1233 900 128 40 40x 980 12 1233 800 128 20 50x 970 7 1232 500 32 10 12x 400 9 1232 450 32 10 24x 350 10 1260 500 32 10 12x 350 Есть еще более интересная возможность сортировки, а именно, сортировать по разным столбцам в зависимости от значения в некотором поле. Пусть, например, в группе W95 мы хотим выполнить сортировку по столбцу speed, в группе W98 — по столбцу hd, в группе W2k — по столбцу price. То есть в каждой группе, характеризуемой ОС описанными выше критериями, нам нужно выполнить сортировку по разным столбцам. Эту, на первый взгляд непростую задачу, решает простой запрос с оператором CASE в предложении ORDER BY: 1. SELECT * 2. FROM PC 3. ORDER BY ram, 4. CASE 5. WHEN ram <= 32 6. THEN speed 7. WHEN ram <= 64 8. THEN hd 9. ELSE price 10. END ; Вот результат этого запроса: code model speed ram hd cd price 9 1232 450 32 10 24x 350 10 1260 500 32 10 12x 350 7 1232 500 32 10 12x 400 1 1232 500 64 5 12x 600 3 1233 500 64 5 12x 600 8 1232 450 64 8 24x 350 2 1121 750 128 14 40x 850 4 1121 600 128 14 40x 850 5 1121 600 128 8 40x 850 6 1233 750 128 20 50x 950 12 1233 800 128 20 50x 970 11 1233 900 128 40 40x 980 Еще о NULL- значениях Смысл NULL-значения — это отсутствие информации или неприменимость данного атрибута в данном кортеже. Можно спросить: «Зачем иметь атрибут, если его значение неприменимо?». Ответ лежит в области моделирования предметной области. Рассмотрим, например, схему базы данных «Компьютеры». Она представляет собой реляционную модель связи «тип- супертип». Сущностями предметной области здесь являются модели компьютерной продукции (супертип), при этом каждый тип продукции (ПК, портативный компьютер или принтер) отображается в отдельную таблицу со связями «многие к одному» с таблицей Product. Такая модель обеспечивает высокую степень нормализации (3НФ). Однако это не единственный способ. Можно было бы хранить всю информацию в одной таблице, которая содержала бы как общие для всех моделей атрибуты (например, цена — price), так и атрибуты, которые имеют смысл только для моделей определенного типа (например, цвет — color — для характеристики принтеров). Для такой схемы NULL- значение является вполне оправданным именно в смысле неприменимости характеристики, то есть NULL в столбце color, будет говорить нам о том, что эта характеристика не имеет отношения, скажем, к моделям ПК. Обратимся теперь ко второй ипостаси NULL-значений — отсутствию информации. Если мы решим отказаться от использования NULL-значений, то должны предложить альтернативу. Естественным путем является применение значения по умолчанию, которое будет подставлено в соответствующий столбец при отсутствии информации. Следует заметить, что таких значений по умолчанию должно быть, по меньшей мере, столько, сколько различных типов данных поддерживается СУБД (целые, строки, дата-время, …). Рассмотрим, например, таблицу Laptop и поле price (цена). Пусть предметная область такова, что на момент ввода информации о моделях портативных компьютеров их цена не всегда известна. При выборе значения по умолчанию мы должны ограничиться только значениями, допустимыми для поля price. Тип данных для столбца (money) заставляет нас выбирать только из числовых значений, совместимых с данным типом и проверочными ограничениями (ограничение типа CHECK), наложенными на допустимые значения для этого столбца. Любое положительное значение в качестве значения по умолчанию будет вызывать путаницу, так как невозможно будет отличить «истинное» значение цены от заменителя отсутствующей цены. Поэтому следует выбрать нуль или любое отрицательное значение. А теперь поговорим о том, чем плоха такая замена. Для примера рассмотрим информацию о моделях 1298, имеющихся в таблице Laptop. Чтобы познакомиться с данными, выполним запрос: 1. SELECT * 2. FROM Laptop 3. WHERE model = 1298 ; Вот результаты выполнения этого запроса: code model speed ram hd price screen 1 1298 350 32 4 700.0 11 4 1298 600 64 10 1050.0 15 6 1298 450 64 10 950.0 12 Рассмотрим задачу получения средней цены модели 1298. Пока все цены известны решение этой задачи не вызывает никаких сомнений: 1. SELECT model, AVG ( price ) avg_price 2. FROM Laptop 3. WHERE model = 1298 4. GROUP BY model; model avg_price 1298 900.0 Пусть теперь цена модели с кодом 4 неизвестна. Если, как было решено ранее, мы будем заменять неизвестное значение, скажем, нулем (UPDATE Laptop SET price = 0 WHERE code=4), то получим заведомо неверное среднее значение цены — 550.0 Если же мы будем использовать NULL-значение, то результат будет вполне правильным — 825.0, так как NULL-значения будут игнорироваться при группировке, в результате чего среднее значение будет вычисляться только по моделям с известной ценой (то есть среднее по двум моделям). Итак, как мы постарались показать, NULL-значение является неотъемлемой особенностью реляционной модели, поэтому рекомендуем научиться корректно работать с такими значениями. Примечание: Ради объективности отсылаем вас к аргументированной критике Дейта относительно использования NULL-значений [1] . Коддом было предложено [6] два разных типа NULL-значений, соответствующих как раз тем двум аспектам их применения, о которых шла речь выше. Сравнение строк, содержащих NULL-значения Как известно, предикаты простого сравнения с NULL-значениями дают истинностное значение UNKNOWN, т.е. ни TRUE и ни FALSE, что означает "неизвестно". Поэтому не стоит удивляться, что в одних случаях, при сравнении между собой NULL-значений, они считаются равными друг другу, а других случаях - нет. Поясним сказанное на примерах. Рассмотрим соединение двух одинаковых строк, содержащих NULL- значения, по равенству всех столбцов. 1. WITH A AS ( 2. SELECT 'a' a, NULL b 3. ) 4. , B AS ( 5. SELECT 'a' a, NULL b 6. ) 7. SELECT * FROM A JOIN B ON A.a=B.a AND A.b=B.b; В этом случае соединяться будут только те строки, для которых предикат соединения есть TRUE. Поскольку предикат соединения в примере оценивается как UNKNOWN, в результате мы не получим ни одной строки. Однако пересечение запросов (так же, как объединение и разность) считает эти строки идентичными: 1. WITH A AS ( 2. SELECT 'a' a, NULL b 3. ) 4. , B AS ( 5. SELECT 'a' a, NULL b 6. ) 7. SELECT * FROM A 8. INTERSECT 9. SELECT * FROM B; a b a NULL Можно сделать вывод о том, что при горизонтальных операциях NULL- значения не считаются равными (и неравными, впрочем, тоже), а при вертикальных трактуются как равные. В частности, при группировке по столбцу, содержащему NULL-значения, последние образуют одну группу. В заключение рассмотрим несколько решений задачи определения количества принтеров с неизвестной ценой. Таблица PrinterN отличается от таблицы Printer тем, что для пары моделей цена установлена в NULL. (1) От общего числа строк отнимем число строк с известной ценой. 1. SELECT COUNT ( * ) - COUNT ( price ) qty FROM printerN; (2) Использование предиката IS NULL для подсчёта строк, для которых цена неизвестна. 1. SELECT COUNT ( * ) FROM printerN WHERE price IS NULL ; (3) Группируем по цене и отбираем группу с неизвестной ценой, используя HAVING. 1. SELECT COUNT ( * ) FROM printerN GROUP BY price HAVING price IS NULL ; Трехзначная логика и предложение Where Рассмотрим следующий пример. Пусть требуется определить корабли с неизвестным годом спуска на воду (база данных "Корабли"). Если мы напишем Решение 8.5.1 1. SELECT * 2. FROM Ships 3. WHERE launched = NULL ; то, как бы ни казалось это странным, мы не получим ни одной записи, даже если такие корабли имеются в таблице Ships (напомним, что столбец launched допускает NULL-значения) Поскольку в доступной базе данных нет кораблей с неизвестным годом спуска на воду, давайте их создадим, чтобы вы могли проверить справедливость данного утверждения: 1. SELECT * 2. FROM ( SELECT name, launched, 3. CASE 4. WHEN launched < 1940 5. THEN NULL 6. ELSE launched 7. END year 8. FROM Ships 9. ) x 10. WHERE year = NULL ; Здесь мы добавили в подзапросе столбец year, который содержит NULL, если корабль был спущен на воду до 1940 года. Итак, почему мы ничего не получили? Здесь следует вспомнить о том, что в SQL (и вообще в реляционной теории) используется трехзначная логика, то есть истинностным значением операции сравнения может быть не только TRUE (истина) и FALSE (ложь), но и UNKNOWN (неизвестно). Это обусловлено существованием NULL-значения, сравнение с которым и дает это истинностное значение. Это интуитивно понятно, если помнить, что NULL-значение служит для замены неизвестной информации. Если мы спросим: «Является ли годом спуска на воду корабля Бисмарк 1939 год»? Ответом будет: «Не знаю». Так как у нас нет информации в базе данных о годе спуска на воду этого корабля, это «не знаю» и есть UNKNOWN. Что происходит, если в предложении WHERE мы используем сравнение с NULL-значением явно или неявно (с NULL-значением в сравниваемом столбце)? Запись попадает в результирующий набор, если предикат дает истинностное значение TRUE. И все, то есть при значениях FALSE или UNKNOWN запись не попадает в результат. Именно поэтому мы ничего и не получили в приведенном выше примере, поскольку для всех строк получаем UNKNOWN. Так как же получить список кораблей с неизвестным годом спуска на воду? Для этого в стандарте SQL имеется специальный предикат IS NULL (и обратный ему IS NOT NULL). Истинностным значением этого предиката не может быть UNKNOWN, то есть год либо известен (FALSE), либо неизвестен (TRUE). Тогда для решения нашей задачи можно написать: Решение 8.5.2 1. SELECT * 2. FROM Ships 3. WHERE launched IS NULL ; Это стандарт. А что же реализации? Все сказанное выше справедливо для SQL Server. Однако это не единственная возможность. Видимо, чтобы сделать программирование на SQL более привычным для тех, кто пользуется традиционными языками программирования, можно отключить стандартную трактовку |