7 Определение данных: CREATE, DROP, ALTER

7.1 Синтаксис CREATE DATABASE 

CREATE DATABASE [IF NOT EXISTS] db_name

CREATE DATABASE создает базу данных с указанным именем. Правила для допустимых имен баз данных приведены в разделе "3.2 Имена баз данных, таблиц, индексов, столбцов и псевдонимов". Ошибка происходит, если база данных уже существует, и Вы не определяли IF NOT EXISTS.

Базы данных в MySQL выполнены как каталоги, содержащие файлы, которые соответствуют таблицам в базе данных. Поскольку нет никаких таблиц в базе данных, когда она первоначально создана, инструкция CREATE DATABASE только создает каталог под каталогом данных MySQL.

Вы можете также создавать базы данных с помощью программы mysqladmin. Подробности в разделе "12 Клиентские скрипты и утилиты MySQL".

7.2 Синтаксис DROP DATABASE

DROP DATABASE [IF EXISTS] db_name

DROP DATABASE удаляет все таблицы в базе данных и удаляет саму базу данных. Если выполнили DROP DATABASE на символически связанной базе данных, связь и первоначальная база данных будут удалены. Будьте ОЧЕНЬ осторожны с этой командой!

DROP DATABASE возвращает число файлов, которые были удалены из каталога баз данных. Обычно это кратное трем число потому, что каждая таблица соответствует файлам .MYD, .MYI и .frm.

Команда DROP DATABASE удаляет из заданного каталога базы данных все найденные файлы со следующими расширениями:

.BAK.DAT.HSH.ISD
.ISM.ISM.MRG.MYD
.MYI.db.frm

Все подкаталоги, имена которых состоят из 2 цифр (каталоги RAID), также будут удалены.

В MySQL Version 3.22 или позже Вы можете использовать ключевые слова IF EXISTS, чтобы предотвратить ошибку, если база данных не существует.

Вы можете также удалять базы данных с помощью команды mysqladmin. Подробности в разделе "12 Клиентские скрипты и утилиты MySQL".

7.3 Синтаксис 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]
  or    PRIMARY KEY (index_col_name,...)
  or    KEY [index_name] (index_col_name,...)
  or    INDEX [index_name] (index_col_name,...)
  or    UNIQUE [INDEX] [index_name] (index_col_name,...)
  or    FULLTEXT [INDEX] [index_name] (index_col_name,...)
  or    [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
            [reference_definition]
  or    CHECK (expr)

type:
        TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  or    SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  or    MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  or    INT[(length)] [UNSIGNED] [ZEROFILL]
  or    INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  or    BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  or    REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  or    NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  or    CHAR(length) [BINARY]
  or    VARCHAR(length) [BINARY]
  or    DATE
  or    TIME
  or    TIMESTAMP
  or    DATETIME
  or    TINYBLOB
  or    BLOB
  or    MEDIUMBLOB
  or    LONGBLOB
  or    TINYTEXT
  or    TEXT
  or    MEDIUMTEXT
  or    LONGTEXT
  or    ENUM(value1,value2,value3,...)
  or    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 | MYISAM }
or      AUTO_INCREMENT = #
or      AVG_ROW_LENGTH = #
or      CHECKSUM = {0 | 1}
or      COMMENT = "string"
or      MAX_ROWS = #
or      MIN_ROWS = #
or      PACK_KEYS = {0 | 1}
or      PASSWORD = "string"
or      DELAY_KEY_WRITE = {0 | 1}
or      ROW_FORMAT= { default | dynamic | fixed | compressed }
or      RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#
or      UNION = (table_name,[table_name...])
or      DATA DIRECTORY="directory"
or      INDEX DIRECTORY="directory"

select_statement:
        [IGNORE | REPLACE] SELECT ...  (Some legal select statement)

CREATE TABLE создает таблицу с заданным именем в текущей базе данных. Правила для допустимых имен таблицы даны в разделе "3.2 Имена баз данных, таблиц, индексов, столбцов и псевдонимов". Ошибка происходит, если не имеется никакой текущей базы данных, или если таблица уже существует.

В MySQL Version 3.22 или позже имя таблицы может быть определено как db_name.tbl_name. Это работает независимо от того, имеется или нет текущая база данных.

