УСТАНОВКА SQL-СЕРВЕРА, ПОДКЛЮЧЕНИЕ КЛИЕНТСКОЙ ПРОГРАММЫ

Лабораторная работа 1

УСТАНОВКА SQL-СЕРВЕРА, ПОДКЛЮЧЕНИЕ КЛИЕНТСКОЙ ПРОГРАММЫ

Цель работы: формирование умений установки на персональный компьютер и конфигурировать сервер баз данных MySQL и закрепление знаний, умений и навыков создания объектов БД.

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

Следующий этап настройки базы данных связан с созданием таблиц.

В СУБД MySQL существует несколько типов таблиц. Основные из них: MyISAM для быстрого чтения, InnoDB для транзакций и ссылочной целостности.

Это делается с помощью SQL-команды

CREATE TABLE tablename(columns)

Columns – разделяемый запятыми список столбцов в таблице. Каждый столбец должен иметь имя, за которым следует тип данных.

create table student

( idStudent int unsigned not null auto_increment primary key,

name char(50) not null,

address char(100) not null,

city char(30) not null,

date date not null

) ENGINE=InnoDB;

Каждая таблица создается с помощью отдельного оператора create table. Определение столбца содержит его имя, за которым следует тип данных. В определениях некоторых столбцов присутствуют и другие спецификаторы. Например составной ключ можно создать путем написания строки primary key(fiel1, field2.. fieldN) в конструкции create table.

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

describe dbname;

Практическая часть

Порядок выполнения работы

1) Установить сервер баз данных MySQL на компьютер.

2) Выполнить первичную настройку программы.

3) Проверить работоспособность сервера MySQL.

4) Запустить программу-клиент mysql.exe и подключиться к серверу.

5) Вывести и записать список существующих БД.

6) В зависимости от варианта создать требуемые таблицы.

7) Найти на диске в директории баз данных MySQL каталог, содержащий созданную БД.

3. Контрольные вопросы

1) Пояснить термин «реляционная база данных».

2) Где хранятся настройки программы СУБД MySQL?

3) Как можно проверить работоспособность СУБД MySQL?

4) Где хранятся данные СУБД MySQL?

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

УПРАВЛЕНИЕ ДОСТУПОМ К ДАННЫМ. КОПИРОВАНИЕ И ВОССТАНОВЛЕНИЕ БД

Цель работы: формирование умений и навыков управления доступом к данным; формирование умений копирования и восстановления БД.

Краткие теоретические сведения

Управление доступом к данным

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

Система привилегий MySQL идентифицирует пользователя по имени и сетевому адресу, с которого осуществляется подключение. При поступлении каждого запроса сервер MySQL проверяет, достаточно ли у пользователя привилегий для его выполнения. Например, при считывании строк из таблицы или удалении таблиц из БД сервер в первом случае проверяет, имеется ли у пользователя для этой таблицы привилегия SELECT, а во втором – имеется ли у него для этой базы привилегия DROP.

При определении прав пользователей сервер использует данные таблиц user, db и host из БД mysql. Каждая из этих таблиц включает в себя поля контекста и привилегий.

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

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

1.2 Создание пользователей и задание им прав

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

CREATE USER < имя пользователя> [IDENTIFIED BY < пароль> ];

Удаление учетной записи пользователя производится с помощью команды DROP USER:

DROP USER < имя пользователя>;

Создание (добавление) и удаление учетных записей пользователей, а также назначение им привилегий осуществляется командами GRANT и REVOKE соответственно.

Основные привилегии СУБД MySQL

Название привилегии	Допустимое действие
SELECT	Считывание данных из таблиц
INSERT	Вставка данных в таблицы
UPDATE	Изменение записей в таблицах
DELETE	Удаление записей из таблиц
INDEX	Создание или уничтожение индексов
ALTER	Модификация структуры таблиц
CREATE	Создание таблиц и баз данных
GRANT	Предоставление другим пользователям привилегий, которые имеет пользователь
DROP FILE	Удаление баз данных или таблиц Чтение и запись файлов на сервере с помощью команд LOAD INFILE и SELECT OUTFILE

Синтаксис команд GRANT и REVOKE:

GRANT < тип привилегии> [(< столбцы> )] ОN < таблица> TO < имя пользователя> [IDENTIFIED BY < пароль> ]; REVOKE < тип привилегии> [(< столбцы> )] ON < таблица> FROM < имя пользователя>;

Название таблицы при назначении привилегий пользователю может задаваться в формате < название базы данных>.< таблица>. Если указать в качестве названия БД или таблицы символ *, то это будет означать, что привилегия относится ко всем БД или таблицам.

Имена новых пользователей можно добавить, применяя команду GRANT. Учетная запись пользователя admin с паролем somepass, имеющего полный набор привилегий, с правом локального подключения к серверу создается командой: GRANT ALL PRIVILEGES ON *.* TO admin@localhost IDENTIFIED BY 'somepass'; Учетная запись пользователя user с паролем userpass, имеющего привилегии выборки, модификации и вставки данных во всех БД, с правом локального подключения к серверу создается командой: mysql> GRANT SELECT, UPDATE, INSERT ON *.* TO user@localhost IDENTIFIED BY 'userpass';

Чтобы обновить настройки привилегий сервера без перезагрузки применяется команда:

FLUSH PRIVILEGES;

Для проверки работы системы привилегий необходимо подключиться к СУБД под именем созданного пользователя:

c: \> mysql –u admin –p

Параметр –p означает необходимость ввода пароля при подключении к серверу.

Копирование и восстановление БД

Утилита mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

Так же mysqldump имеет возможность развертывания баз данных из созданного sql-файла.

Пример использования mysqldump для создания дампа базы данных «database» при помощи перенаправления потока в файл «database.sql»:

mysqldump -uroot -hlocalhost -p database > database.sql

-u или -- user=... — имя пользователя

-h или --host=... — удаленный хост (для локального хоста можно опустить этот параметр)

-p или --password — запросить пароль

database — имя базы данных

database.sql — файл для дампа

Для того чтобы сделать дамп несколько баз данных, необходимо использовать параметр --databases (или сокращенно -B ), пример:

mysqldump -uroot -hlocalhost -p -B database1 database2 database3 > databases.sql

А для того чтобы сделать дамп всех баз данных, необходимо использовать параметр --all-databases (или сокращенно -A ), пример:

mysqldump -uroot -hlocalhost -p -A > all-databases.sql

Для того, чтобы развернуть дамп, перенаправляем поток в обратную сторону и развертываем базу данных:

mysql -uroot -hlocalhost -p database < database.sql

Или через mysql-console:

mysql> use database;

mysql> source database.sql

Практическая часть

Порядок выполнения работы

1) Создать несколько пользователей. Делегировать пользователям следующие права:

- права системного администратора с возможностью передавать эти права другим пользователям;

- права системного администратора без права передавать эти права другим пользователям;

- права на добавление и выборку записей из всех таблиц;

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

2) Научиться пользоваться командой, которая ликвидирует права.

3) Сохранить резервную копию БД на съемном носителе для дальнейшего использования.

3.Контрольные вопросы

1) Каким образом можно ограничить доступ пользователям к БД?

2) Какие виды ограничений бывают?

3) Каким образом можно ликвидировать права пользователей?

Лабораторная работа 3

Индексы и обзоры.

Цель работы: получить практические навыки работы с индексами, оптимизации работы запросов. Научиться создавать обзоры.

Создание индекса

Общий вид создания индекса:

CREATE [UNIQUE|FULLTEXT] INDEX index_name

ON tbl_name (col_name[(length)],... )

Обычно все индексы создаются в таблице во время создания самой таблицы командой CREATE TABLE.

CREATE INDEX дает возможность добавить индексы к существующим таблицам.

Также индекс можно создать следующей командой:

ALTER TABLE tbl_name ADD INDEX (field)

где field – наименование поля.

Рассмотрим пример. Пусть имеется таблица Student с полями idStudent (PK) и name.

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

ALTER TABLE student ADD INDEX (name(5));

В данном примере 5 – длина индекса. Здесь не следует пренебрегать данным параметром во избежание экономии памяти, т.к. чем больше длина индекса, тем больше места занимает таблица на жестком диске. Тем более меньшая длина индекса гораздо повысит скорость операции INSERT.

Проверяем созданный индекс:

SHOW INDEX FROM student;

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

В СУБД MySQL удаление индексов производится операторами DROP INDEX или ALTER TABLE. Удаление первичных ключей (индексов PRIMARY KEY) осуществляется только с помощью оператора ALTER TABLE. Операторы удаления индексов имеют следующий синтаксис:

DROP INDEX < index_name> ON < bl_name>;

ALTER TABLE < tbl_name> DROP INDEX < index_name>;

Оператор для удаления индекса PRIMARY KEY имеет синтаксис:

ALTER TABLE < tbl_name> DROP PRIMARY KEY;

Если такой индекс с именем PRIMARY KEY создан не был, а таблица имеет один или несколько индексов UNIQUE, будет удален первый из них.

Удаление столбцов из таблицы влияет на индексы. Удаляя столбец из таблицы, вы тем самым удаляете этот столбец из индекса. Удаляя все индексируемые столбцы из таблицы, вы удаляете весь индекс.

Оператор EXPLAIN

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

Оператор EXPLAIN можно применять двумя способами:

1) EXPLAIN tbl_name; (идентично describe tbl_name; )

2) Применение оператора EXPLAIN перед SELECT запросами.

Рассмотрим второй способ. Простейший пример использования:

EXPLAIN SELECT * FROM student WHERE idStudent = 2;

В этом примере производится выборка данных студента на основе его идентификатора (idStudent). Вот то, что мы имеем в результате выполнения запроса EXPLAIN:

Id: Идентификатор (ID) таблицы в запросе. EXPLAIN создает по одной записи для каждой таблицы в запросе.

Select_type: simple. Возможные значения: SIMPLE, PRIMARY, UNION, DEPENDENT UNION, SUBSELECT, и DERIVED. Это тип запроса.

· SIMPLE – обычный тип запроса SELECT.

· PRIMARY – внешний (первый) запрос, в котором используются подзапросы и соединения.

· UNION – второй или последний запрос в соединении

· DEPENDENT UNION - второй или последний запрос в соединении, зависящий от первичного запроса

· SUBQUERY – внутренний подзапрос.

· DEPENDENT SUBQUERY – внутренний подзапрос, зависящий от первичного запроса

· DERIVED – подзапрос, использованный в выражении FROM.

Table. Имя таблицы, из которой MySQL читает данные.

Type. Тип объединения, которое использует MySQL. Возможные значения: eq_ref, ref, range, index, или all. (const – таблица в запросе считывается только однажды)

Possible_keys. Список индексов (или NULL, если индексов нет), которые MySQL может использовать для выборки рядов в таблице.

Key. Название индекса, который использует MySQL (после проверки всех возможных индексов).

Key_len. Размер ключа в байтах.

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

Rows. Количество рядов, которые MySQL необходимо проверить, для обработки запроса(! )

Extra. Дополнительная информация о запросе.

Этот пример достаточно прост. Мы производим поиск по первичному ключу (idStudent) и может быть только одна запись, которая подойдет нашим условиям (переменная rows равна 1).

Рассмотрим более расширенный пример. Создадим еще одну таблицу address, содержащую место проживания студентов.

EXPLAIN SELECT *

FROM student

INNER JOIN ADDRESS

ON student.idStudent = address.user

WHERE student.idStudent = 1;

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

Добавим индекс во вторую таблицу для поля user.

ALTER TABLE address ADD INDEX (user);

Наблюдаем результат:

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

Представления (обзоры)

Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базы данных, определенного с помощью оператора SELECT, в момент обращения к представлению. Представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу.

Для создания представления используется оператор CREATE VIEW, имеющий следующий синтаксис:

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

где view_name - имя создаваемого представления. select_statement - оператор SELECT, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении

Оператор CREATE VIEW содержит 4 необязательные конструкции:

1) OR REPLACE - при использовании данной конструкции в случае существования представления с таким именем старое будет удалено, а новое создано.

2) ALGORITM - определяет алгоритм, используемый при обращении к представлению. UNDEFINED – автоматический выбор (по умолчанию).

3) column_list - задает имена полей представления.,

4) WITH CHECK OPTION - при использовании данной конструкции все добавляемые или изменяемые строки будут проверяться на соответствие определению представления. В случае несоответствия данное изменение не будет выполнено. Обратите внимание, что при указании данной конструкции для необновляемого представления возникнет ошибка и представление не будет создано.

