Главное меню

Создание базы данных для веб-приложений

В этом материале мы обсудим методику установки базы данных MySQL для ее использования на веб-сайте.

Рассмотрим следующие темы.

• Создание базы данных.

• Настройка пользователей и полномочий.

• Знакомство с системами полномочий MySQL.

• Создание таблиц базы данных.

• Создание индексов.

• Типы столбцов в MySQL.

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

Customers(CustomerID, Name, Address, City)

Orders(OrderID, CustomerID, Amount, Date)

Books(ISBN, Author, Title, Price)

Order_Items(OrderID, ISBN, Quantity)

Book_Reviews(ISBN, Reviews)

Напомним, что первичные ключи подчеркнуты, а внешние ключи представлены курсивом.

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

• Инсталляция необходимых файлов.

• Настройка пользователя, от имени которого будет выполняться MySQL.

• Настройка пути.

• При необходимости – запуск mysql_install_db.

• Установка пароля для привилегированного пользователя.

• Удаление анонимного пользователя и тестирование базы данных.

• Первоначальный запуск сервера MySQL и его настройка на автоматический запуск в будущем.

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

Если на каком-то этапе работы с этим материалом возникают проблемы, то они могут быть следствием неправильной установки и настройки системы MySQL.

В данных материалах взаимодействие с MySQL осуществляется с использованием клиента командной строки под названием монитора MySQL, который входит в состав каждой версии MySQL. Тем не менее, допускается применение и других клиентов. Например, если вы используете MySQL в веб-среде стороннего хостинга, администраторы предоставляют браузерный интерфейс phpMyAdmin. Различные графические интерфейсы выглядят по-разному и могут отличаться от описанных здесь.

Использование монитора MySQL

Примеры команд MySQL в этом и в следующих материалах завершаются точкой с запятой (;), которая сообщает MySQL о том, что команду необходимо выполнить. Если точку с запятой не поставить, ничего не произойдет. Начинающие пользователи часто сталкиваются с подобной проблемой.

Пропуск точки с запятой позволяет вводить команды в нескольких строках. Мы воспользовались этой возможностью, чтобы облегчить чтение примеров. Продолжения строк легко узнать по символу продолжения, который выводит MySQL. Он выглядит так, как показано ниже:

mysql> grant select

–>

Этот символ означает, что MySQL ожидает продолжения ввода команды. До тех пор, пока не будет введена точка с запятой, после каждого нажатия клавиши <Enter> на экране будут появляться символы продолжения.

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

Вход в MySQL

Для входа в систему MySQL перейдите в командную строку и наберите строку:

mysql -h имя_хоста -u имя_пользователя -р

Команда mysql запускает монитор MySQL. Это клиент командной строки, который соединяется с сервером MySQL.

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

С помощью ключа -u указывается имя_пользователя, под которым необходимо подключиться. Если имя пользователя не указано, по умолчанию будет использоваться имя, под которым был выполнен вход в операционную систему.

Если сервер MySQL установлен на вашем собственном компьютере или сервере, необходимо войти в систему под именем root (привилегированный пользователь) и создать базу данных, о которой мы поговорим чуть позже в этом разделе. Если установка была только что выполнена, то root будет единственным пользователем, который имеет доступ к системе.

Если MySQL используется на компьютере, администратором которого является кто-то другой, применяйте имя пользователя, которое вам выдал администратор.

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

Если вы входите в систему под именем root и пароль для этого пользователя еще не установлен, настоятельно рекомендуем побыстрее установить пароль. Без пароля для пользователя root система, по сути, беззащитна.

Включать пароль в эту строку не обязательно – сервер MySQL запросит его самостоятельно. Фактически лучше пароль не включать в командную строку. Если он введен в командной строке, то появится на экране в форме обычного текста и таким образом может оказаться доступным остальным пользователям.

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

Enter password:

(Если этого не произошло, убедитесь в том, что сервер MySQL запущен, а команда mysql указана где-то в пути поиска.)

Теперь необходимо ввести пароль. Если все пройдет хорошо, вывод, отображаемый на экране, должен быть подобным следующему:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Вас приветствует монитор MySQL. Команды завершаются символами ; или \g.
Идентификатор вашего соединения1, версия сервера: 5.0 .18-nt-log

Для получения справки введите 'help; ' или ' \h '.
Чтобы очистить буфер, введите ' \с '.

mysql>

Если на вашем компьютере такого ответа не последовало, убедитесь, что была выполнена команда mysql_install_db (если это необходимо), установлен и правильно введен пароль для пользователя root. Если вам приходится работать на чужом компьютере, просто убедитесь в корректности ввода пароля.

Теперь на экране должно наблюдаться приглашение на ввод команды MySQL, т.е. система готова к созданию базы данных.

При работе на собственном компьютере выполните инструкции из следующего раздела.

Если вы заходите в систему с чужого компьютера, все это уже должно быть сделано. В этом случае можно сразу перейти к разделу "Использование требуемой базы данных". Для получения общего представления можете ознакомиться с промежуточными разделами, но описанные в них команды выполнить не удастся. (Во всяком случае, они не должны выполняться!)

Создание баз данных и пользователей

Система баз данных MySQL может поддерживать множество различных баз данных. Обычно для одного приложения создается одна база данных. В нашем примере с приложением "Буквофил" база данных будет называться books.

Создание базы данных представляет собой наиболее простую задачу. Введите в командной строке MySQL:

mysql> create database имя_базы;

Вместо имя_базы следует указать имя базы данных, которую требуется создать. Чтобы приступить к реализации примера приложения "Буквофил", сейчас необходимо создать базу данных books.

Это все, что нужно сделать. Ответ должен выглядеть подобно показанному ниже:

Query OK, 1 row affected (0.06 sec)
Запрос успешно выполнен, 1 строка изменена (0.06 с)

Это значит, что все действия были выполнены правильно. Если подобного ответа не последовало, убедитесь, что в конце строки присутствует точка с запятой. Точка с запятой сообщает MySQL, что ввод команды завершен и ее пора выполнять.

Настройка пользователей и полномочий

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

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

Может возникнуть вопрос: "Зачем вообще это нужно?" Ответ связан с системой полномочий.

Знакомство с системой полномочий MySQL

Одно из главных достоинств MySQL – поддержка сложной системы полномочий.

Полномочие (privilege) – это право определенного пользователя выполнять определенное действие над определенным объектом. Это понятие очень близко к понятию прав доступа к файлам.

При создании пользователя в среде MySQL ему предоставляются определенные полномочия, которые определяют, что пользователь может делать в системе, а что – нет.

Принцип минимально необходимых полномочий

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

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

Это справедливо для MySQL, как и для любой другой системы. Так, чтобы выполнять запросы из Интернет-среды, обычному пользователю не нужны все те полномочия, которые предоставлены привилегированному пользователю. Следовательно, потребуется создать еще одного пользователя, который будет обладать лишь теми полномочиями, которые необходимы для доступа к только что созданной базе данных.

Настройка пользователей: команда GRANT

Команды GRANT и REVOKE применяются для предоставления и лишения прав пользователей MySQL на четырех уровнях полномочий:

• глобальный;

• базы данных;

• таблицы;

• столбца.

Чуть позже будет показано, как всем этим пользоваться.

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

GRANT полномочия [столбцы]

ON элемент

ТО имя_пользователя [IDENTIFIED BY 'пароль']

[REQUIRE параметры_ssl]

[WITH [GRANT OPTION | огранич_параметры] ]

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

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

Заполнитель столбцы необязателен. Его можно использовать для указания полномочий применительно к конкретным столбцам. При этом можно указывать имя одного столбца либо разделенный запятыми список имен столбцов.

Заполнитель элемент может быть базой данных или таблицей, к которой применяются новые полномочия. Указав в качестве элемент строку *.*, можно предоставить полномочия для всех баз данных в целом. Такое действие называется предоставлением глобальных полномочий. Этот же эффект достигается и указанием лишь одного символа *, если полномочия не должны применяться к какой-то конкретной базе данных.

Обычно полномочия применяются ко всем таблицам в определенной базе данных – имя_БД.*, к конкретной таблице – имя_БД.имя_таблицы, либо к определенным столбцам – имя_БД.имя_таблицы с последующим списком необходимых столбцов на месте заполнителя столбцы. Все перечисленное представляет три других доступных уровня полномочий: соответственно базы данных, таблицы и столбца. Если при выдаче этой команды используется какая-то конкретная база данных, то просто параметр имя_таблицы будет интерпретироваться как "таблица в текущей базе данных".

В качестве значения имя_пользователя должно быть указано имя, под которым пользователь должен входить в MySQL. Помните, что оно не обязательно должно совпадать с регистрационным именем, под которым выполняется вход в систему. В MySQL имя_пользователя может включать в себя и имя хоста, что весьма удобно для того, чтобы различать пользователей, скажем, laura (которое интерпретируется как laura@localhost) и laura@somewhere. com. Это очень удобно, поскольку часто пользователи в различных доменах имеют одни и те же имена. Кроме того, повышается степень защищенности системы, поскольку можно указать, откуда пользователи могут подключаться к базе данных и даже к каким базам данных или таблицам они могут иметь доступ.

В качестве заполнителя пароль следует указать пароль, необходимый для входа. Руководствуйтесь общими правилами использования паролей. О безопасности мы поговорим чуть позже, отметим лишь, что пароль не должен быть легко угадываемым. Не стоит употреблять слово из словаря или совпадающее с именем пользователя. В идеале пароль должен включать в себя прописные и строчные буквы и небуквенные символы.

Конструкция REQUIRE позволяет указывать, что пользователь должен подключаться через SSL (Secure Sockets Layer – уровень защищенных сокетов) и передавать дополнительные параметры для SSL. Дополнительную информацию о SSL-соединениях с MySQL можно найти в руководстве по MySQL.

Опция WITH GRANT OPTION, если она указана, дает право пользователю предоставлять свои полномочия другим.

Взамен конструкции GRANT OPTION можно указывать следующие конструкции:

MAX_QUERIES_PER_HOUR n

или

MAX_UPDATES_PER_HOUR n

или

MAX_CONNECTIONS_PER_HOUR n

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

Полномочия хранятся в пяти системных таблицах, расположенных в базе данных mysql. Эти пять таблиц называются mysql.user, mysql.db, mysql.host, mysql.tables_priv и mysql.columns_priv. Вместо использования команды GRANT можно непосредственно изменять эти таблицы.

Типы и уровни полномочий

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

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

Полномочия для обычных пользователей непосредственно связаны с определенными командами SQL и правами их выполнения. Подробнее команды SQL рассматриваются в следующем материале. А пока мы постараемся дать общее представление о выполняемых ими действиях. Описание полномочий приведено в табл. 9.1. В столбце "Применяется к" перечислены объекты, к которым могут применяться полномочия данного типа.

Создание базы данных для веб-приложений

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

Помимо полномочий, перечисленных в табл. 9.1, существуют еще полномочия REFERENCES и EXECUTE, которые в настоящий момент не применяются, и GRANT, которые предоставляются опцией WITH GRANT OPTION, а не в списке полномочия.

В табл. 9.2 описаны полномочия, необходимые администраторам.

Создание базы данных для веб-приложений

Эти полномочия можно предоставлять не только администраторам, но прежде чем так поступать, следует хорошо подумать.

Полномочия FILE несколько отличаются от других. Они очень удобны для пользователей, поскольку экономят массу времени, позволяя загружать данные из файла вместо того, чтобы набирать их заново. С другой стороны, у пользователя появляется возможность загрузить любой файл, который сервер MySQL в состоянии увидеть, в том числе базы данных других пользователей и файлы с паролями. Предоставляйте эти полномочия с осторожностью либо предлагайте пользователю загружать данные.

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

Создание базы данных для веб-приложений

Команда REVOKE

Противоположной команде GRANT является команда REVOKE. Она используется для лишения пользователя полномочий и по синтаксису сходна с командой GRANT:

REVOKE полномочия [(столбцы)]

ON элемент

FROM имя_пользователя

Если полномочия были предоставлены с конструкцией WITH GRANT OPTION, их можно удалить (вместе со всеми другими полномочиями) следующим образом:

REVOKE ALL PRIVILEGES, GRANT

FROM имя_пользователя

Примеры использования команд GRANT и REVOKE

Для предоставления полномочий администратору можно набрать:

mysql> grant all

-> on *

-> to fred identified by 'mnb123'

-> with grant option;

Эта команда предоставляет пользователю с именем fred и паролем mnb123 все полномочия для всех баз данных с правом их передачи другим пользователям.

Если этот пользователь в системе не нужен, лишите его всех полномочий:

mysql> revoke all privileges, grant

-> from fred;

Теперь можно определить обычного пользователя без каких-либо полномочий:

mysql> grant usage

-> on books.*

-> to anna identified by 'magic123' ;

Поговорив с Анной (anna), можно больше узнать о ее намерениях и в результате предоставить ей необходимые полномочия:

mysql> grant select, insert, update, delete, index, alter, create, drop

-> on books.*

-> to anna;

Обратите внимание, что для предоставления полномочий не требуется указывать пароль Анны.

Если мы подозреваем, что Анна собирается натворить что-то нехорошее в базе данных, ее полномочия можно ограничить:

mysql> revoke alter, create, drop

-> on books.*

-> from anna;

Позже, когда ей не нужно будет пользоваться базой данных, ее можно лишить вообще всех полномочий:

mysql> revoke all

-> on books.*

-> from anna;

Установка пользователя для доступа из Интернета

Чтобы РНР-сценарии могли подключаться к MySQL, потребуется настроить соответствующего пользователя. В этом случае также можно применить принцип минимально необходимых полномочий. При этом следует задаться вопросом: "Какие действия должны иметь право выполнять сценарии?"

В большинстве случаев сценариям понадобится проводить над строками таблиц только операции SELECT, INSERT, DELETE и UPDATE. Можно поступить следующим образом:

mysql> grant select, insert, delete, update

-> on books.*

-> to bookorama identified by 'bookorama123' ;

Понятно, что для большей безопасности следует выбрать более надежный пароль.

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

mysql> grant select, insert, update, delete, index, alter, create, drop

-> on books.*

-> to bookorama identified by 'bookorama123';

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

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

Использование требуемой базы данных

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

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

mysql> use имя_базы;

где имя_базы – имя соответствующей базы данных.

Можно и не вводить команду use, но тогда база данных должна быть указана во время входа в систему:

mysql -D имя_базы -h имя_хоста -u имя_пользователя -р

В этом примере мы будем работать с базой данных books:

mysql> use books;

После ввода этой команды MySQL должен вывести следующую строку:

Database changed

База данных изменена

Если перед началом работы база данных не была выбрана, MySQL выведет сообщение об ошибке:

ERROR 1046 (3D000) : No Database Selected

ОШИБКА 1046 (3D000) : He выбрана база данных

Создание таблиц баз данных

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

CREATE TABLE имя_таблицы (столбцы)

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

Снова вспомним схему базы данных "Буквофил":

Customers(CustomerID, Name, Address, City)

Orders(OrderID, CustomerID, Amount, Date)

Books(ISBN, Author, Title, Price)

Order_Items(OrderID, ISBN, Quantity)

Book_Reviews(ISBN, Reviews)

В листинге 9.1 показан SQL-код для создания этих таблиц, при этом подразумевается, что база данных books уже существует. Этот код можно найти в файле chapter09/bookorama.sql загружаемого кода.

Существующий SQL-файл в MySQL можно выполнить следующим образом:

> mysql -h хост -u bookorama -D books -р < bookorama.sql

(Не забудьте заменить заполнитель хост именем используемого хоста.)

В данном случае удобно использовать перенаправление, поскольку перед выполнением SQL-код можно отредактировать в любом текстовом редакторе.

Создание базы данных для веб-приложений

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

Значения других ключевых слов

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

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

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

Указывать PRIMARY KEY после названия столбца можно лишь тогда, когда мы имеем дело с первичным ключом в виде одиночного столбца. Альтернативный вариант – конструкция PRIMARY KEY в конце оператора создания таблицы order_items. В данном случае эта форма была использована потому, что первичный ключ состоит из двух столбцов. (Это также создает индекс по объединению двух столбцов.)

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

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

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

create table customers

( customerid int 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), причем эти идентификаторы должны быть беззнаковыми (unsigned). Кроме того, мы воспользовались атрибутом auto_increment, поэтому MySQL позаботится о присвоении уникальных идентификаторов – а нам одной заботой меньше.

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

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

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

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

create table orders

( orderid int unsigned not null auto_increment primary key,

customerid int unsigned not null,

amount float(6,2),

date date not null

);

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

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

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

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

create table books

( isbn char(13) not null primary key,

author char(50),

title char(100),

price float (4,2)

);

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

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

create table order_items

( orderid int unsigned not null,

isbn char(13) not null,

quantity tinyint unsigned,

primary key (orderid, isbn)

);

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

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

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

create table book_reviews

( isbn char (13) not null primary key,

review text

);

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

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

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

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

mysql> show tables;

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

Создание базы данных для веб-приложений

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

mysql> show databases;

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

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

mysql> describe books;

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

Создание базы данных для веб-приложений

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

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

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

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

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

CREATE [UNIQUE | FULLTEXT] INDEX имя_индекса

ON имя_таблицы (имя_столбца_индекса [ (длина) ] [ASC | DESC], . . . ])

(Индексы типа FULLTEXT используются для текстрвых полей; мы рассмотрим их в дальнейшем.)

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

Идентификаторы в MySQL

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

Базы данных в MySQL соответствует каталогам базовой файловой структуры, а таблицы – файлам. Это соответствие напрямую влияет на присваиваемые им имена, а также на зависимость этих имен от регистра букв – если в установленной операционной системе (ОС) имена файлов и каталогов зависят от регистра, то и имена баз данных и таблиц также будут от него зависеть (как, например, в UNIX), в противном случае – нет (например, в Windows). Имена столбцов и псевдонимы не зависят от регистра, однако в одном и том же SQL-операторе нельзя применять и строчные, и прописные символы.

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

mysqladmin -h хост -u root -p variables

В полученном выводе найдите переменную datadir.

Краткий список возможных идентификаторов приведен в табл. 9.4. Единственное дополнительное ограничение состоит в невозможности использования в идентификаторах символов ASCII(0), ASCII(255) или символа кавычки (откровенно говоря, трудно предположить, для чего они могли бы пригодиться).

Создание базы данных для веб-приложений

Так что правила предельно свободны. В качестве идентификаторов можно даже использовать зарезервированные слова и специальные символы всех видов. Единственное ограничение – если вы все же применяете всякого рода странности, то их необходимо заключать в обратные кавычки (на большинстве клавиатур они находятся на клавише с тильдой (~) в верхнем левом углу).

Например:

create database `create database`;

Однако какой бы ни была свобода действий, не стоит забывать о здравом смысле. То, что базу данных можно назвать `create database`, вовсе не означает, что так следует поступать. Здесь, как и в любой другой области программирования, должен применяться один принцип – идентификаторы должны быть как можно более осмысленными.

Типы данных столбцов

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

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

Для многих типов данных при создании столбца выбранного типа можно задавать максимальную выводимую длину. В приведенных ниже таблицах типов данных этот параметр обозначается как М. Если для данного типа он не обязателен, он заключен в квадратные скобки. Максимальное значение М составляет 255.

