Шпаргалка по программированию на SQL, MySQL, MariaDB

Доходчивая статья об индексах.

Оглавление

Запрос данных

Как заставить MySQL или MariaDB различать буквы "е" и "ё" в запросах.

// Такое поведение проявляется, когда способ сравнения для текстового столбца в таблице БД, по которому осуществляется поиск WHERE или производится группировка GROUP BY, указан как utf8_general_ci. Необходимо в PhpMyAdmin зайти в Структуру нужной таблицы и для указанного столбца задать Сравнение utf8_bin.

Запрос с объединением двух таблиц по полю: левая таблица главная, правая второстепенная (LEFT JOIN)

SELECT t1.id as id, t2.elem as el FROM t1 LEFT JOIN t2 ON t1.idk = t2.id WHERE slovo = 'король';

Запрос с подзапросом в предложении FROM (LEFT JOIN)

// Подзапрос в предложении FROM работает также как временная таблица, которая удаляется сразу после того как скрипт отработал.

SELECT t1.id as id, tt2.elem as el FROM t1 LEFT JOIN (SELECT id, elem FROM t3 WHERE pole1 > 3 AND pole1 < 7) AS tt2 ON t1.idk = tt2.id WHERE slovo = 'король';

// здесь на основе таблицы t3 создаётся временная таблица с именем tt2 и полями id и elem, удовлетворяющими условию pole1 > 3 AND pole1 < 7; после чего к таблице t1 с помощью LEFT JOIN присоединяется таблица tt2 по условию t1.idk = tt2.id, далее в таблице t1 выбираются записи по условию slovo = 'король' и из этих записей выбираются поля t1.id as id, tt2.elem as el

Запрос с подзапросом в предложении WHERE (WHERE, COUNT)

SELECT COUNT(id) AS cn FROM clients WHERE id IN (SELECT client FROM sales WHERE tovar IN (1,2,3) GROUP BY client);

// Подзапрос получает клиентов купивших товары с id = 1, 2 или 3, при этом группирует клиентов так, чтобы если один клиент купил, например и товар 1 и товар 3, то он будет выведен единожды
// Основной запрос с помощью агрегатной функции COUNT выводит общее количество выбранных в подзапросе клиентов

Объединение нескольких запросов в один (UNION)

SELECT slovo FROM t1 LEFT JOIN slova ON t1.sub = slova.id
UNION SELECT slovo FROM t1 LEFT JOIN slova ON t1.dei = slova.id
UNION SELECT slovo FROM t1 LEFT JOIN slova ON t1.ob = slova.id;

// удаляет повторяющиеся строки между разными SELECT
// каждый SELECT должен запрашивать одинаковое количество полей с одинаковыми типами данных

Объединение нескольких запросов в один с сортировкой (UNION, ORDER BY)

// отсортирует объединение запросов целиком по второму столбцу (data1/data2/data3) в порядке возрастания

SELECT str1, data1 FROM t1 LEFT JOIN slova ON t1.sub = slova.id
UNION SELECT str2, data2 FROM t1 LEFT JOIN slova ON t1.dei = slova.id
UNION SELECT str3, data3 FROM t1 LEFT JOIN slova ON t1.ob = slova.id
ORDER BY 2;

Объединение нескольких запросов в один с группировкой (UNION, GROUP BY)

// сгруппирует объединение запросов целиком по второму столбцу (data1/data2/data3)

SELECT str, data FROM (SELECT str1, data1 FROM t1 LEFT JOIN slova ON t1.sub = slova.id
UNION SELECT str2, data2 FROM t1 LEFT JOIN slova ON t1.dei = slova.id
UNION SELECT str3, data3 FROM t1 LEFT JOIN slova ON t1.ob = slova.id) t
GROUP BY data;

Поиск по части строки (LIKE)

