SQL с PostgreSQL для инженеров Node.js

Думайте о SQL (языке структурированных запросов) как о задаче вопросов базе данных (сколько, что это за и т. Д.)

Эта статья будет дампом мозгов о том, как использовать систему управления реляционными базами данных PostgreSQL (СУБД).

Основное внимание будет уделено ключевым и продвинутым концепциям стандартного языка SQL и его использованию в PostgreSQL.

Мы будем использовать PgAdmin в качестве панели управления для выполнения SQL-запросов. Это точно такие же запросы, которые вы будете выполнять в своих приложениях на Node.js (с помощью модуля объектно-реляционного сопоставления [ORM}, такого как TypeORM для поклонников TypeScript).

Установка PostgreSQL (PG) в Ubuntu

sudo apt install -y postgresql postgresql-contrib
# change the terminal user to postgres user (created when installing PG):
sudo -i -u postgres
# once logged as postgres user:
psql
 
# once in the PG CLI, type “help” or “\q” to quit
# OR access PG CLI without switching terminal user
sudo -u postgres psql
# create password for default postgres user, once in PG CLI:
ALTER USER postgres PASSWORD ‘myPassword’;

Вы также можете запустить официальный образ PostgreSQL Docker и подключиться к нему с вашего хост-компьютера независимо от вашей операционной системы.

Установка pgAdmin в ubuntu

wget — quiet -O — https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo “deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main” |sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install pgadmin4 pgadmin4-apache2
# During installation, you’re asked to configure initial user 
account. Provide email address.
# by default: postgres@localhost
# enter admin password
# verify apache is running
systemctl status apache2
# if using UFW (Ubuntu Firewall)
sudo ufw allow http
sudo ufw allow https
# execute pgadmin in Ubuntu program launcher
# it will open the browser
# you will be prompted to set a password for accessing pgadmin dashboard
# restart computer (or logout) (not necessary)

Добавить подключение к локальному PG в pgadmin

# click on add a new server
# name that server (arbitrary name)
# in connection tab, enter address of server (127.0.0.1 for local instance) 
# (if running the PG Docker image, make sure the running container ports are exposed to your host system - read the doc)
# enter username and password of a database user
# save
# that connection should appear on the left under server list

Добавить новую базу данных в pgadmin

Щелкните правой кнопкой мыши «Базы данных» / создать / базу данных.

Восстановление базы данных в pgadmin

вы можете восстановить базу данных из файла .tar
из существующей базы данных / щелчок правой кнопкой мыши / восстановление
указать путь к файлу .tar
в параметрах восстановления / выберите предварительные данные, данные, пост-данные
нажмите на восстановление

Запрос базы данных в pgadmin

выберите базу данных
Инструмент правого щелчка / запроса ИЛИ меню "Инструменты" в заголовке
вы можете увидеть существующие таблицы:
‹DB_NAME› / Schemas / public / Tables
у вас есть доступ к истории запросов и вы можете копировать запросы прямо в редактор
вы можете экспортировать результат запросов как csv
у вас есть доступ к документации для pgadmin и postgresql через меню справки в заголовке
введите запрос в текстовой области и нажмите F5 или символ воспроизведения, чтобы выполнить его

Оператор SELECT

получить информацию из таблицы
можно комбинировать с другими операторами в более сложных запросах

СИНТАКСИС:

SELECT <column_name> FROM <table_name>;
# same as:
select <column_name> from <table_name>;
# SQL is case insenitive as far as commands

точка с запятой не обязательна в PG или pgadmin (добавлено автоматически)
, но может помочь для читабельности и перекрестной совместимости РСУБД запросов.
PG сначала смотрит на запрашиваемую таблицу, а не на какой столбец
Чтобы выбрать несколько столбцов, разделите их запятой («,»)
(порядок имен столбцов не имеет значения для PG, однако pgadmin отобразит результаты в порядке запроса)

SELECT c1, c2 FROM mytable;

- чтобы выбрать все столбцы в таблице, используйте *

SELECT * FROM mytable;

Запросы всего = ›ПЛОХАЯ ПРАКТИКА, потому что увеличивает трафик между сервером базы данных и приложением. Это замедляет получение результатов. Делайте это только в тестовых / демонстрационных целях, а не в продакшене.

В pgadmin, чтобы увидеть все столбцы в таблице
выберите таблицу / столбцы

ВЫБЕРИТЕ ДИСКТИНКТ

столбец в таблице может содержать повторяющиеся значения
чтобы перечислить уникальные / уникальные значения, используйте ключевое слово DISTINCT
DISTINCT оперирует столбцом

СИНТАКСИС:

SELECT DISTINCT col_name FROM table
# also works with parentheses
SELECT DISTINCT(col_name) FROM table
# Answers the question what are the unique values in col_name ?
Example:
SELECT DISTINCT rental_rate FROM film;
 
# answers “how many rental rates are there in the film column ?” or “what are the rental rates ?”

PG выполняет запросы наиболее оптимизированным способом, вы не обязательно получаете результаты каждый раз в одном и том же порядке (если вы не используете оператор SORT)

СЧИТАТЬ

Функция COUNT возвращает количество входных строк, соответствующих определенному условию запроса.
вы можете применить COUNT к определенному столбцу или ко всем столбцам, и результат должен быть таким же, потому что он просто подсчитывает количество возвращенных строк

СИНТАКСИС:

SELECT COUNT(name) from table;
SELECT COUNT(*) from table;

COUNT требует скобок, потому что это функция, выполняющая что-то
указание имени столбца может помочь понять вопрос, на который вы пытаетесь ответить
COUNT более полезно в сочетании с такими операторами, как DISTINCT.

SELECT COUNT(DISTINCT color_gradient) FROM color;
# you are executing a count on the result of DISTINCT color_gradient (how many different color gradients ?)
SELECT COUNT(DISTINCT amount) FROM payment;
# you are executing a count on the result of DISTINCT amount (how many different amounts ?)

ВЫБЕРИТЕ ГДЕ

Оператор WHERE позволяет указать условия для столбцов, которые должны быть возвращены,
Обычно используется с оператором SELECT (но не только)

СИНТАКСИС:

SELECT col1, col2
FROM table1
WHERE <conditions>;

Оператор WHERE появляется сразу после оператора FROM
условия используются для фильтрации строк, возвращаемых оператором SELECT
PG предоставляет стандартные операторы для выполнения условий:

- - - - - - - - -
операторы сравнения
- - - - - - - - -

сравнить значение столбца с чем-то

= equal
> greater than
< less than
>= greater than or equal to 
<= less than or equal to
<> / != not equal to (two options)

- - - - - - - - -
логические операторы
- - - - - - - - -

объединить несколько операторов сравнения

AND
OR
NOT

PG использует ОДИНАРНЫЕ ЦИТАТЫ для строк.
Конечно, важно использование заглавных букв внутри кавычек.

SELECT name;choice 
FROM table
WHERE name=’Florian’ AND choice=’red’;
SELECT * FROM film
WHERE rental_rate > 1.99
  AND replacement_cost >= 19.99
  AND rating = ‘R’;

нет необходимости повторять оператор WHERE для каждого ключевого слова AND

SELECT COUNT(*) FROM film
WHERE rental_rate > 4
AND replacement_cost >= 19.99
AND rating = ‘R’;
SELECT COUNT(*) FROM film
WHERE rating = ‘PG-13’ OR rating = ‘R’;
SELECT COUNT(*) FROM film
WHERE rating != ‘R’;

СОРТИРОВАТЬ ПО

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

алфавитный порядок для столбцов на основе строк
числовой порядок для числовых столбцов

СИНТАКСИС

SELECT col1, col2
FROM table
ORDER BY col1,col2 <ASC | DESC>

