Предметный указатель

-- Классический стиль (принят в конспекте)
SELECT
    customer_id,
    SUM(amount) AS total_amount
FROM payment
GROUP BY customer_id;

-- Стиль dbt Labs (современный аналитический)
select
    customer_id,
    sum(amount) as total_amount
from payment
group by customer_id;

SELECT customer_id, rental_date
FROM rental
WHERE rental_date BETWEEN '2005-06-14' AND '2005-06-16';

SELECT rental_id, customer_id, return_date
FROM rental
WHERE return_date NOT BETWEEN '2005-05-01' AND '2005-09-01';

-- Синтаксис
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END;

-- Basic Case Usage
SELECT product_name,
       CASE
           WHEN stock_quantity > 0 THEN 'In Stock'
           ELSE 'Out of Stock'
       END AS stock_status
FROM products;

-- Multiple Conditions
SELECT employee_name,
       CASE
           WHEN salary > 50000 THEN 'High Salary'
           WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium Salary'
           ELSE 'Low Salary'
       END AS salary_category
FROM employees;

-- Using CASE in ORDER BY
SELECT order_id, order_date
FROM orders
ORDER BY
    CASE
        WHEN order_status = 'Pending' THEN 1
        WHEN order_status = 'Shipped' THEN 2
        ELSE 3
    END;

-- Using CASE in UPDATE
UPDATE employees
SET salary_category = 
    CASE
        WHEN salary > 50000 THEN 'High Salary'
        WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END;

SELECT
    -- Работа с датой и временем
    CAST('2019-09-17 15:30:00' AS DATETIME), --> 2019-09-17 15:30:00
    CAST('2019-09-17 15:30:00' AS DATE),     --> 2019-09-17
    CAST('2019-09-17 15:30:00' AS TIME),     --> 15:30:00

    -- Преобразование в десятичное число с заданной точностью и округлением
    CAST('123.456' AS DECIMAL(5, 2)),        --> 123.46
    CAST(123 AS DECIMAL(5, 2)),              --> 123.00

    -- Преобразование в строку
    CAST(12345 AS CHAR),                     --> '12345'
    CAST('HelloWorld' AS CHAR(5)),           --> 'Hello' (обрежет до 5 символов)
    
    -- Преобразование в целые числа
    CAST(-42.8 AS SIGNED),                   --> -43
    CAST('100' AS UNSIGNED),                 --> 100

    -- Бинарное сравнение (с учетом регистра)
    (CAST('abc' AS BINARY) = 'ABC');         --> 0 (False)

SELECT * FROM rental 
WHERE rental_date < CAST('2005-05-25' AS DATE);

SELECT c.first_name, c.last_name, count(*)
FROM customer c
  INNER JOIN rental r
  ON c.customer_id = r.customer_id
GROUP BY c.first_name, c.last_name
HAVING count(*) >= 40;

SELECT COUNT(*) 
FROM large_table
WHERE your_conditions;

CREATE TABLE person(
    person_id INT PRIMARY KEY AUTO_INCREMENT,
    fname VARCHAR(20),
    lname VARCHAR(20),
    -- ...
    postal_code VARCHAR(20)
);

CREATE TEMPORARY TABLE actors_J (
    actor_id smallint(5),
    first_name varchar(45),
    last_name varchar(45)
);

INSERT INTO actors_j
SELECT actor_id, first_name, last_name
FROM actor
WHERE last_name LIKE 'J%';

CREATE VIEW cust_vw AS
SELECT customer_id, first_name, last_name, active
FROM customer;

SELECT DATE('2003-12-31 01:02:03'); --> 2003-12-31

SELECT c.first_name, c.last_name,
  TIME(r.rental_date) rental_time
FROM customer c
  INNER JOIN rental r
  ON c.customer_id = r.customer_id
WHERE DATE(r.rental_date) = '2005-06-14';

SELECT
    DATE_ADD('2018-05-01', INTERVAL 1 DAY), --> '2018-05-02'
    DATE_ADD('2018-05-01', INTERVAL -1 YEAR), --> '2017-05-01'
    DATE_ADD('2018-12-31 23:59:59',
             INTERVAL 1 DAY), --> '2019-01-01 23:59:59'
    DATE_ADD('2020-12-31 23:59:59',
             INTERVAL 1 SECOND), --> '2021-01-01 00:00:00'
    DATE_ADD('2100-12-31 23:59:59',
             INTERVAL '1:1' MINUTE_SECOND), --> '2101-01-01 00:01:00'
    DATE_ADD('2025-12-31 1:58:59', 
             INTERVAL '23:2:2' HOUR_SECOND), --> '2026-01-01 01:01:01'
    DATE_ADD('2017-01-15', 
             INTERVAL '9-11' YEAR_MONTH); --> '2026-12-15'

SELECT DATEDIFF('2019-09-03', '2019-06-21'); --> 74

-- Игнорирует время дня в своих аргументах
SELECT DATEDIFF('2019-09-03 23:59:59', '2019-06-21 00:00:01'); --> 74

DELETE FROM person
WHERE person_id = 2;

DELETE FROM string_tbl;

DESC person;

ORDER BY last_name desc

SELECT DISTINCT actor_id
FROM film_actor
ORDER BY actor_id;

DROP TABLE person;

SELECT EXTRACT(YEAR FROM '2019-09-18 22:19:05');  --> 2019
SELECT EXTRACT(MONTH FROM '2019-09-18 22:19:05'); --> 09
SELECT EXTRACT(DAY FROM '2019-09-18 22:19:05');   --> 18

WHERE last_name IN ('WILLIAMS', 'DAVIS');

WHERE rating NOT IN ('PG-13', 'R', 'NC-17');

SELECT c.first_name, c.last_name, a.address
FROM customer c INNER JOIN address a
  ON c.address_id = a.address_id;

SELECT c.first_name, c.last_name, a.address
FROM customer c INNER JOIN address a
  USING (address_id);

INSERT INTO person
    (person_id, fname, lname, eye_color, birth_date)
VALUES (null, 'William', 'Turner', 'BR', '1972-05-27');

SELECT rental_id, customer_id
FROM rental
WHERE return_date IS NULL;

SELECT rental_id, customer_id, return_date
FROM rental
WHERE return_date IS NOT NULL;

SELECT last_name, first_name
FROM customer
WHERE left(last_name, 1) = 'Q';
-- выделение первой буквы столбца last_name

-- Синтаксис
SELECT column1, column2, ...
FROM table_name
WHERE column_name [NOT] LIKE pattern;

SELECT rating FROM film WHERE title LIKE '%PET%';

SELECT name, name LIKE '%y' ends_in_y
FROM category;

SELECT last_name, first_name
FROM customer
WHERE last_name REGEXP '^[QY]';

SHOW TABLES;

SHOW FULL TABLES;

UPDATE rental
SET return_date = STR_TO_DATE('September 17, 2019', '%M', '%d', '%Y')
WHERE rental_id = 99999;

SELECT c.first_name, c.last_name,
  time(r.rental_date) rental_time
FROM customer c
  INNER JOIN rental r
  ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14';

UPDATE person
SET street = '1225 Tremont St.',
    city = 'Boston',
    state = 'MA',
    country = 'USA',
    postal_code = '02138'
WHERE person_id = 1;

| Подстановочный символ | Соответствие                         |
| --------------------- | ------------------------------------ |
|  _                    | В точности один символ               |
|  %                    | Любое количество символов, включая 0 |


# Примеры выражений поиска

| Выражение поиска | Интерпретация                                         |
| ---------------- | ----------------------------------------------------- |
| F%               | Строка, начинающаяся с F                              |
| %f               | Строка, заканчивающаяся f                             |
| %bas%            | Строка, содержащая подстроку `bas`                    |
| __t_             | Строка из 4 символов с t в третьей позиции            |
| ___-__-____      | Строка из 11 символов с дефисами в 4-й и 7-й позициях |

SELECT name, ROUND(salary * 1.2, 2) 
FROM employees 
WHERE department = 'IT';

-- Порядок написания

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

-- Порядок выполнения

SELECT -- 7: выборка полей
    e.name,
    d.department_name,
    COUNT(o.id) as order_count
FROM employees e -- 1: определение таблиц
    JOIN departments d ON e.department_id = d.id -- 2: соединения
    LEFT JOIN orders o ON e.id = o.employee_id -- 3: дополнительные соединения
WHERE e.salary > 50000 -- 4: фильтрация
GROUP BY e.id, e.name, d.department_name -- 5: группировка
HAVING COUNT(o.id) > 5 -- 6: фильтрация после группировки
ORDER BY order_count DESC -- 8: сортировка
LIMIT 10; -- 9: ограничение вывода

WITH 
first_cte AS (
    -- Шаг 1: готовим первые данные
    SELECT ...
    FROM ...
),

second_cte AS (
    -- Шаг 2: можем использовать данные из first_cte!
    SELECT ...
    FROM first_cte
    WHERE ...
)

-- Шаг 3: финальный SELECT, выдающий результат пользователю
SELECT ...
FROM second_cte
    INNER JOIN other_table ON ...;