Программирование баз данных в Delphi

SELECT Фамилия, Имя, Отдел,  Должность

FROM LichData, Doljnost

WHERE Ключ = Сотрудник;

Теперь все в порядке, отдел  и должность соответствуют каждому  сотруднику. Мы указали, что нам нужны  лишь те записи, значения которых в  поле "Ключ" одной таблицы соответствуют  значениям в поле "Сотрудник" другой таблицы. Полный синтаксис требует  указания таблицы вместе с полем:

WHERE LichData.Ключ = Doljnost.Сотрудник;

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

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

Таблица 8.2 . Операторы сравнения
Оператор	Описание
=	Равно
>	Больше
<	Меньше
>=	Больше или  равно
<=	Меньше или  равно
<>	Не равно

Кроме того, мы можем использовать логические операторы AND, OR и NOT, формируя более сложные запросы:

SELECT Фамилия, Имя, Отдел,  Должность

FROM LichData, Doljnost

WHERE (LichData.Ключ = Doljnost.Сотрудник) 

   AND (Должность = "Бухгалтер");

Логические операторы имеют  более высокий приоритет, поэтому в приведенном примере можно обойтись и без скобок. Данный запрос выдаст нам данные только на бухгалтеров. Как вы могли заметить, в отличие от Delphi, строка в SQL заключается не в одинарные, а в двойные кавычки! Однако SQL более демократичен, одинарные кавычки тоже принимаются. Обычно их используют, если внутри строки требуется указать кавычки, например, 'Строка "в кавычках" будет отображена'.

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

Команда ORDER BY

Команда ORDER BY позволяет сортировать записи по определенному полю как в возрастающем, так и в убывающем порядке. Воспользуемся предыдущим примером, и отсортируем записи по полю "Фамилия":

SELECT Фамилия, Имя, Отдел,  Должность

FROM LichData, Doljnost

WHERE Ключ = Сотрудник

ORDER BY Фамилия;

Как уже говорилось, мы можем сортировать  данные как по возрастанию (ASC), так и по убыванию (DESC) значений. Сортировка по возрастанию установлена "по умолчанию", а вот чтобы сортировать записи по убыванию, после имени поля следует поставить служебное слово DESC:

ORDER BY Фамилия DESC;

Опять заметим, что для сортировки записей наличие индексных полей  необязательно. Часто бывает, что нужно вывести данные из двух таблиц, имеющих связь один-ко-многим. При этом нужно сортировать данные не по одному, а по двум полям:

SELECT Фамилия, Имя, Телефон,  Примечание

FROM LichData, Telephones

WHERE Ключ = Сотрудник

ORDER BY Фамилия, Телефон;

В этом случае мы получим набор  данных, в котором записи отсортированы  вначале по фамилии сотрудника, затем  по его номеру телефона:

 
Рис. 8.1 .  Двойная сортировка данных

Оператор IN

Оператор IN позволяет определить набор значений. Предположим, нам нужны сотрудники, проживающие в городах Москва и Санкт-Петербург. Мы можем сформировать сложный запрос:

SELECT Фамилия, Имя, Город

FROM LichData, Adres

WHERE Ключ = Сотрудник AND

    (Город = "Москва" OR Город = "Санкт-Петербург");

Последнюю строку запроса можно  упростить, если использовать оператор IN:

WHERE Ключ = Сотрудник AND Город  IN

  ("Москва", "Санкт-Петербург");

Представьте, если нужны данные не по двум, а по десятку городов. В какого бы монстра превратился запрос со сложным условием, если не использовать IN! При перечислении строк можно использовать как двойные, так и одинарные кавычки, при перечислении числовых значений кавычки не нужны. Все значения разделяются запятой.

Оператор BEETWEEN

Оператор BEETWEEN работает примерно так же, как IN, но задает не список, а диапазон значений. Предположим, нам нужно выявить сотрудников, которые имеют стаж работы от 4 до 10 лет включительно. Подобный запрос выглядит так:

SELECT Фамилия, Имя, Стаж

FROM LichData

WHERE Стаж BETWEEN 4 AND 10;

Оператор LIKE

Оператор LIKE работает только с символьными и строковыми полями. Этот оператор позволяет находить записи, имеющие заданную подстроку. Предположим, нам требуется вывести всех сотрудников, чья фамилия начинается на букву "Л". Запрос будет таким:

