БАЗЫ ДАННЫХ

факультет
Кафедра вычислительной техники и информационных технологий

данных, расширение круга пользователей информационных систем привели к широкому распространению

наиболее удобных реляционных (табличных) СУБД. Для обеспечения одновременного доступа к данным множества пользователей, нередко расположенных достаточно далеко друг от друга и от места хранения баз данных, созданы сетевые мультипользовательские версии СУБД. В них тем или иным путем решаются специфические проблемы параллельных процессов, целостности (правильности) и безопасности данных, а также санкционирования доступа. Совместная работа пользователей в сетях с помощью унифицированных средств общения с БД возможна только при наличии стандартного языка манипулирования данными, обладающего средствами для реализации перечисленных выше возможностей
Разрабатываемые в начале 80-х языки запросов можно отнести к двум классам:
Алгебраические языки, позволяющие выражать запросы средствами специализированных операторов, применяемых к отношениям (JOIN - соединить, INTERSECT - пересечь, SUBTRACT - вычесть и т.д.).
Языки исчисления предикатов представляют собой набор правил для записи выражения, определяющего новое отношение из заданной совокупности существующих отношений

фирмы IBM (языки ISBL, SQL, QBE) и университетах США (PIQUE,

QUEL). QUEL создавался для СУБД INGRES (Interactive Graphics and Retrieval System), которая была разработана в начале 70-х годов в Университете шт. Калифорния и сегодня входит в пятерку лучших профессиональных СУБД.
Сегодня из всех этих языков полностью сохранились и развиваются QBE (Query-By-Example - запрос по образцу) и SQL, а из остальных взяты в расширение внутренних языков СУБД только наиболее интересные конструкции.
SQL был разработан в 1974 году фирмой IBM для экспериментальной реляционной СУБД System R. После появления на рынке двух пионерских СУБД этой фирмы - SQL/DS (1981 год) и DB2 (1983 год) - он приобрел статус стандарта де-факто для профессиональных реляционных

в 1989 г. (SQL/89 или SQL1). Стандарт был принят ANSI

(Американский Национальный Институт Стандартов) и ISO (международная организация по стандартизации).
Следующая версия стандарта языка SQL принята в 1992 г. (Официальное название стандарта - Международный стандарт языка баз данных SQL (1992) (International Standart Database Language SQL), неофициальное название - SQL/92, или SQL-92, или SQL2). Документ, описывающий стандарт, содержит более 600 страниц. Рассмотрим только некоторые основные понятия языка.
Далее был разработан SQL/99 или SQL3, принятый стандарт объектно-реляционного расширения SQL. Подмножества этого стандарта уже реализованы в коммерческих продуктах, включая Oracle V8 компании Oracle и DB2 корпорации IBM.

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

реализуют тот или иной диалект SQL: SQL*Plus корпорации Oracle; Transact-SQL для СУБД Microsoft SQL Server и др.
В диалектах язык может быть дополнен операторами процедурных языков программирования. Многие системы, не являющиеся реляционными, также имеют в настоящее время средства доступа к реляционным данным. Целью стандартизации является переносимость приложений между различными СУБД.
Нужно заметить, что в настоящее время, ни одна система не реализует стандарт SQL в полном объеме. Кроме того, во всех диалектах языка имеются возможности, не являющиеся стандартными. Таким образом, можно сказать, что каждый диалект - это надмножество некоторого подмножества стандарта SQL. Это затрудняет переносимость приложений, разработанных для одних СУБД, в другие СУБД.
Имеются две формы языка SQL: интерактивный и вложенный.
В интерактивной форме SQL любая введенная команда выполнится сразу же и можно увидеть результаты работы команды.
Вложенный SQL состоит из команд SQL, встроенных внутрь программ, написанных на некотором другом языке (типа Visual Basic или С). В этой главе будет представлен SQL в интерактивной форме. Интерактивный SQL - это форма, наиболее полезная непрограммистам. Все что, рассмотрим относительно интерактивного SQL, в основном, применимо и к вложенной форме.

реляционные СУБД трех групп:
Мощные крупные коммерческие СУБД, ориентированные на хранение

огромных объемов информации (от гигабайт). Наиболее известными СУБД в этой группе являются: Oracle (Oracle Corp.), Ingres (Computer Associates International), Sybase SQL server (Sybase Inc.).
Мобильные компактные свободно распространяемые СУБД, использование которых оправдано и для БД объемом всего лишь в десятки килобайт. К наиболее популярным СУБД этой группы относятся: PostgreSQL (организации PostgreSQL), mySQL (T.C.X. DataKonsult AB), Microsoft SQL Server (Microsoft).
Настольные персональные СУБД, ориентированные на простые варианты построения БД, решение менее сложных задач, на персональные компьютеры и, на меньшие объемы и сравнительно простую структуру данных. К настольным СУБД относятся: Access, входящая в состав пакета Microsoft Office и рассчитанная на одного пользователя; Visual FoxPro.
СУБД первых двух групп построены по принципу «клиент-сервер».

разбитые на несколько групп по выполняемым функциям. Можно выделить следующие

группы операторов (перечислены не все операторы SQL):
DDL (Data Definition Language) - операторы определения объектов базы данных:
CREATE SCHEMA - создать схему базы данных;
DROP SHEMA - удалить схему базы данных;
CREATE DATABASE - создать базы данных;
DROP DATABASE - удалить базу данных;
CREATE TABLE - создать таблицу;
ALTER TABLE - изменить таблицу;
DROP TABLE - удалить таблицу;
CREATE DOMAIN - создать домен;
ALTER DOMAIN - изменить домен;
DROP DOMAIN - удалить домен;
CREATE INDEX - создать индекс;
DROP INDEX - удалить индекс;
CREATE VIEW - создать представление;
DROP VIEW - удалить представление.

манипулирования данными:
SELECT - отобрать строки из таблиц;
INSERT - добавить

строки в таблицу;
UPDATE - изменить строки в таблице;
DELETE - удалить строки в таблице;

DCL (Data Control Language) - операторы контроля данных, защиты и управления данными:
CREATE ASSERTION - создать ограничение;
DROP ASSERTION - удалить ограничение;
COMMIT - зафиксировать внесенные изменения;
ROLLBACK - откатить внесенные изменения.
GRANT - предоставить привилегии пользователю или приложению на манипулирование объектами;
REVOKE - отменить привилегии пользователя или приложения.

данных :
CHARACTER(n) или CHAR(n) символьные строки с постоянной

длиной n
NUMERIC[(n,m)], DECIMA[(n,m)] или DEC[(n,m)] точные числа, здесь n – общее число цифр в числе; m – количество цифр слева от десятичной точки
INTEGER или INT - целые числа
SMALLINT - целые числа меньшего диапазона
REAL - вещественные числа в форме с плавающей точкой
FLOAT[(n)] - вещественные числа большой точности в форме с плавающей точкой, здесь n – общее число байт, отводимое на хранение чисел
DOUBLE PRECISION - вещественные числа большой точности в форме с плавающей точкой
В стандарте SQL/92 добавлены типы данных:
VARCHAR(n) – строки символов переменной длины;
BIT(n) – строки битов постоянной длины;
DATE – календарная дата;
INTERVAL – временной интервал и др.

SQL поддерживаются некоторые дополнительные типы данных, например, TIME, INTERVAL, MONEY.

Некоторые из этих типов специфицированы в стандарте SQL/92, но в текущих реализациях синтаксические и семантические свойства таких типов могут различаться.
Конкретными реализациями SQL поддерживаются собственные наборы типов данных и встроенных функций, позволяющих выполнять обработку данных числового и строкового типов. Определены арифметические операции: + | - | * | /
В качестве удобного примера при дальнейшем рассмотрении реализации операторов SQL будем использовать учебную БД условной торговой фирмы «ЗАКАЗЫ» (пример взят у М. Грабера [3]).
БД состоит из трех таблиц:
Таблица Продавцы :
номер продавца – SNUM;
имя продавца – SNAME;
город, в котором он живет, - CITY;
комиссионные, которые он получает с каждого оформленного заказа, - COMM.

CNUM;
имя заказчика – CNAME;
город, в котором он живет,

- CITY;
номер продавца, который обслуживает данного заказчика, - SNUM;
оценка заказчика – rating.
Таблица Заказы:
номер заказа – ONUM;
сумма заказа – AMT;
дата оформления заказа – ODATE;
номер заказчика, который оплатил данный заказ, - CNUM;
номер продавца, который обслуживает данный заказ, - SNUM.
Приняты следующие соглашения (бизнес-правила):
один продавец может обслуживать несколько заказчиков;
один заказчик работает только с одним продавцом данной фирмы.

SNAME CITY COMM
1001

Пил Лондон 0.12
1002 Серенс Мехико 0.13
1004 Мотье Лондон 0.11
1007 Рифкин Барселона 0.15
1003 Аксельрод Париж 0.10

Таблица 2 – Заказчики (t2)
CNUM CNAME CITY RATING SNUM
2001 Хофман Лондон 100 1001
2002 Джованни Рим 200 1003
2003 Луи Мехико 200 1002
2004 Грасс Берлин 300 1002
2006 Клеменс Лондон 100 1001
2008 Киснерос Мехико 300 1007
2007 Перера Рим 100 1004

является фактически самым важным для пользователя и самым сложным оператором

SQL. Он предназначен для выборки данных из таблиц, т.е. он, собственно, и реализует одно их основных назначений БД - предоставлять информацию пользователю.
Результатом выполнения оператора SELECT всегда является таблица. Таким образом, по результатам действий оператор SELECT похож на операции реляционной алгебры. Любая операция реляционной алгебры может быть выражена подходящим образом сформулированным оператором SELECT. Сложность оператора SELECT определяется тем, что он содержит в себе все возможности реляционной алгебры, а также дополнительные возможности, которых в реляционной алгебре нет.
Замечание. Каждый оператор интерактивного SQL представляет собой одну команду, которая заканчивается «;». В наших примерах служебные слова SQL будем приводить прописными буквами.
Рассмотрим сначала простые конструкции оператора SELECT, постепенно усложняя его запись:
SELECT <список выбора> FROM <имя таблицы>;
Пример 1 Выбрать все данные из таблицы Продавцы:
SELECT snum, sname, city, comm FROM t1;
Результатом работы этой команды будет вывод всей таблицы 1 .

Выбрать все данные из таблицы:
SELECT *

FROM t1;
Здесь «звездочка» (*) служит кратким обозначением всех имен полей в таблице, указанной во фразе FROM. При этом порядок вывода полей соответствует порядку, в котором эти поля определялись при создании.
Пример 3 Выбрать некоторые колонки из исходной таблицы (указание списка отбираемых колонок):
SELECT sname, comm FROM t1;
Эта команда выведет только два указанных столбца из таблицы.
Пример 4 Выбрать некоторые колонки из исходной таблицы (указание списка отбираемых колонок):
SELECT odate, snum, onum, amt FROM t3;
Столбцы по этой команде будут выведены в заданном порядке.
Пример 5 Вывести список номеров продавцов из таблицы Заказы:
SELECT snum FROM t3 ;
Результатом выполнения этой команды является список номеров из таблицы 3 в котором есть повторяющиеся записи.

дубликатов необходимо дополнить запрос ключевым словом DISTINCT (различный, различные), как

показано в следующем примере.
Пример 6 Вывести список номеров продавцов из таблицы Заказы без повторений: SELECT DISTINCT snum FROM t3;
В предыдущих примерах в операторах SELECT была реализована операция проекция. Для реализации операции выборка необходимо в конструкцию оператора добавить предложение WHERE:
SELECT <список выбора> FROM <имя таблицы> WHERE <предикат>;
В синтаксисе предложения WHERE для отбора нужных строк таблицы можно использовать:
операторы сравнения:
= (равно), <> (не равно), < (меньше), <= (меньше или равно), > (больше), > = (больше или равно);
логические операции: OR, NOT, AND;
операторы: IN, BETWEEN …. AND, LIKE, IS NULL.
Пример 7 Вывести имена и комиссионные всех продавцов в Лондоне:
SELECT sname, city FROM t1 WHERE city = ‘Лондон’;

Вывести все данные о заказчиках в Мехико, которые

имеют оценку (рейтинг) выше 200:
SELECT * FROM t2 WHERE city = ‘Мехико' AND rating > 200;
Будет выведена одна строка: CNUM CNAME CITY RATING SNUM
2008 Киснерос Мехико 300 1007
Сложные логические выражения в предикате формируются с помощью скобок.
Пример 9 SELECT * FROM t3
WHERE NOT ((odate = 10/03/2003 AND snum >1002) OR amt > 2000.00);
Сложные логические выражения можно преобразовать, используя аксиомы, называемые законами де Моргана:
NOT (a AND b) ≡ NOT a OR NOT b
NOT (a OR b ≡ NOT a AND NOT b , где a и b – логические выражения, а символ «≡» означает «эквивалентно».
Оператор IN определяет, включено ли значение левого операнда в набор значений (список) правого операнда. Значение предиката, образованного с помощью оператора IN, равно true в том и только в том случае, когда значение левого операнда совпадает хотя бы с одним значением списка правого операнда.

Найти всех продавцов, которые живут в Барселоне или в

Лондоне: SELECT * FROM t1 WHERE city = 'Барселона' OR city = 'Лондон';
Имеется и более простой способ получить ту же информацию:
SELECT * FROM t1 WHERE city IN ( 'Барселона', 'Лондон' );
С помощью оператора BETWEEN ... AND ... (находится в интервале от ... до ...) можно отобрать строки, в которых значение какого-либо столбца находятся в заданном диапазоне. Результат «x BETWEEN y AND z» тот же самый, что результат «x >= y
AND x <= z». Результат «x NOT BETWEEN y AND z» тот же самый, что результат «NOT (x BETWEEN y AND z)».
Пример 11 Этот запрос выбирает всех заказчиков, чьи имена попали в определенный алфавитный диапазон:
SELECT * FROM t2 WHERE cname BETWEEN 'Г' AND 'Л';
Вывод для этого запроса: CNUM CNAME CITY RATING SNUM
2004 Грасс Берлин 300 1002
2006 Клеменс Лондон 100 1001
2008 Киснерос Мехико 300 1007
Обратите внимание, что Луи отсутствует. Это происходит из-за того, что BETWEEN сравнивает строки неравной длины. Строка 'Л' более короткая, чем строка ‘Луи’, поэтому и не выбирается. Важно помнить это когда используете оператор BETWEEN для выбора значений из алфавитных диапазонов.

«имя_столбца» LIKE «текстовая_константа» для столбца текстового типа позволяет отыскать

все значения указанного столбца, соответствующие образцу, заданному «текстовой_константой». Символы этой константы интерпретируются следующим образом:
символ _ (подчеркивание) – заменяет любой одиночный символ (для СУБД ACCESS –«?»);
символ % (процент) – заменяет любую последовательность из N символов (где N может быть нулем) (для СУБД ACCESS –«*»);
все другие символы означают просто сами себя.
Для оператора LIKE типы данных столбца левого операнда и образца должны быть типами символьных строк.
Пример 12 Найти всех заказчиков, чьи имена начинаются с буквы ‘К’:
SELECT * FROM t2 WHERE cname LIKE 'К%';
Наличие неопределенных (NULL) значений повышает гибкость обработки информации, хранящейся в БД. В нашем примере можно предположить ситуацию, что появился новый заказчик, которому еще не был назначен продавец. Вы можете ввести строку для заказчика со значением NULL в поле snum и заполнить это поле значением позже, когда продавец будет назначен. Значение «x IS NULL» равно true тогда и только тогда, когда значение x не определено. Значение предиката «x NOT IS NULL» равно значению «NOT x IS NULL».

Найти все записи в таблице Заказчики с NULL значениями

в snum столбце: SELECT * FROM t2 WHERE snum IS NULL;
Агрегирование данных в запросах
В SQL существует ряд специальных стандартных функций (SQL-функций). Кроме специального случая COUNT(*) каждая из этих функций оперирует совокупностью значений столбца некоторой таблицы и создает единственное значение, определяемое:
COUNT - число значений в столбце;
SUM - сумма значений в столбце;
AVG - среднее значение в столбце;
MAX - самое большое значение в столбце;
MIN - самое малое значение в столбце.
Для функций SUM и AVG рассматриваемый столбец должен содержать числовые значения.
Замечание. Следует отметить, что здесь «столбец» - это столбец виртуальной таблицы (например, представления), в которой могут содержаться данные не только из столбца базовой таблицы, но и данные, полученные путем функционального преобразования и (или) связывания символами арифметических операций значений из одного или нескольких столбцов. При этом вложенность SQL-функций не допускается. Однако из SQL-функций можно составлять любые выражения.
Аргументу всех функций, кроме COUNT(*), может предшествовать ключевое слово DISTINCT (различный), указывающее, что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться функция. Специальная же функция COUNT(*) служит для подсчета всех без исключения строк в таблице (включая дубликаты).

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

SELECT SUM (amt) FROM t3;
В результате будет выведено значение 26658.4 без подписи поля.
Пример 15 Подсчитать число строк в таблице Заказчики:
SELECT COUNT (*) FROM t2;
В результате будет выведено значение 7.
Фраза GROUP BY (группировать по …) инициирует перекомпоновку указанной в предложении FROM таблицы по группам, каждая из которых имеет одинаковые значения в столбце, заданном в GROUP BY, затем к каждой группе применяется заданная функция и оператор SELECT выводит значения для каждой группы.
Пример 16 Найти наибольшую сумму приобретений, полученную каждым продавцом: SELECT snum, MAX (amt) FROM t3 GROUP BY snum;
Вывод для этого запроса:
snum ------ --------
1001 767.19
1002 1713.23
1003 75.75
1014 1309.95
1007 1098.16

список отбираемых полей оператора SELECT, содержащего раздел GROUP BY, можно

включать только агрегатные функции и поля, которые входят в условие группировки. Поэтому команда
SELECT onum, snum, MAX (amt) FROM t3 GROUP BY snum;
не будет выполнена и появится сообщение о синтаксической ошибке. Причина ошибки в том, что в список отбираемых полей включено поле onum, которое не входит в раздел GROUP BY. Можно также использовать GROUP BY с несколькими полями, задавая уровни группировки.
Пример 17 Вывести наибольшую сумму приобретений, получаемую каждым продавцом каждый день:
SELECT snum, odate, MAX (amt) FROM t3 GROUP BY snum, odate;
Вывод для этого запроса:
snum odate ------
1001 10/03/2003 767.19
1001 10/05/2003 4723.00
1001 10/06/2003 9891.88
1002 10/03/2003 5160.45
1002 10/04/2003 75.75
1002 10/06/2003 1309.95
1003 10/04/2003 1713.23
1004 10/03/2003 1900.10
1007 10/03/2003 1098.16

играет такую же роль для групп, что и фраза WHERE

для строк:
она используется для исключения групп, точно так же, как WHERE используется для исключения строк. Эта фраза включается в предложение лишь при наличии предложения GROUP BY, а выражение в предложении HAVING должно принимать единственное значение для группы, т.е. использоваться агрегатная функция.
Пример 18 Предположим, что в предыдущем примере, необходимо увидеть только те максимальные суммы приобретений, значение которых выше $3000.00:
SELECT snum, odate, MAX (amt) FROM t3 GROUP BY snum, odate HAVING MAX (amt) > 3000.00;
Замечание. В одном запросе могут встретиться как условия отбора строк в разделе WHERE, так и условия отбора групп в разделе HAVING. Условия отбора групп нельзя перенести из раздела HAVING в раздел WHERE. Аналогично и условия отбора строк нельзя перенести из раздела WHERE в раздел HAVING, за исключением условий, включающих поля из списка группировки GROUP BY. Добавим в запрос предыдущего примера раздел WHERE:
SELECT snum, MAX (amt) FROM t3 WHERE snum IN (1002,1007) GROUP BY snum HAVING MAX (amt) > 3000.00;

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

WHERE;
выявляются группы, заданные предложением GROUP BY;
внутри каждой группы вычисляется значение функции (MAX (amt) );
исключаются из вывода группы, не удовлетворяющие условию, указанному в предложении HAVING.

В список вывода оператора SELECT можно добавить любые строковые константы и вычисляемые выражения.
Пример 19 Представить комиссионные продавцов в процентном отношении (а не в виде десятичных чисел):
SELECT snum, sname, city, comm * 100, ' % ' FROM t1;
Таблицы - это неупорядоченные наборы данных, и выводимые данные по запросу не обязательно появляются в какой-то определенной последовательности. SQL использует предложение ORDER BY, чтобы упорядочивать вывод. Многочисленные столбцы упорядочиваются один внутри другого, также как с GROUP BY, и можно определять сортировку по возрастанию (ASC) или убыванию (DESC ) для каждого столбца. По умолчанию установлена сортировка по возрастанию.
Пример 20 Вывести таблицу Заказы, отсортированную по номеру заказчика в порядке убывания:
SELECT * FROM t3 ORDER BY cnum DESC;

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

Структура запроса в этом случае имеет вид:
SELECT <список выбора> FROM <имя таблицы> [WHERE <предикат>] [GROUP BY <выражение>] [HAVING <предикат>] [ORDER BY <поля>] ;
Пример 21
SELECT snum, odate, MAX (amt) FROM t3 GROUP BY snum, odate ORDER BY snum ;
Формирование запросов на основе соединения таблиц
С помощью соединения можно сформировать запрос на обработку данных из нескольких таблиц. Следовательно, соединения позволяют обрабатывать множество взаимосвязанных таблиц как единую таблицу, в которой собрана информация о нескольких сущностей. Различные виды реляционной операции соединения были рассмотрены ранее. В этом пункте остановимся на следующих видах соединений:
эквисоединение (по равенству полей)
соединение через справочную целостность;
соединение таблицы с собой (рекурсия);
внешнее соединение.

Полное имя столбца таблицы фактически состоит из имени таблицы, сопровождаемого

точкой и затем имени столбца, например: t1.snum , t1.city, t3.odate. Ранее мы могли опускать имена таблиц при указании столбцов, потому что запрашивали только одну таблицу одновременно, а SQL достаточно интеллектуален, чтобы присвоить соответствующий префикс имени таблицы. Даже, если запрос включает несколько таблиц, еще можно опускать имена таблиц, если все столбцы имеют различные имена. Например, если в одном запросе использованы два столбца city, то нужно указать их с именами t1.city или t2.city, чтобы SQL мог их различать.
Пример 22 Предположим, что необходимо поставить в соответствии каждому продавцу его заказчиков в том городе, в котором они живут:
SELECT t2.cname, t1.sname, t1.city FROM t1, t2
WHERE t1.city = t2.city;
Результат запроса:
cname cname city
Хофман Пил Лондон
Клеменс Пил Лондон
Луи Серенс Мехико
Киснерос Серенс Мехико
Хофман Мотика Лондон
Клеменс Мотика Лондон

произведение, получаем 35 строк (5х7);
производится выборка из полученной таблицы только

тех строк, для которых выполняется условие t1.city = t2.city;
из полученной на шаге 2 таблицы выбираются только 3 указанных столбца (проекция).
В этом запросе использовалось эквисоединение.
Пример 23 Показать для каждого продавца имена всех заказчиков, которых он обслуживает:
SELECT t2.cname, t2.sname FROM t1, t2
WHERE t1.snum= t2.snum;
В этом примере использовано соединение таблиц через справочную целостность, поскольку поле snum в таблице Продавцы является первичным ключом, а в таблице Заказчики – внешним ключом.
Можно создавать запросы, использующие соединение более чем двух таблиц.
Пример 24 Предположим, что нужно найти все заказы заказчиков, не находящихся в тех городах, где живут их продавцы. Для этого необходимо связать все три таблицы: SELECT onum, cname, t3.cnum, t3.snum FROM t1, t2, t3 WHERE t3.city < > t1.city AND t3.cnum= t2.cnum AND t3.snum = t1.snum;

cnum snum

3001 Киснерос 2008 1007
3002 Перера 2007 1004
3006 Джованни 2002 1003
3007 Грасс 2004 1002
3010 Грасс 2004 1002
В запросе таблица может соединяться сама с собой. В этом случае для экземпляров таблицы задаются псевдонимы, которые определяются в предложении FROM после имени таблицы.
Пример 25 Найти все пары заказчиков с одинаковым рейтингом:
SELECT a.cname, b.cname, a.rating FROM t2 a, t2 b WHERE a.rating = b.rating;
Результат запроса:
cname cname rating
Хофман Хофман 100
Хофман Клеменс 100
Хофман Перера 100
Джованни Джованни 200
Джованни Луи 200
Луи Джованни 200 и т.д.

комбинация заказчиков выведена дважды; выводится комбинация строки сама с собой.

Для устранения избыточности необходимо добавить еще одно условие в предикат, чтобы сделать предикат ассиметричным, и те же самые значения в обратном порядке не будут выбираться снова, например:
Пример 26 SELECT a.cname, b.cname, a.rating FROM t2 a, t2 b
WHERE a.rating = b.rating AND a.cname < b.cname;
Вывод в этом случае будет содержать только 5 строк.
В Access для соединения таблиц используется фразы JOIN. К их числу относится операция внутреннего соединения (INNER JOIN) и операции внешнего соединения (LEFT JOIN и RIGHT JOIN). Эти операции могут использоваться в любом предложении FROM. Операция INNER JOIN объединяет записи из двух таблиц, если связывающие поля содержат одинаковые значения. LEFT JOIN выбирает все записи из левой таблицы, даже если в правой таблице нет совпадающих значений. Это относится и к операции RIGHT JOIN, только все записи выбираются из правой таблицы Синтаксис предложения FROM:
FROM таблица_1 [INNER | LEFT| RIGHT | JOIN таблица_2 ON таблица_1.поле_1 оператор таблица_2.поле_2],
где таблица_1, таблица_2 – имена таблиц, которые подлежат соединению;
поле_1, поле_2 – имена полей, используемые для связи;
оператор – чаще всего «=».

SQL обладает большой избыточностью в том смысле, что он часто

предоставляет несколько различных способов формулировки одного и того же запроса.
Очень удобным средством, позволяющим формулировать запросы более понятным образом, является возможность использования подзапросов, вложенных в основной запрос.
Подзапрос - это запрос, который может входить в предикаты условия выборки предложений WHERE и HAVING оператора SELECT или других операторов SQL, использующих WHERE предложение.
Вложенный подзапрос может содержать в своей WHERE (HAVING) фразе другой вложенный подзапрос и т.д. Нетрудно догадаться, что вложенный подзапрос создан для того, чтобы при отборе строк таблицы, сформированной основным запросом, можно было использовать данные из других таблиц. В SQL/89 к подзапросам применяется то ограничение, что результирующая таблица должна содержать в точности один столбец. Заметим еще, что поскольку подзапрос всегда вложен в некоторый другой оператор SQL, то в качестве констант в арифметическом выражении выборки и логических выражениях разделов WHERE и HAVING можно использовать значения столбцов текущих строк таблиц, участвующих в запросах (подзапросах) более внешнего уровня.
Существуют простые и коррелированные (соотнесенные) вложенные подзапросы. Они включаются в WHERE (HAVING) фразу с помощью операторов IN, EXISTS, ALL, ANY или одного из условий сравнения ( = | <> | < | <= | > | > = ).

подзапросы обрабатываются системой «снизу вверх». Первым обрабатывается вложенный подзапрос самого

нижнего уровня. Множество значений, полученное в результате его выполнения, используется при реализации подзапроса более высокого уровня и т.д.
Пример 27 Предположим, что известно имя продавца (Мотика), но неизвестно значение его поля snum, и необходимо извлечь все его заказы из таблицы Заказы :
SELECT * FROM t3 WHERE snum =
( SELECT snum FROM t1 WHERE sname = 'Мотика');
Замечание. Результат выполнения запроса будет эквивалентен результату следующей последовательности действий:
выполнить один раз вложенный подзапрос и получить значение номера продавца (единственной найденной строкой естественно будет snum = 1004);
просканировать таблицу Порядки, каждый раз сравнивая значение номера продавца с результатом подзапроса (WHERE snum = 1004), и отобрать только те строки, в которых предикат принимает значение true.
Замечание. При использовании подзапросов необходимо убедиться, что подзапрос будет выдавать одну и только одну строку вывода. Если подзапрос не выводит никаких значений, то команда не потерпит неудачи, но основной запрос не выведет никаких значений. В этом случае результат подзапроса следует рассматривать как неопределенный (неизвестный). Можно в некоторых случаях использовать DISTINCT, чтобы обеспечить генерацию подзапросом одиночного значения.

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

заказчика с номером 2001:
SELECT * FROM t3 WHERE snum =
( SELECT DISTINCT snum FROM t2 WHERE cnum = 2001 );
Замечание. Обратите внимание, что предикаты, включающие подзапросы, используют структуру < выражение > < оператор > < подзапрос >, а не < подзапрос > < оператор > < выражение > . Любой подзапрос, использующий агрегатную функцию без предложения GROUP BY, будет возвращать одиночное значение для использования в основном предикате.
Пример 29 Вывести все заказы, имеющие сумму приобретений выше средней на 4-е октября:
SELECT * FROM t3 WHERE amt >
( SELECT AVG (amt) FROM t3 WHERE odate = 10/04/2003 );
Средняя сумма приобретений на 4 октября – 894,38. Все строки со значением в поле amt выше 894,38 будут выбраны.
Замечание. Агрегатные функции, примененные к группе (при использовании предложения GROUP BY), могут возвращать несколько значений, следовательно, не допускаются в подзапросах такого характера.
Можно использовать оператор IN с подзапросами, которые возвращают любое число строк.

Вывести все атрибуты таблицы Заказы для продавцов из Лондона:

SELECT * FROM t3 WHERE snum IN
( SELECT snum FROM t1 WHERE city =’Лондон’);
Можно также использовать подзапросы внутри предложения HAVING.
Пример 31 Подсчитать число заказчиков с оценками выше, чем средняя оценка в Мехико:
SELECT rating, COUNT ( DISTINCT cnum ) FROM t2 GROUP BY rating HAVING rating > ( SELECT AVG (rating) FROM t2 WHERE city =’Мехико’);
Соотнесенные (коррелированные) подзапросы
Запросы с коррелированными вложенными подзапросами обрабатываются системой в обратном порядке. Сначала выбирается первая строка рабочей таблицы, сформированной основным запросом, и из нее выбираются значения тех столбцов, которые используются во вложенном подзапросе (вложенных подзапросах). Если эти значения удовлетворяют условиям вложенного подзапроса, то выбранная строка включается в результат. Затем выбирается вторая строка и т.д., пока в результат не будут включены все строки, удовлетворяющие вложенному подзапросу (последовательности вложенных подзапросов).
Пример 32 Найти всех заказчиков в порядках на 3-е октября:
SELECT * FROM t2 outer WHERE 10/03/2003 IN
( SELECT odate FROM t3 inner WHERE outer.cnum = inner.cnum );

работу соотнесенного подзапроса:
Выбирается первая строка из внешнего запроса (строка-

кандидат).
Сохраняются значения из строки кандидата под псевдонимом.
Выполняется вложенный запрос. Там, где есть ссылка на внешний запрос, используется поле из строки кандидата.
Оценивается предикат внешнего запроса на основе результатов подзапроса, выполненного на шаге 3. Если предикат принимает значение «истина», то выводится строка кандидат.
Выполняются шаги 1-4 для следующей строки внешнего запроса и т. д.
Пример 33 Вывести имена и номера всех продавцов, у которых более одного заказчика: SELECT snum, sname FROM t1 main WHERE 1 <
(SELECT COUNT (*) FROM t2 WHERE snum = main.snum );
Предложение HAVING также может включать и коррелированные подзапросы.
Пример 34 Предположим, что необходимо вывести суммарные значения сумм приобретений из таблицы Заказы, сгруппированные по датам, удалив из вывода все строки, где бы сумма не была по крайней мере на 2000.00 выше максимальной (MAX) суммы:
SELECT odate, SUM (amt) FROM t3 a GROUP BY odate HAVING SUM (amt) > ( SELECT 2000.00 + MAX (amt) FROM t3 b WHERE a.odate = b.odate );

кванторов
Кванторы EXISTS (существования), ALL (всеобщности) - понятия, заимствованные из формальной

логики. В языке SQL предикат с квантором существования представляется выражением EXISTS (SELECT * FROM ...). Такое выражение считается истинным только тогда, когда результат вычисления «SELECT * FROM ...» является непустым множеством, т.е. когда существует какая-либо запись в таблице, указанной во фразе FROM подзапроса, которая удовлетворяет условию WHERE подзапроса.
(Практически этот подзапрос всегда будет коррелированным множеством.). Фактически любой запрос, который выражается через IN, может быть альтернативным образом сформулирован также с помощью EXISTS. Предикат с квантором всеобщности представляется выражением ALL (SELECT * FROM ...). Такое выражение считается истинным только тогда, когда предикат сравнения внешнего запроса будет истинным при сравнении со всеми строками подзапроса.
Пример 35 Вывести данные из таблицы Заказчики если, и только если, один или более заказчиков в этой таблице находятся в Мехико:
SELECT cnum, cname, city FROM t2 WHERE EXISTS
( SELECT * FROM t2 WHERE city =’Мехико’);
Данный подзапрос является простым и будет выполнен один раз, а затем будут выведены три столбца таблицы. В соотнесенном подзапросе предложение EXISTS оценивается отдельно для каждой строки таблицы, имя которой указано во внешнем запросе, точно также как и другие операторы предиката, когда вы используете соотнесенный подзапрос.

Вывести номера продавцов, которые имеют нескольких заказчиков: SELECT DISTINCT snum

FROM t2 outer WHERE EXISTS ( SELECT * FROM t2 inner WHERE inner.snum = outer.snum AND inner.cnum < > outer.cnum );
Пример 37 Один из способов, которым можно найти всех продавцов только с одним заказчиком, будет состоять в том, чтобы инвертировать наш предыдущий пример:
SELECT DISTINCT snum FROM t2 outer WHERE NOT EXISTS ( SELECT * FROM t2 inner WHERE inner.snum = outer.snum AND inner.cnum < > outer.cnum );
Кроме предиката EXISTS в подзапросах могут использоваться предикаты ANY (SOME), ALL.
Пример 38 Имеется новый способ нахождения продавцов, у которых заказчики размещены в тех же городах:
SELECT * FROM t1 WHERE city = ANY
(SELECT city FROM t2 );
Оператор ANY берет все значения, выведенные подзапросом (для этого случая - это все значения city в таблице Заказчики), и оценивает их как верные, если любое (ANY) из их равняется значению города текущей строки внешнего запроса. Можно также использовать оператор IN, чтобы создать запрос аналогичный предыдущему:
SELECT * FROM t1 WHERE city IN (SELECT city FROM t2 );
С помощью ALL, предикат принимает значение «истина», если каждое значение выбранное подзапросом удовлетворяет условию в предикате внешнего запроса.

только тех заказчиков, чьи оценки, выше чем у каждого заказчика

в Риме:
SELECT * FROM t2 WHERE rating > ALL
(SELECT rating FROM t2 WHERE city = Rome );
Приведем основные правила записи подзапросов:
подзапрос должен быть заключен в круглые скобки;
подзапрос должен находиться справа от оператора сравнения в предикате;
в подзапросе нельзя использовать GROUP BY.
Объединение нескольких запросов в один
Реляционная операция «объединение» позволяет получить отношение, состоящее из всех строк, входящих в одно или оба объединяемых отношений. Но при этом исходные отношения или их объединяемые проекции должны быть совместимыми по объединению. Для SQL это означает, что две таблицы можно объединять тогда и только тогда, когда:
они имеют одинаковое число столбцов, например, m;
для всех i (i = 1, 2, ..., m) i-й столбец первой таблицы и i-й столбец второй таблицы имеют в точности одинаковый тип данных.

Получить всех продавцов и заказчиков, размещенных в Лондоне и вывести

их как единое целое:
SELECT snum, sname FROM t1 WHERE city = 'Лондон'
UNION
SELECT cnum, cname FROM t2 WHERE city = 'Лондон';
Замечание. Заголовки столбца при выводе исключены, потому что ни один из столбцов, выведенных объединением, не был извлечен непосредственно из только одной таблицы. Обратите внимание, что только последний запрос заканчивается точкой с запятой.
UNION будет автоматически исключать дубликаты строк из вывода. Например, вывод следующего запроса не будет содержать дубликатов:
SELECT snum, city FROM t2 UNION SELECT snum, city FROM t1;
Пример 41 Предположим, что вы должны сделать отчет о том, какие продавцы оформляют наибольшие и наименьшие заказы по датам. Можно объединить два запроса, вставив туда текст, чтобы различать вывод для каждого из них:
SELECT a.snum, sname, onum, ‘наибольший на’, odate FROM t1 a, t3 b WHERE a.snum = b.snum AND b.amt = ( SELECT MAX (amt) FROM t3c WHERE c.odate = b.odate ) UNION
SELECT a.snum, sname, onum, ‘наименьший на’, odate FROM t1 a, t3 b WHERE a.snum = b.snum AND b.amt =
( SELECT MIN (amt) FROM t3 c WHERE c.odate = b.odate);

* | { [ DISTINCT | ALL] ,….}

FROM { < имя_таблицы>
[ < псевдоним > ] }.,.. [ WHERE <предикат>] [ GROUP BY <имя_столбца>.,..]
[ HAVING <предикат>] [ ORDER BY <имя_столбца>.,. | <номер_столбца>.,..]
[ { UNION [ALL] SELECT * | { [DISTINCT | ALL] < < выражение >.,..} FROM .] } ] ...;
 
