ПЛЕЩ. Учебное пособие содержит

• Название: Учебное пособие содержит
• Анкор: ПЛЕЩ.docx
• Дата: 29.06.2018
• Размер: 3.78 Mb.
• Имя файла: ПЛЕЩ.docx
• Тип: Учебное пособие; #20888
• страница: 14 из 20

1.8.2. Типы данных SQL

Приведем описание основных типов данных.

CHARACTER(n) или CHAR(n) – символьные строки постоянной длины в n символов. При задании данного типа под каждое значение всегда отводится n символов, и если реальное значение занимает менее, чем n символов, то СУБД автоматически дополняет недостающие символы пробелами.

NUMERIC[(n,m)] – точные числа, здесь n – общее количество цифр в числе, m – количество цифр слева от десятичной точки.

DECIMAL[(n,m)] – точные числа, здесь n – общее количество цифр в числе, m – количество цифр слева от десятичной точки.

DEC[(n,m)] - то же, что и DECIMAL[(n,m)].

INTEGER или INT – целые числа. В большинстве реализаций тип данных INTEGER соответствует целым числам, хранимым в четырех байтах, a SMALLINT – соответствует целым числам, хранимым в двух байтах. Выбор одного из этих типов определяется размером числа.

FLOAT[(n)] – числа большой точности, хранимые в форме с плавающей точкой. Здесь n – число байтов, резервируемое под хранение одного числа. Диапазон чисел определяется конкретной реализацией.

REAL – вещественный тип чисел, который соответствует числам с плавающей точкой, меньшей точности, чем FLOAT.

DOUBLE PRECISION специфицирует тип данных с определенной в реализации точностью большей, чем определенная в реализации точность для REAL.

VARCHAR(n) – строки символов переменной длины.

NCHAR(N) – строки локализованных символов постоянной длины.

NCHAR VARYING(n) – строки локализованных символов переменной длины.

BIT(n) – строка битов постоянной длины.

BIT VARYING(n) – строка битов переменной длины.

DATE – календарная дата.

ТIMESТАМР(точность) – дата и время.

INTERVAL – временной интервал.

Большинство коммерческих СУБД поддерживают еще дополнительные типы данных, которые не специфицированы в стандарте. Так, например, практически все СУБД в том или ином виде поддерживают тип данных для представления неструктурированного текста большого объема. Этот тип аналогичен типу MEMO в настольных СУБД. Называются эти типы по-разному, например в ORACLE этот тип называется LONG, в DB2 - LONG VARCHAR, в SYBASE и MS SQL Server - TEXT.

Специфика реализации отдельных типов данных серьезным образом влияет на результаты запросов к БД. Особенно это касается реализации типов данных DATE и TIMESTAMP. Поэтому при переносе приложений на разных платформах они могут работать по-разному, и одной из причин может быть различие в интерпретации типов данных.

Для числовых типов данных определены константы в виде последовательности цифр с необязательным заданием знака числа и десятичной точкой. То есть правильными будут константы:

213-314 612.716 + 551.702

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

2.9Е-4 -134.235Е7 0.54267Е18

Строковые константы должны быть заключены в одинарные кавычки:

'Крылов Ю.Д.' 'Санкт-Петербург'

В некоторых реализациях, например MS SQL Server и Informix, допустимы двойные кавычки в строковых константах:

"Москва" "New York"

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

Константы даты, времени и временного интервала в реляционных СУБД представляются в виде строковых констант. Форматы этих констант отличаются в различных СУБД. Кроме того, формат представления даты различен в разных странах. В большинстве СУБД реализованы способы настройки форматов представления дат или специальные функции преобразования форматов дат, как сделано, например, в СУБД ORACLE.

Приведем примеры констант в MS SQL Server:

March 15, 1999 Маг 15 1999 3/15/1999 3-15-99 1999 MAR 15

В СУБД ORACLE та же константа запишется как 15-MAR-99

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

В операторах SQL могут использоваться выражения, которые строятся по стандартным правилам применения знаков арифметических операций сложения (+), вычитания (-), умножения (*) и деления (/). Однако в ряде СУБД операция деления (/) интерпретируется как деление нацело. Имееется возможность выполнения операций сложения и вычитания над датами. В большинстве СУБД также определена операция конкатенации над строковыми данными, обозначается она по-разному. Так, например, для DB2 операция конкатенации обозначается двойной вертикальной чертой, в MS SQL Server – знаком сложения (+)

Имеются стандартные встроенные функций:

BIT_LENGTH(cтpoкa) – количество битов в строке;

САSТ(значение AS тип данных) – значение, преобразованное в заданный тип данных;

CHAR_LENGTH(cтpoкa) – длина строки символов;

CONVERT(cтpoкa USING функция) – строка, преобразованная в соответствии с указанной функцией;

