Создание и модификация структуры таблицы

Дата добавления: 2014-11-24 | Просмотров: 1505

Оператор определения структуры таблицы имеет следующий син­таксис:

CREATE TABLE <имя таблицы>

(<элемент таблицы> [{,<элемент таблицы>}...]),

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

Определение столбца описывается следующим образом

<имя столбца> <тип данных> [<размер>]

[<раздел значения столбца по умолчанию>]

[<раздел ограничений целостности столбца>...],

Раздел значения столбца по умолчанию описывается в свою очередь так:

DEFAULT { <константа> | USER | NULL },

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

- константа с типом, соответствующим типу столбца;

- либо ключевое слово USER, которому при выполнении оператора зане­сения строки соответствует символьная строка, содержащая имя текущего пользователя (в этом случае столбец должен иметь тип символьных строк)

- либо ключевое слово NULL, означающее, что значением по умолчанию является неопределенное значение.

В разделе ограничений целостности столбца указывается один из следующих вариантов:

- NOT NULL [<спецификация уникальности>];

- ограничение по ссылкам

REFERENCES <ссылаемая таблица > [(<список столбцов>)];

- CHECK (<условие>).

Если ограничение NOT NULL не указано, и раздел умолчаний от-^ сутствует, то неявно порождается раздел умолчаний DEFAULT NULL.

Если указано проверочное ограничение столбца CHECK, то условие поиска этого ограничения должно ссылаться только на данный столбец.

Раздел определения ограничений целостности таблицы позволяет задать ограничение уникальности, ограничение по ссылкам и/или опреде­ление проверочного ограничения

Для определения ограничения уникальности указывается одна из спецификаций уникальности (UNIQUE или PRIMARY KEY), после кото­рой в скобках задается список соответствующих столбцов таблицы.

Пусть Т обозначает таблицу, для которой определяются ограничения

целостности.

Каждое имя столбца в списке уникальности должно именовать стол­бец Т и не должно входить в этот список более одного раза. При опреде­лении столбца, входящего в список уникальности, должно быть указано ограничение столбца NOT NULL. Среди ограничений уникальности Т не должно быть более одного определения первичного ключа (ограничения уникальности с ключевым словом PRIMARY KEY).

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

Пример. Создать таблицу Заказчики CREATE TABLE Заказчики

( Номер_заказчика integer NOT NULL, Фамилия_заказчика char (10) NOT NULL, Город char(10), Рейтинг integer, Номер_продавца integer NOT NULL, UNIQUE (Номер_заказчика));

Ограничение по ссылкам определяется ключевыми словами FOREIGN KEY, затем в скобках перечисляются столбцы, для которых за­дается это ограничение, и далее указывается ссылочная спецификация.

Ссылочная спецификация описывается следующим образом REFERENCES <ссылаемая таблица > [(<список столбцов>)]

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

Пример

CREATE TABLE Заказы

