sql查询练习2
2018-06-14 本文已影响6人
夜空最亮的9星
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(50),
loc VARCHAR(50)
);
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
COMM DECIMAL(7,2),
deptno INT,
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)
);
CREATE TABLE salgrade(
grade INT PRIMARY KEY,
losal INT,
hisal INT
);
INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '学工部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '武汉');
/插入emp表数据/
INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);
INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
/插入salgrade表数据/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);
-- 员工表
mysql> select * from emp;
+-------+-----------+-----------+------+------------+----------+----------+--------+
| empno | ename | job | mgr | hiredate | sal | COMM | deptno |
+-------+-----------+-----------+------+------------+----------+----------+--------+
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000.00 | NULL | 20 |
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000.00 | 3000.00 | 30 |
| 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 | 20 |
| 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 |
+-------+-----------+-----------+------+------------+----------+----------+--------+
14 rows in set (0.01 sec)
select * from emp where job like "销售%";
select deptno,avg(sal) from emp GROUP BY deptno;
select * from dept LEFT join (select deptno,avg(sal) from emp GROUP BY deptno) t1 where t1.deptno = dept.deptno;
select dname,loc,t1.ct from dept inner join (select deptno,count(1) as ct from emp GROUP BY deptno) t1 where t1.deptno=dept.deptno order by dept.deptno;
SELECT e.*, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno AND job=(SELECT job FROM emp WHERE ename='庞统');
-- 部门表
mysql> select * from dept;
+--------+-----------+--------+
| deptno | dname | loc |
+--------+-----------+--------+
| 10 | 教研部 | 北京 |
| 20 | 学工部 | 上海 |
| 30 | 销售部 | 广州 |
| 40 | 财务部 | 武汉 |
+--------+-----------+--------+
4 rows in set (0.03 sec)
-- 薪水表
mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
| 2 | 12010 | 14000 |
| 3 | 14010 | 20000 |
| 4 | 20010 | 30000 |
| 5 | 30010 | 99990 |
+-------+-------+-------+
4 rows in set (0.00 sec)
--- 查找第二高薪水
mysql> select max(sal) as SecondHighestSalary from emp where sal < (select Max(sal) from emp);
+---------------------+
| SecondHighestSalary |
+---------------------+
| 30000.00 |
+---------------------+
1 row in set (0.01 sec)
-- 1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
mysql> SELECT d.*, z1.cnt FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z1 WHERE d.deptno = z1.deptno;
+--------+-----------+--------+-----+
| deptno | dname | loc | cnt |
+--------+-----------+--------+-----+
| 10 | 教研部 | 北京 | 3 |
| 20 | 学工部 | 上海 | 5 |
| 30 | 销售部 | 广州 | 6 |
+--------+-----------+--------+-----+
3 rows in set (0.00 sec)
-- 3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
SELECT e.empno, e.ename, e.deptno
FROM emp e, emp m
WHERE e.mgr=m.empno AND e.hiredate<m.hiredate
union
SELECT e.empno, e.ename, d.dname
FROM emp e, emp m, dept d
WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno;
+-------+-----------+-----------+
| empno | ename | deptno |
+-------+-----------+-----------+
| 1001 | 甘宁 | 20 |
| 1002 | 黛绮丝 | 30 |
| 1003 | 殷天正 | 30 |
| 1004 | 刘备 | 20 |
| 1006 | 关羽 | 30 |
| 1007 | 张飞 | 10 |
| 1015 | 张三 | 50 |
| 1001 | 甘宁 | 学工部 |
| 1002 | 黛绮丝 | 销售部 |
| 1003 | 殷天正 | 销售部 |
| 1004 | 刘备 | 学工部 |
| 1006 | 关羽 | 销售部 |
| 1007 | 张飞 | 教研部 |
+-------+-----------+-----------+
13 rows in set (0.00 sec)
-- 4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
mysql> SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;
+-------+-----------+-----------+------+------------+----------+----------+--------+--------+-----------+--------+
| empno | ename | job | mgr | hiredate | sal | COMM | deptno | deptno | dname | loc |
+-------+-----------+-----------+------+------------+----------+----------+--------+--------+-----------+--------+
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000.00 | NULL | 20 | 20 | 学工部 | 上海 |
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000.00 | 3000.00 | 30 | 30 | 销售部 | 广州 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500.00 | 5000.00 | 30 | 30 | 销售部 | 广州 |
| 1004 | 刘备 | 经理 | 1009 | 2001-04-02 | 29750.00 | NULL | 20 | 20 | 学工部 | 上海 |
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500.00 | 14000.00 | 30 | 30 | 销售部 | 广州 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500.00 | NULL | 30 | 30 | 销售部 | 广州 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500.00 | NULL | 10 | 10 | 教研部 | 北京 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000.00 | NULL | 20 | 20 | 学工部 | 上海 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000.00 | NULL | 10 | 10 | 教研部 | 北京 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000.00 | 0.00 | 30 | 30 | 销售部 | 广州 |
| 1011 | 周泰 | 文员 | 1008 | 2007-05-23 | 11000.00 | NULL | 20 | 20 | 学工部 | 上海 |
| 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500.00 | NULL | 30 | 30 | 销售部 | 广州 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000.00 | NULL | 20 | 20 | 学工部 | 上海 |
| 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000.00 | NULL | 10 | 10 | 教研部 | 北京 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | 财务部 | 武汉 |
+-------+-----------+-----------+------+------------+----------+----------+--------+--------+-----------+--------+
15 rows in set (0.00 sec)
-- 5. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
mysql> select job,count(*) from emp where sal > 15000 group by job;
+-----------+----------+
| job | count(*) |
+-----------+----------+
| 保洁员 | 1 |
| 分析师 | 2 |
| 经理 | 3 |
| 董事长 | 1 |
| 销售员 | 1 |
+-----------+----------+
5 rows in set (0.00 sec)
-- 6. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
mysql> SELECT * FROM emp e WHERE e.deptno=(SELECT deptno FROM dept WHERE dname='销售部');
+-------+-----------+-----------+------+------------+----------+----------+--------+
| empno | ename | job | mgr | hiredate | sal | COMM | deptno |
+-------+-----------+-----------+------+------------+----------+----------+--------+
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000.00 | 3000.00 | 30 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500.00 | 5000.00 | 30 |
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500.00 | 14000.00 | 30 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500.00 | NULL | 30 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000.00 | 0.00 | 30 |
| 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500.00 | NULL | 30 |
+-------+-----------+-----------+------+------------+----------+----------+--------+
6 rows in set (0.00 sec)
-- 7. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
mysql> SELECT e.*, d.dname, m.ename, s.grade FROM emp e, dept d, emp m, salgrade s WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisal;
+-------+-----------+-----------+------+------------+----------+------+--------+-----------+-----------+-------+
| empno | ename | job | mgr | hiredate | sal | COMM | deptno | dname | ename | grade |
+-------+-----------+-----------+------+------------+----------+------+--------+-----------+-----------+-------+
| 1004 | 刘备 | 经理 | 1009 | 2001-04-02 | 29750.00 | NULL | 20 | 学工部 | 曾阿牛 | 4 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500.00 | NULL | 30 | 销售部 | 曾阿牛 | 4 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500.00 | NULL | 10 | 教研部 | 曾阿牛 | 4 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000.00 | NULL | 20 | 学工部 | 刘备 | 4 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000.00 | NULL | 20 | 学工部 | 刘备 | 4 |
+-------+-----------+-----------+------+------------+----------+------+--------+-----------+-----------+-------+
5 rows in set (0.00 sec)
--- 7.1 查询每个部门前三薪水
select e1.deptno,d.dname,e1.sal
from emp e1
left join dept d on e1.deptno=d.deptno
where
(
select count(1)
from emp e2
where e2.deptno=e1.deptno and e2.sal>=e1.sal
) <=3 /*这里的数值表示你想取前几名*/
order by deptno, sal desc;
mysql> select e1.deptno,d.dname,e1.sal from emp e1 left join dept d on e1.deptno=d.deptno where (select count(1) from emp e2 where e2.deptno=e1.deptno and e2.sal>=e1.sal) <=3 order by deptno, sal desc;
+--------+-----------+----------+
| deptno | dname | sal |
+--------+-----------+----------+
| 10 | 教研部 | 50000.00 |
| 10 | 教研部 | 24500.00 |
| 10 | 教研部 | 13000.00 |
| 20 | 学工部 | 30000.00 |
| 20 | 学工部 | 30000.00 |
| 20 | 学工部 | 29750.00 |
| 30 | 销售部 | 28500.00 |
| 30 | 销售部 | 16000.00 |
| 30 | 销售部 | 15000.00 |
+--------+-----------+----------+
9 rows in set (0.01 sec)
-- 8.列出与庞统从事相同工作的所有员工及部门名称。
mysql> SELECT e.*, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno AND job=(SELECT job FROM emp WHERE ename='庞统');
+-------+-----------+-----------+------+------------+----------+------+--------+-----------+
| empno | ename | job | mgr | hiredate | sal | COMM | deptno | dname |
+-------+-----------+-----------+------+------------+----------+------+--------+-----------+
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000.00 | NULL | 20 | 学工部 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000.00 | NULL | 20 | 学工部 |
+-------+-----------+-----------+------+------------+----------+------+--------+-----------+
2 rows in set (0.00 sec)
或者
mysql> select * from (select ename,dname,empno,job from emp inner join dept on emp.deptno = dept.deptno) t2 where job = (select job from emp where ename = '庞统');
+-----------+-----------+-------+-----------+
| ename | dname | empno | job |
+-----------+-----------+-------+-----------+
| 诸葛亮 | 学工部 | 1008 | 分析师 |
| 庞统 | 学工部 | 1013 | 分析师 |
+-----------+-----------+-------+-----------+
2 rows in set (0.00 sec)
-- 9.列出薪金高于在部门30工作的所有员工的薪金 的员工姓名和薪金、部门名称。
mysql> SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno AND sal > ALL (SELECT sal FROM emp WHERE deptno=30);
+-----------+----------+-----------+
| ename | sal | dname |
+-----------+----------+-----------+
| 刘备 | 29750.00 | 学工部 |
| 诸葛亮 | 30000.00 | 学工部 |
| 曾阿牛 | 50000.00 | 教研部 |
| 庞统 | 30000.00 | 学工部 |
+-----------+----------+-----------+
4 rows in set (0.00 sec)
-- 11 查询每个部门的平均工资
mysql> select deptno,avg(sal) from emp GROUP BY deptno;
+--------+--------------+
| deptno | avg(sal) |
+--------+--------------+
| 10 | 29166.666667 |
| 20 | 21750.000000 |
| 30 | 15666.666667 |
| 50 | 24500.000000 |
+--------+--------------+
4 rows in set (0.00 sec)
mysql> select * from dept inner join (select deptno,avg(sal) from emp GROUP BY deptno) t1 where t1.deptno = dept.deptno;
+--------+-----------+--------+--------+--------------+
| deptno | dname | loc | deptno | avg(sal) |
+--------+-----------+--------+--------+--------------+
| 10 | 教研部 | 北京 | 10 | 29166.666667 |
| 20 | 学工部 | 上海 | 20 | 21750.000000 |
| 30 | 销售部 | 广州 | 30 | 15666.666667 |
+--------+-----------+--------+--------+--------------+
3 rows in set (0.00 sec)
-- 12 查询每种工作的最高工资、最低工资、人数
mysql> SELECT job,max(sal),min(sal),count(1) from emp group by job;
+-----------+----------+----------+----------+
| job | max(sal) | min(sal) | count(1) |
+-----------+----------+----------+----------+
| 保洁员 | 24500.00 | 24500.00 | 1 |
| 分析师 | 30000.00 | 30000.00 | 2 |
| 文员 | 13000.00 | 8000.00 | 4 |
| 经理 | 29750.00 | 24500.00 | 3 |
| 董事长 | 50000.00 | 50000.00 | 1 |
| 销售员 | 16000.00 | 12500.00 | 4 |
+-----------+----------+----------+----------+
6 rows in set (0.00 sec)
关联部门表，显示出部门
mysql> select dname ,t1.* from dept inner join (SELECT deptno , job,max(sal),min(sal),count(1) from emp group by job) t1 where dept.deptno = t1.deptno;
+-----------+-----------+----------+----------+
| dname | job | max | min |
+-----------+-----------+----------+----------+
| 教研部 | 董事长 | 50000.00 | 50000.00 |
| 学工部 | 经理 | 29750.00 | 24500.00 |
| 学工部 | 文员 | 13000.00 | 8000.00 |
| 学工部 | 分析师 | 30000.00 | 30000.00 |
| 销售部 | 销售员 | 16000.00 | 12500.00 |
+-----------+-----------+----------+----------+
5 rows in set (0.00 sec)
