Лабораторная работа№ 14
Создание SQL-запросов в Delphi
С.В. Каверин
Цель работы: Научиться создавать SQL-запросы к базам данных в среде программирования Delphi, используя компонент ADOQuery.
Постановка задачи. Окончательный вид окна просмотра SQL-запроса, приведен на рисунке 1. Окно содержит три именованные рабочие области.
[pic]
Рисунок 1 – Вид окна «Запросы к БД Товар»
В первой области (сетка DBGrid1) выводится готовый SQL-запрос на отсутствующие товары. Код этого запроса прописан в программе.
Во вторую область (компонент Memo) в ручном режиме вводится клиентский SQL-запрос. Нажимается кнопка «Выполнить запрос», после чего в третьей области (сетка DBGrid2) появляются записи удовлетворяющие условию введенного клиентского запроса.
1 Создание нового окна «Запросы к БД»
1.1 Создаем Form5 (File -> New-> Form-Delphi), сохраняем как Unit5.
1.2 Устанавливаем свойство Form5.Caption = Запросы к БД.
1.3 Знакомим Form5 с Form1 и DatMod (с помощью File->Use Unit..).
1.4 Переходим на Form1. В меню приложения добавляем новую закладку Запросы. Для этого дважды кликаем на компонент MainMenu1 и в окне создания меню выбираем нужные места (выставляя курсор) и в соответствующие свойстве Caption пишем заголовки пунктов (см. рисунок 2).
[pic]
Рисунок 2 – Настройка компонента MainMenu1
1.5 Дважды кликаем по пункту меню Запросы к БД Товар из вкладки Запросы (см. рисунок 2). В обработчике событий пишем код:
[pic]
1.6 Переходим на Form5. Положите на Form5 компонент Memo (закладка Standard), две кнопки Button (закладка Standard) с надписами Выполнить запрос и Создать новый SQL-запрос, две сетки DbGrid (закладка Data Controls) и три метки Label (закладка Standard) с соответствующими надписями синего цвета. Расположите компоненты как на рисунке 1.
1.7 Сохраняем проект (File -> Save All) и компилируем (F9).
2 Создание запроса «Отсутствующие товары»
2.1 На модуль dm положите компонент ADOQuery ((закладка dbGo) и источник DataSource (закладка DataAccess), которому дайте имя dsQuery1.
2.2 Свяжите ADOQuery1 с ADOConnection1, как показано на рисунке 3.
[pic]
Рисунок 3 – Установка свойств ADOQuery1
2.3 В инспекторе dsQuery1 установите свойство DataSet = ADOQuery1.
2.4 Переходим на Form5 и выделяем DbGrid1. В инспекторе DbGrid1 установите свойство DataSource = dsQuery1.
2.5 Переходим на dm. Выделяем компонент ADOQuery1 и кликаем в свойстве SQL по строке TWideStrings.
[pic]
Появляется окно редактора SQL-запросов. Записываем туда код для отсутствующего товара (см. рисунок 4). Нажимаем OK.
[pic]
Рисунок 4 – Окно редактора SQL-запросов с запросом
2.6 В Инспекторе ADOQuery1 устанавливаем свойство: Active = True. Это свойство устанавливается в последнюю очередь, в противном случае, возможно возникновение ошибки «ADOQuery1: Missing SQL property», если свойство ADOQuery1.SQL пусто (рисунок 5).
[pic]
Рисунок 5 – Ошибка отсутствия запроса в свойстве
2.7 Сохраняем проект (File -> Save All) и компилируем (F9).
3 Создание клиентского запроса
3.1 На модуль dm положите компонент ADOQuery ((закладка dbGo) и источник DataSource (закладка DataAccess), которому дайте имя dsQuery2.
3.2 Свяжите ADOQuery2 с ADOConnection1, как показано на рисунке 6.
3.3 В Инспекторе dsQuery2 установите свойство DataSet = ADOQuery2.
3.4 Переходим на Form5 и выделяем DbGrid2. В инспекторе DbGrid2 установите свойство DataSource = dsQuery2.
3.5 По щелчку на Button1 (Выполнить запрос) SQL-запрос из Memo1 следует передать в свойство ADOQuery2.SQL где он автоматически выполняется. Создаем обработчик этого события (OnClick):
[pic]
Пояснение. В Memo1 вводится SQL запрос, после чего нажимается кнопка «Выполнить запрос». В случае какой-либо ошибки, например, выражение не соответствует правилам SQL-запроса, на экран выводится типичное сообщение типа «OK» с надписью «Не удалось выполнить запрос».
3.6 Сохраняем проект (File -> Save All) и компилируем (F9).
4 Наводим «красоту» на форме Form5
4.1 Переходим на Form5. Сделаем так, чтобы при первом открытии этой формы, в Memo1 был заложен следующий клиентский запрос:
[pic]
В Инспекторе компонента Form5 вызываем событие OnCreate (или двойной щелчок по Form5). Отметим, что событие OnCreate генерируется только один раз при создании формы.
В открывшемся окне Code вводим код:
[pic]
4.2 Сохраняем проект (File -> Save All) и компилируем (F9).
4.3 Переходим на Form5. В Инспекторе Button2 устанавливаем свойство: Caption = Создать новый SQL-запрос.
4.4 Создайте обработчик события OnClick кнопки Button2:
[pic]
4.5 На компоненте Memo1 устанавливаем свойство ScrollBars=ssBoth (добавляем горизонтальную и вертикальную полосы прокрутки).
4.6 Сохраняем проект (File -> Save All) и компилируем (F9).
!!! Получился неплохой тренажер для изучения языка SQL. Испытаем?
4 Краткая теория по созданию SQL-запросов
Для выборки данных используется оператор SELECT. Сокращенный формат этого оператора имеет следующий вид:
SELECT Список_Полей /* Обязательное поле */
FROM Список_Таблиц /* Обязательное поле */
WHERE Критерий выбора записей /*Не обязательное поле */
ORDER BY Список полей /*Не обязательное поле */
где
ORDER BY – параметр, который задает условие упорядоченности записей, удовлетворяющих критерию запроса. По умолчанию – сортировка по возрастанию, а с ключевым словом DESC – по убыванию.
Некоторые ограничения:
1.Точка с запятой (;) – завершающий элемент SQL-запроса.
2.Перечисление полей (таблиц) осуществляется через запятую.
3.Для имен (полей или таблиц) с пробелами или дефисами следует использовать квадратные скобки. Например, [Код товара], [Фамилия сотрудника базы], [Кол-во], [Апрель-Август].
Для одинаковых имен полей в таблицах, используемых в запросе, следует перед их названием писать имя таблицы. Например, Заказы.КодТовара и Товары.КодТовара.
Строки обязательно записываются в кавычках. Например, Клиенты.ФИО = ′Иванов И.П.′.
В нижеприведенных примерах используется БД Товар, созданная в Лабораторной работе №11. Исполнение примеров осуществляется в разработанной Информационной системе «Товар».
Пример 1. Вывести записи всех полей таблицы Клиенты.
[pic]
Пояснение: вместо перечисления имен всех полей можно использовать символ «*» (звездочка).
Пример 2. Вывести записи всех полей таблицы Товары, упорядоченных по возрастанию стоимости закупки.
[pic]
В случае требования упорядочивания по убыванию стоимости закупки:
[pic]
Пример 3. Вывести записи полей КодТовара, НаименованиеТовара, СтоимостьЗакупки таблицы Товары, где стоимость закупки товаров колеблется от 1000 до 4000 у.е. Произвести упорядочение по возрастанию стоимости закупки.
[pic]
Пример 4. Вывести записи полей КодТовара, НаименованиеТовара, СтоимостьЗакупки, Наличие, Кол-во таблицы Товары. В запрос включить только имеющиеся товары. Упорядочить по полю Кол-во.
[pic]
Замечание. Порядок вывода полей в запросе указан в операторе Select.
Пояснение. В учебных целях, использованы полные имена полей (включая имя таблицы). Квадратные скобки в названии Товары.[Кол-во] обязательны, т.к. дефис воспринимается также как пробел.
Пример 5. Бессмысленное объединение двух таблиц, запрос соединяет каждую строку из таблицы Заказы с каждой строкой из таблицы Клиенты в одну строку (т.е. декартовое произведение).
[pic]
Пример 6. Объединение 2-х таблиц с помощью слова WHERE. Организовать выборку всех заказов (в виде новой таблицы), указав в ней Код заказа, Дату размещения, Дату исполнения (из таблицы Заказы), ФИО и адрес заказчика (из таблицы Клиенты).
[pic]
Пояснение. В таблице Заказы всего 3 записи. Все они выведены. В описание имен полей, использовать имена таблиц не обязательно. Почему?
Пример 7. Объединение 3-х таблиц с помощью слова WHERE. Организовать выборку всех заказов (в виде новой таблицы), указав в ней Наименование товара (из таблицы Товары), Дату размещения, Дату исполнения (из таблицы Заказы), ФИО и адрес заказчика (из таблицы Клиенты). Упорядочить по убыванию Даты исполнения.
[pic]
Пример 8. Объединение 3-х таблиц с помощью операции INNER JOIN (пересечение). Организовать выборку всех заказов (в виде новой таблицы), указав в ней Наименование товара (из таблицы Товары), Дату размещения, Дату исполнения (из таблицы Заказы), ФИО и адреса заказчика (из таблицы Клиенты). Упорядочить по убыванию Даты исполнения.
[pic]
Замечание. Объединение INNER JOIN всегда можно заменить объединением WHERE (сравнить результаты примеров 7 и8).
Пояснение. В результат объединения INNER JOIN попадают те записи, которые строго удовлетворяют условию объединения, которое указывается после слова ON. Сначала формируется множество записей объединения таблиц Клиенты и Заказы, т.е. выполняется скобка (Клиенты INNER JOIN Заказы ON Клиенты.Код = Заказы.КодКлиента). Затем происходит объединение с таблицей Товары, по ключевым полям КодТовара.
Пример 9. Объединение 2-х таблиц с помощью операции LEFT JOIN (пересечение с необязательным присутствием слева).
Организовать выборку всех товаров (в виде новой таблицы), указав в ней Наименование товара, Количество (из таблицы Товары), а также, если были заказы, то добавить Код заказа, Дату размещения и Дату исполнения заказа (из таблицы Заказы). В выборку включать только имеющиеся товары.
[pic]
Пояснение. В запрос выбираются все строки из таблицы Товар (слева от LEFT JOIN). К ним добавляются строки из таблицы Заказы (справа от LEFT JOIN), причем выбирает только те пары, которые соответствуют выражению указанному после слова ON. Если для какой-то строки из таблицы Товар не нашлось ни одной строки из таблицы Заказы, соответствующей условию, то строка соединяется с полями, имеющими значения NULL (нет значения). К этим записям применяется операция наличия: WHERE Наличие=True.
Пример 10. Работа с датами и текстом. Организовать выборку Модемов, указав в ней Наименование товара, Стоимость закупки (из таблицы Товары), которые поступили с сентября 2007 (из таблицы Поставка).
[pic]
Пояснение. Подстановочный символ «%» замещает любое количество символов. Операция сравнения LIKE 'Модем%' – отбирает записи, которые начинаются со слова «Модем». Искомый текст заключен в одинарные кавычки ( ' ). Дата приведена в формате #Месяц/День/Год#, что верно для MS Access. В других системах формат написания даты может быть другим.
6 Задания для самостоятельной работы
Усовершенствуйте проект своего варианта задания, организуя выбор информации из БД с помощью заданного и клиентского SQL-запросов, по аналогии с разработкой Form5 проекта «Информационная система Товар». Создайте пять SQL-запросов для своей базы данных.
Литература
Фленов М.Е. Библия Delphi //СПб.: БХВ-Петербург.-2011.-688с.