CURRENT_DATE - текущая дата;

CURRENT_TIME(точность) – текущее время с указанной точностью;

CURRENT_TIMESTAMP(точность) – текущие дата и время с указанной точностью;

LOWER(cтpокa) – строка, преобразованная к верхнему регистру;

OCTED_LENGTH(строка) – число байтов в строке символов;

POSITION( первая строка IN вторая строка) – позиция, с которой начинается вхождение первой строки во вторую;

SUBSTRING(cтpoкa FROM n FOR длина) – часть строки, начинающаяся с n-го символа и имеющая указанную длину;

TRANSLATE(строка USING функция) – строка, преобразованная с использованием указанной функции;

TRIM(BOTH символ FROM строка) – строка, у которой удалены все первые и последние символы;

TRIM(LEADING символ FROM строка ) – строка, в которой удалены все первые указанные символы;

TRIM(TRAILING символ FROM строка) – строка, в которой удалены последние указанные символы;

UPPER(строка) – строка, преобразованная к верхнему регистру.

1.8.3. Оператор выбора данных SELECT

1.8.3.1. Назначение и синтаксис оператора

Оператор SELECT реализует все операции реляционной алгебры. В результате его выполнения формируется расчетная таблица, которая может включать данные из других таблиц или представлений (или запросов в СУБД Access) и расчетные поля.

Синтаксис оператора SELECT имеет следующий вид:

SELECT [ALL | DISTINCT] «список полей>|*)

FROM < перечень исходных отношений (таблиц) [<тип связи>]>

[WHERE <условие выборки записей или соединения>]

[GROUP BY <список полей группировки >]

[HAVING < условие выборки групп>]

[ORDER BY <список полей, по которым упорядочить вывод>]

Рассмотрим более детально основные фразы оператора SELECT.

Ключевое слово SELECT сообщает СУБД, что эта команда – запрос. Все запросы начинаются этим словом с последующим пробелом. Затем следует список перечисленных через запятую столбцов, которые выбираются запросом из таблиц, или символ '*' (звездочка) для выбора всей строки или выражения для расчетных полей. Любые столбцы, не перечисленные здесь, не будут включены в результирующее отношение, соответствующее выполнению команды.

Ключевое слово ALL (подразумевается по умолчанию) означает, что в результирующий набор строк включаются все строки, удовлетворяющие условиям запроса.

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

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

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

Для связывания таблиц используется фраза JOIN. Тип связи задается словами: LEFT/RIGHT (в запрос входят все записи из таблицы, стоящей в запросе слева/справа), INNER (входят только записи с совпадающими ключами связи).

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

Select Stroiki.Ns, Stroiki.Ds, Stroiki.Ss, Stroiki.Fs, Stroiki.M, Stroiki.Em, Podrjdhiki.Np, Zakazhiki.Nz FROM “stroiki.DB“ Stroiki

Inner Join “zakazhiki.db“ Zakazhiki ON (Stroiki.Kz = Zakazhiki.Kz)

Inner Join “podrjdhiki.DB“ Podrjdhiki ON (Stroiki.Kp = Podrjdhiki.Kp)

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

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

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

В выражении условий фразы WHERE могут быть использованы следующие операции:

Сравнения { =, <>, >,<, >=,<= }, которые имеют традиционный смысл.

Between A and В – принимает значения между А и В. Предикат истинен, когда сравниваемое значение попадает в заданный диапазон, включая границы диапазона.

Not Between A and В, который истинен тогда, когда сравниваемое значение не попадает в заданный интервал, включая его границы.

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

NOT IN (множество), который истинен тогда, когда сравниваемое значение не входит в заданное множество.

LIKE и NOT LIKE. Предикат LIKE требует задания шаблона, с которым сравнивается заданное значение, предикат истинен, если сравниваемое значение соответствует шаблону, и ложен в противном случае. Предикат NOT LIKE имеет противоположный смысл.

В шаблон могут быть включены специальные символы:

символ подчеркивания (_) – для обозначения любого одиночного символа;

символ процента (%) – для обозначения любой произвольной последовательности символов;

[nk]% – произвольная последовательность символов в интервале от n до k;

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

IS NULL – сравнение с неопределенным значением IS NULL.

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

<имяатрибута>IS NULL и <имяатрибута> IS NOT NULL.

Если в данном в данной строке указанный атрибут имеет неопределенное значение, то условие IS NULL принимает значение «Истина» (TRUE), а предикат IS NOT NULL – «Ложь» (FALSE), в противном случае условие IS NULL принимает значение «Ложь», а предикат IS NOT NULL принимает значение «Истина».

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

Таблица 1.8.3.1

Таблица истинности логических операций

