Конспект виразів структурованих запитів діалектів MySQL та MariaDB

//

  • офіційний 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

ГРАНДИОЗНЫЙ РЕФАКТОРИНГ!

#temporary-table

Зміст

Справочник !!! VPN //www.mysql.ru/docs/mysql-man-4.0-ru/reference.html

!!! MySQL — Использование переменных в запросе https://habr.com/ru/post/442706/

  • VERSION(); возвращает версию

!!! текущее значение счетчика авто-инкремента таблицы:

Базовый синтаксис

Комментарии в запросах

Предусмотрено три способа комментирования:

  • #<comment> отмечает комментарий до конца строки
  • -- <comment> с пробелом отмечает комментарий до конца строки
  • /*<comment>*/ отмечает многострочный комментарий

Литералы

Числовой литерал

/

  • Null
  • 0xHHHH

Строковый и бинарный литерал

  • '...'
  • "..."
  • N'...', n'...', _utf8'...' эквивалентные способы определения литерала в национальной кодировке
  • X'hh...' представляет бинарный литерал заданный hex-последоватльностью
  • интродьюсер (introducer) специфицирует кодировку литерала Character Set Introducers
    • _latin1, _latin2
    • _ascii
    • _utf8mb4
    • _ucs2
  • 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:

Условие 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, где строковый шаблон может содержать подстановочные символы:

  • % - последовательность любых символов любой длины
  • _ - один любой символ

Подстановочный символ в шаблоне становится обычным символом с помощью маскирования \% и \_

Сравнение шаблоном регулярного выражения

Операторы синонимы RLIKE и REGEXP применяют к значению шаблон регулярного выражения, используя функцию REGEXP_LIKE(). Результат оператора можно использовать в условии, в результирующих данных, для группировки, сортировки и других sql-выражениях.

Пример условия отбора значения, приводимого к числовому значению:

Пример условия отбора сообщений без кириллических слов, определяемых как последовательность трех и более кириллических символов:

/

Сравнение на сходство

Сопоставление строковых значений, имеющих общность - многогранная тема...
Сравнение строк на "сходное звучание" реализует оператор
expr1 SOUNDS LIKE expr2 на примере:

/

Условия с подзапросом

//

  • 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:

В MySQL 8.0 добавлена возможность использовать псевдоним new:

Добавление записей в связанные таблицы

Типичная задача: в главную таблицу headTable с автоинкрементируемый ключом ID необходимо добавить новую запись, и ID новой записи использовать для записи в подчиненную таблицу subTable. Пакетный запрос выполнить вставку записей в две таблицы и вернет выборку с ключом заголовочной записи:

///

!!! к SELECT

Нумерация в запросе

Для нумерации по порядку всех записей запроса MySQL не предоставляет штатных средства (в отличии от MS SQL, где доступна функция row_number()), тем не менее нумерация реализуется с помощь переменной и выражения инкремента в запросе:

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] - будет подсчитано число уникальных наборов всех выражений
  • 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]) - разделитель наборов при агрегации, по умолчанию ,

  • 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). Пример счетчика грузов и сумматора веса с условием:

 

О

!!! Декомпозиция и повторное использование выражений

Конструкция WITH позволяет отдельно описать sql-выражение, которое можно использовать в другом sql-выражение один или несколько раз:

!!! 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 ...
  • размещается в памяти
  • не доступна и не вызывает коллизий в других сессиях
  • может использовать имя существующей таблицы при этом получает приоритет над ней

///

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

Временные таблицы создаются и существуют только на время сеанса и удаляются автоматически при закрытии сеанса.

Результат запроса можно поместить во временную таблицу:

Временная таблица удаляется не сразу, что при попытке создания временной таблицы может вызывать ошибку "1050 - Table already exists". Поэтому перед созданием следует ее удалить:

Временные таблицы доступны только внутри сеанса. Временная таблица

///

Процедури та функції

RETURN

LEAVE

LEAVE mark;

Коментарі у запитах

Предусмотрено три способа комментирования:

  • #<comment> отмечает комментарий до конца строки
  • -- <comment> с пробелом отмечает комментарий до конца строки
  • /*<comment>*/ отмечает многострочный комментарий

Транзакции

Транзакции в MySQL поддерживаются с версии 4.0.11 в соответствии с синтаксисом ANSI SQL:

  • START TRANSACTION; - открывает транзакцию
  • COMMIT; - успешно завершает транзакцию
  • ROLLBACK; - отменяет все действия транзакции

Особенности диалекта MySQL

Ограничение числа записей возвращаемых выражением SELECT реализуется выражением LIMIT, а отступ от начала OFFSET. Пример вывода записей с №41 по №50:

Сведения о данных на сервере 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 учитывают права владения и доступа, и отображают не все данные, а только те, которые доступны пользователю. Пример запроса использования физического размера данных таблиц во всех базах в порядке убывания размера (для профилактических работ):

Кодировка строковых значений

Параметры кодировки DB можно получить запросом:

При начальном создании 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:

Расшифровка названия кодировок на примере 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. Наборы кодировок различаются от версии к версии, а также для разных СУБД.

Источник: https://eqsash.com/articles/kakuyu-kodirovku-vybrat-v-mysql-utf8-ili-utf8mb4-utf8mb4-general-ci-utf8mb4-unicode-ci-ili-utf8mb4-0900-ai-ci-chem-oni-otlichayutsya-kak-rasshifrovyvayutsya-i-vozmozhnye-oshibki

Подсистемы низкого уровня MyISAM и InnoDB

Подсистема низкого уровня MySQL реализует файловое представление данных, которыми оперирует MySQL.

Подсистема InnoDB

  • входит в стандартные сборки с версии 4.0, а с версии 5.5 используется по умолчанию
  • поддерживает механизм транзакций и внешних ключей
  • по умолчанию хранит базы в больших совместно используемых файлах, что позволяет использовать постраничный кэш страниц базы данных
  • формат данных InnoDB обеспечивает надежное хранение данных за счет транзакционности и блокировки данных на уровне строки

Подсистема MyISAM

  • использовалась по умолчанию до версии 5.5
  • для каждой таблицы создается один файл данных
  • имеет возможности по сжатию данных и созданию полнотекстовых индексов
  • не поддерживает транзакции и поэтому не является устойчивой к сбоям

 

Индексы

В MySQL существует два вида реализации индексов HASH и BTREE, которые принципиально отлчиаются.

Индекс Hash

  • представляет собой результат функции по ключу
  • не видит растояния до ближайших элементов
  • не может использоваться для операций диапазонов > и <
  • сравнивание ключа целиком
  • линейный при индексировании
  • О(1) при вытаскивании значения

Индекс BTREE

  • индекс индексов
  • сбалансированное дерево в листьях которого гаранируется одинаковое количество предков
  • поиск по диапазонам, =, >, >=, <, <=, or BETWEEN operators
  • для поиска можно использовать префикс ключа
  • может быть использован для сравнения LIKE, если аргумент LIKE является постоянной строкой, которая не начинается с шаблонного символа

/OVER (PARTITION BY ... ORDER BY ...)

Джерела

Leave a Reply