Вход



Поиск по сайту
Google на mf.grsu.by

  
Главная страница >> Библиотека >> Букинист >> Ведение в базы данных >> 6. Введение в язык SQL

Введение в базы данных

Часть 6. Введение в язык SQL

Алексей Федоров, Наталия Елманова

Введение

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

    Операторы SQL

        Data Definition Language (DDL)

        Data Manipulation Language (DML)

        Transaction Control Language (TCL)

        Data Control Language (DCL)

        Cursor Control Language (CCL)

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

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

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

    Выбор данных

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

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

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

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

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

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

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

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

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

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

        Оператор UPDATE

        Оператор DELETE

        Оператор INSERT

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

        Оператор CREATE TABLE

        Оператор ALTER TABLE

        Оператор DROP

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

Заключение

 

В предыдущих двух статьях данного цикла, опубликованных в © 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лены ниже:

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

в начало

Заключение

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

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

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

 



Предыдущая страница Следущая страница

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

  
За содержание страницы отвечает Гончарова М.Н.
©
Кафедра СПиКБ, 2002-2017