А	В	Not A	А And В	A Or B
TRUE	TRUE	FALSE	TRUE	TRUE
TRUE	FALSE	FALSE	FALSE	TRUE
TRUE	Null	FALSE	Null	TRUE
FALSE	TRUE	TRUE	FALSE	TRUE
FALSE	FALSE	TRUE	FALSE	FALSE
FALSE	Null	TRUE	FALSE	Null
Null	TRUE	Null	Null	TRUE
Null	FALSE	Null	FALSE	Null
Null	Null	Null	Null	Null

 

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

EXIST и несуществования NOT EXIST – относятся к встроенным подзапросам.

В фразе GROUP BY<колонка>[,<колонка>...] задаются колонки (поля), по которым производится группирование выходных данных. Все записи таблицы, для которых значения колонок совпадают, отображаются в выборке единственной строкой. Группирование удобно для получения некоторых сводных характеристик (суммы, число записей, среднее) группы.

В выражении могут использоваться собственные агрегатные функции, действующие “по вертикали”: среднее значение в группе (AVG), минимальное (MIN), максимальное (MAX), сумма (SUM), Количество строк или непустых значений полей, которые выбрал запрос (COUNT). Эта функция может иметь в качестве аргумента звездочку (COUNT (*)), что означает подсчет всех записей, попавших в выборку.

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

С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями МАХ и MIN и будут обрабатывать в алфавитном порядке. Нельзя использовать агрегатные функции в предложении WHERE, потому что условия оцениваются в терминах одиночной строки, а агрегатные функции – в терминах групп строк.

В фразе HAVING <условие отбора группы> задаются критерий отбора сформированных в процессе выборки групп.

В условие выборки фразы HAVING, прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY. Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX, вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING: если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.

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

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

SELECT podr, Count(*),Min(zarpl), Avg(zarpl), Max(zarpl), Sum(zarpl)

From Sotrudniki Group By podr Order By podr

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

SELECT * FROM Rl, R2

соответствует декартову произведению таблиц R1 и R2. Выражение

SELECT Rl.A, R2.B FROM Rl. R2

соответствует проекции декартова произведения двух таблиц на два столбца А из таблицы R1 и В из таблицы R2, при этом дубликаты всех строк сохранены, в отличие от операции проектирования в реляционной алгебре, где при проектировании по умолчанию все дубликаты кортежей уничтожаются. 

В следующих примерах из [18, 19] используются базы данных:

1) «Поставки» с таблицами (ключевые поля подчеркнуты):

S – поставщики (Номер_поставщика, Фамилия, состояние, Город);

Р – детали (Номер_детали, Название, цвет, Вес, Город).;

SP – поставки деталей (Номер_поставщика, Номер_детали, Количество);

J – изделия (Номер_изделия, Название, Город);

SPJ поставка деталей для изделий – (Номер_поставщика, Номер_детали, Номер_изделия, Количество).

2) «Сессия» из [19]:

R₁ – результаты сессия (ФИО, Дисциплина, Оценка);

R₂ – список студентов (ФИО, Группа);

R₃ – список учебных дисциплин по группам (Группы, Дисциплина);

R₄ – результаты лабораторных работ (ФИО, Дисциплина, Номер_лаб_раб, Оценка).

1.8.3.2. Объединение таблиц

В первоначальном SQL при объединении отношений (установки связи между отношениями) использовались только условия, задаваемые в части WHERE оператора SELECT. В современном SQL синтаксис фразы FROM выглядит следующим образом:

FROM <список исходных таблиц> |

< выражение естественного объединения > |

< выражение объединения >

< выражение перекрестного объединения > |

< выражение запроса на объединение >

<список исходных таблиц> это есть

<имя_таблицы_1> [ имя синонима таблицы_1] [ ...]

[,<имя_таблицы_n>[ <имя синонима таблицы_n> ] ]

Выражение естественного объединениям:

<имя_таблицы_1> NATURAL { INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]} JOIN <имя_таблицы_2>

Выражение объединениям:

<имя_таблицы_1>

{ INNER |FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]}

JOIN {ON условие [USING (список столбцов)]} <имя_таблицы_2>

Выражение перекрестного объединениям:

<имя_таблицы_1> CROSS JOIN <имя_таблицы_2>

Выражение запроса на объединением:

<имя_таблицы_1> UNION JOIN <имя_таблицы_2>

В этих определениях:

INNER – означает внутреннее объединение.

LEFT – левое объединение, то есть в результат входят все строки таблицы 1, а части результирующих кортежей, для которых не было соответствующих значений в таблице 2, дополняются значениями NULL (неопределено).

RIGHT – правое внешнее объединение, и в отличие от левого объединения в этом случае в результирующее отношение включаются все строки таблицы 2, а недостающие части из таблицы 1 дополняются неопределенными значениями.

FULL – определяет полное внешнее объединение: выполняются и правое и левое внешние объединения и в результирующее отношение включаются все строки из таблицы 1, дополненные неопределенными значениями, и все строки из таблицы 2, также дополненные неопределёнными значениями.

OUTER – означает внешнее, но если заданы ключевые слова FULL, LEFT, RIGHT, то объединение всегда считается внешним.

Рассмотрим примеры выполнения внешних объединений на примере БД «Сессия».

Пример. Создать отношение, в котором будут стоять все оценки, полученные всеми студентами по всем экзаменам, которые они должны были сдавать. Если студент не сдавал данного экзамена, то вместо оценки у него будет стоять неопределенное значение. Для этого выполним последовательно естественное внутреннее объединение таблиц R2 и R3 по атрибуту Группа, а полученное отношение соединим левым внешним естественным объединением с таблицей R1, используя столбцы ФИО и Дисциплина.

SELECT R1.ФИО, R1.Дисциплина. R1.Оценка

FROM (R2 NATURAL INNER JOIN R3)

LEFT JOIN R1 USING ( ФИО. Дисциплина)

Пример. Возьмем БД «Библиотека». Она состоит из трех отношений, имена атрибутов здесь набраны латинскими буквами, что является необходимым в большинстве коммерческих СУБД.

Tаблица BOOKS описывает все книги, присутствующие в библиотеке, она имеет следующие атрибуты:

ISBN – уникальный шифр книги;

TITL – название книги;

AUTOR – фамилия автора;

Таблица READER хранит сведения обо всех читателях библиотеки, и она содержит следующие атрибуты:

NUM_READER – уникальный номер читательского билета;

NAME_READER – фамилию и инициалы читателя;

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

INV – уникальный инвентарный номер экземпляра книги;

ISBN – шифр книги, который определяет, какая это книга, и ссылается на сведения из первой таблицы;

NUM_READER – номер читательского билета, если книга выдана читателю, и Null в противном случае;

Определим перечень книг у каждого читателя; если у читателя нет книг, то номер экземпляра книги равен NULL. Для выполнения этого поиска нам надо использовать левое внешнее объединение, то есть мы берем все строки из таблицы READER и соединяем со строками из таблицы EXEMPLARE, если во второй таблице нет строки с соответствующим номером читательского билета, то в строке результирующего отношения атрибут EXEMPLARE.INV будет иметь неопределенное значение NULL:

SELECT READER.NAME_READER, EXEMPLARE.INV

FROM READER RIGHT JOIN EXEMPLARE ON READER.NUM_READER=EXEMPLARE.NUM_READER

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

SELECT *

FROM (BOOKS LEFT JOIN EXEMPLARE)

LEFT JOIN (READER NATURAL JOIN EXEMPLARE)

USING (ISBN)

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

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

Рассмотри выражение запроса на объединение.

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

Пример. Выдать номера деталей, которые имеют вес более 16 фунтов либо поставляются поставщиком S2.

SELECT НОМЕР_ДЕТАЛИ FROM P WHERE BEC>16
UNION SELECT НОМЕР_ДЕТАЛИ FROM SP WHERE НОМЕР_ПОСТАВЩИКА ='S2';

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

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

Когда строковая константа выступает в качестве элемента, подлежащего выборке, считается, что она имеет тип VARCHAR и длину, равную числу литер в константе, и допускаются неопределенные значения.

Пример. Выдать номер детали, вес в граммах, цвет и максимальный объем поставки для всех красных и голубых деталей, таких, что общий объем их поставки больше, чем 350, исключая при этом из общего объема все такие поставки, для которых количество меньше или равно 200 деталей. Результат упорядочить по убыванию номеров деталей в рамках возрастающих значений этого максимального объема поставки.

SELECT Р.НОМЕР_ДЕТАЛИ, 'вес в граммах = ', Р. ВЕС*454, Р.ЦВЕТ *
'максимальный объем поставки = ', MAX (SP.КОЛИЧЕСТВО)
FROM P, SP
WHERE Р.НОМЕР_ДЕТАЛИ = SP.НОМЕР_ДЕТАЛИ AND

P. ЦВЕТ IN ('Красный', 'Голубой') AND SP.КОЛИЧЕСТВО > 200
GROUP BY Р. НОМЕР_ДЕТАЛИ, Р. ВЕС, Р. ЦВЕТ
HAVING SUM (КОЛИЧЕСТВО) > 350
ORDER BY 6, P. НОМЕР_ДЕТАЛИ DESC;

1.8.3.3. Вложенные и коррелированные запросы

С помощью SQL можно вкладывать запросы внутрь друг друга. Обычно внутренний (вложенный) запрос генерирует значение, которое проверяется в условии внешнего запроса (в фразе WHERE или HAVING), определяющего, верно оно или нет. Совместно с подзапросом можно использовать предикат EXISTS, который возвращает истину, если вывод подзапроса не пуст. Подзапрос заключается в круглые скобки.

