MySQL——连接查询

最新推荐文章于 2023-08-04 00:31:15 发布
最新推荐文章于 2023-08-04 00:31:15 发布
阅读量434 收藏
点赞数
分类专栏： MySQL数据库基础+高级 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36879493/article/details/121680791
版权

连接查询

文章目录


前提：数据准备 

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;

1、什么是连接查询？

从一张表中单独查询，称为单表查询。

emp 表和 dept 表联合起来查询数据，从 emp 表中取员工名字，从 dept 表中取部门名字。

这种跨表查询，多张表联合起来查询数据，被称为连接查询。

2、连接查询的分类

根据表连接的方式分类：

  1. 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  2. 外连接
    • 左外连接（左连接）
    • 右外连接（右连接）
  3. 全连接

3、当两张表进行连接查询时，没有任何条件的限制会发生什么现象？

案例：查询每个员工所在部门名称？

mysql> select ename,deptno from emp;
+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
| MILLER |     10 |
+--------+--------+		//14条记录

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+		//4条记录

两张表连接没有任何条件限制：

select ename,dname from emp, dept;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
...
56 rows in set (0.00 sec)
14 * 4 = 56

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象。（笛卡尔发现的，这是一个数学现象。）

3.1、如何避免笛卡尔积现象？

连接时加条件，满足这个条件的记录被筛选出来！

select 
	ename,dname 
from 
	emp, dept
where
	emp.deptno = dept.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 条，但是匹配的过程中，匹配的次数减少了吗？还是 56 次，只不过进行了四选一。次数没有减少。

注意：通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的连接次数。

提高效率的写法：

select 
	emp.ename,dept.dname 
from 
	emp, dept
where
	emp.deptno = dept.deptno;

// 表起别名。很重要。效率问题。
select 
	e.ename,d.dname 
from 
	emp e, dept d
where
	e.deptno = d.deptno; //SQL92语法。

ename 不会去 dept 表里边查找；同理，dname 不会去 emp 表里边查找。

4、内连接

4.1、内连接之等值连接

案例：查询每个员工所在部门名称，显示员工名和部门名？
emp e 和 dept d 表进行连接。条件是：e.deptno = d.deptno

SQL92 语法：

select 
	e.ename,d.dname
from
	emp e, dept d
where
	e.deptno = d.deptno;

sql92 的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了 where 后面。

SQL99 语法：

select 
	e.ename, d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno;
	
	
//inner 可以省略（带着 inner 可读性更好！！！一眼就能看出来是内连接）
select 
	e.ename,d.dname
from
	emp e
inner join
	dept d
on
	e.deptno = d.deptno; //条件是等量关系，所以被称为等值连接。

sql99 优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加 where。

SQL99 语法：

select 
	...
from
	a
join
	b
on
	a 和 b 的连接条件
where
	筛选条件

4.2、内连接之非等值连接

案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

mysql> select * from emp; e
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
....

mysql> select * from salgrade; s
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

分析：我们要查询 e 表中员工名（ename）、薪资（sal）和 s 表中的薪资等级（grade），条件为当员工的薪资位于 s 表中的最低水平和最高水平之间时显示：

select 
	e.ename, e.sal, s.grade
from
	emp e
[inner] 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 |
+--------+---------+-------+

4.3、内连接之自连接

案例：查询员工的上级领导，要求显示员工名和对应的领导名？

我们可以从 emp 表中查到 empno 和 ename 和 mgr：

mysql> select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

技巧：一张表看成两张表。

emp a 员工表
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

emp b 领导表
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

我们需要查找的是员工表中的 mgr 等于领导表中的 empno。

select 
	a.ename as '员工名', b.ename as '领导名'
from
	emp a
join
	emp b
on
	a.mgr = b.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条记录，没有KING。《内连接》

以上就是内连接中的：自连接，技巧：一张表看做两张表。

5、外连接

mysql> select * from emp; e
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

mysql> select * from dept; d
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

内连接：（A 和 B 连接，AB 两张表没有主次关系。平等的。）

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      |
+--------+------------+

right 代表什么：表示将 join 关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。在外连接当中，两张表连接，产生了主次关系。

外连接（右外连接）：
select 
	e.ename,d.dname
from
	emp e 
right join 
	dept d
