Лабораторная работа №4 "СОЗДАНИЕ И СВЯЗЫВАНИЕ ТАБЛИЦ В СРЕДЕ MYSQL"

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

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

Все идентификаторы, кроме имен псевдонимов, могут содержать до 64 сим­волов. Имена псевдонимов могут иметь до 255 символов.

Идентификаторы могут содержать любые допустимые символы, но имена баз данных не могут содержать символы /, \ и . , а имена таблиц – символы . и /.

Зарезервированные слова можно использовать для идентификаторов, если заключить их в кавычки.

Комментарий в SQL. Начинается с двух дефисов (--), за которыми должен следовать пробел. Кроме того, MySQL содержит ряд собственных комментариев. Shell-комментарий # действует аналогично – все, что расположено правее его, является текстом комментария. С-комментарий /* */ является многострочным – комментарий начинается с /* и заканчивается, когда встретится завершение */.

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

create database имя_базы_данных;

Убедиться в том, что оператор выполнил задачу, можно с помощью оператора

show databases;

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

use имя_базы_данных;

Теперь все действия по умолчанию будут применяться именно к этой БД.

Создание таблиц. Используется оператор CREATE TABLE, который в общем виде выглядит следующим образом:

create [temporary] table [if not exists]

имя_таблицы (определение таблицы)

[type=тип_таблицы];

Ключевое слово TEMPORARY используется для создания таблиц, которые будут существовать только в текущем сеансе работы с БД и будут автоматически удалены, когда сеанс завершится.

При использовании выражения IF NOT EXISTS таблица будет создана толь­ко в том случае, если еще нет таблицы с указанным именем.

Создать таблицу с такой же схемой, как у существующей, позволяет команда

create [temporary] table [if not exists]

имя_таблицы LIKE имя_старой_таблицы;

После имени таблицы в скобках объявляются имена столбцов, их типы и другая информация. В определение столбца можно добавить следующие описания.

• Объявить для любого столбца NOT NULL или NULL (столбцу запрещено или не запрещено содержать значения NULL). По умолчанию – NULL.

• Объявить для столбца значение по умолчанию, используя ключевое слово DEFAULT, за которым должно следовать значение по умолчанию.

• Использовать ключевое слово AUTO_INCREMENT, чтобы генерировать порядковый номер. Автоматически генерируемое значение будет на единицу большим, чем наибольшее значение в таблице. Первая введенная строка будет иметь порядковый номер 1. В таблице можно иметь не более одного столбца AUTO_INCREMENT, и он должен индексироваться.

• Объявить столбец первичным ключом таблицы с помощью выражения PRIMARY KEY.

• Объявить столбец внешним ключом, используя выражение FOREIGN KEY, с ссылкой на соответствующую таблицу с помощью выражения REFERENCES.

• Индексировать столбец с помощью слов INDEX или KEY (синонимы). Такие столбцы не обяза­тельно должны содержать уникальные значения.

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

• Создать полнотекстовые индексы на основе столбцов типа TEXT, CHAR или VARCHAR с помощью слова FULLTEXT (только с таблицами MyISAM).

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

• MyISAM – таблицы этого типа являются «родными» для MySQL, работают очень быстро и поддерживают полнотекстовую индексацию;

• InnoDB – ACID-совместимый механизм хранения, поддерживающий транзакции, внешние ключи, каскадное удаление и блокировки на уровне строк;

• BDB (Berkeley DB) – является механизмом хранения, который обеспечивает поддержку транзакций и блокировки на уровне страниц;

• MEMORY (HEAP) – таблицы целиком хранятся в оперативной памяти и никогда не записываются на диск, поэтому работают очень быстро, но ограничены в размерах и не допускают возможности восстановления в случае отказа системы;

• MERGE – тип позволяет объединить несколько таблиц MyISAM с одной структурой, чтобы к ним можно было направлять запросы как к одной таблице;

• NDB Cluster – тип предназначен для организации кластеров MySQL, когда таблицы распределены между несколькими компьютерами, объединенными в сеть;

• ARCHIVE – тип введен для хранения большого объема данных в сжатом формате; таблицы поддерживают только два SQL-оператора: INSERT и SELECT, причем оператор SELECT выполняется по методу полного сканирования таблицы;

• CSV – формат представляет собой обычный текстовый фал, записи в котором хранятся в строках, а поля разделены точкой с запятой (широко распространен в компьютерном мире, любая программа, поддерживающая CSV-формат, может открыть такой файл);

• FEDERATED – тип позволяет хранить данные в таблицах на другой машине сети (при создании таблицы в локальной директории создается только файл определения структуры таблицы, а все данные хранятся на удаленной машине).

MySQL поддерживает следующие типы данных, допустимые для столбцов:

• числовые;

• строковые;

• календарные;

• null – специальный тип, обозначающий отсутствие информации.

Числовые типы используются для хранения чисел и представляют два подтипа:

• точные числовые типы;

• приближенные числовые типы.

К точным числовым типам (табл. 1) относятся целый тип INTEGER и его вариации, а также вещественный тип decimal (синонимы numeric и dec). Последний используется для представления денежных данных.

Числовые типы могут характеризоваться максимальной длиной М. Для типа decimal параметр м задает число символов для отображения всего числа, a d – для его дробной части. Например: b_price DECIMAL (5, 2). Цифра 5 определяет общее число символов под число, а цифра 2 – количество знаков после запятой (ин­тервал величин от –99.99 до 99.99). Можно не использовать параметры вообще, указать только общую длину или указать длину и число десятичных разрядов.

Объявления точных числовых типов можно завершать ключевыми словами UNSIGNED и (или) ZEROFILL. Ключевое слово UNSIGNED указывает, что столбец содержит только положи­тельные числа или нули. Ключевое слово ZEROFILL означает, что число будет отображаться с ведущими нулями.

Таблица 1

К приближенным числовым типам (табл. 2) относятся:

• FLOAT – представление чисел с плавающей запятой с обычной точностью;

• DOUBLE – представление чисел с плавающей запятой с двойной точностью.

Таблица 2

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

Приближенные числовые данные могут задаваться в обычной форме (например, 45.67) и в форме с плавающей точкой (например, 5.456Е-02 или 4.674Е+04).

Текстовые типы и строки (табл. 3):

• CHAR – хранение строк фиксированной длины;

• VARCHAR – хранение строк переменной длины;

• TEXT, BLOB и их вариации – хранение больших фрагментов текста;

• ENUM и SET – хранение значений из заданного списка.

Таблица 3

Здесь l – длина хранимой в ячейке строки, а приплюсо­ванные к l байты – накладные расходы для хранения длины строки.

Для строк varchar требуется количество символов, равное длине строки плюс 1 байт, тогда как тип char(m), независимо от длины строки, ис­пользует для ее хранения все м символов. Тип char обрабатывается эф­фективнее переменных типов. Нельзя смешивать в таблице столбцы char и varchar. Если есть столбец переменной длины, все столбцы типа char будут приведены к типу varchar.

Типы blob и text аналогичны и отличаются в дета­лях. При выполнении операций над столбцами типа text учитывается ко­дировка, а типа blob – нет. Тип text используется для хранения больших объемов текста, тип blob – для больших двоичных объектов (электронные документы, изобра­жения, звук). Основное отличие text от char и varchar – поддержка полнотекстового поиска.

Строки типов данных enum и set принимают значе­ния из заданного списка. Значение типа enum должно содержать точно одно зна­чение из указанного множества, тогда как столбцы set могут содержать любой или все элементы заданного множества одновременно. Для типа set (как и для enum) при объявлении задается список возможных значений, но ячейка может принимать любое значение из списка, а пустая строка оз­начает, что ни один из элементов списка не выбран.

Типы enum и set задаются списком строк, но во внутреннем представлении элементы множеств сохраняются в виде чисел. Элементы типа enum нумеруются последова­тельно, начиная с 1. Под столбец может отводиться 1 байт (до 256 элементов в списке) или 2 байта (от 257 до 65536 элемен­тов в списке). Элементы типа set обрабатываются как биты, размер типа оп­ределяется числом элементов в списке: 1 байт (от 1 до 8 элементов), 2 байта (от 9 до 16 элементов), 3 байта (от 17 до 24 элементов), 4 байта (от 25 до 32 элементов) и 8 байт (от 33 до 64 элементов).

Календарные типы данных (табл. 4):

