Материал: Реляционная алгебра. Основы SQL

·        Значения NULL можно найти и идентифицировать предложением IS NULL.

·        Значения NULL не равны друг другу. Поэтому нельзя определить, соответствует ли какое-нибудь значение NULL другому значению в базе данных. Тем не менее, предложение DISTINCT воспринимает все NULL одинаково для удаления повторяющихся строк.

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

·        Значения NULL размножаются в процессе вычислений.

·        Итоговые функции игнорируют NULL в процессе вычислений.

·        При группировке предложением GROUP BY все значения NULL будут помещены в одну группу.

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

1.      Операторы определения данных - определяют содержимое реляционной базы данных в виде таблиц и представлений;

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

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

2.2 Создание и обслуживание таблиц

Базовые отношения создаются оператором CREATE TABLE. Следует указать: название таблицы, название столбцов, тип данных для столбцов. Рекомендуется описание каждого столбца начинать с новой строки (не обязательно). Ограничения на элементы столбца:

·        NOT NULL - не разрешает присваивать значения NULL;

·        DEFAULT - задает значения по умолчанию;

·        PRIMARY KEY - задает первичный ключ для таблицы;

·        FOREIGN KEY - задает внешний ключ;

·        UNIQUE - не позволяет вводить в столбец повторяющиеся значения;

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

Пример:

CREATE TABLE Проекты

(Пр№ CHAR(3)  NOT NULL PRIMARY KEY,

Имя_Пр CHAR(15)     UNUQUE,

Гор CHAR(20));

Внешний ключ создается ключевыми словами FOREIGN KEY или REFERENCES в строке описания столбца в команде CREATE TABLE:

CREATE TABLE Поставки

(П№ CHAR(3)    NOT NULL REFERENCE Поставщики,

Пр№ CHAR(5)   NOT NULL REFERENCE Проекты,

Д№ CHAR(3)               NOT NULL REFERENCE Детали,

Кол INTEGER DEFAULT ′???′,KEY (П№ , Пр№, Д№));

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

реляционный запрос sql данные

[CONSTRAINT имя_ограничения] REFERENCES имя_табл (имя_столбца).

Сохранение целостности данных по ссылкам организуется при помощи операторов RESTRICT, CASCADE и SET NULL:

CREATE TABLE Поставки

(П№ CHAR(3)    REFERENCE Поставщики ON UPDATE CASCADE

ON DELETE SET NULL,

Пр№ CHAR(5)   NOT NULL REFERENCE Проекты RESTRICT,

Д№ CHAR(3)               NOT NULL REFERENCE Детали,

Кол INTEGER DEFAULT ′???′,KEY (П№ , Пр№, Д№));

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

Для обеспечения целостности атрибута предназначен оператор CHECK:

CREATE TABLE Детали

         (Д№ CHAR(3)     NOT NULL PRIMARY KEY,

         Имя_Д CHAR(15)        UNUQUE,

         Цвет CHAR(10) CHECK (Цвет='Черный' OR Цвет ='Красный'           OR Цвет                             ='Желтый' OR Цвет ='???'),

         Вес INTEGER,

         Гор CHAR(20));

Ограничения на данные можно организовать и созданием доменов:

CREATE DOMAIN Color CHAR(10) CHECK (Color='Черный' OR Color='Красный'                    OR Color='Желтый' OR Color='???');

Тогда ограничение на поле «Цвет» вводится следующим образом:

CREATE TABLE Детали

         (Д№ CHAR(3)...,

         Цвет Сolor,

         …);

Внести изменение в структуру таблицы можно оператором ALTER с указанием характера изменения - ADD, MODIFY или DELETE:

ALTER TABLE Детали ADD [Дата изготовления] DATE;

Для удаления объектов базы данных используется оператор DROP:

DROP TABLE Детали;

Доступ к данным в многопользовательской системе регулируется с помощью операторов GRANT и REVOKE. Указываются вид полномочий (SELECT, UPDATE, ALL), таблица или представление, по отношению к которым создается полномочие, и имя пользователя:

GRANT UPDATE ON Поставки TO USER1;

Полномочия для всех пользователей устанавливаются через специального пользователя PUBLIC:

GRANT SELECT ON Поставки TO PUBLIC;

Для повышения безопасности полезно открывать доступ к представлениям, а не базовым отношениям.

Оператор REVOKE аннулирует полномочия:

REVOKE UPDATE ON Поставки FROM USER1;

Если аннулировать полномочия через ...FROM PUBLIC, то полномочий лишаются все пользователи.

2.3 Запрос на выборку

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

SELECT [ALL/DISTINCT] [TOP n [PERCENT]] список полейимена таблиц

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

[ORDER BY столбцы сортировки [ASC/DESC]];

В списке полей команды SELECT указываются поля, которые должны быть включены в результирующую таблицу запроса и их имена в этой новой таблице (представлении). В этом случае SELECT выполняет операцию проекции, а условие отбора WHERE - операцию выборки). Имена полей разделяются запятыми. Необязательные параметры ALL и DISTINCT определяют способ отбора строк:

·        ALL - включает все строки, соответствующие указанным далее условиям отбора;

·        DISTINCT (ключевое слово из ANSI SQL-92) - исключает строки с повторяющимися данными на основе только данных результирующего набора записей;

Необязательный параметр TOP n [PERCENT] ограничивает количество записей в результирующей таблице первыми n или n% набора.

Оператор FROM определяет имена таблиц, из которых должны выбираться данные.

