лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
Model price 1121 850 1232 350 1232 400 1232 600 1233 600 1233 950 1233 980 1260 350 2111 NULL 2112 NULL Чтобы заменить NULL-значения нужным текстом, можно воспользоваться оператором CASE: 1. SELECT DISTINCT product.model, 2. CASE 3. WHEN price IS NULL 4. THEN 'Нет в наличии' 5. ELSE CAST ( price AS CHAR ( 20 )) 6. END price 7. FROM Product LEFT JOIN 8. PC ON Product.model = PC.model 9. WHERE product.type = 'pc' ; Оператор CASE в зависимости от указанных условий возвращает одно из множества возможных значений. В нашем примере условием является проверка на NULL. Если это условие выполняется, то возвращается текст «Нет в наличии», в противном случае (ELSE) возвращается значение цены. Здесь есть один принципиальный момент. Поскольку результатом оператора SELECT всегда является таблица, то все значения любого столбца должны иметь один и тот же тип данных (с учетом неявного приведения типов). Поэтому мы не можем наряду с ценой (числовой тип) выводить символьную константу. Вот почему к полю price применяется преобразование типов, чтобы привести его значения к символьному представлению. В результате получим: model price 1121 850 1232 350 1232 400 1232 600 1233 600 1233 950 1233 980 1260 350 2111 Нет в наличии 2112 Нет в наличии Оператор CASE может быть использован в одной из двух синтаксических форм записи: 1-я форма: 1. CASE <проверяемое выражение> 2. WHEN <сравниваемое выражение 1 > 3. THEN <возвращаемое значение 1 > 4. … 5. WHEN <сравниваемое выражение N> 6. THEN <возвращаемое значение N> 7. [ ELSE <возвращаемое значение> ] 8. END 2-я форма: 1. CASE 2. WHEN <предикат 1 > 3. THEN <возвращаемое значение 1 > 4. … 5. WHEN <предикат N> 6. THEN <возвращаемое значение N> 7. [ ELSE <возвращаемое значение> ] 8. END Все предложения WHEN должны иметь одинаковую синтаксическую форму, то есть нельзя смешивать первую и вторую формы. При использовании первой синтаксической формы условие WHEN удовлетворяется, как только значение проверяемого выражения станет равным значению выражения, указанного в предложении WHEN. При использовании второй синтаксической формы условие WHEN удовлетворяется, как только предикат принимает значение TRUE. При удовлетворении условия оператор CASE возвращает значение, указанное в соответствующем предложении THEN. Если ни одно из условий WHEN не выполнилось, то будет использовано значение, указанное в предложении ELSE. При отсутствии ELSE, будет возвращено NULL-значение. Если удовлетворены несколько условий, то будет возвращено значение предложения THEN первого из них, так как остальные просто не будут проверяться. В приведенном выше примере была применена вторая форма оператора CASE. Заметим, что для проверки на NULL стандарт предлагает более короткую форму — оператор COALESCE. Он имеет произвольное число параметров и возвращает значение первого из них, отличного от NULL. Для двух параметров оператор COALESCE(A, B) эквивалентен следующему оператору CASE: 1. CASE 2. WHEN A IS NOT NULL 3. THEN A 4. ELSE B 5. END Решение рассмотренного выше примера при использовании оператора COALESCE можно переписать следующим образом: 1. SELECT DISTINCT Product.model, 2. COALESCE ( CAST ( price AS CHAR ( 20 )) , 'Нет в наличии' ) price 3. FROM Product LEFT JOIN 4. PC ON Product.model = PC.model 5. WHERE Product.type = 'pc' ; Применение первой синтаксической формы оператора CASE можно продемонстрировать на следующем примере. Пример 5.10.1 Вывести все имеющиеся модели ПК с указанием цены. Отметить самые дорогие и самые дешевые модели. 1. SELECT DISTINCT model, price, 2. CASE price 3. WHEN ( SELECT MAX ( price ) 4. FROM PC 5. ) 6. THEN 'Самый дорогой' 7. WHEN ( SELECT MIN ( price ) 8. FROM PC 9. ) 10. THEN 'Самый дешевый' 11. ELSE 'Средняя цена' 12. END comment 13. FROM PC 14. WHERE price IS NOT NULL 15. ORDER BY price; В результате выполнения запроса получим: model price comment 1232 350.0 Самый дешевый 1260 350.0 Самый дешевый 1232 400.0 Средняя цена 1232 600.0 Средняя цена 1233 600.0 Средняя цена 1121 850.0 Средняя цена 1233 950.0 Средняя цена 1233 980.0 Самый дорогой Оператор CASE может быть использован не только в предложении SELECT. Здесь вы можете найти другие примеры его использования. Рассмотрим еще несколько примеров. Посчитать количество рейсов из Ростова в Москву, и количество рейсов, выполняемых в остальные города. Здесь мы можем воспользоваться вычисляемым столбцом, по значениям которого будем выполнять группировку: 1. SELECT flag, COUNT ( * ) qty FROM 2. ( SELECT CASE WHEN town_to = 'Moscow' THEN 'Moscow' ELSE 'Other' END flag 3. FROM Trip 4. WHERE town_from= 'Rostov' 5. ) X 6. GROUP BY flag; flag qty Moscow 4 Other 2 Посчитать общее количество рейсов из Ростова и количество рейсов, пунктом назначения которых не является Москва. В этой задаче тоже требуется выполнить агрегацию по двум выборкам, при этом одна из выборок является подмножеством второй. Поэтому здесь напрямую не подойдёт вычисляемый столбец, по которому можно выполнить группировку. Это годилось для решения предыдущей задачи, когда множество делилось на собственные непересекающиеся подмножества, по каждому из которых требовалось выполнить агрегацию. Для решения данной задачи мы можем посчитать количество по всему множеству и использовать подзапрос для подсчета значений в подмножестве (второе обращение к таблице) или использовать CASE в сочетании с агрегатной функцией, чтобы избежать повторного чтения таблицы. Давайте посмотрим, как оценит оптимизатор эти варианты. Использование подзапроса 1. SELECT COUNT ( * ) total, 2. ( SELECT COUNT ( * ) FROM Trip 3. WHERE town_from= 'Rostov' AND town_to <> 'Moscow' ) non_moscow 4. FROM Trip 5. WHERE town_from= 'Rostov' ; Использование CASE с агрегатной функцией 1. SELECT COUNT ( * ) total_qty, 2. SUM ( CASE WHEN town_to <> 'Moscow' THEN 1 ELSE 0 END ) non_moscow 3. FROM Trip 4. WHERE town_from= 'Rostov' ; Результат, естественно, будет одинаков: total non_moscow 6 2 а вот стоимость второго запроса, как и ожидалось, оказалась вдвое ниже. Вы можете сравнить реальное время выполнения, если сгенерируете достаточный объём данных. Второй вариант можно записать более компактно, если использовать функцию NULLIF - сокращенный вариант частного случая использования CASE: 1. SELECT COUNT ( * ) total_qty, 2. COUNT ( NULLIF ( town_to, 'Moscow' )) non_moscow 3. FROM Trip 4. WHERE town_from= 'Rostov' ; Функция NULLIF возвращает NULL, если её аргументы равны, или первый аргумент в противном случае. В решении используется тот факт, что агрегатные функции не учитывают NULL-значения, которые появляются в аргументе функции COUNT тогда, когда город прибытия равен 'Moscow'. Начиная с версии 2012, в SQL Server появилась функция IIF, хорошо известная тем, кто использует VBA. Эта функция является альтернативой выражению CASE в MS Access и имеет следующий синтаксис: 1. IIF ( <условие>, <выражение, если условие истинно>, <выражение, если условие не истинно> ) Функция возвращает результат вычисления выражения из второго аргумента, если условие есть TRUE; в противном случае возвращается результат вычисления выражения из третьего аргумента. Таким образом, функция 1. IIF ( condition, expression_1, expression_2 ) эквивалентна следующему выражению CASE: 1. CASE WHEN condition THEN expression_1 ELSE expression_2 END С помощью функции IIF мы можем переписать решение первой задачи следующим образом: 1. SELECT DISTINCT product.model, 2. IIF ( price IS NULL , N 'Нет в наличии' , CAST ( price AS CHAR ( 20 ))) price 3. FROM Product LEFT JOIN 4. PC ON Product.model = PC.model 5. WHERE product.type = 'PC' ; В том случае, если вариантов ветвления больше двух, можно использовать вложенные функции IIF. Например, для решения задачи 5.10.1 можно использовать такой запрос: 1. SELECT DISTINCT model, price, 2. IIF ( price= ( SELECT MAX ( price ) FROM PC ) ,N 'Самый дорогой' , 3. IIF ( price= ( SELECT MIN ( price ) FROM PC ) , N 'Самый дешевый' ,N 'Средняя цена' )) comment 4. FROM PC 5. ORDER BY price; Если так и дальше пойдет, то скоро в T-SQL появится оператор SWITCH. Операторы модификации данных Язык манипуляции данными (DML — Data Manipulation Language) помимо оператора SELECT, осуществляющего извлечение информации из базы данных, включает операторы, изменяющие состояние данных. Этими операторами являются: оператор функция INSERT Добавление записей (строк) в таблицу БД UPDATE Обновление данных в столбце таблицы БД DELETE Удаление записей из таблицы БД Вы можете попрактиковаться в написании этих операторов на странице с упражнениями по DML на сайте SQL-EX.RU Оператор INSERT Оператор INSERT вставляет новые записи в таблицу. При этом значения столбцов могут представлять собой литеральные константы, либо являться результатом выполнения подзапроса. В первом случае для вставки каждой строки используется отдельный оператор INSERT; во втором случае будет вставлено столько строк, сколько возвращается подзапросом. Синтаксис оператора следующий: 1. INSERT INTO <имя таблицы> [( <имя столбца>,... )] 2. { VALUES ( <значение столбца>,… )} 3. | <выражение запроса> 4. | { DEFAULT VALUES } Как видно из представленного синтаксиса, список столбцов не является обязательным (об этом говорят квадратные скобки в описании синтаксиса). В том случае, если он отсутствует, список вставляемых значений должен быть полный, то есть обеспечивать значения для всех столбцов таблицы. При этом порядок значений должен соответствовать порядку, заданному оператором CREATE TABLE для таблицы, в которую вставляются строки. Кроме того, эти значения должны относиться к тому же типу данных, что и столбцы, в которые они вносятся. В качестве примера рассмотрим вставку строки в таблицу Product, созданную следующим оператором CREATE TABLE: 1. CREATE TABLE product 2. ( 3. maker char ( 1 ) NOT NULL , 4. model varchar ( 4 ) NOT NULL , 5. type varchar ( 7 ) NOT NULL 6. ) ; Пусть требуется добавить в эту таблицу модель ПК 1157 производителя B. Это можно сделать следующим оператором: 1. INSERT INTO Product 2. VALUES ( 'B' , 1157 , 'PC' ) ; Если задать список столбцов, то можно изменить «естественный» порядок их следования: 1. INSERT INTO Product ( type, model, maker ) 2. VALUES ( 'PC' , 1157 , 'B' ) ; Казалось бы, это совершенно излишняя возможность, которая делает конструкцию только более громоздкой. Однако она становится выигрышной, если столбцы имеют значения по умолчанию. Рассмотрим следующую структуру таблицы: 1. CREATE TABLE product_D 2. ( 3. maker char ( 1 ) NULL , 4. model varchar ( 4 ) NULL , 5. type varchar ( 7 ) NOT NULL DEFAULT 'PC' 6. ) ; Отметим, что здесь значения всех столбцов имеют значения по умолчанию (первые два — NULL, а последний столбец — type — PC). Теперь мы могли бы написать: 1. INSERT INTO Product_D ( model, maker ) 2. VALUES ( 1157 , 'B' ) ; В этом случае отсутствующее значение при вставке строки будет заменено значением по умолчанию — PC. Заметим, что если для столбца в операторе CREATE TABLE не указано значение по умолчанию и не указано ограничение NOT NULL, запрещающее использование NULL в данном столбце таблицы, то подразумевается значение по умолчанию NULL. Возникает вопрос: а можно ли не указывать список столбцов и, тем не менее, воспользоваться значениями по умолчанию? Ответ положительный. Для этого нужно вместо явного указания значения использовать зарезервированное слово DEFAULT: 1. INSERT INTO Product_D 2. VALUES ( 'B' , 1158 , DEFAULT ) ; Поскольку все столбцы имеют значения по умолчанию, для вставки строки со значениями по умолчанию можно было бы написать: 1. INSERT INTO Product_D 2. VALUES ( DEFAULT , DEFAULT , DEFAULT ) ; Однако для этого случая предназначена специальная конструкция DEFAULT VALUES (см. синтаксис оператора), с помощью которой вышеприведенный оператор можно переписать в виде 1. INSERT INTO Product_D DEFAULT VALUES ; Заметим, что при вставке строки в таблицу проверяются все ограничения, наложенные на данную таблицу. Это могут быть ограничения первичного ключа или уникального индекса, проверочные ограничения типа CHECK, ограничения ссылочной целостности. В случае нарушения какого-либо ограничения вставка строки будет отклонена. Рассмотрим теперь случай использования подзапроса. Пусть нам требуется вставить в таблицу Product_D все строки из таблицы Product, относящиеся к моделям персональных компьютеров (type = ‘PC’). Поскольку необходимые нам значения уже имеются в некоторой таблице, то формирование вставляемых строк вручную, во-первых, является неэффективным, а, во-вторых, может допускать ошибки ввода. Использование подзапроса решает эти проблемы: 1. INSERT INTO Product_D 2. SELECT * 3. FROM Product 4. WHERE type = 'PC' ; Использование в подзапросе символа «*» является в данном случае оправданным, так как порядок следования столбцов является одинаковым для обеих таблиц. Если бы это было не так, следовало бы применить список столбцов либо в операторе INSERT, либо в подзапросе, либо в обоих местах, который приводил бы в соответствие порядок следования столбцов: 1. INSERT INTO Product_D ( maker, model, type ) 2. SELECT * 3. FROM Product 4. WHERE type = 'PC' ; или 1. INSERT INTO Product_D 2. SELECT maker, model, type 3. FROM Product 4. WHERE type = 'PC' ; или 1. INSERT INTO Product_D ( maker, model, type ) 2. SELECT maker, model, type 3. FROM Product 4. WHERE type = 'PC' ; Здесь, также как и ранее, можно указывать не все столбцы, если требуется использовать имеющиеся значения по умолчанию, например: 1. INSERT INTO Product_D ( maker, model ) 2. SELECT maker, model 3. FROM Product 4. WHERE type = 'PC' ; В данном случае в столбец type таблицы Product_D будет подставлено значение по умолчанию PC для всех вставляемых строк. Отметим, что при использовании подзапроса, содержащего предикат, будут вставлены только те строки, для которых значение предиката равно TRUE (не UNKNOWN!). Другими словами, если бы столбец type в таблице Product допускал бы NULL-значение, и это значение присутствовало бы в ряде строк, то эти строки не были бы вставлены в таблицу Product_D. Преодолеть ограничение на вставку одной строки в операторе INSERT при использовании конструктора строки в предложении VALUES позволяет искусственный прием использования подзапроса, формирующего строку с предложением UNION ALL. Так если нам требуется вставить несколько строк при помощи одного оператора INSERT, можно написать: 1. INSERT INTO Product_D 2. SELECT 'B' AS maker, 1158 AS model, 'PC' AS type 3. UNION ALL 4. SELECT 'C' , 2190 , 'Laptop' 5. UNION ALL 6. SELECT 'D' , 3219 , 'Printer' ; Использование |