Модуль1_Организация и управление БД

v[Название лаборатории] = t[Название] задает условие правильного сцепления (связывания) значений переменных – кортежей.

x[1] = v [Название специальности] x[2] = t[Изучаемый предмет] определяет набор полей в составе переменной-кортежа x, возвращающей результат запроса. Результат представляет собой бинарное отношение.

Аналогичным образом может быть записано выражение запроса для формирования списка из Фамилии И.О. студентов, изучающих предмет, называемый «Базы данных». В этом запросе необходимо соединить строки всех трех отношений (1) и, накладывая ограничения на сцепленные кортежи одного из них (ЛАБОРАТОРИЯ), извлечь данные из другого (СТУДЕНТ).

{x | ( u) ( v) ( t) (СТУДЕНТ (u) (ЛАБОРАТОРИИ СПЕЦИАЛЬНОСТЕЙ (v)

ЛАБОРАТОРИЯ (t) u[Специальность] = v[Название специальности] v[Название лаборатории] = t[Название]

t[Изучаемый предмет] = «Базы данных» x[1] = u[Фамилия И.О.])}.

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

-используемые отношения: СТУДЕНТ(u) (ЛАБОРАТОРИИ СПЕЦИАЛЬНОСТЕЙ (v)

ЛАБОРАТОРИЯ (t),

-условие сцепления кортежей используемых отношений: u[Специальность] =

v[Название специальности] v[Название лаборатории] = t[Название],

-условие фильтрации сцепленных кортежей: t[Изучаемый предмет] = «Базы данных»,

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

x[1] = u[Фамилия И.О.].

Именно эти элементы являются необходимыми компонентами операторов любых языков запросов к РБД.

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

52

РА в выражение РИ и наоборот. Как РА, так и РИ, обеспечивая реляционную полноту запросов, не предусматривают вычислений и другую обработку возвращаемых запросом данных. Такие возможности заложены в реализациях языков запросов конкретных систем управления базами данных, в том числе и операторе SELECT структурного языка запросов SQL.

Кроме запросов в состав языков манипулирования реляционными данными (DML) включены операторы добавления, удаления и изменения кортежей в отношениях. Их конструкции позволяют одним оператором вносить изменения только в одно отношение. Объектом доступа в операторе включения (INSERT) и удаления (DELETE) является кортеж или несколько полных кортежей отношения. Оператором модификации данных (UPDATE) могут быть изменены значения в определенных кортежах и определенных атрибутах одного отношения. Перечисленные операторы DML составляют основу языка манипулирования данными в SQL. Поскольку добавление и удаление данных возможно только полными кортежами, структуры используемых отношений определяют возможные состояния БД. Вопросы проектирования структуры БД, удовлетворяющей требованиям обработки данных, рассматриваются в следующей главе.

Лекция 8. Структурный язык запросов SQL. Общая структура оператора Select

Запросы к базе данных

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

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

53

соединения и фильтрации их строк. В первом приближении оператор SELECT имеет вид:

SELECT	ALL		<список столбцов результата >
DISTINCT

[INTO <имя таблицы, сохраняющей результат запроса>] FROM < список используемых таблиц базы>

[WHERE <условие отбора строк>]

[GROUP BY [ALL]< список полей для группировки строк >] [HAVING < критерий для отбора групп>]

	[ORDER BY<	список столбцов, по значениям которых	> [	ASC	]]
		упорядочиваются строки результата
				DESC

Общая структура оператора SELECT и назначение его основных параметров показано на рис. 8.1. Первый параметр (<список столбцов, помещаемых в результат >) задает столбцы, а другие параметры (FROM . . . , WHERE . . . и др.) определяют строки создаваемой таблицы.

столбцы результата				таблицы БД и
			FROM<	способ соединения	>	условие отбора	>
		с1, с2,…,с3
SELECT
				их строк		WHERE< строк таблиц

	с1	с2	с3	Результат запроса
				с1	с2 с3

Таблицы, используемые в запросе

Рис.8.1. Структура и назначение параметров оператора SELECT

Параметры оператора SELECT имеют следующее назначение.

54

Опция ALL требует вывести все найденные строки; DISTINCT приводит к исключению из результата дубликатов строк. По умолчанию выводятся все строки.

Параметр <список столбцов результата> определяет столбцы таблицы, создаваемой запросом.

Элементы в списке столбцов разделяются запятыми. Отдельным элементом списка может быть:

1)символ * (звездочка), который требует вывода всех столбцов таблиц, участвующих в запросе. Источником сведений для списка столбцов служат структуры таблиц, указанных в параметре FROM данного запроса;

2)<имя или алиас таблицы>.* – требует вывода значений из всех столбцов заданной таблицы. Алиас – новое имя (псевдоним), которое может быть назначено таблице в параметре FROM данного оператора;

