mysql学习篇-6
韩琳*
MySQL, 数据库,
项目描述
mysql学习篇-6
上传时间
2022.06.30
浏览人数
727人
韩琳*
天津市宁河县
Hot:14320
CREATE TABLE dept (
deptno INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 添加4个部门
INSERT INTO dept(deptno,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','深圳'),
(40,'财务部','广州'),
(50,'董事会','太原');
-- 员工表
CREATE TABLE emp (
empno INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job VARCHAR(50), -- 职务
mgr INT , -- 上级领导
hiredate DATE, -- 入职日期
sal DECIMAL(7,2), -- 工资
comm DECIMAL(7,2), -- 奖金
deptno INT -- 所在部门编号
);
-- 添加员工
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES
(1001,'甘宁','文员',1013,'2000-12-17','8000.00',NULL,20),
(1002,'黛绮丝','销售员',1006,'2001-02-20','16000.00','3000.00',20),
(1003,'殷天正','销售员',1006,'2001-02-22','12500.00','5000.00',30),
(1004,'刘备','经理',1009,'2001-04-02','29750.00',NULL,20),
(1005,'谢逊','销售员',1006,'2001-09-28','12500.00','14000.00',30),
(1006,'关羽','经理',1009,'2001-05-01','28500.00',NULL,30),
(1007,'张飞','经理',1009,'2001-09-01','24500.00',NULL,10),
(1008,'诸葛亮','分析师',1004,'2007-04-19','30000.00',NULL,20),
(1009,'张无忌','董事长',NULL,'2001-11-17','50000.00',NULL,10),
(1010,'韦一笑','销售员',1006,'2001-09-08','15000.00','0.00',30),
(1011,'周泰','文员',1008,'2007-05-23','11000.00',NULL,30),
(1012,'程普','文员',1006,'2001-12-03','9500.00',NULL,30),
(1013,'庞统','分析师',1004,'2001-12-03','30000.00',NULL,20),
(1014,'黄盖','文员',1007,'2002-01-23','13000.00',NULL,10),
(1015,'张三','保洁员',1001,'2013-05-01','80000.00',50000.00,50);
-- 工资等级表
CREATE TABLE salgrade (
grade INT PRIMARY KEY, -- 级别
losal INT, -- 最低工资
hisal INT -- 最高工资
);
-- 添加5个工资等级
INSERT INTO salgrade(grade,losal,hisal) VALUES
(1,7000,12000),
(2,12000,14000),
(3,14000,20000),
(4,20000,30000),
(5,null,90000);
-- 1.列出薪金比关羽高的所有员工
select * from emp e1 where e1.sal > (select e.sal from emp e where e.ename = "关羽");
-- 2.列出所有员工的姓名及其直接上级的姓名
select e.empno,e.ename,ep.empno,ep.ename from emp e left join emp ep on e.mgr = ep.empno;
-- 3.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称
select * from emp e left join emp e1 on e.mgr = e1.empno where date(e.hiredate)>date(e1.hiredate);
-- 4.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select * from dept dt left join emp ep on dt.deptno = ep.deptno;
-- 5.列出所有文员的姓名及其部门名称
select * from emp ep left join dept dt on ep.deptno = dt.deptno where ep.job = "文员";
-- 6.列出最低薪金大于15000的各种工作及从事此工作的员工人数
select ep.job,count(1) from emp ep left join dept dt on ep.deptno = dt.deptno where ep.sal >15000 group by ep.job;
-- 7.列出在销售部工作的员工的姓名
select ep.ename from emp ep left join dept dt on ep.deptno = dt.deptno where dt.dname = "销售部";
-- 8.列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级
select * from emp ep1 left join emp ep2 on ep1.mgr = ep2.empno
left join salgrade se on ep1.sal between se.losal and se.hisal
where ep1.sal>(select avg(ep.sal) from emp ep );
-- 10.列出与庞统从事相同工作的所有员工及部门名称
select * from emp ep1 left join dept dt on ep1.deptno = dt.deptno where ep1.job =(
select ep.job from emp ep where ep.ename = "庞统") and ep1.ename != "庞统";
-- 11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
select * from emp ep1 left join dept dt on ep1.deptno = dt.deptno where ep1.sal >
(select sum(ep.sal) from emp ep left join dept dt on ep.deptno = dt.deptno where dt.deptno = "30");
select * from emp ep1 left join dept dt on ep1.deptno = dt.deptno where ep1.sal >
(select max(ep.sal) from emp ep left join dept dt on ep.deptno = dt.deptno where dt.deptno = "30");
-- 12.列出每个部门的员工数量、平均工资
select dt.dname, count(1) , avg(ep.sal) from emp ep left join dept dt on ep.deptno = dt.deptno group by dt.dname;
韩琳*
