Курс PHP для начинающих - с нуля до джуниора за 2 месяца. Жми!
mysql03 34

Проектирование базы данных MySQL

Курс PHP для начинающих

Тщательное проектирование базы данных чрезвычайно важно для безупречной работы приложения. Как установка принтера в дальнем конце офиса ведет к снижению производительности труда, размещение данных со слабыми взаимосвязями снижает эффективность и может вынудить сервер базы данных тратить значительное время на поиск требуемых данных. Разрабатывая структуру базы данных, задумайтесь о вопросах, возникающих при работе с ней. Например: «Какие дополнительные сведения имеются о продаваемом продукте?» Или: «Верны ли эти имя пользователя и пароль?»

MySQL – это реляционная база данных. Важная особенность реляционных систем, их отличие от одноуровневых баз данных – возможность располагать данные в нескольких таблицах. Взаимосвязанные данные можно хранить в отдельных таблицах и объединять по ключу, общему для обеих таблиц. Ключ – это отношение (relation) между таблицами. Выбор первичного ключа (primary key) – наиболее важное решение, принимаемое при разработке новой базы данных. Самое главное, что следует понимать, – вы должны гарантировать уникальность выбранного ключа. Если есть вероятность того, что значение некоторого атрибута может совпасть у двух записей, то его нельзя использовать в качестве первичного ключа. Если таблица содержит ключевые поля из другой таблицы, то между ними образуется связь – взаимоотношением внешнего ключа (foreign key), например «начальник-подчиненный» или «покупатель-покупка».

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

Код SQL для примеров
-- Создадим таблицу Customers (покупатели)
CREATE TABLE Customers (
    id INT NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(32),
    secondname VARCHAR(50),
    adress VARCHAR(256),
    telephone VARCHAR(20),
    bookTitle VARCHAR(256),
    bookAuthor1 VARCHAR(64),
    bookAuthor2 VARCHAR(64),
    pageCount INT(4),
    dateOrder DATETIME,
PRIMARY KEY(id)) CHARACTER SET utf8;
    
-- Наполнить таблицу какими-то данными
INSERT INTO Customers VALUES 
	(1, 'Александр', 'Иванов', 'Ленинский проспект 68 - 34, Москва 119296', '+7-920-123-45-67', 'Золотые сказки', 'Александр Сергеевич Пушкин', '', 128, '2013-04-18 14:56:00'),
	(NULL, 'Дмитрий', 'Петров', 'Хавская 3 - 128, Москва 115162', '+7-495-123-45-67', 'ASP.NET MVC 4', 'Джесс Чедвик', 'Тодд Снайдер', 432, '2013-02-11 09:18:00'),
	(NULL, 'Дмитрий', 'Петров', 'Хавская 3 - 128, Москва 115162', '+7-495-123-45-67', 'LINQ. Язык интегрированных запросов', 'Адам Фримен', 'Джозеф С. Раттц', 656, '2013-02-25 19:44:00'),
	(NULL, 'Александр', 'Иванов', 'Ленинский проспект 68 - 34, Москва 119296', '+7-920-123-45-67', 'Сказки Старого Вильнюса', 'Макс Фрай', '', 480, '2013-05-02 14:12:00'),
	(NULL, 'Александр', 'Иванов', 'Ленинский проспект 68 - 34, Москва 119296', '+7-920-123-45-67', 'Реверс', 'Сергей Лукьяненко', 'Александр Громов', 352, '2013-03-12 08:25:00'),
	(NULL, 'Елена', 'Козлова', 'Тамбовская - 47, Санкт-Петербург 192007', '+7-920-765-43-21', 'Золотые сказки', 'Александр Сергеевич Пушкин', '', 128, '2013-04-12 12:56:00'),
	(NULL, 'Елена', 'Козлова', 'Тамбовская - 47, Санкт-Петербург 192007', '+7-920-765-43-21', 'ASP.NET MVC 4', 'Джесс Чедвик', 'Тодд Снайдер', 432, '2013-04-14 10:11:00');

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

Курс PHP для начинающих

Нормализация

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

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

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

Исходная таблица Customers

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

Курс PHP для начинающих

Формы нормализации

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

Первая нормальная форма

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

В созданной нами таблице нарушено требование, предъявляемое к повторяющимся столбцам, потому что в столбцах "bookAuthor1" и "bookAuthor2" хранятся одинаковые по смыслу данные. Это несоответствие надо устранить, в противном случае вам может потребоваться добавить много полей для хранения имен авторов (например, если у книги три автора), что приведет к неоправданному расходу пространства, или может не хватить предусмотренного количества полей для хранения всех имен, если над книгой трудились много авторов. Решение заключается в том, чтобы переместить имена всех авторов в отдельную таблицу, которая будет связана с таблицей книг.

Вторая нормальная форма

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

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

Код SQL
-- Создадим новую таблицу Books
CREATE TABLE Books (
    bookId INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(500),
    authors VARCHAR(1000),
    pageCount INT(4),
PRIMARY KEY(bookId)) CHARACTER SET utf8;

-- Заполним таблицу Books и столбец bookId таблицы customers
INSERT INTO Books VALUES(1, 'Золотые сказки', 'Александр Сергеевич Пушкин', 128),
	(NULL, 'ASP.NET MVC 4', 'Джесс Чедвик, Тодд Снайдер', 432),
	(NULL, 'LINQ. Язык интегрированных запросов', 'Адам Фримен, Джозеф С. Раттц', 656),
	(NULL, 'Сказки Старого Вильнюса', 'Макс Фрай', 480),
	(NULL, 'Реверс', 'Сергей Лукьяненко, Александр Громов', 352);

-- Видоизменим таблицу Customers удалив избыточные столбцы bookTitle, bookAuthor1, bookAuthor2, pageCount
-- и добавим столбец bookId
ALTER TABLE customers DROP bookTitle, DROP bookAuthor1, DROP bookAuthor2, DROP pageCount, ADD bookId INT(4);

UPDATE Customers SET bookId = 1 WHERE id = 1;
UPDATE Customers SET bookId = 2 WHERE id = 2;
UPDATE Customers SET bookId = 3 WHERE id = 3;
UPDATE Customers SET bookId = 4 WHERE id = 4;
UPDATE Customers SET bookId = 5 WHERE id = 5;
UPDATE Customers SET bookId = 1 WHERE id = 6;
UPDATE Customers SET bookId = 2 WHERE id = 7;

Этот код позволяет создать дополнительную таблицу Books хранящую данные о книгах. Однако мы еще не выполнили полную нормализацию по второй форме. Можно заметить, что в нескольких строках таблицы Customers повторяется информация о пользователях, сделавших несколько заказов. Для приведения ко второй нормальной форме мы определим новую таблицу Orders (Заказы):

Код SQL
-- Создадим новую таблицу Orders (orderId - идентификатор заказа, userId - идентификатор пользователя, который сделал заказ)
CREATE TABLE Orders (
    orderId INT NOT NULL AUTO_INCREMENT,
    userId INT,
    bookId INT,
    dateOrder DATETIME,
PRIMARY KEY(orderId)) CHARACTER SET utf8;

-- Видоизменим таблицу Customers и добавим данные Orders, обратите внимание
-- что проводить нормализацию заполненной базы данных является трудоемкой задачей,
-- поэтому ее нужно проводить на этапе проектирования базы данных
INSERT INTO Orders (dateOrder, bookId) SELECT dateOrder, bookId FROM Customers;

