В предыдущих двух статьях данного цикла, опубликованных в © 6 и 7 нашего журнала, мы рассмотрели различные механизмы доступа к данным, включая ADO, BDE и их альтернативы. Теперь мы знаем, как выбрать технологию доступа к данным для той или иной пары <СУБД - средство разработки>.

Располагая технологией доступа к данным, можно наконец подумать и о том, каким образом следует манипулировать самими данными и метаданными. Способы манипуляции могут быть специфичными для данной СУБД (например, использование объектов клиентской части этой СУБД для доступа к объектам баз данных) или для данного механизма доступа к данным. Тем не менее существует более или менее универсальный способ манипуляции данными, поддерживаемый почти всеми серверными реляционными СУБД и большинством универсальных механизмов доступа к данным (в том числе при использовании их совместно с настольными СУБД). Этот способ - применение языка SQL (Structured Query Language - язык структурированных запросов). Ниже мы рассмотрим назначение и особенности этого языка, а также изучим, как с его помощью извлекать и суммировать данные, добавлять, удалять и модифицировать записи, защищать данные от несанкционированного доступа, создавать базы данных. Для более подробного изучения SQL мы можем порекомендовать книги Мартина Грабера <Введение в SQL> (М., Лори, 1996) и (М., Лори, 1997).

Введение

Structured Query Language представляет собой непроцедурный язык, используемый для управления данными реляционных СУБД. Термин <непроцедурный> означает, что на данном языке можно сформулировать, что нужно сделать с данными, но нельзя проинструктировать, как именно это следует сделать. Иными словами, в этом языке отсутствуют алгоритмические конструкции, такие как метки, операторы цикла, условные переходы и др.

Язык SQL был создан в начале 70-х годов в результате исследовательского проекта IBM, целью которого было создание языка манипуляции реляционными данными. Первоначально он назывался SEQUEL (Structured English Query Language), затем - SEQUEL/2, а затем - просто SQL. Официальный стандарт SQL был опубликован ANSI (American National Standards Institute - Национальный институт стандартизации, США) в 1986 году (это наиболее часто используемая ныне реализация SQL). Данный стандарт был расширен в 1989 и 1992 годах, поэтому последний стандарт SQL носит название SQL92. В настоящее время ведется работа над стандартом SQL3, содержащим некоторые объектно-ориентированные расширения.

Существует три уровня соответствия стандарту ANSI - начальный, промежуточный и полный. Многие производители серверных СУБД, такие как IBM, Informix, Microsoft, Oracle и Sybase, применяют собственные реализации SQL, основанные на стандарте ANSI (отвечающие как минимум начальному уровню соответствия стандарту) и содержащие некоторые расширения, специфические для данной СУБД.

Более подробную информацию о соответствии стандарту версии SQL, используемой в конкретной СУБД, можно найти в документации, поставляемой с этой СУБД.

Как работает SQL

Давайте рассмотрим, как работает SQL. Предположим, что имеется база данных, управляемая с помощью какой-либо СУБД. Для извлечения из нее данных используется запрос, сформулированный на языке SQL. СУБД обрабатывает этот запрос, извлекает запрашиваемые данные и возвращает их. Этот процесс схематически изображен на рис. 1.

Рис. 1

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

Обратите внимание на то, что SQL сам по себе не является ни СУБД, ни отдельным продуктом. Это язык, применяемый для взаимодействия с СУБД и являющийся в определенном смысле ее неотъемлемой частью.

Операторы SQL

SQL содержит примерно 40 операторов для выполнения различных действий внутри СУБД. Ниже приводится краткое описание категорий этих операторов.

Data Definition Language (DDL)

Data Definition Language содержит операторы, позволяющие создавать, изменять и уничтожать базы данных и объекты внутри них (таблицы, представления и др.). Эти операторы перечислены в табл. 1.

Таблица 1

Оператор	Описание
CREATE TABLE	Применяется для добавления новой таблицы к базе данных
DROP TABLE	Применяется для удаления таблицы из базы данных
ALTER TABLE	Применяется для изменения структуры имеющейся таблицы
CREATE VIEW	Применяется для добавления нового представления к базе данных
DROP VIEW	Применяется для удаления представления из базы данных
CREATE INDEX	Применяется для создания индекса для данного поля
DROP INDEX	Применяется для удаления существующего индекса
CREATE SCHEMA	Применяется для создания новой схемы в базе данных
DROP SCHEMA	Применяется для удаления схемы из базы данных
CREATE DOMAIN	Применяется для создания нового домена
ALTER DOMAIN	Применяется для переопределения домена
DROP DOMAIN	Применяется для удаления домена из базы данных