В MySQL Version 3.23 или позже Вы можете использовать ключевое слово TEMPORARY, когда Вы создаете таблицу. Временная таблица будет автоматически удалена, если подключение завершается. Причем, имя связано с подключением. Это означает, что два различных подключения могут оба использовать то же самое имя временной таблицы без того, чтобы войти в противоречие друг с другом или с существующей таблицей с тем же самым именем. Существующая таблица будет скрыта, пока временная таблица не удалена.

В MySQL Version 3.23 или позже Вы можете использовать ключевые слова IF NOT EXISTS, чтобы ошибка не произошла, если таблица уже существует. Обратите внимание, что не имеется никакой проверки, что структуры таблиц идентичны.

Каждая таблица tbl_name представляется некоторыми файлами в каталоге баз данных. В случае таблиц MyISAM-типа Вы получите:

ФайлНазначение
tbl_name.frmОпределение таблицы (форма)
tbl_name.MYDДанные таблицы
tbl_name.MYIИндекс таблицы

Для получения большего количества информации относительно свойств различных типов столбцов обратитесь к разделу "5 Типы столбцов":

7.3.1 Тихие изменения спецификации столбца

В некоторых случаях MySQL тихо изменяет спецификацию столбца, заданную в инструкции CREATE TABLE. Это может также происходить с ALTER TABLE:

Если Вы хотите видеть, использовал или нет MySQL столбец не того типа, какой Вы определили, выдайте инструкцию DESCRIBE tbl_name после создания или изменения Вашей таблицы.

Некоторые другие изменения типа столбца могут происходить, если Вы сжимаете таблицу, используя myisampack.

7.4 Синтаксис 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
        MODIFY [COLUMN] create_definition
        DROP [COLUMN] col_name
        DROP PRIMARY KEY
        DROP INDEX index_name
        DISABLE KEYS
        ENABLE KEYS
        RENAME [TO] new_tbl_name
        ORDER BY col
        table_options

ALTER TABLE позволяет Вам изменять структуру существующей таблицы. Например, Вы можете добавлять или удалять столбцы, создавать или уничтожать индексы, изменять тип существующих столбцов, переименовывать столбцы или непосредственно таблицу. Вы можете также изменять комментарий для таблицы и тип таблицы. Подробности в разделе "7.3 Синтаксис CREATE TABLE ".

Если Вы используете ALTER TABLE, чтобы изменить спецификацию столбца, но DESCRIBE tbl_name указывает, что Ваш столбец не был изменен, возможно, что MySQL игнорировал Вашу модификацию по одной из причин, описанных в разделе "7.3.1 Тихие изменения спецификации столбца". Например, если Вы пробуете изменять столбец VARCHAR на CHAR, MySQL будет все же использовать именно VARCHAR, если таблица содержит другие столбцы переменной длины.

ALTER TABLE работает, делая временную копию первоначальной таблицы. Перенастройка выполняется на копии, затем первоначальная таблица будет удалена, а новая переименована в старую. Это выполнено таким способом, чтобы все модификации были автоматически переназначены к новой таблице. В то время, как выполняется ALTER TABLE, первоначальная таблица читаема другой клиентурой. Модификации и записи остановлены, пока новая таблица не будет готова.

Обратите внимание, что, если Вы используете любую другую опцию ALTER TABLE, чем RENAME, MySQL будет всегда создавать временную таблицу, даже если данные не должны быть строго скопированы (подобно тому, когда Вы изменяете имя столбца). Авторы планируют исправить это в будущем, но поскольку ALTER TABLE вызывается относительно редко, это не имеет высокого приоритета в TODO.

Имеется пример, который показывает, некоторые из способов применения ALTER TABLE. Мы начинаем с таблицы t1, которая создана как показано ниже:

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

Чтобы переименовывать таблицу из t1 в t2, надо: 

mysql> ALTER TABLE t1 RENAME t2;

Чтобы изменить столбец a с типа 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;

Чтобы добавить новый целочисленный столбец c с поддержкой AUTO_INCREMENT, надо:

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=#.

