Rakendusserverid — Work 3

Ссылка на задание: MySQL part 1

Вступление.

Для начала установим вируальную машину, настроим её и зайдем под root пользователем (sudo su).

Далее нам нужно установить mariadb-server (мы будем работать с ним).

apt install mariadb-server

Далее проходим безопасную установку.

mysql_secure_installation

Enter current password for root (enter for none):
Switch to unix_socket authentication [Y/n] n
Change the root password? [Y/n] n
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y

Далее заходим в mysql, всё под тем же root пользователем, и начинаем творить чудеса.

Создаем пользователя для дальнейшей работы, желательно с тем же именем что и пользователь linux сервера.

create user 'nurk'@'localhost' identified by '123';

Далее нам нужно создать базу данных, создаем её.

create database aleksandrn;

И сразу же даем права на эту базу нашему пользователю.

grant all privileges on aleksandrn.* to 'nurk'@'localhost';

Выходим из mysql написав exit, и так же выходим из под root пользователя, и начинаем выполнять задания.

Для начала зайдем под нашим пользователем в mysql.

mysql -u nurk -p

И переключимся на нашу созданную базу данных.

use aleksandrn;

Пример:

Задание 1.

Сформулируйте SQL запрос для создания таблицы book, и создайте таблицу book.
Структура таблицы book:

Код:

create table book(
	book_id int primary key auto_increment,
	title varchar(50),
	author varchar(30),
	price decimal(8, 2),
	amount int
);

Пример:

Задание 2.

Занесите новую строку в таблицу book (текстовые значения (тип VARCHAR) заключать либо в двойные, либо в одинарные кавычки)

Код:

insert into book (title, author, price, amount) values ('Мастер и Маргарита', 'Булгаков М.А.', 670.99, 3);

Пример:

Задание 3 и 4.

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

Код:

insert into book (title, author, price, amount) values ('Белая гвардия', 'Булгаков М.А.', 540.50, 5);
insert into book (title, author, price, amount) values ('Идиот', 'Достоевский Ф.М.', 460.00, 10);
insert into book (title, author, price, amount) values ('Братья Карамазовы', 'Достоевский Ф.М.', 799.01, 2);

select * from book;

Пример:

Задание 5.

Выбрать авторов, название книг и их цену из таблицы book.

Код:

select author, title, price from book;

Пример:

Задание 6.

Выбрать названия книг и авторов из таблицы book, для поля title задать имя (псевдоним) Название, для поля author – Автор.

Код:

select title as Название, author as Автор from book;

Пример:

Задание 7.

Для упаковки каждой книги требуется один лист бумаги, цена которого 1 рубль 65 копеек. Посчитать стоимость упаковки для каждой книги (сколько денег потребуется, чтобы упаковать все экземпляры книги). В запросе вывести название книги, ее количество и стоимость упаковки, последний столбец назвать pack.

Код:

select title, amount, amount * 1.65 as pack from book;

Пример:

Задание 8.

При анализе продаж книг выяснилось, что наибольшей популярностью пользуются книги Михаила Булгакова, на втором месте книги Сергея Есенина. Исходя из этого решили поднять цену книг Булгакова на 10%, а цену книг Есенина — на 5%. Написать запрос, куда включить автора, название книги и новую цену, последний столбец назвать new_price. Значение округлить до двух знаков после запятой.

Код:

select author, title, round(if(author = 'Булгаков М.А.', price * 1.1, if(author = 'Есенин С.А.', price * 1.05, price)), 2) as new_price from book;

Пример:

Задание 9.

Вывести автора, название и цены тех книг, количество которых меньше 10.

Код:

select author, title, price from book where amount < 10;

Пример:

Задание 10.

Вывести название, автора, цену и количество всех книг, цена которых меньше 500 или больше 600, а стоимость всех экземпляров этих книг больше или равна 5000.

Тут нам нужно будет добавить в таблицу книгу “Стихотворения и поэмы” Есенина, хотя по заданию этой книги нету, но без неё будет пустой запрос (книга всплывает в примерах у Михаила)

insert into book (title, author, price, amount) values ('Стихотворения и поэмы', 'Есенин С.А.', 650.00, 15);

Код:

select title, author, price, amount from book where (price < 500 or price > 600) and price * amount >= 5000;

Пример (без книги “Стихотворения и поэмы”):

Пример (с книгой “Стихотворения и поэмы”):

Задание 11.

Вывести название и авторов тех книг, цены которых принадлежат интервалу от 540.50 до 800 (включая границы), а количество или 2, или 3, или 5, или 7.

Код:

select title, author from book where price between 540.50 and 800 and amount in (2, 3, 5, 7);

Пример:

Задание 12.

Вывести автора и название книг, количество которых принадлежит интервалу от 2 до 14 (включая границы). Информацию отсортировать сначала по авторам (в обратном алфавитном порядке), а затем по названиям книг (по алфавиту).

Код:

select author, title from book where amount between 2 and 14 order by author DESC, title ASC;

Пример:

Задание 13.

Вывести название и автора тех книг, название которых состоит из двух и более слов, а инициалы автора содержат букву «С». Считать, что в названии слова отделяются друг от друга пробелами и не содержат знаков препинания, между фамилией автора и инициалами обязателен пробел, инициалы записываются без пробела в формате: буква, точка, буква, точка. Информацию отсортировать по названию книги в алфавитном порядке.

Код:

select title, author from book where title like '% %' and author like '%С.%';

Пример: