Создание и модификация таблиц
ЛАБОРАТОРНАЯ РАБОТА № 12
Администрирование БД Mysql. Создание доменов и таблиц
МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ
Разработала преподаватель
Старовойтова А.А.
Обсуждено и одобрено
на заседании цикловой комиссии
спецдисциплин специальности
2– 40 01 01 «Программное обеспечение
информационных технологий»
Протокол № __ от ____________
Цель работы
1.1 Формирование умений администрировать БД, создавать домены и таблицы, накладывать ограничения на столбцы и таблицы.
Методическое обеспечение
2.1 Методические рекомендации по выполнению лабораторной работы
2.2 Материальное обеспечение:
– персональный компьютер IBM PC.
Последовательность выполнения работы
3.1 Изучить теоретическое обоснование лабораторной работы
3.2 Создать домены и таблицы в СУБД MySQL, наложить ограничения на столбцы и таблицы
3.3 Составить отчет
3.4 Составить ответы на контрольные вопросы
3.5 Предоставить отчет преподавателю для проверки
Теоретическое обоснование
Создание доменов
Перед тем как создавать таблицы, которые ссылаются на домены необходимо задать описание доменов при помощи команды create domain. В результате ее выполнения создается домен, на который можно ссылаться в командах создания и модификации таблиц. Столбцы, базирующиеся на доменах, наследуют все его характеристики, причем часть из них может быть переопределена в локальных описаниях столбцов.
Синтаксис описания домена:
Create domain <name_domain> as <data_type>
[Default {litaral | Null | User}]
[Not null] [Check (<dom_condition>)];
Name_domain – имя создаваемого домена;
Data_type – тип данных;
Default {litaral | Null | User} – задание значения по умолчанию;
Значение по умолчанию присваивается соответствующему атрибуту при создании новой строки таблицы, если его значение не указано явно.
Параметр litaral указывает значение явно, Null оставляет значение пустым, User – имя пользователя, создавшего запись.
Для полей типа «дата» можно указывать now (тогда будет вводиться текущая дата).
Not null – запрещает ввод пустых значений;
Check (<dom_condition>) – задает ограничение (описание контроля данных при вводе и изменении).
Для задания условия используются следующие ключевые слова: value (подразумевает значение, вводимое в поле), is null/is not null, between … and …, like, in, а также все арифметические и логические операторы.
Домены создаются независимо друг от друга, следовательно, check в домене не может ссылаться ни на какой другой домен или столбец таблицы.
Домен может иметь только одну конструкцию check.
Конструкция check не может быть переопределена при описании атрибута таблицы. Если при описании поля таблицы, ссылающегося на домен, имеющий check, указано свое ограничение, то действовать будут оба ограничения.
Примеры:
Create_domain username as varchar (20)
Default user;
Create domain month as smallint
Check (value between 1 and 12);
Create domain d_elem as char (2)
Check (value in (‘Au’, ‘Ag’, ‘Pr’, ‘Pd’, ‘Os’));
Create domain pveight as numeric (12,2)
Default null check ((value is null) or (value >125));
Удаление домена
Удаление домена осуществляется командой drop domain, если домен используется в какой-либо таблице, то удалить его нельзя.
Синтаксис:
Drop domain name_doman;
Запуск MySQL
Управление сервером обычно осуществляется из командной строки. Для входа на сервер используется команда mysql-u root, которая не имеет пароля. Изначально существует единственный пользователь, которому предоставляется право входа.
Первое, что нужно сделать – войти под именем root и установить для него пароль. Команда mysql может использовать следующие опции, представленные в таблице 1.
Таблица 1 – Опции команды mysql
| Опция | Описание |
| -?,--help | Справка |
| -h,--hostname=[hostname] | Имя сервера mysql. |
| -u,--user=[user] | Имя пользователя для доступа к mysql. |
| -p,--password=[password] | Пароль пользователя для доступа к mysql. |
| -P,--port=[port] | Порт для соединения с сервером. |
| -V,--version | Информация о версии |
Если на экране выведено окно, представленное на рисунке 1, то вход в базу данных mysql, которая используется для администрирования сервера, осуществлен.
| mysqlbin>mysql -u root mysql Welcome to the MySQL monitor.Commands end with ; or g. Your MySQL connection id is 1 to server version: 3.23.19 Type 'help' for help. mysql> |
Рисунок 1 – Вход в БД MySql
Наличие в статусной строке иконки светофора с активным зеленым цветом также указывает на то, что сервер запущен (см. рисунок 2).
Рисунок 2 - Приложение winmysqladmin запущено
В ней содержатся 5 таблиц, называемых таблицами привилегий. Для ввода пароля необходимо ввести команды, представленные на рисунке 3.
| mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; mysql>quit mysqlbin>mysqladmin -u root reload |
Рисунок 3 –Установка нового пароля
После установки пароля для root нужно перезагрузить сервер командой mysqladmin reload, чтобы изменения вступили в силу. После этого можно попробовать войти снова.
Если получено приглашение mysql monitor, значит все правильно работает. Можно начинать настраивать таблицы привилегий, вводить новых пользователей, создавать базы данных и таблицы.
Таблицы привилегий
Таблицы привилегий используются для предоставления доступа к базам данных и таблицам в них пользователям.
Для просмотра таблиц, необходимо ввести команду, представленную на рисунке 4.
mysql>show tables;
|
Рисунок 4 – Таблицы привилегий
Кратко рассмотрим функции каждой из таблиц.
User - определяет, разрешено ли пользователю, пытающемуся подключиться к серверу делать это. Содержит имя пользователя, пароль а также привилегии. Для просмотра нужно ввести команду show columns from user.
Изначально эта таблица содержит пользователя root с паролем, который установлен и именем хоста '%'. По умолчанию root может входить с любого хоста, имеет все привилегии и доступ ко всем базам данных. Также в таблице содержится запись для пользователя '%', которую нужно сразу же удалить, так как она предоставляет доступ любому пользователю.
Для добавления нового пользователя нужно выполнить действия, представленные на рисунке 5.
insert into user (host,user,password) values ('%.domain.com','john',password('df456');
insert into user (host,user,password) values ('localhost,'mary',password('kitchen');
select host,user,password from user;
|
Рисунок 5 – Создание нового пользователя
Db -определяет к каким базам данных, каким пользователям и с каких хостов разрешен доступ. В этой таблице можно предоставлять каждому пользователю доступ к базам данных и назначать привилегии. Для просмотра нужно выполнить команду show columns from db;.
По умолчанию, все привилегии установлены в 'N'.
Host используется для расширения диапазона доступа в таблице db. К примеру, если доступ к какой-либо базе данных должен быть предоставлен более чем одному хосту, тогда следует оставить пустой колонку host в таблице db, и внести в таблицу host необходимые имена хостов. для просмотра нужно выполнить команду show columns from host;.
Здесь также можно задавать привилегии для доступа к базе данных.Они обычно редко используются без необходимости. Все привилегии доступа нужно задавать в таблице db для каждого пользователя, а в таблице host только перечислить имена хостов. Сервер читает все таблицы, проверяет имя пользователя, пароль, имя хоста, имя базы данных, привилегии. Если в таблице db привилегии select, insert установлены в 'Y', а в таблице host в 'N', то в итоге пользователь все равно получит 'Y'.
Эти 3 таблицы являются основными. В новых версиях MySQL, начиная с 3.22 добавлены еще 2 таблицы- tables_priv и columns_priv, которые позволяют задать права доступа к определенной таблице в базе данных и даже к определенной колонке. Они работают подобно таблице db, только ссылаются на таблицы и колонки. Также, начиная с версии 3.22 можно использовать команду GRANT для предоставления доступа к базам данных, таблицам и колонкам таблиц, что избавляет от необходимости вручную модифицировать таблицы db,tables_priv и columns_priv.
Привилегии предоставляемые MySQL представлены в таблице 2.
Таблица 2 - Привилегии, предоставляемые MySQL
| Привилегия | Колонка | Где используется |
| select | Select_priv | таблицы |
| insert | Insert_priv | таблицы |
| update | Update_priv | таблицы |
| delete | Delete_priv | таблицы |
| index | Index_priv | таблицы |
| alter | Alter_priv | таблицы |
| create | Create_priv | БД,таблицы,индексы |
| drop | Drop_priv | БД или таблицы |
| grant | Grant_priv | БД или таблицы |
| references | References_priv | БД или таблицы |
| reload | Reload_priv | администрирование сервера |
| shutdown | Shutdown_priv | администрирование сервера |
| process | Process_priv | администрирование сервера |
| file | File_priv | доступ к файлам на сервере |
В MySQL существуют 3 специальных пользователя: monty,admin и dummy.
Monty - суперпользователь. Может входить на сервер как с локального хоста, так и с любого хоста в сети. Имеет доступ ко всем базам данных и все привилегии, но если root может в первый раз войти без пароля, monty должен использовать пароль и должен быть добавлен в таблицы привилегий вручную.
Аdmin - пользователь, который может входить на сервер с локального хоста без пароля и которому назначаются административные привилегии Reload и Process. Пользователь admin может использовать команды mysqladmin reload, mysqladmin refresh и mysqladmin flush-*,а также mysqladmin processlist.
Доступа к базам данных admin не имеет. Привилегии для доступа к определенным БД должны быть назначены индивидуально или в таблице db, или командой GRANT.
Dummy - пользователь, который может входить на сервер без пароля ,но только с локального хоста. Все глобальные привилегии устанавливаются в 'N' кроме "USAGE",что позволяет dummy создавать пользователей без привилегий. Права доступа к базам данных должны быть заданы root'ом.
Создание баз данных
Создание базы данных в MySQL производится с помощью утилиты mysqladmin. Изначально существует только БД mysql для администратора и БД test, в которую может войти любой пользователь и которая по умолчанию пуста. Для создания БД необходимо ввести команды, представленные на рисунке 6.
| mysqlbin>mysqladmin -u root -p create data1 Enter password:****** Database "data1" created. mysqlbin> |
Рисунок 6 –Создание базы данных
По умолчанию, root имеет доступ ко всем базам данных и таблицам. Перейти в созданную базу данных можно, используя команду mysql (см. рисунок 7).
| mysqlbin>mysql -u root -p data1 Enter password:****** Welcome to MySQL monitor. |
Рисунок 7 – Переход в БД
Теперь можно создавать таблицы и вводить информацию.
Создание и модификация таблиц
Для создания таблицы customers в БД data1 необходимо ввести команды, представленные на рисунке 8.
| mysql>use data1 Database changed. mysql>create table customers ( emp_id int(4) not null auto_increment, emp_name varchar(10) not null, emp_lname varchar(15) not null, address varchar(60) not null, phone int(10), primary key(emp_id)); |
Рисунок 8 – Создание таблицы
Вводить данные в нее можно несколькими способами:
– вручную, используя команду insert into;
– загрузить данные из текстового файла, что является более предпочтительным, особенно если нужно ввести несколько тысяч записей.
– использовать утилиту mysqlimport также для загрузки данных из текстового файла.
Пример ввода данных вручную представлен на рисунке 9.
| mysql>insert into customers (emp_id,emp_name,emp_lname,address,phone) -->values ('1001','John','Walker','New York','1236458794'); или mysql>insert into customers values ('1001','John','Walker','New York','1236458794'); |
Рисунок 9 – Ввод данных в таблицу
Аuto_increment в столбце emp_id, означает, что числовое значение этого столбца будет автоматически увеличиваться на единицу с каждой новой записью. Значение в такой столбец вводится один раз для задания точки отсчета, а дальше сервер будет сам подставлять нужные значения.
Синтаксис команды LOAD DATA INFILE.
DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 't']
[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '' ]]
[LINES TERMINATED BY 'n']
[IGNORE number LINES]
[(col_name,...)]
Предположим, существует некоторый текстовый файл 123.txt,содержащий 2000 записей, которые нужно внести в таблицу. Нужно создать таблицу, имеющую такую же структуру и такое же число полей, как и файл (а также подходящие типы данных). Предположим, что поля в файле разделены запятыми. Кроме того, файл должен находиться в нужной базе данных. Вводим следующую команду:
LOAD DATA INFILE '123.txt' into table customers fields terminated by ',';
Данные из файла помещаются в таблицу.
Индивидуальные задания
5.1 Запустить сервер MySQL. Зарегистрировать своего пользователя в консольном приложении, задать ему права.
5.2 С помощью утилиты Mysqlshow выполнить команду на просмотр структуры и состав таблиц базы Mysql.
5.3 Создать необходимые домены для БД «Заказы», приведенной на рисунке 10.
5.4 Создать базу данных «Заказы», приведенную на рисунке 10 и заполнить ее данными, предварительно, скопировав их в Ms Excel.
Таблица поставщиков (S)
| Hомеp поставщика | Фамилия | Рейтинг | Город |
| S1 | Смит | Лондон | |
| S2 | Джонс | Париж | |
| S3 | Блейк | Париж | |
| S4 | Кларк | Лондон | |
| S5 | Адамс | Афины |
Таблица деталей (P)
| Номер детали | Название | Цвет | Вес | Город |
| P1 | Гайка | Красный | Лондон | |
| P2 | Болт | Зеленый | Париж | |
| P3 | Винт | Голубой | Рим | |
| P4 | Винт | Красный | Лондон | |
| P5 | Кулачок | Голубой | Париж | |
| P6 | Блюм | Красный | Лондон |
Таблица изделий (J)
| Номер изделия | Название | Город |
| J1 | Жесткий диск | Париж |
| J2 | Перфоратор | Рим |
| J3 | Считыватель | Афины |
| J4 | Принтер | Афины |
| J5 | Флоппи-диск | Лондон |
| J6 | Терминал | Осло |
| J7 | Лента | Лондон |
Таблица поставок (SPJ)
| Номер поставщика | Номер детали | Номер изделия | Количество |
| S1 | P1 | J1 | |
| S1 | P1 | J4 | |
| S2 | P3 | J1 | |
| S2 | P3 | J2 | |
| S2 | P3 | J3 | |
| S2 | P3 | J4 | |
| S2 | P3 | J5 | |
| S2 | P3 | J6 |
Рисунок 10 – База данных «Заказы»
Содержание отчета
6.1 Создание нового документа
6.2 Сохранение созданного документа под соответствующим именем в своей рабочей папке
6.3 Демонстрация выполненной работы преподавателю
Контрольные вопросы
7.1 Каким способом возможен запуск серверной части СУБД.
7.2 Что такое привилегия. Каково её предназначение.
7.3 Какие основные утилиты входят в состав СУБД, какие функции они выполняют?
Список литературы
1 Дюбуа, Поль. MySQL/ Поль Дюбуа. 3-е изд. М.:Санкт-Петербург, 2007, с. 38-185