• Date – для хране­ния даты (формат YYYY-MM-DD для дат вида 2009-10-15 и формат YY-MM-DD для дат вида 09-10-15);

• Time – для хранения времени суток (формат hh:mm:ss, где hh – часы, mm – минуты, ss – секунды, например, 10:48:56);

• Datetime– для представления и даты, и времени суток;

• Timestamp – если в соответствующем столбце строки не указать конкретное значение или NULL, там будет записано время, когда соответствующая строка была создана или в последний раз изменена (в формате DATETIME);

• Year – позволяет хранить только год.

Таблица 4

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

'2009-04-06 02:04:08' '2009-4-06 02:04:08' '2009-4-6 02:04:08'

'2009-4-6 2:04:08' '2009-4-6 2:4:08' '2009-4-6 2:4:8'

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

'09-12-31 11:30:45' '09.12.31 11+30+45' '09/12/31 11*30*45'

При указании времени после секунд через точку можно указать микросекунды, т. е. использовать расширенный формат вида hh:mm:ss.ffffff, например '10:25:14.000001'. Кроме того, можно использовать краткие форматы НН:ММ и НН (вместо пропущенных величин бу­дут подставлены нулевые значения).

Если время задается в недопустимом формате, то в поле записывается нулевое значе­ние. Нулевое значение присваивается полям временного типа по умолчанию, когда им не присваивается инициирующее значение (табл. 5).

Таблица 5

Формат timestamp совпадает с datetime, но во внутреннем представлении дата хранится как число секунд, прошедших с полуночи 1 января 1970 г. (такое исчисление принято в операционной системе UNIX, а да­та 01.01.1970 считается началом эпохи UNIX и днем рождения операционной системы).

Если в таблице несколько столбцов timestamp, при модификации записи текущее время будет записываться только в один из них (первый). Можно явно указать столбец, которому необходимо назначать текущую дату при создании или изменении записи. Чтобы поля принимали текущую дату при создании записи, следует после определения столбца доба­вить default current_timestamp. Если текущее время должно выставляться при модификации записи, при использовании оператора update следует добавить on update current_timestamp.

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

Рекомендации по выбору типа данных.

• Обработка числовых данных происхо­дит быстрее строковых. Так как типы enum и set имеют внутреннее числовое представление, им следует отдавать предпочтение перед другими видами строковых данных, если это возможно.

• Производительность можно увеличить за счет представления строк в виде чисел. Пример – преобразование IP-адреса из строки в bigint. Это позволит уменьшить размер таблицы и значительно увеличить скорость при сортировке и выборке данных, но потребует дополнительных преобразований.

• Базы данных хранятся на жестком диске, и чем меньше места они занимают, тем быстрее происходит поиск и извлечение. Если есть возможность, следует выбирать типы данных, занимающие меньше места.

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

• Если применение столбцов с данными переменной длины неизбежно, для дефрагментации таблицы следует применять команду optimize table.

Обеспечение ссылочной целостности. Задается конструкцией:

foreign key [name_key] (col1, ... ) REFERENCES tbl (tbl_col, ... )

[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT | SET DEFAULT}] [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT | SET DEFAULT}]

Конструкция позволяет задать внешний ключ с необязательным именем name_key на столбцах, которые задаются в круглых скобках (один или несколько). Ключевое слово references указывает таблицу tbl, на которую ссылается внешний ключ, в круглых скобках указываются имена столбцов. Необязательные конструкции ON DELETE и ON UPDATE позволяют задать поведение СУБД при удалении и обновлении строк из таблицы-предка. Параметры, следующие за этими ключевыми словами, имеют следующие значения:

• CASCADE – при удалении или обновлении записи в таблице-предке, содержащей первичный ключ, записи со ссылками на это значение в таблице-потомке удаляются или обновляются автоматически;

• SET NULL – при удалении или обновлении записи в таблице-предке, содержащей первичный ключ, в таблице-потомке значения внешнего ключа, ссылающегося на таблицу-предка, устанавливаются в null;

• NO ACTION – при удалении или обновлении записей, содержащих первичный ключ, с таблицей-потомком никаких действий не производится;

• RESTRICT – если в таблице-потомке имеются записи, ссылающиеся на первичный ключ таблицы-предка, при удалении или обновлении записей с таким первичным ключом возвращается ошибка;

