obsidian/hardcore web development/11. SQL/Сортировка данных.md

select * from author order by name;

-- Сортировка по возрастанию
select * from author order by author_id
select * from author order by author_id asc;

-- Сортировка по убыванию
select * from author order by author_id desc;

-- Сортировка по author_id в порядке убывания (с указанием порядка null)
select author_id, name, description
	from author
order by 1 nulls last;

-- Временная таблица для сортировка
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;

~={yellow}Работа с данным, созданными в SQL-запросе=~

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

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

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

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

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

~={yellow}Задания на сортировку с базой птиц=~

select * from family order by family_id desc;

select * from family order by description nulls first;
select * from family order by description nulls last;
select family_name from family where family_name like 'Д%' order by family_name;

~={yellow}Ограничение количества результатов запроса=~

-- Верунь количество книг
select count(*) from book;

-- Вернуть 10 записей
select book_id, name, author_id
from book
order by name limit 10;

-- Вернуть сдедующие 10 записей (параметр offset)
select book_id, name, author_id
from book
order by name limit 10 offset 10;

~={yellow}Изменение данных в базе=~

--- Изменить описание
update author
set description='Французский писатель, классик приключенческой литературы, один из основоположников жанра научной фантастики.'
where name='Жюль Верн';

select description from author where name='Жюл

--- Дополнить описание
update author
set description=description || ' И человек хороший. Скорее всего.'
where name='Лусиану Рамальо';

select description from author where name='Лусиану Рамальо';

--- Удаление не будет возможно если на эту запись ссылаются другие записи
delete from author where name='какой-то автор';

--- Но можно удалить запись вместе со всеми записями, которые на нее ссылются

--- Для начала создадим новую схему
drop schema if exists tests cascade;
create schema tests

create table tests.author (
	author_id bigint generated always as identity primary key,
	name varchar(150) not null check (length(name) >= 3),
	description text check (length(description) >= 30)
);

create table tests.book_category (
	category_id int generated always as identity primary key,
	name varchar(150) not null check (length(name) >= 2)
);

create table tests.book (
	book_id bigint generated always as identity primary key,
	name varchar(255) not null check (length(name) >= 2),
	-- тут важно! Добавлен ON DELETE CASCADE
	author_id bigint not null references tests.author(author_id) on delete cascade,
	description text check (length(description) >= 20),
	cover varchar(255),
	category_id int not null references tests.book_category(category_id)
);

--- При удалении автора удалятся и его книги
delete from tests.author where name='Михаил Шолохов';

~={yellow}Очистка таблицы. Быстрый способ.=~

truncate table book;

--- Медленный способ
delete from book where true;

~={yellow}Обновление записи=~

--- Создами временную таблицу
create temp table books_with_isbn (
	book_id bigint generated always as identity primary key,
	name varchar(255),
	isbn char(17) unique
);

insert into books_with_isbn(name, isbn) values(
	'Тихий Доннн', '978-5-389-16579'
);

--- При возникновении конфликта isbn обновить запись и
--- взять имя из новой записи
insert into books_with_isbn (name, isbn) values (
	'Тихий Дон', '978-5-389-16579'
) on conflict(isbn) do update 
set name = excluded.name; /*do nothing*/


table books_with_isbn;

~={yellow}Форматированный вывод=~

select format('Птичка: %s', species_name) as "птичка" from species
order by species_name;