Введение в СУБД MySQL

Введение в СУБД MySQL

2-е издание, исправленное

Национальный Открытый Университет “ИНТУИТ”
2016

2

Введение в СУБД MySQL/ - М.: Национальный Открытый Университет “ИНТУИТ”, 2016

Курс посвящен системе управления базами данных MySQL. Рассматриваются основы MySQL:
запросы, модели баз данных, а также транзакции. На примерах рассмотрен весь спектр вопросов,
касающихся языковой структуры, допустимых типов столбцов, операторов, операций и функций, а
также существующих расширений MySQL.
Курс рассчитан на разработчиков Web-приложений и администраторов любой квалификации, а также
на студентов и преподавателей соответствующих дисциплин. Рассматриваются основы системы
MySQL и языка SQL: от моделей баз данных, до сложных запросов. Курс содержит множество
примеров: на практике рассмотрен весь спектр вопросов, касающихся языковой структуры,
допустимых типов столбцов, операторов, операций и функций, а также существующих расширений
MySQL. Кроме того, рассмотрены вопросы взаимодействия системы MySql с языками PHP и Perl.

(c) ООО “ИНТУИТ.РУ”, 2007-2016
(c) 2007-2016

3

Введение в MySQL

В этой лекции рассматриваются вводные понятия баз данных, их виды, и даётся обзор
основных характеристик MySQL.

Компьютерные системы хранения

В наши дни люди часто говорят о базах данных. Компьютеры составляют
неотъемлемую часть современного общества, поэтому нередко можно услышать фразы
вроде “Я поищу твою запись в базе данных”. И речь идет не о больших ящиках, где
хранятся груды папок, а о компьютерных системах, предназначенных для ускоренного
поиска информации.

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

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

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

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

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

Рис. 1.1.  Схема передачи данных в архитектуре “клиент/сервер”

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

4

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

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

Строки таблиц могут быть связаны друг с другом одним из трех способов. Простейшее
отношение — “один к одному”. В этом случае строка первой таблицы соответствует
одной единственной строке второй таблицы. На диаграммах такое отношение
выражается записью 1:1.

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

Наконец, при отношении “многие ко многим” строки первой таблицы могут быть
связаны с произвольным числом строк во второй таблице. Такое отношение
записывается как N:M.

СУБД

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

Подобная обработка данных осуществляется посредством языка четвертого поколения
(4GL), который поддерживает запросы, записываемые и исполняемые немедленно.
Данные быстро утрачивают свою актуальность, поэтому скорость доступа к ним важна.
Кроме того, программист должен иметь возможность формулировать новые запросы.
Они называются нерегламентированными (ad hoc), поскольку не хранятся в самой базе
данных и служат узкоспециализированным целям.

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

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

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

5

таблиц.

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

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

Концепции баз данных

Системы управления файлами

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

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

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

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

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

6

работает много служащих, поэтому его имя будет неизбежно повторяться. Если где-то
это имя будет записано неправильно, формально получится, что у служащего другой
начальник. При замене начальника его имя придется “вылавливать” по всей базе
данных.

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

Иерархические базы данных

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

На рис. 1.2 изображена простая иерархическая база данных, в которой фиксируется
деятельность независимого подрядчика. Корень дерева представляет собой запись о
клиенте. Ее потомками являются две записи о счет-фактурах и три записи об оплатах
счетов. Структура счета номер 17 уточняется в трех дочерних записях, у счета номер
23 одна такая запись.

Рис. 1.2.  Иерархическая база данных

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

7

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

Сетевые базы данных

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

Следуя спецификации CODASYL, сетевая модель поддерживает DDL (Data Definition
Language — язык определения данных) и DML (Data Manipulation Language — язык
обработки данных). Это специальные языки, предназначенные для определения
структуры базы данных и составления запросов. Несмотря на их наличие, программист
по-прежнему должен знать структуру базы данных.

В сетевой модели допускаются отношения “многие ко многим”, а записи не зависят
друг от друга. При удалении записи удаляются и все ее связи, но не сами связанные
записи.

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

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

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

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

В сравнении с рассмотренными выше моделями реляционная модель требует от СУБД

8

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

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

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

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

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

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

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

Объектно-ориентированные базы данных

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

9

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

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

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

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

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

Большим недостатком объектно-ориентированных баз данных является их тесная связь
с применяемым языком программирования. К данным, хранящимся в реляционной
СУБД, могут обращаться любые приложения, тогда как, к примеру, Java-объект,
помещенный в ООБД, будет представлять интерес лишь для приложений, написанных
на Java.

Объектно-реляционные базы данных

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

10