Основы SQL

Основные команды в SHELL:

\! chcp 1251 фикс кодировки
\l список баз данных
\c подключает оболочку к серверу MySQL и назначает
глобальный сеанс.
\d имя таблицы подключиться к таблице
\dt имя_таблицы просмотр

Работа с базами данных:

CREATE DATABASE имя_базы_данных;
DROP DATABASE имя_базы_данных;

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

CREATE TABLE имя таблицы;
DROP TABLE имя_таблицы;
CREATE TABLE users(
id BIGSERIAL NOT NULL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
gender VARCHAR(50) NOT NULL,
email VARCHAR(50),
date_of_birth DATE NOT NULL,
country_of_birth VARCHAR(50) NOT NULL
);

Добавление данных в таблицу:

INSERT INTO users(
first_name, last_name, gender, email, date_of_birth)
VALUES('Ann', 'Black', 'FEMALE', 'ann@gmail.com', '1999-01-01');

Импорт из файла:

\i C:/Users/Alex/Desktop/users.sql
сайт генератор: https://www.mockaroo.com/

Пример файла

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

SELECT - определяет, какие столбцы из таблицы будут выбраны для вывода в результате запроса.
FROM - указывает таблицу или таблицы, из которых нужно выбрать данные.
WHERE - определяет условия для фильтрации строк.
GROUP BY - группирует строки по заданным столбцам.
HAVING - определяет условия для фильтрации групп.
ORDER BY - сортирует результаты запроса по заданным столбцам.
LIMIT - ограничивает количество строк, возвращаемых запросом.

Запросы, выборка данных:

SELECT email FROM users; все почты из таблицы
SELECT first_name, last_name FROM users; имена и фамилии из таблицы
SELECT DISTINCT country_of_birth FROM users ORDER BY country_of_birth DESC; поиск уникальных стран с сортировкой по стране в обратном порядке
SELECT * FROM users LIMIT 10 OFFSET 5; вывести 10 строк со сдвигом 5
SELECT * FROM users FETCH FIRST 5 ROW ONLY; первые пять строк другим способом
SELECT * FROM users WHERE gender = 'Female' and (country_of_birth = 'Russia' OR country_of_birth = 'China'); выбрать женщин из России или Китая
SELECT * FROM users WHERE country_of_birth IN ('China', 'Russia', 'Argentina'); метод IN более удобен для нескольких вариантов чем OR
SELECT * FROM users WHERE date_of_birth BETWEEN '1993-01-01' AND '1995-12-30'; выборка между двумя датами
SELECT * FROM users WHERE email LIKE '%@gmail.%'; выбрать все email с текстом @gmail. внутри
SELECT * FROM users WHERE email iLIKE 's%'; метод iLIKE игнорирует разницу заглавной и строчных букв
SELECT gender, COUNT(*) FROM users GROUP BY gender; подсчет кол-ва людей каждого гендера сгруппированных по гендеру
SELECT gender, COUNT(*) FROM users GROUP BY gender HAVING COUNT(*) > 10; вывести только тех чье кол-во больше 10
SELECT gender, COUNT(*) FROM users GROUP BY gender HAVING COUNT(*) > 10 ORDER BY count; вывести только тех чье кол-во больше 10 и отсортировать
SELECT first_name AS name, last_name AS surename FROM users; создать псевдоним/временное имя вместо имен колонок
SELECT COALESCE(email, 'not applicablle') FROM users; заменить пустые значения на значение 'not applicablle' или другое
SELECT COUNT(*) FROM users WHERE email IS NOT NULL and date_of_birth BETWEEN '1991-01-01' AND '1997-01-01'; просто посчитать кол-во человек по запросу

АГРЕГАТЫ И БАЗОВАЯ АРИФМЕТИКА:

SELECT MAX(price) FROM holiday; максимальное значение из колонки
SELECT MIN(price) FROM holiday; минимальное значение из колонки
SELECT ROUND(AVG(price)) FROM; holiday округленное среднее значение
SELECT SUM(price) FROM holiday; сумма всех значений
SELECT country, SUM(price) FROM holiday GROUP BY country ORDER BY SUM(price) DESC; сумма всех значений с сортировкой от максимального
SELECT country, SUM(price) FROM holiday WHERE country IN ('China','Russia','Brazil','Japan','Peru','Poland') GROUP BY country HAVING SUM(price)<70000 ORDER BY SUM(price) ASC LIMIT 5; сумма путевок в выбранных странах меньше 70000, с сортировкой и лимитом выдачи
+ - / * ^(степень) %(деление по модулю)

РАБОТА С ДАТОЙ И ВРЕМЕНЕМ:

SELECT NOW(); текущее время
SELECT NOW()::DATE; выведет только дату 2023-05-24
SELECT NOW()::TIME; выведет только время 02:46:03.679377
SELECT NOW() - INTERVAL '1 YEAR 10 MONTH 10 DAYS'; выведет дату из прошлого, можно использовать + для будущей даты.
SELECT EXTRACT(MONTH FROM NOW()); выведет цифру месяца, также можно использовать YEAR, DAY, DOW(день недели)
SELECT first_name, date_of_birth, AGE(NOW(), date_of_birth) FROM users; узнать возраст

УДАЛЕНИЕ:

DELETE FROM users WHERE id = 1; удалить строку с id = 1
DELETE FROM users WHERE email LIKE '%google.com%'; удаляем всех у кого почта содержит google.com

ОБНОВЛЕНИЕ ДАННЫХ:

UPDATE users SET first_name = 'Mike', email = 'kimberle@mail.ru' WHERE id = 3; поменять почту и имя у пользователя с id 3 (обязательно условие иначе обновится все строки)

ОГРАНИЧЕНИЯ И ПРОВЕРКИ:

ALTER TABLE users ADD CONSTRAINT unique_email_address UNIQUE (email); добавляем ограничение, чтобы email был уникальным значением
ALTER TABLE users ADD CONSTRAINT gender_const CHECK (gender = 'Female OR gender = 'Male'); делаем проверку, чтобы gender был только Female или Male
INSERT INTO users( id, first_name, last_name, gender, email, date_of_birth) VALUES(3, 'Ann', 'Black', 'Female', 'ann@gmail.com', '1999-01-01') ON CONFLICT (id) DO NOTHING; если есть такой же id (id должен быть primary key, т.е выступает в роли ограничителя), то ничего не делать, т.е не вернется ошибка
INSERT INTO users( id, first_name, last_name, gender, email, date_of_birth) VALUES(3, 'Ann', 'Black', 'Female', 'ann@gmail.com', '1999-01-01') ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email; если есть такой же id (id должен быть primary key, т.е выступает в роли ограничителя), то исключить из проверки email и заменить только это поле. Называется UPSERT (UPDATE + INSERT).

PRIMARY KEYS:

ALTER TABLE users DROP CONSTRAINT users_pkey; удалям primary key
ALTER TABLE users ADD PRIMARY KEY(id); добавляем id как primary key

FOREGIN KEYS:

ALTER TABLE users ADD bike_id BIGINT REFERENCES bikes(id); добавляем внешний ключ (bike_id) в таблицу users, который будет ссылаться на поле id в таблице bikes;
ALTER TABLE users ADD UNIQUE(bike_id); делаем bike_id уникальным в таблице users (у каждого свой велосипед) Если вы хотите явно указать имя для ограничения, вы можете добавить его после ключевого слова "CONSTRAINT". Например: ALTER TABLE users ADD CONSTRAINT unique_bike_id UNIQUE(bike_id);

Объединение таблиц (JOINS):

INNER JOIN SELECT * FROM users JOIN bikes ON users.bike_id = bikes.id; выбрать всех юзеров у кого есть байк
LEFT JOIN SELECT * FROM users LEFT JOIN bikes ON users.bike_id = bikes.id; выбрать всю таблицу слева и добавить значения из правой таблицы, в нашем случае будет много пустых значений, т.к велосипед есть только у 1 работника, поэтому можно отфильтровать:
SELECT * FROM users LEFT JOIN bikes ON users.bike_id = bikes.id WHERE bike_id IS NOT NULL;
RIGHT JOIN SELECT * FROM users RIGHT JOIN bikes ON users.bike_id = bikes.id; выведет полностью правую таблицу и заполнит, общими данными из левой, если они есть
FULL OUTER JOIN SELECT * FROM users FULL OUTER JOIN bikes ON users.bike_id = bikes.id; полностью две таблицы

УНИКАЛЬНЫЕ ИДЕНТИФИКАТОРЫ UUID:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; сначала устанавливаем расширение
SELECT uuid_generate_v4(); генерация рандомного id с помощью функции
INSERT INTO passport (passport_serial, issue_date, expire_date, country_of_issue) VALUES (uuid_generate_v4(), '2019_03_19', '2045_03_19', 'UK'); генерация рандомного uuid при заполнение таблицы