SHOW DATABASES;

CREATE DATABASE STD;

use std;

CREATE TABLE ITEM (quantity int(11) NOT NULL, measure varchar(100) NOT NULL, price int(11) NOT NULL, last_delivery datetime NOT NULL, name text NOT NULL, item_number int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT);
CREATE TABLE HELPER (item_number int(11) NOT NULL, inn INT NOT NULL);
CREATE TABLE LIST (receipt_date date NOT NULL, shipping_date date NOT NULL, item_quantity int(11) NOT NULL, note_id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, inn int(11) NOT NULL REFERENCES ITEM(item_number));
CREATE TABLE PROVIDER (address text NOT NULL, phone varchar(20) NOT NULL, inn int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT);


SHOW TABLES;

DESCRIBE LIST;



INSERT INTO ITEM (quantity, measure, price, last_delivery, name) VALUES (45,'литры',100,'2019-09-10 14:25','Кола');
INSERT INTO ITEM (quantity, measure, price, last_delivery, name) VALUES (25,'литры',149,'2019-09-10 14:50','Пепси');
INSERT INTO ITEM (quantity, measure, price, last_delivery, name) VALUES (43,'килограммы',550,'2019-09-10 14:10','Свинина');
INSERT INTO ITEM (quantity, measure, price, last_delivery, name) VALUES (55,'килограммы',150,'2019-09-10 12:10','Пельмени');
INSERT INTO ITEM (quantity, measure, price, last_delivery, name) VALUES (120,'килограммы',2000,'2019-09-10 10:10','Печенье');
INSERT INTO ITEM (quantity, measure, price, last_delivery, name) VALUES (47,'тонны',10000,'2019-09-10 10:10','Соль');



INSERT INTO HELPER (item_number, inn) VALUES (1,1);
INSERT INTO HELPER (item_number, inn) VALUES (2,2);
INSERT INTO HELPER (item_number, inn) VALUES (3,3);
INSERT INTO HELPER (item_number, inn) VALUES (4,4);
INSERT INTO HELPER (item_number, inn) VALUES (5,5);
INSERT INTO HELPER (item_number, inn) VALUES (6,6);

INSERT INTO HELPER (item_number, inn) VALUES (9,9);


INSERT INTO LIST (receipt_date, shipping_date, item_quantity, inn) VALUES ('2019-09-10 14:22', '2019-09-10 11:33', 444, 2);
INSERT INTO LIST (receipt_date, shipping_date, item_quantity, inn) VALUES ('2019-09-10 14:22', '2019-09-10 11:33', 211,  3);
INSERT INTO LIST (receipt_date, shipping_date, item_quantity, inn) VALUES ('2019-09-10 11:44', '2019-09-10 17:25', 11,  1);
INSERT INTO LIST (receipt_date, shipping_date, item_quantity, inn) VALUES ('2019-09-10 06:55', '2019-09-10 14:22', 22,  4);
INSERT INTO LIST (receipt_date, shipping_date, item_quantity, inn) VALUES ('2019-09-10 14:25', '2019-09-10 15:41', 33,  5);
INSERT INTO LIST (receipt_date, shipping_date, item_quantity, inn) VALUES ('2019-09-10 14:11', '2019-09-10 13:25', 556,  6);



INSERT INTO PROVIDER (address, phone) VALUES ('Волгоград','658741');
INSERT INTO PROVIDER (address, phone) VALUES ('Москва','6676786781');
INSERT INTO PROVIDER (address, phone) VALUES ('Новосибирс','14220');
INSERT INTO PROVIDER (address, phone) VALUES ('Тула','2524');
INSERT INTO PROVIDER (address, phone) VALUES ('Санкт-Петербург','25077');



DELETE FROM HELPER WHERE inn = 1474; - удаление стоо

UPDATE PROVIDER SET address = 'Новосибирск' WHERE address = 'Новосибирс';




DROP DATABASE STD;

SELECT * FROM PROVIDER//