SELECT slovo FROM t1 WHERE slovo LIKE '%бор%'; // бор - в любой части слова
SELECT slovo FROM t1 WHERE slovo LIKE 'бор%'; // бор - в начале слова
SELECT slovo FROM t1 WHERE slovo LIKE '%бор'; // бор - в конце слова

Поиск по части строки (INSTR)

SELECT * FROM t1 WHERE INSTR('вид', soch) > 0 GROUP BY soch;

// INSTR(string, substring) - возвращает позицию подстроки в строке
// выполняет поиск без учета регистра
// первая позиция в строке - 1
// если не найдено, то вернет 0

Округление (CEIL, ROUND, FLOOR)

UPDATE dohod SET pole = CEIL(pole2/12); // обновит поле с округлением до целого в большую сторону

UPDATE dohod SET pole = ROUND(pole2/12); // обновит поле с округлением до целого по математическим правилам

UPDATE dohod SET pole = FLOOR(pole2/12); // обновит поле с округлением до целого в меньшую сторону

Вывод помесячной суммы с группировкой по году и месяцу (DATE_FORMAT)

SELECT DATE_FORMAT(datao, '%Y %m') as dat, SUM(summa) as smm FROM dohod GROUP BY DATE_FORMAT(datao, '%Y %m') ORDER BY dat DESC LIMIT 13;

DATE_FORMAT(date, format_mask) - форматирует дату по заданной маске
%d - день месяца в виде числового значения (от 01 до 31)
%e - день месяца в виде числового значения (от 0 до 31)
%H - час (от 00 до 23)
%i - минуты (от 00 до 59)
%j - день года (001 — 366)
%M - название месяца полностью (January to December)
%m - название месяца в виде числового значения (от 00 до 12)
%s - секунды (от 00 до 59)
%T - время в 24-часовом формате (hh:mm:ss)
%W - имя дня недели полностью (Sunday to Saturday)
%w - день недели, где Sunday=0 and Saturday=6
%Y - год в виде числового значения из 4 цифр

Сравнение дат (CURDATE, NOW, INTERVAL)

SELECT * FROM dohod WHERE DATE(datao) > "2023-06-26"; // здесь поле datao имеет тип даты и времени вида 2023-06-26 13:47:39
SELECT * FROM dohod WHERE datao >= '2023-06-26'; // здесь поле datao имеет тип даты вида 2023-06-26
SELECT * FROM dohod WHERE datao >= '2023-06-26 09:00:00'; // здесь поле datao имеет тип даты и времени вида 2023-06-26 13:47:39
SELECT * FROM dohod WHERE datao >= CURDATE() - INTERVAL 1 YEAR; // выбрать записи с датой не позднее, чем 1 год от сегодняшней даты (здесь поле datao имеет тип даты вида 2023-06-26)
SELECT * FROM dohod WHERE datao >= NOW() - INTERVAL 1 YEAR; // выбрать записи с датой не позднее, чем 1 год от сегодняшней даты (здесь поле datao имеет тип даты и времени вида 2023-06-26 13:47:39)

Функции:
CURDATE() - возвратит дату вида 2023-06-26
NOW() - возвратит дату и время вида 2023-06-26 13:47:39
INTERVAL - функция для произведения арифметических операций сложения и вычитания с датой и временем
CURDATE() - INTERVAL 1 YEAR // отнимет 1 год от текущей даты
CURDATE() + INTERVAL 2 MONTH // прибавит 2 месяца к текущей дате
NOW() - INTERVAL 3 DAY // отнимет 3 дня от текущей даты
NOW() + INTERVAL 4 HOUR // прибавит 4 часа к текущей дате
NOW() - INTERVAL 5 MINUTE // отнимет 5 минут от текущей даты
NOW() + INTERVAL 6 SECOND // прибавит 6 секунд к текущей дате

Оператор сравнения "не равно" (<>)

SELECT * FROM dohod WHERE pole1 <> ''; // вернёт записи, где pole1 не равно пустому значению;

Удалить начальные и конечные пробелы из строки (TRIM)

SELECT * FROM dohod WHERE TRIM(pole1) <> '' OR TRIM(pole2) <> ''; // вернёт записи, где pole1 либо pole2 не пусты; пробелы в полях будут приведены к пустому значению; поля со значением NULL также будут приведены к пустому значению, поэтому отдельная проверка IS NOT NULL для них не понадобится

Конкатенация (сложение) значений полей в запросе (CONCAT)

SELECT CONCAT(d1, d2, d3) AS rs FROM dohod WHERE pole = 1 GROUP BY rs; // под именем rs будет выведено, например "123", если d1=1, d2=2, d3=3

SELECT CONCAT(d1, ' - ', d3) AS rs FROM dohod WHERE pole = 1 GROUP BY rs; // под именем rs будет выведено, например "1 - 3", если d1=1, d3=3

SELECT id FROM dohod WHERE CONCAT('@', pole, '@') LIKE '%@125@%'; // выведет все записи, где в значениях поля pole встречается подстрока @125@

SELECT id FROM dohod, table WHERE CONCAT('@', dohod.pole, '@') LIKE CONCAT('%,', table.id, ',%'); // выведет все записи, где в значениях поля pole встречается значение поля id обрамлённое запятыми

Вернуть подстроку из текстового поля (SUBSTRING)

SELECT id, SUBSTRING(pole, 1, 200) AS pole FROM table; // вернёт первые 200 символов текстового поля pole

Группировка записей (GROUP BY)

SELECT datao, SUM(summa) FROM dohod GROUP BY datao; // вернёт общую сумму на заданную дату

Найти дубли в поле и их количество (HAVING)

SELECT pole, COUNT(pole) AS count FROM table GROUP BY pole HAVING count > 1; // вернёт записи с одинаковыми значениями и выведет количество повторов

Сортировка записей (ORDER BY)

SELECT datao FROM dohod ORDER BY dat; // по умолчанию сортировка от меньшего к большему

SELECT datao FROM dohod ORDER BY dat ASC; // сортировка в порядке возрастания (от меньшего к большему)

SELECT datao FROM dohod ORDER BY dat DESC; // сортировка в порядке убывания(от большему к меньшему)

SELECT datao, summa FROM dohod ORDER BY 2 DESC; // сортировка по номеру позиции столбца в запрашиваемом наборе (в данном примере по столбцу summa, стоящему во 2-й позиции)

SELECT datao, summa FROM dohod ORDER BY datao DESC, summa ASC; // сортировка по нескольким столбцам - сперва по datao по убыванию, затем по summa по возрастанию

Ограничение количества возвращаемых записей (LIMIT)

SELECT * FROM dohod ORDER BY dat LIMIT 10; // вернёт первые 10 записей запроса

Запросить записи со смещением (LIMIT, OFFSET)

SELECT * FROM dohod ORDER BY dat LIMIT 10 OFFSET 4; // вернёт 10 записей запроса пропустив первые 4 записи, то есть с 5-й по 14-ю

Запрос любого из заданного множества значений (IN, NOT IN)

SELECT * FROM dohod WHERE pole IN (1, 4, 7, 9); // вернёт записи, у которых столбец pole имеет значение равное 1 или 4 или 7 или 9

SELECT * FROM dohod WHERE pole IN (SELECT id FROM table_name WHERE price = 200); // вернёт записи, у которых столбец pole имеет значение равное id, возвращённым подзапросом

SELECT * FROM dohod WHERE pole NOT IN (1, 4, 7, 9); // вернёт записи, у которых столбец pole имеет любое значение кроме равного 1 или 4 или 7 или 9

Найти MAX или MIN от SUM (SUM, LIMIT, DATE_FORMAT)

SELECT DATE_FORMAT(datao, '%Y') AS god, SUM(summa) AS sm FROM prihod GROUP BY god ORDER BY sm DESC LIMIT 1; // получить сумму дохода и год, в котором была максимальная сумма дохода
SELECT DATE_FORMAT(datao, '%Y') AS god, SUM(summa) AS sm FROM prihod GROUP BY god ORDER BY sm ASC LIMIT 1; // получить сумму дохода и год, в котором была минимальная сумма дохода

