数据库mysql入门基础--07.连接查询(最最最最重要)

二荆条3.0
于 2021-12-05 09:38:09 发布
阅读量391
点赞数
文章标签：数据库 mysql sql
本文链接：https://blog.youkuaiyun.com/sfwefqe/article/details/121719573
版权
七.连接查询
1.什么是连接查询？
	从一张表中单独查询，称为单表查询。
	emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中去部门名字。
	这种跨表查询，多张表联合起来查询数据，被称为连接查询。

2.连接查询的分类
	根据语法的年代分类：
		SQL92：1992年的时候出现的语法
		SQL99：1999年的时候出现的语法
	我们这里重点学习SQL99.(这个过程中简单演示一个SQL92的例子)
	
	根据表连接的方式分类：
		内连接：
			等值连接
			非等值连接
			自连接
		外连接：
			左外连接(左连接)
			右外连接(右连接)
		全连接(不讲)
		
3.当两张表进行连接查询时，没有任何条件的限制会发生什么现象？
案例：查询每个员工所在部门名称？
	select ename,deptno from emp;	
	|  ename  |  deptno  |
	|  SMITH  |    20    |
	|  ALLEN  |    30    |
	|  WARD   |    30    |
	|  JONES  |    20    |
	|  MARTIN |    30    |
	|  BLACK  |    30    |
	|  CLARK  |    10    |
	|  SCOTT  |    20    |
	|  KING   |    10    |
	|  TUENER |    30    |
	|  ADAMS  |    20    |
	|  JAMES  |    30    |
	|  FORD   |    20    |
	|  MILLER |    10    |  
	select * from dept;	
	| deptno | dname      | loc      |
	| 	10   | ACCOUNTING | NEW YORK |
	| 	20   | RESEARCH   | DALLAS   |
	| 	30   | SALES      | CHICAGO  |
	| 	40   | OPERATIONS | BOSTON   |
	
 	两张表连接没有任何限制：select ename,deptno 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
	当两张表进行连接查询没有任何条件限制的时候，最终查询结果条数是两张表条数的乘积，这种现象被称为：笛卡尔积现象(笛卡尔发现的，这是一个数学现象。)

4.怎么避免笛卡尔积现象？
	连接时加条件，满足这个条件的记录被筛选出来。
	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      |
	| BLACK | SALES      |
	| TURNER| SALES      |
	| JAMES | SALES      |
	思考：最终查询的结果条数是14条，但是匹配的过程中，匹配的次数减少了吗？
		**没有，还是56次，只不过进行了四选一。**
	注意：	通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的连接次数。
**
	技巧：给表起别名,很重要，数据量大的时候可以提高效率！！！
		select
			e.emp,d.dname
		from
			emp e,dept d
		where
			e.deptno = d.deptno;//SQL92语法
**

5.内连接之等值连接**(内连接的特点：完成能够匹配上这个条件的数据查询出来。)**
案例：查询每个员工所在部门名称，显示员工名和部门名
emp e和dept d表进行连接，条件是：e.deptno = d.deptno
SQL92语法:
		select
			e.emp,d.dname
		from
			emp e,dept d
		where
			e.deptno = d.deptno and 后面加条件;
		缺点：结构不清晰，表的连接条件和后期进一步筛选的条件，都放到了where的后面。
SQL99语法：
		select
			e.emp,d.dname
		from
			emp e
		join
			dept d
		on
			e.deptno = d.deptno
		where
			条件;
		优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where条件语句。
		SQL99语法：
			select
				...
			from
				a
			(inner) join//内连接inner,可以省略，带着inner可读性更好，一眼就可以看出来是内连接
				b
			on
				a和b的连接条件
			where
				筛选条件;

6.内连接之非等值连接
案例：找出每个员工的薪资等级，要求显示员工名，薪资，薪资等级。
	第一步：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     |
	...
	第二步：select * from salgrade;   s
	| grade | losal | hisal |
	|    1  |  700  |  1200 |
	|    2  | 1201  |  1400 |
	|    3  | 1401  |  2000 |
	|    4  | 2001  |  3000 |
	|    5  | 3001  |  9999 |
	第三步：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.0 |     1 |
			| ALLEN | 1600.00 |     3 |
			| WARD  | 1250.00 |     2 |
			| JONES | 2975.00 |     4 |
			| MARTIN| 1250.00 |     2 |
			| BLAKE |  2850.0 |     4 |
			| SCOTT | 3000.00 |     4 |
			| KING  | 5000.00 |     5 |
			| TUENER| 1500.00 |     3 |
			| ADAMS | 1100.00 |     1 |
			| JAMES |   950.0 |     1 |
			| FORD  | 3000.00 |     4 |
			| MILIER| 1300.00 |     2 |

