Home PostgreSQL
Post
Cancel

PostgreSQL

AGN on Oct 292021-10-29T09:30:00+05:00
11 min read

install postgresql

Установка

1

sudo apt-get install postgresql postgresql-contrib

https://www.digitalocean.com/community/tutorials/how-to-install-postgresql-on-ubuntu-20-04-quickstart-ru Поле установки необходимо создать роли (пользователя postgres) Вход в консоль postgresql под юзером postgres

1

psql postgres

Для создания роли наберите в терминале следующую команду. И введите имя роли. Проще ввсети свое имя под которым вы работаете

1

sudo -u postgres createuser --interactive

Далее создайте базу данных с таким же именем как и имя роли которую вы создали в пострес. Для этого наберите

1

createdb <your_name>

Теперь находясь в терминале под своим именем вы можете просто войти в консоль postgres набрав команду

1

psql

Основные команды для работы в консоли с postgresql

Просмотреть список баз данных

1

postgres=# \l

Далее в качестве примера приводится конспект по видео с youtube https://www.youtube.com/watch?v=WpojDncIWOw

Create DB and requests example

Cоздадим базу данных магазина

1

postgres=# create database shop;

подключимся к базе данных

1

postgres=# \c shop

посмотрим какие есть таблицы в данной базе

1

shop=# \d

1

shop=# \dt

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

Пока таблиц нет. Создадим таблицу сustomer

1
2
3
4
5
6

