Главная              Рефераты - Разное

Учебное пособие: Методические указания рассмотрены и рекомендованы к изданию методическим семинаром кафедры автоматики и компьютерных систем «29»

Федеральное агентство по образованию

Государственное образовательное учреждение высшего профессионального образования

«ТОМСКИЙ ПОЛИТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ »

Утверждаю

Декан (директор)

С. А. Гайворонский

« » 2009 г.

А.А. Пономарев

Создание структуры БД и манипулирование данными в СУБД MySQL

Методические указания к выполнению лабораторных работ

по курсу «Базы данных» для студентов III курса специальностей 230105 «Программное обеспечение вычислительной техники и автоматизированных систем». 230201 «Информационные системы и технологии».

Издательство

Томского политехнического университета

2009

УДК 681.324.016

ББК 00000

П00

Пономарев А.А.

П00 Базы данных: методические указания по выполнению лабораторного практикума для студентов специальностей 230105 « Программное обеспечение вычислительной техники и автоматизированных систем» , 230201 « Информационные системы и технологии». В трех частях, часть первая. /Сост. А.А. Пономарев. – Томск: Изд-во ТПУ, 2009. – 80 с.

УДК 681.324.016

ББК 00000

Методические указания рассмотрены и рекомендованы
к изданию методическим семинаром кафедры
автоматики и компьютерных систем

« 29 » октября 2009 г.

Зав. кафедрой АиКС

доктор технических наук __________Г.П. Цапко

Председатель учебно-методической

комиссии __________Е.А. Кочегурова

Рецензент

Доктор технических наук, профессор

Д.Г. Копаница

© Пономарев А.А., 2009

© Томский политехнический университет, 2009

©Оформление. Издательство Томского
политехнического университета, 2009


Оглавление

Лабораторная работа №1. Интерфейс СУБД MySQL. Предоставление доступа и привилегий. Утилиты, входящие в состав СУБД.. 4

Лабораторная работа №2. Моделирование баз данных средствами Erwin. 16

Лабораторная работа №3. Создание баз данных и таблиц в среде MYSQL. Информационное наполнение. 26

Лабораторная работа №4 Создание запросов и модификация таблиц базы данных. 35

Лабораторная работа №5. Работа с внешними базами данных. 46

Ограничение доступа. 46

Лабораторная работа №6. Представления, хранимые процедуры, функции, триггеры. 51

Представления. 51

Хранимые процедуры и функции. 52

Триггеры.. 57

Словарь данных. 57

Курсоры.. 60

Лабораторная работа №1. Интерфейс СУБД MySQL. Предоставление доступа и привилегий. Утилиты, входящие в состав СУБД

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

Запуск MySQL

Управление сервером обычно осуществляется из командной строки. Запуск в Windows 95/98/2000/XP осуществляется через сеанс DOS выполнением следующей команды:

D:\usr\local\Mysql\bin\mysqld --standalone

Эта команда запустит демон mysql в фоновом режиме. В Windows 95/98 не предусмотрен запуск mysqld в виде службы. В Windows 2000 демон mysql запускается в виде службы.

Можно осуществить запуск winmysqladmin.exe, в этом случае все настройки перечисляются в файле my.ini

При запуске mysqld можно указывать следующие опции:

Таблица 1- Опции команды MySQLD

-?, --help

Справка

-b, --basedir=[path]

Путь к каталогу в котором установлен mysql

-h, --datadir [homedir]

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

-l, --log=[filename]

Имя журнала транзакций

-L, --language=[language]

Язык по умолчанию(обычно English).

-P, --port=[port]

Порт для соединения.

--skip-grant-tables

Игнорировать таблицы привилегий. Это дает любому ПОЛНЫЙ доступ ко всем таблицам. Не следует предоставлять обычным пользователям разрешений на запуск mysqld.

--skip-name-resolve

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

--skip-networking

Использовать подключения только через интерфейс localhost.

-V, --version

Вывести информацию о версии.

Наличие в статусной строке иконки светофора с активным зеленым цветом указывает на то, что сервер запущен (см. рис 1).

Рисунок 1 - Приложение winmysqladmin запущено

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

Таблица 2 - Опции команды MySQL

-?, --help

Справка

-h,--hostname=[hostname]

Имя сервера mysql.

-u, --user=[user]

Имя пользователя для доступа к mysql.

-p, --password=[password]

Пароль пользователя для доступа к mysql.

-P, --port=[port]

Порт для соединения с сервером.

-V, --version

Информация о версии

Примечание . Команды mysqld и mysql имеют еще некоторые опции, но в данный момент они особого интереса не представляют.

Запуск из сеанса ДОС осуществляется как показано на Рисунок 2 (в указанном случае осуществляется подключение к БД mysql).

Рисунок 2 - Запуск консоли MYSQL

Для выполнения в строке наберите команду: mysql –u root

Рисунок 3 - Успешный запуск консоли

Если вы это получили, значит вы успешно вошли в консоль mysql, которая используется для администрирования сервера.

Для составления отчета вам понадобятся приведение команд, которые вы будете посылать на сервер. В MySQL имеется возможность ведение протокола выполняемых команд, чтобы запустить ведение протокола необходимо выполнить команду

\T filename

!!! обязательно в верхнем регистре. Filename – имя файла, в который будут записываться команды (создается автоматически при выполнении команды, и действует во время жизни сеанса, т.е. в случае отключения от сервера лог прерывается и для возобновления необходимо повторить команду с выводом в новый файл, так как команда затирает имеющиеся в файле данные).

Просмотр списка БД, доступных на сервере осуществляется командой SHOW DATABASES.

Для выполнения в строке наберите команду: show databases.

Командой: USE MYSQL ; – выбираем текущую БД где MYSQL имя БД.

Система привилегий и безопасность в MySQL

· User

· Db

· Host

· Пользовательские привилегии

База данных mysql и таблицы привилегий.

Итак, вы успешно вошли в базу данных mysql, которая используется для администрирования сервера. Что же здесь находится? А находятся здесь 5 таблиц, которые ничем не отличаются от других таблиц баз данных, за исключением того, что эти таблицы используются для предоставления доступа к базам данных и таблицам в них пользователям. Рассмотрим каждую из них.

Введите следующую команду, show tables , которая покажет таблицы в базе данных mysql.

Кратко рассмотрим функции каждой из таблиц:

Таблица User

Определяет, разрешено ли пользователю, пытающемуся подключиться к серверу делать это. Содержит имя пользователя, пароль а также привилегии. Если ввести команду show columns from user; то получим следующее:

Таблица 3- Структура таблицы User

Field

Type

Null

Key

Default

Extra

Host

char(60)

PRI

User

char(16)

PRI

Password

char(41)

Select_priv

enum('N','Y')

N

Insert_priv

enum('N','Y')

N

Update_priv

enum('N','Y')

N

Delete_priv

enum('N','Y')

N

Create_priv

enum('N','Y')

N

Drop_priv

enum('N','Y')

N

Reload_priv

enum('N','Y')

N

Shutdown_priv

enum('N','Y')

N

Process_priv

enum('N','Y')

N

File_priv

enum('N','Y')

N

Grant_priv[1]

enum('N','Y')

N

References_priv

enum('N','Y')

N

Index_priv

enum('N','Y')

N

Alter_priv

enum('N','Y')

N

Show_db_priv

enum('N','Y')

N

Super_priv

enum('N','Y')

N

Create_tmp_table_priv

enum('N','Y')

N

Lock_tables_priv

enum('N','Y')

N

Execute_priv

enum('N','Y')

N

Repl_slave_priv

enum('N','Y')

N

Repl_client_priv

enum('N','Y')

N

Create_view_priv

enum('N','Y')

N

Show_view_priv

enum('N','Y')

N

Create_routine_priv

enum('N','Y')

N

Alter_routine_priv

enum('N','Y')

N

Create_user_priv

enum('N','Y')

N

Event_priv

enum('N','Y')

N

Trigger_priv

enum('N','Y')

N

ssl_type

enum('','ANY','X509','SPECIFIED')

ssl_cipher

blob

NULL

x509_issuer

blob

NULL

x509_subject

blob

NULL

max_questions

int(11) unsigned

0

max_updates

int(11) unsigned

0

max_connections

int(11) unsigned

0

max_user_connections

int(11) unsigned

0

