Всё для Учёбы — студенческий файлообменник
1 монета
doc

Лекция «MySQL» по Базам данных (Иванько А. Ф.)

1. Преимущества MySQL

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

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

Система MySQL хорошо проверена и надежна. Она используется целым рядом очень требовательных заказчиков, таких как, например, Yahoo.

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

2. Терминология баз данных

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

Объекты и отношения

Основой БД являются объекты и отношения (т.е. их связи).

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

Отношения могут быть разного типа:

1. Взаимнооднозначными (отношение типа "один к одному")

2. "Один ко многим" (или "многие к одному", в зависимости от направления)

3. "Многие ко многим".

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

Зависимости или таблицы

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

Рис. 3.2. В таблице employee хранятся кодовые номера служащих, их имена, а также информация о должности и месте работы каждого служащего

Столбцы или атрибуты

В таблицах базы данных каждый столбец или атрибут описывает некоторую часть данных, которыми характеризуется каждая запись в таблице. Термины столбец и атрибут (свойство) в этом контексте взаимозаменяемы, но столбец — это на самом деле часть таблицы, тогда как атрибут характеризует реальный объект, для моделирования которого используется таблица. На рис. 3.2 каждый служащий имеет атрибуты employee ID (кодовый номер), name (имя), job (должность) и department ID (номер отдела). Эти атрибуты формируют столбцы таблицы employee, которые иногда так и называют — атрибутами.

Строки, записи, кортежи

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

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

Ключ — это минимальный суперключ.

Например, в таблице employee для идентификации любой строки можно использовать пару атрибутов employee ID и name. Для той же цели можно использовать и набор, состоящий из всех столбцов (employeelD, name, job, department ID). Оба эти набора являются суперключами.

Однако для идентификации строк совсем не обязательно использовать все столбцы. Достаточно, например, использовать employeelD. Этот столбец оказывается минимальным суперключом — минимальным набором столбцов, с помощью которых можно однозначно идентифицировать любую строку. Таким образом, employeelD является ключом.

В таблице employee идентифицировать служащего можно как по имени (столбец name), так и по его кодовому номеру (столбец employeelD). Оба указанных столбца являются ключами и называются потенциальными ключами, поскольку они оказываются кандидатами на роль первичного ключа.

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

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

Функциональные зависимости

Если в таблице имеется функциональная зависимость между столбцами А и В, которую можно обозначить А—>В, это значит, что значение столбца А определяет

значение столбца В. Например, в таблице employee столбец employeelD функ-ционально определяет столбец name (равно как и все другие атрибуты в данном конкретном примере).

Схемы Термин схема или схема базы данных обозначает структуру или дизайн базы данных, т.е. форму базы данных без данных как таковых. Если хотите, то схема — это шаблон для данных в базе данных.

Схему указанной выше конкретной таблицы можно представить в следующем виде:

employee(employeelD, name, job, departmentID)

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

3. Принципы проектирования баз данных

При разработке базы данных важно знать следующее.

■ Какую информацию необходимо помнить? Другими словами, информацию

о каких вещах или объектах нужно хранить?

■ Какие вопросы будут задаваться базе данных? (Такие вопросы называют

запросами.)

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

Избыточность и утрата данных

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

Рис. 3.3. Такая схема порождает избыточность: информация о названиях отделов сохра-няется снова и снова

Вместо указанной таблицы можно предложить следующую:

employee(employeelD, name, job, departmentID) department(departmentID, name)

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

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

Аномалии

Аномалии представляют собой немного более сложное понятие. Аномалии — это проблемы, возникающие в данных из-за дефектов проектирования базы данных. Существуют три вида аномалий:

1. Аномалии вставки

Аномалии вставки проявляются тогда, когда мы пытаемся добавить данные в дефектную таблицу.

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

2. Аномалии удаления

Аномалии удаления возникают тогда, когда мы удаляем данные из дефектной схемы.

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

3. Аномалии модификации

Аномалии модификации возникают тогда, когда мы изменяем данные дефектной схемы.

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

4. Нормализация

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

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

Первая нормальная форма

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

Вторая нормальная форма

Говорят, что схема находится во второй нормальной форме (сокращенно называемой 2NF), если все атрибуты, не являющиеся частью первичного ключа, полностью функционально зависимы от первичного ключа, а сама схема уже находится в первой нормальной форме. Что это значит? Это значит, что каждый атрибут, не являющийся ключом, должен функционально зависеть от всех составляющих ключа. Так, если первичный ключ составлен из нескольких столбцов, то всякий другой атрибут в таблице должен зависеть от комбинации всех этих столбцов.

Третья нормальная форма

Третья нормальная форма требует, чтобы атрибуты не зависели ни от чего, кроме ключа.

Формально, чтобы схема находилась в третьей нормальной форме (3NF), мы должны исключить все транзитивные (промежуточные зависимости) зависимости, а сама схема должна уже находиться во второй нормальной форме. Но что такое транзитивная зависимость?

employeeDepartment(employeelD, name, job, departmentID, departmentName)

Эта схема содержит следующие функциональные зависимости:

employeelD —> name, job, departmentID, departmentName

departmentID —> departmentName

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

Но заметим также, что

employeelD —> departmentID employeelD —> departmentName

и departmentID —> departmentName

Это транзитивная (промежуточная) зависимость.

Нормальная форма Бойс-Кодда

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

Чаще всего, как в нашем примере, для этого не требуется предпринимать никаких дополнительных усилий. Но если появляется зависимость, нарушающая первое правило, нам придется снова пройти путь разделения таблиц, аналогичный пройденному выше для 1NF, 2NF и 3NF.

5. Создание баз данных, таблиц и индексов

Чувствительность к регистру и идентификаторы

■ Имена базы данных подчиняются тем же правилам зависимости о регистра

