17.高级查询(多表连接)

17.1 多表连接介绍

什么是连接连接是在多个表之间通过一定的连接条件，使表之间发生关联，进而能从多个表之间获取数据。语法为：

SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;

在 WHERE子句中书写连接条件。如果在多个表中出现相同的列名，则需要使用表名作为来自该表的列名的前缀。 N个表相连时，至少需要N－1个连接条件。

17.2连接类型

等值连接
非等值连接

笛卡尔积是：第一个表中的所有行和第二个表中的所有行都发生连接。笛卡尔积在下列情况产生：连接条件被省略连接条件是无效的为了避免笛卡尔积的产生，通常需要在WHERE子句中包含一个有效的连接条件。

17.3 等值连接

限制歧义列名
使用表的别名

在用到多个表时可以使用表名作前缀来限定列；通过使用表前缀可以提高性能；通过使用列的别名可以区分来自不同表但是名字相同的列；

练习

-- 1.写一个查询，显示所有员工姓名，部门编号，部门名称。 SELECT e.ename 员工姓名, d.deptno 部门编号 , d.dname 部门名称。 FROM emp e ,dept d WHERE e.deptno = d.deptno
-- 2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金 SELECT e.ename 员工姓名, d.loc 工作地点 , e.comm 奖金 FROM emp e ,dept d WHERE e.deptno = d.deptno AND d.loc ='CHICAGO' AND e.comm IS NOT NULL
-- 3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。 SELECT e.ename 员工姓名, d.loc 工作地点 FROM emp e ,dept d WHERE e.deptno = d.deptno AND e.ename LIKE '%A%';

17.4 多表连接的写法

1.分析要查询的列都来自于哪些表，构成FROM子句;

2.分析这些表之间的关联关系，如果表之间没有直接关联关系，而是通过另一个中间表关联，则也要在FROM子句中补充中间关联表;

3.接下来在WHERE子句中补充表之间的关联关系，通常N个表，至少要有N-1个关联关系;

4.分析是否还有其它限制条件，补充到WHERE子句的表关联关系之后，作为限制条件;

5.根据用户想要显示的信息，补充SELECT子句。

6.分析是否有排序要求，如果排序要求中还涉及到其它表，则也要进行第2步补充排序字段所在的表，并且添加表之间的关联关系;

17.5 自身连接

也叫自连接，是一个表通过某种条件和本身进行连接的一种方式，就如同多个表连接一样。

自身连接练习
有如下菜单表，请查询所有子菜单项目的名称，url ，创建时间，以及上级菜单的名称

sql