ORDER BY обычно появляется ближе к концу запроса, так как вы хотите сначала выбрать и отфильтровать перед окончательной сортировкой
По умолчанию выполняется сортировка ASCendingly (от наименьшего к наибольшему), если вы опускаете тип сортировки.
можно использовать ORDER BY для нескольких столбцов
это имеет смысл, когда в одном столбце есть повторяющиеся записи

— — — — — — — — — — — — — — — — — — — — — — — -
|     company     |    name    |   sales      |
 — — — — — — — — — — — — — — — — — — — — — — — — -
|     Apple       |   Florian  |   987654     |
 — — — — — — — — — — — — — — — — — — — — — — — — -
|     Apple       |    Malua   |    352468    |
 — — — — — — — — — — — — — — — — — — — — — — — — -
|     Aveni       |     Jeqn   |     2056     |
 — — — — — — — — — — — — — — — — — — — — — — — — -
|     Zenika      |     Toto   |    100000    |
 — — — — — — — — — — — — — — — — — — — — — — — — -
|     Aveni       |     Titi   |    5346      |
— — — — — — — — — — — — — — — — — — — — — — — — -
# running the following query on the above table
SELECT company, name
FROM table
ORDER BY company,sales;
# first sort = alpha order on company column
# second sort = numer order (ASC) on sales column

можно указать для каждого столбца вид сортировки:

ORDER BY company DESC,sales ASC;

ORDER BY идет после команды WHERE
вы можете упорядочить по столбцу, который вы не выбираете / не извлекаете, но это создает путаницу
если вы сортируете по столбцу, обычно его извлекают, чтобы увидеть сортировку

LIMIT

позволяет ограничить количество строк, возвращаемых при запросе таблицы
полезно получить частичные данные, чтобы увидеть макет таблицы
полезно в сочетании с ORDER BY
LIMIT - это последняя команда, которую нужно выполнить

# answers “what were the 5 most recent paymentswhere there was a monetary transaction ?” :
SELECT * FROM payment
WHERE amount != 0.00
ORDER BY payment_date DESC
LIMIT 5;
# to see the layout of the payment table:
SELECT * FROM payment
LIMIT 1;
# "what were the id’s of the first customers who created a payment ?" :
SELECT customer_id FROM payment
ORDER BY payment_date ASC
LIMIT 10;
# "What are the 5 shortest movies in length ?" :
SELECT title,length FROM film
ORDER BY length ASC
LIMIT 5;

МЕЖДУ

Оператор BETWEEN, используемый для сопоставления значения с диапазоном значений
он включает границы,
эквивалентные:

WHERE value >= <low_boundary> AND value <= <high_boundary>;

СИНТАКСИС:

WHERE value BETWEEN <low> AND <high>;

НЕ МЕЖДУ

value NOT BETWEEB <low> AND <high>

для получения строк, в которых значение исключено, значение из диапазона значений
эквивалентное:

WHERE value < <low> AND value > <high>

Оператор BETWEEn может использоваться с датами в стандартном формате ISO 8601 (гггг-мм-дд):

date BETWEEN ‘2020–03–16’ AND ‘2020–06–13’;

при использовании оператора BETWEEN с датами, которые содержат временную метку, обратите внимание на использование операторов сравнения BETWEEN vs `‹ = `или` ›=`, потому что временные метки начинаются с 0:00, если вы хотите инклюзивность или эксклюзивность около 0:00 AM

SELECT COUNT(*) FROM payment
WHERE amount NOT BETWEEN 8 AND 9;
SELECT * FROM payment
WHERE payment_date BETWEEN ‘2007–02–01’ AND ‘2007–02–15’;
# will retrieve everything up until 2007–02–15 0:00
# does NOT go to the 24:00 mark
# Using the BETWEEN operator will stop at the beginning of the upper boundary day (0:00 AM); not the end of the day (24:00 PM).
# So, results during 2007–02–15 will not be displayed.

Чтобы не усложнять, при использовании BETWEEN с датами ISO (проверьте документацию)

всегда указывайте следующую дату как верхнюю границу, чтобы получать результаты до предыдущего дня включительно

IN

Оператор IN полезен для проверки того, включено ли значение в список из нескольких опций.

СИНТАКСИС

WHERE value IN (opt1,opt2)

Примеры:

SELECT color FROM table
WHERE color IN (‘red’, ‘blue’, ‘green’); // implicit OR statement color = ‘red’ OR color = ‘blue’:
SELECT color FROM table
WHERE color NOT IN (‘red’, ‘blue’, ‘green’);
SELECT * FROM payment
WHERE amount IN (0.99, 1.98, 1.99)
ORDER BY amount;
SELECT * FROM customer
WHERE first_name IN (‘John’, ‘Florian’, ‘Robert’);
SELECT * FROM customer
WHERE first_name LIKE ‘J%’ ;
SELECT * FROM customer
WHERE first_name ILIKE ‘J%’ AND last_name ILIKE ‘s%’ ;

LIKE и ILIKE (сопоставление с образцом)

Оператор LIKE позволяет выполнять сопоставление шаблонов со строковыми данными с использованием подстановочных знаков.

# percent (%) matches any sequence of characters
 
# underscore (_) matches any single character
 
# all names starting by capital A
WHERE name LIKE ‘A%’;
 
# all names ending with lowercqse a
WHERE name LIKE ‘%a’;
 
# LIKE is case sensitive

ILIKE не чувствителен к регистру

# Use _ as a wild card for a single character
WHERE title LIKE ‘Mission Impossible _’:
# use multiple underscores to match a specific number of single characters
WHERE version LIKE ‘#__’;
# combining pattern matching operators
# matches Terrence, Perry, Berringer, Merlot, Cerlock :
WHERE name LIKE ‘_er%’;
 
# PG supports full regex, check the docs

Примеры:

# how many payment transactions where greater than $5 ?
SELECT COUNT(amount)
FROM payment
WHERE amount > 5;
# How many actors have a first name that starts with letter P ?
SELECT COUNT(*)
FROM actor
WHERE first_name LIKE ‘P%’
# How many districts are your customers from ?
SELECT COUNT(DISTINCT district)
FROM address;
# List the districts of your customers
SELECT DISTINCT district
FROM address;
# How many films have a rating of R and a replacement cost beteen $5 and $15 ?
SELECT COUNT(*)
FROM film
WHERE rating = ‘R’
AND replacement_cost BETWEEN 5 AND 15;
# How many films have the word “Truman” somewhere in the title?
SELECT COUNT(*)
FROM film
WHERE title LIKE ‘%Truman%’;

Агрегатные функции

SQL предоставляет множество агрегатных функций

идея состоит в том, чтобы взять несколько входов и вернуть один выход

общие агрегатные функции:

AVG() — returns average value
 COUNT() — returns the number of values
 MAX() — returns maximum value
 MIN() — returns minimum value
 SUM() — returns sum of all values

вышеуказанные функции применяются к одному столбцу, что имеет смысл

агрегатные функции используются в предложениях SELECT или HAVING.
AVG () возвращает значение с плавающей запятой с множеством десятичных знаков. Используйте ROUND (), чтобы указать точность после десятичной дроби
COUNT () просто возвращает количество строк, поэтому вы обычно просто используете COUNT (*)

SELECT MIN(replacement_cost) FROM film;
SELECT MAX(replacement_cost) FROM film;
SELECT MIN(replacement_cost), MAX(replacement_cost) FROM film;
SELECT ROUND(AVG(replacement_cost), 2) // precision of 2 decimals
FROM film;
SELECT SUM(replacement_cost)
FROM film;

ГРУППА ПО

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

____________________________
|    category   |   data   |
____________________________
|        A      |   152    |
____________________________
|        A      |   486    |
____________________________
|        B      |   749    |
____________________________
|        B      |   384    |
____________________________
|        C      |   511    |
____________________________

