obsidian/hardcore web development/11. SQL/room_1.sql

-- -----------------------------------------------------------------
-- Создание таблиц

-- Таблица авторов
create table author (
	author_id bigint generated always as identity primary key,
	name varchar(150) no null check (length(name) >= 3),
	description text check (length(description) >= 30)
);

-- Таблица категорий книг
create table book_category (
	category_id int generated always as identity primary key,
	name varchar(150) not null check (length(name) >= 2)
);

-- Таблица книг
create table book (
	book_id bigint generated always as identity primary key,
	name varchar(255) not null check (length(name) >= 2),
	author_id bigint not null references author(author_id),
	description text check (length(description) >= 30),
	cover varchar(255),
	category_id int not null references book_catefory(category_id)
);

-- -----------------------------------------------------------------
-- Создание временных таблиц и вставка данных

create temp table something (id serial, name text);

insert into something (name) values('hello');
insert into something (name) values('dratuti');

-- -----------------------------------------------------------------
-- Редактирование таблиц

alter table 

-- -----------------------------------------------------------------
-- Удаление таблиц

drop table if exists author, book_category, book;

-- -----------------------------------------------------------------
-- Вставка данных

insert into author (name, description) values (
	'Михаил Шолохов',
	'Великий русский советский писатель, журналист и киносценарист.'
), (
	'Лусиану Рамальо',
	'Автор замечательных книг по языку программирования Python'
);

-- -----------------------------------------------------------------
-- Выборка данных

-- Выбрать все данные из таблицы. Так нельзя делать в prodaction коде.
select * from author;
table author order by name desc limit 1;

select name as Имя, description as Описание from book;

-- Преобразование к верхнему регистру
select upper(name) as Имя, lower(description) as Описание from book;

-- Можно сослаться на конретную таблицу через .
-- Последовательность: база данных -> схема -> таблица
select book.name as Имя from rroom_db.public.book;

show search_path;

-- -----------------------------------------------------------------
-- Выборка данных из других источников

-- Выборка из подзапроса
select description from (
	select * from author where name="Михаил Шолохов"
);

-- Выборка из вьюшки (заранее созданный запрос)

-- Создание вьюшки
create view book_with_author as
	select book.name as book_name, author.name as author_name 
	from book join author using (autor_id);

-- Выборка из вьюшки
select * from book_with_author;

-- Выборка из функции

select num from generate_series(1, 10) num; -- от 1 до 10 включительно 

select d
	from generate_series('2023-01-01'::date,
						 '2023-01-05'::date,
						 '1 day'::interval) d; -- генерация серии дат

-- Выборка из набора значений. Где t - виртуальная таблица						 -- 
select * from (
values (1, 'Алексей'), (2, 'Петр'), (3, 'Иннокентий')
) as t(id, name);
				
-- Выборка из виртуальной таблицы только поля name
select t.name from (
values (1, 'Алексей'), (2, 'Петр'), (3, 'Иннокентий')
) as t(id, name);

-- -----------------------------------------------------------------
-- Фильтрация данных

select * from author where author_id=1;

-- AND
select * from author where name='Михаил Шолохов';
select * from author where name='Михаил Шолохов' and author_id=200;

-- OR
select * from author where name='Михаил Шолохов' or author_id=200;

-- NOT
select * from author where not author_id = 1;
select * from author where author_id != 1;
select * from author where author_id <> 1;

-- Compare
select * from author where author_id <= 2;

-- Between
select * from author where author_id between 1 and 10;

-- IN
select * from author where author_id in (1, 2, 3, 4, 5);

-- Выборка авторов у которых есть книга или книги
select * from author where author_id in (
	select author_id from book
);

select * from author where author_id in (
	select author_id from book where name='Тихий Дон'
);

-- LIKE (шаблоны)
-- % - нуль или больше символов
-- _ - ровно один символ

select * from author where name like '%Шолохов%'

-- ILIKE - поиск по шаблону, не обращает внимание на регистр символов
select * from author where name ilike '%михаил%'
select * from author where name ilike '%миХАил%'

-- ger exp (регуляные выражения)
-- ^ - начало строки
-- \w+ - любой алфивитно-цифровой символ, где + - это один или более символов
-- \W+ - любой не алфивитно-цифровой символ
-- $ - конец строки

-- найти название книги, которое состоит из двух слов
select name from book where name ~ '^\w+\W+\w+$';


-- -----------------------------------------------------------------
-- 11.19 Особое значение NULL, coalesce
-- NULL при сравнении с чем-либо дает NULL

drop table if exists author_without_checks;
table author_without_checks;
-- Создадим временную таблицу
create temp table author_without_checks (
	author_id bigint generated always as identity primary key,
	name varchar(150) not null,
	description text
);

-- и вставим запись
insert into author_without_checks (name) values (
	'какой-то автор'
);

-- проверка на NULL
select * from author_without_checks where description is null;

-- coalesce - подствит нужное значение если значение NULL
select 
	author_id,
	name,
	coalesce(description, 'не заполнено') as description
from author_without_checks;

-- обновление данных
update author_without_checks set description='привет' where author_id=1;


-- -----------------------------------------------------------------
-- 11.20 Сортировка данных

select * from author order by name;

-- по возрастанию (так происходит по умолчанию)
select * from author order by id asc;

-- по убыванию
select * from author order by id desc;

-- можно указать номер колонки для сортировки. Но это лучше не использовать.
select author_id, name, description from author order by 1;

-- можно указать куда вставлять NULL. В данном случае поместить в конц выборки.
select author_id, name, description from author order by 3 nulls last;

-- сортировка по нескольким колонкам

-- создадим временную таблицу
drop table if exists author_tmp;
table author_tmp;

create temp table author_tmp (
	author_id bigint generated always as identity primary key,
	first_name varchar(150) not null,
	last_name varchar(150) not null
);

insert into author_tmp (first_name, last_name) values
   ('Михаил', 'Шишкин'),
   ('Михаил', 'Веллер'),
   ('Михаил', 'Шолохов'),
   ('Михаил', 'Зощенко'),
   ('Михаил', 'Булгаков'),
   ('Александр', 'Беляев'),
   ('Александр', 'Пушкин'),
   ('Лусиану', 'Рамальо');

select * from author_tmp order by first_name;
select * from author_tmp order by first_name, last_name;
select * from author_tmp order by first_name, last_name desc;
select * from author_tmp order by first_name desc, last_name desc;

-- Можно сортировать и виртуальные таблицы

select * from (values ('Яблоко'), ('Апельсин'), ('Банан'));

-- сортировка просто по номеру колонки
select * from (values ('Яблоко'), ('Апельсин'), ('Банан')) t order by 1;

-- даём колонке имя, но сортировка по номеру колонки
select * from (values ('Яблоко'), ('Апельсин'), ('Банан')) t(fruit) order by 1;


-- -----------------------------------------------------------------
-- 11.21 Ограничение количества результатов запроса

-- count - возвращает количество записей в таблице
select count(*) from book;

-- выборка первых 10и записей
select book_id, name, author_id from book order by name limit 10;

-- выборка следующих 10 записей
select book_id, name, author_id from book order by name limit 10 offset 10;


-- -----------------------------------------------------------------
-- Заметки

-- Остановился на 21 главе 

-- -----------------------------------------------------------------
-- Мусор

select |/25;