Иптышев А.А., Морозов Д.И., Городилов А.А., Иптышев М.А. Базы данных
Подождите немного. Документ загружается.
71
<select_one> = выражение SELECT по одному полю, которое возвращает в
точности одно значение.
Приведенного неполного синтаксиса достаточно для большинства задач,
решаемых в различных предметных областях. Проще всего синтаксис SQL можно
понять из примеров. Поэтому мы приведем несколько примеров создания таблиц с
помощью SQL.
Пример A: Простая таблица с конструкцией PRIMARY KEY на уровне поля
CREATE TABLE REGION (
REGION REGION_NAME NOT NULL PRIMARY KEY,
POPULATION INTEGER NOT NULL);
Предполагается, что в базе данных определен домен REGION_NAME,
например, следующим образом:
CREATE DOMAIN REGION_NAME
AS VARCHAR(40) CHARACTER SET WIN1251 COLLATE PXW_CYRL;
Пример B: Таблица с предложением UNIQUE как на уровне поля, так и на
уровне таблицы
CREATE TABLE GOODS (
MODEL SMALLINT NOT NULL UNIQUE,
NAME CHAR(10) NOT NULL,
ITEMID INTEGER NOT NULL, CONSTRAINT MOD_UNIQUE
UNIQUE (NAME, ITEMID));
Пример C: Таблица с определением первичного ключа, внешнего ключа и
конструкции CHECK, а также символьных массивов
CREATE TABLE JOB (
JOB_CODE JOBCODE NOT NULL,
JOB_GRADE JOBGRADE NOT NULL,
JOB_REGION REGION_NAME NOT NULL,
JOB_TITLE VARCHAR(25) CHARACTER SET WIN1251 COLLATE
PXW_CYRL NOT NULL,
MIN_SALARY SALARY NOT NULL,
MAX_SALARY SALARY NOT NULL,
JOB_REQ BLOB(400,1) CHARACTER SET WIN1251,
LANGUAGE_REQ VARCHAR(15) [5],
PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_REGION),
FOREIGN KEY (JOB_REGION) REFERENCES REGION (REGION),
CHECK (MIN_SALARY < MAX_SALARY));
Данный пример создает таблицу, содержащую информацию о работах
(профессиях). Типы полей основаны на доменах JOBCODE, JOBGRADE,
REGION_NAME и SALARY. Определен массив LANGUAGE_REQ, состоящий из 5
элементов типа VARCHAR(15). Кроме того, введено поле JOB_REQ, имеющее тип
BLOB с подтипом 1 (текстовый блоб) и размером сегмента 400. Для таблицы
72
определен первичный ключ, состоящий из трех полей JOB_CODE, JOB_GRADE и
JOB_REGION. Далее, определен внешний ключ (JOB_REGION), ссылающийся на
поле REGION таблицы REGION. И, наконец, включено предложение CHECK,
позволяющее производить проверку соотношения для двух полей и вызывать
исключительное состояние при нарушении такого соотношения.
Пример D: Таблица с вычисляемым полем
CREATE TABLE SALARY_HISTORY (
EMP_NO EMPNO NOT NULL,
CHANGE_DATE DATE DEFAULT "NOW" NOT NULL,
UPDATER_ID VARCHAR(20) NOT NULL,
OLD_SALARY SALARY NOT NULL,
PERC_CHANGE DOUBLE PRECISION DEFAULT 0 NOT NULL
CHECK (PERC_CHANGE BETWEEN -50 AND 50),
NEW_SALARY COMPUTED BY
(OLD_SALARY + OLD_SALARY * PERC_CHANGE / 100),
PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID),
FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO));
Данный пример создает таблицу, где среди других полей имеется вычисляемое
(физически не существующее) поле NEW_SALARY, значение которого вычисляется
по значениям двух других полей (OLD_SALARY и PERC_CHANGE).
11.3. ЗАКЛЮЧЕНИЕ
Итак, мы рассмотрели, как создавать таблицы с помощью SQL-выражений.
Этот процесс, хотя и не столь удобен, как интерактивное средство Database Desktop,
однако обладает наиболее гибкими возможностями по настройке Вашей системы и
управления ее связями.
12. ФИЛЬТРАЦИЯ И СОРТИРОВКА ДАННЫХ С
ИСПОЛЬЗОВАНИЕМ SQL
12.1. КОМАНДЫ ЯЗЫКА МАНИПУЛИРОВАНИЯ ДАННЫМИ
12.1.1. Оператор Select
Наиболее важной командой языка манипулирования данными является
команда SELECT, с ее помощью возможно выполнение базовых операций
реляционных баз данных. Синтаксис у нее такой:
SELECT список_столбцов
FROM список_таблиц
[WHERE условие_выборки]
73
[ORDER BY направление_сортировки]
Операторы SELECT должны содержать слова SELECT и FROM; другие
ключевые слова, такие как WHERE или ORDER BY, являются необязательными.
За ключевым словом SELECT следуют сведения о том, какие именно поля
необходимо включить в результирующий набор данных. Звездочка (*) обозначает
все поля таблицы, например:
SELECT *
Для выбора одной колонки применяется следующий синтаксис:
SELECT Capital
Пример выбора нескольких колонок имеет вид:
SELECT Name, Capital, Continent
Если выбор данных осуществляется из нескольких таблиц и при этом
выбираются одноименные поля из разных таблиц, следует ссылаться на имена
таблиц для полной идентификации полей, включаемых в результирующий набор
данных, например:
SELECT Customers.Name, Country. Name
12.1.2. Ключевое слово From
Для указания имен таблиц, из которых выбираются записи, применяется
ключевое слово FROM, например, чтобы получить все записи из таблицы Country,
необходимо использовать следующий запрос:
SELECT * FROM Country
Результатом запроса будет набор данных содержащих в себе таблицу Country.
Если в результирующем наборе данных нужны только поля CompanyName и
ContactName, мы можем ввести следующее предложение SELECT:
SELECT Name, Capital FROM Country
Пример запроса к более чем одной таблице приведен ниже:
SELECT Country.Name, Customers.FirstName
FROM Country, Customers
12.1.3. Фильтрация запросов с помощью ключевого слова Where
Для фильтрации результатов, возвращаемых оператором SELECT, можно
использовать предложение WHERE, синтаксис которого имеет вид:
WHERE BOOL1 [{AND | OR} BOOL2 […]]
Например, вместо получения полного списка стран можно ограничиться
только теми из них, у которых значение поля Population равно 800000:
SELECT * FROM Country
WHERE Population = 800000
В предложении WHERE можно использовать различные выражения,
например:
SELECT * FROM Country
WHERE Continent = ‘South America’ AND Area > 100000
или:
SELECT Name, Capital FROM Country
WHERE Continent <> ‘South America’ OR Population <= 10000000
74
Кроме шести операторов отношений: =, <, >, >=, <=, <>, можно использовать
специальные операторы сравнения:
Оператор BETWEEN применяется для проверки нахождения
результирующего значения внутри заданного интервала, например:
SELECT Name, Continent
FROM Country
WHERE Name BETWEEN ‘C’ AND ‘D’
Выберет два столбца Name и Continent из таблицы Country при условии, что
название страны начинается с буквы ‘С’.
Для сопоставления данных с маской применяется ключевое слово LIKE,
рассмотрим аналогичный пример:
SELECT Name, Continent
FROM Country
WHERE Name LIKE ‘C%’
В данной маске символ ‘%’ (процент) заменяет любую последовательность
символов, а символ ‘_’ (подчеркивание) — один любой символ, т.о. результат
запроса будет совпадать с предыдущим примером.
Можно использовать сложную маску, например, чтобы найти все страны в
названии столиц которых есть символы ‘si’, необходимо применить следующий
запрос:
SELECT *
FROM Country
WHERE Capital LIKE ‘%si%’
Используя оператор IN, можно задать список значений, в котором должно
содержаться значение поля:
SELECT Name, Continent
FROM Country
WHERE Capital IN (‘Brasilia’, ‘Santiago’, ‘Quito’)
Результат этого запроса будет аналогичен результату следующего:
SELECT Name, Continent
FROM Country
WHERE Capital = ‘Brasilia’
OR Capital = ‘Santiago’
OR Capital = ‘Quito’
12.1.4. Сортировка с помощью Order By
Предложение ORDER BY (необязательное) применяется для сортировки
результирующего набора данных по одной или нескольким колонкам. Для
определения порядка сортировки используются ключевые слова ASC (по
возрастанию) или DESC (по убыванию). По умолчанию данные сортируются по
возрастанию. Синтаксис предложения ORDER BY имеет вид:
ORDER BY Колонка1 [{ASC | DESC}]
[, Колонка2 [{ASC | DESC}] [,…]
Например, для сортировки сотрудников по фамилии и затем по имени следует
использовать следующий SQL-запрос:
SELECT LastName, FirstName, Salary
FROM Employee
75
ORDER BY LastName, FirstName
Если сортировка данных требуется в убывающем порядке, используется
ключевое слово DESC:
SELECT LastName, FirstName, Salary
FROM Employee
ORDER BY LastName DESC, FirstName DESC
12.2. ПРИМЕРЫ SQL-ЗАПРОСОВ С ИСПОЛЬЗОВАНИЕМ ОПЕРАТОРОВ
СОРТИРОВКИ И ФИЛЬТРАЦИИ
Упорядочивание с использованием имен столбцов:
SELECT first_name, last_name, dept_no,
job_code, salary
FROM employee
ORDER BY last_name
получить список сотрудников, упорядоченный по фамилиям в алфавитном
порядке:
FIRST_NAME LAST_NAME DEPT_NO JOB_CODE SALARY
============ ============= ======= ======== ===========
Janet Baldwin 110 Sales 61637.81
Oliver H. Bender 000 CEO 212850.00
Ann Bennet 120 Admin 22935.00
Dana Bishop 621 Eng 62550.00
Kelly Brown 600 Admin 27000.00
Jennifer M. Burbank 622 Eng 53167.50
Kevin Cook 670 Dir 111262.50
Roger De Souza 623 Eng 69482.62
Roberto Ferrari 125 SRep 99000000.00
SELECT first_name, last_name, dept_no,
job_code, salary
FROM employee
ORDER BY last_name DESC
получить список сотрудников, упорядоченный по фамилиям в порядке,
обратном алфавитному:
FIRST_NAME LAST_NAME DEPT_NO JOB_CODE SALARY
============ ============= ======= ======== ===========
Katherine Young 623 Mngr 67241.25
Bruce Young 621 Eng 97500.00
Michael Yanowski 100 SRep 44000.00
Takashi Yamamoto 115 SRep 7480000.00
Randy Williams 672 Mngr 56295.00
K. J. Weston 130 SRep 86292.94
Claudia Sutherland 140 SRep 100914.00
76
Walter Steadman 900 CFO 116100.00
Willie Stansbury 120 Eng 39224.06
Roger Reeves 120 Sales 33620.62
Столбец, определяющий порядок вывода строк, не обязательно дожен
присутствовать в списке выбираемых элементов (столбцов):
SELECT first_name, last_name, dept_no,
job_code
FROM employee
ORDER BY salary
получить список сотрудников, упорядоченный по их зарплате:
FIRST_NAME LAST_NAME DEPT_NO JOB_CODE
=============== =============== ======= ========
Ann Bennet 120 Admin
Kelly Brown 600 Admin
Sue Anne O'Brien 670 Admin
Mark Guckenheimer 622 Eng
Roger Reeves 120 Sales
Bill Parker 623 Eng
Предикат BETWEEN задает диапазон значений, для которого выражение
принимает значение true. Разрешено также использовать конструкцию NOT
BETWEEN.
SELECT first_name, last_name, salary
FROM employee
WHERE salary BETWEEN 20000 AND 30000
получить список сотрудников, годовая зарплата которых больше 20000 и
меньше 30000:
FIRST_NAME LAST_NAME SALARY
=============== ========== ===============
Ann Bennet 22935.00
Kelly Brown 27000.00
Тот же запрос с использованием операторов сравнения будет выглядеть
следующим образом:
SELECT first_name, last_name, salary
FROM employee
WHERE salary >= 20000
AND salary <= 30000
получить список сотрудников, годовая зарплата которых больше 20000 и
меньше 30000:
77
FIRST_NAME LAST_NAME SALARY
=============== ========== ===============
Ann Bennet 22935.00
Kelly Brown 27000.00
Запрос с предикатом BETWEEN может иметь следующий вид:
SELECT first_name, last_name, salary
FROM employee
WHERE last_name BETWEEN "Nelson" AND "Osborne"
получить список сотрудников, фамилии которых начинаются с “Nelson” и
заканчиваются “Osborne”
FIRST_NAME LAST_NAME SALARY
=============== =============== ================
Robert Nelson 105900.00
Carol Nordstrom 42742.50
Sue Anne O'Brien 31275.00
Pierre Osborne 110000.00
Значения, определяющие нижний и верхний диапазоны, могут не являться
реальными величинами из базы данных. И это очень удобно - ведь мы не всегда
можем указать точные значения диапазонов.
SELECT first_name, last_name, salary
FROM employee
WHERE last_name BETWEEN "Nel" AND "Osb"
получить список сотрудников, фамилии которых находятся между “Nel” и
“Osb”
FIRST_NAME LAST_NAME SALARY
=============== =============== ================
Robert Nelson 105900.00
Carol Nordstrom 42742.50
Sue Anne O'Brien 31275.00
В данном примере значений “Nel” и “Osb” в базе данных нет. Однако, все
сотрудники, входящие в диапазон, в нижней части которого начало фамилий
совпадает с “Nel” (т.е. выполняется условие “больше или равно”), а в верхней части
фамилия не более “Osb” (т.е. выполняется условие “меньше или равно” - а именно
“O”, “Os”, “Osb”), попадут в выборку. Отметим, что при выборке с использованием
предиката BETWEEN поле, на которое накладывается диапазон, считается
упорядоченным по возрастанию.
78
13. ИНСТРУМЕНТЫ ПОИСКА В БД И ПОДГОТОВКИ ОТЧЕТОВ
Вплоть до шестой версии Delphi имела набор компонентов для создания
отчетов по наборам данных, расположенный на вкладке QReport, в седьмой Delphi
эта вкладка по умолчанию отстутствует, однако появился новый механизм создания
отчетов – Rave Reports v5.0. Разумеется, компоненты QReport по-прежнему
доступны разработчику – пакет DCLQRT70.BPL находится в папке
C:\Program Files\Delphi7\Bin и может быть установлен в Палитру компонентов
обычным способом.
13.1. КОМПОНЕНТЫ ВКЛАДКИ QREPORT
На странице Палитры компонентов QReport расположено более двух десятков
компонентов, применяемых для построения отчетов.
Основным компонентом, на котором строится весь отчет, является QuickRep.
Он представляет ряд возможностей по управлению создаваемым отчетом, включая
формирование заголовка, полос, шрифтов, установок принтера и др. Этот компонент
является визуальным и после его соединения с базой данных может использоваться
как контейнер полос QRBand, составляющих отчет.
QRBand – заготовка для расположения данных, заголовков, титула отчета и
др.
QRSubDetail – дочерняя полоса. Привязывается к родительской полосе и
служит для ее расширения. Любая полоса может стать родительской с помощью
установки значения True в ее свойство HasChild.
QRExpr – применяется для вывода значений, являющихся результатом
вычислений выражений. Алгоритм вычисления выражений строится при помощи
редактора формул данного компонента.
QRSysData – служит для вывода в отчете системной величины: даты,
времени, номера страницы и т.п.
Компоненты QRLabel, QRMemo, QRRichText, QRShape, QRImage,
размещаемые на полосах отчета, являются аналогами обычных компонентов - Label,
Memo, RichText, Shape, QRImage. Основной особенностью соответствующих
компонентов QuickReport является их способность печататься в тех полосах отчета
QRBand, в которых они размещены. Компоненты имеют два свойства,
отсутствующих в обычных компонентов: Frame и Size.
Свойство Frame имеет ряд подсвойств, определяющих рамку вокруг
компонента: Color - цвет, Style - стиль, Width - ширина, DrawBotton, DrawLeft,
DrawRight, DrawTop - определяют наличие рамки соответственно внизу, слева,
справа и вверху компонента.
Свойства Size имеет подсвойства, определяющие размер и место размещения
компонента при печати. Все определяется в единицах измерения, заданных
свойством Units компонента QuickRep.
Некоторые компоненты имеют свойство AlignToBand - выравнивание в
полосе. Если это свойство установить в true, то компонент будет выровнен по краю
79
полосы, заданному свойством Alignment: taLeftJustify - влево, taCenter - по
центру, taRightJustify - вправо.
13.2. КОМПОНЕНТ QUICKREP
Компонент представляет собой разлинованную сетку, являющуюся заготовкой
для будущего отчета. На ней располагаются все остальные составные части отчета.
Основные свойства QuickRep:
Bands: TQuickRepBands – содержит свойства типа boolean, которые после
установки в них значений True включают в отчет соответствующие полосы:
HasColumnHeader – заголовки столбцов;
HasDetail – информацию по каждой записи;
HasPageFooter – подвал страницы;
HasPageHeader – заголовок страницы;
HasSummary – подвал отчета;
HasTitle – заголовок отчета.
Dataset: TDataSet – указывает набор данных (TTable, TQuery), на основе
которых создается отчет.
Основные методы QuickRep:
procedure Prepare; - готовит отчет для вывода в файл, необходимо вызывать
этот метод перед каждым сохранением отчета;
procedure Preview; - выводит стандартное окно предварительного просмотра;
procedure Print; - печатает отчет на принтере;
Пример сохранения отчета в файл:
procedure TForm1.Button1Click(Sender: TObject);
begin
try
QuickRep1.Prepare;
QuickRep1.QRPrinter.Save(‘MyReport.qrp’);
finally
QuickRep1.QRPrinter.Free;
end;
end;
Чтобы на этапе конструирования просмотреть в окне предварительного
просмотра содержимое отчета в том виде, как он будет выводиться на печать, нужно
выбрать опцию Preview во вспомогательном меню компонента QuickRep. Для
изменения параметров этого отчета следует выбрать там же команду Report Settings.
13.3. КОМПОНЕНТ QRBAND
Компоненты QRBand являются основными частями отчета и используются
для размещения на них отображающих компонентов, таких как QRLabel, QRDBText,
QRImage и т.п.
Основные свойства:
BandType типа TQRBandType указывает назначение полосы.
80
Frame: TQRFrame - содержит свойства, которые управляют отображением
рамок вокруг компонента, их толщиной, цветом.
13.4. КОМПОНЕНТ QREXPR
Если требуется вывести некоторую итоговую информацию для каких-либо
данных, представленных в текущем отчете, такию как сумма числовых значений
какого-либо столбца, максимальное значение поля и пр., используется специальный
компонент QRExpr.
Выражения в QRExpr формируются с помощью специального редактора
Expression Wizard, который вызывается в окне Object Inspector кнопкой […] в поле
данных свойства Expression этого компонента (рис. 29)
Рис. 29 - Интерфейс Expression Wizard
Нажатие на кнопку Database field вызывает диалог, в котором можно выбрать
столбец из набора данных, кнопка Function вызывает диалог выбора функции,
математической или статистической операции и т.д. При нажатии кнопки Variable
можно воспользоваться одной из предопределенных переменных (текущая
страница, название приложения и пр.), либо создать новую. Кнопка Validate
позволяет проверить правильность введенного выражения.
Примеры выражений
Вывод максимального значения поля Area
MAX(Table1.Area)
Вывод суммы всех значений из поля Population
SUM(Table1.Population)
Вывод текущей страницы в формате: «Страница: N»
'Страница: '+PAGENUMBER