SELECT Фамилия, Имя, Отчество

FROM LichData

WHERE Фамилия LIKE 'Л%';

Следует учитывать, что оператор LIKE чувствителен к регистру букв. Если вы будете производить поиск записи в программе при помощи SQL-запроса, позаботьтесь заранее привести буквы к нужному регистру.

Оператор LIKE использует маску символов, что позволяет задавать довольно сложные условия. Маска может иметь два специальных символа:

• "_" - Символ подчеркивания обозначает, что в этом месте должен быть любой символ. Например, "м_р" может выводить такие слова, как "мир", "мор" или "мур", но не сможет вывести слово "мера".
• "%" - Символ процента обозначает, что в этом месте может быть любое количество любых символов. Например, маска '_и_и%' выведет такие фамилии, как Лисичкин, Синичкин, Милиев.

Агрегатные функции

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

AVG - Функция возвращает среднее арифметическое значение из всех значений данного поля. Предположим, нам требуется выяснить средний стаж всех сотрудников предприятия. Такие данные могут быть сформированы следующим запросом:

SELECT AVG (Стаж)

FROM LichData;

MAX - Функция возвращает максимальное значение указанного поля. Синтаксис аналогичен функции AVG.

MIN - Функция возвращает минимальное значение указанного поля. Синтаксис аналогичен функции AVG.

SUM - Функция возвращает сумму значений в указанном поле. Синтаксис аналогичен функции AVG.

COUNT - Функция возвращает общее количество строк, сформированных запросом. В нашем случае это количество будет равно количеству сотрудников. Однако так называемые NULL-строки, то есть строки без значения, функция не учитывает. Если у какого-то сотрудника нет указания стажа его работы, то COUNT вернет меньшее количество, чем имеется сотрудников на предприятии. Синтаксис аналогичен функции AVG.

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

SELECT SUM (Стоимость * Количество)

FROM Pokupateli;

В скобках функции указаны нужные параметры. Вначале для каждой записи вычисляется значение, в нашем  случае, значение поля "Стоимость" умножается на значение поля "Количество". Таким образом, мы находим общую  сумму, которую заплатил данный покупатель. Обойдя все поля таблицы, получим все суммы всех покупателей, после чего функция SUM вернет нам общую сумму, полученную от всех покупателей.

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

Команда GROUP BY

Команда GROUP BY позволяет группировать записи по какому-то определенному значению, и применяется совместно с агрегатными функциями. Предположим, нам требуется не просто получить средний стаж всех сотрудников, а еще и разбить эти данные по отделам. Вдруг директору приспичит узнать, в каком отделе у него работает больше всего молодых или старых специалистов! В нашу задачу входит выявить средний стаж сотрудников для каждого имеющегося отдела. Это мы можем выявить таким запросом:

SELECT AVG(Стаж), Отдел

FROM LichData, Doljnost

WHERE Ключ = Сотрудник

GROUP BY Отдел;

Как видите, команда GROUP BY используется после команды WHERE и группирует записи по значению поля "Отдел". В результате получим таблицу из двух полей. Первое поле будет сформировано агрегатной функцией, второе поле "Отдел" из таблицы Doljnost. Для каждого отдела будет рассчитано среднее значение поля "Стаж".

Команда GROUP BY позволяет группировать записи не только по одному, но и по множеству полей. Предположим, одну и ту же должность могут иметь несколько человек (например, пять бухгалтеров в бухгалтерии). Нам требуется найти самого старого сотрудника не только по отделу, но и по занимаемой должности. Нас выручит следующий запрос:

SELECT Отдел, Должность, MAX(Стаж)

FROM LichData, Doljnost

WHERE Ключ = Сотрудник

GROUP BY Отдел, Должность;

В результате мы получим набор данных, сгруппированный не только по отделам, но и по должностям. Если должность занимает только один человек, то мы получим его стаж. Если несколько - то наибольший из них.

Команда DISTINCT | ALL

Команда DISTINCT (Отличие) предназначена для удаления избыточных (дублирующих) данных. Предположим, нам нужно получить список отделов на предприятии. Мы можем воспользоваться запросом:

SELECT Отдел

FROM Doljnost;

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

SELECT DISTINCT Отдел

FROM Doljnost;

В результате мы получим все названия имеющихся отделов, но эти названия не будут повторяться.

