MySql

Введение

БД – хранилище информации, с которым можно работать с помощью компьютера.

СУБД – набор программ, с помощью которых управлять созданием и использованием БД.

Основные функции СУБД:

1. управление данными на внешней памяти (на дисках) (как хранятся данные в файловой системе, сколько там файлов, как они представлены);
2. управление данными в оперативной памяти с использованием дискового кэша;
3. журнализация изменений, резервное копирование и восстановление базы данных после сбоев;
4. поддержка языков БД (язык определения БД, язык манипулирования данными).

Реляционные базы данных

• Relation – отношения
• Данные хранятся в таблице
• Таблицы взаимосвязаны друг с другом (между ними присутствуют некоторые отношения)
• Язык SQL

SQL – structured query language – язык структурированных запросов. С его помощью можно создавать, изменять и управлять данными. 

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

Количество столбцов при создании должно быть конечным. Т.е. добавление данных идет по строкам, как бы вниз.

Правила «Нормальные формы»

1. Все элементы внутри ячеек должны быть атомарными, не списками (элемент называется атомарным, если его нельзя разделить на части)

2. Все строки должны быть различными

В каждой таблице БД должен существовать столбец или несколько столбцов, которые идентифицирует строку в БД. Такой столбец называется «Первичный ключ».

Удовлетворение 1 и 2 правилам – соответствует 1-ой Нормальной форме.

3. Любое поле таблицы, не входящее в состав Первичного ключа функционально полно зависит от Первичного ключа. (зависимость только от первичного ключа)

Удовлетворение трём правилам – соответствует 2-ой Нормальной форме.

4. Таблица удовлетворяет 3-ей Нормальной форме, если она удовлетворяет всем предыдущим правилам и любой её не Ключевой атрибут функционально зависит только от первичного ключа. 

Вторичный ключ – значение равное первичному ключу другой таблицы.

Личный вывод: если в столбце есть повторяющиеся элементы, то лучше вынести их в отдельную таблицу и присвоить каждому уникальному элементу идентификатор (первичный ключ).

Создание/удаление базы данных, таблицы

БД в MySql назеваются schema (схемами)

Создать БД: 

Правой кнопкой в «молоке» поля «Navigator» à «Create Schema»
Или на панели нажать кнопку «Create a new schema»

– указываем name «Shop» и кодировку в полях: «Charset/Collation»«utf8_bin»
Нажать «Apply» (применить)

Появляется окошко с кодом:

CREATE SCHEMA `shop` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ;

Нажать «Apply» (применить)

Создать таблицу:

Нажать стрелку выпадающего списка возле созданной БД «shop» à нажать правой кнопкой на «Tables» нажать «Create Table»

Присвоить имя таблице в поле «Table Name» «category» à  стрелочки  à Заполнить «Column Name» «id» (наименование столбца, колонки), указать тип данных в столбце «Datatype» «INT» (числовой) , отметить «Primary key», «Not null», «Auto Increment» по необходимости.

С другими столбцами по аналогии:
«Column Name» «name» à тип данных в столбце «Datatype» (текстовый) «VARCHAR(128)» à чекнуть «Not null»
«Column Name» «discount» à тип данных в столбце «Datatype» (числовой) «TINYINT» à чекнуть «Not null»
Нажать «Apply» (применить)

Появляется окошко с кодом:

CREATE TABLE `shop`.`category` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(128) NOT NULL,
    `discount` TINYINT NOT NULL,
    PRIMARY KEY (`id`));

Нажать «Apply» (применить)

Отобразить содержимое таблицы:

Нажать стрелку выпадающего списка «Tables» à нажать правой кнопкой на «Category» нажать «Select Rows»

Появляется окно таблицы с кодом:

SELECT * FROM ship.category;

Внести изменения в таблицу

Нажать правой кнопкой на «Category» нажать «Alter Table» (изменить таблицу) à стрелочки à Заполнить необходимые поля
Нажать «Apply» (применить)
Появляется окошко с кодом:

ALTER TABLE `ship`.`category`
-- синтаксис: [ИЗМЕНИТЬ ТАБЛИЦУ] `наименование БД(схемы)`.`наименование таблицы`
ADD COLUMN `alias_name` VARCHAR(128) NULL AFTER `discount`; -- синтаксис: [ДОБАВИТЬ КОЛОНКУ] `наименование колонки`  [ТИП ДАННЫХ] [МОЖЕТ БЫТЬ ПУСТЫМ] [ДОБАВИТЬ ПОСЛЕ СТОЛБЦА] `наименование столбца`

Нажать «Apply» (применить)

Удаление

Нажать правой кнопкой на «Category» нажать «Drop Table»
Появляется окошко с вопросом: удалить или показать код
DROP TABLE `shop`.`category`;
Удаление БД à нажать правой кнопкой на БД «shop» нажать Drop Schema
Появляется окошко с вопросом: удалить или показать код
Аналогично удаление БД à 

«DROP SCHEMA»
DROP DATABASE `shop`;

Примечание: DATABASE и Schema – одно и тоже.

Работа через консоль:

mysql> show databases;
mysql> use shop;
mysql> show tables;
mysql> CREATE TABLE `shop`.`category` (  `id` INT NOT NULL,    `name` VARCHAR(128) NOT NULL,  `discount` TINYINT(128) NOT NULL,  PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
mysql> ALTER TABLE `shop`.`category`
ADD COLUMN `alias_name` VARCHAR(128) NULL AFTER `discount`;
mysql> show columns;

Заполнение таблиц

Создание доп. таблиц:

Нажать стрелку выпадающего списка возле созданной БД «shop» à нажать правой кнопкой на «Tables» нажать «Create Table»
Присвоить имя таблице в поле «Table Name» «brand» à  стрелочки  à Заполнить «Column Name» «id» (наименование столбца, колонки), указать тип данных в столбце «Datatype» (числовой) «INT», отметить «Primary key», «Not null», «Auto Increment» по необходимости.
С другими столбцами по аналогии:
«Column Name» «name» à тип данных в столбце «Datatype» (текстовый) «VARCHAR(128)» à чекнуть «Not null»
Нажать «Apply» (применить)

Появляется окошко с кодом:

CREATE TABLE `shop`.`brand` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(128) NOT NULL,
PRIMARY KEY (`id`));
Нажать «Apply» (применить)

ПК в «молоке» поля «Navigator» à «Refresh all» (обновить всё)
Появилась новая таблица «brand»
Нажать стрелку выпадающего списка возле созданной БД «shop» à нажать правой кнопкой на «Tables» нажать «Create Table»
Присвоить имя таблице в поле «Table Name» «product_type» à  стрелочки  à Заполнить «Column Name» «id» (наименование столбца, колонки), указать тип данных в столбце «Datatype» (числовой) «INT», отметить «Primary key», «Not null», «Auto Increment» по необходимости.
С другими столбцами по аналогии:
«Column Name» «name» à тип данных в столбце «Datatype» (текстовый) «VARCHAR(128)» à чекнуть «Not null»
Нажать «Apply» (применить)

Появляется окошко с кодом:

CREATE TABLE `shop`.`product_type` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(128) NOT NULL,
PRIMARY KEY (`id`));
Нажать «Apply» (применить)

ПК в «молоке» поля «Navigator» à «Refresh all» (обновить всё)
Появилась новая таблица «product_type»

Теперь необходимо заполнить таблицы данными.

Отобразить содержимое таблицы:  

Нажать стрелку выпадающего списка «Tables» à нажать правой кнопкой на «category» нажать «Select Rows»

Появляется окно таблицы с кодом:

SELECT * FROM shop.category;

Для изменения данных в ячейках через интерфейс нажать кнопку «Edit current row»

(редактировать текущую строчку), заполнить значениями и нажать «Apply» (применить)

Или написать код:

use shop;
INSERT INTO category (name, discount) VALUES ('Женская одежда', '5');
INSERT INTO category (name, discount) VALUES ('Мужская одежда', '0');
INSERT INTO category (name, discount, alias_name) VALUES ('Женская обувь', 10, NULL);
INSERT INTO category (name, discount, alias_name) VALUES ('Мужская обувь', 15, 'man''s shoes');

Синтаксис:
[INSERT INTO - внести в] [название таблицы] [имена столбцов] [VALUES] [значения];

Отобразить содержимое таблицы:  

SELECT * FROM shop.category;

Чтобы автоматически, для каждой следующей строки, увеличивать порядковый номер строки «id»: установить галочку в поле «AI» (Auto Increment)
Код:
ALTER TABLE `shop`.`category`
CHANGE COLUMN `id` `id` INT NOT NULL AUTO_INCREMENT ;

Добавим ещё одну строку:

use shop;
INSERT INTO category (name, discount) VALUES ('Шляпы', 0);

Заполнить данными таблицу «Бренды», используя графический интерфейс:

Правой кнопкой на таблицу «brand» нажать «Alter Table» – установить флаг «AI» (Auto Increment) на колонку «id»
Нажать «Apply» (применить)

Код:
ALTER TABLE `shop`.`brand`
CHANGE COLUMN `id` `id` INT NOT NULL AUTO_INCREMENT ;
Редактирование значений таблицы «brand» – «Select Rows» – заполняем.
Нажать «Apply» (применить)

Появляется окошко с кодом:
INSERT INTO `shop`.`brand` (`name`) VALUES ('Marc O\'Polo');
INSERT INTO `shop`.`brand` (`name`) VALUES ('ALCOTT');
INSERT INTO `shop`.`brand` (`name`) VALUES ('GUESS');
Нажать «Apply» (применить)

Заполнить данными таблицу «Тип товара», используя SQL-команды.

Редактирование значений таблицы:

Правой кнопкой на таблицу «product_type» – «Alter Table» – установить флаг «AI» (Auto Increment) на колонку «id»

Код:
USE shop;
INSERT INTO product_type (name) VALUES ('Платье');
INSERT INTO product_type (name) VALUES ('Футболка');

Отобразить содержимое таблицы:  
Правой кнопкой на таблицу «product_type» – «Select Rows»
Код: SELECT * FROM shop.product_type;

Получение данных из таблиц базы данных
SELECT – отобразить (выбрать, отобрать, создать выборку, показать выборку)
WHERE – условие, по умолчанию можно использовать только первичный ключ
DISTINCT – уникальное значение (отличающийся, определенный, особый)
ORDER BY (ASC, DESC) – сортировка (в прямом и обратном порядке)
LIMIT – количество первых строк (ограничение, граница)

Получить данные обо всех категориях товара:
код:
use shop;
-- == SELECT == --
-- вывести все категории товара
SELECT * FROM category;

-- == WHERE == --
-- вывести категории товаров с идентификатором, равным 3
SELECT * FROM category WHERE id = 3;

-- вывести категории товаров, у которых скидка не равна 0
SELECT * FROM category WHERE discount <> 0;

-- вывести категории товаров, у которых скидка больше 5
SELECT * FROM category WHERE discount > 5;

-- вывести категории товаров, у которых скидка больше 5 и меньше 15
SELECT * FROM category WHERE (discount > 5) and (discount < 15);

-- вывести категории товаров, у которых скидка меньше 5 или больше или равен 10
SELECT * FROM category WHERE (discount < 5) or (discount >= 10);

-- вывести категории товаров, у которых скидка не меньше 5
SELECT * FROM category WHERE NOT (discount < 5);

-- вывести категории товаров, у которых есть псевдоним
SELECT * FROM category WHERE alias_name IS NOT NULL;

-- вывести категории товаров, у которых нет псевдонима
SELECT * FROM category WHERE alias_name IS NULL;

-- == select <столбец> == --
-- отобразить названия всех категорий товара
SELECT name FROM category;

-- отобразить названия и скидки
SELECT name, discount FROM category;
SELECT discount, name  FROM category;

-- отобразить все скидки
SELECT discount FROM category;

-- DISTINCT
-- вывести все уникальные значения скидок
SELECT DISTINCT discount FROM category;

-- == ORDER BY == -- сортировка
-- вывести все категории товаров, и отсортировать их по размеру скидки
SELECT * FROM category ORDER BY discount; -- ASC прямой порядок, по умолчанию

-- вывести все категории товаров, и отсортировать их по размеру скидки в обратном порядке
SELECT * FROM category ORDER BY discount DESC; -- DESC обратный порядок (по убыванию)

-- вывести все категории товаров с ненулевой скидкой, и отсортировать их по размеру скидки в обратном порядке
SELECT * FROM category WHERE discount <> 0 ORDER BY discount DESC; -- ORDER BY команда должна идти в конце, порядок важен

-- == limit == -- кол-во первых строк
-- вывести первые 2 категории товаров
SELECT * FROM category WHERE id < 3;

-- вывести первые 2 категории товаров
SELECT * FROM category LIMIT 2;

-- вывести первые 2 категории товаров со скидкой не равной нулю
SELECT * FROM category WHERE discount <> 0 LIMIT 2;

USE shop; -- чтобы начать работу с базой данных «shop»

-- отобразить название бренда с идентификатором 3
SELECT * FROM brand WHERE id = 3; -- выйдут все столбцы таблицы «brand», где «id» равно «3»

SELECT name FROM brand WHERE id = 3; -- выйдет только столбец «name» таблицы «brand», где «id» равно «3»

-- отобразить первые 2 типа товара
SELECT * FROM product_type LIMIT 2;

-- отобразить все категории товаров со скидкой < 10%, и отсортировать их по названию
SELECT * FROM category WHERE discount < 10 ORDER BY name;

Data definition statements (DDS): команды с помощью, которых определяем структуру таблицы
·         CREATE (создание)
·         ALTER (изменение)
·         DROP (удаление)

Data manipulation statements (DMS):
·         SELECT (выбрать, отобрать, создать выборку, показать выборку)
·         UPDATE (обновить)
·         DELETE (удалить)
 
Код:
UPDATE category SET name = ‘Головные уборы’ WHERE id = 5;
SELECT * FROM category;
Разбор кода:
UPDATE – обновить
category – указываем какую таблицу
SET – даём команду установить, задать, настроить
name – указываем в каком столбце
= "Головные уборы" – указываем какое значение установить
WHERE – условие, если не указать, то заменим все значения в столбце
SELECT * FROM category;  -- показать таблицу «category»
 
Код:

UPDATE category SET discount = 3 WHERE id = 0;  -- заменить в таблице category скидку 0 на 3, но так не получится, стоит safe mode, обновление надо привязывать к первичному ключу

UPDATE category SET discount = 3 WHERE id in ( 2 , 5 );   -- id = 2 or id = 5, заменить в таблице category скидку на 3 в строках, где id = 2 or id = 5

SELECT * FROM category;

DELETE FROM category WHERE id = 5; -- удалить строку ‘Головные уборы’

USE shop; -- чтобы начать работу с базой данных «shop»

-- с помощью команды update заполнить поля «alias_name» для всех категорий (команда для каждой строки)

UPDATE category SET alias_name = 'women''s clothing' WHERE id = 1;

UPDATE category SET alias_name = 'man''s clothing' WHERE id = 2;

UPDATE category SET alias_name = 'women''s shoes' WHERE id = 3;

SELECT * FROM category;

-- добавить новый бренд «Тетя Клава company»

INSERT INTO category (name, discount) VALUES ('Тетя Клава company', 0);

SELECT * FROM category;

DELETE FROM category WHERE id = 5; -- удалить строку, только id не 5

Создать таблицу «Товары»

CREATE TABLE `shop`.`product` (

  `id` INT NOT NULL,

  `brand_id` INT NOT NULL,

  `product_type_id` INT NOT NULL,

  `category_id` INT NOT NULL,

  `price` DECIMAL(10,2) NOT NULL,

  PRIMARY KEY (`id`));

 

ALTER TABLE `shop`.`product`

CHANGE COLUMN `id` `id` INT NOT NULL AUTO_INCREMENT ;

 

Добавить товар

USE shop;

INSERT INTO product (brand_id, product_type_id, category_id, price) VALUES (1, 1, 1, 8999);

INSERT INTO product (brand_id, product_type_id, category_id, price) VALUES (10, 1, 1, 8999); -- на согласованность данных (не консистентность), т.к. бренда с id = 10 у нас нет

Таблица товаров должна хранить информацию о товарах соответствую имеющимся брендам, типом товаров и категории товаров.

В таблице «product» в поле «brand_id»  должны содержаться значение идентификаторов бренда, которые есть в таблице «brand».

Задать такую логику в БД.

Правой кнопкой на таблицу «product» нажать «Alter Table» – стрелочки – перейти во вкладку «Foreign key» – в поле «Foreign key name» указываем название правила «fk_brand_product» в поле «Referenced Table» указываем связь с таблицей «brand»; далее чекаем для какого столбца действует правило, в данном случае «brand_id» и «Referenced Table» выбираем «id».

Это указывает на то, что «brand_id» это не просто какой-то рядовой столбец, который хранит, какие-то целочисленные значения, а это внешний ключ таблицы «brand»; и что значения столбца «brand_id» равны значению столбца «id» таблицы «brand».

Перед тем как применить данное правило, необходимо или удалить из таблицы «Товары» строку с id=10 или задать значение в таблице «Бренды» с таким идентификатором.

Например:

INSERT INTO `shop`.`brand`; (id, name) VALUES (10, 'Тетя Клава company');

 

Код:

ALTER TABLE `shop`.`product`

ADD CONSTRAINT `fk_brand_product`

  FOREIGN KEY (`brand_id`)

  REFERENCES `shop`.`brand` (`id`)

  ON DELETE NO ACTION

  ON UPDATE NO ACTION;

 

Теперь если попытаться создать товар с несуществующим идентификатором – выйдет ошибка.

Если попробовать удалить строчку с идентификатором 10 из таблицы «Бренды» - то будет сообщение об ошибке. Потому что в связанной таблице «Товары» есть строчка, ссылающаяся на этот бренд.

Чтобы совершить операцию удаления строки есть два варианта:

1.      Можно зайти в таблицу «Товары» и удалить все строки, ссылающиеся на бренд, который необходимо удалить. И только потом удалить строку из таблицы «Бренды»

2.      Правой кнопкой на таблицу «product» нажать «Alter Table» – стрелочки – перейти во вкладку «Foreign key» – в поле «Foreign key Options» выбрать  «CASCADE»  Т.е. указываем, что при операции удаление строки из таблицы «Бренды», то все взаимосвязанные с ней строки из таблицы «Товары» будут также каскадно удалены.

Код:

ALTER TABLE `shop`.`product`

DROP FOREIGN KEY `fk_brand_product`

ALTER TABLE `shop`.`product`

ADD CONSTRAINT `fk_brand_product`

  FOREIGN KEY (`brand_id`)

  REFERENCES `shop`.`brand` (`id`)

  ON DELETE CASCADE

  ON UPDATE NO ACTION;

Схема диаграмма

«Database» – «Revers engineer»

На схеме указаны все созданные таблицы и связи между ними. 

Создать взаимосвязь между таблицами «product» и «category», «product_type»

Код:

ALTER TABLE `shop`.`product`

ADD INDEX `fk_category_product_idx` (`category_id` ASC) VISIBLE,

ADD INDEX `fk_product_type_product_idx` (`product_type_id` ASC) VISIBLE;

ALTER TABLE `shop`.`product`

ADD CONSTRAINT `fk_category_product`

  FOREIGN KEY (`category_id`)

  REFERENCES `shop`.`category` (`id`)

  ON DELETE CASCADE

  ON UPDATE NO ACTION,

ADD CONSTRAINT `fk_product_type_product`

  FOREIGN KEY (`product_type_id`)

  REFERENCES `shop`.`product_type` (`id`)

  ON DELETE CASCADE

  ON UPDATE NO ACTION;

На диаграме-схеме видно, что таблица «product» связана с тремя другими таблицами. 

Создать таблицу для хранения заказов

CREATE TABLE `shop`.`order` (

  `id` INT NOT NULL AUTO_INCREMENT,

  `user_name` VARCHAR(128) NOT NULL,

  `phone` VARCHAR(32) NOT NULL,

  `datatime` DATETIME NOT NULL,

  PRIMARY KEY (`id`));

Добавим информацию о заказе

INSERT INTO shop.`order`; (user_name, phone, datatime) VALUES (`Василий`, `555-55-55`, `2016-05-09 14:20`);

SELECT * FROM shop.`order`;

Где-то нужно хранить информацию о том, какие товары попадут в этот заказ. Для этого нужно создать отдельную таблицу, в которой будет храниться пара значений: идентификатор заказа и идентификатор продукта, который попадёт в этот заказ.

CREATE TABLE `shop`.`order_products` (

  `order_id` INT NOT NULL,

  `product_id` INT NOT NULL,

  `count` INT NULL,

  PRIMARY KEY (`order_id`, `product_id`));

Т.к. пара `order_id`, `product_id` в данном случае всегда будет уникальна, указываем оба как первичный ключ для этой таблицы.

Добавили ещё товары в таблицу

Добавить товары к заказу

Для корректной работы и для избежание ошибок: добавления не существующих товаров к заказу и/или добавление товаров к несуществующему заказу, создадим связь

Код:

ALTER TABLE `shop`.`order_products`

ADD INDEX `fk_order_products_idx` (`product_id` ASC) VISIBLE,

ALTER TABLE `shop`.`order_products`

ADD CONSTRAINT `fk_order_products_order`

  FOREIGN KEY (`order_id`)

  REFERENCES `shop`.`order` (`id`)

  ON DELETE NO ACTION

  ON UPDATE NO ACTION,

ADD CONSTRAINT `fk_order_products_product`

  FOREIGN KEY (`product _id`)

  REFERENCES `shop`.`product` (`id`)

  ON DELETE NO ACTION

  ON UPDATE NO ACTION;

Так это будет выглядеть на диаграмме

Объединение таблиц

INNER JOIN

LEFT JOIN / RIGHT JOIN

FULL OUTER JOIN

Как отобразить всю информацию о товарах списке товаров?

USE shop;

SELECT * FROM product;  -- получаем информацию о всех товарах, которые у нас есть

Отображается таблица с идентификаторами, а для отображения списка товаров нужны реальные названия бренда, типа товара, категории.

Синтаксис SQL который позволяет получать запросы объединения нескольких таблиц.

SELECT * FROM product

            INNER JOIN category on product.category_id = category.id;

Используем оператор [INNER JOIN] (внутреннее соединение). Объединяем таблицу [product] с таблицей [category] по следующему принципу: [category_id] в таблице [product] соответствует (равен) идентификатору [id] в таблице [category].

SELECT product.id, price, name  FROM product;  -- т.к. в объединённой таблице получилось два столбца «id», то необходимо уточнить, который именно выводить [product.id]

            INNER JOIN category on product.category_id = category.id;

Поменять таблицы местами

SELECT * FROM category

            INNER JOIN product on product.category_id = category.id;

К объединенной таблице можно применять стандартные фильтрации

SELECT * FROM product

            INNER JOIN category on product.category_id = category.id

            WHERE price < 10000;

Можно объединять любое количество таблиц

SELECT * FROM product

            INNER JOIN category on product.category_id = category.id

            INNER JOIN brand on brand.id = product.brand_id

            INNER JOIN product_type on product_type.id = product.product_type_id;

Убрать лишние столбцы

SELECT product.id, brand.name,  product_type.name, category.name, product.price FROM product

            INNER JOIN category on product.category_id = category.id

            INNER JOIN brand on brand.id = product.brand_id

            INNER JOIN product_type on product_type.id = product.product_type_id;

USE shop;

SELECT * FROM product

            INNER JOIN category ON product.category_id = category.id;

SELECT product.id, brand.name, product_type.name, 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;

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;

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

WHERE product_type_id = 2;

Найти категории товара, для которой нет ни одной единицы товара

SELECT * FROM category

            LEFT JOIN product on product.category_id = category.id;

SELECT * FROM category

            LEFT JOIN product on product.category_id = category.id;

WHERE product.id is NULL;

SELECT category.* FROM category

            LEFT JOIN product on product.category_id = category.id;

WHERE product.id is NULL;

SELECT category.* FROM product

            RIGHT  JOIN category on product.category_id = category.id;

WHERE product.id is NULL;

Вывести все типы товаров, для которых нет ни одного товара

Добавим новый тип товара

INSERT INTO product.type (name) VALUES (`Шуба`);

 

SELECT * FROM product.type

            LEFT JOIN product on product.product.type_id = product.type.id;

SELECT product.type.* FROM product.type

            LEFT JOIN product on product.product.type_id = product.type.id;

WHERE product.id is NULL;

Вывести информацию обо всех товарах, которые не попали ни в один из заказов

SELECT * FROM `order`

            INNER JOIN order_ products ON order_ products.order_id = `order`.id

            INNER JOIN product ON order_ products. product _id = product.id;

SELECT * FROM `order`

            INNER JOIN order_ products ON order_ products.order_id = `order`.id

            RIGHT  JOIN product ON order_ products. product _id = product.id;

SELECT * FROM `order`

            INNER JOIN order_ products ON order_ products.order_id = `order`.id

            RIGHT  JOIN product ON order_ products. product _id = product.id

WHERE `order`.id is NULL;

SELECT product.* FROM `order`

            INNER JOIN order_ products ON order_ products.order_id = `order`.id

            RIGHT  JOIN product ON order_ products.product _id = product.id

WHERE `order`.id is NULL;

INSERT INTO `order` (user_name, phone, datetime) VALUES (`Петр`, `888-88-88`, `2015-04-05`);

SELECT product.* FROM `order`

           INNER JOIN order_ products ON order_ products.order_id = `order`.id

           INNER JOIN product ON order_ products.product _id = product.id;

FULL OUTER JOIN – в MySQL нет. Вместо него используется UNION

Агрегирующие функции

Агрегирующие функции производят вычисления одного «собирающего» значения (количество, суммы, среднего, максимального, минимального значения и т.п.) для заданных групп строк таблицы.

COUNT

SUM

MAX

MIN

USE shop;

SELECT * FROM product;

SELECT count(*) FROM product;

SELECT count(*) FROM product WHERE product.price < 10000;

SELECT sum(price) FROM product;

SELECT sum(price), min(price), max(price) FROM product;

Добавим несколько товаров в заказ «Петр»

USE shop;

INSERT INTO order_products (order_id, product_id, `count`) VALUES (2, 3, 2);

INSERT INTO order_products (order_id, product_id, `count`) VALUES (2, 4, 3);

 

Объединим таблицы и выведем общую информацию

SELECT * FROM `order`

            INNER JOIN order_products ON order_products.order_id  = `order`.id

            INNER JOIN product ON product.id = order_products.order_id  