(Номер_заказа integer NOT NULL PRIMARY KEY,

Сумма decimal,

Дата_заказа date NOT NULL,

Номер_заказчика integer NOT NULL

Номер_продавца integer NOT NULL

FOREIGN KEY (Номер_продавца, Номер_заказчика) REFERENCES

Заказчики (Номер_продавца, Номер_заказчика);

Внешний ключ не обязательно состоит только из одного поля. По­добно первичному ключу, внешний ключ может иметь любое число по­лей, которые все обрабатываются как единый модуль. Внешний ключ и родительский ключ, на который он ссылается, должны иметь одинаковое количество полей и совпадающие типы полей. Каждое значение во внеш­нем ключе должно быть представлено один и только один раз в родитель­ском ключе. Внешний ключ может содержать только те значения, которые фактически представлены в родительском ключе или пустые (NULL).

Любые значения, которые помещаются командой INSERT или UPDATE в поля, определенные как внешние ключи, должны уже быть представлены в их родительских ключах. Можно помещать пустые (NULL) значения в эти поля, несмотря на то, что значения NULL запре­щены в родительских ключах, если они имеют ограничение NOT NULL. Можно удалять (DELETE) любые строки с внешними ключами, не ис­пользуя родительские ключи вообще.

Ограничение FOREIGN KEY может указать имя создаваемой в те­кущем операторе таблицы как таблицы родительского ключа.

Пример. Пусть таблица Служащие должна иметь поле Менеджер. Это поле содержит номера менеджера каждого из служащих, если такой есть. Но так как каждый менеджер в то же время остается служащим, то он будет также представлен в этой таблице. Необходимо создать таблицу Служащие, где поле Номер_служащего объявляется как первичный ключ, а поле Менеджер как внешний ключ, ссылающийся на саму таблицу Слу­жащие:

CREATE TABLE Служащие (Номер_служащего integer NOT NULL

PRIMARY KEY, Фамилия char(10) NOT NULL UNIQUE, Менеджер integer REFERENCES Служащие);

Так как внешний ключ ссылается на первичный ключ таблицы, спи­сок столбцов может быть исключен.

Возможное содержание таблицы Служащие:

Номер_служащего Фамилия___Менеджер

Внешний ключ, который ссылается на свою же таблицу, должен по­зволять неопределенные значения NULL. Иначе нельзя было бы вставить

первую строку.

Для задания определения проверочного ограничения указывается

ключевое слово CHECK и в скобках условие проверки.

Проверочное ограничение определяет условие, которому должна удовлетворять в отдельности каждая строка таблицы Т. Это условие не должно содержать подзапросов, спецификаций агрегатных функций, а также ссылок на внешние переменные или параметров. В него могут вхо­дить только имена столбцов данной таблицы и константы.

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

заданное условие.

Пример. Создать таблицу Продавцы CREATE TABLE Продавцы

(Номер_продавца integer NOT NULL UNIQUE, Фамилия_продавца char(l 0) NOT NULL UNIQUE, Город char(10) CHECK

(Город IN ("Киев", 'Одесса', 'Донецк')), Комиссионные decimal CHECK (Комиссионные < 1 ));

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

Удаление таблиц осуществляется оператором

DROP TABLE <имя таблицы>;

Изменение структуры уже существующей таблицы выполняется оператором

ALTER <имя таблицы>

ADD/DROP (<имя столбца> <тип данных> [,<имя столбца> <тип данных>...]);

Желательно как можно реже обращаться к этому оператору, так как можно стереть нужные данные. Лучше создать новую таблицу и исполь­зовать команду INSERT - SELECT для перезаписи в новую таблицу данных из старой таблицы.

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

1. Вывести номера заказов, сумму и дату для всех строк из таблицы Заказы.
2. Вывести все строки из таблицы Заказчики, в которых номер про­давца равен 1001.
3. Вывести рейтинг и фамилию каждого заказчика из города Киев.
4. Вывести фамилии продавцов из города Одесса, у которых комис­сионные выше 0.1.
5. Вывести список всех заказчиков с рейтингом не выше 100, кроме тех, которые проживают в Киеве.
6. Что будет выведено в результате выполнения следующего запро­са?

SELECT *

FROM Заказы

WHERE (Сумма < 1000 OR

i. NOT (Дата_заказа = 10.03.99 AND Номер_заказчика > 2003));

1. Определить общую сумму заказов на 3 октября текущего года.
2. Пусть каждый продавец имеет комиссионные в размере 12%. Вы­вести номер заказа, номер продавца и сумму комиссионный для каждого заказа.
3. Вывести список заказчиков в порядке убывания рейтинга. Фами­лии заказчиков сопровождать их номером и рейтингом.
4. Вывести общие суммы заказов на каждый день в порядке убыва­ния этих сумм.
5. Определить минимальную сумму заказа для каждого заказчика.
6. Используя подзапрос, выбрать все заказы заказчика Иванова.
7. Вывести фамилии и рейтинги всех заказчиков, имеющих среднее значение рейтинга.
8. Используя соотнесенный подзапрос, выбрать фамилии и номера всех заказчиков с максимальным для их города рейтингом.
9. Поместить следующие значения в таблицу Продавцы в указанном порядке: в поле Город - Киев, в поле Фамилия - Петров, в поле Комисси­онные - NULL, в поле Номер_продавца - 1100.
10. Удалить все заказы заказчика Сидорова из таблицы Заказы.
11. Увеличить рейтинг всех заказчиков города Одесса на 100.
12. Создать таблицу Студент с полями Номер_зачетной_ книжки, Фамилия, Имя, Отчество, Год_рождения, Номер_группы.
13. Создать таблицу Заказы так, чтобы все значения поля Но-мер_заказа, а также все комбинации значений полей Номер_заказчика и Номер_продавца отличались друг от друга, и так, чтобы значения NULL исключались в поле Дата_заказа.

Список рекомендуемой литературы

1. Карпова Т.С «Базы данных: модели, разработка, реализация» СПб.: Питер - 2002.

1. Microsoft Corporation. «Проектирование и реализация баз данных Microsoft SQL Server 2000» Москва - 2003

3. Марков А.С., Лисовский К.Ю. «Базы данных. Введение в теорию и методологию» Москва - 2006 .

4. С.Д. Кузнецов. «Основы современных баз данных» Центр Информационных технологий МГУ, 2006.

5. Т.Ф. Лебедева. «Базы данных» Учебное пособие. Кемерово -2006 г.

1. А. Горев, С. Макашарипов, Р. Ахаян. Эффективная работа с СУБД