Data Manipulation Language (DML)

Data Manipulation Language содержит операторы, позволяющие выбирать, добавлять, удалять и модифицировать данные. Обратите внимание на то, что эти операторы не обязаны завершать транзакцию, внутри которой они вызваны. Операторы DML представлены в табл. 2.

Таблица 2

Оператор	Описание
SELECT	Применяется для выбора данных
INSERT	Применяется для добавления строк к таблице
DELETE	Применяется для удаления строк из таблицы
UPDATE	Применяется для изменения данных

Иногда оператор SELECT относят к отдельной категории, называемой Data Query Language (DQL).

Transaction Control Language (TCL)

Операторы Transaction Control Language применяются для управления изменениями, выполненными группой операторов DML. Операторы TCL представлены в табл. 3.

Таблица 3

Оператор	Описание
COMMIT	Применяется для завершения транзакции и сохранения изменений в базе данных
ROLLBACK	Применяется для отката транзакции и отмены изменений в базе данных
SET TRANSACTION	Применяется для установки параметров доступа к данным в текущей транзакции

Data Control Language (DCL)

Операторы Data Control Language, иногда называемые операторами Access Control Language, применяются для осуществления административных функций, присваивающих или отменяющих право (привилегию) использовать базу данных, таблицы в базе данных, а также выполнять те или иные операторы SQL. Операторы DCL представлены в табл. 4.

Таблица 4

Оператор	Описание
GRANT	Применяется для присвоения привилегии
REVOKE	Применяется для отмены привилегии

Cursor Control Language (CCL)

Операторы Cursor Control Language используются для определения курсора, подготовки SQL-предложений для выполнения, а также для некоторых других операторов. Операторы CCL представлены в табл. 5.

Таблица 5

Оператор	Описание
DECLARE CURSOR	Применяется для определения курсора для запроса
EXPLAIN	Применяется для описания плана запроса. Этот оператор представляет собой расширение SQL для Microsoft SQL Server 7.0. Он не обязан выполняться в других СУБД. Например, в случае Oracle следует использовать оператор EXPLAIN PLAN
OPEN CURSOR	Применяется для открытия курсора при получении результатов запроса
FETCH	Применяется для получения строки из результатов запроса
CLOSE CURSOR	Применяется для закрытия курсора
PREPARE	Применяется для подготовки оператора SQL для выполнения
EXECUTE	Применяется для выполнения оператора SQL
DESCRIBE	Применяется для описания подготовленного запроса

Все операторы SQL имеют вид, показанный на рис. 2.

Рис. 2

Каждый оператор SQL начинается с глагола, представляющего собой ключевое слово, определяющее, что именно делает этот оператор (SELECT, INSERT, DELETE...). В операторе содержатся также предложения, содержащие сведения о том, над какими данными производятся операции. Каждое предложение начинается с ключевого слова, такого как FROM, WHERE и др. Структура предложения зависит от его типа - ряд предложений содержит имена таблиц или полей, некоторые могут содержать дополнительные ключевые слова, константы или выражения.

Ключевые слова ANSI/ISO SQL92

Некоторые ключевые слова, определенные в стандарте ANSI SQL, не могут быть использованы в качестве имен объектов баз данных (таблиц, полей, имен пользователей). Эти ключевые слова приведены в табл. 6.

Таблица 6.

