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 (部门号)。
请按要求完成以下操作:
- 用SQL语句在tb_dept表的deptname属性上创建一个唯一性降序索引idx_dept。
- 编写SQL语句, 在tb dept中添加一条记录, deptno为D4, deptname为“营销部"
- 用SQL语句根据员工的出生年份查询员工的年龄 (设当前年份为2014年), 要求查询结果显示为“姓名”和“年龄”, 记录顺序仍按表中顺序为序。
- 用SQL语句为“市扬部”的负责人建立一个视图v_dept, 要求显示该部门负责人的姓名及其管理的员工姓名、学历 和工资。
- 新建一个名称为 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 (部门号) 。
请按要求完成以下操作:
- 创建一个名称为
- 创建一个名称为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 ;
