Конспект: SQL / основы / postgre / на примере PostgreSQL

Ссылки

https://b14esh.com/www/sql/postgresql.html

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads - PostgreSQL Database  для Windows
https://www.asozykin.ru/posts/demo_database_sql_foundation - инструкция по установке PostgreSQL для Windows
https://www.dropbox.com/s/311jgpz9d3p1966/sql_foundation.sql?dl=1 - демонстрационная база (загружаем сюда databases\postgres\shema\public\tables)
https://github.com/fivethirtyeight/data - таблицы поиграть
https://postgrespro.ru/docs/postgresql/9.6/app-pgrestore - документация

Видео материалы Andrey Sozykin: 
https://www.youtube.com/watch?v=uGKIXTUjZbc&list=PLtPJ9lKvJ4oh5SdmGVusIVDPcELrJ2bsT&index=1&ab_channel=AndreySozykin - Full Playlist |  Основы SQL
https://www.youtube.com/watch?v=uGKIXTUjZbc&ab_channel=AndreySozykin - Базы данных и SQL | Основы SQL
https://www.youtube.com/watch?v=0Jw3pnF0huk&ab_channel=AndreySozykin - Оператор SELECT | Основы SQL
https://www.youtube.com/watch?v=Q8UmK7wC9Hk&ab_channel=AndreySozykin - Фильтрация данных в SQL | WHERE | Основы SQL
https://www.youtube.com/watch?v=bYdjR6QexJY&ab_channel=AndreySozykin - Сортировка в SQL: ORDER BY | Основы SQL
https://www.youtube.com/watch?v=7nD1e4m9Wgg&ab_channel=AndreySozykin - Создание таблиц в SQL | Основы SQL
https://www.youtube.com/watch?v=eyWGkfBYmIY&ab_channel=AndreySozykin - Вставка и изменение данных в SQL | Основы SQL
https://www.youtube.com/watch?v=YCreL-HOg98&ab_channel=AndreySozykin - Группировка в SQL | Архив
https://www.youtube.com/watch?v=_IoSA74hEtw&ab_channel=AndreySozykin - Группировка в SQL | Основы SQL
https://www.youtube.com/watch?v=q0nuhf7vzkE&ab_channel=AndreySozykin - Агрегатные функции | Основы SQL
https://www.youtube.com/watch?v=ytfXUvCsNuo&ab_channel=AndreySozykin - Группировки и фильтрация в SQL: HAVING | Основы SQL
https://www.youtube.com/watch?v=fDlK96jKH1k&ab_channel=AndreySozykin - Декомпозиция данных в базе | Основы SQL

https://b14esh.com/www/sql/postgresql.html

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads - PostgreSQL Database для Windows

https://www.asozykin.ru/posts/demo_database_sql_foundation - инструкция по установке PostgreSQL для Windows

https://www.dropbox.com/s/311jgpz9d3p1966/sql_foundation.sql?dl=1 - демонстрационная база (загружаем сюда databases\postgres\shema\public\tables)

https://github.com/fivethirtyeight/data - таблицы поиграть

https://postgrespro.ru/docs/postgresql/9.6/app-pgrestore - документация

Видео материалы Andrey Sozykin:

https://www.youtube.com/watch?v=uGKIXTUjZbc&list=PLtPJ9lKvJ4oh5SdmGVusIVDPcELrJ2bsT&index=1&ab_channel=AndreySozykin - Full Playlist | Основы SQL

https://www.youtube.com/watch?v=uGKIXTUjZbc&ab_channel=AndreySozykin - Базы данных и SQL | Основы SQL

https://www.youtube.com/watch?v=0Jw3pnF0huk&ab_channel=AndreySozykin - Оператор SELECT | Основы SQL

https://www.youtube.com/watch?v=Q8UmK7wC9Hk&ab_channel=AndreySozykin - Фильтрация данных в SQL | WHERE | Основы SQL

https://www.youtube.com/watch?v=bYdjR6QexJY&ab_channel=AndreySozykin - Сортировка в SQL: ORDER BY | Основы SQL

https://www.youtube.com/watch?v=7nD1e4m9Wgg&ab_channel=AndreySozykin - Создание таблиц в SQL | Основы SQL

https://www.youtube.com/watch?v=eyWGkfBYmIY&ab_channel=AndreySozykin - Вставка и изменение данных в SQL | Основы SQL

https://www.youtube.com/watch?v=YCreL-HOg98&ab_channel=AndreySozykin - Группировка в SQL | Архив

https://www.youtube.com/watch?v=_IoSA74hEtw&ab_channel=AndreySozykin - Группировка в SQL | Основы SQL

https://www.youtube.com/watch?v=q0nuhf7vzkE&ab_channel=AndreySozykin - Агрегатные функции | Основы SQL

https://www.youtube.com/watch?v=ytfXUvCsNuo&ab_channel=AndreySozykin - Группировки и фильтрация в SQL: HAVING | Основы SQL

https://www.youtube.com/watch?v=fDlK96jKH1k&ab_channel=AndreySozykin - Декомпозиция данных в базе | Основы SQL

Основное psql:

psql -l - список баз данных
psql -d dbname - подключение к БД dbname