Изначально эта таблица содержит пользователя root без пароля. По умолчанию root может входить с любого хоста, имеет все привилегии и доступ ко всем базам данных.Также в таблице содержится запись для пользователя '%'.

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

Для добавления нового пользователя your _ name , можно выполнить следующие операторы языка (Insert):

Insert into user (host, user, password, ssl_cipher[2] , x509_issuer, x509_subject ) values ('localhost’, 'your_name', password('your_pass'), ‘’, ‘’, ‘’);

Выполнением команды

Select host, user, password from user;

Мы выводим перечисленные поля в виде таблицы

Host

User

Password

%

root

456g879k34df9

Если необходимо выделить все столбцы таблицы, то необходимо набрать * в качестве аргумента команды select .

Чтобы изменения вступили в силу нужно перегрузить сервер, предварительно закончив текущий сеанс работы командой quit .

mysqladmin -u root reload (эта команда перегружает сервер)

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

Mysql/bin/mysql -u your_name -p mysql

Enter password:*******

Если же после этой операции вы не получите приглашение ко входу, то необходимо будет повторить вход в сервер под учетной записью ROOT и назначить необходимые права. Т.о., недостаточно добавить сведения о пользователе в системную БД, дополнительно необходимо назначить права пользователю, после чего можно начинать настраивать таблицы привилегий, вводить новых пользователей, создавать базы данных и таблицы, то есть делать все то, что называется администрированием. Назначить права можно указанием инструкцией INSERT для заполнения соответствующие привилегии (перечень привилегий см.
Таблица 3)

Mysql/bin/mysql -u root

И выполнить следующий запрос к БД:

Mysql>USE MYSQL;

Mysql>GRANT ALL PRIVILEGES ON *.* TO 'your_name'@'localhost [3] ' IDENTIFIED BY 'your_pass' WITH GRANT OPTION;

Mysql>FLUSH PRIVILEGES;

Если пароль был случайно забыт, чтобы его задать по новой, придется стереть файлы mysql.frm mysql.MYI и mysql.MYD из папки с базами данных, затем запустить скрипт mysql_install_db и повторить все по новой. Можно воспользоваться ключом MYSQL и ввести -- skip - grant - tables , при этом все пароли будут имеет пустое поле.

Команда имеет вид mysqld --skip-grant-tables.

Пояснения:

1.Команда insert вставляет данные в таблицу, не забывайте завершать команды ';'.

2.При вводе пароля используйте функцию password(), иначе пароль работать не будет!

3.Все пароли шифруются mysql, поэтому в поле Password вы видите абракадабры. Это делается в целях безопасности.

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

5.При задании имени хоста для входа через сеть рекомендуется явно указывать полное имя хоста, а не '%'. В приведенном выше примере пользователю mary разрешается вход на сервер со всех машин домена tomsk.ru. Можно также указывать IP-адреса машин и маски подсетей для большей безопасности.

Таблица Db

Определяет к каким базам данных каким пользователям и с каких хостов разрешен доступ. В этой таблице можно предоставлять каждому пользователю доступ к базам данных и назначать привилегии. Eсли выполнить команду show columns from db; получим следующее:

Таблица 4 - Структура таблицы Db

Field

Type

Null

Key

Default

Extra

Host

char(60)

PRI

Db

char(32)

PRI

User

char(16)

PRI

Select_priv

char(1)

N

Insert_priv

char(1)

N

Update_priv

char(1)

N

Delete_priv

char(1)

N

Create_priv

char(1)

N

Drop_priv

char(1)

N

· По умолчанию, все привилегии установлены в 'N'. Например, предоставим юзеру mary доступ к базе данных mysql и дадим ему привилегии select , insert и update (описание основных команд mysql будет дано в следующих лабораторных работах, сейчас ваша цель увидеть, как работают таблицы привилегий).

· Для справки:

Insert into db (host, user, db, select_priv, insert_priv, update_priv)

Values (''localhost', 'your_name', mysql, 'Y', 'Y', 'Y');

· Привилегии, устанавливаемые в таблице db, распространяются только на базу данных library. Если же установить эти привилегии в таблице user, то они будут распространяться и на другие базы данных, даже если доступ к ним и не установлен явно.

Таблица Host

Таблица host используется для расширения диапазона доступа в таблице db. К примеру, если доступ к какой-либо базе данных должен быть предоставлен более чем одному хосту, тогда следует оставить пустой колонку host в таблице db, и внести в таблицу host необходимые имена хостов. Выполним команду

show columns from host;

Таблица 5 - Структура таблиц Host

Field

Type

Null

Key

Default

Extra

Host

char(60)

PRI

Db

char(32)

PRI

Select_priv

char(1)

N

Insert_priv

char(1)

N

Update_priv

char(1)

N

Delete_priv

char(1)

N

Create_priv

char(1)

N

Drop_priv

char(1)

N

Как видно из таблицы, здесь также можно задавать привилегии для доступа к базе данных.Они обычно редко используются без необходимости. Все привилегии доступа нужно задавать в таблице db для каждого пользователя, а в таблице host только перечислить имена хостов.Сервер читает все таблицы, проверяет имя пользователя, пароль, имя хоста, имя базы данных, привилегии.Если в таблице db привилегии select, insert установлены в 'Y', а в таблице host в 'N', то в итоге юзер все равно получит 'Y'.Чтобы не вносить путаницы, лучше назначать привилегии в таблице db.

Эти 3 таблицы являются основными.В новых версиях MySQL, начиная с 3.22 добавлены еще 2 таблицы- tables_priv и columns_priv, которые позволяют задать права доступа к определенной таблице в базе данных и даже к определенной колонке. Они работают подобно таблице db, только ссылаются на таблицы и колонки. Также, начиная с версии 3.22 можно использовать команду GRANT для предоставления доступа к базам данных, таблицам и колонкам таблиц, что избавляет от необходимости вручную модифицировать таблицы db, tables_priv и columns_priv. Команда GRANT будет подробно рассмотрена в следующих разделах.

Привилегии, предоставляемые MySQL

Таблица 6 - Привилегии пользователя[4]

Привилегия

Колонка

Где используется

select

Select_priv

таблицы

insert

Insert_priv

таблицы

Update

Update_priv

таблицы

delete

Delete_priv

таблицы

index

Index_priv

таблицы

alter

Alter_priv

таблицы

create

Create_priv

БД, таблицы, индексы

drop

Drop_priv

БД или таблицы

grant

Grant_priv

БД или таблицы

References

References_priv

БД или таблицы

reload

Reload_priv

администрирование сервера

Shutdown

Shutdown_priv

администрирование сервера

Process

Process_priv

администрирование сервера

file

File_priv

доступ к файлам на сервере

Основные утилиты MySQL.

В состав дистрибутива MySQL входят следующие утилиты:

· mysqld

· mysql

· mysqladmin

· mysqlaccess

· mysqlshow

· mysqldump

· isamchk

Утилиты mysqld и mysql были подробно рассмотрены ранее, поэтому возвращаться к ним не будем. Кратко рассмотрим остальные.

Mysqladmin

Утилита для администрирования сервера. Может использоваться администратором, а также некоторыми пользователями, которым предоставлены определенные привилегии, например – Reload_priv, Shutdown_priv, Process_priv и File_priv . Данная команда может использоваться для создания баз данных, изменения пароля пользователя(администратор может изменить пароль любому пользователю, а рядовой пользователь – только свой собственный), перезагрузки и остановки сервера, просмотра списка процессов, запущенных на сервере. Mysqladmin поддерживает следующие команды:

Таблица 7 - Опции команды MySQLadmin

Create [database_name]

Создает базу данных

Drop [database_name]

Удаляет базу данных и все таблицы в ней

Reload

Перезагружает сервер

Shutdown

Останавливает работу сервера MySQL

Processlist

Выводит список процессов на сервере

Status

Выводит сообщение о статусе сервера

Пример использования mysqladmin для изменения пароля:

mysqladmin -u your_name password your_pass

Следует заметить, что в случае использования mysqladmin для установки пароля, не требуется использование функции password().Mysqladmin сам заботится о шифровании пароля.

Mysqlaccess

Используется для проверки привилегий пользователя для доступа к конкретной базе данных. Общий синтаксис:

mysqlaccess [host] [user] [db] опции

Полезная утилита для проверки прав доступа пользователя, если он получает сообщение Access denied, при попытке соединиться с базой данных.

Опции:

Таблица 8 - Опции команды MySQLAccess

-?, --help

Справка

-u, --user=[username]

Имя пользователя

-p, --password=[password]

Пароль пользователя

-h, --host=[hostname]

Имя хоста для проверки прав доступа

-d, --db=[dbname]

Имя базы данных для проверки прав доступа

-U, --superuser=[susername]

Имя суперпользователя(root)

-P, --spassword=[spassword]

Пароль администратора

-b, --brief

Выводит краткие сведения о таблице

Mysqlshow

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

mysqlshow [опции] [database [table [field]]]

Mysqlshow может использовать следующие параметры:

Таблица 9 - Параметры команды Mysqlshow

-?, --help

Справка

-h, --host=[hostname]

Имя сервера

-k, --key

Показать ключи для таблицы

-p, --password=[password]

Пароль пользователя

-u, --user=[username]

Имя пользователя

-p, --port=[port]

Порт для связи

-V, --version

Вывести информацию о версии

Если ввести mysqlshow без аргументов, будут показаны все базы данных, если указать имя БД, будут показаны все таблицы в ней.

Команды

mysqlshow

mysqlshow mysql

Mysqldump

Программа mysqldump используется для создания дампа содержания базы данных MySQL. Она пишет инструкции SQL в стандартный вывод. Эти инструкции SQL могут быть переназначены в файл. Можно резервировать базу данных MySQL, используя mysqldump, но при этом Вы должны убедиться, что в этот момент с базой данных не выполняется никаких других действий. А то mysqldump Вам такого нарезервирует...

Программа mysqldump поддерживает следующие параметры (Вы можете использовать короткую или подробную версию):

Таблица 10 - Опции команды MySQLdump

-#, --debug=[options]

Вывести в протокол отладочную информацию. В общем виде 'd:t:o, filename`.

-?, --help

Справка.

-c, --compleat-insert

Генерируйте полные инструкции insert (не исключая значений, которые соответствуют значениям столбца по умолчанию).

-h, --host=[hostname]

Соединиться с сервером hostname.

-d, --no-data

Экспорт только схемы информации (исключая данные).

-t, --no-create-info

Экспорт только данных, исключая информацию для создания таблицы. Противоположность -d.

-p, --password=[password]

Пароль пользователя, для соединения с сервером MySQL. Обратите внимание, что не должно быть пробела между -p и паролем.

-q, --quick

Не буферизовать результаты запроса, дамп выдать непосредственно к STDOUT.

-u, --user=[username]

Имя пользователя. Если не задано, используется текущий логин.

-v, --verbose

Вывести подробную информацию относительно различных стадий выполнения mysqldump.

-P, --port=[port]

Порт для связи.

-V, --version

Информация о версии.

Вы можете направить вывод mysqldump в клиентскую программу MySQL, чтобы копировать базу данных. ПРИМЕЧАНИЕ: Вы должны убедиться, что база данных не изменяется в это время, иначе Вы получите противоречивую копию!

Для справки:

mysqldump -u root -p mysql user>mysql-1.sql mysqldump -u root mysql>mysql-2.sql

Примечание флаг –p используется в случае, если пользователь наделен паролем.

После выполнения этой команды у нас появился файл mysql-1.sql и mysql-2.sql. Загрузим их в текстовый редактор, чтобы поподробнее изучить, и, возможно, немного поправить.

Задание

Запустите сервер MySQL. Зарегистрируйте своего пользователя в консольном приложении, задайте ему права.

С помощью утилиты Mysqlshow выполните команду на просмотр структуры и состав таблиц базы Mysql. Приведите в отчете её схему. С помощью утилиты Mysqldump получите полный дамп базы Mysql (данные и таблицы), а также отдельные дампы таблиц и данных.

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

1. Каким способом возможен запуск серверной части СУБД.

2. Что такое привилегия. Каково её предназначение.

3. Какие основные утилиты входят в состав СУБД, какие функции они выполняют.

Лабораторная работа №2. Моделирование баз данных средствами Erwin

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

Основные сведения

Система ERwin поддерживает прямое и обратное моделирование баз данных. При прямом моделировании схема базы данных описывается в прямом виде с использованием диаграммы сущность-связь. Сущности на диаграмме представляются прямоугольниками. Каждый прямоугольник может иметь различные визуальные атрибуты. Каждой сущности должно быть присвоено уникальное имя. Имена сущностей необходимо задавать в единственном числе. Это определяется тем, что система всегда оперирует отдельными экземплярами сущности. При этом отдельные экземпляры сущности рассматриваются как объекты, а сущности – как класс объектов. Если сущности были описаны при моделировании в BPwin, то их можно просто импортировать в ERwin. Пример диаграммы с созданными сущностями приведен на рисунке.

Рисунок 4 - Пример диаграммы с созданными сущностями

Построение моделей в ERwin

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

ERwin предоставляет возможности создавать и управлять этими двумя различными уровнями представления одной диаграммы (модели), равно как и иметь много вариантов отображения на каждом уровне. Термин "логический уровень" в ERwin соответствует концептуальной модели.

Этапы построения информационной модели .

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

Erwin создает визуальное представление (модель данных) для решаемой задачи. Это представление может использоваться для детального анализа, уточнения и распространения документации, необходимой в цикле разработки. Однако ERwin далеко не только инструмент для рисования. ERwin автоматически создает базу данных (таблицы, индексы, хранимые процедуры, триггеры для обеспечения ссылочной целостности и другие объекты, необходимые для управления данными).

Создание сущности.

Для внесения сущности в модель необходимо щелкнуть по кнопке сущности на панели инструментов (Erwin Toolbox) , затем - по тому месту на диаграмме, где необходимо расположить новую сущность. Щелкнув правой кнопкой мыши по сущности и выбрав из всплывающего меню пункт Entity Editor, можно вызвать диалог Entity Editor, в котором определяются имя, описание и комментарии сущности.

Каждая сущность должна быть полностью определена с помощью текстового описания в закладке Definition. Эти определения полезны как на логическом уровне, поскольку позволяют понять, что это за объект, так и на физическом уровне, поскольку их можно экспортировать как часть схемы и использовать в реальной БД (CREATE COMMENT on entity_name ). Закладки Note, Note2, Note3, UDP (User Defined Properties - Свойства, определенные пользователем) служат для внесения дополнительных комментариев и определений к сущности.

В закладке Icon каждой сущности можно поставить в соответствие изображение, которое будет отображаться в режиме просмотра модели на уровне иконок и изображение, которое будет отображаться на всех других уровнях.

Закладка UDP диалога Entity Editor служит для определения свойств, определяемых пользователем (User - Defined Properties). При нажатии на кнопку этой закладки вызывается диалог User - Defined Property Editor (также вызывается из меню Edit/UDPs). В нем необходимо указать вид объекта, для которого заводится UDP (диаграмма в целом, сущность, атрибут и т.д.) и тип данных. Для внесения нового свойства следует щелкнуть в таблице по кнопке и внести имя, тип данных, значение по умолчанию и определение.

Создание атрибутов.

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

Рисунок 5 - Создание нового домена

Рисунок 6 - Указание свойств нового домена

Рисунок 7 - Значение по умолчанию для нового домена

Рисунок 8 - Использование домена для указания типа данных атрибуту.[5]

Для описания атрибутов следует, щелкнув правой кнопкой по сущности, выбрать в появившемся меню пункт Attribute Editor. Появится диалог Attribute Editor.

Если щелкнуть по кнопке New, то в появившемся диалоге New Attribute можно указать имя атрибута, имя соответствующей ему в физической модели колонки и домен. Домен атрибута будет использоваться при определении типа колонки на уровне физической модели.

Для атрибутов первичного ключа в закладке General диалога Attribute Editor необходимо сделать пометку в окне выбора Primary Key.
Закладки Definition, Note и UDP несут те же функции, что и при определении сущности, но на уровне атрибутов.

Для большей наглядности диаграммы каждый атрибут можно связать с иконкой. Это можно сделать при помощи списка выбора Icon в закладке General.

Очень важно дать атрибуту правильное имя. Атрибуты должны именоваться в единственном числе и иметь четкое смысловое значение.

Согласно синтаксису IDEF1X, имя атрибута должно быть уникальным в рамках модели (а не только в рамках сущности!). По умолчанию при попытке внесения уже существующего имени атрибута ERwin переименовывает его. Например, если атрибут Комментарий уже существует в модели, другой атрибут (в другой сущности) будет назван Комментарий/2, затем Комментарий/3 и т.д.
При переносе атрибутов внутри и между сущностями можно воспользоваться техникой drag&drop, выбрав кнопку в палитре инструментов.

Для создания новой связи следует выбрать идентифицирующую или неидентифицирующую связь в палитре инструментов (ERwin Toolbox), щелкнуть сначала по родительской, а затем по дочерней сущности.
В палитре инструментов кнопка соответствует идентифицирующей связи, кнопка связи многие-ко-многим и кнопка соответствует неидентифицирующей связи. Для редактирования свойств связи следует щелкнуть правой кнопкой мыши по связи и выбрать на контекстном меню пункт Relationship Editor.

В закладке General появившегося диалога можно задать мощность, имя и тип связи.

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

Мощность связи (Cardinality) - служит для обозначения отношения числа экземпляров родительской сущности к числу экземпляров дочерней.
Различают четыре типа мощности:

· общий случай, когда одному экземпляру родительской сущности соответствуют 0, 1 или много экземпляров дочерней сущности, не помечается каким-либо символом;

· символом P помечается случай, когда одному экземпляру родительской сущности соответствуют 1 или много экземпляров дочерней сущности (исключено нулевое значение);

· символом Z помечается случай, когда одному экземпляру родительской сущности соответствуют 0 или 1 экземпляр дочерней сущности (исключены множественные значения);

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

По умолчанию символ, обозначающий мощность связи, не показывается на диаграмме. Для отображения имени следует в контекстном меню, которое появляется, если щелкнуть правой кнопкой мыши по любому месту диаграммы, не занятому объектами модели, выбрать пункт Display Options/Relationship и затем включить опцию Cardinality.

Тип связи (идентифицирующая/неидентифицирующая).

В IDEF1X различают зависимые и независимые сущности. Тип сущности определяется ее связью с другими сущностями. Идентифицирующая связь устанавливается между независимой (родительский конец связи) и зависимой (дочерний конец связи) сущностями. Когда рисуется идентифицирующая связь, ERwin автоматически преобразует дочернюю связь в зависимую. Зависимая сущность изображается прямоугольником со скругленными углами.

Экземпляр зависимой сущности определяется только через отношение к родительской сущности. При установлении идентифицирующей связи атрибуты первичного ключа родительской сущности автоматически переносятся в состав первичного ключа дочерней сущности. Эта операция дополнения атрибутов дочерней сущности при создании связи называется миграцией атрибутов. В дочерней сущности новые атрибуты помечаются как внешние ключи - (FK).

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

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

Для неидентифицирующей связи можно указать обязательность (Nulls в закладке General диалога Relationship Editor). В случае обязательной связи (No Nulls) при генерации схемы БД атрибут внешнего ключа получит признак NOT NULL, несмотря на то, что внешний ключ не войдет в состав первичного ключа дочерней сущности. В случае необязательной связи (Nulls Allowed) внешний ключ может принимать значение NULL. Необязательная неидентифицирующая связь помечается прозрачным ромбом со стороны родительской сущности

Имя связи (Verb Phrase) - фраза, характеризующая отношение между родительской и дочерней сущностями. Для связи один-ко-многим идентифицирующей или неидентифицирующей достаточно указать имя, характеризующей отношение от родительской к дочерней сущности (Parent-to-Child). Для связи многие-ко-многим следует указывать имена как Parent-to-Child, так и Child-to-Parent. Для отображения имени следует в контекстном меню, которое появляется, если щелкнуть правой кнопкой мыши по любому месту диаграммы, не занятому объектами модели, выбрать пункт Display Options/Relationship и затем включить опцию Verb Phrase.

Имя роли или функциональное имя (Rolename) - это синоним атрибута внешнего ключа, который показывает, какую роль играет атрибут в дочерней сущности. Задать имя роли можно в закладке Rolename/RI Actions диалога Relationship Editor.

Создание ключей.

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

Первичный ключ (primary key) - это атрибут или группа атрибутов, однозначно идентифицирующие экземпляр сущности. Атрибуты первичного ключа на диаграмме не требуют специального обозначения - это те атрибуты, которые находятся в списке атрибутов выше горизонтальной линии. При внесении нового атрибута в диалоге Attribute Editor для того, чтобы сделать его атрибутом первичного ключа, нужно включить флажок Primary Key в нижней части закладки General. На диаграмме ключевой атрибут можно внести в состав первичного ключа, воспользовавшись режимом переноса атрибутов (кнопка в палитре инструментов).

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

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

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

Альтернативный ключ (Alternative Key) - это потенциальный ключ, не ставший первичным.

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

На диаграмме атрибуты альтернативных ключей обозначаются как (Akn.m.), где n - порядковый номер ключа, m - порядковый номер атрибута в ключе. Когда альтернативный ключ содержит несколько атрибутов, (Akn.m.) ставится после каждого.

Внешние ключи (Foreign Key) создаются автоматически, когда связь соединяет сущности: связи образуют ссылку на атрибуты первичного ключа в дочерней сущности и эти атрибуты образуют внешний ключ в дочерней сущности (миграция ключа). Атрибуты внешнего ключа обозначаются символом (FK) после своего имени.

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

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

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

После указания всем атрибутам формата данных необходимо созданную логическую модель преобразовать в физическую. Для этого необходимо в Tools выбрать Derive New Model , где в качестве Target Databases выберите ODBC / Generic (для использования в СУБД MySQL) см. Рисунок 9. Наша модель (см Рисунок 4) будет преобразована к виду см.Рисунок 11.

Далее выбрав в меню Tools / Forward Engineer / Shema Generation и задав необходимые настройки, получим в меню Preview код на языке SQL для реализации схемы БД в СУБД MySQL.

Рисунок 9 - Преобразование логической модели в физическую

Рисунок 10 - Физическая модель с указанием формата данных.

Рисунок 11 - Генерация кода SQL

Задание

1. Выполните построение диаграммы с заданными сущностями (прямое моделирование) для заданной предметной области.

2. Задайте атрибуты для каждой определенной сущности. При задании атрибутов используйте домены.

3. Введите связи между сущностями. Присвойте связям уникальные имена.

4. Используя СУБД MYSQL, решите прямую генерацию базы данных для проектируемой информационной.

5. Отчет должен содержать концептуальную модель и физическую базу данных в СУБД MYSQL.

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

1. В чем состоит различие логического и физического уровней представления моделей данных с помощью ERwin?

2. В чем различие между моделями данных, представленных в форме диаграммы сущность-связь, на основе ключей и в виде полной атрибутивной модели?

3. Какие основные компоненты содержат модели данных, представленные по методологии IDEF1X?

Лабораторная работа №3. Создание баз данных и таблиц в среде MYSQL. Информационное наполнение.

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

Содержание работы и методические указания к ее выполнению

1. Ознакомиться с возможностями работы клиентского приложения MySQL .

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

Функция

Описание

create database DB_name

создание базы данных

Use database

выбор существующей базы данных

close database

закрытие файлов текущей базы данных

drop database

удаление базы данных

create table

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

alter table

модификация структуры базы данных

drop table

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

insert

добавление одной или нескольких строк в таблицу

delete

удаление одной или нескольких строк из таблицы

update

модификация одной или нескольких строк таблицы

LOAD DATA INFILE

загрузка данных в таблицы из файла

3. Создать базу данных.

Создание базы данных в MySQL производится с помощью утилиты mysqladmin. Изначально существует только БД mysql для администратора и БД test, в которую может войти любой пользователь и которая по умолчанию пуста. Приведенный ниже пример иллюстрирует создание базы данных.

Mysql/bin>mysqladmin -u root -p create data_name

Enter password:******

Database "data_name" created.

mysqlbin>

Где data_name – имя создаваемой БД. Проверить, что БД создана можно ранее рассмотренной командой Show databases или утилитой mysqlshow .

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

Mysql/bin>mysql -u root -p data1

Enter password:******

Welcome to MySQL monitor.

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

mysql>use data1

Database changed.

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

CREATE DATABASE Base_name

Где Base_name имя создаваемой базы данных. В созданной базе можно создавать таблицы и вводить информацию. Указанные операции можно выполнить, используя специализированное программное обеспечение, например MySQL-Front, запуск которого осуществляется из меню ПУСК/ПРОГРАММЫ (см. рис 1, 2).

Необходимо указать:

· Имя;

· Хост;

· Пароль;

· Порт;

· Имя БД (при необходимости).

Рисунок 12 - Запуск MySQL-front

Рисунок 13 - Создание БД в среде MySQL-front

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

Рисунок 14 - Использование синтаксиса SQL

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

CREATE TABLE , синтаксис которой приведен в приложении. Для таблицы J:

CREATE TABLE j (

Jnum varchar(6) NOT NULL default '',

Jnam varchar(20) default NULL,

Ci varchar(20) default NULL,

PRIMARY KEY (Jnum)

) TYPE=MyISAM;

Значками /* */ - выделяются комментарии в тексте запроса.

При создании таблиц выполнить такую реализацию, чтобы она отражала структуру таблиц, указанную ниже (таблице S, P, J, SPJ ) и должны быть наложены следующие ограничения:

- поля номер_поставщика, номер_детали, номер_изделия во всех таблицах имеет символьный тип и длину 6 (varchar (6));

- поля рейтинг, вес и количество имеют целочисленный тип (integer ) ;

- поля фамилия, город (поставщика, детали или изделия), название (детали или изделия) имеют символьный тип и длину 20 (varchar (20));

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

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

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

а)Вручную, используя команду insert into ;

Пример ввода данных вручную (команда INSERT ):

mysql>insert into J (Jnum, Jnam, Ci)values ('J1',' Жесткий диск ','Париж ' );

или

mysql>insert into J values ('J1',' Жесткий диск ','Париж ');

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

Таким образом SQL инструкция имеет следующий вид

INSERT INTO table_name (id, name) VALUES ('id_value', 'name_value' );

Записать и выполнить совокупность запросов для занесения нижеприведенных данных в созданные таблицы

insert into имя_таблицы [(поле [,поле]...)] values (константа [,константа]...)

б)Загрузить данные из текстового файла, что является более предпочтительным, особенно если нужно ввести несколько тысяч записей.