UPDATE Orders SET userId = 1 WHERE orderId = 1;
UPDATE Orders SET userId = 2 WHERE orderId = 2;
UPDATE Orders SET userId = 2 WHERE orderId = 3;
UPDATE Orders SET userId = 1 WHERE orderId = 4;
UPDATE Orders SET userId = 1 WHERE orderId = 5;
UPDATE Orders SET userId = 3 WHERE orderId = 6;
UPDATE Orders SET userId = 3 WHERE orderId = 7;

ALTER TABLE customers DROP dateOrder;

-- Удалить дублирующие данные пользователей из таблицы Customers
ALTER IGNORE TABLE customers ADD UNIQUE INDEX (telephone);
ALTER TABLE customers DROP bookId;

Теперь данные оформлены безупречно. У нас появились отдельные таблицы со сведениями о покупателях (Customers), книгах (Books) и покупках (Orders).

Третья нормальная форма

Если вы завершили приведение к первой и второй нормальным формам, возможно, вам не потребуется ничего больше делать с базой данных, чтобы привести ее к третьей нормальной форме. Для приведения к третьей нормальной форме нужно просмотреть таблицы и выделить данные, которые не зависят от первичного ключа, но зависят от других значений. Пока еще не совсем понятно, как применить это к нашим таблицам. В таблице Customers компоненты адреса не имеют прямого отношения к покупателю. Название улицы и номер дома связаны с почтовым индексом, почтовый индекс – с городом и, наконец, сам город – с областью или краем. Третья нормальная форма требует, чтобы каждая такая часть данных была выделена в отдельную таблицу.

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

Код SQL
-- Создадим новую таблицу Adresses
CREATE TABLE Adresses (
    userId INT NOT NULL AUTO_INCREMENT,
    city VARCHAR(30),
    street VARCHAR(50),
    postcode INT(6),
PRIMARY KEY(userId)) CHARACTER SET utf8;

-- Видоизменим таблицу Customers и добавим данные в Adresses
ALTER TABLE Customers DROP adress;
INSERT INTO Adresses (city, street, postcode) VALUES ('Москва', 'Ленинский проспект 68 - 34',  119296),
	('Москва', 'Хавская 3 - 128',  115162),
    ('Санкт-Петербург', 'Тамбовская - 47', 192007);
UPDATE Customers SET id = 3 WHERE id = 6;

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

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

Курс PHP для начинающих

Типы связей

Взаимоотношения или связи, в базах данных подразделяются на следующие категории:

  • связи "один-к-одному";

  • связи "один-ко-многим";

  • связи "многие-ко-многим".

Мы рассмотрим каждую из этих связей на примере созданной нами базы данных.

Связи "один-к-одному"

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

Связь "один-к-одному" между покупателями и их адресами

Например, чтобы вывести адрес пользователя "Александр Иванов" можно воспользоваться следующей SQL-конструкцией:

Код SQL
SELECT * FROM customers JOIN adresses ON (customers.id = adresses.userid) WHERE customers.id = 1;
Пример использования связи "один-к-одному"

Связь "один-ко-многим"

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

Связь "один-ко-многим" между покупателями и заказами, заказами и книгами

Например, чтобы вывести все заказы пользователя "Александр Иванов" можно воспользоваться следующей SQL-конструкцией:

Код SQL
SELECT * FROM customers JOIN orders ON (customers.id = orders.userid) WHERE customers.id = 1;
Пример использования связи "один-ко-многим"

Связь "многие-ко-многим"

Связь "многие-ко-многим" возникает между двумя таблицами, когда в каждой из них может присутствовать несколько ключей другой таблицы. Например, покупатель приобретает в интернет-магазине сразу несколько книг. Или одну и ту же книгу приобретают несколько покупателей. На рисунке ниже показана связь "многие-ко-многим" между покупателями и приобретенными книгами:

Связь "многие-ко-многим" между покупателями и приобретенными книгами

Чтобы данные со связью "многие-ко-многим" могли быть представлены в базе данных, этот тип связи преобразуется в две связи "один-ко-многим" с помощью таблицы отображения (mapping table). В нашем случае такой таблицей является Orders.

Курс PHP для начинающих

Создание резервных копий и восстановление данных

Даже при грамотном администрировании баз данных иногда возникают определенные проблемы. Аппаратный сбой может привести, в частности, к непредсказуемому поведению веб-страниц. Теперь, когда вы работаете с базой данных, простого резервного копирования файлов (HTML, PHP и изображений) на веб-сервере недостаточно. Нет ничего хуже, чем заставлять пользователей своего веб-сайта повторно вводить учетную информацию для регистрации. При наличии полной резервной копии вы сможете оценить разницу между восстановлением за час и повторным изобретением колеса. Мы рассмотрим несколько тактик резервного копирования баз данных.

Копирование файлов базы данных

Вы можете просто копировать файлы базы данных MySQL, как делаете это с файлами HTML и PHP. Если есть возможность создавать резервные копии обычных файлов, точно так же можно создавать резервные копии файлов базы данных MySQL.

Мы не рекомендуем использовать подобный подход при перемещении базы данных с одной машины на другую, поскольку в разных версиях MySQL файлы баз данных могут иметь разные форматы. MySQL сохраняет свои файлы с данными в специальном каталоге, который, как правило, размещается в C:\Program Files\MySQL\MySQL Server 4.1\data\[имя_базы_данных] в ОС Windows, и в /var/lib/mysql – в различных UNIX-системах, например Linux и Mac OS X. Перед копированием файлов базы данных необходимо остановить работу сервера MySQL, чтобы обеспечить неизменность всех файлов во время копирования.

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

Команда mysqldump

Гораздо лучше выполнять резервное копирование с помощью инструмента командной строки MySQL. Это инструмент, позволяющий создать резервную копию и восстановить данные, а также переместить базу данных с одного сервера на другой; утилита mysqldump создает текстовый файл с инструкциями SQL, необходимыми для создания объектов базы данных и вставки данных.

Утилита mysqldump запускается из командной строки и принимает параметры для создания резервной копии единственной таблицы, базы данных и т.п. Синтаксис команды:

mysqldump -u пользователь -p пароль объекты_для_резервного_копирования

По умолчанию mysqldump создает и выводит резервную копию на стандартное устройство вывода (обычно это экран). Указанный пользователь должен иметь право на доступ к копируемым объектам. Перед копированием утилита предложит ввести пароль для данного пользователя. Чтобы выполнить копирование в файл, нужно добавить в конец команды символ (>) и указать имя файла.

Ниже мы привели примеры команд, выполняющих резервное копирование базы данных с именем users из командной строки:

CMD
mysqldump -u root -p users > my_backup.sql

Данная команда сообщает утилите mysqldump необходимость зарегистрироваться в базе данных с привилегиями пользователя root и создать резервную копию базы данных users. Перед копированием у вас будет запрошен пароль пользователя root, указанный в процессе установки. Результат работы утилиты сохраняется в файле с именем my_backup.sql с помощью оператора перенаправления – символа "больше чем" (>). Ниже показана начальная часть файла my_backup.sql, созданного утилитой mysqldump:

Экспортированный код SQL
-- MySQL Dump
CREATE DATABASE IF NOT EXISTS users DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-- ...