Пример. Вывести крупные стройки со сметой выше средней по стройкам:

SELECT * FROM Stroiki WHERE Ss > (SELECT AVG(Ss) FROM Stroiki).

Пример. Вывести из базы «Сессия» список тех, кто сдал все положенные экзамены.

SELECT ФИО

FROM R1

WHERE Оценка > 2

GROUP BY ФИО

HAVING COUNT(*) = (SELECT COUNT(*)

FROM R2, R3

WHERE R2.Группа=R3.Группа AND R2.ФИО=R1.ФИО)

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

Особенностью этого запроса является то, что в подзапросе используется поле (R1.ФИО) таблицы R1, которая указана во внешнем запросе. Такой подзапрос называется коррелированным. В этом случае подзапрос выполняется для каждой текущей записи таблицы R1 (что может существенно увеличить время выполнения всего запроса – подза*прос будет выполняться для каждого студента; лучшим решением является оформление подзапроса в форме представления, в котором выводятся число всех экзаменов по группам). Например, если первая запись таблицы R1 содержит значения: Иванов, Информатика и 4, то выполнится подзапрос, в котором будут связаны таблицы R2 и R3 по полю Группа в которой учится Иванов (можно заметить, что связь таблиц R1 и R2 по полю ФИО не допускает полных тезок, лучше связывать по учетным номерам студентов). В результате, определится число экзаменов в группе, в которой учится Иванов и далее, выполнится группировка по Иванову и посчитается число положительных оценок за экзамены и если это число совпадет с числом экзаменов по группе, то фамилия Иванова выведется в результирующую таблицу запроса.

Можно заметить, что фразы FROM и WHERE можно написать более современно, используя фразу установки связи между таблицами – JOIN:

FROM R2, R3 INNER JOIN ON (R2.Группа=R3.Группа)

WHERE R2.ФИО=R1.ФИО

1.8.3.4. Запросы, использующие EXIST, ANY, ALL

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

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

Ключевое слово ALL требует, чтобы предикат сравнения был бы истинен при сравнении со всеми строками подзапроса.

Пример. Вывести список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал.

SЕLЕСТ ФИО

FROM R2 a, R3

WHERE R2.Fpynna=R3.Группа AND Дисциплина = "БД" AND NOT EXISTS

(SELECT ФИО FROM Rl WHERE ФИО=а.ФИО AND Дисциплина = "БД")

Пример. Вывести студентов, которые сдали все экзамены на оценку не ниже чем «хорошо»:

SELECT R1.ФИО FROM R1 WHERE 4 > = ALL

(SELECT R1.Оценка FROM R1 AS R11 WHERE R1.ФИО = R11.ФИО)

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

SELECT R1.ФИО

FROM R1 WHERE R1.Оценка >= ANY

(SELECT R4.Оценка FROM R4

WHERE R1.Дисциплина = R4. Дисциплина AND R1.ФИО = R4.ФИО)

Пример. Выдать фамилии поставщиков, которые поставляют деталь P2.
SELECT ФАМИЛИЯ FROM S WHERE EXISTS
(SELECT * FROM SP WHERE

НОМЕР_ПОСТАВЩИКА=S.НОМЕР_ПОСТАВЩИКА

AND НОМЕР_ДЕТАЛИ = 'P2');

Пример. Выдать фамилии поставщиков, которые не поставляют деталь Р2.

SELECT ФАМИЛИЯ FROM S WHERE NOT EXIST
(SELECT * FROM SP WHERE НОМЕР_ПОСТАВЩИКА=S.НОМЕР_ПОСТАВЩИКА
AND НОМЕР_ДЕТАЛИ = 'Р2');

Пример. Выдать фамилии поставщиков, которые поставляют все детали.

SELECT ФАМИЛИЯ FROM S WHERE NOT EXISTS
(SELECT * FROM P WHERE NOT EXISTS

(SELECT * FROM SP WHERE НОМЕР_ПОСТАВЩИКА=
S.НОМЕР_ПОСТАВЩИКА AND НОМЕР_ДЕТАЛИ= Р.НОМЕР_ДЕТАЛИ));

1.8.3.5. Стандартные функции

В SQL предусматривается ряд специальных стандартных функций:
COUNT (число значений в столбце), SUM (сумма значений по столбцу), AVG (среднее числовое значение в столбце), MAX (самое большое значение в столбце). В общем случае аргументу функции может факультативно предшествовать ключевое слово DISTINCT (различный), указывающее, что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться функция. Для функции COUNT ключевое слово DISTINCT должно быть специфицировано. Специальная функция COUNT (*), для которой использование DISTINCT не допускается, предусмотрена для подсчета всех строк в таблице без исключения каких-либо дубликатов. Если DISTINCT специфицируется, то аргумент должен состоять только из имени столбца. Если DISTINCT не специфицировано, аргумент может представлять собой арифметическое выражение, например ВЕС * 454.