3)<имя столбца таблицы, участвующей в запросе> – выводит значение из указанного столбца. Если исходные таблицы имеют столбцы с совпадающими именами, то имени такого столбца в операторе должно предшествовать имя таблицы в виде: <имя таблицы>.<имя столбца>;

4)<выражение> [[AS ] <новое имя столбца>] или

<новое имя столбца>:<выражение> или

<имя столбца> = <выражение> – выполняет вычисления значения заданного выражения для каждой строки и выводит его в отдельном столбце под указанным именем. В выражении могут использоваться имена столбцов, константы, переменные, скалярные функции, связанные знаками операций, соответствующими типу вычисляемого выражения. Например, два элемента списка, заданные выражениями: [цена] * [количество] AS [Стоимость], ‘руб.’, создают в результате запроса два столбца. В первом столбце с названием Стоимость будет представлена стоимость товара

55

(произведение цены на количество), во втором (без названия) во всех строках выводится значение «руб.».

Оператор

Select[цена]*[количество]AS[Стоимость], ‘руб.’AS[Единица изм.] From...

вернет таблицу следующего вида:

	Таблица 8.1
Стоимость	Единица изм.
534	руб.
. . . . .	руб.

Если требуется вывести все столбцы из таблиц, участвующих в запросе, то их список задается символом *. Отсутствие параметра WHERE приводит к выводу всех строк. Например, оператор SELECT * FROM authors выводит все сведения (все столбцы и все строки) из таблицы authors.

Следующий параметр INTO < имя таблицы, сохраняющей результат запроса > сохраняет результат запроса в новой таблице БД. Таблицы, заданной в параметре INTO, не должно быть в БД. При отсутствии в запросе параметра INTO результат возвращается приложению, направившему запрос без сохранения в БД. Для применения параметра INTO в MS SQL Server требуется специальное разрешение, задаваемое опцией БД ‘select into/ bulkcopy’.

Параметр FROM < список используемых таблиц базы > определяет источники данных, используемые для извлечения информации.

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

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

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

<таблица> [, . . . . . .] ;

56

Оператор SELECT * FROM authors, titleauthor, titles заставит сцепить каждую строку таблицы authors с каждой строкой таблицы titleauthor, а затем их с каждой строкой таблицы titles и вывести все столбцы этих трех таблиц; 2) заданием таблиц одновременно с указанием способа сцепления их строк:

• < таблица > [CROSS JOIN < таблица > . . . . . . .]

Данный вариант эквивалентен предыдущему и выполняет сцепление каждой строки первой таблицы с каждой строкой второй и т.д. ;

• < таблица 1> [INNER JOIN< таблица 2> ON<условие соединения строк>] Здесь ключевыми словами INNER JOIN задается внутреннее соединение строк, приводящее к сцеплению только тех строк первой таблицы с теми строками второй, для которых истинно <условие соединения строк>. Условие соединения задается логическим выражением, в котором участвуют поля соединяемых таблиц.

Пример внутреннего соединения

Select * From Titles INNER JOIN Sales on Titles.Title_id = Sales .Title_id

Запрос выведет все сведения о книгах и их продажах, но только для книг , по которым существовали продажи

LEFT

•< таблица 1> [RIGHT [OUTER] JOIN < таблица 2>

FULL

ON <условие соединения строк> . . . .]

LEFT [OUTER] JOIN − левое внешнее соединение. Строки, соединенные по правилу внутреннего соединения, дополняются строками первой (левой) таблицы, не соединяющимися со строками второй таблицы, т.е. не имеющего ни одной строки во второй таблице, для которой истинно <условие соединения строк>. При этом в полях дополняющих строк из правой таблицы выводятся значения NULL. Таким образом, при левом соединении все строки из левой таблицы обязательно включаются в результат. Те из них, которым по условию соединения не находятся

57

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

Пример левого внешнего соединения

Select * From Titles LEFT JOIN Sales on

Titles.Title_id = Sales .Title_id

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

RIGHT JOIN − правое внешнее соединение, выполняется аналогично левому соединению, только таблицы меняются ролями. В результат запроса включаются строки внутреннего соединения, дополненные строками правой таблицы, не соединившиеся по условию со строками левой таблицы.

Пример правого внешнего соединения

Select * From Sales RIGHT JOIN Titles on Titles.Title_id = Sales .Title_id

Запрос выведет все сведения о книгах и их продажах, включая и те, по которым не было продаж

FULL JOIN − полное внешнее соединение, создает соединенные строки, являющиеся объединением результатов правого и левого внешнего соединения.

В качестве исходной таблицы в параметре FROM могут использоваться таблицы и представления БД, подзапросы к БД – внутренний оператор SELECT и специальные функции, создающие наборы строк. Синтаксис определения табличного источника в параметре FROM имеет следующие варианты записи:

−<имя таблицы> [[AS] <алиас>]. Здесь алиас – новое имя таблицы,

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

58

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

−другой тип табличного источника – подзапрос – записывается следующим образом: <подзапрос> [AS] <алиас> [(<список из имен столбцов>)]. Здесь <подзапрос> это отдельный оператор SELECT, который создает на время выполнения запроса динамическую таблицу с именем, определяемым алиасом. Опция <список из имен столбцов> используется в подзапросе, если его столбцы в списке вывода определяются выражениями. Например, SELECT a1.* FROM (SELECT au_lname, au_fname, phone FROM authors) AS a1. Здесь а1 – псевдоним подзапроса. Данный запрос просто возвратит результат, сформированный подзапросом;

−<функция набора строк> [[AS] <алиас>].

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

Лекция 9. Опции фильтрации, группировки и сортировки Select. Операторы включения, удаления и изменения данных в РБД

Параметр WHERE

Следующий параметр оператора SELECT − параметр WHERE – содержит критерий, по которому фильтруются строки, созданные параметром FROM. Критерий для отбора строк задается логическим выражением, построенным из отдельных условий (высказываний), соединяемых логическими операциями

NOT, AND и OR.

Используются следующие выражения для логических условий: 1. <выражение 1> <операция сравнения> <выражение 2>

59

<выражение 1> и <выражение 2> могут быть любых, но одинаковых типов, для которых определены операции сравнения из множества { =, >, !>, <, !<, >=, <=, <>, != }. Условие принимает значение истина, если выполняется сравнение для значений операндов, заданных <выражением 1> и <выражением 2>.

2.<выражение> [NOT] BETWEEN <выражение 1> AND <выражение 2>

Данное условие истинно (при отсутствии NOT), если значение, вычисленное для первого параметра (<выражение>), попадает в отрезок, образованный значениями параметров <выражение 1> и <выражение 2>. Все выражения должны принадлежать одному типу. NOT инвертирует результат операции.

3.<выражение> LIKE ‘<шаблон для проверки>’. В MS SQL Server

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

•* (для ACCESS) или % (для MS SQL Server) – любая последовательность символов. Например, шаблонам «ЗАО*» и ‘ЗАО%’ соответствуют все строки, начинающиеся на ЗАО;

•? (для ACCESS) или _ (подчеркивание для MS SQL Server) – разрешает любой единственный символ в позиции, занятой данным символом;

•[<символ>-< символ>] – любой символ из заданного диапазона символов используемой кодовой таблицы. Здесь знаки ‘[‘ и ‘]’ являются элементом синтаксиса, например, запись [d-f] означает любой символ из множества

{d, e, f};

60

•[^<символ>-< символ>] – инвертирование диапазона символов (только для MS SQL Server). Условие истинно, если в позиции, определяемой заданным шаблоном, стоит любой символ кроме символов приведенного диапазона.

4.< строковое выражение > [NOT] IN (<список строковых констант>). При отсутствии NOT условие истинно, если значение выражения хотя бы однократно встречается в заданном множестве констант. Например, WHERE color IN (‘Красный’, ‘Желтый’, ‘Зеленый’).

5.<строковое выражение> [NOT] IN (<подзапрос>)

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

6. < выражение > IS	NULL	проверяет, имеет ли выражение какое-либо
	NOT NULL

значение. Данное условие обычно используется для проверки, присутствует (введено) ли какое-либо значение в поле таблицы. Проверка отсутствия данных путем сравнения ( ‘=’ ) со значением NULL вместо операции IS в общем случае неверна.

7.EXISTS (<подзапрос>)

Проверяет факт наличия результата подзапроса. Принимает значение истина, если подзапрос возвращает хотя бы одну строку. Поскольку данные подзапроса значения не имеют, в списке столбцов подзапроса обычно используют символ * в следующем виде: SELECT * FROM … .

8.<выражение> <операция сравнения> ANYALL (<подзапрос>)

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

61