6. Язык sql и его использование в Delphi

Язык SQL (Structured Query Language – структурированный язык запросов) на сегодняшний день стал фактически стандартом среди языков, обеспечивающих операции над базами данных (создание, изменение, поиск) и включен в состав языковых средств многих систем управления базами данных. Имеется он и в Delphi. В первом параграфе рассмотрим сам язык, а во втором – его использование в Delphi.

6.1. Элементы языка sql

Для большей наглядности приведем его описание на примерах, базирующихся на двух приведенных таблицах.

Таблица VENDOR

VNUM	VNAME	VSTREET
V1	Chops Ahoy	Main
V2	The Board Walk	Back
V3	Tube City	Main
V4	The Drive-in	Middle
V5	Disks ‘ Us	Front
V6	Monitor Mania	Front

Таблица PART

PNUM	PNAME	PRICE	PVNUM	PUNUM
P1	XT	1000	V1	U1
P2	XT	1200	V2	U2
P3	AT	2000	V1	U3
P4	Mono	125	V3	U1
P5	mono	125	V6	U2
P6	Hi-res	325	V6	U3
P7	20 Meg	350	V4	U1
P8	20 Meg	300	V5	U2
P9	40 Meg	450	V5	U3

Простейшая программа на SQL:

SELECT *

FROM vendor

SELECT, FROM – операторы языка, vendor – имя обрабатываемого файла. Как Pascal, так и SQL нечувствителен к использованию больших или маленьких букв. Для большей ясности программ рекомендуют операторы языка писать большими буквами и с новой строки, все остальное – маленькими буквами. Для написания программ на SQL необходимо знать структуру файла, точнее, имя файла, имена и типы полей. Наша простейшая программа предназначена для выдачи файла полностью.

Как видно из самого названия языка SQL, он предназначен в первую очередь для формулирования запросов. Простейшие запросы ограничивают количество выдаваемой информации:

SELECT pname,punum

FROM part

WHERE price > 500

Нетрудно видеть, что перечисление полей за оператором SELECT ограничивает их число (в теории реляционных баз данных это называется проекцией, она вызывает сжатие исходной таблицы по горизонтали), а оператор WHERE ограничивает выдачу данных записями, удовлетворяющими заданному условию (в теории реляционных баз данных это называется селекцией и она вызывает сжатие таблицы по вертикали).

Оператор SELECT имеет более широкие возможности, рассмотрим здесь две из них.

• В операторе SELECT можно использовать арифметические выражения и функции (sin, cos, exp, log и т.д.) над числовыми полями (вот почему надо знать тип данных в файле!):

• SELECT pname, price*0.75, log(price)

• FROM part

• Moжно использовать дополнение DISTINCT

SELECT DISTINCT vstreet

FROM vendor

В результате будут выданы только разные значения поля vstreet, в нашем случае ответом является {Back Front Main Middle}.

Оператор FROM имеет следующие дополнительные возможности:

• Задание полного имени файла, например, “D\MyDan\Vendor.db”, это нужно при одновременной обработке файлов из разных директорий или когда путь отличается от текущего (задание текущей директории рассмотрим ниже).

• Введение псевдонимов, это нужно при одновременной обработке двух или более файлов. Например,

SELECT pp.pname, vv.vname

FROM “D\MyDan\Vendor.db” vv, part pp

WHERE vv.vnum=pp.pvnum

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

Примечание:в наших таблицах нет полей с одинаковыми именами, поэтому псевдонимы необязательны.

Вопрос читателю: какую операцию реляционной алгебры реализует эта программа? В случае затруднений с ответом – см. выше! Ответ на последний запрос показан на рис. 6.1.

Оператор WHERE предназначен для выражения условий поиска и должен содержать логическое выражение. Логическое выражение состоит из знакомых нам операций сравнения =, >, >=, <, <=, <>; логических операций OR, AND, NOT; для изменения приоритета можно использовать скобки. Правила написания логических выражений аналогичны правилам их написания на Pascal’e; необходимо запомнить, что в SQL символьные константы заключают в кавычки, а не в апострофы.

Рис. 6.1. Ответ на запрос

Пример.

SELECT *

FROM part

