· 6 years ago · Nov 12, 2019, 10:20 AM
1SHOW DATABASES;
2
3CREATE DATABASE STD;
4
5use std;
6
7CREATE 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);
8CREATE TABLE HELPER (item_number int(11) NOT NULL, inn INT NOT NULL);
9CREATE 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));
10CREATE TABLE PROVIDER (address text NOT NULL, phone varchar(20) NOT NULL, inn int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT);
11
12
13SHOW TABLES;
14
15DESCRIBE LIST;
16
17
18
19INSERT INTO ITEM (quantity, measure, price, last_delivery, name) VALUES (45,'литры',100,'2019-09-10 14:25','Кола');
20INSERT INTO ITEM (quantity, measure, price, last_delivery, name) VALUES (25,'литры',149,'2019-09-10 14:50','Пепси');
21INSERT INTO ITEM (quantity, measure, price, last_delivery, name) VALUES (43,'килограммы',550,'2019-09-10 14:10','Свинина');
22INSERT INTO ITEM (quantity, measure, price, last_delivery, name) VALUES (55,'килограммы',150,'2019-09-10 12:10','Пельмени');
23INSERT INTO ITEM (quantity, measure, price, last_delivery, name) VALUES (120,'килограммы',2000,'2019-09-10 10:10','Печенье');
24INSERT INTO ITEM (quantity, measure, price, last_delivery, name) VALUES (47,'тонны',10000,'2019-09-10 10:10','Соль');
25
26
27
28INSERT INTO HELPER (item_number, inn) VALUES (1,1);
29INSERT INTO HELPER (item_number, inn) VALUES (2,2);
30INSERT INTO HELPER (item_number, inn) VALUES (3,3);
31INSERT INTO HELPER (item_number, inn) VALUES (4,4);
32INSERT INTO HELPER (item_number, inn) VALUES (5,5);
33INSERT INTO HELPER (item_number, inn) VALUES (6,6);
34
35INSERT INTO HELPER (item_number, inn) VALUES (9,9);
36
37
38INSERT INTO LIST (receipt_date, shipping_date, item_quantity, inn) VALUES ('2019-09-10 14:22', '2019-09-10 11:33', 444, 2);
39INSERT INTO LIST (receipt_date, shipping_date, item_quantity, inn) VALUES ('2019-09-10 14:22', '2019-09-10 11:33', 211, 3);
40INSERT INTO LIST (receipt_date, shipping_date, item_quantity, inn) VALUES ('2019-09-10 11:44', '2019-09-10 17:25', 11, 1);
41INSERT INTO LIST (receipt_date, shipping_date, item_quantity, inn) VALUES ('2019-09-10 06:55', '2019-09-10 14:22', 22, 4);
42INSERT INTO LIST (receipt_date, shipping_date, item_quantity, inn) VALUES ('2019-09-10 14:25', '2019-09-10 15:41', 33, 5);
43INSERT INTO LIST (receipt_date, shipping_date, item_quantity, inn) VALUES ('2019-09-10 14:11', '2019-09-10 13:25', 556, 6);
44
45
46
47INSERT INTO PROVIDER (address, phone) VALUES ('Волгоград','658741');
48INSERT INTO PROVIDER (address, phone) VALUES ('Москва','6676786781');
49INSERT INTO PROVIDER (address, phone) VALUES ('Новосибирс','14220');
50INSERT INTO PROVIDER (address, phone) VALUES ('Тула','2524');
51INSERT INTO PROVIDER (address, phone) VALUES ('Санкт-Петербург','25077');
52
53
54
55DELETE FROM HELPER WHERE inn = 1474; - удаление стоо
56
57UPDATE PROVIDER SET address = 'Новосибирск' WHERE address = 'Новосибирс';
58
59
60
61
62DROP DATABASE STD;
63
64SELECT * FROM PROVIDER//
65
66
67
68
69
70ALTER TABLE ITEM ADD TEST int(11) NOT NULL - добавить стобцы
71
72ALTER TABLE ITEM MODIFY TEST varchar(100); - редактировать стобцы;
73
74ALTER TABLE ITEM DROP COLUMN TEST - удалить стобцы;
75
76
77
78
79
80
81
82
83SELECT * FROM ITEM;
84SELECT * FROM ITEM WHERE measure = 'килограммы';
85SELECT * FROM ITEM WHERE measure = 'килограммы' ORDER BY item_number DESC;
86SELECT * FROM PROVIDER WHERE address = 'Новосибирск' AND phone = 14220;
87SELECT name, MAX(quantity) FROM ITEM GROUP BY name;
88SELECT name, MAX(quantity) FROM ITEM GROUP BY name HAVING COUNT(*) > 1;
89
90
91SELECT COUNT(1) FROM ITEM;
92
93
94SELECT * FROM LIST INNER JOIN HELPER ON LIST.inn = HELPER.inn INNER JOIN ITEM ON ITEM.item_number = HELPER.inn;
95SELECT * FROM LIST LEFT OUTER JOIN HELPER ON LIST.inn = HELPER.inn;
96SELECT * FROM LIST RIGHT OUTER JOIN HELPER ON LIST.inn = HELPER.inn;
97SELECT * FROM LIST FULL OUTER JOIN HELPER ON LIST.inn = HELPER.inn;
98SELECT * FROM LIST CROSS JOIN HELPER;
99
100
101
102
103
104
105
106\g DROP PROCEDURE IF EXISTS limit_on;
107
108
109******************** ВЫВОД ОПРЕДЕЛЕННОГО КОЛИЧЕСТВА ЗАПИСЕЙ *************************
110
111DELIMITER //
112
113CREATE PROCEDURE `limit_on` (IN u_limit INT)
114BEGIN
115 SELECT * FROM LIST LIMIT u_limit;
116END//
117
118CALL limit_on(4)//
119
120*********************** МАКСИМАЛЬНОЕ ЗНАЧЕНИЕ В ТАБЛИЦЕ **********************
121
122DELIMITER //
123
124CREATE PROCEDURE `max_quantity` (OUT highest_salary INT)
125BEGIN
126 SELECT MAX(quantity) INTO highest_salary FROM ITEM;
127END//
128
129CALL max_quantity(@M)//
130
131SELECT @M//
132
133
134*********************** КОЛИЧЕСТВО НОМЕРОВ У ПОСТАВЩИКОВ ИЗ 1 ГОРОДА ************************
135
136DELIMITER //
137
138CREATE PROCEDURE `phone_number` (INOUT phone_list INT, IN city VARCHAR(100))
139BEGIN
140 SELECT COUNT(phone) INTO phone_list FROM PROVIDER WHERE address = city;
141END//
142
143CALL phone_number(@phone_numbers, 'Москва')//
144
145SELECT @phone_numbers//
146
147
148
149*********************** ПОДСЧЕТ КОЛИЧЕСТВА ТОВАРОВ И ВЫВОД НУЖНО ЛИ ЗАКАЗЫВАТЬ ПОПОЛНЕНИЕ ************************
150
151DELIMITER //
152
153CREATE PROCEDURE `items_quantity` (OUT items_result VARCHAR(100))
154BEGIN
155 DECLARE items_number INT;
156 SELECT COUNT(*) INTO items_number FROM ITEM;
157 IF items_number > 10
158 THEN
159 SET items_result = 'Записей больше 10';
160 ELSE
161 SET items_result = 'Записей меньше 10';
162 END IF;
163END//
164
165CALL items_quantity(@items_result)//
166
167SELECT @items_result//
168
169
170
171
172
173
174
175
176
177
178
179DROP PROCEDURE IF EXISTS quantity//
180
181DELIMITER //
182
183CREATE PROCEDURE `quantity` (IN item_name VARCHAR(100), OUT item_count INT)
184BEGIN
185 SELECT SUM(quantity) INTO item_count FROM ITEM WHERE name = item_name;
186END//
187
188CALL quantity('Кола', @item_count)//
189
190SELECT @item_count//
191
192
193
194
195
196
197DROP PROCEDURE IF EXISTS phones//
198
199DELIMITER //
200
201CREATE PROCEDURE `phones` (IN city VARCHAR(100))
202BEGIN
203 SELECT phone FROM PROVIDER WHERE address = city AS result;
204END//
205
206CALL phones('Москва')//