Нижегородский Государственный Технический Университет
Лабораторная работа №1
по дисциплине: «Базы данных»
Тема: «MySQL»
Выполнил студент группы 10-В-1
Проверил:
г. Нижний Новгород
2014г.
1. Создайте новую базу данных MySQL, выполнив запрос
CREATE DATABASE IF NOT EXISTS library;
2. Выберите созданную базу и при помощи оператора CREATE TABLE создайте таблицы. Используйте архитектуру MyISAM.
CREATE TABLE authors
(
id SMALLINT PRIMARY KEY, lastName VARCHAR(20), firstName VARCHAR(20), midName VARCHAR(20), birthDate DATE
) ENGINE = MyISAM DEFAULT CHARSET = utf8;
CREATE TABLE books
(
id INT PRIMARY KEY, authorId SMALLINT, title VARCHAR(255), ISBN VARCHAR(15), cnt TINYINT
) ENGINE = MyISAM DEFAULT CHARSET = utf8;
CREATE TABLE readers
(
id MEDIUMINT PRIMARY KEY, name VARCHAR(50), address VARCHAR(255), phone VARCHAR(11)
)ENGINE = MyISAM DEFAULT CHARSET = utf8;
CREATE TABLE orders
(
id MEDIUMINT PRIMARY KEY, bookId INT, readerId MEDIUMINT, orderDate DATETIME, duration TINYINT
)ENGINE = MyISAM DEFAULT CHARSET = utf8;
3. Заполните таблицы данными.
INSERT authors (id, lastName, firstName, midName, birthDate) VALUES (1,'Рагозин','Степан','Максимович','1892-10-07') ,(2,'Алашев','Ростислав','Антонович','1908-01-23') ,(3,'Хилков','Артур','Эдгарович','1934-07-12') ,(4,'Шлоссман','Элеонора','Вильевна','1925-09-15');
|
INSERT books (id, authorId, title, ISBN, cnt) VALUES (1,1,'Сознание Мира','978-3-16-1484-0',15) ,(2,1,'Старый Моряк','978-5-69-1204-7',12) ,(3,2,'Ручная Антилопа','978-5-28-0044-2',11) ,(4,2,'Римский Апрель','978-5-93-3470-7',31) ,(5,3,'Американец Эдди','978-3-54-0152-3',29) ,(6,3,'Актерская Эмоция','978-5-28-0267-2',27) ,(7,4,'Эксперимент Волна','978-5-89-9822-6',25) ,(8,4,'Эра Вещества','978-5-93-4212-4',8);
|
INSERT orders (id, bookId, readerId, orderDate, duration) VALUES (1,5,2,'2014-01-12 10:11',15) ,(2,4,1,'2014-01-14 11:47',22) ,(3,3,2,'2014-01-15 17:06',13) ,(4,8,2,'2014-01-18 15:28',17) ,(5,5,3,'2014-01-29 13:29',25) ,(6,4,1,'2014-02-04 16:36',20) ,(7,5,4,'2014-02-07 16:27',20) ,(8,6,3,'2014-02-21 15:08',7);
|
INSERT readers (id, name, address, phone) VALUES (1,'','NN, Акимова, 7','88996217520') ,(2,'','SRV, Ленина, 25','88531056620') ,(3,'','DZR, Советов, 11','88224777323') ,(4,'','NN, Прямая, 6','88992704985') ,(5,'','NN, Бекетова, 23','88991736948');
|
4. Обеспечьте ссылочную целостность данных при помощи триггеров. Проверьте вставкой, изменением и удалением связанных данных.
CREATE TRIGGER authors_delete BEFORE DELETE
ON authors FOR EACH ROW
DELETE FROM books WHERE authorId=OLD. id;
CREATE TRIGGER authors_update BEFORE UPDATE
ON authors FOR EACH ROW
UPDATE books SET authorId=NEW. id
WHERE authorId=OLD. id;
CREATE TRIGGER books_delete BEFORE DELETE
ON books FOR EACH ROW
DELETE FROM orders WHERE bookId=OLD. id;
CREATE TRIGGER books_update BEFORE UPDATE
ON books FOR EACH ROW
UPDATE orders SET bookId=NEW. id
WHERE bookId=OLD. Id;
CREATE TRIGGER readers_delete BEFORE DELETE
ON readers FOR EACH ROW
DELETE FROM orders WHERE readerId=OLD. Id;
CREATE TRIGGER readers_update BEFORE UPDATE
ON readers FOR EACH ROW
UPDATE orders SET readerId=NEW. Id
WHERE readerId=OLD. Id;
Пример:
Изменим значение id в таблице readers и посмотрим, изменилось ли это значение в таблице orders.
UPDATE readers
SET id=10
WHERE id=4;
Следовательно, триггер работает
5. При помощи триггера ограничьте максимальный срок выдачи книги 14 днями. Проверьте ограничение вводом некорректных данных.
delimiter $$
CREATE TRIGGER orders_insertn BEFORE INSERT
ON orders FOR EACH ROW
IF NEW. duration > 14
THEN SET NEW. duration = 14;
END IF$$
delimiter ;
delimiter $$
CREATE TRIGGER orders_insertn BEFORE UPDATE
ON orders FOR EACH ROW
IF NEW. duration > 14
THEN SET NEW. duration = 14;
END IF$$
delimiter ;
Пример
INSERT orders
(id, bookId, readerId, orderDate, duration)
VALUES
(9,5,4,'2014-02-07 16:25',20);
6. Выполните следующие запросы выборки:
6.1 Вывести список читателей, живущих в Нижнем Новгороде.
Предполагаемые id: 1,4,5
SELECT * FROM readers
WHERE locate('NN',address)!=0
6.2 Вывести список всех книг в библиотеке с указанием автора, названия, ISBN и количества экземпляров, отсортированный по фамилии автора в алфавитном порядке.
Предполагаемые books. authorId: 2,2,1,1,3,3,4,4
SELECT authorId, lastName, firstName, midName, title, ISBN, cnt
FROM authors, books
WHERE authors. id=books. authorId
ORDER BY lastName
6.3 Вывести список имён читателей, имеющих на руках книги, с указанием количества взятых ими книг. Результат отсортировать по количеству взятых книг по убыванию.
Предполагаемые readerId: 2,1,3,4
SELECT readerId, name, count(readers. id)
FROM readers, orders
WHERE readers. id=orders. readerId
GROUP BY readerId
ORDER BY count(readers. id) DESC
6.4 Вывести список имён читателей, взявших книги на срок, больший, чем средний срок выдачи книг (средний срок вычисляется в запросе на основе данных обо всех выдачах). Результат отсортировать по имени читателя в алфавитном порядке.
Предполагаемые readerId:1,4,3
SELECT readerId, name, duration
FROM readers, orders
WHERE (orders. duration > (SELECT AVG(orders. duration) FROM orders)) AND (readers. id=orders. readerId)
GROUP BY readers. id
ORDER BY readers. name
6.5 Вывести список фамилий авторов и их инициалов (в одном поле), родившихся до 1917 года, чьи книги брали читать за последнюю неделю.
Для опыта изменим orderDate в таблице orders с id 3 и 6 так, чтобы получились следующие данные:
Итак, предполагаемые authorId:2
SELECT authors. id, concat_ws(' ',lastName, firstName, midName), birthDate, orders. orderDate
FROM authors, orders, books
WHERE (date_add(orderDate, INTERVAL 8 DAY)>DATE(NOW())) AND birthDate < '1917-01-01' AND books. id=orders. bookId AND authors. id=books. authorId
GROUP BY authors. id
6.6 Вывести список имён авторов (фамилия, имя, отчество, разделённые пробелом, в одном поле), чьи книги находятся на руках у читателей, с указанием количества выданных книг для каждого автора. Результат отсортировать по имени автора в обратном алфавитном порядке.
Предполагаемые id: 4,3,2
SELECT authors. id, concat_ws(' ',lastName, firstName, midName), count(orders. bookId)
FROM authors, orders, books
WHERE books. id=orders. bookId AND authors. id=books. authorId
GROUP BY authors. id
ORDER BY firstName DESC
6.7 Вывести список имён читателей, которые превысили срок выдачи книги (на сегодняшний день), а также дату предполагаемого возврата книги. Результат отсортировать по имени читателя в алфавитном порядке.
Предполагаемые orders. id:2,1,4,3,5. Однако при группировке по имени читателя, некоторые id будут отсутствовать
SELECT name, date_add(orderDate, INTERVAL duration DAY), orders. id
FROM orders, readers
WHERE date_add(orderDate, INTERVAL duration DAY)< DATE(NOW())) AND readerId=readers. id
GROUP BY name
ORDER BY readers. name
7. Используя оператор GRANT создайте пользователя с привилегией SELECT на таблицу authors.
GRANT select ON authors
TO new_user@localhost
IDENTIFIED BY 'pass'
8. От имени созданного пользователя выполните запрос выборки к таблице authors. Убедитесь в успешном выполнении запроса. Попытайтесь выполнить вставку или изменение данных в таблице authors и выборку из других таблиц базы.
SELECT * FROM authors
INSERT authors
(id, lastName)
VALUES
(5,'Иванов')
UPDATE authors
SET lastName='Иванов'
WHERE id=1
SELECT * FROM books
9. Задайте созданному пользователю привилегии INSERT и DELETE. Проверьте результаты.
GRANT delete, insert ON authors
TO new_user@localhost
IDENTIFIED BY 'pass'
INSERT authors
(id, lastName)
VALUES
(5,'Иванов');
DELETE FROM authors
WHERE id=5;