SELECT *, price * `count` FROM `order`

            INNER JOIN order_products ON order_products.order_id  = `order`.id

            INNER JOIN product ON product.id = order_products.order_id 

WHERE ‘order’.id = 1;

SELECT sum(price * `count`) as total_price FROM `order`

            INNER JOIN order_products ON order_products.order_id  = `order`.id

            INNER JOIN product ON product.id = order_products.order_id 

WHERE ‘order’.id = 1;

    SELECT `order`.user_name, price * `count` as total_price FROM `order`

            INNER JOIN order_products ON order_products.order_id  = `order`.id

            INNER JOIN product ON product.id = order_products.order_id;

 SELECT `order`.user_name, sum(price * `count`) as total_price FROM `order`

            INNER JOIN order_products ON order_products.order_id  = `order`.id

            INNER JOIN product ON product.id = order_products.order_id 

GROUP BY `order`.user_name;

SELECT `order`.user_name, max(price) FROM `order`

            INNER JOIN order_products ON order_products.order_id  = `order`.id

            INNER JOIN product ON product.id = order_products.order_id 

GROUP BY `order`.user_name;

SELECT `order`.user_name, max(price), count(*) FROM `order`

            INNER JOIN order_products ON order_products.order_id  = `order`.id

            INNER JOIN product ON product.id = order_products.order_id 

GROUP BY `order`.user_name;

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.order_id 

GROUP BY `order`.user_name;

Добавим условие

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.order_id 

WHERE user_name = ‘Василий’

GROUP BY `order`.user_name;

 

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.order_id 

WHERE user_name LIKE ‘В%’

GROUP BY `order`.user_name;

Использование условий для агрегирующих функций

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.order_id 

GROUP BY `order`.user_name

HAVING sum(`count`) >= 5;

или

SELECT `order`.user_name, max(price), sum(`count`) as order_ count FROM `order`

            INNER JOIN order_products ON order_products.order_id  = `order`.id

            INNER JOIN product ON product.id = order_products.order_id 

GROUP BY `order`.user_name

HAVING order_ count >= 5;

Производительность
Индексы

Выбираем таблицу, нажимаем правой кнопкой – выбираем изменить – переходим во вкладку «Индексы»

Добавляем индекс

Код:

ALTER TABLE `shop`.`products`

ADD INDEX price_index (`price` ASC);

Транзакции

ACID

Atomicity — Атомарность

Consistency — Согласованность

Isolation — Изолированность

Durability — Надёжность (долговечность)

Создадим таблицу банковского счета

Создадим таблицу банковского счета

Код:

CREATE TABLE `shop`.`user_bank_account` (

`id` INT NOT NULL,

`money` DECIMAL (10, 2) NOT NULL,

`user_name` VARCHAR (45) NOT NULL,

PRIMARY KEY (`id`));

 

Добавим в таблицу пару строк

INSERT INTO `shop`.`user_bank_account` (`id`, `money`, `user_name`) VALUES (‘1’, ‘100’, ‘Дмитрий’);

INSERT INTO `shop`.`user_bank_account` (`id`, `money`, `user_name`) VALUES (‘2’, ‘200’, ‘Евгений’);

SELECT * FROM `shop`.`user_bank_account`;

Добавим в таблицу пару строк

INSERT INTO `shop`.`user_banjk_account` (`id`, `money`, `user_name`) VALUES (‘1’, ‘100’, ‘Дмитрий’);

INSERT INTO `shop`.`user_banjk_account` (`id`, `money`, `user_name`) VALUES (‘2’, ‘200’, ‘Евгений’);

SELECT * FROM `shop`.`user_banjk_account`;

 

START TRANSACTION;

            UPDATE `shop`.`user_banjk_account` SET money = money - 100 WHRER id = 1;

            UPDATE `shop`.`user_banjk_account` SET money = money + 100 WHRER id = 2;

COMMIT