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

Это статья-шпаргалка с примерами по программированию на SQL, а также настройке MariaDB и MySQL. Здесь для удобства я собрал часто используемые мной директивы и некоторые другие особенности. Статья будет пополняться по мере необходимости.

 

Оглавление

 

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

Как заставить 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

Объединение нескольких запросов в один (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

Вывод помесячной суммы с группировкой по году и месяцу (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 цифр

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

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 обрамлённое запятыми

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

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

Сортировка записей (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

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

Вставка записи в таблицу (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

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

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

Преобразовать результат запроса в числовой тип (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

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

Добавить столбец в таблицу (INSERT INTO)

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

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

Поделиться статьей:  

Читайте все статьи из IT - Шпаргалки по программированию:

Поделитесь своим мнением

Правила сообщений

Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Хороший отзыв
Яндекс.Метрика
© 2016 - 2020 Хороший отзыв · Разумно · Дельно · Опытно · Идейно