• SET DEFAULT – согласно стандарту SQL, при удалении или обновлении первичного ключа в таблице-потомке для ссылающихся на него записей в поле внешнего ключа должно устанавливаться значение по умолчанию (в MySQL это ключевое слово зарезервировано, но не обрабатывается).

Создание индексов. Индексы играют большую роль в БД, т. к. это основной способ ускорения их работы. Записи в таблице располагаются хаотически. Чтобы найти нужную запись, необходимо сканировать всю таблицу, на что уходит много времени. Идея индексов состоит в том, чтобы создать для столбца копию, которая постоянно будет поддерживаться в отсортированном состоя­нии. Это позволяет быстро осуществлять поиск по такому столбцу.

Все необходимые индексы формируются при создании таблицы. Индексированы будут все столбцы, объявленные как PRIMARY KEY, KEY, UNIQUE или INDEX. Индекс также можно добавить с помощью оператора CREATE INDEX. Перед выполнением оператор преобразуется в оператор ALTER TABLE. Например, создание индекса с именем name на основе поля u_name из таблицы users:

create index name on users (u_name);

Перед ключевым сло­вом index может присутствовать UNIQUE, требующее уникальности ограниче­ния.

Корректность таблиц в БД можно проверить с помощью оператора

SHOW TABLES;

Более подробную информацию о структуре таблицы дает команда

DESCRIBE имя_таблицы;

Переименование БД. Специального оператора переименования БД нет, но можно переименовать каталог БД в системном каталоге (…\DATA).

Удаление БД. Удалить всю БД вместе с ее содержимым можно командой:

drop database [IF EXISTS] имя_базы_данных;

Удаление таблиц и индексов. Удалить таблицу можно с помощью оператора:

drop table [IF EXISTS] имя_таблицы;

Удалить индекс можно с помощью оператора:

drop index имя_индекса on имя_таблицы;

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

alter table users add index name (u_name);

Оператор ALTER TABLE является исключительно гибким, поэтому он имеет огромное множество дополнительных ключевых слов.

Задание

При выполнении лабораторной работы необходимо для заданной предметной области средствами MySQL:

• создать базу данных;

• создать таблицы, определить поля таблиц, индексы;

• определить связи между таблицами и ограничения целостности;

• составить отчет по лабораторной работе.

Пример выполнения работы

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

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

Логическая модель данных предметной области в стандарте IDEF1X представлена на рис. 1. Выделены сущности КАТАЛОГ, КНИГА, КЛИЕНТ, ЗАКАЗ, между которыми установлены неидентифицирующие связи мощностью oдин-ко-многим, определенные спецификой предметной области.

Рис. 1. Логическая модель данных предметной области

Физическая модель данных предметной области в стандарте IDEF1X для целевой СУБД MySQL представлена на рис. 2.

Рис. 2. Физическая модель предметной области

База данных book состоит из четырех таблиц:

• catalogs – список торговых каталогов;

• books – список предлагаемых книг;

• users – список зарегистрированных пользователей магазина;

• orders – список заказов (осуществленных сделок).

Таблица catalogs состоит из двух полей:

• cat_ID – уникальный код каталога;

• cat_name – имя каталога.

Оба поля должны быть снабжены атрибутом not null, поскольку неопределенное значение для них недо­пустимо.

Таблица books состоит из семи полей:

• book_ID – уникальный код книги;

• b_name – название книги;

• b_author – автор книги;

• b_year – год издания;

• b_price – цена книги;

• b_count – количество книг на складе;

• b_cat_ID – код каталога из таблицы catalogs.

Цена книги b_price и количество экземпляров на складе b_count могут иметь атрибут null. На момент доставки час­то неизвестны количество товара и его цена, но отразить факт наличия товара в прайс-листе необходимо.

Поле b_cat_ID устанавливает связь между таблицами catalogs и books. Это поле должно быть объявлено как внешний ключ (FK) с правилом каскадного удаления и обновления. Обновление таблицы catalogs вызовет автоматическое обновление таблицы books. Удаление каталога в таблице catalogs приведет к автоматическому удалению всех записей в таблице books, соответствующих каталогу.

Таблица users состоит из семи полей:

• user_ID – уникальный код покупателя;

• u_name – имя покупателя;

• u_patronymic – отчество покупателя;

