Основное / БД. Лабораторные работы

УДК 004.65(075.8)

ББК 32.973.26–018.2 я73 З58

Рецензент кандидат технических наук, доцент СПбГЭТУ

А.Ю. Волков

Рекомендовано к печати редакционно-издательским советом СПбГУТ

Зернов, П.С.

З58 Базы данных: методические указания к лабораторным работам / П.С. Зернов. – СПб. : Изд-во СПбГУТ, 2012. – 36 с.

Методические указания предназначены для студентов 3-го курса по направлению «Программная инженерия», профилю «Разработка программ- ного обеспечения инфокоммуникационных сетей и систем», дисциплине «Базы данных». Содержат девять работ, составленных в порядке усвоения основных понятий языка SQL, понимания устройства СУБД «MySQL». Каждая работа содержит краткую постановку задачи, рекомендации по ее выполнению и контрольные вопросы.

УДК 004.65(075.8)

ББК 32.973.26–018.2 я73

©Зернов П.С., 2012

©Федеральное государственное образовательное бюджетное учреждение высшего профессионального образования «Санкт-Петербургский государственный университет телекоммуникаций им. проф. М.А. Бонч-Бруевича», 2012

2

3

ВВЕДЕНИЕ

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

Методические указания призваны помочь студентам, изучающим дисциплину «Базы данных», освоить его с практической стороны в ходе выполнения лабораторных работ. В первой и второй работах рассматриваются возможности языка SQL по конструированию запросов на выборку, обновление, удаление и добавление данных. Изучаются вопросы внутреннего и внешнего объединения таблиц. В работах с третьей по пятую исследуются возможности создания хранимых процедур, встроенных функции и транзакций. В работе номер шесть рассматриваются курсоры MySQL. Седьмая работа посвящена обеспечению целостности данных и триггерам. В работах номер восемь и девять студентам предстоит создать не только базу данных MySQL, но и разработать на языке PHP скрипт, позволяющий просматривать и редактировать данные через веб- браузер. Таким образом, в заключительных работах студентам предстоит применить весь спектр знаний, полученных в ходе изучения дисциплины, для создания веб-интерфейса к собственной базе данных MySQL.

4

ПОРЯДОК ВЫПОЛНЕНИЯ ЛАБОРАТОРНЫХ РАБОТ

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

2.Выполнение очередной лабораторной работы на персональном компьютере (ПК) в классе согласно последовательности пунктов задания и оформление черновика.

3.Оформление отчета по результатам выполненной лабораторной работы согласно требованиям;

4.Защита лабораторной работы.

Отчет должен содержать следующие материалы:

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

цель работы;

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

выводы.

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

5

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

SQL ЗАПРОСЫ В MYSQL

Цель работы – получение практических навыков создания запросов на выборку, обновление и удаление строк в таблице базы данных на языке

SQL.

Задание

1. Создать базу данных «university» в программе-дизайнере MySQL Workbench.

2.В базе данных «university» создать таблицу «students» с полями:

id тип int – ключ (PK), счетчик (AI);name тип varchar, ненулевое (NN);d_id тип int.

3.Заполнить таблицу «students» произвольными записями (вкладка Inserts) - 5 строк (поле id следует заполнять нулями).

4.Сохранить созданную в программе-дизайнере схему базы данных на локальный компьютер.

5.Запустить генерацию базы данных на сервере MySQL (Пункт меню: Database->Forward Engineer. В опциях необходимо поставить галки против пунктов: DROP Objects Before Each CREATE Object и Generate INSERT

Statements for Tables).

6.Подключиться к базе данных MySQL (команда mysql –u root –p).

7.Активизировать базу данных «university» (команда use).

8.Выполнить SQL команду: SELECT * FROM students; результаты записать в отчет.

9.Выполнить SQL команды:

UPDATE students SET name = ‘Ivan’ WHERE id = 2;

SELECT * FROM students WHERE id = 2; результаты записать в отчет.

10. Выполнить SQL команды:

DELETE FROM students WHERE id = 2;

SELECT * FROM students; результаты записать в отчет. 11. Проанализировать полученные результаты.

Справочные материалы

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

SELECT * FROM <имя таблицы>;

Для обозначения конца SQL запроса, по умолчанию в MySQL используется символ ‘;’. Не забывайте ставить его в конце каждого запроса. Если запрос состоит из нескольких строк, то переход на новую строку в

6

программе-клиенте командной строки MySQL осуществляется по нажатию клавиши Enter, а в конце последней строки также добавляется символ ‘;’.

Если необходимо выбрать из таблицы строки, которые удовлетворяют какому-либо критерию, то к SQL запросу добавляют команду WHERE

<условие отбора>. Например, запрос:

SELECT * FROM students WHERE name = ‘Ivan’;

отобразит только те записи (строки) из таблицы students, в которых значение в столбце name соответствует имени 'Ivan'.Обратите внимание на то, что если в качестве условия отбора используется строковая константа, ее значение необходимо заключать в одинарные кавычки. Если в качестве условия задается число, то значение в кавычки не ставится.

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

UPDATE <имя таблицы>

SET <имя столбца1>=<значение 1>

…

SET <имя столбцаN>=<значение N> [WHERE <условие отбора>];

Обратите внимание, условие отбора помещено в квадратные скобки. Это означает, что команда WHERE не обязательно должна присутствовать в тексте SQL запроса. При наличии условия в запросе квадратные скобки не пишутся. Пример запроса на обновление строк:

UPDATE students SET name = ‘Petr’ WHERE name = ‘Ivan’;

После выполнения запроса во всех строках таблицы students, содержащих в столбце name значение ‘Ivan’, имя студента будет изменено

на ‘Petr’.

Команда удаления строк в упрощенном общем виде выглядит следующим образом:

DELETE FROM <имя таблицы> [WHERE <условие отбора>];

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

7

Пример оформления отчета

Лабораторная работа 1 SQL запросы в MySQL

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

Результат выполнение команды: SELECT * FROM students;

Результат выполнение команд:

UPDATE students SET name = ‘Ivan’ WHERE id = 2; SELECT * FROM students WHERE id = 2;

Результат выполнение команд:

DELETE FROM students WHERE id = 2; SELECT * FROM students;

Выводы

Для извлечения информации из таблиц базы данных используются запросы, написанные на языке SQL. Команда SELECT создает запрос на выборку строк, при этом существует возможность указания условия их

8

отбора посредством инструкцией WHERE. Команда UPDATE осуществляет изменение/обновление данных, при этом если условие не указано, то изменения происходят во всех строках таблицы. Команда DELETE удаляет строки таблицы согласно условию. Если условие не указано, то таблица очищается, т.е. происходит удаление всех ее строк. Сама таблица не удаляется.

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

1. Какую команду нужно ввести в командной строке, чтобы получить доступ к MySQL? (Путь к каталогу bin, в который установлены исполняемые файлы MySQL, записан в системную переменную окружения

PATH).

2.Напишите запрос, выводящий информацию обо всех студентах из таблицы students.

3.Напишите запрос, выводящий информацию только о студентах с именем ‘Petr’ из таблицы students.

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

students.

5.Напишите запрос, изменяющий имя студента ‘Ivan’ на ‘Petr’ в

таблице students.

6.Напишите запрос, удаляющий всех студентов с именем ‘Egor’ в

таблице students.

7.Напишите запрос, очищающий таблицу students.

9

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

ОБЪЕДИНЕНИЕ ТАБЛИЦ

С ПОМОЩЬЮ ОПЕРАТОРА JOIN В MYSQL

Цель работы – получение практических навыков создания запросов на внутренние и внешние объединения таблиц на языке SQL.

Задание

1. Создать базу данных «university» в программе-дизайнере MySQL Workbench.

2.В базе данных «university» создать таблицу «departments» с полями:

id тип int – ключ (PK);

name тип varchar, ненулевое (NN).

3.Заполнить таблицу «departments» пятью произвольными записями (поле id следует заполнять уникальными целыми числами, например порядковыми номерами).

4.В базе данных «university» создать таблицу «users» с полями:

id тип int – ключ (PK), счетчик (AI);

name тип varchar, ненулевое (NN);

d_id тип int.

5.Заполнить таблицу «users» произвольными семью записями (поле id следует оставить незаполненным), при этом:

пять строк таблицы «users» должны содержать в поле d_id какое- либо уникальное число из поля id таблицы «departments»;

две строки из таблицы «users» должны содержать в поле d_id числа, не используемые в строках таблицы «departments» в поле id.

6.Сохранить созданную в программе-дизайнере схему базы данных на локальный компьютер.

7.Запустить генерацию базы данных на сервере MySQL (Пункт меню: Database->Forward Engineer. В опциях необходимо поставить галки против пунктов DROP Objects Before Each CREATE Object и Generate INSERT

Statements for Tables).

8.Подключиться к базе данных MySQL (команда mysql –u root –p).

9.Активизировать базу данных «university» (команда use university).

10.Последовательно выполнить следующие команды и записать в

отчет команду и полученную на экране таблицу:

SELECT u.id, u.name, d.name FROM users u INNER JOIN departments d ON u.d_id = d.id;

SELECT u.id, u.name, d.name FROM users u LEFT JOIN departments d ON u.d_id = d.id;

SELECT u.id, u.name, d.name FROM users u RIGHT JOIN departments d ON u.d_id = d.id;

SELECT u.id, u.name, d.name FROM users u CROSS JOIN departments d;

10