База данных. ЭУМК Базы данных. Пояснительная записка Теоретический раздел Практический раздел Раздел контроля знаний Вспомогательный раздел Пинск
 Скачать 2.33 Mb.
|
|
Пример 7. Создать запрос, который возвращает названия фирм, не имеющих факса: SELECT TITLE, FAX FROM CLIENTS WHERE FAX IS NULL Запрос должен вернуть названия четырёх фирм: МАП Инфо, Дамодара-Сервис, IMC Computers, Stop. Выполним теперь оператор с использованием IS NOT NULL: SELECT Title, Fax FROM CLIENTS WHERE FAX IS NOT NULL Результат должен будет содержать следующие строки: SELECT Title, Fax FROM CLIENTS WHERE FAX IS NOT NULL TITLE FAX Омикс 0921-12 34 67 Белкантон (5)555-7293 E-Life 7675-3426 Bepca 089-0877451 Comtris (2)283-3397 Net Line (21)555-8765 NTTs (5) 555-1948 Интеллекте 2967 3333 ZS (9)331-7256 Медиа-софт 035-640231 Эликон-М (907)555-2880 Соло 0522-556722 Olymp (91)745 6210 Oki (11)555-2168 Сравнение с диапазоном и списком значений В предыдущей секции были рассмотрены операторы, которые выполняют сравнение с единственным значением, в то время как операторы BETWEEN и IN сравнивают искомое значение со множеством заданных значений. Оператор BETWEEN проверяет попадание искомого значения в заданный диапазон. Пример 8. Применение оператора BETWEEN. Создать запрос, который возвращает все фамилии, начинающиеся с букв, расположенных между "А" и "К". Заметим, что запрос не включает фамилии, начинающиеся с последней буквы диапазона - "К". Это происходит потому, что оператору BETWEEN удовлетворяют значения меньшие или равные конечному значению диапазона. Поэтому фамилия, начинающаяся с "К" и содержащая ещё другие буквы, будет больше, чем "К". Инструкция должна принять вид: SELECT SURNAME, NAME FROM EMPLOYEES WHERE SURNAME BETWEEN 'А' AND 'К' Результирующий набор должен будет содержать: SURNAME NAME Белова Мария Пример 9. Применение оператора BETWEEN. Создать запрос, который возвращает наименования товаров, цена которых лежит в пределах между 14 и 32 включительно. SELECT CATEGORY, SUPPLIER, PRICE FROM GOODS WHERE PRICE BETWEEN 14 AND 32 ORDER BY PRICE Результат должен состоять из 11 строк, включающих нижнюю и верхнюю границы диапазона. CATEGORY SUPPLIER PRICE Корпуса и блоки питания SV-Trading 14 Модули памяти Uni 15 Устройства ввода и указания Гвин-Медиа 15 Модемы Iven 18 Модули памяти ПК Сервис 19 Сетевое оборудование DAAS 22 CD-ROM B.S.T.Group 23 CD-ROM Конструктив 25 Видеокарты Stepfor 28 Сетевое оборудование BelSoft 32 Сетевое оборудование Гвин-Медиа 32 Поиск с использованием оператора IN Оператор IN сравнивает искомые значения с одним из значений заданного списка. Значения списка должны отделяться друг от друга запятыми. Можно использовать оператор NOT для поиска значений, несовпадающих ни с одним из значений заданного списка. Пример 10. Создать запрос, который возвращает названия товаров, имеющих следующие цены: 15, 19, 32. SELECT CATEGORY, SUPPLIER, PRICE FROM GOODS WHERE PRICE IN (15,19,32) ORDER BY PRICE, CATEGORY Должен быть получен следующий результирующий набор строк: CATEGORY SUPPLIER PRICE Модули памяти Uni 15 Устройства ввода и указания Евин-Медиа 15 Модули памяти ПК Сервис 19 Сетевое оборудование BelSoft 32 Сетевое оборудование Евин-Медиа 32 Применение логических операторов AND и OR Несколько условий поиска можно объединить в одном предложении WHERE посредством использования логических операторов AND и OR. Когда два условия связываются оператором AND, то имеется в виду, что оба условия должны быть истинными для искомой строки. Пример 11. Посредством использования области критериев конструктора запросов создать запрос, который возвращает служащих, работающих на должности представителя и принятых на работу до 1 января 1994 г. Должна быть сформирована следующая инструкция: SELECT Surname, Name, Post, DateHiring, HomeTelephone FROM Employees WHERE Post = 'Представитель' AND DateHiring < '1.01.1994' В результате оператор должен вернуть одну строку: Белова Мария Представитель 1992-01-05 00:00:00. ООО (017) 555-9857 Пример 12. Посредством использования области критериев конструктора запросов создать запрос, который возвращает клиентов из Германии или Италии. Для поиска строк, удовлетворяющих хотя бы одному условию среди нескольких заданных, используют логический оператор OR. Должа быть получена следующая инструкция: SELECT Title, Address, City, Country FROM Clients WHERE (Country ='Германия' OR Country ='Италия') Результат запроса должен содержать четыре строки: Верса Берлинская пл., 43 Мюнхен Германия Медиа-софт ул Людовика, 22 Бергамо Италия Stop Тачерстрасс, 10 Кюневачьд Германия Соло ул. Провинциальная, 124 Реджио-Эмшио Италия Управление порядком вычисления условий Когда вводятся сложные условия поиска, следует быть уверенным в правильности порядка выполнения этих условий. Предположим, что мы хотим выбрать служащих, принятых на работу до 1993 или после 1993 и родившихся до 1960 года. Пример 13. Неудачная попытка выполнения составного условия. Создайте и выполните следующий оператор: SELECT SurName, Name, Post, DateBirth, DateHiring FROM Employees WHERE (DateHiring < CONVERT(DATETIME, '1993-01-01 00:00:00', 102)) OR (DateHiring > CONVERT(DATETIME, '1993-12-31 00:00:00', 102)) AND (DateBirth < CONVERT(DATETIME, '1960-01-01 00:00:00', 102)) Можно увидеть, что в результате присутствуют только принятые на работу служащие моложе 1960 года: Белова Мария Представитель 08.12.1968 01.05.1992 Крылова Анна Внутренний координатор 09.01.1958 05.03.1994 Этот запрос возвращает неожиданный результат, потому что логический оператор AND имеет более высокий приоритет, чем оператор OR. Это значит, что выражение с оператором AND выполняется раньше выражения с оператором OR. Пример 14. Успешная попытка выполнения составного условия. Для изменения нормального приоритета выполнения операций используют скобки. В нижеследующем упражнении скобки размещаются вокруг двух дат приема на работу, чтобы они проверялись оператором AND, как единое целое. Выполним предыдущий оператор, но с правильной расстановкой скобок: SELECT SurName, Name, Post, DateBirth, DateHiring FROM Employees WHERE ((DateHiring < CONVERT(DATETIME, '1993- 01-01 00:00:00', 102)) OR (DateHiring > CONVERT(DATETIME, '1993-12-31 00:00:00', 102))) AND (DateBirth < CONVERT(DATETIME, '1960-01-01 00:00:00', 102)) Теперь мы получили желаемый результат: Крылова Анна Внутренний координатор 09.01.1958 05.03.1994 Примечание. Учёт приоритета выполнения операций важен не только при использовании операторов AND и OR, все операции имеют определённый приоритет, который управляет порядком их выполнения. Этот порядок определяется в описании любого языка программирования и, в частности, языка SQL. В случае неуверенности в правильности выполнения операций следует применять скобки. 4 Контрольные вопросы 1) Что такое условие поиска и где оно применяется? 2) Объясните правила применения шаблонов в условиях поиска. 3) Как выполняется проверка на неопределённое значение? 4) Как формулируется условие поиска для проверки на диапазон допустимых значений? 5) Как формулируется условие поиска для проверки на перечень конкретных значений? 6) Объясните правила управления порядком вычисления логических условий. ЛАБОРАТОРНАЯ РАБОТА № 5 ИСПОЛЬЗОВАНИЕ ПОДЗАПРОСОВ В ЯЗЫКЕ SQL 1 Цель работы 1) Научиться использовать подзапросы в составе инструкций Transact-SQL. 2) Научиться применять кванторы в условиях поиска данных в базе данных. 3) Научиться применять в запросах агрегатные функции. 2 Задание 1) Изучить правила и условия применения подзапросов в составе инструкций Transact-SQL и выполнить предложенные примеры. 2) Изучить правила и условия применения кванторов и выполнить предложенные примеры. 3) Изучить правила и условия применения в запросах агрегатных функций и выполнить предложенные примеры. 3 Порядок выполнения работы 3.1 Использование подзапросов 3.2 Применение кванторов в подзапросах 3.3 Применение агрегатных функций 3.4 Группирование и упорядочение результата запроса 3.4.1 Применение предложения ORDER BY 3.4.2 Применение предложения GROUP BY 3.4.3 Применение предложения HAVING 3.1 Использование подзапросов Подзапросы являются специальным случаем предложения WHERE, но в силу важности этого инструмента они заслуживают отдельного обсуждения. Вспомним, что в предложении WHERE записывается имя столбца, оператор сравнения и значение, с которым осуществляется сравнение. SQL сервер сравнивает содержимое столбца с заданным значением посредством указанного оператора. Вместо значения, с которым производится сравнение в предложении WHERE, можно использовать оператор SELECT. Внутреннее предложение SELECT называют подзапросом. SQL сервер выполняет подзапрос и использует его результат в качестве значения для сравнения в предложении WHERE. Предположим, например, что мы хотим выбрать список всех товаров фирмы, которая поставляет принтеры Lexmark. Без использования подзапроса вначале нужно определить фирму: SELECT Supplier FROM Goods WHERE Mark='Lexmark Z35' Этот запрос вернёт "SV-Trading". Используя этот результат можно сформировать второй запрос, чтобы выбрать все товары фирмы "SV-Trading'': SELECT Mark FROM Goods WHERE Supplier='SV-Trading'; Использование подзапроса позволяет объединить оба запроса в один оператор. Пример 1. Выполнить подзапрос, возвращающий единичное значение. SELECT Mark FROM Goods WHERE Supplier = (SELECT Supplier FROM Goods WHERE Mark='Lexmark Z35') В этом случае подзапрос возвращает единственное значение "SV-Trading", в качестве значения, с которым будет осуществляться сравнение в предложении WHERE. Подзапрос должен вернуть единственное значение, поскольку в предложении WHERE осуществляется проверка на знак равенства ("="). В противном случае запрос сгенерирует ошибку. Результирующий набор для обсуждаемого оператора будет состоять из трех наименований товаров. Подзапросы с множественным результатом Если подзапрос возвращает более одного значения, то содержащее его предложение WHERE должно использовать оператор, осуществляющий сравнение с несколькими значениями. Оператор IN является именно таким оператором. Пример 2. Выполнить следующий пример, в котором выбирается название товара и фирмы, заказавшей какой-либо товар фирмы «SV-Tradingx. В этом примере используется подзапрос, который возвращает все товары фирмы «SV- Trading». Главный запрос, в свою1 очередь, выбирает клиента и проверяет, является ли он клиентом «SV-Trading». SELECT Client, Goods FROM Orders, Ordered WHERE Goods IN (SELECT Mark FROM Goods WHERE Supplier='SV-Trading') AND Orders.CodeOrder=Ordered.CodeOrder Результирующий набор должен выглядеть следующим образом: Client Goods Соло Lexmark Z35 3.2 Применение кванторов в подзапросах Нижеследующая таблица 5.1 обобщает кванторы, которые сравнивают выражение в левой части условия с результатом выполнения подзапроса, содержащегося в правой части. Таблица 5.1 - Классификация кванторов в SQL Server Оператор Действие All Принимает значение "истина", если сравнение возвращает истину для всех результирующих значений подзапроса. ANY или SOME Принимает значение "истина", если сравнение возвращает истину хотя бы для одного результирующего значения подзапроса. EXISTS Определяет, существует ли хотя бы одно значение в результате подзапроса. Пример 1. Применение ALL. Предположим, что мы хотим найти товары, которые дороже всех товаров фирмы «CD-Life». Введём следующий запрос: SELECT Supplier, Mark, Price FROM Goods WHERE Price > ALL(SELECT Price FROM Goods WHERE Supplier='CD-Life') Результат должен выглядеть следующим образом: Supplier Mark Price BelSoft Canon LBP810 185 DAAS Samsung 550B 150 Q-Senter Intel P4 1700MHz Box 137 Ситипринт Samtron 76E 180 Этот пример использует квантор ALL. Значение столбца Price в каждой строке таблицы проверяется для каждого значения подзапроса. Если цена больше всех значений подзапроса, то строка помещается в результирующий набор. Пример 2. Применение ANY, EXISTS. Вместо проверки условия для всех значений подзапроса, запрос можно переписать таким образом, чтобы условие выполнялось, по крайней мере, для одного значения: SELECT Supplier, Mark, Price FROM Goods WHERE Price > ANY(SELECT Price FROM Goods WHERE Supplier='CD-Life') Этот оператор должен вернуть 14 строк, в которых цена больше хотя бы одного значения подзапроса. Ключевое слово ANY является синонимом SOME. Они являются взаимозаменяемыми. Ещё одним квантором является EXISTS. Он осуществляет проверку на существование хотя бы одной строки, удовлетворяющей условиям подзапроса, и возвращает соответственно TRUE или FALSE, даже если строки содержат значения NULL. 3.3 Применение агрегатных функций SQL содержит агрегатные функции, которые вычисляют единичное значение на основе группы значений. Группой значений могут являться данные определённого столбца для заданного набора строк. Агрегатная функция может использоваться в предложении SELECT или где угодно вместо значения в операторе SELECT. Нижеследующая таблица 5.2 перечисляет некоторые агрегатные функции, поддерживаемые SQL сервером. Таблица 5.2 - Агрегатные функции Функция Действия AVG( [ ALL | DISTINCT ] value) Возвращает среднее значение поля для группы строк. COUNT ([ ALL ! DISTINCT ] value | * ) Осуществляет подсчет количества строк, при этом в полях, входящих в value, игнорируется значение NULL. M1N([ ALL j DISTINCT ] value) Возвращает минимальное значение в столбце для группы строк. MAX([ ALL | DISTINCT] value) Возвращает максимальное значение в столбце дня группы строк. SUM([ ALL | DISTINCT ] value) Возвращает сумму всех значений value для группы строк. Пример 1. Предположим, что мы хотим узнать, сколько имеется различных поставщиков в таблице Goods. Введём следующий оператор: SELECT COUNT(Supplier) FROM Goods В качестве результата будет выведено одно значение - 45. Однако это не тот результат, который нам нужен, поскольку он включает повторяющиеся значения. Чтобы подсчитать только уникальные названия поставщиков следует использовать ключевое слово DISTINCT: SELECT COUNT (DISTINCT Supplier) FROM Goods Будет получен другой правильный результат. COUNT 19 Замечание. Полученное число поставщиков равно количеству записей в таблице Suppliers. Пример 2. Вычислить среднюю стоимость товаров в Goods: SELECT AVG(Price) FROM Goods Результат будет иметь следующий вид: AVG 45,568000 Пример 3. В одном операторе SELECT можно применять несколько агрегатных функций. Введём следующий оператор, который подсчитывает число служащих и сотрудника с наибольшим стажем: SELECT COUNT(Surname), MIN(DateHiring) FROM Employees Результат будет иметь вид: COUNT M1N 2 1992-01-05 Замечание. Если некоторое значение, вовлечённое в подсчёт агрегатной функцией, есть NULL или является неизвестным, то данная строка игнорируется целиком, чтобы избежать фатальной ошибки. Например, если вычисляется среднее значение для пятидесяти строк, десять из которых содержат значение NULL, то в действительности это значение будет являться средним значением. для сорока строк. Пример 3. Чтобы увидеть, как агрегатная функция игнорирует строки, содержащие значения NULL выполним следующий тест. SELECT FAX FROM SUPPLIERS Полученный результат состоит из 19 строк, 11 из которых равны NULL. Подсчитаем число значений в столбце Fax: SELECT COUNT(Fax) FROM Suppliers Результат будет содержать число восемь, а не 19. 3.4 Группирование и упорядочение результата запроса 3.4.1 Применение предложения ORDER BY Строки таблиц базы данных не хранятся в каком-либо определённом порядке. После выполнения запроса можно обнаружить, что порядок следования строк нас не устраивает. Предложение ORDER BY позволяет нам указать желаемый порядок следования строк в результате запроса. Также можно применить предложение GROUP BY. чтобы сгруппировать результаты агрегатных функций. Для сортировки можно использовать один или более столбцов посредством указания имён или порядковых номеров. Предложение ORDER BY имеет следующий синтаксис: ORDER BY [col_name | int] [ASCfENDING] | DESCENDING]] [,...] Обратим внимание на то, что можно определить несколько столбцов, на основании которых будет осуществляться сортировка, а также на то, что ссылаться на столбцы можно не только по их именам, но и по порядковым номерам их следования в предложении SELECT. По умолчанию SQL сервер использует возрастающий порядок (ASCENDING), однако мы можем определить и убывающий (DESCENDING) порядок. Пример 1. Выполним следующий запрос: SELECT Mark, Price FROM Goods WHERE Price > 70 Здесь не определен какой-либо порядок вывода результирующих строк, и результат будет иметь следующий вид: Mark Price HDD Seagate Baracuda 4 40Gb 87 HDD IBM 60Gb 79 Canon LBP810 185 Samsung 550B 150 Epson EPL520 90 Intel P4 1700MHz Box 137 Samtron 76E 180 Asus A7S333 86 TV-tuner Aver MediaTV Studio 75 Пример 2. Выполним тот же самый запрос, но с сортировкой по столбцу Mark: SELECT Mark, Price FROM Goods WHERE Price>70 ORDER BY Mark Обратим внимание, что, результирующий набор отсортирован по столбцу Mark в алфавитном порядке по возрастанию. Пример 3. Упорядочим теперь результирующий набор по столбцу Price таблицы Goods: SELECT Mark, Price FROM Goods WHERE Price>70 ORDER BY Price |