- •Информация и данные
- •Основные понятия систем с базами данных
- •Пользователи информационной системы с БД
- •Требования к информационным системам с базами данных
- •Основные компоненты ИС с базами данных
- •Архитектура систем с базами данных. Понятие модели данных
- •Сущности и их свойства
- •Связи (отношения) между сущностями
- •Виды связей между сущностями
- •Еще о сущностях, их свойствах и связях между ними
- •Модели данных. Ранние подходы к организации баз данных
- •Основные понятия реляционной модели данных
- •Структуры данных реляционной модели. Реляционные отношения
- •Свойства отношений
- •Отсутствие в отношении одинаковых кортежей
- •Кортежи отношения не упорядочены (сверху вниз)
- •Атрибуты отношения не упорядочены (слева направо)
- •Значения всех атрибутов являются атомарными
- •Виды отношений
- •Реляционная база данных
- •Реляционная модель. Операции над данными
- •Реляционная алгебра
- •Пересечение отношений
- •Вычитание отношений
- •Декартово произведение отношений
- •Проекция
- •Выборка (ограничение)
- •Естественное соединение отношений
- •Деление
- •Реляционное исчисление
- •Примеры правильно построенных формул
- •Язык SQL
- •Отличие SQL от процедурных языков программирования
- •Формы и составные части SQL
- •Условия и терминология
- •Простейшие SELECT-запросы
- •Ограничения целостности в реляционной модели
- •Ограничения целостности уровня атрибута
- •Домены отношений
- •Отсутствующая информация или NULL-значения.
- •Ограничения целостности уровня кортежа
- •Ограничения целостности уровня отношения
- •Потенциальные, первичные, альтернативные ключи отношения
- •Потенциальные ключи и NULL-значения
- •Ограничения целостности уровня базы данных
- •Внешние ключи и NULL-значения
- •Правила ссылочной целостности
- •При обновлении кортежа в родительском отношении
- •При удалении кортежа в родительском отношении
- •При вставке кортежа в дочернее отношение
- •При обновлении кортежа в дочернем отношении
- •Средства обеспечения целостности данных в СУБД
- •Поддержка декларативных ограничений целостности в языке SQL
- •Проектирование базы данных
- •Функциональная зависимость
- •Нормализация отношений базы данных
- •Нормальные формы
- •Декомпозиция без потерь и функциональные зависимости
- •Первая и вторая нормальные формы.
- •Третья нормальная форма.
- •Многозначные зависимости и четвертая нормальная форма
- •Зависимости соединения и пятая нормальная форма
- •Итоговая схема процедуры нормализации
- •Структуры хранения данных и методы доступа
- •Хранение отношений и доступ к хранимым данным
- •Индексирование
- •Управление транзакциями и целостность баз данных
- •Транзакции и параллелизм
- •Проблемы, возникающие при параллельном выполнении транзакций
- •Проблема потери результатов обновления
- •Проблемы несовместимого анализа
- •Несовместимый анализ – неповторяемое считывание
- •Несовместимый анализ – фиктивные элементы (фантомы)
- •Собственно несовместимый анализ
- •Конфликты между транзакциями
- •Методы сериализации транзакций
- •Решение проблем параллелизма при помощи блокировок
- •Проблема потери результатов обновления
- •Проблема несовместимого анализа. Неповторяемое считывание
- •Фиктивные элементы (фантомы)
- •Собственно несовместимый анализ
- •Уровни изоляции. Объекты синхронизационных блокировок
- •Предикатные синхронизационные блокировки
- •Метод временных меток
- •Уровни изоляции.
- •Синтаксис операторов SQL, определяющих уровни изоляции
8. Язык SQL
Язык запросов к базам данных SQL появился в 70-е годы. Его прототип был разработан фирмой IBM и известен под названием SEQUEL (Structured English QUEry Language). SQL вобрал в себя достоинства реляционной модели, в частности то, что в ее основе лежит математический аппарат реляционной алгебры и реляционного исчисления, используя при этом сравнительно небольшое число операторов и простой синтаксис.
Благодаря своим качествам язык SQL стал вначале «де-факто», а затем и официально утвержденным в качестве стандарта языка для работы с реляционными базами данных, поддерживаемого всеми ведущими мировыми фирмами, действующими в области технологии баз данных. Использование выразительного и эффективного стандартного языка позволило в настоящее время обеспечить высокую степень независимости разрабатываемых прикладных программных систем от конкретного типа используемой СУБД, существенно поднять уровень и унификацию инструментальных средств разработки приложений, работающих с реляционными базами данных.
Говоря о стандарте языка SQL, следует заметить, что большинство его коммерческих реализаций имеют большие или меньшие отступления от стандарта. Это, конечно, ухудшает совместимость систем, использующих различные «диалекты» SQL. С другой стороны, полезные расширения реализаций языка относительно стандарта являются средством развития языка и со временем включаются в новые редакции стандарта.
Языку SQL посвящено большое число книг, в том числе и учебного назначения, некоторые из них приведены в списке литературы данного пособия [9-12], в частности, учебное пособие [9] специально посвящено вопросам практического изучения языка SQL. В связи с этим в данном пособии мы рассмотрим лишь важные общие особенности данного языка, имеющие значение для последующего изложения материала.
72
8.1.Отличие SQL от процедурных языков программирования
Язык SQL – относится к классу непроцедурных языков программирования. В отличие от универсальных процедурных языков, которые также могут быть использованы для работы с базами данных, язык SQL ориентирован не на записи, а на множества. Это означает следующее. В качестве входной информации для формулируемого на языке SQL запроса к базе данных используется множество кортежей-записей одной или нескольких таблицотношений. В результате выполнения запроса также образуется множество кортежей результирующей таблицы-отношения. Другими словами в SQL результатом любой операции над отношениями также является отношение. Запрос SQL задает не процедуру, т.е. последовательность действий, необходимых для получения результата, а условия, которым должны удовлетворять кортежи результирующего отношения, сформулированные в терминах входного (или входных) отношений.
8.2.Формы и составные части SQL
Существуют и используются две формы языка SQL: интерактивный SQL
и встроенный SQL.
Интерактивный SQL используется для непосредственного ввода и получения результата SQL-запросов пользователем в интерактивном режиме.
Встроенный SQL состоит из команд SQL, встроенных внутрь программ, которые обычно написаны на некотором другом языке (Паскаль, С, С++ и др.). Это делает программы, написанные на таких языках, более мощными и эффективными, обеспечивая возможность работы с помощью них с данными, хранящимися в реляционных базах данных, требуя, однако, введения дополнительных средств, обеспечивающих интерфейс SQL с языком, в который он встраивается.
И интерактивный, и встроенный SQL обычно разделяют на следующие составные части.
Язык Определения Данных – DDL (Data Definition Language), дает возможность создания, изменения и удаления различных объектов базы данных (таблиц, индексов, пользователей, привилегий и т.д.).
К числу дополнительных функций языка определения данных DDL могут быть включены также средства определения ограничений целостности данных,
73
определения порядка в структурах хранения данных, описания элементов физического уровня хранения данных.
Язык Обработки Данных – DML (Data Manipulation Language),
предоставляет возможность выборки информации из базы данных и преобразования хранящейся в ней данных.
Тем не менее, это не два различных языка, а компоненты единого SQL.
8.3.Условия и терминология
Ключевые слова – это используемые в выражениях SQL слова, которые имеют специальное назначение. Например, они могут обозначать конкретные команды SQL. Ключевые слова нельзя использовать для других целей, например в качестве имен объектов базы данных.
Операторы SQL являются инструкциями, с помощью которых производится обращение SQL к базе данных. Операторы состоят из одной или более отдельных логических частей, называемых предложениями. Предложения начинаются соответствующим ключевым словом и состоят из ключевых слов и аргументов.
Следует обратить внимание на то, что термины, используемые в языке SQL, несколько отличаются от терминов, принятых при описании реляционной модели. В частности, вместо термина отношение в нем используется термин таблица, вместо терминов кортеж и атрибут, соответственно, строка и столбец.
8.4.Выборка данных. Оператор SELECT
Простейшие SELECT-запросы
Оператор SELECT (ВЫБРАТЬ) языка SQL является самым важным и самым часто используемым оператором. Он предназначен для выборки информации из таблиц базы данных. Упрощенный синтаксис оператора SELECT выглядит следующим образом.
74
SELECT [DISTINCT] <список атрибутов>
FROM <список таблиц> [WHERE <условие выборки>]
[ORDER BY <список атрибутов>]
[GROUP BY <список атрибутов>] [HAVING <условие>]
[UNION <выражение с оператором SELECT>];
В квадратных скобках указаны элементы, которые могут в запросе отсутствовать.
Ключевое слово SELECT сообщает СУБД, что данное предложение является запросом на извлечение информации. После слова SELECT через запятую перечисляются наименования полей (список атрибутов), содержимое которых запрашивается.
Обязательным ключевым словом в предложении-запросе SELECT является слово FROM (из). За ключевым словом FROM указывается список разделенных запятыми имен таблиц, из которых извлекается информация.
Например,
SELECT NAME, SURNAME FROM STUDENT;
SQL-запрос должен заканчиваться символом «точка с запятой». Приведенный запрос осуществляет выборку всех значений полей NAME и
SURNAME из таблицы STUDENT.
Его результатом является таблица следующего вида
NAME |
SURNAME |
Иван |
Иванов |
Петр |
Петров |
Вадим |
Сидоров |
Борис |
Кузнецов |
Ольга |
Зайцева |
Андрей |
Павлов |
Павел |
Котов |
Артем |
Лукин |
Антон |
Петров |
Вадим |
Белкин |
…… |
…… |
Порядок следования столбцов в этой таблице соответствует порядку полей NAME и SURNAME, указанному в запросе, а не их порядку во входной таблице
STUDENT.
Обратим внимание на то, что получаемые в результате SQL-запроса таблицы не в полной мере отвечают определению реляционного отношения. В
75
частности в них могут оказаться дубликаты кортежей с одинаковыми значениями атрибутов.
Например, запрос: “Получить список названий городов, в которых живут студенты, сведения о которых находятся в таблице STUDENT”, можно записать в следующем виде
SELECT CITY FROM STUDENT;
Его результатом будет таблица
CITY
Орел
Курск
Москва
Брянск
Липецк
Воронеж
Белгород
Воронеж
NULL
Воронеж
……
Можно видеть, что в этой таблице могут встречаться одинаковые строки. Они выделены жирным шрифтом.
Для исключения из результата SELECT-запроса повторяющихся записей используется ключевое слово DISTINCT (отличный). Если запрос SELECT извлекает множество полей, то DISTINCT исключает дубликаты строк, в которых значения всех выбранных полей идентичны.
Введение в выражение оператора SELECT, предложения, определяемого ключевым словом WHERE (где), позволяет вводить выражение условия (предикат), принимающее значение истина или ложь для значений полей строк таблиц, к которым обращается оператор SELECT. Предложение WHERE определяет, какие строки указанных таблиц должны быть выбраны. В таблицу, являющуюся результатом запроса, включаются только те строки, для которых условие (предикат), указанное в предложении WHERE, принимает значение истина.
Пример
Написать запрос, выполняющий выборку имен (NAME) всех студентов с фамилией (SURNAME) Петров, сведения о которых находятся в таблице
STUDENT
SELECT SURNAME, NAME
FROM STUDENT
WHERE SURNAME = ‘Петров’;
76
В задаваемых в предложении WHERE условиях могут использоваться операции сравнения, задаваемые следующими операторами: = (равно), > (больше), < (меньше), >= (больше или равно), <= (меньше или равно), <> (не равно), а также логические операторы AND, OR и NOT.
Например, запрос для получения имен и фамилий студентов, обучающихся на третьем курсе и получающих стипендию (размер стипендии больше нуля) будет выглядеть таким образом
SELECT NAME, SURNAME FROM STUDENT
WHERE KURS = 3 AND STIPEND > 0;
8.5.Реализация операций реляционной алгебры средствами языка SQL. Реляционная полнота SQL
Впредыдущих разделах, посвященных рассмотрению реляционной алгебры, говорилось, что одной из важных сторон наличия в реляционной модели такого математического аппарата является возможность оценки и доказательства реляционной полноты практически используемых языков запросов к базам данных, в частности языка SQL. Для того, чтобы показать, что язык SQL является реляционно полным, нужно показать, что любой оператор реляционной алгебры может быть выражен средствами SQL. На самом деле достаточно показать, что средствами SQL можно выразить любой из примитивных реляционных операторов. Ниже приведены примеры реализации реляционных операторов с помощью языка SQL.
Оператор объединения
Реляционная алгебра: A UNION B Оператор SQL:
SELECT * FROM A
UNION
SELECT * FROM B;
Оператор пересечения
Реляционная алгебра: A INTERSECT B
77
Оператор SQL:
SELECT A.ПОЛЕ1, A.ПОЛЕ2, …,
FROM A, B
WHERE A.ПОЛЕ1=B.ПОЛЕ1 AND A.ПОЛЕ2=B.ПОЛЕ2 AND …;
или
SELECT A.* FROM A, B
WHERE A.pk =B.pk;
где A.pk и B.pk первичные ключи таблиц A и B
Оператор вычитания
Реляционная алгебра: A MINUS B Оператор SQL:
SELECT * FROM A
WHERE A.pk NOT IN (SELECT pk FROM B);
где A.pk и B.pk первичные ключи таблиц A и B
Оператор декартового произведения
Реляционная алгебра: A TIMES B Оператор SQL:
SELECT A.ПОЛЕ1, A.ПОЛЕ2, …, B.ПОЛЕ1, B.ПОЛЕ2, …
FROM A, B;
или
SELECT A.ПОЛЕ1, A.ПОЛЕ2, …, B.ПОЛЕ1, B.ПОЛЕ2, …
FROM A CROSS JOIN B;
Оператор проекции
Реляционная алгебра: A[X, Y, …, Z] Оператор SQL:
SELECT DISTINCT X, Y, …, Z FROM A;
Оператор выборки
Реляционная алгебра: A WHERE θ Оператор SQL:
SELECT * FROM A
WHERE θ;
78
Оператор θ-соединения
Реляционная алгебра: (A TIMES B) WHERE θ Оператор SQL:
SELECT A.ПОЛЕ1, A.ПОЛЕ2, …, B.ПОЛЕ1, B.ПОЛЕ2, …
FROM A, B
WHERE θ;
или
SELECT A.ПОЛЕ1, A.ПОЛЕ2, …, B.ПОЛЕ1, B.ПОЛЕ2, …
FROM A CROSS JOIN B WHERE θ;
Оператор деления
Реляционная алгебра: A(X,Y) DEVIDE BY B(Y) Оператор SQL:
SELECT DISTINCT A.X FROM A
WHERE NOT EXIST
(SELECT *
FROM B
WHERE NOT EXIST
(SELECT * FROM A A1
WHERE
A1.X=A.X AND A1.Y=B.Y));
Таким образом, приведенные выражения доказывают, что язык SQL, как и реляционная алгебра, является реляционно полным.
Следует обратить внимание на то, если в приведенных запросах в таблицах будут присутствовать NULL-значения (см. ниже раздел 9.1), то все вышеперечисленные запросы могут отработать неверно, т.к. NULL < > NULL и NULL = NULL – есть ложь.
Это, однако, не опровергает сделанного вывода о реляционной полноте SQL, так как NULL-значения реляционной моделью не поддерживаются.