Синтаксис команды LOAD DATA INFILE.

DATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE]

INTO TABLE tbl_name

[FIELDS

[TERMINATED BY 't']

[OPTIONALLY] ENCLOSED BY '']

[ESCAPED BY '' ]]

[LINES TERMINATED BY 'n']

[IGNORE number LINES]

[(col_name,...)]

Пример :

LOAD DATA LOCAL INFILE '/MyDocs/categories.txt' REPLACE
INTO TABLE category FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED
BY '\"' LINES TERMINATED BY '\n'

В данном случае файл categories.txt находится на машине под управлением MS Windows, в каталоге C:\MyDocs .

Обратите внимание на UNIX стиль написания пути. Слово

REPLACE

В SQL запросе означает, что необходимо замещать записи с совпадающими значениями ключей.

INTO TABLE[6]

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

FIELDS TERMINATED BY ';' [7]

указывает разделители полей, порядок полей должен быть таким же, как и в таблице назначения,

OPTIONALLY ENCLOSED BY '\"'

указывает, что поля VARCHAR взяты в двойные кавычки, и

LINES TERMINATED BY '\r' [8]

в) Использовать утилиту mysqlimport также для загрузки данных из текстового файла.

Эти и другие операции можно выполнить также и в программе MySQL-Front.

Рисунок 15 - Использование программы MySQL-front для заполнения таблиц данными из файла


Таблица поставщиков (S)

Hомеp поставщика

Фамилия

Рейтинг

Город

S1

Смит

20

Лондон

S2

Джонс

10

Париж

S3

Блейк

30

Париж

S4

Кларк

20

Лондон

S5

Адамс

30

Афины

Таблица деталей (P)

Номер детали

Название

Цвет

Вес

Город

P1

Гайка

Красный

12

Лондон

P2

Болт

Зеленый

17

Париж

P3

Винт

Голубой

17

Рим

P4

Винт

Красный

14

Лондон

P5

Кулачок

Голубой

12

Париж

P6

Блюм

Красный

19

Лондон

Таблица изделий (J)

Номер изделия

Название

Город

J1

Жесткий диск

Париж

J2

Перфоратор

Рим

J3

Считыватель

Афины

J4

Принтер

Афины

J5

Флоппи-диск

Лондон

J6

Терминал

Осло

J7

Лента

Лондон

Таблица поставок (SPJ)

Номер поставщика

Номер детали

Номер изделия

Количество

S1

P1

J1

200

S1

P1

J4

700

S2

P3

J1

400

S2

P3

J2

200

S2

P3

J3

200

S2

P3

J4

