Иптышев А.А. Курс лекций по дисциплине базы данных

Подождите немного. Документ загружается.

Format('SELECT * FROM %s WHERE %s=%d', [tblName, fldName,

fldValue])

Результатом команды форматирования будет

SELECT * FROM Employee WHERE EmpNo=2

Такая функциональность обеспечивает чрезвычайную гибкость при

динамическом выполнении запроса.

10.2 Контроль запросов с помощью SQL Monitor

SQL Monitor (рис. 27) используется для контроля запросов, пересылае-

мых клиентским приложением серверу баз данных посредством BDE, и их

результатов, а также измерения времени между ними. Для его запуска следу-

ет выбрать пункт SQL Monitor из меню Database Delphi. Главное окно SQL

Monitor состоит из двух частей. В верхней части отображаются последова-

тельно генерируемые SQL-предложения и сведения об откликах сервера, а

также порядковый номер и время их наступления, а в нижней части - полный

текст SQL-запроса. Список, отображаемый в верхнем окне, можно сохранить

в файле для дальнейшего анализа. На рис.27 представлен типичный вывод

сведений при работе приложения, рассмотренного в предыдущей статье дан-

ного цикла.

Рис. 27 Интерфейс программы SQL Monitor

10.3 SQL Explorer

SQL Explorer (рис.28) является инструментом для создания статических

запросов. Для его запуска следует выбрать пункт в меню Database команду

Explore или запустить его в качестве отдельного приложения. В левой части

окна Explorer на странице Databases представлены в виде раскрывающегося

списка свойства всех описанных в файле конфигурации BDE псевдонимов. В

правой части окна можно просмотреть содержимое таблиц, ввести и выпол-

нить SQL-запрос к таблице, а также получить сведения о псевдонимах баз

данных (драйвер БД, местоположение, используемый языковый драйвер и

иные параметры, содержащиеся в файле конфигурации BDE), сведения о

таблицах (тип таблицы, версия, дата последнего обновления и др.) и, при не-

обходимости, внести в них изменения.

Рис. 28 Интерфейс программы SQL Explorer

Компонент TQuery позволяет использовать язык SQL для получения

определенного набора данных из одной или нескольких таблиц. В Delphi

компонент TQuery можно использовать как с серверами данных файлового

типа (например, Paradox и dBASE), так и с серверами SQL. После назначения

свойству DatabaseName компонента TQuery псевдонима или пути к каталогу,

можно ввести в его свойство SQL те операторы языка SQL, которые необхо-

димо выполнить в выбранной базе данных. Например, если объект Queryl

связан с псевдонимом DBDEMOS, следующий код будет возвращать все за-

писи в таблице BIOLIFE, в которых значение поля Length (cm) больше 100:

select * from BIOLIFE where BIOLIFE."Length (cm)" > 100

Подобно другим наборам данных, запрос будет выполняться в том слу-

чае, если его свойству Active присвоено значение True или после вызова ме-

тода Open (). Если же требуется выполнить запрос, который не возвращает

результирующий набор данных

(например, insert into), для его выполнения необходимо использовать

метод ExecuteSQL(), а не метод Ореп().

Другим важным свойством компонента TQuery является свойство

RequestLive. Это свойство показывает, можно ли редактировать полученный

результирующий набор данных. Если возвращаемые запросом данные требу-

ется редактировать, его значение следует установить равным True.

Простая установка значения свойства RequestLive не гарантирует, что

результирующий набор данных можно будет редактировать. В зависимости

от структуры запроса, ядро BDE может оказаться неспособным получить ре-

зультирующий набор, доступный для редактирования. Например, запросы, в

которых содержится ключевое слово having, используется функция to_date

или поле с абстрактным типом данных (ADT — Abstract Data Type), предос-

тавляют результаты, которые нельзя отредактировать. (Для получения пол-

ного перечня существующих ограничений обратитесь к документации по

BDE.) Чтобы определить, можно ли будет редактировать результаты запроса,

проверьте перед его открытием значение свойства CanModif у.

В следующей главе мы подробнее поговорим о свойствах компонента

TQuery, таких как параметрические запросы и SQL-оптимизация.

Тема 11 Синтаксис основных запросов, их опций и

параметров

11.1 Язык SQL. Введение

11.1.1 Состав языка SQL

Structured Query Language (SQL) представляет собой непроцедурный

язык, используемый для управления данными реляционных СУБД. В этом

языке отсутствуют алгоритмические конструкции, такие как метки, операто-

ры цикла, условные переходы и др. Язык SQL предназначен для манипули-

рования данными в реляционных базах данных, определения структуры баз

данных и для управления правами доступа к данным в многопользователь-

ской среде.

Поэтому, в язык SQL в качестве составных частей входят:

- язык манипулирования данными (Data Manipulation Language, DML)

- язык определения данных (Data Definition Language, DDL)

- язык управления данными (Data Control Language, DCL).

11.1.2 Обзор

На данном уроке мы познакомимся возможностью создания таблиц че-

рез посылку SQL-запросов. С помощью Database Desktop удобно создавать

или локальные базы данных или только простейшие SQL-серверные базы

данных, состоящие из небольшого числа таблиц, не очень сильно связанных

