MySQL 基础教程[4]

以你之姓@ 2023-10-04 23:24 5阅读 0赞

#### MySQL 基础教程\[4\] ####

*  问题1
 *  问题1代码
 *  问题2
 *  问题2代码

*  本系列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.  创建一个名称为  f n y y g g \\mathrm\{fn\} y \\mathrm\{ygg\} fnyygg 的存储函数, 要求根据输入的员工号判断其工龄是否大于等于 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 ;