Зачем создателю сайта нужна база данных?

С чего обычно начинает человек, делающий свои первые сайты? Сперва он осваивает HTML — благо это язык гипертекстовой разметки, а не язык программирования. Затем он узнает о возможности отделить оформление страницы от ее содержания с помощью таблиц каскадных стилей (CSS). Наконец следом многие осваивают JavaScript, язык программирования скриптов на стороне клиента.

На заметку: скрипты делятся на два вида. Одни выполняются на стороне клиента — то есть без изменений скачиваются на компьютер пользователя, где их выполняет сам браузер. Другие выполняются на стороне сервера. Пользователь не может получить их исходный код, а видит только результат работы — будь то текст, картинка или HTML-страничка. Первые (например, JavaScript) созданы затем, чтобы слегка разнообразить отображение страниц, вторые (например, PHP или JSP) на лету создают динамические страницы: каталоги, форумы, поисковики. Именно они и позволяют делать все то, о чем мы поговорим в этой статье.

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

К примеру, если вы хотите сделать сайт с новостной лентой, легко обновляемой галереей или гостевой книгой, то без программирования на стороне сервера уже не обойтись. Вы придете к необходимости создания того, что сейчас называется «движком» сайта, или, если выражаться более солидно, системой управления содержимым (калька с английского Content Management System (CMS).

Для большей наглядности зайдем с другой стороны и на примере посмотрим, чем отличается статический сайт от динамического. Представьте себе продуктовый ларек и продуктовый магазин. У ларька все на одном месте — и доставка, и склад, и продажа. Все это обычно вручную обслуживается одним человеком. И все на виду. Другое дело — магазин. Есть торговый зал, где продавцы общаются с покупателями. А есть служебные помещения, где совершенно другие люди занимаются оформлением и обеспечением торговли. А есть еще и складские помещения. Чувствуете разницу?

CMS-сайт примерно так и устроен. Есть лицевые страницы, которые видит посетитель сайта. А есть и особые, редакторские страницы, доступ к которым получают те, кто наполняет его содержимым. Они совсем не обязаны быть программистами — «движок» затем и создан, чтобы автоматизировать и упростить их работу.

Ну хорошо, скажете вы, внимательно проследив за аналогией с магазином. Доставку и продажу мы разделили. Это понятно. А как насчет упомянутого склада? Вот! Тут-то мы подходим к сути данной статьи. Все правильно. Допустим, мы организовали регистрацию пользователей — теперь нужно где-то хранить их имена, пароли и уровни доступа. Завели на сайте интернет-магазин — понадобится хранилище информации о товарах, ценах, заказах и т.п. Для этого-то и нужна база данных на сайте.

Мне доводилось встречать выложенные в интернете бесплатные «движки», авторы которых заявляли в числе достоинств тот факт, что их движок обходится без MySQL. На практике это всегда означало, что авторы в качестве базы данных использовали текстовый файл, где данные разделяются запятыми. Это хорошо тем, что от хостинга не требуется поддержки баз данных. Ну допустим. А чем это плохо? Пока база данных мала, никаких особых проблем не возникнет. Но если она состоит из десятков тысяч записей о товарах, да еще и хранит разного рода связанную с ними информацию (о заказах, например), то разницу вы почувствуете очень быстро.

Самая большая, но далеко не единственная проблема — скорость отбора нужной информации. Чтобы найти нужную строку в таком текстовом файле, его приходится всегда просматривать от начала до нужного места. Никакой возможности быстрого позиционирования там не существует. Есть и другие недостатки такого подхода — они станут ясны при перечислении достоинств того типа хранения данных, который можно встретить в профессиональных «движках». Это основанные на SQL реляционные базы данных.

Из них наиболее популярны в интернете MySQL и PostgresSQL. Кстати, первая начинала свой путь с довольно простой и бесхитростной SQL-системы, но с каждой версией она все больше наращивала возможности, и место предельно упрощенной SQL-системы постепенно заняла SQLite.

Реляционные базы данных

Само понятие реляционный (англ. relation — отношение) связано с разработками известного английского специалиста в области систем баз данных Эдгара Кодда (Edgar Codd), сотрудника фирмы IBM. В 1970 году им был создан формальный аппарат реляционной алгебры для обработки данных. Позже он сформулировал 12 правил, которым должна соответствовать любая система по управлению реляционными базами данных (RDBMS — Relation Database Management System).

Хотя язык SQL создавался с целью воплотить идеи Эдгара Кодда в жизнь, сам Кодд не признал SQL, как и ряд других СУБД (систем по управлению баз данных), в качестве истинно реляционных. Это и стало причиной, побудившей его к публикации своих знаменитых «12 правил». Но поскольку и сам теоретик, и практики, воплощавшие его мечту, работали в одной и той же фирме, это привело к уходу Кодда из IBM. Вместе с рядом единомышленников, в том числе известным теоретиком в той же области Кристофером Дейтом, он основал собственную консалтинговую компанию.

Особенности реляционных баз

Основные особенности реляционных баз можно сформулировать так:

Все данные представлены в виде набора простых таблиц (двумерных массивов), разбитых на строки и столбцы, на пересечении которых расположены данные. У каждого столбца есть имя, уникальное в пределах таблицы, причем все значения в одном столбце — однородны, т.е. имеют один тип. Каждая строка имеет одно или несколько полей, набор значений в которых уникален в пределах таблицы. Этот набор называется первичным ключом (primary key) и служит для идентификации строки. Этот принцип не допускает, в частности, хранение в таблице совершенно одинаковых строк. Имя таблицы, имя столбца и первичный ключ однозначно определяют хранимый элемент данных. Строки в реляционной базе данных не упорядочены. Упорядочивание производится в момент формирования ответа на запрос. Запросы к базе данных возвращают результат в виде таблиц, которые также могут выступать как объект для новых запросов.

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

Справочник стран COUNTRIES
ID	NAME
1	Россия
2	Франция
3	Марокко
4	Япония

В таблице COUNTRIES всего два столбца:

ID — код страны; NAME — ее название.

Столбец ID служит первичным ключом таблицы, а столбец NAME содержит ту полезную информацию, которую мы и будем стремиться извлекать запросами. Все данные столбца ID — целочисленны, столбца NAME — содержат текстовую информацию.

Отношения между таблицами

Чтобы база данных стала реляционной, одних данных мало. Между ними нужны еще и связи (те самые relations, от которых и пошло слово «реляционный»).

Для связи между таблицами служит так называемый внешний ключ (foreign key). Название довольно точно выражает его суть. Если в таблице A есть столбец для хранения первичного ключа таблицы B, то такой столбец и называется внешним ключом. Первичные и внешние ключи устанавливают связи между таблицами, превращая набор таблиц в цельную конструкцию — реляционную базу данных.

Приведу пример. Допустим, мы создали еще одну простую таблицу — справочник товаров. Назовем ее GOODS.

Товарный справочник GOODS
ID	NAME	PRICE	UNIT	COUNTRY
1	Яблоки	50.00	кг	Россия
2	Груши	60.40	кг	Франция
3	Апельсины	40.00	кг	Марокко
4	Макароны	21.00	шт	Франция
5	Кефир	25.30	шт	Россия
6	Молоко	30.50	шт	Россия

Ее колонки: ID — первичный ключ, NAME — название товара, PRICE — его цена, UNIT — краткое название единицы измерения, COUNTRY — название страны-производителя.

Хорошо ли построена такая таблица? Вроде бы всем упоминавшимся выше принципам она удовлетворяет: уникальные имена столбцов с однородными данными, строки с уникальным первичным ключом. Казалось бы, все на месте. Тем не менее построена она непрофессионально. Здесь мы подходим к принципам, о которых я еще не упоминал, — к понятию о нормализации таблиц. Суть в том, чтобы всюду, где только можно, избегать избыточности в хранении данных путем выделения их в отдельные таблицы.

Посмотрим на нашу таблицу GOODS. Чем она плоха? Представьте себе, что завтра придется изменить название какой-нибудь страны. Такое случается часто. Бирма когда-то меняла свое название на Мьянму, Польша — на Польскую Республику. Хочется ли вам менять огромное количество строк во всех таблицах, где эти страны упоминаются? Представьте также, что вас попросят отобрать запросом весь штучный товар. Можете ли вы быть уверены в том, что оператор всюду набил эту аббревиатуру правильно и одинаково? Скорее всего, окажется, что в таблице встречаются все мыслимые вариации: «шт», «Шт», «шт.», «штук» и «штуки».

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

Справочник единиц измерения UNITS
ID	NAME	SHORT_NAME
1	Штуки	шт
2	Килограммы	кг

Сам справочник товаров GOODS будет теперь выглядеть совершенно по-другому (см. таблицу).

Товарный справочник GOODS после нормализации
ID	NAME	PRICE	UNIT_ID	COUNTRY_ID
1	Яблоки	50.00	2	1
2	Груши	60.40	2	2
3	Апельсины	40.00	2	3
4	Макароны	21.00	1	2
5	Кефир	25.30	1	1
6	Молоко	30.50	1	1

Что изменилось? Вместо столбцов с названиями единиц измерения и стран появились столбцы UNIT_ID и COUNTRY_ID с кодами, отсылающими нас к другим таблицам. Это и есть внешние ключи. Что означает значение 2 в столбце UNIT_ID? Оно означает, что интересующая нас информация по единице измерения находится той строке таблицы UNITS, где ID = 2. Достаточно заглянуть в этот справочник, чтобы убедиться, что называется эта единица полностью «штуки», а кратко — «шт».

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

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

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

Язык запросов. Мини-учебник по SQL

Что такое SQL?

SQL — это самый распространенный язык запросов к базам данных. Расшифровывается аббревиатура так: Structured Query Language — «язык структурированных запросов».

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

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

Собственно, именно поэтому базы данных профессионально сделанных сайтов, как правило, реляционны и SQL-управляемы.

Строим запросы

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

Временно оставив в стороне формирование структуры таблиц, поговорим об операциях с данными. Начнем с запросов. В основе запроса лежит команда SELECT. Ее задача — взять исходные данные таблиц и на основании запроса пользователя построить временную «отчетную» таблицу, которую и вернуть в виде результата. Очевидно, что запросы ничего не меняют в базе данных. Их задача — извлекать данные в указанном виде. Итак, приступим:

SELECT * FROM goods ORDER BY name

Этот запрос извлечет все данные из таблицы GOODS, сортированные по названиям товаров. Все — потому что после ключевого слова SELECT стоит «звездочка»: она дословно означает «все колонки».

Порядок сортировки (в нашем случае — «name»), вообще говоря, может состоять из нескольких полей, перечисленных через запятую. В этом случае сначала выборка сортируется по первой указанной колонке, а те значения, которые оказались в ней одинаковыми, дополнительно сортируются по второй, и так далее.

Ограничиваем набор столбцов

SELECT name, price FROM goods

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

Результат выборки
NAME	PRICE
Яблоки	50.00
Груши	60.40
Апельсины	40.00
Макароны	21.00
Кефир	25.30
Молоко	30.50

Задаем дополнительное условие выбора

SELECT name, price FROM goods
WHERE price<30 ORDER BY price DESC

Этот запрос извлечет названия и цены товаров для всех записей, где цена меньше 30, и отсортирует их в порядке убывания цен. Ключевое слово WHERE служит для ограничения выборки по строкам, а ключевое слово DESC (descending) указывает сортировать не по возрастанию (как обычно), а по убыванию. Результат опять же можно посмотреть в виде таблицы.

Результат выборки
NAME	PRICE
Кефир	25.30
Макароны	21.00

Связываем таблицы

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

SELECT goods.id, goods.name, goods.price,
units.name AS unit, countries.name AS country
FROM units, countries, goods
WHERE units.id= goods.unit_id AND countries.id= goods.country_id
ORDER BY goods.id

Рассмотрим этот запрос подробнее. В нем фигурирует уже не одна, а три таблицы, связанные через внешние и первичные ключи. Две связи, как видите, прописаны сразу после ключевого слова WHERE.

Из таблиц UNITS и COUNTRIES отобраны строки, первичные ключи которых равны значениям внешних ключей UNIT_ID и COUNTRY_ID. Чтобы уточнить, из какой таблицы мы хотим взять поля ID и NAME (ведь такие названия есть в нескольких таблицах), мы через точку приписываем имя таблицы к их названиям.

Для удобства можно временно назначить таблицам более короткие псевдонимы — это делается в секции FROM, через пробел сразу после названий таблиц. Назначим для UNITS, COUNTRIES и GOODS в качестве псевдонимов буквы U, C и G, соответственно:

SELECT g.id, g.name, g.price, u.name AS unit, c.name AS country
FROM units u, countries c, goods g
WHERE u.id=g.unit_id AND c.id=g.country_id
ORDER BY g.id

Обратите внимание, что и в выходной таблице некоторые столбцы тоже переименованы, заменены на псевдонимы. Это особенно актуально, когда в одном запросе встречаются одинаковые имена столбцов у разных таблиц (в данном случае — NAME). Ключевое, хотя и необязательное, слово AS служит как раз для этого.

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

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

SELECT g.id, g.name, g.price, u.name AS unit, c.name AS country
FROM goods g
LEFT JOIN units u ON u.id=g.unit_id
LEFT OUTER JOIN countries c ON c.id=g.country_id
ORDER BY g.id

В этом запросе явно обозначена главная таблица — GOODS. Также здесь обеспечено «левое присоединение» вспомогательных таблиц через внешние ключи к основной таблице (LEFT JOIN) и внешнее (OUTER) присоединение таблицы стран.

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

Считаем строки

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

SELECT COUNT(*) AS cnt FROM goods

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

Результат выборки

CNT

6

Вроде бы мы получили на выходе число. Но на самом деле это просто вырожденная таблица из одной строки и одного столбца. Усложним задачу:

SELECT MAX(price) AS max_price, MIN(price) AS min_price
FROM goods WHERE unit_id=2

Такой запрос вернет нам максимальную и минимальную цены, встречающиеся у весовых товаров (напомню, что первичный ключ 2 для весовых товаров в нашей базе данных соответствует килограммам). Результат будет выглядеть как таблица из одной строки и двух столбцов. Называются столбцы, как мы и указали: MAX_PRICE и MIN_PRICE, а в двух ячейках размещаются вычисленные значения.

Более сложные агрегирующие запросы

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

SELECT country_id, MAX(price) AS max_price, MIN(price) AS min_price
FROM goods GROUP BY country_id

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

Результат выборки
COUNTRY_ID	MAX_PRICE	MIN_PRICE
1	50.00	25.30
2	60.40	21.00
3	40.00	40.00

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

SELECT c.name AS country, a.max_price, a.min_price
FROM countries c,
(SELECT country_id, MAX(price) AS max_price, MIN(price) AS min_price
FROM goods GROUP BY country_id) a
WHERE c.id=a.country_id

Наш предыдущий запрос включен сюда в скобках как самая обычная таблица с псевдонимом А (вот и еще одна полезная функция псевдонимов).

Результат выборки
COUNTRY	MAX_PRICE	MIN_PRICE
Россия	50.00	25.30
Франция	60.40	21.00
Марокко	40.00	40.00

Как вносить изменения в таблицы?

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

Управление данными в SQL осуществляется тремя основными командами: INSERT — для добавления новых строк, UPDATE — для их редактирования, DELETE — для удаления.

Добавим в таблицу COUNTRIES новую страну:

INSERT INTO countries (id,name) VALUES (4,"Бирма")

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

После этого мы узнаем, что страна эта давно уже сменила название на Мьянму и спешно исправляем оплошность:

UPDATE countries SET name=«Мьянма» WHERE id=4

Выяснив, что товары из Мьянмы в ближайшее время вноситься в наш справочник товаров не будут, мы решаем удалить эту строку из справочника. Делаем это так:

DELETE FROM countries WHERE id=4

Хочу обратить внимание, что блок WHERE в командах UPDATE и DELETE может воздействовать не только на одну строку, но и на любую их совокупность, заданную условием, а его отсутствие оказывает действие на всю таблицу. Для того чтобы удалить из таблицы GOODS все весовые товары, мы можем выполнить команду:

DELETE FROM goods WHERE unit_id=2

А для того чтобы очистить всю эту таблицу — команду:

DELETE FROM goods

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

Создаем базу данных

Управление базами данных как объектами

Будем считать, что наша небольшая экскурсия по запросам и командам SQL со стороны «торгового зала» завершена. Заглянем теперь в его «служебные помещения» и познакомимся с тем, как создается сама база данных. Эта часть языка SQL не столь стандартизирована и сильно отличается в различных реализациях. Поэтому в дальнейших примерах я буду придерживаться синтаксиса, принятого в самой популярной на веб-серверах системе — MySQL.

MySQL — продукт шведской компании MySQL AB. Ее основатели — Дэвид Аксмарк, Аллан Ларсон и Майкл Видениус (последний больше известен по прозвищу — Монти). По одной из версий, первая часть названия продукта (My) — не что иное, как англизированная запись имени дочери М. Видениуса. Однако точно за происхождение названия сегодня не могут поручиться даже отцы-создатели. Существует версия, по которой «my» — это префикс, с которого начинались названия рабочих каталогов на их компьютерах.

Из всех команд чаще всего нам будут нужны три: CREATE (создать), ALTER (изменить) и DROP (уничтожить).

Чтобы создать новую базу данных с названием, ну скажем, OUR_SHOP, следует выполнить команду:

CREATE DATABASE our_shop

Еще лучше сразу при ее создании установить нужную кодировку (ведь по умолчанию в MySQL используется latin1). В итоге команда будет выглядеть так.

CREATE DATABASE our_shop CHARACTER SET cp1251

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

ALTER DATABASE our_shop CHARACTER SET cp1251

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

DROP DATABASE our_shop

Управление таблицами

Чтобы создать таблицу GOODS, на которой мы отрабатывали манипуляции с данными, потребуется составить команду примерно такого вида:

CREATE TABLE goods (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL UNIQUE,
price DECIMAL(10,2) NOT NULL,
unit_id INT DEFAULT 1,
country_id INT )

Разберем эту команду подробнее. Тип INT устанавливается для столбцов с целочисленными данными, тип VARCHAR(100) обеспечивает хранение строк с длиной не более 100 символов, DECIMAL(10,2) соответствует действительным числам с не более чем десятью знаками и точностью в два знака после запятой.

Столбец ID объявлен первичным ключом (PRIMARY KEY).

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

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

Команда DEFAULT задает значение по умолчанию — то, которое будет записываться в базу при добавлении новой строки, если не указано иное. В нашем случае она обеспечивает автоматическое объявление товара штучным (код = 1) в случае, если при добавлении новых строк не будет указан другой код.

Признак UNIQUE обеспечивает уникальность значений в колонке (в нашем случае — уникальность названий товаров).

Если в будущем вы захотите перенастроить объявленные командой CREATE столбцы таблицы, сделать это можно командой ALTER. Например, таблицу GOODS можно нарастить строчной колонкой REMARK (подкоманда ADD):

ALTER TABLE goods ADD remark VARCHAR(50)

Поработав с ней немного и убедившись, что 50 символов для примечания явно недостаточно, увеличиваем максимальный размер строки до 250 (блок CHANGE):

ALTER TABLE goods CHANGE remark remark VARCHAR(250)

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

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

ALTER TABLE goods DROP remark

Удалить таблицу целиком можно командой DROP:

DROP TABLE goods

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

Индексы и индексация таблиц

Представьте себе, что ваш приятель загадал число между 1 и 1000 и просит вас угадать его за минимальное число попыток, сообщая лишь о том, в большую или меньшую сторону вы ошиблись. Как вы поступите? Очевидно, предложите при первой попытке версию 500 (то есть начнете с середины). Если он ответит: «меньше», — предложите 250. Если «больше» — 750. Так, разбивая интервалы пополам, вы уложитесь в 10 попыток (ведь 210 > 103). Если бы приятель загадал число в пределах миллиарда, то количество попыток уложилось бы в 30 (230 > 109).

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

Как это делается практически? Поясню на примерах. Допустим, вас часто просят отобрать информацию о товарах российского производства. Чтобы по колонке COUNTRY_ID таблицы GOODS фильтрация производилась быстрее, создадим по ней индекс с именем IDX_GOODS_COUNTRY:

CREATE INDEX idx_goods_country ON goods(country_id)

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

DROP INDEX idx_goods_country

Транзакции

Чтобы вы могли наглядно представить себе, что такое транзакция, и понять, зачем она нужна, приведу один простой пример. Представьте себе, что со счета Иванова на счет Петрова переводится сумма в 1000 рублей. Счета и того, и другого находятся в одной и той же базе данных. Как осуществляется перевод? Одна команда UPDATE уменьшает счет Иванова на 1000 руб., другая — на ту же сумму увеличивает счет Петрова.

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

В англоязычной литературе принято набор основных свойств транзакций обозначать мнемонической аббревиатурой ACID (в переводе — «кислота»). Буквами этого слова закодированы четыре свойства: Atomicity — неделимость, Consistency — согласованность, Isolation — изоляция, Durability — устойчивость). Транзакция неделима в том смысле, что представляет собой единое целое и является минимальным блоком алгоритма. Она согласованна, потому что не нарушает отношения между элементами данных или целостность базы данных даже при одновременной работе многих пользователей. Транзакция всегда изолирована, поскольку ее результаты не зависят от предыдущих или последующих транзакций. И наконец, устойчивость означает, что если транзакция завершена (зафиксирована), то внесенные ею изменения гарантированно сохранятся в базе данных.

