Что такое ddl в sql
Введение в базы данных
Алексей Федоров, Наталия Елманова
В предыдущих двух статьях данного цикла, опубликованных в № 6 и 7 нашего журнала, мы рассмотрели различные механизмы доступа к данным, включая ADO, BDE и их альтернативы. Теперь мы знаем, как выбрать технологию доступа к данным для той или иной пары «СУБД — средство разработки».
Располагая технологией доступа к данным, можно наконец подумать и о том, каким образом следует манипулировать самими данными и метаданными. Способы манипуляции могут быть специфичными для данной СУБД (например, использование объектов клиентской части этой СУБД для доступа к объектам баз данных) или для данного механизма доступа к данным. Тем не менее существует более или менее универсальный способ манипуляции данными, поддерживаемый почти всеми серверными реляционными СУБД и большинством универсальных механизмов доступа к данным (в том числе при использовании их совместно с настольными СУБД). Этот способ — применение языка SQL (Structured Query Language — язык структурированных запросов). Ниже мы рассмотрим назначение и особенности этого языка, а также изучим, как с его помощью извлекать и суммировать данные, добавлять, удалять и модифицировать записи, защищать данные от несанкционированного доступа, создавать базы данных. Для более подробного изучения SQL мы можем порекомендовать книги Мартина Грабера «Введение в SQL» (М., Лори, 1996) и «SQL. Справочное руководство» (М., Лори, 1997).
Введение
Structured Query Language представляет собой непроцедурный язык, используемый для управления данными реляционных СУБД. Термин «непроцедурный» означает, что на данном языке можно сформулировать, что нужно сделать с данными, но нельзя проинструктировать, как именно это следует сделать. Иными словами, в этом языке отсутствуют алгоритмические конструкции, такие как метки, операторы цикла, условные переходы и др.
Язык SQL был создан в начале 70-х годов в результате исследовательского проекта IBM, целью которого было создание языка манипуляции реляционными данными. Первоначально он назывался SEQUEL (Structured English Query Language), затем — SEQUEL/2, а затем — просто SQL. Официальный стандарт SQL был опубликован ANSI (American National Standards Institute — Национальный институт стандартизации, США) в 1986 году (это наиболее часто используемая ныне реализация SQL). Данный стандарт был расширен в 1989 и 1992 годах, поэтому последний стандарт SQL носит название SQL92. В настоящее время ведется работа над стандартом SQL3, содержащим некоторые объектно-ориентированные расширения.
Существует три уровня соответствия стандарту ANSI — начальный, промежуточный и полный. Многие производители серверных СУБД, такие как IBM, Informix, Microsoft, Oracle и Sybase, применяют собственные реализации SQL, основанные на стандарте ANSI (отвечающие как минимум начальному уровню соответствия стандарту) и содержащие некоторые расширения, специфические для данной СУБД.
Более подробную информацию о соответствии стандарту версии SQL, используемой в конкретной СУБД, можно найти в документации, поставляемой с этой СУБД.
Как работает SQL
Давайте рассмотрим, как работает SQL. Предположим, что имеется база данных, управляемая с помощью какой-либо СУБД. Для извлечения из нее данных используется запрос, сформулированный на языке SQL. СУБД обрабатывает этот запрос, извлекает запрашиваемые данные и возвращает их. Этот процесс схематически изображен на рис. 1.
Как мы увидим позже, SQL позволяет не только извлекать данные, но и определять структуру данных, добавлять и удалять данные, ограничивать или предоставлять доступ к данным, поддерживать ссылочную целостность.
Обратите внимание на то, что SQL сам по себе не является ни СУБД, ни отдельным продуктом. Это язык, применяемый для взаимодействия с СУБД и являющийся в определенном смысле ее неотъемлемой частью.
Операторы SQL
SQL содержит примерно 40 операторов для выполнения различных действий внутри СУБД. Ниже приводится краткое описание категорий этих операторов.
Data Definition Language (DDL)
Data Definition Language содержит операторы, позволяющие создавать, изменять и уничтожать базы данных и объекты внутри них (таблицы, представления и др.). Эти операторы перечислены в табл. 1.
Применяется для добавления новой таблицы к базе данных
Применяется для удаления таблицы из базы данных
Применяется для изменения структуры имеющейся таблицы
Применяется для добавления нового представления к базе данных
Применяется для удаления представления из базы данных
Применяется для создания индекса для данного поля
Применяется для удаления существующего индекса
Применяется для создания новой схемы в базе данных
Применяется для удаления схемы из базы данных
Применяется для создания нового домена
Применяется для переопределения домена
Применяется для удаления домена из базы данных
 |
