Лекция 23.03.-25.03.20 Операторы определения данных

ОСНОВЫ ЯЗЫКА SQL

Лекция. Группы операторов. Типы данных

Язык реляционных БД SQL был разработан в середине 70-х годов в рамках исследовательского проекта экспериментальной реляционной СУБД System R от компании IBM. Данный проект включал в себя разработку реляционной СУБД и языка SEQUEL (Structured English Query Language). Данное название только частично отражало суть языка. Язык был ориентирован главным образом на удобную и понятную пользователям формулировку запросов к реляционной БД, фактически он уже являлся полноценным языком реляционной БД и содержал помимо операторов формулирования запросов и манипулирования БД, следующие средства:

 определения схемы БД и манипулирования ей;

 определения ограничений целостности и триггеров;

 создания представлений БД;

 определения структур физического уровня, поддерживающих эффективное выполнение запросов;

 автоматизации доступа к таблицам и их полям;

 поддержки точек сохранения транзакции и откатов.

В конце 70-х годов корпорацией Oracle был выпущен модифицированный вариант языка SEQUEL, получивший название SQL. В 1983 г. компания IBM выпустила SQL в составе СУБД DB2.

Язык SQL был настолько удачен, что несколько позже, в 1986 г. Американский национальный институт стандартизации (ANSI) принял его в качестве стандарта. После этого стандарт уже пересматривался несколько раз, в 1989, 1992 г. в результате в язык SQL были внесены некоторые незначительные изменения. В настоящее время наиболее распространенным стандартом является SQL-92.

Типы команд SQL

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

 DCL (Data Control Language) – язык управления данными. Команды языка предназначены для управления доступом к информации, хранящейся в БД. (таблица 19.1);

 DDL (Data Definition Language) – язык определения данных. Его команды используются для создания и изменения структуры объектов БД (таблица 19.2);

 DML (Data Manipulation Language) – язык манипулирования данными. Используется для манипулирования информацией, содержащейся в объектах БД (таблица 19.3);

 DQL (Data Query Language) – язык запросов к данным. Наиболее часто используемая группа, состоящая всего из одного оператора SELECT, предназначенного для формирования запросов к БД (таблица 19.4);

 TCL (Transaction Control Language) – язык управления транзакциями (таблица 19.5);

 CCL (Cursor Control Language) – язык управления курсором (таблица 19.6);

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

Таблица 19.1- Средства управления данными DCL

Оператор	Описание
ALTER DATABASE	Изменение набора основных объектов БД
ALTER DBAREA	Изменение существующей области хранения БД
ALTER PASSWORD	Изменяет пароль для всей базы данных
CREATE DATABASE	Создает новую базу данных и определяет ее основные параметры
CREATE DBAREA	Создает область хранения и делает ее доступной для размещения данных
DROP DATABASE	Удаляет БД (при наличии прав)
DROP DBAREA	Удаляет область хранения если в ней не располагаются активные данные
GRANT	Предоставляет права доступа на действия с объектами БД
REVOKE	Лишает прав доступа к объектам БД или над действиями с объектами БД

Таблица 19.2 - Операторы определения данных DDL

Оператор	Описание
CREATE TABLE	Создает новую таблицу в БД
DROP TABLE	Удаляет существующую таблицу из БД
ALTER TABLE	Изменяет структуру таблицы или ограничения таблицы
CREATE VIEW	Создает представление (виртуальную таблицу) соответствующую некоторому SQL запросу
DROP VIEW	Удаляет ранее созданное представление
ALTER VIEW	Изменяет существующее представление
CREATE INDEX	Создает индекс для некоторой таблицы
DROP INDEX	Удаляет существующий индекс

Таблица 19.3 - Операторы манипулирования данными DML

Оператор	Описание
DELETE	Удаляет одну или несколько записей согласно условиям отбора. Применение оператора согласуется с принципами поддержки ссылочной целостности, поэтому оператор не всегда выполняется корректно, даже если синтаксически записан правильно
INSERT	Вставляет одну или несколько записей, согласно условию отбора, в базовую таблицу
UPDATE	Обновляет значения одного или нескольких полей в одной или нескольких записях, соответствующих условиям отбора