\? - помощь по сокращённым командам 
\l - список баз данных
\l+ - просмотр существующих баз данных c более детальным выводом(размер, описание баз данных)
\c dbname - подсоединение к БД dbname
\d table - структура таблицы table
\du - список всех пользователей и их привилегий
\dt - список всех таблиц
\dt+ - список всех таблиц с описанием

psql -l - список баз данных

psql -d dbname - подключение к БД dbname

\? - помощь по сокращённым командам

\l - список баз данных

\l+ - просмотр существующих баз данных c более детальным выводом(размер, описание баз данных)

\c dbname - подсоединение к БД dbname

\d table - структура таблицы table

\du - список всех пользователей и их привилегий

\dt - список всех таблиц

\dt+ - список всех таблиц с описанием

Оператор SELECT \ выборка

#работаем с демо-базой
#SELECT - извлечь, выбрать
#* - все символы

SELECT что FROM откуда;
SELECT * FROM superheroes; - вывести все столбцы из таблицы superheroes

SELECT name,universe,appearances FROM superheroes; - вывести информацию из таблицы superheroes  столбцы name,universe,appearances

SELECT name AS hero_name, appearances FROM superheroes; - вывести информацию из таблицы superheroes столбцы name,appearances, таблицу name назвать как hero_name
SELECT name hero_name, appearances FROM superheroes; - вывести информацию из таблицы superheroes столбцы name,appearances, таблицу name назвать как hero_name

SELECT DISTINCT(align) FROM superheroes; - вывести уникальные значения для столбца align
SELECT DISTINCT(gender) FROM superheroes; - вывести уникальные значения для столбца gender
SELECT DISTINCT(eye) FROM superheroes; - вывести уникальные значения для столбца eye

SELECT DISTINCT(hair) FROM superheroes LIMIT 10; - вывести уникальные значения для столбца hair, ограничить десятью строками

#работаем с демо-базой

#SELECT - извлечь, выбрать

#* - все символы

SELECT что FROM откуда;

SELECT * FROM superheroes; - вывести все столбцы из таблицы superheroes

SELECT name,universe,appearances FROM superheroes; - вывести информацию из таблицы superheroes столбцы name,universe,appearances

SELECT name AS hero_name, appearances FROM superheroes; - вывести информацию из таблицы superheroes столбцы name,appearances, таблицу name назвать как hero_name

SELECT name hero_name, appearances FROM superheroes; - вывести информацию из таблицы superheroes столбцы name,appearances, таблицу name назвать как hero_name

SELECT DISTINCT(align) FROM superheroes; - вывести уникальные значения для столбца align

SELECT DISTINCT(gender) FROM superheroes; - вывести уникальные значения для столбца gender

SELECT DISTINCT(eye) FROM superheroes; - вывести уникальные значения для столбца eye

SELECT DISTINCT(hair) FROM superheroes LIMIT 10; - вывести уникальные значения для столбца hair, ограничить десятью строками

WHERE \ Операторы сравнения в WHERE \ Логические операторы в WHERE \Фильтрация данных в SQL

Операторы сравнения в WHERE:
= Равно
<> Не равно
!= Не равно
> Больше
>= Больше или равно
< Меньше
<= Меньше лили равно
between Значение находится в указанном диапазоне ( от AND до)
in Значение входит в список
like Проверка строки на соответствие шаблону (% - любое количество символов(включая 0), _ - ровно один символ) 

Логические операторы в  WHERE:
AND Логические И
OR Логические ИЛИ
NOT Логическое НЕ

Операторы сравнения в WHERE:

= Равно

<> Не равно

!= Не равно

> Больше

>= Больше или равно

< Меньше

<= Меньше лили равно

between Значение находится в указанном диапазоне ( от AND до)

in Значение входит в список

like Проверка строки на соответствие шаблону (% - любое количество символов(включая 0), _ - ровно один символ)

Логические операторы в WHERE:

AND Логические И

OR Логические ИЛИ

NOT Логическое НЕ

WHERE \ Фильтрация данных в SQL \ примеры

43
SELECT * FROM superheroes WHERE gender = 'Female Characters';
SELECT * FROM superheroes WHERE align = 'Reformed Criminals';

SELECT * From superheroes WHERE year BETWEEN 2000 AND 2005;

SELECT * FROM superheroes WHERE hair IN ('Strawberry Blond Hair', 'Red Hair', 'Auburn Hair');

SELECT * FROM superheroes WHERE hair LIKE '%Blond%';
SELECT * FROM superheroes WHERE name LIKE '%Spider-Man%';
SELECT * FROM superheroes WHERE name LIKE '%Earth-616%';

SELECT * FROM superheroes WHERE gender = 'Female Characters' AND align = 'Bad Characters';

SELECT * FROM superheroes WHERE hair = 'Red Hair' OR hair = 'Strawberry Blond Hair' OR hair = 'Auburn Hair';

SELECT * FROM superheroes WHERE hair NOT IN ('Blond Hair', 'Black Hair', 'Brown Hair', 'Red Hair');

SELECT * FROM superheroes WHERE gender = 'Female Characters';

SELECT * FROM superheroes WHERE align = 'Reformed Criminals';

SELECT * From superheroes WHERE year BETWEEN 2000 AND 2005;

