Лекция 7. Запросы.

7.1. Запросы

Аппарат запросов – мощное средство, позволяющее быстро выполнить различные действия над данными таблиц.

Запросы записываются на специальном языке SQL. Команды языка SQL предназначены для выборки данных из таблиц, вставки записей в таблицы, удаления и изменения записей таблиц. Кроме этого, с помощью команд SQL можно создавать, удалять, переименовывать, копировать таблицы и индексы.

Рассмотрим некоторые, основные структуры операторов языка SQL.

1. Оператор выборки данных SELECT

SELECT [DISTINCT] *| <список выбираемых полей>

FROM <список таблиц>

[WHERE <условие>]

[ORDER BY <список полей>]

[GROUP BY <список полей>]

Оператор предназначен для формирования набора данных, структура которого определяется список выбранных полей, а записи – указанными условиями.

Символ «*» задает отбор всех полей указанных таблиц.

В списке таблиц указываются имена таблиц, из которых формируется набор данных.

Слово DISTINCT задает условие, при котором выбираются только записи с различными значениями.

Например, результатом следующего запроса будет список всех фамилий из таблицы students.

SELECT students.name

FROM students

Результат следующего запроса – все различные фамилии.

SELECT DISTINCT students.name

FROM students

Ключевое слово FROM позволяет задавать для таблиц так называемые «алиасы» (псевдонимы), а затем использовать их для доступа к полям.

Например,

SELECT s.name

FROM students s или

SELECT s.name, s.date_birth

FROM students s

Алиасы удобно использовать для обозначения доступа к полям таблиц с длинными именами. Для полей таблиц, указанных в операторе SELECT, также можно задавать имена с помощью ключевого слова AS:

SELECT s.name AS fio, s.date_birth AS birth

Новые имена для полей можно использовать только в рамках данного запроса, реальные имена полей в таблице не будут изменяться!

В условии в части WHERE задается логическое выражение, связывающее значения полей таблиц, из которых выбирается информация.

Например, следующий оператор выбирает всех студентов первого курса с их фамилиями и адресами.

SELECT s.fio, s.adress

FROM students s

WHERE s.course=1

Логическое выражение может быть простым или сложным. В сложном логическом выражении используются логические операции NOT, AND, OR.

Например, следующий оператор формирует набор данных, содержащий фамилии и адреса студентов первого курса, первой группы.

SELECT s.fio, s.adress

FROM students s

WHERE (s.course=1) AND (s.group_st=1)

Чаще всего запросы используются для выборки данных из нескольких таблиц.

Например, для выбора фамилий студентов и всех их оценок за интересующую сессию необходимо выполнить следующий запрос:

SELECT s.name, m.mark

FROM students s, marks m

WHERE (s.id_stud=m.id_stud) AND (m.date_sem= ' 01.02.2001')

Можно добавить и наименования дисциплин:

SELECT s.name, m.mark, sb.name

FROM students s, marks m, subjects sb

WHERE (s.id_stud=m.id_stud) AND (m.id_subj=sb.id_subj)

2. Опция ORDER BY в команде SELECT

Использование ключевого слова ORDER BY позволяет упорядочивать записи в формируемом наборе данных. Записи сортируются по значениям полей, указанных в ORDER BY. Например, следующий оператор отсортирует записи по фамилиям по алфавиту, внутри по наименованию дисциплин.

SELECT s.name, m.mark, sb.name

FROM students s, marks m, subjects sb

WHERE (s.id_stud=m.id_stud) AND (m.id_subj=sb.id_subj)

ORDER BY s.name, sb.name

3. Опция GROUP BY в команде SELECT

Использование ключевого слова GRОUP BY позволяет группировать записи по одинаковым значениям заданных полей для выполнения так называемых функций агрегации или групповых функций (количество записей в группе, сумма значений заданного поля в группе и т.д.).

Реализованы следующие функции: SUM (сумма), COUNT (количество), AVG (среднее), MAX (наибольшее), MIN (наименьшее).

Например, следующий оператор позволяет подсчитать суммарный балл всех студентов по заданной сессии и упорядочить набор данных по возрастанию суммарного балла:

SELECT s.name AS Fio, SUM(m.mark) AS SumM

FROM students s, marks m

WHERE (s.id_stud=m.id_stud)

GROUP BY Fio

ORDER BY SumM

Обратите внимание, что после ключевого слова GROUP BY д.б. указаны все поля, заданные до функции агрегации.

4. Компонент Query, представляющий запросы

Запросы представляют специальный класс TQuery. Этот класс задается компонентом Query (страница DataAccess). Для использования запроса в приложении необходимо установить на какой-либо форме или модуле компонент Query и задать значения (по крайней мере) свойствам: DatabaseName и SQL.