Таблица 19.4 - Язык запросов к данным DQL

Оператор	Описание
SELECT	Оператор, полностью реализующий возможности реляционной алгебры. Позволяет сформировать результирующие отношение, соответствующее запросу

Таблица 19.5- Средства управления транзакциями TCL

Оператор	Описание
COMMIT	Завершает транзакцию (комплексную взаимосвязанную обработку информации, объединенную в транзакции)
ROLLBACK	Откат транзакции (отмена изменений, проведенных в ходе выполнения транзакции)
SAVEPOINT	Сохраняет промежуточную точку (состояние) БД, для реализации возможности отката

Таблица 19.6- Средства управления курсором СCL

Оператор	Описание
DECLARE	Определяет курсор для запроса
OPEN	Открывает курсор (Формирует виртуальный НД, соответствующий описанию курсора)
FETCH	Считывает очередную строку из виртуального НД открытого курсора
CLOSE	Закрывает открытый курсор
PREPARE	Готовит оператор SQL к динамическому выполнению
EXECUTE	Выполняет оператор SQL, ранее подготовленный к динамическому выполнению

Типы данных языка SQL

В языке SQL имеется шесть скалярных типов данных, определенных стандартом. Их краткое описание представлено в таблице 19.7

Таблица 19.7 – типы данных языка SQL

Тип данных	Объявления
Символьный	CHAR \| VARCHAR
Битовый	BIT \| BIT VARYING
Точные числа	NUMERIC \| DECIMAL \| INTEGER \| SMALLINT
Вещественные числа	FLOAT \| REAL \| DOUBLE PRECISION
Дата/время	DATE \| TIME \| TIMESTAMP
Интервал	INTERVAL

Строковые типы:

 CHARACTER(n) или CHAR(n) - символьные строки постоянной длины в n символов. При задании данного типа под каждое значение всегда отводится n символов, и если реальное значение занимает менее, чем n символов, то СУБД автоматически дополняет недостающие символы пробелами.

 VARCHAR(n) - строки символов переменной длины.

Битовые типы:

 ВIT(п) - строка битов постоянной длины.

 BIT VARYING(n) - строка битов переменной длины.

Точные типы:

 NUMERIC[(n,m)] - точные числа, здесь и - общее количество цифр в чис-. ле, m - количество цифр слева от десятичной точки.

 DECIMAL[(n,m)] - точные числа, здесь п - общее количество цифр в числе, m - количество цифр слева от десятичной точки.

 DEC[(n,m)] - то же, что и DECIMAl.[(n,m)].

 INTEGER или INT - целые числа.

 SMALLINT - целые числа меньшего диапазона.

Вещественные типы:

 FLOAT[(n)] - числа большой точности, хранимые в форме с плавающей точкой. Здесь n - число байтов, резервируемое под хранение одного числа. Диапазон чисел определяется конкретной реализацией.

 REAL - вещественный тип чисел, который соответствует числам с плавающей точкой, меньшей точности, чем FLOAT.

 DOUBLE PRECISION специфицирует тип данных с определенной в реализации точностью большей, чем определенная в реализации точность для REAL.

Типы даты/времени и интервал:

 DATE - календарная дата.

 TIME – формат времени.

 ТIМЕSТАМР(точность) - дата и время.

 INTERVAL - временной интервал.

Большинство коммерческих СУБД поддерживают дополнительные типы данных, которые не специфицированы в стандарте. Так, например, практически все СУБД в том или ином виде поддерживают тип данных для представления неструктурированного текста большого объема. Этот тип аналогичен типу MEMO в настольных СУБД. Называются эти типы по-разному, например в ORACLE этот тип называется LONG, в DB2 - LONG VARCHAR, в SYBASE и MS SQL Server - TEXT.

Контрольные вопросы

1. Что представляет собой язык SQL?

2. Что общего между языком SQL и реляционной алгеброй?

3. Какие средства включает в себя язык SQL?

4. Какие типы команд выделяют в языке SQL?

5. Назовите основные команды языка DML.

6. Назовите основные команды языка DDL.