500

S2

P3

J5

600

S2

P3

J6

400

S2

P3

J7

800

S2

P5

J2

100

S3

P3

J1

200

S3

P4

J2

500

S4

P6

J3

300

S4

P6

J7

300

S5

P2

J2

200

S5

P2

J4

100

S5

P5

J5

500

S5

P5

J7

100

S5

P6

J2

200

S5

P1

J4

100

S5

P3

J4

200

S5

P4

J4

800

S5

P5

J4

400

S5

P6

J4

500

Убедиться в успешности выполненных действий. При необходимости исправить ошибки. Для ускорения процесса ввода данных рекомендуется воспользоваться командой LOAD DATA (синтаксис см. в приложении), предварительно скопировав содержимое перечисленных таблиц сначала в Excel, а оттуда в текстовые файлы. Такой порядок необходим, для того, чтобы текстовый файл был с табуляцией.

5. Выполнить модификацию структуры таблицы SPJ, добавив в SPJ поле с датой поставки. Убедиться в успешности выполненных действий. При необходимости исправить ошибки (команда Alter table).

6. Уничтожить созданные таблицы, предварительно сохранив инструкции для восстановления структуры БД и информационного наполнения, используя средства работы СУБД[9] . Убедиться в успешности выполненных действий.

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

Рисунок 16 - Концептуальная модель учебной базы данных

Проверить результат заполнения таблиц, написав и выполнив простейший запрос:

select * from имя_таблицы

При наличии ошибок выполнить корректировку, исправив либо удалив ошибочные строки таблиц

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

1. В каких режимах возможно создание базы данных?

2. Какие типы данных допустимы при создании таблицы?

3. Как выполнить создание таблицы средствами СУБД?

4. Как выполнить создание таблицы средствами языка SQL?

5. Как разделяются операторы SQL в случае нескольких операторов в запросе?

6. Каким образом выполнить простейшие операции вставки строк данных в таблицу средствами SQL?

7. Каким образом выполнить простейшие операции модификации строк таблицы средствами SQL?

8. Каким образом выполнить просмотр таблицы?

9. Как получить информацию о структуре таблицы в рамках СУБД MySQL?

Лабораторная работа №4 Создание запросов и модификация таблиц базы данных.

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

Содержание работы и методические указания к ее выполнению

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

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

insert - добавление одной или нескольких строк в таблицу;

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

update - модификация одной или нескольких строк таблицы;

union - объединение запросов в один запрос.

2. Изучить состав, правила и порядок использования ключевых фраз оператора select:

select - описание состава данных, которые следует выбрать по запросу (обязательная фраза);

from - описание таблиц, из которых следует выбирать данные (обязательная фраза);

where - описание условий поиска и соединения данных при запросе;

group by - создание одной строки результата для каждой группы (группой называется множество строк, имеющих одинаковые значения в указанных столбцах);

having - наложение одного или более условий на группу;

order by - сортировка результата выполнения запроса по одному или нескольким столбцам;

into outfile - создание файла, в который будет осуществлен вывод результатов соответствующего запроса.

Порядок следования фраз в команде select должен соответствовать приведенной выше последовательности. Для лучшего понимания механизма функционирования выполните следующие упражнения:

I. Простые запросы на языке SQL

Запрос на языке SQL формируется с использованием оператора Select. Оператор Select используется

  • для выборки данных из базы данных;
  • для получения новых строк в составе оператора Insert;
  • для обновления информации в составе оператора Update.

В общем случае оператор Select содержит следующие семь спецификаторов, расположенных в операторе в следующем порядке:

  • спецификатор Select;
  • спецификатор From;
  • спецификатор Where;
  • спецификатор Group by;
  • спецификатор Having;
  • спецификатор Order by;

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

Спецификатор Where добавляется для выборки определенных строк или указания условия соединения. Спецификатор Order by добавляется для изменения порядка получаемых данных. Спецификатор Into temp добавляется для сохранения этих результатов в виде таблицы с целью выполнения последующих запросов. Два дополнительных спецификатора оператора Select - Group by (спецификатор группирования) и Having (спецификатор условия выборки группы) - позволяют выполнять более сложные выборки данных.