SELECT * FROM superheroes WHERE hair IN ('Strawberry Blond Hair', 'Red Hair', 'Auburn Hair');

SELECT * FROM superheroes WHERE hair LIKE '%Blond%';

SELECT * FROM superheroes WHERE name LIKE '%Spider-Man%';

SELECT * FROM superheroes WHERE name LIKE '%Earth-616%';

SELECT * FROM superheroes WHERE gender = 'Female Characters' AND align = 'Bad Characters';

SELECT * FROM superheroes WHERE hair = 'Red Hair' OR hair = 'Strawberry Blond Hair' OR hair = 'Auburn Hair';

SELECT * FROM superheroes WHERE hair NOT IN ('Blond Hair', 'Black Hair', 'Brown Hair', 'Red Hair');

ORDER BY \ сортировка

!!! Иногда при запросах SELECT данные могут выводится в разном порядке.
!!! ORDER BY - сортировка вывода.
!!! Порядок сортировки:
!!! ASC - сортировка по возрастанию ( применяется по умолчанию)
!!! DESC - сортировка по убыванию
 
SELECT * FROM superheroes ORDER BY year;
SELECT * FROM superheroes ORDER BY appearances;

SELECT * FROM superheroes ORDER BY id;
SELECT * FROM superheroes WHERE name LIKE '%Thor%';

SELECT * FROM superheroes ORDER BY appearances DESC;
SELECT * FROM superheroes ORDER BY year DESC limit 10;
SELECT * FROM superheroes ORDER BY appearances DESC limit 5;

SELECT * FROM superheroes WHERE align = 'Bad Characters' ORDER BY appearances DESC;
SELECT * FROM superheroes WHERE align = 'Bad Characters' AND gender = 'Female Characters' ORDER BY appearances DESC LIMIT 5;

SELECT * FROM superheroes ORDER BY year, appearances LIMIT 10;

SELECT * FROM superheroes ORDER BY year, appearances DESC LIMIT 10;

!!! Иногда при запросах SELECT данные могут выводится в разном порядке.

!!! ORDER BY - сортировка вывода.

!!! Порядок сортировки:

!!! ASC - сортировка по возрастанию ( применяется по умолчанию)

!!! DESC - сортировка по убыванию

SELECT * FROM superheroes ORDER BY year;

SELECT * FROM superheroes ORDER BY appearances;

SELECT * FROM superheroes ORDER BY id;

SELECT * FROM superheroes WHERE name LIKE '%Thor%';

SELECT * FROM superheroes ORDER BY appearances DESC;

SELECT * FROM superheroes ORDER BY year DESC limit 10;

SELECT * FROM superheroes ORDER BY appearances DESC limit 5;

SELECT * FROM superheroes WHERE align = 'Bad Characters' ORDER BY appearances DESC;

SELECT * FROM superheroes WHERE align = 'Bad Characters' AND gender = 'Female Characters' ORDER BY appearances DESC LIMIT 5;

SELECT * FROM superheroes ORDER BY year, appearances LIMIT 10;

SELECT * FROM superheroes ORDER BY year, appearances DESC LIMIT 10;

Создание \ удаление \ изменение таблиц в SQL

!!! CREATE TABLE - создать таблицу
!!! INT - целое число 
!!! CHAR(n) - строка фиксированной длины, n - число символов
!!! VARCHAR(n) - строка переменной длины, n - число символов
!!! BOOLEAN - логический тип данных (True, Fulse)
!!! INT - Целое число
!!! DATE - дата
!!! TIMESTAMP - дата и время
!!! NUMERIC(p,s) - действительное число(p - количество значащих цифр, s- количество цифр после запятой) Хранится точно.
!!! REAL Действительное число одинарной точности, формат IEEE 754
!!! DOUBLE PRECESIOM - действительное число двойной точности, формат IEEE 754
 
!!! PRIMARY KEY - нужен для того чтобы отличать записи друг от друга
!!! SERIAL PRIMARY KEY - параметр SERIAL облегчает создание PRIMARY KEY, идентификатор будет создаваться автоматически
!!! SERIAL - является стандартным только для postgreSQL, например для MySQL "AUTO_INCREMENT"

CREATE TABLES superheroes( id INT SERIAL PRIMARY KEY, 
                           name VARCHAR(100), 
                           align VARCHAR(30), 
                           eye VARCHAR(30), 
                           hair VARCHAR(30), 
                           gender VARCHAR(30),
                           appearances INT, 
                           year INT, 
                           universe VARCHAR(10) 
                         );

# \d superheroes; - так в командной строке postgresql, командой "d" имя таблицы "superheroes", мы можем посмотреть информацию о таблице.

# describe superheroes;  - так в командной строке mysql, командой "describe" имя таблицы "superheroes", мы можем посмотреть информацию о таблице.

!!! DROP TABLE - удалить таблицу
DROP TABLE superheroes; - удалить таблицу "superheroes"


!!! Скрипт создания таблицы superheroes, IF EXITS если таблица существует то она буде очищена
-- Создаем таблицу
DROP TABLE IF EXISTS superheroes; 
CREATE TABLE superheroes(id SERIAL PRIMARY KEY, name VARCHAR(100), align VARCHAR(30), eye VARCHAR(30), hair VARCHAR(30), gender VARCHAR(30), appearances INT, year INT, universe VARCHAR(10));

!!! ALTER TABLE - изменение таблицы
ALTER TABLE superheroes ADD COLUMN alive BOOLEAN; - создать столбец "alive"
ALTER TABLE superheroes ADD COLUMN first_appearance TIMESTAMP; - создать столбец "first_appearance"
ALTER TABLE superheroes DROP COLUMN year; - удалить столбец "year"
ALTER TABLE superheroes RENAME COLUMN name TO hero_name; - переименовать столбец "name" в "hero_name"
ALTER TABLE superheroes RENAME TO comic_characters; - переименовать таблицу "superheroes" в "comic_characters"

!!! CREATE TABLE - создать таблицу

!!! INT - целое число

!!! CHAR(n) - строка фиксированной длины, n - число символов

!!! VARCHAR(n) - строка переменной длины, n - число символов

!!! BOOLEAN - логический тип данных (True, Fulse)

!!! INT - Целое число

!!! DATE - дата

!!! TIMESTAMP - дата и время

!!! NUMERIC(p,s) - действительное число(p - количество значащих цифр, s- количество цифр после запятой) Хранится точно.

!!! REAL Действительное число одинарной точности, формат IEEE 754

!!! DOUBLE PRECESIOM - действительное число двойной точности, формат IEEE 754

!!! PRIMARY KEY - нужен для того чтобы отличать записи друг от друга

!!! SERIAL PRIMARY KEY - параметр SERIAL облегчает создание PRIMARY KEY, идентификатор будет создаваться автоматически

!!! SERIAL - является стандартным только для postgreSQL, например для MySQL "AUTO_INCREMENT"

CREATE TABLES superheroes( id INT SERIAL PRIMARY KEY,

name VARCHAR(100),

align VARCHAR(30),

eye VARCHAR(30),

hair VARCHAR(30),

gender VARCHAR(30),

appearances INT,

year INT,

universe VARCHAR(10)

);

# \d superheroes; - так в командной строке postgresql, командой "d" имя таблицы "superheroes", мы можем посмотреть информацию о таблице.

# describe superheroes; - так в командной строке mysql, командой "describe" имя таблицы "superheroes", мы можем посмотреть информацию о таблице.

!!! DROP TABLE - удалить таблицу

DROP TABLE superheroes; - удалить таблицу "superheroes"

!!! Скрипт создания таблицы superheroes, IF EXITS если таблица существует то она буде очищена

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

DROP TABLE IF EXISTS superheroes;

CREATE TABLE superheroes(id SERIAL PRIMARY KEY, name VARCHAR(100), align VARCHAR(30), eye VARCHAR(30), hair VARCHAR(30), gender VARCHAR(30), appearances INT, year INT, universe VARCHAR(10));

!!! ALTER TABLE - изменение таблицы

ALTER TABLE superheroes ADD COLUMN alive BOOLEAN; - создать столбец "alive"

ALTER TABLE superheroes ADD COLUMN first_appearance TIMESTAMP; - создать столбец "first_appearance"

ALTER TABLE superheroes DROP COLUMN year; - удалить столбец "year"

ALTER TABLE superheroes RENAME COLUMN name TO hero_name; - переименовать столбец "name" в "hero_name"

ALTER TABLE superheroes RENAME TO comic_characters; - переименовать таблицу "superheroes" в "comic_characters"

Языки SQL

DDL = Data Definition Language - язык описания данных (CREATE TABLE, DROP TABLE, ALTER TABLE)
DML = Data Manipulation Language - язык манипулирования данными (SELECT, INSERT, UPDATE)
DCL = Data Control Language - язык управления доступом к данным

DDL = Data Definition Language - язык описания данных (CREATE TABLE, DROP TABLE, ALTER TABLE)

DML = Data Manipulation Language - язык манипулирования данными (SELECT, INSERT, UPDATE)

DCL = Data Control Language - язык управления доступом к данным

Вставка данных в таблицу \ оператор INSERT

!!! создаем пустую таблицу
DROP TABLE IF EXISTS superheroes; 
CREATE TABLE superheroes(id SERIAL PRIMARY KEY, name VARCHAR(100), align VARCHAR(30), eye VARCHAR(30), hair VARCHAR(30), gender VARCHAR(30), appearances INT, year INT, universe VARCHAR(10));

INSERT INTO superheroes(name, appearances, universe) VALUES ('Spider-Man', 4043, 'marvel');
SELECT * FROM superheroes WHERE appearances = 4043 
SELECT * FROM superheroes WHERE appearances = 4043 AND name NOT IN ('Spider-Man (Peter Parker)');

INSERT INTO superheroes(name, align, eye, hair, gender, appearances, year, universe) VALUES('ZABILO', 'VEry BAD', 'ZOR GLASs', 'Black Hair', 'SPIDER', 4043, 1962, 'zole');

INSERT INTO superheroes(id, name, align, eye, hair, gender, appearances, year, universe) VALUES(999999999, 'ZABIL', 'VEry BAD', 'ZOR GLASs', 'Black Hair', 'SPIDER', 4043, 1962, 'zole');

