Практическая работа № 5. Создание сложных запросов на выборку.

 

Практическая работа

При выполнении лабораторной работы необходимо:

· для заданной предметной области построить многотабличный запрос на выборку с использованием объединения;

· для заданной предметной области построить запрос на выборку, содержащий вложенный запрос;

· составить отчет по лабораторной работе.

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

Хранимые процедуры обладают следующими преимуществами.

· Повторное использование кода – после создания хранимой процедуры ее можно вызывать из любых приложений и SQL-запросов.

· Сокращение сетевого трафика – вместо нескольких запросов экономнее послать серверу запрос на выполнение хранимой процедуры и сразу получить ответ.

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

· Простота доступа – хранимые процедуры позволяют инкапсулировать сложный код и оформить его в виде простого вызова.

· Выполнение бизнес-логики – хранимые процедуры позволяют перенести код сохранения целостности БД из прикладной программы на сервер БД. Бизнес-логика в виде храни­мых процедур не зависит от языка разработки приложения.

Создание хранимых процедур. Реализуется оператором

CREATE PROCEDURE имя_процедуры ( [ параметр [, ... ] ] )

[характеристика ...] тело_процедуры

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

[ IN | OUT | INOUT ] имя_параметра тип

Ключевые слова in, out, inout задают направление передачи данных:

· in – данные передаются строго внутрь хранимой процедуры; если параметру с данным модификатором присваивается новое значение, при выхо­де из процедуры оно не сохраняется и параметр принимает значение, которое он имел до вызова;

· out – данные передаются строго из хранимой процедуры, если параметр имеет какое-то начальное значение, то внутри хранимой процедуры это значение во внимание не принимается;

 

· inout – значение этого параметра как принимается во внимание внутри процеду­ры, так и сохраняет свое значение при выходе из нее.

Список аргументов, заключенных в круглые скобки, присутствует всегда. Если аргументы отсутствуют, следует использовать пустой список. Если ни один из модификаторов не указан, считается, что параметр объявлен с ключевым словом in.

Телом процедуры является составной оператор begin ... end, внут­ри которого могут располагаться другие операторы:

[ label: ] BEGIN

statements

END [ label ]

Оператор, начинающийся с необязательной метки label (любое уникальное имя), может заканчиваться выражением end label. Внутри составного оператора begin ... end может находиться другой составной оператор. Если хранимая процедура содержит один запрос, то составной оператор можно не использовать.

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

Пример создания простейшей хранимой процедуры:

Что­бы вызвать хранимую процедуру, необходимо применить оператор call, после кото­рого помещается имя процедуры и ее параметры в круглых скобках:

Рекомендуется избегать использования названий хранимых процедур, совпадающих с именами встроенных функций MySQL. В теле хранимой процедуры можно использовать многострочный комментарий, который начинается с последовательности /* и заканчивается последо­вательностью */ .

Рассмотрим хранимые процедуры с параметрами. Создадим и вызовем процедуру, которая присваивает поль­зовательской переменной @х новое значение:

Через параметр value процедуре передается числовое значение 123456, которое она присваивает пользовательской переменной @х. Моди­фикатор in сообщает, что данные передаются внутрь функции. Проверим корректность работы процедуры:

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

Создадим процедуру numcatalogs(), которая подсчитывает число записей в таблице catalogs базы данных book:

Хранимая процедура numcatalogs() имеет один целочисленный параметр total, в котором сохраняется число записей в таблице catalogs. Осуществляется это при помощи оператора select ... into ... from. В качестве параметра функции numcatalogs() передает­ся пользовательская переменная @а.

Создадим хранимую процедуру catalogname(), которая будет возвращать по первич­ному ключу catID название каталога cat_name. Для этого потребует­ся определить параметр id с атрибутом in, и catalog с атрибутом OUT.

Операторы управления потоком данных. Хранимые процедуры позволяют реализовать сложную логику с помощью операторов ветвления и циклов. Вне хранимых процедур эти операторы применять нельзя. Ветвление программы по условию позволяет реализовать оператор:

IF лог_выражение THEN оператор

[ELSEIF лог_выражение THEN оператор] ...

[ELSE оператор]

END IF ;

Логическое выражение может принимать два значения:

· 0 (ложь);

· значение, отличное от нуля (истина).

Если логическое выражение истинно, то выполняется опера­тор в блоке THEN, иначе выполняется список операторов в блоке else (если блок else имеется). В логических выражениях можно использовать операторы сравнения ( = , >, >=, <> , <, <= ). Логические выражения можно комбинировать с помощью операторов && (И), а также || (ИЛИ). Если в блоках if, elseif и else – два или более операторов, необходимо использовать составной оператор begin ... end.

Множественный выбор позволяет осуществить оператор:

CASE выражение

WHEN значение THEN оператор

[WHEN значение THEN оператор] ...

[ELSE оператор]

END CASE ;

Выражение сравнивается со значениями. Как только найдено соответствие, выполняется соответствующий оператор. Если соответствия не найдены, выполняется оператор, размещенный после ключевого слова else (если оно при­сутствует).

В MySQL имеется несколько операторов, позволяющих реализовать циклы. Первый оператор цикла имеет следующий синтаксис:

[ label: ] WHILE условие DO

операторы

END WHILE [ label ] ;

Операторы выполняются в цикле, пока истинно условие. При каждой итерации условие прове­ряется, и если при очередной проверке оно будет ложным (0), цикл завершится. Если условие ложно с самого нача­ла, то цикл не выполнится ни разу. Если в цикле выполняется более одного оператора, не обязательно заключать их в блок begin ... end, т. к. эту функцию выполняет сам оператор while.

Досрочный выход из цикла обеспечивает оператор:

LEAVE label ;

Оператор прекращает выполнение блока, помеченного меткой label (например, прекращает выполнение цикла по достижении критического числа итераций).

Досрочное прекращение цикла также обеспечивает опе­ратор

ITERATE label ;

В отличие от оператора leave оператор iterate не прекращает выполнение цикла, он лишь выполняет досрочное прекращение текущей итерации. Оператор leave эквивалентен оператору BREAK, а оператор iterate эквивалентен оператору continue в С-подобных языках про­граммирования.

 

Второй оператор цикла имеет следующий вид:

[ label: ] REPEAT

операторы UNTIL условие END REPEAT [label] ;

Условие проверяется не в начале, а в конце оператора цикла. Таким образом, цикл выполняет по крайней мере одну итерацию независимо от условия. Следует отметить, что цикл выполняется, пока условие ложно. Оператор цикла может быть снабжен необязательной меткой label, по которой можно осуществлять досрочный выход из цикла при помощи операторов leave и iterate.

Реализовать бесконечный цикл позволяет оператор

[ label: ] LOOP

операторы END LOOP [ label ] ;

Цикл loop (в отличие от операторов while и repeat) не имеет условий выхода. По­этому данный цикл должен обязательно иметь в составе оператор leave.

Осуществлять безусловный переход позволяет оператор

GOTO label ;

Оператор осуществляет переход к оператору, помеченному меткой label. Это может быть как оператор begin, так и любой из циклов: while, repeat и loop. Кроме того, метка может быть не привязана ни к одному из операторов, а объявлена при помощи оператора

LABEL label ;

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

Удаление хранимых процедур. Для удаления процедур используется оператор

DROP PROCEDURE [IF EXISTS] имя_процедуры ;

Если удаляемой процедуры с таким именем не существует, оператор возвращает ошибку, которую можно подавить, если использовать необязательное ключевое слово if exists.

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

Для объявления такого обработчика предназначен оператор

DECLARE тип_обработчика HANDLER FOR код_ошибки [, ... ] выражение;