on
	e.deptno = d.deptno;

// outer是可以省略的，带着可读性强。
select 
	e.ename,d.dname
from
	emp e 
right outer join 
	dept d
on
	e.deptno = d.deptno;

外连接（左外连接）：
select 
	e.ename,d.dname
from
	dept d 
left join 
	emp e
on
	e.deptno = d.deptno;

// outer是可以省略的，带着可读性强。
select 
	e.ename,d.dname
from
	dept d 
left outer join 
	emp e
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      |
| NULL   | OPERATIONS |
+--------+------------+

带有 right 的是右外连接，又叫做右连接。
带有 left 的是左外连接，又叫做左连接。
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法。

思考：外连接的查询结果条数一定是 >= 内连接的查询结果条数？
正确

案例：查询每个员工的上级领导，要求显示所有员工的名字和领导名？

select 
	a.ename as '员工名', b.ename as '领导名'
from
	emp a
left join
	emp b
on
	a.mgr = b.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  |
+--------+--------+

6、三张表、四张表怎么连接

语法：

select 
	...
from
	a
join
	b
on
	a和b的连接条件
join
	c
on
	a和c的连接条件
right join
	d
on
	a和d的连接条件

一条 SQL 中内连接和外连接可以混合。都可以出现！

案例：找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级？

select 
	e.ename,e.sal,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  | sal     | dname      | grade |
+--------+---------+------------+-------+
| SMITH  |  800.00 | RESEARCH   |     1 |
| ALLEN  | 1600.00 | SALES      |     3 |
| WARD   | 1250.00 | SALES      |     2 |
| JONES  | 2975.00 | RESEARCH   |     4 |
| MARTIN | 1250.00 | SALES      |     2 |
| BLAKE  | 2850.00 | SALES      |     4 |
| CLARK  | 2450.00 | ACCOUNTING |     4 |
| SCOTT  | 3000.00 | RESEARCH   |     4 |
| KING   | 5000.00 | ACCOUNTING |     5 |
| TURNER | 1500.00 | SALES      |     3 |
| ADAMS  | 1100.00 | RESEARCH   |     1 |
| JAMES  |  950.00 | SALES      |     1 |
| FORD   | 3000.00 | RESEARCH   |     4 |
| MILLER | 1300.00 | ACCOUNTING |     2 |
+--------+---------+------------+-------+

案例：找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级？

select 
	e.ename,e.sal,d.dname,s.grade,l.ename
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 l
on
	e.mgr = l.empno;

+--------+---------+------------+-------+-------+
| ename  | sal     | dname      | grade | ename |
+--------+---------+------------+-------+-------+
| SMITH  |  800.00 | RESEARCH   |     1 | FORD  |
| ALLEN  | 1600.00 | SALES      |     3 | BLAKE |
| WARD   | 1250.00 | SALES      |     2 | BLAKE |
| JONES  | 2975.00 | RESEARCH   |     4 | KING  |
| MARTIN | 1250.00 | SALES      |     2 | BLAKE |
| BLAKE  | 2850.00 | SALES      |     4 | KING  |
| CLARK  | 2450.00 | ACCOUNTING |     4 | KING  |
| SCOTT  | 3000.00 | RESEARCH   |     4 | JONES |
| KING   | 5000.00 | ACCOUNTING |     5 | NULL  |
| TURNER | 1500.00 | SALES      |     3 | BLAKE |
| ADAMS  | 1100.00 | RESEARCH   |     1 | SCOTT |
| JAMES  |  950.00 | SALES      |     1 | BLAKE |
| FORD   | 3000.00 | RESEARCH   |     4 | JONES |
| MILLER | 1300.00 | ACCOUNTING |     2 | CLARK |
+--------+---------+------------+-------+-------+

7、子查询

7.1、什么是子查询？

select 语句中嵌套 select 语句，被嵌套的 select 语句称为子查询。

7.2、子查询都可以出现在哪里呢？

select
	..(select).
from
	..(select).
where
	..(select).

7.3、where 子句中的子查询

案例：找出比最低工资高的员工姓名和工资？

select 
	ename,sal
from
	emp 
where
	sal > min(sal);
	
ERROR 1111 (HY000): Invalid use of group function
where子句中不能直接使用分组函数。

实现思路：

//第一步：查询最低工资是多少
select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+

//第二步：找出>800的
select ename,sal from emp where sal > 800;

//第三步：合并
select ename,sal from emp where sal > (select min(sal) from emp);
+--------+---------+
| ename  | sal     |
+--------+---------+
| 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 |
+--------+---------+

7.4、from 子句中的子查询

注意：from 后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）

案例：找出每个岗位的平均工资的薪资等级。

//第一步：找出每个岗位的平均工资（按照岗位分组求平均值）
select job,avg(sal) from emp group by job;
+-----------+-------------+
| job       | avgsal      |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| CLERK     | 1037.500000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |
| SALESMAN  | 1400.000000 |
+-----------+-------------+t表

//第二步：克服心理障碍，把以上的查询结果就当做一张真实存在的表t。
mysql> select * from salgrade; s表
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
//t表和s表进行表连接，条件：t表avg(sal) between s.losal and s.hisal;

select 
	t.*, s.grade
from
	(select job,avg(sal) as avgsal from emp group by job) t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;

+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| ANALYST   | 3000.000000 |     4 |
| MANAGER   | 2758.333333 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+

7.5、select 后面出现的子查询（这个内容不需要掌握，了解即可！！！）

案例：找出每个员工的部门名称，要求显示员工名，部门名？

select 
	e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname 
from 
	emp e;

+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+

//错误：ERROR 1242 (21000): Subquery returns more than 1 row
select 
	e.ename,e.deptno,(select dname from dept) as dname
from
	emp e;

注意：对于 select 后面的子查询来说，这个子查询只能一次返回 1 条结果，多于 1 条，就报错了。！

8、UNION 合并查询结果集

案例：查询工作岗位是 MANAGER 和 SALESMAN 的员工？

select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
select ename,job from emp where job in('MANAGER','SALESMAN');
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+

union 的效率要高一些。对于表连接来说，每连接一次新表，
则匹配的次数满足笛卡尔积，成倍的翻。。。
但是 union 可以减少匹配的次数。在减少匹配次数的情况下，
还可以完成两个结果集的拼接。

a 连接 b 连接 c
a 10 条记录
b 10 条记录
c 10 条记录
匹配次数是：1000

a 连接 b 一个结果：10 * 10 --> 100次
a 连接 c 一个结果：10 * 10 --> 100次
使用 union 的话是：100 次 + 100 次 = 200 次。（union 把乘法变成了加法运算）

union 在使用的时候有注意事项吗？

//错误的：union在进行结果集合并的时候，要求两个结果集的列数相同。
select ename,job from emp where job = 'MANAGER'
union
select ename from emp where job = 'SALESMAN';

// MYSQL可以，oracle语法严格，不可以，报错。要求：结果集合并时列和列的数据类型也要一致。
select ename,job from emp where job = 'MANAGER'
union
select ename,sal from emp where job = 'SALESMAN';
+--------+---------+
| ename  | job     |
+--------+---------+
| JONES  | MANAGER |
| BLAKE  | MANAGER |
| CLARK  | MANAGER |
| ALLEN  | 1600    |
| WARD   | 1250    |
| MARTIN | 1250    |
| TURNER | 1500    |
+--------+---------+

9、limit（非常重要）

9.1、limit 的作用

将查询结果集的一部分取出来。通常使用在分页查询当中。

分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体验差。可以一页一页翻页看。

9.2、limit 怎么用呢？

完整用法：limit startIndex, length

  • startIndex 是起始下标，length 是长度。
  • 起始下标从 0 开始。

缺省用法：limit 5; 这是取前 5。

案例：按照薪资降序，取出排名在前 5 名的员工

select 
	ename,sal
from
	emp
order by 
	sal desc
limit 5; //取前5

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

+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

注意：mysql 当中 limit 在 order by 之后执行！！！！！！

案例：取出工资排名在 [3-5] 名的员工

select 
	ename,sal
from
	emp
order by
	sal desc
limit
	2, 3;

2表示起始位置从下标2开始，就是第三条记录。
3表示长度。

+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

案例：取出工资排名在 [5-9] 名的员工

select 
	ename,sal
from
	emp
order by 
	sal desc
limit
	4, 5;

+--------+---------+
| ename  | sal     |
+--------+---------+
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+

9.3、分页

每页显示 3 条记录
第 1 页：limit 0, 3 [0 1 2]
第 2 页：limit 3, 3 [3 4 5]
第 3 页：limit 6, 3 [6 7 8]
第 4 页：limit 9, 3 [9 10 11]

每页显示 pageSize 条记录
第 pageNo 页：limit (pageNo - 1) * pageSize , pageSize

public static void main(String[] args){
	// 用户提交过来一个页码，以及每页显示的记录条数
	int pageNo = 5; //第5页
	int pageSize = 10; //每页显示10条

	int startIndex = (pageNo - 1) * pageSize;
	String sql = "select ...limit " + startIndex + ", " + pageSize;
}

记公式：
limit (pageNo-1) * pageSize , pageSize

10、关于 DQL 语句的大总结

select 
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...
limit
	...

执行顺序

  1. from
  2. where
  3. group by
  4. having
  5. select
  6. order by
  7. limit…
前丨尘忆·梦
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL连接查询——MySQL 基础查询你会了吗？
12-14
前言： 由于大一学习数据库不扎实，学到后面有点吃力，所以回过头来认真学习一边，写一些学习总结，提醒自己。也要告诫读者，把基础打扎实。读者觉得有用的话可以收藏点赞哦！ 上一篇：MySQL函数查询——MySQL 基础查询你会了吗？ 文章目录2、MySql连接查询2.1、内连接(sql92 和sql99)2.1.1、sql92`1、等值连接`2、 为表起别名6、可以加排序吗？7、可以实现三表连接吗？2、非等值连接3、自连接2.1.2、sql99标准1、等值连接2、非等值连接3、 自连接2.2、外连接左外连接右外连接2.3、交叉连接(笛卡尔积) 2、MySql连接查询 年代划分 分sql92 和sq
mysql join不加关联条件的含义
鸭梨的博客
04-18 3710
select * from student,course; +----+------+----------+-----+----+------+----+ |s_id|s_name|s_birth |s_sex|c_id|c_name|t_id| +----+------+----------+-----+----+------+----+ |01 |赵雷 |1990-01-01|男 |01 |语文 |02 | |01 |赵雷 |1990-01-01|男 |02
MySQL连接查询
health的博客
05-08 9258
首先创建两个表 fruits表，包含水果 id、名字、价格 orders表，包含 id 和订单号（num） 1.内连接查询（INNOR JOIN） 使用普通sql语句 select fruits.id,name,price,num from fruits,orders where fruits.id=orders.id; 使用内连接查询语句（结果与上图相同） s...
MySQL--多表连接查询
zxn200125的博客
12-26 404
多表连接查询，简述表之间的关系，以及笛卡尔积查询方式和子查询方式
mysql 多表连接
weixin_43548518的博客
12-23 1009
select * from a,b —笛卡尔积 SELECT * FROM A JOIN B --隐式内连接 SELECT * FROM A INNER JOIN B–显示内连接 select* from a , b where a.id=b.id
mysql 或hive left join不加关联条件
鸭梨的博客
04-07 2930
mysql left join不加关联条件 create table if not exists u1 ( id int, name varchar(20) ) ENGINE = MyISAM DEFAULT CHARSET = utf8; insert into u1 values (1, 'a'); insert into u1 values (2, 'b'); insert into u1 values (3, 'c'); create table if not exist
你知道left join左连接条件在on和where上有什么区别吗？
最新发布
linyuchen820的博客
08-04 136
left join左连接条件在on和where上的区别
聊聊sql的join语句
weixin_46378680的博客
02-15 1209
之前面试官问了join语句，想来自己对join语句的理解就是那几张集合图，感觉没有很清楚，论坛上的大多文章也是几个集合图来敷衍，因此本文实际操作数据库，演示join语句的用法。 首先建立两张表： student表： teacher表： 首先join语句一般情况下是要跟连接条件on的，那么可以不加连接条件吗？答案是：可以 如果不加连接条件，执行SELECT * FROM student INNER JOIN teacher; 发现结果是两个表的笛卡尔积！ 加上连接条件S...
【MySQL作业】外连接查询——美和易思外连接查询应用习题
01-21
由于需要获取所有客户的基本信息，如果采用左连接加以实现，必须从左至右依次连接客户表和订单表，此时客户表将作为左表。 select cName 姓名 , ordersID 订单 ID, ordersDate 下单日期 from customer c left join ...
mysql连接——jdbc驱动
03-28
用于mysql的连接，一个驱动文件jar包，用于连接mysql数据库
MYSQL——C API测试例程
10-22
在VS2017下编程的X64DEBUG模式下连接X64MYSQL5.6.38版本的测试例程
JAVA之连接MySQL数据库——源码下载
09-07
JAVA之连接MySQL数据库——源码下载 JAVA之连接MySQL数据库——源码下载 JAVA之连接MySQL数据库——源码下载
26.MySQL中的内连接INNER JOIN
码海无际的博客
04-17 452
目录 1.简介 2.准备 3.select ...... from A,B 的用法 4.select ...... from A,B where ......的用法(sql92标准) 5.select ...... from A join B on ......的用法(sql99标准) 1.简介 内连接是一种常见的连接查询，它根据匹配条件返回第1个表与第2个表所有匹配成功的记录。 ...
(十四) SQL连接查询
jssd的博客
07-30 330
文章目录多表查询多表链接方式内连接:外连接子查询笛卡尔乘积什么是笛卡尔乘积如何避免出现笛卡尔乘积多表连接语法语法结构使用原则等值连接什么时等值连接使用表的别名表别名使用表别名的原则多于两个表的连接非等值连接自连接外连接什么是外连接什么是孤儿数据外连接类型 (SQL99)SQL99中的外连接语法格式左外连接(LEFT OUTER JOIN)右外连接(RIGHT OUTER JOIN)全外连接Orac...
有趣的SQL二：两个表没连接条件的JOIN
热门推荐
老叶茶馆
07-26 1万+
文章节选自松华老师《SQL开发与优化》专栏，想阅读更多内容请扫码订阅今天逛论坛看到了一个，奇葩需求，他想把两个表的结果连在一起显示我把需求整理如下 ：下面有两个表 a 和...
数据库 连接基础知识 等值连接 自然连接
qq_23835497的博客
07-24 8489
数据库连接： 一、等值连接 1.内连接： 即最常见的等值连接，指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。 2.外连接： 连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。 2.1左外连接： 左表数据行全部保留，右边表保留符合连接条件的行 2.2右外连接： 右表数据行全部保留，左边表保留符合连接条件的行 2
MySQL 多表查询
weixin_52629592的博客
05-23 722
多表查询,也称为关联查询.指两个或更多个表一起完成查询操作 一.多表查询的前提条件 这些一起查询的表之间是有关联的(一对一,一对多),它们之间一定是有关联字段的,这个关联字段可能建立了外键,也可能没有 如: 员工表和部门表,依靠"部门编号"进行关联 案例: 有三个表,分别为Employees,Departments,Locations 以我们现有的知识,想查找一个员工的location_id,就得从Employees查询到department_id,再从Departments中查询到
SQL ——深入分析左连接
崩玉~雅的博客
01-09 429
背景：在一次开发中，查询出现了2条重复数据。数据来源于2张表的不同字段，使用左连接方式连表。在这之前，对于左连接的印象始终停留在：以左边（x left join y ) 即x 的表为最终结果为基础，右边取需要的字段，如果右边符合条件的数据行的字段为空，则取空。 直到出现了以上现象：左连接出现了2条重复数据，颠覆了我的想象。。 测试： 环境：mysql5.5.62 1：新建2张表做测试...
MySQL——创建表
前尘忆梦的博客
12-03 3961
创建表 1、建表的语法格式：(建表属于 DDL 语句，DDL 包括：create drop alter) create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型); create table 表名( 字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型 ); 表名：建议以 t_ 或者 tbl_ 开始，可读性强。见名知意。 字段名：见名知意。 表名和字段名都属于标识符。 2、关于 mysql 中的数据类型？ 很多数据类型，我们只需要掌握一些常
PHP+Mysql——博客系统
05-26
3. 查询数据，例如查询所有博客： ```php $sql = "SELECT * FROM blogs"; $result = $conn->query($sql); while ($row = $result->fetch_assoc()) { echo $row["title"] . " "; } ``` 4. 更新数据，例如更新一...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值