WHERE price>500 AND pvnum=“V1”

Ответ

PNUM	PNAME	PRICE	PVNUM	PUNUM
P1	XT	1000	V1	U1
P3	AT	2000	V1	U3

Имеются и свои предикаты SQL, не имеющие аналогов в Pascal’е. Рассмотрим их подробнее.

Предикат BETWEEN (между).

price BETWEEN 200 AND 500 эквивалентен (price >=200 AND price <=500)

Предикат IN

pname IN (“XT”,”AT”) эквивалентен (pname=“XT” OR pname=“AT”)

С помощью предиката IN можно составить сложные запросы, содержащие подзапросы (оператор subSELECT):

SELECT vname

FROM vendor

WHERE vnum IN

(SELECT pvnum

FROM part

WHERE pname =“XT”)

Ответ: Chips Ahoy

The Board Walk

В подзапросе можно выделить только одно поле т.е. за оператором SELECT можно писать имя только одного поля. Почему так сделано?

За оператором WHERE можно писать условие по полям нескольких файлов. Такой пример был приведен выше и его результат на рис. 6.1.

Приведенными тремя операторами возможности языка SQL не ограничиваются. Продолжим изучение операторов.

Оператор ORDER BY позволяет упорядочить выдаваемые записи.

Программа

SELECT vname,vstreet

FROM vendor

ORDER BY vname

выдает записи, упорядоченные по алфавиту по полю vname. По умолчанию упорядочивается по возрастанию.

Программа

SELECT vstreet, vname

FROM vendor

ORDER BY 1 ASC, 2 DESC

выдает поля vstreet и vname, упорядоченные по возрастанию по vstreet и при равенстве его значений по убыванию vname. Вместо имен полей можно писать их номера для сокращения записи, если в SELECT кроме имен полей используются функции (об этом позже), то при ссылках на них допускается указывать только их номера.

Оператор группировки GROUP BY позволяет группировать данные с одинаковыми значениями в одном и том же поле в разных записях и вычислять разные характеристики для полученных групп.

Программа

SELECT punum, COUNT(*), SUM(price)

FROM part

GROUP BY punum

выдает следующую таблицу:

punum	COUNT(*)	SUM OF price
U1	3	1475
U2	3	1625
U3	3	2775

В первой строке для записей, где в поле punum имеется значение U1 заданы их количество и сумма значений в поле price.

Оставим читателю вопрос: что выдает следующая программа?

SELECT pname, punum, AVG(price), COUNT(*)

FROM part

GROUP BY pname, punum

ORDER BY 3

Примечание:написание последней строки в виде ORDER BY AVG(price) является ОШИБКОЙ! См. об этом выше.

Правила, которые необходимо выполнять при использовании оператора GROUP BY:

• при наличии в программе оператора GROUP BY в SELECT можно использовать только имена полей, включенных в GROUP BY и имена функций: AVG (среднее арифметическое), COUNT (количество), MAX (максимальное значение), MIN (минимальное значение), SUM (сумма); подумайте над вопросом: почему такое ограничение?

• если в программе имеются операторы GROUP BY и SELECT одновременно, то сначала формируют группы и затем осуществляется селекция.

С помощью оператора GROUP BY можно легко запрограммировать задачи, программы для которых на Pascal’е были бы весьма длинными. Допустим, что имеется файл с кадровыми данными (в том числе и фамилия, имя, отчество) студентов. Требуется определить, какое имя сколько раз встречается. Пусть имеются сведения об автомобилестроительных компаниях: название компании, модель выпускаемой машины, ее характеристики. Естественно, что каждая компания выпускает много моделей. Определить для каждой компании самую скоростную, дорогую, дешевую, экономичную и т.д. машину.

Оператор HAVING непосредственно связан с оператором GROUP By и позволяет задавать дополнительные условия. Программа

SELECT pname, AVG(price), COUNT(*)

FROM part

GROUP BY pname

HAVING AVG(price)>800

выдает ответ:

pname	AVERAGE OF price	COUNT(*)
AT	2000	1
XT	1100	2

Здесь приведено далеко не полное описание языка SQL. Мы ограничились лишь основными его возможностями и базовыми операторами.