Выражение содержит SQL-запрос, который выполняется при срабаты­вании обработчика. Тип обработчика может принимать одно из трех значений:

· continue – выполнение текущей операции продолжается после выполнения опе­ратора обработчика;

· exit – выполнение составного оператора begin ... end, в котором объявлен обработчик, прекращается;

· undo – данный вид обработчика в текущей версии не поддерживается.

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

· sqlstate [value] значение – значение sqlstate является пятисимвольным кодом ошибки в шестнадцатеричном формате (стандарт в SQL); примеры кодов – 'hy000', 'hy001', '42000' и т. д.; один код обозначает сразу несколько ошибок;

· sqlwarning – любое предупреждение MySQL; это ключевое слово позволяет назначить обработчик для всех предупреждений; обрабатываются любые события, для которых код sqlstate начинается с 01;

· not found – любая ошибка, связанная с невозможностью найти объект (таблицу, процедуру, функцию, столбец и т. п.); обрабатываются любые события, для которых код sqlstate начинается с 02;

· sqlexception – ошибки, не охваченные ключевыми словами sqlwarning и not found;

· mysql_error_code – обычные четырехзначные ошибки MySQL, такие как 1020, 1232, 1324 и т. п.;

· именованное условие (см. ниже).

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

DECLARE именованное условие CONDITION FOR код ошибки;

Оператор объявляет именованное условие для кода ошибки. Так, для обрабатываемой ошибки 1062 (23000) – дублирование уникального индекса, оператор может выглядеть следующим образом:

DECLARE 'violation' CONDITION FOR SQLSTATE '23000';

DECLARE 'violation' CONDITION FOR 1062;

Первое объявление охватывает все ошибки со статусом 23000, второй вид ошибок более узкий и включает только дублирование уникального индекса.

Курсоры. Если результирующий запрос возвращает одну запись, поместить результаты в про­межуточные переменные можно с помощью оператора select ... into ... from. Однако результирующие таблицы чаще содержат несколько записей, и использование такой конструкции приводит к воз­никновению ошибки 1172: «Результат содержит более чем одну строку».

Избежать ошибки можно, добавив предложение limit 1 или назначив CoNTiNUE-обработчик ошибок. Однако такая процедура реализует не то поведение, которое ожидает пользователь. Кроме того, существуют ситуации, когда требуется обработать именно многострочную результирующую таблицу.

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

Работа с курсорами происходит по следующему алгоритму:

1. При помощи инструкции DECLARE курсор CURSOR FOR связывается имя курсора с выполняемым запросом.

2. Оператор open выполняет запрос, связанный с курсором, и устанавливает курсор перед первой записью результирующей таблицы.

3. Оператор fetch помещает курсор на первую запись результирующей таблицы и извлекает данные из записи в локальные переменные хранимой процедуры. По­вторный вызов оператора fetch приводит к перемещению курсора к следующей записи, и так до тех пор, пока записи в результирующей таблице не будут исчерпа­ны. Эту операцию удобно осуществлять в цикле.

4. Оператор close прекращает доступ к результирующей таблице и ликвидирует связь между курсором и результирующей таблицей.

 

Практическая работа № 6. Создание хранимых процедур.

 

Практическая работа

При выполнении лабораторной работы необходимо:

· для заданной предметной области написать две хранимые процедуры и включить их в БД;

· составить отчет по лабораторной работе.

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

 

 

1. Создадим хранимую процедуру, которая выводит число заказов покупателя по вводимому в качестве параметра процедуры коду покупателя.

Параметр total является выходным, его значение равно числу заказов покупателя, код которого записывается во входной параметр user_kod.Процедура считает все строки, где код клиента совпадает с параметром user_kod.

До вызова процедуры присваиваем параметру процедуры значение кода клиента. Затем вызываем процедуру оператором CALL. Для вывода результата можно воспользоваться оператором SELECT.

2. Создадим хранимую процедуру, которая записывает в новую таблицу fevral все заказы, сделанные в феврале 2009 г. Предварительно необходимо создать новую пустую таблицу fevral со структурой, аналогичной структуре таблицы orders.

Хранимая процедура ord_fevr ( ) использует курсор curf, который в цикле читает данные из таблицы orders и добавляет их в таблицу fevral.

Вызов процедуры осуществляется оператором call. Для просмотра результата выполнения процедуры используем полную выборку из таблицыfevral.

 

 

Практическая работа № 7 Создание триггеров

 

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

Рассмотрим следующие вопросы:

· понятие триггера;

· создание триггеров с помощью оператора CREATE trigger;

· удаление триггеров с помощью оператора DROP trigger.

Триггер – эта та же хранимая процедура, но привязанная к событию изменения со­держимого конкретной таблицы.

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

· insert – вставка новых данных в таблицу;

· delete – удаление данных из таблицы;

· Update – обновление данных в таблице.

Например, при оформлении нового заказа, т. е. при добавлении новой записи в таблицу orders, можно создать триггер, автоматически вычитающий число заказанных товарных позиций в таблице books.

Создание триггеров

Создать новый триггер позволяет оператор:

CREATE TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt ;

Оператор создает триггер с именем trigger_name, привязанный к таблице tbl_name. Не допускается привязка триггера к временной таблице или представлению. Конструкция trigger_time указывает момент выполнения триггера и может прини­мать два значения:

 

· before – действия триггера производятся до выполнения операции изменения таблицы;

· after – действия триггера производятся после выполнения операции изменения таблицы.

Конструкция trigger_event показывает, на какое событие должен реагировать триггер, и может принимать три значения:

· insert – триггер привязан к событию вставки новой записи в таблицу;

· update – триггер привязан к событию обновления записи таблицы;

· delete – триггер привязан к событию удаления записей таблицы.

Для таблицы tbl_name может быть создан только один триггер для каждого из со­бытий trigger_event и момента trigger_time. Таким образом, для каждой из таблиц мо­жет быть создано всего шесть триггеров.

Конструкция trigger_stmt представляет тело триггера – оператор, который необ­ходимо выполнить при возникновении события trigger_event в таблице tbl_name.

Если требуется выполнить несколько операторов, то необходимо использовать составной оператор begin ... end. Синтаксис и допустимые операторы такие же, как и у хранимых процедур. Внутри составного оператора begin ... end допускаются все специфичные для хранимых процедур операторы и конструкции:

· другие составные операторы begin ... end;

· операторы управления потоком (if, case, while, loop, repeat, leave, iterate);

· объявления локальных переменных при помощи оператора declare и назначение им значений при помощи оператора set;

· именованные условия и обработчики ошибок.

В MySQL триггеры нельзя привязать к каскадному обновлению или удалению записей из таблицы типы InnoDB по связи первичный ключ/внешний ключ.

Триггеры сложно использовать, не имея доступа к новым записям, которые вставляются в таблицу, или старым записям, которые обновляются или удаляются. Для доступа к новым и старым записям используются префиксы new и old соответст­венно. Если в таблице обновляется поле total, то получить доступ к старому значению можно по имени old.total, а к новому – new.total.

Пример простейшего триггера для учебной БД book см. в пункте «Пример выполнения работы» (пример 1). Он демонстрирует работу триггеров после добавления запи­си в таблицу без вмешательства в запрос. Рассмотрим триггер, который будет включаться до вставки новых записей в таблицу orders и ограничивает число заказываемых товаров до 1:

Часто при обновлении полей таблицы производится попытка добавления некорректных значений. Пример триггера, который при добавлении нового покупателя преобразует полные имена и отчества в инициалы, см. в пункте «Пример выполнения работы» (пример 2). Он привязан к событию INSERT. Чтобы имя и отчество не могло быть отредактировано при помощи оператора update, можно создать триггер, привязанный к событию update.