У п р а ж н е н и я

1. Выбор всех строк и столбцов таблицы.

Пример .

Выдать полную информацию о поставщиках.

Select * from S

Результат: таблица S в полном объеме.

Подготовьте запрос и проверьте полученный результат.

2. Измение порядка следования столбцов.

Пример .

Выдать таблицу S в следующем порядке: фамилия, город, рейтинг, номер_поставщика.

Select фамилия, город, рейтинг, номер_поставщика from S

Результат: таблица S в требуемом порядке.

Подготовьте запрос и проверьте полученный результат.

3. Выбор заданных столбцов.

Пример .

Выдать номера всех поставляемых деталей.

Select номер_детали from SPJ

Результат: столбец номер_детали таблицы SPJ

Подготовьте запрос и проверьте полученный результат.

4. Выбор без повторения.

Пример .

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

Select distinct номер _ детали from SPJ

Результат:

номер_детали

P1

P2

P3

P4

P5

P6

Подготовьте запрос и проверьте полученный результат.

5. Использование в запросах констант и выражений.

Пример .

Select номер_детали, "вес в граммах", вес*454 from P

Результат:

P1 вес в граммах=5448

---------------------

---------------------

P6 вес в граммах=8226

Подготовьте запрос и проверьте полученный результат.

6.Ограничение в выборке.

Пример .

Выдать номера всех поставщиков, находящихся в Париже с рейтингом > 20.

Select номер_поставщика from S where город="Париж" and рейтинг>20

Результат:

номер_поставщика

S3

Подготовьте запрос и проверьте полученный результат.

7. Выборка с упорядочиванием.

Пример .

Выдать номера поставщиков, находящихся в Париже в порядке убывания рейтинга.

Select номер_поставщика, рейтинг from S where город="Париж" order by рейтинг desc

Результат:

номер_поставщика

рейтинг

S3

30

S2

10

Подготовьте запрос и проверьте полученный результат.

8. Упорядочивание по нескольким столбцам.

Пример .

Выдать список поставщиков, упорядоченных по городу, в пределах города - по рейтингу.

Select * from S order by 4, 3

Результат:

Hомеp_поставщика

Фамилия

Рейтинг

Гоpод

S5

Адамс

30

Атенс

S1

Смит

20

Лондон

S4

Кларк

20

Лондон

S2

Джонс

10

Париж

S3

Блейк

30

Париж

Подготовьте запрос и проверьте полученный результат.

9. Фраза in ( not in ).

Пример .

Выдать детали, вес которых равен 12, 16 или 17.

Select номер_детали, название, вес from P where вес in (12, 16, 17)

Результат:

номер_детали

Название

вес

P1

Гайка

12

P2

Болт

17

P3

Винт

17

P5

Кулачок

12

Подготовьте запрос и проверьте полученный результат.

12. Выбор по шаблону.

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

Включение в выражение ключевого слова NOT порождает условие c обратным смыслом. Ключевое слово LIKE соответствует стандарту ANSI.


СИМВОЛ

ЗНАЧЕНИЕ

LIKE

%

Заменяет последовательность символов

-

Заменяет любой одиночный символ

\

Отменяет специальное назначение следующего за ним символа

Примеры.

а) Выбрать список деталей, начинающихся с буквы "Б"[10]

Select номер_детали, название, вес from P where название like "Б%"

Результат:

номер_детали

название

вес

P5

Болт

12

P6

Блюм

19

II. Использование функций

1. Агрегатные функции.

Примеры.

а) Выдать общее количество поставщиков.

Select count (*) from S

Результат: 5

Подготовьте запрос и проверьте полученный результат.

б) Выдать общее количество поставщиков, поставляющих в настоящее время детали.

Select count ( distinct номер_поставщика ) from SPJ

Результат: 4

Подготовьте запрос и проверьте полученный результат.

в) Выдать количество поставок для детали P2.

Select count (*) from SPJ where номер_детали='P2'

Результат: 5

Подготовьте запрос и проверьте полученный результат.

г) Выдать общее количество поставляемых деталей 'P2'.

Select sum (количество) from SPJ where номер_детали=' P 2'

Результат: 1000

Подготовьте запрос и проверьте полученный результат.

д) Выдать средний, минимальный и максимальный объем поставок для поставщика S1 с соответствующим заголовком.

Select avg(количество) average, min(количество) minimum,

max(количество) maximum from SPJ where номер_поставщика='S1'

Результат:

average

minimum

maximum

216.6

100

400

Подготовьте запрос и проверьте полученный результат.

2. Ниже привиден перечень всех функций, используемых в операторе Select

Функции

select_expression может содержать следующие функции и операторы:

+ - * /

Арифметические действия.

%

Остаток от деления (как в C)

| &

Битовые функции (используется 48 бит).

- С

Мена знака числа.

( )

Скобки.

BETWEEN(A, B, C)

(A >= B) AND (A <= C).

BIT_COUNT()

Количество бит.

ELT(N, a, b, c, d)

Возвращает a, если N == 1, b, если N == 2 и т. д. a, b, c, d строки.

ПРИМЕР :

ELT(3, "First", "Second", "Third", "Fourth")
вернет "Third".

FIELD(Z, a, b, c)

Возвращает a, если Z == a, b, если Z == b и т. д. a, b, c, d строки.

ПРИМЕР :

FIELD("Second", "First", "Second", "Third", "Fourth")
вернет "Second".

IF(A, B, C)

Если A истина (!= 0 and != NULL), то вернет B, иначе вернет C.

IFNULL(A, B)

Если A не null, вернет A, иначе вернет B.

ISNULL(A)

Вернет 1, если A == NULL, иначе вернет 0. Эквивалент ('A == NULL').

NOT !

NOT, вернет TRUE (1) или FALSE (0).

OR, AND

Вернет TRUE (1) или FALSE (0).

SIGN()

Вернет -1, 0 или 1 (знак аргумента).

SUM()

Сумма столбца.

= <> <= < >= >

Вернет TRUE (1) или FALSE (0).

expr LIKE expr

Вернет TRUE (1) или FALSE (0).

expr NOT LIKE expr

Вернет TRUE (1) или FALSE (0).

expr REGEXP expr

Проверяет строку на соответствие регулярному выражению expr.

select_expression может также содержать один или большее количество следующих математических функций.

ABS()

Абсолютное значение (модуль числа).

CEILING()

()

EXP()

Экспонента.

FORMAT(nr, NUM)

Форматирует число в формат '#, ###, ###.##' с NUM десятичных цифр.

LOG()

Логарифм.

LOG10()

Логарифм по основанию 10.

MIN(), MAX()

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

MOD()

Остаток от деления (аналог %).

POW()

Степень.

ROUND()

Округление до ближайшего целого числа.

RAND([integer_expr])

Случайное число типа float, 0 <= x <= 1.0, используется integer_expr как значение для запуска генератора.

SQRT()

Квадратный корень.

select_expression может также содержать одну или больше следующих строковых функций.

CONCAT()

Объединение строк.

INTERVAL(A, a, b, c, d)

Возвращает 1, если A == a, 2, если A == b... Если совпадений нет, вернет 0. A, a, b, c, d... строки.

INSERT(org, strt, len, new)

Заменяет подстроку org[strt...len(gth)] на new. Первая позиция строки=1.

LCASE(A)

Приводит A к нижнему регистру.

LEFT()

Возвращает строку символов, отсчитывая слева.

LENGTH()

Длина строки.

LOCATE(A, B)

Позиция подстроки B в строке A.

LOCATE(A, B, C)

Позиция подстроки B в строке A, начиная с позиции C.

LTRIM(str)

Удаляет все начальные пробелы из строки str.

REPLACE(A, B, C)

Заменяет все подстроки B в строке A на подстроку C.

RIGHT()

Get string counting from right.

RTRIM(str)

Удаляет хвостовые пробелы из строки str.

STRCMP()

Возвращает 0, если строки одинаковые.

SUBSTRING(A, B, C)

Возвращает подстроку из A, с позиции B до позиции C.

UCASE(A)

Переводит A в верхний регистр.

Еще несколько просто полезных функций, которые тоже можно применить в select_expression.

CURDATE()

Текущая дата.

DATABASE()

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

FROM_DAYS()

Меняет день на DATE.

NOW()

