MySQL多表查询操作

青旅半醒 2023-09-26 22:23 131阅读 0赞

### 文章目录 ###

*  前言
 *  内连接
 *   *  表别名
 *  外连接
 *   *  左外连接
     *  右外连接
     *  全外连接
 *  总结

# 前言 #

`JOIN` 表示连接，`inner`表示内连接，`outer`表示外连接，缺省情况是内连接。  
ON后面接<连接条件>

![在这里插入图片描述][66e93554bab24dcd9503c298a177e595.png]

# 内连接 #

默认的表数据连接是内连接，只会查询出满足连接条件的行

语句格式`FROM 表1 [inner] JOIN 表2 ON <连接条件>`  
![在这里插入图片描述][81fe4f5b74314633ab60c1e60fc1518c.png]  
表X和Y内连接结果

SELECT  a, X.b, Y.b, c 
    FROM X  
    JOIN Y  
    ON X.b=Y.b;

![在这里插入图片描述][11c8b687227e44d0b08a876f58d0a1ae.png]  
一张表和自身连接。必须给表起别名

• 【例】查询与C语言在同一出版社的图书信息  
分析：C语言一书在Book表中，与它在同一出版社的图书也在Book表中。二者通过publish相等进行连接

SELECT BookW.* FROM Book BookC 
    JOIN Book BookW 
    ON BookC.publish=BookW.publish 
    WHERE BookC.name='C语言' 
    AND BookW.name<> 'C语言';

• 内连接的特点：只会保留满足连接条件的数据到连接结果中

## 表别名 ##

通过表别名简化查询语句，方便多表查询的编写  
表别名一般是表的首字母

【例】查询员工名字，工作地点，部门名字  
emp表内字段：ename，job，deptid  
dept表内字段：deptid，dname

SELECT ename,job,dname
    FROM emp e,dept d
    WHERE e.deptid=d.deptid;

【例】写一个查询，显示所有员工姓名，部门编号，部门名称。  
emp表内字段：ename，deptid  
dept表内字段：deptid，dname

SELECT ename,d.deptid,dname
    FROM emp e,dept d
    WHERE e.deptid=d.deptid;

先要确定出查询数据来自几个表SELECT  
在FROM把这多个表列出来，别名加空格起上  
然后写连接条件WHERE  
最后写其他条件AND

【例】查询在HK的员工编号，姓名，部门编号，工作地点  
有两个表，分别是emp员工表与dept部门表  
emp表内字段：empid，ename，deptid  
dept表内字段：deptid，loc

SELECT empid,ename,emp.deptid,loc 
    FROM emp,dept
    WHERE emp.deptid=dept.deptid
    AND loc='HK';

【例】查询工作在HK的员工姓名，职位，部门名字，工资等级，按工资等级升序排列

这里用到了三个表，但多于两个表时，也是拆成两两连接  
emp表内字段：empid，ename，deptid，sal  
dept表内字段：deptid，loc，empid  
salgrade表内字段：grade，losal，hisal

SELECT ename,job,dname,grade
    FROM emp e,dept d,salgrade s
    WHERE e.empid=d.empid
    AND e.sal BETWEEN s.losal AND s.hisal 
    AND loc = 'HK'
    ORDER BY grade;

# 外连接 #

和内连接相对的就是外连接。

外连接的特点：内连接只会查询出满足连接条件的行，外连接也会把不满足连接条件的数据显示到连接结果中。

外连接分类：

## 左外连接 ##

**表位置在左侧用左外连接**

语法格式：`FROM 表1 LEFT JOIN 表2 ON <连接条件>`

`LEFT JOIN`表示左外连接

**左外连接以FROM子句中的左边表为基准表，把左侧数据都查出来，无论是否满足连接条件**

【例】表 X 和 Y 进行左外连接语句及连接结果

SELECT a,X.b,Y.b,c 
    FROM x left outer 
    JOIN y ON x.b=Y.b;

![在这里插入图片描述][4bde6f65423147b398f370985d3f3f1f.png]  
在连接过程中，左侧表一的元组 r 如果不能满足连接条件，r 保留在连接的结果中  
在连接结果中，和 r 对应的表二的例值用NULL来代替

【例】使用左连接，查询每个学生的姓名，班长姓名，没有班长的King（班主任）也要显示出来。

stu表内字段：sname，mgr，stuid

SELECT s.sname,m.sname
    FROM stu s left JOIN stu m
    ON s.mgr=m.stuid;

## 右外连接 ##

**表位置在右侧用右外连接**

语法格式：`FROM 表1 RIGHT JOIN 表2 ON <连接条件>`

**右外连接以FROM子句中的右边表为基准表，把右侧数据都查出来，无论是否满足连接条件**

【例】表 X 和 Y 进行右外连接语句及连接结果

SELECT a, X.b, Y.b, c 
    FROM X RIGHT 
    JOIN Y ON X.b=Y.b;

![在这里插入图片描述][56777bb89ba14a81b2d1751180d9c59d.png]  
右侧表Y的元组（3，Q），虽未能在表X中找到相匹配的元组，单保留在结果当中  
同时 a，X.b 的值用NULL填充

【例】使用右连接，查询每个学生的姓名，班长姓名，没有班长的King（班主任）也要显示出来。

stu表内字段：sname，mgr，stuid

SELECT s.sname,m.sname
    FROM stu m right JOIN stu s
    ON s.mgr=m.stuid;

## 全外连接 ##

`union` 合并，把左外连接和右外连接连在一起

语法格式：`FROM 表1 FULL [OUTER] JOIN 表2 ON <连接条件>`

【例】表 X 和 Y 进行右外连接语句

SELECT a, X.b, Y.b, c 
    FROM X FULL OUTER 
    JOIN Y ON X.b=Y.b;

【例】查询所有读者借阅情况（包括没借阅图书的读者），显示读者编号，读者姓名，图书编号和书名。

SELECT R.reader_ID, R.name, B.book_ID, B.name 
    FROM Reader AS R LEFT JOIN Borrow 
    AS BW 
    ON R.reader_ID=BW.reader_ID LEFT 
    JOIN Book AS B ON B.book_ID=BW.book_ID;

# 总结 #

如果查询的数据超过两个表，那么就需要多表查询

当多于两个表时，拆分即可，两两连接

在from中，列出多个表名字，逗号分割

在where语句中，指定连接条件

在写多表连接时，如果语句中出现两个表都存在的列，需要加上表名前缀（否则不知道哪个表的）

**外连接:**  
通过外连接可以查询到不满足连接条件的数据行

1.  左外：是以出现在from子句中左侧的表为基准，查询出基表的所有数据行
2.  右外：是以出现在from子句中右侧的表为基准，查询出基表的所有数据行
3.  全外：查询出两个表的所有数据行

[66e93554bab24dcd9503c298a177e595.png]: https://img-blog.csdnimg.cn/66e93554bab24dcd9503c298a177e595.png
[81fe4f5b74314633ab60c1e60fc1518c.png]: https://img-blog.csdnimg.cn/81fe4f5b74314633ab60c1e60fc1518c.png
[11c8b687227e44d0b08a876f58d0a1ae.png]: https://img-blog.csdnimg.cn/11c8b687227e44d0b08a876f58d0a1ae.png
[4bde6f65423147b398f370985d3f3f1f.png]: https://img-blog.csdnimg.cn/4bde6f65423147b398f370985d3f3f1f.png
[56777bb89ba14a81b2d1751180d9c59d.png]: https://img-blog.csdnimg.cn/56777bb89ba14a81b2d1751180d9c59d.png