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

22 Глава 1. Введение в MySQL

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

Запрос, показанный в листинге 1.2, представляет собой инструкцию SELECT. Она отбирает записи, соответствующие следующему критерию: поле Color записи содер жит строку 'White'. Но возвращается не вся запись целиком, а лишь четыре поля: VIN,Make,Model и ModelYear.

SELECT VIN, Make, Model, ModelYear

FROM car

WHERE Color = 'White';

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

Показанный выше запрос можно модифицировать как угодно, но результат пока что будет одним и тем же. СУБД ненайдет ни одной записи, так как таблица пуста! Следовательно, нужно что то добавить в нее. Для этого предназначена инструкция INSERT. Данная инструкция относится к семейству запросов на изменение и офици альноназываетсязапросомнадобавлениезаписей.

В листинге 1.3 показана инструкция, которая добавляет запись в таблицу саr. По рядок значений соответствует порядку столбцов в таблице.

INSERT INTO car VALUES ( '12345678901234567', 'Plymouth', 'Roadrunner',

1969,

5500.00,

'Blue',

148123,

'Unrestored'

);

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

Имея заполненную таблицу, Кен может производить в ней более предметный по иск. Например, если клиент спросит, есть ли в продаже автомобили "Форд" по цене меньше $10000, Кен сможет проверить это с помощью показанного ниже запроса (листинг 1.4).

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

SELECT *

FROM car

WHERE Make = 'Ford'

AND WholesalePrice < 9000.00;

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

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

UPDATE car

SET Color = 'White'

WHERE VIN = '10203040506070809';

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

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

DELETE FROM car

WHERE VIN IN ('12345678901234567', '10203040506070809');

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

Абстракция

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

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

24 Глава 1. Введение в MySQL

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

CREATE TABLE car_color (

ColorCode INT(6) NOT NULL AUTO_INCREMENT,

NameVARCHAR(16),

PRIMARY KEY(ColorCode)

);

Номер цвета является целым числом, а столбец ColorCode — первичным ключом таблицы. Кен не хочет самостоятельно назначать эти номера, поэтому он возлагает данную задачу на СУБД, помечая поле ColorCode ключевым словом AUTO_INCREMENT. При добавлении очередной записи в таблицу 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 в нем недопустимы. С другой стороны, в определении поля стоит ключевое слово AUTO_INCREMENT, а это означает, что в поле будут автоматически вставляться целые числа: в первой записи это будет число 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, car_color.Name

FROM car LEFT JOIN car_color

ON car.ColorCode = car_color.ColorCode

WHERE car.Make = 'Plymouth';

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

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

26Глава 1. В ведение в MySQL

1)Каждая запись таблицы саr сравнивается с каждой записью таблицы car_color.

2)Если поле ColorCode таблицы саr совпадает с полем ColorCode таблицы car_color (объединение по равенству), создается итоговая запись, состоящая из полей первой таблицы, к которым присоединены поля второй таблицы.

3)Для каждой записи таблицы саr, полю ColorCode которой не найдено соот ветствие в таблице car_color, создается итоговая запись, состоящая из полей первой таблицы, к которым вместо полей второй таблицы присоединены зна чения NULL (левое объединение).

4) Из объединенной таблицы отбираются записи, соответствующие условию

WHERE.

5) В результаты запроса включаются поля, заданные в предложении SELECT.

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

В листинге 1.11 несложно заметить одну особенность многотабличных запросов: при ссылке на поля таблиц следует избегать неоднозначности. Например, обе таблицы содержат поле ColorCode. Чтобы явно указать, к какой таблице относится поле, нужно задать полное имя поля. Синтаксис полного имени таков: имя_ таблицы..имя_поля.

Преимущества баз данных

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

Традиционные подходы к управлению данными

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

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

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

Преимущества баз данных

27

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

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

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

Базы данных были разработаны как раз с целью решения проблем, возникающих при создании специализированных подпрограмм для обработки данных и построе нияотчетов.

Унификация средств доступа

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

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

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

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

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

28 Глава 1. Введение в MySQL

Повышение производительности

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

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

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

Централизация средств управления данными ведет также к усилению их безопас ности. СУБД может назначать аутентифицированным пользователям определенные права. Она, к тому же, способна выполнять резервное копирование данных, не пре рывая работу пользователей.

Усиление целостности

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

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

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