С таблицами MyISAM, если Вы не изменяете столбец на AUTO_INCREMENT, номер последовательности не будут изменен. Если Вы удаляете столбец с поддержкой AUTO_INCREMENT, а затем добавляете другой с AUTO_INCREMENT, числа снова начнутся с 1.

Подробности в разделе "11.6.1 Проблемы с ALTER TABLE".

7.5 Синтаксис RENAME TABLE

RENAME TABLE tbl_name TO new_table_name[, tbl_name2 TO new_table_name2,...]

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

CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;

Переименование выполняется слева направо, это означает, что, если Вы хотите менять два имени таблицы, Вы должны сделать так:

RENAME TABLE old_table    TO backup_table,
             new_table    TO old_table,
             backup_table TO new_table;

Таблицы можно не только переименовывать, но еще и перемещать между разными базами данных. Например:

RENAME TABLE current_database.table_name TO other_database.table_name;

Когда Вы выполняете RENAME, Вы не можете иметь блокированные таблицы или активные транзакции. Вы должны также иметь привилегии ALTER и DROP на первоначальной таблице и привилегии CREATE и INSERT на новой таблице.

Если MySQL сталкивается с любыми ошибками в процессе переименования, он будет делать обратное переименование для всех переименованных таблиц, чтобы вернуть все обратно в первоначальное состояние.

7.6 Синтаксис DROP TABLE

DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE]

DROP TABLE удаляет одну или большее количество таблиц. Все данные таблицы и определение таблицы будут удалены, так что будьте внимательны с этой командой!

В MySQL Version 3.22 или позже Вы можете использовать ключевые слова IF EXISTS, чтобы предотвратить ошибку для таблиц, которых нет.

RESTRICT и CASCADE ничего не делают и нужны лишь для совместимости с другими СУБД.

ОБРАТИТЕ ВНИМАНИЕ: DROP TABLE не транзакционно-безопасна и автоматически завершает любые активные транзакции.

7.7 Синтаксис CREATE INDEX

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON tbl_name (col_name[(length)],... )

Инструкция CREATE INDEX ничего не делает до Version 3.22. В Version 3.22 или позже CREATE INDEX она отображена к инструкции ALTER TABLE, чтобы создать индексы. Подробности в разделе "7.4 Синтаксис ALTER TABLE ".

Обычно Вы создаете все индексы на таблице во время непосредственного создания таблицы с помощью вызова CREATE TABLE. Подробности в разделе "7.3 Синтаксис CREATE TABLE ". CREATE INDEX позволяет Вам добавлять индексы к существующим в базе таблицам.

Список столбцов в форме (col1,col2,...) создает индекс по многим столбцам. Индексные значения будут сформированы, связывая значения из заданных для индексации столбцов.

Для столбцов CHAR и VARCHAR индексы могут быть созданы с использованием только части столбца, применяя синтаксис col_name(length). На столбцах BLOB и TEXT длина требуется обязательно. Инструкция, показанная ниже, создает индекс с использованием первых 10 символов из столбца name:

mysql> CREATE INDEX part_of_name ON customer (name(10));

Поскольку большинство имен обычно отличается по первым 10 символам, этот индекс не должен быть намного медленнее, чем индекс, созданный из всего столбца name. Также использование частичных столбцов для индексов может делать индексный файл намного меньше, что сохранит много дискового пространства и ускорит операции INSERT!

Обратите внимание, что Вы можете добавлять индекс на столбце, который может иметь значения NULL, и на столбцах типов BLOB и TEXT только, если Вы используете MySQL Version 3.23.2 или более новую и применяете тип таблицы MyISAM.

Индексы FULLTEXT могут индексировать только столбцы типов VARCHAR и TEXT в таблицах типа MyISAM. Индексы FULLTEXT доступны в MySQL Version 3.23.23 и более новых. Подробности в разделе "9.4 Полнотекстовый поиск в MySQL".

7.8 Синтаксис DROP INDEX

DROP INDEX index_name ON tbl_name

DROP INDEX удаляет индекс с именем index_name из таблицы tbl_name. DROP INDEX ничего не делает до версии Version 3.22, а начиная с нее, этот вызов перенацелен на соответствующий вызов ALTER TABLE для удаления индекса. Подробности в разделе "7.4 Синтаксис ALTER TABLE".

Hosted by uCoz