• u_surname – фамилия покупателя;

• u_phone – телефон покупателя (если имеется);

• u_email – e-mail покупателя (если имеется);

• u_status – статус покупателя.

Статус покупателя представлен полем типа enum, которое может принимать одно из четырех значений:

• active – авторизованный покупатель, который может осуществлять покупки через Интернет;

• passive – неавторизованный покупатель (значение по умолчанию), который осуществил процедуру регистрации, но не подтвердил ее и пока не может осуществлять покупки через Интернет, однако ему доступны каталоги для просмотра;

• lock – заблокированный покупатель, не может осуществлять покупки и просматривать каталоги магазина;

• gold – активный покупатель с хорошей кредитной историей, которому предоставляется скидка при следующих покупках в магазине.

Поля u_phone и u_email могут быть снабжены атрибутом null. Остальные поля должны получить атрибут not null.

Таблица orders вклю­чает пять полей:

• order_ID – уникальный номер сделки;

• o_user_ID – номер пользователя из таблицы users;

• o_book_ID – номер товарной позиции из таблицы books;

• o_time – время совершения сделки;

• o_number – число приобретенных товаров.

Поля таблицы orders должны быть снабжены атрибутом not null, т. к. при совершении покупки вся информация должна быть занесена в таблицу.

В таблице orders устанавливается связь с таблицами users (за счет поля o_user_id) и books (за счет поля o_book_id). Эти поля объявлены как внешние ключи (FK) с правилом каскадного удаления и обновления. Обновление таблиц users и books приведет к автоматическому обновлению таблицы orders. Удаление любого пользователя в таблице users приведет к автоматическому удалению всех записей в таблице orders, соответствующих этому пользователю.

Операторы создания, удаления БД book имеют следующий вид:

DROP DATABASE IF EXISTS book;

CREATE DATABASE book;

USE book;

Целесообразно создать в Блокноте текстовый файл и записать туда эти операторы. Для рассматриваемого примера содержимое блокнота представлено на рис.3.

Рис.3 Блокнот с набором команд

Создадим БД book:

CREATE DATABASE book;

Рис.4

Прежде чем работать с БД, необходимо выбрать эту БД с помощью оператора USE:

USE book;

Рис.5

Далее создадим таблицы, в соответствии с моделями, представленными на рис.1-2.

CREATE TABLE catalogs (cat_ID int(6) NOT NULL AUTO_INCREMENT,cat_name varchar(20) NOT NULL,PRIMARY KEY (cat_ID));

CREATE TABLE books (book_ID int(6) NOT NULL AUTO_INCREMENT,b_name varchar(100) NOT NULL,b_author varchar(100) NOT NULL,b_year year NOT NULL,b_price decimal(7,2) NULL default '0.00',b_count int(6) NULL default '0',b_cat_ID int(6) NOT NULL default '0', PRIMARY KEY (book_ID),FOREIGN KEY (b_cat_ID) REFERENCES catalogs(cat_ID) ON DELETE CASCADE ON UPDATE CASCADE);

CREATE TABLE users (user_ID int(6) NOT NULL AUTO_INCREMENT,u_name varchar(20) NOT NULL,u_patronymic varchar(20) NOT NULL,u_surname varchar(20) NOT NULL,u_phone varchar(12) NULL,u_email varchar(20) NULL,u_status ENUM ('active','passive','lock','gold') default 'passive',PRIMARY KEY (user_ID));

CREATE TABLE orders (order_ID int(6) NOT NULL AUTO_INCREMENT,o_user_ID int NOT NULL,o_book_ID int NOT NULL,o_time datetime NOT NULL default '0000-00-00 00:00:00',o_number int(6) NOT NULL default '0',PRIMARY KEY (order_ID),FOREIGN KEY (o_book_ID) REFERENCES books(book_ID) ON DELETE CASCADE ON UPDATE CASCADE,FOREIGN KEY (o_user_ID) REFERENCES users(user_ID) ON DELETE CASCADE ON UPDATE CASCADE);

Рис.6 Таблица books

Рис.7 Таблица users

Рис.8 Таблица orders

Рис.9 Таблица catalogs

Просмотрим результат:

Рис.10

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

1. Титульный лист.

2. Скриншоты всех этапов создания в соответствии с примером.

Варианты заданий

13