ALTER TABLE ITEM ADD TEST int(11) NOT NULL - добавить стобцы

ALTER TABLE ITEM MODIFY TEST varchar(100);   - редактировать стобцы;

ALTER TABLE ITEM DROP COLUMN TEST   - удалить стобцы;








SELECT * FROM ITEM;
SELECT * FROM ITEM WHERE measure = 'килограммы';
SELECT * FROM ITEM WHERE measure = 'килограммы' ORDER BY item_number DESC;
SELECT * FROM PROVIDER WHERE address = 'Новосибирск' AND  phone = 14220;
SELECT name, MAX(quantity) FROM ITEM GROUP BY name;
SELECT name, MAX(quantity) FROM ITEM GROUP BY name HAVING COUNT(*) > 1;


SELECT COUNT(1) FROM ITEM;


SELECT * FROM LIST INNER JOIN HELPER ON LIST.inn = HELPER.inn INNER JOIN ITEM ON ITEM.item_number = HELPER.inn;
SELECT * FROM LIST LEFT OUTER JOIN HELPER ON LIST.inn = HELPER.inn;
SELECT * FROM LIST RIGHT OUTER JOIN HELPER ON LIST.inn = HELPER.inn;
SELECT * FROM LIST FULL OUTER JOIN HELPER ON LIST.inn = HELPER.inn;
SELECT * FROM LIST CROSS JOIN HELPER;







\g DROP PROCEDURE IF EXISTS limit_on;


******************** ВЫВОД ОПРЕДЕЛЕННОГО КОЛИЧЕСТВА ЗАПИСЕЙ *************************

DELIMITER //

CREATE PROCEDURE `limit_on` (IN u_limit INT)
BEGIN
    SELECT * FROM LIST LIMIT u_limit;
END//

CALL limit_on(4)//

*********************** МАКСИМАЛЬНОЕ ЗНАЧЕНИЕ В ТАБЛИЦЕ **********************

DELIMITER //

CREATE PROCEDURE `max_quantity` (OUT highest_salary INT)
BEGIN
     SELECT MAX(quantity) INTO highest_salary FROM ITEM;
END//

CALL max_quantity(@M)//

SELECT @M//


*********************** КОЛИЧЕСТВО НОМЕРОВ У ПОСТАВЩИКОВ ИЗ 1 ГОРОДА ************************

DELIMITER //

CREATE PROCEDURE `phone_number` (INOUT phone_list INT, IN city VARCHAR(100))
BEGIN
     SELECT COUNT(phone) INTO phone_list FROM PROVIDER WHERE address = city;
END//

CALL phone_number(@phone_numbers, 'Москва')//

SELECT @phone_numbers//



*********************** ПОДСЧЕТ КОЛИЧЕСТВА ТОВАРОВ И ВЫВОД НУЖНО ЛИ ЗАКАЗЫВАТЬ ПОПОЛНЕНИЕ ************************

DELIMITER //

CREATE PROCEDURE `items_quantity` (OUT items_result VARCHAR(100))
BEGIN
     DECLARE items_number INT;
     SELECT COUNT(*) INTO items_number FROM ITEM;
	 IF items_number > 10
	 THEN
	 SET items_result = 'Записей больше 10';
	 ELSE
	 SET items_result = 'Записей меньше 10';
	 END IF;
END//

CALL items_quantity(@items_result)//

SELECT @items_result//











DROP PROCEDURE IF EXISTS quantity//

DELIMITER //

CREATE PROCEDURE `quantity` (IN item_name VARCHAR(100), OUT item_count INT)
BEGIN
     SELECT SUM(quantity) INTO item_count FROM ITEM WHERE name = item_name;
END//

CALL quantity('Кола', @item_count)//

SELECT @item_count//






DROP PROCEDURE IF EXISTS phones//

DELIMITER //

CREATE PROCEDURE `phones` (IN city VARCHAR(100))
BEGIN
     SELECT phone FROM PROVIDER WHERE address = city AS result;
END//

CALL phones('Москва')//