7. Назовите основные команды языка DCL.

8. Назовите основные команды языка DQL.

9. Назовите основные команды языка управления транзакциями.

10. Назовите основные типы данных языка SQL.

11. Назовите строковые типы данных языка SQL.

12. Назовите числовые типы данных языка SQL.

13. Назовите типы представления даты и времени.

Лекция 23.03.-25.03.20 Операторы определения данных

Определение таблиц

Для создания и модификации объектов в языке SQL определена группа операторов DDL (Data Definition Language – язык описания данных). В группе DDL определены операторы для создания, удаления и изменения таблиц, представлений и индексов. Рассмотрим операторы для работы с таблицами.

Создание таблицы БД осуществляется оператором CREATE TABLE, имеющим следующий синтаксис:

CREATE TABLE имя таблицы (

Поле_1: тип_данных ограничения,

Поле_2: тип_данных ограничения)

Главное в команде создания таблицы – определение имени таблицы и описание набора имен полей, которые указываются в соответствующем порядке. Кроме того, этой командой оговариваются типы данных и размеры полей таблицы.

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

CREATE TABLE TOVAR

(ID INT NOT NULL PRIMARY KEY,

Name VARCHAR(50) NOT NULL,

Price MONEY NOT NULL,

Type VARCHAR(25) NOT NULL,

Sort VARCHAR(50),

City VARCHAR(50))

Ключевое слово NULL используется для указания того, что в данном столбце могут содержаться пустые значения NULL. Значение NULL отличается от пробела или нуля – к нему прибегают, когда необходимо указать, что данные неизвестны. Если указано ключевое слово NOT NULL, то будут отклонены любые попытки пропустить ввод данных в данное поле. По умолчанию стандарт SQL предполагает наличие ключевого слова NULL.

PRIMARY KEY – определяет поле первичного ключа, в некоторых реализациях СУБД может использоваться другой способ определения первичного ключа:

CREATE TABLE TOVAR

(ID INT NOT,

...

City VARCHAR(50),

PRIMARY KEY (ID));

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

PRIMARY KEY (поле_1, …, поле_n)

Структура существующей таблицы может быть модифицирована, для модификации таблицы используется команда ALTER TABLE, которая позволяет: добавлять и удалять поля, переопределять первичные и внешние ключи и переопределять ограничения. Объявление начинается с команды ALTER TABLE Имя_таблицы, а далее следует объявление модификации. Внутри команды может быть только одна модификация. Возможные модификации:

ALTER COLUMN Имя_поля SET DEFAULT Выражение

ALTER COLUMN Имя_поля DROP DEFAULT

ADD Имя_поля

DROP COLUMN Имя_поля

ADD CONSTRAINT Имя_ограничения

DROP CONSTRAINT Имя_ограничения

Например, добавим к таблице товар новое поле – единица (Unit):

ALTER TABLE TOVAR

ADD Unit VARCHAR(10) NOT NULL

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

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

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

ALTER TABLE TOVAR

ALTER COLUMN City CHAR(30) NOT NULL

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

ALTER TABLE TOVAR

ADD CONSTRAINT FK_Сategory

FOREIGN KEY (ID_Сategory)

REFERENCES Сategory

Удаление таблицы осуществляется командой DROP TABLE, при этом используется простой и понятный синтаксис:

DROP TABLE TOVAR

Оператор DROP TABLE дополнительно позволяет указывать, следует ли операцию удаления выполнять каскадом. Если в операторе указано ключевое слово RESTRICT, то при наличии в базе данных хотя бы одного объекта, существование которого зависит от удаляемой таблицы, выполнение оператора DROP TABLE будет отменено. Если указано ключевое слово CASCADE, автоматически удаляются и все прочие объекты базы данных, чье существование зависит от удаляемой таблицы, а также другие объекты, зависящие от удаляемых объектов. Общий эффект от выполнения оператора DROP TABLE с ключевым словом CASCADE может оказаться весьма ощутимым, поэтому подобные операторы следует использовать с максимальной осторожностью.

Определение индексов

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

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

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

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

CREATE [ UNIQUE ] INDEX имя_индекса