Недостатки баз данных

29

Недостатки баз данных

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

Определенную сложность представляют различия, связанные с реализацией кон кретных СУБД. Существуют международные стандарты языка SQL, но мало какой разработчик СУБД устоит перед искушением дополнить стандарт собственными рас ширениями. В результате перенос базы данных из одной СУБД в другую часто оказы вается затруднительным. Подробнее об этом рассказывается в главе 28, "Перенос данных в разные СУБД". Если заранее известно о том, что базу данных придется пе реносить в другую СУБД, постарайтесь не использовать функциональные возможно сти, специфичные для MySQL. Это существенно упростит процесс переноса.

Зачем нужна программа MySQL

Прежде чем рассматривать особенности использования программы MySQL, необ ходимо понять, почему есть смысл работать именно с ней, а не сOracle, к примеру, или PostgreSQL, или любой другой известной СУБД. Многие находят программу MySQL особенно привлекательной из за того, что ее легко изучить. Она также оказы вается достаточно гибкой в самых разных ситуациях. Там, где другие СУБД навязы вают одну модель данных, MySQL предлагает варианты.

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

MySQL — это быстрая СУБД. На Web узле www.mysql.com приведены результаты тес тов, в которых сравнивается производительность MySQL и других реляционных СУБД. Во многих случаях разница существенна, а повышенная производительность ценится всегда.

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

Команда разработчиков MySQL старается реализовывать все, что предписывается стандартом ANSI. Но если та или иная функция пользователю не нужна, он может от ключить ее на этапе компиляции. Это, опять таки, свидетельствует о гибкости про граммы.

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

30 Глава 1. Введение в MySQL

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

Стоимость приобретения и эксплуатации MySQL очень невелика, как и у всякого открытого ПО. Программу можно бесплатно загрузить с нескольких Web узлов, к тому же она входит во многие дистрибутивы Linux. Существуют многочисленные теле конференции, посвященные вопросам поддержки пользователей MySQL. Сотрудники компании MySQL AB постоянно проверяют сообщения, поступающие в список рас сылки [e-mail], и бесплатно устраняют возникающие проблемы. Кроме то го, за умеренную плату компания оказывает гарантированную, персональную под держку всем желающим.

MySQL — надежнаяСУБД. Так как ее исходные коды доступны для всеобщего обо зрения, пользователи регулярно находят и исправляют ошибки по мере их появления.

Другим следствием открытости кода стала доступность MySQL для множества платформ. Эта программа может работать в большинстве версий UNIX, Linux, Win dows и даже в менее популярных операционных системах, например в OS/2.

История MySQL

Однажды Микаэлю "Монти" Видениусу пришла в голову мысль добавить SQL модуль в качестве интерфейса к своей старой базе данных, которую он на протяже нии 15 лет вел в компании ТСХ DataKonsult AB. Он решил использовать для этой цели открытую реляционную СУБД mSQL, но ему не удалось заинтересовать ее автора, Дэ вида Хьюза (David Hughes). Тогда Монти начал создавать собственную реляционную СУБД, клиентский интерфейс которой был смоделирован на основе API функций mSQL, чтобы в новую систему можно было перенести приложения, написанные для mSQL. В планы Монти никогда не входило реализовывать стандарт языка SQL цели ком, но после того как он поделился исходными кодами программы с сообществом разработчиков, ответная реакция оказалась ошеломляющей.

Первая версия MySQL, тогда еще интерфейса к старой базе данных Монти, была закончена в мае 1995 года. На ее написание ушло три месяца. С этого момента про грамма MySQL начала свой путь к тому, чтобы стать самой популярной СУБД, исполь зуемой в Internet.

Компанию ТСХ DataKonsult AB впоследствии переименовали в MySQL AB, и с его дня Монти является руководителем ее технического отдела. Эта шведская компания целиком посвятила себя разработке и поддержке программы MySQL. С самого начала компания стала прибыльной за счет оказания платной поддержки, предоставления платных консультаций и продажи лицензий на встроенную версию MySQL. Это пре красный пример того, что модель распространения программ с открытыми кодами является вполне жизнеспособной.

В июне 2000 г. программа MySQL стала доступна на условиях общей лицензии GNU (GNU General Public License, GPL). Это дает возможность каждому пользовате лю улучшать программу и передавать ее своим коллегам без каких либо лицензионных отчислений.