Создание SQL-запросов в Delphi

Лабораторная работа№ 14
Создание SQL-запросов в DelphiС.В. Каверин
Цель работы: Научиться создавать SQL-запросы к базам данных в среде программирования Delphi, используя компонент ADOQuery.
Постановка задачи. Окончательный вид окна просмотра SQL-запроса, приведен на рисунке 1. Окно содержит три именованные рабочие области.

Рисунок 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).

Рисунок 2 – Настройка компонента MainMenu1
1.5 Дважды кликаем по пункту меню Запросы к БД Товар из вкладки Запросы (см. рисунок 2). В обработчике событий пишем код:

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.

Рисунок 3 – Установка свойств ADOQuery1
2.3 В инспекторе dsQuery1 установите свойство DataSet = ADOQuery1.
2.4 Переходим на Form5 и выделяем DbGrid1. В инспекторе DbGrid1 установите свойство DataSource = dsQuery1.
2.5 Переходим на dm. Выделяем компонент ADOQuery1 и кликаем в свойстве SQL по строке TWideStrings.

Появляется окно редактора SQL-запросов. Записываем туда код для отсутствующего товара (см. рисунок 4). Нажимаем OK.

Рисунок 4 – Окно редактора SQL-запросов с запросом
2.6 В Инспекторе ADOQuery1 устанавливаем свойство: Active = True. Это свойство устанавливается в последнюю очередь, в противном случае, возможно возникновение ошибки «ADOQuery1: Missing SQL property», если свойство ADOQuery1.SQL пусто (рисунок 5).

Рисунок 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):

Пояснение. В Memo1 вводится SQL запрос, после чего нажимается кнопка «Выполнить запрос». В случае какой-либо ошибки, например, выражение не соответствует правилам SQL-запроса, на экран выводится типичное сообщение типа «OK» с надписью «Не удалось выполнить запрос».
3.6 Сохраняем проект (File -> Save All) и компилируем (F9).
4 Наводим «красоту» на форме Form5
4.1 Переходим на Form5. Сделаем так, чтобы при первом открытии этой формы, в Memo1 был заложен следующий клиентский запрос:

В Инспекторе компонента Form5 вызываем событие OnCreate (или двойной щелчок по Form5). Отметим, что событие OnCreate генерируется только один раз при создании формы.
В открывшемся окне Code вводим код:

4.2 Сохраняем проект (File -> Save All) и компилируем (F9).
4.3 Переходим на Form5. В Инспекторе Button2 устанавливаем свойство: Caption = Создать новый SQL-запрос.
4.4 Создайте обработчик события OnClick кнопки Button2:

4.5 На компоненте Memo1 устанавливаем свойство ScrollBars=ssBoth (добавляем горизонтальную и вертикальную полосы прокрутки).
4.6 Сохраняем проект (File -> Save All) и компилируем (F9).
!!! Получился неплохой тренажер для изучения языка SQL. Испытаем?
4 Краткая теория по созданию SQL-запросов
Для выборки данных используется оператор SELECT. Сокращенный формат этого оператора имеет следующий вид:
SELECT Список_Полей /* Обязательное поле */
FROM Список_Таблиц /* Обязательное поле */
WHERE Критерий выбора записей /*Не обязательное поле */
ORDER BY Список полей /*Не обязательное поле */
где
ORDER BY – параметр, который задает условие упорядоченности записей, удовлетворяющих критерию запроса. По умолчанию – сортировка по возрастанию, а с ключевым словом DESC – по убыванию.
Некоторые ограничения:
Точка с запятой (;) – завершающий элемент SQL-запроса.
Перечисление полей (таблиц) осуществляется через запятую.
Для имен (полей или таблиц) с пробелами или дефисами следует использовать квадратные скобки. Например, [Код товара], [Фамилия сотрудника базы], [Кол-во], [Апрель-Август].
Для одинаковых имен полей в таблицах, используемых в запросе, следует перед их названием писать имя таблицы. Например, Заказы.КодТовара и Товары.КодТовара.
Строки обязательно записываются в кавычках. Например, Клиенты.ФИО = ′Иванов И.П.′.
В нижеприведенных примерах используется БД Товар, созданная в Лабораторной работе №11. Исполнение примеров осуществляется в разработанной Информационной системе «Товар».
Пример 1. Вывести записи всех полей таблицы Клиенты.

Пояснение: вместо перечисления имен всех полей можно использовать символ «*» (звездочка).
Пример 2. Вывести записи всех полей таблицы Товары, упорядоченных по возрастанию стоимости закупки.

