5 СЕМЕСТР / ЛР / ЛР2 / ЛР_№2_БД_MySQL_РеализацияБД_с_примером

Лабораторная работа №2

Создание БД на сервере MySQL

Цель работы: изучить операции по созданию и заполнению базы

данных на сервере MySQL.

Базовые сведения.

Впредыдущей лабораторной работе требовалось выполнить простейшие операции по созданию и заполнению таблиц БД. Здесь рассматриваются более сложные операции: связывание таблиц и изменение макета таблицы.

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

CREATE TABLE tab1 (

ID int NOT NULL PRIMARY KEY,

FIO char(40));

CREATE TABLE tab2 (

ID int,

NAME char(50) NOT NULL,

PRIMARY KEY (ID));

Для организации связей между таблицами БД используются внешние

ключи. Внешним ключом может быть один или несколько столбцов,

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

Внешний ключ обозначается словосочетанием FOREIGN KEY.

Синтаксис внешнего ключа выглядит следующим образом.

FOREIGN KEY (имя_столбца_в_дочерней_таблице) REFERENCES

имя_родительской_таблицы (имя_ключевого столбца_в_родительской_таблице).

Объявление в дочерних таблицах внешних ключей позволяет обеспечить встроенную защиту целостности данных. При попытке выполнить запрос

INSERT или UPDATE к дочерней таблице сервер автоматически проверит,

существует ли новое значение внешнего ключа в родительской таблице. Если это значение отсутствует, запрос не будет выполнен и целостность данных не будет нарушена.

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

-Во всех записях дочерней таблицы найденное значение стирается, т.е. в

поле внешнего ключа записывается значение NULL.

-Все записи, содержащие во внешнем ключе найденное значение,

удаляются из дочерней таблицы (каскадное обновление / удаление).

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

ON UPDATE (SET NULL или CASCADE),

ON DELETE (SET NULL или CASCADE) .

Для изменения свойств уже созданной таблицы используется оператор

ALTER TABLE имя изменяемой таблицы. Например, возможны следующие изменения.

Добавление нового столбца в таблицу:

ADD имя_нового_столбца свойства_нового_столбца [FIRST | AFTER

имя_существующего_столбца].

Добавление первичного ключа в таблицу:

ADD PRIMARY KEY (имя_столбца).

Изменение описания столбца:

CHANGE имя_столбца новое_имя_столбца свойства_столбца .

Удаление столбца из таблицы:

DROP имя_столбца.

Удаление первичного ключа таблицы:

DROP PRIMARY KEY.

Переименование таблицы:

RENAME новое_имя_таблицы.

Если таблица содержит только один столбец, то этот столбец не может быть удален.

Удаление таблицы, выполняется командой DROP TABLE.

В заключение рассмотрим несколько примеров.

Создаем таблицу «Таблица1», где C_NO – первичный ключ; поля C_NO и

FIO являются обязательными для заполнения (NOT NULL); типы полей указаны сразу после названия поля.

CREATE TABLE TAB1

(

C_NO int NOT NULL PRIMARY KEY,

FIO char(40) NOT NULL,

ADDR char(30),

PHONE char(11) );

Переименуем созданную таблицу в таблицу «Клиенты».

ALTER TABLE TAB1

RENAME CLIENTS;

Создаем таблицу «Заказы», где O_NO – первичный ключ; все поля

являются обязательными для заполнения; типы полей указаны сразу после названия поля; C_NO – внешний ключ, связывающий таблицу «Заказы» и «Клиенты» по полю C_NO; доступно каскадное обновление данных в дочерней таблице.

FOREIGN KEY (C_NO) REFERENCES CLIENTS (C_NO)

ON UPDATE CASCADE );

Изменим таблицу «Заказы»: добавим в таблицу обязательное для

заполнения поле «тип заказа», поместив его после поля O_NO.

ALTER TABLE ORDERS

ADD TYPE char(30) NOT NULL AFTER O_NO;

Заполняем таблицу «Клиенты». Первые две строки таблицы заполняются полностью, в последней заполняем только обязательные поля

(C_NO, FIO).

insert into CLIENTS values (‘1’, ‘Ivanov A.A.’, ‘Nevskiy 54’, ‘9876789’); insert into CLIENTS values (‘2’, ‘Petrov B.G.’, ‘Borovaya 12’, ‘6789543’); insert into CLIENTS (C_NO, FIO) values (‘3’, ‘Zayceva S.F.’);

Заполняем таблицу «Заказы».

insert into ORDERS values (‘1’, ‘type1’, ‘2’); insert into ORDERS values (‘2’, ‘type2’, ‘3’);

Порядок выполнения работы.

1.Создать новую БД на сервере MySQL.

2.Создать необходимые таблицы и установить связи между ними.

3.Заполнить таблицы данными.

4.Выполнить 3 инструкции ALTER TABLE .

Содержание отчета

1.Цель работы.

2.Схема данных.

3.Описание выполненных действий на языке SQL.

4.Скриншоты созданных таблиц.

Вопросы для самопроверки

1.Что такое первичный, вторичный ключ?

2.Как образуется связь между таблицами?

3.Что значит каскадное обновление таблиц?

4.Какие изменения в таблицу можно внести при помощи оператора

ALTER TABLE?