//
офіційний MySQL 8.0 Reference Manual > Data Manipulation Statements //dev.mysql.com
MariaDB: Complete list of SQL statements for data definition, data manipulation, etc. //mariadb.com
ГРАНДИОЗНЫЙ РЕФАКТОРИНГ!
Справочник !!! VPN //www.mysql.ru/docs/mysql-man-4.0-ru/reference.html
!!! MySQL — Использование переменных в запросе https://habr.com/ru/post/442706/
- VERSION(); возвращает версию
!!! текущее значение счетчика авто-инкремента таблицы:
1 2 3 |
SELECT Auto_increment FROM information_schema.tables WHERE table_schema = 'myDb' AND table_name = 'myTable'; |
Базовый синтаксис
Комментарии в запросах
Предусмотрено три способа комментирования:
- #<comment> отмечает комментарий до конца строки
- -- <comment> с пробелом отмечает комментарий до конца строки
- /*<comment>*/ отмечает многострочный комментарий
Литералы
Числовой литерал
/
- Null
- 0xHHHH
Строковый и бинарный литерал
- '...'
- "..."
- N'...', n'...', _utf8'...' эквивалентные способы определения литерала в национальной кодировке
- X'hh...' представляет бинарный литерал заданный hex-последоватльностью
- интродьюсер (introducer) специфицирует кодировку литерала Character Set Introducers
- _latin1, _latin2
- _ascii
- _utf8mb4
- _ucs2
12SELECT _ucs2 X'00520065004B0053', _ascii X'52654B53'>ReKS ReKS
- B'b...', b'b...' определяет бинарный литерал заданный битовой последовательностью
Литерал даты и времени
- '2023-08-24', '2023-08-24 12:00:00', '12:00:00' литерал даты и времени
- '0000-00-00' специальный литерал нулевой даты, который выражает неизвестную или некорректную дату, сходный по смыслу с NULL (но не NULL)
- '2023-08-00', '2023-00-00',... литерал неполной даты (incomplete dates) применяется в специальных случаях
Операторы
Вспомогательные операторы !!!
//dev.mysql.com/doc/refman/8.0/en/sql-utility-statements.html
- HELP 'search_string'
- USE db_name
- DESCRIBE, EXPLAIN операторы используются либо для получения информации о структуре таблицы, либо для планов выполнения запроса
Выборка данных
Несколько сокращенный формат выражения SELECT:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
WITH [RECURSIVE] cte_name [(col_name [, col_name] ...)] AS (subquery) [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ... SELECT [STRAIGHT_JOIN] [DISTINCT | DISTINCTROW | ALL] select_expression,... [INTO {OUTFILE | DUMPFILE} 'file_name' export_options] [FROM table_references [WHERE where_definition] [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...] [HAVING where_definition] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...] [LIMIT [offset,] rows] [PROCEDURE procedure_name] [FOR UPDATE | LOCK IN SHARE MODE]] |
Условие cоединения таблиц
//
- CROSS JOIN tbl1, tbl2, ... предполагает полное декартово произведение таблиц, ограничиваемое в условиях WHERE
- tbl1 INNER JOIN tbl2 ON ...
- tbl1 STRAIGHT_JOIN tbl2 ... устанавливает порядок использования таблиц для тех случаев, если оптимизатор сервера устанавливает порядок неправильно
- tbl1 NATURAL JOIN tbl2 соединяет таблицы по всем совпадающим полям таблиц
- tbl1 JOIN tbl2 USING ( col1, col2, ...) соединяет таблицы по совпадающим полям таблиц, перечисленным в USING
Условие в запросе
Условия реализуют отбор по набору условий после выражения WHERE
Условие к значению числового типа
Условие к значению строкового типа
Условие точного равенства строковых значений выражается оператором = и неравенство оператором != .
!!! кодировки???
Сравнение простым шаблоном
Сравнение значения с простым шаблоном реализуется оператором str LIKE template, где строковый шаблон может содержать подстановочные символы:
- % - последовательность любых символов любой длины
- _ - один любой символ
Подстановочный символ в шаблоне становится обычным символом с помощью маскирования \% и \_
1 2 3 |
SELECT val FROM (SELECT '8%' AS val UNION SELECT '35%' UNION SELECT '99\\') AS tbl WHERE val LIKE '__\%'; -> 35% |
Сравнение шаблоном регулярного выражения
Операторы синонимы RLIKE и REGEXP применяют к значению шаблон регулярного выражения, используя функцию REGEXP_LIKE(). Результат оператора можно использовать в условии, в результирующих данных, для группировки, сортировки и других sql-выражениях.
Пример условия отбора значения, приводимого к числовому значению:
1 |
SELECT expr FROM tbl WHERE expr RLIKE '^[+-]*[0-9]+(\.[0-9]+){0,1}$'; |
Пример условия отбора сообщений без кириллических слов, определяемых как последовательность трех и более кириллических символов:
1 |
SELECT msg FROM inbox WHERE msg NOT RLIKE '.*[А-Яа-я]{3,}.*'; |
/
Сравнение на сходство
Сопоставление строковых значений, имеющих общность - многогранная тема...
Сравнение строк на "сходное звучание" реализует оператор
expr1 SOUNDS LIKE expr2 на примере:
1 |
SELECT word, SOUNDEX(word) FROM dictionary WHERE word SOUND LIKE 'demo'; |
/
Условия с подзапросом
//
- EXISTS(SELECT...)
- NOT EXISTS(SELECT...)
- expression IN (SELECT...) условие выполняется
- expression {=,<,<=,>,>=,<>,!=,<=>} ANY(SELECT...) условие выполняется, если хотя бы одно значение подзапроса удовлетворяет условию
- SOME(...) является полным синонимом ANY, вопреки смысловым различиям в человеческой речи
- expression {=,<,<=,>,>=,<>,!=,<=>} ALL(SELECT...) условие выполняется, если все значения подзапросу удовлетворяют условию
Функции управления потоком
//
- IF(cond_expr, true_expr, false_expr)
- IFNULL(expr1,expr2) If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.
- NULLIF(expr1,expr2) Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
Агрегатные функции
Дивись http://reks.biz/?p=4714
!!! к INSERT
Обновление при вставке дублирующего ключа
Вставка набора значений, которая может выявить коллизию ключа на лету заменяется обновлением при использовании выражения ON DUPLICATE KEY UPDATE:
1 2 3 4 5 |
INSERT INTO phones ( phone, owner ) VALUES ( "380678001234", "ReKS" ), ( "380688001234", "BeTune" ) ON DUPLICATE KEY UPDATE owner = VALUES( owner ); |
В MySQL 8.0 добавлена возможность использовать псевдоним new:
1 2 |
... ON DUPLICATE KEY UPDATE owner = new.owner; |
Добавление записей в связанные таблицы
Типичная задача: в главную таблицу headTable с автоинкрементируемый ключом ID необходимо добавить новую запись, и ID новой записи использовать для записи в подчиненную таблицу subTable. Пакетный запрос выполнить вставку записей в две таблицы и вернет выборку с ключом заголовочной записи:
1 2 3 4 5 6 7 |
# вставка в головную и подчиненную таблицу INSERT INTO headTable (name) VALUES ('new head'); INSERT INTO subTable (headID, name) VALUES (LAST_INSERT_ID(), 'new sub'); # или через сохранение нового ID в переменной SET @NEW_ID:=LAST_INSERT_ID(); INSERT INTO subTable (headID, name) VALUES (@NEW_ID, 'new sub'); |
///
!!! к SELECT
Нумерация в запросе
Для нумерации по порядку всех записей запроса MySQL не предоставляет штатных средства (в отличии от MS SQL, где доступна функция row_number()), тем не менее нумерация реализуется с помощь переменной и выражения инкремента в запросе:
1 2 3 4 |
SET @row_number = 0; SELECT (@row_number:=@row_number+1) AS row_number, name FROM wp_terms ORDER BY name; |
REPLACE
//dev.mysql.com/doc/refman/8.0/en/replace.html
В MySQL 8.0 был добавлен новый оператор REPLACE, который работает аналогично оператору INSERT...ON DUPLICATE KEY UPDATE.
Функции
MySQL 8.0 Reference Manual > Mathematical Functions //dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html
Агрегатні функції
MySQL 5.6 Reference Manual > Aggregate Function Descriptions //dev.mysql.com
Агрегатные функции применяются в запросах с единственным результирующим выражением или в запросах с группировкой:
- COUNT([DISTINCT] expr [, expr]) подсчитывает количество значений в группировке
- [DISTINCT] expr - будет подсчитано число уникальных значений выражения
- [, expr] - будет подсчитано число уникальных наборов всех выражений
- [DISTINCT] expr - будет подсчитано число уникальных значений выражения
- MAX([DISTINCT] expr ), MIN([DISTINCT] expr ) отбирает наибольшее и наименьшее значение выражения
- [DISTINCT] - предусмотрено, но на результат не влияет
- GROUP_CONCAT([DISTINCT] expr [,expr ...][ORDER BY {unsigned_integer | col_name | expr}[ASC | DESC] [,col_name ...]][SEPARATOR str_val]) агрегирует составную строку
- [DISTINCT] expr [,expr ...] - одно или несколько выражений набора для агрегации в строку
- [ORDER BY {unsigned_integer | col_name | expr} - устанавливает порядок наборов перед агрегацией
- [ASC | DESC] [,col_name ...]]
- [SEPARATOR str_val]) - разделитель наборов при агрегации, по умолчанию ,
1SELECT GROUP_CONCAT( place, ': ', racer ORDER BY place SEPARATOR '\n' ) FROM race - SUM([DISTINCT] expr) - вычисляет сумму всех выражений или только уникальных при [DISTINCT]
- AVG([DISTINCT] expr) - вычисляет среднее значение всех выражений или только уникальных при [DISTINCT]
- VAR_POP(expr), VARIANCE(expr) (синоним) вычисляет стандартную дисперсию генеральной совокупности expr (в знаменателе общее количество строк)
- VAR_SAMP(expr) вычисляет выборочную дисперсию expr (в знаменателе общее количество строк - 1)
- BIT_AND(expr), BIT_OR(expr), BIT_XOR(expr) - выполняет побитовое И, ИЛИ, Исключающее ИЛИ для всех выражений
- STD(expr) статистическое отклонение
STDDEV(expr)
STDDEV_POP(expr)
STDDEV_SAMP(expr) - VAR_POP(expr) статистическая дисперсия
VAR_SAMP(expr)
VARIANCE(expr)
Виборче агрегування
Вычисление агрегатной функции по критерию реализуется с помощью функции IF(condition,true_expr,fals_expr). Пример счетчика грузов и сумматора веса с условием:
1 2 3 4 |
SELECT SUM(IF(weight>100; 1; 0)) AS ExcessCargo, # счетчик грузов выше предела 100 SUM(IF(weight>100; 0; weight)) AS CargoWeight # сумматор веса грузов до предела 100 FROM CargoList |
О
!!! Декомпозиция и повторное использование выражений
Конструкция WITH позволяет отдельно описать sql-выражение, которое можно использовать в другом sql-выражение один или несколько раз:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
WITH taxs (rate, name) AS ( SELECT 0, '0%' UNION ALL SELECT 0.2, '20%'), taxed AS ( SELECT name, rate FROM goods WHERE rate <= 0.2) SELECT taxed.name AS name, taxs.name AS tax FROM taxed INNER JOIN taxs ON taxed.rate = taxs.rate; |
!!! WITH RECURSIVE
Конструкция WITH в sql-выражении добавлена в MySQL 8.0.1. Она создает одно или несколько общих табличных выражений CTE (Common Table Expressions), каждое из которых представляет временный результирующий набор, доступный к использованию произвольное число раз до конца выражения.
!!!Тимчасові таблиці
Временные таблицы создаются и удаляются почти также, как обычные, для использования до завершения текущей сессии.
- создается оператором CREATE TEMPORARY TABLE tmp_table ...
- применимо CREATE TEMPORARY TABLE tmp_table SELECT ... FROM
- не применимо CREATE TEMPORARY TABLE tmp_table LIKE
- удаляется оператором DROP TEMPORARY TABLE tmp_table ...
- размещается в памяти
- не доступна и не вызывает коллизий в других сессиях
- может использовать имя существующей таблицы при этом получает приоритет над ней
///
!!!Временные таблицы
Временные таблицы создаются и существуют только на время сеанса и удаляются автоматически при закрытии сеанса.
Результат запроса можно поместить во временную таблицу:
1 |
CREATE TEMPORARY TABLE table SELECT expr, ... |
Временная таблица удаляется не сразу, что при попытке создания временной таблицы может вызывать ошибку "1050 - Table already exists". Поэтому перед созданием следует ее удалить:
1 |
DROP TEMPORARY TABLE IF EXISTS table[, table]... |
Временные таблицы доступны только внутри сеанса. Временная таблица
///
Процедури та функції
RETURN
LEAVE
LEAVE mark
;
Коментарі у запитах
Предусмотрено три способа комментирования:
- #<comment> отмечает комментарий до конца строки
- -- <comment> с пробелом отмечает комментарий до конца строки
- /*<comment>*/ отмечает многострочный комментарий
Транзакции
Транзакции в MySQL поддерживаются с версии 4.0.11 в соответствии с синтаксисом ANSI SQL:
- START TRANSACTION; - открывает транзакцию
- COMMIT; - успешно завершает транзакцию
- ROLLBACK; - отменяет все действия транзакции
Особенности диалекта MySQL
Ограничение числа записей возвращаемых выражением SELECT реализуется выражением LIMIT, а отступ от начала OFFSET. Пример вывода записей с №41 по №50:
1 |
SELECT * FROM tovar LIMIT 10 OFFSET 40; |
Сведения о данных на сервере MySQL
Приведенные ниже команды позволяют получить сведения о составе баз данных, составе и структуре таблиц, числе записей и объеме хранимых и освобожденных данных:
- SELECT user FROM mysql.user; - список пользователей сервера (требует привилегии чтения системных таблиц)
- SHOW DATABASES; - набор имен баз данных
- SHOW TABLES [FROM <база>]; - набор имен таблиц в базе
- SHOW COLUMNS FROM <таблица> [FROM <база>]; - набор сведений о полях таблицы (Field, Type, Null, Key, Default, Extra)
- DESCRIBE <таблица>; - команда работает аналогично описанной выше
- SHOW CREATE TABLE <таблица>; - набор с sql-выражением "CREATE TABLE..." для воссоздания таблицы
- SHOW INDEX FROM <таблица>; - набор сведений об индексах таблицы
- SHOW GRANTS FOR user [FROM <таблица>]; - привилегии для пользователя
- show grants for 'root'@'%'; - cписок привилегий для пользователя root
- SHOW VARIABLES; - набор имен и значений системных переменных
- SHOW [FULL] PROCESSLIST; - набор сведений о текущих процессах сервера
- SHOW STATUS; - набор имен и значений переменных статистики
- SHOW TABLE STATUS [FROM <база>]; - набор сведений о записях в таблицах (Name, Engine, Version, Row_forman, Rows, Avg_row_length, Data_length, Max_data_length, Inder_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Collation, Checksub, Create_options, Comment, Max_index_length, Temporary)
- OPTIMIZE таблица; - оптимизирует таблицу, освобождая ненужные данные Data_free (впрочем, с таблицами InnoDB возникают нюансы)
Информационная схема
Метаданные MySQL DB, её структура, базы, таблицы, столбцы и их свойства доступны для чтения средствами SQL. Это реализуется через виртуальную базу данных INFORMATION_SCHEMA, в составе которой есть несколько виртуальных таблиц:
- CHARACTER_SETS — таблица доступных наборов символов
- COLLATIONS — таблица доступных сравнений
- COLUMNS — таблица всех столбцов во всех таблицах базы данных с полным описанием типа, кодировки, ключа, икремента, умолчания и т.п.
- ENGINES — таблица типов хранилищ
- PROCESSLIST — таблица текущих процессов
- STATISTICS — таблица всех индексов во всех таблицах базы данных
- TABLES — список всех таблиц базы данных
- и возможны другие...
Запросы к INFORMATION_SCHEMA учитывают права владения и доступа, и отображают не все данные, а только те, которые доступны пользователю. Пример запроса использования физического размера данных таблиц во всех базах в порядке убывания размера (для профилактических работ):
1 2 3 4 |
SELECT table_schema, table_name, data_length FROM information_schema.tables WHERE table_type='BASE TABLE' ORDER BY data_length DESC |
Кодировка строковых значений
Параметры кодировки DB можно получить запросом:
1 |
SHOW VARIABLES LIKE "character%" |
При начальном создании DB и после при подключении к DB может возникнуть вопрос о кодировке. По умолчанию предлагается utf8_general_ci. Выбранная кодировка распространяется на все таблицы БД, что влияет на то, как будут обрабатываться данные в запросах, например, может не учитываться регистр или, не сохраняются некоторые символы локализаций. Для большинства современных проектов рекомендуется использовать кодировку, относящуюся к utf8. Сопоставления utf8 являются 3-ех байтными, что обозначается как mb3 или не указывается. Обычная utf8 имеет специфичные ограничения MySQL, которые не позволяют использовать символы выше 0xFFFD.
Для старых приложений возможно стоит использовать utf8_general_ci, для новых – utf8mb4_general_ci, utf8mb4_unicode_ci или utf8mb4_0900_ai_ci. Предпочтительным вариантом является не general, а unicode. Отличаются они тем, что utf8mb4_general_ci немного быстрее при выполнении сортировки, но могут возникать проблемы с сортировкой для некоторых языков, в то время как utf8mb4_unicode_ci не имеет подобного недостатка.
Если для таблицы установлена сопоставление general, и сортировка в запросе выполняется не совсем правильно, то можно задать правильное сопоставление, используя COLLATE:
1 |
...ORDER BY name COLLATE utf8_unicode_ci |
Расшифровка названия кодировок на примере utf8mb4_0900_ai_ci:
- utf8 обозначает кодировку;
- mb4 обозначает версию или сколько байт используется в обработке данных для одного символа. Если не указано, то обычно подразумевается mb3;
- 0900 обозначает версию алгоритма сопоставления Unicode (UCA), на которой базируется сопоставление. Если не указано, то обычно подразумевается версия 4.0.0;
- ai обозначает нечувствительность к диакритическим знакам (например, древнегреческие ᾱ, ᾰ). Если не указано, подразумевается ai или as в зависимости от следующей части в имени сравнения, то есть ai для ci и as для cs;
- ci обозначает нечувствительность к регистру, означает, что не будет разницы между строчными и заглавными символами в запросах к БД. Существуют также версии cs, которые являются чувствительными к регистру.
Случаи ошибок в кодировке
При написании программного кода может возникать ошибка вида: Unknown collation: 'utf8mb4_0900_ai_ci' (Неизвестное сопоставление: 'utf8mb4_0900_ai_ci'). Это в большинстве случае означает отсутствие требуемой кодировки на сервере баз данных. Например, utf8mb4_0900_ai_ci – это новое сопоставление, доступное только начиная с MySQL 8.0. Также ошибка может появиться в случае применения кодировки, предназначенной для MySQL в другой СУБД, например, в MariaDB. Наборы кодировок различаются от версии к версии, а также для разных СУБД.
Подсистемы низкого уровня MyISAM и InnoDB
Подсистема низкого уровня MySQL реализует файловое представление данных, которыми оперирует MySQL.
Подсистема InnoDB
- входит в стандартные сборки с версии 4.0, а с версии 5.5 используется по умолчанию
- поддерживает механизм транзакций и внешних ключей
- по умолчанию хранит базы в больших совместно используемых файлах, что позволяет использовать постраничный кэш страниц базы данных
- формат данных InnoDB обеспечивает надежное хранение данных за счет транзакционности и блокировки данных на уровне строки
Подсистема MyISAM
- использовалась по умолчанию до версии 5.5
- для каждой таблицы создается один файл данных
- имеет возможности по сжатию данных и созданию полнотекстовых индексов
- не поддерживает транзакции и поэтому не является устойчивой к сбоям
Индексы
В MySQL существует два вида реализации индексов HASH и BTREE, которые принципиально отлчиаются.
Индекс Hash
- представляет собой результат функции по ключу
- не видит растояния до ближайших элементов
- не может использоваться для операций диапазонов > и <
- сравнивание ключа целиком
- линейный при индексировании
- О(1) при вытаскивании значения
Индекс BTREE
- индекс индексов
- сбалансированное дерево в листьях которого гаранируется одинаковое количество предков
- поиск по диапазонам, =, >, >=, <, <=, or BETWEEN operators
- для поиска можно использовать префикс ключа
- может быть использован для сравнения LIKE, если аргумент LIKE является постоянной строкой, которая не начинается с шаблонного символа