Data Definition Language (DDL)– язык определения данных Бази даних та інформаційні системи Лекція 13

План лекции Введение. 1.Создание таблиц (CREATE TABLE) - Типы данных - Обобщенная запись оператора CREATE TABLE - Определения столбца - Ограничения столбца (с использованием имен ограничений и без) - Ограничения таблицы 2.Модификация таблиц (ALTER TABLE) 3.Удаление таблиц (DROP TABLE) Заключение ХНУРЕ кафедра Інформатики доц. Яковлева О.В. 2

Цель лекции : ХНУРЕ кафедра Інформатики доц. Яковлева О.В. 3

Введение Язык Data Definition Language (DDL) не работает с данными, а работает с объектами базы данных Для создания объектов используется оператор: CREATE Для модификации объектов используется оператор: ALTER Для удаления объектов используется оператор: DROP ХНУРЕ кафедра Інформатики доц. Яковлева О.В. 4

Создание таблиц : CREATE TABLE Типы данных ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 5

Создание таблиц: CREATE TABLE Приступая к созданию таблицы, необходимо иметь ответы на следующие вопросы : 1. Как будет называться таблица ? 2. Как будут называться столбцы ( поля ) таблицы ? 3. Какие типы данных будут закреплены за каждым столбцом ? 4. Какой размер памяти должен быть выделен для хранения каждого столбца ? 5. Какие столбцы таблицы требуют обязательного ввода ? 6. Из каких столбцов будет состоять первичный ключ ? 7. Какие колонки станут внешними ключами ? 8. Какие типы ограничений должны использоваться ? Имена таблиц 1. Имя должно начинаться с буквы, a-z 2. Могут содержать буквы, цифры и знак подчеркивания 3. Длина имени не должна превышать 18 знаков ( стандарт SQL) 4. В базе данных не должно быть объектов с одинаковыми именами 5. В качестве имен не должны быть использованы зарезервированные слова ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 6

Запись SQL - операторов Язык, в терминах которого дается описание языка SQL, называется метаязыком. Синтаксические определения обычно задают с помощью специальной металингвистической символики, называемой Бэкуса-Наура формулами (БНФ). Прописные буквы используются для записи зарезервированных слов. Строчные буквы употребляются для записи слов, определяемых пользователем. Применяемые в нотации БНФ символы и их обозначения показаны в таблице: ХНУРЕ кафедра Інформатики доц. Яковлева О.В. 7 СимволОбозначение ::= Равно по определению | Необходимость выбора одного из нескольких приведенных значений {…} Обязательный выбор некоторой конструкции из списка […] Необязательный выбор некоторой конструкции из списка [,…n] Необязательная возможность повторения конструкции от нуля до нескольких раз. Отдельные вхождения элемента разделяются запятыми [ …n] Необязательная возможность повторения конструкции от нуля до нескольких раз. Отдельные вхождения элемента разделяются пробелами В находится название синтаксического блока. Данное обозначение используется для группировки или маркировки частей синтаксиса

Создание таблиц: CREATE TABLE Для создания объектов используется оператор CREATE Создание БД: CREATE DATABASE Создание таблицы CREATE TABLE Обобщённая запись оператора CREATE TABLE имя_таблицы ( { } [, …n] [ [, …n] ]) Определение столбца содержит: имя столбца; тип данных или AS ; наличие счетчика (IDENTITY (SQL Server) или COUNTER (Access)); обязательность значений (NULL | NOT NULL ); ограничения столбца: значение по умолчанию (DEFAULT); уникальность значений (PRIMARY KEY | UNIQUE ) (целостность сущностей) ссылающееся значение (REFERENCES) (ссылочная целостность) ограничение, заданное логическим выражением (CHECK) (ограничение домена, корпоративные ограничения) ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 8