Удаление триггеров. Удалить существующий триггер позволяет оператор

DROP TRIGGER trigger_name;

Практическая работа

При выполнении лабораторной работы необходимо:

· для заданной предметной области написать два триггера для разных таблиц базы данных;

· составить отчет по лабораторной работе.

 

Практическая работа № 8. Транзакции.

 

Рассмотрим следующие вопросы:

· создание представлений с помощью оператора CREATE VIEW;

· удаление представлений с помощью оператора DROP VIEW.

Основная структурная единица реляционных БД – таблицы, но язык SQL предоставляет еще один способ организации данных. Представление – это запрос на выборку, которому присваивается уникальное имя и который можно сохранять или удалять из БД как хранимую процедуру. Представления позволяют увидеть результаты сохраненного запроса так, как будто это полноценная таблица. MySQL, встретив в запросе ссылку на представление, ищет его определение в БД. Пользовательский запрос с участием представления преобразуется в эквивалентный запрос к исходным таблицам. Если определение представления простое, то каждая строка представления формируется «на лету». Если определение сложное, MySQL материализует представление в виде временной таблицы. Клиент, обращаясь к представлению, будет видеть только столбцы результирующей таблицы. Не имеет значения, сколько столбцов в исходной таблице и является ли запрос, лежащий в основе представления, одно- или многотабличным. Клиенту можно запретить обращаться к исходным таблицам, но снабдить привилегиями обращения к представлениям. На одном наборе таблиц можно создать гибкие системы доступа.

Преимущества представлений:

· безопасность – каждый пользователь имеет доступ к небольшому числу представлений, содержащих только ту информацию, которую ему позволе­но знать;

· простота запросов – с помощью представления можно извлечь данные из не­скольких таблиц и представить их как одну таблицу (запрос ко многим таблицам заменяется однотабличным запросом к представлению);

· простота структуры – представления позволяют создать для каждого пользовате­ля собственную структуру БД (отображаются данные, которые ему нужны);

· защита от изменений – таблицы и их структура могут постоянно изменяться и переименовываться; представления позволяют создавать виртуальные таблицы со старыми именами и структурой, позволяя избежать модификации приложений.

 

Недостатки представлений:

· производительность – представления создают видимость существования таблицы, и MySQL приходится преобразовывать запрос к представлению в запрос к исходным таблицам; если представление отображает многотабличный запрос, то простой запрос к представлению становится сложным объединением;

· ограничение на обновление – когда пользователь пытается обновить строки представления, MySQL необходимо обновить строки в исходных таблицах; это возможно только для простых представлений, сложные представления доступны только для выборки.

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

Создание представлений. Осуществляется при помощи оператора