!!! Можно не указывать поля для  НО с такой вставкой могут быть проблемы!!!
!!! Не рекомендуется так делать !!!
!!! INSERT INTO superheroes VALUES(777, 'ZABIL', 'VEry BAD', 'ZOR GLASs', 'Black Hair', 'SPIDER', 4043, 1962, 'zole');

!!! создаем пустую таблицу

DROP TABLE IF EXISTS superheroes;

CREATE TABLE superheroes(id SERIAL PRIMARY KEY, name VARCHAR(100), align VARCHAR(30), eye VARCHAR(30), hair VARCHAR(30), gender VARCHAR(30), appearances INT, year INT, universe VARCHAR(10));

INSERT INTO superheroes(name, appearances, universe) VALUES ('Spider-Man', 4043, 'marvel');

SELECT * FROM superheroes WHERE appearances = 4043

SELECT * FROM superheroes WHERE appearances = 4043 AND name NOT IN ('Spider-Man (Peter Parker)');

INSERT INTO superheroes(name, align, eye, hair, gender, appearances, year, universe) VALUES('ZABILO', 'VEry BAD', 'ZOR GLASs', 'Black Hair', 'SPIDER', 4043, 1962, 'zole');

INSERT INTO superheroes(id, name, align, eye, hair, gender, appearances, year, universe) VALUES(999999999, 'ZABIL', 'VEry BAD', 'ZOR GLASs', 'Black Hair', 'SPIDER', 4043, 1962, 'zole');

!!! Можно не указывать поля для НО с такой вставкой могут быть проблемы!!!

!!! Не рекомендуется так делать !!!

!!! INSERT INTO superheroes VALUES(777, 'ZABIL', 'VEry BAD', 'ZOR GLASs', 'Black Hair', 'SPIDER', 4043, 1962, 'zole');

Изменение данных \ оператор UPDATE

!!! Рекомендуется для точного изменения данных использовать первичный ключ(PRIMARY KEY), то есть в нашем случае столбец ID

UPDATE superheroes SET name='Batman' , universe='dc' WHERE id=777

UPDATE superheroes SET gender='Man' WHERE gender='Male Characters'

UPDATE superheroes SET gender='Male Characters' WHERE gender='SPIDER'

UPDATE superheroes SET name='XXXX' WHERE name='ZABILO'

!!! Рекомендуется для точного изменения данных использовать первичный ключ(PRIMARY KEY), то есть в нашем случае столбец ID

UPDATE superheroes SET name='Batman' , universe='dc' WHERE id=777

UPDATE superheroes SET gender='Man' WHERE gender='Male Characters'

UPDATE superheroes SET gender='Male Characters' WHERE gender='SPIDER'

UPDATE superheroes SET name='XXXX' WHERE name='ZABILO'

Удаление данных из таблицы \ оператор DELETE

!!! Рекомендуется для точного удаления данных использовать первичный ключ(PRIMARY KEY), то есть в нашем случае столбец ID

DELETE FROM superheroes WHERE id=2;

DELETE FROM superheroes WHERE name='Spider-Man';

DELETE FROM superheroes; - удалить все данные из таблицы

!!! Рекомендуется для точного удаления данных использовать первичный ключ(PRIMARY KEY), то есть в нашем случае столбец ID

DELETE FROM superheroes WHERE id=2;

DELETE FROM superheroes WHERE name='Spider-Man';

DELETE FROM superheroes; - удалить все данные из таблицы

Группировка в SQL:

!!! Функции агрегации COUNT, SUM, AVG, MAX, MIN
!!! функция COUNT(*) - подсчет кол-ва строк

SELECT gender, COUNT(*) FROM superheroes GROUP BY gender

SELECT align, COUNT(*) FROM superheroes GROUP BY align

SELECT universe, align, COUNT(*) FROM superheroes GROUP BY universe, align - многоуровневая группировка данных в SQL

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair ORDER BY COUNT(*) DESC
------------------------------------------------------------------------------------------------------------
SELECT hair, COUNT(*) FROM superheroes - из таблицы superheroes столбец hair с кол-вом строк
WHERE gender='Female Characters' - выполняем выборку по столбцу gender со значением 'Female Characters'
GROUP BY hair - сортируем по волосам
ORDER BY COUNT(*) DESC - сортируем от большего значения к меньшему
------------------------------------------------------------------------------------------------------------

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair ORDER BY COUNT(*) DESC LIMIT 5

!!! Функции агрегации COUNT, SUM, AVG, MAX, MIN

!!! функция COUNT(*) - подсчет кол-ва строк

SELECT gender, COUNT(*) FROM superheroes GROUP BY gender

SELECT align, COUNT(*) FROM superheroes GROUP BY align

SELECT universe, align, COUNT(*) FROM superheroes GROUP BY universe, align - многоуровневая группировка данных в SQL

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair ORDER BY COUNT(*) DESC

------------------------------------------------------------------------------------------------------------

SELECT hair, COUNT(*) FROM superheroes - из таблицы superheroes столбец hair с кол-вом строк

WHERE gender='Female Characters' - выполняем выборку по столбцу gender со значением 'Female Characters'

GROUP BY hair - сортируем по волосам

ORDER BY COUNT(*) DESC - сортируем от большего значения к меньшему