Агрегатные функции: AVG, SUM, MIN, MAX, COUNT (MIN, MAX, COUNT, AVG, SUM)

AVG и SUM работают только с числовыми столбцами
MIN, MAX и COUNT работают с числами, строками и датами
Все агрегатные функции за исключением COUNT(*) игнорируют значения NULL в столбце

AVG: вычисляет среднее значение
SELECT AVG(pole) AS sred FROM tbl; // среднее значение по столбцу pole
SELECT AVG(pole * pole2) AS sred FROM tbl; // среднее значение результатов умножения столбца pole на pole2

SUM: вычисляет сумму значений
SELECT SUM(pole) AS sm FROM tbl; // сумма значений по столбцу pole
SELECT SUM(pole * pole2) AS sm FROM tbl; // сумма значений результатов умножения столбца pole на pole2

MIN: вычисляет наименьшее значение
SELECT MIN(pole) AS mn FROM tbl; // по столбцу pole

MAX: вычисляет наибольшее значение
SELECT MAX(pole) AS mx FROM tbl; // по столбцу pole

COUNT: вычисляет количество строк в запросе
SELECT COUNT(*) AS cn FROM tbl; // количество строк в запросе (здесь строки NULL не игнорируются)
SELECT COUNT(pole) AS cn FROM tbl; // количество строк в запросе по столбцу pole (здесь строки NULL игнорируются)

В одном запросе можно использовать сразу несколько агрегатных функций
SELECT COUNT(*) AS cn, MIN(pole) AS mn, MAX(pole) AS mx, AVG(pole) AS sred FROM tbl;

Запрос суммы полей и сортировка по сумме полей (ORDER BY)

SELECT (summa + price) AS sum1, data FROM table ORDER BY sum1 DESC;

Запрос с оператором IN и сортировка по порядку IN запроса (IN, FIELD)

SELECT * FROM table WHERE pole IN (1, 4, 2, 3) ORDER BY FIELD(pole, 1, 4, 2, 3);

Добавление данных

Вставка записи в таблицу (INSERT INTO)

INSERT INTO table_name (pole_int, pole_str, pole_date) VALUES (1, 'ку-ку', '2020-05-31 20:10:19');

Вставка нескольких записей в таблицу (INSERT INTO)

INSERT INTO table_name (pole_int, pole_str, pole_date) VALUES (1, 'ку-ку', '2020-05-31 20:10:19'), (2, 'ку-ку2', '2020-05-30 20:11:20'), (3, 'ку-ку3', '2020-05-29 20:12:21');

Обновление данных

Обновление записей в таблице (UPDATE)

UPDATE table_name SET price = price + 1; // в таблице table_name увеличит значение в столбце price на 1 для всех записей в таблице

UPDATE table_name SET price = 200 WHERE price = 195; // в таблице table_name заменит значение в столбце price на 200, для столбцов price у которых значение = 195

UPDATE table_name SET price = 200, name = 'монитор' WHERE price = 195; // обновить несколько столбцов с заданным условием

UPDATE table_name SET price = DEFAULT, name = NULL WHERE price = 195; // можно задавать DEFAULT - значение по умолчанию для столбца или NULL - пустое значение

Обновление записей в таблице с присвоением значения одного поля другому (UPDATE)

UPDATE table_name SET pole1 = pole2 WHERE id = 5;

Обновление записей в таблице с использованием подзапроса (UPDATE, SELECT)

UPDATE table_name SET
 price = (SELECT SUM(summa) FROM table_name2 WHERE cat = 1),
 name = (SELECT pole FROM table_name3 WHERE category = 2)
WHERE price = 195

Преобразовать результат запроса в числовой тип (CAST)