WHERE определяет условие для отбора записей и реализует реляционную операцию выборки. Условие задается текстовым оператором типа LIKE для текстовых полей или числовыми операторами типа >, <, =, < >, >=, BETWEEN для числовых полей. Если WHERE не использован, то запрос возвратит все записи, удовлетворяющие критерию SELECT.

Модификатор ORDER BY определяет порядок сортировки записей в созданной таблице. Ключевыми словами ASC и DESC можно определить сортировку по возрастанию или убыванию соответственно.

Пример инструкции запроса на выборку:

Результатом выполнения запроса будет новая таблица, содержащая два столбца Имя_Д и Вес и множество строк, удовлетворяющих условию Вес>500, отсортированных по убыванию веса.

2.4 Статистические функции

Статистические или агрегатные функции (итоговые в реляционной алгебре) используются тогда, когда необходимо определить статистические данные (сумму, среднее, минимальное, максимальное и т.п.) группы записей с общим значением атрибута. Для этого используется инструкция GROUP BY:

SELECT статистическая функция (имя поля) AS заголовок поля [, список полей]

FROM имена таблиц

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

GROUP BY условие группировки

[HAVING условие для результата]

[ORDER BY столбцы сортировки];

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

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

Примеры команд SQL, применяющих статистические функции:

Общее количество деталей можно получить следующим образом:

SUM(Поставки.Кол) FROM Поставки;

В предложении SELECT можно указывать несколько скалярных выражений:

SELECT MIN(Кол), MAX(Кол), SUM(Кол), AVG(Кол) FROM Поставки;

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

Количество кортежей в отношении можно посчитать следующим образом:

SELECT COUNT (*) AS Кол_кортежей FROM Поставки;

В результате получим таблицу из одной строки с заголовком “Кол_кортежей”.

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

SELECT Пк.ПN, SUM(Пк.Кол)Поставки AS ПкBY Пк.ПN;

(Здесь показан пример использования псевдонимов, которые вводятся в предложении FROM при помощи оператора AS (его можно и пропустить). Использование псевдонимов упрощает запись команд. Они также используются и для организации некоторых запросов.)

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

На создаваемые оператором GROUP BY результаты можно накладывать ограничения оператором HAVING, например:

SELECT Пк.ПN, SUM (Пк.Кол)Поставки AS ПкBY Пк.ПNCOUNT(*)>2;

Предложение HAVING COUNT(*)>2 выделяет только те группы, в которых количество кортежей больше 2 (поставщики выполнили более 2 поставок).

2.5 Создание соединений

Реляционная операция произведения двух отношений TIMES реализуется в SQL, если указать имена этих отношений в предложении FROM:

* FROM Проекты, Поставки;

Если дополнить эту команду предложением WHERE, сравнивающим значения атрибутов этих отношений, то будет реализована реляционная операция соединения JOIN: Например, соединение отношений Проекты и Поставки:

* Проекты, ПоставкиПроекты.ПрN=Поставки.ПрN;

Подобным образом можно соединить произвольное число отношений:

SELECT DISTINCT П.Имя_П, Д.Имя_Д, Пр.Имя_Пр, Пк.КолПоставщики П, Детали Д, Проекты Пр, Поставки Пк

WHERE Д.ДN=Пк.ДN

AND П.ПN=Пк.ПN

AND Пр.ПрN=Пк.ПрN

AND Пк.Кол>500;

Такое соединение выполняется с конца. Сначала из таблицы Поставки удаляются строки со значениями поля Кол менее или равным 500. Затем соединяются кортежи отношения Поставки с теми кортежами отношения Проекты, у которых совпадают значения атрибута ПрN. После этого кортежи созданного представления соединяются с кортежами отношения Поставщик, у которых совпадают значения атрибута ПN. Далее, полученные кортежи соединяются с кортежами отношения Детали, у которых совпадают значения атрибута ДN.

Для организации соединений между таблицами и их объединения может быть использован и оператор JOIN…ON, который указывает на подключаемую таблицу и связь между полями:

SELECT список полей

FROM имя таблицы {INNER/LEFT/RIGHT} JOIN связанная таблица

ON условие связи

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

[ORDER BY столбцы сортировки];

В приведенной инструкции показано, как оператор JOIN окружен именами двух связываемых таблиц, причем вместо правого имени может использоваться повторно конструкция JOIN … ON, называемая вложенной: [имя таблицы {INNER/LEFT/RIGHT} JOIN связанная таблица ON условие связи]. В этом случае первая таблица соединяется с соединением второй и третьей таблиц. Инструкция SQL может содержать набор нескольких вложенных конструкций JOIN … ON. Их число обычно равно общему количеству таблиц, включенных в запрос, минус один. Перед оператором JOIN должен быть указан тип соединения:

·        INNER - соединяет записи из двух таблиц, если связующие поля этих таблиц содержат одинаковые значения;

·        LEFT (RIGHT) - соединяет записи исходных таблиц, причем левое внешнее соединение включает все записи из первой (левой) таблицы и присоединяет к ним записи из второй таблицы, если связующие поля содержат одинаковые значения. Правое внешнее соединение включает все записи из второй (правой) таблицы и присоединяет к ним записи из первой таблицы, если связующие поля содержат одинаковые значения.

Конструкция ON условие связи позволяет описать два поля и связь между ними (одно поле в таблице связанная таблица, второе - в таблице имя таблицы). В выражении условие связи присутствует оператор сравнения значений полей, который возвращает значения True или False. Если значение выражения True, то объединенная запись включается в результирующий набор.

Пример инструкции на соединение отношений Проекты и Поставки базы данных Проекты-Поставщики-Детали по значениям полей ПрN, присутствующим в обоих отношениях: