3. Разработка базы данных с использованием операторов определения объектов

Разработка базы данных в MySQL начинается с создания самой базы данных с помощью оператора CREATE DATABASE. Синтаксис оператора CREATE DATABASE:

CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset] [COLLATE collation];

- db_name – имя, которое будет присвоено создаваемой базе данных (длина имени не более 64 символов).

- IF NOT EXISTS – если не указать этот параметр, то при попытке создания базы данных с уже существующим именем, возникнет ошибка выполнения команды.

- CHARACTER SET, COLLATE – используется для задания стандартной кодировки таблицы и порядка сортировки.

После создания базы данных создаем таблицы в БД. Создание таблицы в базе данных производится командой CREATE TABLE. Синтаксис команды CREATE TABLE:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,…)] [table_options] [select_statement]

- tbl_name – задает имя таблицы, которая будет создана в текущей базе данных. Если никакая база данных на момент вызова команды CREATE TABLE не была принята текущей, то возникнет ошибка выполнения команды. Начиная с MySQL 3.22, введена возможность явно указать базу данных, в которой будет создана новая таблица, при помощи синтаксиса db_name.tbl_name.

- TEMPORARY – этот параметр используется для создания временной таблицы с именем tbl_name в течение только текущего сценария. По окончанию выполнения сценария созданная таблица удаляется.

- IF NOT EXISTS – если указан этот параметр и производится попытка создать таблицу с дублирующим именем (т.е. таблица с таким именем в текущей БД уже есть), то таблица создана не будет и сообщение об ошибке не появиться. В противном случае таблица также создана не будет, но команда вызовет ошибку. Следует отметить, что при создании сравниваются только имена таблиц. Внутренние структуры не сравниваются.

- create_definition - определяет внутреннюю структуру создаваемой таблицы (названия и типы полей, ключи, индексы и т.д.). Возможные синтаксисы 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)

- col_name – задает имя столбца в создаваемой таблице.

- type – задает тип данных для столбца col_name. Возможные значения параметра type (перечислим не все, а только часто используемые):

- INT[(length)] [UNSIGNED] [ZEROFILL] – целое число

- REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] – вещественное число

- VARCHAR(length) [BINARY] – строка

- DATE – дата

- [NOT NULL | NULL] – указывает, может ли данных столбец содержать незаполненное (пустое) значение или нет. Если не указано, то по умолчанию принимается NULL.

- [DEFAULT default_value] – задает значение по умолчанию для данного столбца. При вставке новой записи в таблицу командой INSERT если значение для поля col_name явно указано не было, то устанавливается значение default_value.

- [AUTO_INCREMENT] – при вставке новой записи в таблицу поле с этим атрибутом автоматически получит числовое значение, на 1 больше самого большого значения для этого поля в текущий момент времени. Данная возможность обычно используется для генерирования уникальных идентификаторов строк. Столбец, для которого применяется атрибут AUTO_INCREMENT, должен иметь целочисленный тип. В таблице может быть только один столбец с атрибутом AUTO_INCREMENT. Так же этот столбец должен быть проиндексирован. Отсчет последовательности чисел для AUTO_INCREMENT начинается с 1. Это могут быть только положительные числа.

После создания таблиц нужно их заполнить данными. Для этого используется команда INSERT, которая производит вставку новых строк в таблицу. Синтаксис команды INSERT:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,…)] VALUES (expression,…),(…),…

либо

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name

SET col_name=expression, col_name=expression, …

либо

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,…)] SELECT …

Общие положения работы команды INSERT:

- tbl_name – задает имя таблицы, в которую будет вставлена новая строка. На момент запуска команды INSERT таблица с таким именем должна существовать в базе данных.

- LOW_PRIORITY – если указан этот параметр, то вставка новой записи будет отложена до тех пор, пока другие сценарии не закончат чтение из этой таблицы. Надо отметить, что если таблица часто используется, то при указании этого параметра может пройти достаточно много времени, прежде чем данная команда будет выполнена.

- DELAYED – если указан этот параметр, то после выполнения команды INSERT сценарий сразу же получит ответ от БД об успешной вставке новой записи, а запись будет вставлено только после завершения использования данной таблицы другим сценарием. Это может быть удобно, если требуется высокая скорость работы скрипта.

- IGNORE – если некоторые поля таблицы имеют ключи PRIMARY или UNIQUE, и производится вставка новой строки, в которой эти поля имеют дублирующее значение, то действие команды аварийно завершается и выдается ошибка №1062 («Duplicate entry 'val' for key N»). Если в команде INSERT указано ключевое слово IGNORE, то вставка записей не прерывается, а строки с дублирующими значениями просто не вставляются.

Если MySQL был сконфигурирован с использованием опции DONT_USE_DEFAULT_FIELDS, то команда INSERT будет генерировать ошибку, если явно не указать величины для всех столбцов, которые требуют значений не-NULL.

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

- INSERT … VALUES – в этом случае в команде четко указывается порядок следования устанавливаемых полей и их значений. Если для поля или группы полей, присутствующих в таблице, не установить значение, то используется значение, установленное по умолчанию при создании таблицы. Если при выполнении команды INSERT не были указаны названия полей, то в VALUES() должны быть указаны значения для всех полей таблицы.

- INSERT … SET – в этом случае в команде каждому полю, присутствующему в таблице, присваивается значение в виде имя поля='значение'. Так же, как и с INSERT … VALUES, если одному или нескольким полям не задать значение, то установится значение по умолчанию. В качестве значений полям можно присваивать не только значения, но и выражения. В выражениях разрешено использовать значения полей таблицы, которые уже были установлены в этой команде.

- INSERT … SELECT – такой синтаксис позволяет внести в таблицу большое количество записей за один раз, причем из разных таблиц. Если для таблицы, в которую происходит вставка записей, не указан список полей, то значения для всех полей будут определены на основании результата работы SELECT. Если некоторые поля не определены, то для них будут принято значение по умолчанию.