вам нужно выбрать столбец, содержащий категориальные данные, чтобы использовать GROUP BY (чтобы результаты были понятны)
категориальные столбцы не непрерывны
, они могут быть числовыми, но обрабатываться как категория (класс 1, класс 2, класс 3). В приведенной выше таблице у вас есть класс A, класс B и класс C. В этом контексте класс является синонимом категории.

SYNTAX

SELECT category_col, <AGG_FUNC>(data_col)
FROM table
WHERE category-col != ‘A’
GROUP BY category-col;

GROUP BY должна располагаться сразу после оператора FROM или WHERE.

В операторе SELECT столбцы должны:

вызвать на них агрегатную функцию
быть в звонке GROUP BY

Для ясности: если вы ВЫБИРАЕТЕ столбец, но не применяете к нему агрегатную функцию, он ДОЛЖЕН появиться в GROUP BY:

SELECT company,division, SUM(sales)
FROM finance_table
# need to include campany and division columns because not used in aggregate function:
GROUP BY company,division;
# returns the total sum of sales per division per company

Операторы WHERE не должны ссылаться на результат агрегирования. Используйте предложение HAVING для фильтрации результатов агрегирования

SELECT company,division, SUM(sales)
FROM finance_table
# here you filter the division column before grouping by:
WHERE division IN (‘marketing’,’transport’)
GROUP BY company,division;

Если вы хотите отсортировать результаты на основе агрегата, обязательно укажите всю функцию:
то, что вы ORDER BY, должно существовать в предложении SELECT

SELECT company, SUM(sales)
FROM finance_table
GROUP BY company
ORDER BY SUM(sales);
SELECT customer_id
FROM payment
GROUP BY customer_id
ORDER BY customer_id;

Примеры:

# What is the total amount spent per customer ?
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC;
# How many transactions per customer ?
SELECT customer_id, COUNT(amount)
FROM payment
GROUP BY customer_id
ORDER BY COUNT(amount) DESC;
# What is the total amount spent per staff per customer ?
SELECT customer_id,staff_id, SUM(amount)
FROM payment
GROUP BY staff_id,customer_id;
# usually, you are going to GROUP BY in the same order than you SELECT (order in GROUP BY matters):
SELECT staff_id,customer_id, SUM(amount)
FROM payment
GROUP BY staff_id,customer_id
ORDER BY staff_id, SUM(amount) DESC;
# To GROUP BY by a timestamp, you must first convert it to a date (removes the time part, to have large enough classes to group by)
 
SELECT DATE(payment_date), SUM(amount)
FROM payment
GROUP BY DATE(payment_date)
ORDER BY SUM(amount)
# How many payment did each staff member handled ?
SELECT staff_id, COUNT(amount)
FROM payment
GROUP BY staff_id;
# What is the average replacement cost per MPAA rating ?
SELECT rating,ROUND(AVG(replacement_cost), 2)
FROM film
GROUP BY rating;
# What are the top 5customer ids of the top 5 customers by total spend ?
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC
LIMIT 5;

ИМЕЮЩИЕ

позволяет фильтровать после агрегирования
вы не можете использовать WHERE для фильтрации агрегированных результатов, потому что они появляются после выполнения WHERE
HAVING позволяет нам фильтровать совокупные результаты вместе с GROUP BY

SELECT company, SUM(sales)
FROM finance_table
WHERE company != ‘Google’
GROUP BY company
HAVING SUM(sales) > 1000;
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 100;

вы должны использовать агрегатную функцию в HAVING, а не только имя столбца

Примеры:

# Number of cistomers per store where greater than 300 ?
SELECT store_id, COUNT(customer_id) FROM customer
GROUP BY store_id
HAVING COUNT(customer_id) > 300
# What are customers with 40 payment or more ?
SELECT customer_id, COUNT(payment_id)
FROM payment
GROUP BY customer_id
HAVING COUNT(payment_id) >= 40;
# What are the customer ids who have spent more than $100 in payment transactions with staff id member 2 ?
SELECT staff_id, customer_id, SUM(amount)
FROM payment
WHERE staff_id = 2
GROUP BY staff_id, customer_id
HAVING SUM(amount) > 100;
# Return the customer IDs of customers who have spent at least $110 with the staff member who has an ID of 2.
SELECT customer_id, SUM(amount)
FROM payment
WHERE staff_id = 2
GROUP BY customer_id
HAVING SUM(amount) >= 110;
# How many films begin with the letter J?
SELECT count(*)
FROM film
WHERE title LIKE ‘J%’;
# What customer has the highest customer ID number whose name starts with an ‘E’ and has an address ID lower than 500?
SELECT first_name, last_name
FROM customer
WHERE first_name LIKE ‘E%’
AND address_id < 500
ORDER BY customer_id DESC
LIMIT 1;

ПРИСОЕДИНЯЕТСЯ

объединить информацию из нескольких таблиц

AS

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

СИНТАКСИС:

SELECT column AS new_name
FROM table;
SELECT SUM(column) AS new_name
FROM table;

помогает для читабельности вывода

SELECT SUM(amount) AS net_revenue
FROM payment;

Оператор AS выполняется в КОНЦЕ запроса
. Вы НЕ МОЖЕТЕ использовать псевдоним внутри оператора WHERE или HAVING:

SELECT customer_id, SUM(amount) AS total_spent
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 100;
# you **cannot** use the alias in a conditional clause
# AS creates aliases for the OUTPUT RESULTS

Псевдоним, созданный AS в операторе FROM, можно повторно использовать в запросе (см. Раздел подзапроса).

ВНУТРЕННЕЕ СОЕДИНЕНИЕ (симметричное)

это JOIN по умолчанию
JOINs позволяют нам объединять несколько таблиц вместе
различные типы JOIN, чтобы решить, как работать с данными, присутствующими только в одной из соединенных таблиц

_________________________
 REGISTRATIONS LOGINS
_________________________
-----------------------------------------
|  reg_id  |  name  |  log_id |  name   |
-----------------------------------------
|     1    | Andrew |    1    |  Xavier |
-----------------------------------------
|     2    |  Bob   |    2    |  Andrew |
-----------------------------------------
|     3    | Carl   |    3    |  Yoland |
-----------------------------------------
|     4    | David  |    4    |   Bob   |
-----------------------------------------

INNER JOIN приведет к набору записей, соответствующих ОБЕИМ таблицам (выше ttable)

СИНТАКСИС:

SELECT * 
FROM tableA
INNER JOIN tableB
ON tableA.col_match = tableB.col_match;

ВНУТРЕННИЕ СОЕДИНЕНИЯ являются симметричными, поскольку вы возвращаете строки, существующие в обеих таблицах, в одном столбце (то есть в обеих таблицах).

SELECT *
FROM registrations
INNER JOIN logins
ON registrations.name = logins.name;

вернет все столбцы совпадающих строк в обеих таблицах

SELECT reg_if,logins.name,log_id
FROM registrations
INNER JOIN logins
ON registrations.name = logins.name;

порядок таблиц не имеет значения в qINNER JOIN

...
FROM registrations
INNER JOIN logins
...
# same as :
...
FROM logins
INNER JOIN registrations
...

JOIN без INNER будет рассматриваться PostgreSQL как INNER JOIN.
При выборе столбцов, если они появляются только в одной таблице, вам не нужно указывать таблицу (`tableA.col_1`)

SELECT payment_id, payment.customer_id, first_name
FROM payment
INNER JOIN customer
ON payment.customer_id = customer.customer_id

если столбец отображается в обеих таблицах, необходимо указать имя таблицы.

ВНЕШНЕЕ СОЕДИНЕНИЕ

позволяет работать со значениями, присутствующими только в одной из объединяемых таблиц

ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ (симметричное)

захватывает все в обеих таблицах и заполняет нулевыми значениями, если в другой таблице нет совпадений

СИНТАКСИС:

SELECT * FROM tableA
 FULL OUTER JOIN tableB
 ON tableA.col_match = tableB.col_match;