ON имя_таблицы (имя_столбца[ASC|DESC][,...n])

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

операторе указано ключевое слово UNIQUE, уникальность значений ключа индекса будет автоматически поддерживаться системой. Требование уникальности значений обязательно для первичных ключей, а также возможно и для других столбцов таблицы (например, для альтернативных ключей). Хотя создание индекса допускается в любой момент, при его построении для уже заполненной данными таблицы могут возникнуть проблемы, связанные с дублированием данных в различных строках. Следовательно, уникальные индексы (по крайней мере, для первичного ключа) имеет смысл создавать непосредственно при формировании таблицы. В результате система сразу возьмет на себя контроль за уникальностью значений данных в соответствующих столбцах. Пример создания индекса для поля «Название товара»:

CREATE INDEX New_index

ON TOVAR (Name)

Если созданный индекс впоследствии окажется ненужным, его можно удалить с помощью оператора DROP INDEX:

DROP INDEX New_index

Контрольные вопросы

1. Какие операторы определения данных вы знаете?

2. Какой синтаксис имеет оператор создания таблицы?

3. Что такое NOT NULL и NULL?

4. Какие проблемы могут быть при добавлении в таблицу поля с ограничением NOT NULL?

5. Как изменить имя поля таблицы?

6. Как удалить таблицу БД?

7. Что такое индекс и в чем его назначение?

8. Как объявить индекс?

9. Как удалить индекс?

Задание на 31.03.2020

1. Изучить Лекцию. Структура оператора SELECT

2. Выполнить конспект – Структура оператора SELECT

a. Подготовить отчет по контрольным вопросам в формате PowerPoint.

b. Отчет отправить по адресу annaerem@mail.ru

Лекция 31.03.2020. Структура оператора SELECT

Язык запросов строится на единственном операторе SELECT, используемом чаще всех операторов языка SQL. Он производит выборки данных из таблиц БД и предоставляет их пользователям в необходимом виде. Практически SELECT реализует всю мощь реляционной алгебры.

В наиболее общем виде он имеет следующий формат:

SELECT [DISTINCT или ALL] <имена полей возвращаемых запросом>

FROM <имена таблиц используемых в запросе>

WHERE <условия отбора записей>

GROUP BY <имена группируемых полей>

HAVING <условия отбора>

UNION <оператор select>

PLAN <план выполнения запроса>

ORDER BY <список полей сортировки>

SELECT - ключевое слово, которое сообщает СУБД, что эта команда - запрос. Все запросы начинаются этим служебным словом с последующим пробелом. За ним может следовать способ выборки - с удалением дубликатов записей (DISTINCT) или без удаления (ALL, по умолчанию). Затем через запятую следует список полей включаемых в результат запроса. Может использоваться символ «*» (звездочка) означает, что в результирующий набор включаются все поля из исходных таблиц или из указанной таблицы, например Товары.* (из таблицы товары выбираются все поля). При необходимости поля таблиц можно переименовать, для этого используется оператор AS.

Раздел FROM - используется совместно с SELECT, должен присутствовать в каждом запросе. В нем, через запятую, перечисляются используемые в запросе таблицы. В случае если таблиц несколько, то запрос неявно выполняет декартово произведение. Таблицам можно присвоить имена-псевдонимы, что бывает полезно для осуществления операции соединения таблицы с самой собою или для доступа из вложенного подзапроса к текущей записи внешнего запроса. Все последующие разделы оператора SELECT являются необязательными.

Если необходимо из таблицы А (таблица 6.1) выбрать только записи в необходимых полях (Фамилия, Зарплата), т.е. выполнить проекцию то можно записать следующий код (результат - таблица 7.2):

SELECT A.Фамилия, A.Зарплата FROM А

Полностью вся таблица будет получена в следующем случае:

SELECT * FROM А

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

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

• Предикаты сравнения { =, <>, >, <, >=, <= }, которые имеют традиционный смысл, например, следующий код языка SQL использованный для таблицы 6.1 даст результат, приведенный в таблице 7.1:

SELECT * FROM А

WHERE Зарплата<3000

• Предикат Between A and В - принимает значения между А и В. Предикат истинен, когда сравниваемое значение попадает в заданный диапазон, включая границы диапазона. Одновременно в стандарте задан и противоположный предикат Not Between A and В, который истинен тогда, когда сравниваемое значение не попадает в заданный интервал, включая его границы. Например:

SELECT * FROM А

WHERE Зарплата BETWEEN 2000 and 3000

В результате выполнения этого запроса будут возвращены все записи, у которых значения поля «Зарплата» находятся в интервале от 2000 до 3000, включительно.

• Предикат вхождения во множество - IN (множество) истинен тогда, когда сравниваемое значение входит во множество заданных значений. При этом множество значений может быть задано простым перечислением или встроенным подзапросом. Одновременно существует противоположный предикат NOT IN (множество), который истинен тогда, когда сравниваемое значение не входит в заданное множество.

SELECT * FROM А

WHERE Фамилия IN(‘Иванов’, ‘Петров’)

В результирующий набор данных будут включены те записи, для которых значения поля «Фамилия» совпадут с элементами множества определенными предикатом сравнения IN, т.е. записи о сотрудниках: Иванов и Петров.

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

o символ подчеркивания (_) - для обозначения любого одиночного символа;

o символ процента (%) - для обозначения любой произвольной последовательности символов;

o остальные символы, заданные в шаблоне, обозначают самих себя.

SELECT * FROM А

WHERE Фамилия LIKE ‘П%’

В результате выполнения запроса будут получены все записи, значения поля «Фамилия» для которых совпадет с шаблоном ‘П%’, т.е. Все фамилии сотрудников начинающиеся с символа «П».

• Предикат сравнения с неопределенным значением IS NULL. Понятие неопределенного значения было внесено в концепции БД позднее. Неопределенное значение интерпретируется как значение, неизвестное на данный момент времени. При сравнении неопределенных значений не действуют стандартные правила сравнения: одно неопределенное значение никогда не считается равным другому неопределенному значению. Для вы явления равенства значения некоторого поля неопределенному применяют специальные стандартные предикаты: <имя поля>IS NULL и <имя поля > IS NOT NULL.

• Предикаты существования EXIST и не существования NOT EXIST. Эти предикаты относятся к встроенным подзапросам.

Раздел ORDER BY определяет список полей сортировки. Последовательность перечисления полей имеет важное значение и определяет порядок сортировки в результирующем отношении. Например, если первым полем списка будет указана Фамилия, а вторым Номер группы, то в результирующем отношении сначала будут собраны в алфавитном порядке студенты, и если найдутся однофамильцы, то они будут расположены в порядке возрастания номеров групп. Приведем простой пример сортировки записей по полю «Фамилия»:

SELECT * FROM А

ORDER BY Фамилия

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

Таблица 21.1 – Основные агрегатные (итоговые) функции

Функция Результат

COUNT Количество строк или непустых значений полей, которые выбрал запрос

SUM Сумма всех выбранных значений данного поля

AVG Среднеарифметическое значение всех выбранных значений данного поля

MIN Наименьшее из всех выбранных значений данного поля

MAX Наибольшее из всех выбранных значений данного поля

В разделе HAVING задаются предикаты-условия, накладываемые на каждую группу, и имеет тот же синтаксис, что и раздел WHERE. Другими словами раздел HAVING используется при группировке вместо раздела WHERE.

Агрегатные функции можно использовать без группировки, если вся таблица будет рассматриваться как одна группа и результатом запроса будет одна строка. Например, требуется определить количество экземпляров книг библиотеки, в этом случае запрос будет иметь вид:

SELECT COUNT(*) AS Количество

FROM Экземпляры;

Если требуется определить количество экземпляров книг имеющихся сейчас в библиотеке, тогда запрос примет вид:

SELECT COUNT(*) AS Количество

FROM Экземпляры

GROUP BY Наличие

HAVING Наличие = Да;

Контрольные вопросы

1. В чем связь реляционной алгебры и оператора SELECT?

2. Назовите формат использования оператора SELECT в общем виде.

3. Как из результатов запроса исключить повторяющиеся записи?

4. Как накладываются условия на отбираемые записи?