Для объявления начала транзакции в MySQL используется стартовая команда:

BEGIN TRANSACTION

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

Успешное выполнение всех операций завершается командой фиксации:

COMMIT

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

Для отката изменений, сделанных в ходе выполнения транзакции, используется команда:

ROLLBACK

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

В таком режиме работает MySQL версий 3 и ниже или при установленном значении системной переменной AUTOCOMMIT=1. В режиме выключенной автофиксации (AUTOCOMMIT=0) каждое изменение в базе данных следует фиксировать явным образом (завершать командой COMMIT). Кому-то это может показаться утомительным, но именно такой режим обеспечивает целостную работу с данными, так как позволяет использовать всю мощь механизма транзакций. Именно в этом режиме работы мы можем переложить деньги Иванова на счет Петрова, не опасаясь, что они зависнут между счетами.

Как произносится SQL?

Одни произносят эту аббревиатуру как «эскуэль», другие (особенно американцы) — как «сиквел». Последнее произношение может показаться странным, если не знать историю создания языка. А ведет эта история свое начало от той же самой фундаментальной работы Кодда. Для реализации его модели группа разработчиков из IBM предложила систему управления базами данных «System R» и язык запросов для обслуживания этой системы. Язык сначала назывался не SQL, а SEQUEL (Structured English Query Language). Такое название — не случайно. Начальный замысел создателей состоял в том, чтобы создать простой язык, максимально приближенный к бытовому английскому. Это и сейчас ощущается в синтаксисе SQL. Но, как и любой другой язык, SQL рос, развивался, ветвился на множество диалектов и, разумеется, усложнялся. Этому противостояло другое начало — консервативное. Принимались стандарты языка (ANSI — c 1986 г., OSI — с 1987 г.). Так или иначе, идея о том, чтобы любая англоязычная кухарка могла управлять базами данных, оказалась невоплощенной. В современных реализациях языка (особенно таких, как SQL и PL/SQL для Oracle 10g) могут разобраться лишь профессиональные программисты. Тем не менее основные конструкции языка довольно просты и не требуют особой подготовки.

Что дальше?

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