# Postgres will grab registrations table then look for matches in logins tables
# will place matches on the same rows like an INNER JOIN
# then will fill in null values no match in other table
 
_______________________________________
 reg_id     name     log_id      name
_______________________________________
     1     Andrea       2       Andrew
_______________________________________ 
     2       Bob        4         Bob
_______________________________________
     3     Charlie   [ null ]  [ null ]
_______________________________________
     4      David    [ null ]  [ null ]
_______________________________________
  [ null ] [ null ]      1      Xavier
_______________________________________
  [ null ] [ null ]      3      Yolanda
_______________________________________

ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ с ГДЕ (симметричное)

уточнение ПОЛНОГО ВНЕШНЕГО СОЕДИНЕНИЯ с ГДЕ
получить строки, уникальные для любой таблицы (не найдены в обеих / найдены только в одной) = противоположность ВНУТРЕННЕГО СОЕДИНЕНИЯ

СИНТАКСИС:

SELECT * FROM tableA
 FULL OUTER JOIN tableB
 ON tableA.col_match = tableB.col_match
 WHERE tableA.id_col IS null 
 OR tableB.id_col IS null;
# will only show results that are unique to either registrations or logins no more values that appear in both:
 
 SELECT * FROM registrations
 FULL OUTER JOIN logins
 ON registrations.name = logins.name
 WHERE registrations.reg_id IS null 
 OR logins.log_id IS null;
# check for customers that don’t have transactions and transactions that don’t have customer associated to:
SELECT *
FROM customer
FULL OUTER JOIN payment
ON customer.customer_id = payment.customer_id
WHERE customer.customer_id IS null
OR payment.payment_id IS null

LEFT OUTER JOIN / LEFT JOIN

приводит к набору записей, которые находятся в левой таблице
, если нет совпадения с правой таблицей, результаты равны нулю

СИНТАКСИС:

SELECT * 
 FROM tableA
 LEFT OUTER JOIN tanleB // same as LEFT JOIN tanleB in SQL syntax 
 ON tableA.col_match = tableB.col_match

Несимметричный = порядок таблиц имеет значение
Левая таблица - это первая таблица, на которую ссылается оператор FROM.
результатом будут все значения из левой таблицы (значения исключительно в левой таблице и значения в обеих таблицах)
значения, исключительные для левой таблицы, будут иметь столбцы правой таблицы, заполненные нулем

SELECT *
 FROM registrations
 LEFT OUTER JOIN logins
 ON registrations.name = logins.name;
_______________________________________
   reg_id     name    log_id      name
_______________________________________
     1       Andrew     2        Andrew
_______________________________________
     2        Bob       4          Bob
_______________________________________
     3      Charlie  [ null ]   [ null ]
_______________________________________
     4       David   [ null ]   [ null ]
_______________________________________

LEFT OUTER JOIN / LEFT JOIN с WHERE

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

SELECT * 
 FROM tableA
 LEFT OUTER JOIN tanleB 
 ON tableA.col_match = tableB.col_match
 WHERE tableB.id_col IS null;
SELECT *
 FROM registrations
 LEFT OUTER JOIN logins
 ON registrations.name = logins.name
 WHERE logins.log_id IS null;
_____________________________________________
   reg_id     name      log_id      name
_____________________________________________
      3      Charlie   [ null ]    [ null ]
_____________________________________________
      4       David    [ null ]    [ null ]
_____________________________________________
SELECT film.film_id, title, inventory_id, store_id
FROM film
LEFT OUTER JOIN inventory
ON film.film_id = inventory.film_id;
# Which films are not in inventory nor in stor ?
SELECT film.film_id, title, inventory_id, store_id
FROM film
LEFT OUTER JOIN inventory
ON film.film_id = inventory.film_id
WHERE inventory.film_id IS NULL;

ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ / ПРАВОЕ СОЕДИНЕНИЕ

то же самое, что LEFT JOIN (/ LEFT OUTER JOIN), за исключением того, что таблицы переключаются
строго то же самое, что и переключение порядка таблиц в LEFT OUTER JOIN

СИНТАКСИС:

SELECT *
FROM tableA
RIGHT OUTER JOIN tableB
ON tableA.col_match = tableB.col_match;

вы берете все в таблице оператора RIGHT OUTER JOIN

Цель состоит в том, чтобы сохранить тот же порядок таблиц в запросе, но иметь механизмы для захвата всего слева или справа (диаграмма Венна)

RIGHT OUTER JOIN / RIGHT JOIN с WHERE

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

SELECT *
FROM tableA
RIGHT OUTER JOIN tableB
ON tableA.col_match = tableB.col_match
WHERE tableA.id_col IS null;

Вы должны иметь возможность получить тот же результат с LEFT JOIN или RIGHT JOIN, переключая порядок таблиц

СОЮЗ

Оператор UNION, используемый для объединения набора результатов из двух или более операторов SELECT
служит для объединения двух результатов вместе

СИНТАКСИС:

SELECT col_name(s) FROM table1
UNION
SELECT col_name(s) FROM table2;

SELECT, которые объединены в UNION, должен быть логичным и каким-то образом совпадать, чтобы Postgres мог складывать результаты друг над другом.

____________________
    sales2021_Q1     
____________________
|  NAME   |  AMOUNT |
____________________
|  David  |   1550  |
____________________
|  Bruna  |   1050  |
____________________
___________________
    sales2021_Q2
___________________
|  NAME   | AMOUNT |
___________________
|  David  |  2050  |
___________________
|  Bruna  |  1550  |
___________________
SELECT * FROM sales2021_Q1
UNION
SELECT * FROM sales2021_Q2;
# displays:
 
_______________
 NAME  | AMOUNT 
_______________
 David | 1550 
_______________
 Bruna | 1050 
_______________
 David | 2050
_______________
 Bruna | 1550
_______________
# what are the emails of the customers who live in California ?
SELECT district, email
FROM customer
INNER JOIN address
ON customer.address_id = address.address_id
WHERE address.district = ‘California’;
# List all movies Nick Wahlberg has been in:
SELECT film.title, actor.first_name, actor.last_name from film_actor
INNER JOIN film
ON film_actor.film_id = film.film_id
INNER JOIN actor
ON film_actor.actor_id = actor.actor_id
WHERE actor.first_name = ‘Nick’ AND actor.last_name = ‘Wahlberg’;
# The second INNER JOIN is on the results of the first INNER JOIN

РАСШИРЕННЫЕ ТЕМЫ SQL

Метки времени и ЭКСТРАКТ

более полезно при создании баз данных, чем их запросы
PG может хранить информацию о дате и времени:

TIME — contains only time
DATE — contains only date
TIMESTAMP — contains date and time
TIMESTAMPZ — contains date, time and timezone

при разработке таблицы и базы данных и выборе типа данных времени следует тщательно продумать (прочтите документацию).
в зависимости от варианта использования вам может потребоваться или не потребоваться полный уровень TIMESTAMPZ
Вы всегда можете восстановить историческую информацию, но не можете ее добавить
думайте о долгосрочной перспективе при выборе данных, вы не можете позже добавить временные данные, которые вы изначально не записывали
Этот вид данных наиболее полезен при работе с CRUD-операциями.

SHOW ALL
# Show the values of all configuration parameters, with descriptions.
 
SHOW TIMEZONE
# show the timezone runtime parameter of the computer on which PG is running
SELECT NOW()
# returns a timestamp with time zone (GMT standandard-
 
SELECT TIMEOFDAY()
# returns timestamp as a human friendly string
 
SELECT CURRENT_TIME
# returns a time with time zone
 
SELECT CURRENT_DATE
# returns only the date

ИЗВЛЕЧЕНИЕ ИНФОРМАЦИИ О ВРЕМЕНИ И ДАТЕ

ИЗВЛЕКАТЬ( )

позволяет получить подкомпонент значения даты
вы можете извлечь:

YEAR
 MONTH
 DAY
 WEEK
 QUARTER

