Нижегородский государственный университет
имени Н. И. Лобачевского
Кафедра информатики и автоматизации научных исследований
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
по курсам «Теория информационных систем» и «Базы данных»
Разделы «Реляционная алгебра» и «Язык SQL»
Нижний Новгород 2005
УДК 519.6
Методические указания по курсам «Теория информационных систем» и «Базы данных». Разделы «Реляционная алгебра» и «Язык SQL»
/Сост. Фомина И.А., Исаев С.А. - Нижний Новгород: Нижегородский государственный университет, 2005.
Материал предназначен для студентов специальности “Прикладная информатика” факультета ВМК (формы обучения дневная, вечерняя, заочная). Он также может быть интересен всем, кто в силу научных, учебных и практических целей заинтересован в рассмотрении абстрактной трактовки запросов в рамках реляционной модели и изучении языка запросов SQL. Данные методические указания могут быть использованы как помощь при изучении теоретического материала и при выполнении практических и лабораторных работ в терминал - классе.
Составители - канд. техн. наук, доцент Фомина И.А.
канд. техн. наук, ассистент Исаев С.А.
Рецензент - канд. техн.-наук, доцент Карпенко С.Н.
Нижегородский государственный университет имени Н.И.Лобачевского 2005г.
Часть1. Основы реляционных баз данных
Впервые термин "реляционная модель данных" появился в статье сотрудника фирмы IBM д-ра Кодда (Codd E.F., A Relational Model of Data for Large Shared Data Banks. CACM 13: 6, June 1970). Будучи математиком по образованию Кодд предложил использовать для обработки данных аппарат теории множеств (объединение, пересечение, разность, декартово произведение). Он показал, что любое представление данных сводится к совокупности двумерных таблиц особого вида, известного в математике как
отношение
– relation (англ.).
Реляционной является БД, в которой все данные, доступные пользователю, организованы в виде набора двумерных таблиц, а все операции над данными сводятся к операциям над этими таблицами.
Рис. 1.1. Некоторые операции реляционной алгебры
Предложив реляционную модель данных, Кодд создал и инструмент для удобной работы с отношениями – реляционную алгебру. Каждая операция этой алгебры использует одну или несколько таблиц (отношений) в качестве ее операндов и получает в результате новую таблицу, т.е. позволяет "разрезать" или "склеивать" таблицы (рис. 1.1).
Реляционная алгебра в явном виде представляет набор операций, которые можно использовать, чтобы сообщить системе, как в базе данных из определенных отношений реально построить необходимое отношение.
Реляционные операторы обладают одним важным свойством: они замкнуты относительно понятия отношения. Это означает, что выражения реляционной алгебры определяются над отношениями реляционных БД и результатом вычисления также являются отношения. Поскольку результатом любой реляционной операции является некоторое отношение, можно образовывать реляционные выражения, в которых вместо отношения-операнда некоторой реляционной операции находится вложенное реляционное выражение.
Выражения реляционной алгебры строятся на основе алгебраических операций (высокого уровня), и подобно тому, как интерпретируются арифметические и логические выражения, выражение реляционной алгебры также имеет процедурную интерпретацию. Другими словами, запрос, представленный на языке реляционной алгебры, может быть вычислен на основе вычисления элементарных алгебраических операций с учетом их старшинства и возможного наличия скобок.
Набор основных алгебраических операций состоит из восьми операций, которые делятся на два класса - теоретико-множественные операции и специальные реляционные операции, дополненные некоторыми специальными операциями, специфичными для баз данных.
В состав теоретико-множественных операций входят традиционные операции над множествами:
- объединение;
- пересечение;
- разность;
- декартово произведение.
Специальные реляционные операции включают:
- выборку;
- проекцию;
- естественное соединение;
- деление.
Операции объединения, пересечения и разности требуют от операндов совместимости по типу. Два отношения совместимы по типу, если:
каждое из них имеет одно и то же множество имен атрибутов (одна и та же степень),
соответствующие атрибуты (с одинаковыми именами) определены на одном и том же домене.
Отношение А Отношение В
| ID_NUM |
NAME |
CITY |
AGE |
ID_NUM |
NAME |
CITY |
AGE |
| 1809 |
Иванов |
Москва |
45 |
1809 |
Иванов |
Москва |
45 |
| 1996 |
Петров |
Нижний Новгород |
39 |
1896 |
Галкин |
Иваново |
40 |
| 1777 |
Сидоров |
Рязань |
21 |
Объединением
двух совместимых по типу отношений А и В (А È В) называется отношение с тем же заголовком, как в отношениях А и В, и с телом, состоящим из множества кортежей t, принадлежащих А или В или обоим отношениям.
А È В
| ID_NUM |
NAME |
CITY |
AGE |
| 1809 |
Иванов |
Москва |
45 |
| 1996 |
Петров |
Нижний Новгород |
39 |
| 1777 |
Сидоров |
Рязань |
21 |
| 1896 |
Галкин |
Иваново |
40 |
При выполнении операции объединения двух отношений создается отношение, включающее кортежи, входящие хотя бы в одно из отношений-операндов. Обратите внимание, что повторяющиеся кортежи удаляются по определению отношения.
Пересечением двух совместимых по типу отношений А и В (А Ç В) называется отношение с тем же заголовком, как в отношениях А и В, и с телом, состоящим из множества кортежей t, принадлежащих одновременно обоим отношениям А и В.
Операция пересечения двух отношений создает отношение, включающее все кортежи, входящие в оба отношения-операнда.
А Ç В
| ID_NUM |
NAME |
CITY |
AGE |
| 1809 |
Иванов |
Москва |
45 |
Разностью
двух совместимых по типу отношений А и В (А - В) называется отношение с тем же заголовком, как в отношениях А и В, и с телом, состоящим из множества кортежей t, принадлежащих отношению А и не принадлежащих отношению В.
Отношение, являющееся разностью двух отношений включает все кортежи, входящие в первое отношение, такие, что ни один из них не входит во второе отношение.
А - В
| ID_NUM |
NAME |
CITY |
AGE |
| 1996 |
Петров |
Нижний Новгород |
39 |
| 1777 |
Сидоров |
Рязань |
21 |
Декартово произведение
двух отношений А и В (А ´ В), где А и В не имеют общих имен атрибутов, определяется как отношение с заголовком, представляющим собой сцепление двух заголовков исходных отношений А и В, и телом, состоящим из множества кортежей t таких что первым является любой кортеж отношения А,
а вторым – любой кортеж, принадлежащий отношению В. Кардинальное число результирующего отношения равно произведению кардинальных чисел исходных отношений, а степень равняется сумме степеней.
Пусть отношение А –
содержит имена всех текущих поставщиков, а отношение В –
номера всех текущих деталей. Тогда А ´ В – это все текущие пары поставщик – деталь и деталь – поставщик.
Отношение А Отношение В
| S1 |
P1 |
| S2 |
P2 |
| S3 |
P3 |
| P4 |
А ´ В
| S1 |
P1 |
S2 |
P1 |
S3 |
P1 |
| S1 |
P2 |
S2 |
P2 |
S3 |
P2 |
| S1 |
P3 |
S2 |
P3 |
S3 |
P3 |
| S1 |
P4 |
S2 |
P4 |
S3 |
P4 |
На практике явное использование операции декартово произведение требуется только для очень сложных запросов. Эта операция включена в реляционную алгебру по концептуальным соображениям: (декартово произведение требуется как промежуточный шаг при определении операции θ - соединения, которая используется довольно часто).
Выборка
– это сокращенное название θ - выборки, где θ означает любой скалярный оператор сравнения ().
θ -
выборкой,
из отношения А по атрибутам Х и Y (А where X θ Y) называется отношение, имеющее тот же заголовок, что и отношение А, и тело, содержащее множества кортежей t отношения А,
для которых проверка условия Х θ У дает значение истина. Атрибуты X и Y должны быть определены на одном и том же домене, а оператор должен иметь смысл для этого домена.
Операция выборка (или операция ограничение отношения) - создает новое отношение, содержащее только те строки отношения – операнда, которые удовлетворяют некоторому условию ограничения.
Пример операции выборки.
Отношение А.
| ID_NUM |
NAME |
CITY |
AGE |
| 1809 |
Иванов |
Москва |
45 |
| 1996 |
Петров |
Нижний Новгород |
39 |
| 1777 |
Сидоров |
Рязань |
21 |
| 1896 |
Галкин |
Москва |
30 |
A where CITY = 'Москва'
| ID_NUM |
NAME |
CITY |
AGE |
| 1809 |
Иванов |
Москва |
45 |
| 1896 |
Галкин |
Москва |
30 |
A where CITY = 'Москва' and AGE < 40
| ID_NUM |
NAME |
CITY |
AGE |
| 1896 |
Галкин |
Москва |
30 |
Проекцией
отношения А по атрибутам Х, Y,…,Z (A[X, Y,…Z]), где каждый из атрибутов принадлежит отношению А, называется отношение с заголовком {Х, Y,…,Z} и с телом, содержащим множество всех кортежей вида <Х:x, Y:y, ..., Z:z> таких, что в отношении A имеется кортеж, атрибут Х которого имеет значение x, атрибут Y имеет значение y, ..., атрибут Z имеет значение z. Тем самым, при выполнении операции проекции получается «вертикальное» подмножество данного отношения, то есть подмножество, получаемое исключением всех атрибутов, отношения-операнда с естественным уничтожением потенциально возникающих кортежей-дубликатов.
Пример операции проекции.
A [NAME, CITY] A [CITY]
| NAME |
CITY |
CITY |
| Иванов |
Москва |
Москва |
| Петров |
Нижний Новгород |
Нижний Новгород |
| Сидоров |
Рязань |
Рязань |
| Галкин |
Москва |
Соединение отношений
- создает новое отношение, каждый кортеж которого является результатом сцепления кортежей операндов (исходных отношений). Соединение имеет две разновидности: естественное соединение и соединение по условию (θ -соединение).
Пусть X={X1, X2, …, Xm}, Y={Y1, Y2, …, Yn}, Z={Z1, Z2, …, Zk}.
Естественным соединением отношений A(X,Y) и B(Y,Z) (A JOIN B) называется отношение с заголовком {Х, Y, Z} и с телом, содержащим множество всех кортежей вида <Х:x, Y:y, Z:z> таких, для которых в отношении A значение атрибута Х равно x, а значение атрибута Y равно y, и в отношении В значение атрибута Y равно y, а атрибута Z равно z. При естественном соединении производится сцепление строк операндов соединения по общим атрибутам.
Замечание 1. Соединения не всегда выполняются по внешнему ключу и соответствующему потенциальному ключу, хотя такие соединения очень распространены и являются важным частным случаем.
Замечание 2. Если отношения A и B не имеют общих атрибутов, то выражение A JOIN B эквивалентно A ´B.
Отношение А (поставщики) Отношение В (детали)
| ID_NUM |
NAME |
CITY |
STATUS |
IP_NUM |
NAIMEN |
CITY |
WEIGHT |
| 1809 |
Иванов |
Москва |
20 |
Р123 |
Болт |
Москва |
12 |
| 1996 |
Петров |
Нижний Новгород |
15 |
Р896 |
Гайка |
Нижний Новгород |
14 |
| 1777 |
Сидоров |
Рязань |
10 |
Р432 |
Шарнир |
Москва |
15 |
A JOIN B
| ID_NUM |
NAME |
STATUS |
CITY |
IP_NUM |
NAIMEN |
CITY |
WEIGHT |
| 1809 |
Иванов |
20 |
Москва |
Р123 |
Болт |
Москва |
12 |
| 1809 |
Иванов |
20 |
Москва |
Р432 |
Шарнир |
Москва |
15 |
| 1996 |
Петров |
15 |
Нижний Новгород |
Р896 |
Гайка |
Нижний Новгород |
14 |
Тета – соединение
. Пусть отношения А и В не имеют общих имен атрибутов и θ определяется так же, как в операции выборки.
θ - соединением отношения А по атрибуту X с отношением В по атрибуту Y называется результат вычисления выражения (A´B) WHERE X θ Y.
θ - соединение – это отношение с тем же заголовком, что и при декартовом произведении отношений А и В, и с телом, содержащим множество кортежей t Î A´B , таких что вычисление условия X θ Y дает значение истина для данного кортежа. Атрибуты X и Y должны быть определены на одном и том же домене, а оператор должен иметь смысл для этого домена.
Таким образом, операция θ -соединение эквивалентна двум операциям: нахождению расширенного декартова произведения двух отношений (при необходимости с переименованием соответствующих атрибутов) и последующему выполнению указанной выборки из полученного результата. Если условие выполнено, полученная строка включается в отношение – результат.
Пример операции θ - соединения.
Отношение А (поставщики) Отношение В (поставки)
| ID_NUM |
NAME |
CITY |
STATUS |
ID_NUM |
IP_NUM |
QTY |
| 1809 |
Иванов |
Москва |
20 |
1809 |
Р123 |
100 |
| 1996 |
Петров |
Нижний Новгород |
15 |
1809 |
Р896 |
200 |
| 1777 |
Сидоров |
Рязань |
10 |
1777 |
Р432 |
150 |
| 1996 |
Р432 |
150 |
||||
| 1996 |
Р123 |
250 |
(A´B (RENAME ID_NUM AS AID_NUM) WHERE QTY <200
| ID_NUM |
NAME |
CITY |
STATUS |
AID_NUM |
IP_NUM |
QTY |
| 1809 |
Иванов |
Москва |
20 |
1809 |
Р123 |
100 |
| 1996 |
Петров |
Нижний Новгород |
15 |
1996 |
Р432 |
150 |
| 1777 |
Сидоров |
Рязань |
10 |
1777 |
Р432 |
150 |
Операция деления
У операции реляционного деления два операнда - бинарное и унарное отношения. Пусть X={X1, X2, …, Xm}, Y={Y1, Y2, …, Yn}.
Делением отношений А(Х,Y) на В(Y) (А/В) называется отношение с заголовком {X} и телом, содержащим множество всех кортежей {X:x}, таких что существует кортеж {X:x, Y:y}, который принадлежит отношению А для всех кортежей {Y:y}, принадлежащих отношению В.
Деление отношений - создает новое отношение, содержащее атрибуты первого отношения, отсутствующие во втором отношении и кортежи первого отношения, которые совпали кортежами второго. Для выполнения этой операции второе отношения должно содержать лишь атрибуты, совпадающие с атрибутами первого.
Замечание: Операция деления полезна тогда, когда запрос содержит слово «все».
Пример:
Отношение А Отношение В Отношение В1 Отношение В2
| S# |
P# |
P# |
P# |
P# |
| S1 |
P1 |
P1 |
P2 |
P1 |
| S1 |
P2 |
P3 |
P2 |
|
| S1 |
P3 |
P3 |
||
| S1 |
P4 |
|||
| S2 |
P1 |
A/B |
A/В1 |
A/B2 |
| S2 |
P3 |
S1 |
S1 |
S1 |
| S3 |
P2 |
S2 |
S3 |
|
| S3 |
P3 |
Кроме рассмотренных выше операций в состав алгебры включаются:
● операция присваивания, позволяющая сохранить в базе данных результаты вычисления алгебраических выражений (A:= B),
○ операция переименования атрибутов, дающая возможность корректно сформировать заголовок (схему) результирующего отношения (A RENAME X AS Y),
● операция расширения, позволяющая создавать новое отношение, дополненное атрибутом, значения которого получены посредством некоторых скалярных вычислений (EXTEND <имя отношения> AS <скалярное выражение>),
● операция подведения итогов, дающая возможность разбивать множество кортежей отношения на группы в соответствии с содержимым одного или нескольких атрибутов, и внутри каждой группы применять определенный оператор агрегирования (аналог операции GROUP BY в языке SQL). Операторы агрегирования предназначены для подведения итогов в определенном столбце таблицы – отношения, например, для нахождения суммарных, средних, минимальных и максимальных значений (SUM, AVG, MIN, MAX, COUNT).
SUMMARIZE <реляционное выражение> BY (атрибуты) ADD <функция агрегирования> AS <новое имя атрибута>
Пример: SUMMARIZE SP BY (P#) ADD SUM(QTY) AS TOTAL_QTY
Отношение SP Результат
| S# |
P# |
QTY |
P# |
TOTAL_QTY |
| S1 |
P1 |
300 |
P1 |
600 |
| S1 |
P2 |
200 |
P2 |
300 |
| S1 |
P3 |
400 |
P3 |
800 |
| S1 |
P4 |
200 |
P4 |
300 |
| S2 |
P1 |
300 |
||
| S2 |
P3 |
200 |
||
| S3 |
P2 |
100 |
||
| S3 |
P3 |
200 |
||
| S3 |
P4 |
100 |
Примеры использования реляционных операторов.
Пример №1
.
Ежемесячно из цехов поступают отчеты о выпуске продукции за прошедший месяц, содержащие номер цеха, код продукции, дату выпуска и количество выпущенной продукции в таблице Новая продукция.
Новая продукция (номер цеха, код продукции, дата выпуска, количество)
На заводе имеется сводная таблица, содержащая сведения о выпускаемой продукции в целом, с такой же структурой.
Выпуск продукции (номер цеха, код продукции, дата выпуска, количество)
Так как атрибуты отношений совпадают, то для обновления сведений об ассортименте и количестве выпускаемой продукции необходимо отношение Новая продукция объединить с исходным отношением Выпуск продукции.
Новая продукция È Выпуск продукции
Пример №2
.
Имеется набор экзаменационных ведомостей - отношений с совпадающими атрибутами:
Ведомость_i (группа_i, номер зачетной книжки_i, фамилия студента_i, дата_i, дисциплина_i, оценка_i)
Подготовить список студентов, получивших только отличные оценки, с атрибутами Номер зачетной книжки и Фамилия студента.
Для экзаменационных ведомостей нужной группы:
1. Выполняем ограничение исходных отношений, отбирая из каждого в новое отношение кортежи, удовлетворяющие условию оценка_i = 'отлично'. Получили списки отличников группы по дисциплинам.
Отличники_i := Ведомость_i WHERE оценка_i = 'отлично'
2. Выполняем проекцию полученных отношений, отбирая из каждого только атрибуты номер зачетной книжки и фамилия студента. Получили новые списки отличников, в которых остались только номера зачетных книжек и фамилии студентов.
Отличники_i [номер зачетной книжки, фамилия студента]
3. Пересечение последних даст нам искомое отношение - Список отличников, содержащее номера зачетных книжек и фамилии общие для всех списков отличников
Пример №3
.
Используя ежемесячные отчеты цехов о выпуске продукции (смотри пример №1), подготовить сведения о выпуске новых видов продукции за последний квартал.
Для решения этой задачи выполняем:
1. Из отношения Выпуск продукции делаем выборку дата выпуска больше последней даты прошлого квартала. Результат ограничения помещаем во временную таблицу_1:
Таблица_1:=Выпуск продукции WHERE дата выпуска>'30/06/2005'
2. Затем над той же исходной таблицей выполняем ограничение дата выпуска не больше последней даты прошлого квартала и заносим результат во временную таблицу_2:
Таблица_2:=Выпуск продукции WHERE дата выпуска<='30/06/2005'
3. Разность отношений 1 и 2 даст искомые сведения.
Таблица_1 – Таблица_2
Замечание: Один и тот же запрос можно сформулировать несколькими способами. В приведенном ниже запросе используется оператор выборки по дате:
Выпуск продукции WHERE дата выпуска > '30/06/2005'
AND дата выпуска <= '30/09/2005'
Пример №4
.
Список студентов факультета содержит для каждого студента ФИО., Дату рождения, Шифр группы и Признак наличия стипендии (да, нет).
Список (фамилия студента, группа, дата рождения, стипендия)
Необходимо сформировать список студентов заданной группы, получающих стипендию.
Для этого:
1. Создаем вспомогательное отношение с атрибутами Шифр группы и Признак наличия стипендии.
Стипендия (группа, стипендия)
2. Заполняем один кортеж этого отношения, поместив в него шифр заданной группы и отметку о получении стипендии (да).
| группа |
стипендия |
| 8109 |
да |
3. Деление исходного списка на вспомогательное отношение создаст искомый список с атрибутами: ФИО и Дата рождения.
Вот другая формулировка того же запроса:
Сделаем θ - выборку из отношения Список. Найдем все кортежи, которые удовлетворяют двум условия: шифр группы совпадает с заданным и признак наличия стипендии = 'да'.
Возьмем проекцию полученного отношения по атрибутам ФИО и Дата рождения.
Список WHERE группа = 8109 AND стипендия = 'да' [ФИО, дата рождения]
Пример №5
.
Информация о поставщиках, деталях и поставках содержится в трех отношения.
Отношение Поставщики (S) содержит номер поставщика, его имя, город и статус
S (S#, SNAME, CITY, STATUS)
Отношение Детали (P) содержит информацию о коде детали, наименовании, весе, цвете и месте хранения.
P (P#, PNAME,WEIGHT, COLOR, CITY)
Отношение Поставка (SP) содержит сведения о номере поставщика, коде детали и количестве.
SP (S#, P#, QTY)
Необходимо получить имена поставщиков, которые не поставляют деталь с кодом Р2.
Рассмотрим пошаговое решение этого запроса:
1. Возьмем проекцию отношения S по атрибуту S#. Получим отношение Т1 с одним атрибутом:
T1 := S[S#]
2. Из отношения SP выберем кортежи, в которых код детали равен Р2. Получим отношение Т2 с той же самой структурой, что и исходное отношение SP:
T2 := SP WHERE P# = 'P2'
3. Возьмем проекцию отношения Т2 по атрибуту S#. Получим отношение Т3 с одним атрибутом:
T3 := T2 [S#]
4. Разность отношений Т1 и Т3 даст номера тех поставщиков, которые не поставляют деталь с кодом Р2.
T4 := T1 - T1
Операция естественного соединения по атрибуту S# позволяет сформировать отношение Т5 с такой же структурой, что и отношение S, но кортежи этого отношения будут содержать информацию лишь о тех поставщиках, которые не поставляют деталь Р2:
T5 := T4 JOIN S
5. Выполним проекцию отношения Т5 по атрибуту SNAME. Получим искомое отношение, содержащее имена поставщиков:
T6 := T5 [SNAME]
Выразим данный запрос в виде одной формулы.
( ( S [S#] - ( SP WHERE P# = 'P2' ) [S#] ) JOIN S ) [SNAME]
В заключение данного раздела отметим, что существует альтернативный подход манипулирования реляционными данными, основанный на математической логике (точнее, на исчислении предикатов первого порядка), - реляционное исчисление.
Принципиальное различие между ними следующее: алгебра представляет набор операторов, с помощью которых из определенных отношений можно построить
необходимое отношение; в то время как исчисление представляет собой просто систему обозначений для определения
необходимого отношения в терминах данных отношений. Пользователь в терминах реляционного исчисления лишь устанавливает определенные характеристики необходимого отношения, оставляя системе решать, что соединять, проецировать и т.д., чтобы получить требуемый результат. Таким образом, формулировка запроса в терминах исчисления
носит описательный
характер, а алгебраическая
формулировка – предписывающий
.
На самом деле эти отличия существуют только внешне. На самом деле механизмы реляционной алгебры и реляционного исчисления эквивалентны, т.е. для любого допустимого выражения реляционной алгебры можно построить эквивалентную (т.е. производящую такой же результат) формулу реляционного исчисления и наоборот, то есть между ними существует взаимнооднозначное соответствие.
Различия связаны с разным уровнем процедурности. Выражения реляционной алгебры строятся на основе алгебраических операций (высокого уровня), и подобно тому, как интерпретируются арифметические и логические выражения, выражение реляционной алгебры также имеет процедурную интерпретацию. Другими словами, запрос, представленный на языке реляционной алгебры, может быть вычислен на основе вычисления элементарных алгебраических операций с учетом их старшинства и возможного наличия скобок. Для формулы реляционного исчисления однозначная интерпретация, вообще говоря, отсутствует. Формула только определяет условия, которым должны удовлетворять кортежи результирующего отношения. Поэтому языки реляционного исчисления являются более непроцедурными или декларативными. Исчисление ближе к естественному языку, а алгебра – к языку программирования.
Алгебра и исчисление обладают большой выразительной мощностью: очень сложные запросы к базе данных могут быть выражены с помощью одного выражения реляционной алгебры или одной формулы реляционного исчисления. Именно по этой причине эти механизмы включены в реляционную модель данных. Конкретный язык манипулирования реляционными БД называется реляционно полным
, если любой запрос, выражаемый с помощью одного выражения реляционной алгебры или одной формулы реляционного исчисления, может быть выражен с помощью одного оператора этого языка.
Заметим, что крайне редко алгебра или исчисление принимаются в качестве полной основы какого-либо языка БД. Обычно язык основывается на некоторой смеси алгебраических и логических конструкций. Тем не менее, знание алгебраических и логических основ языков баз данных часто бывает полезно на практике.
Созданы языки манипулирования данными, позволяющие реализовать все операции реляционной алгебры и практически любые их сочетания. Среди них наиболее распространены SQL (Structured Query Language –
структуризованный язык запросов) и QBE (Quere-By-Example –
запросы по образцу). Оба относятся к языкам очень высокого уровня, с помощью которых пользователь указывает, какие данные необходимо получить, не уточняя процедуру их получения.
Часть 2. SQL- структуризованный язык запросов
Все языки управления данными, созданные до появления реляционных баз данных и разработанные для многих систем управления базами данных персональных компьютеров, были ориентированы на операции с данными, представленными в виде логических записей файлов. Это требовало от пользователей детального знания организации хранения данных и достаточных усилий для указания не только того, какие данные нужны, но и того, где они размещены, и как шаг за шагом получить их.
Язык SQL (Structured Query Language - структуризованный язык запросов) ориентирован на операции с данными, представленными в виде логически взаимосвязанных совокупностей таблиц. Особенность предложений этого языка состоит в том, что они ориентированы в большей степени на конечный результат обработки данных, чем на процедуру этой обработки. SQL сам определяет, где находятся данные, какие индексы и даже наиболее эффективные последовательности операций следует использовать для их получения: не надо указывать эти детали в запросе к базе данных.
SQL был разработан начале 70-х годов прошлого века в отделениях фирмы IBM. И уже к 80-ым годам стал фактическим стандартом для профессиональных реляционных СУБД. К настоящему времени он используется в таких системах управления реляционными базами данных, как Oracle, INGRES, Informix, Sybase, SQLbase, Microsoft SQL Server, DB2 (СУБД самой IBM), SQL/DC, Paradox, Access, FoxPro, dBase, Approach, MySQL, PosgreSQL и многими другими. Уже более 140 продуктов имеют в своем составе SQL-интерфейс. Можно с уверенностью сказать, что если вы собираетесь в ближайшем будущем использовать реляционные базы данных, то вам придется работать с SQL.
Реализация в SQL концепции операций, ориентированных на табличное представление данных, позволило создать компактный язык с небольшим набором операторов. SQL может использоваться как интерактивный (для выполнения запросов) и как встроенный (для построения прикладных программ). В нем существуют:
◦ операторы определения данных (определение баз данных, а также определение и уничтожение таблиц и индексов);
◦ запросы на выбор данных;
◦ предложения модификации данных (добавление, удаление и изменение данных);
◦ арифметические вычисления (включая разнообразные функциональные преобразования), обра
◦ упорядочение строк и (или) столбцов при выводе содержимого;
◦ агрегатирование данных: группирование данных и применение к этим группам таких операций, как среднее, сумма, максимум, минимум, число элементов и т.п.
◦ и др.
Data Definition Language
Язык определения данных (Data Definition Language или DDL) включает в себя все операторы, используемые для определения объектов реляционной базы данных, прежде всего это операторы CREATE DATABASE, DROP DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE., CREATE INDEX и DROP INDEX.
CREATE DATABASE
CREATE DATABASE
имя базы данных
Оператор CREATE DATABASE создает новую базу данных с именем имя базы данных. Если база данных с таким именем уже существует, то выполнение этого оператора приводит к ошибке.
DROP DATABASE
DROP DATABASE
имя базы данных
Оператор DROP DATABASE удаляет базу данных с именем имя базы данных.
CREATE TABLE
CREATE [TEMPORARY] TABLE имя таблицы
(определение, . . .)
[предложение SELECT]
определение:
{ имя столбца тип [ NOT NULL | NULL ] [DEFAULT значение][ PRIMARY_KEY ]
| PRIMARY KEY (столбцы)
| UNIQUE имя индекса (столбец[(длина)],...)
| INDEX имя индекса(столбец[(длина)],...)
| FOREIGN KEY (columns) REFERENCES имя таблицы [ (столбцы) ]
[ ON DELETE { CASCADE | SET NULL } ] [ ON UPDATE { CASCADE | SET NULL } ]
}
Оператор CREATE TABLE создает новую таблицу с именем имя таблицы. Если указывается ключевое слово TEMPORARY, таблица существует только до конца текущего сеанса соединения или запуска оператора DROP TABLE, после чего удаляется.
Попытка создать таблицу с уже используемым именем приводит к появлению ошибки.
В качестве определения может выступать описание столбца, определение ключа или предложение FOREIGN KEY для описания внешнего ключа.
Описание столбца начинается с имени столбца и его типа и может сопровождаться несколькими необязательными ключевыми словами.
В SQL используются следующие основные типы данных, форматы которых могут несколько различаться для разных СУБД:
INTEGER
- целое число (обычно до 10 значащих цифр и знак);
SMALLINT
- короткое целое (обычно до 5 значащих цифр и знак);
DECIMAL(p,q)
- десятичное число, имеющее p цифр (0 < p < 16) и знак; с помощью q задается число цифр справа от десятичной точки (q < p, если q = 0, оно может быть опущено);
FLOAT
- вещественное число с 15 значащими цифрами и целочисленным порядком, определяемым типом СУБД;
CHAR(n)
- символьная строка фиксированной длины из n символов (0 < n < 256);
VARCHAR(n)
- символьная строка переменной длины, не превышающей n символов (n > 0 и разное в разных СУБД, но не меньше 4096);
DATE
- дата в формате, определяемом специальной командой (по умолчанию mm/dd/yy); поля даты могут содержать только реальные даты, начинающиеся за несколько тысячелетий до н.э. и ограниченные пятым-десятым тысячелетием н.э.;
TIME
- время в формате, определяемом специальной командой, (по умолчанию hh.mm.ss);
DATETIME
- комбинация даты и времени;
MONEY
- деньги в формате, определяющем символ денежной единицы ($, руб, ...) и его расположение (суффикс или префикс), точность дробной части и условие для показа денежного значения.
В некоторых СУБД еще существует тип данных LOGICAL, DOUBLE и ряд других.
При определении типа столбца можно определять NULL или NOT NULL, т.е. может или нет столбец содержать значения NULL. По умолчанию установлено значение NULL.
Значение по умолчанию DEFAULT должно быть константой, оно не может быть функцией или выражением. Если для данного столбца не задается никакой величины DEFAULT, то СУБД автоматически назначает ее. Если столбец может принимать NULL как допустимую величину, то по умолчанию присваивается значение NULL. Если столбец объявлен как NOT NULL, то значение по умолчанию зависит от типа столбца: для числовых типов значение по умолчанию равно 0; для типов даты и времени значение по умолчанию равно соответствующей нулевой величине для данного типа; для строковых типов значением по умолчанию является пустая строка.
PRIMARY KEY определяет столбец как первичный ключ.
UNIQUE определяет столбец как альтернативный ключ.
Предложения PRIMARY KEY и UNIQUE определяют так же и индексы, которые должны содержать уникальные значения.
Предложение INDEX дает возможность добавить индекс к создаваемой таблице.
С помощью выражения имя столбца (длина) можно указать индекс, для которого используется только часть столбца CHAR или VARCHAR. Это поможет сделать файл индексов намного меньше.
Определяемые ключи и индексы базируется на перечисленных в столбцах, каждый из которых должен быть столбцом таблицы. Если указывается сразу несколько столбцов, их названия следует разделить запятыми.
При добавлении предложения SELECT таблица создается с помощью результирующего набора, возвращаемого запросом.
В следующем примере показано создание таблицы Clients
CREATE TABLE Clients (
ID_NUM INTEGER NOT NULL PRIMARY KEY,
Name CHAR(64) NOT NULL,
City CHAR(32) NOT NULL,
AGE SMALLINT NOT NULL,
INDEX part_of_city (City(10))
)
ALTER TABLE
ALTER TABLE имя таблицы
операции
Оператор ALTER TABLE позволяет изменять структуру таблицы. Для его выполнения задайте имя таблицы и определите операции, которые необходимо выполнить над таблицей.
Предложение операции определяет одно или несколько разделяемых запятыми операций. Все эти операции выполняются в порядке указания. В качестве операций можно задавать следующие.
ADD COLUMN описание столбца
Добавляет столбец в таблицу. Описание имеет такой же формат, как и в операторе CREATE TABLE.
ALTER TABLE Clients ADD COLUMN Phone CHAR(16)
Этот пример добавляет в таблицу Clients новый столбец Phone.
ADD PRIMARY KEY (столбцы)
Добавляет первичный ключ с именем PRIMARY по определенным столбцам. Если первичный ключ уже существует, результатом выполнения этой операции будет ошибка.
ADD UNIQUE имя индекса (столбец[(длина)],...)
Добавляет индекс с уникальным значением по таблице.
ADD INDEX имя индекса (столбец[(длина)],...)
Добавляет индекс таблице.
CHANGE COLUMN имя столбца описание столбца
Изменяет название и описание столбца. Предложение описание столбца имеет такой же формат, какой используется для оператора CREATE TABLE. В описание столбца включено его новое имя, поэтому если необходимо оставить это имя неизменным, его обязательно нужно ввести дважды.
DROP PRIMARY KEY
Удаляет первичный ключ из таблицы.
DROP INDEX имя индекса
Удаляет индекс из таблицы.
DROP TABLE
DROP TABLE имя таблицы [,имя таблицы]...
Удаляет указанную таблицу (или таблицы) из базы данных.
CREATE INDEX
CREATE [UNIQUE] INDEX имя индекса
ON имя таблицы (имя столбца[(длина)],...)
Оператор CREATE INDEX дает возможность добавить индексы к существующим таблицам.
Список столбцов в форме (имя столбца_1, имя столбца_2,...) создает индекс для нескольких столбцов. Величины индексов формируются путем конкатенации величин указанных столбцов.
Предложение UNIQUE указывает, что никаким двум строкам в индексируемой таблице не позволяется принимать одно и то же значение для индексируемого столбца (или комбинации столбцов) в одно и то же время.
Для столбцов типов CHAR и VARCHAR с помощью параметра имя столбца(длина) могут создаваться индексы, для которых используется только часть. Пример, приведенный ниже, создает индекс, используя первые 10 символов столбца Name:
CREATE INDEX part_of_name ON Clients (Name(10))
Поскольку большинство имен обычно имеют отличия друг от друга в первых 10 символах, данный индекс не должен быть намного медленнее, чем созданный из столбца Name целиком. Кроме того, используя неполные столбцы для индексов, можно сделать файл индексов намного меньше, а это позволяет сэкономить место на диске и к тому же повысить скорость операций INSERT.
DROP INDEX
DROP INDEX имя индекса ON имя таблицы
Оператор DROP INDEX удаляет индексы, указанные в имя индекса из таблицы имя таблицы. Пример:
DROP INDEX part_of_name ON Clients
Data Manipulation Language
Язык управления данными (Data Manipulation Language или DML) включает все операторы, которые используются для записи (хранения), изменения и поиска данных в таблицах. Основные операторы этого языка: SELECT, INSERT, UPDATE и DELETE. Оператор SELECT применяется для формирования запросов, и, возможно, является наиболее сложным из одиночных операторов SQL. Остальные операторы используются для манипулирования данными в пределах одной таблицы.
SELECT
Все запросы на получение практически любого количества данных из одной или нескольких таблиц выполняются с помощью единственного предложения SELECT. В общем случае результатом реализации предложения SELECT является другая таблица. К этой новой таблице может быть снова применена операция SELECT и т.д., т.е. такие операции могут быть вложены друг в друга.
Предложение SELECT может использоваться как:
1. самостоятельная команда на получение и вывод строк таблицы, сформированной из столбцов и строк одной или нескольких таблиц;
2. элемент WHERE- или HAVING-условия , называемый «подзапрос» или «вложенный запрос».
Оператор SELECT имеет следующий формат:
SELECT [DISTINCT]
{ select_выражение
| агрегатная функция
| {имя | псевдоним } таблицы.*
| *
},...
FROM таблицы
[WHERE условие отбора строк]
[GROUP BY {{имя | псевдоним } таблицы.] имя столбца}.,..
[HAVING условие отбора групп]]
[{UNION }[ALL]
оператор select]
[ORDER BY {{столбец-результат [ASC|DESC]}.,..}
|{{положительное целое [ASC|DESC]}.,..}]
При указании ключевых слов следует точно соблюдать порядок, указанный выше. Например, предложение HAVING должно располагаться после всех выражений GROUP BY и перед всеми выражениями ORDER BY.
SELECT применяется для извлечения строк, выбранных из одной или нескольких таблиц.
{имя | псевдоним } таблицы.* означает выборку всех столбцов из таблицы
* используется, если нужно выбрать все столбцы из всех таблиц, перечисленных в выражении FROM. Выражение select_выражение задает столбцы, из которых будет состоять новая таблица – результат выборки. Используя ключевое слово AS, select_выражению в SELECT можно присвоить псевдоним.
select_выражение [AS имя столбца]
В качестве select_выражения может использоваться конструкция вида
{[{имя | псевдоним} таблицы.] столбец | выражение | константа }
В качестве выражения может использоваться конструкция, содержащая имена столбцов, SQL функции, арифметические, логические, битовые, строковые операторы, операции сравнения, круглые скобки для управления приоритетом операций.
Ссылки на столбцы могут задаваться в виде столбец, имя таблицы.столбец или псевдоним таблицы.столбец. Имя таблицы или псевдоним таблицы можно не указывать для ссылок на столбцы, если эти ссылки нельзя истолковать неоднозначно.
Включение параметра DISTINCT препятствует появлению в выборке одинаковых записей.
□ Предложение FROM таблицы задает таблицы, из которых надлежит извлекать строки. Если указано имя более чем одной таблицы, следует выполнить соединение.
Ссылку на таблицу можно заменить псевдонимом, используя имя таблицы [AS] псевдоним.
□ Предложение WHERE включает набор условий для отбора строк.
WHERE [NOT] условие_1 [[AND|OR][NOT] условие_2]...
где условие_1, _2 ... – одна из следующих конструкций:
выражение { = | <> | < | <= | > | >= } { значение | ( подзапрос ) }
выражение [NOT] BETWEEN значение_2 AND значение_3
выражение [NOT] IN { ( константа_1 [,константа_2]... ) | ( подзапрос ) }
выражение IS [NOT] NULL
выражение [NOT] LIKE 'шаблон'
EXISTS ( подзапрос )
Выражения в WHERE строятся по тем же правилам, что и в select_выражениях.
□ Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции. Если это предложение отсутствует и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT, должны быть включены в агрегатные функции, и эти функции будут применяться ко всему набору строк, которые удовлетворяют запросу. В противном случае все столбцы списка SELECT, не вошедшие в агрегатную функцию, должны быть сгруппированы с помощью предложения GROUP BY. Все выходные строки запроса, которые сгруппированы по равенству значений столбцов, образуют единую группу. Далее к этим группам применяются агрегатные функции (SUM, COUNT, AVG, MIN или MAX), указанные во предложении SELECT, что приводит к замене всех значений группы на единственное значение (сумма, количество, среднее, минимальное или максимальное значение).
□ С помощью предложения HAVING можно включать дополнительное условие отбора. Конструкция HAVING очень похожа на WHERE, однако если предложение WHERE определяет предикат для фильтрации строк, то предложение HAVING применяется после группировки для определения аналогичного предиката, фильтрующего группы по значениям агрегатных функций. Это предложение необходимо для проверки значений, которые получены с помощью агрегатной функции не из отдельных строк таблиц, указанных в предложении FROM, а из групп таких строк. Поэтому такая проверка не может содержаться в предложении WHERE.
□ Предложение UNION включает все выходные строки каждого из запросов. Если определен параметр ALL, то сохраняются все дубликаты выходных строк, в противном случае оставляется только одна строка.
□ С помощью предложения ORDER BY можно расположить результаты одного или нескольких запросов в определенном порядке. Строки сортируются в соответствии со значениями столбцов, указанных в списке. Первый столбец имеет наивысший приоритет, второй столбец задает порядок сортировки дублируемых значений первого столбца, третий столбец вступает в действие, если совпадают значения во втором столбце, и т.д. Можно задать параметр сортировки ASC (по возрастанию, используется по умолчанию) или DESC (по убыванию) отдельно для каждого столбца. Сортировка набора символов будет осуществляться в соответствии с его упорядочивающей последовательностью. Вместо имен столбцов можно указывать целые числа. Эти числа указывают на местоположение столбца в выходных данных, так что 1 будет указывать на первый столбец, а 5 – на пятый столбец и т.д. Если выходные столбцы не имеют имен, то будут использоваться номера.
C l i e n t s
| ID_NUM |
NAME |
CITY |
AGE |
| 1809 |
Иванов |
Москва |
45 |
| 1996 |
Петров |
Нижний Новгород |
39 |
| 1777 |
Сидоров |
Рязань |
21 |
Если нужно получить все данные о заказчиках из Твери, включенных в таблицу Clients, необходимо воспользоваться оператором SELECT для формирования следующего запроса:
SELECT *
FROM Clients
WHERE City = 'Тверь'
Первая строка означает "выбери все столбцы", а предложение FROM указывает на таблицу, из которой они должны быть выбраны. С помощью WHERE запрашиваем не просто конкретную строку, а все строки, которые удовлетворяют указанному условию (ассоциируются с Тверью). Не имеет никакого значения, сколько заказчиков из Твери записано в таблице - ни одного или десять тысяч. Все записи будут получены с помощью этого оператора.
Другие примеры:
□ Найти только имена заказчиков из Твери, возраст которых превышает 40 лет.
SELECT Name FROM Clients WHERE City = 'Тверь' AND Age > 40
□ Найти данные о всех заказчиках, фамилии которых начинаются на букву «И»
SELECT * FROM Clients WHERE Name LIKE 'И%'
Здесь в выражении WHERE использовалось сравнение с шаблоном 'И%'. Знак % заменяет последовательность произвольной длины любых символов.
□ Показать год рождения каждого заказчика
SELECT Name, (2005-AGE) AS BirthYear FROM Clients
Год рождения - вычисляемый столбец, для обозначения нового столбца использован псевдоним BirthYear.
□ Показать, города, где проживают заказчики.
SELECT DISTINCT City FROM Clients
□ Вычислить средний возраст заказчиков из Москвы
SELECT AVG(AGE) AS AvgAge FROM Clients WHERE City = 'Москва'
В запросе используется агрегатная функция AVG() для вычисления среднего значения в столбце AGE среди строк, где City = 'Москва'. В результате, при любом количестве заказчиков из разных городов, получится таблица, состоящая из из одной строки и одного столбца.
□ Показать, сколько заказчиков в каждом городе
SELECT City, COUNT(*) AS Client_Count FROM Clients GROUP BY City
В запросе применяется группировка. Здесь все записи из таблицы разбиваются на группы с одинаковыми названиями городов GROUP BY City, и в каждой группе вычисляется количество строк COUNT(*).
□ Показать города, где количество заказчиков не превышает 10
SELECT City, COUNT(*) AS Client_Count FROM Clients
GROUP BY City HAVING COUNT(*)<=10
□ Определит количество заказчиков в каждом городе можно только с помощью группировки, поэтому ограничение на количество заказчиков можно вводить только в HAVING, а не в WHERE.
□ Найти самых молодых заказчиков
SELECT * FROM Clients WHERE Age = (SELECT MIN(Age) FROM Clients)
Здесь используется вложенный запрос (подзапрос) в условии WHERE. Подзапрос возвращает минимальный возраст среди всех заказчиков. Внешний запрос выводит данные о заказчиках, возраст которых равен минимальному.
□ Найти самых молодых и самых пожилых заказчиков
SELECT * FROM Clients WHERE Age = (SELECT MIN(Age) FROM Clients)
UNION
SELECT * FROM Clients WHERE Age = (SELECT MAX(Age) FROM Clients)
Запрос построен как объединение UNION двух запросов. Первый находит самых молодых, второй – самых пожилых заказчиков. В случае, если в таблице все заказчики одного возраста, то в получаемой выборке не будет дублирования, т.к. UNION здесь используется без ALL.
Реализация операций реляционной алгебры оператором SELECT
С помощью предложения SELECT можно реализовать любую операцию реляционной алгебры.
□ Выборка (горизонтальное подмножество) таблицы создается из тех ее строк, которые удовлетворяют заданным условиям. Пример:
SELECT *
FROM Clients
WHERE City = 'Тверь'
□ Проекция (вертикальное подмножество) таблицы создается из указанных ее столбцов (в заданном порядке) с последующим исключением избыточных дубликатов строк. Пример:
SELECT DISTINCT City FROM Clients
□ Объединение двух таблиц содержит те строки, которые есть либо в первой, либо во второй, либо в обеих таблицах. Пример:
SELECT * FROM Clients WHERE Age = (SELECT MIN(Age) FROM Clients)
UNION
SELECT * FROM Clients WHERE Age = (SELECT MAX(Age) FROM Clients)
□ Пересечение двух таблиц содержит только те строки, которые есть и в первой, и во второй. Пример:
SELECT * FROM Clients WHERE Age IN (SELECT MIN(Age) FROM Clients)
□ Разность двух таблиц содержит только те строки, которые есть в первой, но отсутствуют во второй. Пример:
SELECT * FROM Clients WHERE City NOT IN
(SELECT City FROM Clients GROUP BY City HAVING Count(*)<=10)
□ Декартово произведение таблиц и различные виды соединений рассмотрены ниже.
Выборки из нескольких таблиц
Когда в предложении FROM указаны несколько таблиц, то все они неявно считаются соединяемыми. По сути это означает, что можно получить все возможные комбинации строк (по одной из каждой таблицы), и именно с такой конкатенацией будут работать остальные операторы запроса. Эта конкатенированная таблица носит название декартово произведение или перекрестное соединение (cross join). Чаще всего пользователю нужно исключить большинство строк и выделить определенные данные, что обычно реализуется посредством установления отношений (или условий) при помощи предложения WHERE. Другой способ установить отношения – использование встроенных операций соединения, чтобы осуществить внутреннее соединение в предложении FROM. Результат этого соединения есть порожденная таблица, которая и должна обрабатываться остальными операторами запроса.
Допускаются перекрестные (CROSS), естественные (NATURAL) соединения, т.е. соединения по нескольким столбцам с одинаковыми именами, и соединения типа «объединений» (UNION). Соединение UNION и оператор UNION являются различными понятиями. Оператор UNION служит для объединения выходных данных нескольких операторов SELECT.
перекрестное соединение:
таблица A CROSS JOIN таблица B
естественное соединение:
таблица A [NATURAL] [тип соединения] JOIN таблица B
соединение объединения:
таблица A UNION JOIN таблица B
объединение посредством предиката:
таблица A [тип соединения] JOIN таблица B ON предикат
объединение посредством имен столбцов:
таблица A [тип соединения] JOIN таблица B USING (имя столбца,..)
тип соединения:
INNER|{{LEFT|RIGHT|FULL|[OUTER]}
Все эти структуры служат для того, чтобы заместить имя таблицы в предложении FROM. Хотя в перечне приведен синтаксис соединений, которые используют только две таблицы, можно соединять любое их количество. Результатом соединения является таблица, которая обрабатывается в качестве исходной остальными операторами запроса.
Ключевое слово OUTER (внешний) не является обязательным, оно употребляется только для уточнения и не используется ни в каких операциях с данными.
CROSS – перекрестное соединение. Это простое декартово произведение. Используются все комбинации строк. Пример перекрестного соединения приведен в таблице 2.2.
INNER – внутреннее соединение. Это тип соединения по умолчанию, оно используется, когда другой тип соединения не задан. Соединяются только те строки, где найдены совпадающие значения столбца. Внутреннее соединение иллюстрируется в таблице 2.3.
| TABLE A
|
TABLE B
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Таблицы 2.1.1 и 2.1.2 Две исходные таблицы для соединения |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Перекрестное соединение A CROSS JOIN B
Таблица 2.2 |
Внутреннее соединение A INNER JOIN В
Таблица 2.3 Левое внешнее соединение A LEFT OUTER JOIN В
Таблица 2.4 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
LEFT (OUTER) – левое (внешнее). Это соединение включает в себя все строки из таблицы А (совпадающие и несовпадающие) плюс совпадающие значения из таблицы В. Для строк из таблицы А, которым не найдено соответствие, значения NULL заносятся в столбцы, извлекаемые из таблицы В. Этот тип соединения иллюстрируется в таблице 2.4.
RIGHT (OUTER) – правое (внешнее). Как можно предположить, это соединение является обратным предыдущему. Другими словами, все строки из таблицы В (правой таблицы) представлены в соединении и они дополнены совпадающими строками из таблицы А, в столбцы для строк, не имеющих совпадения, заносятся значения NULL. Этот тип соединения иллюстрируется в таблице 2.5.
FULL (OUTER) – полное (внешнее). Это комбинация левого и правого соединения. Присутствуют все строки из обоих таблиц. Если строки совпадают, то они заполнены реальными значениями. В несовпадающих строках значения столбцов заполняются значениями NULL. Этот тип соединения иллюстрируется в таблице 2.6.
| Правое внешнее соединение A RIGHT OUTER JOIN <В
Таблица 2.5 |
Полное внешнее соединение A FULL OUTER JOIN В
Таблица 2.6 |
UNION – объединение. Этот тип соединения отличается от оператора UNION, который используется для объединения выходной информации нескольких запросов. Соединение типа UNION является обратным по отношению к INNER: оно включает только те строки из таблиц, для которых не найдено совпадений. В них используются значения NULL для столбцов, полученных из другой таблицы. Если взять полное внешнее соединение и удалить из него строки, полученные путем внутреннего соединения тех же таблиц, то в результате получится соединение типа «объединения» (union). Этот тип соединения иллюстрируется в таблице 2.7.
| Соединение типа объединение A UNION JOIN В
Таблица 2.7 |
Тип соединения только определяет, из каких строк будут состоять выходные данные. Другие возможные соединения могут быть напрямую заданы с помощью ON или USING. USING определяет соединение по столбцам. Если это естественное соединение, то имена столбцов и типы данных должны совпадать. USING отличается от NATURAL тем, что позволяет соединять в таблицах только заданное подмножество столбцов с совпадающими именами, a NATURAL автоматически соединяет все столбцы с одинаковыми именами.
ON определяет предикат, который представляет собой выражение, подобное Сity='Тверь', например, A.NAME=B.NAME. Результатом вычисления выражения могут быть TRUE, FALSE или UNKNOWN (если присутствует значение NULL). Значение предиката в данном случае может вычисляться с использованием значений, взятых из строки-кандидата соединения. Под строкой - кандидатом понимается строка, которая, в свою очередь, получается, как декартово произведение строк таблиц, участвующих в соединении.
INSERT
INSERT INTO имя таблицы [(столбец_1, столбец_2,...)]
VALUES (выражение_1, выражение_2,...),
или
INSERT INTO имя таблицы [(столбец_1, столбец_2,...)]
SELECT ...
Оператор INSERT вставляет новые строки в существующую таблицу. Форма данной команды INSERT ... VALUES вставляет строки в соответствии с точно указанными в команде значениями. Форма INSERT ... SELECT вставляет строки, выбранные из другой таблицы или таблиц.
Имя таблицы - таблица, в которую должны быть внесены строки. Столбцы, для которых заданы величины в команде, указываются в списке имен столбцов.
Если не указан список столбцов для INSERT ... VALUES или INSERT ... SELECT, то величины для всех столбцов должны быть определены в списке VALUES() или в результате работы SELECT.
Любой столбец, для которого явно не указано значение, будет установлен в свое значение по умолчанию. Например, если в заданном списке столбцов не указаны все столбцы в данной таблице, то не упомянутые столбцы устанавливаются в свои значения по умолчанию.
Выражение_i может относится к любому столбцу, который ранее был внесен в список значений. Например, можно указать следующее:
INSERT INTO tbl_name (col1, col2) VALUES(15, col1*2)
Но нельзя указать:
INSERT INTO tbl_name (col1, col2) VALUES(col2*2, 15)
Оператор INSERT ... SELECT обеспечивает возможность быстрого внесения большого количества строк в таблицу из одной или более таблиц.
Для оператора INSERT ... SELECT необходимо соблюдение следующего условия. Целевая таблица команды INSERT не должна появляться в утверждении FROM части SELECT данного запроса, поскольку в SQL запрещено производить выборку из той же таблицы, в которую производится вставка.
В следующем примере показано добавление в таблицу Clients новой строки о заказчике Бобров, 41 год, из г.Тула.
INSERT INTO Clients (ID_NUM, Name, City, Age)
VALUES (1225, 'Бобров', 'Тула', 41)
UPDATE
UPDATE имя таблицы
SET столбец_1 = выражение_1 [,столбец_2 = выражение_2 ...]
[WHERE условие отбора строк]
Оператор UPDATE обновляет столбцы в соответствии с их новыми значениями в строках существующей таблицы. В выражении SET указывается, какие именно столбцы следует модифицировать и какие величины должны быть в них установлены. В выражении WHERE, если оно присутствует, задается, какие строки подлежат обновлению. В остальных случаях обновляются все строки.
Если доступ к столбцу из указанного выражения осуществляется по аргументу имя столбца, то оператор UPDATE использует для этого столбца его текущее значение. Например, следующая команда устанавливает в столбце Age значение, на единицу большее его текущей величины:
UPDATE Clients SET Age = Age + 1
Если в UPDATE изменяются значения нескольких столбцов, то новые значения присваиваются слева направо, т.е. сначала столбец_1 = выражение_1, потом столбец_2 = выражение_2 и т.д.
DELETE
DELETE FROM имя таблицы
[WHERE условие отбора строк]
Оператор DELETE удаляет из таблицы, удовлетворяющие заданному условию отбора строк. Условие отбора строк формулируется по тем же правилам, что и операторе SELECT. В том числе допускается использование вложенных запросов.
В следующем примере из таблицы Clients удаляются записи о заказчиках из города Тула.
DELETE FROM Clients WHERE City = 'Тула'
Рекомендуемая литература
Бен Форта. Освой самостоятельно SQL. 10 минут на урок. 3-е издание. Изд. Вильямс. Серия: 10 минут на урок, 2005
Бхамидипати К. SQL. Справочник программиста. 2003
Вейскас Дж. Эффективная работа: Microsoft Office Access 2003. Изд. Питер. Серия: Эффективная работа, 2005
Вьейра Р. SQL Server 2000.Программирование. в 2-х томах. Изд. Бином. Серия: Лаборатория знаний, 2004
Грабер М. SQL. Справочное руководство, 2004
Гроф Дж. Р., Вайнберг П.Н. SQL: полное руководство. К.: Издательская группа BHV, 1999.
Дейт К.Дж. Введение в системы баз данных. К.; М.; СПб.: Издательский дом «Вильямс», 2000.
Дибетт П. Знакомство с Microsoft SQL Server 2005. Изд. Русская Редакция. Серия: Разработка ПО, 2005
Избачков Ю. С., Петров В. Н. Информационные системы: Учебник для вузов. 2-е издание. Изд. Питер. Серия: Учебное пособие, 2005
Крёнке Д. Теория и практика построения баз данных. 9-е издание. Изд. Питер, 2004
Кузнецов С.Д. SQL. Язык реляционных баз данных. Изд. Майор. Серия: Мой компьютер, 2001
Малыхина М.П. Базы данных: основы, проектирование, использование. Изд. Питер. Серия: Учебное пособие, 2004
MySQL AB. MySQL. Руководство администратора. Изд. Вильямс, 2005 г.
Полякова Л. Основы SQL. Серия: Основы информационных технологий, 2004
Селко Д. SQL для профессионалов. Изд. Современная экономика и право, 2004
Тейлор А.Дж. SQL для "чайников", 5-е издание. Изд. Диалектика. Серия: Для чайников, 2005
Тоу Д.. Настройка SQL. Изд. Питер. Серия: Для профессионалов, 2004
Хернандес М. и др. SQL-запросы для простых смертных. Практическое руководство по манипулированию данными в SQL. 2003
Кириллов В.В., Громов Г.Ю., Структуризированный язык запросов (SQL). http://www.citforum.ru/database/sql_kg/index.shtml
Кузнецов С.Д. Системы обработки информации - язык баз данных SQL со средствами поддержания целостности. http://www.citforum.ru/database/sql/index.shtml
Кузнецов С.Д. Язык реляционных баз данных SQL и его стандарты. ComputerWorld #4/97 http://www.citforum.ru/database/articles/art_18.shtml
Кузнецов С.Д. Введение в стандарты языка баз данных SQL. http://www.citforum.ru/database/sqlbook/index.shtml
Кузнецов С.Д. Основы современных баз данных
. Информационно-аналитические материалы Центра Информационных Технологий. Http:// www.citmgu/ru/