MySQL数据库技术 第二版 章末 答案—单元8

admin管理员组

文章数量:1794759

MySQL数据库技术 第二版 章末 答案—单元8

---

MySQL数据库技术 第二版 章末 答案—单元8 以下是我个人所尝试过得答案，若有不正确的地方请告知，谢谢！ 由于实在是太多内容了，原谅我省略题目内容 单元8 数据库编程 ---- P166-P168

跳到底部，进行题目筛选

实训8 底部 1、存储过程

---

-- 创建存储过程 USE yggl; DELIMITER $$ CREATE PROCEDURE eqIncome(IN propler1 CHAR(6),IN propler2 CHAR(6),OUT isHeight INT(1)) BEGIN DECLARE Income1 FLOAT; DECLARE Income2 FLOAT; SELECT income-outcome INTO Income1 FROM salary WHERE propler1 = employeeid; SELECT income-outcome INTO Income2 FROM salary WHERE propler2 = employeeid; IF Income1>Income2 THEN SET isHeight=0; ELSE SET isHeight=1; END IF; END $$ DELIMITER; -- 调用存储过程 CALL eqIncome('000001','108991',@是否高于); -- 输出结果 SELECT @是否高于 AS 前者是否高于后者; 2、存储函数

---

-- 2.1、创建存储函数 SET GLOBAL log_bin_trust_function_creators=1; USE yggl; DELIMITER $$ CREATE FUNCTION EM_NUM() RETURNS INTEGER BEGIN RETURN (SELECT COUNT(*) FROM employees); END $$ DELIMITER; -- 2.2、创建存储函数 SET GLOBAL log_bin_trust_function_creators=1; USE yggl; DELIMITER $$ CREATE FUNCTION isyjb(m1 CHAR(6)) RETURNS CHAR(4) BEGIN DECLARE bm,ed CHAR(6); SELECT departmentid,education INTO bm,ed FROM employees WHERE employeeid=m1; IF bm='4' THEN RETURN ed; ELSE RETURN 'no'; END IF; END $$ DELIMITER; -- 调用存储函数 SELECT isyjb('000001'); SELECT isyjb('302566'); 3、触发器

---

-- 3.1、创建触发器 USE yggl; DELIMITER $$ CREATE TRIGGER deleteEm AFTER DELETE ON employees FOR EACH ROW BEGIN DELETE FROM salary WHERE employeeid=old.employeeid; END $$ DELIMITER; -- 3.2、创建触发器 USE yggl; DELIMITER $$ CREATE TRIGGER insertEm AFTER INSERT ON employees FOR EACH ROW BEGIN INSERT INTO salary VALUES(new.employeeid,0,0); END $$ DELIMITER; -- 3.3、创建触发器 USE yggl; DELIMITER $$ CREATE TRIGGER InCrease AFTER UPDATE ON employees FOR EACH ROW BEGIN DECLARE n TINYINT; SELECT workyear-old.workyear INTO n FROM employees WHERE employeeid=old.employeeid; UPDATE salary SET income=income+n*500 WHERE employeeid=old.employeeid; END $$ DELIMITER; 思考8 1、简答题 底部

---

2、写SQL语句 底部

---

-- 查看版本 SELECT @@version; USE xscj; -- 1、 SET @user1=1, @user2=2, @user3=3; SELECT @user1, @user2, @user3; -- 2、 SELECT LEFT(课程名, 3) FROM kc; -- 3、 SELECT SUBSTRING(姓名, 1, 1) AS 姓, SUBSTRING(姓名, 2, LENGTH(姓名)-1 ) AS 名 FROM xs WHERE 性别=0 ORDER BY 姓名; -- 4、 SELECT 姓名, YEAR(NOW()) - YEAR(出生时间) AS 年龄 FROM xs WHERE 性别=0; -- 5、 SELECT 姓名, IF(性别=0, "女", "男") AS 性别, 专业名 FROM xs WHERE 姓名 LIKE "__"; -- 6、 DELIMITER $$ DROP PROCEDURE IF EXISTS `demo`$$ CREATE PROCEDURE xscj.demo(IN xh CHAR(6)) BEGIN DELETE FROM xs_copy WHERE 学号=xh; END $$ -- end与$$之间的空格可选 DELIMITER ; -- DELIMITER与逗号之间的空格是必须的，且有且只有一个【tab也不行】。否则语法出错 -- 7、 DELIMITER $$ CREATE PROCEDURE test1(IN XH CHAR(6),IN KCM CHAR(3)) BEGIN DECLARE cj TINYINT; SELECT 成绩 INTO cj FROM xs_kc WHERE 学号=XH AND 课程号 = KCM; IF cj < 60 THEN UPDATE xs_kc SET 学分=0 WHERE 学号=XH AND 课程号 = KCM; ELSE UPDATE xs_kc,kc SET xs_kc.学分=kc.学分 WHERE (xs_kc.学号=XH AND xs_kc.课程号 = KCM) AND (kc.课程号 = KCM); END IF; END $$ DELIMITER ; -- 调用存储过程 CALL test1('081101','102'); CALL test1('081103','102'); -- 8、 DELIMITER $$ CREATE FUNCTION fun1( ) RETURNS INTEGER -- 注意：是RETURNS。不是RETURN BEGIN RETURN (SELECT COUNT(*) FROM xs); END$$ DELIMITER ; -- 9、 DELIMITER $$ CREATE TRIGGER tri1 AFTER DELETE ON xs FOR EACH ROW BEGIN DELETE FROM xs_kc WHERE 学号=old.学号; END$$ DELIMITER ; /* This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table' 原因：由于在 mysql 5.7.2+ 版本之前，我们只能为表中的事件创建一个触发器。 解决方法1：只保留一个触发器。 解决方法2：更换版本 */ -- 10、 DELIMITER $$ CREATE TRIGGER tri2 BEFORE UPDATE ON xs_kc FOR EACH ROW BEGIN IF new.成绩<60 THEN UPDATE xs_kc SET 学分=0 WHERE 学号=new.学号; ELSE UPDATE xs_kc SET 学分=学分 WHERE 学号=new.学号; END IF; END$$ DELIMITER ; -- 11、 DELIMITER $$ CREATE TRIGGER tri3 AFTER INSERT ON xs_kc FOR EACH ROW BEGIN IF new.成绩>=60 THEN UPDATE xs SET 总学分=总学分+new.学分 WHERE 学号=new.学号; END IF; END$$ DELIMITER ; -- 12、 DELIMITER $$ CREATE PROCEDURE proce1() BEGIN REPLACE student SELECT * FROM xs; END$$ DELIMITER ; -- 调用 -- DELIMITER $$ CREATE TRIGGER tri10 AFTER INSERT ON xs FOR EACH ROW CALL proce1(); -- DELIMITER ; 回到实训7 回到思考7 思考七--写SQL语句 回到顶部

本文标签： 单元数据库技术答案mySQL