# SQL # Тестовые данные и общая информация Перейдите по [ссылке](https://dev.mysql.com/doc/index-other.html) загрузите “sakila database” в разделе Example Databases. ```bash wget https://downloads.mysql.com/docs/sakila-db.zip unzip sakila-db.zip MariaDB [sakila]> source /home/kali/sql/sakila-db/sakila-schema.sql MariaDB [sakila]> source /home/kali/sql/sakila-db/sakila-data.sql ``` - К некоторым БД нельзя отправить запрос без from. Поэтому таблица dual со столбцом dummy: SELECT now() FROM dual; - Можно подключить вывод отсета в xml: mysql -u lrngsql -p --xml bank - При соединении с БД возвращается id Welcome to the MySQL monitor. Commands end with ; or \\g. Your MYSQL connection id is 11 Server version: 8.0.15 MySQL Community Server - GPL - В операциях сравнения использовать is null. Значение может быть null, но оно не может быть равно null - Если столбец autoincrement not null, то 0 = NULL = value+1 # Типы данных и наборы символов **Наборы символов:** Список установленных наборов ``` SHOW CHARACTER SET; ``` По умолчанию utf 8mb4 varchar(20) character set latinl : при создании таблицы другой набор набор по-умолчанию для всей таблицы ``` create database european_sales character set latinl; ``` **Общая информация** - M максимальное количество. Для целых - кол-во цифр, для дробных - общее кол-во цифр, для строк макс. кол-во символов - D кол-во цифр после запятой, должно быть не больше M-2 - fsp применимо к дате/времени, определяет точность (кол-во дробной части секунды) - при strict mode перебор значения вызывает исключение, при restrictive - максимально возможное значение
Тип | Доп. информация |
char(255 символов) varchar(65535) | char(20) 20 символов char фиксировано, при хранении добавляет справа PAD символы |
tinytext(255) text(65535) mediumtext(16 млн) longtext(4 млр) | если больше, то обрежутся конечные пробелы считаются при сортировке первые 1024 байта, но можно увеличить tinytext, text - можно не использовать |
tinyint(1) smallint(2) mediumint int bigint(8) | указано кол-во байтов при создании указывается визуальное кол-во цифр+один символ на знак ```sql CREATE TABLE employees (salary INTEGER(5) UNSIGNED); ``` int максимум 10, поэтому это преобразуется в int(11) ```sql CREATE TABLE employees (id INT(255)); ``` ZEROFILL заполняет 0 слева. ```sql CREATE TABLE documents (document_no INT(5) ZEROFILL); ``` |
DECIMAL = NUMERIC | фиксированное кол-во цифр. Равно int с коэффициентом деления. Первая цифра - общее кол-во цифр (M), вторая - кол-во цифр после запятой. Знак неявно добавляется. Т.е. от -9.99 до 9.99 ```sql CREATE TABLE taxes (tax_rate DECIMAL(3, 2)); ``` M < 65, по-умолчанию 10 при переборе ошибка, при выходе за пределы точности - округление по математическим правилам способ вывода больших чисел с игнором младших цифр ```sql select truncate(tax_rate * 1000, 0) from taxes; ``` в агрегатных функциях работает общее ограничение на 64 цифры, вычисление точное. |
float, double | приближенные числа отображаются как были внесены, но в агрегатных функциях могут появиться неточности ```sql CREATE TABLE typed_numbers( id TINYINT, float_values FLOAT, decimal_values DECIMAL(3, 2)); INSERT INTO typed_numbers VALUES (1, 1.1, 1.1), (2, 1.1, 1.1), (3, 1.1, 1.1); SELECT * FROM typed_numbers; | id | float_values | decimal_values | +------+--------------+------------------+ | 1 | 1.1 | 1.10 | | 2 | 1.1 | 1.10 | | 3 | 1.1 | 1.10 | SELECT SUM(float_values), SUM(decimal_values) FROM typed_numbers; | SUM(float_values) | SUM(decimal_values) | +--------------------+---------------------+ | 3.3000000715255737 | 3.30 | ``` Нельзя напрямую сравнивать в агрегатах, нужно сравнивать разницу ABS(v1 - v2) > 0.1 ```sql SELECT id, SUM(col1) as v1, SUM(col2) as v2 FROM temp GROUP BY id HAVING v1 <> v2; ``` нельзя, внешне одинаковые числа будут разными |
битовый тип | column\_name BIT(M) ``` CREATE TABLE bit_values (val BIT(7)); INSERT INTO bit_values VALUES(b'1011'); - равно VALUES(b'0001011'), в этой нотации b и B равны. INSERT INTO bit_values VALUES(0b1011); INSERT INTO bit_values VALUES(2); - автоматическое преобразование числа в битовую маску ``` select выводит в виде 0x0A select bin(...) - без 0 слева select lpad(bin(...), 7, '0') - привычный вид с 0 слева |
date datetime timestamp year time | 00-00-0000 вместо NULL, не отображается ```sql %а Краткое имя дня недели - Sun, Mon, ... %b Краткое имя месяца — Jan, Feb, ... %с Числовое значение месяца (0..11) %d Числовое значение дня месяца (00..31) %f Число микросекунд (000000..999999) %Н Час дня в 24-часовом формате (00..23) %h Час дня в 12-часовом формате (01..12) %i Минуты в часе (00..59) %j День года (001..366) %М Полное имя месяца (January..December) %m Числовое значение месяца %р AM или РМ %s Число секунд (00..59) %W Полное имя дня недели (Sunday..Saturday) %w Числовое значение дня недели (0=Sunday..6=Saturday) %Y Значение года (четыре цифры) ``` timestamp зависит от time\_zone ```sql CREATE TABLE datetime_temp(ts TIMESTAMP, dt DATETIME); INSERT INTO datetime_temp VALUES(NOW(), NOW()); SELECT ts, dt FROM datetime_temp; | 2017-10-14 18:10:25 | 2017-10-14 18:10:25 | SET time_zone = '+03:00'; SELECT ts, dt FROM datetime_temp; | 2017-10-14 21:10:25 | 2017-10-14 18:10:25 | ``` Если сервер MAXDB mode, TIMESTAMP = DATETIME column\_name TIME; ``` 838:59:59 to 838:59:59 + 6 цифр миллисекунд некорректное в 00:00:00, нельзя отличить от корректно добавленного 00:00:00 ``` |
select | Определяет,какие столбцы следует включить в результирующий набор запроса |
from | Определяет таблицы, из которых следует выбирать данные, а также таблицы, которые должны быть соединены |
where | Отсеивает ненужные данные |
group by | Используется для группировки строк по общим значениям столбцов |
having | Отсеивает ненужные данные |
order by | Сортирует строки окончательного результирующего набора по одному или нескольким столбцам |
%M | Полное имя месяца (January..December) |
%m | Числовое значение месяца |
%d | Числовое значение дня месяца (00..31) |
%j | День года (001..366) |
%W | Полное имя дня недели (Sunday.Saturday) |
%Y | Значение года (четыре цифры) |
%y | Значение года (две цифры) |
%H | Час дня в 24-часовом формате (00..23) |
%h | Час дня в 12-часовом формате (01..12) |
%i | Минуты в часе (00..59) |
%s | Число секунд (00..59) |
%f | Число микросекунд (000000..999999) |
%p | AM или РМ |
%a | Краткое имя дня недели — Sun, Mon,... |
%b | Краткое имя месяца — Jan,Feb,... |
Функция | Описание |
RANK | Аналогична функции DENSE\_RANK() за исключением того, что в последовательности ранжированных значений есть пробелы, когда две или более строки имеют одинаковый ранг. |
DENSE\_RANK | Присваивает ранг каждой строке в своем разделе на основе предложения ORDER BY. Он присваивает одинаковый ранг строкам с одинаковыми значениями. Если две или более строки имеют одинаковый ранг, то в последовательности ранжированных значений не будет пробелов. |
ROW\_NUMBER | Назначает последовательное целое число каждой строке в своем разделе |
CUME\_DIST | Вычисляет совокупное распределение значения в наборе значений. |
FIRST\_VALUE | Возвращает значение указанного выражения относительно первой строки в рамке окна. |
LAG | Возвращает значение N-й строки перед текущей строкой в разделе. Возвращает NULL, если предшествующей строки не существует. |
LAST\_VALUE | Возвращает значение указанного выражения относительно последней строки в рамке окна. |
LEAD | Возвращает значение N-й строки после текущей строки в разделе. Возвращает NULL, если никакой последующей строки не существует. |
NTH\_VALUE | Возвращает значение аргумента из N-й строки рамки окна |
NTILE | Распределяет строки для каждого раздела окна в указанное количество ранжированных групп. |
PERCENT\_RANK | Вычисляет процентильный ранг строки в разделе или наборе результатов |
MylSAM | Нетранзакционный, табличная блокировка, скоростной доступ на чтение с малым кол-вом записи |
MEMORY | Нетранзакционный, табличная блокировка, для скоростного кэша, таблицы в памяти, при нехватке своп на диск, не поддерживает TEXT BLOB |
CSV | Транзакционный, данные в файлах с данными с разделением запятыми, обмен данными. Нет индексов. |
InnoDB | Транзакционный, блокировка на уровне строки |
Merge | Специальный механизм, используемый для создания нескольких идентичных таблиц MylSAM в виде единой таблицы (так называемое разбиение таблицы) |
Archive | хранение больших количеств неиндексированных данных, в основном для архивных целей. Не поддерживает DELETE, UPDATE. |
Blackhole | для репликации |
federated | Одна база на нескольких серверах. Создает клиентское соединение и выполняет еще раз запрос, получает данные. По-умолчанию выключен. |
NDB cluster |
Название | Назначение и создание |
Простой индекс | ускоряет поиск данных ``` ALTER TABLE customer ADD INDEX idx email (email); ``` |
Уникальный индекс | запрещает два элемента с одинаковым значением ``` ALTER TABLE customer ADD UNIQUE idx_email (email); ``` |
Многостолбцовый индекс | важна последовательность - эффекта не будет при поиске только по последнему столбцу ``` ALTER TABLE customer ADD INDEX idx_full_name (last_name, first_name); ``` |
.schemata | базы данных |
.tables | информация обо всех таблицах |
.views | о представлениях |
.columns | о столбцах |
.statistics | об индексах |
.table\_constraints | об ограничениях |
.user\_privilege | Кто имеет привилегии для различных объектов схемы |
.schema\_privileges | Кто имеет привилегии для различных баз данных |
.table\_privileges | Кто имеет привилегии для различных таблиц |
.column\_privileges | Кто имеет привилегии для различных столбцов таблиц |
.routines | Сохраненные подпрограммы (процедуры и функции) |
.triggers | Триггеры таблиц |
.plugins | Подключаемые модули сервера |
.engines | Доступные механизмы хранения |
.partitions | Разбиения таблиц |
.events | Запланированные события |
.processlist | Выполняющиеся процессы |
.referential\_constraints | Внешние ключи |
.parameters | Параметры хранимых процедур и функций |
.profiling | Информация о профилях пользователей |
Уровень изолированности | Черновое чтение | Неповторяющееся чтение | Фантомное чтение | Блокировка чтения |
READ UNCOMMITTED | Да | Да | Да | Нет |
READ COMMITTED | Нет | Да | Да | Нет |
REPEATABLE READ | Нет | Нет | Да | Нет |
SERIALIZABLE | Нет | Нет | Нет | Да |
NAME | Название инструмента |
ENABLED | Включен или нет |
TIMED | Планировщик. Если disabled, работать не будет |
ALTER | Менять структуру таблицы или БД |
CREATE | Создавать новые БД и таблицы |
DELETE | Удалять строки в таблице |
INSERT | Добавлять строки в таблицу |
SELECT | Читать данные из таблицы |
UPDATE | Обновлять данные в таблице |
DROP | Удалять БД |
ALL PRIVILEGES | все, кроме GRANT; |
USAGE PRIVILEGES | никаких привилегий; |
FILE | разрешает читать файлы на сервере; |
INDEX | создавать индексы для таблиц; |
DROP | удалять таблицы; |
EVENT | обработка событий; |
TRIGGER | создание триггеров. |
GRANT | изменять права пользователей; |
SUPER | суперпользователь; |
PROCESS | получение информации о состоянии MySQL; |
RELOAD | позволяет перезагружать таблицы привилегий; |
SHUTDOWN | позволяет отключать или перезапускать базу данных; |
SHOW DATABASES | просмотр списка баз данных; |
LOCK TABLES | блокирование таблиц при использовании SELECT; |
REFERENCES | создание внешних ключей для связывания таблиц; |
CREATE USER | создание пользователей; |