ресурс для начинающих веб-разработчиков
комплексные веб-услуги по созданию сайтов

Следующий этап настройки базы данных связан с созданием таблиц. Это делается с помощью SQL-команды CREATE TABLE. Общая форма оператора CREATE TABLE выглядит следующим образом:

CREATE TABLE tablename (columns)

Заполнитель tablename необходимо заменить именем конкретной таблицы, которую требуется создать, а columns — разделяемым запятыми списком столбцов в таблице.

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

Вспомним схему базы данных интернет магазина:

Customer (CustomerID, Name, Address, City);

Orders (OrderID, CustomerID, Amount, Date);

Books (ISBN, Author, Title, Price);

Order_Items (OrderID, ISBN, Quantity);

Book_Reviews (ISBN, Reviews);

SQL-код создания таблиц для приложения Интернет-магазин

CREATE TABLE customers (
customerid int(10) unsigned NOT NULL auto_increment primary key,
name char(50) NOT NULL,
address char(100) NOT NULL,
city char(30) NOT NULL,
);

CREATE TABLE orders (
orderid int(10) unsigned NOT NULL auto_increment,
customerid int(10) unsigned NOT NULL,
amount float(6,2),
date date NOT NULL,
PRIMARY KEY (orderid)
);

CREATE TABLE books (
isbn char(13) NOT NULL,
author char(50),
title char(100),
price float(5, 2),
PRIMARY KEY (isbn)
);

CREATE TABLE order_items (
orderid int(10) unsigned NOT NULL,
isbn char(13) NOT NULL,
quantity tinyint(3) unsigned,
PRIMARY KEY (orderid, isbn)
);

CREATE TABLE IF book_reviews (
isbn char(13) NOT NULL,
review text,
PRIMARY KEY (isbn)
);

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

Значения ключевых слов

NOT NULL означает, что все строки таблицы должны иметь значение в этом атрибуте. Если ключевое слово NOT NULL не указано, поле может быть пустым.

AUTO_INCREMENT — это специальная функция MySQL, которую можно использовать применительно к числовым столбцам. Если при вставке строк в таблицу оставить это поле пустым, MySQL автоматически сгенерирует значение уникального идентификатора. Это значение будет на единицу больше максимального значения, уже существующего в столбце. Каждая таблица может содержать не более одного такого поля. Столбцы для которых указано ключевое слово AUTO_INCREMENT, должно быть проиндексированы.

Ключевое слово PRIMARY KEY, следующее за именем столбца, определяет, что этот столбец является первичным ключем таблицы. Записи в этом столбце должны быть уникальными. MySQL будет автоматически индексировать этот стлбец. Обратите внимание, что ранее, когда столбец customerid использовался в таблице customers, это делалось с применением AUTO_INCREMENT. Автоматическое индексирование по первичному ключу обеспечивает индексирование, требуемое для применения функции AUTO_INCREMENT.

Указывать PRIMARY KEY после названия столбца можно лишь тогда, когда мы имеем дело с первичным ключем в виде одиночного столбца. Существует альтернативный способ объявления первичного ключа, представленный ниже:

CREATE TABLE customers (
customerid int(10) unsigned NOT NULL auto_increment,
name char(50) NOT NULL,
address char(100) NOT NULL,
city char(30) NOT NULL,
PRIMARY KEY (customerid)
);

В таблице order_items применен альтернативный вариант конструкции PRIMARY KEY, из-за того, что первичный ключ состоит из двух столбцов. Это также создает индекс, основанный на двух столбцах.

Ключевое слово UNSIGNED, заданное после целочислового типа, ознасает, что соответствующее значение может быть только нулевым или положительным (т.е. беззнаковым).

Что означают типы столбцов

В качестве примера рассмотрим первую таблицу:

CREATE TABLE customers (
customerid int(10) unsigned NOT NULL auto_increment primary key,
name char(50) NOT NULL,
address char(100) NOT NULL,
city char(30) NOT NULL,
);

При создании любой таблицы реобходимо принимать решение в отношении типов столбцов.

В соответствии со схемой таблица customers содержит четыре столбца. Первый, customerid — это первичный ключ, который определен непосредственно. Согласно нашему решению, он будет представляться целым числом (тип данных int), причем эти идентификаторы должны быть беззнаковыми (insignet). Кроме того мы воспользовались auto_increment, поэтому MySQL позаботился уникальных идентификаторов.

Все остальные столбцы содержат данные строкового типа. Для них выбран тип char. Он определяет поля фиксированной ширины. Ширина указывается в скобках, поэтому, например, имя (поле name) состоит из 50 символов.

Этот тип данных всегда будет назначать 50 символов для имени, даже если не все символы будут использоваться. Для соблюдения требуемого размера MySQL добавит к данным соответствующее количество пробелов. Альтернативным типом данных является varchar, который использует только необходимый объем (плюс один байт). Таким образом, существует небольшой компромисс — varchar использует меньше места, зато char работает быстрее.

Обратите внимание, что все столбцы объявлены NOT NULL. Это минимальная оптимизация, в результате которой система будет работать немного быстрее.

Некоторые операторы CREATE отличаются по синтаксису. Взгляните на таблицу orders:

CREATE TABLE orders (
orderid int(10) unsigned NOT NULL auto_increment,
customerid int(10) unsigned NOT NULL,
amount float(6,2),
date date NOT NULL,
PRIMARY KEY (orderid)
);

Столбец amount хранит числа с плавающей запятой (тип float). Для большинства типов данных с плавающей запятой можно определить ширину отображения данных и количество десятичных разрядов. В данном случае итог по заказу хранится в долларах, поэтому выбрана сравнительно большая ширина отображения итога (6) и два десятичных разряда для представления центов.

Столбец date имеет тип данных date.

В данной таблице указано, что все столбцы должны быть NOT NULL, кроме amount. Почему? Когда в базу данных вносится заказ, его необходимо сохранить в таблице orders, добавить заказные элементы в order_items и только тогда подсчитать сумму заказа. На этапе создания заказа сумма заказа не известна, поэтому ей можно присвоить значение NULL.

Таблице books обладает похожими характеристиками:

CREATE TABLE books (
isbn char(13) NOT NULL,
author char(50),
title char(100),
price float(4,2),
PRIMARY KEY (isbn)
);

В этом случае не требуется генерировать первичный ключ, потому что номера ISBN получаются в другом месте. Остальные поля оставлены NULL, поскольку в первую очередь книжный магазин узнает ISBN, а потом уже название книги, авторов и цену.

Таблица order_items демонстрирует применение первичных ключей со множеством столбцов:

CREATE TABLE order_items (
orderid int(10) unsigned NOT NULL,
isbn char(13) NOT NULL,
quantity tinyint(3) unsigned,
PRIMARY KEY (orderid, isbn)
);

Количество экземпляров конкретной книги обозначено как TINYINT UNSIGNED; этот параметр может принимать значения от 0 до 255.

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

И наконец, рассмотрим таблицу book_reviews:

CREATE TABLE IF book_reviews (
isbn char(13) NOT NULL,
review text,
PRIMARY KEY (isbn)
);

Здесь присутствует новый тип данных, о котором мы еще не говорили. Он предназначен для объемных текстов, например, статей. Существует несколько вариантов данного типа, и они рассматриваются позже. Чтобы понять процесс создания таблиц в деталях, стоит начать с идентификаторов и названий столбцов в целом, а потом уже перейти к типам данных. Для начала взглянем на созданную нами базу данных.

Просмотр базы данных с помощью команд SHOW и DESCRIBE

Войдите в монитор MySQL и начните работу с базой данных books. Таблицы в базе можно просмотреть, набрав:

mysql> show tables;

MySQL отобразит список таблиц базы данных:

mysql> use books;
Database changed


Командой show можно пользоваться и для просмотра списка баз данных:

mysql> show databases;

Если у вас нет привлегий SHOW DATABASES, вы будите видеть только базы данных, для которых у вас установлены привлегии.

Команда DESCRIBE дает возможность увидеть дополнительную информацию по конкретной таблице, например, books^

mysql> describe books;

MySQL выведет информацию, которая была введена во время создания базы данных:

4 rows in set (0.09 sec)

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

Cоздание индексов

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

Одна из общих проблем, с которыми сталкиваются новички в MySQL, состоит в том, что они сталкиваются с достаточно низкой производительностью, в то время как источники утверждают, наоборот, об исключительно высокой производительности MySQL. Проблема связанная с низкой производительностью, возникает из-за того, что новички попросту забывают о создании индексов в своих базах данных. (В MySQL допустимо создавать таблицы без первичных ключей либо индексов)

Для начала рассмотрим, что будут делать индексы, которые создаются автоматически. Если оказывается, что запускаете множество запросов в столбце, который не является ключевым, можете создать по нему индекс и тем самым увеличить производительность. Индекс создается с помощью опреатора CREATE INDEX. Общий синтаксис этого оператора выглядит следующим образом:

CREATE [UNIQUE [FULLTEXT] INDEX index_name
ON table_name (index_column_name [(length)] [ASC|DESC], ...])

Здесь index_name — имя индекса, table_name — имя таблицы, index_column_name — имя столбца, по которому создается индекс. Индексы типа FULLTEXT используются для текстовых полей.

Необязательное поле length (длина) — позволяет указать, что индексироваться должны только первые length символов столбца. Можно также выбрать, как должна выполняться индексация: по возрастанию (ASC) или по убыванию (DESC); по умолчанию принимается ASC.

Возможно вам известно, что MySQL поддерживает более одного типа таблиц или механизмов хранения, включая несколько типов, безопастных в отношении транзакций. До настоящего момента все таблицы в базе данных используют механизм хранения по умолчанию — MyISAM.