SQL, запросы и шпаргалка для MySQL/MariaDB

SQL

SQL — формальный непроцедурный язык программирования, применяемый для создания, модификации и управления данными в произвольной реляционной базе данных, управляемой соответствующей системой управления базами данных (СУБД). Используется в MySQL, PostgreSQL и так далее.

Шпаргалка по командам и запросам SQL для MySQL/MariaDB

Типы

TINYINT Может хранить числа от -128 до 127
SMALLINT Диапазон от -32 768 до 32 767
MEDIUMINT Диапазон от -8 388 608 до 8 388 607
INT Диапазон от -2 147 483 648 до 2 147 483 647
BIGINT Диапазон от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807
FLOAT от –3.402823466E+38 до –1.175494351E-38, 0 и 1.175494351E-38 до 3.402823466E+38
DOUBLE, DOUBLE PRECISION, REAL от -1.7976931348623157E+308 до -2.2250738585072014E-308, 0 и 2.2250738585072014E-308 до 1.7976931348623157E+308
DATE в диапазоне от «1000-01-01» до «9999-12-31»
DATETIME Дата и время Допустимые диапазоны от «1000-01-01 00:00:00» до «9999-12-31 23:59:59». MySQL хранит поле типа DATETIME в виде «YYYY-MM-DD HH:MM:SS» (ГГГГ-ММ-ДД ЧЧ-ММ-СС).
TIMESTAMP Дата и время Диапазон от «1970-01-01 00:00:00» до, примерно, 2037 года. поле типа TIMESTAMP в видах «YYYYMMDDHHMMSS» (TIMESTAMP(14)), «YYMMDDHHMMSS» (TIMESTAMP(12)), «YYYYMMDD» (TIMESTAMP(8))
TIME Диапазон от «-838:59:59» до «838:59:59». MySQL хранит поле TIME в виде «HH:MM:SS», но позволяет присваивать значения столбцам TIME с использованием либо строки или числа.
YEAR Если вы используете 4 цифра, то допустимые значения 1901-2155, и 0000. Если 2 цифры, то 1970-2069 (70-69). MySQL хранит значения поля YEAR в формате «YYYY».
VARCHAR Строка переменной длины. от 1 до 255 символов.
TINYTEXT с максимальной длиной 255 (28 - 1) символов.
ТЕХТ с максимальной длиной 65535 (216 - 1) символов.
MEDIUMTEXT с максимальной длиной 16777215 (224 - 1) символов.
LONGTEXT с максимальной длиной 4294967295 (232 - 1) символов.

Общие команды

Список таблиц

show tables;

