Методические указания по выполнению практической части лабораторной работы

В данном примере подробно описано создание БД, создание и редактирование таблиц, атрибутов, наполнение и модификация информации в таблицах, а также использование индексов и ключей.

На примере показано использование следующих операторов:

o CREATE

o DESC

o INSERT

o ALTER

o UPDATE

o DELETE

o SELECT

С применением различных дескрипторов и на различных элементах БД.

На что следует обратить внимание перед началом работы

1) Большинство возникающих ошибок вызываются некорректным синтаксисом. Всегда следует проверять запросы. И настоятельно рекомендуется сперва писать запрос в «Блокноте», (а еще лучше в «Notepad++» и лишь потом копировать их в командную строку.

2) Следует отличать ` ` и ‘ ’ . Это два разных символа. Первый в латинской раскладке на месте буквы «ё», второй на месте русской «э».

` ` - используется для названий атрибутов, БД, таблиц итд. В принципе, данные кавычки можно опускать, они необходимы лишь в случае использования в названии каких-либо символов (тире, пробел итд)

‘ ’ – Используется для выделения строковых данных и данных даты и времени.

3) MySQL по умолчанию НЕ чувствителен к регистру. Но принято любые команды писать большими буквами, а любые данные – мелкими. (Если не указать специально, то абсолютно не важно, с маленькой или с большой буквы вводятся названия таблиц, БД, атрибутов).

4) Связь может быть реализована между атрибутами с абсолютно идентичными типами данных. INT UNSIGNED и INT – разные типы данных.

5) FOREIGN KEY обязательно должен ссылаться на уникальный атрибут (Например, на PRIMARY KEY или UNIQUE)

6) Комментарии выделены двумя способами:

-- Комментарий	В случае комментирования строки
/* Комментарий */	В случае комментирования нескольких строк

7) Текст запросов помещен в рамку.

Начало работы с MySQL

1) Запустить командную строку (В Windows 7 ОБЯЗАТЕЛЬНО от имени Администратора).

2) Подключиться к серверу с помощью команды

Mysql –uroot –ppassowrd

3) Установить необходимую кодировку клиента с помощью команды

SET NAMES ‘cp866’;

Все эти три пункта следует делать каждый раз при запуске консоли.

Рассмотрим создание БД на примере БД для Интернет-продаж

Интернет-магазины реализуют потребителям бытовую технику (утюги, чайники, кофеварки) разных моделей известных фирм-производителей (Philips, Bosh, Mulinex и др.). Заказы осуществляются клиентами в интернет-магазинах и доставляются клиенту по адресу.

Данная БД содержит пять таблиц со следующими атрибутами:

· Заказы (Код заказа, Код магазина, Код товара, Дата заказа, Количество, Код клиента);

· Товары (Код товара, Название товара, Фирма, Цена);

· Клиенты (Код клиента, Телефон клиента, Адрес Клиента);