Обратной командой является ALL, принятая по умолчанию. Если вы не используете DISTINCT, то автоматически используется ALL (то есть, показываются все записи).

Команда HAVING

Команда HAVING позволяет определить условия, чтобы удалить определенные группы из полученного набора данных, точно так же, как команда WHERE делает это для отдельных записей. Предположим, нам нужно получить максимальный стаж работы по каждой должности, как это мы делали выше, но при этом указать, что этот максимальный стаж должен быть более 7 лет. Следовательно, если на какой-то должности работают молодые сотрудники, имеющие меньший стаж работы, эта должность не будет приниматься. Для задания условия мы обычно используем команду WHERE, но в этой команде нельзя использовать агрегатные функции, формирующие значение из группы записей. Другими словами, запрос, подобный этому:

SELECT Отдел, Должность, MAX(Стаж)

FROM LichData, Doljnost

WHERE Ключ = Сотрудник AND (MAX(Стаж) > 7)

GROUP BY Отдел, Должность;

вызовет ошибку. Использование в  запросе команды HAVING решает эту проблему:

SELECT Отдел, Должность, MAX(Стаж)

FROM LichData, Doljnost

WHERE Ключ = Сотрудник

GROUP BY Отдел, Должность

HAVING MAX(Стаж) > 7;

Мы изучили все основные команды SQL-запросов. На основе этих команд можно создавать запросы любой сложности. Однако еще раз заметим, что хорошие знания языка SQL позволят вам создавать более гибкие и мощные приложения, так что не ленитесь, изучайте SQL. Ведь вам наверняка придется создавать клиент-серверные базы данных, а в этой архитектуре вся работа с данными осуществляется только посредством SQL.

9. Лекция: Приемы создания и модификации таблиц программно: версия для печати и PDA  
На этой лекции вы познакомитесь с тремя способами создания таблиц программно. Вы создадите как простую, так и сложную индексированную таблицу, научитесь создавать таблицы через SQL-запрос.

На прошлых лекциях мы изучили  немало способов создания и обработки  таблиц. В большинстве случаев, база данных и таблицы в ней проектируются  и создаются заранее, например, такими утилитами, как Database Desktop, или СУБД MS Access. Однако иногда приходится создавать таблицы программно, то есть, не во время проектирования приложения, а во время его работы. Предположим, каждый пользователь программы должен иметь свою собственную таблицу в базе данных, или даже свою собственную БД. Сделать это заранее программист не может, ведь неизвестно, сколько пользователей на одном ПК будут работать с программой, и какие у них будут имена.

К сожалению, в большинстве учебной  литературы информация о программном  создании таблиц или вовсе отсутствует, или очень скудна - описывается только какой то один способ создания только одного типа таблиц. А ведь программисту, в зависимости от обстоятельств, может понадобиться создание разного типа таблиц, разными способами. В данной лекции мы попробуем восполнить этот пробел и разберем три способа программного создания как простых, так и индексированных таблиц.

BDE. Простая таблица.

Наиболее простой способ создания таблицы без индексов предлагает механизм доступа к данным BDE. Плюсы данного способа в простоте выполнения, в возможности создания таблиц как текстового типа, так и dBase, Paradox или FoxPro.

Суть данного способа заключается  в предварительном создании объектов-полей  в редакторе полей компонента TTable. Это также означает, что еще на этапе проектирования можно настроить формат объектов-полей по своему усмотрению. Рассмотрим этот способ на примере. Создайте новое приложение. Форму как всегда назовите fMain, сохраните модуль под именем Main, а проект в целом назовите как угодно. На форму установите простую панель, очистите ее свойство Caption, а свойству Align присвойте значение alTop. В левой части панели установите рядом две простые кнопки, а в правой - компонент TDBNavigator с вкладки Data Controls Палитры компонентов. Ниже панели установите сетку TDBGrid, в ее свойстве Align выберите значение alClient. У кнопок измените свойство Caption: на первой кнопке напишите "Создать таблицу", на второй - "Открыть таблицу".

Также нам потребуется еще четыре не визуальных компонента. Прямо на сетку, или в любое другое место установите компонент TTable с вкладки BDE, компонент TDataSource с вкладки Data Access, и компоненты TSaveDialog и TOpenDialog с вкладки Dialogs.