数据代码在文末。

dept：部门表emp：员工表salgrade ：工资等级表
deptno：部门编号empno：员工编号grade：等级
dname：部门名称ename:员工名字losal：最低薪资
loc：部门位置job：工作岗位hisal：最高薪资
mgr：上级领导编号
hiredate：入职时间
sal：月薪
comm：补助/津贴
deptno：部门编号

查询每个部门最高薪水的人员名称

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# （1）求出每个部门的最高薪水
select e.deptno,max(e.sal) maxsal from emp e
group by e.deptno; 
+--------+---------+
| deptno | maxsal  |
+--------+---------+
|     20 | 3000.00 |
|     30 | 2850.00 |
|     10 | 5000.00 |
+--------+---------+

# （2）将以上表当作一个临时表t，多表连接（join...on...）
select e.deptno,e.ename,t.maxsal,e.sal
from (select e.deptno,max(e.sal) as maxsal from emp e group by e.deptno) t
join emp e on e.deptno=t.deptno
where t.maxsal=e.sal
order by e.deptno;
+--------+-------+---------+---------+
| deptno | ename | maxsal  | sal     |
+--------+-------+---------+---------+
|     10 | KING  | 5000.00 | 5000.00 |
|     20 | SCOTT | 3000.00 | 3000.00 |
|     20 | FORD  | 3000.00 | 3000.00 |
|     30 | BLAKE | 2850.00 | 2850.00 |
+--------+-------+---------+---------+

查询哪些人的薪水在部门平均薪水之上

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# （1）部门平均薪水
select e.deptno,avg(sal) avgsal from emp e 
group by deptno; 
+--------+-------------+
| deptno | avgsal     |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+

# （2）当作临时表t，多表连接（join...on...）
select t.deptno,e.ename
from (select e.deptno,avg(sal) avgsal from emp e group by e.deptno) t
join emp e on e.deptno=t.deptno
where e.sal>t.avgsal
order by t.deptno;
+--------+-------+
| deptno | ename |
+--------+-------+
|     10 | KING  |
|     20 | JONES |
|     20 | SCOTT |
|     20 | FORD  |
|     30 | ALLEN |
|     30 | BLAKE |
+--------+-------+

查询每个部门的平均薪水等级

每个部门的平均薪水的等级

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# （1）部门的平均薪水
select e.deptno,avg(e.sal) as avgsal
from emp e
group by e.deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+

#（2）部门的薪水等级
select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

