（四）MySQL员⼯部门查询建表脚本
-- 部门表
CREATE TABLE dept(
deptno INT PRIMARY KEY AUTO_INCREMENT COMMENT '部门编号', -- 部门编号
dname VARCHAR(20) COMMENT '部门名字',  -- 部门名字
loc VARCHAR(20) COMMENT '地址'  -- 地址
);
-- 员⼯表
CREATE TABLE emp(
empno INT PRIMARY KEY AUTO_INCREMENT,-- 员⼯编号
ename VARCHAR(20) COMMENT '员⼯姓名', -- 员⼯姓名          -
job VARCHAR(20) COMMENT '岗位', -- 岗位
mgr INT COMMENT '直接领导编号',  -- 直接领导编号
hiredate date COMMENT '雇佣⽇期，⼊职⽇期', -- 雇佣⽇期，⼊职⽇期
sal INT COMMENT '薪⽔', -- 薪⽔
comm INT COMMENT '提成',  -- 提成
deptno INT NOT NULL COMMENT '部门编号', -- 部门编号
foreign key (deptno) references dept(deptno)
);
INSERT INTO dept VALUES(10,'财务部','北京');
INSERT INTO dept VALUES(20,'研发部','上海');
INSERT INTO dept VALUES(30,'销售部','⼴州');
INSERT INTO dept VALUES(40,'⾏政部','深圳');
INSERT INTO emp VALUES(7369,'刘⼀','职员',7902,'1980-12-17',800,null,20);
INSERT INTO emp VALUES(7499,'陈⼆','推销员',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
INSERT INTO emp VALUES(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
INSERT INTO emp VALUES(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
INSERT INTO emp VALUES(7788,'周⼋','分析师',7566,'1987-06-13',3000,null,20);
INSERT INTO emp VALUES(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
INSERT INTO emp VALUES(7844,'郑⼗','推销员',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'郭⼗⼀','职员',7788,'1987-06-13',1100,null,20);
INSERT INTO emp VALUES(7900,'钱多多','职员',7698,'1981-12-03',950,null,30);
INSERT INTO emp VALUES(7902,'⼤锦鲤','分析师',7566,'1981-12-03',3000,null,20);
INSERT INTO emp VALUES(7934,'⽊有钱','职员',7782,'1983-01-23',1300,null,10);
练习
-- 1．列出⾄少有⼀个员⼯的所有部门。
SELECT d.dname 部门名称, COUNT(*) 员⼯数量
FROM emp e
LEFT JOIN dept d
ON e.deptno = d.deptno
GROUP BY e.deptno;
-
- 2．列出薪⾦⽐"刘⼀"多的所有员⼯。
SELECT ename 员⼯名称, sal 员⼯薪⽔
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = '刘⼀');
-- 3．列出所有员⼯的姓名及其直接上级的姓名。
ame 员⼯, ame 上级
FROM emp self
LEFT JOIN emp mgr
= pno;
-- 4．列出受雇⽇期早于其直接上级的所有员⼯。
ame 员⼯, ame 上级, self.hiredate 员⼯受雇⽇期, mgr.hiredate 上级受雇⽇期
FROM emp self
LEFT JOIN emp mgr
= pno
WHERE self.hiredate < mgr.hiredate;
-- 5．列出部门名称和这些部门的员⼯信息，同时列出那些没有员⼯的部门。
SELECT d.dname 部门名称, e.ename 员⼯信息
FROM dept d
LEFT JOIN emp e
ON e.deptno = d.deptno;
-- 6．列出所有job为“职员”的姓名及其部门名称。
ame 员⼯名称, d.dname 部门名称, e.job
FROM emp e
LEFT JOIN dept d
ON e.deptno = d.deptno
WHERE e.job = '职员';
-- 7．列出最低薪⾦⼤于1500的各种⼯作。
SELECT DISTINCT job FROM emp WHERE sal > 1500;
-- 8．列出在部门 "销售部" ⼯作的员⼯的姓名，假定不知道销售部的部门编号。
ame FROM emp e
LEFT JOIN dept d
ON e.deptno = d.deptno
WHERE d.dname = '销售部';
-
- 9．列出薪⾦⾼于公司平均薪⾦的所有员⼯。
SELECT ename, sal FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);
-- 10．列出与"周⼋"从事相同⼯作的所有员⼯。
SELECT ename, job FROM emp WHERE job = (SELECT job FROM emp WHERE ename = '周⼋');
-- 11．列出薪⾦等于部门30中员⼯的薪⾦的所有员⼯的姓名和薪⾦。
SELECT ename, sal, deptno FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30);
-- 12．列出薪⾦⾼于在部门30⼯作的所有员⼯的薪⾦的员⼯姓名和薪⾦。
SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
-- 13．列出在每个部门⼯作的员⼯数量、平均⼯资。
SELECT dept.deptno,pno),AVG(sal)
FROM dept
LEFT JOIN emp
ON dept.deptno=emp.deptno
GROUP BY dept.deptno;
-- 14．列出所有员⼯的姓名、部门名称和⼯资。
ame 姓名, d.dname 部门名称, e.sal ⼯资 FROM emp e
LEFT JOIN dept d
ON e.deptno = d.deptno;
-- 15．列出所有部门的详细信息和部门⼈数。
SELECT d.deptno 部门编号, d.dname 部门名称, d.loc 位置, unt ⼈数 FROM dept d
LEFT JOIN (SELECT deptno, COUNT(deptno) count FROM emp GROUP BY deptno) tmp
ON d.deptno = tmp.deptno;
-- 16．列出各种⼯作的最低⼯资。
SELECT job, MIN(sal) 最低⼯资 FROM emp GROUP BY job;
-- 17．列出各个部门的经理的最低薪⾦。
SELECT d.dname 部门名称, tmp.sal 薪⽔ FROM dept d
LEFT JOIN (SELECT MIN(sal) sal, deptno FROM emp WHERE job = '经理' GROUP BY deptno) tmp
ON d.deptno = tmp.deptno;
-- 18．列出所有员⼯的年⼯资，按年薪从低到⾼排序。
SELECT ename, (sal + IFNULL(comm, 0)) * 12 FROM emp;
-- 19.查出emp表中薪⽔在3000以上（包括3000）的所有员⼯的员⼯号、姓名、薪⽔。
SELECT empno, ename, sal FROM emp WHERE sal >= 3000;
-
- 20.查询出所有薪⽔在'陈⼆'之上的所有⼈员信息。
SELECT empno, ename, sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = '陈⼆');
-- 21.查询出emp表中部门编号为20，薪⽔在2000以上（不包括2000）的所有员⼯，显⽰他们的员⼯号，姓名以及薪⽔，以如下列名显⽰：员⼯编号员⼯名字薪⽔SELECT empno 员⼯编号, ename 员⼯名字, sal 薪⽔ FROM emp WHERE sal > 2000 AND deptno = 20;
-- 22.查询出emp表中所有的⼯作种类（⽆重复）
SELECT job FROM emp GROUP BY job;
-- 23.查询出所有奖⾦（comm）字段不为空的⼈员的所有信息。
SELECT * FROM emp WHERE comm IS NOT NULL;
-- 24.查询出薪⽔在800到2500之间（闭区间）所有员⼯的信息。（注：使⽤两种⽅式实现and以及between and）
SELECT * FROM emp WHERE sal BETWEEN 800 AND 2500;
SELECT * FROM emp WHERE sal >= 800 AND sal <= 2500;
-- 25.查询出员⼯号为7521，7900，7782的所有员⼯的信息。（注：使⽤两种⽅式实现，or以及in）
SELECT * FROM emp WHERE empno IN (7521, 7900, 7782);
SELECT * FROM emp WHERE empno = 7521 OR empno = 7900 OR empno = 7782;
-- 26.查询出名字中有“张”字符，并且薪⽔在1000以上（不包括1000）的所有员⼯信息。
SELECT * FROM emp WHERE ename LIKE '%张%' AND sal > 1000;
-- 27.查询出名字第三个汉字是“多”的所有员⼯信息。
SELECT * FROM emp WHERE ename LIKE '__多%';
-- 28.将所有员⼯按薪⽔升序排序，薪⽔相同的按照⼊职时间降序排序。
SELECT * FROM emp ORDER BY sal, hiredate DESC;
-- 29.将所有员⼯按照名字⾸字母升序排序，⾸字母相同的按照薪⽔降序排序。 order by convert(name using gbk) asc;
SELECT ename, SUBSTR(ename, 1, 1), sal FROM emp ORDER BY CONVERT(substring(ename,1,1) USING gbk), sal DESC;
-- 30.查询出最早⼯作的那个⼈的名字、⼊职时间和薪⽔。
SELECT * FROM emp WHERE hiredate = (SELECT MIN(hiredate) FROM emp);
-- 31.显⽰所有员⼯的名字、薪⽔、奖⾦，如果没有奖⾦，暂时显⽰100.
SELECT ename, sal, IFNULL(comm, 100) comm FROM emp;
-- 32.显⽰出薪⽔最⾼⼈的职位。
SELECT job, sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);mysql group by order by
-- 33.查出emp表中所有部门的最⾼薪⽔和最低薪⽔，部门编号为10的部门不显⽰。
SELECT deptno, MAX(sal) 最⾼薪⽔, MIN(sal) 最低薪⽔ FROM emp WHERE deptno != 10 GROUP BY deptno;
-- 34.删除10号部门薪⽔最⾼的员⼯。
DELETE FROM emp WHERE sal = (SELECT emp.sal FROM (SELECT MAX(sal) sal FROM emp WHERE deptno = 10) emp); -- 35.将薪⽔最⾼的员⼯的薪⽔降30%。
UPDATE emp SET sal = sal * 0.7 WHERE sal = (SELECT tmp.sal FROM (SELECT MAX(sal) sal FROM emp) tmp);
-- 36.查询员⼯姓名，⼯资和⼯资级别(⼯资>=3000 为3级，⼯资>2000 为2级，⼯资<=2000 为1级)
-- 语法：case when ... then ... when ... then ... else ... end
SELECT ename, sal,
CASE WHEN sal>=3000 THEN '3级'
WHEN sal>=2000 THEN '2级'
ELSE '1级'
END
FROM emp;