COALESCE(значение1, значение2, ..., значениеN)

SELECT name, COALESCE(phone, 'Нет номера') AS phone_display
FROM users;

name    phone    phone_display
Иван    +10101   +10101
Анна    NULL     Нет номера

SELECT name, COALESCE(work_phone, mobile_phone, home_phone, 'Нет') AS preferred_phone
FROM users;

name  work_p mobile_p home_p pref_p
Иван  NULL   NULL     555-1  555-1
Анна  777-5  999-9    NULL   777-5
Олег  NULL   NULL     NULL   Нет

id  name  phones
1   Иван  +123, +456, +789

id   name   phone
1    Иван   +123
1    Иван   +456
1    Иван   +789

student_id  course_id course_name
1           SQL101    Основы SQL
2           SQL101    Основы SQL

student_id   course_id
1            SQL101
2            SQL101

course_id        course_name
SQL101           Основы SQL

employee_id   dep_id   dep_name
1             10       IT
2             20       HR

employee_id   dep_id
1             10
2             20

dep_id   dep_name
10       IT
20       HR

teacher_id   subject      classroom
1            SQL          101
1            Python       101
1            SQL          102
1            Python       102

teacher_id   subject
1            SQL
1            Python

teacher_id   classroom
1            101
1            102

project_id  employee_id role
1           100         Разработчик
1           101         Тестировщик
2           100         Разработчик
2           102         Аналитик

project_id
1
2

employee_id
100
101
102

project_id employee_id role
1          100         Разработчик
1          101         Тестировщик
2          100         Разработчик
2          102         Аналитик

employee_id salary dep  date
1           5000   IT   2024-01-01
1           5500   IT   2024-03-01
1           5500   HR   2024-05-01

employee_id salary   date
1           5000     2024-01-01
1           5500     2024-03-01

employee_id dep  date
1           IT   2024-01-01
1           HR   2024-05-01

SELECT name, age FROM users;
-- Выбирает столбцы name и age из таблицы users

INSERT INTO users (name, age) VALUES ('Алексей', 25);
-- Вставляет строку с именем "Алексей" и возрастом 25 в таблицу users

UPDATE users SET age = 26 WHERE name = 'Алексей';
-- Обновляет возраст пользователя "Алексей" на 26

DELETE FROM users WHERE age < 18;
-- Удаляет всех пользователей младше 18 лет

SELECT * FROM users WHERE age > 20;
-- Выбирает всех пользователей старше 20 лет

SELECT * FROM users ORDER BY age DESC;
-- Выбирает всех пользователей и сортирует их по возрасту по убыванию

SELECT age, COUNT(*) FROM users GROUP BY age;
-- Считает, сколько пользователей каждого возраста

SELECT users.name, orders.total 
FROM users 
JOIN orders ON users.id = orders.user_id;
-- Выбирает имена пользователей и суммы их заказов

SELECT столбец1, столбец2, ...
FROM таблица
ORDER BY столбец1 [ASC | DESC], столбец2 [ASC | DESC], ...;

-- 1. По возрасту по возрастанию
SELECT name, age FROM users ORDER BY age;

-- 2. По возрасту по убыванию
SELECT name, age FROM users ORDER BY age DESC;

-- 3. По городу, затем по возрасту
SELECT name, city, age FROM users ORDER BY city ASC, age DESC;

-- 4. По длине имени
SELECT name FROM users ORDER BY LENGTH(name);

SELECT department, AVG(salary)
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING AVG(salary) > 5000
ORDER BY AVG(salary) DESC
LIMIT 3;

SELECT выражение AS псевдоним
FROM таблица AS псевдоним;

SELECT name, salary * 1.15 AS adjusted_salary
FROM employees;

SELECT e.name, d.name AS department
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.id;

SELECT 
    name AS employee_name,
    salary AS base_salary,
    salary * 0.13 AS tax_amount,
    salary * 0.87 AS net_salary
FROM employees;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

ROLLBACK;

CREATE TRIGGER имя_триггера
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON имя_таблицы
FOR EACH ROW
BEGIN
   -- SQL-действия*
END;
FOR EACH ROW означает, что триггер сработает для каждой строки, к которой применимо событие.

CREATE TRIGGER log_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO logs(message)
  VALUES (CONCAT('Добавлен сотрудник: ', NEW.name));
END;

CREATE TRIGGER protect_admin_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
  IF OLD.role = 'admin' THEN
    SIGNAL SQLSTATE '45000' 
      SET MESSAGE_TEXT = 'Нельзя удалить администратора!';
  END IF;
END;

INSERT INTO target_table (col1, col2)
SELECT col_a, col_b
FROM source_table
WHERE condition;

INSERT INTO employees_backup
SELECT * FROM employees;

INSERT INTO orders_archive
SELECT * FROM orders
WHERE order_date < CURDATE() - INTERVAL 1 YEAR;

INSERT INTO sales_summary (category, total_sales)
SELECT category, SUM(amount)
FROM sales
GROUP BY category;

INSERT INTO newsletter_subscribers (user_id, user_name)
SELECT u.id, u.name
FROM users AS u
JOIN subnoscriptions AS s ON u.id = s.user_id
WHERE s.active = 1;

SELECT t1.column, t2.column
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.id = t2.foreign_id;

id   name     dept_id
1    Alice    10
2    Bob      20
3    Charlie  NULL
4    Diana    30

id    name
10    HR
20    Engineering
30    Marketing
40    Sales

SELECT e.name, d.name AS department
FROM employees AS e
INNER JOIN departments AS d
ON e.dept_id = d.id;

name   department
Alice  HR
Bob    Engineering
Diana  Marketing

DELETE FROM таблица1
WHERE признак = 'значение';

TRUNCATE таблица1;

DROP таблица1;

Customer ─< Order >─ Product
    |          |        |
    id     order_id     id
    name   order_date   name
    email  total        price

Customer ─────< Order

Order >───── Product

OrderItem (
  order_id INT,    -- внешний ключ к Order
  product_id INT,  -- внешний ключ к Product
  quantity INT     -- количество единиц товара
)