MySQL 基础教程[4]

• 本系列MySQL 基础教程通过“问题-代码”的方式介绍各类方法，每篇设置2个MySQL综合问题，并给出解决方案。

问题1

给定的企业员工管理数据库db_ygg1中包括部门表tb_dept和职工表tb_emp, tb_dept包含的字段有deptno（部门号） deptname（部门名称) 、manager (部门负责人)、office (办公地点) 、telephone (电话), tb_emp包含的字段有eno （员工号)、name（姓名）、sex (性别) 、 birth (出生年份)、、education (学历)、worktime了(参加工作时间)、 title (职务)、salary (工资)、telephone (电话) 和deptno (部门号)。
请按要求完成以下操作:

1. 用SQL语句在tb_dept表的deptname属性上创建一个唯一性降序索引idx_dept。
2. 编写SQL语句, 在tb dept中添加一条记录, deptno为D4, deptname为“营销部"
3. 用SQL语句根据员工的出生年份查询员工的年龄 (设当前年份为2014年), 要求查询结果显示为“姓名”和“年龄”, 记录顺序仍按表中顺序为序。
4. 用SQL语句为“市扬部”的负责人建立一个视图v_dept, 要求显示该部门负责人的姓名及其管理的员工姓名、学历 和工资。
5. 新建一个名称为 stud的用户, 主机名为localhost, 并为其授予对tb_emp表的select权限。

问题1代码

#【1】
use da_yggl;
create unique index idx_dept on tb_dept(deptname desc)；
#【2】
insert into tb_dept(deptno, deptname) values("D4","营销部")；
#【3】
select name as "姓名"，(2014-birth) as "年龄" from tb_emp  order by deptname desc；
#【4】
create view v_dept('部门负责人姓名','姓名','年龄','工资') as select p.manager, t.name, t.education, t.salary  from tb_emp p, tb_dept t where p.deptname="市场部" and t.deptno=p.p.deptno;
#【5】
create user 'stud'@'localhost';
grant select on tb_emp to 'stud'@'localhost' with grant option;

问题2

给定的企业员工管理数据库db_yggl中包括部门表tb_dept和职工表tb_emp, tb_dept包含的字段有deptno（部门号）、 deptname（部门名称) 、manager (部门负责人) 、office (办公地点) 、telephone (电话)，tb_empe包含的字段有eno (员工号)、name（姓名) 、 sex (性别) 、birth (出生年份) 、 education (学历) 、worktime（参加工作时间) 、 title (职务) 、 salary (工资) 、 telephone (电话) 和deptno (部门号) 。
请按要求完成以下操作:

1. 创建一个名称为 $\mathrm{fn}y\mathrm{ygg}$ 的存储函数, 要求根据输入的员工号判断其工龄是否大于等于 30 , 如果大于等于 30 , 则将 其工资增加 5%作为奖励。 (说明: 计算工龄时只计年份)

2. 创建一个名称为sp_emp的存储过程, 要求逐行显示“市场部”员工的姓名、学历、工资和该部门的平均工资。

问题2代码

#【1】
DELIMITER //
CREATE FUNCTION fn_yggl(eid CHAR(4))
returns CHAR(20)
DETERMINISTIC
BEGIN
    DECLARE workage INT;
    DECLARE newsalary FLOAT;
    SELECT YEAR(CURDATE())-SUBSTRING(worktime,1,4) INTO  workage FROM tb_emp WHERE eno=eid;
    IF workage>=30 THEN
        UPDATE tb_emp SET salary=salary*1.05 WHERE eno=eid;
        RETURN('工资已经更新');
    ELSE
        RETURN('工作年限不够，工资不能更新');
    END IF;
END //
DELIMITER ;
SELECT fn_yggl('1118');


#【2】
DELIMITER $$
CREATE procedure sp_emp (OUT avgsalary FLOAT)
BEGIN 
DECLARE ename VARCHAR(12); 
DECLARE edu VARCHAR(10); 
DECLARE esalary FLOAT;
DECLARE totalsalary FLOAT; 
DECLARE count SMALLINT;
DECLARE done INT DEFAULT -1;
DECLARE cur_emp CURSOR FOR 
SELECT name, education, salary
FROM tb_emp INNER JOIN tb_dept 
ON tb_emp.deptno=tb_dept.deptno AND deptname= '市场部';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
SET totalsalary=0;
SET count=0;
OPEN cur_emp;
FETCH cur_emp INTO ename, edu, esalary;
WHILE (done!=1) DO
	SELECT ename, edu, esalary;
	SET totalsalary=totalsalary+esalary;
	SET count=count+1;
	FETCH cur_emp INTO ename, edu, esalary;
END WHILE;
IF (count>0) THEN
	SET avgsalary= totalsalary/count;
ELSE
	SET avgsalary=0;
END IF;
CLOSE cur_emp;
END $$
DELIMITER ;