MySQL. Библиотека профессионала - Аткинсон Л

72 Глава 6. Язык SQL

Определение данных

Для определения структуры базы данных предназначенакоманда CREATE. Если ба за данных еще не была создана посредством утилиты mysqladmin, то этоможно сде лать с помощью инструкции CREATE DATABASE. Единственным ее аргументом явля ется имя базы данных (она создается пустой). Естественно, если база с таким именем уже существует, будет выдано сообщение об ошибке. В листинге 6.1 демонстрируется создание базы store.

CREATE DATABASE store

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

Таблица создается с помощью инструкции CREATE TABLE. Ей нужно указать не только имя таблицы, но и ее полно е определение, состоящее из определений отдель ных полей.

Имя таблицы может быть полным либо неполным. К полномуимени добавляется имя базы данных, например store.item. Точно так же полное имя столбца включает в себя имена базы данных и таблицы: store.item.price. Но гораздо удобнее назна чить стандартную базу данных, тогда отпадет необходимость в полных именах. Для этого предназначена инструкция USE. Имя базы данных можно задать и при вызове программы mysql. Подробнее об этом рассказывается в главе 14, "Утилиты команд ной строки".

Общий формат инструкции CREATE TABLE таков:

CREATE TABLE имя_таблицы

(определение столбца, . . . )

Определение столбца включает в себя имя столбца и спецификацию его типа. Таблица, создаваемая в листинге 6.2, содержит четыре столбца: ID, Name, Price и Description.

