MySQL数据库技术第二版章末答案

本文链接：https://blog.csdn.net/qq_40927409/article/details/111460755

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 ;