СИНТАКСИС:

EXTRACT(YEAR FROM date_col)

Примеры:

SELECT EXTRACT(YEAR FROM payment_date) AS payment_year
FROM payment;
SELECT EXTRACT(MONTH FROM payment_date) AS payment_month
FROM payment;
SELECT EXTRACT(QUARTER FROM payment_date) AS payment_quarter
FROM payment;

ВОЗРАСТ( )

вычисляет и возвращает текущий возраст с учетом отметки времени

СИНТАКСИС:

AGE(date_col)
# returns 
 20 years 1 mon 5 days 01:34:13.003423
SELECT AGE(payment_date)
FROM payment;

TO_CHAR ()

общая функция для преобразования типов данных (не только типов даты) в текст
полезно для формирования метки времени
СИНТАКСИС:

TO_CHAR(date_col, ‘MM-dd-YYYY’)

посмотрите документы PG (функции форматирования типов данных) для шаблонов для форматирования вывода

9.8. Функции форматирования типов данных
Функции форматирования PostgreSQL предоставляют мощный набор инструментов для преобразования различных типов данных (дата / время… www.postgresql.org

Разделение между частями даты произвольное.

TO_CHAR(date_col, ‘MM-dd-YYYY’)
 TO_CHAR(date_col, ‘MMddYYYY’)
 TO_CHAR(date_col, ‘MM/dd/YYYY’)
 TO_CHAR(date_col, ‘MONTH YYYY’)

Примеры:

# During which months did payments occur (show full name of month) ?
 
SELECT DISTINCT TO_CHAR(payment_date, ‘MONTH’)
FROM payment;
# How many payments occurred on a Monday ?
SELECT COUNT(*)
FROM payment
WHERE EXTRACT(
 ISODOW FROM payment_date
) = 1;

МАТЕМАТИЧЕСКИЕ ФУНКЦИИ И ОПЕРАТОР

Посмотрите документы:

Https://www.postgresql.org/docs/12/functions-math.html

SELECT ROUND(rental_rate/replacement_cost, 4) * 100 AS percent_cost
FROM film;

СТРОЧНЫЕ ФУНКЦИИ И ОПЕРАЦИИ

PG предоставляет множество строковых функций и операторов, которые позволяют редактировать / комбинировать / изменять столбцы текстовых данных.
посмотрите документы
https://www.postgresql.org/docs/12/functions-string.html

SELECT length(first_name)
FROM customer;
# use alias to give name to result column
SELECT first_name || ‘ ‘ || UPPER(last_name) AS full_name
FROM customer;
# Generate email from first letter of firstname and lastname:
SELECT LOWER(LEFT(first_name, 1)) || LOWER(last_name) || ‘@gmail.com’ 
AS customer_email
FROM customer:

ПОД ЗАПРОС

Подзапрос позволяет создавать сложные запросы, по сути выполняя запрос по результатам другого запроса.
Пример SYNTAX:

SELECT student,grade
FROM test_scores
WHERE grade > (
 SELECT AVG(grade)
 FROM test-scores
);

подзапрос запускается первым, потому что он заключен в круглые скобки и дает результат
вы можете использовать оператор IN вместе с подзапросом для проверки нескольких возвращенных результатов
это можно сделать с помощью JOIN, но иногда проще использовать подзапрос

SELECT student,grade
 FROM test_scores
 WHERE student IN (
  SELECT student
  FROM honor_roll_table
 );

оператор EXISTS используется для проверки наличия строк в подзапросе
внутри функции EXISTS () передается подзапрос, чтобы проверить, возвращаются ли какие-либо строки с подзапросом

# returns true or false wether rows were returned
SELECT col_name
 FROM table_name
 WHERE EXISTS(
  SELECT COL_NAME
  FROM table_name
  WHERE condition
);
# List the films that have a rental rate higher than the average rental rate for all films:
SELECT title, rental_rate
FROM film
WHERE rental_rate > (
 SELECT AVG(rental_rate)
 FROM FILM
);
# List film names returned on the 29th May 20005:
SELECT film_id,title
FROM film
WHERE film.film_id IN(
 SELECT inventory.film_id
 FROM rental
 INNER JOIN inventory
 ON rental.inventory_id = inventory.inventory_id
 WHERE rental.return_date BETWEEN ‘2005–05–29’ AND ‘2005–05–30’
)
ORDER BY title;
# List customers who have at least one payment greater than $11:
SELECT first_name,last_name
FROM customer AS c
WHERE EXISTS(
 SELECT *
 FROM payment AS p
 WHERE p.customer_id = c.customer_id
 AND amount > 11
);

САМОПРИСОЕДИНЕНИЕ

запрос, в котором таблица присоединяется к самой себе
полезно для сравнения значений в столбце строк в одной и той же таблице
можно рассматривать как объединение двух копий одной и той же таблицы
таблица не копируется, но SQL действует так, как если бы
Нет особого синтаксиса, кроме стандартного синтаксиса JOIN с одинаковой таблицей с обеих сторон
Необходимо использовать псевдонимы таблиц, чтобы не было двусмысленности

SELECT A.col,B.col
 FROM tableA AS A
 INNER JOIN tableA AS B
 ON A.some_col = B.other_col;
 
 
SELECT emp.name, report.name AS rep
FROM employees AS emp
INNER JOIN employees AS report
ON emp.emp_id = report.report_id;
# Find all the pairs of films that have the same length:
SELECT f1.title,f2.title,f1.length
FROM film AS f1
JOIN film AS f2
ON f1.film_id != f2.film_id // notice !=, otherwise will join row with itself
AND f1.length = f2.length

База данных может иметь несколько схем
таблицы, которые не входят в общедоступную схему, должны запрашиваться с использованием имени частной содержащей схемы:

SELECT * FROM private_schema.my_table

УПРАЖНЯТЬСЯ

# Produce a list of the total number of slots booked per facility in the month of September 2012. 
# Produce an output table consisting of facility id and slots, sorted by the number of slots.
SELECT B.facid,F.name,SUM(B.slots) AS booked_slots
 FROM cd.bookings AS B
 INNER JOIN cd.facilities AS F
 ON B.facid = F.facid
 WHERE B.starttime BETWEEN ‘2012–09–01’ AND ‘2012–10–1’
 GROUP BY B.facid,F.name
 ORDER BY SUM(B.slots);
# Produce a list of facilities with more than 1000 slots booked. 
# Produce an output table consisting of facility id and total slots, sorted by facility id.
SELECT facid,SUM(slots)
 FROM cd.bookings
 GROUP BY facid
 HAVING SUM(slots) > 1000
 ORDER BY facid;
# How can you produce a list of the start times for bookings for tennis courts, for the date ‘2012–09–21’? 
# Return a list of start time and facility name pairings, ordered by the time.
SELECT B.starttime, F.name
 FROM cd.bookings AS B
 INNER JOIN cd.facilities F
 ON B.facid = F.facid
 WHERE F.name LIKE ‘Tennis Court _’ /* or WHERE F.facid IN (0,1) */
 AND B.starttime BETWEEN ‘2012–09–21’ AND ‘2012–09–22’
 ORDER BY B.starttime;
# How can you produce a list of the start times for bookings by members named ‘David Farrell’?
SELECT B.starttime, (M.firstname || ‘ ‘ || M.surname) AS member_name
 FROM cd.bookings AS B
 INNER JOIN cd.members AS M
 ON B.memid = M.memid
 WHERE M.firstname = ‘David’ AND M.surname = ‘Farrell’;

СОЗДАНИЕ ДАННЫХ И ТАБЛИЦ

ТИПЫ ДАННЫХ

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

Boolean data types:
 True or False
 
Character data types:
 char, varchar and text
 
Numerice data types:
 integer and floating-point number
 
Temporal:
 date, time, timestamp and interval

другие виды:

UUID
 Universality Unique Identifiers
 
Array
 stores an array of strings, numbers, etc.
 
JSON
 
Hstore
 key-value pair

специальные типы, такие как:

network address
geometric data
etc.

в каждом из перечисленных выше типов данных есть еще более тонкие типы данных, прочтите документацию
при создании баз данных и таблиц необходимо тщательно продумать, какие типы данных следует использовать для хранения данных
прочтите документацию PG об ограничениях конкретных типов данных
https://www.postgresql.org /docs/9.5/datatype.html
поиск передовых методов о том, какие типы данных использовать для хранения определенных типов данных
например, сохранять телефонные номера в виде текстовых данных (varchar)
планируйте долгосрочное хранение
вы можете удалить исторические данные, но не добавлять данные, которые вы изначально не записывали.
запишите дополнительную информацию, которая необходима в начале

ПЕРВИЧНЫЕ И ИНОСТРАННЫЕ КЛЮЧИ

ПЕРВИЧНЫЙ КЛЮЧ - это столбец или группа столбцов, которые используются для уникальной идентификации строки в таблице.
первичные ключи позволяют нам легко определить, какие столбцы следует использовать для объединения таблиц.
столбцы первичного ключа должны быть уникальными в каждой строке и отличаться от нуля
первичным ключом может быть любой столбец, который соответствует этим стандартам
ИНОСТРАННЫЙ КЛЮЧ - это поле или группа полей в таблице, которые однозначно идентифицируют строку в другой таблице.
внешний ключ определяется в таблице, которая ссылается на первичный ключ другой таблицы
таблица, содержащая FK, называется СПРАВОЧНАЯ ТАБЛИЦА ИЛИ ДЕТСКАЯ ТАБЛИЦА.
таблица, на которую ссылается FK, называется СООТВЕТСТВУЮЩАЯ ТАБЛИЦА или РОДИТЕЛЬСКАЯ ТАБЛИЦА.
таблица может иметь несколько FK в зависимости от ее отношений с другими таблицами
PK и FK - хороший выбор столбцов для объединения таблиц
при создании таблиц и определении столбцов,
вы можете использовать CONSTRAINTS для определения столбцов как PK или присоединения отношения FK к другой таблице
чтобы получить информацию о PK и FK в PostgreSQL, в PgAdmin:

look into the details of the tables and columns in the database explorer:
Schemas / <NAME_SCHEMA> (public as default choice) / Tables / <TABLE_NAME> / Constraints
 
 the golden key symbol represents the primary key for that table (<TABLE_NAME>_pkey)
 the dual silver key symbol represents foreign keys (which are constraints on that table) (<TABLE_NAME>_<COL_NAME>_fkey)
 
 to get more details on keys: 
 — select the FK in explorer and click on dependencies tab in header
 — select FK, right click / properties / columns 
 
 
 the tables in the “public” Schema represent public relationships
 you can query these table directly, no need to do “FROM public.<TABLE_NAME>”
 
 if tables are not in the public Schema, you need to specify the schema name to access the tables
 “FROM <SCHEMA_NAME>.<TABLE_NAME>”

ОГРАНИЧЕНИЯ

ограничения - это правила, применяемые к столбцам данных в таблице
также могут использоваться для ограничения всего в таблице
используются для ПРЕДОТВРАЩЕНИЯ НЕДЕЙСТВИТЕЛЬНЫХ ДАННЫХ от ввода в БД
обеспечивает точность и надежность данных в БД
ограничения делятся на две основные категории:
ограничения столбца:
ограничивают данные в столбце, чтобы они соответствовали определенным условиям
ограничения таблицы :
применяется ко всей таблице, а не к отдельному столбцу
наиболее распространенные ОГРАНИЧЕНИЯ КОЛОНКИ:

NOT NULL constraint
 ensures that a column cannot have NULL value
 UNIQUE constraint
 ensures that all values in a column are different
 
PRIMARY Key
 uniquely identifies each row / record in a database table
 FOREIGN Key
 constrains data based on columns in other tables
 creates a relationship with another table
 
CHECK constraint
 ensures that all values in a column satisfy certain conditions
 
EXCLUSION constraint
 ensures that if any two rows are compared on the specified column or expression using the specified operator,not all of these comparisons will return TRUE

ОГРАНИЧЕНИЯ ТАБЛИЦЫ:

CHECK (condition)
 to check a condition when inserting or updating data
REFERENCES
 to constrain the value stored in the column that must exist in a column in another column
UNIQUE (column_list)
 forces the values stored in the columns listed inside the parentheses to be uniqque (within multiple columns)
PRIMARY KEY (column_list)
 allows you to define the primary key that consists of multiple columns

СОЗДАЙТЕ

Полный общий синтаксис:

CREATE TABLE <TABLE_NAME> (
 <COLUMN_NAME> <DATA_TYPE> <COLUMN_CONSTRAINT>,
 <COLUMN_NAME> <DATA_TYPE> <COLUMN_CONSTRAINT>,
 <TABLE_CONSTRAINT> <TABLE_CONSTRAINT>
 ) INHERITS <EXISTING_TABLE_NAME>;

ОБЩИЙ ПРОСТОЙ СИНТАКСИС:

CREATE TABLE <TABLE_NAME> (
 <COLUMN_NAME> <DATA_TYPE> <COLUMN_CONSTRAINT>,
 <COLUMN_NAME> <DATA_TYPE> <COLUMN_CONSTRAINT>,
 );
 
 
 CREATE TABLE players (
 player_id SERIAL PRIMARY KEY,
 AGE SMALLINT NOT NULL,
 );

SERIAL
- в PostgreSQL последовательность - это особый вид объекта базы данных, который генерирует последовательность целых чисел
- последовательность часто используется в качестве столбца первичного ключа в таблице
- она создает объект последовательности и установите следующее значение, сгенерированное последовательностью, в качестве значения по умолчанию для столбца
- это идеально подходит для первичного ключа, потому что он автоматически регистрирует уникальные целочисленные записи для вас при вставке, когда вы вставляете больше данных в этот table, вам не нужно будет указывать уникальное значение для col_id самостоятельно
- если строка позже будет перемещена, столбец с типом данных SERIAL НЕ будет настраиваться, что указывает на то, что строка была удалена из последовательности ( 1,2,4,6,7,8 - идентификаторы 3 и 5 удалены)
вы можете запустить запрос CREATE TABLE только один раз

CREATE TABLE account (
 user_id SERIAL PRIMARY KEY,
 username VARCHAR(50) UNIQUE NOT NULL,
 password VARCHAR(50) NOT NULL,
 email VARCHAR(250) UNIQUE NOT NULL,
 created_on TIMESTAMP NOT NULL,
 last_login TIMESTAMP
);
CREATE TABLE job (
 job_id SERIAL PRIMARY KEY,
 job_name VARCHAR(200) UNIQUE NOT NULL
);

при ссылке на внешние ключи в таблице используйте тип данных INTEGER вместо SERIAL

CREATE TABLE account_job (
 user_id INTEGER REFERENCES account(user_id),
 job_id INTEGER REFERENCES job(job_id),
 hired_date TIMESTAMP 
);

ВСТАВЛЯТЬ

позволяет добавлять строки в таблицу
общий синтаксис:

INSERT INTO <TABLE_NAME> (<COL_1>,<COL_2>,…)
 VALUES
 (<VALUR_1>, <VALUR_2>,…),
 (<VALUR_1>, <VALUR_2>,…),
 … ;

СИНТАКСИС для вставки значений из другой таблицы

INSERT INTO <TABLE_NAME> (<COL_1>,<COL_2>,…)
 SELECT <COL_1>,<COL_2>,…
 FROM <OHTER_TABLE>
 WHERE <CONDITION>;

вставленные значения строк должны совпадать с таблицей, включая ограничения
Столбцы SERIAL не нуждаются в значении.

INSERT INTO account (username,password,email, created_on)
VALUES
 (‘toto’, ‘passwordtoto’, ‘[email protected]’, CURRENT_TIMESTAMP),
 (‘titi’, ‘passwordtiti’, ‘[email protected]’, CURRENT_TIMESTAMP),
 (‘tutu’, ‘passwordtutu’, ‘[email protected]’, CURRENT_TIMESTAMP);

при вставке данных в таблицу с помощью FK вы должны убедиться, что значения существуют в ссылочных таблицах
вы не можете добавить user_id FK для пользователя, которого нет в таблице учетных записей, потому что он нарушает ограничение

ОБНОВИТЬ

ключевое слово UPDATE позволяет изменять значения столбцов в таблице
общий СИНТАКСИС

UPDATE <TABLE_NAME>
 SET <COL_1>=<VALUE_X>,
 <COL_2>=<VALUE_Y>,….
 WHERE <CONDITION>;
 
 
 UPDATE account
 SET last_login = CURRENT_TIMESTAMP
 WHERE last_login IS NULL;

чтобы сбросить все без условия WHERE, просто опустите его
установить на основе другого столбца:

UPDATE account
 SET last_login=created_on

с использованием значений другой таблицы (также известного как UPDATE join):

UPDATE tableA
 SET original_col = tableB.new_col
 FROM tableB
 WHERE tableA.id = tableB.id;

вернуть затронутые строки:

UPDATE account
 SET last_login = created_on
 RETURNING account_id,last_login;
 
 
 UPDATE account
 SET last_login = created_on
 RETURNING email,created_on,last_login; 
 
 UPDATE account_job
 SET hired_date = account.created_on
 FROM account
 WHERE account.user_id = account_job.user_id;

УДАЛИТЬ

используйте предложение DELETE для удаления строк из таблицы
СИНТАКСИС:

DELETE FROM <TABLE_NAME>
 WHERE row_id = 1;

вы можете удалять строки в зависимости от их присутствия в других таблицах (также известное как соединение DELETE)

DELETE FROM tableA
 USING tableB
 WHERE tableA.id = tableB.id;
# if there is a match, row will be deleted from tableA

вы можете удалить все строки из таблицы:

DELETE FROM <TABLE_NAME>;

вы можете добавить вызов RETURNING для возврата удаленных строк:

DELETE FROM <TABLE_NAME>
 RETURNING <COL_NAME>;
 
 
 DELETE FROM job
 WHERE job_name = ‘jobidon’
 RETURNING job_id,job_name;

ИЗМЕНИТЬ

предложение ALTER позволяет вносить изменения в существующую структуру таблицы, например
- добавление, извлечение или переименование столбцов
- изменение типа данных столбца
- установка значений DEFAULT для столбца
- добавить ограничения CHECK
- переименовать таблицу
общий СИНТАКСИС:

ALTER TABLE <TABLE_NAME> <ACTION>;

добавление столбцов:

ALTER TABLE <TABLE_NAME>
 ADD COLUMN <COL_NAME> <TYPE>;
ALTER TABLE job
 ADD COLUMN totocol INTEGER;

удаление столбцов:

ALTER TABLE <TABLE_NAME>
 DROP COLUMN <COL_NAME>;

изменить ограничения существующего столбца:

ALTER TABLE <TABLE_NAME>
 ALTER COLUMN <COL_NAME>
 SET DEFAULT <VALUE>;
.. DROP DEFAULT;
 .. SET NOT NULL; 
 .. DROP NOT NULL; 
 .. ADD CONSTRAINT <CONSTRAINT_NAME>;
 etc…

переименование таблицы:

ALTER TABLE information
 RENAME TO new_info;

переименование столбца:

ALTER TABLE new_info
 RENAME COLUMN person TO people; 
 
 ALTER TABLE new_info
 ALTER COLUMN people DROP NOT NULL;
 
 INSERT INTO new_info(title)
 VALUES (‘mytile’)
 RETURNING *;

УРОНИТЬ

DROP позволяет полностью удалить столбец в таблице
в PostgreSQL это также автоматически удалит все его индексы и ограничения, связанные с столбцом
он не будет удалять столбцы, используемые в представлениях, триггерах или хранимых процедурах, без дополнительного предложения CASCADE
общий СИНТАКСИС:

ALTER TABLE <TABLE_NAME>
 DROP COLUMN <COL_NAME>;
# remove all dependencies
 
 ALTER TABLE <TABLE_NAME>
 DROP COLUMN <COL_NAME> CASCADE;
# check for existence to avoid error
ALTER TABLE <TABLE_NAME>
 DROP COLUMN IF EXISTS <COL_NAME>;
# drop multiple columns
ALTER TABLE <TABLE_NAME>
 DROP COLUMN <COL_1>,
 DROP COLUMN <COL_2>,
 DROP COLUMN <COL_3>;
 
 
ALTER TABLE new_info
DROP COLUMN IF EXISTS people
# will not return an error if the column does not exist, just a notice that the column does not exists

ЧЕК

позволяет создавать более индивидуальные ограничения, которые соответствуют определенному условию
например, убедитесь, что все вставленные целочисленные значения не достигают определенного порогового значения.
общий синтаксис

CREATE TABLE <TABLE_NAME>(
 tab_id SERIAL PRIMARY KEY,
 age SMALLINT CHECK(age > 21),
 parent_age SMALLINT CHECK(parent_age > age)
);

если ограничения нарушены, он вернет ошибку

CREATE TABLE employees (
 emp_id SERIAL PRIMARY KEY,
 first_name VARCHAR(50) NOT NULL,
 last_name VARCHAR(50) NOT NULL,
 birthdate DATE CHECK(birthdate > ‘1900–01–01’),
 hire_date DATE CHECK(hire_date > birthdate),
 salary INTEGER CHECK(salary > 0)
);
 
 
 INSERT INTO employees(
  first_name, 
  last_name, 
  birthdate, 
  hire_date, 
  salary
 )
 VALUES
 (‘toto’, ‘titi’, ‘1976–08–01’, ‘2001–10–01’, 450000);

Условные выражения и процедуры

добавить логику к командам и рабочим процессам в SQL

КЕЙС

used tp выполнять код SQL только при соблюдении определенных условий
аналогично ifelse в другом языке программирования
два основных способа использования оператора CASE:
- общий CASE
- выражение CASE
оба метода могут привести к одинаковым результатам
СИНТАКСИС для общего оператора CASE:

CASE
 WHEN <CONDITION_1> THEN <RESULT_1>
 WHEN <CONDITION_2> THEN <RESULT_2>
 ELSE <SOME_OTHER_RESULT>
 END
 
 
 SELECT col_1
 CASE WHEN col1 = 1 THEN ‘one’
 WHEN col1 = 2 THEN ‘two’
 ELSE ‘other’ AS <LABEL>
 END
 FROM table_1;

по умолчанию будет возвращать результаты в виде другого столбца с именем «case», поэтому используйте псевдоним
Синтаксис выражения CASE:
- сначала оценивает выражение
- затем последовательно сравнивает результат с каждым значением в предложениях WHEN
СИНТАКСИС выражения CASE:

CASE <EXPRESSION>
 WHEN <VALUE_1> THEN <RESULT_1>
 WHEN <VALUE_2> THEN <RESULT_2>
 ELSE <SOME-OTHER_RESULT>
 END
 
 
 SELECT col_1
 CASE col_1 
 WHEN 1 THEN ‘one’
 WHEN 2 THEN ‘two’
 ELSE ‘other’ AS <LABEL>
 END
 FROM table_1;

- подводить итоги:

- Общий синтаксис CASE:
- более гибкие условия (равенство, сравнение, членство в диапазоне и т. Д.)
- можно проверять различные условия в каждом предложении WHEN

Выражение CASE:
- проверено одно выражение (вызов столбца)
- только условие равенства

SELECT customer_id,
 CASE
 WHEN (customer_id <= 100)THEN ‘Premium’
 WHEN (customer_id BETWEEN 100 AND 200) THEN ‘Plus’
 ELSE ‘Normal’
 END AS customer_class
 FROM customer;
 
 
 SELECT customer_id,
 CASE customer_id
 WHEN 2 THEN ‘Winner’
 WHEN 5 THEN ‘Second Place’
 ELSE ‘Normal’
 END AS raffle_results
 FROM customer;

- вы можете использовать функции агрегирования результатов оператора CASE:

SELECT 
 SUM(
  CASE rental_rate
  WHEN 0.99 THEN 1
  ELSE 0
  END
 ) AS bargain,
 SUM(
  CASE rental_rate
  WHEN 2.99 THEN 1
  ELSE 0
  END
 ) AS regular,
 SUM(
  CASE rental_rate
  WHEN 4.99 THEN 1
  ELSE 0
  END
 ) AS premium
 FROM film;
 
# would be more difficult to do this with GROUP BY and HAVING, and would be less flexible in column display
# Compare number of movies per ratings:
SELECT
 SUM(
  CASE rating
  WHEN ‘R’ THEN 1
  ELSE 0
  END
 ) AS r,
 SUM(
  CASE rating
  WHEN ‘PG’ THEN 1
  ELSE 0
  END
 ) AS pg,
 SUM(
  CASE rating
  WHEN ‘PG-13’ THEN 1
  ELSE 0
  END
 ) AS pg13
 FROM film;

КОАЛЕС

Функция COALESCE принимает неограниченное количество аргументов
возвращает первый аргумент, который не является нулевым
если все аргументы равны нулю, функция COALESCE вернет ноль
предоставленные аргументы обычно являются именами столбцов

- СИНТАКСИС:

COALESCE( <ARG_1> , <ARG_2> , <ARG_3> , … , <ARG_N> )
SELECT COALESCE(1,2);
# returns 1
SELeCT COALESCE(NULL, 2, 3)
# returns 3

Функция COALESCE полезна:

- при запросе таблицы, содержащей нулевые значения
- подстановка нулевых значений другим значением

обычно используется при выполнении математических операций со столбцами, содержащими нулевые значения, чтобы заменить их на 0 без изменения столбцов

SELECT item, 
 (price — COALESCE(discount, 0)) AS final
 FROM table_1;
 
# when discount value is null, it will be replaced by 0

В РОЛЯХ

Оператор CAST, используемый для преобразования одного типа данных в другой
не каждый экземпляр типа данных может быть CAST для другого типа данных, это должно быть разумно
СИНТАКСИС для функции CAST (стандартный SQL):

SELECT CAST(‘5’ AS INTEGER)

Сокращенный оператор PostgreSQL CAST (::)

SELECT ‘5’::INTEGER

вы обычно используете его для нескольких экземпляров типа данных (столбца):

SELECT CAST(date_col AS TIMESTAMP)
 FROM table_1;

посчитать количество цифр в номере:

# PostgreSQL specific
 
SELECT 
 inventory_id,
 char_length(inventory_id::VARCHAR) 
FROM rental;
 
# standard SQL
SELECT 
 inventory_id,
 char_length( CAST(inventory_id AS VARCHAR) )
FROM rental

NULLIF

Функция NULLIF принимает 2 входа и возвращает NULL, если оба равны, в противном случае возвращает первый переданный аргумент.

NULLIF(<ARG_1>, <ARG_2>)
 
NULLIF(10, 10)
# returns NULL
NULLIF(10, 99)
# returns 10

очень полезно в тех случаях, когда значение NULL может вызвать ошибку или нежелательный результат
полезно, чтобы избежать ошибки деления на ноль (деление на ноль вернет ноль)

SELECT (
 SUM(CASE department WHEN ‘A’ THEN 1 ELSE 0 END) /
 NULLIF( 
   SUM(
     CASE department WHEN ‘B’ THEN 1 ELSE 0 END
   ),
   0
 )
) AS department_ratio
FROM depts;

ВЗГЛЯДЫ

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

вместо того, чтобы выполнять один и тот же запрос снова и снова в качестве отправной точки, создайте ВИД, чтобы быстро увидеть этот запрос с помощью простого вызова

SELECT * FROM <VIEW>

представление - это объект базы данных, относящийся к сохраненному запросу
представление может быть доступно как **** ВИРТУАЛЬНАЯ ТАБЛИЦА **** в postgresql
представление НЕ хранит данные физически, оно хранит ТОЛЬКО запрос
вы можете обновлять и изменять существующие представления
создать представление:

CREATE VIEW <VIEW_NAME> AS <QUERY_TO_BE_STORED>;
CREATE VIEW customer_info AS
 SELECT c.first_name, c.last_name, a.address
 FROM customer AS c
 INNER JOIN address AS a
 ON c.address_id = a.address_id;

запрос представления

SELECT * FROM customer_info;

обновить представление

CREATE OR REPLACE VIEW <VIEW_NAME> AS <QUERY_TO_BE_STORED>;
CREATE OR REPLACE VIEW customer_info AS
 SELECT c.first_name, c.last_name, a.address, a.district
 FROM customer AS c
 INNER JOIN address AS a
 ON c.address_id = a.address_id;

безопасно удалить представление

DROP VIEW IF EXISTS <VIEW_NAME>;
 
 DROP VIEW IF EXISTS customer_info;

изменить название просмотра

ALTER VIEW <VIEW_NAME> RENAME TO <NEW_VIEW_NAME>;
ALTER VIEW customer_info RENAME TO c_info;

ИМПОРТ И ЭКСПОРТ

с помощью pgadmin вы можете импортировать данные из файла .csv в существующую таблицу

Примечание:

не все внешние файлы данных работают
- варианты форматирования, макросы, типы данных, tec. может помешать команде импорта прочитать файл
- вам нужно будет отредактировать файл, чтобы сделать его совместимым с SQL, или изменить таблицу в соответствии с файлом csv
- в противном случае вы должны указать правильный путь к файлу pgadmin завершится ошибкой

- команда Import НЕ создает таблицу для вас
- предполагается, что таблица уже существует
- нет стандартного автоматического способа создания таблицы в pgadmin или postgresql непосредственно из файла csv (пока)
Конечно, есть альтернативные решения для создания таблицы на основе заголовков csv
https://stackoverflow.com/questions/21018256/can-i-automatically-create -a-table-in-postgresql-from-a-csv-file-with-headers
https://www.enterprisedb.com/postgres-tutorials/how-import-and-export-data- using-csv-files-postgresql
https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table
< br /> https://www.postgresql.org/docs/9.2/sql-copy.html
переименовать таблицу:

ALTER TABLE <CURRENT_NAME> RENAME TO <NEW_NAME>;

выберите существующую таблицу в pgadmin explorer / нажмите правую кнопку мыши / «импорт / экспорт»
/ нажмите кнопку переключения экспорта, чтобы отобразить импорт
/ выбрать файл на вашем хосте
/ в таблице столбцов, вы можете выбрать столбцы для импорта
/ если файл csv имеет заголовки, нажмите кнопку заголовков, чтобы не дублировать их как значения
/ выберите разделитель
/ отправьте форму
для экспорта, аналогичный процесс, выберите таблицу / щелкните правой кнопкой мыши / «импорт / экспорт»
укажите файл для экспорта
установите желаемый разделитель и другие параметры

Вот и все

Теперь вы знаете достаточно, чтобы быть опасным с SQL, и понимаете запросы, выполняемые ORM за кулисами. Есть еще много чего узнать, так что продолжайте!

Чтобы увидеть больше моих историй:

Florian GOTO - Medium
Вы когда-нибудь хотели заблокировать версию Node.js, которая использовалась для запуска вашего проекта? Всегда хотел автоматически переключиться на… medium.com

SQL с PostgreSQL для инженеров Node.js