CREATE TABLE item (

ID INT(6) NOT NULL AUTO_INCREMENT,

Name CHAR(32) NOT NULL,

Price DECIMAL(4,2) NOT NULL,

Description CHAR(255) DEFAULT 'No Description',

PRIMARY KEY(ID),

KEY (Name)

Определение существующей таблицы можно узнать с помощью инструкции DESCRIBE (сокращенный вариант— DESC). В листинге 6.3 показан результат, выда ваемый этой инструкцией в случае таблицы item.

Аналогичную информацию выдает инструкция SHOW COLUMNS, общий формат ко торой таков:

SHOW COLUMNS

FROM имя_таблицы

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

Столбец ID созданной выше таблицы item содержит целые числа в диапазоне от О до 999999. Значения NULL в нем недопустимы, на что указывает спецификатор NOT NULL. Кроме того, столбец работает в режиме счетчика (флаг AUTO_INCREMENT). Это означает, что при вставке в таблицу новой строки значение ее столбца ID будет вы числено автоматически на основании значения в предыдущей строке. Нумерация на чинается с единицы. Это хороший способ присвоения записям уникальных иденти фикаторов.

У читателей может возникнуть вопрос: почему MySQL сообщает, что по умолча нию значение столбца ID равно NULL? Ведь в инструкции CREATE TABLE стандартное значение не было задано, более того, явно указано, что столбец не может содержать значения NULL. На самом деле значение по умолчанию есть у каждого столбца. Если оно не указано, MySQL сделает выбор самостоятельно. В случае столбцов счетчиков NULL — это хороший выбор, так как при вставке самой первой строки в столбец будет записано значение 1.

Столбец Name содержит строку из 32 х символов. У каждого элемента таблицы должно быть имя, поэтому столбец помечен спецификатором NOT NULL.

Столбец Price хранит значение стоимости в десятичном формате: четыре цифры до запятой и две — после. Тип с плавающей запятой в данном случае не подходит, по тому что он не обеспечивает точность вычислений.

Столбец Description представляет собойтекстовое поле максимальной длины: 255 символов. Он может принимать значения NULL, но благодаря наличию предло жения DEFAULT они будут преобразовываться в строку ' No Description '.

74Глава 6. Язык SQL

Вконце инструкции CREATE TABLE определяются первичный и вторичный клю чи. MySQL проследит, чтобы в столбец ID помещались уникальные значения, а кроме того, для столбцов ID и Name будут созданы индексы, что ускорит поиск в них.

На имена баз данных, таблиц и столбцов накладывается ряд ограничений. Если за глянуть "за кулисы", то окажется, что базы данных представляются в виде каталогов файловой системы сервера, а таблицы являются файлами этих каталогов. Из практи ческих соображений длина имен ограничена 64 мя символами. Если в операционной системе, где установлена СУБД MySQL, в именах файлов учитывается регистр, то имена баз данных и таблиц в MySQL тоже будут чувствительными к регистру. Имена

столбцов всегда не зависят от регистра.

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

Вставка записей

Инструкция INSERT добавляет строку в таблицу. Общий формат ее таков:

INSERT INTO имя_таблицы

VALUES (значение, ...)

Необходимо задать значения всех столбцов, причем в том порядке, в котором оп ределения столбцов указывались в инструкции CREATE TABLE. В главе 13, "Инструкции SQL", будут описаны разновидности инструкции INSERT, позволяющие указывать подмножество столбцов, а также вставлять в таблицу записи, возвращаемые подчиненной инструкцией SELECT.

Инструкция, показанная в листинге 6.4, создает новую строку в таблице item. В столбец ID будет записано ближайшее доступное число. Обратите внимание на то, что строковые значения берутся в одинарные кавычки.

INSERT INTO item VALUES( NULL,

'toothbrush',

1.25,

'A deluxe toothbrush'

)

Обновление записей

Для изменения полей существующих записей предназначена инструкция UPDATE, общий формат которой следующий:

UPDATE имя_таблицы

SET выражение, ...

WHERE условие_отбора

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

Предложение WHERE задает правило отбора обновляемых строк. Если оно отсутству ет, изменению подвергнутся все строки таблицы. Инструкция, показанная в листин ге 6.5, обновляет значения столбцов Name и Price элемента с идентификатором 1. Дру гие строки остаются нетронутыми.

UPDATE item

SET Name='Toothbrush', Price=1.15

WHERE ID=1

Условия отбора строк необходимо тщательно проверять, чтобы по ошибке не уда лить тысячу строк вместо одной. На всякий случай можно воспользоваться опциейsafe updates программы mysql, указав в ней максимальное число удаляемых строк. Но еще лучше перед выполнением инструкции UPDATE ввести эквивалентную инструкцию SELECT, чтобы проверить, какие записи соответствуют условию отбора.

Удаление записей

Инструкция DELETE удаляет строку из таблицы. Общий форматинструкции таков:

DELETE FROM имя_таблицы

WHERE условие_отбора

Если предложение WHERE отсутствует, будут удалены все строки таблицы. В лис

тинге 6.6 демонстрируется удаление отдельной строки.

DELETE FROM item

WHERE ID=16

Запросы

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

SELECT имя_столбца,...

FROM имя_таблицы, ...

WHERE условие_отбора

GROUP BY имя_столбца,...

ORDER BY имя_столбца, ...

LIMIT лимит

Объединения 77

Объединения

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

Для примера создадим вторую таблицу it em_option, которая будет участвовать в операциях объединения с таблицей item. В листинге 6.11 показаны инструкции, соз дающие обе таблицы и заносящие в них данные. В таблице item_option хранятся опи сания возможных вариантов изготовления товаров, перечисленных в таблице item.

CREATE TABLE item (

ID INT(6) NOT NULL AUTO_INCREMENT,

Name CHAR(32) NOT NULL,

Price DECIMAL(4,2) NOT NULL,

Description CHAR(255) DEFAULT 'No Description',

PRIMARY KEY(ID), KEY (Name)

);

INSERT INTO item VALUES (1, 'Toothbrush', 1.25, NULL); INSERT INTO item VALUES (2, 'Comb', 2.50, NULL); INSERT INTO item VALUES (3, 'Brush', 3.00, NULL); INSERT INTO item VALUES (4, 'Toothpaste', 0.75, NULL);

CREATE TABLE item_option (

ID INT(6) NOT NULL AUTO_INCREMENT,

Item INT(6) NOT NULL,

Name CHAR(32) NOT NULL,

PRIMARY KEY(ID),

FOREIGN KEY(Item) REFERENCES Item (ID)

) ;

INSERT INTO item_pption VALUES (1, 2, 'Red Plastic'); INSERT INTO item_option VALUES (2, 2, 'Blue Plastic1 );

Чтобы создать внутреннее объединение таблиц, достаточно указать их имена в предложении FROM инструкции SELECT. При отсутствии предложения WHERE полу чится самый общий вариант такого объединения — декартово произведение, в кото ром каждая строка первой таблицы объединена с каждой строкой второй таблицы. Если же задано условие отбора, то в результаты запроса попадут лишь те строки таб лицы произведения, в которых значения связанных столбцов удовлетворяют требуе мому критерию {связанным называетсястолбец, присутствующий в условии отбора).

78Глава 6. Язык SQL

Влистинге 6.12 выполняется объединение двух таблиц по равенству столбцов item. ID и item_option. Item, т.е. для каждой строки первой таблицы находится со ответствующий элемент второй таблицы. Из объединенной таблицы отбираются три столбца: с названием и ценой товара, а также с названием варианта его изготовления.

В листинге 6.12 содержатся примеры псевдонимов таблиц и столбцов. Третий столбец результатов запроса называется так же, как и первый, поэтому во избежание путаницы он переименуется в Option Name. На псевдонимы столбцов можно ссы латься в предложениях GROUP BY и ORDER BY, рассматриваемых ниже.

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

В листинге 6.13 показано то же самое внутреннее объединение, что и в листин ге 6.12, но на этот раз в предложении FROM присутствует операция INNER JOIN. В по добном случае условие отбора переносится из предложения WHERE в предложение ON.

SELECT i.Name, i.Price, o.Name AS 'Option Name'

FROM item i INNER JOIN item_option о

ON i.ID = o.Item

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

mysql> SELECT i.Name, i.Price, o.Name AS 'Option Name' > FROM item i LEFT JOIN item_option о

5 rows in set (0 . 00sec)

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

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

Предложение ORDER BY содержит имена столбцов, по которым осуществляется сортировка результатов запроса. На столбец можно сослаться по имени (краткому или полному), псевдониму или порядковому номеру в предложении SELECT (нумера ция столбцов начинается с единицы). В листинге 6.15 таблица результатов сортирует ся по названию товара.

SELECT i.Name, i.Price, o.Name AS 'Option Name'

FROM item i LEFT JOIN item_option о

ON i.ID = o.Item

ORDER BY i.Name

Предложение ORDER BY может содержать не одно имя, а целый список. Столбцы перечисляются в порядке убывания приоритетов. В первую очередь записи сортиру ются по столбцу, указанному первым. Если возникает "конфликт" (две или более строк имеют одинаковое значение в данном поле), проверяется второй столбец, за тем третий и т.д. По умолчанию сортировка всех столбцов ведется по возрастанию. Если нужно задать обратный порядок, укажите рядом с именем столбца ключевое слово DESC. В листинге 6.16 показан пример сортировки товаров по цене, начиная от самых дорогих.

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

В листинге 6.18 показан запрос, возвращающий описание товара, у которого наи большее число вариантов изготовления. Если таких записей окажется две или более, будет выбрана та из них, которая стоит первой в списке. Предложение LIMIT инст рукции SELECT является специфичной особенностью MySQL.

SELECT i.Name, i.Price, count(о.ID) AS 'Options'

FROM item i LEFT JOIN item__option о

ON i.ID =o.Item

GROUP BY i.Name, i.Price

ORDER BY Options DESC, Name

LIMIT 1

Изменение определения таблицы

Инструкция ALTER TABLE позволяет менять определение таблицы. Можно добав лять, удалять и модифицировать определения столбцов, а также добавлять и удалять индексы. Допускается переименование таблицы.

Инструкция, показанная в листинге 6.19, добавляет в таблицу item столбец Inventory, в которомбудет указываться количество товара на складе. После выпол нения запроса каждая запись таблицы будет содержать в поле Inventory значение 0.

ALTER TABLE item

ADD COLUMN Inventory INT(4) NOT NULL DEFAULT 0