В столбце-аргументе всегда перед применением функции исключаются все неопределенные значения, независимо от того, специфицировано ли DISTINCT, за исключением случая COUNT (*), при котором неопределенные значения обрабатываются точно так же, как и значения, не являющиеся неопределенными. Если оказывается, что аргумент – пустое множество, функция COUNT принимает значение нуль. Все другие функции принимают в этом случае неопределенное значение.

Пример. Выдать общее количество поставщиков.

SELECT COUNT (*) FROM SP

Пример. Выдать общее количество поставщиков, поставляющих в настоящее время детали:

SELECT COUNT (DISTINCT НОМЕР_ПОСТАВЩИКА) FROM SP;

Пример. Выдать количество поставок для детали Р2.
SELECT COUNT (*) FROM SP WHERE НОМЕР_ДЕТАЛИ = 'Р2';

Пример. Выдать общее количество поставляемых деталей Р2.
SELECT SUM (КОЛИЧЕСТВО) FROM SP WHERE НОМЕР_ДЕТАЛИ = 'Р2';

Пример. Выдать номера поставщиков со значением поля СОСТОЯНИЕ меньшим, чем максимальное состояние в таблице S.
SELECT НОМЕР_ПОСТАВЩИКА FROM S
WHERE СОСТОЯНИЕ < (SELECT MAX (СОСТОЯНИЕ) FROM S);

Пример. Выдать номер поставщика, состояние и город для всех поставщиков, у которых состояние больше или равно среднему для их конкретного города.

SELECT НОМЕР_ПОСТАВЩИКА, СОСТОЯНИЕ, ГОРОД
FROM S SX WHERE СОСТОЯНИЕ > =
(SELECT AVQ (СОСТОЯНИЕ) FROM S SY WHERE SY.ГОРОД = SX.ГОРОД);

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

Требуется для каждой поставляемой детали выдать номер этой детали и общий объем поставок. Запрос имеет вид:

SELECT НОМЕР_ДЕТАЛИ, SUM (КОЛИЧЕСТВО) FROM SP
GROUP BY НОМЕР_ДЕТАЛИ ORDER BY НОМЕР-ДЕТАЛИ;

Запрос с группировкой реализуется фразой GROUP BY. В рассматриваемом примере строки таблицы SP группируются таким образом, что в одной группе содержатся все строки для детали Р1, в другой–все строки для детали Р2 и т. д. Далее, к каждой группе перекомпонованной таблицы, а не к каждой строке исходной таблицы применяется фраза SELECT. Каждое выражение во фразе SELECT должно принимать единственное значение для группы, т. е. оно может быть либо самим полем, указанным во фразе GROUP BY, либо арифметическим выражением, включающим это поле, либо константой, либо такой функцией, как SUM, которая оперирует всеми значениями данного поля в группе и сводит эти значения к единственному значению.

Фраза GROUP BY не предполагает ORDER BY (упорядочить по). Чтобы гарантировать упорядочение результата этого примера по номерам деталей, следует специфицировать фразу ORDER BY НОМЕР-ДЕТАЛИ. Если поле, по значениям которого осуществляется группирование, содержит какие-либо неопределенные значения, то каждое из них порождает отдельную группу.

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

SELECT НОМЕР_ДЕТАЛИ, SUM (КОЛИЧЕСТВО)
FROM SP WHERE НОМЕР_ПОСТАВЩИКА= 'S1'
GROUP BY НОМЕР_ДЕТАЛИ;

Строки, не удовлетворяющие фразе WHERE, исключаются до того, как будет осуществляться какое-либо группирование.

Пример. Выдать номера деталей для всех деталей, поставляемых более чем одним поставщиком (тот же пример, что и в 5.2.5).

SELECT НОМЕР_ДЕТАЛИ FROM SP GROUP BY НОМЕР_ДЕТАЛИ
HAVING COUNT (*)>1;

Фраза HAVING используется для того, чтобы исключать группы и она работает только на одном уровне. Невозможно разбить каждую из этих групп на группы более низкого уровня и т. д., а затем применить некоторую стандартную функцию, например SUM или AVG на каждом уровне группирования.

1.8.4. Операторы обновления базы

1.8.4.1. Оператор корректировки данных UPDATE

Оператор UPDATE имеет следующий общий формат:

UPDATE таблица SET поле = выражение [, поле = выражение]…

[WHERE условие];

Все записи в таблице, которые удовлетворяют условию, обновляются в соответствии с присваиваниями «поле = выражение» во фразе SET.

Пример изменения одной записи. Изменить цвет детали Р2 на желтый, увеличить ее вес на 5 и установить значение города «неизвестен» (NULL).

