Советы по созданию кластерных индексов.

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

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

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

· Кластерные индексы идеальны для запросов, где есть выбор по диапазону или нужны сортированные результаты. Так происходит потому, что данные в кластерном индексе физически отсортированы по какому-то столбцу. Запросы, получающие выгоду от кластерных индексов, обычно включают в себя операторы BETWEEN, <, >, GROUP BY, ORDER BY, и агрегативные операторы типа MAX, MIN, и COUNT.

· Кластерные индексы хороши для запросов, которые ищут запись с уникальным значением (типа номера служащего) и когда нужно вернуть большую часть данных из записи или всю запись. Так происходит потому, что запрос покрывается индексом.

· Кластерные индексы хороши для запросов, которые обращаются к столбцам с ограниченным числом значений, например столбцы, содержащие данные о странах или штатах. Но если данные столбца мало отличаются, например, значения типа "да/нет", "мужчина/женщина", то такие столбцы вообще не должны индексироваться.

· Кластерные индексы хороши для запросов, которые используют операторы GROUP BY или JOIN.

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

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

Вообще, если таблица нуждается только в одном индексе, следует сделать его кластерным индексом.

Лекция 10, 11, 12, 13, 14

SQL

Последствием публикации статей Кодда, вводящих реляционную модель и реляционные языки, стал взрыв активности коммерческих и исследовательских организаций по реализации реляционных языков. Тремя наиболее важными языками, появившимися в результате этих усилий, стали SQL, QBE (Query-by-Example, запрос по образцу) и (Query Language). SQL и QBE были созданы IBM в 70-е годы и выполняли сходные функции. хотя SQL – текстовый язык, а QBE – графический. QUEL – оригинальный язык INGRES, РСУБД, созданной в 70-е годы Калифорнийским университетом Беркли.

В 1986 году был принят первый стандарт ANSI для SQL. Этот стандарт был пересмотрен (несильно) в 1989 и (существенно) в 1992. SQL является единственным стандартом ANSI реляционного языка БД.

Хотя название SQL предполагает, что это язык запросов, он включает в себя помимо средств запросов определение таблиц, обновление БД, определение представлений данных и привилегий доступа.

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

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

При зучении SQL будем использовать для примера следующую БД для строительной компании “Премьер”:

Рабочий (ИдРабочего, Фамилия, ПочасоваяСтавка, Специальность, Начальник)

Назначение (ИдРабочего, ИдЗдания, ДатаНачала, КоличесвтоДней)

Здание (ИдЗдания, Адрес, Тип, КолЭтажей, Статус)

Определение схемы и таблиц

В SQL-92 определены следующие типы данных:

Точные числовые:

Integer - целое число

Small integer – короткое целое

Numeric (p,s) - число

Decimal (p,s) – десятичное число

р –общее количество знаков после запятой, s – число знаков после запятой

Приблизительные числовые:

Real – действительное число

Double precision – число с двойной точностью

Float – с плавающей запятой

Символьные строки:

Char – символьная строка

VarChar – символьная строка переменной длины

Двоичные строки:

Bit (n)

Varbit (n)

Эти поля используются для флагов и других двоичных масок.

Дата-Время:

Date

Time

Timestamp дата и время

Time with time zone – время и часовой пояс

Timestamp with time zone – дата, время и часовой пояс

Interval (промежуток)

Year-month – год и месяц

Day-Time – день и время

Промежуток – разность между двумя датами или между двумя моментами времени.

В текущих реализациях синтаксические и семантические свойства некоторых типов могут различаться.

Определение домена – это конкретный тип данных, который может быть определен в схеме и использоваться при определении столбцов. Пусть для идентификаторов нужно задать тип данных, значение по умолчанию и условие не пустоты. При каждом определении идентификатора необходимо указывать эти условия. Можно упростить задачу, создав домен.

CREATE DOMAIN Идентификатор NUMERIC(4) DEFAULT 0 CHECK (VALUE IS NOT NULL).

Таблицы определяются в три этапа:

1. Таблице присваивается имя

2. Определяется каждый столбец с возможными ограничительными условиями на него

3. Определяются ограничительные условия на таблицу.

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

CREATE TABLE <имя таблицы>

(<элемент таблицы> [{,<элемент таблицы>}...]), где

<элемент т-цы> ::=

{< Определение столбца> |

| <Определение первичного ключа> |

| <Определение внешнего ключа> |

| <Условие уникальности данных> |

| <Условие проверки>}

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

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

<опр-е столбца> ::=

<имя столбца> <тип д-х> [<размер>]

[DEFAULT { <константа> | USER | NULL }]

[<раздел огр-й целостности столбца>...]

< раздел огр-й целостности столбца > ::=

NOT NULL [UNIQUE | PRIMARY KEY]

| <огр-е по ссылкам>

| CHECK (<усл-е>)

В разделе значения по умолчанию указывается значение, которое должно быть помещено в строку, заносимую в данную таблицу, если значение данного столбца явно не указано. Значение по умолчанию может быть указано в виде константы с типом, соответствующим типу столбца; путем задания ключевого слова USER, которому при выполнении оператора занесения строки соответствует символьная строка, содержащая имя текущего пользователя (в этом случае столбец должен иметь тип символьных строк); или путем задания ключевого слова NULL, означающего, что значением по умолчанию является неопределенное значение.

Ограничение по ссылкам для столбца задается в виде:. REFERENCES <ссылаемая таблица > [(<список столбцов>)]

Условие в проверочном ограничении столбца должно ссылаться только на данный.

Раздел определения ограничений целостности таблицы обладает следующим синтаксисом:

< определение первичного ключа > ::=

< {UNIQUE | PRIMARY KEY} (<список уникальности>)

< определение внешнего ключа > ::=

FOREIGN KEY (<список столбцов>) REFERENCES <ссылаемая таблица > [(<список столбцов>)]

< Условие проверки > ::=

CHECK (<усл-е>)

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

Далее T обозначает таблицу, для которой определяются ограничения целостности.

При определении столбца, входящего в список уникальности, должно быть указано ограничение столбца NOT NULL.

Пример

CREATE TABLE Рабочий

( ИдРабочего Идентификатор PRIMARY KEY,

Фамилия char (12) NOT NULL,

Почасовая ставка numeric (5,2),

Специальность char (12),.

Начальник numeric(4),

FOREIGN KEY Начальник REFERENCES Начальник ON DELETE SET NULL;

CREATE TABLE Здание

(ИдЗдания Идентификатор PRIMARY KEY,

Адрес char (20),.

Тип char (10) DEFAULT ‘Офис’

CHECK (Тип IN ‘Офис’,’Склад’,’Магазин’,’Жилой дом’),.

КолЭтажей numeric(2),

Статус numeric(1) DEFAULT 1 CHECK (Статус>0 AND Статус<4)

CREATE TABLE Назначение

(ИдЗдания Идентификатор,

ИдРабочего Идентификатор,

ДатаНачала date,

КоличествоДней interval date(3),

PRIMARY KEY (ИдЗдания, ИдРабочего),

FOREIGN KEY ИдЗдания REFERENCES Здание ON DELETE CASCADE;

FOREIGN KEY ИдРабочего REFERENCES Рабочий ON DELETE CASCADE);

. Подобно первичному ключу, внешний ключ может иметь любое число полей

Внешний ключ и родительский ключ, на который он ссылается должны иметь одинаковое количество полей и совпадающие типы полей. Каждое значение во внешнем ключе должно быть представлено один, и только один раз, в родительском ключе.

Все поля, используемые как родительские ключи, должны иметь ограничение PRIMARY KEY или ограничение UNIQUE. Иначе требуется, чтобы в определении родительской таблицы присутствовало определение первичного ключа, и список родительских ключей для ограничения по ссылке неявно полагается совпадающим со списком имен столбцов из определения первичного ключа таблицы родительской таблицы.

Ограничение по ссылкам удовлетворяется, если для каждой корректной ссылки существует объект, на который она ссылается. В привычной программистам терминологии, ограничение по ссылкам не позволяет производить "висячие" ссылки, не ведущие ни к какому объекту.

Любые значения, которые помещаются командой INSERT или UPDATE в поля, определенные как внеш. ключи, должны уже быть представлены в их родительских ключах. М. помещать пустые(NULL) значения в эти поля, несмотря на то что значения NULL запрещены в родительских ключах, если они имеют ограничение NOT NULL. М. удалять (DELETE ) любые строки с внешними ключами, не используя родительские ключи вообще.

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

ON DELETE CASCADE означает, что если строка таблицы Рабочий, на которую ссылается внешний ключ, будет удалена, то удаление должно быть распространено на все ссылающиеся на нее строки таблицы Назначение.

Команда ON DELETE (при удалении) аналогична команде ON UPDATE (при обновлении) и обе команды имеют опции:

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

CASCADE – при удалении строки-предка автоматически удаляются строки-потомки

SET NULL – установить пустое значение внешним ключам в строках-потомках

SET DEFAULT – установить во внешних ключах строк-потомков значение, по умолчанию установленное для данного столбца.

Удалить таблицу можно с помощью оператора

DROP TABLE ИмяТаблицы.

Изменить структуру таблицы можно с помощью оператора

ALTER <имя таблицы>

ADD/DROP (<имя столбца> <тип данных>

[,<имя столбца> <тип данных>...]);

Можно также добавить или удалить первичный ключ. внешний ключ, условие уникальности, условие проверки.

Пример

ALTER TABLE Назначение DROP FOREIGH KEY Рабочий

ALTER TABLE Рабочий DROP PRIMARY KEY ADD PRIMARY KEY (Фамилия)

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

Одним из структурных элементов физической памяти, присутствующим в большинстве РСУБД, является индекс. Индекс – это средство, обеспечивающее быстрый доступ к строкам таблицы на основе значений одного или нескольких столбцов.

Пусть есть таблица

Индекс имеет вид

Указатели на строки

Индекс обеспечивает доступ к таблице на основе столбца Категория.

СУБД пользуется индексом так же, как люди предметным указателем книги. В индексе хранятся значения данных и указатели на строки, где эти данные встречаются. Значения в индексе отсортированы, чтобы СУБД могла быстро найти требуемое значение. Поиск в индексе осуществляется быстро благодаря сортировке и короткой длине строк таблицы индексов. переход от индекса к строке тоже происходит быстро, так как в индексе содержится информация о том, где на диске располагается эта строка. Недостатки индекса – дополнительное место на диске и необходимость обновления при обновлении таблицы. Полезно создавать индекс для столбцов, которые часто используются в условиях поиска. Для первичного ключа СУБД всегда создает индекс.

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

CREATE INDEX ИмяИндекса ON ИмяТаблицы (ИмяСтолбца [{ASC | DESC}] [,ИмяСтолбца [{ASC | DESC}]…])

Удаление индекса – DROP INDEX ИмяИндекса.

Для изменения данных используются ператоры INSERT, UPDATE и DELETE, позволяющие, соответственно, добавлять, изменять значения в строках и удалять строки определенной таблицы в БД.

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

INSERT INTO ИмяТаблицы [(СписокСтолбцов)] {VALUES (СписокЗначений) |

Опертор SELECT}

Если список столбцов опущен, он создается автоматически и в нем слева направо перечисляются все столбцы таблицы. При добавлении в таблицу новой строки всем столбцам, отсутсвующим в списке столбцов, автоматически присваивается значение NULL. Значение NULL можно присвоить и явным образом, включив в список значений ключевое слово NULL.

Пример

INSERT INTO Рабочий VALUES(1001, ‘Иванов’,5.4,’Электрик’,1200)

Оператор DELETE имеет синтаксис

DELETE FROM ИмяТаблицы [WHERE УсловиеПоиска]

Если нет условия WHERE, то удаляются все строки из таблицы. Но сама таблица не удаляется.

Пример.

DELETE FROM Рабочий WHERE Фамилия=’Иванов’

Оператор UPDATE

UPDATE ИмяТаблицы SET {Имястолбца=Выражение [,Имястолбца=Выражение…]}

[WHERE УсловиеПоиска]

Пример

UPDATE Рабочий SET ПочасоваяСтавка=6.0 WHERE Фамилия=’Иванов’

Простые запросы

Простой запрос – это запрос, который обращается только к одной таблице.

Пример. Кто работает штукатурами?

select Фамилия from Рабочий where Специальность=’Штукатур’

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

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

Предложение WHERE содержит условие, на основании которого выбираются строки таблицы.

Приведенный запрос обрабатывается системой в следующем порядке: FROM, WHERE, SELECT.

Если нужно выввести данные без повторений, используется ключевое слово DISTINCT.

Пример. Перечислить все специальности

SELECT DISTINCT Специальность FROM Рабочий

Пример. Привести все данные о зданиях офисов.

SELECT * FROM Здание WHERE Тип=’Офис’

Звездочка означает «строка целиком».

Пример. Какова недельная зарплата каждого электрика?

SELECT Фамилия, ‘Недельная зарплата=’, 40*ПочасоваяСтавка

FROM Рабочий

WHERE Специальность=’Электрик’

ORDER BY Фамилия

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

Команда ORDER BY сортирует результат запроса в возрастающем алфавитно-числовом порядке по указанному столбцу если нужно упорядочить результаты по убыванию, к команде нужно добавить слово DESC. Это предложение может сортировать результаты по нескольким столбца, по одним – по возрастаноию, по другим – по убыванию.

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

Пример. У кого почасовая ставка от 3 до 5 гривен?

SELECT * FROM Рабочий WHERE ПочасоваяСтавка>=3 and ПочасоваяСтавка<=5

Для сравнения столбцов с другими столбцами или с константами могут использоваться 6 операторов сравнения =, <, >, <>, >=, <=. Для создания составных условий или для отрицания условия могут использоваться булевы операции AND, OR или NOT. Для группировки условий используются скобки.

для формулировки этого запроса можно использовать оператор BETWEEN.

SELECT * FROM Рабочий WHERE ПочасоваяСтавка BETWEEN 3 and 5

BETWEEN определяет закрытый интервал.

Пример. Перечислить штукатуров, кровельщиков и электриков.

SELECT * FROM Рабочий

WHERE Специальность IN ('Штукатур','Кровельщик','Электрик')

Предикат IN проверяет, находится ли значение поля среди указанных в списке.

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

Пример. Перечислить работников, чья специальность начинается на «Элек».

SELECT * FROM Рабочий WHERE Специальность LIKE (‘Элек%')

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

Пример. Найти всех работников по фамилии Иванов

SELECT * FROM Рабочий WHERE Фамилия LIKE (‘Иванов ____')

В этом случае 4 знака подчеркивания гасят инициалы.

Пример. Найти всех работников, чьи фамилии начинаются на К

SELECT * FROM Рабочий WHERE Фамилия LIKE (‘К%')

После LIKE может присутствовать раздел ESCAPE, который задает некоторый одиночный символ x, тогда пары символов "x_" и "x%" представляют одиночные символы "_" и "%", соответственно.

SELECT *

FROM Список

WHERE Описание LIKE ‘ %/_%’ ESCAPE ‘/’;

Вывод тех строк таблицы Список, в которых столбец Описание содержит символ _.

Предикат NULL всегда принимает значения true или false. При этом значение "x IS NULL" равно true тогда и только тогда, когда значение x не определено. Значение предиката "x NOT IS NULL" равно значению "NOT x IS NULL".

Многотабличные запросы

Пример. Каковы специальности рабочих, назначенных на здание 345?

Данные, необходимые для ответа, находятся в двух таблицах

SELECT Специальность FROM Рабочий, Назначение

WHERE Рабочий.ИдРаб=Назначение.ИдРаб and ИдЗд=345

Сначала обрабатывается предложение FROM. Если указано несколько таблиц, то система создает декартово произведение строк этих таблиц. После создания гигантской таблицы система применяет команду WHERE. Первое условие в запросе – условие соединения. Так как обе таблицы содержат столбец ИдРаб, то их декартово произведение будет содержать два столбца с таким именем. Чтобы различать их, перед именем столбца через точку помещается имя таблицы.

Пример. Перечислить рабочих, назначенных на здания офисов.

SELECT Фамилия FROM Рабочий, Назначение, Здание

WHERE Рабочий.ИдРаб=Назначение.ИдРаб and Назначение.ИдЗд=Здание.ИдЗд and Тип='Офис'

Можно присоединить таблицу саму к себе.

Пример. Перечислить работников, указав фамилии их начальников.

SELECT А.Фамилия, В.Фамилия FROM Рабочий А, Рабочий В

WHERE А.Начальник=В.ИдРаб

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

Подзапросы

Подзапросом называется запрос внутри запроса.

Пример. Каковы специальности рабочих, назначенных на здание 345?

SELECT Специальность FROM Рабочий

WHERE ИдРаб IN

(SELECT ИдРаб FROM Назначение

WHERE ИдЗд=345)

Предложение SELECT подзапроса содержит один и только один столбец. Иначе предложение WHERE внешнего запроса не имела бы смысла.

В этом примере подзапрос может выполняться прежде, чем хотя бы одна строка рассматривается внешним запросом, то есть подзапрос логически независим от главного запроса. Говорят, что такой подзапрос не коррелирован с главным запросом.

Некоррелированным подзапросом называется подзапрос, значение которого не зависит ни от какого внешнего запроса.

Внутри подзапроса может быть другой подзапрос.

Пример. . Перечислить рабочих, назначенных на здания офисов.

SELECT Фамилия FROM Рабочий

WHERE ИдРаб IN

(SELECT ИдРаб FROM Назначение

WHERE ИдЗд IN

(SELECT ИдЗд FROM Здание

WHERE Тип='Офис’))

Здесь нигде не нужно указывать имена таблиц перед именами столбцов, так как каждый подзапрос обрабатывает одну и только одну таблицу.

Выполнение запроса происходит в порядке изнутри наружу.

Коррелированные подзапросы

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

Пример. Перечислить рабочих, чьи почасовые ставки выше, чем ставки их начальников.

SELECT Фамилия FROM Рабочий A

WHERE A.ПочасоваяСтавка >

(SELECT В.ПочасоваяСтавка FROM Рабочий В

WHERE В.ИдРаб=А.ИдРаб)

Логические этапы выполнения этого запроса таковы:

1. Система создает две копии таблицы Рабочий. Копия А относится к работнику, а копия В – к начальнику.

2. Затем ситема рассматривает каждую строку А. Строка выбирается, если она удовлетворяет условию WHERE. Условие в ней означает, что строка будет выбрана, если величина ПочасовойСтавки в ней больше, чем Почасовая ставка, порожденная подзапросом.

3. Подзапрос выбирает величину почасовой ставки из строки В, ИдРаб которой равен Начальник строки А, в данный момент рассматриваемой главным запросом. Это Почасовая ставка начальника. Так как А.ПочасоваяСтавка может сравниваться только с одним значением, подзапрос должен выдавать только одну величину. Эта величина меняется в зависимости от того, какая строка А рассматривается. Таким образом, подзапрос коррелирует с главным запросом.

Предикат EXISTS

Пусть нужно идентифицировать рабочих, которые не назначены на некоторое здание.

Решение

SELECT ИдРаб FROM Назначение WHERE ИдЗд<>345

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

Правильное решение:

SELECT ИдРаб FROM Рабочий

WHERE NOT EXISTS

(SELECT * FROM Назначение

WHERE Назначение.ИдРаб=Рабочий.ИдРаб AND

ИдЗд<>345)

Предикаты EXISTS (NOT EXISTS) всегда помещается перед подзапросом. Значением предиката EXISTS всегда является true или false, и это значение равно true тогда и только тогда, когда результат вычисления подзапроса не пуст.

В этом примере был использован коррелированный подзапрос. Можно обойтись некоррелированным подзапросом, если использовать предикат IN

SELECT ИдРаб FROM Рабочий

WHERE ИдРаб NOT IN

(SELECT ИдРаб FROM Назначение

WHERE Назначение.ИдРаб=Рабочий.ИдРаб AND

ИдЗд<>345)

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

Пример Перечислить рабочих, назначенных на каждое здание.

Этот вопрос может быть реализован в SQL с помощью двойного отрицания. Запрос можно переформулировать следующим образом: Перечислить таких работников, для которых не существует здания, на которое они не назначены.

SELECT ИдРаб FROM Рабочий

WHERE NOT EXISTS

(SELECT ИдЗд FROM Здание

WHERE NOT EXISTS

(SELECT * FROM Назначение

WHERE Назначение.ИдЗд=Здание.ИдЗд AND

Назначение.ИдРаб=Рабочий.ИдРаб

Предикат с квантором

Предикат с квантором имеет синтаксис

<выражение> <операция сравнения> <квантор> <подзапрос>

Квантор - это ALL или SOME(ANY).

Пусть x результат вычисления арифметического выражения левой части предиката, а S - результат вычисления подзапроса.

Предикат "x <операция сравнения> ALL S" имеет значение true, если S пусто или результат сравнения "x <операция сравнения> s" равно true для каждой строки s, входящей в S. Предикат "x <операция сравнения> ALL S" имеет значение false, если результат сравнения "x <операция сравнения> s" равно false хотя бы для одной s, входящей в S.

Предикат "x < операция сравнения > SOME S" имеет значение false, если S пусто или результат сравнения "x <операция сравнения > s" равно false для каждой s, входящей в S. Предикат "x < операция сравнения > SOME S" имеет значение true, если результат сравнения "x < операция сравнения > s" равно true хотя бы для одной s, входящей в S.

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

SELECT Фамилия FROM Рабочий А

WHERE ПочасоваяСтавка >= ALL

(SELECT ПочасоваяСтавка FROM Рабочий В

WHERE А.Специальность=В.Специальность)

Встроенные функции

В SQL есть 5 агрегатных функций, которые рассматривают множество строк таблицы и выдают одно значение. Это MAX, MIN, AVG, SUM, COUNT.

Пример. Определить максимальную и минимальную почасовые ставки

SELECT MAX(ПочасоваяСтавка), MIN(ПочасоваяСтавка) FROM Рабочий

Функция COUNT подсчитывает общее количество данных и может ссылаться на строку целиком. а не на отдельный столбец.

Пример. Сколько зданий имеют тип Офис?

SELECT COUNT(*)FROM Здание WHERE Тип=’Офис’

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

Пример. Сколько всего разных специальностей?

SELECT COUNT(DISTINCT Специальность) FROM Рабочий

Функции AVG и SUM должны использоваться только с числовыми столбцами. Все функции, кроме COUNT, можно использовать с вычисляемыми выражениями.

Запросы с группировкой

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

SELECT Начальник, MAX(ПочасоваяСтавка) FROM Рабочий

GROUP BY Начальник

При обработке этого запроса система сначала разбивает строки таблицы Рабочий на группы по следующему правилу. Строки помещаются в одну группу тогда и только тогда, когда у них Совпадает значение поля Начальник. Затем предложение SELECT применяется к каждой группе. поскольку в каждой группе только одно значение поля Начальник, то никакой неопределенности Начальник в группе нет. Для каждой группы SELECT выводит Начальник, а также вычисляет и выводит значение МАХ(ПочасоваяСтавка).

В предложении SELECT со встроенными функциями могут встречаться только те столбцы, которые входят в предложение GROUP BY.

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

С предложением GROUP BY можно использовать WHERE, которое выполняется перед GROUP BY. Но можно также применять условия и к группам, созданным с помощью GROUP BY. Это делается при помощи предложения HAVING.

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

SELECT Начальник, MAX(ПочасоваяСтавка) FROM Рабочий

GROUP BY Начальник

HAVING COUNT(*)>1

Разница между WHERE и HAVING состоит в том, что WHERE применяется к строкам, а HAVING – к группам.

Запрос может содержать и WHERE, и HAVING. WHERE выполняется первой.

Встроенные функции могут использоваться только в предложении SELECT или HAVING. Предложение SELECT c встроенной функцией может быть также частью подзапроса.

Пример. У кого из работников почасовая ставка выше среднего?

SELECT Фамилия FROM Рабочий

WHERE ПочасоваяСтавка>

(SELECT AVG(ПочасоваяСтавка) FROM Рабочий)

Это пример некоррелированного запроса.

Пример. У кого из работников почасовая ставка выше средней почасовой ставки среди подчиненных того же начальника?

SELECT А.Фамилия FROM Рабочий А

WHERE А.ПочасоваяСтавка>

(SELECT AVG(В.ПочасоваяСтавка) FROM Рабочий В

WHERE А.Начальник=В.Начальник)

Операции реляционной алгебры.

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

Пример. Пусть есть Т1 -таблица одного вида деталей, имеющих один набор характеристик, и Т2 – таблица другого вида деталей, имеющих другой вид характеристик, и пусть обе таблицы имеют поля с названием деталей и их стоимостью. Получить список всех выпускаемых деталей.

SELECT Название, Стоимость FROM T1

UNION

SELECT Название, Стоимость FROM T2

В результирующей таблице не содержится дублей строк, даже если они относятся к разным таблицам. Чтобы сохранить дубли, используется сочетание UNION ALL.

Пример. Выявить детали с одинаковыми названиями в разных таблицах.

SELECT Название FROM T1

INTERSECT

SELECT Название FROM T2

Аналогично объединению дубли удаляются. Иначе используется ALL.

Пример.Пусть нужно найти детали, не встречающиеся во второй таблице.

SELECT Название FROM T1

EXCEPT

SELECT Название FROM T2

Операция соединения выполняется следующим образом:

Пример. Показать рабочих, которые назначены на работу с 10.05.00

SELECT Фамилия

FROM Рабочий JOIN Назначение ON Рабочий.ИдРаб=Назначение.ИдРабочего

WHERE ДатаНачала=’10.05.00’

Возможно левое и правое объединение

Пример. Показать рабочих, не назначенных ни на одно здание.

SELECT Фамилия

FROM Рабочий LEFT JOIN Назначение ON Рабочий.ИдРаб=Назначение.ИдРабочего

WHERE Назначение.ИдРаб IS NULL

Представления данных

До сих пор мы определяли базовые таблицы. Части базовых таблиц или выведенная из них информация может быть определена в представлениях, которые также определяются в БД.

Представление данных – это окно. через которое видна часть БД. представления данных полезны для поддержания конфеденциальности путем ограничения доступа к определенным частям БД и для упрощения выполнения часто используемых запросов.

Синтаксис оператора создания представления

CREATE VIEW ИмяПредставления [(Список столбцов)]

AS запрос SELECT

[WITH CHECK OPTION]

Если список столбцов не указан, то представление наследует имена столбцов из SELECT.

Пример. Создается представление

CREATE VIEW Сотрудник

AS SELECT ИдРаб, Фамилия, Специальность FROM Рабочий.

Система на самом деле не создает значения данных для Сотрудник до тех пор, пока к нему не обращаются. В этот момент выполняется запрос SELECT? Таким образом, данные представления динамически обновляются.

Пусть часто нужна информация об электриках и их назначениях.

Создается представление Электрики

CREATE VIEW Электрики

AS SELECT Фамилия, ИдЗд, ДатаНачала

FROM Рабочий JOIN Назначение ON Рабочий.ИдРаб=Назначение.ИдРаб

WHERE Специальность=’Электрик’

В запросах можно использовать представления данных.

Пример. Какие электрики работают на здании 345?

SELECT Фамилия FROM Электрики WHERE ИдЗд=345.

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

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

CREATE VIEW МаксимальнаяСтавка (ИдНач, МаксСтавка)

AS SELECTНачальник, МАХ(ПочасоваяСтавка)

FROM Рабочий

GROUP BY Начальник

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

• ⇐ Назад
• 12