Создание таблиц : CREATE TABLE ::= {имя_столбца тип_данных} [ IDENTITY [ (начало, приращение) ] [ NULL | NOT NULL ] ] [ [ …n] ] ::= [ CONSTRAINT имя_ограничения ] { [DEFAULT ](Access не поддерживает) | [PRIMARY KEY | UNIQUE ] | [ [ FOREING KEY ] REFERENCES имя_род_таблицы [имя_столбца_род_таблицы] [ ON UPDATE {CASCADE | SET NULL | SET DEFAULT | NO ACTION} ] [ ON DELETE {CASCADE | SET NULL | SET DEFAULT | NO ACTION} ] | CHECK ( ) }(Access не поддерживает) ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 9

Создание таблиц : CREATE TABLE Рисунок 1.1 – Схема данных (Access) БД «Торговля» Пример 1.1 Создание таблицы Товар без использования ограничений CREATE TABLE Товар (КодТовара INT IDENTITY(1,1), Название VARCHAR(50), Тип VARCHAR(30), Сорт VARCHAR(20), Цена MONEY, ГородТовара INT) ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 10

Создание таблиц : CREATE TABLE Рисунок 1.1 – Схема данных (Access) БД «Торговля» Пример 2.1 Создание таблицы Город с использованием ограничений столбца, но без использования названий ограничений (CONSTRAINT) CREATE TABLE Город (КодГорода INT PRIMARY KEY, Название VARCHAR(40) NOT NULL); Пример 1.2 (MS SQL Server) Создание таблицы Товар с использованием ограничений столбца, но без использования названий ограничений (CONSTRAINT) CREATE TABLE Товар (КодТовара INT IDENTITY(1,1) PRIMARY KEY, Название VARCHAR(50) UNIQUE, Тип VARCHAR(30) NOT NULL DEFAULT ' мебель ', Сорт VARCHAR(20) CHECK (Сорт IN ( высший ', первый ', второй ' )), Цена MONEY, ГородТовара INT REFERENCES Город (КодГорода) ON UPDATE CASCADE ON DELETE CASCADE) ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 11

Создание таблиц : CREATE TABLE Рисунок 1.1 – Схема данных (Access) БД «Торговля» Пример 1.3 (MS Access) Создание таблицы Товар с использованием ограничений столбца, но без использования названий ограничений (CONSTRAINT) CREATE TABLE Товар (КодТовара COUNTER PRIMARY KEY, Название VARCHAR(50) UNIQUE, Тип VARCHAR(30) NOT NULL, Сорт VARCHAR(20), Цена MONEY, ГородТовара INT REFERENCES Город (КодГорода)) Замечание ! Для задания в MS Access режима каскадного обносления / удаления нажмите Ctrl+G, в появившемся окне "Immediate window" выполните : CurrentProject.Connection.Execute CREATE TABLE Товар (КодТовара COUNTER PRIMARY KEY, Название VARCHAR(50) UNIQUE, Тип VARCHAR(30) NOT NULL, Сорт VARCHAR(20), Цена MONEY, ГородТовара INT REFERENCES Город (КодГорода) ON UPDATE CASCADE ON DELETE CASCADE) ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 12

Создание таблиц : CREATE TABLE Пояснения: (IDENTITY (SQL Server) или COUNTER (Access)) - для таблицы может быть только один столбец-счетчик; - если шаг и приращение не указаны, то по умолчанию они равны 1 NULL | NOT NULL - по умолчанию используется NULL; DEFAULT - используется для задания значения по умолчанию, если значение NULL запрещено; PRIMARY KEY - предложение PRIMARY KEY в пределах таблицы может использоваться только один раз, - не может принимать значение NULL; - СУБД автоматически создает уникальный индекс для столбцов, входящих в первичный ключ UNIQUE - используется для обеспечения уникальности значений (альтернативных ключей), рекомендуется использовать с NOT NULL ; - значение NULL может использоваться для столбца с UNIQUE, но только 1 раз) ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 13

Создание таблиц : CREATE TABLE Пояснения: [ FOREING KEY ] REFERENCES имя_род_таблицы [имя_столбца_род_таблицы] - СУБД отклонит выполнение операторов INSERT или UPDATE если значение внешнего ключа не соответствует значению потенциального ключа родительской таблицы - предложение [ FOREING KEY ] REFERENCES в пределах таблицы может использоваться произвольное количество раз; CHECK ( ) - допускается применение нескольких ограничений к одному и тому же столбцу; - используется практически со всеми операторами, которые применяются для задания условий в строке WHERE оператора SELECT: Сравнение =,, =, ; Диапазон - BETWEEN / NOT BETWEEN; Принадлежность множеству IN/NOT IN; Соответствие шаблону LIKE / NOT LIKE; - подзапросы в CHECK не применяются. ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 14

Создание таблиц : CREATE TABLE ВЫЧИСЛЯЕМЫЕ ПОЛЯ (Access не поддерживает) ::= {имя_столбца } задает значение для вычисляемого столбца; Вычисляемые столбцы - это виртуальные столбцы, т. е. физически в таблице они не хранятся и вычисляются с использованием значений столбцов той же таблицы; В выражении для вычисляемого столбца могут присутствовать имена обычных столбцов, константы и функции, связанные одним или несколькими операторами. Подзапросы в таком выражении участвовать не могут. Ограничения использования вычисляемых полей: не могут входить во внешний ключ; для них не используются значения по умолчанию; не могут участвовать в операциях INSERT и UPDATE. Пример. create table t1 (col1 int primary key, col2 int, col3 as col1+col2); (Access вычисляемые поля не поддерживает) Нельзя! Update t1 set col3= col3*2 where col1=1; ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 15

Создание таблиц : CREATE TABLE Рисунок 1.1 – Схема данных (Access) БД «Торговля» Пример 3.1 (MS SQL Server) Создание таблицы Клиент с использованием ограничений столбца, но без использования названий ограничений (CONSTRAINT) ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 16

Создание таблиц : CREATE TABLE Именованные ограничения CONSTRAINT Фраза CONSTRAINT позволяет задать уникальное имя ограничению в пределах БД, что позволит впоследствии отменить то или иное ограничение с помощью оператора ALTER TABLE Пример 3.2 (MS SQL Server) Создание таблицы Клиент с использованием именованных ограничений столбца(CONSTRAINT) CREATE TABLE Клиент (КодКлиента INT IDENTITY(1,1) CONSTRAINT PK_Client PRIMARY KEY, Фамилия VARCHAR(50) NOT NULL, Имя VARCHAR(30) NOT NULL, Отчество VARCHAR(20), Фирма VARCHAR(20), ГородКлиента INT CONSTRAINT FK_ClientCity REFERENCES Город (КодГорода) ON UPDATE CASCADE ON DELETE CASCADE, Телефон CHAR(10)); или с использованием слова FOREIGN KEY (Access не поддерживает) ГородКлиента INT CONSTRAINT FK_ClientCity FOREIGN KEY REFERENCES Город (КодГорода) ON UPDATE CASCADE ON DELETE CASCADE или без указание поля ПК, на кот. идет ссылка ) ГородКлиента INT CONSTRAINT FK_ClientCity FOREIGN KEY REFERENCES Город ON UPDATE CASCADE ON DELETE CASCADE ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 17

Создание таблиц : CREATE TABLE. Именованные ограничения CONSTRAINT Рисунок 1.1 – Схема данных (Access) БД «Торговля» Пример 2.2 Создание таблицы Город с использованием CONSTRAINT Пример 1.4 (MS SQL Server) Создание таблицы Товар с использованием CONSTRAINT CREATE TABLE Товар (КодТовара INT IDENTITY(1,1) CONSTRAINT PK_Tovar PRIMARY KEY, Название VARCHAR(50) CONSTRAINT U_TovarName UNIQUE, Тип VARCHAR(30) NOT NULL CONSTRAINT D_Type DEFAULT ' мебель ', Сорт VARCHAR(20) CONSTRAINT Ch_Sort CHECK (Сорт IN ( высший ', первый ', второй ' )), Цена MONEY, ГородТовара INT CONSTRAINT FK_TovatCoty REFERENCES Город (КодГорода) ON UPDATE CASCADE ON DELETE CASCADE); ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 18

Создание таблиц: CREATE TABLE ОГРАНИЧЕНИЯ ТАБЛИЦЫ CREATE TABLE имя_таблицы ( { } [, …n] [ [, …n] ]) Определение таблицы содержит ограничения, которые накладываются сразу на несколько столбцов: уникальность значений (PRIMARY KEY | UNIQUE ) (целостность сущностей) ссылающееся значение (REFERENCES) (ссылочная целостность) ограничение, заданное логическим выражением (CHECK) (ограничение домена, корпоративные ограничения) ::= [ CONSTRAINT имя_ограничения ] {[PRIMARY KEY | UNIQUE (имя_столбца [, …n])] | [ FOREING KEY (имя_столбца [, …n]) REFERENCES имя_род_таблицы [(имя_столбца_род_таблицы [, …n])] [ ON UPDATE {CASCADE | SET NULL | SET DEFAULT | NO ACTION} ] [ ON DELETE {CASCADE | SET NULL | SET DEFAULT | NO ACTION} ] | CHECK ( ) } ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 19

Создание таблиц : CREATE TABLE. Именованные ограничения CONSTRAINT Рисунок 1.1 – Схема данных (Access) БД «Торговля» Пример 2.3 Создание таблицы Город с использованием ограничений на таблицу (без CONSTRAINT) CREATE TABLE Город (КодГорода INT, Название VARCHAR(40) NOT NULL, PRIMARY KEY (КодГорода) ); Пример 2.4 Создание таблицы Город с использованием ограничений на таблицу (с CONSTRAINT) ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 20

Создание таблиц : CREATE TABLE. Именованные ограничения CONSTRAINT Рисунок 1.1 – Схема данных (Access) БД «Торговля» Пример 4 (MS SQL Server) Создание таблицы Сделка (с CONSTRAINT) CREATE TABLE Сделка (Дата Date, КодТовара INT CONSTRAINT FK_SdelkaTovat REFERENCES Товар(КодТовара), КодКлиента INT CONSTRAINT FK_SdelkaClient REFERENCES Клиент(КодКлиента), Кол_во INT, CONSTRAINT PK_Sdelka PRIMARY KEY(КодТовара, КодКлиента, Дата)); ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 21

Создание таблиц : DROP TABLE. Удаление таблицы с заданным именем. DROP TABLE имя _ таблицы ; ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 22

Создание таблиц : ALTER TABLE. Применяется для модификации структуры таблицы. Пусть существует таблица TestMy, заданная скриптом CREATE TABLE TestMy ( с ol1 INT NOT NULL, с ol2 INT); Ниже приведены примеры использования оператора ALTER TABLE Добавление колонки ALTER TABLE TestMy ADD COLUMN col3 INT; // Access ALTER TABLE TestMy ADD col3 INT; //SQL Server, Access ALTER TABLE TestMy ADD col3 INT, col4 INT; //SQL Server, Access ALTER TABLE TestMy ADD col3 INT, col4 as col3*0.1; // SQL Server Access вычисляемые поля (col4 as col3*0.1) не поддерживает Удаление колонки ALTER TABLE TestMy DROP COLUMN col3; //SQL Server, Access ALTER TABLE TestMy DROP col3; //Access Редактирование свойств поля (SQL Server, Access) ALTER TABLE TestMy ALTER COLUMN col3 VARCHAR(10) NOT NULL; //SQL Server, Access ALTER TABLE TestMy ALTER col3 VARCHAR(10) NOT NULL; // Access ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 23

Создание таблиц : ALTER TABLE. Добавление ограничения (SQL Server, Access) ALTER TABLE TestMy ADD CONSTRAINT PK_TestMy PRIMARY KEY ( с ol1); // ( если с ol1 NOT NULL) ALTER TABLE TestMy ADD CONSTRAINT FK_TestMy_TM FOREIGN KEY (col2) REFERENCES TestMy2(col2); ALTER TABLE TestMy ADD CONSTRAINT CH_TestMy CHECK (col3 IN( первый, второй, третий )); // SQL Server Добавление поля вместе с ограничением (SQL Server, Access) ALTER TABLE TestMy ADD col2 VARCHAR(20) CONSTRAINT c_col2 Unique NOT NULL; Удаление ограничения (SQL Server, Access) ALTER TABLE TestMy DROP CONSTRAINT c_col2; ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 24

Создание таблиц : CREATE TABLE (примеры) Рисунок 1.1 – Сведения об объектах недвижимости, арендованных клиентом Сидоровым И.И. Результат нормализации (3НФ): Аренда (НомерК(ВК), НомерО(ВК),ДатаН, ДатаО) Клиент (НомерК, ФИОК) Объект (НомерО, АдресО, Плата, НомерВ(ВК)) Владелец (НомерВ, НазвВ) ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 25 стр1Агентство Квартал Сведения об аренде объектов недвижимости Дата Номер клиента: К68 ФИО клиента: СидоровИ.И. Номер объекта Адрес объектаДата начала аренды Дата оконч. аренды ПлатаНомер владельца Наименование владельца Кв4г.Харьков ул.Есенина 6, кв Васильченко В.А. Д5Харьковская область, пос. Русская Лозовая Филатов В.К.

Создание таблиц : CREATE TABLE (примеры) Рисунок 1.2 – Схема БД «Гонки» ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 26

Создание таблиц : CREATE TABLE (примеры) Рисунок 1.3 – Схема БД «Туристическая фирма» ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 27

Вопросы ХНУРЕ кафедра Інформатики доц. Яковлева О.В. 28