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

((Поставки [П№,Д№] DIVIDEBY Детали [Д№] JOIN Поставщики) [Имя_П]

. Получить номера поставщиков, которые поставляют по крайней мере все те детали, которые поставляет поставщик П2.

Поставки [П№,Д№] DIVIDEDBY (Поставки WHERE Имя_П=’П2’) [Д№]

. Получить имена поставщиков, которые не поставляют деталь Д2.

((Поставщики [П№] MINUS (Поставки WHERE Д№=’Д2’) [П№])

JOIN (Поставщики) [Имя_П]

Покажем, в качестве примера, как выглядит запись команды на выполнение последнего запроса, сделанная на языке SQL:

SELECT DISTINCT Поставщики.[Имя_П] FROM Поставщики

WHERE Поставщики.[П.№] NOT IN

(SELECT Поставки.[П№] FROM Поставки

WHERE Поставки.[Д№]=’Д2’);

Глава 2. Основы SQL

Познакомимся с синтаксисом и конструкцией языка структурированных запросов SQL (Structured Query Language). Это позволит лучше понимать процесс обработки данных и создавать эффективные запросы.- наиболее распространенный язык управления базами данных архитектуры клиент-сервер. Стандарт современной версии ANSI SQL был принят в 1992 году. Эта версия дает возможность использовать общий набор операторов в любой SQL-совместимой программе управления базами данных. Так, MS ACCESS использует версию Microsoft Jet SQL. СУБД SQL Server 7.0 использует расширенную версию SQL - Transact-SQL. Разработчики SQL-совместимых программ управления базами данных имеют право свободно расширять этот язык, если поддерживается стандартный набор основных команд, описанный в ANSI SQL-92. Мы будем уделять большее внимание версии Jet SQL.

Стандарт ANSI SQL-92 содержит около 200 ключевых слов, а в Jet SQL используется менее половины из них. Кроме того, в Jet SQL зарезервирован набор слов, не имеющих аналогов в ANSI SQL-92.

Все ключевые слова ANSI SQL, поддерживаемые используемой модификацией Jet SQL, а также зарезервированные уникальные слова, можно найти в справочных пособиях (в том числе и в системе помощи MS Access).

Возможности современных реляционных баз данных позволяют осуществлять эффективное управление данными, используя одновременно два языка - язык реляционных баз данных SQL и язык программирования VBA (Visual Basic for Applications), разработанный для программных продуктов пакета MS Office.

Команда SQL - это последовательность заданных операторами действий, выполненных в определенном порядке.

Синтаксис языка включает слова и символы, которые можно применять, а также правила, по которым эти слова и символы можно использовать при создании команд и программ. Структура команды SQL показана на следующем примере (мы продолжаем использовать базу данных Проекты-Поставщики-Детали, смоделированную в прошлых главах): Найти поставщиков из Минска.

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

Ключевые слова - операторы, условия, модификаторы, итоговые функции - имеют в SQL определенное значение и применение, которое строго регламентировано.

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

Синтаксис SQL предусматривает использование знаков пунктуации:

·        запятые используются для разделения компонентов списка параметров;

·        точки используются для отделения имен таблиц от имен полей;

·        точка с запятой ставится в конце инструкции Jet SQL;

·        квадратные скобки используются для выделения имен полей только тогда, когда в именах используются пробелы или другие знаки пунктуации, не разрешенные в SQL;

·        одинарная (предпочтительней) или двойная кавычка применяется для описания строчных переменных;

·        символы * и ? используются для маскирования окончания или одного символа соответственно;

·        символ # применяется для представления одной цифры в операторе LIKE.

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

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

Язык SQL может быть встроенным (когда команды SQL как элементы включаются в какие-нибудь программы, написанные на процедурных языках общего назначения C, Java, Pascal… или языках сценариев Perl, PHP, Python…) и/или интерактивным.

2.1 Типы данных

Категории типов данных:

·        Character string - Строки символов;

·        Bit string - Строки битов;

·        Exact numeric - Рациональные (целые и действительные) числа с плавающей десятичной точкой;

·        Approximate numeric - Вещественные числа (с плавающей точкой);

·        Date time - значения даты и времени;

·        Interval - интервалы даты и времени.

         Строковые типы данных

Character (n) - строка фиксированной длины n. Максимальная длина строки определяется конкретной СУБД. В Access - 256 символов. Если символов меньше чем n, то добавляются пробелы. Синонимы - Char(n).

Character varying (n) - строка переменной длины, длинной менее n. Максимальное n зависит от СУБД. Синонимы: Char varying, Charvar.

National Character (National Char, NChar) - совпадает с типом Char, только хранит лишь стандартизованные многобайтовые или двухбайтовые знаки (Unicode). Записывается: N'a*b'. National Character Varying - то же для строк переменной длины.

Unicode - единое множество чисел (16-разрядных), которое представляет знаки почти всех мировых языков. Содержит 65536 = 216 знаков.

В СУБД Access к строковым типам данных относятся: text и memo.

         Битовые типы данных

BIT (n) - строка фиксированной длины (фиксированные числа битов). Max длина определяется СУБД. Если длина строки меньше n, то получите сообщение об ошибке. В стоке BIT перед первой кавычкой должна стаять латинская В, например, В'01001' - это строка типа BIT(5). Bit varying - аналогично, как Charvar.

Bit - тип данных используется для хранения так называемых больших бинарных объектов (Binary Large Object - BLOB) - например, звук, изображение.

В СУБД Access к bit типу данных относятся: yes, no, binary, OLE object.

         Точные числовые типы данных

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

Имеет фиксированные точность и масштаб:

·        точность - число значащих цифр в записи числа (т.е. общее количество цифр в десятичной записи без учета десятичной точки);

·        масштаб - число цифр справа от десятичной точки (масштаб ≤ точности).

Типы:

Numeric (точность [,масштаб]) - представляет произвольное рациональное число.

Decimal - аналогичен NUMERIC, но только задает нижнюю границу точности, т. е. СУБД может выбрать большую точность, чем заказано пользователем.

Integer (или INT) - представляет произвольное целое число.

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

В СУБД Access: Decimal, Integer, byte, long integer.

Пример: Хранение числа 123,55:

         Вещественные числовые типы данных

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

FLOAT (точность) - представляет произвольное рациональное приближение действительного числа с плавающей точкой. Значение точности представляется не в количестве значащих десятичных цифр, а в количестве битов. Точность не должна быть меньше 1. Максимальная точность зависит от СУБД. Для преобразования десятеричной точности в бинарную надо умножить десятеричную точность на 3.32193. Например, 7 знаков точности дают 24 бита.

REAL - совпадает с FLOAT, но точность вводить не надо, ее автоматически определяет СУБД. Числа типа REAL называют числами одинарной точности с плавающей точкой.

DOUBLE PRECISION - вдвое превышает точность REAL - числа двойной точности с плавающей точкой.

В СУБД Access - single, double.

         Календарные типы данных

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

·        DATE - имеет формат YYYY-MM-DD.

·        TIME - имеет формат HH:MM:SS. Можно добавить аргумент “точность” для долей секунд.

·        TIMESTAMP - имеет формат YYYY-MM-DD_ HH:MM:SS.

·        B СУБД ACCESS: date/time.

·        Интервальные типы данных обозначают расстояние между датами или двумя отметками времени суток. Access не поддерживает.

         Значения NULL

Отображает отсутствующие или неизвестные данные.

·        В SQL ключевое слово NULL определяет значение Null.

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

·        Значения 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 определяют способ отбора строк: