Шпаргалка по программированию на 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 = 'король';

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

// отсортирует объединение запросов целиком по второму столбцу (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;

Поиск по части строки(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 цифр

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

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

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

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

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

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

// индекс для столбца или нескольких столбцов таблицы может ускорить выборку 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 Хороший отзыв · Разумно · Дельно · Опытно · Идейно