В случае требования упорядочивания по убыванию стоимости закупки:

Пример 3. Вывести записи полей КодТовара, НаименованиеТовара, СтоимостьЗакупки таблицы Товары, где стоимость закупки товаров колеблется от 1000 до 4000 у.е. Произвести упорядочение по возрастанию стоимости закупки.

Пример 4. Вывести записи полей КодТовара, НаименованиеТовара, СтоимостьЗакупки, Наличие, Кол-во таблицы Товары. В запрос включить только имеющиеся товары. Упорядочить по полю Кол-во.

Замечание. Порядок вывода полей в запросе указан в операторе Select.
Пояснение. В учебных целях, использованы полные имена полей (включая имя таблицы). Квадратные скобки в названии Товары.[Кол-во] обязательны, т.к. дефис воспринимается также как пробел.
Пример 5. Бессмысленное объединение двух таблиц, запрос соединяет каждую строку из таблицы Заказы с каждой строкой из таблицы Клиенты в одну строку (т.е. декартовое произведение).

Пример 6. Объединение 2-х таблиц с помощью слова WHERE. Организовать выборку всех заказов (в виде новой таблицы), указав в ней Код заказа, Дату размещения, Дату исполнения (из таблицы Заказы), ФИО и адрес заказчика (из таблицы Клиенты).

Пояснение. В таблице Заказы всего 3 записи. Все они выведены. В описание имен полей, использовать имена таблиц не обязательно. Почему?
Пример 7. Объединение 3-х таблиц с помощью слова WHERE. Организовать выборку всех заказов (в виде новой таблицы), указав в ней Наименование товара (из таблицы Товары), Дату размещения, Дату исполнения (из таблицы Заказы), ФИО и адрес заказчика (из таблицы Клиенты). Упорядочить по убыванию Даты исполнения.

Пример 8. Объединение 3-х таблиц с помощью операции INNER JOIN (пересечение). Организовать выборку всех заказов (в виде новой таблицы), указав в ней Наименование товара (из таблицы Товары), Дату размещения, Дату исполнения (из таблицы Заказы), ФИО и адреса заказчика (из таблицы Клиенты). Упорядочить по убыванию Даты исполнения.

Замечание. Объединение INNER JOIN всегда можно заменить объединением WHERE (сравнить результаты примеров 7 и8).
Пояснение. В результат объединения INNER JOIN попадают те записи, которые строго удовлетворяют условию объединения, которое указывается после слова ON. Сначала формируется множество записей объединения таблиц Клиенты и Заказы, т.е. выполняется скобка (Клиенты INNER JOIN Заказы ON Клиенты.Код = Заказы.КодКлиента). Затем происходит объединение с таблицей Товары, по ключевым полям КодТовара.
Пример 9. Объединение 2-х таблиц с помощью операции LEFT JOIN (пересечение с необязательным присутствием слева).
Организовать выборку всех товаров (в виде новой таблицы), указав в ней Наименование товара, Количество (из таблицы Товары), а также, если были заказы, то добавить Код заказа, Дату размещения и Дату исполнения заказа (из таблицы Заказы). В выборку включать только имеющиеся товары.

Пояснение. В запрос выбираются все строки из таблицы Товар (слева от LEFT JOIN). К ним добавляются строки из таблицы Заказы (справа от LEFT JOIN), причем выбирает только те пары, которые соответствуют выражению указанному после слова ON. Если для какой-то строки из таблицы Товар не нашлось ни одной строки из таблицы Заказы, соответствующей условию, то строка соединяется с полями, имеющими значения NULL (нет значения). К этим записям применяется операция наличия: WHERE Наличие=True.
Пример 10. Работа с датами и текстом. Организовать выборку Модемов, указав в ней Наименование товара, Стоимость закупки (из таблицы Товары), которые поступили с сентября 2007 (из таблицы Поставка).

Пояснение. Подстановочный символ «%» замещает любое количество символов. Операция сравнения LIKE 'Модем%' – отбирает записи, которые начинаются со слова «Модем». Искомый текст заключен в одинарные кавычки ( ' ). Дата приведена в формате #Месяц/День/Год#, что верно для MS Access. В других системах формат написания даты может быть другим.
6 Задания для самостоятельной работы
Усовершенствуйте проект своего варианта задания, организуя выбор информации из БД с помощью заданного и клиентского SQL-запросов, по аналогии с разработкой Form5 проекта «Информационная система Товар». Создайте пять SQL-запросов для своей базы данных.
Литература
Фленов М.Е. Библия Delphi //СПб.: БХВ-Петербург.-2011.-688с.