17:22
Итоговый проект: Разработка полноценного проекта с использованием всех изученных тем.

Курс по SQL

Урок 50: Итоговый проект: Разработка полноценного проекта с использованием всех изученных тем

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

Описание проекта

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

  1. Создание базы данных и таблиц
  2. Вставка начальных данных
  3. Выполнение сложных запросов
  4. Настройка безопасности и прав доступа
  5. Настройка репликации и резервного копирования

Шаг 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 и восстановили ее из резервной копии.

Категория: SQL | Просмотров: 23 | Добавил: Admin | Рейтинг: 0.0/0
Всего комментариев: 0
Имя *:
Email *:
Код *: