other
mysql

02关联表查询

# 字段去重

使用distinct对结果集去重复。

#查询有多少种工作岗位
select
 distinct job
from EMP;

1
2
3
4

distinct 只能出现在所有字段的最前面；去重，并不会改变原数据结构。

# distinct会把后面的所有字段联合起来去重
select 
 distinct deptno, job
from EMP;

+--------+-----------+
| deptno | job       |
+--------+-----------+
|     20 | CLERK     |
|     30 | SALESMAN  |
|     20 | MANAGER   |
|     30 | MANAGER   |
|     10 | MANAGER   |
|     20 | ANALYST   |
|     10 | PRESIDENT |
|     30 | CLERK     |
|     10 | CLERK     |
+--------+-----------+
9 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 关联查询

查询员工所在部门名称，并显示员工名称；由于数据在2张表中，员工名称ename在EMP表，部门信息在DEPT表中。

#首先要给表进行命名，这样避免两个表的字段存在重复。
#添加where查询条件，可以过滤出来需要显示的信息。
mysql> select e.ename, d.dname from EMP e, DEPT d where e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

#符合笛卡尔积查询，显示56条数据。
select e.ename, d.dname from EMP e, DEPT d;

1
2

当添加where过滤条件时，并不会影响笛卡尔积匹配查询的次数。

# 内连接-等值查询

内连接的等值查询，条件是等量关系

#查询每个员工的部门名称，要求显示员工名和部门名
select 
 e.ename,d.dname 
from 
 EMP e
join
 DEPT d
on
 e.deptno = d.deptno;
+-----------+--------------+
| 员工名     | 部门名称       |
+-----------+--------------+
| CLARK     | ACCOUNTING   |
| KING      | ACCOUNTING   |
| MILLER    | ACCOUNTING   |
| SMITH     | RESEARCH     |
| JONES     | RESEARCH     |
| SCOTT     | RESEARCH     |
| ADAMS     | RESEARCH     |
| FORD      | RESEARCH     |
| ALLEN     | SALES        |
| WARD      | SALES        |
| MARTIN    | SALES        |
| BLAKE     | SALES        |
| TURNER    | SALES        |
| JAMES     | SALES        |
+-----------+--------------+
14 rows in set (0.01 sec)

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

# 内连接-非等值查询

内连接中的非等值连接，连接条件中的关系是非等量关系；比如成绩分数不显示具体分数，而是根据分数显示等级

#查询出每个员工的工资等级，并显示员工的姓名、工资、工资等级
#第一步先查询工资
mysql> select ename,sal from EMP;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
14 rows in set (0.01 sec)
#第二步查询工资等级
mysql> select * from SALGRADE;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
#第三步，将每个员工的工资匹配下工资等级
mysql> select e.ename, e.sal, s.grade
 from
  EMP e
 join
  SALGRADE s
 on
  e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.01 sec)

#还可以按照薪资等级排序
mysql> select e.ename, e.sal, s.grade
 from
  EMP e
 join
  SALGRADE s
 on
  e.sal between s.losal and s.hisal;
 order by
  grade;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| MARTIN | 1250.00 |     2 |
| MILLER | 1300.00 |     2 |
| WARD   | 1250.00 |     2 |
| ALLEN  | 1600.00 |     3 |
| TURNER | 1500.00 |     3 |
| FORD   | 3000.00 |     4 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| JONES  | 2975.00 |     4 |
| KING   | 5000.00 |     5 |
+--------+---------+-------+
14 rows in set (0.01 sec)

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

# 内连接-自连接查询

在同一个表内数据的查询, 特点是：一张表看成2张表；自己和自己的一些字段相互连接查询。

#查询员工的领导，显示员工名和领导名,[存在的关系：员工的领导编号MGR = 领导的员工编号EMPNO]
#由于领导(MGR)也是数据员工表，所以是EMP表自己连接自己进行查询
mysql> select
  e.ename as '员工', s.ename as '领导'
 from
  EMP e
 join
  EMP s
 on
  e.mgr = s.empno;
+--------+--------+
| 员工   | 领导   |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
13 rows in set (0.01 sec)

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

注意：此时查询出来的数据为13条，少了 KING数据的显示，因为 KING 的 MGR 为 NULL 无法进行等值匹配，所以把数据给删除了。为了解决这个问题，必须使用下面的外连接查询。

# 外连接查询

# 内连接和外连接的区别：

内连接：

a表和b表进行连接，两张表能匹配的数据查询出来【匹配不上会丢失，比如上面的KING数据】。a表和b表没有主副之分。两张表是平等的。

外连接

a表和b表进行连接，一张表是主表，一张是副表。主要查询主表数据，如果副表的数据没有和主表相匹配上，副表会自动模拟出 NULL 为之相互匹配。 总结：外连接查询，主表的数据不会丢失。

# 外连接分类：

左外连接，表示左表是主表
右外连接，表示右表是主表

#内连接
mysql> select
  e.ename as '员工', s.ename as '领导'
 from
  EMP e
 join
  EMP s
 on
  e.mgr = s.empno;
#外连接
mysql> select
  e.ename as '员工', s.ename as '上级'
 from 
  EMP e
 left join
  EMP s
 on 
  e.mgr = s.empno;
+--------+--------+
| 员工    | 上级   |
+--------+--------+
| 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  |
+--------+--------+
14 rows in set (0.00 sec)

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

使用外连接，可以把主表的所有数据都显示出来，如果没有和副表匹配上的，副表会自动填充NULL数据。

在join前添加 left或者right 就是左外连接或右外连接。

left 左边的表就是主表。

right 右边的表是主表

#右外连接，查询员工和领导
mysql> select
  e.ename as '111', s.ename as '222'
 from
  EMP s
 right join
  EMP e
 on
  e.mgr = s.empno;
+--------+-------+
| 员工    | 上级   |
+--------+-------+
| 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 |
+--------+-------+
14 rows in set (0.00 sec)

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

外连接特点，主表数据不会丢失，匹配不出来的会填充NUll

#找出哪个部门没有员工，【首先确定下来主表】
mysql> select
  e.*, d.*
 from 
  EMP e
 right join
  DEPT d
 on
  e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | DEPTNO | DNAME      | LOC      |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |     40 | OPERATIONS | BOSTON   |
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
15 rows in set (0.00 sec)

mysql> select
 e.*, d.*
from 
 EMP e
right join
 DEPT d
on
 e.deptno = d.deptno;
where
 e.empno is null;
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
| EMPNO | ENAME | JOB  | MGR  | HIREDATE | SAL  | COMM | DEPTNO | DEPTNO | DNAME      | LOC    |
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
|  NULL | NULL  | NULL | NULL | NULL     | NULL | NULL |   NULL |     40 | OPERATIONS | BOSTON |
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
1 row in set (0.00 sec)

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

# 3张表连接查询

先处理两张表的查询，然后在查其它表 ... A join B on 条件 join C on ... 表示： A表和B表先进行连接查询，再和C表进行连接查询

#找出每个员工的员工名、部门名称、工资等级；
第一步先把需要的数据查询并显示出来
mysql> select
 e.ename, e.sal, e.deptno
from 
 EMP e;
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| SMITH  |  800.00 |     20 |
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| JAMES  |  950.00 |     30 |
| FORD   | 3000.00 |     20 |
| MILLER | 1300.00 |     10 |
+--------+---------+--------+
14 rows in set (0.00 sec)
#显示工资表
mysql> select * from  SALGRADE;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.04 sec)

#先使用EMP和DEPT表关联查询，然后再用EMP和SALGRADE关联查询
mysql> select 
 e.ename, d.dname, s.grade
from 
 EMP e
join
 DEPT d
on
 e.deptno = d.deptno
join
 SALGRADE s
on
 e.sal between s.losal and s.hisal;

+--------+------------+-------+
| ename  | dname      | grade |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+
14 rows in set (0.04 sec)

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

查询员工的部门、薪资等级、上级领导【需要使用到外连接】

mysql> select
 e.ename as '员工姓名', d.dname as '部门', s.grade as '薪资等级', e1.ename as '上级领导'
from
 EMP e
join
 DEPT d
on
 e.deptno = d.deptno
join
 SALGRADE s
on
 e.sal between s.losal and s.hisal
left join
 EMP e1
on
 e.mgr = e1.empno;

+--------------+------------+--------------+--------------+
| 员工姓名     | 部门       | 薪资等级     | 上级领导     |
+--------------+------------+--------------+--------------+
| SMITH        | RESEARCH   |            1 | FORD         |
| ALLEN        | SALES      |            3 | BLAKE        |
| WARD         | SALES      |            2 | BLAKE        |
| JONES        | RESEARCH   |            4 | KING         |
| MARTIN       | SALES      |            2 | BLAKE        |
| BLAKE        | SALES      |            4 | KING         |
| CLARK        | ACCOUNTING |            4 | KING         |
| SCOTT        | RESEARCH   |            4 | JONES        |
| KING         | ACCOUNTING |            5 | NULL         |
| TURNER       | SALES      |            3 | BLAKE        |
| ADAMS        | RESEARCH   |            1 | SCOTT        |
| JAMES        | SALES      |            1 | BLAKE        |
| FORD         | RESEARCH   |            4 | JONES        |
| MILLER       | ACCOUNTING |            2 | CLARK        |
+--------------+------------+--------------+--------------+
14 rows in set (0.00 sec)

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

# 子查询

子查询就是select语句中可以嵌套select语句，被嵌套的语句就是子查询。子查询可以出现在select、from、where后面

