数据库学习笔记(九)-----表操作详述（下）

---

前言

上文讲了表的插入、删除、更新、查询，本文带你了解一下表的联合查询。

---

一、联合查询

1.内连接查询

在MySQL中内连接数据查询通过“INNER JOIN…ON”语句来实现，语法形式如下所示。

SELECT field1,field2,…,fieldn FROM tablename1 INNER JOIN tablename2 [INNER JOIN tablenamen] ON CONDITION

其中，参数fieldn表示要查询的字段名，来源于所连接的表tablename1和tablename2，关键字INNER JOIN表示表进行内连接，参数CONDITION表示进行匹配的条件。
举例:
输入以下代码：

create database school;
use school;
insert into class valuse(1001,'玛卡巴卡','物联网一班'),(1002,'唔西迪西','物联网二班'),(1003,'汤姆布利柏','物联网三班');
create table student(id int,belongclass int,name varchar(64));
insert into student values(1,1001,'甲'),(2,1001,'乙'),(3,1003,'丙'),(4,1002,'丁');
select * from student inner join class on student.belongclass=class.class_id;
#给student表取别名a,给class表取别名b，效果同上
select * from student as a inner join class as b on a.belongclass=b.class_id;

当表名特别长时，直接使用表名很不方便，或者在实现自连接操作时，直接使用表名无法区别表。为了解决这一类问题，MySQL提供了一种机制来为表取别名，具体语法如下：

SELECT field1, field2, ...,fieldn [AS] otherfieldn FROM tablename1 [AS] othertablename1，
 ...,Tablenamen [AS] othertablenamen ... where othertablename1.fieldx = othertablenamen.fieldx ... ;

其中，参数tablename为表原来的名字，参数othertablename为新表名，之所以要为表设置新的名字，是为了让SQL语句代码更加直观、更加人性化和实现更加复杂的功能。

1.自连接

内连接查询中存在一种特殊的等值连接——自连接。所谓自连接，就是指表与其自身进行连接。
举例：

#查询与甲同班级的其他学生
use school;
select * from student as a inner join student as b on a.belongclass=b.belongclass and b.name='甲';

2.等值/不等值连接

1. 内连接查询中的等值连接就是在关键字ON后的匹配条件中通过等于关系运算符（=）来实现等值条件。
2. 内连接查询中的不等连接就是在关键字ON后的匹配条件中通过除了等于关系运算符来实现不等条件外，还可以使用关系运算符，包含“>”“>=”“<”“<=”和“!=”等运算符号。
   举例：

#等值连接
select * from student as a inner join student as b on a.belongclass=b.belongclass;
#不等值连接
select * from student as a inner join student as b on a.belongclass!=b.belongclass;

2.外连接查询

当我们在查询数据时，要求返回所操作表中至少一个表的所有数据记录，通过SQL语句“OUTER JOIN…ON”来实现。外连接数据查询语法形式如下：

SELECT field1,field2,…,fieldn FROM tablename1 LEFT|RIGHT [OUTER] JOIN tablename2 ON CONDITION

在上述语句中，参数fieldn表示所要查询的字段名字，来源于所连接的表tablename1和tablename2，关键字OUTER JOIN表示表进行外连接，参数CONDITION表示进行匹配的条件。
外连接查询可以分为以下二类：

1.左外连接

外连接查询中的左外连接，就是指新关系中执行匹配条件时，以关键字LEFT JOIN左边的表为参考表。左连接的结果包括LEFT OUTER字句中指定的左表的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表均为空值。
举例：

select * from student as s left outer join class as c on s.belongclass=c.class_id;
select * from class as c left outer join student as s on c.class_id=s.belongclass;

2.右外连接

外连接查询中的右外连接在新关系中执行匹配条件时，以关键字RIGHT JOIN右边的表为参考表，如果右表的某行在左表中没有匹配行，左表将返回空值。
举例：

select * from student as s right outer join class as c on s.belongclass=c.class_id;
select * from class as c right outer join student as s on c.class_id=s.belongclass;

3.合并查询数据记录

在MySQL中通过关键字UNION来实现合并操作，即可以通过其将多个SELECT语句的查询结果合并在一起，组成新的关系。在MySQL软件中，合并查询数据记录可通过SQL语句UNION来实现，具体语法形式如下：

SELECT field1,field2,…,fieldn  FROM tablename1
    UNION | UNION ALL
    SELECT field1,field2,…,fieldn  FROM tablename2
    UNION | UNION ALL  SELECT field1,field2,…,fieldn ;

注意： 多个选择语句select 的列数相同就可以合并,union和union all的区别是union all是把结果集直接合并在一起，而union 是将union all后的结果再执行一次distinct，去除重复的记录后的结果。
举例：

select teacher as people from class union all select name from student;	#people是给列取的别名

4.子查询

所谓子查询，是指在一个查询中嵌套了其他的若干查询，即在一个SELECT查询语句的WHERE或FROM子句中包含另一个SELECT查询语句。在查询语句中，外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询，也被称为嵌套查询。
通过子查询可以实现多表查询，该查询语句中可能包含IN、ANY、ALL和EXISTS等关键字，除此之外还可能包含比较运算符。理论上，子查询可以出现在查询语句的任意位置，但是在实际开发中子查询经常出现在WHERE和FROM子句中。子查询一般有以下五类：

1.带比较运算符的子查询

子查询可以使用比较运算符。这些比较运算符包括=、!=、>、>=、<、<=和<>等。其中，<>与!=是等价的。比较运算符在子查询中使用得非常广泛，如查询分数、年龄、价格和收入等。

#查询‘甲’所在班级班主任的名字
select teacher from class where class_id=(select belongclass from student where name='甲');

注意：使用比较运算符时，select 子句获得的记录数不能大于1条!

2.带关键字IN的子查询

一个查询语句的条件可能落在另一个SELECT语句的查询结果中，这时可以使用IN关键字，NOT IN的用法与IN相同。SQL示例如下：

select teacher from class where class_id in (select belongclass from student where name='甲');		#查询‘甲’所在班级班主任的名字
select teacher from class where class_id in (select belongclass from student where name like '%');		#查询‘甲’、‘乙’、‘丙’、‘丁’所在班级班主任的名字

与比较查询不同，IN的子查询select子句获得的记录可以是多条

3.带关键字EXISTS的子查询

关键字EXISTS表示存在，后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行；如果至少返回一行，那么EXISTS的结果为true，此时外层语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

#如果1001班有学生，就查询1001班的班级信息
select * from class where class_id=1001 and exists(select * from student where belongclass=1001);	

4.带关键字ANY的子查询

关键字ANY表示满足其中任一条件。使用关键ANY时，只要满足内层查询语句返回的结果中的任何一个就可以通过该条件来执行外层查询语句。关键字ANY通常和比较运算符一起使用。例如，“>ANY”表示大于任何一个值，“=ANY”表示等于任何一个值。

外部select语句 查询条件 any(子查询select语句);

5.带关键字ALL的子查询

关键字ALL表示满足所有条件。使用关键字ALL时，只有满足内层查询语句返回的所有结果才可以执行外层查询语句。关键字ALL也经常与比较运算符一起使用。例如，“>ALL”表示大于所有值，“<ALL”表示小于所有值。

外部select语句 查询条件 all(子查询select语句);

---

总结

文章到这，表操作基本就结束了，以上是个人学习笔记，仅供参考！