Текущее время в форматах YYYYMMDDHHMMSS или "YYYY-MM-DD HH:MM:SS". Формат зависит от того в каком контексте используется NOW(): числовом или строковом.

PASSWORD()

Шифрует строку.

PERIOD_ADD(P:N)

Добавить N месяцев к периоду P (в формате YYMM).

PERIOD_DIFF(A, B)

Возвращает месяцы между A и B. Обратите внимание, что PERIOD_DIFF работает только с датами в форме YYMM или YYYMM.

TO_DAYS()

Меняет DATE (YYMMDD) на номер дня.

UNIX_TIMESTAMP([date])

Возвращает метку времени unix, если вызвана без date (секунды, начиная с GMT 1970.01.01 00:00:00). При вызове со столбцом TIMESTAMP вернет TIMESTAMP.

date может быть также строкой DATE, DATETIME или числом в формате YYMMDD (или YYYMMDD).

USER()

Возвращает логин текущего пользователя.

WEEKDAY()

Возвращает день недели (0 = понедельник, 1 = вторник, ...).

Групповые функции в операторе select :

Следующие функции могут быть использованы в предложении GROUP:

AVG()

Среднее для группы GROUP.

SUM()

Сумма элементов GROUP.

COUNT()

Число элементов в GROUP.

MIN()

Минимальный элемент в GROUP.

MAX()

Максимальный элемент в GROUP.

Задание :

1. Выполнить проверку запросов из:

· 1го раздела (2, 6, 7, 9, 12), 2го раздела (а, б, д)

2. Подготовить 3 запроса с использованием различных функций работа с полем дата, со строковыми данными (в том числе групповых).

3. Подготовить и выполнить средствами СУБД MySQL 4 запроса по выборке информации из таблиц базы данных с использованием агрегатных функций..

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

Варианты заданий на составление запросов по выборке информации из таблиц базы данных

Вариант 1.

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

2. Выдать все триплеты "номер поставщика, номер детали и номер изделия", такие, что в каждом триплете указанные поставщик, деталь и изделие не являются попарно соразмещенными в одном городе.

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

4. Выдать номера и фамилии поставщиков, поставляющих детали для какого-либо изделия с деталью P1 в количестве, большем, чем средний объем поставок детали P1 для этого изделия.

Вариант 2.

1. Выдать общее количество деталей P1, поставляемых поставщиком S1.

2. Выдать все пары названий городов, таких, что какой-либо поставщик из первого города поставляет детали для некоторого изделия, изготовляемого во втором городе.

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

4. Выдать номера деталей, поставляемых каким-либо поставщиком из Лондона, для изделия, изготавливаемого также в Лондоне.

Вариант 3.

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

2. Выдать общее число изделий (не деталей), для которых поставляет детали поставщик S1.

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

4. Выдать все триплеты "номер поставщика, номер детали и номер изделия", такие, что в каждом триплете указанные поставщик, деталь и изделие являются попарно соразмещенными в одном городе.

Вариант 4.

1. Выдать номера и фамилии поставщиков, поставляющих по крайней мере одну деталь, поставляемую по крайней мере одним поставщиком, который поставляет по крайней мере одну красную деталь. Т.е. необходимо выдать полные сведения о всех поставщиках которые поставляют такие детали, которые есть в поставках поставщиков, поставляющих красные детали.

2. Выдать список деталей, поставляющихся для всех изделий, изготавливаемых в Лондоне.

3. Выдать номера деталей, поставляемых каким-либо поставщиком из Лондона.

4. Выдать номера деталей, поставляемых для какого-либо изделия из Лондона.

Вариант 5.

1. Выдать номера изделий, для которых детали поставляются по крайней мере одним поставщиком не из того же самого города, что и изделие.

2. Выдать список всех поставок, в которых количество деталей находится в диапазоне от 300 до 750 включительно.

3. Выдать номера изделий, использующих, по крайней мере, одну деталь, поставляемую поставщиком S1. Т.е. показать такие изделия, для производства которых пригодились бы детали, поставляемые поставщиком S1.

4. Выдать номера и названия деталей, поставляемых для какого-либо изделия в Лондоне.

Варианты заданий на составление запросов по модификации информации из таблиц базы данных

Вариант 1.

1. Увеличить на 10 рейтинг всех поставщиков, рейтинг которых в настоящее время меньше, чем рейтинг поставщика S4.

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

Вариант 2.

1. Удалить все изделия, для которых нет поставок деталей.

2. Построить таблицу с номерами поставщиков и парами номеров деталей, таких, что некоторый поставщик поставляет обе указанные детали. При этом пары вида P1 и P2, а также P2 и P1 считать одинаковыми.

Вариант 3.

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

2. Построить таблицу с комбинациями "цвет детали-город, где хранится деталь", исключая дубликаты пар (цвет-город).

Вариант 4.

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

2. Вставить в таблицу S нового поставщика с номером S10 с фамилией Уайт из города Нью-Йорк с неизвестным рейтингом.

Вариант 5.

1. Удалить все изделия из Рима и все соответствующие поставки.

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

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

1. Что такое коррелированный запрос? Чем отличается коррелированный запрос от некоррелированного?

2. Какие существуют ограничения на формирование коррелированного запроса?

3. Каким образом сохранить результаты запроса в таблице?

4. Какими средствами SQL реализуются следующие операции реляционной алгебры: ограничение, декартово произведение, проекция, пересечение, объединение, разность, соединение?

5. Что такое внешнее соединение?

6. В каких случаях вместо фразы IN можно использовать операцию сравнения?

7. Какие существуют средства группирования в SQL? Как они используются?

Лабораторная работа № 5. Работа с внешними базами данных. Ограничение доступа.

Цель работы: Ознакомиться со средствами предоставления полномочий на использование баз данных и таблиц и основами работы с внешними базами данных.

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

СУБД MySQL использует специальную базу данных для предоставления прав доступа к своим базам данных. Эти права могут базироваться на именах серверов и/или пользователей и предоставляться для одной или нескольких баз данных

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

СУБД MySQL имеет три таблицы, а именно:

База данных: mysql Таблица: db

База данных: mysql Таблица: db

Поле

Тип

Null

Ключ

Умолчание

Extra

Хост

char(60)

PRI

Db

char(32)

PRI

Пользователь

char(16)

PRI

Select_priv

char(1)

N

Insert_priv

char(1)

N

Update_priv

char(1)

N

Delete_priv

char(1)

N

Create_priv

char(1)

N

Drop_priv

char(1)

N

База данных: mysql Таблица: host

Поле

Тип

Null

Ключ

Умолчание

Extra

Хост

char(60)

PRI

Db

char(32)

PRI

Select_priv

char(1)

N

Insert_priv

char(1)

N

Update_priv

char(1)

N

Delete_priv

char(1)

N

Create_priv

char(1)

N

Drop_priv

char(1)

N

База данных: mysql Таблица: user

База данных: mysql Таблица: user

Поле

Тип

Null

Key

Умолчание

Extra

Хост

char(60)

PRI

Пользователь

char(16)

PRI

Пароль

char(8)

Select_priv

char(1)

N

Insert_priv

char(1)

N

Update_priv

char(1)

N

Delete_priv

char(1)

N

Create_priv

char(1)

N

Drop_priv

char(1)

N

Reload_priv

char(1)

N

Shutdown_priv

char(1)

N

Process_priv

char(1)

N

File_priv

char(1)

N

Текущей базой данных называется база данных, открытая с помощью операторов use Database или с помощью утилиты mysqladmin. Любая другая база данных называется внешней. Для ссылки на таблицу во внешней базе данных необходимо указать имя этой базы данных как часть имени таблицы, например, salesdb:contracts, где salesdb - имя внешней базы данных, contracts - имя таблицы. К имени базы данных можно добавить имя сервера, т.е. сетевой машины, где запущен еще один сервер баз данных mysql, и таким образом в случае распределенной базы данных обращение к таблице contracts базы данных salesdb, размещенной на сервере central, будет выглядеть следующим образом: salesdb@central:contracts.

В программе MYSQL-FRONT также существует механизм, обеспечивающий наделение пользователей определенными правами (см. Рисунок 17).

Рисунок 17 - Редактирование прав пользователя

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

Задание (общее):

1. Убедиться, что в таблице поставщиков S имеются строки с Вашими фамилиями (задание выполнялось в третьей лабораторной работе).