# （3）将临时表t与表salgrade进行连接,查询平均薪水在哪个等级范围中
select t.deptno,s.grade
from (select e.deptno,avg(e.sal) as avgsal from emp e group by e.deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal
order by t.deptno;
+--------+-------+
| deptno | grade |
+--------+-------+
|     10 |     4 |
|     20 |     4 |
|     30 |     3 |
+--------+-------+

每个部门的平均的薪水等级

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# （1）查询部门每个人的薪水等级在哪个等级范围中
select e.deptno,e.ename,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
order by e.deptno;
+--------+--------+-------+
| deptno | ename  | grade |
+--------+--------+-------+
|     10 | CLARK  |     4 |
|     10 | KING   |     5 |
|     10 | MILLER |     2 |
|     20 | SMITH  |     1 |
|     20 | JONES  |     4 |
|     20 | SCOTT  |     4 |
|     20 | ADAMS  |     1 |
|     20 | FORD   |     4 |
|     30 | ALLEN  |     3 |
|     30 | WARD   |     2 |
|     30 | MARTIN |     2 |
|     30 | BLAKE  |     4 |
|     30 | TURNER |     3 |
|     30 | JAMES  |     1 |
+--------+--------+-------+

# （2）将临时表t按每个部门的等级进行平均
select t.deptno,avg(t.grade) as avgGrade
from (select e.deptno,e.ename,s.grade from emp e 
    join salgrade s on e.sal between s.losal and s.hisal order by e.deptno) t
group by t.deptno;
+--------+----------+
| deptno | avgGrade |
+--------+----------+
|     20 |   2.8000 |
|     30 |   2.5000 |
|     10 |   3.6667 |
+--------+----------+

查询最高薪水（不用max函数）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
# 第一种方法
select sal from emp
order by sal desc
limit 1;
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

# 第二种方法
# （1）将两张一样的表进行比较，并且清除重复值
select distinct a.sal
from emp a
join emp b
on a.sal<b.sal;
+---------+
| sal     |
+---------+
| 1300.00 |
|  950.00 |
| 1100.00 |
| 1500.00 |
| 1250.00 |
|  800.00 |
| 2450.00 |
| 2850.00 |
| 1600.00 |
| 2975.00 |
| 3000.00 |
+---------+

# （2）选取除上表以外的最大值
select sal 
from emp 
where sal not in
(select distinct a.sal from emp a join emp b on a.sal<b.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

查询平均薪水最高的部门的部门编号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# （1）每个部门的平均薪水
select e.deptno,avg(sal) avgsal
from emp e
group by e.deptno
order by e.deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

# （2）临时表t中查询最高平均薪水
#（select中加入t.deptno，则会出错,因为会出现所有deptno，而最高只有一个平均薪水）
select max(t.avgsal) maxavgsal
from (select e.deptno,avg(sal) avgsal from emp e group by e.deptno) t;
+-------------+
| maxavgsal   |
+-------------+
| 2916.666667 |
+-------------+

# （3）使用having语句过滤
select e.deptno,avg(e.sal) as avgsal
from emp e 
group by e.deptno
having avgsal=(select max(t.avgsal) maxavgsal 
                from (select e.deptno,avg(sal) avgsal from emp e group by e.deptno) t);
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

查询平均薪水最高的部门的部门名称

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 将上一题作为临时表f，连接dept表 
select d.deptno,d.dname,f.avgsal
from (select e.deptno,avg(e.sal) as avgsal
        from emp e 
        group by e.deptno
        having avgsal=(select max(t.avgsal) maxavgsal 
                from (select e.deptno,avg(sal) avgsal from emp e group by e.deptno) t)) f
join dept d
on d.deptno=f.deptno;
+--------+------------+-------------+
| deptno | dname      | avgsal      |
+--------+------------+-------------+
|     10 | ACCOUNTING | 2916.666667 |
+--------+------------+-------------+

# 在having语句添加子查询，先将emp表与dept表先连接，再确定最高薪水
select e.deptno,d.dname,avg(e.sal) avgsal
from emp e
join dept d
on e.deptno=d.deptno
group by e.deptno,d.dname
having avgsal=(select max(t.avgsal) maxavgsal 
                from (select e.deptno,avg(sal) avgsal from emp e group by e.deptno) t);
+--------+------------+-------------+
| deptno | dname      | avgsal      |
+--------+------------+-------------+
|     10 | ACCOUNTING | 2916.666667 |
+--------+------------+-------------+

查询平均薪水的等级最低的部门的部门名称

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
# （1）每部门平均薪水的等级
select t.deptno,t.dname,s.grade
from (select e.deptno,d.dname,avg(sal) avgsal 
        from emp e join dept d on e.deptno=d.deptno
        group by deptno) t
join salgrade s 
on t.avgsal between s.losal and s.hisal;
+--------+------------+-------+
| deptno | dname      | grade |
+--------+------------+-------+
|     20 | RESEARCH   |     4 |
|     30 | SALES      |     3 |
|     10 | ACCOUNTING |     4 |
+--------+------------+-------+

# （2）平均薪水等级最低的部门
select min(f.grade) minGrade 
from (select t.deptno,t.dname,s.grade
        from (select e.deptno,d.dname,avg(sal) avgsal 
                from emp e join dept d on e.deptno=d.deptno
                group by deptno) t
        join salgrade s 
        on t.avgsal between s.losal and s.hisal) f;
+----------+
| minGrade |
+----------+
|        3 |
+----------+

# （3）最低等级的部门名称（用where或having筛选）
select 
    t.deptno,t.dname,s.grade
from 
    (select e.deptno,d.dname,avg(sal) avgsal 
        from emp e 
        join dept d 
        on e.deptno=d.deptno
        group by deptno) t
join 
    salgrade s 
on 
    t.avgsal between s.losal and s.hisal 
having 
    s.grade = (select min(f.grade) minGrade 
                from (select t.deptno,t.dname,s.grade
                        from (select e.deptno,d.dname,avg(sal) avgsal 
                                from emp e join dept d on e.deptno=d.deptno group by deptno) t
                        join salgrade s on t.avgsal between s.losal and s.hisal) f);
+--------+-------+-------+
| deptno | dname | grade |
+--------+-------+-------+
|     30 | SALES |     3 |
+--------+-------+-------+

查询比普通员工的最高薪水还要高的经理人姓名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
# 普通员工没有出现在mgr上
# （1）查找mgr
select distinct mgr from emp;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7788 |
| 7782 |
+------+

select * from emp where empno in (select distinct mgr from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+

# （2）筛选普通员工
select * from emp where empno not in (select distinct mgr from emp);
Empty set (0.00 sec)  #not in 不会自动忽略空值，in会自动忽略空值
select * from emp where empno not in (select distinct mgr from emp where mgr is not null);
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK    | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+----------+------+------------+---------+---------+--------+

# （3）查找最高薪水
select max(sal) as maxsal from emp where empno not in (select distinct mgr from emp where mgr is not null);
+---------+
| maxsal  |
+---------+
| 1600.00 |
+---------+

# （4）比普通员工最高薪水的经理人名字
select ename,sal from emp
where sal>(select max(sal) as maxsal from emp 
            where empno not in (select distinct mgr from emp where mgr is not null));
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

查询薪水最高的前五名员工

1
2
3
4
5
6
7
8
9
10
11
12
select ename,sal from emp 
order by sal desc
limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

查询薪水最高的第6-10名员工

1
2
3
4
5
6
7
8
9
10
11
12
select ename,sal from emp
order by sal desc
limit 5,5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
+--------+---------+

查询最后入职的5名员工

1
2
3
4
5
6
7
8
9
10
11
12
select ename,hiredate from emp
order by hiredate
limit 5;
+-------+------------+
| ename | hiredate   |
+-------+------------+
| SMITH | 1980-12-17 |
| ALLEN | 1981-02-20 |
| WARD  | 1981-02-22 |
| JONES | 1981-04-02 |
| BLAKE | 1981-05-01 |
+-------+------------+

查询每个薪水等级有多少员工

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# （1）每个员工的薪水等级
select e.ename,s.grade,e.sal from emp e
join salgrade s
on e.sal between s.losal and s.hisal;
+--------+-------+---------+
| ename  | grade | sal     |
+--------+-------+---------+
| SMITH  |     1 |  800.00 |
| ALLEN  |     3 | 1600.00 |
| WARD   |     2 | 1250.00 |
| JONES  |     4 | 2975.00 |
| MARTIN |     2 | 1250.00 |
| BLAKE  |     4 | 2850.00 |
| CLARK  |     4 | 2450.00 |
| SCOTT  |     4 | 3000.00 |
| KING   |     5 | 5000.00 |
| TURNER |     3 | 1500.00 |
| ADAMS  |     1 | 1100.00 |
| JAMES  |     1 |  950.00 |
| FORD   |     4 | 3000.00 |
| MILLER |     2 | 1300.00 |
+--------+-------+---------+

# （2）临时表t，分组函数group by
select t.grade,count(t.grade) courtgrade
from (select e.ename,s.grade,e.sal from emp e 
        join salgrade s on e.sal between s.losal and s.hisal) t
group by t.grade;
+-------+------------+
| grade | courtgrade |
+-------+------------+
|     1 |          3 |
|     3 |          2 |
|     2 |          3 |
|     4 |          5 |
|     5 |          1 |
+-------+------------+

列出所有员工及领导名字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 一张表当作两张表,所有员工需要left join
select e.ename,b.ename as leadername
from emp e 
left join emp b
on e.mgr=b.empno;
+--------+------------+
| ename  | leadername |
+--------+------------+
| SMITH  | FORD       |
| ALLEN  | BLAKE      |
| WARD   | BLAKE      |
| JONES  | KING       |
| MARTIN | BLAKE      |
| BLAKE  | KING       |
| CLARK  | KING       |
| SCOTT  | JONES      |
| KING   | NULL       |
| TURNER | BLAKE      |
| ADAMS  | SCOTT      |
| JAMES  | BLAKE      |
| FORD   | JONES      |
| MILLER | CLARK      |
+--------+------------+

受雇日期早于其直接上级的所有员工编号、姓名、部门名称

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
# （1）所有员工、领导的编号、姓名
select e.empno,e.deptno,e.ename,e.hiredate,b.ename as leadername,b.hiredate as leaderhiredate
from emp e
left join emp b
on e.mgr=b.empno;
+-------+--------+--------+------------+------------+----------------+
| empno | deptno | ename  | hiredate   | leadername | leaderhiredate |
+-------+--------+--------+------------+------------+----------------+
|  7369 |     20 | SMITH  | 1980-12-17 | FORD       | 1981-12-03     |
|  7499 |     30 | ALLEN  | 1981-02-20 | BLAKE      | 1981-05-01     |
|  7521 |     30 | WARD   | 1981-02-22 | BLAKE      | 1981-05-01     |
|  7566 |     20 | JONES  | 1981-04-02 | KING       | 1981-11-17     |
|  7654 |     30 | MARTIN | 1981-09-28 | BLAKE      | 1981-05-01     |
|  7698 |     30 | BLAKE  | 1981-05-01 | KING       | 1981-11-17     |
|  7782 |     10 | CLARK  | 1981-06-09 | KING       | 1981-11-17     |
|  7788 |     20 | SCOTT  | 1987-04-19 | JONES      | 1981-04-02     |
|  7839 |     10 | KING   | 1981-11-17 | NULL       | NULL           |
|  7844 |     30 | TURNER | 1981-09-08 | BLAKE      | 1981-05-01     |
|  7876 |     20 | ADAMS  | 1987-05-23 | SCOTT      | 1987-04-19     |
|  7900 |     30 | JAMES  | 1981-12-03 | BLAKE      | 1981-05-01     |
|  7902 |     20 | FORD   | 1981-12-03 | JONES      | 1981-04-02     |
|  7934 |     10 | MILLER | 1982-01-23 | CLARK      | 1981-06-09     |
+-------+--------+--------+------------+------------+----------------+

# （2）早于上级的所有员工的编号、姓名
select t.deptno,t.ename
from (select e.deptno,e.ename,e.hiredate,b.ename as leadername,b.hiredate as leaderhiredate
        from emp e left join emp b on e.mgr=b.empno) t
where t.hiredate<t.leaderhiredate;
+--------+-------+
| deptno | ename |
+--------+-------+
|     20 | SMITH |
|     30 | ALLEN |
|     30 | WARD  |
|     20 | JONES |
|     30 | BLAKE |
|     10 | CLARK |
+--------+-------+

# （3）早于上级的所有员工的编号、姓名、部门名称
select f.empno,f.ename,d.dname
from (select t.empno,t.deptno,t.ename
        from (select e.empno,e.deptno,e.ename,e.hiredate,b.ename leadername,b.hiredate 
               leaderhiredate
                from emp e left join emp b on e.mgr=b.empno) t
        where t.hiredate<t.leaderhiredate) f
join dept d
on d.deptno=f.deptno;
+-------+-------+------------+
| empno | ename | dname      |
+-------+-------+------------+
|  7369 | SMITH | RESEARCH   |
|  7499 | ALLEN | SALES      |
|  7521 | WARD  | SALES      |
|  7566 | JONES | RESEARCH   |
|  7698 | BLAKE | SALES      |
|  7782 | CLARK | ACCOUNTING |
+-------+-------+------------+
# 化简版
select d.dname,e.empno,e.ename
from emp e
join emp b
on e.mgr=b.empno
join dept d
on e.deptno=d.deptno
where e.hiredate>b.hiredate;
+------------+-------+--------+
| dname      | empno | ename  |
+------------+-------+--------+
| SALES      |  7654 | MARTIN |
| RESEARCH   |  7788 | SCOTT  |
| SALES      |  7844 | TURNER |
| RESEARCH   |  7876 | ADAMS  |
| SALES      |  7900 | JAMES  |
| RESEARCH   |  7902 | FORD   |
| ACCOUNTING |  7934 | MILLER |
+------------+-------+--------+

列出部门名称和员工信息，同时列出没有员工的部门

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
select d.dname,e.*
from emp e
right join dept d
on e.deptno=d.deptno;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| ACCOUNTING |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
| ACCOUNTING |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
| ACCOUNTING |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
| RESEARCH   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
| RESEARCH   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
| RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
| RESEARCH   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
| RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
| SALES      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
| SALES      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
| SALES      |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
| SALES      |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
| SALES      |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
| SALES      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
| OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+

列出至少5个员工的所有部门

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# （1）每个部门的员工数量
select e.deptno,count(e.ename) as totalEmp
from emp e
group by e.deptno;
+--------+----------+
| deptno | totalEmp |
+--------+----------+
|     20 |        5 |
|     30 |        6 |
|     10 |        3 |
+--------+----------+

# （2）有5个员工的部门
select t.deptno,t.totalEmp
from (select e.deptno,count(e.ename) as totalEmp
        from emp e group by e.deptno) t
where t.totalEmp>=5;
+--------+----------+
| deptno | totalEmp |
+--------+----------+
|     20 |        5 |
|     30 |        6 |
+--------+----------+

select e.deptno,count(e.ename) as totalEmp
from emp e
group by e.deptno
having totalEmp>=5;
+--------+----------+
| deptno | totalEmp |
+--------+----------+
|     20 |        5 |
|     30 |        6 |
+--------+----------+

列出薪水比SMITH多的所有员工信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# （1）查询"SMITH"薪水
select sal from emp where ename="SMITH";
+--------+
| sal    |
+--------+
| 800.00 |
+--------+

# （2）比较
select * from emp where sal>(select sal from emp where ename="SMITH");
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

——数据：bjpowernode.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7369, 'SMITH', 'CLERK', 7902,  '1980-12-17', 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20', 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22', 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839,  '1981-04-02', 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7654,'MARTIN', 'SALESMAN', 7698,  '1981-09-28', 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01', 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09', 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17', 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08', 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23', 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7900, 'JAMES', 'CLERK', 7698,  '1981-12-03', 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7902, 'FORD', 'ANALYST', 7566,  '1981-12-03', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES ( 7934, 'MILLER', 'CLERK', 7782,  '1982-01-23', 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 5, 3001, 9999); 
commit;
dept
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

emp
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

salgrade
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+