Методические указания рассмотрены и рекомендованы к изданию методическим семинаром кафедры автоматики и компьютерных систем «29»
| скачать Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования «ТОМСКИЙ ПОЛИТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ» Утверждаю Декан (директор) С. А. Гайворонский « » 2009 г. А.А. Пономарев Создание структуры БД и манипулирование данными в СУБД MySQL Методические указания к выполнению лабораторных работ по курсу «Базы данных» для студентов III курса специальностей 230105 «Программное обеспечение вычислительной техники и автоматизированных систем». 230201 «Информационные системы и технологии». Издательство Томского политехнического университета 2009 УДК 681.324.016 ББК 00000 П00 ^ П00 Базы данных: методические указания по выполнению лабораторного практикума для студентов специальностей 230105 «Программное обеспечение вычислительной техники и автоматизированных систем», 230201 «Информационные системы и технологии». В трех частях, часть первая. /Сост. А.А. Пономарев. – Томск: Изд-во ТПУ, 2009. – 80 с. УДК 681.324.016 ББК 00000 Методические указания рассмотрены и рекомендованы к изданию методическим семинаром кафедры автоматики и компьютерных систем « 29 » октября 2009 г. Зав. кафедрой АиКС доктор технических наук __________Г.П. Цапко Председатель учебно-методической комиссии __________Е.А. Кочегурова Рецензент Доктор технических наук, профессор ^ © Пономарев А.А., 2009 ©  Томский политехнический университет, 2009©Оформление. Издательство Томского политехнического университета, 2009 Оглавление Лабораторная работа №1. Интерфейс СУБД MySQL. Предоставление доступа и привилегий. Утилиты, входящие в состав СУБД 4 Лабораторная работа №2. Моделирование баз данных средствами Erwin 16 Лабораторная работа №3. Создание баз данных и таблиц в среде MYSQL. Информационное наполнение. 27 Лабораторная работа №4 Создание запросов и модификация таблиц базы данных. 35 Лабораторная работа №5. Работа с внешними базами данных. Ограничение доступа. 46 Лабораторная работа №6. Представления, хранимые процедуры, функции, триггеры 51 Представления 51 Хранимые процедуры и функции 52 Триггеры 57 Словарь данных 58 Курсоры 60 СОЗДАНИЕ СТРУКТУРЫ БД И МАНИПУЛИРОВАНИЕ ДАННЫМИ В СУБД MYSQL 81 ^ Цель работы: Ознакомиться с приложениями, включенными в состав СУБД MySQL. Получить навыки управления учетными записями пользователей и определения привилегий. Ознакомиться с утилитами, входящими в состав СУБД MySQL, получить навыки работы с ними. ^ Управление сервером обычно осуществляется из командной строки. Запуск в Windows 95/98/2000/XP осуществляется через сеанс DOS выполнением следующей команды:
Эта команда запустит демон mysql в фоновом режиме. В Windows 95/98 не предусмотрен запуск mysqld в виде службы. В Windows 2000 демон mysql запускается в виде службы. Можно осуществить запуск winmysqladmin.exe, в этом случае все настройки перечисляются в файле my.ini При запуске mysqld можно указывать следующие опции: ^
Наличие в статусной строке иконки светофора с активным зеленым цветом указывает на то, что сервер запущен (см. рис 1).  ^ Теперь можно попытаться войти в сервер. В случае, если предполагается управление сервером через консоль, то необходимо использовать команду mysql. Изначально существует единственный пользователь, которому предоставляется право входа - root, которая не имеет пароля. Первое, что нужно сделать войти под именем root и зарегистрировать нового пользователя и установить для него пароль. Команда mysql может использовать следующие опции: ^
Примечание. Команды mysqld и mysql имеют еще некоторые опции, но в данный момент они особого интереса не представляют. Запуск из сеанса ДОС осуществляется как показано на Рисунок 2 (в указанном случае осуществляется подключение к БД mysql).  ^ Для выполнения в строке наберите команду: mysql –u root  Рисунок 3 - Успешный запуск консоли Если вы это получили, значит вы успешно вошли в консоль mysql, которая используется для администрирования сервера. Для составления отчета вам понадобятся приведение команд, которые вы будете посылать на сервер. В MySQL имеется возможность ведение протокола выполняемых команд, чтобы запустить ведение протокола необходимо выполнить команду \T filename !!! обязательно в верхнем регистре. Filename – имя файла, в который будут записываться команды (создается автоматически при выполнении команды, и действует во время жизни сеанса, т.е. в случае отключения от сервера лог прерывается и для возобновления необходимо повторить команду с выводом в новый файл, так как команда затирает имеющиеся в файле данные). Просмотр списка БД, доступных на сервере осуществляется командой ^ Для выполнения в строке наберите команду: show databases. Командой: USE MYSQL; – выбираем текущую БД где MYSQL имя БД. Система привилегий и безопасность в MySQL
База данных mysql и таблицы привилегий. Итак, вы успешно вошли в базу данных mysql, которая используется для администрирования сервера. Что же здесь находится? А находятся здесь 5 таблиц, которые ничем не отличаются от других таблиц баз данных, за исключением того, что эти таблицы используются для предоставления доступа к базам данных и таблицам в них пользователям. Рассмотрим каждую из них. Введите следующую команду, show tables, которая покажет таблицы в базе данных mysql. Кратко рассмотрим функции каждой из таблиц: Таблица User Определяет, разрешено ли пользователю, пытающемуся подключиться к серверу делать это. Содержит имя пользователя, пароль а также привилегии. Если ввести команду show columns from user; то получим следующее: ^
Изначально эта таблица содержит пользователя root без пароля. По умолчанию root может входить с любого хоста, имеет все привилегии и доступ ко всем базам данных.Также в таблице содержится запись для пользователя '%'. В БД MYSQL содержатся таблицы, называемых таблицами привилегий. Система привилегий будет подробно рассмотрена в следующих работах, а пока вы можете выполнить команды на добавления своего пользователя: Для добавления нового пользователя your_name, можно выполнить следующие операторы языка (Insert):
Если необходимо выделить все столбцы таблицы, то необходимо набрать * в качестве аргумента команды select. Чтобы изменения вступили в силу нужно перегрузить сервер, предварительно закончив текущий сеанс работы командой quit.
После установки пароля для пользователя нужно перезагрузить сервер командой mysqladmin reload, чтобы изменения вступили в силу. После этого можно попробовать войти снова:
Если же после этой операции вы не получите приглашение ко входу, то необходимо будет повторить вход в сервер под учетной записью ROOT и назначить необходимые права. Т.о., недостаточно добавить сведения о пользователе в системную БД, дополнительно необходимо назначить права пользователю, после чего можно начинать настраивать таблицы привилегий, вводить новых пользователей, создавать базы данных и таблицы, то есть делать все то, что называется администрированием. Назначить права можно указанием инструкцией INSERT для заполнения соответствующие привилегии (перечень привилегий см. Таблица 3)
Если пароль был случайно забыт, чтобы его задать по новой, придется стереть файлы mysql.frm mysql.MYI и mysql.MYD из папки с базами данных, затем запустить скрипт mysql_install_db и повторить все по новой. Можно воспользоваться ключом MYSQL и ввести --skip-grant-tables, при этом все пароли будут имеет пустое поле. Команда имеет вид mysqld --skip-grant-tables. Пояснения: 1.Команда insert вставляет данные в таблицу, не забывайте завершать команды ';'. 2.При вводе пароля используйте функцию password(), иначе пароль работать не будет! 3.Все пароли шифруются mysql, поэтому в поле Password вы видите абракадабры. Это делается в целях безопасности. 4.Не есть хорошей практикой назначать привилегии пользователям в таблице user, так как в этом случае они являются глобальными и распространяются на все базы данных. Предоставляйте привилегии каждому пользователю к конкретной базе данных в таблице db, которая будет рассмотрена далее. 5.При задании имени хоста для входа через сеть рекомендуется явно указывать полное имя хоста, а не '%'. В приведенном выше примере пользователю mary разрешается вход на сервер со всех машин домена tomsk.ru. Можно также указывать IP-адреса машин и маски подсетей для большей безопасности. Таблица Db Определяет к каким базам данных каким пользователям и с каких хостов разрешен доступ. В этой таблице можно предоставлять каждому пользователю доступ к базам данных и назначать привилегии. Eсли выполнить команду show columns from db; получим следующее: ^
Таблица Host Таблица host используется для расширения диапазона доступа в таблице db. К примеру, если доступ к какой-либо базе данных должен быть предоставлен более чем одному хосту, тогда следует оставить пустой колонку host в таблице db, и внести в таблицу host необходимые имена хостов. Выполним команду show columns from host; ^
Как видно из таблицы, здесь также можно задавать привилегии для доступа к базе данных.Они обычно редко используются без необходимости. Все привилегии доступа нужно задавать в таблице db для каждого пользователя, а в таблице host только перечислить имена хостов.Сервер читает все таблицы, проверяет имя пользователя, пароль, имя хоста, имя базы данных, привилегии.Если в таблице db привилегии select, insert установлены в 'Y', а в таблице host в 'N', то в итоге юзер все равно получит 'Y'.Чтобы не вносить путаницы, лучше назначать привилегии в таблице db. Эти 3 таблицы являются основными.В новых версиях MySQL, начиная с 3.22 добавлены еще 2 таблицы- tables_priv и columns_priv, которые позволяют задать права доступа к определенной таблице в базе данных и даже к определенной колонке. Они работают подобно таблице db, только ссылаются на таблицы и колонки. Также, начиная с версии 3.22 можно использовать команду GRANT для предоставления доступа к базам данных, таблицам и колонкам таблиц, что избавляет от необходимости вручную модифицировать таблицы db, tables_priv и columns_priv. Команда GRANT будет подробно рассмотрена в следующих разделах. ^ Таблица 6 - Привилегии пользователя4
Основные утилиты MySQL. В состав дистрибутива MySQL входят следующие утилиты:
Утилиты mysqld и mysql были подробно рассмотрены ранее, поэтому возвращаться к ним не будем. Кратко рассмотрим остальные. Mysqladmin Утилита для администрирования сервера. Может использоваться администратором, а также некоторыми пользователями, которым предоставлены определенные привилегии, например – Reload_priv, Shutdown_priv, Process_priv и File_priv. Данная команда может использоваться для создания баз данных, изменения пароля пользователя(администратор может изменить пароль любому пользователю, а рядовой пользователь – только свой собственный), перезагрузки и остановки сервера, просмотра списка процессов, запущенных на сервере. Mysqladmin поддерживает следующие команды: ^
Пример использования mysqladmin для изменения пароля: mysqladmin -u your_name password your_pass Следует заметить, что в случае использования mysqladmin для установки пароля, не требуется использование функции password().Mysqladmin сам заботится о шифровании пароля. Mysqlaccess Используется для проверки привилегий пользователя для доступа к конкретной базе данных. Общий синтаксис: mysqlaccess [host] [user] [db] опции Полезная утилита для проверки прав доступа пользователя, если он получает сообщение Access denied, при попытке соединиться с базой данных. Опции: ^
Mysqlshow Используется, чтобы показать, с какими базами данных работает сервер, какие таблицы содержит каждая БД и какие колонки есть в каждой таблице.Синтаксис: mysqlshow [опции] [database [table [field]]] Mysqlshow может использовать следующие параметры: ^
Если ввести mysqlshow без аргументов, будут показаны все базы данных, если указать имя БД, будут показаны все таблицы в ней. Команды mysqlshow mysqlshow mysql Mysqldump Программа mysqldump используется для создания дампа содержания базы данных MySQL. Она пишет инструкции SQL в стандартный вывод. Эти инструкции SQL могут быть переназначены в файл. Можно резервировать базу данных MySQL, используя mysqldump, но при этом Вы должны убедиться, что в этот момент с базой данных не выполняется никаких других действий. А то mysqldump Вам такого нарезервирует... Программа mysqldump поддерживает следующие параметры (Вы можете использовать короткую или подробную версию): Таблица 10 - Опции команды MySQLdump
Вы можете направить вывод mysqldump в клиентскую программу MySQL, чтобы копировать базу данных. ПРИМЕЧАНИЕ: Вы должны убедиться, что база данных не изменяется в это время, иначе Вы получите противоречивую копию! Для справки: mysqldump -u root -p mysql user>mysql-1.sql mysqldump -u root mysql>mysql-2.sql Примечание флаг –p используется в случае, если пользователь наделен паролем. После выполнения этой команды у нас появился файл mysql-1.sql и mysql-2.sql. Загрузим их в текстовый редактор, чтобы поподробнее изучить, и, возможно, немного поправить. Задание Запустите сервер MySQL. Зарегистрируйте своего пользователя в консольном приложении, задайте ему права. С помощью утилиты Mysqlshow выполните команду на просмотр структуры и состав таблиц базы Mysql. Приведите в отчете её схему. С помощью утилиты Mysqldump получите полный дамп базы Mysql (данные и таблицы), а также отдельные дампы таблиц и данных. Контрольные вопросы: 1. Каким способом возможен запуск серверной части СУБД. 2. Что такое привилегия. Каково её предназначение. 3. Какие основные утилиты входят в состав СУБД, какие функции они выполняют.
|
плохо
1хорошо
1отлично
1 Разместите кнопку на своём сайте или блоге: