Инструкция SQL select извлекает информацию из базы данных и возвращает её в виде таблицы результатов запроса. Данная инструкция состоит из шести синтаксических единиц, называемых зачастую предложениями. Предложения select и from являются обязательными, остальные четыре включаются в запрос при необходимости.
- в предложении select указывается список столбцов, которые должны быть возвращены инструкцией.
- в предложении from указывается список таблиц, которые содержат элементы данных, извлекаемые запросом.
Например следующий запрос извлекает из таблицы Staff три столбца:
Вывести для каждого сотрудника имя, фамилию и занимаемую должность
selectfname, lname, position
fromstaff;
Помимо этого в предложении select может содержаться и так называемый “вычисляемый столбец” , например:
Выдать строки сотрудников с указанием зарплаты с 10 % надбавкой
selectfname, lname, position, (salary + 0.1*salary)aspercent
fromstaff;
В данном запросе percent определяет название столбца в результирующей таблице.
- предложение where показывает, что в результаты запроса следует включать только некоторые строки. В данном предложении вслед за ключевым словом where следует условие отбора, которое и определяет какие строки должны включаться в результирующий набор.
В SQL обычно используются пять основных условий отбора (в стандарте ANSI/ISO они называются предикатами)
- сравнение
- проверка на принадлежность диапазону
- проверка на членство в множестве
- проверка на соответствие шаблону
- проверка на равенство значению NULL.
Приведем примеры использования различных условий отбора в предложении where
Найти служащих, родившихся до 1988 года
selectfname, lname
fromstaff
where DOB<’01-Jan-88’;
Здесь следует обратить внимание на формат записи данных типа дата. В различных СУБД формат записи даты неодинаков. Используемый в примере формат поддерживается в ORACLE и специфичен для него. Даты в ORACLE так же как и строковые константы заключаются в парные одинарные кавычки.
Найти служащих, родившихся в интервале времени с 1октября 1963 по 31 декабря 1971
selectfname, lname
fromstaff
where DOB between ’01-Oct-63’ and ’31-Dec-71’;
Здесь следует отметить, что проверка на принадлежность диапазону не расширяет возможностей SQL, поскольку её можно выразить в виде двух сравнений, т. е. выражение A between B and C эквивалентно (A>=B) and (A<=C)
Вывести информацию об офисах, расположенных в Минске, Витебске и Бресте
select address, tel_no
from branch
where city in (‘Минск’, ‘Витебск’, ‘Брест’);
Проверка in не добавляет новых возможностей, так же как и between and, так как условие X in (A, B, C) полностью эквивалентно условию (X=A) or (X=B) or (X=C)
- Вывести информацию о всех сотрудников фамилии которых начинаются на букву К
select lname, address, tel_no
from staff
where lname like ‘K%’
Здесь также следует обратить внимание на запись шаблона строки сравнения в условии like. Указанный способ задания строки шаблона характерен для диалекта ORACLE и отличен от регламентированного стандартом. Символ ‘%’ - замещает произвольную последовательность символов, а ‘_’ – замещает одиночный символ. Строки-шаблоны так же как и обыкновенные строки-константы заключаются в парные одинарные кавычки.
Запросы с объединением таблиц
Если необходимо получить информацию более чем из одной таблицы, то можно либо применить подзапрос, либо выполнить соединение таблиц. Для выполнения соединения достаточно в предложении from указать имена объединяемых таблиц, а в предложении where указать столбцы соединения таблиц.
Составить список всех сотрудников, работающих в Минском отделении.
select fname, lname, position, S.tel_no
from Branch B, Staff S
where B.bno=S.bno and city = ‘Минск’;
Тот же запрос можно выполнить с помощью подзапроса
select fname, lname, position, tel_no
from staff where bno in (select bno from branch where city= ‘Минск’);
В связи с подчиненными запросами можно выделить ряд особенностей:
- Таблица результатов подчиненного запроса всегда состоит из одного столбца
- В подчиненный запрос не может включаться предложение order by
- Имена столбцов в подчиненном запросе могут являться ссылками на столбцы таблиц главного запроса
Итоговые запросы
Результирующую таблицу итогового запроса можно рассматривать как некий отчет. Для получения подобных отчетов в запросе на получение итоговой информации требуется указывать предложение group by и возможное having для отбора групп. Ограничением при выполнении итоговых запросов является то, что здесь в предложении select могут употребляться лишь столбцы группировки (т.е. те которые указываются в предложении group by), строковые константы и статистические функции. Таких функций в SQL пять:
- sum() – для вычисления суммы всех значений столбца-аргумента
- avg() – для вычисления среднего значения столбца
- min() – определяет минимальное значение столбца
- max() – определяет максимальное значение столбца
- count() – подсчитывает число всех определенных значений столбца
- count(*) – подсчитывает число строк таблицы.
Определить сколько в среднем получают сотрудники в зависимости от занимаемой ими должности.
select position, avg(salary)
from staff
group by position;
Подсчитать количество сотрудников работающих в каждом из офисов, исключив офисы, в которых работает менее 2 человек.
select bno, count(sno)
from staff
group by bno;
having count(sno)>2;
Представления
Представление – объект базы данных, представляющий собой именованный и сохраненный запрос. Часто представления также называют “виртуальными таблицами”. В случае если определение представления простое, СУБД выполняет его “на лету”, в обратном же случае СУБД приходится “материализовать ” представление, т.е. сохранять его результаты во временной таблице. Создаются представления посредством инструкции create view . Использование данной инструкции продемонстрируем на примере.
Создать представление, включающее в себя список сотрудников, работающих в отделениях Минска.
create view Minsk as select fname, lname, address, position, tel_no, sex, dob
from staff
where bno in (select bno
from branch
where city=’Минск’);
· Варианты заданий
1. Получить список сотрудников с зарплатой от 200 до 300.
Получить список сотрудников, работающих в офисах Бреста и Гомеля.
Определить суммарную и среднюю зарплату сотрудников в зависимости от занимаемой ими должности.
Создать представление с информацией о офисах в Бресте.
2. Определить адреса и телефоны офисов, расположенных в Минске и Гродно.
Кто из сотрудников предлагает для аренды 3-х комнатные квартиры.
Вывести итоговый отчет о средней и суммарной зарплатах в зависимости от половой принадлежности сотрудников.
Создать представление с информацией о директорах отделений.
3. Определить адреса всех 3-х комнатных квартир, предлагаемых в аренду.
Получить список арендаторов, осматривавших объекты аренды 20 октября 1999 года.
Определить минимальную и максимальную зарплаты сотрудников различных отделений.
Создать представление с информацией о владельцах, чьи дома или квартиры осматривались потенциальными арендаторами.
4. Вывести номера домашних телефонов всех директоров.
Составить список владельцев всех 3-х комнатных квартир.
Подсчитать количество сотрудников в каждом из отделений.
Создать представление сотрудниках и объектов, которые они предлагают в аренду.
5. Вывести список сотрудников, родившихся до 1980 года.
Подсчитать сколько сотрудников работает в отделении в Бресте
Вывести количество арендаторов, желающих арендовать 3-х комнатные и 4-х комнатные квартиры.
Создать представление об объектах с минимальной рентной стоимостью.
6. Определить адреса всех квартир с рентной стоимостью не более 300
Подсчитать сколько менеджеров работает в Минске
Получить итоговый список с количеством домов и квартир сдаваемых в аренду.
Создать представление о арендаторах, желающих арендовать 3-х комнатные квартиры.
Создать представление об отделении с максимальным количеством работающих сотрудников.
7. Вывести домашние телефоны всех потенциальных арендаторов, желающих арендовать дома.
Вывести телефоны владельцев, дома или квартиры которых осматривались 12 сентября 2001года.
Определить квартиры и дома минимальной рентной стоимости.
Создать представление о женщинах директорах.
8. Вывести список всех женщин-менеджеров
Определить максимальную зарплату сотрудников в отделении в Гродно
Определить количество осмотров с группировкой по датам.
Создать представление о количестве сделанных осмотров с комментариями.
9. Определить сколько объектов было осмотрено потенциальными арендаторами за Октябрь 1996 года
Создать список сотрудников предлагающих объекты недвижимости в Минске.
Определить суммарную рентную стоимость объектов в Минске и Гродно.
Создать представление о сотрудниках, чьи фамилии начинаются с буквы ‘О’.
10. Кто из арендаторов желает снять 4-хкомнатные квартиры.
Определить сколько потенциальных арендаторов осмотрели предлагаемые им квартиры или дома.
Определить какие из офисов имеют более 3-х сотрудников.
Создать представление, содержащее информацию об отделении, где предлагаются в аренду наиболее недорогие 2-х комнатные квартиры в смысле их средней стоимости.
· Контрольные вопросы
1. Как вы понимаете значение NULL?
2. Какова общая структура запроса на извлечение информации
3. Перечислите особенности итоговых запросов.
4. Что такое представление и для чего создаются такие объекты базы данных.