------------------------------------------------------------------------------------------------------------

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair ORDER BY COUNT(*) DESC LIMIT 5

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

!!! Агрегатные функции, функции используемые вместе с группировкой (например с GROUP BY)
!!! Можно использовать с: фильтрацией (WHERE), сортировкой (ORDER BY), ограничение кол-ва строк (LIMIT) и т.д.

SELECT align, COUNT(*) FROM superheroes GROUP BY align

!!! AVG - среднее значение
!!! COUNT - количество значений
!!! MAC - максимальное значение
!!! MIN- минимальное значение
!!! SUM - сумма

SELECT align, COUNT(*), SUM(appearances) FROM superheroes GROUP BY align - подсчитаем количество появления супергероев к комиксах (appearances - кол-во появлений, align - характер героя)

SELECT align, AVG(appearances), SUM(appearances)/COUNT(*) AS average FROM superheroes GROUP BY align

SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year

SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year ORDER BY year

!!! ниже не правильная сортировка ...
!!! SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year ORDER BY min !!! не правильный запрос
!!! SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year ORDER BY max !!! не правильный запрос

!!! вот так правильно будет 
SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year ORDER BY MAX(appearances) DESC
SELECT year, MIN(appearances), MAX(appearances) AS max_ap FROM superheroes GROUP BY year ORDER BY max_ap DESC

SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year ORDER BY MIN(appearances) DESC
SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year ORDER BY MIN(appearances) 

SELECT year, MIN(appearances), MAX(appearances) AS max_ap FROM superheroes GROUP BY year ORDER BY max_ap DESC LIMIT 5

SELECT COUNT(*), MIN(appearances), MAX(appearances), SUM(appearances), AVG(appearances) FROM superheroes

!!! Агрегатные функции, функции используемые вместе с группировкой (например с GROUP BY)

!!! Можно использовать с: фильтрацией (WHERE), сортировкой (ORDER BY), ограничение кол-ва строк (LIMIT) и т.д.

SELECT align, COUNT(*) FROM superheroes GROUP BY align

!!! AVG - среднее значение

!!! COUNT - количество значений

!!! MAC - максимальное значение

!!! MIN- минимальное значение

!!! SUM - сумма

SELECT align, COUNT(*), SUM(appearances) FROM superheroes GROUP BY align - подсчитаем количество появления супергероев к комиксах (appearances - кол-во появлений, align - характер героя)

SELECT align, AVG(appearances), SUM(appearances)/COUNT(*) AS average FROM superheroes GROUP BY align

SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year

SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year ORDER BY year

!!! ниже не правильная сортировка ...

!!! SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year ORDER BY min !!! не правильный запрос

!!! SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year ORDER BY max !!! не правильный запрос

!!! вот так правильно будет

SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year ORDER BY MAX(appearances) DESC

SELECT year, MIN(appearances), MAX(appearances) AS max_ap FROM superheroes GROUP BY year ORDER BY max_ap DESC

SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year ORDER BY MIN(appearances) DESC

SELECT year, MIN(appearances), MAX(appearances) FROM superheroes GROUP BY year ORDER BY MIN(appearances)

SELECT year, MIN(appearances), MAX(appearances) AS max_ap FROM superheroes GROUP BY year ORDER BY max_ap DESC LIMIT 5

SELECT COUNT(*), MIN(appearances), MAX(appearances), SUM(appearances), AVG(appearances) FROM superheroes

Группировки и фильтрация в SQL: HAVING

!!! WHERE - фильтрация строк
!!! HAVING - фильтрация результатов группировке

!!! Порядок выполнения (SELECT)
!!! Выбор таблицы (FROM)
!!! Фильтрация строк из таблицы (WHERE)
!!! Группировка (GROUP BY)
!!! Фильтрация результатов группировки (HAVING)
!!! Сортировка по count (ORDER BY count DESC)
!!! Ограничение вывода (LIMIT)
!!! Диапазон вывода (BETWEEN 50 AND 300)

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair
SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair ORDER BY count DESC

!!!  НЕ ПРАВИЛЬНАЯ КОНСТРУКЦИЯ
!!! SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters'  AND COUNT(*) > 10 GROUP BY hair
!!! ERROR: ОШИБКА:  агрегатные функции нельзя применять в конструкции WHERE

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair HAVING COUNT(*) > 10

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair HAVING COUNT(*) > 10 ORDER BY count DESC

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair HAVING COUNT(*) > 10 ORDER BY count DESC LIMIT 3

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair HAVING COUNT(*) BETWEEN 50 AND 300

!!! WHERE - фильтрация строк

!!! HAVING - фильтрация результатов группировке

!!! Порядок выполнения (SELECT)

!!! Выбор таблицы (FROM)

!!! Фильтрация строк из таблицы (WHERE)

!!! Группировка (GROUP BY)

!!! Фильтрация результатов группировки (HAVING)

!!! Сортировка по count (ORDER BY count DESC)

!!! Ограничение вывода (LIMIT)

!!! Диапазон вывода (BETWEEN 50 AND 300)

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair ORDER BY count DESC

!!! НЕ ПРАВИЛЬНАЯ КОНСТРУКЦИЯ

!!! SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' AND COUNT(*) > 10 GROUP BY hair

