Шпаргалка по MySQL

—РАБОТА С БАЗОЙ ДАННЫХ MYSQL

———Добавление строки————

ALTER TABLE  `progect_info` ADD  `times` INT( 4 ) NOT NULL ;

———CREATE———

CREATE DATABASE `my_db` CHARACTER SET utf8 COLLATE utf8_general_ci;

———-DROP———-

DROP TABLE IF EXISTS `dumawp_nf3_action_meta`;

———-CREATE———-

CREATE TABLE `dumawp_nf3_action_meta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `key` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` longtext COLLATE utf8mb4_unicode_ci,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

———-INSERT———-

INSERT INTO `dumawp_nf3_action_meta` (`id`, `parent_id`, `key`, `value`) VALUES (1, 1, 'label', 'Store Submission');

———-SELECT————

SELECT * FROM table WHERE id IS NOT NULL;
SELECT * FROM table WHERE id IS NULL;
SELECT name FROM table WHERE id = 1;
SELECT surname, name FROM table WHERE id > 1;

———-DISTINCT (группировка одинаковых значений)————

SELECT DISTINCT surname, name FROM table WHERE id > 1;

———-ORDER BY (сортировка)————

SELECT name FROM table ORDER BY id ASC;
SELECT name FROM table ORDER BY id DESC;
SELECT name FROM table WHERE id < 100 ORDER BY id DESC;

———-LIMIT (ограничение количества выдачи)————

SELECT * FROM table LIMIT 2; //первые две записи
SELECT * FROM table WHERE id > 10 LIMIT 2; //первые две записи после условия

———-DELETE————

DELETE FROM table WHERE id = 5;

———-UPDATE————

UPDATE table SET name = "Наталья Ивановна" WHERE id IN ( 3, 5);
UPDATE table SET name = REPLASE(name, 'old name', 'new name');
UPDATE table SET name = REPLASE(name, 'old name', 'new name') WHERE surname = 'home' OR family = 'new-family';

———ВНЕШНИЙ КЛЮЧ——-
— связывает таблицы

——-СОСТАВНОЙ ПЕРВИЧНЫЙ КЛЮЧ———
— состоит из 2-х и более первичных ключей

——-ТИП ДАННЫХ ДЛЯ ДЕНЕГ——

DECIMAL(10,2)

тип данных для денежных единиц. 10 — максимальная длина строки, 2 — числел после запятой

———FOREIGN KEY———

ALTER TABLE `shop`.`product` 
ADD CONSTRAINT `fk_brand_product` 
FOREIGN KEY (`brand_id`) 
REFERENCES `shop`.`brand` (`id`)
ON DELETE NO ACTION (NO ACTION - не удалять если есть связи, CASCADE - удалять связанные строки при удалении главной)
ON UPDATE NO ACTION;
-- если есть строки противоречащие правилу, запрос не будет произведен.

———INNER JOIN———
———объединение (в которое обязаны попасть строки из двух таблиц которые соответствуют запросам совпадений)———

SELECT * FROM product INNER JOIN category ON product.category_id = category.id;
SELECT name, price FROM product INNER JOIN category ON product.category_id = category.id;
-- (в выводе не должно быть названия которое находится в двух таблицах, либо уточнять таблицу. например product.id)
SELECT name, price FROM product INNER JOIN category ON product.category_id = category.id WHERE price < 10000;
SELECT product.id, brand.name, product.price FROM product 
	INNER JOIN category ON product.category_id = category.id 
	INNER JOIN brand ON product.brand_id = brand.id 
	INNER JOIN product_type ON product.product_type_id = product_type.id ;
SELECT product.id, brand.name as brand_name, product_type.name as product_type, category.name as category_name, product.price FROM product 
	INNER JOIN category ON product.category_id = category.id 
	INNER JOIN brand ON product.brand_id = brand.id 
	INNER JOIN product_type ON product.product_type_id = product_type.id ;
-- с помощью as мы присваиваем новое имя для вывода значений (для удобства) brand,name as brand_name

———LEFT JOIN———
———объединение (в которое обязана попасть все строки первой/левой таблицы)———

SELECT category.* FROM category
	LEFT JOIN product ON product.category_id = category.id
	WHERE product.id is null;
- -выводим все категории которые не привязаны к продуктам

———RIGHT JOIN———
———объединение (в которое обязана попасть все строки второй/правой таблицы)———

SELECT category.* FROM category
	RIGHT JOIN product ON product.category_id = category.id;

——FULL OUTER JOIN——НЕТ В MYSQL!!!——
———объединение (в которое обязана попасть все строки первой и второй таблицы)———

———-UNION———
—склейка запросов——

SELECT * FROM `order`
	LEFT JOIN order_products ON order_products.order_id = `order`.id
	LEFT JOIN product ON order_products.product_id = product.id

UNION

SELECT * FROM `order`
	INNER JOIN order_products ON order_products.order_id = `order`.id
	RIGHT JOIN products ON order_products.product_id = product.id
	WHERE `order`.id is null;

——Агрегирующие функции——
—внутренние функции MySQL—

SELECT count(*) FROM products; - количество ответов (строк)
SELECT sum(price) FROM products; - суммирует все результаты ячейки price в выдаче
SELECT max(price) FROM products; - максимальное значение ячейки price в выдаче
SELECT min(price) FROM products; - минимальное значение ячейки price в выдаче
SELECT sum(price), max(price), max(price) FROM products; - все вместе ^
SELECT sum(price) as total_price, max(price) as max_price, max(price) as min_price FROM products; - все вместе ^
-- для удобного вывода присвоим новые имена

———GROUP BY——-
—группирование по значению в яцейке

SELECT `order`.user_name, max(price), sum(`count`) FROM `order`
	INNER JOIN order_products ON order_products.order_id = `order`.id
	INNER JOIN product ON product.id = order_products.product_id
	GROUP BY `order`.user_name;

——LIKE——
—вхождения

SELECT `order`.user_name, max(price), sum(`count`) FROM `order`
	INNER JOIN order_products ON order_products.order_id = `order`.id
	INNER JOIN product ON product.id = order_products.product_id
	WHERE user_name LIKE 'в%'
	GROUP BY `order`.user_name;
-- ячейка user_name со словом начинающимся на букву "в". % - означает любые символы

——-HAVING——-
—для оперирования агрегирующими функциями

SELECT `order`.user_name, max(price), sum(`count`) FROM `order`
	INNER JOIN order_products ON order_products.order_id = `order`.id
	INNER JOIN product ON product.id = order_products.product_id
	GROUP BY `order`.user_name
	HAVING sum(`count`) >= 5;

 

——УСКОРЕНИЕ РАБОТЫ БД——-
— маленькие базы лучше не оптимизировать
— база данных сохраняет значение выдачи для повторной быстрой выдаче
— ячейке по которой производится поиск добавляем INDEX

 

——ТРАНЗАКЦИИ——
— гарантированное выполнение группы команд
— ACID
— Atomicity — Атомарность (выполняются все либо не одной задачи)
— Consistency — Согласованность (данные в базе остаются в согласованном состоянии (например сумарное значение денег при переводу сохраняется))
— Isolation — Изолированность (если транзакция начилась, ей ничего не помешает)
— Durability — Долговечность (сбой системы не изменит транзакции)

START TRANSACTION;
	UPDATE `shop`.`user_bank_account` SET money = money - 100 WHERE id = 1;
	UPDATE `shop`.`user_bank_account` SET money = money + 100 WHERE id = 2;
COMMIT