7.内连接之自连接
案例：查询员工的上级领导，要求显示员工名的对应的领导名(mgr是领导的编号)
	第一步：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 | BLACK | 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 | BLACK | 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 | BLACK | 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|
	第三步：连接查询
	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**
以上就是内连接中的自连接,技巧是把一张表看作两张表。

8.外连接
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     |
	| 7689  | 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 | 1981-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     |
dept表：命名为d
	| deptno | dname      | loc      |
	|     10 | ACCOUNTING | NEW YORK |
	|     20 | RESEARCH   | DALLAS   | 
	|     30 | SALES      | CHICAGO  |
	|     40 | OPERATIONS | BOSTON   | 
分析：dept表中deptno=40的记录在emp表中没有对应的记录。
(1)右外连接:	select
					e.ename,d.dname
				from
					emp e 
				right join 
					dept d
				on
					e.deptno = d.deptno;
	**该语句表示的意义是：除了将能够匹配上e.deptno = d.deptno的ename,dname筛选出来之外，还要将主表dept中没有匹配到的记录展示出来。**
	right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。
	在外连接当中，两张表连接，产生了主次关系;在内连接当中，两张表是平等的，没有主次关系。
	| ename | dname      |  
	| CLARK | ACCOUNTING |
	| KING  | ACCOUNTING |
	| MILLER| ACCOUNTING |
	| SMITH | RESEARCH   |
	| JONES | RESEARCH   |
	| SCOTT | RESEARCH   |
	| ADAMS | RESEARCH   |
	| FORD  | RESEARCH   |
	| ALLEN | SALES      |
	| WARD  | SALES      |
	| MARTIN| SALES      |
	| BLACK | SALES      |
	| TURNER| SALES      |
	| JAMES | SALES      |
	| NULL  | OPERATIONS |
	如果次表中没有记录能跟主表中的记录匹配时，就显示为NULL。
(2)左外连接:	select
					e.ename,d.dname
				from
					dept d 
				left (outer) join emp e
				on
					e.deptno = d.deptno;
		
	**带有right的是右外连接，又叫做右连接。
	带有left的是左外连接，又叫做左连接。
	任何一个右连接都有左连接的写法。
	任何一个左连接都有右连接的写法。
	左右连接是一一对应的。outer也是可以省略的，带着可读性强。
	所以区分内外连接的主要方法是看join前面有没有right/left.**
	
	思考：外连接的查询结果条数一定是 >= 内连接的查询结果条数？
	正确。
	
	案例：查询每个员工的上级领导，要求显示<所有员工>的名字和领导名。
	第一步：select empno,ename,mgr from emp; a
	| empno | ename | mgr |
	|  7369 | SMITH | 7902|
	|  7499 | ALLEN | 7698|
	|  7521 | WARD  | 7698|
	|  7566 | JONES | 7839|
	|  7654 | MARTIN| 7698|
	|  7698 | BLACK | 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 | BLACK | 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 | BLACK | 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|
	第三步：内连接查询
	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**
以上就是内连接中的自连接,技巧是把一张表看作两张表。
	第四步：使用外连接查询，令员工表为主表，可以查询出所有员工的记录，包括KING
	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  |
	**14条记录,包括KING**

9.三张表,四张表怎么连接？
	语法：
		select
			...
		from
			a
		join
			b
		on
			a和b的连接条件
		join
			c
		on
			a和c的连接条件
		right join
			d
		on
			a和d的连接条件
			
	一条SQL中内连接和外连接可以混合，都可以出现！
	
	案例：找出每个员工的部门名称以及工资等级，要求显示员工名，部门名，薪资和薪资等级？
	第一步：查询数据包含的所有表
		查询emp表： select * from 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     |
		| 7689  | 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 | 1981-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     |
		查询dept表： select * from dept;
		| deptno | dname      | loc      |
		|     10 | ACCOUNTING | NEW YORK |
		|     20 | RESEARCH   | DALLAS   | 
		|     30 | SALES      | CHICAGO  |
		|     40 | OPERATIONS | BOSTON   | 
		查询salgrade表： select * from salgrade;  
		| grade | losal | hisal |
		|    1  |  700  |  1200 |
		|    2  | 1201  |  1400 |
		|    3  | 1401  |  2000 |
		|    4  | 2001  |  3000 |
		|    5  | 3001  |  9999 |
	第二步：连接查询
		select 
			e.name,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 a
	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;