CREATE TABLE IF NOT EXISTS `adresses` (
  `userId` int(11) NOT NULL AUTO_INCREMENT,
  `city` varchar(30) DEFAULT NULL,
  `street` varchar(50) DEFAULT NULL,
  `postcode` int(6) DEFAULT NULL,
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Дамп данных таблицы `adresses`
--

INSERT INTO `adresses` (`userId`, `city`, `street`, `postcode`) VALUES
(1, 'Москва', 'Ленинский проспект 68 - 34', 119296),
(2, 'Москва', 'Хавская 3 - 128', 115162),
(3, 'Санкт-Петербург', 'Тамбовская - 47', 192007);

-- --------------------------------------------------------

--
-- Структура таблицы `books`
--

CREATE TABLE IF NOT EXISTS `books` (
  `bookId` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) DEFAULT NULL,
  `authors` varchar(1000) DEFAULT NULL,
  `pageCount` int(4) DEFAULT NULL,
  PRIMARY KEY (`bookId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Дамп данных таблицы `books`
--

INSERT INTO `books` (`bookId`, `title`, `authors`, `pageCount`) VALUES
(1, 'Золотые сказки', 'Александр Сергеевич Пушкин', 128),
(2, 'ASP.NET MVC 4', 'Джесс Чедвик, Тодд Снайдер', 432),
(3, 'LINQ. Язык интегрированных запросов', 'Адам Фримен, Джозеф С. Раттц', 656),
(4, 'Сказки Старого Вильнюса', 'Макс Фрай', 480),
(5, 'Реверс', 'Сергей Лукьяненко, Александр Громов', 352);

-- ...

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

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

CMD
mysqldump -u root -p users customers > my_backup_customers.sql

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

CMD
mysqldump -u root -p --all-databases > my_backup.sql

Пустая копия базы данных (только структура) создается с помощью ключа --no-data. Ключ --no-create-info позволяет выполнить противоположную операцию – создать только резервную копию данных. Разумеется, в резервной копии мало проку, если не знаешь, как восстановить из нее базу данных.

Восстановление из резервной копии

Восстановить базу данных из файла, созданного с помощью утилиты mysqldump, достаточно просто. Файл резервной копии – это просто набор инструкций SQL, которые могут исполняться клиентом командной строки mysql и тем самым восстанавливать данные из резервной копии.

Если резервная копия базы данных в файле my_backup.sql создавалась с ключом --all-databases, то восстановить базу данных можно так:

CMD
mysql -u root -p < my_backup.sql
Курс PHP для начинающих

Расширенный SQL

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

Индексы

Индексы в базе данных играют ту же роль, что и алфавитный указатель в книге. Если вы попытаетесь найти в книге слова CREATE TABLE без алфавитного указателя, то сначала вам придется просмотреть значительное число страниц, чтобы обнаружить подходящий раздел. Затем необходимо просмотреть весь раздел. При таком способе очень неэффективно расходуется время – ваше или базы данных. Решение этой проблемы заключается в том, чтобы добавить индексы.

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

Однако эта проблема имеет и обратную сторону. Если индексы так хороши, почему бы не индексировать все подряд? Есть несколько причин, которые можно привести против такого решения:

  • пространство, выделяемое под индексы, ограничено;

  • даже в обычных книгах создание и обслуживание гигантских всеобъемлющих алфавитных указателей очень неэффективно;

  • слишком большой объем данных в индексах приводит к увеличению времени чтения индексов при выборке данных.

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

  1. Вместе с оператором WHERE - например, для выполнения запроса SELECT * FROM customers WHERE firstname = 'Elena'; будет использован индекс по полю firstname (если он существует).

  2. Вместе с оператором ORDER BY - например, для выполнения запроса SELECT * FROM orders ORDER BY dateOrder; будет использован индекс по полю dateOrder (если он существует).

  3. Вместе с операторами MIN и MAX - например, для поля, передаваемого функции MIN или MAX, определен индекс.

Просто запомните: индексы должны быть определены до того, как они будут использоваться. Можно определить индексы для базы данных в команде CREATE TABLE либо создать их позже, для уже существующих таблиц, с помощью специальных команд SQL. Если определение индекса является частью команды CREATE TABLE, оно указывается в конце блока кода, например так:

UNIQUE customers(firstname)

Команда UNIQUE создает индекс для поля с именем firstname. Тот же самый индекс можно создать с помощью специальной инструкции SQL, как показано ниже:

Код SQL
CREATE UNIQUE INDEX authind ON Customers (firstname);

Попробуем получить описание таблицы Customers:

Код SQL
DESCRIBE Customers;

Результат:

Таблица Customers с уникальным индексом

Обратите внимание на новое значение UNI в столбце key для поля firstname.

В MySQL можно создавать индексы, состоящие из нескольких столбцов (на приведенном выше рисунке уникальные индексы имеют столбцы firstname и telephone). Такие составные индексы позволяют обеспечить уникальную комбинацию двух или больше полей. Наилучшими кандидатами в индексы являются поля, которые с большой долей вероятности будут участвовать в конструкции WHERE. А если вы точно знаете, какие комбинации ключей будут использоваться, то они станут наилучшими кандидатами для построения индексов, состоящих из нескольких столбцов.

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

Расширенная выборка данных

Мы уже рассматривали способ соединения таблиц в инструкции SELECT с помощью инструкции WHERE, но есть и другой способ соединения таблиц. Если заменить ключевое слово WHERE словом LEFT JOIN ON, то будет выполнено левое, или внешнее соединение (outer join). Левое соединение позволяет произвести запрос к двум таблицам, между которыми есть связь, но при этом для одной из таблиц возвращаются записи, даже если они не соответствуют записям в другой таблице. На примере наших таблиц можно было бы построить запрос, который возвращал бы список не только покупателей с их покупками, но и покупателей, которые не сделали ни одной покупки.

Синтаксис:

SELECT поля FROM левая_таблица 
LEFT JOIN правая_таблица 
ON левая_таблица.поле_связи = правая_таблица.поле_связи;

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

Код SQL
SELECT b.title, COUNT(orderId) "Books" FROM Books b, Orders o WHERE b.bookId = o.bookId GROUP BY title;

Результат исполнения запроса:

Группировка и объединение таблиц

В таблице ниже приведен перечень функций, используемых с инструкцией GROUP BY:

Функции для работы со сгруппированными данными
Функция Действие, выполняемое над сгруппированными данными
COUNT() Подсчет количества строк
SUM() Подсчет суммы значений
AVG() Вычисление среднего значения
MIN() Определение минимального значения
MAX() Определение максимального значения

Эти функции можно использовать в запросе и без инструкции GROUP BY. В этом случае все полученные данные рассматриваются как принадлежащие одной группе.

Функции базы данных

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

Функции для работы со строками

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

В языке PHP объединение строк выполняется с помощью оператора точки (.); в MySQL есть аналогичная функция CONCAT, объединяющая строковые значения полей. Например, функция CONCAT позволяет вернуть единственное поле, соединяющее в себе имя покупателя и его номер телефона:

Код SQL
SELECT CONCAT ('Пользователь: ', firstname, ' ', secondname, ' , телефон: ', telephone) FROM Customers;

Этот запрос вернет:

Конкатенация строк в MySQL

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

Имя поля, указываемое в качестве параметра функции, не нужно заключать в одиночные или двойные кавычки. Иначе MySQL примет его за литеральное значение. Функция CONCAT объединит столько полей, сколько вы ей зададите.

Иногда соединяемые при конкатенации поля разделяют некоторым символом. Это может потребоваться, например, при экспортировании таблиц. В таком случае следует применять функцию CONCAT_WS. Например, следующий запрос позволяет получить значения всех полей таблицы Customers, разделенные запятыми:

Код SQL
SELECT CONCAT_WS(',', *) FROM Customers;

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

Функции для работы с датой и временем

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

Дни, месяцы, годы и недели

Бывает трудно припомнить, глядя на дату, – вторник это был или четверг. В MySQL есть функции, позволяющие мгновенно ответить на подобные вопросы. Это очень удобно! Функции для подобных расчетов присутствуют и в PHP.

Функция WEEKDAY принимает дату в качестве аргумента и возвращает число. Это число означает день недели: понедельнику соответствует 0, вторнику – 1 и т.д. Есть и аналогичная функция DAYOFWEEK, которая, по смутным предположениям, должна делать то же самое, но нумерует дни недели иначе – начиная с воскресенья, которому соответствует число 1. В примере ниже показано, как с помощью функции WEEKDAY определяется день недели, соответствующий 12 апреля 1961 года:

Код SQL
SELECT WEEKDAY('1961-04-12');

Этот запрос вернет число 2, таким образом, 12 апреля 1961 года была среда. Число вместо дня недели может показаться несколько странным, но в MySQL есть и функция, которая возвращает название дня недели (на английском) - DAYNAME.

Есть еще функции DAYOFMONTH и DAYOFYEAR, аналогичные функции DAYOFWEEK. Они получают дату в качестве аргумента и возвращают число. Функция DAYOFMONTH возвращает число месяца, а функция DAYOFYEAR – количество дней, прошедших с начала календарного года. Есть функция MONTHNAME, аналогичная функции DAYNAME, которая возвращает название месяца.

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

Часы, минуты и секунды

При работе с такими типами данных, как datetime, timestamp или time, в поле сохраняется указанное время. В MySQL есть несколько функций для работы с этим временем. Их имена вполне логичны: HOUR, MINUTE и SECOND. Функция HOUR принимает в качестве аргумента время и возвращает число часов в диапазоне от 0 до 23. Функция MINUTE возвращает число минут в диапазоне от 0 до 59, аналогично, функция SECOND возвращает число секунд в том же диапазоне.

MySQL предоставляет функции DATE_ADD и DATE_SUB, позволяющие складывать и вычитать даты. Их синтаксис:

DATE_ADD(дата, INTERVAL выражение тип)
DATE_SUB(дата, INTERVAL выражение тип)

Например, если вы захотите найти дату, которая была 12 дней тому назад, можете воспользоваться запросом из примера:

Код SQL
SELECT DATE_SUB(NOW(), INTERVAL 12 day);

Дата, которую вернет этот запрос, зависит от того, когда вы его выполните. Начиная с версии 3.23, MySQL поддерживает синтаксис операторов (+) и (-) для работы с датами, как показано ниже:

Код SQL
-- Результат будет аналогичен предыдущему
SELECT NOW() - INTERVAL 12 day;

Функция NOW возвращает текущие дату и время согласно системным часам вашего компьютера (или сервера). Но если часы показывают неверную дату, то и функция NOW вернет ошибочную дату. В MySQL есть несколько функций, возвращающих текущую дату, время или текущую дату и время одновременно. Функции CURDATE и CURRENT_DATE возвращают текущую дату в формате 'YYYY-MM-DD'. Функции CURTIME и CURRENT_TIME возвращают текущее время в формате 'HH:MM:SS'.

Транзакции

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

Такой механизм базы данных с поддержкой транзакций, как InnoDB или BDB, начинает транзакцию по команде start transaction. Завершается транзакция при подтверждении или отмене изменений. Завершить транзакцию можно двумя командами. Команда commit сохраняет все изменения в базе данных. Команда rollback отменяет все изменения.

В примере ниже создается таблица с поддержкой транзакций, в нее вставляются данные, затем запускается транзакция, в рамках которой данные удаляются, и в заключение выполняется откат транзакции (отмена удаления):

Код SQL
CREATE TABLE sample_innodb (
    id int(11) NOT NULL auto_increment,
    name varchar(150) default NULL,
PRIMARY KEY (id)
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO sample_innodb VALUES 
	(1, 'Александр'),
	(2, 'Дмитрий');

start transaction;
DELETE FROM sample_innodb WHERE id = 1;
DELETE FROM sample_innodb WHERE id = 2;
rollback;

Поскольку произошел откат транзакции, данные из таблицы не были удалены.

Команды MySQL
Взаимодействие PHP и MySQL

Комментарии (0)

Результаты поиска по запросу

Система Orphus