UPDATE table_name SET name = 'монитор', price =  CAST((SELECT id FROM table_name2 WHERE elem = 20) AS SIGNED) WHERE id = 5; // в функции CAST аргумент SIGNED - означает преобразование в отрицательное или положительное INTEGER число, UNSIGNED - в положительное INTEGER число; также возможны варианты аргумента: DATE, DATETIME, TIME, BINARY, CHAR

Удаление записи в таблице (DELETE)

DELETE FROM table_name WHERE id = 3; // удалит запись с id = 3 из таблицы table_name

Заменить все вхождения подстроки в поле на другую подстроку (UPDATE, REPLACE)

// заменит все '<' на '&lt;' в pole
UPDATE table SET pole = REPLACE(pole, '<', '&lt;');

Обновление записей в таблице с конкатенацией строки (UPDATE, CONCAT)

UPDATE table_name SET pole1 = CONCAT(123, ',', pole1) WHERE id = 5;
// если в pole1 было записано 456, то станет 123,456

Перемещение пункта списка со сдвигом всех остальных пунктов (UPDATE)

Есть несколько списков с разным количеством пунктов. Каждый пункт представлен отдельной записью в таблице, где:
id - номер пункта (принимает значение: $idv - номер перемещаемого пункта)
idr - номер списка (принимает значения: $rubv - номер списка перемещаемого пункта; $rubc - номер списка целевого пункта)
npp - порядковый номер пункта в списке (принимает значения: $nppv - порядковый номер перемещаемого пункта в списке; $nppc - порядковый номер целевого пункта в списке)
Перемещаемый пункт должен встать сразу под целевым.

Если перемещение пункта происходит внутри одного списка и перемещаемый пункт находится ниже целевого
$query = "UPDATE table SET npp = npp + 1 WHERE idr = " . $rubc . " AND npp > " . $nppc . " AND npp < " . $nppv . ";";
$query = "UPDATE table SET npp = " . ($nppc+1) . " WHERE id = " . $idv . ";";

Если перемещение пункта происходит внутри одного списка и перемещаемый пункт находится выше целевого
$query = "UPDATE table SET npp = npp - 1 WHERE idr = " . $rubc . " AND npp > " . $nppv . " AND npp <= " . $nppc . " ;";
$query = "UPDATE table SET npp = " . $nppc . " WHERE id = " . $idv . ";";

Если перемещение пункта происходит в другой список
$query = "UPDATE table SET npp = " . ($nppc+1) . ", idr = " . $rubc . "  WHERE id = " . $idv . ";";
$query = "UPDATE table SET npp = npp + 1 WHERE idr = " . $rubc . " AND npp > " . $nppc . " AND id <> " . $idv . ";";
$query = "UPDATE table SET npp = npp - 1 WHERE idr = " . $rubv . " AND npp > " . $nppv . ";";

Создание таблицы, столбца, индекса

Добавить столбец в таблицу (ALTER TABLE, ADD)

ALTER TABLE dohod ADD pole1 INT DEFAULT NULL;
ALTER TABLE dohod ADD pole2 VARCHAR(10) DEFAULT NULL, ADD pole3 INT DEFAULT NULL, ADD pole4 INT DEFAULT NULL; // добавить несколько столбцов

Создание индекса для таблицы (CREATE INDEX)

// индекс для столбца или нескольких столбцов таблицы может ускорить выборку SELECT, имеет смысл создавать индексы для столбцов используемых в предложениях WHERE и ORDER BY
CREATE INDEX pole ON dohod(pole); // создаст индекс для столбца pole в таблице dohod
CREATE INDEX pole1_pole2 ON dohod(pole1, pole2); // создаст составной индекс для столбцов pole1 и pole2 в таблице dohod

Удалить столбец из таблицы (ALTER TABLE, DROP COLUMN)

ALTER TABLE tbl DROP COLUMN pole1; // удалить 1 столбец
ALTER TABLE tbl DROP COLUMN pole1, DROP COLUMN pole2; // удалить несколько столбцов