!!! ERROR: ОШИБКА: агрегатные функции нельзя применять в конструкции WHERE

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair HAVING COUNT(*) > 10

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair HAVING COUNT(*) > 10 ORDER BY count DESC

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair HAVING COUNT(*) > 10 ORDER BY count DESC LIMIT 3

SELECT hair, COUNT(*) FROM superheroes WHERE gender='Female Characters' GROUP BY hair HAVING COUNT(*) BETWEEN 50 AND 300

Декомпозиция данных в базе | Основы SQL

Foreign key - внешний ключ

Суть "Декомпозиция данных в базе" - это, уменьшение повторяющихся данных в таблице, 
например у нас есть таблица супер героев и в ней столбец align (характеристика героев - bad, good, neutral, reformed criminals),
и мы заполняя эту таблицу вводим его характеристику и можем ошибаться в столбике введя Bad, bad, BADD и т.д.,
и мы для того что бы уменьшить кол-во ошибок/вводимых_данных можем создать еще одну таблицу align_id вида:
id align
1  Goog
2  Bad
3  Neutral
4  Reformed Criminal
Сделать ссылку таблицы на другую.
Это нам даст:
Уменьшение кол-ва дублированных данных.
Уменьшение ошибок при заполнение таблиц.

Типы связей:
Один к одному
Один ко многим
Многие ко многим

Foreign key - внешний ключ

Суть "Декомпозиция данных в базе" - это, уменьшение повторяющихся данных в таблице,

например у нас есть таблица супер героев и в ней столбец align (характеристика героев - bad, good, neutral, reformed criminals),

и мы заполняя эту таблицу вводим его характеристику и можем ошибаться в столбике введя Bad, bad, BADD и т.д.,

и мы для того что бы уменьшить кол-во ошибок/вводимых_данных можем создать еще одну таблицу align_id вида:

id align

1 Goog

2 Bad

3 Neutral

4 Reformed Criminal

Сделать ссылку таблицы на другую.

Это нам даст:

Уменьшение кол-ва дублированных данных.

Уменьшение ошибок при заполнение таблиц.

Типы связей:

Один к одному

Один ко многим

Многие ко многим

Запрос данных из нескольких таблиц: JOIN | Основы SQL

!!! внутренние объединения
!!! INNER JOIN используется по умолчанию

Пример использования JOIN:
SELECT products.name, product_types.type_name 
FROM products JOIN product_types 
ON producys.type_id = product_types.id;


Псевдонимы:
SELECT p.name, t,type_name
FROM products AS p JOIN product_types AS t
ON p.type_id = t.id;


Фильтрация запроса:
SELECT p.name AS product_name,
       p.type_name AS product_type,
       p.price AS product_price
FROM products AS p JOIN product_types AS t
ON p.type_id = t.id
WHERE t.type_name='Онлайн-курс';


Фильтрация запроса из нескольких таблиц:
SELECT p.name AS product_name,
       p.type_name AS product_type,
       p.price AS product_price
FROM products AS p JOIN product_types AS t
ON p.type_id = t.id
WHERE t.type_name='Онлайн-курс'
AND p.price = 0;


Сортировка:
SELECT p.name AS product_name,
       p.type_name AS product_type,
       p.price AS product_price
FROM products AS p JOIN product_types AS t
ON p.type_id = t.id
WHERE t.type_name='Онлайн-курс'
ORDER BY p.price DESC;

!!! внутренние объединения

!!! INNER JOIN используется по умолчанию

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

SELECT products.name, product_types.type_name

FROM products JOIN product_types

ON producys.type_id = product_types.id;

Псевдонимы:

SELECT p.name, t,type_name

FROM products AS p JOIN product_types AS t

ON p.type_id = t.id;

Фильтрация запроса:

SELECT p.name AS product_name,

p.type_name AS product_type,

p.price AS product_price

FROM products AS p JOIN product_types AS t

ON p.type_id = t.id

WHERE t.type_name='Онлайн-курс';

Фильтрация запроса из нескольких таблиц:

SELECT p.name AS product_name,

p.type_name AS product_type,

p.price AS product_price

FROM products AS p JOIN product_types AS t

ON p.type_id = t.id

WHERE t.type_name='Онлайн-курс'

AND p.price = 0;

Сортировка:

SELECT p.name AS product_name,

p.type_name AS product_type,

p.price AS product_price

FROM products AS p JOIN product_types AS t

ON p.type_id = t.id

WHERE t.type_name='Онлайн-курс'

ORDER BY p.price DESC;

Типы объединений в SQL | Основы SQL

Типы:
INNER JOIN используется по умолчанию
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
CROSS JOIN

Пример LEFT:
SELECT products.name, product_types.type_name 
FROM products LEFT OUTER JOIN product_types
ON products.type_id = product_types.id;

Пример RIGHT:
SELECT products.name, product_types.type_name 
FROM product_types RIGHT OUTER JOIN products
ON product_types.id = products.type_id;

Пример FULL:
SELECT products.name, product_types.type_name
FROM products FULL OUTER JOIN product_types
ON products.type_id = product_types.id;

Пример CROSS:
SELECT products.name, product_types.types_name
FROM products CROSS JOIN product_types;