Создаём таблицу

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type_acc` int(11) NOT NULL DEFAULT '0',
  `parent_id` int(11) NOT NULL,
  `name_acc` tinytext NOT NULL,
  `description_acc` tinytext,
  `requisites_acc` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

NOT NULL - не может быть пустым

AUTO_INCREMENT - автоматическое добавление следующего значения

DEFAULT - значение добавится, если не указано явно

Посмотреть описание таблицы

DESC accounts;

или

SHOW COLUMNS FROM accounts;

Удаление таблицы

DROP TABLE accounts;

Команда, которая создала таблицу

SHOW CREATE TABLE accounts; (выдаст коменду из пункта “Создаём таблицу”)

Работаем с пользователями

Создание пользователя с нужным паролем:

CREATE USER UserName IDENTIFIED BY “password”;

Даём право выбирать данные из таблицы:

GRANT SELECT ON TableName TO UserName;

Аналогично, разрешаем что-то добавлять, но не обновлять:

GRANT INSERT ON TableName TO UserName;

Разрешаем всё в определённой таблице:

GRANT ALL ON TableName TO UserName;

Разрешаем всё на все базы (первая звёздочка) и таблицы (вторая)

GRANT ALL ON TableName TO UserName;

ЗАБИРАЕМ! право выбирать данные из таблицы:

REVOKE SELECT ON . TO UserName;

Создаём роли (не работает в MySQL, не поддерживает!):

CREATE ROLE GroupName;

Разрешаем всё в определённой таблице для группы:

GRANT ALL ON TableName TO GroupName;

Удаление роли

DROP ROLE GroupName;

Читаем данные из таблицы, SELECT-запросы

SELECT * FROM accounts; (получаем все значения из таблицы accounts)
SELECT * FROM accounts WHERE name_acc = ‘значение’; (получаем всё, что содержит значение, при числах апострофы не нужны (WHERE id = 5))
SELECT id FROM accounts WHERE name_acc = ‘значение’; (получаем только id, результат будет быстрее)
SELECT name_acc FROM accounts WHERE name_acc = ‘значение’ AND id = ‘значение 2’; (результат по 2 проверкам)
SELECT name_acc FROM accounts WHERE name_acc = ‘значение’ AND id > ‘значение 2’; (вторая проверка выбирает значения больше указанного, можно > < => <=)
SELECT name_acc FROM accounts WHERE id BETWEEN 2 AND 10; (ищем значение между 2 и 10 (включительно!))
SELECT name_acc FROM accounts WHERE NOT id BETWEEN 2 AND 10; (NOT обязательно после WHERE, ищем всё, кроме указанного диапазона)
SELECT name_acc FROM accounts WHERE name_acc = ‘значение’ OR id = ‘значение 2’; (тут ИЛИ)
SELECT name_acc FROM accounts WHERE name_acc IN(‘значение1’,‘значение2’,‘значение3’); (один вариант вместо кучи OR)
SELECT name_acc FROM accounts WHERE name_acc NOT IN(‘значение1’,‘значение2’,‘значение3’); (обратное предыдущему варианту, не выбираем указанное)
SELECT name_acc FROM accounts WHERE parent_id IS NULL; (ищем что незаполнено в столбце parent_id)
SELECT name_acc FROM accounts WHERE name_acc LIKE ‘%ие’; (ищем похожее, % - любое количество символов)
SELECT name_acc FROM accounts WHERE NOT name_acc LIKE ‘%ие’; (NOT обязательно после WHERE, ищем не содержащее указанное значение)
SELECT name_acc FROM accounts ORDER BY name_acc; (выборка с сортировкой по алфавиту по полю name_acc)
SELECT name_acc FROM accounts ORDER BY name_acc DESC; (выборка с сортировкой по алфавиту В ОБРАТНОМ ПОРЯДКЕ по полю name_acc)
SELECT SUM(debet) FROM operations WHERE account = 8; (суммируем данные по столбцу debet для account 8)
SELECT account, SUM(debet) FROM operations GROUP BY account ORDER BY SUM(debet); (суммы, группируя по account, например, суммируя платы по компаниям)
SELECT account, AVG(debet) FROM operations GROUP BY account; (получаем среднее значение, группируя по полю account)
SELECT account, MAX(debet) FROM operations GROUP BY account; (получаем максимальное значение, группируя по полю account)
SELECT account, MIN(debet) FROM operations GROUP BY account; (получаем минимальное значение, группируя по полю account)
SELECT COUNT(debet) FROM operations; (получаем количество записей в столбце debet)
SELECT * FROM operations LIMIT 2; (получаем только 2 строки)
SELECT * FROM operations LIMIT 1,2; (получаем строки со второй (отсчёт с 0, поэтому стоит 1) и 2 строки всего)
SELECT type_acc from accounts WHERE id=5 UNION SELECT date_oper from operations WHERE account=8;

(UNION объединяет запросы)

Преобразование типов, CAST

SELECT CAST(“2017-02-23” AD DATE) - строка в дату

Числовые функции

MOD(x,y) остаток деления

RAND() случайное число

ROUND(x) округляет до целого

ROUND(x,y) округляет до дробной части y. Например ROUND(5.2346345723, 2) вернёт 5.23

SIGN(x) возвращает 1, если число положительное, -1 если отрицательное

Удаление записей, DELETE-запросы

DELETE FROM accounts; (удаляет все записи из таблицы)

DELETE FROM accounts WHERE name_acc = ‘значение’; (удаляем строку по значению, если их несколько - удаляются все)

DELETE FROM accounts WHERE name_acc = ‘значение’ AND id = ‘значение 2’; (результат по 2 проверкам, удаляем по совпадению с двумя значениями)

DELETE FROM accounts WHERE name_acc = ‘значение’ OR id = ‘значение 2’; (удаляем строки или по первому совпадению или по второму)

Вставить записи, INSERT-запросы

Эти запросы не заменяют данные, а добавляют!

INSERT INTO accounts (ст1, ст2) VALUES (‘значение 1’, 15); (для чисел кавычки не нужны)

Обновить записи, UPDATE-запросы

UPDATE accounts SET name_acc = ‘Новое значение’ WHERE id = 7; (обновляем name_acc на строке с id = 7)

Так же как и в SELECT, можно использовать AND , OR и т.д.

Изменение таблицы, ALTER-запросы

ALTER TABLE accounts ADD test INT NOT NULL AFTER account; (добавил поле test после поля account)

ALTER TABLE accounts DROP test; (удалил поле test)

Для ALTER так же доступны CHANGE и MODIFY

Транзакции

Обеспечивают:

• Атомарность (или все операции выполнены или не выполнена ни одна)
• Целостность
• Изолированность (пока одна транзакция не пройдёт, вторая не тронется)
• Устойчивость (сохранение изменённых данных)

Транзакции доступны на InnoDB и BDB. Если у таблицы другой движок, сменить можно так:

ALTER TABLE account TYPE = InnoDB;

Как это работает

START TRANSACTION;
SELECT …
UPDATE …
INSERT …

и тут:

ROLLBACK; (эта команда отменит изменения)

или

COMMIT; (Закрепить, согласится с изменениями)

Не забыть закрыть транзакцию, иначе журнал будет расти до бесконечности. :)

Временные таблицы

Существует до завершения сеанса пользователя. Создать:

CREATE TEMPORARY TABLE temp_accounts;

Временная таблица из результата запроса:

CREATE TEMPORARY TABLE temp_accounts AS SELECT * FROM accounts;

Графические программы для работы с БД MySQL/MariaDB

• MySQL workbench
• DBeaver Community