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 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 ('Москва','150220');
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 HELPER;





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;
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;