ABSOLUTE	CROSS	GET	NEXT	SPACE
ACTION	CURRENT	GLOBAL	NO	SQL
ADD	CURRENT_DATE	GO	NOT	SQLCODE
ALL	CURRENT_TIME	GOTO	NULL	SQLERROR
ALLOCATE	CURRENT_TIMESTAMP	GRANT	OCTET_LENGTH	SQLSTATE
ALTER	CURRENT_USER	GROUP	OF	SUBSTRING
AND	CURSOR	HAVING	ON	SUM
ANY	DATE	HOUR	ONLY	SYSTEM_USER
ARE	DAY	IDENTITY	OPEN	TABLE
AS	DEALLOCATE	IMMEDIATE	OPTION	TEMPORARY
ASC	DEC	IN	OR	THEN
ASSERTION	DECIMAL	INDICATOR	ORDER	TIME
AT	DECLARE	INITIALLY	OUTER	TIMESTAMP
AUTHORIZATION	DEFAULT	INNER	OUTPUT	TIMEZONE_HOUR
AVG	DEFERRABLE	INPUT	OVERLAPS	TIMEZONE_MINUTE
BEGIN	DEFERRED	INSENSITIVE	PAD	TO
BETWEEN	DELETE	INSERT	PARTIAL	TRAILING
BIT	DESC	INT	POSITION	TRANSACTION
BIT_LENGTH	DESCRIBE	INTEGER	PRECISION	TRANSLATE
BOTH	DESCRIPTOR	INTERSECT	PREPARE	TRANSLATION
BY	DIAGNOSTICS	INTERVAL	PRESERVE	TRIM
CASCADE	DISCONNECT	INTO	PRIMARY	TRUE
CASCADED	DISTINCT	IS	PRIOR	UNION
CASE	DOMAIN	ISOLATION	PRIVILEGES	UNIQUE
CAST	DOUBLE	JOIN	PROCEDURE	UNKNOWN
CATALOG	DROP	KEY	PUBLIC	UPDATE
CHAR	ELSE	LANGUAGE	READ	UPPER
CHARACTER	END	LAST	REAL	USAGE
CHAR_LENGTH	END-EXEC	LEADING	REFERENCES	USER
CHARACTER_LENGTH	ESCAPE	LEFT	RELATIVE	USING
CHECK	EXCEPT	LEVEL	RESTRICT	VALUE
CLOSE	EXCEPTION	LIKE	REVOKE	VALUES
COALESCE	EXEC	LOCAL	RIGHT	VARCHAR
COLLATE	EXECUTE	LOWER	ROLLBACK	VARYING
COLLATION	EXISTS	MATCH	ROWS	VIEW
COLUMN	EXTERNAL	MAX	SCHEMA	WHEN
COMMIT	EXTRACT	MIN	SCROLL	WHENEVER
CONNECT	FALSE	MINUTE	SECOND	WHERE
CONNECTION	FETCH	MODULE	SECTION	WITH
CONSTRAINT	FIRST	MONTH	SELECT	WORK
CONSTRAINTS	FLOAT	NAMES	SESSION	WRITE
CONTINUE	FOR	NATIONAL	SESSION_USER	YEAR
CONVERT	FOREIGN	NATURAL	SET	ZONE
CORRESPONDING	FOUND	NCHAR	SIZE
COUNT	FROM	NULLIF	SMALLINT
CREATE	FULL	NUMERIC	SOME

Стандарт SQL также включает список потенциальных ключевых слов, зарезервированных для последующих версий стандарта SQL. Эти ключевые слова приведены в табл. 7.

Таблица 7

AFTER	EQUALS	OLD	RETURN	TEST
ALIAS	GENERAL	OPERATION	RETURNS	THERE
ASYNC	IF	OPERATORS	ROLE	TRIGGER
BEFORE	IGNORE	OTHERS	ROUTINE	TYPE
BOOLEAN	LEAVE	PARAMETERS	ROW	UNDER
BREADTH	LESS	PENDANT	SAVEPOINT	VARIABLE
COMPLETION	LIMIT	PREORDER	SEARCH	VIRTUAL
CALL	LOOP	PRIVATE	SENSITIVE	VISIBLE
CYCLE	MODIFY	PROTECTED	SEQUENCE	WAIT
DATA	NEW	RECURSIVE	SIGNAL	WHILE
DEPTH	NONE	REF	SIMILAR	WITHOUT
DICTIONARY	OBJECT	REFERENCING	SQLEXCEPTION
EACH	OFF	REPLACE	SQLWARNING
ELSEIF	OID	RESIGNAL	STRUCTURE

С помощью чего можно выполнить SQL-операторы

