Вы - -й посетитель этой странички 

Уроки по теме "Базы данных"

Л.О.Сергеев

Введение

ЧАСТЬ 1 Подготовка к урокам
§ 1 Что такое MySOL ?

§ 2 Почему MySOL?

§ 3 Получение и установка программного обеспечения
Различные программы-лиенты
Консольный клиент mysqIexe
GUI-клиент mysqlgui

§ 4 Реляционные базы данных: основные понятия
База данных "Страница классного журнала"
Что такое ключ?
Что такое связь?
Реляционные операторы выбора, проектирования и объединения
Оператор выбора
Оператор проектирования
Оператор объединения
Что такое запрос ?
Что является результатом запросаf

§ 5 Основы языка SQL
Команда USE
Синтаксис команды USE
Пример использования команды USE
Команда SHOW
Синтаксис и примеры использования команды SHOW
Команда CREATE DATABASE
Синтаксис команды CREATE DATABASE
Пример использования команды CREATE DATABASE
Команда CREATE TABLE
Синтаксис команды CREATE TABLE
Команда INSERT
Синтаксис команды INSERT
Пример использования команды INSERT
Команда SELECT
Синтаксис команды SELECT
Часть <запрашиваемая информация>
Часть FROM
Часть WHERE
Часть GROUP BY
Часть HAVING
Часть ORDER BY
Команда UPDATE
Синтаксис команды UPDATE
Пример использования команды UPDATE
Команда DELETE
Синтаксис команды DELETE
Пример использования команды DELETE
Команда DROP
Синтаксис команды DROP
Пример использования команды DROP

§ 6 Установка MySOL в локальной сети класса Подготовка и администрирование учебной базы данных Установка и настройка MySOL в локальной сети
Конфигурация компьютера-клиента
Конфигурация компьютера-сервера
Установка учебной базы данных
Таблица student
Таблица event
Таблица score
Таблица absence
Связи между таблицами
Настройка параметров системы безопасности
" Кухня" системы безопасности MySOL

Литература

Введение

В данной публикации обобщен опыт автора по преподаванию темы "Балы данных". Материал состоит из трех частей (глав). В первой части (она целиком вошла в этот номер) описывается подготовка к урокам Эта подготовка включает, в частности, установку и настройку требуемого программного обеспечения. В первой части содержится также краткий теоретический материал, посвященный реляционным базам данных. Изложение всех вопросов сопровождается большим количеством примеров. Во второй части (вторая и третья части будут опубликованы в осенних номерах "Информатики") приводится поурочное планирование и описываются сами уроки. Во вторую часть включены также дидактические материалы по теме "Базы данных". В третью часть вошли материалы для углубленного изучения темы, факультативов и т.д.
В качестве основного инструмента (программной поддержки) для изучения темы "Базы данных" выбрана СУБД MySQL. Причины такого решения кратко изложены в § 2.
Вопросы по этому материалу автор просит направлять в редакцию газеты "Информатика" (на адрес inf@lseptember.ru).

ЧАСТЬ 1. ПОДГОТОВКА К УРОКАМ

§ 1. Что такое MySQL?

MySQL представляет собой свободно распространяемую реляционную систему управления базами данных (СУБД), построенную на технологии клиент—сервер. СУ БД MySQL включает в себя SQL-сервер (его часто называют "сервером баз данных"), программы-клиенты и сопутствующее программное обеспечение (средства администрирования, интерфейсы для взаимодействия с приложениями, написанными на различных языках программирования, и документацию).
Имеются версии MySQL для различных операционных систем. Необходимо подчеркнуть (для тех, кто не имеет достаточного опыта работы с системами типа клиент—сервер, это иногда бывает неочевидно), что сервер баз данных и программы-клиенты могут работать под управлением различных операционных систем.
Следует отметить, что мы постоянно используем системы, построенные на технологии клиент-сервер, но, как правило, лишены необходимости вникать в подробности взаимодействия клиента и сервера. К примеру, когда мы просматриваем web-страницы, размещенные на web-сервере, клиентом является наш браузер (а сервером, разумеется, web-сервер). Браузер (клиент) отправляет запросы, в ответ на которые сервер производит определенные действия, например, пересылает браузеру запрошенную web-страницу.
Взаимодействие с SQL-сервером также производится посредством запросов, в ответ на которые сервер возвращает запрашиваемую информацию или выполняет требуемую модификацию данных. Запросы к SQL-серверу формулируются на языке SQL (Structured Query Language}.
Обычно взаимодействие программ-клиентов с сервером MySQL производится по протоколу TCP/IP.