· Интернет-магазины (Код магазина, Электронный адрес, Оплата доставки (да/нет);

· Доставка (Код доставки, Код заказа, Дата и время доставки, Код клиента).

ü При проектировании нужно учесть, что один товар может продаваться в нескольких интернет-магазинах, Интернет-магазин может предлагать к продаже несколько товаров.

ü Товар может быть связан с несколькими заказами. Заказ связан с одним товаром.

ü В интернет-магазине может осуществляться несколько заказов. Заказ связан с одним интернет-магазином.

ü Заказ подлежит одной доставке одному клиенту. Клиент может быть связан с несколькими доставками, доставка связана с одним заказом. Клиент также связан с несколькими заказами.

Создадим новую БД

IF NOT EXISTS, добавленное к команде CREATE, не позволит создать БД или таблицу, если БД или таблица с таким именем уже существует.

CREATE SCHEMA IF NOT EXISTS `Интернет-продажи`;

Командой USE мы определяем, с какой БД будем работать в данный момент.

USE `Интернет-продажи` ;

4.3.2 Создадим таблицу «Интернет-Магазины»

CREATE TABLE IF NOT EXISTS `Интернет-Магазины` -- Создадим новую таблицу.

(

`Код Магазина` INT UNSIGNED NOT NULL AUTO_INCREMENT , -- Столбец беззнаковый, ненулевой, автозаполняющийся.

`Электронный адрес` VARCHAR(45) NULL , -- Может быть *нулевым*, т.е. не иметь значения вообще.

`Оплата доставки` ENUM('да','нет') NOT NULL , -- Столбец может принимать значение только «да» или «нет».

PRIMARY KEY (`Код Магазина`) -- Объявление первичного ключа.

);

Проверим структуру созданной таблицы

DESC `Интернет-Магазины`; -- DESC сокращенно от DESCRIBE. Можно использовать оба варианта.

4.3.3 Создадим таблицу «Товары»

DROP TABLE IF EXISTS `Товары` ; -- Удалить таблицу «Товары», если такая уже существует.

CREATE TABLE IF NOT EXISTS `Товары`

(

`Код Товара` INT UNSIGNED NOT NULL AUTO_INCREMENT ,

`Название товара` VARCHAR(85) NOT NULL ,

`Фирма` VARCHAR(55) NOT NULL ,

`Цена` FLOAT NULL ,

PRIMARY KEY (`Код Товара`)

);

Модифицируем таблицу оператором ALTER.

Добавим индекс. Индекс ускоряет поиск по данному столбцу, за счет создания предварительной сортировки.

ALTER TABLE `Товары` ADD INDEX `ixFirm` (`Фирма` ASC) ; -- ASC означает, что сортировка будет происходить по возрастанию. (DESC –по убыванию). В данном случае это не принципиально.

Проверим правильность структуры таблицы.

DESC `Товары`; -- `` обязательно ставить лишь в случае наличия дополнительных символов, например тире или пробела. Но любые средства разработки, например Workbench ставит их везде по умолчанию.

4.3.4 Создадим таблицу «Клиенты»

DROP TABLE IF EXISTS `Клиенты` ;

CREATE TABLE IF NOT EXISTS `Клиенты`

(

`Код Клиента` INT UNSIGNED NOT NULL AUTO_INCREMENT ,

`ФИО клиента` VARCHAR(80) NOT NULL ,

`Телефон клиента` INT NULL , -- В дальнейшем изменим тип на строковый – VARCHAR.

`Адрес клиента` VARCHAR(150) NULL ,

PRIMARY KEY (`Код Клиента`) ,

INDEX `ixClient` (`ФИО клиента` ASC)

);

Модифицируем таблицу. Изменим столбец «Телефон клиента», сменив для него тип данных на строковый. Для номеров телефонов лучше использовать тип данных VARCHAR, т.к. в этом случае удобнее осуществлять поиск. Например, можно будет проводить поиск по трем первым цифрам номера.

Заодно сделаем ввод номера телефона обязательным.

ALTER TABLE Клиенты MODIFY `Телефон клиента` VARCHAR(7) NOT NULL; -- Семь символов для телефона в данном примере достаточно.

Проверим правильность параметров таблицы.

DESC Клиенты;

Создадим таблицу «Заказы» c лишней, ненужной колонкой.

DROP TABLE IF EXISTS `Заказы` ;

CREATE TABLE IF NOT EXISTS `Заказы`

(

`Код Заказа` INT UNSIGNED NOT NULL AUTO_INCREMENT ,

`Код магазина` INT UNSIGNED NOT NULL ,

`Код товара` INT UNSIGNED NOT NULL ,

`Количество` INT NOT NULL ,

`Код клиента` INT UNSIGNED NOT NULL ,

`Лишняя колонка` INT NOT NULL, -- Ненужная колонка, которая будет удалена далее оператором ALTER TABLE.

PRIMARY KEY (`Код Заказа`) ,

INDEX `FKShopCod` (`Код магазина` ASC) , -- Создадим индекс по столбцу «Код магазина».

INDEX `FKGoodsCod` (`Код товара` ASC) ,

INDEX `FKClient` (`Код клиента` ASC),

CONSTRAINT `FKShopCod` -- Создадим ограничение на созданный индекс,

FOREIGN KEY (`Код магазина` ) -- превратив его во внешний ключ,

REFERENCES `Интернет-Магазины` (`Код Магазина` ), -- ссылающийся на столбец «Код магазина» из таблицы «Интернет-магазины».

CONSTRAINT `FKGoodsCod`

FOREIGN KEY (`Код товара` )

REFERENCES `Товары` (`Код Товара` ),

CONSTRAINT `FKClient`

FOREIGN KEY (`Код клиента` )

REFERENCES `Клиенты` (`Код Клиента` )

);

Примечание: MySQL 5, движок InnoDB поддерживает ссылочную целостность. Это означает, что задав вышеупомянутый внешний ключ, значениями атрибута «Код магазина» таблицы «Заказы» могут являться только те значения, которые уже существуют в одноименном столбце таблицы «Интернет-магазины». Проще говоря, нельзя сделать заказ по несуществующему в БД магазину. Аналогично два других внешних ключа.

Модифицируем таблицу.

В этот удалим не нужную нам колонку, и создадим другую, содержащую дату заказа.

ALTER TABLE заказы -- Изменим таблицу «Заказы».

ADD COLUMN `Дата заказа` DATE NULL, -- Добавим колонку «Дата заказа» типа DATE, которая не обязательна для заполнения (NULL).

DROP COLUMN `Лишняя колонка`; -- Удалим «лишнюю колонку».

Проверим правильность структуры таблицы.

DESC Заказы;

4.3.5 Создадим таблицу «Доставка»

DROP TABLE IF EXISTS `Доставка` ;

CREATE TABLE IF NOT EXISTS `Доставка`

(

`Код доставки` INT UNSIGNED NOT NULL AUTO_INCREMENT ,

`Код заказа` INT UNSIGNED NOT NULL ,

`Дата и время доставки` DATETIME NULL ,

`Код клиента` INT UNSIGNED NOT NULL ,

PRIMARY KEY (`Код доставки`) ,

INDEX `FKOrder` (`Код заказа` ASC) ,

INDEX `FKClient2` (`Код клиента` ASC) , -- Важно! В системе не должно быть двух индексов с одинаковыми именами!

CONSTRAINT `FKOrder`

FOREIGN KEY (`Код заказа` )

REFERENCES `Заказы` (`Код Заказа` ),

CONSTRAINT `FKClient2`

FOREIGN KEY (`Код клиента` )

REFERENCES `Клиенты` (`Код Клиента` )

);

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

DESC Доставка;

*Примечание: В данном случае удобно в конце строки «;» заменить на «\G» - это преобразует результат в «вертикальную форму». Так стоит делать, если в стандартном представлении строки наползают друг на друга.

DESC Доставка \G

Теперь выполним заполнение таблиц.

4.3.6 Заполним таблицу «Интернет-Магазины»

INSERT INTO `Интернет-Магазины` (`Электронный адрес`, `Оплата доставки`) -- Указываем, какие столбцы мы собираемся заполнять. Первый столбец опустим т.к. при создании таблицы мы указали, чтобы он заполнялся автоматически.

VALUES ('[email protected]','да'),

('[email protected]','да'),

('[email protected]','нет'),

('[email protected]','да'),

('[email protected]','да');

Проверим правильность заполнения оператором SELECT.

SELECT * FROM `Интернет-магазины`;

4.3.7 Заполним таблицу «Товары»

INSERT INTO Товары -- Можно не указывать, какие именно столбцы будут заполняться данным запросом. В таком случае необходимо будет заполнить все столбцы. Причем, не в том порядке, в котором мы их обозначали в таблице, а в том, в каком они реально существуют в БД.

VALUES (NULL,'Чайник','Braun',1500), -- Т.к. для первого столбца мы при создании таблицы поставили автозаполнение, то при вводе значений в таблицу, данный столбец заполнять не нужно. В матрице данных отсутствие значение обозначаем NULL.*

(NULL,'Пылесос','Samsung',3500),

(NULL,'Электровеник','Yamaha',999),

(NULL,'Микроволновка','Whirlpool',5000),

(NULL,'Электротерка','Yamaha',999),

(NULL,'Кофеварка','Braun',1500),

(NULL,'Холодильник','Samsung',5800),

(NULL,'R2-D2','Sony',150000);

*NULL – это не ноль. Это отсутствие вообще, какого бы ни было значения. Важно понимать следующие моменты: NULL ≠ 0, NULL+1 = NULL.

Проверим заполнение таблицы.

SELECT * FROM Товары;

Мы видим, что колонка «Код товара» заполнилась автоматически. Если же попробовать указать уже существующий номер кода товара, например:

INSERT INTO Товары

VALUES (5,'Золотой Самовар','Braun',800000);

Получим следующую ошибку:

Повторение «5» для первичного ключа. А первичный ключ по определению должен быть уникальным. Программа не позволяет нарушить это правило.

4.3.8 Заполним таблицу «Клиенты»

INSERT INTO Клиенты

VALUES (NULL, 'Петров И.И.', '1234567', 'Москва, ул. Пахарей 17-5'),

(NULL, 'Иванов П.П.', '8901234', 'Москва, ул. Слесарей 22-1д-2'),

(NULL, 'Сумкин Ф.Б.', '5678901', 'МО, Химки, ул. Улицы 42-123'),

(NULL, 'Сидоров С.М.', '4145252', 'Москва, ул. Бауманская 212-2'),

(NULL, 'Пушкин С.А.', '9982989', 'Магадан, ул. Пушкина 12-44');

Допустим, мы не хотим, чтобы в нашу таблицу могли ввести второго человека с такой же фамилией и инициалами.

Для этого Модифицируем таблицу «Клиенты», введя уникальный индекс, который запретит вводить повторяющиеся значения по столбцу «ФИО Клиента».

Для начала удалим уже существующий НЕуникальный индекс.

ALTER TABLE Клиенты DROP INDEX `ixClient`;

Добавим уникальный индекс, накладывающий ограничение на столбец «ФИО Клиента»:

ALTER TABLE Клиенты ADD CONSTRAINT `ixName` UNIQUE INDEX (`ФИО Клиента`);

Теперь, если попробовать ввести повторяющееся значение фамилии, то команда не пройдет.

INSERT INTO Клиенты

VALUES (NULL, 'Петров И.И.', '4440102', 'Москва, ул. Сезам 1-5');

Предположим, у нас есть список фамилий, которые необходимо ввести и среди этого списка могут встречаться повторяющиеся. В этом случае ни одна из фамилий списка не будет введена. Одна единственная повторяющаяся фамилия отменит всю команду. Чтобы этого не происходило, и все фамилии благополучно записались в БД проигнорировав повтор, нужно использовать команду IGNORE.(Повторенная фамилия записана не будет, зато запишутся все остальные.)

INSERT IGNORE INTO Клиенты

VALUES (NULL, 'Петров Т.П.', '4440102', 'Москва, ул. Сезам 1-5'),

(NULL, 'Сидорова А.К.', '4145252', 'Москва, ул. Бауманская 212-2'),

(NULL, 'Пушкин С.А.', '9982989', 'Магадан, ул. Пушкина 12-44');

Проверим правильность заполнения.

SELECT * FROM Клиенты;

Как мы видим, второй «Пушкин С.А.» записан не был.

Исправим инициалы первого (и единственного в нашей БД) Пушкина оператором UPDATE:

UPDATE Клиенты -- Обновить таблицу клиенты

SET `ФИО Клиента`= 'Пушкин А.С.' -- Установить «Пушкин А.С» в столбце «ФИО Клиента»

WHERE `ФИО Клиента` = 'Пушкин С.А.'; -- У тех строк, где «ФИО Клиента» равняется «Пушкин С.А.»

Можно было сделать то же самое, и другим способом:

UPDATE Клиенты -- Обновить таблицу клиенты

SET `ФИО Клиента`= 'Пушкин А.С.' -- Установить «Пушкин А.С» в столбце «ФИО Клиента»

WHERE `Код клиента` = 5; -- У тех строк, где «Код Клиента» равняется 5

Теперь удалим последнего Клиента из Таблицы.

DELETE FROM Клиенты

WHERE `Код Клиента` = 8;

*Чтобы удалить ВСЕ строки из таблицы следует написать « DELETE FROM Клиенты;»

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

При этом следует обратить внимание, как записываются данные типа DATE. Существует правило: От большего к меньшему. То есть год-месяц-число (час-минуты-секунды).

INSERT INTO Заказы (`Код магазина`, `Код товара`, `Дата заказа`, `Количество`, `Код клиента`)

VALUES (1,1,'2010-01-11',1,2),

(2,2,'2008-01-31',1,3),

(2,2,'2007-01-09',2,4),

(3,4,'2002-01-01',1,5),

(2,1,'2011-02-22',2,2),

(4,1,'2006-08-30',1,1),

(5,6,'2005-07-30',1,3),

(1,3,'2009-06-30',2,4),

(5,5,'2009-05-30',1,1);

4.3.10 Заполним таблицу «Доставка»

Хоть MySQL пятой версии и поддерживает ссылочную целостность (FOREING KEY), т.к. таблицу «Доставка» мы заполняем отдельно, то мы вполне можем доставить товар другому клиенту (не тому, кто его заказывал).

В данном случае в таблице «Доставка» есть столбец «Код заказа», который ссылается на соответствующий столбец в таблице «Заказы», который имеет функцию АВТОЗАПОЛНЕНИЯ. Автозаполнение работает таким образом, что даже после удаления строк оно продолжает свой счет. То есть после нескольких пробных попыток или серии удалений вполне может получиться следующая картина:

SELECT * FROM Заказы;

И это следует учитывать при заполнении таблицы «Доставки». Поэтому запрос будет выглядеть следующим образом:

INSERT INTO Доставка (`Код заказа`, `Дата и время доставки`, `Код клиента`)

VALUES (28, '2010-01-11 20:00:00', 2),

(29, '2008-01-31 22:50:00', 3),

(30, '2007-02-10 20:00:00', 4),

(31, '2010-01-15 06:00:00', 5),

(32,NULL, 2); -- Т.к. на столбце не стоит ограничения, можно оставить ячейку без значения.