Урок 50: Итоговый проект: Разработка полноценного проекта с использованием всех изученных тем
Добро пожаловать в финальный урок нашего курса по SQL. В этом уроке мы разработаем полноценный проект, который объединит все изученные темы. Проект будет включать создание базы данных, таблиц, вставку данных, выполнение сложных запросов, настройку безопасности, репликацию и резервное копирование данных.
Описание проекта
Мы будем разрабатывать систему управления библиотекой. В системе будут храниться данные о книгах, авторах, читателях и операциях по выдаче книг. Проект включает следующие шаги:
- Создание базы данных и таблиц
- Вставка начальных данных
- Выполнение сложных запросов
- Настройка безопасности и прав доступа
- Настройка репликации и резервного копирования
Шаг 1: Создание базы данных и таблиц
Создадим базу данных library и необходимые таблицы для хранения данных о книгах, авторах, читателях и операциях по выдаче книг.
-- Создание базы данных
CREATE DATABASE library;
-- Переход к базе данных
USE library;
-- Создание таблицы authors (авторы)
CREATE TABLE authors (
author_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
birthdate DATE
);
-- Создание таблицы books (книги)
CREATE TABLE books (
book_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author_id INT,
published_year INT,
genre VARCHAR(50),
FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
-- Создание таблицы readers (читатели)
CREATE TABLE readers (
reader_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(15)
);
-- Создание таблицы borrowings (выдачи книг)
CREATE TABLE borrowings (
borrowing_id INT AUTO_INCREMENT PRIMARY KEY,
book_id INT,
reader_id INT,
borrowing_date DATE,
return_date DATE,
FOREIGN KEY (book_id) REFERENCES books(book_id),
FOREIGN KEY (reader_id) REFERENCES readers(reader_id)
);
Результат выполнения
База данных и таблицы созданы успешно.
Шаг 2: Вставка начальных данных
Вставим начальные данные в таблицы authors, books и readers.
-- Вставка данных в таблицу authors
INSERT INTO authors (name, birthdate) VALUES
('George Orwell', '1903-06-25'),
('J.K. Rowling', '1965-07-31'),
('Mark Twain', '1835-11-30');
-- Вставка данных в таблицу books
INSERT INTO books (title, author_id, published_year, genre) VALUES
('1984', 1, 1949, 'Dystopian'),
('Harry Potter and the Philosopher\'s Stone', 2, 1997, 'Fantasy'),
('Adventures of Huckleberry Finn', 3, 1884, 'Adventure');
-- Вставка данных в таблицу readers
INSERT INTO readers (name, email, phone) VALUES
('John Doe', 'john.doe@example.com', '123-456-7890'),
('Jane Smith', 'jane.smith@example.com', '098-765-4321');
Результат выполнения
Данные вставлены успешно.
Шаг 3: Выполнение сложных запросов
Выполним несколько сложных запросов для извлечения информации из базы данных.
Запрос 1: Найти книги, которые были выданы, но еще не возвращены
SELECT b.title, r.name, br.borrowing_date
FROM borrowings br
JOIN books b ON br.book_id = b.book_id
JOIN readers r ON br.reader_id = r.reader_id
WHERE br.return_date IS NULL;
Результат выполнения
title |
name |
borrowing_date |
1984 |
John Doe |
2024-05-01 |
Harry Potter and the Philosopher's Stone |
Jane Smith |
2024-05-02 |
Запрос 2: Найти самых активных читателей
SELECT r.name, COUNT(br.borrowing_id) AS borrow_count
FROM readers r
JOIN borrowings br ON r.reader_id = br.reader_id
GROUP BY r.name
ORDER BY borrow_count DESC;
Результат выполнения
name |
borrow_count |
John Doe |
3 |
Jane Smith |
2 |
Шаг 4: Настройка безопасности и прав доступа
Настроим пользователей и назначим им права доступа к базе данных.
-- Создание пользователя librarian
CREATE USER 'librarian'@'localhost' IDENTIFIED BY 'securepassword';
-- Назначение привилегий пользователю librarian
GRANT SELECT, INSERT, UPDATE, DELETE ON library.* TO 'librarian'@'localhost';
-- Создание пользователя reader
CREATE USER 'reader'@'localhost' IDENTIFIED BY 'readerpassword';
-- Назначение привилегий пользователю reader
GRANT SELECT ON library.* TO 'reader'@'localhost';
Результат выполнения
Пользователи созданы и привилегии назначены успешно.
Шаг 5: Настройка репликации и резервного копирования
Настроим репликацию и выполним резервное копирование базы данных.
Настройка репликации
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=library
-- Настройка основного сервера
CREATE USER 'replicator'@'%' IDENTIFIED BY 'replicapassword';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
-- Настройка реплицированного сервера
[mysqld]
server-id=2
CHANGE MASTER TO
MASTER_HOST='master_host',
MASTER_USER='replicator',
MASTER_PASSWORD='replicapassword',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS= 107;
START SLAVE;
Результат выполнения
Репликация настроена успешно.
Резервное копирование
-- Выполнение полного резервного копирования
mysqldump -u root -p library > library_backup.sql
-- Восстановление базы данных из резервной копии
mysql -u root -p library < library_backup.sql
Результат выполнения
Резервное копирование и восстановление базы данных выполнены успешно.
Упражнения
Упражнение 1: Создание и заполнение таблицы
Создайте таблицу publishers для хранения данных о издателях книг и вставьте несколько записей.
Решение:
-- Создание таблицы publishers
CREATE TABLE publishers (
publisher_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
address VARCHAR(200)
);
-- Вставка данных в таблицу publishers
INSERT INTO publishers (name, address) VALUES
('Penguin Books', '123 Penguin St, New York, NY'),
('HarperCollins', '195 Broadway, New York, NY');
Объяснение: Мы создали таблицу publishers и вставили данные о издателях книг.
Упражнение 2: Выполнение сложного запроса
Напишите запрос для нахождения всех книг, опубликованных после 2000 года, и их авторов.
Решение:
SELECT b.title, a.name
FROM books b
JOIN authors a ON b.author_id = a.author_id
WHERE b.published_year > 2000;
Объяснение: Мы объединили таблицы books и authors и выбрали книги, опубликованные после 2000 года, вместе с именами их авторов.
Упражнение 3: Настройка прав доступа
Создайте пользователя assistant и назначьте ему привилегии на чтение данных из таблицы books.
Решение:
-- Создание пользователя assistant
CREATE USER 'assistant'@'localhost' IDENTIFIED BY 'assistantpassword';
-- Назначение привилегий пользователю assistant
GRANT SELECT ON library.books TO 'assistant'@'localhost';
Объяснение: Мы создали пользователя assistant и назначили ему привилегии на чтение данных из таблицы books.
Упражнение 4: Выполнение резервного копирования
Выполните резервное копирование базы данных library и восстановите ее из резервной копии.
Решение:
-- Выполнение полного резервного копирования
mysqldump -u root -p library > library_backup.sql
-- Восстановление базы данных из резервной копии
mysql -u root -p library < library_backup.sql
Объяснение: Мы выполнили резервное копирование базы данных library и восстановили ее из резервной копии.