символов, каким следуют каталоги используемой операционной системы. Имена

таблиц следуют тем же правилам, что и имена файлов. Все остальное не

зависит от регистра.

■ Все идентификаторы, кроме имен псевдонимов, могут содержать до 64 сим

волов. Имена псевдонимов могут иметь до 255 символов.

■ Идентификаторы могут содержать любые допустимые символы, но имена

баз данных не могут содержать символы /, \ и ., а имена таблиц — символы

. и /.

■ Зарезервированные слова можно использовать для идентификаторов, если

заключить их в кавычки.

Создание базы данных

■ create database имяБД; создает базу данных.

■ use database имяБД; выбирает базу данных для использования.

Создание таблиц

■ Используйте оператор create table, общая форма которого имеет сле-дующий вид:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] имя_таблицы [(определение_сгеаtе,...)] [опции^таблицы] [onepaTop__select]

или

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] имя_таблицы LIKE имя старой таблицы;

определение_сгеаte:

имя_столбца тип [NOT NULL | NULL] [DEFAULT значение]

[AUTO^INCREMENT] [PRIMARY KEY] [определение_ссылки] или PRIMARY KEY {имя_столбца_индекса,...) или KEY [имя_индекса] (имя_столбца_индекса, ...) или INDEX [имя__индекса] (имя_столбца__индекса, . . . ) или UNIQUE [INDEX] [имя_индекса]

(имя_столбца_индекса, ...) или FULLTEXT [INDEX] [имя_индекса]

(имя_столбца_индекса,...) или [CONSTRAINT символ] FOREIGN KEY [имя_индекса]

(имя_столбца_индекса,...) [определение_ссылки] или CHECK (выражение)

Типы столбцов

■ Точными числовыми типами являются TINYINT, SMALLINT, INT,

MEDIUMINT, BIGINT, NUMERIC и DECIMAL.

■ Приближенными числовыми типами являются FLOAT и DOUBLE.

■ Строковые типы — это CHAR, VARCHAR, TEXT и BLOB.

■ К типам даты и времени относятся DATE, TIME, DATETIME, TIMESTAMP

и YEAR.

■ Имеются также различные псевдонимы для этих типов данных.

Удаление баз данных, таблиц и индексов

■ Базу данных удаляют с помощью оператора

drop database имяБД;

■ Таблицу удаляют с помощью оператора

drop table имяТаблицы;

■ Индекс удаляют с помощью оператора

drop index имяИндекса on имяТаблицы;

Изменение структуры таблиц

■ Структуру таблицы можно изменить с помощью оператора ALTER TABLE.

Общий вид этого оператора следующий:

ALTER [IGNORE] TABLE имя__таблицы спецификация_а1Ьег {, спецификация__а11ег . . . ]

спецификация alter:

ADD [COLUMN] определение_сгеаЬе

[FIRST | AFTER имя_столбца] или ADD [COLUMN] (определение_сгеаЬе,

определение_сгеаЬе, . . .)

или ADD INDEX [имя_индекса] (имя__столбца__индвкса, . . . ) или ADD PRIMARY KEY (имя_столбца_индекса, . . .) или ADD UNIQUE [имя__индекса] (имя_столбца__индекса, . . .) или ADD FULLTEXT [имя__индекса] (имя__столбца_индекса, . . .) или ADD [CONSTRAINT символ] FOREIGN KEY [имя__индекса]

[имя_столбца_индекса,...) [определение_ссылки] или ALTER [COLUMN] имя^столбца

{SET DEFAULT литерал | DROP DEFAULT} или CHANGE [COLUMN] старое_имя_столбца

определение_сгеаЬе [FIRST | AFTER имя_столбца] или MODIFY [COLUMN] определение_сгеаЬе

[FIRST | AFTER имя^столбца] или DROP [COLUMN] имя^столбца или DROP PRIMARY KEY или DROP INDEX имя_индекса или DISABLE KEYS или ENABLE KEYS

или RENAME [TO] новое_имя_таблицы или ORDER BY имя столбца или опции_таблицы

6. Вставка, удаление и обновление данных

Вставка данных

■ Оператор SELECT имеет следующую общую форму:

SELECT столбцы

FROM таблицы

[WHERE условия]

[GROUP BY группа

[HAVING групповые_условия]]

[ORDER BY сортировка_столбцов]

[LIMIT пределы];

■ Выражение select * возвращает данные всех столбцов, a select!

имяСтолбца — данные указанного столбца.

■ Можно использовать нотацию база_данных. таблица для таблиц

и таблица. столбец или база_данных. таблица. столбец для столб

цов, чтобы исключить двусмысленность.

■ Псевдонимы являются альтернативными именами таблиц и столбцов и опре

деляются так:

select столбец as псевдоним_столбца from таблица as псевдоним_таблицы;

■ Выражение WHERE используется для выбора строк, удовлетворяющих кри

териям поиска.

■ Ключевое слово DISTINCT удаляет повторения из результирующего мно

жества. ,'

■ Выраженив^ЗЯОиР BY распределяет возвращенные строки по группам. Он

используется, главным образом, в совокупности с групповыми функциям!

наподобие count ().

• Выражение HAVING работает подобно WHERE для групп.

■ Выражение, ORDER BY сортирует строки результата по значениям указан!

ных вами столбцов.

■ Выражение LIMIT ограничивает число возвращаемых строк из общего рг

зультирующего набора. Можно указать максимум для числа возвращаемы

строк и смещение, определяющее строку, с которой следует начать вывод,

Удаление данных

Delete from [название таблицы] where [условие]

Обновление данных

update [название таблицы]

set [имя столбца1] =[значение1],

set [имя столбца2] =[значение2]

where [условие];

Показать полностью…
Похожие документы в приложении