UPDATE P SET ЦВЕТ = 'Желтый', ВЕС = ВЕС + 5, ГОРОД = NULL
WHERE НОМЕР_ДЕТАЛИ = 'Р2';

Пример изменения группы записей. Удвоить состояние всех поставщиков, находящихся в Лондоне.
UPDATE S SET СОСТОЯНИЕ = 2*СОСТОЯНИЕ WHERE ГОРОД = 'Лондон';

Пример изменения с подзапросом. Установить объем поставок равным нулю для всех поставщиков из Лондона.

UPDATE SP SET КОЛИЧЕСТВО = 0 WHERE 'Лондон' =
(SELECT ГОРОД FROM S WHERE S.НОМЕР_ПОСТАВЩИКА =
SP.НОМЕР_ПОСТАВЩИКА);
Пример обновления нескольких таблиц. Изменить номер поставщика S2 на S9.

UPDATE S SET НОМЕР_ПОСТАВЩИКА = 'S9'

WHERE НОМЕР_ПОСТАВЩИКА = 'S2';

UPDATE SP SET НОМЕР_ПОСТАВЩИКА = 'S9'

WHERE НОМЕР_ПОСТАВЩИКА = 'S2';

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

1.8.4.2. Оператор удаления записей DELETE

Оператор DELETE имеет следующий общий формат:

DELETE FROM таблица [WHERE условие];

Удаляются все записи в «таблице», которые удовлетворяют условию.
Пример удаления единственной записи. Удалить поставщика S1.
DELETE FROM S WHERE НОМЕР_ПОСТАВЩИКА = 'S1’.

Пример удаления группы записи. Удалить всех поставщиков из Лондона.
DELETE FROM S WHERE ГОРОД = 'Лондон';

Пример удаления всех записей. Удалить все поставки.

DELETE FROM SP;

Пример удаления с подзапросом. Удалить все поставки для поставщиков из Лондона.

DELETE FROM SP WHERE 'Лондон' =
(SELECT ГОРОД FROM S
WHERE S. НОМЕР_ПОСТАВЩИКА =SP.НОМЕР_ПОСТАВЩИКА);

1.8.4.3. Оператор включения записей INSERT

Оператор INSERT имеет следующий общий формат:

INSERT INTO таблица [(поле [,поле]…)]
VALUES (константа [,константа…);
ИЛИ:
INSERT INTO таблица [(поле [,поле] . . .)] подзапрос;

В первом формате в «таблицу» вставляется строка, имеющая заданные значения для указанных полей, причем 1-я константа в списке констант соответствует i-му полю в списке полей.

Во втором формате вычисляется «подзапрос»; копия результата, представляющего собой, вообще говоря, множество строк, вставляется в «таблицу». При этом i-й столбец этого результата соответствует i-му полю в списке полей. В обоих случаях отсутствие списка полей эквивалентно спецификации списка всех полей в таблице.

Пример вставки единственной записи. Добавить в таблицу Р деталь Р7 (город 'Атенс', вес – 2, название и цвет в настоящее время неизвестны).

INSERT INTO Р (НОМЕР_ДЕТАЛИ, ГОРОД, BEC) VALUES ('Р7', 'Атенс', 2);

Порядок слева – направо, в котором поля указаны в предложении INSERT, не обязательно должен совпадать с порядком слева – направо, в записи таблицы.

Пример вставки единственной записи без указания имен полей.
Добавить деталь Р8 в таблицу Р, при этом: название - 'Звездочка', цвет - 'Розовый', вес – 14, город – 'Ницца'.

INSERT INTO P VALUES ('Р8', 'Звездочка', 'Розовый', 14, 'Ницца');

Отсутствие списка полей эквивалентно спецификации списка всех полей в таблице в порядке слева – направо, как они были определены в записи таблицы.

Пример вставки группы записи. Для каждой поставляемой детали получить ее номер и общий объем поставок, сохранить результат в базе данных.
CREATE TABLE ВРЕМЕННАЯ
(НОМЕР_ДЕТАЛИ CHAR (6), ОБЪЕМ_ПОСТАВКИ INTEGER);
INSERT INTO ВРЕМЕННАЯ (НОМЕР_ДЕТАЛИ, ОБЪЕМ_ПОСТАВКИ)
SELECT НОМЕР_ДЕТАЛИ, SUM (КОЛИЧЕСТВО) FROM SP
GROUP BY НОМЕР_ДЕТАЛИ;

Здесь предложение SELECT выполняется точно так же, как обычно, но результат не возвращается пользователю, а копируется в таблицу ВРЕМЕННАЯ. В конечном счете таблицу ВРЕМЕННАЯ можно будет уничтожить, когда она больше не будет нужна:

DROP TABLE ВРЕМЕННАЯ;

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

1.8.5. Представления

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

Общий синтаксис предложения CREATE VIEW (создать представление):
CREATE VIEW имя–представления [(имя–столбца[ , имя–столбца] . . .)]
AS подзапрос
[WITH CHECK OPTION];

Подзапрос не может включать ни оператора UNION, ни фразы ORDER BY.

Пример.
CREATE VIEW КРАСНЫЕ_ДЕТАЛИ (НОМЕР_ДЕТАЛИ, НАЗВАНИЕ, ВЕС, ГОРОД)
AS SELECT НОМЕР_ДЕТАЛИ, НАЗВАНИЕ, ВЕС, ГОРОД FROM P
WHERE ЦВЕТ = 'Красный';

Пример.
CREATE VIEW PQ (НОМЕР_ДЕТАЛИ, ОБЩЕЕ_КОЛИЧЕСТВО)
AS SELECT НОМЕР_ДЕТАЛИ, SUM (КОЛИЧЕСТВО) FROM SP
GROUP BY НОМЕР_ДЕТАЛИ;

Пример.

CREATE VIEW ПАРЫ_ГОРОДОВ (ГОРОД_ПОСТАВЩИКА,
ГОРОД_ДЕТАЛИ) AS SELECT S.ГОРОД, Р.ГОРОД FROM S, SP, P
WHERE S.НОМЕР_ПОСТАВЩИКА =SP.НОМЕР_ПОСТАВЩИКА
AND SP.HOMEP_ДЕТАЛИ = Р.НОМЕР_ДЕТАЛИ;

Пример.

CREATE VIEW ЛОНДОНСКИЕ_КРАСНЫЕ_ДЕТАЛИ
AS SELECT НОМЕР_ДЕТАЛИ, ВЕС FROM КРАСНЫЕ_ДЕТАЛИ
WHERE ГОРОД = 'Лондон';

Пример.

CREATE VIEW ХОРОШИЕ_ПОСТАВЩИКИ
AS SELECT НОМЕР_ПОСТАВЩИКА, СОСТОЯНИЕ, ГОРОД FROM S
WHERE СОСТОЯНИЕ > 15 WITH CHECK OPTION;

Фраза «WITH CHECK OPTION» (с проверкой) указывает, что для операций UPDATE и INSERT над этим представлением должна осуществляться проверка, которая обеспечивает удовлетворение определяющего представление предиката обновленной или вставляемой строкой (СОСТОЯНИЕ>15).

Предложение DROP VIEW имеет следующий синтаксис:

DROP VIEW имя_представления;

В результате исполнения этого предложения уничтожается специфицированное представление,

Пример. DROP VIEW КРАСНЫЕ_ДЕТАЛИ;

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

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

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

CREATE VIEW RATE

DEPARTMENT. COUNT(*), SUM(SALARY), SUM(PREMIUM), MAX(SALARY), MIN(SALARY),AVERAGE (SALARY), MAX(PREMIUM), MIN(PREMIUM), AVERAGE (PREMIUM) AS SELECT DEPARTMENT, COUNT(*), SUM(SALARY). SUM(PREMIUM). MAX(SALARY),

MIN(SALARY), AVERAGE (SALARY), MAX(PREMIUM), MIN(PREMIUM),

AVERAGE (PREMIUM) FROM EMPLOYEE GROUP BY DEPARTMENT;

Объеденные представления. Часто представления базируются на многотабличных запросах. Такое использование позволяет упростить разработку пользовательского интерфейса, сохранив при этом корректность схемы базы данных.

Пример. Создать представление, которое содержит список читателей-должников с указанием книг, которые у них на руках, и указанных в базе сроков сдачи этих книг.

CREATE VIEW DEBTORS ISBN, TITLE, NUM_READER, NAME, ADRES, HOME_PHON, WORK_PHON, DATA_OUT AS

SELECT ISBN, TITLE, NUM_READER, NAME, ADRES, HOME_PHON, WORK_PHON, DATA_OUT

FROM BOOKS, EXEMPLAR, READERS

WHERE BOOKS.ISBN = EXEMPLAR.ISBN AND

EXEMPLAR.NUM_READER = READERS.NUM_READER AND

EXEMPLAR.PRESENT = FALSE AND

EXEMPLAR.DATA OUT < GetDate();

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

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

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

В запросе должен отсутствовать предикат DISTINCT.

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

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

В предложении WHERE не должен стоять вложенный запрос; в нем могут присутствовать только простые условия поиска.

В запросе не должно присутствовать выражение группировки GROUP BY или HAVING.

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

Рассмотрим преимущества представлений.

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

Они дают возможность различным пользователям по-разному видеть одни и те же данные, возможно, даже в одно и то же время.

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

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

Для скрытых данных автоматически обеспечивается секретность. «Скрытые данные» обозначает здесь данные, невидимые через некоторое заданное представление. Ясно, что такие данные защищены от доступа через это конкретное представление.

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