Эксперт

Сергей

Задать вопрос

Мы готовы помочь Вам.

Лабораторная работа №1 Разработка физической модели базы данных с учетом декларативной ссылочной целостности
1. Теоретическая часть
Физические модели данных (PDM) обычно генерируются из логических моделей, но могут быть и воссозданы из действующей базы данных. Средства физического моделирования поддерживают множество стандартных нотаций, а также документирование, генерацию кода и воссоздание структур данных для более чем 70 РСУБД (в том числе новейших версий СУБД Oracle, IBM, Microsoft, Sybase, Teradata, MySQL, HP NeoView и многих других). Поддерживаются все объекты СУБД, необходимые для связывания таблиц и достижения требуемой производительности, такие как индексы, констрейнты, партиции и кластеры, а также новые технологии, такие как Java, XML и веб-сервисы в БД; проектируются средства обеспечения безопасности, применяются передовые методы создания представлений, обеспечивается ряд других возможностей. Физическую модель данных, как правило, можно использовать для оценки объема будущей БД; она может содержать специфическую информацию о выделении дисковой памяти для заданной СУБД.
1. Можно выделить следующие этапы методологии физического проектирования баз данных.
2. 1. Перенос глобальной логической модели данных в среду целевой СУБД.
3. 2. Проектирование основных отношений.
4. 3. Разработка способов получения производных данных.
5. 4. Реализация ограничений предметной области.
6. 5. Проектирование физического представления базы данных.
7. 6. Анализ транзакций.
8. 7. Выбор файловой структуры.
9. 8. Определение индексов.
10. 9. Определение требований к дисковой памяти.
11. 10. Проектирование пользовательских представлений.
12. 11. Разработка механизмов защиты.
13. 12. Обоснование необходимости введения контролируемой избыточности.
14. 13. Текущий контроль и настройка операционной систем
15. Логическая модель расширяет концептуальную путем определения для сущностей их атрибутов, описаний и ограничений, уточняет состав сущностей и взаимосвязи между ними.
16. Физическая модель данных описывает реализацию объектов логической модели на уровне объектов конкретной базы данных.

Логическое представление реляционных баз данных упрощается созданием связей между данными на основе (логической) конструкции, называемой таблицей. Под таблицей понимается двумерная структура, состоящая из строк и столбцов. Пользователь должен понимать, что таблица содержит группу связанных сущностей, т. е. набор сущностей; по этой причине термины набор сущностей и таблица чаще всего означают одно и то же. Таблица также называется отношением (relation), поскольку создатель реляционной модели Э. Ф. Кодд (Е. F. Codd) использовал термин “отношение” как синоним слова “таблица”. Правила целостности ЦЕЛОСТНОСТЬ НА УРОВНЕ СУЩНОСТИ Требование Все элементы первичного ключа уникальны и никакая часть первичного ключа не может быть пустой (null) Назначение Гарантирует, что каждая сущность (логический объект) будет иметь уникальную идентификацию, а значения внешнего ключа могут должным образом ссылаться на значения первичного ключа
7
Пример Счет не может иметь несколько дублирующихся значений и не может иметь пустое значение (null). Короче говоря, все счета уникально идентифицируются своим номером ЦЕЛОСТНОСТЬ НА УРОВНЕ ССЫЛКИ Требования Внешний ключ может иметь или пустое значение (если только он не является частью первичного ключа данной таблицы), или значение, совпадающее со значением первичного ключа в связанной таблице. (Каждое непустое значение внешнего ключа должно ссылаться на существующее значение первичного ключа.) Назначение Допускается, что атрибут не имеет соответствующего значения, но атрибут не может принимать недопустимые значения. Выполнение правила целостности на уровне ссылки делает невозможным удаление строки в одной таблице, где первичный ключ имеет обязательное соответствие со значением внешнего ключа в другой таблице Пример Клиенту может быть не назначен (еще) торговый агент, но невозможно назначить клиенту несуществующего агента
Теперь о внешних ключах:
 Если сущность С связывает сущности А и В, то она должна включать внешние ключи, соответствующие первичным ключам сущностей А и В.
 Если сущность В обозначает сущность А, то она должна включать внешний ключ, соответствующий первичному ключу сущности А.
 Здесь для обозначения любой из ассоциируемых сущностей (стержней, характеристик, обозначений или даже ассоциаций) используется новый обобщающий термин “Цель” или “Целевая сущность”.
 Таким образом, при рассмотрении проблемы выбора способа представления ассоциаций и обозначений в базе данных основной вопрос, на который следует получить ответ: “Каковы внешние ключи?”. И далее, для каждого внешнего ключа необходимо решить три вопроса:
 1. Может ли данный внешний ключ принимать неопределенные значения (NULL-значения)? Иначе говоря, может ли существовать некоторый экземпляр сущности данного типа, для которого неизвестна целевая сущность, указываемая внешним ключом? В случае поставок это, вероятно, невозможно – поставка, осуществляемая неизвестным поставщиком, или поставка неизвестного продукта не имеют смысла. Но в случае с сотрудниками такая ситуация однако могла бы иметь смысл – вполне возможно, что какой-либо сотрудник в данный момент не зачислен вообще ни в какой отдел. Заметим, что ответ на данный вопрос не зависит от прихоти проектировщика базы данных, а определяется фактическим образом действий, принятым в той части реального мира, которая должна быть представлена в рассматриваемой базе данных. Подобные замечания имеют отношение и к вопросам, обсуждаемым ниже.
 2. Что должно случиться при попытке УДАЛЕНИЯ целевой сущности, на которую ссылается внешний ключ? Например, при удалении поставщика, который осуществил по крайней мере одну поставку. Существует три возможности:
КАСКАДИРУЕТСЯ
Операция удаления “каскадируется” с тем, чтобы удалить также поставки этого поставщика.
ОГРАНИЧИВАЕТСЯ
Удаляются лишь те поставщики, которые еще не осуществляли поставок. Иначе операция удаления отвергается.
УСТАНАВЛИВАЕТСЯ
Для всех поставок удаляемого поставщика NULL-значение внешний ключ устанавливается в неопределенное значение, а затем этот поставщик удаляется. Такая возможность, конечно, неприменима, если данный внешний ключ не должен содержать NULL-значений.
8
 3. Что должно происходить при попытке ОБНОВЛЕНИЯ первичного ключа целевой сущности, на которую ссылается некоторый внешний ключ? Например, может быть предпринята попытка обновить номер такого поставщика, для которого имеется по крайней мере одна соответствующая поставка. Этот случай для определенности снова рассмотрим подробнее. Имеются те же три возможности, как и при удалении:
КАСКАДИРУЕТСЯ
Операция обновления “каскадируется” с тем, чтобы обновить также и внешний ключ впоставках этого поставщика.
ОГРАНИЧИВАЕТСЯ
Обновляются первичные ключи лишь тех поставщиков, которые еще не осуществляли поставок. Иначе операция обновления отвергается.
УСТАНАВЛИВАЕТСЯ
Для всех поставок такого поставщика NULL-значение внешний ключ устанавливается в неопределенное значение, а затем обновляется первичный ключ поставщика. Такая возможность, конечно, неприменима, если данный внешний ключ не должен содержать NULL-значений.
 Таким образом, для каждого внешнего ключа в проекте проектировщик базы данных должен специфицировать не только поле или комбинацию полей, составляющих этот внешний ключ, и целевую таблицу, которая идентифицируется этим ключом, но также и ответы на указанные выше вопроса (три ограничения, которые относятся к этому внешнему ключу).
Ссылочную целостность при выполнении лабораторной необходимо описать в таблице вида

2. Выполнение лабораторной работы
Создать физическую модель базы данных, находящуюся в третьей нормальной форме в соответствии с заданным вариантом. Расписать ссылочную целостность БД в таблице
10
3. Содержание отчета
Содержание отчета: — Текст задания. — физическую модель БД. — таблица с описанием ссылочной целостности.
4. Варианты заданий
Варианты заданий приведены в Приложении 2.

Лабораторная работа №2 Создание и модификация базы данных и таблиц базы данных
1. Теоретическая часть