При создании представления основанного на нескольких таблицах возможна ситуация повторения представления. Например:

CREATE VIEW v AS SELECT a.id, b.id FROM a, b;

Поэтому необходимо использовать псевдонимы имен:

CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a, b;

Для просмотра содержимого представления используется оператор SELECT (полностью аналогично как в случае простой таблицы).

SELECT * FROM v;

Практическая часть

1) Составить сложные запросы для 3 или более таблиц с использованием INNER JOIN.

2) Проанализировать запросы с помощью оператора EXPLAIN.

3) Оптимизировать запросы добавлением индексов.

4) Зафиксировать результаты.

5) Создать обзоры по БД вашего варианта

3. Контрольные вопросы

1) Для чего необходимо обеспечивать уникальность значений ключевых полей?

2) Перечислите виды индексов.

3) Для чего используется оператор EXPLAIN?

4) Что такое обзор?

5) Почему рекомендуется создавать псевдонимы имен в обзорах?

ЛАБОРАТОРНАЯ РАБОТА 4

Синтаксис хранимого кода

Для использования множественных инструкций необходимо, чтобы была возможность посылать строки запросов, содержащие разделитель операторов;. Добиться этого можно путем применения команды DELIMITER в командной строке клиента mysql. Замена завершающего запрос разделителя; (например, на разделитель $$) позволяет использовать; в теле процедуры.

Хранимый код может включать множественные инструкции, благодаря составному оператору BEGIN…END, который и определяет блок инструкций.

[< метка начала>: ] BEGIN

[< блок инструкций> ]

END [< метка конца> ]

Значения < метка начала> и < метка конца>, если оба заданы, должны быть одинаковыми. Метка позволяет не путать операторы, если они вложены.

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

DECLARE < имя переменной> [,...] type [DEFAULT < значение> ]

Эта инструкция используется, чтобы объявить внутренние переменные, переменные, которые определяются для процедур, функций, триггеров. Чтобы задать значение по умолчанию для переменной, надо указать DEFAULT. Значение может быть определено как выражение, оно не обязательно должно быть константой. Если DEFAULT отсутствует, начальное значение NULL, type, здесь, подобен стандартным типам данных.

Пример:

DECLARE a INT DEFAULT 5;

Курсоры – это указатели на ресурс выборки. Курсоры существуют только в хранимой процедуре или функции. Курсоры довольно медленные, поэтому использовать их стоит по необходимости. Создаются курсоры при помощи оператора DECLARE:

DECLARE < имя курсора> CURSOR FOR < select выражение>

Эта инструкция объявляет курсор. Каждый курсор должен иметь уникальное имя. < select выражение> – SELECT, не может иметь оператор INTO.

Инструкция OPEN открывает предварительно объявленный курсор:

OPEN < имя курсора>

Инструкция FETCH выбирает следующую строку (если строка существует), используя определенный открытый курсор, и продвигает указатель курсора:

FETCH < имя курсора> INTO < имя переменной> [, < имя переменной> ]...

Если больше нет доступных строк, происходит условие No Data. Чтобы обнаружить это условие, можно установить обработчик для этого:

CLOSE < имя курсора>

CLOSE Закрывает предварительно открытый курсор. Если курсор не закрыт, он все равно закроется в конце составной инструкции (обычно по наступлению END), в которой он был объявлен.

Следует обратить внимание: сначала объявляются переменные, потом курсоры:

CREATE PROCEDURE curdemo()

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE a CHAR(16);

DECLARE b, c INT;

DECLARE cur1 CURSOR FOR SELECT id, data FROM test.t1;

DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;

OPEN cur1;

OPEN cur2;

< блок инструкций>

CLOSE cur1;

CLOSE cur2;

END

Констукция SELECT … INTO так же позволяет «заполнить» переменную, прямо из таблицы:

SELECT < имя столбца> [,...]

INTO < имя переменной> [,...] < sql выражение>

Этот синтаксис SELECT сохраняет выбранные столбцы непосредственно в переменные. Следовательно, количество выбранных столбцов слева от INTO должно быть равно количеству переменных справа от INTO.

Пример:

SELECT id, data INTO x, y FROM test.t1 WHERE id_tm = 5;