shop=# create table customer(
shop(# id serial primary key,
shop(# name varchar(255),
shop(# phone varchar(30),
shop(# email varchar(255)
shop(# );

посмотрим созданную таблицу

1

shop=# \d

Что покажет нам список таблиц

1
2
3
4
5
6

              List of relations
 Schema |      Name       |   Type   | Owner 
--------+-----------------+----------+-------
 public | customer        | table    | anton
 public | customer_id_seq | sequence | anton
(2 rows)

Посмотрим саму таблицу

1

shop=# \d customer

1
2
3
4
5
6
7
8
9

                                    Table "public.customer"
 Column |          Type          | Collation | Nullable |               Default                
--------+------------------------+-----------+----------+--------------------------------------
 id     | integer                |           | not null | nextval('customer_id_seq'::regclass)
 name   | character varying(255) |           |          | 
 phone  | character varying(30)  |           |          | 
 email  | character varying(255) |           |          | 
Indexes:
    "customer_pkey" PRIMARY KEY, btree (id)

Coздадим таблицу продуктов

1
2
3
4
5

shop=# create table product(
shop(# id serial primary key,
shop(# name varchar(255),
shop(# description text,
shop(# price integer);

1
2
3
4
5
6
7
8
9

shop=# \d
              List of relations
 Schema |      Name       |   Type   | Owner 
--------+-----------------+----------+-------
 public | customer        | table    | anton
 public | customer_id_seq | sequence | anton
 public | product         | table    | anton
 public | product_id_seq  | sequence | anton
(4 rows)

Создадим таблицу product_photo

1
2
3
4

shop=# create table product_photo(
shop(# id serial primary key, 
shop(# url varchar(255),
shop(# product_id integer references product(id));

В данной строке

1

shop(# product_id integer references product(id));

устанавливает связь между таблицами product_photo и product

Создадим и другие таблицы

1
2
3

shop=# create table cart(
shop(# customer_id integer references customer(id),
shop(# id serial primary key);

1
2
3

shop=# create table cart_product(
shop(# cart_id integer references cart(id),
shop(# product_id integer references product(id));

Посмотрим все созданные таблицы

1
2
3
4
5
6
7
8
9
10
11
12
13
14

shop=# \d
                List of relations
 Schema |         Name         |   Type   | Owner 
--------+----------------------+----------+-------
 public | cart                 | table    | anton
 public | cart_id_seq          | sequence | anton
 public | cart_product         | table    | anton
 public | customer             | table    | anton
 public | customer_id_seq      | sequence | anton
 public | product              | table    | anton
 public | product_id_seq       | sequence | anton
 public | product_photo        | table    | anton
 public | product_photo_id_seq | sequence | anton
(9 rows)

Заполнение таблиц данными

Заполним наши таблицы

1

shop=# insert into customer(name, phone, email) values('Василий', 02, 'vasya@mail.com');

1
2

shop=# insert into customer(name, phone, email)
values('Петр', 03, 'petya@mail.com');

Выбрать все поля из таблицы customer

1
2
3
4
5
6

shop=# select * from customer;
 id |  name   | phone |     email      
----+---------+-------+----------------
  1 | Василий | 2     | vasya@mail.com
  2 | Петр    | 3     | petya@mail.com
(2 rows)

Заполним таблицу product

1
2
3
4
5

shop=# insert into product (name, description, price)
shop-# values ('iPhone', 'крутой телефон', 100000);

shop=# insert into product (name, description, price)
values ('Apple watch', 'крутые часы', 50000);

Посмотрим, что получилось

1
2
3
4
5
6

shop=# select * from product;
 id |    name     |  description   | price  
----+-------------+----------------+--------
  1 | iPhone      | крутой телефон | 100000
  2 | Apple watch | крутые часы    |  50000
(2 rows)

Заполним таблицу product_photo

1

shop=# insert into product_photo (url, product_id) values ('iphone_photo', 1);

Просмотрим существующие записи в таблице

1
2
3
4
5

shop=# select * from product_photo;
 id |     url      | product_id 
----+--------------+------------
  1 | iphone_photo |          1
(1 row)

Join

Потренируемся использовать join

1

shop=# select * from product_photo pp;

1
2
3
4
5

 id |     url      | product_id 
----+--------------+------------
  1 | iphone_photo |          1
(1 row)

pp в данном случае alias (синоним полного названия таблицы) Попробуем join left

1
2
3
4
5

shop=# select pp.* from product_photo pp left join product p on p.id = pp.product_id;
 id |     url      | product_id 
----+--------------+------------
  1 | iphone_photo |          1
(1 row)

Если захотим посмотреть все поля

1
2
3
4
5

shop=# select * from product_photo pp left join product p on p.id = pp.product_id;
 id |     url      | product_id | id |  name  |  description   | price  
----+--------------+------------+----+--------+----------------+--------
  1 | iphone_photo |          1 |  1 | iPhone | крутой телефон | 100000
(1 row)

Eсли захотим посмотреть выборочные поля

1
2
3
4
5

shop=# select pp.*, p.name from product_photo pp left join product p on p.id = pp.product_id;
 id |     url      | product_id |  name  
----+--------------+------------+--------
  1 | iphone_photo |          1 | iPhone
(1 row)

Попробуем добавить фото на несуществующий продукт. Для этого нам необходимо удалить внешний ключ из таблицы product_photo

1

shop=# alter table product_photo drop constraint product_photo_product_id_fkey;

Наша таблица немного изменится было

1
2
3
4
5
6
7
8
9
10
11

shop=# \d product_photo
                                      Table "public.product_photo"
   Column   |          Type          | Collation | Nullable |                  Default                  
------------+------------------------+-----------+----------+-------------------------------------------
 id         | integer                |           | not null | nextval('product_photo_id_seq'::regclass)
 url        | character varying(255) |           |          | 
 product_id | integer                |           |          | 
Indexes:
    "product_photo_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "product_photo_product_id_fkey" FOREIGN KEY (product_id) REFERENCES product(id)

Стало

1
2
3
4
5
6
7
8
9

shop=# \d product_photo
                                      Table "public.product_photo"
   Column   |          Type          | Collation | Nullable |                  Default                  
------------+------------------------+-----------+----------+-------------------------------------------
 id         | integer                |           | not null | nextval('product_photo_id_seq'::regclass)
 url        | character varying(255) |           |          | 
 product_id | integer                |           |          | 
Indexes:
    "product_photo_pkey" PRIMARY KEY, btree (id)

Теперь мы можем создавать фотографию на несуществующий в базе данных продукт

1

shop=# insert into product_photo (url, product_id) values ('unknown photo', 100);

Посмотрим, что получилось

1
2
3
4
5
6

shop=# select * from product_photo;
 id |      url      | product_id 
----+---------------+------------
  1 | iphone_photo  |          1
  2 | unknown photo |        100
(2 rows)

Посмотрим как теперь работает left join. Из левой таблицы выбираются все записи

1
2
3
4
5
6

shop=# select pp.*, p.name from product_photo pp left join product p on p.id = pp.product_id;
 id |      url      | product_id |  name  
----+---------------+------------+--------
  1 | iphone_photo  |          1 | iPhone
  2 | unknown photo |        100 | 
(2 rows)

И сравним это с тем как работает right join

1
2
3
4
5
6

shop=# select pp.*, p.name from product_photo pp right join product p on p.id = pp.product_id;
 id |     url      | product_id |    name     
----+--------------+------------+-------------
  1 | iphone_photo |          1 | iPhone
    |              |            | Apple watch
(2 rows)

Теперь из правой таблицы выбираются все записи ( в данном случае правая таблица - главная). По продукту Apple watch у нас не создано фото. Поэтому в полях id, url, product_id для продукта Apple watch значения - null

Теперь можно посмотреть как работает inner join. Просто заменим в запросе left join на inner join

1
2
3
4
5

shop=# select pp.*, p.name from product_photo pp inner join product p on p.id = pp.product_id;
 id |     url      | product_id |  name  
----+--------------+------------+--------
  1 | iphone_photo |          1 | iPhone
(1 row)

Для запоминания приведем мнемоническую картинку для join ![[join.jpg]]

Удаление и обновление данных

Посмотрим еще раз нашу таблицу product_photo

1
2
3
4
5
6

shop=# select * from product_photo;
 id |      url      | product_id 
----+---------------+------------
  1 | iphone_photo  |          1
  2 | unknown photo |        100![join.jpg](app://local/%2Fhome%2Fanton%2Fror%2Fknowledge_base%2Fimages%2Fjoin.jpg?1619869929725)
(2 rows)

И удалим фото несуществующего товара

1

shop=# delete from product_photo where id=2;

Обновим фото

1

shop=# update product_photo set url='iphone_image2' where id=1;

1
2
3
4
5

shop=# select * from product_photo;
 id |      url      | product_id 
----+---------------+------------
  1 | iphone_image2 |          1
(1 row)

Select

Создадим заказ для одного из клиентов

1

shop=# insert into cart (customer_id) values (1);

1
2
3
4
5

shop=# select * from cart;
 customer_id | id 
-------------+----
           1 |  1
(1 row)

Наполним только что созданную корзину продуктами посредством добавления продуктов в промежуточную таблицу cart_product. Добавим сразу два товара одним запросом

1

shop=# insert into cart_product (cart_id, product_id) values (1, 1), (1, 2);

1
2
3
4
5
6
7

shop=# select * from cart_product 
;
 cart_id | product_id 
---------+------------
       1 |          1
       1 |          2
(2 rows)

Сложные запросы

Сформируем таблицу с именем клиента и с общей суммой заказов этого клиента

Достанем имена клиентов

1

shop=# select c.name from customer c;

1
2
3
4
5

  name   
---------
 Василий
 Петр
(2 rows)

Заджойним корзину

1

shop=# select c.name, cart.id as cart_id from customer c left join cart on cart.customer_id=c.id;

1
2
3
4
5

  name   | cart_id 
---------+---------
 Василий |       1
 Петр    |        
(2 rows)

Используем еще один join

1

shop=# select c.name, cart.id as cart_id from customer c left join cart on cart.customer_id=c.id left join cart_product cp on cp.cart_id=cart.id;

1
2
3
4
5
6

  name   | cart_id 
---------+---------
 Василий |       1
 Василий |       1
 Петр    |        
(3 rows)

Выведем продукты Васи

1

shop=# select c.name, cart.id as cart_id, cp.product_id from customer c left join cart on cart.customer_id=c.id left join cart_product cp on cp.cart_id=cart.id;

1
2
3
4
5
6

  name   | cart_id | product_id 
---------+---------+------------
 Василий |       1 |          1
 Василий |       1 |          2
 Петр    |         |           
(3 rows)

Еще добавим один join

1

shop=# select c.name, cart.id as cart_id, p.name from customer c left join cart on cart.customer_id=c.id left join cart_product cp on cp.cart_id=cart.id left join product p on p.id=cp.product_id;

1
2
3
4
5
6

  name   | cart_id |    name     
---------+---------+-------------
 Василий |       1 | iPhone
 Василий |       1 | Apple watch
 Петр    |         | 
(3 rows)

1

shop=# select c.name, cart.id as cart_id, p.name, p.price from customer c left join cart on cart.customer_id=c.id left join cart_product cp on cp.cart_id=cart.id left join product p on p.id=cp.product_id;

1
2
3
4
5
6

  name   | cart_id |    name     | price  
---------+---------+-------------+--------
 Василий |       1 | iPhone      | 100000
 Василий |       1 | Apple watch |  50000
 Петр    |         |             |       
(3 rows)

Теперь пришла очередь проссумировать товары в корзине. Уберем лишние поля, сгруппируем и проссумируем, используем group by c.name и sum(p.price)

1

shop=# select c.name, sum(p.price) from customer c left join cart on cart.customer_id=c.id left join cart_product cp on cp.cart_id=cart.id left join product p on p.id=cp.product_id group by c.name;

1
2
3
4
5

  name   |  sum   
---------+--------
 Петр    |       
 Василий | 150000
(2 rows)

У Петра есть поле null. Сделаем чтобы отображался 0

1

shop=# select c.name, coalesce(sum(p.price), 0) as order_sum from customer c left join cart on cart.customer_id=c.id left join cart_product cp on cp.cart_id=cart.id left join product p on p.id=cp.product_id group by c.name;

1
2
3
4
5

  name   | order_sum 
---------+-----------
 Петр    |         0
 Василий |    150000
(2 rows)

Выполним сортировку по сумме заказа

1

shop=# select c.name, coalesce(sum(p.price), 0) as order_sum from customer c left join cart on cart.customer_id=c.id left join cart_product cp on cp.cart_id=cart.id left join product p on p.id=cp.product_id group by c.name order by order_sum desc;

1
2
3
4
5

  name   | order_sum 
---------+-----------
 Василий |    150000
 Петр    |         0
(2 rows)

Если нам необходимо вывести только тех клиентов, которые что-то приборели, то есть у кого сумма заказа больше нуля, воспользуемся having

1

shop=# select c.name, coalesce(sum(p.price), 0) as order_sum from customer c left join cart on cart.customer_id=c.id left join cart_product cp on cp.cart_id=cart.id left join product p on p.id=cp.product_id group by c.name having sum(p.price)>0;

1
2
3
4

  name   | order_sum 
---------+-----------
 Василий |    150000
(1 row)

Отличие where от having

having фильтрует группы where фильтрует строки

Пример использования limit

1

shop=# select * from customer order by name limit 1;

1
2
3
4

 id |  name   | phone |     email      
----+---------+-------+----------------
  1 | Василий | 2     | vasya@mail.com
(1 row)

Выводится только один клиент Для смещения применяем offset

1

shop=# select * from customer order by name limit 1 offset 1;

1
2
3
4

 id | name | phone |     email      
----+------+-------+----------------
  2 | Петр | 3     | petya@mail.com
(1 row)

Что применяется часто для пагинации - разбивки больших массивов информации на страницы

Database
database postgres PostgreSQL SQL
This post is licensed under CC BY 4.0 by the author.
Recent Update
Contents

VPS

Gems