obsidian/hardcore web development/11. SQL/Создание таблиц.md

pii Создаем таблицу авторов:

create table 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 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_category(category_id)
);

Создание временной таблицы:

=> psql create temp table something (id serial, name text) 

-- Вставляем данные
=> insert into something (name) values ('hello')
=> insert into something (name) values ('draturi')

-- Получаем данные
=> select * from something;

-- Или так
create temp table author_without_checks (
	author_id bigint generated always as identity primary key,
	name varchar(150) not null,
	description text
);

~={yellow}Создание таблицы из select запроса=~

create table some_table as select * from base_table;

~={cyan}Создание таблиц в базе bird из задания=~

create table family (
	family_id bigint generated always as identity primary key,
	family_name varchar(150) not null,
	description text
);

create table genus (
	genus_id bigint generated always as identity primary key,
	genus_name varchar(150) not null,
	description text,
	family_id bigint not null references family(family_id)
);

create table species (
	species_id bigint generated always as identity primary key,
	species_name varchar(150) not null,
	genus_id bigint not null references genus(genus_id),
	description text,
	average_length smallint not null,
	average_weight smallint not null,
	primary_color varchar(50) not null
);

~={yellow}Сохранение информации=~

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

insert into author (name) values (
	'Жюль Верн'
);

insert into book_category (name) values (
    'Художественная литература'
), (
    'Литература по программированию'
);

insert into book (name, author_id, description, cover, category_id) values (
    'Тихий Дон',
    1,
    'Одно из наиболее значительных, масштабных и талантливых произведений русскоязычной литературы, принесшее автору Нобелевскую премию.',
    'https://cdn.rroom.io/17558b4d-59dd-4f8e-b2c7-51b0d7da5216.png',
    1
), (
    'Python. К вершинам мастерства',
    2,
    'Лучшая книга по углубленному изучению Python.',
    'https://cdn.rroom.io/2bee8345-a535-4fe3-add9-8db804ea89ae.png',
    2
), (
    'Судьба человека',
    1,
    'Пронзительный рассказ о временах Великой Отечественной войны, одно из первых произведений советской литературы, в котором война показана правдиво и наглядною.',
    'https://cdn.rroom.io/271755e5-046f-4842-85cf-4e22cb17b294.png',
    1
);

~={yellow}Выбор информации=~

-- Вывод всей таблицы author
select * from author;

-- Другой способ получить всю таблицу
table author;

-- В таком запросе тоже можно писать сортировки
table author order by name desc limit 1;

select name, description from book;

-- Alias
select name as book_name, description from book;

-- Можно указать имя таблицы. И можно указать алиас таблицы
select b.name as book_name from book as b;

-- Можно указать имя базы данных и имя схемы
select name from rroom_db.public.book as b;

-- Преобразование к верхнему регистру
-- Без alias название колонки будет upper
select upper(name) as book_name from book;

-- Место где postgres ище схемы
show search_path;

-- Получить квадратный корень из числа 225
select |/225;

~={yellow}Выбор информации. Еще варианты.=~

-- У меня не сработал такой запрос 
select description from (
	select * from author where name='Михаил Шолохов'
);

-- Создаем вьюшку и далее выбираем из нее, а не из нового запроса
create view book_with_author as
select book.name, author.name from book join author using (author_id);

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

-- Выбор из функции
select num from generate_series(1, 10) num;

-- Тоже выбор из функции
select d
from generate_series('2023-01-01'::date,
					'2023-01-10'::date,
					'1 day'::interval) d;

-- Для тестов можно создать набор данных
select * from (
values (1, 'Алексей'), (2, 'Петр'), (3, 'Иннокентий')
) as t(id, name);

-- Или так
select * from (values
    ('The Shawshank Redemption', 9.3, 1994),
    ('The Godfather', 9.2, 1972),
    ('The Dark Knight', 9.1, 2008),
    ('Inception', 8.8, 2010)
) t(movie, imdb_rating, year);

~={yellow}Фильтрация данных=~

---Ключевое слово `WHERE` означает фильтрацию, то есть достань мне все записи из таблички авторов, где поле `author_id = 1`
select * from author where author_id=1;

-- and, or, not
select * from author where name='Михаил Шолохов' and author_id=200;
select * from author where name='Михаил Шолохов' or false;
select * from author where not author_id = 1;
select * from author where author_id != 1;
select * from author where author_id <> 1;

-- between
select * from author where author_id between 1 and 2;
select * from author where author_id >= 1  and author_id <= 2;

-- 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
);

-- LIKE (передача шаблона)
select * from author where name like '%Шолохов%';

select * from family where family_name like '%а%' or family_name like '%с%';

-- ilike - шаблон без учета регистра
select * from author where name like '%шолохов%';

-- reg exp
select name from book where name ~ '^\w+\W+\w+$';