长沙软件开发_seo排名优化什么意思_宿州百度seo排名软件_十大成功营销策划案例

1.

CREATE TABLE goods (gid char(8) PRIMARY KEY ,name varchar(10),price decimal(8, 2),num int
);CREATE TABLE orders (oid int PRIMARY KEY AUTO_INCREMENT,gid char(10) NOT NULL ,name varchar(10),price decimal(8, 2),onum int,otime date
);INSERT INTO goods VALUES('A0001','橡皮',2.5,100),('B0001','小楷本',2.8,210),('C0001','铅笔',1.2,120),('D0001','计算器',28,20);-- 1
DELIMITER //
CREATE TRIGGER insert_orders AFTER INSERT
ON orders FOR EACH ROW
BEGINUPDATE goods SET num = num - NEW.onum WHERE name = NEW.name;
END //
DELIMITER ;# 测试
INSERT INTO orders VALUES (1,'A0001','橡皮',2.5,10,date(now()));
SELECT * FROM goods;
SELECT * FROM orders;-- 2
DELIMITER //
CREATE TRIGGER delete_orders  AFTER DELETE
ON orders FOR EACH ROW
BEGINUPDATE goods SET num = num + OLD.onum WHERE name = OLD.name;
END //
DELIMITER ;# 测试
DELETE FROM orders WHERE oid = 1;
SELECT * FROM goods;
SELECT * FROM orders;-- 3
DELIMITER //
CREATE TRIGGER update_orders  AFTER UPDATE
ON orders FOR EACH ROW
BEGINUPDATE goods SET num = num + (OLD.onum - NEW.onum) WHERE name = OLD.name;
END //
DELIMITER ;# 测试
UPDATE orders SET onum = onum - 5 WHERE oid = 1;
SELECT * FROM goods;
SELECT * FROM orders;

2.

CREATE TABLE emp_new
(sid            int,name           varchar(11),age            int,worktime_start date,incoming       int,dept2          int
);INSERT INTO emp_new
VALUES (1789, '张三', 35, '1980/1/1', 4000, 101),(1674, '李四', 32, '1983/4/1', 3500, 101),(1776, '王五', 24, '1990/7/1', 2000, 101),(1568, '赵六', 57, '1970/10/11', 7500, 102),(1564, '荣七', 64, '1963/10/11', 8500, 102),(1879, '牛八', 55, '1971/10/20', 7300, 103);SELECT * FROM emp_new;-- 1
DELIMITER //
CREATE PROCEDURE s1()
BEGINSELECT name,incoming FROM emp_new;
END //
DELIMITER ;CALL s1();-- 2
DELIMITER //
CREATE PROCEDURE s2(IN e_name varchar(11))
BEGINSELECT name,age FROM emp_new WHERE name = e_name;
END //
DELIMITER ;CALL s2('张三');-- 3
DELIMITER //
CREATE PROCEDURE s3(IN e_dept int, OUT avg_incoming double)
BEGINSELECT round(avg(incoming), 2) INTO avg_incomingFROM emp_new WHERE dept2 = e_dept GROUP BY dept2;
END //
DELIMITER ;SET @avg_incoming = 0.0 ;
CALL s3(101, @avg_incoming);
SELECT @avg_incoming;