- •4.1. Основные понятия
- •4.2. Создание псевдонима базы данных
- •4.3. Создание таблиц базы данных
- •4.3.1. Объявление полей
- •4.3.2. Изменение языкового драйвера
- •4.3.3. Определение индексов
- •4.3.4. Определение ссылочной целостности между таблицами
- •4.3.5. Изменение структуры таблицы
- •4.4. Открытие и сохранение таблицы
- •4.5. Дополнительные утилиты
- •5.1. Взаимодействие классов, обеспечивающих доступ к таблицам
- •5.2. Классы tField и tTable: основное назначение и свойства
- •5.2.1. Класс tField: основное назначение и свойства
- •5.2.2. Класс tТable: основное назначение
- •5.2.3. Основные свойства класса tTable
- •5.2.4. Основные методы класса tTable
- •1) Доступ к таблице бд
- •2) Перемещение по записям таблицы
- •3) Режим редактирования таблицы
- •4) Доступ к значениям полей по имени
- •5) Добавление, удаление записей
- •6.1. Индексирование таблиц
- •6.2. Поиск записей
- •6.3. Поля компонента Table
- •6.4. Вычисляемые и связанные поля
- •Лекция 7. Запросы.
- •7.1. Запросы
- •7.3. Запросы с параметрами
- •Перед обращением к данному запросу необходимо задать значения двух параметров (типы данных: Country – String, Year- Short), а затем выполнить запрос:
- •7.4. Запросы с операторами delete, insert, update
Лекция 7. Запросы.
7.1. Запросы
Аппарат запросов – мощное средство, позволяющее быстро выполнить различные действия над данными таблиц.
Запросы записываются на специальном языке SQL. Команды языка SQL предназначены для выборки данных из таблиц, вставки записей в таблицы, удаления и изменения записей таблиц. Кроме этого, с помощью команд SQL можно создавать, удалять, переименовывать, копировать таблицы и индексы.
Рассмотрим некоторые, основные структуры операторов языка SQL.
Оператор выборки данных 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)
Опция 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
Опция 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 д.б. указаны все поля, заданные до функции агрегации.
Компонент Query, представляющий запросы
Запросы представляют специальный класс TQuery. Этот класс задается компонентом Query (страница DataAccess). Для использования запроса в приложении необходимо установить на какой-либо форме или модуле компонент Query и задать значения (по крайней мере) свойствам: DatabaseName и SQL.
Для свойства DatabaseName необходимо выбрать из списка алиас БД, с которой происходит работа. Значение свойства SQL – совокупность строк запроса на языке SQL.
Для запуска запроса используется оператор:
<имя запроса>.Open
Набор данных, формируемый с помощью запроса, можно отобразить как и набор данных, представляемый компонентом TTable с помощью компонент DataSource и DBGrid.
Для этого необходимо:
установить на форме компоненты DataSource и DBGrid (с именами, например, DataSource1 и DBGrid1);
свойству DataSet компонента DataSource придать значение имени запроса (например, Query1);
свойству DataSource компонента DBGrid придать значение имени источника данных (DataSource1);
Примеры использования аппарата запросов
Пусть даны БД, представляющие информацию о книгах, хранящихся в библиотеке, представляют следующие таблицы: 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 |
Название типа |
Сформируем запросы.
Список всех авторов по алфавиту, книги которых имеются в библиотеке.
SELECT fio
FROM authers
ORDER BY fio
Список всех авторов по алфавиту, книги которых имеются в библиотеке с указанием количества книг.
SELECT a.fio, COUNT(b.id)
FROM authers a, books b
WHERE (a.id=b.id_aut)
GROUP BY a.fio
ORDER BY fio
Список всех авторов по алфавиту, у которых книги выходили в заданном издательстве.
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
Список всех книг, опубликованных в заданном году с указанием автора и издательства, упорядоченный по наименованию издательства, затем по фамилии автора и наименованию книги
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
Все книги данного автора, опубликованные после указанного года
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
Максимальный тираж по издательствам в заданном году
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
Суммарный тираж в страницах за заданный год заданного издательства
SELECT SUM(b.pages_book)
FROM publ p, books b
WHERE (b.id_publ=p.id) AND (b.year_publ=2001) AND (p.name='Несчастный случай')
Все книги французских авторов, изданных заданным издательством
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
Поэтические сборники английских авторов, изданных в заданном году
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