Все современные серверные СУБД (а также многие популярные настольные СУБД) содержат в своем составе утилиты, позволяющие выполнить SQL-предложение и ознакомиться с его результатом. В частности, клиентская часть Oracle содержит в своем составе утилиту SQL Plus, а Microsoft SQL Server - утилиту SQL Query Analyzer. Именно этой утилитой мы воспользуемся для демонстрации возможностей SQL, а в качестве базы данных, над которой мы будем <экспериментировать>, возьмем базу данных NorthWind, входящую в комплект поставки Microsoft SQL Server 7.0. В принципе, можно использовать другую базу данных и любую другую утилиту, способную выполнять в этой базе данных SQL-предложения и отображать результаты (или даже написать свою, используя какое-либо средство разработки - Visual Basic, Delphi, C++Builder и др.). Однако на всякий случай рекомендуется сделать резервную копию этой базы данных.

Работа с операторами SQL

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

Выбор данных

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

SELECT column-list
FROM table-list
[WHERE where-clause]
[ORDER BY order-by-clause]

Операторы SELECT должны содержать слова SELECT и FROM; другие ключевые слова, такие как WHERE или ORDER BY, являются необязательными.

За ключевым словом SELECT следуют сведения о том, какие именно поля необходимо включить в результирующий набор данных. Звездочка (*) обозначает все поля таблицы, например:

SELECT *

Для выбора одной колонки применяется следующий синтаксис:

SELECT CompanyName

Пример выбора нескольких колонок имеет вид:

SELECT CompanyName, ContactName, ContactTitle

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

SELECT Customers.CompanyName, Shippers.CompanyName

Предложение FROM

Для указания имен таблиц, из которых выбираются записи, применяется ключевое слово FROM, например:

SELECT * FROM Customers

SELECT * FROM Customers

Этот запрос возвратит все поля из таблицы Customers.

Если в результирующем наборе данных нужны только поля CompanyName и ContactName, мы можем ввести следующее предложение SELECT:

SELECT CompanyName, ContactName FROM Customers

SELECT CompanyName, ContactName FROM Customers

Пример запроса к более чем одной таблице приведен ниже:

SELECT Customers.CompanyName, Shippers.CompanyName
FROM Customers, Shippers

SELECT Customers.CompanyName, Shippers.CompanyName

FROM Customers, Shippers

Предложение WHERE

Для фильтрации результатов, возвращаемых оператором SELECT, можно использовать предложение WHERE, синтаксис которого имеет вид:

WHERE expression1 [{AND | OR} expression2 [:]]

Например, вместо получения полного списка продуктов можно ограничиться только теми из них, у которых значение поля CategoryID равно 4:

SELECT * FROM Products
WHERE CategoryID = 4

SELECT * FROM Products
WHERE CategoryID = 4

В предложении WHERE можно использовать различные выражения, например:

SELECT * FROM Products
WHERE CategoryID = 2 AND SupplierID > 10

или:

SELECT ProductName, UnitPrice FROM Products
WHERE CategoryID = 3 OR UnitPrice < 50

или:

SELECT ProductName, UnitPrice FROM Products
WHERE Discontinued IS NOT NULL

Выражение 'IS NOT NULL' означает, что соответствующая колонка результирующего набора данных не может содержать пустых значений.

В предложении WHERE можно использовать один из шести операторов отношений, определенных в SQL. Эти операторы приведены в табл. 8.

Таблица 8

Оператор	Описание
<	Меньше
<=	Меньше или равно
<>	Не равно
=	Равно
>	Больше
>=	Больше или равно

Помимо перечисленных выше простых операторов сравнения, можно использовать и специальные операторы сравнения, приведенные в табл. 9.

Таблица 9

Оператор	Описание
ALL	Применяется совместно с операторами сравнения при сравнении со списком значений
ANY	Применяется совместно с операторами сравнения при сравнении со списком значений
BETWEEN	Применяется при проверке нахождения значения внутри заданного интервала (включая его границы)
IN	Применяется для проверки наличия значения в списке
LIKE	Применяется при проверке соответствия значения заданной маске

Приведем несколько примеров применения этих операторов. Для сопоставления данных с маской применяется ключевое слово LIKE:

SELECT CompanyName, ContactName
 FROM Customers
WHERE CompanyName LIKE 'M%'

SELECT CompanyName, ContactName
FROM Customers
WHERE CompanyName LIKE 'M%'

В данной маске символ '%' (процент) заменяет любую последовательность символов, а символ '_' (подчеркивание) - один любой символ. Тот же самый результат может быть получен следующим способом:

SELECT CompanyName, ContactName
 FROM Customers
WHERE CompanyName BETWEEN 'M' AND 'N'