Элементы, используемые в команде SELECT:
< список выбора > - выражения, включающее имена столбцов, функции, знаки операций
<предикат>::=< выражение1 > < оператор> < выражение2> |
[NOT] EXISTS < выражение2 >
< оператор> ::= > | < | >= | <= | <> | = | IN | LIKE | BETWEEN …AND | OR | AND | NOT
< выражение2 >::= < выражение1> | [ANY | SOME | ALL ] <подзапрос>
<подзапрос>::= (SELECT * | { [ DISTINCT | ALL] < выражение >,….} FROM { < имя_таблицы> [ < псевдоним > ] }.,.. [ WHERE <предикат>] …)  

с помощью операторов DELETE (удалить), INSERT (вставить) и UPDATE (обновить).

Оператор удаления данных DELETE
Оператор DELETE имеет формат:
DELETE FROM <базовая таблица | представление> [WHERE <предикат>];
и позволяет удалить содержимое всех строк указанной таблицы (при отсутствии WHERE фразы) или тех ее строк, которые выделяются WHERE фразой.
Пример 42 Удалить все содержимое таблицы Продавцы:
DELETE FROM t1;
Теперь, когда таблица пуста, ее можно окончательно удалить командой DROP TABLE. Обычно, нужно удалить только некоторые определенные строки из таблицы. Чтобы определить, какие строки будут удалены, используется предикат, так же как и для запросов SELECT.
Пример 43 Удалить продавца Аксельрода из таблицы Продавцы:
DELETE FROM t1 WHERE snum = 1003;
Замечание. Рекомендуется сначала выполнить оператор SELECT, имеющий такое же предложение WHERE, чтобы убедиться те ли строки будем удалять.
Пример 44 Если закрыто ведомство в Лондоне, то чтобы удалить всех заказчиков назначенных, к продавцам в Лондоне, используем следующий оператор: DELETE FROM t2 WHERE snum = ANY
( SELECT snum FROM t1 WHERE city = 'Лондон' );
Эта команда удалит из таблицы Заказчики строки с заказчиками Хофман и Клеменс (назначенных для Пила), и Перера (назначенного к Мотика).

