3. Задание на лабораторную работу

1. На сайте http:\www.mysql.com. или у преподавателя взять дистрибутив MySQL5.6.

2. Установить СУБД MySQL5.6 в соответствии с инструкцией, описанной в методических указаниях.

3. Произвести настройку конфигурации MySQL5.6.

4. Создать учетную запись нового пользователя, определив его привилегии.

5. Просмотреть список всех пользователей системы.

6. Просмотреть список привилегий созданного пользователя.

7. Удалить права нового пользователя.

8. Удалить нового пользователя.

ЛАБОРАТОРНАЯ РАБОТА №2

1. Создание и работа с базами данных mysql

Создание базы данных в MySQL сводится к созданию нового подкаталога в каталоге данных (например, с:\mysql 5.6\data) и осуществляется с помощью команды GREATE DATABASE (рис.1).

Рис.1. Команда создания базы данных

В результате в каталоге, который был указан при установке в опции Data Path (например, C:\ ProgramData\MySQL\Mysql Server 5.6\) появится новый каталог «тест». Максимальная длина имени базы данных 64 знака.

Проконтролировать создание базы данных, а также узнать имена существующих баз данных можно при помощи оператора SHOW DATABASES (рис.2).

Рис.2. Просмотр созданных баз данных

В этом списке присутствуют две базы – information_schema и mysql, которые являются служебными и необходимы для поддержания сервера MySQL в работоспособном состоянии. В них хранится информация об учетных записях, настройках и т.д.

Удаление базы данных производится командой DROP DATABASE (рис.3).

Рис.3. Удаление базы данных

Если производится попытка создания уже существующей базы, то выводится сообщение об ошибке (рис.4).

Рис. 4. Cообщение об ошибке

Если такие ошибки нежелательны, то для их предотвращения оператор GREATE DATABASE дополняется конструкцией IF NOT EXISTS. В этом случае база создается, если она не существует, а если существует, то никакие действия не производятся и ошибка не выдается.

Для переименования базы данных в версиях MySQL начиная с 5.1.7 и заканчивая 5.1.22 используется оператор RENAME DATABASE, который имеет следующий синтаксис:

RENAME DATABASE (имя базы) TO (новое имя базы);

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

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

Рис.5. Выбор базы данных для работы

В MySQL подаваемые команды должны заканчиваться точкой с запятой. Исключением являются команды USE и QUIT (выход), после них точка с запятой может ставиться, а может нет. Отличие команды USE от остальных заключается также в том, что она должна задаваться одной строкой.

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

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

Таблица 1

SQL-команды для работы с таблицами

Название команды		Назначение команды
CREATE TABLE		Создает новую таблицу
DESCRIBE, SHOW FULL COLUMNS FROM		Показывает структуру созданных таблиц
ALTER TABLE		Производит изменение структуры таблицы
DROP TABLE		Удаляет одну или несколько таблиц
INSERT INTO … VALUES		Вставляет новые записи в существующую таблицу
DELETE		Удаляет записи
SELECT	Извлекает данные
SHOW	Команда мониторинга таблиц
UPDATE	Команда обновления столбцов

Рассмотрим подробно синтаксис и примеры использования этих команд.

Создание таблиц в базе данных производится командой CREATE TABLE, которая в общем виде имеет следующий синтаксис (параметры в квадратных скобках указываются по необходимости):

GREATE [TEMPORARY] TABLE [IF NOT EXISTS] имя_таблицы [(спецификация_1, спецификация_2,…)] [параметры_тaблицы] [добавление_значений]

Параметр TEMPORARY используется для создания временной таблицы с именем имя_таблицы в течении только текущего сценария, по окончании которого она удаляется.

Если указан параметр IF NOT EXISTS и производится попытка создать таблицу с уже существующем в этой базе именем, то таблица создана не будет.

Параметр спецификация_n определяет внутреннюю структуру создаваемой таблицы (названия и типы полей, ключи, индексы и т.д.). Возможные синтаксисы параметры спецификация_n представлены в таблице.

Таблица 2

Синтаксис параметра спецификация_n

имя_столбца type [NOT NULL|NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition]

PRIMARY KEY (имя_индексированного_столбца_1, имя_индексированного_столбца_2,..)

KEY [index_name] (имя_индексированного_столбца_1, имя_индексированного_столбца_2,..)

INDEX [index_name] (имя_индексированного_столбца_1, имя_индексированного_столбца_2,..)

INIQUE [INDEX] [index_name] (имя_индексированного_столбца_1, имя_индексированного_столбца_2,..)

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

Продолжение таблицы 2

[CONSTANT symbol] FOREIGN KEY [index_name] (имя_индексированного_столбца_1, имя_индексированного_столбца_2,..) [reference_definition]

CHECK (expr)

Параметр имя_столбца задает имя столбца в создаваемой таблице.

Параметр type задает тип данных для столбца имя_столбца. Возможные значения этого параметра представлены в таблице. Чем больше диапазон значений у типа данных, тем больше памяти он занимает. Правильный выбор типа данных позволяет сэкономить место для хранения этих данных. Различные типы данных MySQL представлены в табл.3.

Таблица 3

Типы данных в MySQL

Тип данных	Диапазон значений и описание
Числовые типы данных
TINYINT(M)	От -128 до 127 или от 0 до 255. Целое число. Может быть объявлено положительным с помощью ключевого слова UNSIGNED, тогда элементам столбца не может быть присвоено отрицательное значение. М - необязательный параметр - количество отводимых под число символов
SMALLINT(M)	Тоже, что и TINYINT(M), но диапазон от -32768 до 32767 или от 0 до 65535
MEDIUMINT(M)	Тоже, что и TINYINT(M), но диапазон от -8388608 до 8388607 или от 0 до 16777215

Продолжение таблицы 3

Тип данных	Диапазон значений и описание
INT(M) или INTRGER(M)	Тоже, что и TINYINT(M), но диапазон от -2147683648 до 2147683648 или от 0 до 4294967295
BIGINT(M)	Тоже, что и TINYINT(M), но диапазон от -263 до 263-1 лил от 0 до 264
BOOL BOOLEAN	Либо 0 (ложь), либо 1 (истина)
DECIMAL(M,D) DEC(M,D) NUMERIC(M,D)	Диапазон зависит от параметров M и D. Используется для величин повышенной точности, например, для денежных данных. М – количество отводимых под число символов (максимум 64). D – количество знаков после запятой (максимум 30). Например, DECIMAL(5,2) будет хранить числа от -99,99 до 99,99
FLOAT(M,D)	Вещественное число (с плавающей точкой).
DOUBLE(M,D)	Аналогично FLOAT, но с большим диапазоном
Строковые типы данных
CHAR(M) VARCHAR(M)	Позволяет хранить строку фиксированной длины М. Значение М от 0 до 65535
BLOB, TEXT	Позволяют хранить большие объемы текста. TEXT используется для хранения текста, а BLOB – для хранения изображений, звука и т.д. Максимальный размер 216-1 символов.
MEDIUMBLOB, MEDIUMTEXT	Аналогично предыдущему, но максимальный размер 224-1 символов.
LONGBLOB, LONGTEXT	Аналогично предыдущему, но максимальный размер 232-1 символов.

Продолжение табл.3

Тип данных	Диапазон значений и описание
ENUM(‘знач1’, (‘знач2’,.., ‘значN’)	Максимальный размер 65535 элементов. Строки этого типа могут принимать только одно из значений указанного множества.
SET(‘знач1’, (‘знач2’,.., ‘значN’)	Максимальный размер 64 элементов. Строки этого типа могут принимать любой или все элементы из значений указанного множества.
Календарные типы данных
DATE	Предназначен для хранения даты. В качестве первого значения указывается год в формате «YYYY», через дефис – месяц в формате «MM», потом день в формате «DD»
TIME	Предназначен для хранения времени суток. Значение хранится в формате hh:mm:ss часы-минуты-секунды.
DATATIME	Предназначен для хранения даты и времени суток. Значение вводится и хранится в формате YYYY- MM- DD hh:mm:ss.
TIMESTAMP	Предназначен для хранения даты и времени суток в виде количества секунд, прошедших с полуночи 1 января 1970 года (начало эпохи UNIX)
YAER(M)	Предназначен для хранения года. М задает формат года. Если этот параметр не указан, то по умолчанию он равен 4.

Параметр [NOT NULL|NULL] указывает, может ли данный столбец содержать значение NULL или нет.

Параметр [DEFAULT default_value] задает значение по умолчанию для данного столбца. Если привставке новой записи в таблицу значение для поля имя_столбца укзано не было, то устанавливается значение default_value.

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

На рис.6 представлен пример создания таблицы client, содержащей три поля:

- id – уникальный идентификатор записи;

- name – имя клиента;

- age – возраст клиента.

Рис.6. Пример создания таблицы

Указание на то, какое поле будет первичным ключом таблицы можно прописывать непосредственно при описании столбца (рис.7).

Рис.7. Указание первичного ключа

Пример заполнения таблицы с помощью команды INSERT представлен на рис.8. Описание и синтаксис команды будет рассмотрен далее.

Рис. 8. Заполнение таблицы данными

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

Рис.9. Созданная таблица

В некоторых типах таблиц существует возможность задавать параметр [AUTO_INCREMENT] для вторичного столбца составного ключа. В этом случае ключом записи (его уникальным значением) будет являться значение сразу двух полей. При этом первое поле является префиксом, а второе – уникальным числовым значением для этого префикса. Эту особенность удобно использовать при необходимости добавления данных по группам. Например, необходимо расширить рассмотренную ранее таблицу, чтобы была возможность сгруппировать клиентов некоторого предприятия по отделам. Для этого добавим в таблицу еще одно поле – category – название отдела. Пример создания таблицы представлен на рис.10.

Рис. 10. Пример задания составного ключа

Вставим в таблицу новые записи (рис. 11).

Рис.11. Заполнение таблицы

На рис. 12 представлены все записи в таблице client, упорядоченные по содержимому полей id и category.

Рис.12. Упорядоченная таблица

Параметр PRIMARY KEY задает первичный ключ таблицы. Все значения столбца, помеченного как первичный ключ, не должны содержать значения NULL. Если при создании таблицы первичный ключ явно указан не был, а приложение его запрашивает, то MySQL автоматически устанавливает первый столбец с параметром UNIQUE, если во всех значениях этого столбца нигде не встречается значение NULL. В качестве первичного ключа можно задать как один, так и несколько столбцов (рис.10).

PRIMARY KEY (столбец_1, столбец_2,…),

Только в этом случае ни один, ни другой столбец не может быть первичным, т.е. не может быть описан:

PRIMARY KEY (столбец_1), PRIMARY KEY (столбец_2)

Поля PRIMARY KEY являются проиндексированными.

Параметр KEY является синонимом к INDEX.

Параметр INDEX задает поля, которые будут проиндексированы. Индексация полей способствует увеличению скорости работы команды SELECT (по аналогии с поиском нужной информации в книге с оглавлением и без оглавления). Недостатком в этом случае является существенное увеличение размера базы данных. В MySQL проиндексированы могут быть поля любого типа. Для ускорения работы в полях типа char и varchar можно индексировать только несколько первых символов. Для полей типа text и blob такое частичное индексирование является обязательным (поля этих типов могут быть индексированы только в таблицах типа MylSAM). Не все типы таблиц допускают, чтобы проиндексированное поле имело значение NULL. Чтобы избежать ошибок, рекомендуется таким полям всегда назначать значение NOT NULL. Если параметр index_name, который задает имя индекса, не указан, то индексу присваивается мя первого индексируемого столбца.

Например, создадим таблицу client с полями name и age и проиндексируем ее по полю name по первым 5 буквам:

Рис. 13. Создание проиндексированной таблицы

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

Рис.14. Сведения об индексах

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

Например, в таблице client значения столбца number_contract (номер контракта) должны быть уникальными (рис.15).

Рис.15. Задание уникального значения столбца

Тогда при попытке записи одинакового номера контракта в несколько полей выдается сообщение об ошибке (рис.16).

Рис. 16. Сообщение об ошибке

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

Ключи FOREIGN KEY и CHECK введены для совместимости при переносе кода с других SQL-баз данных при запуске приложений, работающих со ссылками.

Параметр параметры_таблицы задает дополнительные параметры создаваемой таблицы, представленные в табл.4.

Таблица 4

Дополнительные параметры таблицы

Опция	Возможные значения	Назначение опции
Type	{BDB|HEAP|ISAM|innoDB|MERGE|MRG_MYISAM| MYISAM}	Задает тип создаваемой таблицы. Возможные типы таблиц: BDB – таблицы с поддержкой транзакций и блокировкой страниц HEAP – данные этой таблицы хранятся только в памяти ISAM – оригинальный обработчик таблиц InnoDB - таблицы с поддержкой транзакций и блокировкой строк MERGE, MRG_MYISAM – набор таблиц MYISAM, используемый как одно таблица MYISAM – обработчик, обеспечивающий переносимость таблиц в бинарном виде (заменяет ISAM)
AUTO-INCREMENT	-	Устанавливает для данной таблицы следующее значение AUTO-INCREMENT
AVG_ROW_LENGTH	-	Приближенное значение средней длины строки для данной таблицы (для больших таблиц с записями переменной длины)
CHECKSUM	{0|1}	Следует установить значение 1, чтобы поддерживалась проверка контрольной суммы для всех строк (для нахождения поврежденных таблиц)

Продолжение табл.4

Опция	Возможные значения	Назначение опции
COMMENT	“string”	Комментарий для данной таблицы длиной 60 символов
MAX_ROWS	-	Максимальное число строк, которое предполагается хранить в данной таблице
MIN_ROWS	-	Минимальное число строк, которое предполагается хранить в данной таблице
PAC_KEYS	{0|1|DEFAULT}	Установка в 0 отключает уплотнение ключей, в 1 – получает меньший индекс. При установке в DEFAULT обработчик таблиц будет уплотнять только длинные столбцы char|varchar.
PASSWORD	“string”	Шифрует файл ‘.frm’ c помощью пароля (не функционирует в стандартной версии MySQL)
DELAY_KEY_WRITE	{0|1}	Установка в 1 задерживает операции обновления ключей, пока не закроется указанная таблица
ROW_FORMAT	{default|dynamic|fixed| compressed}	Определяет, каким образом должны храниться строки

Продолжение табл.4

Опция	Возможные значения	Назначение опции
RAID_TYPE	{1|STRIPED|RAID0}	Разбивает файл данных на участки, чтобы преодолеть лимит файловой системы, не поддерживающей большие файлы
UNION	{table_name, [table_name…]}	Используется в случае необходимости использования совокупности идентичных таблиц, как одну таблицу (для таблиц типа MERGE)
INSERT_METHOD	{NO|FIRST|LAST}	Для внесения данных в таблицы типа MERGE
DATA DIRECTORY	“абсолютный путь к каталогу”	Указывают, где обработчик таблиц должен помещать табличные и индексные файлы
INDEX DIRECTORY	“абсолютный путь к каталогу”

Параметр добавление_значений добавляет к создаваемой таблице поля и значения, полученные в результате работы команды SELECT. Создадим таблицу client и заполним ее данными. Создадим таблицу client1, содержащую один столбец number_contract (по умолчанию значение 1234) и присоединим к ней все столбцы таблицы client. Результат представлен на рис.17.

Рис.17. Присоединение столбцов к таблице

Чтобы посмотреть все таблицы, созданные в текущей базе, необходимо использовать SQL-команду SHOW TABLES (рис.18).

Рис.18. Просмотр всех таблиц базы данных

Для того чтобы посмотреть структуру созданных таблиц, используется команда DISCRIBE (рис.19).

Рис. 19. Просмотр структуры созданных таблиц

Более полное описание структуры таблицы, включающее права доступа и комментарии, можно получить с помощью команды, показанной на рис.20.

Рис.20. Полное описание структуры таблицы

Изменение структуры таблицы осуществляется с помощью команды ALTER TABLE. Она позволяет добавлять и удалять столбцы, создавать и уничтожать индексы, переименовывать столбцы и саму таблицу.

Основные преобразования, выполняемые оператором ALTER TABLE, представлены в табл.5.

Таблица 5

Преобразования, выполняемые оператором ALTER TABLE

Синтаксис команды		Описание команды
ADD название_нового_столбца тип_данных_столбца [FIRST|AFTER имя_соседнего_столбца]		Добавление нового столбца «название_нового_столбца». Необязательная конструкция FIRST добавляет новый столбец перед столбцом имя_соседнего_столбца. Необязательная конструкция AFTER добавляет новый столбец после столбца имя_соседнего_столбца. Если место добавления не указано, то по умолчанию столбец добавляется в конец таблицы.
ADD INDEX [имя_индекса] (имя_столбца,…)		Добавление индекса имя_индекса для столбца имя_столбца. Если имя индекса не указано, ему присваивается имя, совпадающее с именем индексируемого столбца
ADD PRIMARY KEY (имя_столбца,…)		Делает столбец имя_столбца или группу столбцов первичным ключом таблицы
CHANGE старое_имя новое_имя тип		Изменение столбца с именем старое_имя на столбец с именем новое_имя и типом данных тип
DROP имя_столбца		Удаление столбца
DROP PRIMARY KEY	Удаление первичного ключа
DROP INDEX имя_индекса	Удаление индекса имя_индекса

Например, добавим в таблицу client столбец address с типом данных char(30) после столбца name и посмотрим результат командой DISCRIBE (рис.21).

Рис.21. Добавление столбца в таблицу

Переименуем один из столбцов таблицы и изменим диапазон данных (рис.22).

Рис. 22. Переименование столбца таблицы

Удалим один из столбцов таблицы, как показано на рис.23.

Рис.23. Удаление столбца таблицы

Удаление всей таблицы или нескольких таблиц осуществляется с помощью команды DROP TABLE. В случае нескольких таблиц их имена указываются через запятую (рис.24)

Рис. 24. Удаление таблиц