§ 2. Почему MySQL?

Коротко ответ на вопрос, вынесенный в заголовок этого параграфа, можно сформулировать следующим образом: потому что MySQL позволяет наглядно продемонстрировать суть изучаемых понятий. При этом очень важно то, что MySQL позволяет продемонстрировать работу с СУБД "в чистом виде", не отягощенную множеством несущественных деталей, которые, конечно, облегчают работу профессионалам, хорошо понимающим, что происходит на самом деле, но по меньшей мере не способствуют лучшему пониманию сути. С этой точки зрения выбор MS Access представляется не особенно (на взгляд автора, даже "особенно не") удачным. Таким образом, выбор MySQL обусловлен прежде всего методическими соображениями.
Очень важно то, что MySQL распространяется свободно. И дело здесь не только в этических соображениях (хотя они превалируют), но и в том, что не возникает никаких проблем с получением последних версий программ, патчей и т.п. Поскольку MySQL очень широко распространена, то недостатка в информации пользователи этой СУБД тоже не испытывают.
Еще одно достоинство MySQL заключается в том, что эта СУБД чрезвычайно просто интегрируется с различными системами, в частности, с web-приложениями. Дистрибутив MySQL содержит достаточное количество примеров взаимодействия с SQL-сервером из программ на языках С. РНР. Perl и т.д.

§ 3. Получение и установка программного обеспечения

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

Дистрибутив последней версии MySOL можно получить с сайта разработчиков http://www.mysql.com  Единственная проблема, которую приходится при этом решать, — большой объем архива (он, разумеется, может меняться от версии к версии, но наверняка превышает 10 Мб). Имя архива (применительно к Windows -версии) имеет вид mysql-x.xx.xx-win. zip, где х.хх.хх — номер версии MySOL (например, 3.23.42).
Файлы из архива следует распаковать во временный каталог. Для начала установки необходимо запустить файл setup.exe. Единственный существенный вопрос, на который приходится отвечать в процессе установки, связан с указанием каталога, в который будет установлена MySOL. "По умолчанию" MySOL устанавливается в каталог С: \mysql. Помимо выбора каталога, надо будет указать тип установки. Мы рекомендуем для начала остановиться на "типичной" конфигурации, а уже потом, при необходимости, изменять ее.