друг с другом. Если же Вам необходимо создать базу данных, состоящую из

большого числа таблиц, имеющих сложные взаимосвязи, можно воспользо-

ваться языком SQL. При этом можно воспользоваться компонентом Query в

Delphi, каждый раз посылая по одному SQL-запросу, а можно записать всю

последовательность SQL-предложений в один так называемый скрипт и по-

слать его на выполнение. Конечно, для этого нужно хорошо знать язык SQL,

но сложного в этом ничего нет! Конкретные реализации языка SQL незначи-

тельно отличаются в различных SQL-серверах, однако базовые предложения

остаются одинаковыми для всех реализаций.

11.2 Создание таблиц с помощью SQL

Если Вы хотите воспользоваться компонентом TQuery, сначала помес-

тите его на форму. После этого настройте свойство DatabaseName на нужный

Вам алиас. После этого можно ввести SQL-предложение в свойство SQL. Для

выполнения запроса, изменяющего структуру, вставляющего или обновляю-

щего данные на сервере, нужно вызвать метод ExecSQL компонента TQuery.

Для выполнения запроса, получающего данные с сервера (т.е. запроса, в ко-

тором основным является оператор SELECT), нужно вызвать метод Open

компонента TQuery. Это связано с тем, что BDE при посылке запроса типа

SELECT открывает так называемый курсор, с помощью которого осуществ-

ляется навигация по выборке данных (подробней об этом см. в уроке, посвя-

щенном TQuery).

Приведем упрощенный синтаксис SQL-предложения для создания таб-

лицы на SQL-сервере InterBase (более полный синтаксис можно посмотреть в

online-справочнике по SQL, поставляемом с локальным InterBase):

CREATE TABLE table

(<col_def> [, <col_def> | <tconstraint> ...]);

где

table - имя создаваемой таблицы,

<col_def> - описание поля,

<tconstraint> - описание ограничений и/или ключей (квадратные скобки

[] означают необязательность, вертикальная черта | означает “или”).

Описание поля состоит из наименования поля и типа поля (или домена

- см. урок 9), а также дополнительных ограничений, накладываемых на поле:

<col_def> = col {datatype | COMPUTED BY (<expr>) | domain}

[DEFAULT {literal | NULL | USER}]

[NOT NULL] [<col_constraint>]

[COLLATE collation]

Здесь

col - имя поля;

datatype - любой правильный тип SQL-сервера (для InterBase такими

типами являются SMALLINT, INTEGER, FLOAT, DOUBLE PRECISION,

DECIMAL, NUMERIC, DATE, CHAR, VARCHAR, NCHAR, BLOB), сим-

вольные типы могут иметь CHARACTER SET - набор символов, определяю-

щий язык страны. Для русского языка следует задать набор символов

WIN1251;

COMPUTED BY (<expr>) - определение вычисляемого на уровне сер-

вера поля, где <expr> - правильное SQL-выражение, возвращающее единст-

венное значение;

domain - имя домена (обобщенного типа), определенного в базе дан-

ных;

DEFAULT - конструкция, определяющая значение поля по умолчанию;

NOT NULL - конструкция, указывающая на то, что поле не может быть

пустым;

COLLATE - предложение, определяющее порядок сортировки для вы-

бранного набора символов (для поля типа BLOB не применяется). Русский

набор символов WIN1251 имеет 2 порядка сортировки - WIN1251 и

PXW_CYRL. Для правильной сортировки, включающей большие буквы, сле-

дует выбрать порядок PXW_CYRL.

Описание ограничений и/или ключей включает в себя предложения

CONSTRAINT или предложения, описывающие уникальные поля, первич-

ные, внешние ключи, а также ограничения CHECK (такие конструкции могут

определяться как на уровне поля, так и на уровне таблицы в целом, если они

затрагивают несколько полей):

<tconstraint> = [CONSTRAINT constraint <tconstraint_def>]

Здесь

<tconstraint_def> = {{PRIMARY KEY | UNIQUE} (col[,col...])

| FOREIGN KEY (col [, col ...]) REFERENCES other_table

| CHECK (<search_condition>)}

<search_condition> =

{<val> <operator> {<val> | (<select_one>)}

| <val> [NOT] BETWEEN <val> AND <val>

| <val> [NOT] LIKE <val> [ESCAPE <val>]

| <val> [NOT] IN (<val> [, <val> ...] |

<val> = {

col [<array_dim>] | <constant> | <expr> | <function>

| NULL | USER | RDB$DB_KEY } [COLLATE collation]

<constant> = num | "string" | charsetname "string"

<function> = {

COUNT (* | [ALL] <val> | DISTINCT <val>)

| SUM ([ALL] <val> | DISTINCT <val>)

| AVG ([ALL] <val> | DISTINCT <val>)

| MAX ([ALL] <val> | DISTINCT <val>)

| MIN ([ALL] <val> | DISTINCT <val>)

| CAST (<val> AS <datatype>)

| UPPER (<val>)

| GEN_ID (generator, <val>)

}

<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}

<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. Для таблицы определен первичный ключ, состоящий из трех

полей 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 условие_выборки]

[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 […]]