MySQL - Записки

Актуально для версии 8.4

База данных

SHOW DATABASES; # показать список Баз Данных
CREATE DATABASE example_db DEFAULT CHARSET=utf8; # создание базы данных
DROP DATABASE example_db; # удаление БД (IF EXIST) # если сущуствует
USE example_db; # перейти (использовать) БД

Таблица

SHOW TABLES; # показать список таблиц

DESCRIBE example_table; # показать структуру таблицы

CREATE TABLE `example_table` (
`id` int(11) AUTO_INCREMENT,
`name` varchar(255),
PRIMARY KEY (`id`)
) ENGINE=InnoDB; # создание таблицы с двумя столбцами

CREATE TABLE `users` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `group_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB; # создание таблицы с каскадными связями, удаление строки ведет к удалению связанных строк

TRUNCATE TABLE example_table; # очистка таблицы вместе со значением инкремента
DROP TABLE example_table; # удаление таблицы

Пользователь и привелегии

CREATE USER 'example_user'@'localhost' IDENTIFIED BY 'pass'; # создаем пользователя
DROP USER 'example_user'@'localhost';

SHOW GRANTS; # показать привилегии (права) текущего пользователя
GRANT ALL PRIVILEGES ON example_db.* TO 'example_user'@'localhost'; # даем полные привилегии пользователю на БД
REVOKE ALL PRIVILEGES ON example_db.* FROM 'example_user'@'localhost'; # удалить все привилегии с конкретной БД
FLUSH PRIVILEGES; # Применить изменения

Вставка данных

INSERT INTO example_table VALUES (NULL, 'test_username'); # вставка соблюдением последовательности колонок

INSERT INTO `department` (`group_id`, `name`)
VALUES
(4, 'Гриша'),
(5, 'Иван'); # многострочная вставка с явным указанием столбцов

Выборка данных

#### SELECT - выборка данных
# AS - переименование полей, к примеру `id` AS `Identificator`
# DISTINCT - только уникальные значения

#### WHERE - операторы условий: = | > | < | >= | =< | != | LIKE (% | _) | IN | NOT IN | | BETWEEN | NOT BEETWEN
# AND, OR логические операторы

# (+ | * ..), COUNT(), MIN(), MAX(), AVG(), SUM() - агрегационные функции
# CONCAT(), SUBSTRING(), REPLACE() - стоковые функции
# ABS(), MOD(), ROUND(), FLOOR(), CEIL() - математические функции
# NOW(), WEEKDAY(), DAYOFMONTH(), MONTH() - функции для работы с датой и временем

# JOIN (INNER | LEFT | RIGHT) - присоединение таблицы справа по обоюдному совпадению или с левой или правой стороны 

# GRUOP BY - группировка по
# HAVING - условия после группировки, аналогично where (можно использовать агрегатные функции)

# ORDER BY - сортировка, пример id ASC, name DESC
# LIMIT $ - ограничение на кол-во
# OFFSET $ - сдвиг (отступ) на кол-во

SELECT {fields} WHERE {conditions} {MORE..} FROM example_table; # структура запроса

Проектирование БД

1. Концептуальное проектирование, построение модели предметной области на наиболее высоком уровне абстракции.
Включает в себя описание объектов или понятий предметной области и связей между.

2. Логическое проектирование - создание схемы базы данных.
Включает в себя структурированные объекты, их идентификаторы и направленные связи с их типами.

3. Физическое проектирование
Создание конкретной структуры базы данных - физическая модель хранения и обработки данных.
Включает в себя ограничения объектов базы данных, типов данных, выбор хранилищ, создание индексов и тп.

Транзакция

START TRANSACTION;
UPDATE `price` SET `price`.`sum` = 100 WHERE `price`.`product_id` = 1;
UPDATE `price` SET `price`.`sum` = 200 WHERE `price`.`product_id` = 2;
COMMIT; # выполнение запросов одной нераздельной операцией с вариативной блокировкой

Индексы

ALTER TABLE products ADD INDEX category_id (category_id);

ALTER TABLE products 
ADD INDEX category_id (category_id, price); # мультиколочный

# + скорость поиска / - поддержка индекса на изменениях

назад