После завершения установки каталог С: \mysql будет иметь вид:
С : \mysql
bench
bin
data
Docs
examples
include
lib
scripts
share
infolist.txt
my-example.cnf
mysqlbug.txt
Readme
Uninst.isu
В каталоге bin находятся все исполняемые файлы, в том числе сам SQL-сервер и программы-клиенты. Документация (на английском языке) содержится в каталоге Docs. Каталог data предназначен для хранения баз данных. Каждая база данных хранится в отдельном каталоге, имя которого совпадает с ее названием. После установки в каталоге data имеются два подкаталога (соответственно имеются две базы данных) — mysql и test.
Файл mу-example.cnf является шаблоном файла конфигурации. Его требуется переименовать в my.cnf и поместить в корневой каталог диска С:. Но это можно сделать позже (тем более что в этот файл потребуется внести некоторые изменения). Сейчас гораздо интереснее опробовать SQL-сервер в работе. SQL-сервер запускается фaйлoм mysqld.ехе (название файла образовано от "MySQL Daemon"), который расположен в каталоге bin. В случае удачного запуска на экране, вы не увидите ничего. Сервер запускается молча. Далее следует запустить файл mysql.ехе — программу-клиент, работающую в текстовом режиме. Если все было сделано правильно, то клиент запустится, установит соединение с сервером и выведет приглашение, в ответ на которое модою вводить команды. Более подробному знакомству с командами языка SQL посвящен § 5, пока же вы можете попробовать MySOL в работе на примере команд SHOW DATABASES и SELECT (см. примеры 1 и 2). Результатом команды SHOW DATABASES является перечень баз данных, доступных на SQL-сервере (см. пример 1). А команда SELECT NOW() , USER() сообщает текущую дату и время (результат NOW ( ) ) и имя пользователя, под которым программа-клиент установила соединение с сервером (результат USER () ). Обратите внимание на то, что команды завершаются символом ";". Это является особенностью данной программы-клиента, а не требованием языка SQL. Следует также обратить внимание на то, что в примере 2 специально показан способ ввода многострочной команды. Если строка не завершается символом ";", про грамма mysql выводит в следующей строке приглашение "->", которое свидетельствует о том, что ожидается продолжение команды.
Пример 1
С : \mysql\bin>mysql . ехе
Welcome  to  the  MySQL  monitor.    Commands  end  with  ; or  \g.
Your  MySQL  connection  id  is  2 to  server  version : 3.23.3 6 -debug
Type  'help; ' or  ' \h ' for  help . Type  ' \c'  to  clear  the  buffer
mysql>

Пример 2
mysql>  SHOW  DATABASES;
-------------
| Database|
-------------
| mysql     |
| test        |
-------------
2 rows in  set   (0.00 sec)

mysql> SELECT  NOW ( ) ,
-> USER() ;

Работу сервера mysqld следует всегда завершать правильно. Для этого необходимо использовать следующую команду:
                  С:\mysql\bin>mysqladmin.exe -u root shutdown
Программа mysqladmin.exe находится в каталоге С: \mysql\bin. Впоследствии, когда вы, руководствуясь инструкциями из § 6, определите пароль для пользователя root, при вызове программы mysqladmin надо будет давать этот пароль:
С:\mysqi\bin>mysqiaamin.схс   -u root  -рпароль shutdown

Различные программы-клиенты

Консолыный клиент mysql.exe

Во всех примерах, приведенных в этом номере, и при проведении уроков мы используем консольную программу-клиент mysql.exe. Работать с этой программой, мягко говоря, не слишком удобно. Ее основной недостаток —
отсутствие возможности редактирования строк запроса в процессе ввода (после перехода на новую строку). Отметим, что этого недостатка лишены версии клиента mysql для Linux и FreeBSD. Для облегчения работы с mysal следует использовать возможности выполнения запроса, записанного в текстовом файле, и ведения протокола (сооттствующие примеры приведены ниже). И все же нам кажется целесообразным на первых порах (и на уроках) использовать именно этот, не слишком удобный клиент. При всех его недостатках он имеет и одно существенное методическое достоинство: заставляет вводить запросы вдумчиво, "проговаривая" каждый оператор, каждый шаг.

Рис. 1

GUI-клиент mysqlgui

