Принципы использования баз данных 23

SELECT *

FROM car WHERE Make = Ford

AND WholesalePrice < 9000.00;

Звездочка (*) означает выборку всех полей Записи. Кен рассчитывает заработать на продаже каждого автомобиля не менее 1000 долларов, поэтому в запросе фигурирует откорректированная цена.

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

UPDATE car SET Color WHERE VIN =

= White

10203040506070809;

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

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

DELETE

FROM car

WHERE VIN IN (12345678901234567, 10 203040506070809);

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

Абстракция

Работая с таблицей саr, Кенвскоре обнаруживает проблему. Дело в Том, что в поле Color хранится текстовое название цвета, вводимое пользователем. Во-перв1х, утомительно вводить все время одни и те же названия, во-вторых, легко можно допустить ошибку в написании. Нужно придумать лучшую организацию таблицы.

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

номер. Для этого нужно создать еще одну таблицу с двумя полями: в одном хранится код цвета, во втором - его название. В листинге 1.7 показана инструкция, создающая такую таблицу.

CREATE TABLE car color (

ColorCode INT(6) NOT NULL AUTO INCREMENT, Name VARCHAR(16), PRIMARY KEY(ColorCode)

Номер цвета является целым числом, а столбец ColorCode - первичным ключом таблицы. Кен не хочет самостоятельно назначать эти номера, поэтому он возлагает данную задачу на СУБД, помечая поле ColorCode ключевым словом AUTOINCREMENT. При добавлении очередной записи в таблицу car color MySQL автоматически запишет в это поленомер, на единицу больший предыдущего.

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

SELECT DISTINCT Color FROM car;

Ключевое слово DISTINCT заставляет MySQL удалить из таблицы результатов дублирующиеся значения. Теперь можно ввести серию запросов, вставляющих записи в таблицу car color по одной за раз. Но это очень неуклюжий подход. Более эффективное решение- объединить инструкции INSERT и SELECT, создавподчиненный запрос. Результате! инструкции SELECT будут непосредственно вставляться в таблицу car color (листинг 1.9).

INSERT INTO car color (Name)

SELECT DISTINCT Color

FROM car;

После названия таблицы car color в круглых скобках указан столбец, куда заносятся данные (Name). Обычно в пропущенные поля вставляется специальная константа NULL, обозначающая отсутствующее значение. Но поле ColorCode помечено спецификатором NOT NULL, т.е. значения NULL в нем недопустимы. С другой стороны, в определении поля стоит ключевое слово AUTOINCREMENT, а это означает, что в поле будут автоматически вставляться целые числа: в первой записи это будет число 1, во второй - 2 и т.д.

Принципы использования баз данных 25

На следующем этапе требуется перестроить таблицу саr, модифицировав определение столбца Color (листинг 1.10).

ALTER TABLE car

CHANGE Color ColorCode INT(6) NOT NULL;

К сожалению, подобная модификация означает удаление всей информации из столбца Color. MySQL поменяет тип столбца, подставив во все поля значение 0. Далее Кену придется ввести последовательность инструкций UPDATE, чтобы создать в таблице саr правильные ссылки на новую таблицу car color.

После обновления таблицы саr в ней будут храниться не названия цветов, а их коды (в поле ColorCode). Белый цвет будет иметь, к примеру, код 1, а темно-синий цвет - код 13. Запомнить соответствие между номерами и названиями цветов едва ли возможно. Необходимо, чтобы при выполнении запросов к таблице саr отображались именно названия цветов, а не их коды.

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

SELECT car.VIN, carcolor.Name FROM car LEFT JOIN car color

ON car.ColorCode = carcolor.ColorCode WHERE car.Make = Plymouth;

Возможные результаты такого запроса представлены в листинге 1.12.

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