# where后面嵌套子查询

找出高于平均薪资的员工

第一步可以分组查询算出平均工资
mysql> select avg(sal) from EMP;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)
第二步使用子查询，把select语句放在where后
select * 
from
 EMP
where
 sal > (select avg(sal) from EMP);

+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  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 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.00 sec)

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

# from后面嵌套子查询

找出每个部门平均薪水的薪资等级。【先算平均薪水，再查询等级】

#按照部门分组，算出平均薪资
mysql> select deptno, avg(sal) as avgsal from EMP group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.01 sec)

# 把上面查出来的结果当作一张临时表t, 让t表和SALGRADE s表连接,条件是
# t.avgsal between s.losal and s.hisal
mysql> select 
 t.deptno,s.grade 
from 
 (select deptno, avg(sal) as avgsal from EMP group by deptno) t
join
 SALGRADE s
on 
 t.avgsal between s.losal and s.hisal;

+--------+-------+
| deptno | grade |
+--------+-------+
|     10 |     4 |
|     20 |     4 |
|     30 |     3 |
+--------+-------+
3 rows in set (0.01 sec)

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

找出每个部门的薪水等级的平均值。

#先找出每个员工的薪水等级
mysql> select
 e.ename,e.deptno,e.sal,s.grade 
from
 EMP e
join
 SALGRADE s
on
 e.sal between s.losal and s.hisal;
+--------+--------+---------+-------+
| ename  | deptno | sal     | grade |
+--------+--------+---------+-------+
| SMITH  |     20 |  800.00 |     1 |
| ALLEN  |     30 | 1600.00 |     3 |
| WARD   |     30 | 1250.00 |     2 |
| JONES  |     20 | 2975.00 |     4 |
| MARTIN |     30 | 1250.00 |     2 |
| BLAKE  |     30 | 2850.00 |     4 |
| CLARK  |     10 | 2450.00 |     4 |
| SCOTT  |     20 | 3000.00 |     4 |
| KING   |     10 | 5000.00 |     5 |
| TURNER |     30 | 1500.00 |     3 |
| ADAMS  |     20 | 1100.00 |     1 |
| JAMES  |     30 |  950.00 |     1 |
| FORD   |     20 | 3000.00 |     4 |
| MILLER |     10 | 1300.00 |     2 |
+--------+--------+---------+-------+
14 rows in set (0.00 sec)
#第二步，按照 deptno 分组，求grade的平均值
mysql> select
 e.ename,e.deptno,avg(s.grade) as '部门薪资等级平均值'
from
 EMP e
join
 SALGRADE s
on
 e.sal between s.losal and s.hisal
group by
 e.deptno;

+-------+--------+-----------------------------+
| ename | deptno | 部门薪资等级平均值          |
+-------+--------+-----------------------------+
| CLARK |     10 |                      3.6667 |
| SMITH |     20 |                      2.8000 |
| ALLEN |     30 |                      2.5000 |
+-------+--------+-----------------------------+
3 rows in set (0.00 sec)

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

# select后面嵌套子查询

显示每个员工的名称和所在部门名称

# 第一种方法，使用的关联查询，查询的次数更多
select e.ename, d.dname
from
 EMP e
join
 DEPT d
on
 e.deptno = d.deptno;

+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.03 sec)

# 第二种方法，没有使用关联查询
select 
 e.ename,(select d.dname from DEPT d where e.deptno = d.deptno) as dname
from 
 EMP e;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

# 可见，下面这种方法查询的更快

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

# union联合查询

可以将查询结果相加；

#找出工作岗位是 MANAGER 和 SALESMAN 的员工；
#第一种： select ename,job from EMP where job='SALESMAN' or job = 'MANAGER';
#第二种： select ename,job from EMP where job in('SALESMAN', 'MANAGER');
#第三种：
select ename,job from EMP where job='SALESMAN'
union
select ename,job from EMP where job='MANAGER';

+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
+--------+----------+
7 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# limit查询,[处理分页查询]

limit取结果集中的部分数据，语法：limit startIndex, length startIndex: 表示起始位置 length：取数据的长度取出工资前5名的员工【将员工工资降序排序，取前面5个】

select
 ename,sal
from
 EMP
order by 
	sal 
desc
limit 0,5;

+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# sql语句执行顺序

【from - where - group by - having - select - order by - limit】 ** 从哪分组过滤查排序分页**

select   ------5
...
from     ------1
...
where    ------2
...
group by ------3
...
having   ------4
...
order by ------6
...
limit    ------7
... ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

本站部分内容来源网络转载，如有侵权，请联系删除；本站不负任何版权责任！

编辑

上次更新: 2025/10/22, 08:49:36

← 01初步认识mysql和单表查询 03数据表的编辑、事务、索引→