Для свойства DatabaseName необходимо выбрать из списка алиас БД, с которой происходит работа. Значение свойства SQL – совокупность строк запроса на языке SQL.

Для запуска запроса используется оператор:

<имя запроса>.Open

Набор данных, формируемый с помощью запроса, можно отобразить как и набор данных, представляемый компонентом TTable с помощью компонент DataSource и DBGrid.

Для этого необходимо:

1. установить на форме компоненты DataSource и DBGrid (с именами, например, DataSource1 и DBGrid1);

2. свойству DataSet компонента DataSource придать значение имени запроса (например, Query1);

3. свойству DataSource компонента DBGrid придать значение имени источника данных (DataSource1);

2. Примеры использования аппарата запросов

Пусть даны БД, представляющие информацию о книгах, хранящихся в библиотеке, представляют следующие таблицы: Authers (информация об авторах), Books (информация о книгах), Publ (информация об издательствах), Country (справочник стран), TypeBook (справочник направлений литературных произведений, например, поэзия, проза, критика и т.д.).

Структура таблиц следующая.

Authers

Наименование поля	Тип	Примечание
Id	S*	Код автора
Fio	A 60	Фамилия, имя, отчество автора
Date_Birth	D	Дата рождения
Date_Dearth	D	Дата смерти
Id_Country	S	Код страны проживания

Books

Наименование поля	Тип	Примечание
Id_Aut	S*	Код автора
Id	S*	Код книги
Name	A 255	Название книги
Id_Publ	S	Код издательства
Id_Type	S	Код литературного направления
Year_Publ	S	Год издания
Pages_Book	S	Кол-во страниц
Number	S	Тираж

Publ

Наименование поля	Тип	Примечание
Id	S*	Код издательства
Name	A 60	Название издательства
Id_Country	S	Код страны

Country

Наименование поля	Тип	Примечание
Id	S*	Код страны
Name	A 60	Название страны

TypeBook

Наименование поля	Тип	Примечание
Id	S*	Код типа произведения
Name	A 60	Название типа

Сформируем запросы.

1. Список всех авторов по алфавиту, книги которых имеются в библиотеке.

SELECT fio

FROM authers

ORDER BY fio

2. Список всех авторов по алфавиту, книги которых имеются в библиотеке с указанием количества книг.

SELECT a.fio, COUNT(b.id)

FROM authers a, books b

WHERE (a.id=b.id_aut)

GROUP BY a.fio

ORDER BY fio

3. Список всех авторов по алфавиту, у которых книги выходили в заданном издательстве.

SELECT DISTINCT a.fio

FROM authers a, publ p,books b

WHERE (a.id=b.id_aut) AND(b.id_publ=p.id) AND (p.name= 'САМОЕ ХОРОШЕЕ ИЗДАТЕЛЬСТВО' )

ORDER BY fio

4. Список всех книг, опубликованных в заданном году с указанием автора и издательства, упорядоченный по наименованию издательства, затем по фамилии автора и наименованию книги

SELECT p.name, a.fio, b.name

FROM authers a, publ p, books b

WHERE (b.id_publ=p.id) AND (b.id_aut=a.id) AND (b.year_publ=2001)

ORDER BY p.name, a.fio, b.name

5. Все книги данного автора, опубликованные после указанного года

SELECT b.name

FROM authers a, books b

WHERE (b.id_aut=a.id) AND (a.fio='Иванов Иван Иванович') AND (b.year_publ>1975)

ORDER BY b.name

6. Максимальный тираж по издательствам в заданном году

SELECT p.name, MAX(b.number)

FROM publ p, books b

WHERE (b.id_publ=p.id) AND (b.year_publ=2001)

GROUP BY p.name

ORDER BY p.name

7. Суммарный тираж в страницах за заданный год заданного издательства

SELECT SUM(b.pages_book)

FROM publ p, books b

WHERE (b.id_publ=p.id) AND (b.year_publ=2001) AND (p.name='Несчастный случай')

8. Все книги французских авторов, изданных заданным издательством

SELECT a.fio, b.name

FROM authers a, books b, country c, publ p

WHERE (b.id_aut=a.id) AND (a.id_country=c.id) AND (c.name='Франция') AND (p.id=b.id_publ) AND (p.name='Все печатаем')

ORDER BY a.fio,b.name

9. Поэтические сборники английских авторов, изданных в заданном году

SELECT a.fio, b.name

FROM authers a, books b, country c, typebook t

WHERE (b.id_aut=a.id) AND (a.id_country=c.id) AND (c.name='Англия') AND (t.id=b.id_type) AND (t.name='Поэзия') AND (b.year_publ=2000)

ORDER BY a.fio,b.name