В последнем примере мы можем расширить область поиска. В частности, при поиске компаний с именами, начинающимися с букв от A до C, можно выполнить следующий оператор SELECT:

SELECT CompanyName, ContactName
 FROM Customers
 WHERE CompanyName BETWEEN 'A' AND 'D'

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

SELECT CompanyName, ContactName
FROM Customers
WHERE CompanyName LIKE '%bl%'

SELECT CompanyName, ContactName
FROM Customers
WHERE CompanyName LIKE '%bl%'

Маска '%bl%' показывает, что до и после искомой подстроки может быть любое количество произвольных символов.

Используя оператор IN, можно задать список значений, в котором должно содержаться значение поля:

SELECT CompanyName, ContactName
FROM Customers
WHERE CustomerID IN ('ALFKI', 'BERGS', 'VINET')

Операторы AND, OR и NOT

Мы уже рассматривали пример применения оператора AND для логических операций, связанных с требованием, чтобы запись удовлетворяла двум разным критериям. Рассмотрим следующий запрос:

SELECT CompanyName, ContactName
 FROM Customers
WHERE CompanyName LIKE 'S%' AND Country = 'USA'

Результатом выполнения этого запроса будет список заказчиков, находящихся в США, название которых начинается с буквы S.

Оператор OR позволяет выбрать записи, удовлетворяющие хотя бы одному из перечисленных условий, в то время как оператор NOT используется для исключения из набора данных записей, удовлетворяющих данному условию. Например, можно применить оператор OR для поиска всех заказчиков, либо находящихся в Калифорнии, либо имеющих название, начинающееся с буквы S (и при этом находящихся где угодно):

SELECT CompanyName, ContactName
FROM Customers
 WHERE CompanyName LIKE 'S%' OR Region='CA'

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

Теперь рассмотрим пример применения оператора NOT. Для исключения некоторых заказчиков из результирующего набора данных можно использовать запрос вида:

SELECT CompanyName, ContactName
 FROM Customers
 WHERE Country NOT IN ('USA', 'UK')

В результате выполнения этого запроса мы получим список заказчиков из всех стран, кроме США и Великобритании.

Предложение ORDER BY

Предложение ORDER BY (необязательное) применяется для сортировки результирующего набора данных по одной или нескольким колонкам. Для определения порядка сортировки используются ключевые слова ASC (по возрастанию) или DESC (по убыванию). По умолчанию данные сортируются по возрастанию. Синтаксис предложения ORDER BY имеет вид:

ORDER BY column1 [{ASC | DESC}]
[, column2 [{ASC | DESC}] [,:]

Например, для сортировки сотрудников по фамилии и затем по имени следует использовать следующий SQL-запрос:

SELECT LastName, FirstName, Title
FROM Employees
 ORDER BY LastName, FirstName

Если сортировка данных требуется в убывающем порядке (например, требуется список продуктов в порядке убывания цен), используется ключевое слово DESC:

SELECT ProductName, UnitPrice
 FROM Products
ORDER BY UnitPrice DESC

Связывание таблиц

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

SELECT ProductName, CategoryName
FROM Products, Categories

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

SELECT ProductName, CategoryName
FROM Products, Categories
WHERE Products.CategoryID = Categories.CategoryID

Можно сравнить результаты этих двух запросов.

SELECT ProductName, CategoryName
FROM Products, Categories

SELECT ProductName, CategoryName
FROM Products, Categories
WHERE Products.CategoryID = Categories.CategoryID

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

SELECT column-list
FROM table1, table2
WHERE table1.column1=table2.column2

Следующие несколько примеров связывания таблиц характерны для Microsoft Access и Microsoft SQL Server и могут не работать с другими СУБД, однако мы полагаем, что иллюстрируемая ими функциональность достаточно важна.

Существует несколько типов связывания таблиц. Например, следующий оператор SQL осуществляет так называемое внутреннее соединение таблиц (inner join) - в этом случае в результирующем наборе данных содержатся записи, в которых значения в связанных полях совпадают:

SELECT ProductName, CategoryName
FROM Products INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID

Так называемые внешние соединения (outer joins) позволяют нам включить в результат запроса все строки из одной таблицы и соответствующие им строки из другой таблицы. Например:

SELECT ProductName, CategoryName
 FROM Products LEFT OUTER JOIN Categories
ON Products.CategoryID = Categories.CategoryID

Это было так называемое левое внешнее соединение (left outer join). Существуют также правые внешние соединения (right outer join), возвращающие все строки из второй (то есть правой) таблицы и соответствующие им строки из другой таблицы:

SELECT ProductName, CategoryName
FROM Products RIGHT OUTER JOIN Categories
ON Products.CategoryID = Categories.CategoryID

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

SELECT ProductName, CategoryName
FROM Products FULL OUTER JOIN Categories
ON Products.CategoryID = Categories.CategoryID

Для получения всех комбинаций строк из обеих таблиц (декартова произведения) можно использовать ключевое слово CROSS JOIN без указания связываемых полей:

SELECT ProductName, CategoryName
FROM Products CROSS JOIN Categories

Если в запросе используется более трех таблиц, можно иcпользовать вложенные соединения.

Предложение GROUP BY

Для вычисления суммарных значений на основе данных одной или нескольких таблиц можно использовать предложение GROUP BY, имеющее такой синтаксис:

GROUP BY {column1} [, :]

Например, следующий запрос связывает две таблицы, сортирует их по полю CustomerID, для каждого значения CustomerID создает одну строку в результирующем наборе данных и вычисляет количество значений поля OrderID для каждого значения CustomerID:

SELECT Customers.CustomerID,
COUNT (Orders.OrderID)
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID

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

Таблица 10

Функция	Назначение
AVG	Вычисляет среднее
COUNT	Вычисляет количество непустых значений в данной колонке
MAX	Вычисляет наибольшее значение в колонке
MIN	Вычисляет наименьшее значение в колонке
SUM	Вычисляет сумму значений в колонке
STDEV	Вычисляет несмещенное стандартное отклонение для данной колонки. Эта функция используется в Microsoft Access и Microsoft SQL Server. В случае Oracle она называется STD
STDEVP	Вычисляет смещенное стандартное отклонение для данной колонки. Эта функция используется в Microsoft Access и Microsoft SQL Server
VAR	Вычисляет несмещенную дисперсию отклонения для данной колонки. Эта функция используется в Microsoft Access и Microsoft SQL Server. В случае Oracle она называется VARIANCE
VARP	Вычисляет смещенную дисперсию отклонения для данной колонки. Эта функция используется в Microsoft Access и Microsoft SQL Server

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

Таблица 11

Функция	Назначение
ABS	Возвращает абсолютное значение числа
CEIL	Округляет дробное число
FLOOR	Удаляет дробную часть числа
GREATEST	Возвращает наибольшее из двух значений. Эта функция используется в Microsoft Access и Microsoft SQL Server
LEAST	Возвращает наименьшее из двух значений. Эта функция используется в Microsoft Access и Microsoft SQL Server
MOD	Возвращает остаток от деления одного числа на другое
POWER	Возвращает значение, равное одному числу в степени, равной другому числу
ROUND	Округляет число с точностью до указанного десятичного знака
SIGN	Возвращает -1, если число отрицательное, и 1, если положительное
SQRT	Вычисляет квадратный корень числа
LEFT	Возвращает указанное число знаков строки, начиная слева. Эта функция используется в Microsoft Access и Microsoft SQL Server
RIGHT	Возвращает указанное число знаков строки, начиная справа. Эта функция используется в Microsoft Access и Microsoft SQL Server
UPPER	Заменяет все буквы в строке на прописные
LOWER	Заменяет все буквы в строке на строчные
INITCAP	Расставляет заглавные буквы в начале слов в строке
LENGTH	Вычисляет число символов в строке
LPAD	Добавляет указанный символ в левую часть строки в количестве, необходимом для того, чтобы строка имела заданную длину
RPAD	Добавляет указанный символ в правую часть строки в количестве, необходимом для того, чтобы строка имела заданную длину
SUBSTR	Извлекает подстроку нужной длины из строки, начиная с указанной позиции

Предложение HAVING

Предложение HAVING имеет назначение, сходное с предложением WHERE, но используется с агрегатными данными. Например:

SELECT Customers.CustomerID,
COUNT (Orders.OrderID)
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID
HAVING COUNT(Orders.OrderID) >= 10

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

Ключевые слова ALL и DISTINCT

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

SELECT DISTINCT Country FROM Customers

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

Ключевое слово TOP

Ключевое слово TOP может быть использовано для возврата первых n строк или первых n процентов таблицы. Например, запрос:

SELECT TOP 10 * FROM PRODUCTS
ORDER BY ProductName

возвращает первые 10 продуктов из таблицы, тогда как запрос:

SELECT TOP 25 PERCENT * FROM PRODUCTS
ORDER BY ProductName

вернет первую четверть записей таблицы.

Модификация данных

До сих пор мы изучали операторы SQL для извлечения данных. Помимо этого язык SQL может быть использован для обновления и удаления данных, копирования записей в другие таблицы и выполнения многих других операций. Ниже мы рассмотрим операторы UPDATE, DELETE и INSERT, используемые для решения некоторых из этих задач.

Оператор UPDATE

Для изменения значений в одной или нескольких колонках таблицы применяется оператор UPDATE. Синтакcис этого оператора имеет вид:

UPDATE tableSET column1 = expression1 [, column2 = expression2] [,:]
[WHERE criteria]

Выражение в предложении SET может быть константой или результатом вычислений. Например, для повышения цен всех продуктов, стоящих меньше 10 долл., можно выполнить следующий запрос:

UPDATE Products
SET UnitPrice = UnitPrice * 1.1
WHERE UnitPrice < 10

Оператор DELETE

Для удаления строк из таблиц следует использовать оператор DELETE, синтаксис которого имеет вид:

DELETE
FROM table
[WHERE criteria]

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

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

DELETE
FROM Products
WHERE Discontinued = 1

Отметим, что полезно использовать оператор SELECT с тем же синтаксисом, что и оператор DELETE, чтобы проверить, какие именно записи будут удалены, прежде чем действительно их удалять. Ниже показан оператор SELECT для приведенного выше запроса на удаление данных:

SELECT ProductName
FROM Products
WHERE Discontinued = 1

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

SELECT CompanyName
FROM Customers
WHERE Customers.CustomerID NOT IN
(SELECT CustomerID FROM Orders WHERE OrderDate > 01/01/96)

а затем заменить оператор SELECT на оператор DELETE:

DELETE FROM Customers
WHERE Customers.CustomerID NOT IN
(SELECT CustomerID FROM Orders WHERE OrderDate > 01/01/96)

Замечание. При использовании в операторах SQL даты или времени, а также полей, содержащих такие данные, следует уточнить синтаксис таких предложений в документации из комплекта поставки используемой СУБД.

Оператор INSERT

Для добавления записей в таблицы следует использовать оператор INSERT, синтаксис которого имеет вид:

INSERT [INTO] table
( [column_list]
{ VALUES ( { DEFAULT | NULL | expression }
 } [, :]
 )

Например, для добавления нового клиента в таблицу Customers можно использовать следующий запрос:

INSERT INTO Customers
(CustomerID, CompanyName)
VALUES
('XYZFO', 'XYZ Deli')

Модификация метаданных

Существует несколько операторов SQL для управления метаданными, используемых для создания, изменения или удаления баз данных и содержащихся в них объектов (таблиц, представлений и др.). Мы рассмотрим некоторые из них: CREATE TABLE, ALTER TABLE и DROP.

Оператор CREATE TABLE

Для создания новой таблицы необходимо использовать оператор CREATE TABLE, синтаксис которого имеет вид:

CREATE TABLE table
( column1 type1 [(size1)][CONSTRAINT _
column-constraint1]
[, column2 type2 [(size2)][CONSTRAINT _
column-constraint2]
[, ...]]
[CONSTRAINT table-constraint1 _
[,table-constraint2 [, ...]]]);

В этом операторе следует указать имя поля, тип данных для него (тип данных должен поддерживаться данной СУБД), длину (для некоторых типов полей) и, если нужно, серверные ограничения (с применением ключевого слова CONSTRAINT). Например, следующий запрос создает таблицу с именем Simple с четырьмя колонками - LastName, FirstName, EMail и HomePage:

CREATE TABLE Simple
(FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
EMail varchar(50),
HomePage varchar(255)
 )

Мы можем расширить эту таблицу добавлением поля PersonID, которое будет использовано как первичный ключ:

CREATE TABLE Simple
( PersonID Integer NOT NULL PRIMARY KEY,
 FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
 EMail varchar(50),
HomePage varchar(255)
 )

и указать, что комбинация полей LastName и FirstName должна быть уникальна:

CREATE TABLE Simple
( PersonID Integer NOT NULL PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
EMail varchar(50),
HomePage varchar(255),
CONSTRAINT SimpleConstraint UNIQUE
(FirstName, LastName)
 )

Используя предложение SELECT и ключевое слово INTO, мы можем создавать новые таблицы, основанные на условии, указанном в предложении WHERE. Например:

SELECT *
INTO NewOrders
FROM Orders
WHERE OrderDate > 1/1/97

Этот запрос создаст новую таблицу NewOrders и заполнит ее данными о заказах начиная с 1 января 1997 года.

Оператор ALTER TABLE

Для изменения структуры существующей таблицы можно использовать оператор ALTER TABLE. Применяя его, можно добавить или удалить поле или серверное ограничение. Существует четыре разновидности оператора ALTER TABLE.

Первая разновидность этого оператора используется для добавления колонки к таблице, и ее синтаксис имеет вид:

ALTER TABLE table ADD [COLUMN] column datatype 
[(size)]
[CONSTRAINT sinlge-column-constraint]

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

ALTER TABLE Simple ADD Phone varchar(30)

Вторая разновидность оператора ALTER TABLE применяется для добавления серверных ограничений к таблице, а ее синтаксис имеет вид:

ALTER TABLE table ADD CONSTRAINT constraint

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

Третья разновидность предложения ALTER TABLE применяется для удаления поля из таблицы:

ALTER TABLE table DROP [COLUMN] column

Ключевое слово COLUMN использовать не обязательно. Например:

ALTER TABLE Simple DROP Phone

Обратите внимание на то, что для удаления проиндексированных полей следует сначала удалить индекс. Это можно сделать с помощью четвертой разновидности предложения ALTER TABLE:

ALTER TABLE table DROP CONSTRAINT index

Ниже приведен пример такого запроса:

ALTER TABLE Simple DROP CONSTRAINT PrimaryKey

Оператор DROP

Для удаления таблиц или индексов можно использовать оператор DROP, имеющий две разновидности. Первая из них применяется для удаления таблицы из базы данных:

DROP TABLE table

Вторая разновидность используется для удаления индекса:

DROP INDEX index ON table

Другие операторы SQL

Как было отмечено ранее, существует около 40 операторов SQL. Мы рассмотрели большинство из них. Некоторые из не рассмотренных нами операторов перечиcлены ниже:

ператоры CREATE, такие как CREATE DATABASE, CREATE VIEW и CREATE TRIGGER (два последних из них мы рассмотрим в следующей статье данного цикла);
операторы ALTER, такие как ALTER DATABASE, ALTER VIEW и ALTER TRIGGER;
операторы DROP, такие как DROP DATABASE, DROP VIEW и DROP TRIGGER;
BEGIN TRANSACTION, COMMIT TRANSACTION и ROLLBACK TRANSACTION для выполнения группы нескольких операторов как единой логической группы;
DECLARE CURSOR, OPEN и FETCH для работы с курсорами;
GRAND и REVOKE для добавления или удаления прав на использование объектов базы данных, а также CREATE USER, ALTER USER, DROP USER, CREATE GROUP, ALTER GROUP и DROP GROUP для управления списком пользователей и групп пользователей.

Заключение

В данной статье мы рассмотрели все основные компоненты языка SQL. Мы узнали, что:

SQL - непроцедурный язык, предназначенный для управления данными в реляционных СУБД. Последний официальный стандарт был опубликован ANSI в 1992 году, и современная реализация SQL называется SQL92. Язык SQL поддерживается большинством производителей СУБД;
оператор SELECT следует использовать для извлечения данных из таблиц. Предложение WHERE можно применять для того, чтобы ограничить результирующий набор данных записями, удовлетворяющими заданному условию;
предложение GROUP BY может быть использовано для создания результирующего набора данных, содержащего суммарные данные из одной или нескольких таблиц;
для получения данных из нескольких таблиц можно использовать ключевое слово JOIN;
для изменения данных применяется операторы INSERT, UPDATE и DELETE;
операторы CREATE, ALTER и DROP могут быть использованы для создания, модификации и удаления баз данных и содержащихся в них объектов - таблиц, представлений и др.

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

КомпьютерПресс 10'2000