Когда вы получите достаточный опыт работы с MySOL и консольный клиент mysol вас совсем "утомит", можно преходить к использованию GUI-клиента. Как и MySOL, эта программа распространяется свободно (но не входит стандартный дистрибутив). Получить mysqlgui можно с сайта разработчиков (http://www.mysql.com). На рис. 2 показано окно клиента mysqlgui, а на рис. 3 — окно с результатом запроса.

§ 4. Реляционные базы данных: основные понятия

Что такое база данных (БД)? В [3] БД определяется (в данном случае слово "определяется" не следует понимать формально) как совокупность систематизированных сведений об объектах окружающего нас мира. Мы склонны считать такую формулировку удачной. Удачным нам кажется и то, что в этой же публикации отмечено различие между понятиями "база данных" и "система управления базами данных" (СУБД). Под СУБД понимается специализированное программное обеспечение для работы с базами данных. В упомянутой публикации дается краткий обзор типов баз данных, среди которых выделяются иерархические, сетевые и реляционные. MySQL представляет собой реляционную систему управления базами данных (или, что то же самое, систему управления реляционными базами данных), соответственно мы будем обсуждать только базы данных этого типа.
В реляционных базах данных все данные представляются в таблицах. Далее мы увидим, что, для того чтобы СУБД называлась реляционной, одного требования представления данных в таблицах мало, но пока мы им ограничимся. Опыт показывает, что в этом месте следует подчеркнуть, что в реляционных базах данных используются только такие таблицы, в которых все строки (и соответственно столбцы) состоят из одного и того же числа ячеек. Если вы будете заниматься базами данных после изучения основ языка HTML, то увидите, что упомянуть об этом будет нелишне (особо памятливые дети обязательно вспомнят про COLSPAN и ROWSPAN).
При проектировании базы данных определится структура каждой таблицы (вполне возможно, что в базе данных имеется только одна таблица, хотя в "серьезных" проектах такое встречается редко). Определить структуру таблицы, значит определить набор (имена, типы и, при необходимости, особые свойства) ее столбцов. Таким образом, все данные в каждом столбце таблицы имеют один тип. Кроме того, важно понять, что таблицы состоят из конечного, определенного на этапе проектирования БД, набора столбцов. Разумеется, СУБД предоставляют возможность модифицировать структуру таблиц, но сути это не меняет: набор столбцов конечен и определен проектировщиком БД. Необходимо пояснить, почему мы уделяем тому факту, что набор столбцов конечен, значительное внимание. Дело в том, что, когда в классе начинается разговор о таблицах, все сразу вспоминают про самую близкую сердцу школьника таблицу — страницу классного журнала. Так вот, это "плохой" пример. Ведь в таблицу в классном журнале с каждым новым уроком фактически добавляются столбцы. А впрочем... раз этот пример не нравится, почему бы с ним не разобраться? Итак, попробуем спроектировать небольшую базу данных для учета успеваемости и посещений уроков по одному предмету. И на этом примере рассмотрим ряд важных понятий, связанных с реляционными базами данных.
База данных "Страница классного журнала"
Приступая к проектированию базы данных, прежде всего следует определиться с тем, какие данные требуется хранить. Иногда, для того чтобы получить ответ на этот вопрос, приходится долго работать с заказчиком (экспертом) или структурировать свои собственные мысли. В нашем случае ситуация облегчается тем, что мы знаем, как выглядит журнал и что в нем хранят. Поскольку мы не собираемся делать "лучше лучшего", постараемся просто "положить" бумажный классный журнал на "музыку" реляционной базы данных. На странице журнала, относящейся к одному предмету, присутствует список учеников (обычно для каждого ученика указываются имя и фамилия), даты уроков и оценки. Кроме того, учителя обычно помечают карандашом типы некоторых уроков (и соответственно происхождение полученных на них оценок: "контрольная работа", "зачет" и т.д.). Будем считать, что мы специально выделяем лишь контрольные работы, таким образом, уроки у нас бывают лишь двух типов: "К" — контрольная работа; "О" — обычный урок. Все?.. Нет, не все. Помимо оценок, в журнале обычно отмечаются пропуски уроков. Это тоже следует учесть при проектировании структуры БД. Наконец, мы будем считать, что в одной клеточке журнала хранится только одна оценка, т.е. клеточек вида 2/5 в журнале не встречается. Итак, "набросаем" макет таблицы, содержащей перечисленные данные, и для примера заполним несколько строчек.

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

Перечислим те недостатки выбранного представления данных, которые лежат на поверхности.
1. Зачем мы трижды повторили, что 2002-09-04 была контрольная работа? Ясно, что если уж она была, то у всех. И вообще, если уж был урок, то у всех (но не следует забывать, что ученик мог отсутствовать на уроке или просто не получить за него опенку).
2. Как мы собираемся хранить значения в поле "Содержимое клеточки журнала" ? Как число? Но в этом столбце встречаются и символы "н". Как символ? Можно, конечно, но тогда при различных вычислениях (например, при вычислении средней опенки) придется что-то с этим символом делать, чтобы превратить его в цифру.
3. Вдруг у нас в классе имеются два Петра Первых? Как мы их собираемся различать?
4. Наконец, зачем нам третья строка таблицы? Это в журнале имеются пустые клеточки, нам-то они зачем?
Рассмотрим эти вопросы по порядку, начиная с последнего.
4. Такие строки нам не нужны. Можно просто выбросить их из таблицы.
3. Отвечая на третий вопрос, стоит еще раз вспомнить, как выглядит страница журнала. Все ли данные мы перенесли в таблицу базы данных? Ведь вопрос, который возник перед нами, не является специфичным именно для базы данных, его как-то приходится решать и в обычном журнале. Оказывается, мы просто забыли о том, что список учеников в журнале пронумерован, а номер и является тем уникальным полем, которое однозначно определяет каждого ученика. Имеет смысл завести отдельную таблицу для хранения номеров (уникальных идентификаторов) учеников и их имен и фамилий. Тогда именно номера можно будет использовать в других таблицах, что исключит ненужное дублирование данных.
2. Символы "н" нам мешают. Так может быть, и для фиксации пропусков завести отдельную таблицу? И отмечать в этой таблице, кто и в какой день отсутствовал.
1. И для типов уроков тоже можно завести отдельную таблицу. Достаточно будет один раз пометить в этой таблице тип урока.
После этих рассуждений выпишем новую структуру таблиц. Теперь, поскольку таблиц стало несколько, дадим им названия.

Лучше? Лучше. Но... Возможно, вы уже и сами обратили внимание на то, что мы не все предусмотрели в нашей таблице. Более точно: мы не учли важную особенность исходных данных: дело в том, что в один день может быть проведено больше одного урока. Конечно, записать их в таблицу "Уроки" не проблема, но вот как мы поймем из таблицы "Оценки", за какой именно урок выставлена оценка? Эта проблема решается тем же способом, каким мы обеспечили уникальность учеников: уроки должны получить уникальные (для простоты — числовые) идентификаторы. Проще говоря, как и учеников, уроки (все, от начала до конца учебного года, насквозь) надо пронумеровать. И указывать в таблице "Оценки" не даты, а номера уроков. В таблице "Пропуски" мы тоже будем указывать номера уроков (ведь ученик может прийти на контрольную, а обычный урок пропустить). Снова изменим структуру таблиц (изменения коснутся лишь таблиц "Оценки", "Уроки" и "Пропуски").

Что такое ключ?

Вероятно, вы обратили внимание на то, что мы выделили полужирным шрифтом названия некоторых полей (поля "Номер ученика" в таблице "Ученики", поля "Номер ученика" и "Номер урока" в таблице "Оценки" и т.д.). Выделенные поля являются ключами (ключевыми полями) соответствующих таблиц. Понятие ключа является одним из ключевых понятий баз данных, поэтому остановимся на нем подробнее. Из приведенного примера, в частности, видно, что ключом таблицы может быть одно поле или набор полей (в последнем случае употребляют термин "составной ключ"). Ключ однозначно определяет каждую строку таблицы, то есть никакие две строки таблицы не содержат одинаковые значения ключа (в случае составного ключа значения 6 составляющих его полях могут повторяться, но набор этих значений должен быть уникальным).
Всегда ли в таблицах выделяют ключевые поля ? В принципе наличие ключа не является обязательным. Реляционные СУБД позволяют создавать таблицы и без ключевых полей. Но, как правило, отсутствие в таблице ключа бывает вызвано ошибками проектирования базы данных. Поэтому, если вы видите, что в таблице нельзя выделить ключевые поля, следует внимательно проверить проект, скорее всего что-то в нем не так. А как выделять ключ? Всегда ли ключевые поля выделяются однозначно? Вообще говоря, нет. Но следует понимать, что ключи в таблицах выделяются не просто "чтобы были". Основное назначение ключей — связывание таблиц.
В нашем примере таблицу "Ученики" можно связать с таблицами "Оценки" и "Пропуски" по значению поля "Номер ученика". Таблица "Уроки" может быть связана с таблицами "Оценки" и "Пропуски" по значению поля "Номер урока". Но что значит "связана"?
Что такое связь?
Вообще говоря, с точки зрения MySQL связей между таблицами не существует. Подобной точки зрения придерживаются разработчики и некоторых других реляционных СУБД. Хотя в языке SQL имеются средства для описания связей (их использование в MySQL не приводит к ошибке, они просто игнорируются) и, безусловно, связи широко используются при работе с реляционными базами данных. Так в чем же противоречие? Противоречия нет. Таблицы связываются так, как мы (разработчики БД) себе это представляем. Например, первой строке таблицы "Ученики" ("Номер ученика"=1) соответствуют первая и третья строки таблицы "Оценки" (с тем же значением поля "Номер ученика"). Таким образом, мы считаем (так мы спроектировали БД), что таблицы "Ученики" и "Оценки" связаны по значениям полей "Номер ученика" и "Номер ученика" (здесь мы хотим подчеркнуть, что поля, по которым производится связывание, не обязаны иметь одинаковые названия, хотя чаще всего так и делают, чтобы не запутаться). Для описания связей между таблицами в языке SQL имеются специальные ключевые слова REFERENCES и FOREING KEY, которые употребляются при описании полей таблиц. MySQL их игнорирует.
Итак, мы фактически зафиксировали два положения: связь — это соответствие между строками двух таблиц; связь между таблицами устанавливается по значениям некоторых полей (т.е. таблицы не могут быть связаны "вообще", они обязательно связываются по некоторым значениям). Выбор полей, по которым производится связывание, определяет тип связи: "один ко многим", "многие ко многим", "один к одному". Иногда на этапе проектирования базы данных становится ясно, что с содержательной точки зрения таблицы связаны, но подходящих полей для организации связи не находится. Это свидетельствует о том, что следует завести еще одну "связующую" таблицу (чуть ниже мы рассмотрим соответствующий пример). Мы не станем подробно рассматривать различные типы связей, лишь кратко опишем их (исчерпывающий рассказ о типах можно найти, например, в [2] ).
При связывании таблиц А и В "один к одному" каждой строке таблицы А соответствует одна и только одна строка таблицы В и наоборот. Связи "один к одному" можно организовывать, но они, вообще говоря, не имеют большого смысла, поскольку их наличие свидетельствует о том, что мы разделили по отдельным таблицам то, что можно было не разделять (и даже, возможно, не следовало разделять). Впрочем, иногда связи "один к одному" используются для оптимизации работы с базой данных.
При связывании таблиц А и В "один ко многим" каждой строке таблицы В соответствует ровно одна строка таблицы А. Связи типа "один ко многим" — основной инструмент разработчиков баз данных. В частности, в рассмотренном нами примере таблица "Ученики" связана с таблицами "Оценки" и "Пропуски" именно как "один ко многим". Так же связаны таблицы "Уроки" и "Оценки" и "Уроки" и "Пропуски". При связывании таблиц "один ко многим" на стороне "одного" всегда используется ключевое поле таблицы. Связанное с ним поле на стороне "многих" называют внешним ключом.
При связывании таблиц А и В "многие ко многим" каждой строке таблицы А могут соответствовать несколько строк таблицы В и наоборот. Если при проектировании базы данных между двумя таблицами появляется связь "многие ко многим", ее следует ликвидировать путем введения дополнительной таблицы. Приведем пример. Пусть нам нужно описать систему "учителя — предметы" (какие учителя какие предметы ведут). Ясно, что один предмет могут вести несколько учителей, а один учитель, такое встречается сплошь и рядом, может вести несколько предметов. Список учителей будет храниться в таблице "Учителя", список предметов — в таблице "Предметы":