class-notes/2208/调考试题答案/24年12月.md

#1 创建表
CREATE TABLE t_family(
	fno INT(11) PRIMARY KEY AUTO_INCREMENT,
	eno INT(11) NOT NULL COMMENT '职工编号',
	ename VARCHAR(255) NOT NULL COMMENT '职工姓名',
	faname VARCHAR(255),
	moname VARCHAR(255)
);

#2 添加字段
ALTER TABLE t_salary ADD grade VARCHAR(255) COMMENT '评级';

#3 修改字段数据类型
ALTER TABLE t_employee MODIFY sex ENUM('男', '女');

#4 更新记录
UPDATE t_department d
SET d.tel = '128'
WHERE d.dname = '法务部';

#5 条件、排序查询
SELECT e.ename, e.sex, e.birthday
FROM t_employee e
WHERE e.birthday >= '2000-1-1'
ORDER BY e.birthday DESC;

#6 分组、多表连接查询
SELECT d.dname, SUM(s.basepay + s.overtime + s.allowance + s.insurance)
FROM t_employee e, t_department d, t_salary s
WHERE e.dno = d.dno AND e.eno = s.eno
GROUP BY d.dno;

#7 分组+排序 视图
CREATE VIEW v_jobcount AS
SELECT j.jobtitle, COUNT(*)
FROM t_employee e, t_job j
WHERE e.jobno = j.jno
GROUP BY j.jno
ORDER BY 2;

#8 触发器
CREATE TRIGGER  tri_updateGrade
BEFORE UPDATE ON t_salary FOR EACH ROW
BEGIN
	IF	NEW.basepay < 6000 THEN
			SET NEW.grade = 'C';
	ELSEIF NEW.basepay < 8000 THEN 
			SET NEW.grade = 'B'; 
	ELSEIF NEW.basepay >= 8000 THEN
			SET NEW.grade = 'A';
	END IF;
END;

#9 存储过程
CREATE  PROCEDURE `pro_getDepartmentTel`(in `in_dno` int,OUT dept_phone VARCHAR(100)) 
	BEGIN

		SELECT CONCAT('部门名称-', dname, '，电话-', tel) INTO dept_phone
		FROM t_department
		WHERE dno = in_dno limit 1;
			
		IF dept_phone IS NULL THEN
        			SET dept_phone = '没有找到相应的部门';
		END IF;
	END