Синтаксис оператора CREATE TABLE
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,…)] [table_options] [select_statement] create_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition] или PRIMARY KEY (index_col_name,…) или KEY [index_name] (index_col_name,…) или INDEX [index_name] (index_col_name,…) или UNIQUE [INDEX] [index_name] (index_col_name,…) или FULLTEXT [INDEX] [index_name] (index_col_name,…) или [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,…) [reference_definition] или CHECK (expr) type:
12
TINYINT[(length)] [UNSIGNED] [ZEROFILL] или SMALLINT[(length)] [UNSIGNED] [ZEROFILL] или MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] или INT[(length)] [UNSIGNED] [ZEROFILL] или INTEGER[(length)] [UNSIGNED] [ZEROFILL] или BIGINT[(length)] [UNSIGNED] [ZEROFILL] или REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] или DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] или FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] или DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] или NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] или CHAR(length) [BINARY] или VARCHAR(length) [BINARY] или DATE или TIME или TIMESTAMP или DATETIME или TINYBLOB или BLOB или MEDIUMBLOB или LONGBLOB или TINYTEXT или TEXT или MEDIUMTEXT или LONGTEXT или ENUM(value1,value2,value3,…) или SET(value1,value2,value3,…) index_col_name: col_name [(length)] reference_definition: REFERENCES tbl_name [(index_col_name,…)] [MATCH FULL | MATCH PARTIAL] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT table_options: TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM } или AUTO_INCREMENT = # или AVG_ROW_LENGTH = # или CHECKSUM = {0 | 1} или COMMENT = “string” или MAX_ROWS = # или MIN_ROWS = # или PACK_KEYS = {0 | 1 | DEFAULT} или PASSWORD = “string” или DELAY_KEY_WRITE = {0 | 1} или ROW_FORMAT= { default | dynamic | fixed | compressed } или RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=# RAID_CHUNKSIZE=# или UNION = (table_name,[table_name…]) или INSERT_METHOD= {NO | FIRST | LAST } или DATA DIRECTORY=”абсолютный путь к каталогу” или INDEX DIRECTORY=”абсолютный путь к каталогу” select_statement: [IGNORE | REPLACE] SELECT … (любое корректное выражение SELECT)
Оператор CREATE TABLE создает таблицу с заданным именем в текущей базе данных.
13
. Если нет активной текущей базы данных или указанная таблица уже существует, то возникает ошибка выполнения команды. В версии MySQL 3.22 и более поздних имя таблицы может быть указано как db_name.tbl_name. Эта форма записи работает независимо от того, является ли указанная база данных текущей. В версии MySQL 3.23 при создании таблицы можно использовать ключевое слово TEMPORARY. Временная таблица автоматически удаляется по завершении соединения, а ее имя действительно только в течение данного соединения. Это означает, что в двух разных соединениях могут использоваться временные таблицы с одинаковыми именами без конфликта друг с другом или с существующей таблицей с тем же именем (существующая таблица скрыта, пока не удалена временная таблица). В версии MySQL 4.0.2 для создания временных таблиц необходимо иметь привилегии CREATE TEMPORARY TABLES. В версии MySQL 3.23 и более поздних можно использовать ключевые слова IF NOT EXISTS для того, чтобы не возникала ошибка, если указанная таблица уже существует. Следует учитывать, что при этом не проверяется идентичность структур этих таблиц. Каждая таблица tbl_name представлена определенными файлами в директории базы данных. В случае таблиц типа MyISAM это следующие файлы:
Файл
Назначение
tbl_name.frm
Файл определения таблицы
tbl_name.MYD
Файл данных
tbl_name.MYI
Файл индексов
Чтобы получить более полную информацию о свойствах различных типов столбцов, section 6.2 Типы данных столбцов:
 Если не указывается ни NULL, ни NOT NULL, то столбец интерпретируется так, как будто указано NULL.
 Целочисленный столбец может иметь дополнительный атрибут AUTO_INCREMENT. При записи величины NULL (рекомендуется) или 0 в столбец AUTO_INCREMENT данный столбец устанавливается в значение value+1, где value представляет собой наибольшее для этого столбца значение в таблице на момент записи. Последовательность AUTO_INCREMENT начинается с 1. See section 8.4.3.126 mysql_insert_id(). Если удалить строку, содержащую максимальную величину для столбца AUTO_INCREMENT, то в таблицах типа ISAM или BDB эта величина будет восстановлена, а в таблицах типа MyISAM или InnoDB – нет. Если удалить все строки в таблице командой DELETE FROM table_name (без выражения WHERE) в режиме AUTOCOMMIT, то для таблиц всех типов последовательность начнется заново. Примечание: в таблице может быть только один столбец AUTO_INCREMENT, и он должен быть индексирован. Кроме того, версия MySQL 3.23 будет правильно работать только с положительными величинами столбца AUTO_INCREMENT. В случае внесения отрицательного числа оно интерпретируется как очень большое положительное число. Это делается, чтобы избежать проблем с точностью, когда числа “заворачиваются” от положительного к отрицательному и, кроме того, для гарантии, что по ошибке не будет получен столбец AUTO_INCREMENT со значением 0. В таблицах MyISAM и BDB можно указать вторичный столбец AUTO_INCREMENT с многостолбцовым ключом. See section 3.5.9 Использование атрибута AUTO_INCREMENT.
14
Последнюю внесенную строку можно найти с помощью следующего запроса (чтобы сделать MySQL совместимым с некоторыми ODBC-приложениями):
 SELECT * FROM tbl_name WHERE auto_col IS NULL
 Величины NULL для столбца типа TIMESTAMP обрабатываются иначе, чем для столбцов других типов. В столбце TIMESTAMP нельзя хранить литерал NULL; при установке данного столбца в NULL он будет установлен в текущее значение даты и времени. Поскольку столбцы TIMESTAMP ведут себя подобным образом, то атрибуты NULL и NOT NULL неприменимы в обычном режиме и игнорируются при их задании. С другой стороны, чтобы облегчить клиентам MySQL использование столбцов TIMESTAMP, сервер сообщает, что таким столбцам могут быть назначены величины NULL (что соответствует действительности), хотя реально TIMESTAMP никогда не будет содержать величины NULL. Это можно увидеть, применив DESCRIBE tbl_name для получения описания данной таблицы. Следует учитывать, что установка столбца TIMESTAMP в 0 не равнозначна установке его в NULL, поскольку 0 для TIMESTAMP является допустимой величиной.
 Величина DEFAULT должна быть константой, она не может быть функцией или выражением. Если для данного столбца не задается никакой величины DEFAULT, то MySQL автоматически назначает ее. Если столбец может принимать NULL как допустимую величину, то по умолчанию присваивается значение NULL. Если столбец объявлен как NOT NULL, то значение по умолчанию зависит от типа столбца:
o Для числовых типов, за исключением объявленных с атрибутом AUTO_INCREMENT, значение по умолчанию равно 0. Для столбца AUTO_INCREMENT значением по умолчанию является следующее значение в последовательности для этого столбца.
o Для типов даты и времени, отличных от TIMESTAMP, значение по умолчанию равно соответствующей нулевой величине для данного типа. Для первого столбца TIMESTAMP в таблице значение по умолчанию представляет собой текущее значение даты и времени. See section 6.2.2 Типы данных даты и времени.
o Для строковых типов, кроме ENUM, значением по умолчанию является пустая строка. Для ENUM значение по умолчанию равно первой перечисляемой величине (если явно не задано другое значение по умолчанию с помощью директивы DEFAULT).
Значения по умолчанию должны быть константами. Это означает, например, что нельзя установить для столбца даты в качестве значения по умолчанию величину функции, такой как NOW() или CURRENT_DATE.
 KEY является синонимом для INDEX.
 В MySQL ключ UNIQUE может иметь только различающиеся значения. При попытке добавить новую строку с ключом, совпадающим с существующей строкой, возникает ошибка выполнения команды.
 PRIMARY KEY представляет собой уникальный ключ KEY с дополнительным ограничением, что все столбцы с данным ключом должны быть определены как NOT NULL. В MySQL этот ключ называется PRIMARY (первичный). Таблица может иметь только один первичный ключ PRIMARY KEY. Если PRIMARY KEY отсутствует в таблицах, а некоторое приложение запрашивает его, то MySQL может превратить в PRIMARY KEY первый ключ UNIQUE, не имеющий ни одного столбца NULL.
 PRIMARY KEY может быть многостолбцовым индексом. Однако нельзя создать многостолбцовый индекс, используя в определении столбца атрибут ключа PRIMARY KEY. Именно таким образом только один столбец будет отмечен как первичный. Необходимо использовать синтаксис PRIMARY KEY(index_col_name, …).
15
 Если ключ PRIMARY или UNIQUE состоит только из одного столбца и он принадлежит к числовому типу, то на него можно сослаться также как на _rowid (новшество версии 3.23.11).
 Если индексу не назначено имя, то ему будет присвоено первое имя в index_col_name, возможно, с суффиксами (_2, _3, …), делающими это имя уникальным. Имена индексов для таблицы можно увидеть, используя SHOW INDEX FROM tbl_name. SHOW Syntax.
 Только таблицы типов MyISAM, InnoDB и BDB поддерживают индексы столбцов, которые могут иметь величины NULL. В других случаях, во избежание ошибки, необходимо объявлять такие столбцы как NOT NULL.
 С помощью выражения col_name(length) можно указать индекс, для которого используется только часть столбца CHAR или VARCHAR. Это поможет сделать файл индексов намного меньше. See section 5.4.4 Индексы столбцов.
 Индексацию столбцов BLOB и TEXT поддерживают только таблицы с типом MyISAM. Назначая индекс столбцу с типом BLOB или TEXT, всегда НЕОБХОДИМО указывать длину этого индекса:
 CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
 При использовании выражений ORDER BY или GROUP BY со столбцом типа TEXT или BLOB используются только первые max_sort_length байтов. See section 6.2.3.2 Типы данных BLOB и TEXT.
 В версии MySQL 3.23.23 и более поздних можно создавать также специальные индексы FULLTEXT. Они применяются для полнотекстового поиска. Эти индексы поддерживаются только таблицами типа MyISAM и они могут быть созданы только из столбцов VARCHAR и TEXT. Индексирование всегда выполняется для всего столбца целиком, частичная индексация не поддерживается. Более подробно эта операция описана в разделе MySQL section 6.8 Полнотекстовый поиск в MySQL.
 Выражения FOREIGN KEY, CHECK и REFERENCES фактически ничего не делают. Они введены только из соображений совместимости, чтобы облегчить перенос кода с других SQL-серверов и запускать приложения, создающие таблицы со ссылками. See section 1.9.3 Расширения MySQL к ANSI SQL92.
 Для каждого столбца NULL требуется один дополнительный бит, при этом величина столбца округляется в большую сторону до ближайшего байта.
 Максимальную длину записи в байтах можно вычислить следующим образом:
 длина записи = 1
 + (сумма длин столбцов)
 + (количество столбцов с допустимым NULL + 7)/8
 + (количество столбцов с динамической длинной)
 Опции table_options и SELECT реализованы только в версиях MySQL 3.23 и выше. Ниже представлены различные типы таблиц:
Тип таблицы
Описание
BDB или Berkeley_db
Таблицы с поддержкой транзакций и блокировкой страниц. See section 7.6 Таблицы BDB или Berkeley_DB.
HEAP
Данные для этой таблицы хранятся только в памяти. See section 7.4 Таблицы HEAP.
ISAM
Оригинальный обработчик таблиц. See section 7.3 Таблицы ISAM.
InnoDB
Таблицы с поддержкой транзакций и блокировкой строк. See section 7.5 Таблицы InnoDB.
MERGE
Набор таблиц MyISAM, используемый как одна таблица. See section 7.2
16
Таблицы MERGE.
MRG_MyISAM
Псевдоним для таблиц MERGE
MyISAM
Новый обработчик, обеспечивающий переносимость таблиц в бинарном виде, который заменяет ISAM. See section 7.1 Таблицы MyISAM.
 See section 7 Типы таблиц MySQL. Если задается тип таблицы, который не поддерживается данной версией, то MySQL выберет из возможных типов ближайший к указанному. Например, если задается TYPE=BDB и данный дистрибутив MySQL не поддерживает таблиц BDB, то вместо этого будет создана таблица MyISAM. Другие табличные опции используются для оптимизации характеристик таблицы. Эти опции в большинстве случаев не требуют специальной установки. Данные опции работают с таблицами всех типов, если не указано иное:
Опция
Описание
AUTO_INCREMENT
Следующая величина AUTO_INCREMENT, которую следует установить для данной таблицы (MyISAM).
AVG_ROW_LENGTH
Приближенное значение средней длины строки для данной таблицы. Имеет смысл устанавливать только для обширных таблиц с записями переменной длины.
CHECKSUM
Следует установить в 1, чтобы в MySQL поддерживалась проверка контрольной суммы для всех строк (это делает таблицы немного более медленными при обновлении, но позволяет легче находить поврежденные таблицы) (MyISAM).
COMMENT
Комментарий для данной таблицы длиной 60 символов.
MAX_ROWS
Максимальное число строк, которые планируется хранить в данной таблице.
MIN_ROWS
Минимальное число строк, которые планируется хранить в данной таблице.
PACK_KEYS
Следует установить в 1 для получения меньшего индекса. Обычно это замедляет обновление и ускоряет чтение (MyISAM, ISAM). Установка в 0 отключит уплотнение ключей. При установке в DEFAULT (MySQL 4.0) обработчик таблиц будет уплотнять только длинные столбцы CHAR/VARCHAR.
PASSWORD
Шифрует файл `.frm’ с помощью пароля. Эта опция не функционирует в стандартной версии MySQL.
DELAY_KEY_WRITE
Установка в 1 задерживает операции обновления таблицы ключей, пока не закроется указанная таблица (MyISAM).
ROW_FORMAT
Определяет, каким образом должны храниться строки. В настоящее время эта опция работает только с таблицами MyISAM, которые поддерживают форматы строк DYNAMIC и FIXED. See section 7.1.2 Форматы таблиц MyISAM.
 При использовании таблиц MyISAM MySQL вычисляет выражение max_rows * avg_row_length, чтобы определить, насколько велика будет результирующая таблица. Если не задана ни одна из вышеупомянутых опций, то максимальный размер таблицы будет составлять 4Гб (или 2Гб если данная операционная система поддерживает только таблицы величиной до 2Гб). Это делается для того, чтобы, если нет реальной необходимости в
17
больших файлах, ограничить размеры указателей, что позволит сделать индексы меньше и быстрее. Если опция PACK_KEYS не используется, то по умолчанию уплотняются только строки, но не числа. При использовании PACK_KEYS=1 числа тоже будут уплотняться. При уплотнении двоичных числовых ключей MySQL будет использовать сжатие префиксов. Это означает, что выгода от этого будет значительной только в случае большого количества одинаковых чисел. При сжатии префиксов для каждого ключа требуется один дополнительный байт, в котором указано, сколько байтов предыдущего ключа являются такими же, как и для следующего (следует учитывать, что указатель на строку хранится в порядке “старший-байт-в-начале” сразу после ключа – чтобы улучшить компрессию). Это означает, что при наличии нескольких одинаковых ключей в двух строках записи все последующие “аналогичные” ключи будут занимать только по 2 байта (включая указатель строки). Сравним: в обычном случае для хранения последующих ключей требуется размер_хранения_ключа + размер_указателя (обычно 4) байтов. С другой стороны, если все ключи абсолютно разные, каждый ключ будет занимать на 1 байт больше, если данный ключ не может иметь величину NULL (в этом случае уплотненный ключ будет храниться в том же байте, который используется для указания, что ключ равен NULL).
 Если после команды CREATE указывается команда SELECT, то MySQL создаст новые поля для всех элементов в данной команде SELECT. Например:
 mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (a), KEY(b))
 TYPE=MyISAM SELECT b,c FROM test2;
Эта команда создаст таблицу MyISAM с тремя столбцами a, b и c. Отметим, что столбцы из команды SELECT присоединяются к таблице справа, а не перекрывают ее. Рассмотрим следующий пример: mysql> SELECT * FROM foo; +—+ | n | +—+ | 1 | +—+ mysql> CREATE TABLE bar (m INT) SELECT n FROM foo; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM bar; +——+—+ | m | n | +——+—+ | NULL | 1 | +——+—+ 1 row in set (0.00 sec) Каждая строка в таблице foo вносится в таблицу bar со своим значением из foo, при этом в новые столбцы в таблице bar записываются величины, заданные по умолчанию. Команда CREATE TABLE … SELECT не создает автоматически каких-либо индексов. Это сделано преднамеренно, чтобы команда была настолько гибкой, насколько возможно. Чтобы иметь индексы в созданной таблице, необходимо указать их перед данной командой SELECT: mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
18
Если возникает ошибка при копировании данных в таблицу, то они будут автоматически удалены. Чтобы обеспечить возможность использовать для восстановления таблиц журнал обновлений/двоичный журнал, в MySQL во время выполнения команды CREATE TABLE … SELECT не разрешены параллельные вставки.
 Воспользовавшись опцией RAID_TYPE, можно разбить файл данных MyISAM на участки с тем, чтобы преодолеть 2Гб/4Гб лимит файловой системы под управлением ОС, не поддерживающих большие файлы. Разбиение не касается файла индексов. Следует учесть, что для файловых систем, которые поддерживают большие файлы, эта опция не рекомендуется! Для получения более высокой скорости ввода-вывода можно разместить RAID-директории на различных физических дисках. RAID_TYPE будет работать под любой операционной системой, если конфигурация MySQL выполнена с параметром –with-raid. В настоящее время для опции RAID_TYPE возможен только параметр STRIPED (1 и RAID0 являются псевдонимами для него). Если указывается RAID_TYPE=STRIPED для таблицы MyISAM, то MyISAM создаст поддиректории RAID_CHUNKS с именами `00′, `01′, `02′ в директории базы данных. В каждой из этих директорий MyISAM создаст файл `table_name.MYD’. При записи данных в файл данных обработчик RAID установит соответствие первых RAID_CHUNKSIZE*1024 байтов первому упомянутому файлу, следующих RAID_CHUNKSIZE*1024 байтов – следующему файлу и так далее.
 Опция UNION применяется, если необходимо использовать совокупность идентичных таблиц как одну таблицу. Она работает только с таблицами MERGE. иметь привилегии SELECT, UPDATE и DELETE. Все сопоставляемые таблицы должны принадлежать той же базе данных, что и таблица MERGE.
 Для внесения данных в таблицу MERGE необходимо указать с помощью INSERT_METHOD, в какую таблицу данная строка должна быть внесена.. Эта опция была введена в MySQL 4.0.0.
 В созданной таблице ключ PRIMARY будет помещен первым, за ним все ключи UNIQUE и затем простые ключи. Это помогает оптимизатору MySQL определять приоритеты используемых ключей, а также более быстро определять сдублированные ключи UNIQUE.
 Используя опции DATA DIRECTORY=”каталог” или INDEX DIRECTORY=”каталог”, можно указать, где обработчик таблицы должен помещать свои табличные и индексные файлы. Следует учитывать, что указываемый параметр directory должен представлять собой полный путь к требуемому каталогу (а не относительный путь). Данные опции работают только для таблиц MyISAM в версии MySQL 4.0, если при этом не используется опция –skip-symlink.
Синтаксис оператора ALTER TABLE
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec …] alter_specification: ADD [COLUMN] create_definition [FIRST | AFTER column_name ] или ADD [COLUMN] (create_definition, create_definition,…) или ADD INDEX [index_name] (index_col_name,…) или ADD PRIMARY KEY (index_col_name,…) или ADD UNIQUE [index_name] (index_col_name,…) или ADD FULLTEXT [index_name] (index_col_name,…) или ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,…) [reference_definition] или ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} или CHANGE [COLUMN] old_col_name create_definition [FIRST | AFTER column_name] или MODIFY [COLUMN] create_definition [FIRST | AFTER column_name] или DROP [COLUMN] col_name или DROP PRIMARY KEY
19
или DROP INDEX index_name или DISABLE KEYS или ENABLE KEYS или RENAME [TO] new_tbl_name или ORDER BY col или table_options
Оператор ALTER TABLE обеспечивает возможность изменять структуру существующей таблицы. Например, можно добавлять или удалять столбцы, создавать или уничтожать индексы или переименовывать столбцы либо саму таблицу. Можно также изменять комментарий для таблицы и ее тип. See section 6.5.3 Синтаксис оператора CREATE TABLE.
Если оператор ALTER TABLE используется для изменения определения типа столбца, но DESCRIBE tbl_name показывает, что столбец не изменился, то, возможно, MySQL игнорирует данную модификацию по одной из причин, описанных в разделе section 6.5.3.1 Молчаливые изменения определений столбцов. Например, при попытке изменить столбец VARCHAR на CHAR MySQL будет продолжать использовать VARCHAR, если данная таблица содержит другие столбцы с переменной длиной. Оператор ALTER TABLE во время работы создает временную копию исходной таблицы. Требуемое изменение выполняется на копии, затем исходная таблица удаляется, а новая переименовывается. Так делается для того, чтобы в новую таблицу автоматически попадали все обновления кроме неудавшихся. Во время выполнения ALTER TABLE исходная таблица доступна для чтения другими клиентами. Операции обновления и записи в этой таблице приостанавливаются, пока не будет готова новая таблица. Следует отметить, что при использовании любой другой опции для ALTER TABLE кроме RENAME, MySQL всегда будет создавать временную таблицу, даже если данные, строго говоря, и не нуждаются в копировании (например, при изменении имени столбца). Мы планируем исправить это в будущем, однако, поскольку ALTER TABLE выполняется не так часто, мы (разработчики MySQL) не считаем эту задачу первоочередной. Для таблиц MyISAM можно увеличить скорость воссоздания индексной части (что является наиболее медленной частью в процессе восстановления таблицы) путем установки переменной myisam_sort_buffer_size достаточно большого значения.
 Для использования оператора ALTER TABLE необходимы привилегии ALTER, INSERT и CREATE для данной таблицы.
 Опция IGNORE является расширением MySQL по отношению к ANSI SQL92. Она управляет работой ALTER TABLE при наличии дубликатов уникальных ключей в новой таблице. Если опция IGNORE не задана, то для данной копии процесс прерывается и происходит откат назад. Если IGNORE указывается, тогда для строк с дубликатами уникальных ключей только первая строка используется, а остальные удаляются.
 Можно запустить несколько выражений ADD, ALTER, DROP и CHANGE в одной команде ALTER TABLE. Это является расширением MySQL по отношению к ANSI SQL92, где допускается только одно выражение из упомянутых в одной команде ALTER TABLE.
 Опции CHANGE col_name, DROP col_name и DROP INDEX также являются расширениями MySQL по отношению к ANSI SQL92.
 Опция MODIFY представляет собой расширение Oracle для команды ALTER TABLE.
 Необязательное слово COLUMN представляет собой “белый шум” и может быть опущено.
 При использовании ALTER TABLE имя_таблицы RENAME TO новое_имя без каких-либо других опций MySQL просто переименовывает файлы, соответствующие заданной таблице. В этом
20
случае нет необходимости создавать временную таблицу. See section 6.5.5 Синтаксис оператора RENAME TABLE.
 В выражении create_definition для ADD и CHANGE используется тот же синтаксис, что и для CREATE TABLE. Следует учитывать, что этот синтаксис включает имя столбца, а не просто его тип. See section 6.5.3 Синтаксис оператора CREATE TABLE.
 Столбец можно переименовывать, используя выражение CHANGE имя_столбца create_definition. Чтобы сделать это, необходимо указать старое и новое имена столбца и его тип в настоящее время. Например, чтобы переименовать столбец INTEGER из a в b, можно сделать следующее:
 mysql> ALTER TABLE t1 CHANGE a b INTEGER;
При изменении типа столбца, но не его имени синтаксис выражения CHANGE все равно требует указания обоих имен столбца, даже если они одинаковы. Например: mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL; Однако начиная с версии MySQL 3.22.16a можно также использовать выражение MODIFY для изменения типа столбца без переименовывания его: mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
 При использовании CHANGE или MODIFY для того, чтобы уменьшить длину столбца, по части которого построен индекс (например, индекс по первым 10 символам столбца VARCHAR), нельзя сделать столбец короче, чем число проиндексированных символов.
 При изменении типа столбца с использованием CHANGE или MODIFY MySQL пытается преобразовать данные в новый тип как можно корректнее.
 В версии MySQL 3.22 и более поздних можно использовать FIRST или ADD … AFTER имя_столбца для добавления столбца на заданную позицию внутри табличной строки. По умолчанию столбец добавляется в конце. Начиная с версии MySQL 4.0.1, можно также использовать ключевые слова FIRST и AFTER в опциях CHANGE или MODIFY.
 Опция ALTER COLUMN задает для столбца новое значение по умолчанию или удаляет старое. Если старое значение по умолчанию удаляется и данный столбец может принимать значение NULL, то новое значение по умолчанию будет NULL. Если столбец не может быть NULL, то MySQL назначает значение по умолчанию так, как описано в разделе section 6.5.3 Синтаксис оператора CREATE TABLE.
 Опция DROP INDEX удаляет индекс. Это является расширением MySQL по отношению к ANSI SQL92. See section 6.5.8 Синтаксис оператора DROP INDEX.
 Если столбцы удаляются из таблицы, то эти столбцы удаляются также и из любого индекса, в который они входят как часть. Если все столбцы, составляющие индекс, удаляются, то данный индекс также удаляется.
 Если таблица содержит только один столбец, то этот столбец не может быть удален. Вместо этого можно удалить данную таблицу, используя команду DROP TABLE.
 Опция DROP PRIMARY KEY удаляет первичный индекс. Если такого индекса в данной таблице не существует, то удаляется первый индекс UNIQUE в этой таблице. (MySQL отмечает первый уникальный ключ UNIQUE как первичный ключ PRIMARY KEY, если никакой другой первичный ключ PRIMARY KEY не был явно указан). При добавлении UNIQUE INDEX или PRIMARY KEY в таблицу они хранятся перед остальными неуникальными ключами, чтобы можно было определить дублирующиеся ключи как можно раньше.
 Опция ORDER BY позволяет создавать новую таблицу со строками, размещенными в заданном порядке. Следует учитывать, что созданная таблица не будет сохранять этот порядок строк
21
после операций вставки и удаления. В некоторых случаях такая возможность может облегчить операцию сортировки в MySQL, если таблица имеет такое расположение столбцов, которое вы хотели бы иметь в дальнейшем. Эта опция в основном полезна, если заранее известен определенный порядок, в котором преимущественно будут запрашиваться строки. Использование данной опции после значительных преобразований таблицы дает возможность получить более высокую производительность.
 При использовании команды ALTER TABLE для таблиц MyISAM все неуникальные индексы создаются в отдельном пакете (подобно REPAIR). Благодаря этому команда ALTER TABLE при наличии нескольких индексов будет работать быстрее.
 Начиная с MySQL 4.0, вышеуказанная возможность может быть активизирована явным образом. Команда ALTER TABLE … DISABLE KEYS блокирует в MySQL обновление неуникальных индексов для таблиц MyISAM. После этого можно применить команду ALTER TABLE … ENABLE KEYS для воссоздания недостающих индексов. Так как MySQL делает это с помощью специального алгоритма, который намного быстрее в сравнении со вставкой ключей один за другим, блокировка ключей может дать существенное ускорение на больших массивах вставок.
 Применяя функцию C API mysql_info(), можно определить, сколько записей было скопировано, а также (при использовании IGNORE) – сколько записей было удалено из-за дублирования значений уникальных ключей.
 Выражения FOREIGN KEY, CHECK и REFERENCES фактически ничего не делают. Они введены только из соображений совместимости, чтобы облегчить перенос кода с других серверов SQL и запуск приложений, создающих таблицы со ссылками. See section 1.9.4 Отличия MySQL от ANSI SQL92.
Ниже приводятся примеры, показывающие некоторые случаи употребления команды ALTER TABLE. Пример начинается с таблицы t1, которая создается следующим образом: mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10)); Для того чтобы переименовать таблицу из t1 в t2: mysql> ALTER TABLE t1 RENAME t2; Для того чтобы изменить тип столбца с INTEGER на TINYINT NOT NULL (оставляя имя прежним) и изменить тип столбца b с CHAR(10) на CHAR(20) с переименованием его с b на c: mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); Для того чтобы добавить новый столбец TIMESTAMP с именем d: mysql> ALTER TABLE t2 ADD d TIMESTAMP; Для того чтобы добавить индекс к столбцу d и сделать столбец a первичным ключом: mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a); Для того чтобы удалить столбец c: mysql> ALTER TABLE t2 DROP COLUMN c;
Для того чтобы добавить новый числовой столбец AUTO_INCREMENT с именем c:
22
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c); Заметьте, что столбец c индексируется, так как столбцы AUTO_INCREMENT должны быть индексированы, кроме того, столбец c объявляется как NOT NULL, поскольку индексированные столбцы не могут быть NULL. При добавлении столбца AUTO_INCREMENT значения этого столбца автоматически заполняются последовательными номерами (при добавлении записей). Первый номер последовательности можно установить путем выполнения команды SET INSERT_ID=# перед ALTER TABLE или использования табличной опции AUTO_INCREMENT = #.
2. Выполнение лабораторной работы
По аналогии с примерами, приведенными в п. 1 и создать базу данных с разработанной физической моделью. Продемонстрировать умение добавить и удалить столбец командой alter table
3. Содержание отчета
Содержание отчета: — Текст задания. — физическую модель БД. — скрипт создания таблиц базы данных на SQL; — скрипт изменения структуры таблиц базы данных на SQL.
4. Варианты заданий
Варианты заданий приведены в Приложении 2.