Типы:

INNER JOIN используется по умолчанию

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

CROSS JOIN

Пример LEFT:

SELECT products.name, product_types.type_name

FROM products LEFT OUTER JOIN product_types

ON products.type_id = product_types.id;

Пример RIGHT:

SELECT products.name, product_types.type_name

FROM product_types RIGHT OUTER JOIN products

ON product_types.id = products.type_id;

Пример FULL:

SELECT products.name, product_types.type_name

FROM products FULL OUTER JOIN product_types

ON products.type_id = product_types.id;

Пример CROSS:

SELECT products.name, product_types.types_name

FROM products CROSS JOIN product_types;

Схема базы данных | Основы SQL:

https://drawsql.app/

1	https://drawsql.app/

Подзапросы | Основы SQL:

!!! Подзапросы заключаются в скобки ()

Пример:
SELECT id, name, price
FROM products
WHERE price = (SELECT MAX(price)
               FROM products);
Пример:
SELECT id, name, price
FROM products
WHERE id IN (SELECT product_id
             FROM sales);

Пример:
!!! вместо SELECT может быть UPDATE, DELETE, INSERT
UPDATE products
SET price = price + 500
WHERE types_id = (SELECT id
                  FROM product_types
                  WHERE type_name='Книга');

!!! Подзапросы заключаются в скобки ()

Пример:

SELECT id, name, price

FROM products

WHERE price = (SELECT MAX(price)

FROM products);

Пример:

SELECT id, name, price

FROM products

WHERE id IN (SELECT product_id

FROM sales);

Пример:

!!! вместо SELECT может быть UPDATE, DELETE, INSERT

UPDATE products

SET price = price + 500

WHERE types_id = (SELECT id

FROM product_types

WHERE type_name='Книга');

Транзакции | Основы SQL

!!! СУБД - система управления базами данных
!!! Транзакция - последовательность команд SQL, которые должны быть выполнены полностью или не выполнены вообще.
!!! Во всех современных СУБД автоматическая фиксация транзакций / Нет возможности отменить изменения

Пример запросов:
START TRANSACTION;

UPDATE accounts SET balance = balance - 15000
WHERE account_number = 123456;

UPDATE accounts SET balance = balance + 15000
WHERE account_number = 987656;

COMMIT; - принять транзакцию (в случае успеха действие по умолчанию.)
ROLLBACK;  - если что то пошло не так, изменения откатить 

Причины не выполнения запроса:
Отказ СУБД
      Аппаратная проблема
      Программная ошибка в СУБД или операционной системе
      Не хватает места на диске для записи данных
Отказ приложения пользователя
      Аппаратная проблема на клиенте
      Программная ошибка в приложении или операционной системе
      Пользователь прервал работу приложения
Потеря сетевого соединения клиента и сервера СУБД
      Аппаратная проблема с сетевым оборудованием клиента/сервера
      Проблемы с сеть.

!!! СУБД - система управления базами данных

!!! Транзакция - последовательность команд SQL, которые должны быть выполнены полностью или не выполнены вообще.

!!! Во всех современных СУБД автоматическая фиксация транзакций / Нет возможности отменить изменения

Пример запросов:

START TRANSACTION;

UPDATE accounts SET balance = balance - 15000

WHERE account_number = 123456;

UPDATE accounts SET balance = balance + 15000

WHERE account_number = 987656;

COMMIT; - принять транзакцию (в случае успеха действие по умолчанию.)

ROLLBACK; - если что то пошло не так, изменения откатить

Причины не выполнения запроса:

Отказ СУБД

Аппаратная проблема

Программная ошибка в СУБД или операционной системе

Не хватает места на диске для записи данных

Отказ приложения пользователя

Аппаратная проблема на клиенте

Программная ошибка в приложении или операционной системе

Пользователь прервал работу приложения

Потеря сетевого соединения клиента и сервера СУБД

Аппаратная проблема с сетевым оборудованием клиента/сервера

Проблемы с сеть.

Индексы | Основы SQL :

Индексы - это структура данных, позволяющая быстро определить положение интересующих данных в базе.
Создаются для столбца (совокупность столбцов) в таблице.
Обеспечивает возможность повысить производительность запросов.
Реализуется автоматически СУБД.
Изменение SQL запроса не требуется.
Ускоряет чтение из СУБД.
Может замедлить запись в СУБД.

Пример создания индексов вручную:
CREATE INDEX supeheroes_name_idx
ON superheros(name);

Пример запроса:
SELECT name, appearances, eye, hair
FROM superheroes
WHERE name = 'Iron Man(Anthony\ "Tony\" Stark)'

Индексы - это структура данных, позволяющая быстро определить положение интересующих данных в базе.

Создаются для столбца (совокупность столбцов) в таблице.

Обеспечивает возможность повысить производительность запросов.

Реализуется автоматически СУБД.

Изменение SQL запроса не требуется.

Ускоряет чтение из СУБД.

Может замедлить запись в СУБД.

Пример создания индексов вручную:

CREATE INDEX supeheroes_name_idx

ON superheros(name);

Пример запроса:

SELECT name, appearances, eye, hair

FROM superheroes

WHERE name = 'Iron Man(Anthony\ "Tony\" Stark)'