 |
Задание для индивидуальной работы 4
|
|
|
|
Напишите и отладьте сценарий, который вставляет по несколько строк в каждую таблицу вашей базы данных.
DQL. Запросы
DQL (Data Query Language) – язык запросов, составная часть SQL. Состоит из единственной команды SELECT. По поводу этой команды написаны целые книги, здесь мы кратко на примерах рассмотрим ее основные возможности. За более подробной информацией обращайтесь к MSDN и справочной литературе.
Обязательные ключевые слова команды – SELECT и FROM.
Выборка из одной таблицы
Тривиальная выборка всех полей и всех строк одной таблицы.
Получить полную информацию обо всех предприятиях:
SELECT * FROM k_firm
Выбор отдельных полей таблицы.
Получить названия и адреса всех предприятий:
SELECT firm_name, firm_addr FROM k_firm
Результат:
firm_name firm_addr
----------------------
Альфа Москва
Бета Казань
Гамма Париж
Дельта Лондон
Омега Токио
Поля выборки можно переименовывать. Если новое название состоит из нескольких слов, помещайте его в двойные кавычки.
SELECT firm_name AS "Название предприятия",
firm_addr AS "Адрес предприятия"
FROM k_firm
Результат:
Название предприятия Адрес предприятия
-----------------------------------------------------------Альфа Москва
Бета Казань
Гамма Париж
Дельта Лондон
Омега Токио
В списке полей выборки можно использовать выражения. В этом случае часто приходится преобразовывать данные из одного типа в другой с помощью функции CONVERT. Строковые константы следует помещать в одинарные кавычки. Операция + означает сцепление строк.
Распечатать информацию о счетах:
SELECT 'Счет № '+CONVERT(CHAR(6),bill_num)+
' от '+ CONVERT(CHAR(12),bill_date)+
' на сумму '+CONVERT(CHAR(9),bill_sum)
FROM k_bill
Результат:
------------------------------------------------
Счет № 1 от ноя 12 2011 на сумму 1000
Счет № 2 от дек 12 2011 на сумму 2000
|
|
|
Счет № 3 от янв 12 2012 на сумму 2000
Счет № 4 от дек 12 2011 на сумму 6000
Счет № 5 от янв 12 2012 на сумму 2000
Счет № 6 от янв 12 2012 на сумму 2500
Счет № 7 от дек 12 2011 на сумму 1500
Счет № 8 от дек 12 2011 на сумму 1200
Счет № 9 от янв 12 2012 на сумму 10000
Для того чтобы исключить дубликаты строк, нужно использовать ключевое слово DISTINCT.
Напечатать список городов, в которых находятся предприятия-клиенты:
SELECT DISTINCT firm_addr FROM k_firm
Результат:
firm_addr
----------
Казань
Лондон
Москва
Париж
Токио
Использование условий отбора
Для выбора отдельных строк по некоторому критерию используется ключевое слово WHERE
Получить список предприятий, расположенных в Москве:
SELECT firm_name as "Название предприятия"
FROM k_firm
WHERE firm_addr='Москва'
Результат:
Название предприятия
--------------------
Альфа
Для сравнения поля со значением NULL нельзя использовать операции = и!=, вместо них нужно использовать выражения IS NULL и IS NOT NULL.
Получить список постоянно работающих сотрудников, т.е., таких, у которых staff_termdate равно NULL:
SELECT staff_name FROM k_staff
WHERE staff_termdate IS NULL
Результат:
staff_name
-----------
Иванов
Петров
Сидоров
Условия могут быть сложные, представляющие собой комбинацию нескольких операций сравнения. В них можно использовать логические связки AND и OR, а также отрицание NOT.
Получить список предприятий, расположенных в Москве или Казани:
SELECT firm_name as "Название предприятия"
FROM k_firm
WHERE firm_addr='Москва' OR firm_addr='Казань'
Результат:
Название предприятия
---------------------
Альфа
Бета
Если условие заключается в сравнении поля со списком значений, удобно использовать ключевое слово IN.
Получить список предприятий, расположенных в Москве или Казани:
SELECT firm_name as "Название предприятия"
FROM k_firm
WHERE firm_addr IN ('Москва','Казань')
Результат:
Название предприятия
---------------------
Альфа
Бета
Если условие заключается в сравнении поля с диапазоном значений, удобно использовать ключевое слово BETWEEN.
|
|
|
Получить список договоров, заключенных в ноябре 2011 г.:
SELECT * FROM k_contract
WHERE contract_date BETWEEN '2011-11-01' AND '2011-11-30'
Заметим, что полезно предварительно задать желаемый формат даты год-месяц-день:
SET DATEFORMAT YMD
Результат:
contract_num contract_date contract_type firm_num staff_num
----------------------------------------------------------
1 2011-11-01 00:00:00.000 A 1 1
4 2011-11-15 00:00:00.000 A 2 2
7 2011-11-12 00:00:00.000 A 4 1
Для полей строкового типа можно применять сравнение с подстрокой.
Получить список сотрудников, фамилия которых начинается на И:
SELECT staff_name FROM k_staff
WHERE staff_name LIKE 'И%'
(рассмотрите более подробно использование LIKE в MSDN )
Результат:
staff_name
----------
Иванов
Использование агрегирующих функций
Для подсчета итоговых значений используются функции SUM, COUNT, MAX, MIN, AVG. Если не используется группировка строк, запрос с использованием итоговой функции вернет ровно одну строку.
Подсчитать, на какую сумму выставлены счета в декабре.
SELECT SUM(bill_sum) FROM k_bill
WHERE bill_date
BETWEEN '2011-12-01' AND '2011-12-31'
Результат:
----------------------------------------
Функция COUNT позволяет подсчитать, сколько строк в таблице имеется вообще.
Подсчитать количество сотрудников.
SELECT COUNT(*) FROM k_staff
Результат:
-----------
А также эта функция позволяет подсчитать, сколько строк с не-NULL-значениями в определенном поле.
Подсчитать количество временно работающих сотрудников (у них заполнен срок окончания трудового довора – поле staff_termdate).
SELECT COUNT(staff_termdate) FROM k_staff
Результат:
-----------
Сортировка
Для сортировки используется ключевое слово ORDER BY и имя поля или его номер в списке полей выборки.
Напечатать список сотрудников, отсортированный по алфавиту:
SELECT staff_name FROM k_staff ORDER BY 1
Результат:
staff_name
-------------
Григорьев
Иванов
Петров
Семенов
Сидоров
Можно сортировать строки даже по такому полю, которое не входит в список полей выборки.
Напечатать список сотрудников, отсортированный по дате поступления на работу:
SELECT staff_name FROM k_staff ORDER BY staff_hiredate
Сортировать данные можно и по убыванию. Кроме того, можно ограничить количество строк в результате.
Напечатать информацию о 5 последних выписанных счетах в порядке убывания даты счета:
SELECT TOP 5 bill_num, bill_date
FROM k_bill ORDER BY bill_date DESC
Результат:
bill_num bill_date
-------- -----------------------
3 2012-01-12 00:00:00.000
6 2012-01-12 00:00:00.000
|
|
|
5 2012-01-12 00:00:00.000
9 2012-01-12 00:00:00.000
4 2011-12-12 00:00:00.000
Подзапросы
Для более сложных формулировок иногда удобно использовать подзапросы.
Подзапрос всегда указывается в скобках.
Подзапрос может быть несвязанным, т.е. в формулировке подзапроса нет ссылки на главный запрос. В этом случае подзапрос выполняется один раз при выполнении главного запроса. В данном примере используется ключевое слово IN, так как подзапрос может возвращать несколько значений.
Получить список договоров, по которым в декабре выписаны счета:
SELECT contract_num, contract_date FROM k_contract
WHERE contract_num IN
(SELECT contract_num FROM k_bill
WHERE bill_date
BETWEEN '2011-12-01' AND '2011-12-31')
Результат:
contract_num contract_date
-----------------------------------
1 2011-11-01 00:00:00.000
2 2011-10-01 00:00:00.000
4 2011-11-15 00:00:00.000
5 2011-08-01 00:00:00.000
Тот же самый запрос с использованием ключевого слова ANY:
SELECT contract_num, contract_date FROM k_contract c
WHERE contract_num = ANY
(SELECT contract_num FROM k_bill
WHERE bill_date
BETWEEN '2011-12-01' AND '2011-12-31')
Тот же самый запрос можно выполнить и с помощью связанного подзапроса, т.е., подзапроса, в котором есть ссылка на главный запрос. Для ссылки на таблицу главного запроса нужно указать псевдоним. Такой подзапрос будет выполняться заново для каждой строки главного запроса.
Кроме того, в данном примере иллюстрируется использование ключевого слова EXISTS:
SELECT contract_num, contract_date FROM k_contract c
WHERE EXISTS
(SELECT * FROM k_bill b
WHERE bill_date
BETWEEN '2011-12-01' AND '2011-12-31'
AND c.contract_num=b.contract_num)
Пример использования ключевого слова ALL.
Напечатать информацию о товаре (товарах) с наименьшей ценой.
SELECT price_name, price_sum FROM k_price
WHERE price_sum <= ALL
(SELECT price_sum FROM k_price)
Результат:
price_name price_sum
--------------------------
Раздача слонов 100.00
Этот запрос можно сформулировать и по-другому. В этом примере мы можем использовать операцию сравнения =, т.к. подзапрос возвращает ровно одну строку и один столбец.
SELECT price_name, price_sum FROM k_price
WHERE price_sum =
(SELECT MIN(price_sum) FROM k_price)
А так, как в следующем примере, запрос формулировать нельзя, поскольку при запуске будет выдана ошибка. Если используются агрегирующие функции без группировки, в списке полей могут присутствовать только агрегирующие функции.
|
|
|
SELECT price_name, MIN(price_sum) FROM k_price
Результат – сообщение об ошибке:
Msg 8120, Level 16, State 1, Line 1
Столбец "k_price.price_name" недопустим в списке выбора, поскольку он не содержится ни в статистической функции, ни в предложении GROUP BY.
(Подробнее по поводу ошибок – см. Приложение 1.)
Группировка
Для подведения итога по группе данных используется комбинация ключевого слова GROUP BY и агрегирующих функций. Причем в списке полей для выборки могут присутствовать только поля группировки и агрегирующие функции (при необходимости можно просто расширить список полей группировки).
Получить список договоров и общую сумму счетов по каждому договору:
SELECT contract_num, SUM(bill_sum) AS contract_sum
FROM k_bill
GROUP BY contract_num
Результат:
contract_num contract_sum
--------------------------
1 5000
2 8000
3 2500
4 1500
5 11200
В том случае, когда нужно выбрать не все группы, а только некоторые из них, используется ключевое слово HAVING:
Получить список договоров, имеющих 2 или более счетов, и общую сумму счетов по каждому договору:
SELECT contract_num, SUM(bill_sum) AS contract_sum
FROM k_bill
GROUP BY contract_num
HAVING COUNT(bill_num)>=2
Результат:
contract_num contract_sum
------------ ----------------------------------------
1 5000
2 8000
5 11200
|
|
|