Названия переменных не чувствительны к регистру. Важно: имена переменных SQL не должны совпадать с именами столбцов.

В конструкции управления потоком входят IF, CASE, LOOP, WHILE, REPLACE ITERATE и LEAVE. Многие из этих конструкций могут содержат другие инструкции. Такие конструкции могут быть вложены. Например, IF мог бы содержать цикл, который непосредственно содержит WHILE, который в свою очередь включает в себя оператор CASE.

Инструкция IF:

IF < условие> THEN < блок операторов>

[ELSEIF < условие> THEN < блок операторов> ]...

[ELSE < блок операторов> ]

END IF

Инструкция CASE:

CASE < выражение> WHEN < значение>

THEN < блок операторов>

[WHEN < значение> THEN < блок операторов> ]...

[ELSE < блок операторов> ]

END CASE

или

CASE WHEN < условие> THEN < блок операторов>

[WHEN < условие> THEN < блок операторов> ]...

[ELSE < блок операторов> ]

END CASE

В первом варианте обрабатывается < блок операторов> , если < выражение> =< значение> . Во втором – результат для первого указанного условия < условие> , если оно истинно. Если соответствующая величина результата не определена, то обрабатывается < блок операторов> , указанное после оператора ELSE. Если часть ELSE в выражении отсутствует, возвращается NULL.

Пример:

SELECT CASE 1 WHEN 1 THEN " one" WHEN 2 THEN " two" ELSE " more" END;

SELECT CASE WHEN 1> 0 THEN " true" ELSE " false" END;

SELECT CASE BINARY " B" WHEN " a" THEN 1 WHEN " b" THEN 2 END;

Инструкция WHILE:

[< метка начала>: ]

WHILE < условие> DO < блок операторов>

END WHILE

[< метка конца> ]

Операторный список < блок операторов> внутри инструкции WHILE будет повторятся до тех пор пока < условие> равно true.

Инструкция LEAVE:

LEAVE label

Эта инструкция используется, чтобы из выйти любой указаной конструкции управления потоком данных. Может использоваться внутри BEGIN … END или же конструкций цикла (LOOP, REPEAT, WHILE). Можно сравнить c инструкцией break из С++.

Инструкция REPEAT:

[< метка начала> : ]

REPEAT < блок операторов>

UNTIL < условие>

END REPEAT

[< метка конца> ]

Пример с использование различных конструкций:

CREATE PROCEDURE curdemo()

BEGIN

DECLARE done INT DEFAULT 0; # переменная, определяющая условие выполнения нашего цикла

DECLARE a CHAR(16);

DECLARE b, c INT;

DECLARE cur1 CURSOR FOR SELECT id, data FROM test.t1; # создаём курсоры

DECLARE cur2 CURSOR FOR SELECT id FROM test.t2;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; # статус, когда курсор истощатся

OPEN cur1; # открываем курсор

OPEN cur2;

REPEAT

FETCH cur1 INTO a, b; # забираем циклом данные из курсоров, пока не кончатся данные

FETCH cur2 INTO c;

IF NOT done THEN # пока done 0

IF b < c THEN INSERT INTO test.t3 VALUES (a, b); # записываем в таблицу наименьшее из b и c

ELSE INSERT INTO test.t3 VALUES (a, c);

END IF;

UNTIL done END REPEAT;

CLOSE cur1;

CLOSE cur2;

END

Практическая часть

Порядок выполнения работы

1) В зависимости от варианта создать несколько триггеров для различных таблиц (возможные ситуации включения триггеров предусмотреть по собственному усмотрению).

2) Научиться использовать команду удаления триггеров.

3) В зависимости от варианта создать несколько процедур для различных таблиц (возможные ситуации использования процедур предусмотреть по собственному усмотрению).

3. Контрольные вопросы

1) С какими объектами БД связываются триггеры?

2) Какие виды инструкций активизируют триггер?

3) Каким образом можно удалить триггер?

4) В чем отличие хранимой процедуры от функции

5) Какие конструкции управления потоками данных вы знаете?

Лабораторная работа 1

УСТАНОВКА SQL-СЕРВЕРА, ПОДКЛЮЧЕНИЕ КЛИЕНТСКОЙ ПРОГРАММЫ

12 Следующая ⇒