Слайд 1§ 7. Обзор основных операторов структурированного языка запросов к БД
SQL
1. Общие сведения о языках запросов к реляционным БД
2. Структура
языка SQL и его типы данных. Понятие представления
Основные операторы языка SQL
Слайд 21. Общие сведения о языках запросов к реляционным БД
Запрос представляет собой специальным образом описанное требование, определяющее состав производимых
на БД операций по выборке, удалению или модификации хранимых данных.
Для подготовки запросов чаще всего используются два основных языка описания запросов: QBE и SQL.
Слайд 3Главное отличие между этими языками заключается в способе формирования запросов:
язык QBE предполагает ручное формирование запроса, в то время как
использование языка SQL означает программирование запроса.
Теоретической основой языка QBE является реляционное исчисление с переменными-доменами. Этот язык позволяет формировать сложные запросы к БД путем заполнения предлагаемой СУБД запросной формы. Такой способ формирования запросов обеспечивает высокую наглядность и не требует указания алгоритма выполнения операции. Достаточно описать образец ожидаемого результата (откуда и название языка – запрос по образцу).
Слайд 4Для того чтобы узнать имена доступных отношений БД, в языке
QBE предусмотрен запрос на выборку имен отношений. Имена атрибутов исходного
отношения могут вводиться в шаблон вручную или автоматически. Во втором случае используется запрос на выборку имен атрибутов.
В современных СУБД, например, в ACCESS (диалоговые окна) и VISUAL FOX PRO, многие действия по подготовке запросов с помощью языка QBE выполняются визуально с помощью мыши («протаскиванием» мышью имени атрибута одного отношения к атрибуту другого).
Первый вариант языка QBE был предложен в 1975-1977 г. М. М. Злуффом.
Слайд 5Краткая история стандартизации языка SQL
Деятельность по стандартизации языка
SQL началась практически одновре- менно с появлением его первых коммерческих
реализаций. В 1982 г. комитету по БД Американского национального института стандар- тов (ANSI) было поручено разработать спецификацию стандартного языка реляционных БД.
Слайд 6Первый документ из числа проектов стандарта датирован октябрем 1985 г.
и является уже не первым проектом стандарта ANSI. Стандарт был
принят ANSI в 1986 г., а в 1987 г. одобрен Международной организацией по стандартизации (ISO). Этот стандарт языка SQL принято называть SQL/86.
К 1989 г. стандарт SQL/86 был несколько расширен, был подготовлен и принят следующий стандарт, получивший название ANSI/ISO SQL/89.
Слайд 7Осознавая неполноту стандарта SQL/89, на фоне завершения разработки этого стандарта
специалисты различных компаний начали работу над стандартом SQL2. Эта работа
также длилась несколько лет, было выпущено много проектов стандарта, пока, наконец, в марте 1992 г. не был принят окончательный проект стандарта SQL/92. Этот стандарт стал существенно полнее стандарта SQL/89 и охватывал практически все аспекты, необходимые для реализации приложений: манипулирование схемой БД, динамический SQL, управление транзакциями и сессиями (сессия - это последовательность транзакций, в пределах которой сохраняются временные отношения), и др.
Слайд 8В 1995 г. стандарт был дополнен спецификацией интерфейса уровня вызова
(Call-Level Interface – SQL/CLI). SQL/CLI представляет собой набор спецификаций интерфейсов
процедур, вызовы которых позволяют выполнять динамически зада-ваемые операторы SQL. Интерфейсы проце- дур определены для всех основных языков программирования: C, Ada, Pascal и др. Стандарт SQL/CLI послужил основой для создания повсеместно распространенных сегодня интерфейсов ODBC (Open Database Connectivity) и JDBC (Java Database Connec-tivity). По сути дела SQL/CLI представляет собой альтернативу динамическому SQL.
Слайд 9В 1996 г. к стандарту SQL/92 был добавлен еще один
компонент SQL/PSM (Persistent Stored Modules). Основная цель этой спецификации состоит
в том, чтобы стандартизиро-вать способы определения и исполь-зования хранимых процедур, то есть программ, включающих операторы SQL, которые сохраняются в БД, могут вызываться приложениями и выполняются внутри СУБД.
Слайд 10Незадолго до завершения работ по определению стандарта SQL2 была начата
разработка стандар-та SQL3, которую частично уда-лось завершить только в 1999
г., и по этой причине стандарт получил название SQL:1999 (самые первые проекты SQL3 занимали около 1500 страниц). В 1999 г. были приняты пять первых частей стандарта SQL:1999.
Слайд 11Первая часть стандарта посвящена описанию концептуальной структуры стандарта; вторая –
образует базис стандарта; третья – уточняет по сравнению с SQL/92
спецификацию SQL/CLI; в четвертой части специ-фицируется SQL/PSM – синтаксис и семантика языка определения храни-мых процедур; в пятой части опреде-ляются правила связывания SQL для стандартных версий языков програм-мирования FORTRAN, PASCAL и др.
Слайд 12В конце 2003 г. был принят и опубликован новый вариант
Международного стандарта SQL:2003, который существенно отлича-ется от стандарта SQL:1999, и
состоит из 9 частей. Особо следует отметить новые части – с номерами 13 и 14 (некоторые час-ти по сравнению со стандартом SQL:1999 перестали существовать). Часть 13 посвя-щена использованию подпрограмм и типов SQL в языке программирования Java. Часть 14 посвящена спецификации языковых средств, позволяющих работать с XML-документами в среде SQL.
Слайд 132. Структура языка SQL и его типы данных. Понятие представления
Опишем базовые механизмы языка SQL. Язык SQL, соответствующий последним
стандартам, представляет собой богатый и сложный язык. Поэтому приходится разбивать язык на уровни такие, что каждый уровень языка включает все конструкции, входящие в более низкие уровни. Возможны различные способы разбиения языка на уровни. Один из способов разбиения языка SQL на уровни имеет следующий вид.
Слайд 14
Разделение языка SQL на уровни
Прямой SQL
Встроенный SQL
Динамический SQL
Прямой
SQL (di-rect) содержит кон-струкции, которые можно использо-вать при прямом взаимодействии
ко-нечного пользова-теля с СУБД (напри-мер, в интерактив-ном режиме).
Слайд 15 Встроенный (embedded) уровень расширяется конструкциями, позволяющи-ми использовать возможности прямого
SQL в программах, написанных на традицион-ных языках программирования.
На уровне
динамического (dynamic) языка SQL во встраиваемый SQL добавляются конструкции, позволяющие приложениям обращаться к СУБД с конструкциями прямого SQL, которые динамически образуются во время выполнения программы.
Слайд 16Типы данных в языке SQL
Данные, хранящиеся в
столбцах таблиц SQL-ориентированной БД, являются типизиро-ванными, то есть представляют собой
значения одного из типов данных, предопределенных в языке SQL или определяемых пользователями путем применения соответствующих средств языка. Для этого при определении отношения каждому его атрибуту назначается некоторый тип данных (или домен), и в дальнейшем СУБД должна следить за тем, чтобы в каждом столбце каждой строки каждого отношения присутствовали только допустимые значения.
Слайд 17Категории типов данных
Точные целочисленные типы (INTEGER, SMALLINT).
Точные типы, допускающие наличие
дробной части (NUMERIC (p, s), где p задает точность значений,
а s – число десятичных цифр в дробной части.
DECIMAL (p, s), DECIMAL (p), DECIMAL .
Литералы типов точных чисел, представляемые в виде строк символов, изображающих десятичные числа со знаком или без знака, допускается внутри числа разделительная точка).
Слайд 18
Приближенные числовые типы
REAL – числа с плавающей точкой одинарной точности
(точность опре-деляется конкретной реализацией).
DOUBLE PRECISION – числа с плаваю-щей точкой
двойной точности.
FLOAT(p) – параметрический тип (точ-ность задается параметром p).
Литеральное выражение вида xEy.
Слайд 19Типы символьных строк
Тип CHARACTER (x) (или CHAR) - значе-ниями являются
символьные строки (на-бор символов ASCII).
Тип CHARACTER VARYING (x), где x
– ко-личество символов в строке.
Тип CHARACTER LARGE OBJECT – предназначен для определения столбцов, хранящих большие и разные по размеру группы символов.
Литералы символов строк, заключен-ные в одинарные или двойные кавычки.
Слайд 20Операции над символьными типами
Операция конкатенации (обозначает-ся «||») - соединение строк.
Функция
выделения подстроки (обоз-начается SUBSTRING) принимает три аргумента – строку, номер
начальной позиции и длину.
Функция APPER возвращает строку, в которой все строчные буквы строки-ар-гумента заменяются прописными.
Слайд 21 Функция LOVER заменяет в заданной строке все прописные
буквы строчными.
Функция определения длины (CHARA-CTER_LENGTH, OCTET_LENGTH, BIT_-LENGTH) возвращает длину
заданной символьной строки в символах, октетах или битах в виде целого числа.
Функция определения позиции (POSITION) определяет первую позицию в строке S, с которой в нее входит заданная строка S1 (если не входит, возвращается нуль).
Слайд 22Типы даты и времени
Тип DATE. Значения этого типа состоят из
компонентов-значений года (4 де-сятичных цифры), месяца (2 десятич-ные цифры от
01 до 12) и дня некоторой даты (2 десятичные цифры от 01 до 31).
Литералы типа DATE представляются в виде строки «’yyyy-mm-dd’», напри-мер, литерал ‘1949-04-08’ означает да-ту 8 апреля 1949 г.
Слайд 23 Тип TIME. Значения этого параметризо-ванного типа состоят из компонентов-значений
часа (2 десятичные цифры от 00 до 23), минуты (2
десятичные цифры от 00 до 59) и секунды (2 десятичные цифры от 00 до 59, но может включать и дополнительные цифры, представляю-щие доли секунды).
Существуют и другие типы даты и времени: типы временной метки, типы времени и временной метки с временной зоной, типы временных интервалов.
Слайд 24Булевский тип
При определении атрибута булевского типа указывается просто
спецификация BOOLEAN. Булевский тип состоит из трех значений: true, false
и unknown (соответствующие литералы обознача-ются TRUE, FALSE и UNKNOWN). Поддерживается возможность построе-ния булевских выражений, которые вы-числяются в трехзначной логике.
Слайд 25Другие типы данных в SQL
Типы коллекций: типы массивов ARRAY (в
стандарте SQL:1999) и типы мультимножеств MULTISET (стандарт SQL:2003).
Анонимные строчные типы
ROW.
Типы, определяемые пользователем – структурные типы и индивидуальные типы.
Ссылочные типы.
Слайд 26Замечание 1
В SQL для СУБД FOX PRO тип
данных атрибутов обозначается одной из букв: C – символьный, M
– примечания, D – дата, L – логический, F–действи-тельное число с плавающей точкой, N – числовой.
Слайд 27Понятие представления
В результате выборки данных из одного или
нескольких отношений может быть получено множество кортежей, назы-ваемое представлением.
Представление по существу является таблицей, формируемой в результате выполнения запроса.
Слайд 28 Преимущество использования представ-лений по сравнению запросами к основной
таблице состоит в том, что представление будет модифицировано автоматически каждый
раз, когда таблица, лежащая в его основе, изменяется. Содержание пред-ставления не фиксировано, и обновляется каждый раз, когда на него ссылаются в команде.
Для удобства работы с представлениями в язык SQL введено понятие курсора.
Слайд 29Описание и использование курсора
Курсор представляет собой своеобраз-ный
указатель, используемый для перемещения по наборам кортежей при их обработке.
В описательной части программы вы-полняют связывание переменной типа курсор (CURSOR) с оператором языка SQL (обычно с оператором SELECT).
Слайд 30В выполняемой части программы производится открытие курсора (OPEN), перемеще-ние
курсора по кортежам (FETCH ), сопровождаемое соответствующей обработкой и,
на-конец, закрытие курсора (CLOSE <имя курсора>).
Слайд 313. Основные операторы языка SQL
Опишем минимальное подмножество языка
SQL, опираясь на его реализа-цию в стандартном интерфейсе ODBC (совместимость
открытых БД) фирмы MICROSOFT (стандарт ISO SQL/92 и ANSI SQL/92). Операторы языка SQL можно условно разделить на два подъязыка: язык определения данных (DDL) и язык манипулирования данными (DML).
Слайд 33Формат операторов подъязыка DDL
1. CREATE TABLE
( < имя_столбца > < тип данных > [NOT NULL]
[, < имя_столбца > < тип данных > [NOT NULL ]]…)
В СУБД FOX PRO вместо CREATE TABLE пишут CREATE DBF .
Слайд 34Конструкция NOT NULL служит дополнительным правилом контроля вводимых значений и
для столбца означает, что в нем должно быть определено не
пустое значение. В некоторых СУБД в качестве NOT NULL указывается размер поля в символах.
Слайд 35Пример 1
CREATE TABLE STUDENTS
(SNUM INTEGER,
SFAM CHAR (20),
SIMA CHAR (10),
SOTCH CHAR
(15),
STIP DECIMAL);
Создано отношение с именем «Студенты» и атрибутами
соот-ветственно «Номер студенческого биле-та», «Фамилия», «Имя», «Отчество» студента и «Размер получаемой стипен-дии».
Слайд 36Замечания 2 и 3
Порядок расположения атрибутов в отношении определяется тем,
в какой последовательности они указаны в команде создания отношения.
Кроме создания
отношения в SQL/92 аналогичной командой CREATE SCHEMA можно создавать схемы с необязательным указанием автора, а также можно создать домен – CREATE DOMAIN.
Слайд 372. Оператор удаления отношения
Оператор удаления отношения имеет формат
DROP TABLE
Перед удалением отношения необ-ходимо убедиться
в том, что оно не ссылается на другое отношение и не используется в каком-либо пред-ставлении. Кроме того, чтобы удалить отношение, пользователь должен быть его собственником. Перед удалением отношение должно быть очищено.
Слайд 383. Формат оператора изменения структуры отношения
ALTER TABLE
отношения>
({ADD, MODIFY, DROP} [] [NOT NULL]
[, ({ADD, MODIFY, DROP} <имя_атри-бута>[<тип данных>] [NOT NULL]…]);
ADD, MODIFY, DROP – соот-ветственно добавление, изменение и удаление од-ного или не-скольких ат-рибутов отно-шения.
Слайд 39Пример 2
Добавим к отношению STUDENTS два атрибута для
хранения инфор-мации о курсе и специальности студента:
ALTER TABLE STUDENTS
(ADD
COURS INTEGER,
SPEC CHAR (10));
Слайд 404. Формат оператора создания индекса
CREATE [UNIQUE] INDEX
индекса> ON ( [ASC|DESC]
[, имя_атрибута>
[ASC|DESC]…]);
Индексы создаются для ускорения вы-полнения запросных и поисковых опера-ций с отношением. Опция UNIQUE (уни-кальность значений во всех указанных атрибутах) является необязательной.
ASC|DESC – сорти-ровка значений.
Слайд 41Пример 3
Очевидно, что в отношении STUDENTS одним из
наиболее употребляемых может стать индекс по атрибуту, содержащему фамилию студента.
Тогда команда для создания такого индекса будет следующей:
CREATE INDEX SFAMIND ON STUDENTS (SFAM);
Слайд 425. Формат оператора удаления индекса
DROP INDEX
Этот оператор
позволяет уда-лять созданный ранее индекс с соответствующим именем. При этом
удаление индекса не воз-действует на данные, которые содержатся в индексированных атрибутах.
Слайд 436. Формат оператора создания представления
CREATE VIEW
[( [,]…)]
AS ;
Представление формируется в резуль-тате выполнения
запроса. Если имена атрибутов в представлении не указы-ваются, то будут использоваться име-на атрибутов из запроса, описываемого соответствующим оператором SELECT.
Слайд 44Пример 4
Создадим представление с именем OTLSTUD, которое содержит
инфор-мацию о студентах, получающих сти-пендию в размере 1200 руб. (его
можно использовать в командах наравне с другими таблицами):
CREATE VIEW OTLSTUD AS SELECT * FROM STUDENTS WHERE STIP= 1200;
Слайд 457. Формат оператора удаления представления
DROP VIEW
Следует различать
модифицируемые представления и представления, пред-назначенные только для чтения. Модифицируемые представления
в SQL должны удовлетворять некоторым критериям, на которых мы не оста-навливаемся и рекомендуем изучить их самостоятельно.
Слайд 468. Операторы назначения и удаления привилегий
В языке SQL
одним из принципов защи-ты данных служит система привилегий, то есть
прав пользователя на прове-дение тех или иных действий над опре-деленными объектами БД. Создает пользователей и назначает им привиле-гии администратор БД. Существует не-сколько типов привилегий, соответ-ствующих типам операций над БД.
Слайд 47Каждый пользователь БД в среде SQL имеет специальное идентификационное имя
(ID) доступа. Команда, посланная в БД, ассоциируется с определенным пользователем,
то есть со специальным идентификатором доступа. В системах с большим количеством пользователей существует специальная процедура входа в систему, которую пользователь должен обязательно выполнить для получения доступа.
Слайд 48Каждый пользователь имеет набор привилегий. Эти привилегии могут изме-няться со
временем: новые добавляться командой GRANT, старые удаляться ко-мандой REVOKE. Различные
форматы команды GRANT позволяют сразу пере-давать несколько привилегий одному пользователю (списки привилегий разде-ляются запятыми), нескольким пользова-телям, для отдельных пользователей можно вводить ограничения и т.д.
Слайд 49Пример 5
Пусть пользователь Гамидов владеет таблицей студентов STUDENTS
и желает разрешить пользователю Куриловой выполнить запрос к этой таблице.
Тогда Гамидов должен в этом случае выполнить команду
GRANT SELECT ON STUDENTS TO
Курилова
Слайд 50Теперь Курилова может выполнять запросы к таблице STUDENTS без других
привилегий, то есть она может только выбирать значения, но не
может выполнить любое другое действие, которое бы воздействовало на значения в таблице, включая использование ее в качестве родительской таблицы внешнего ключа.
Слайд 521. Формат оператора выборки кортежей
SELECT[ALL|DISTINCT]
FROM
[WHERE ]
[GROUP BY
[,
<имя_атрибута>]…]
[HAVING <условие поиска>]
[ORDER BY <спецификация>
[, <спецификация> ]…];
Слайд 53Оператор SELECT позволяет произ-водить выборку и вычисления над данными из
одного или нескольких отношений. Результатом выполнения этого оператора является ответная
таблица, которая может иметь (ALL), или не иметь (DISTINCT) повторя-ющиеся кортежи. По умолчанию в ответную таблицу включаются все кортежи, в том числе и повто-ряющиеся.
Слайд 54В отборе участвуют кортежи одной или нескольких таблиц, перечисленных в
списке таблиц после слова FROM.
При использовании в списке данных не-скольких
таблиц для указания принад-лежности атрибута некоторой таблице применяют конструкцию вида
<Имя_отношения> . <Имя_атрибута> .
Список данных может содержать и вы-ражения над атрибутами, в том числе, знаки арифметических операций, конс-танты и круглые скобки.
Слайд 55Операнд WHERE задает условия, которым должны удовлетворять кортежи в результирующей
табли-це. Выражение является логическим. Его элемен-тами могут быть
имена атрибутов, операции сравнения, логические связки and, or, not, круглые скобки, а также специальные функции LIKE, NULL, IN и др.
Слайд 56
Операнд GROUP BY позволяет выделять в результирующем множестве кортежей группы.
Группой являются кортежи с сов-падающими значениями в столбцах, перечисленных за
ключевыми словами GROUP BY. Выделение групп требуется для использования в логических выра-жениях операндов WHERE и HAVING, где HAVING<условия поиска> является спе-циальной формой операнда WHERE, и указывает дополнительный критерий отбо-ра данных в каждую группу.
Слайд 57 В логических и арифметических выра-жениях можно использовать следую-щие
функции как групповые:
AVG, MAX, MIN, SUM, COUNT.
Наконец,
опция ORDER BY задает по-рядок сортировки результирующего множества кортежей. Она аналогична конструкции оператора CREATE INDEX.
Каждая <спецификация> представляет собой пару
<имя_атрибута> [ASC|DESC].
Слайд 582. Формат оператора изменения кортежей
UPDATE
SET = = {, NULL}
[,
SET =
= {, NULL}…]
[ WHERE ];
Выполнение
опера-тора UPDATE сос-тоит в изменении значений в опреде-ленных операндом SET атрибутах для тех кортежей, кото-рые удовлетворяют условию, заданному операндом WHERE.
Слайд 593. Форматы оператора вставки новых кортежей
а) INSERT INTO
[()]
VALUES ();
б) INSERT INTO
[(<список атрибутов>)]
<предложение SELECT>;
Слайд 604. Формат оператора удаления кортежей и замечание
DELETE FROM
[WHERE ];
Замечание. Язык SQL обладает ре-ляционной полно-той.
![3. Формат оператора изменения структуры отношения ALTER TABLE ({ADD, MODIFY, DROP} [] [NOT NULL]](/img/thumbs/67c1c904552845f7f08a711a87660883-800x.jpg "Язык БД SQL 3. Формат оператора изменения структуры отношения ALTER TABLE ({ADD, MODIFY, DROP}")
![4. Формат оператора создания индекса CREATE [UNIQUE] INDEX ON ( [ASC|DESC] [, имя_атрибута>](/img/thumbs/5e819fbc56ea206aeb44b34176b78b79-800x.jpg "Язык БД SQL 4. Формат оператора создания индекса CREATE [UNIQUE] INDEX ON ( [ASC|DESC]")
![6. Формат оператора создания представления CREATE VIEW [( [,]…)] AS ; Представление формируется](/img/tmb/2/154389/a7d734d3380f430e39f2984b134b66aa-800x.jpg "Язык БД SQL 6. Формат оператора создания представления CREATE VIEW [( [,]…)] AS ;")
![1. Формат оператора выборки кортежей SELECT[ALL|DISTINCT] FROM [WHERE ] [GROUP BY [, ]…]](/img/thumbs/d46b6dfab1fe06a2868800816afa4fe8-800x.jpg "Язык БД SQL 1. Формат оператора выборки кортежей SELECT[ALL|DISTINCT] FROM [WHERE ] [GROUP BY")
![2. Формат оператора изменения кортежейUPDATE SET = = {, NULL}[, SET = = {, NULL}…][ WHERE](/img/thumbs/a678aace41082f867ee1636551cb7166-800x.jpg "Язык БД SQL 2. Формат оператора изменения кортежейUPDATE SET = = {, NULL}[, SET")
![3. Форматы оператора вставки новых кортежейа) INSERT INTO [()]VALUES ();б) INSERT INTO [()];](/img/thumbs/f78e5c8e2dba1d5544c1c3b7f08857b4-800x.jpg "Язык БД SQL 3. Форматы оператора вставки новых кортежейа) INSERT INTO [()]VALUES ();б) INSERT INTO [()];")
![4. Формат оператора удаления кортежей и замечаниеDELETE FROM [WHERE ]; Замечание. Язык SQL обладает ре-ляционной полно-той.](/img/thumbs/bd29b4124450199d4ff3e977d5b0cea9-800x.jpg "Язык БД SQL 4. Формат оператора удаления кортежей и замечаниеDELETE FROM [WHERE ]; Замечание.")