INSERT имеет один из следующих форматов:
INSERT INTO

| представление> [(<столбец> [,<столбец>] ...)] VALUES (<константа>|<переменная> [,<константа>| <переменная>] ...);
или
INSERT INTO <базовая таблица | представление> [(<столбец> [,<столбец>] ...)] <подзапрос>;
В первом формате в таблицу вставляется строка со значениями полей, указанными в перечне фразы VALUES (значения), причем i-е значение соответствует i-му столбцу в списке столбцов (столбцы, не указанные в списке, заполняются NULL-значениями). Если в списке VALUES фразы указаны все столбцы модифицируемой таблицы и порядок их перечисления соответствует порядку столбцов в описании таблицы, то список столбцов в предложении INTO можно опустить.
Пример 45 Добавить нового продавца:
INSERT INTO t1 VALUES (1009, 'Вильсон', 'Лондон', 0.12);
Замечание. Команды DML не производят никакого вывода, но СУБД должна дать некоторое подтверждение того, что данные были использованы. Имя таблицы (в нашем случае - Продавцы) должно быть предварительно определено в команде CREATE TABLE , а каждое значение, заданное в предложении значений VALUES, должно совпадать с типом данных столбца, в который оно вставляется.

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

таблица, а потом строки рабочей таблицы загружаются в модифицируемую таблицу.
При этом i-й столбец рабочей таблицы (i-й элемент списка выбора SELECT) соответствует i-му столбцу в списке столбцов модифицируемой таблицы. Здесь также при выполнении указанных выше условий может быть опущен список столбцов фразы INTO.
Можно также использовать команду INSERT, чтобы выбирать значения из одной таблицы и помещать их в другую. Чтобы сделать это, вы просто заменяете предложение VALUES (из предыдущего примера) на соответствующий запрос:
Пример 46 INSERT INTO Лондон_1 SELECT * FROM t1 WHERE city = 'Лондон';
Здесь выбираются все значения, произведенные запросом - все строки из таблицы Продавцы со значениями city = ‘Лондон’ - и помещаются в таблицу, называемую Лондон_1. Эта таблица должна отвечать следующим условиям:

она должна уже быть создана командой CREATE TABLE.;
она должна иметь четыре столбца, которые совпадают с таблицей Продавцы по типу данных.

UPDATE имеет формат:
UPDATE SET =

<значение> [,<столбец>= <значение>] ...[WHERE <предикат>];
где <значение>::=<столбец> |< выражение> | <константа> | <переменная>
и может включать столбцы лишь из обновляемой таблицы, т.е. значение одного из столбцов модифицируемой таблицы может заменяться на значение ее другого столбца или выражения, содержащего значения нескольких ее столбцов, включая изменяемый.
При отсутствии WHERE фразы обновляются значения указанных столбцов во всех строках модифицируемой таблицы. WHERE фраза позволяет сократить число обновляемых строк, указывая условия их отбора.
Пример 47 Предположим, что продавец Мотика с номером 1004 ушел на пенсию, и необходимо переназначить его номер новому продавцу:
UPDATE t1 SET sname = 'Гибсон', city = 'Бостон', comm = .10 WHERE snum = 1004;
Эта команда передаст новому продавцу Гибсону, всех текущих заказчиков бывшего продавца Мотика и их заказы.
Пример 48 Можно, используя коррелированный подзапрос, увеличить комиссионные всех продавцов, которые были назначены по крайней мере двум заказчикам: UPDATE t1 SET comm = comm + .01 WHERE 2 < =
(SELECT COUNT (cnum) FROM t2 WHERE t2.snum =t1.snum);
Теперь продавцы Пил и Серенс, имеющие нескольких заказчиков, получат повышение своих комиссионных.

определения таблицы  
Базовые таблицы описываются в SQL с помощью предложения

CREATE TABLE (создать таблицу). Рассмотрим синтаксис этого предложения:
CREATE TABLE <имя_таблицы>
(<элемент_таблицы> [,<элемент_таблицы >*,] [ограничения_целостности_таблицы]);
< элемент_таблицы > ::= <определение_столбца>
< определение_столбца > ::= <имя_столбца> <тип_данных> [DEFAULT <значение>] [<ограничения_ целостности_столбца>...]
< ограничения_ целостности_столбца > ::= NULL | NOT NULL | UNIQUE <спецификация>] | REFERENCES <спецификация> | CHECK (<проверочное_ограничение>)| PRIMARY KEY| FOREIGN KEY
Кроме имени таблицы, в операторе специфицируется список элементов таблицы, каждый из которых служит либо для определения столбца, либо для определения ограничения целостности определяемой таблицы. Требуется наличие хотя бы одного определения столбца. Для столбца задается имя и его тип данных, а также два необязательных раздела: раздел значения столбца по умолчанию и раздел ограничений целостности столбца.

определения таблицы  
Ограничение – это свойство, назначенное столбцу или таблице,

которое запрещает ввод в указанный столбец (или столбцы) недопустимых значений.
Основные виды ограничений: NULL, NOT NULL, DEFAULT, PRIMARY KEY, FOREIGN KEY, REFERENCES, CHECK, UNIQUE. Ограничения могут быть без имени или с именем, тогда перед ограничением вставляется слово CONSTRAINT <имя_ограничения>. Наличие имени ограничения позволяет ссылаться на него в операторе изменения таблицы, например:
ALTER TABLE Tab1 ADD CONSTRAINT col1 CHECK (col1 BETWEEN 0 AND 1);
В разделе значения по умолчанию DEFAULT указывается значение, которое должно быть помещено в строку, заносимую в данную таблицу, если значение данного столбца явно не указано. Значение по умолчанию может быть указано в виде литеральной константы с типом, соответствующим типу столбца, или путем задания ключевого слова NULL, означающего, что значением по умолчанию является неопределенное значение. Если значение столбца по умолчанию не специфицировано, и в разделе ограничений целостности столбца указано NOT NULL, то попытка занести в таблицу строку с NULL-значением данного столбца приведет к ошибке.

определения таблицы  
Указание в разделе ограничений целостности NOT NULL приводит

к неявному порождению проверочного ограничения целостности для всей таблицы «CHECK (C IS NOT NULL)» (где C - имя данного столбца). Если ограничение NOT NULL не указано, и раздел умолчаний отсутствует, то неявно порождается раздел умолчаний DEFAULT NULL. Если указана спецификация уникальности, то порождается соответствующая спецификация уникальности для таблицы.
Если в разделе ограничений целостности указано ограничение по ссылкам данного столбца (REFERENCES <спецификация>), то порождается соответствующее определение ограничения по ссылкам для таблицы:
FOREIGN KEY(C) REFERENCES <спецификация>
Пример 49 Создать таблицу:
CREATE TABLE Заказчики
( cnum integer NOT NULL PRIMARY KEY,
cname char (10) NOT NULL,
city char (10) DEFAULT 'Лондон',
rating integer,
snum integer NOT NULL,
UNIQUE (cnum, snum));
UNIQUE (cnum, snum) – это ограничение целостности таблицы, утверждающее, что комбинация номеров должна быть уникальной, т.е. у каждого заказчика только один продавец.

определения таблицы  
Пример 50 В следующем примере для задания

составного первичного ключа используется ограничение целостности таблицы PRIMARY KEY для пар:
CREATE TABLE Имена
( firstname char (10) NOT NULL,
lastname char (10) NOT NULL,
city char (10),
PRIMARY KEY ( firstname, lastname ));
Пример 51 В данном примере использовано ограничение по ссылкам:
CREATE TABLE Продавцы
(snum integer NOT NULL PRIMARY KEY,
sname char(10) NOT NULL,
city char(10), comm decimal,
snum integer REFERENCES Customers);
Существующую базовую таблицу можно в любой момент уничтожить с помощью предложения:
DROP TABLE <имя_таблицы>; по которому удаляется описание таблицы, ее данные, связанные с ней представления и индексы, построенные для столбцов таблицы.

SQL существует также предложение ALTER TABLE (изменить таблицу), которое позволяет

добавить справа к таблице новый столбец, изменить или удалить столбец, т.е. модифицировать описание таблицы, добавить ограничение.
Для построения индекса в SQL существует предложение CREATE INDEX (создать индекс), имеющее формат:
CREATE [UNIQUE] INDEX <имя_индекса> ON < имя_таблицы >
(<столбец >[[ASC] | DESC] [, <столбец> [[ASC] | DESC]] ...);
где UNIQUE (уникальный) указывает, что никаким двум строкам в индексируемой базовой таблице не позволяется принимать одно и то же значение для индексируемого столбца (или комбинации столбцов) в одно и то же время.
Для удаления индекса используется предложение:
DROP INDEX <имя_индекса>;
Замечание. Так как индексы могут создаваться или уничтожаться в любое время, то перед выполнением запросов целесообразно строить индексы лишь для тех столбцов, которые используются в WHERE и ORDER BY фразах запроса, а перед модификацией большого числа строк таблиц с индексированными столбцами эти индексы следует уничтожить.

определения представлений CREATE VIEW
Представление - это виртуальная таблица, которая сама

по себе не существует, но для пользователя выглядит таким образом, как будто она существует. Представление не поддерживаются его собственными физическими хранимыми данными. Вместо этого в каталоге таблиц хранится определение, оговаривающее, из каких столбцов и строк других таблиц оно должно быть сформировано.
Механизм представлений (view) является мощным средством языка SQL, позволяющим скрыть реальную структуру БД от некоторых пользователей за счет определения представления БД, которое реально является некоторым хранимым в БД запросом с именованными столбцами, а для пользователя ничем не отличается от базовой таблицы БД (с учетом технических ограничений).
Любая реализация должна гарантировать, что состояние представляемой таблицы точно соответствует состоянию базовых таблиц, на которых определено представление. Синтаксис предложения CREATE VIEW имеет вид:
CREATE VIEW <имя_представления> [(<столбец>[,<столбец>] ...)] AS <подзапрос> [WITH CHECK OPTION];
где подзапрос, следующий за AS и являющийся определением данного представления, не исполняется, а просто сохраняется в каталоге;

определения представлений CREATE VIEW
необязательная фраза «WITH CHECK OPTION» (с опцией

проверки) указывает, что для операций INSERT и UPDATE над этим представлением должна осуществляться проверка, обеспечивающая удовлетворение WHERE фразы подзапроса;
список имен столбцов должен быть обязательно определен лишь в тех случаях, когда:
а) хотя бы один из столбцов подзапроса не имеет имени (создается с помощью выражения, SQL-функции или константы);
б) два или более столбцов подзапроса имеют одно и то же имя;
если же список отсутствует, то представление наследует имена столбцов из подзапроса.
Пример 52 Например, создадим представление Лондон_продавцы, которое может рассматриваться пользователем как новая таблица в базе данных:
CREATE VIEW Лондон_продавцы AS SELECT * FROM t1 WHERE city = 'Лондон';
Пример 53 Следующее представление содержит данные о количестве заказчиков с каждым значением рейтинга:
CREATE VIEW Оценка (rating, number) AS
SELECT rating, COUNT (*) FROM t2 GROUP BY rating;

определения представлений CREATE VIEW
Пример 54 Предположим, что компания предусматривает премию

для тех продавцов, которые имеют заказчика с самым большим заказом для любой указанной даты. Можно проследить эту информацию с помощью представления:
CREATE VIEW Максимум AS
SELECT b.odate, a.snum, a.sname FROM t1 a, t3 b
WHERE a.snum = b.snum AND b.amt =
(SELECT MAX (amt) FROM t3 c WHERE c.odate = b.odate);
Представляемая таблица V является модифицируемой (т.е. по отношению к V можно использовать операторы DELETE, UPDATE, INSERT) в том и только в том случае, если выполняются следующие условия для спецификации запроса:
в списке выборки не указано ключевое слово DISTINCT;
каждое арифметическое выражение в списке выборки представляет собой одну спецификацию столбца, и спецификация одного столбца не появляется более одного раза (не должно быть агрегатных функций и выражений);
в разделе FROM указана только одна таблица, являющаяся либо базовой таблицей, либо модифицируемым представлением;
в условии выборки раздела WHERE не используются подзапросы;
отсутствуют разделы GROUP BY и HAVING.

определения представлений CREATE VIEW
Таким образом, могут быть модифицируемые представления (пример

52) и представления только для чтения, которые разрешается использовать только в команде SELECT (примеры 53, 54). С помощью представлений можно создать библиотеку сложных запросов и работать с сохраненными представлением как с таблицами.
Возможна ситуация, когда в модифицируемое представление добавляются данные, которые «проглатываются» (swallowed) в базовой таблице.
Пример 55 Рассмотрим такое представление:
CREATE VIEW Рейтинг AS
SELECT cnum, rating FROM t2 WHERE rating = 300;
Это - представление модифицируемое. Оно просто ограничивает доступ к определенным строкам и столбцам в таблице. Предположим, что вы вставляете (INSERT) следующую строку:
INSERT INTO Рейтинг VALUES (2018, 200);
Это - допустимая команда INSERT в этом представлении. Строка будет вставлена с помощью представления Рейтинг в таблицу Заказчики. Однако когда она появится там, она исчезнет из представления, поскольку значение оценки не равно 300. Это - обычная проблема. Пользователь не сможет понять, почему введя строку, он не может ее увидеть, и будет неспособен при этом удалить ее.

определения представлений CREATE VIEW
Вы можете быть гарантированы от модификаций такого

типа с помощью предложения WITH CHECK OPTION в определение представления.
Пример 56 Добавим это предложение в команду примера 55:
CREATE VIEW Рейтинг AS
SELECT cnum, rating FROM Заказчики WHERE rating = 300
WITH CHECK OPTION;
Вышеупомянутая вставка будет отклонена.
Замечание. Требование WITH CHECK OPTION в определении представления имеет смысл только в случае определения модифицируемой представляемой таблицы.

Для удаления представления используется оператор:
DROP VIEW <имя_представления>;

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

уничтожения, причем как целые таблицы, так и отдельные их поля.
Для этого имеются две возможности:
механизм представлений, рассмотренный ранее и используемый для скрытия засекреченных данных от пользователей, не обладающих правом доступа;
подсистема санкционирования доступа, позволяющая предоставить указанным пользователям определенные привилегии на доступ к данным и дать им возможность избирательно и динамически передавать часть выделенных привилегий другим пользователям, отменяя впоследствии эти привилегии, если потребуется.
Каждый пользователь в среде SQL , имеет специальное идентификационное имя или номер. Команда, посланная в базе данных, ассоциируется с определенным пользователем, или иначе, специальным идентификатором доступа.
Команда интерпретируется и разрешается (или запрещается) на основе информации, связанной с идентификатором (ID) доступа пользователя, подавшего команду. Обычно при установке СУБД в нее вводится какой-то идентификатор, который должен далее рассматриваться как идентификатор наиболее привилегированного пользователя - системного администратора. Каждый, кто может войти в систему с этим ID (и может выдержать тесты на достоверность), будет считаться системным администратором до выхода из системы. Системный администратор может создавать БД и имеет все привилегии на их использование.

управления привилегиями
Эти привилегии или их часть могут предоставляться другим

пользователям (пользователям с другими ID). В свою очередь, пользователи, получившие привилегии от системного администратора, могут передать их (или их часть) другим пользователям, которые могут их передать следующим и т.д.
Определение привилегий производится в следующем синтаксисе:
GRANT <привилегии> ON < объект> TO < субъект >
[{<субъект >}...] [WITH GRANT OPTION];
<привилегии> - список, состоящий из одной или нескольких привилегий, разделенных запятыми, либо фраза ALL PRIVILEGES (все привилегии);
<привилегии>::= ALL [PRIVILEGES] | <действие> [{,<действие> }...]
<действие>::= SELECT | INSERT | DELETE | UPDATE [(<список столбцов>)]
| REFERENCES [(<список столбцов>)]
[(<список столбцов>)]::= <имя_столбца> [{,<имя_столбца> }...]
<объект> - имя и, если надо, тип объекта (база данных, таблица, представление, индекс и т.п.);
<субъекты> - список, включающий один или более идентификаторов доступа, разделенных запятыми, либо специальное ключевое слово PUBLIC (общедоступный).
<субъект> ::= PUBLIC | <идентификатор_доступа_пользователя>

управления привилегиями

Замечание. Привилегией REFERENCES по отношению к указанным столбцам таблицы

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

Пример 57 GRANT SELECT, INSERT ON Заказы TO Адриан, Диана;
Пример 58 GRANT ALL ON Заказчики TO Стефан;
Пример 59 GRANT SELECT ON Заказы TO PUBLIC;

Пользователь должен иметь возможность передать привилегии (или их часть) другим пользователям. Обычно это делается в системах, где один или более пользователей создают несколько (или все) базовые таблицы в БД, а затем передают ответственность за них тем, кто будет фактически с ними работать. SQL позволяет делать это с помощью предложения WITH GRANT OPTION.

управления привилегиями

Пример 60 Если Диана хотела бы, чтобы Адриан имел

право предоставлять привилегию SELECT в таблице Заказчики другим пользователям, она дала бы ему привилегию SELECT с использованием предложения WITH GRANT OPTION: GRANT SELECT ON Заказчики TO Адриан WITH GRANT OPTION;
После выполнения этой команды Адриан получил право передавать привилегию SELECT третьим лицам.

Если пользователь USER_1 предоставил какие-либо привилегии другому пользователю USER_2, то он может впоследствии отменить все или некоторые из этих привилегий. Отмена осуществляется с помощью предложения REVOKE (отменить), общий формат которого очень похож на формат предложения GRANT:
REVOKE <привилегии> ON < объект> FROM < субъект > [{,<субъект >}...];
Пример 61 REVOKE INSERT, DELETE ON Заказчики FROM Адриан, Стефан.

управления транзакциями

Транзакция - это неделимая, с точки зрения воздействия на

СУБД, последовательность операций манипулирования данными. Для пользователя транзакция выполняется по принципу «все или ничего», т.е. либо транзакция выполняется целиком и переводит базу данных из одного целостного состояния в другое целостное состояние, либо, если по каким-либо причинам, одно из действий транзакции невыполнимо, или произошло какое-либо нарушение работы системы, БД возвращается в исходное состояние, которое было до начала транзакции (происходит откат транзакции).
С этой точки зрения, транзакции важны как в многопользовательских, так и в однопользовательских системах.
В однопользовательских системах транзакции - это логические единицы работы, после выполнения которых БД остается в целостном состоянии.
Транзакции также являются единицами восстановления данных после сбоев - восстанавливаясь, система ликвидирует следы транзакций, не успевших успешно завершиться в результате программного или аппаратного сбоя.
Эти два свойства транзакций определяют атомарность (неделимость) транзакции.
В многопользовательских системах, кроме того, транзакции служат для обеспечения изолированной работы отдельных пользователей - пользователям, одновременно работающим с одной базой данных, кажется, что они работают как бы в однопользовательской системе и не мешают друг другу.

управления транзакциями

Транзакция или логическая единица работы БД, - это в

общем случае последовательность ряда таких операций, которые преобразуют некоторое непротиворечивое состояние базы данных в другое непротиворечивое состояние, но не гарантируют сохранения непротиворечивости во все промежуточные моменты времени.
Транзакция обладает четырьмя важными свойствами, известными как свойства АСИД:
(А) Атомарность. Транзакция выполняется как атомарная операция - либо выполняется вся транзакция целиком, либо она целиком не выполняется.
(С) Согласованность. Транзакция переводит базу данных из одного согласованного (целостного) состояния в другое согласованное (целостное) состояние. Внутри транзакции согласованность базы данных может нарушаться.
(И) Изоляция. Транзакции разных пользователей не должны мешать друг другу (например, как если бы они выполнялись строго по очереди).
(Д) Долговечность. Если транзакция выполнена, то результаты ее работы должны сохраниться в базе данных, даже если в следующий момент произойдет сбой системы.

управления транзакциями

Транзакция обычно начинается автоматически с момента присоединения пользователя к

СУБД и продолжается до тех пор, пока не произойдет одно из следующих событий:
подана команда COMMIT (зафиксировать транзакцию);
подана команда ROLLBACK (откатить транзакцию);
произошло отсоединение пользователя от СУБД;
произошел сбой системы.
Свойства АСИД транзакций не всегда выполняются в полном объеме. Особенно это относится к свойству И (изоляция). В идеале, транзакции разных пользователей не должны мешать друг другу, т.е. они должны выполняться так, чтобы у пользователя создавалась иллюзия, что он в системе один. Простейший способ обеспечить абсолютную изолированность состоит в том, чтобы выстроить транзакции в очередь и выполнять их строго одну за другой. Очевидно, при этом теряется эффективность работы системы. Поэтому реально одновременно выполняется несколько транзакций (смесь транзакций). Различается несколько уровней изоляции транзакций. На низшем уровне изоляции транзакции могут реально мешать друг другу, на высшем - они полностью изолированы. За большую изоляцию транзакций приходится платить большими накладными расходами системы и замедлением работы. Пользователи или администратор системы могут по своему усмотрению задавать различные уровни изоляции всех или отдельных транзакций.

управления транзакциями

Свойство Д (долговечность) также не является абсолютными свойством, т.к.

некоторые системы допускают вложенные транзакции. Если транзакция Б запущена внутри транзакции А, и для транзакции Б подана команда COMMIT, то фиксация данных транзакции Б является условной, т.к. внешняя транзакция А может откатиться. Результаты работы внутренней транзакции Б будут окончательно зафиксированы только, если будет зафиксирована внешняя транзакция А.
Свойство (С) - согласованность транзакций определяется наличием понятия согласованности базы данных. База данных находится в согласованном (целостном) состоянии, если выполнены (удовлетворены) все ограничения целостности, определенные для базы данных.
Ограничение целостности (бизнес-правило) - это некоторое утверждение, которое может быть истинным или ложным в зависимости от состояния базы данных.
Примерами ограничений целостности могут служить следующие утверждения:
Пример 1. У каждого заказчика только один продавец.
Пример 2. Каждый сотрудник имеет уникальный табельный номер.
Пример 3. Сотрудник обязан числиться только в одном отделе.
Пример 4. Сумма денег накладной обязана равняться сумме произведений цен товаров на количество товаров для всех товаров, входящих в накладную.

управления транзакциями

Как видно из этих примеров, некоторые из ограничений целостности

являются ограничениями реляционной модели данных . Пример 2 представляет ограничение, реализующее целостность сущности. Примеры 1, 3 представляют ограничения, реализующие ссылочную целостность. Другие ограничения являются достаточно произвольными утверждениями, связанными с правилами в конкретной предметной области (пример 4).
Любое ограничение целостности является семантическим понятием, т.е. появляется как следствие определенных свойств объектов предметной области и/или их взаимосвязей.
Вместе с понятием целостности базы данных возникает понятие реакции системы на попытку нарушения целостности. Система должна не только проверять, не нарушаются ли ограничения в ходе выполнения различных операций, но и должным образом реагировать, если операция приводит к нарушению целостности. Имеется два типа реакции на попытку нарушения целостности:
Отказ выполнить "незаконную" операцию.
Выполнение компенсирующих действий.

управления транзакциями
По времени проверки ограничения делятся на:
немедленно проверяемые ограничения;

ограничения с отложенной проверкой.
По области действия ограничения делятся на:
ограничения домена;
ограничения атрибута;
ограничения кортежа;
ограничения отношения;
ограничения базы данных.
Стандарт языка SQL поддерживает только декларативные ограничения целостности, реализуемые как:
ограничения домена;
ограничения, входящие в определение таблицы;
ограничения, хранящиеся в базе данных в виде независимых утверждений (assertion).
Проверка ограничений допускается как после выполнения каждого оператора, могущего нарушить ограничение, так и в конце транзакции. Во время выполнения транзакции можно изменить режим проверки ограничения.
Стандарт SQL не предусматривает процедурных ограничений целостности, реализуемых при помощи триггеров и хранимых процедур. Таким образом, реализация ограничений средствами конкретной СУБД обладает большей гибкостью, нежели с использованием исключительно стандартных средств SQL.
 

параллельной работы транзакций

Современные многопользовательские системы допускают одновременную работу большого числа

пользователей. При этом, если не предпринимать специальных мер, транзакции будут мешать друг другу. Этот эффект известен как проблемы параллелизма.
Имеются три основных следствия проблемы параллелизма:
проблема потери результатов обновления;
проблема незафиксированной зависимости (чтение "грязных" данных, неаккуратное считывание);
проблема несовместимого анализа.
Решение проблем параллелизма состоит в нахождении такой стратегии запуска транзакций, чтобы обеспечить сериальность графика запуска и не слишком уменьшить степень параллельности.
Под сериализаций параллельно выполняющихся транзакций понимается такой порядок планирования их работы, при котором суммарный эффект смеси транзакций эквивалентен эффекту их некоторого последовательного выполнения. Сериальный план выполнения смеси транзакций - это такой план, который приводит к сериализации транзакций.

параллельной работы транзакций

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

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

График запуска транзакции называется верным (сериализуемым), если он эквивалентен какому-либо последовательному графику.

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

параллельной работы транзакций

Одним из методов обеспечения сериальности графика запуска является

протокол доступа к данным при помощи блокировок.
В простейшем случае различают S-блокировки (Shared locks разделяемые) и X-блокировки (eXclusive locks монопольные). Протокол доступа к данным имеет вид:
Прежде, чем прочитать объект, транзакция должна наложить на этот объект S-блокировку.
Прежде, чем обновить объект, транзакция должна наложить на этот объект X-блокировку. Если транзакция уже заблокировала объект S-блокировкой (для чтения), то перед обновлением объекта S-блокировка должна быть заменена X-блокировкой.
Если блокировка объекта транзакцией B отвергается оттого, что объект уже заблокирован транзакцией A, то транзакция B переходит в состояние ожидания. Транзакция B будет находиться в состоянии ожидания до тех пор, пока транзакция A не снимет блокировку объекта.
X-блокировки, наложенные транзакцией A, сохраняются до конца транзакции A.

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

к данным, то проблемы параллелизма решаются (почти все, кроме «фантомов»), но появляются тупики. Состояние тупика (dead locks) характеризуется тем, что две или более транзакции пытаются заблокировать одни и те же объекты, и бесконечно долго ожидают друг друга.
Для разрушения тупиков система периодически или постоянно поддерживает граф ожидания транзакций. Наличие циклов в графе ожидания свидетельствует о возникновении тупика. Для разрушения тупика одна из транзакций (наиболее дешевая с точки зрения системы) выбирается в качестве жертвы и откатывается.
Эффект фиктивных элементов (фантомов) заключается в том, что здесь за один шаг выполняется достаточно много операций - чтение одновременно нескольких строк, удовлетворяющих некоторому условию. Транзакция A дважды выполняет выборку строк с одним и тем же условием. Между выборками вклинивается транзакция B, которая добавляет новую строку, удовлетворяющую условию отбора. Транзакция A ничего не знает о существовании транзакции B, и, т.к. сама она не меняет ничего в базе данных, то ожидает, что после повторного отбора будут отобраны те же самые строки.
Результат. Транзакция A в двух одинаковых выборках строк получила разные результаты.
Для решения проблемы «фантомов», а также для уменьшения накладных расходов, вызываемых большим количеством блокировок, применяются более сложные методы.