Операторы SELECT, INSERT, UPDATE, DELETE

Составные условия AND, OR

SELECT name FROM users WHERE age >= 18 AND city = "Krasnodar"

упростим с помощью IN

SELECT * FROM products WHERE country = "RU" OR country = "CY" OR country = "USA"

можно сократить до команды:

SELECT * FROM products WHERE country IN ('RU', 'CY', 'USA')
SELECT * FROM orders WHERE status IN ('cancelled')

SELECT * FROM products WHERE country IN ('RU', 'CY', 'USA') AND price < 10000

NOT IN (все кроме NOT IN):

SELECT * FROM orders WHERE status NOT IN ('cancelled')

упростим с помощью BETWEEN (NOT BETWEEN - для случаев не попадающих в диапазон)

SELECT * FROM products WHERE price >= 10000 AND price <= 20000

до:

SELECT * FROM products WHERE price BETWEEN 10000 AND 20000

SELECT * FROM products WHERE price BETWEEN 10000 AND 20000 OR country = 'RU'

SELECT * FROM orders WHERE sum >= 3000 AND products_count < 3
SELECT * FROM orders WHERE status = 'cancelled' AND sum BETWEEN 3000 AND 10000
SELECT * FROM orders WHERE status = 'cancelled' AND sum NOT BETWEEN 3000 AND 10000

Сортировка результатов (ORDER BY)

SELECT last_name, first_name, age WHERE age >= 18 ORDER BY last_name
SELECT last_name, first_name, age WHERE age >= 18 ORDER BY last_name, first_name

SELECT last_name, first_name, age WHERE age >= 18 ORDER BY age
SELECT last_name, first_name, age WHERE age >= 18 ORDER BY last_name DESC
**DESC - в обратном порядке**

SELECT * FROM products ORDER BY price
SELECT name, price FROM products ORDER BY price DESC
SELECT * FROM products WHERE price >= 5000 ORDER BY price DESC
SELECT name, count, price FROM products WHERE price < 3000 ORDER BY name
SELECT last_name, first_name FROM users ORDER BY last_name, first_name
SELECT * FROM users WHERE salary >= 40000 ORDER BY salary DESC, first_name
SELECT * FROM users WHERE salary < 30000 AND salary > 0 ORDER BY birthday

Ограничение выборки:

SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 5
следующие 5, указываем пропуск (сколько пропустить, сколько получить):
SELECT * FROM products WHERE count > 0 ORDER BY price DESC LIMIT 5,5

Выберите из таблицы orders 5 самых дорогих заказов за всё время. Данные нужно отсортировать в порядке убывания цены. Отмененные заказы не учитывайте.

SELECT * FROM orders WHERE status NOT IN ('cancelled') ORDER BY sum DESC LIMIT 5

Выберите из таблицы products название и цены трех самых дешевых товаров, которые есть на складе.

SELECT name, price FROM products WHERE count > 0 ORDER BY price LIMIT 3

Сайт выводит товары по 5 штук. Выберите из таблицы products товары, которые пользователи увидят на 3 странице каталога при сортировке в порядке возрастания цены (price).

SELECT * FROM products ORDER BY price limit 10,5

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

UPDATE billing SET currency = 'USD' WHERE payer_email = 'foo@bar.ru' AND sum > 500;

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

INSERT INTO billing VALUES (
'foo@bar.ru',
'baz@buzz.ru',
'500',
'This is text');

Если мы хотим вставить не все колонки записи, то надо указать атрибуты:

INSERT INTO billing (payer_email, recipient_email) VALUES ('foo@bar.ru', 'baz@buzz.ru');

Удаление:

DELETE FROM billing WHERE email = "foo@mail.com"

Агрегация данных:

COUNT()
AVG() - среднее значение

SELECT AVG(budget) FROM projects;

DATEDIFF()

SELECT AVG(DATEDIFF(pr_finish, pr_start)) FROM projects WHERE pr_finish IS NOT NULL;

MAX()
MIN()

GROUP BY

Синонимы as

SELECT AVG(DATEDIFF(pr_finish, pr_start)) __as avg_days__ FROM projects WHERE pr_finish IS NOT NULL GROUP BY client ORDER BY avg_days;