XmTWaRfm

1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
4
5-- -----------------------------------------------------
6-- Schema mydb
7-- -----------------------------------------------------
8DROP SCHEMA IF EXISTS `mydb` ;
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
14USE `mydb` ;
15
16-- -----------------------------------------------------
17-- Table `mydb`.`k_staff`
18-- -----------------------------------------------------
19DROP TABLE IF EXISTS `mydb`.`k_staff` ;
20
21CREATE TABLE IF NOT EXISTS `mydb`.`k_staff` (
22  `staff_num` INT auto_increment,
23  `staff_name` VARCHAR(45) NOT NULL,
24  `staff_post` VARCHAR(45) NULL,
25  `staff_hiredate` DATE NULL,
26  `staff_termdate` DATE NULL,
27  `K_dept_dept_num` INT,
28  PRIMARY KEY (`staff_num`),
29  INDEX `fk_k_staff_K_dept1_idx` (`K_dept_dept_num` ASC),
30  CONSTRAINT `fk_k_staff_K_dept1`
31    FOREIGN KEY (`K_dept_dept_num`)
32    REFERENCES `mydb`.`K_dept` (`dept_num`)
33    ON DELETE NO ACTION
34    ON UPDATE NO ACTION)
35ENGINE = InnoDB;
36
37
38-- -----------------------------------------------------
39-- Table `mydb`.`K_dept`
40-- -----------------------------------------------------
41DROP TABLE IF EXISTS `mydb`.`K_dept` ;
42
43CREATE TABLE IF NOT EXISTS `mydb`.`K_dept` (
44  `dept_num` INT auto_increment,
45  `dept_full_name` VARCHAR(45) NULL,
46  `dept_short_name` VARCHAR(10) NULL,
47  `k_staff_staff_num` INT,
48  PRIMARY KEY (`dept_num`),
49  INDEX `fk_K_dept_k_staff_idx` (`k_staff_staff_num` ASC),
50  CONSTRAINT `fk_K_dept_k_staff`
51    FOREIGN KEY (`k_staff_staff_num`)
52    REFERENCES `mydb`.`k_staff` (`staff_num`)
53    ON DELETE NO ACTION
54    ON UPDATE NO ACTION)
55ENGINE = InnoDB;
56
57
58-- -----------------------------------------------------
59-- Table `mydb`.`k_firm`
60-- -----------------------------------------------------
61DROP TABLE IF EXISTS `mydb`.`k_firm` ;
62
63CREATE TABLE IF NOT EXISTS `mydb`.`k_firm` (
64  `firm_num` INT auto_increment,
65  `firm_name` VARCHAR(45) NOT NULL,
66  `firm_addr` VARCHAR(45) NULL,
67  `firm_phone` VARCHAR(20) NULL,
68  `append_date` Date null,
69  `update_date` Date null,
70  PRIMARY KEY (`firm_num`))
71ENGINE = InnoDB;
72
73
74-- -----------------------------------------------------
75-- Table `mydb`.`k_contract`
76-- -----------------------------------------------------
77DROP TABLE IF EXISTS `mydb`.`k_contract` ;
78
79CREATE TABLE IF NOT EXISTS `mydb`.`k_contract` (
80  `contract_num` INT auto_increment,
81  `contract_date` DATE NULL,
82  `contract_type` ENUM('A', 'B', 'C') NULL,
83  `k_firm_firm_num` INT NOT NULL,
84  `k_staff_staff_num` INT NOT NULL,
85  PRIMARY KEY (`contract_num`),
86  INDEX `fk_k_contract_k_firm1_idx` (`k_firm_firm_num` ASC),
87  INDEX `fk_k_contract_k_staff1_idx` (`k_staff_staff_num` ASC),
88  CONSTRAINT `fk_k_contract_k_firm1`
89    FOREIGN KEY (`k_firm_firm_num`)
90    REFERENCES `mydb`.`k_firm` (`firm_num`)
91    ON DELETE NO ACTION
92    ON UPDATE NO ACTION,
93  CONSTRAINT `fk_k_contract_k_staff1`
94    FOREIGN KEY (`k_staff_staff_num`)
95    REFERENCES `mydb`.`k_staff` (`staff_num`)
96    ON DELETE NO ACTION
97    ON UPDATE NO ACTION)
98ENGINE = InnoDB;
99
100
101-- -----------------------------------------------------
102-- Table `mydb`.`k_bill`
103-- -----------------------------------------------------
104DROP TABLE IF EXISTS `mydb`.`k_bill` ;
105
106CREATE TABLE IF NOT EXISTS `mydb`.`k_bill` (
107  `bill_num` INT auto_increment,
108  `bill_date` DATE NULL,
109  `bill_sum` DECIMAL(9,2) NULL,
110  `bill_term` DATE NULL,
111  `bill_peni` DECIMAL(6,2) NULL,
112  `k_contract_contract_num` INT NOT NULL,
113  PRIMARY KEY (`bill_num`),
114  INDEX `fk_k_bill_k_contract1_idx` (`k_contract_contract_num` ASC),
115  CONSTRAINT `fk_k_bill_k_contract1`
116    FOREIGN KEY (`k_contract_contract_num`)
117    REFERENCES `mydb`.`k_contract` (`contract_num`)
118    ON DELETE NO ACTION
119    ON UPDATE NO ACTION)
120ENGINE = InnoDB;
121
122
123-- -----------------------------------------------------
124-- Table `mydb`.`k_payment`
125-- -----------------------------------------------------
126DROP TABLE IF EXISTS `mydb`.`k_payment` ;
127
128CREATE TABLE IF NOT EXISTS `mydb`.`k_payment` (
129  `payment_num` INT,
130  `payment_date` DATE NULL,
131  `payment_sum` DECIMAL(9,2) NULL,
132  `k_bill_bill_num` INT NOT NULL,
133  PRIMARY KEY (`payment_num`, `k_bill_bill_num`),
134  INDEX `fk_k_payment_k_bill1_idx` (`k_bill_bill_num` ASC),
135  CONSTRAINT `fk_k_payment_k_bill1`
136    FOREIGN KEY (`k_bill_bill_num`)
137    REFERENCES `mydb`.`k_bill` (`bill_num`)
138    ON DELETE NO ACTION
139    ON UPDATE NO ACTION)
140ENGINE = InnoDB;
141
142
143-- -----------------------------------------------------
144-- Table `mydb`.`k_price`
145-- -----------------------------------------------------
146DROP TABLE IF EXISTS `mydb`.`k_price` ;
147
148CREATE TABLE IF NOT EXISTS `mydb`.`k_price` (
149  `price_num` INT auto_increment,
150  `price_name` VARCHAR(45) NOT NULL,
151  `price_sum` VARCHAR(45) NULL,
152  `price_type` VARCHAR(1) NULL,
153  PRIMARY KEY (`price_num`))
154ENGINE = InnoDB;
155
156
157-- -----------------------------------------------------
158-- Table `mydb`.`k_protokol`
159-- -----------------------------------------------------
160DROP TABLE IF EXISTS `mydb`.`k_protokol` ;
161
162CREATE TABLE IF NOT EXISTS `mydb`.`k_protokol` (
163  `kolvo` INT NULL,
164  `price_sum` DECIMAL(9,2) NOT NULL,
165  `k_bill_bill_num` INT NOT NULL,
166  `k_price_price_num` INT NOT NULL,
167  PRIMARY KEY (`k_bill_bill_num`, `k_price_price_num`),
168  INDEX `fk_k_protokol_k_price1_idx` (`k_price_price_num` ASC),
169  CONSTRAINT `fk_k_protokol_k_bill1`
170    FOREIGN KEY (`k_bill_bill_num`)
171    REFERENCES `mydb`.`k_bill` (`bill_num`)
172    ON DELETE NO ACTION
173    ON UPDATE NO ACTION,
174  CONSTRAINT `fk_k_protokol_k_price1`
175    FOREIGN KEY (`k_price_price_num`)
176    REFERENCES `mydb`.`k_price` (`price_num`)
177    ON DELETE NO ACTION
178    ON UPDATE NO ACTION)
179ENGINE = InnoDB;
180
181
182SET SQL_MODE=@OLD_SQL_MODE;
183SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
184SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
185 
186INSERT INTO k_firm (firm_name, firm_addr)
187VALUES('Альфа', 'Москва');
188INSERT INTO k_firm (firm_name, firm_addr)
189VALUES('Бета', 'Казань');
190INSERT INTO k_firm (firm_name, firm_addr)
191VALUES('Гамма', 'Париж');
192INSERT INTO k_firm (firm_name, firm_addr)
193VALUES('Дельта', 'Лондон');
194INSERT INTO k_firm (firm_name, firm_addr)
195VALUES('Омега', 'Токио'); 
196select * from k_firm;
197
198INSERT INTO k_dept (dept_short_name, dept_full_name)
199VALUES('Sales', 'Отдел продаж');
200INSERT INTO k_dept (dept_short_name, dept_full_name)
201VALUES('Mart', 'Отдел маркетинга');
202INSERT INTO k_dept (dept_short_name, dept_full_name)
203VALUES('Cust', 'Отдел гарантийного обслуживания');
204
205INSERT INTO k_staff (staff_name, K_dept_dept_num, staff_hiredate, staff_post)
206VALUES('Иванов', 1, '1999-01-01', 'Менеджер');
207INSERT INTO k_staff (staff_name, K_dept_dept_num, staff_hiredate, staff_post)
208VALUES('Петров', 2, '2010-10-13','Менеджер');
209INSERT INTO k_staff (staff_name, K_dept_dept_num, staff_hiredate, staff_post)
210VALUES('Сидоров', 3, '2005-12-01','Менеджер');
211INSERT INTO k_staff (staff_name, staff_hiredate, staff_post)
212VALUES('Семенов', '1990-01-01','Директор');
213INSERT INTO k_staff (staff_name, K_dept_dept_num, staff_hiredate, staff_post)
214VALUES('Григорьев', 3, '2008-12-19','Программист');
215
216INSERT INTO k_contract
217(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 1, 1,'2011-11-01');
218INSERT INTO k_contract
219(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('B', 1, 2,'2011-10-01');
220INSERT INTO k_contract
221(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('C', 1, 1,'2011-09-01');
222INSERT INTO k_contract
223(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 2, 2,'2011-11-15');
224INSERT INTO k_contract
225(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('B', 2, 2,'2011-08-01');
226INSERT INTO k_contract
227(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('C', 3, 1,'2011-07-15');
228INSERT INTO k_contract
229(contract_type, k_firm_firm_num, k_staff_staff_num, contract_date) VALUES('A', 4, 1,'2011-11-12');
230
231INSERT INTO k_bill
232(k_contract_contract_num, bill_date, bill_term, bill_sum)
233VALUES(1, '2011-11-12', '2011-12-12', 1000);
234INSERT INTO k_bill
235(k_contract_contract_num, bill_date, bill_term, bill_sum)
236VALUES(1, '2011-12-12', '2012-01-12', 2000);
237INSERT INTO k_bill
238(k_contract_contract_num, bill_date, bill_term, bill_sum)
239VALUES(1, '2012-01-12', '2012-02-12',2000);
240INSERT INTO k_bill
241(k_contract_contract_num, bill_date, bill_term, bill_sum)
242VALUES(2, '2011-12-12', '2012-01-12', 6000);
243INSERT INTO k_bill
244(k_contract_contract_num, bill_date, bill_term, bill_sum)
245VALUES(2, '2012-01-12', '2012-02-12', 2000);
246INSERT INTO k_bill
247(k_contract_contract_num, bill_date, bill_term, bill_sum)
248VALUES(3, '2012-01-12', '2012-02-12', 2500);
249INSERT INTO k_bill
250(k_contract_contract_num, bill_date, bill_term, bill_sum)
251VALUES(4, '2011-12-12', '2012-01-12', 1500);
252INSERT INTO k_bill
253(k_contract_contract_num, bill_date, bill_term, bill_sum)
254VALUES(5, '2011-12-12', '2012-01-12', 1200);
255INSERT INTO k_bill
256(k_contract_contract_num, bill_date, bill_term, bill_sum)
257VALUES(5, '2012-01-12', '2012-02-12', 10000);
258SELECT * FROM k_bill;
259
260UPDATE k_dept SET k_staff_staff_num=2
261WHERE dept_short_name='Mart';
262UPDATE k_dept SET k_staff_staff_num=3
263WHERE dept_short_name='Cust';
264UPDATE k_dept SET k_staff_staff_num=1
265WHERE dept_short_name='Sales';
266--
267-- Перед выполнением этого запроса необходимо снять галочку в настройках
268-- edit -> preferences -> sql editor проматываем вниз и снимаем галочку с последнего пункта(safe updates)
269-- Затем выходим и заходим снова в workbench. Пробуем выполнить запросы и всё работает
270--
271DELETE FROM k_firm WHERE firm_num=5;
272
273create view	k_contract1
274As
275SELECT k_contract.contract_num, k_contract.contract_date,
276k_contract.contract_type, k_contract.k_firm_firm_num,
277k_staff.staff_name
278FROM k_contract INNER JOIN
279k_staff ON k_contract.k_staff_staff_num = k_staff.staff_num
280WHERE k_dept_dept_num = 1;
281select * from k_contract1;
282
283UPDATE k_contract1 SET contract_date='2011-11-02'
284WHERE contract_num=1;
285
286SELECT * FROM k_contract1;
287
288
289-- представление для неоплаченных счетов
290create view owe_bills as 
291SELECT b.bill_num AS "Номер счета",
292b.bill_date AS "Дата счета",
293b.bill_sum AS "Сумма счета",
2940 AS "Сумма оплаты"
295FROM k_bill b
296WHERE b.bill_num NOT IN (SELECT k_bill_bill_num FROM k_payment);
297
298select * from owe_bills;
299
300-- Представление(необновляемое потому что есть столбец который просчитывается с помощью функции sum) для всех договоров
301create view all_contracts as
302SELECT CONCAT('Договор № ', CONVERT(k_contract_contract_num, CHAR), ' на сумму ') AS "Номер", SUM(bill_sum) AS "Сумма" FROM k_bill
303GROUP BY k_contract_contract_num
304UNION
305SELECT 'ИТОГО: ', SUM(bill_sum) FROM k_bill ORDER BY 1;
306
307select * from all_contracts;
308
309-- Информация о 5 последних выписанных счетах
310create view bill_info as 
311SELECT bill_num, bill_date
312FROM k_bill ORDER BY bill_date DESC LIMIT 5;
313
314select * from bill_info;
315
316-- Процедура делает тоже, что и представление выше, только при её вызове можно указать сколько строчек нас интересует
317delimiter //
318create procedure procedure1 (limit_val int)
319begin
320	SELECT bill_num, bill_date
321	FROM k_bill ORDER BY bill_date DESC LIMIT limit_val;
322end//
323
324call procedure1(3);
325
326-- Процедура выводит конкретный заказ
327delimiter //
328create procedure procedure2 (num_param int)
329begin
330 select * from k_contract where contract_num = num_param;
331end//
332
333call procedure2(2);
334
335
336-- для создания триггеров пришлось добавить в таблицу k_firm 2 столбца дата обновления и дата создания
337
338delimiter //
339create trigger update_trigger
340before update on k_firm
341for each row
342begin
343		set new.update_date = now();
344END//
345select * from k_firm;
346update k_firm
347set firm_addr = 123 where firm_num =3;
348select * from k_firm;
349
350delimiter //
351create trigger insert_trigger
352before insert on k_firm
353for each row
354begin
355		set new.append_date = now();
356END//
357
358insert into k_firm (firm_name, firm_addr) values('123', '123');
359select * from k_firm;