CREATE TABLE `menu` (
  `menu_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单id',
  `menu_name` varchar(100) DEFAULT NULL COMMENT '菜单名称',
  `url` varchar(100) DEFAULT NULL COMMENT '菜单url',
  `parent_id` int(11) DEFAULT NULL COMMENT '上级菜单id',
  `active` int(11) DEFAULT '1' COMMENT '是否有效，1 有效，0 失效',
  `createtime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`menu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;



insert  into `menu`(`menu_id`,`menu_name`,`url`,`parent_id`,`active`,`createtime`) values (1,'MySQL介绍','/course/03-dbms/index?id=_1-mysql介绍',NULL,1,'2021-08-25 11:19:48'),(2,'rdbms-术语','/course/03-dbms/index?id=_12-rdbms-术语',1,1,'2021-08-25 11:20:21'),(3,'mysql介绍','/course/03-dbms/index?id=_13-mysql介绍',1,1,'2021-08-25 11:20:46'),(4,'mysql的安装','/course/03-dbms/index?id=_2-mysql的安装',NULL,1,'2021-08-25 11:21:24'),(5,'下载','/course/03-dbms/index?id=_21-下载',4,1,'2021-08-25 11:21:34'),(6,'解压','/course/03-dbms/index?id=_22-解压',4,1,'2021-08-25 11:21:46'),(7,'添加到系统服务','/course/03-dbms/index?id=_23-添加到系统服务',4,1,'2021-08-25 11:22:00');

答案：

sql

select 
  m.`menu_name`,
  m.`url`,
  m.`createtime`,
  
  pm.`menu_name` 
from
  menu m,
  menu pm 
where m.`parent_id` = pm.`menu_id`

17.6 ANSI SQL

ANSI SQL：1999标准的连接语法 mysql 除了上述自己的连接语法外，同时支持美国国家标准协会（ANSI）的SQL：1999标准的连接语法。

17.6.1 交叉连接

cross join

sql

SELECT * FROM emp , dept ;

SELECT * FROM emp CROSS JOIN dept;

17.6.2 自然连接

自然连接是对两个表之间相同名字和数据类型的列进行的等值连接；如果两个表之间相同名称的列的数据类型不同，则会产生错误；使用NATURAL JOIN子句来完成。

sql

SELECT * FROM emp , dept  WHERE emp.`deptno` = dept.`deptno`

SELECT * FROM emp NATURAL JOIN dept

17.6.3 USING子句

自然连接是使用所有名称和数据类型相匹配的列作为连接条件，而USING子句可以指定用某个或某几个相同名字和数据类型的列作为连接条件。

sql

SELECT 	e.ename,e.ename,e.sal,deptno,d.loc 
FROM   	emp e JOIN dept d USING (deptno)

使用USING子句创建连接时，应注意以下几点：如果有若干个列名称相同但数据类型不同，自然连接子句可以用USING子句来替换，以指定产生等值连接的列。如果有多于一个列都匹配的情况，使用USING子句只能指定其中的一列。 USING子句中的用到的列不能使用表名和别名作为前缀。 NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用。

17.6.4 on 子句

自然连接条件基本上是具有相同列名的表之间的等值连接；如果要指定任意连接条件,或指定要连接的列，则可以使用ON子句；用ON将连接条件和其它检索条件分隔开，其它检索条件写在WHERE子句； ON子句可以提高代码的可读性。

sql

SELECT * FROM emp JOIN dept ON emp.`deptno` = dept.`deptno`

inner join

sql


 SELECT 
  w.*,
  mgr.ename 
FROM
	emp w 
  INNER JOIN 
	emp mgr 
 ON w.mgr = mgr.empno

17.6.5 外连接

在多表连接时，可以使用外部连接来查看哪些行,按照连接条件没有被匹配上。左外连接以FROM子句中的左边表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会被显示出来。右外连接以FROM子句中的右边表为基表，该表所有行数据按照连接条件无论是否与左边表能匹配上，都会被显示出来

左外连接

查询所有雇员姓名，部门编号，部门名称，包括没有部门的员工也要显示出来

右外连接

查询所有雇员姓名，部门编号，部门名称，包括没有员工的部门也要显示出来

17.8.练习

使用SQL-99写法，完成如下练习

创建一个员工表和部门表的交叉连接。
使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期
使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点
使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级
使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
显示员工SMITH的姓名，部门名称，直接上级名称
显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。
显示员工KING和FORD管理的员工姓名及其经理姓名。
显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。

答案：

sql

-- 1创建一个员工表和部门表的交叉连接。
SELECT 
  * 
FROM
  emp 
  CROSS JOIN dept 

-- 2 使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期
SELECT 
  ename 员工姓名,
  dname 部门名称,
  hiredate 入职日期 
FROM
  emp NATURAL 
  JOIN dept 
WHERE hiredate > '1980-05-01' 

-- 3 使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点
SELECT 
  ename 员工姓名,
  dname 部门名称,
  loc 工作地点 
FROM
  emp 
  JOIN dept USING (deptno) 
WHERE loc = 'CHICAGO' 

-- 4 使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级

SELECT 
  ename 员工姓名,
  dname 部门名称,
  loc 工作地点 ,
  sal 
FROM
  emp 
  JOIN dept ON emp.deptno = dept.deptno
WHERE loc = 'CHICAGO' 

-- 5 使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

SELECT 
  e1.ename 员工的姓名,
  e2.ename 经理姓名 
FROM
  emp e1 
  LEFT JOIN emp e2 
    ON e1.mgr = e2.empno ;
    
    
-- 5 使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
SELECT 
  e1.ename 经理姓名 ,
  e2.ename 员工的姓名
FROM
  emp e1 
  RIGHT JOIN emp e2 
    ON e1.empno = e2.mgr ;
    

-- 6显示员工SMITH的姓名，部门名称，直接上级名称
SELECT 
  e1.ename,d.dname,e2.ename
FROM
  emp e1 
  INNER JOIN dept d 
    ON e1.deptno = d.deptno 
  INNER JOIN emp e2 
    ON e1.mgr = e2.empno 
  WHERE e1.ename ='SMITH'


-- 7 显示员工姓名，部门名称，工资，工资级别，要求工资级别大于3级。
SELECT ename,dname,sal,
(CASE WHEN sal<1000 THEN '第1级'
     WHEN sal>=1000 AND sal<2000 THEN  '第2级'
     WHEN sal>=2000 AND sal<3000 THEN  '第3级'
     ELSE '第4级'
     END) LEVEL
FROM emp  JOIN dept ON emp.deptno = dept.deptno



-- 8 显示员工KING和FORD管理的员工姓名。

SELECT 
  mgr.ename '领导的姓名',
  employee.ename '员工的姓名' 
FROM
  emp mgr 
  JOIN emp employee 
    ON mgr.empno = employee.mgr 
ORDER BY mgr.ename 



-- 9 显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。
SELECT 
employee.ename '员工的姓名' ,
employee.hiredate ,
  mgr.ename '领导的姓名',
  mgr.hiredate 
  
FROM emp employee
  
  JOIN   emp mgr 
    ON employee.mgr = mgr.empno 
    WHERE employee.hiredate < mgr.hiredate 
ORDER BY employee.ename

17.高级查询(多表连接) ​

17.1 多表连接介绍 ​

17.2连接类型 ​

17.3 等值连接 ​

17.4 多表连接的写法 ​

17.5 自身连接 ​

17.6 ANSI SQL ​

17.6.1 交叉连接 ​

17.6.2 自然连接 ​

17.6.3 USING子句 ​

17.6.4 on 子句 ​

inner join ​

17.6.5 外连接 ​

17.8.练习 ​