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

62Глава 5. Реляционная модель

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

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

Пока что речь шла только о первичных и внешних ключах. Но помимо них есть еще несколько типов ключей.

Суперключ— это совокупность атрибутов, уникальным образом идентифицирую щих каждую запись. Например, в табл. 5.2 в качестве суперключа можно использовать объединение всех атрибутов или же, к примеру, столбцов "Фамилия" и "Дата рожде ния". А вот сочетание столбцов "Фамилия" и "Команда" не подойдет, так как в коман де "Oakland Athletics" есть два игрока однофамильца.

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

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

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

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

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

Отношения 63

Отношения

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

Отношение "один ко многим" (1:N) является естественным типом отношений в ре ляционной базе данных. Оно реализуется с помощью внешних ключей, рассмотрен ных выше. При отношении 1:N любой строке первой таблицы может соответствовать несколько записей второй таблицы. Если проанализировать связь в противополож ном отношении, то окажется, что строке второй таблицы соответствует всего одна запись первой таблицы.

Видеально спроектированной реляционной базе данных отношение "один к одно му" (1:1) не нужно. Если каждой строке одной таблицы соответствует одна строка дру гой таблицы, то это обычно свидетельствует о том, что обе таблицы нужно объеди нить в единое целое. Исключение из правила— необычный случай, когда число столбцов таблицы превышает предел, установленный в СУБД. В MySQL этот предел равен 3000, так что маловероятно, чтобы кому то пришло в голову его превысить. Есть СУБД, где предельное число столбцов гораздо меньше, например 250, но даже этого числа вполне достаточно для большинства приложений. В будущих версиях MySQL жесткий предел будет вообще снят, и создавать столбцы можно будет до тех пор, пока не закончится место на диске.

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

Вреляционной базе данных нельзя напрямую создать отношение "многие ко многим" (M:N). Его необходимо преобразовать в два отношения 1:N, устанавливаемых с проме жуточной таблицей. Например, бейсболист, особенно игрок внешнего поля ("аутфилд"), может занимать на поле более одной позиции. Если информацию обо всех занимаемых позициях хранить в общей таблице, то получится, что есть группа игроков

снесколькими позициями иесть позиции, занимаемые несколькими игроками.

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

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

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

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

Рис. 5.2. Проекция фамилий игроков

Операция пересечения выполняется над двумя таблицами идентичной структуры. В результате возвращаются только те записи, которые встречаются в обеих исходных таблицах. Предположим, нам необходимо узнать, какие игроки принимали участие в матче "Всех звезд" как в 1999, так и в 2000 году. Результат соответствующей операции пересечения представлен на рис. 5.3.

Рис. 5.З. Операция пересечения

Операция сложения также выполняется над двумя таблицами идентичной структу ры. При этом в результирующую таблицу попадают все записи исходных таблиц. На пример, с помощью данной операции можно получить объединенный список участ ников матчей "Всех звезд" в 1999 и 2000 году (рис. 5.4). Результирующая таблица будет содержать 16 строк, а не 18, поскольку два игрока принимали участие в обоих матчах и их имена повторяются.

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

66 Глава 5. Реляционная модель

Рис. 5.4. Операция сложения

Рис. 5.5. Операция вычитания

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

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

Рис. 5.6. Операция умножения

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

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

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

Рис. 5.7. Операция деления

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

68 Глава 5. Реляционная модель

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

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

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

Рассмотрим внутреннее объединение, схема которого представлена на рис. 5.8. Объединение выполняется по столбцу "Номер команды", присутствующему в обеих таблицах и включаемому в результаты запроса только один раз. Обратите внимание на третью строку правой таблицы. Для нее нет эквивалента в левой таблице, поэтому она не попадет в результаты запроса.

по столбцу "Номер команды"

Рис. 5.8. Внутреннееобъединение

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

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

Является ли MySQL настоящей реляционной СУБД 69

Рис. 5.9. Левое внешнее объединение

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

Полное внешнее объединение реализуется так, как если бы правое и левое объе динения выполнялись одновременно. Поддержка данного типа объединений отсутст вует в MySQL версии 3.23, но должна появиться в версии 4.1.

Является ли MySQL настоящей реляционнойСУБД

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

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

MySQL 3.23 не выполняет проверку внешних ключей. Не поддерживаются также некоторые реляционные операции, например полное внешнее объединение и деле ние. Тем не менее MySQL является настоящей реляционной СУБД.

ЯЗЫК SQL

В этой главе...

SQL — язык четвертого поколения Определение данных Вставка записей Обновление записей Удаление записей Запросы Объединения

Упорядочение результатов запроса Группировка результатов запроса Ограничение числа возвращаемых записей Изменение определения таблицы

QL— это язык взаимодействия с базами данных, применяемый в большинстве реляционных СУБД, включая MySQL. Основной стандарт языка был принят в 1992 г. и называется SQL2 или SQL 92. Продолжается также работа над наиболее

современным стандартом SQL3.

В MySQL команды SQL вводятся с помощью одного из клиентов, описанных в гла ве 3, "Взаимодействие с MySQL". В настоящей же главе будут рассмотрены основные возможности языка. Подробное знакомство со всеми поддерживаемыми инструкция ми SQL произойдет вглаве 13, "Инструкции SQL".

SQL — язык четвертого поколения

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

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

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

Часто для удобства восприятия инструкции SQL записывают в нескольких стро ках, что допускается синтаксическим анализатором. В программе mysql конец инст рукции помечается специальной командой. Проще всего ввести точку с запятой (;), но можно также на брать \д или до. Все эти команды не являются частью языка, они необходимы лишь интерпретатору mysql.