Необязательные значения во всех описаниях заключены в квадратные скобки.

Числовые типы

Числовые типы представляют либо целые числа, либо числа с плавающей точкой. Для чисел с плавающей точкой можно указывать количество цифр после десятичной точки. В нашем материале этот параметр обозначен как D. Максимальное значение, которое можно выбрать для D составляет 30 или M – 2 (т.е. максимальная ширина отображения минус два – один символ для вывода десятичной точки и второй для целой части числа), в зависимости от того, какое значение окажется меньшим.

Целочисленный тип может также быть определен как UNSIGNED, как показано в листинге 9.1.

Всем числовым типам можно присвоить атрибут ZEROFILL. Такие значения будут отображаться на экране с ведущими нулями. При наличии этого атрибута поле автоматически становится еще и UNSIGNED.

Целочисленные типы перечислены в табл. 9.5. Обратите внимание, что диапазоны для чисел со знаком приводятся в одной строке, а чисел без знака – во второй.

Создание базы данных для веб-приложений

Типы данных с плавающей запятой перечислены в табл. 9.6.

Создание базы данных для веб-приложений

Типы даты и времени

MySQL поддерживает несколько типов даты и времени, которые приведены в табл. 9.7. Эти типы позволяют вводить данные либо в строковом, либо в числовом формате. Особо отметим, что если в поле типа TIMESTAMP не занесено какое-либо значение вручную, в него заносится дата и время последней операции, выполненной над данной строкой. Это свойство весьма полезно при записи информации о транзакциях.

Создание базы данных для веб-приложений

В табл. 9.8 приведены различные возможные типы вывода для формата TIMESTAMP.

Создание базы данных для веб-приложений

Строковые типы

Строковые типы подразделяются на три группы. Первая группа – простые "старые добрые" строки, которые представляют собой короткие фрагменты текста. Это типы CHAR (символы фиксированной длины) и VARCHAR (символы переменной длины). В каждом типе можно указать ширину поля. Столбцы с типом CHAR будут дополняться пробелами до максимальной ширины, независимо от размеров данных, в то время как в столбцах с типом VARCHAR ширина зависит от размеров данных. (Следует отметить, что MySQL усекает пробелы в конце текстовых строк типа CHAR во время извлечения и в конце строк типа VARCHAR во время сохранения.) При работе с этими типами приходится искать компромисс между занимаемым объемом памяти и скоростью обработки.

Вторая группа – это типы TEXT и BLOB. Их размеры могут быть разными. Первый тип данных предназначен для более длинных текстовых фрагментов, второй – для бинарных данных. BLOB означает binary large object (большой двоичный объект) и может содержать любые данные – например, изображения или аудиозаписи.

На практике столбцы TEXT и BLOB идентичны, за исключением того, что данные типа TEXT чувствительны к регистру букв, a BLOB – нет. Поскольку столбцы этих типов могут хранить большие объемы данных, их применение требует более детального анализа, но об этом речь пойдет в дальнейшем.

К третьей группе принадлежат два специальных типа SET и ENUM. Тип SET используется для указания того, что значения в данном столбце принадлежат конкретному набору фиксированных значений. Столбец может содержать несколько значений из набора. Фиксированный набор может содержать до 64 элементов.

Тип ENUM представляет перечисление. Этот тип весьма схож с SET, за исключением того, что столбцы этого типа могут содержать лишь одно из фиксированных значений или значение NULL, а максимальное количество элементов в перечислении составляет 65 535.

Краткие описания строковых типов данных можно найти в табл. 9.9, 9.10 и 9.11. В табл. 9.9 приведено описание простых строковых типов.

Создание базы данных для веб-приложений

В табл. 9.10 описаны типы TEXT и BLOB. Максимальная длина поля TEXT в символах равна максимальному размеру в байтах файла, который может храниться в этом поле.

Создание базы данных для веб-приложений

В табл. 9.11 содержится описание типов ENUM и SET.

Создание базы данных для веб-приложений