CRFATE [OR REPLACE] [ALGORITHM = {UNDEFINED / MERGE / TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement

[WITH [CASCADED / LOCAL] CHECK OPTION];

Оператор создает представление view_name со столбцами, перечисленными в column_list, на основании запроса select_statement. Рассмотрим создание представления cat, которое дублирует таблицу catalogs базы данных book:

Представление рассматривается как полноценная таблица и может быть просмотрено в списке таблиц БД при помощи оператора show tables:

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

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

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

Создадим представление manager1 для менеджера, работающего с каталогами «Интернет» и «Сети»:

Наиболее удобно использовать представления для формирования сгруппированных таблиц. При работе с такими таблицами MySQL самостоятельно формирует временную таблицу – см. пункт «Пример выполнения работы» (пример 2).

Удаление представлений.Выполняется с помощью оператора:

DROP VIEW [IF EXISTS] view_name [, view_name] … ;

Оператор позволяет уничтожить одно или несколько представлений, например:

 

Практическая работа № 9. Работа с представлениями.

 

Практическая работа

При выполнении лабораторной работы необходимо:

· для заданной предметной области создать два представления в БД;

· сформировать запрос к одному из представлений;

· составить отчет по лабораторной работе.

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

 

 

СУБД MySQL является многопользовательской средой, поэтому для доступа к таблицам БД могут быть созданы различные учетные записи с разным уровнем привилегий. Учетной записи редактора можно предоставить привилегии на просмотр таблицы, добавление новых записей и обновление уже существующих. Администратору БД можно предоставить более широкие полномочия (возможность создания таблиц, редактирования и удаления уже существующих). Для пользователя БД достаточно лишь просмотра таблиц.

Рассмотрим следующие вопросы:

· создание, редактирование и удаление учетных записей пользователей;

· назначение и отмена привилегий.

Учетная запись является составной и принимает форму 'username' @ 'host', где username – имя пользователя, a host – наименование хоста, с которого пользователь может обращаться к серверу. Например, записи 'root' @ '127.0.0.1' и 'wet' @ '62.78.56.34' означают, что пользователь с именем root может обращаться с хоста, на котором расположен сервер, a wet – только с хоста с IP-адресом 62.78.56.34.

IP-адрес 127.0.0.1 всегда относится к локальному хосту. Если сервер и клиент установлены на одном хосте, то сервер слушает соединения по этому адресу, а клиент отправляет на него SQL-запросы.

IP-адрес 127.0.0.1 имеет псевдоним localhost, поэтому учетные записи вида 'root' @ '127.0.0.1' можно записывать в виде 'root' @ 'localhost'.

Число адресов, с которых необходимо обеспечить доступ пользователю, может быть значительным. Для задания диапазона в имени хоста используется специальный символ "%". Так, учетная запись 'wet' @ '%' позволяет пользователю wet обращаться к серверу MySQL с любых компьютеров сети.

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

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

CREATE USER 'username' @ 'host' [IDENTIFIED BY [PASSWORD] 'пароль'];

Оператор создает новую учетную запись с необязательным паролем. Если пароль не указан, в его качестве выступает пустая строка. Разумно хранить пароль в виде хэш-кода, полученного в результате необратимого шифрования. Чтобы воспользоваться этим механизмом авторизации, необходимо поместить между ключевым словом identified by и паролем ключевое слово password.

 

Удаление учетной записи. Удалить учетную запись позволяет оператор

DROP USER 'username' @ 'host';

Изменение имени пользователя в учетной записи. Осуществляется с помощью оператора

RENAME USER старое_имя TO новое_имя;

Назначение привилегий. Рассмотренные выше операторы позволяют создавать, удалять и редактировать учетные записи, но они не позволяют изменять привилегии пользователя – сооб­щать MySQL, какой пользователь имеет право только на чтение информации, какой на чтение и редактирование, а кому предоставлены права изменять структуру БД и создавать учетные записи.

Для решения этих задач предназначены операторы grant (назначает привилегии) и revoke (удаляет привилегии). Если учетной записи, котораяпоказана в операторе grant, не существует, то она автоматически создается. Удаление всех привилегий с помощью оператора revoke не приводит к авто­матическому уничтожению учетной записи. Для удаления пользователя необходимо воспользоваться оператором drop user.

В простейшем случае оператор grant выглядит следующим образом:

Данный запрос создает пользователя с именем wet и паролем pass, который может обращаться к серверу с локального хоста (localhost) и имеет все права (all) для всех баз данных (*.*). Если такой пользователь существует, то его привилегии будут изменены на all.

Вместо ключевого слова all можно использовать любое из ключевых слов, представленных в табл. 9. Ключевое слово on в операторе grantзадает уровень привилегий, которые могут быть заданы на одном из четырех уровней, представленных в табл. 10. Для таблиц можно установить только следующие типы привилегий: SELEСТ, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX и ALTER. Это следует учитывать при использовании конструкции grant all, которая назначает привилегии на текущем уровне. Так, запрос уровня базы данных grant all on db.* не предоставляет никаких глобальных привилегий.

Отмена привилегий. Для отмены привилегий используется оператор revoke:

Оператор revoke отменяет привилегии, но не удаляет учетные записи, для их удаления необходимо воспользоваться оператором drop USER.

Таблица 9

Привилегия	Операция, разрешенная привилегией
ALL [PRIVILEGES]	Комбинация всех привилегий, за исключением привилегии GRANT option, которая задается отдельно
ALTER	Позволяет редактировать таблицу с помощью оператора ALTER TABLE
ALTER ROUTINE	Позволяет редактировать или удалять хранимую процедуру
CREATE	Позволяет создавать таблицу при помощи оператора CREATE TABLE
CREATE ROUTINE	Позволяет создавать хранимую процедуру
CREATE TEMPORARY TABLES	Позволяет создавать временные таблицы
CREATE USER	Позволяет работать с учетными записями c помощью CREATE USER, DROP USER, RENAME USER и REVOKE ALL PRIVILEGES
CREATE VIEW	Позволяет создавать представление с помощью оператора CREATE VIEW
DELETE	Позволяет удалять записи при помощи оператора delete
DROP	Позволяет удалять таблицы при помощи оператора DROP TABLE
EXECUTE	Позволяет выполнять хранимые процедуры
INDEX	Позволяет работать с индексами, в частности, использовать операторы CREATE INDEX и DROP INDEX
INSERT	Позволяет добавлять в таблицу новые записи оператором insert
LOCK TABLES	Позволяет осуществлять блокировки таблиц при помощи операторов LOCK TABLES и UNLOCK TABLES. Для вступления в действие этой привилегии должна быть установлена привилегия SELECT
select	Позволяет осуществлять выборки таблиц оператором SELECT
Show databases	Позволяет просматривать список всех таблиц на сервере при помощи оператора show databases
Show view	Позволяет использовать оператор show create view
UPDATE	Позволяет обновлять содержимое таблиц оператором UPDATE
USAGE	Синоним для статуса «отсутствуют привилегии»
GRANT OPTION	Позволяет управлять привилегиями других пользователей, без данной привилегии невозможно выполнить операторы grant и REVOKE

 

Таблица 10

Ключевое слово ON	Уровень
ON *.*	Глобальный уровень – пользователь с полномочиями на глобальном уровне может обращаться ко всем БД и таблицам, входящим в их состав
ON db.*	Уровень базы данных – привилегии распространяются на таблицы базы данных db
ON db.tbl	Уровень таблицы – привилегии рас­пространяются на таблицу tbl базы данных db
ON db.tbl	Уровень столбца – привилегии касаются отдельных столбцов в таблице tbl базы данных db. Список столбцов указывается в скобках через запятую после ключевых слов select, insert, update

Практическая работа

При выполнении лабораторной работы необходимо:

· создать учетную запись нового пользователя и наделить его определенными привилегиями;

· составить отчет по лабораторной работе.

 

 

Практическая работа № 10. Управление правами пользователей.

 

Для работы выберем два компьютера, подключенных к локальной сети. На одном необходимо развернуть сервер MySQL, на другой – скопировать клиент командной строки mysql.exe. Определим IP-адрес сервера:

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

Назначим этому пользователю привилегии глобального уровня:

На клиентском компьютере в командной строке (например, с помощью FAR), запустим клиент командной строки в следующем формате:

Наблюдаем отклик удаленного сервера и работаем с ним как обычно:

 

Варианты заданий к лабораторным работам

 

1. Страховая компания. Страховая компания имеет филиалы, которые характеризуются наименованием, адресом и телефоном. В филиалы обращаются клиенты с целью заключения договора о страховании. В зависимости от принимаемых на страхование объектов и страхуемых рисков договор заключается по определенному виду страхования (страхование автотранспорта от угона, страхование домашнего имущества, добровольное медицинское страхование). При заключении договора фиксируются: дата заключения, страховая сумма, вид страхования, тарифная ставка и филиал, в котором заключался договор. Договоры заключают страховые агенты. Помимо информации об агентах (фамилия, имя, отчество, адрес, телефон) нужно хранить филиал, в котором они работают. Необходимо иметь возможность рассчитывать заработную плату агентам. Заработная плата составляет некоторый процент от страхового платежа (платеж – страховая сумма, умноженная на тарифную ставку). Процент зависит от вида страхования, по которому заключен договор.

2. Гостиница. Гостиница предоставляет номера клиентам. Каждый номер характеризуется вместимостью, комфортностью (люкс, полулюкс, обычный) и ценой. О клиентах собирается определенная информация (фамилия, имя, отчество, паспортные данные, адрес жительства и некоторый комментарий). Сдача номера клиенту производится при наличии свободных мест в номерах, подходящих клиенту по указанным выше параметрам. При заселении фиксируется дата заселения. При выезде из гостиницы для каждого места запоминается дата освобождения. Необходимо также осуществлять бронирование номеров. Для постоянных клиентов, а также для определенных категорий клиентов предусмотрена система скидок. Скидки могут суммироваться.

 

3. Ломбард. В ломбард обращаются различные лица с целью получения денежных средств под залог товаров. Клиент сообщает фамилию, имя, отчество и другие паспортные данные. После оценивания стоимости принесенного в качестве залога товара работник ломбарда определяет сумму, которую готов выдать на руки клиенту, а также свои комиссионные. Кроме того определяется срок возврата денег. Если клиент согласен, то договоренности фиксируются в виде документа, деньги выдаются клиенту, а товар остается в ломбарде. Если в указанный срок не происходит возврата денег, товар переходит в собственность ломбарда. После перехода прав собственности на товар, ломбард может продавать товары по цене, меньшей или большей, чем была заявлена при сдаче. Цена может меняться несколько раз, в зависимости от ситуации на рынке (например, владелец ломбарда может устроить распродажу зимних вещей в конце зимы). Помимо текущей цены нужно хранить все возможные значения цены для данного товара.

4. Оптово-розничная продажа товаров. Компания торгует товарами из определенного спектра. Каждый товар характеризуется наименованием, оптовой ценой, розничной ценой и справочной информацией. В компанию обращаются покупатели, для каждого из которых в базе данных фиксируются стандартные данные (наименование, адрес, телефон, контактное лицо). По каждой сделке составляется документ, в котором наряду с покупателем фиксируются количество купленного им товара и дата покупки. Обычно покупатели в рамках одной сделки покупают не один товар, а сразу несколько. Также компания решила предоставлять скидки в зависимости от количества закупленных товаров и их общей стоимости.

5. Ведение заказов. Компания занимается оптовой продажей различных товаров. Каждый из товаров характеризуется ценой, справочной информацией и признаком наличия или отсутствия доставки. В компанию обращаются заказчики. Для каждого из них в базе данных запоминаются стандартные данные (наименование, адрес, телефон, контактное лицо). По каждому заказу составляется документ, в котором наряду с заказчиком фиксируются количество купленного им товара и дата покупки. Доставка товаров может производиться способами, различными по цене и скорости. Нужно хранить информацию о том, какими способами может осуществляться доставка каждого товара, и информацию о том, какой вид доставки (и какую стоимость доставки) выбрал клиент при заключении сделки.

6. Бюро по трудоустройству. Бюро готово искать работников для различных работодателей и вакансии для ищущих работу специалистов различного профиля. При обращении в бюро работодателя его стандартные данные (название, вид деятельности, адрес, телефон) фиксируются в базе данных. При обращении в бюро соискателя его стандартные данные (фамилия, имя, отчество, квалификация, профессия, иные данные) также фиксируются в базе данных. По каждому факту удовлетворения интересов обеих сторон составляется документ. В документе указываются соискатель, работодатель, должность и комиссионные (доход бюро). В базе должна фиксироваться не только сделка, но и информация по открытым вакансиям. Кроме того для автоматического поиска вариантов необходимо вести справочник «Виды деятельности».

7. Нотариальная контора. Нотариальная контора готова предоставить клиенту определенный комплекс услуг. Услуги формализованы, т. е. составлен их список с описанием каждой услуги. При обращении клиента его стандартные данные (название, вид деятельности, адрес, телефон) фиксируются в базе данных. По каждому факту оказания услуги клиенту составляется документ, в котором указываются дата, услуга, сумма сделки, комиссионные (доход конторы), описание сделки. В рамках одной сделки клиенту может быть оказано несколько услуг. Стоимость каждой услуги фиксирована. Кроме того компания предоставляет в рамках одной сделки различные виды скидок. Скидки могут суммироваться.

8. Фирма по продаже запчастей. Фирма продает запасные части для автомобилей. Фирма имеет определенный набор поставщиков, по которым известны название, адрес и телефон. У поставщиков приобретаются детали. Каждая деталь характеризуется названием, артикулом и ценой. Некоторые из поставщиков могут поставлять одинаковые детали (один артикул). Каждый факт покупки запчастей у поставщика фиксируется в базе данных, причем обязательными для запоминания являются дата покупки и количество приобретенных деталей. Цена детали может меняться от поставки к поставке. Поставщики заранее ставят фирму в известность о дате изменения цены и ее новом значении. Нужно хранить не только текущее значение цены, но и всю историю изменения цен.

9. Курсы по повышению квалификации. В учебном заведении организованы курсы повышения квалификации. Группы слушателей формируются в зависимости от специальности и отделения. В каждую из них включено определенное количество слушателей. Проведение занятий обеспечивает штат преподавателей, для каждого из которых в базе данных зарегистрированы стандартные анкетные данные (фамилия, имя, отчество, телефон) и стаж работы. В результате распределения нагрузки получена информация о том, сколько часов занятий проводит каждый преподаватель с соответствующими группами. Хранятся также сведения о виде занятий (лекция, практика), дисциплине и оплате за 1 час. Размер почасовой оплаты зависит от предмета и типа занятия. Кроме того каждый преподаватель может вести не все предметы, а только некоторые.

10. Определение факультативов для студентов. Преподаватели кафедры в высшем учебном заведении обеспечивают проведение факультативных занятий по некоторым предметам. Имеются сведения о студентах, включающие стандартные анкетные данные (фамилия, имя, отчество, группа, адрес, телефон). По каждому факультативу существует определенное количество часов и вид проводимых занятий (лекции, практика, лабораторные работы). В результате работы со студентами появляется информация о том, кто из них записался на какие факультативы. Существует некоторый минимальный объем факультативных предметов, которые должен прослушать каждый студент. По окончании семестра в базу данных заносится информация об оценках, полученных студентами на экзаменах. Некоторые из факультативов могут длиться более одного семестра. В каждом семестре для предмета устанавливается объем лекций, практик и лабораторных работ в часах. В качестве итоговой оценки за предмет берется последняя оценка, полученная студентом.

11. Распределение учебной нагрузки. Необходимо распределять нагрузку между преподавателями кафедры. Имеются сведения о преподавателях, включающие наряду с анкетными данными сведения об их ученой степени, занимаемой должности и стаже работы. Преподаватели кафедры должны обеспечить проведение занятий по некоторым дисциплинам. По каждой из них существует определенное количество часов. В результате распределения нагрузки необходимо получить информацию следующего рода: «Такой-то преподаватель проводит занятия по такой-то дисциплине с такой-то группой». Все проводимые занятия делятся на лекционные и практические. По каждому виду занятий устанавливается свое количество часов. Кроме того данные по нагрузке нужно хранить несколько лет.

12. Распределение дополнительных обязанностей. Кафедра вуза имеет штат сотрудников, каждый из которых получает определенный оклад. Каждый месяц возникает потребность в выполнении некоторой дополнительной работы, не входящей в круг основных обязанностей сотрудников. Для наведения порядка в этой сфере классифицированы все виды дополнительных работ и определена сумма оплаты по факту их выполнения. При возникновении дополнительной работы назначается ответственный, фиксируется дата начала. По факту окончания фиксируется дата и выплачивается дополнительная сумма к зарплате с учетом классификации. Необходимо учесть разделение сотрудников на преподавателей и учебно-вспомогательный персонал. Для первых нужно хранить сведения об ученой степени и ученом звании, для вторых – о должности. Некоторые работы являются трудоемкими и срочными, что требует привлечения к их выполнению нескольких сотрудников. Длительность работ различна. Нужно заранее планировать длительность работы и количество сотрудников, занятых для выполнения работы.

13. Техническое обслуживание станков. Компания занимается ремонтом станков и другого оборудования. Клиентами компании являются промышленные предприятия. Ремонтные работы организованы следующим образом: все станки классифицированы по типам, странам-производителям, годам выпуска и маркам. Все виды ремонта отличаются названием, продолжительностью в днях, стоимостью. Исходя из этих данных, по каждому факту ремонта фиксируется вид станка, дата начала и дата окончания ремонта. Анализ показал, что нужно не просто подразделять станки по типам, а иметь информацию о том, сколько раз ремонтировался тот или иной станок.

14. Туристическая фирма. Фирма продает путевки клиентам. У каждого клиента запрашиваются стандартные данные – фамилия, имя, отчество, адрес, телефон. После этого сотрудники компании выясняют у клиента, куда он хотел бы поехать отдыхать. Ему демонстрируются различные варианты, включающие страну проживания, особенности климата, отель. Обсуждается длительность пребывания и стоимость путевки. Если удалось найти приемлемый вариант, регистрируется факт продажи путевки (или путевок, если клиент покупает сразу несколько), фиксируется дата отправления. Иногда клиенту предоставляется скидка (скидки фиксированы и могут суммироваться). Фирма работает с несколькими отелями (название, категория, адрес) в нескольких странах. Путевки продаются на одну, две или четыре недели. Стоимость путевки зависит от длительности тура и отеля.

15. Грузовые перевозки. Компания осуществляет перевозки грузов по различным маршрутам. Необходимо отслеживать стоимость перевозок с учетом заработной платы водителей. Для каждого маршрута определено название, вычислено примерное расстояние и установлена некоторая оплата для водителя. Информация о водителях включает фамилию, имя, отчество и стаж. Для проведения расчетов хранится полная информация о перевозках (маршрут, водитель, даты отправки и прибытия). По факту некоторых перевозок водителям выплачивается премия. Фирма решила ввести гибкую систему оплаты. Оплата водителям должна зависеть не только от маршрута, но и от стажа водителя. Кроме того, нужно учесть, что перевозку могут осуществлять два водителя.

16. Учет телефонных переговоров. Телефонная компания предоставляет абонентам телефонные линии для междугородних переговоров. Абонентами компании являются юридические лица, имеющие телефонную точку, ИНН, расчетный счет в банке. Стоимость переговоров зависит от города, в который осуществляется звонок, и времени суток (день, ночь). Каждый звонок абонента автоматически фиксируется в базе данных. При этом запоминаются город, дата, длительность разговора и время суток. Компания решила ввести гибкую систему скидок. Так, стоимость минуты теперь уменьшается в зависимости от длительности разговора. Размер скидки для каждого города разный.

17. Учет внутриофисных расходов. Сотрудники частной фирмы могут осуществлять мелкие покупки для нужд фирмы, предоставляя в бухгалтерию товарный чек. Бухгалтерия отслеживает внутриофисные расходы. Фирма состоит из отделов, каждый из которых имеет название. В каждом отделе работает определенное количество сотрудников. Сотрудники могут осуществлять покупки в соответствии с видами расходов. Каждый вид расходов имеет название, некоторое описание и предельную сумму средств, которые могут быть потрачены по данному виду расходов в месяц. При каждой покупке сотрудник оформляет документ, где указывает вид расхода, дату, сумму и отдел. Нужно хранить данные о расходах не только в целом по отделу, но и по отдельным сотрудникам. Нормативы по расходованию средств устанавливаются не в целом, а по каждому отделу за каждый месяц. Неиспользованные в текущем месяце деньги могут быть использованы позже.

18. Библиотека. Библиотека решила зарабатывать деньги, выдавая напрокат книги, имеющиеся в небольшом количестве экземпляров. У каждой книги, выдаваемой в прокат, есть название, автор, жанр. В зависимости от ценности книги для каждой из них определена залоговая стоимость (сумма, вносимая клиентом при взятии книги напрокат) и стоимость проката (сумма, которую клиент платит при возврате книги, получая назад залог). Читатели регистрируются в картотеке, которая содержит стандартные анкетные данные (фамилия, имя, отчество, адрес, телефон). Каждый читатель может обращаться в библиотеку несколько раз. Все обращения читателей фиксируются, при этом по каждому факту выдачи книги запоминаются дата выдачи и ожидаемая дата возврата. Стоимость проката книги должна зависеть не только от самой книги, но и от срока ее проката. Кроме того, необходимо добавить систему штрафов за вред, нанесенный книге, и систему скидок для некоторых категорий читателей.

19. Прокат автомобилей. Фирма, занимающаяся прокатом автомобилей, имеет автопарк, содержащий некоторое количество автомобилей различных марок, стоимостей и типов. Каждый автомобиль имеет свою стоимость проката. В пункт проката обращаются клиенты. Клиенты проходят обязательную регистрацию, в ходе которой о них собирается стандартная информация (фамилия, имя, отчество, адрес, телефон). Каждый клиент может обращаться в пункт проката несколько раз. Обращения клиентов фиксируются, при этом по каждой сделке запоминаются дата выдачи и ожидаемая дата возврата. Стоимость проката автомобиля должна зависеть не только от самого автомобиля, но и от срока его проката, а также от года выпуска. Также нужно ввести систему штрафов за возвращение автомобиля в ненадлежащем виде и систему скидок для постоянных клиентов.

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

21. Инвестиционная компания. Компания занимается вложением денежных средств в ценные бумаги, которые характеризуются рейтингом, доходностью за прошлый год, минимальной суммой сделки и некоторой дополнительной информацией. Клиентами компании являются предприятия, которые доверяют ей управлять их свободными денежными средства на определенный период. Необходимо выбрать вид ценных бумаг, которые позволят получить прибыль и компании и клиенту. При работе с клиентом существенной является информация о предприятии – название, вид собственности, адрес и телефон. Каждая инвестиция характеризуется информацией о клиенте, информацией о ценной бумаге, котировкой бумаги, датой ее покупки и датой ее продажи. Необходимо хранить историю котировок каждой ценной бумаги. Кроме того, помимо вложений в ценные бумаги существует возможность вкладывать деньги в банковские депозиты.

22. Занятость актеров театра. Коммерческий директор театра организует привлечение актеров и заключение контрактов. Каждый год театр осуществляет постановку различных спектаклей. Каждый спектакль имеет определенный бюджет. Для участия в конкретных постановках в определенных ролях привлекаются актеры. С каждым из актеров заключается персональный контракт на определенную сумму. Каждый актер имеет стаж работы, некоторые из них удостоены различных званий. В рамках одного спектакля на одну и ту же роль привлекается несколько актеров. Контракт определяет базовую зарплату актера, а по итогам реально отыгранных спектаклей актеру назначается премия. В базе данных нужно хранить информацию за несколько лет.

23. Платная поликлиника. В поликлинике работают врачи различных специальностей, имеющие разную квалификацию. Каждый день в поликлинику обращаются пациенты. Все пациенты проходят обязательную регистрацию, при которой в базу данных заносятся стандартные анкетные данные (фамилия, имя, отчество, год рождения, адрес). При обращении в поликлинику пациент обследуется и проходит лечение у разных специалистов. Каждый пациент может обращаться в поликлинику несколько раз, нуждаясь в различной медицинской помощи. Все обращения пациентов фиксируются, при этом устанавливается диагноз, определяется стоимость лечения, запоминается дата обращения. Общая стоимость лечения зависит от стоимости консультаций и процедур, назначенных пациенту. Для определенных категорий граждан предусмотрены скидки.

24. Анализ динамики показателей финансовой отчетности предприятий. Информационно-аналитический центр крупного холдинга отслеживает динамику показателей предприятий холдинга. В структуру холдинга входят несколько предприятий. Каждое предприятие имеет стандартные характеристики (название, реквизиты, телефон, контактное лицо). Работа предприятия может быть оценена следующим образом: в начале каждого отчетного периода на основе финансовой отчетности вычисляется определенный набор показателей. Важность показателей характеризуется некоторыми числовыми константами. Значение каждого показателя измеряется в некоторой системе единиц. Некоторые показатели считаются в рублях, некоторые в долларах, некоторые в евро. Для удобства работы с показателями нужно хранить изменения курсов валют относительно друг друга.

25. Учет телекомпанией стоимости прошедшей в эфире рекламы. Работа коммерческой службы телевизионной компании построена следующим образом: заказчики просят поместить свою рекламу в определенной передаче в определенный день. Каждый рекламный ролик имеет определенную продолжительность. Для каждой организации-заказчика известны банковские реквизиты, телефон и контактное лицо для проведения переговоров. Передачи имеют определенный рейтинг. Стоимость минуты рекламы в каждой передаче определяется, исходя из рейтинга передачи и прочих соображений. Необходимо хранить информацию об агентах, заключивших договоры на рекламу. Зарплата рекламных агентов составляет некоторый процент от общей стоимости рекламы, прошедшей в эфире.

26. IT-компания. Компания оказывает IT-услуги организациям и предприятиям. В компании работают сотрудники, о которых должна сохраняться стандартная информация и данные о квалификации (владение языками и системами программирования, знание СУБД, операционных систем). В компанию обращаются клиенты, о которых собираются стандартные данные (наименование и адрес организации, телефон, адрес электронной почты, фамилия, имя и отчество контактного лица для связи). Задание для клиента выполняет определенный сотрудник, при этом фиксируется дата выдачи задания и трудоемкость выполнения (в часах). При повторном обращении клиент переходит в категорию постоянных и получает скидку. С ростом компании возникла необходимость разделения ее на отделы. Увеличились масштабы проектов, и теперь задание клиента поручается отделу. В рамках одного договора может выполняться несколько заданий разными отделами компании.

27. Ювелирная мастерская. Ювелирная мастерская осуществляет изготовление ювелирных изделий для частных лиц на заказ. Мастерская работает с определенными материалами (платина, золото, серебро, драгоценные камни). При обращении потенциального клиента выясняется, какое именно изделие ему необходимо. Все изготавливаемые изделия принадлежат к некоторому типу (серьги, кольца, броши, браслеты), выполняются из определенного материала, имеют некоторый вес и цену (включающую стоимость материалов и работы). Ювелирное изделие может состоять из нескольких материалов. Кроме того, постоянным клиентам мастерская предоставляет скидки.

28. Парикмахерская. Парикмахерская стрижет клиентов в соответствии с их пожеланиями и некоторым каталогом различных видов стрижки. Для каждой стрижки определены название, категория (мужская, женская, детская), стоимость работы. Для наведения порядка составляется база данных клиентов, где запоминаются их анкетные данные (фамилия, имя, отчество). Начиная с 5-ой стрижки, клиент переходит в категорию постоянных и получает скидку в 3 % при каждой последующей стрижке. После того, как закончена очередная работа, в БД фиксируются стрижка, клиент и дата производства работ. Кроме того, у парикмахерской появился филиал и необходима раздельная статистика по филиалам. Стоимость стрижки может меняться с течением времени. Нужно хранить не только последнюю цену, но и все данные по изменению цены стрижки.

29. Химчистка. Химчистка осуществляет прием у населения вещей для выведения пятен. Для наведения порядка составляется база данных клиентов, в которой запоминаются их анкетные данные (фамилия, имя, отчество, адрес, телефон). Начиная с 3-го обращения, клиент переходит в категорию постоянных клиентов и получает скидку в 3 % при чистке каждой последующей вещи. Все оказываемые услуги подразделяются на виды, имеющие название, тип и стоимость, зависящую от сложности работ. Работа с клиентом первоначально состоит в определении объема работ, вида услуги и, соответственно, ее стоимости. Если клиент согласен, он оставляет вещь (при этом фиксируется услуга, клиент и дата приема) и забирает ее после обработки (при этом фиксируется дата возврата). Химчистка заключает с клиентом договор. Клиент может одновременно сдавать в чистку несколько вещей. У химчистки появились филиалы, и необходима раздельная статистика по филиалам. Введены надбавки за срочность и сложность.

30. Сдача в аренду торговых площадей. Торговый центр сдает в аренду коммерсантам свои торговые площади. В результате планирования определено некоторое количество торговых точек в пределах здания, которые могут сдаваться в аренду. Для каждой из торговых точек важными данными являются этаж, площадь, наличие кондиционера и стоимость аренды в день. С потенциальных клиентов собираются стандартные данные (название, адрес, телефон, реквизиты, контактное лицо). При появлении потенциального клиента ему показывают имеющиеся свободные площади. При достижении соглашения оформляется договор и в базе данных фиксируется торговая точка, клиент, период (срок) аренды. Некоторые клиенты в рамках одного договора арендуют сразу несколько торговых точек, причем для каждой точки возможен свой срок аренды. Дата заключения договора может не совпадать с датой начала аренды. Необходимо собирать информацию об ежемесячных платежах, поступающих от арендаторов.

 

 

БИБЛИОГРАФИЧЕСКИЙ СПИСОК.

 

1. Веллинг Л., Томсон Л. MySQL: Учебное пособие: Пер. с англ. – М.: Изд. Дом «Вильямс», 2005. – 304 с.

2. Малков О. Б., Белимова Е. В. Проектирование баз данных с использованием CASE-технологии: Метод. указания. – Омск: Изд-во ОмГТУ, 2003. – 48 с.

3. Малков О.Б., Гегечкори Е.Т. Базы данных: Методические указания к выполнению лабораторных работ. – Омск: Изд-во ОмГТУ, 2007. – 112 с.

4. Самоучитель MySQL 5 / М. В. Кузнецов, И. В. Симдянов – СПб: БХВ-Петербург, 2007. – 560 с.

5. MySQL. Справочник по языку: Пер. с англ. – М.: Издательский дом «Вильямс», 2005. – 432 с.

 

Приложения

Приложение 1. Основные математические функции MySQL

Обозначение	Описание
ABS(X)	Возвращает абсолютное значение аргумента X
ACOS(X)	Возвращает арккосинус аргумента Х или NULL, если значение X не находится в диапазоне от -1 до 1
ASIN(X)	Возвращает арксинус аргумента Х или NULL, если значение X не находится в диапазоне от -1 до 1
ATAN(X)	Возвращает арктангенс аргумента Х
CEIL(X)	Принимает дробный аргумент Х и возвращает первое целое число, находящееся справа от аргумента
COS(X)	Вычисляет косинус угла X, заданного в радианах
COT(X)	Вычисляет котангенс угла X, заданного в радианах
DEGREES(X)	Возвращает значение угла X, преобразованное из радиан в градусы
EXP(X)	Вычисляет значение ex
FLOOR(X)	Принимает дробный аргумент Х и возвращает первое целое число, находящееся слева от аргумента
LN(X)	Вычисляет натуральный логарифм числа X
LOG2(X)	Вычисляет логарифм числа X по основанию 2
LOG10(X)	Вычисляет десятичный логарифм числа X
MOD(M,N)	Возвращает остаток от деления целого числа M на целое число N
PI( )	Используется без аргументов. Возвращает число π
POW(X,Y)	Возвращает значение числа X, возведенного в степень Y
RADIANS(X)	Возвращает значение угла X, преобразованное из градусов в радианы
RAND(X)	Возвращает случайное значение с плавающей точкой в диапазоне от 0.0 до 1.0
ROUND(X)	Возвращает округленное до ближайшего целого значение числа X
SIGN(X)	Позволяет определить знак числа X. Возвращает -1, 0 или 1, если X отрицательно, равно нулю или положительно
SIN(X)	Вычисляет синус угла X, заданного в радианах
SQRT(X)	Вычисляет квадратный корень числа X
TAN(X)	Вычисляет тангенс угла X, заданного в радианах
TRUNCATE (X,D)	Возвращает число X с дробной частью, имеющей D знаков после запятой. Если количество знаков в X больше D, лишние разряды усекаются. Если меньше, то в конец числа добавляются нули

Приложение 2. Основные строковые функции MySQL

 

Обозначение	Описание
ASCII(str)	Возвращает значение ASCII-кода первого символа строки str. Для пустой строки возвращается значение 0
BIN(N)	Принимает десятичное число N и возвращает его двоичное представление
BIT_LENGTH(str)	Принимает строку str и возвращает ее длину в битах
CHAR(N1, N2, … )	Принимает последовательность из ASCII-кодов и возвращает строку, построенную путем объединения соответствующих им символов
CHAR_LENGTH(str)	Принимает строку str и возвращает число символов в строке
CHARSET(str)	Возвращает имя кодировки, в которой представлена строка
COLLATION(str)	Возвращает порядок сортировки, установленный для кодировки аргумента str
CONCAT(str1, str2, …)	Возвращает строку, созданную путем объединения всех аргументов, количество которых не ограничено. Если хотя бы один аргумент равен NULL, то возвращается значение NULL
CONCAT_WS(separator, str1, str2, …)	Также объединяет аргументы str1, str2 и т.д., помещая между ними разделитель separator
CONV(N, from_base, to_base)	Преобразует число N из одной системы счисления from_base в другую to_base. Параметры from_base и to_base могут принимать значения от 2 до 36
ELT(N, str1, str2, …)	Возвращает N-ю строку из списка аргументов str1, str2, … (для N=1 возвращается str1, для N=2 – str2 и т.д.)
FIELD(str, str1, str2, …)	Находит строку str в списке str1, str2, …и возвращает номерстроки в этом списке (нумерация начинается с 1)
FIND_IN_SET(str, str_list)	Ищет вхождение строки str в список str_list и возвращает номерстроки в этом списке (нумерация начинается с 1). Параметр str_list – набор строк, разделенных запятыми
HEX(N_or_S)	Возвращает значение аргумента в виде шестнадцатеричного числа. Аргумент может быть как числом, так и строкой. Во втором случае функция переводит в шестнадцатеричное представление каждый символ строки и объединяет результат
INSERT(str, pos, len, new_str)	Возвращает строку str, в которой подстрока, начинающаяся с позиции pos и имеющая длину len символов, заменена подстрокой new_str
INSTR(str, substr)	Возвращает позицию первого вхождения подстроки substr в строку str
LEFT(str, len)	Возвращает len крайних левых символов строки str
LENGTH(str)	Возвращает длину строки str
LOCATE(substr, str [, pos])	Возвращает позицию первого вхождения подстроки substr в строку str. При наличии необязательного аргумента pos поиск начинается с позиции, указанной в этом аргументе
LOWER(str)	Возвращает строку str , записанную строчными символами
LPAD(str, len, padstr)	Возвращает строку str, дополненную слева строкой padstr до длины len символов. Если строка содержит более len символов, то она усекается доlen
LTRIM(str)	Возвращает строку str, в которой удалены все начальные пробелы
MID(str, pos [, len])	Возвращает подстроку строки str, которая начинается с позиции pos и имеет длину len символов. Если параметр len не указывается, то подстрока возвращается, начиная с позиции pos и до конца строки str
OCT(N)	Принимает десятичное число N и возвращает его в восьмеричной системе счисления
ORD(str)	Возвращает значение ASCII-кода первого символа строки str. В отличие от функции ASCII( ) корректно работает с многобайтными кодировками
REPEAT(str, count)	Возвращает строку, полученную из count повторений строки str
REPLACE(str, from_str, to_str)	Возвращает строку str, в которой все подстроки from_str заменены to_str
REVERSE(str)	Возвращает строку str, записанную в обратном порядке
RIGHT(str, len)	Возвращает len крайних правых символов строки str, или всю строку, если аргумент len равен NULL или меньше 1
RPAD(str, len, padstr)	Возвращает строку str, дополненную справа строкой padstr до длины len символов
RTRIM(str)	Возвращает строку str, в которой удалены все конечные пробелы
SPACE(N)	Возвращает строку, состоящую из N пробелов, или пустую строку, если N имеет отрицательное значение
SUBSTRING_INDEX(str, delim, N)	Возвращает подстроку строки str. Если параметр N имеет положительное значение, то функция находит N-е вхождение (отсчет слева) подстроки delim в строку str и возвращает всю часть строки, расположенную слева от подстроки delim. Если N имеет отрицательное значение, то функция находит N-е вхождение (отсчет справа) подстроки delim в строку str и возвращает часть строки, расположенную справа от подстроки delim
TRIM([[BOTH | LEADING | TRAILING ] [remstr] FROM ] str)	Удаляет из строки str расположенные в начале (в конце) символы, указанные в строке remstr. Если указано ключевое слово LEADING, удаляются расположенные в начале символы, если TRAILING – в конце, если BOTH – и в начале и в конце. Если ключевые слова не заданы, по умолчанию принимается BOTH. Если строка remstr не задана, то в качестве удаляемых символов выступают пробелы
UNHEX(str)	Является обратной функции HEX( ) и интерпретирует каждую пару символов строки str как шестнадцатеричный код, который необходимо преобразовать в символ
UPPER(str)	Переводит все символы строки str в верхний регистр

 

 

Приложение 3. Основные функции даты и времени MySQL

 

Обозначение	Описание
ADDDATE(date, INTERVAL expr type)	Возвращает дату date, к которой прибавлен временной интервал, определяемый вторым параметром. Например, ADDDATE(‘2009-03-20’, INTERVAL 10 day)
ADDTIME(expr1, expr2)	Возвращает результат сложения двух временных значений
CURDATE( )	Возвращает текущую дату в формате ‘YYYY-MM-DD’
CURTIME( )	Возвращает текущее время суток в формате ‘hh:mm:ss’
DATE(datetime)	Извлекает из значения datetime дату, отсекая часы, минуты и секунды
DATEDIFF(begin, end)	Вычисляет разницу в днях между датами begin и end
DATE_FORMAT(date, format)	Форматирует время date в соответствии со строкой format
DAY(date)	Возвращает порядковый номер дня в месяце (от 1 до 31)
DAYNAME(date)	Возвращает день недели в виде полного английского названия
DAYOFWEEK(date)	Возвращает порядковый номер дня недели. В западных странах неделя начинается с воскресенья, номер которого1.
DAYOFYEAR(date)	Возвращает порядковый номер дня в году (от 1 до 366)
EXTRACT(type FROM datetime)	Принимает дату и время суток и возвращает часть, определяемую параметром type. Например, EXTRACT(YEAR FROM ‘2009-12-31 14:30:15’)
FROM_DAYS(N)	Принимает число дней N, прошедших с нулевого года, и возвращает дату в формате ‘YYYY-MM-DD’. Обычно используется совместно с функцией TO_DAYS(date)
HOUR(datetime)	Извлекает из значения datetime часы (от 0 до 23)
LAST_DAY(datetime)	Принимает дату и время суток и возвращает дату – последний день текущего месяца
MAKEDATE(year, dayofyear)	Принимает год year и номер дня в году dayofyear и возвращает дату в формате ‘YYYY-MM-DD’
MAKETIME(hour, minute, second)	Принимает час hour, минуты minute и секунды second и возвращает время суток в формате ‘hh:mm:ss’
MINUTE(datetime)	Извлекает из значения datetime минуты (от 0 до 59)
MONTH(datetime)	Возвращает числовое значение месяца года (от 1 до 12)
MONTHNAME(datetime)	Возвращает название месяца в виде полного английского названия
NOW( )	Возвращает текущую дату и время в формате ‘YYYY-MM-DD hh:mm:ss’
PERIOD_ADD(period, N)	Добавляет N месяцев к значению даты period. Аргумент period должен быть представлен в числовом формате YYYYMMDD или YYMM

 

Основные функции даты и времени MySQL (продолжение)

Обозначение	Описание
PERIOD_DIFF(period1, period2)	Вычисляет разницу в месяцах между двумя датами, представленными в числовом формате YYYYMMDD или YYMM
QUARTER(datetime)	Возвращает значение квартала года (от 1 до 4)
SECOND(datetime)	Извлекает из значения datetime секунды (от 0 до 59)
SUBDATE(date, INTERVAL expr type)	Возвращает дату date, из которой вычитается временной интервал, определяемый вторым параметром. Например, SUBDATE(‘2009-05-15’, INTERVAL 5 day)
SUBTIME(datetime, time)	Вычитает из величины datetime время time
TIME(datetime)	Извлекает из значения datetime время суток
TIMEDIFF(expr1, expr2)	Возвращает разницу между временными значениями expr1 и expr2
TIMESTAMP(date, time)	Принимает в качестве аргумента дату date ивремя time и возвращает полный вариант в формате ‘YYYY-MM-DD hh:mm:ss’
TIMESTAMPADD(interval, int_expr, datetime_expr)	Прибавляет к дате и времени суток datetime_expr временной интервал int_expr, единицы измерения которого задаются параметром interval. Например, TIMESTAMPADD(WEEK, 1, ‘2009-09-02’)
TIMESTAMPDIFF(interval, datetime_expr1, datetime_expr2)	Возвращает разницу между двумя датами datetime_expr1 и datetime_expr1. Единицы измерения интервала задаются параметром interval. Например, TIMESTAMPDIFF(MONTH, ‘2005-02-01’, ‘2005-05-01’)
TO_DAYS(date)	Принимает дату date и возвращает число дней N, прошедших с нулевого года
WEEK(date)	Возвращает номер недели в году (от 0 до 53) для даты date. Предполагается, что неделя начинается с воскресенья
WEEKDAY(date)	Возвращает номер дня недели (0 – для понедельника, 1 – для вторника, 6 – для воскресенья) для даты date
YEAR(datetime)	Возвращает год из значения datetime
YEARWEEK(date)	Возвращает число в формате YYYYWW, представляющее год и номер недели (от 0 до 53) в году и соответствующее дате date

 

 

---

Дата добавления: 2019-11-16; просмотров: 351; Мы поможем в написании вашей работы!

Поделиться с друзьями:

---

---

Мы поможем в написании ваших работ!