2. Откорректировать экранную форму, созданную в третьей лабораторной работе для работы с таблицей поставок SPJ, обеспечив возможность ввода и модификации данных. Занести произвольным образом несколько строк (5-10 строк) о поставках, связанных с Вашими фамилиями.

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

4. Повторить задание п.3 с той разницей, что сведения о номенклатуре деталей и изделий (P и J) должна браться из собственной базы данных, а сведения о поставщиках и поставках (S и SPJ) должны браться из базы данных соседней бригады. Предварительно необходимо узнать имя этой базы данных. Убедитесь в невозможности выполнения задания.

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

6. Повторите задание п.4. Сравните результаты с результатами, полученными в п.3.

7. Сделайте попытку изменить информацию о поставщиках-владельцах базы данных (город, рейтинг и т.д.) в таблице S внешней базы данных. Убедитесь в невозможности выполнения задания.

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

9. Повторите задание п.7. Проверьте успешность выполнения действий.

10. Дождавшись, когда владелец внешней базы данных закончит выполнение п.9, сделайте попытку удалить из таблицы S используемой Вами внешней базы данных поставщиков с именами, принадлежащими владельцам базы данных, и связанные с ними поставки из таблицы SPJ. Убедитесь в невозможности выполнения задания.

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

12. Повторите задание п.10. Проверьте успешность выполнения действий.

13. Отнимите предоставленные Вами права на пользование Вашей базой данных.

Индивидуальные варианты заданий

Вариант 1.

1. Выдать список всех поставок, в которых количество деталей находится в диапазоне от 300 до 750 включительно.

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

Вариант 2.

1. Выдать цвета деталей, поставляемых поставщиком S6.

2. Выдать номера и фамилии поставщиков, поставляющих деталь Р1 для какого-либо изделия в количестве, большем среднего объема поставок детали Р1 для этого изделия.

Вариант 3.

1. Выдать названия изделий, для которых поставляются детали поставщиком S6.

2. Выдать номера и названия изделий, для которых поставщик S6 поставляет несколько деталей каждого из поставляемых им типов.

Вариант 4.

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

2. Выдать номера изделий, для которых детали полностью поставляет поставщик S6.

Вариант 5.

1. Выдать номера и фамилии поставщиков, поставляющих детали для какого-либо изделия с деталью P1 в количестве, большем, чем средний объем поставок детали P1 для этого изделия.

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

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

1. Кто является владельцем базы данных?

2. Какими правами обладают другие пользователи по отношению к Вашей базе данных?

3. Какими правами обладает администратор базы данных по отношению к Вашей базе данных?

4. Каким образом предоставляются права на пользование базой данных и отдельными ее таблицами?

5. Каким образом изымаются права на пользование базой данных и отдельными ее таблицами?

6. Что такое внешняя база данных?

7. Как идентифицируется таблица внешней базы данных?

8. Как идентифицируется таблица внешней распределенной базы данных?


Лабораторная работа №6. Представления, хранимые процедуры, функции, триггеры

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

Представления

Представления (views) можно сравнить с временными таблицами, наполненными динамически формируемым содержимым.. В настоящей реализации есть две возможности создания представлений: с использованием алгоритма временных таблиц MySQL и с созданием самостоятельной таблицы. Нас интересует именно второй способ (первый был реализован, скорее всего, исходя из соображений совместимости и унификации). Такие представления позволяют значительно снизить объём кода, в котором часто повторялись простые объединения таблиц. К ним (после создания) применимы любые запросы, возвращающие результат в виде набора строк. То есть команды SELECT, UPDATE, DELETE, можно применять так же, как и к реальным таблицам. Важно и то, что посредством представлений можно более гибко распоряжаться правами пользователей базы данных, так как в этом случае есть возможность предоставлять доступ на уровне отдельных записей различных таблиц.

Создание представлений

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

Синтаксис команды CREATE VIEW

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

Пример создания и работы простейшего представления:

Create View v as Select column 1 from T

Insert into v Values (1)

Select * from v

Результат

+---------+

| column1 |

+---------+

| 1 |

+---------+

1 row in set (0.00 sec)

Представление может быть создано на основе различных параметров предложения SELECT, при этом можно ссылаться на другие таблицы и представления. Конструкция может использовать оператор UNION и другие подзапросы.

Синтаксис команды ALTER VIEW

Для внесения изменений в представление используется команда ALTER VIEW

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list )] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

Синтаксис команды DROP VIEW

Для удаления представления используется команда DROP VIEW

VIEW [IF EXISTS] view_name [, view_name ] ... [RESTRICT | CASCADE]

ПРИМЕР

mysql> CREATE TABLE t (qty INT, price INT); mysql> INSERT INTO t VALUES(3, 50); mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t; mysql> SELECT * FROM v; +------+-------+-------+| qty | price | value |+------+-------+-------+| 3 | 50 | 150 |

Хранимые процедуры и функци и

В СУБД MySQL появилась возможность создания и хранения функций и процедур. Объявление и работа с процедурами и функциями отличаются в следующем:

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

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

· вызов процедуры выполняется отдельным оператором;

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

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

Как процедуры, так и функции могут возвращать значения (в виде набора записей). Различие состоит в том, что функция вызывается из запроса, а процедура из отдельной команды.

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

Пример создания хранимой процедуры в MySQL 5:

CREATE PROCEDURE p ()

LANGUAGE SQL

NOT DETERMINISTIC

SQL SECURITY DEFINER

COMMENT 'A Procedure' <--

SELECT CURRENT_DATE, RAND() FROM t

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

mysql> call p2()

+--------------+-----------------+

| CURRENT_DATE | RAND() |

+--------------+-----------------+

| 2005-06-27 | 0.7822275075896 |

+--------------+-----------------+

1 row in set (0.26 sec)

Query OK, 0 rows affected (0.26 sec)

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

CREATE FUNCTION factorial (n DECIMAL(3,0))

RETURNS DECIMAL(20,0)

DETERMINISTIC

BEGIN

DECLARE factorial DECIMAL(20,0) DEFAULT 1;

DECLARE counter DECIMAL(3,0);

SET counter = n;

factorial_loop: REPEAT

SET factorial = factorial * counter;

SET counter = counter - 1;

UNTIL counter = 1

END REPEAT;

RETURN factorial;

END

В приложении:

INSERT INTO t VALUES (factorial(pi))

SELECT s1, factorial (s1) FROM t

UPDATE t SET s1 = factorial(s1)

WHERE factorial(s1) < 5

Разумеется эффективность применения хранимых процедур существенно возрастает при вызове их с параметрами (аргументами). Ниже дан пример процедуры с обработкой переданных ей параметров:

CREATE PROCEDURE p1 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1;

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 + 1; <--

ELSE

UPDATE t SET s1 = s1 + 2;

END IF;

END;

Вызов процедуры теперь будет таким:

mysql> CALL p2(0) // Query OK, 2 rows affected (0.28 sec)

и в результате запроса мы получим:

mysql> SELECT * FROM t

+----+

| s1 |

+----+

| 6 |

| 6 |

+------+

2 rows in set (0.01 sec)

Кроме условных, возможны и любые циклические конструкции:

CREATE PROCEDURE p3 ()

BEGIN

DECLARE v INT;

SET v = 0;

WHILE v < 5 DO

INSERT INTO t VALUES (v);

SET v = v + 1;

END WHILE;

END;

Вызов процедуры:

mysql> CALL p3()

+------+

| s1 |

+------+

…………

| 0 |

| 1 |

| 2 |

| 3 |

| 4 |

+------+

Query OK, 1 row affected (0.00 sec)

Также применимы итерации, переходы, словом, всё, что предполагает стандарт.

Внутри функций и хранимых процедур осуществлена реализация курсоров, но, к сожалению, она пока ограничена (ASESITIVE, READ ONLY и NONSCROLL):

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT;

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

UNTIL b = 1

END REPEAT;

CLOSE cur_1;

SET return_val = a;

END;

Создание процедур и функций

CREATE

[DEFINER = { user | CURRENT_USER }]

PROCEDURE sp_name ([proc_parameter [,...]])

[characteristic ...] routine_body

CREATE

[DEFINER = { user | CURRENT_USER }]

FUNCTION sp_name ([func_parameter [,...]])

RETURNS type

[characteristic ...] routine_body

proc_parameter :

[ IN | OUT | INOUT ] param_name type

func_parameter :

param_name type

type :

Any valid MySQL data type