Иногда оператор SELECT относят к отдельной категории, называемой Data Query Language (DQL).
Transaction Control Language (TCL)
Операторы Transaction Control Language применяются для управления изменениями, выполненными группой операторов DML. Операторы TCL представлены в табл. 3.
Применяется для завершения транзакции и сохранения изменений в базе данных
Применяется для отката транзакции и отмены изменений в базе данных
Применяется для установки параметров доступа к данным в текущей транзакции
|
|
Все операторы SQL имеют вид, показанный на рис. 2.
Каждый оператор SQL начинается с глагола, представляющего собой ключевое слово, определяющее, что именно делает этот оператор (SELECT, INSERT, DELETE. ). В операторе содержатся также предложения, содержащие сведения о том, над какими данными производятся операции. Каждое предложение начинается с ключевого слова, такого как FROM, WHERE и др. Структура предложения зависит от его типа — ряд предложений содержит имена таблиц или полей, некоторые могут содержать дополнительные ключевые слова, константы или выражения.
Ключевые слова ANSI/ISO SQL92
Некоторые ключевые слова, определенные в стандарте ANSI SQL, не могут быть использованы в качестве имен объектов баз данных (таблиц, полей, имен пользователей). Эти ключевые слова приведены в табл. 6.
Стандарт SQL также включает список потенциальных ключевых слов, зарезервированных для последующих версий стандарта SQL. Эти ключевые слова приведены в табл. 7.
С помощью чего можно выполнить SQL-операторы
Все современные серверные СУБД (а также многие популярные настольные СУБД) содержат в своем составе утилиты, позволяющие выполнить SQL-предложение и ознакомиться с его результатом. В частности, клиентская часть Oracle содержит в своем составе утилиту SQL Plus, а Microsoft SQL Server — утилиту SQL Query Analyzer. Именно этой утилитой мы воспользуемся для демонстрации возможностей SQL, а в качестве базы данных, над которой мы будем «экспериментировать», возьмем базу данных NorthWind, входящую в комплект поставки Microsoft SQL Server 7.0. В принципе, можно использовать другую базу данных и любую другую утилиту, способную выполнять в этой базе данных SQL-предложения и отображать результаты (или даже написать свою, используя какое-либо средство разработки — Visual Basic, Delphi, C++Builder и др.). Однако на всякий случай рекомендуется сделать резервную копию этой базы данных.
Что такое ddl в sql
С другой стороны, производители СУБД вводят в SQL дополнительные возможности, не поддерживаеме стандартами, нарушая тем самым совместимость SQL для разных СУБД.
DDL и DML
Типы данных
Каждый столбец и домен, из которого берутся значения в этот столбец, имеют свой тип данных. В стандарте SQL определены следующие семь основных типов данных:
Каждый основной тип имеет один или несколько подтипов.
Разработчики СУБД нарушают стандарты SQL. Осбенно сильно отличаются от стандартных типы данных. Более того, наборы типов данных в разных СУБД тоже отличаются друг от друга. Например, в СУБД MS SQL Server и MS ACCESS есть тип данных MONEY, а в Oracle и в MySQL отсутствует. Различие типов данных является одной из причин несовместимости различных СУБД и возникающих трудностей при переносе базы данных из одной СУБД в другую. В таблице 1 перечислены типы данных СУБД ACCESS.
Таблица 1. Типы данных в ACCESS
Структура реляционной базы данных
Для работы с базами данных создаются информационные системы, состоящие из прикладных программ, СУБД и базы данных. Пользователи, как правило, называют базой данных всю информационную систему. Такая терминологическая путаница очень мешает в ситуациях, когда пользователь пытается рассказать разработчику о неполадках в информационной системе.
Описание структуры базы данных называется метаданными и хранится в базе вместе с основными данными.
Нотация Бэкуса-Наура
Нотация, или форма, Бэкуса-Наура используется для описания синтаксиса операторов языков программирования. В ней применяются следующие обозначения:
Более полно смысл перечисленных обозначений станет ясен после их использования для описания операторов.
Операторы языка описания данных
Перечислим основные операторы языка SQL, с помощью которых создаётся и изменяется cтруктура базы данных:
Оператор CREATE TABLE
Пример. Создание таблицы из четырёх столбцов.
Ограничения столбца имеют следующие значения:
Ограничение может иметь имя, которое задаётся перед ограничением ключевым словом CONSTRAINT:
Пример создание двух связанных таблиц. Рассмотрим сущности писатель и книга. Для простоты будем считать, что у книги может быть только один автор. Тогда между сущностями писатель и книга существует связь типа один ко многим. Для отображения этой связи в реляционной модели создадим в таблице pisatel первичный ключ Id_p, а в таблицу kniga добавим внешний ключ Id_pisatel. Кроме того, зададим адрес писателя по умолчанию и проверку числа страниц в книге. Соответствующие запросы будут выглядеть так:
В таблице kniga поле naim = потенциальный ключ.
Пример составного первичного ключа. В таблице tovar пара значений наименование, изготовитель должна быть уникальной.
Оператор ALTER TABLE
Оператор ALTER TABLE служит для изменения структуры существующих таблиц. В реляционной базе данных существует множество часто неявных, скрытых связей, которые при изменении структуры таблиц могут быть нарушены. Поэтому применять оператор ALTER TABLE следует крайне осторожно.
Синтаксическая формула оператора ALTER TABLE:
Пример добавления столбца. К таблице заказ добавляется столбец цена, имеющий тип MONEY.
Оператор DROP TABLE
Синтаксическая формула оператора DROP TABLE:
Оператор CREATE INDEX
Синтаксическая формула оператора CREATE INDEX:
Пример. Содаётся индекс kniga_ind для таблицы kniga.
Оператор DROP INDEX
Синтаксическая формула оператора DROP INDEX:
Оператор CREATE VIEW
В контексте реляционных баз данных термин VIEW переводится на русский язык как представление. Синтаксическая формула оператора CREATE VIEW:
Оператор CREATE VIEW необычен тем, что содержит в себе оператор SELECT, принадлежащий языку манипулирования данными. Заметьте, что в синтактической формуле используется не имя представления,что было бы логично, а имя таблицы. Когда в каком-либо запросе языка манипулирования данными встречается имя таблицы, объявленное в операторе CREATE VIEW, то выполняется запрос SELECT, объявленный в том же CREATE VIEW. Результаты этого запроса рассматриваются как обыкновенная таблица!
Данные могут извекаться в представление из одной или из некольких настоящих таблиц. Источником формирования представления может быть другое представление. В том случае, когда предсавление связано только с одной таблицей, оно может использоваться для изменения (оператор UPDATE), удаления (оператор DELETE) и добавления (оператор INSERT) данных в породившую его таблицу.
Предложение CHECK OPTION служит для проверки нарушения целостности данных при использовании представления в операторах INSERT и UPDATE. CASCADED распространяет проверку на все уровни вложенности представления, а LOCAL ограничивает проверку только одним уровнем.
Пример. Создаётся представление, в которое отбираются только дешёвые товары из таблицы товар
Оператор DROP VIEW
Синтаксическая формула оператора удаления представления DROP VIEW:
RESTRICT вызывает сообщение об ошибке при существовании ссылки на это представление.
При задании CASCADE удаляются все объекты, в которых есть ссылки на удаляемое представление.
Оператор GRANT
Оператор GRANT служит для назначения прав (привилегий) пользователям. Синтаксическая формула оператора GRANT:
GRANT OPTION даёт возможность передавать права другим пользователям
PUBLIC предоставляет указанные в операторе GRANT права всем пользователям.
1. Пользователю Петрову предоставляется право добавлять данные в таблицу книга.
2. Пользователям Lada и Genja предоставляется право просматриваь таблицу книга и добавлять в неё данные.
3. Пользователям Andre и Peter предоставляется право обновлять поле naim в таблице книга.
Оператор REVOKE
Оператор REVOKE служит для отмены привилегий. Синтаксическая формула оператора REVOKE:
В состав оператора языка SQL могут входить другие операторы языка SQL, математические операторы (арифметические, логические, операторы отношения) и функции, строковые операторы и функции.
Операция над данными строкового типа
Конкатенация данных строкового типа и сложения числовых данных обозначаются одним значком «+», но дают разный результат, например,
Оператор SELECT
Общая синтаксическая формула оператора SELECT очень сложна и мало подходит для начинающего изучать язык SQL. Поэтому начнём с самых простых частных случаев.
Выборка всех строк
Запрос на выборку всей таблицы имеет самый простой вид:
Пример. Из базы выбирается вся таблица заказ.
| N_заказа | изделие | фирма | к_во |
|---|---|---|---|
| 1 | Ноутбук | Альфа | 2 |
| 2 | Мышка | Бета | 4 |
| 3 | Принтер | Альфа | 1 |
| 4 | флешка | Гамма | 5 |
| 5 | Мышка | Бета | 1 |
| изделие | фирма |
|---|---|
| Ноутбук | Альфа |
| Мышка | Бета |
| Принтер | Альфа |
| флешка | Гамма |
| Мышка | Бета |
Из примера видно, что реальная таблица реляционной базы данных в отличие от теоретического отношения может содержать одинаковые строки. Чтобы исключить дублирование строк, нужно вставить DISTINCT после SELECT:
| изделие | фирма |
|---|---|
| Ноутбук | Альфа |
| Мышка | Бета |
| Принтер | Альфа |
| флешка | Гамма |
Псевдонимы. Названия столбцов часто неудобны при просмотре результатов запроса. Для замены имён столбцов синонимами используется следующий синтаксис:
| Номер заказа | изделие | фирма | количество |
|---|---|---|---|
| 1 | Ноутбук | Альфа | 2 |
| 2 | Мышка | Бета | 4 |
| 3 | Принтер | Альфа | 1 |
| 4 | флешка | Гамма | 5 |
| 5 | Мышка | Бета | 1 |
Вычисляемые поля. Вместо имени поля можно использовать арифметическое или строковое выражение. Подсчитаем стоимость товаров в таблице затраты, изменив единицу измерения стоимости на тыс. руб.
| Товар | Цена | к_во |
|---|---|---|
| Стол | 12000 | 5 |
| Стул | 1700 | 20 |
| Шкаф | 18500 | 2 |
Запрос выглядит так:
В запросе использована функция str(числовое выражение), преобразующая выражение в строковый тип. Результат запроса
| Товар | Цена | Количество | Стоимость |
|---|---|---|---|
| Стол | 12000 | 5 | 60 тыс. руб. |
| Стул | 1700 | 20 | 34 тыс. руб. |
| Шкаф | 18500 | 2 | 37 тыс. руб. |
Агрегатные функции служат для вычисления характеристик таблицы в целом. Всего агрегатных функций пять: count, sum, max, min и avg.
| К-во товаров | Стоимость всех товаров | Макс. цена | Мин. цена | Средняя цена |
|---|---|---|---|---|
| 3 | 131000 | 18500 | 1700 | 10733 |
Выборка строк, удовлетволяющих условию
Для выборки из таблицы только тех строк, содержимое которых удовлетворяет заданному условию, дополним синтактическую формулу предложением WHERE:
1. Использование операций сравнения.
Выберем из таблицы Затраты строки, в которых цена больше 5000.
| Товар | Цена | Количество |
|---|---|---|
| Стол | 12000 | 5 |
| Шкаф | 18500 | 2 |
В этом примере цена имеет числовой тип. Можно сравнивать и текстовую (строковую) информацию. Вся текстовая информация хранится в памяти ЭВМ в виде последовательности цифровых кодов символов. Таблицы кодирования составлены так, что следующая буква алфавита имеет больший код, чем предыдущая, то есть, А клиенты все строки с фамилиями, начинающимися на буквы с А по И включительно, нужно выполнить следующий запрос
Рассмотрим два примера использования в СУБД ACCESS функций СDATE и DatePart.
Функция СDATE( дата в текстовом виде) преобразует дату в тип DATE. Выберем из таблицы студенты все строки с датами родения позже 31.12.1994.
Функция DatePart(часть даты, дата) возвращает часть даты. Параметр часть даты может принимать следующие значения:
Пример выделения из даты года и месяца. Из таблицы клиенты выбираются ФИО и Год рождения клиентов, родившихся в мае.
| ФИО | Дата рождения |
|---|---|
| Сергеева Е.И. | 16.02.1994 |
| Петров А.П. | 16.05.1995 |
| Петров В.П. | 07.10.1989 |
| Иванов С.В. | 21.05.1956 |
| ФИО | Год рождения |
|---|---|
| Петров А.П. | 1995 |
| Иванов С.В. | 1956 |
2. Применение логических операторов AND, OR, NOT
Выберем из таблицы клиенты все клиентов, родившихся между 1990-м и 1999-м годами или родившихся не в мае.
| ФИО | Дата рождения |
|---|---|
| Сергеева Е.И. | 16.02.1994 |
| Петров А.П. | 16.05.1995 |
| Петров В.П. | 07.10.1989 |
3. Применение специальных операторов сравнения IN, BETWEEN, LIKE, IS NULL
Перечисленные операторы иначе называют предикатами. Предикаты IN и BETWEEN служат для сокращении записи условия выборки.
Предикат IN используется в тех случаях, когда в условиях выборки нужно задать не диапазон, а список значений. Выберем из таблицы Заказы заказы для городов БЕРН, Женева и Грасс.
Предикат BETWEEN, наоборот, используется для задания диапазона значений, включая границы диапазона. Выберем заказы с ценой от 200 до 1000.
При задании диапазона дат необходимо преобразовать даты из тектового типа в тип DATE’.
Можно задавать текстовый диапазон. В результате выполнения запроса
выберутся заказы для стран на буквы от А до И включительно.
Предикат LIKE служит для поиска текстовой инфомации по шаблону. Заменим в предыдущем операторе предикат BETWEEN на LIKE, используя шаблоны, принятые в СУБД ACCESS.
В стандарте SQL есть только два символа для поиска по шаблону:
Примеры шаблонов в ACCESS
Если пользователи ничего не записали в какое-либо поле, то считается что оно хранит признак пустоты NULL. Для того, чтобы найти строки, в которых заданное поле хранит NULL, нужно написать условие
Для задания обратного условия нужно написать
Предложения GROUP BY и HAVING
Предложение GROUP BY служит для разбиения всех строк таблицы на группы и последующего применения к каждой группе агрегатных функций. Например, для фирмы, занимающейся доставкой товаров, представляет интерес не только общее количество заказов, но и распределение заказов по странам. Такие данные получаются в результате выполнения запроса
| Страна получателя | Количество заказов | Сумм стоим доставки | Макс стоим доставки |
|---|---|---|---|
| Австрия | 39 | 6 637,24р. | 789,95р. |
| Аргентина | 16 | 598,58р. | 217,86р |
| Бельгия | 20 | 1 341,89р. | 424,30р. |
| . | . | . | . |
| Франция | 80 | 4 276,20р. | 487,38р. |
| Швейцария | 18 | 1 368,53р. | 232,42р. |
| Швеция | 36 | 2 992,81р. | 328,74р. |
Предложение HAVING служит для отбора групп, удовлетворяющих условию. В условие могут входить агрегатные функции. Перепишем предыдущий запрос, включив в него условие отбора только тех стран, максимальная стоимость доставки в которые больше четырёхсот рублей.
| Страна получателя | Количество заказов | Сумм стоим доставки | Макс стоим доставки |
|---|---|---|---|
| Австрия | 39 | 6 637,24р. | 789,95р. |
| Бельгия | 20 | 1 341,89р. | 424,30р. |
| Бразилия | 83 | 4 880,19р. | 890,78р. |
| Германия | 117 | 11 341,09р. | 1 007,64р. |
| Ирландия | 19 | 2 755,24р. | 603,54р. |
| США | 122 | 13 771,29р. | 830,75р. |
| Франция | 80 | 4 276,20р. | 487,38р. |
Общая структура однотабличного запроса имеет вид:
| Страна получателя | Количество заказов | Сумм стоим доставки | Макс стоим доставки |
|---|---|---|---|
| Германия | 53 | 4 148,10р. | 810,05р. |
| США | 51 | 5 778,58р. | 830,75р. |
| Бразилия | 35 | 2 513,43р. | 890,78р. |
| Австрия | 14 | 2 311,57р. | 789,95р. |
| Ирландия | 9 | 1 890,41р. | 603,54р. |
| Бельгия | 9 | 793,16р. | 424,30р. |
Предложени ORDER BY
Многотабличные запросы
На практике часто с помощью одного запроса данные собираются сразу из нескольких таблиц. Некоторые СУБД позволяют в одном запросе делать выборку из нескольких баз данных.
Рассмотрим простейший двухтабличный запрос. Необходимо выбрать из базы данных названия всех городов вместе с названиями стран, в которых они находятся. Для этого используем связанные между собой таблицы Города и Страны