MySQL快速入门篇---联合查询
一、什么是联合查询
1.1、概念
联合查询是SQL中用于合并多个SELECT语句结果集的操作。条件是被合并的结果集列数相同并且数据类型兼容。也可以说一次查询涉及两张或两张以上的表，就称为联合查询。
1.2、为什么要使用联合查询
如果数据被拆分到多个表中，我们需要查看一条数据的完整信息，这时就需要用到联合查询。
1.3、语法
SELECT * FORM 表名,表名......;
注意：多个表进行联合查询，取笛卡尔积
1.4、示例
创建一个班级表，一个学生表
# 创建班级表
drop table if exists class;
create table class(c_id bigint auto_increment primary key,c_name varchar(10)
);
# 创建学生表
drop table if exists students;
create table students(id bigint auto_increment primary key,name varchar(10),class_id bigint
);
# 插入数据
insert into class(c_id,c_name) values (1,'数学1班'),(2,'物理1班'),(3,'化学1班');
insert into students(id,name,class_id) values (1,'张三',1),(2,'李四',1),(3,'王五',2),(4,'赵六',3);
# 进行联合查询
select * from students,class;其运行结果如下：
通过观察，我们发现两张表联合查询的结果集中，有些是无效数据。那么问题来了，如何过滤掉这些无效数据呢？
通过表与表之间的连接条件过滤掉无效数据
select * from students,class where students.class_id=class.c_id;
其运行结果如下：
注意：class_id和c_id的前面要加上各自对应的"表名.",这样数据库才能精确地知道到底该使用哪张表里的id列
通过指定列查询，精减查询结果
select students.id,students.name,students.class_id,class.c_name from students,class where students.class_id=class.c_id;
其运行结果如下：
通过给表起别名来简化查询语句
select s.id,s.name,s.class_id,c.c_name from students s,class c where s.class_id=c.c_id;
其运行结果如下：
二、内连接
2.1、概念
内连接是根据两个表中的共同字段，将符合条件的数据进行关联查询，具体来说，当两个表中的连接条件都满足时，才会返回相关联的数据。
2.2、语法
//写法一
select 字段 from 表1 别名,表2 别名 where 连接条件 and 其它条件;
//写法2
select 字段 from 表1 别名 [inner] join 表2 别名 on 连接条件 where 其它条件;
简记：join两边是表名，on的后边是条件。
2.3、示例
查询学生id为4的同学的班级和姓名
select c.c_name,s.name from students s join class c on s.class_id=c.c_id where s.id=4;
其运行结果如下：
三、外连接
3.1、概念
外连接是关系数据库中的一种操作。它用于合并两个或多个表中的数据，和内连接不同的是，外连接会返回至少一个表中的所有行。
3.2、分类
外连接分为左外连接、右外连接和全外连接三种类型，MySQL不支持全外连接。
左外连接
返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对应字段会显示为NULL。
1、语法
select 字段名 from 表名1 left join 表名2 on 连接条件;
left join 左边的表为基准表
2、示例
插入在班级表中没有编号的学生的信息
insert into students values(5,'钱七',4);
使用左外连接
select s.id,s.name,c.c_name from students s left join class c on s.class_id=c.c_id;
其结果运行如下：
由上图，我们可以看出学生表的数据完全显示，班级表没有的数据为Null。
右外连接
与左外连接相反，返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记录，则结果集中对应字段会显示为NULL。
1、语法
select 字段 from 表名1 right join 表名2 on 连接条件;
2、示例
插入在学生表中没有编号的班级信息
insert into class values(5,'地理1班');
使用右外连接
select s.id,s.name,c.c_name from students s right join class c on s.class_id=c.c_id;
其运行结果如下：
全外连接
结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某⼀边表中没有匹配的记录，则结果集中对应字段会显示为NULL。
由于MySQL不支持，这里我就不多赘述。
四、自连接
4.1、概念
自连接是自己与自己取笛卡尔积，可以把行转化成列，在查询的时候可以使用where条件对结果进行过滤，或者说实现行与⾏行之间的比较。在做表连接时为表起不同的别名。
4.2、示例
创建成绩表
create table score(id bigint,数学 int,物理 int,化学 int
);
# 插入数据
insert into score values(1,70,80,90),(2,88,66,90),(3,75,82,86);
# 查询表结果
select * from score;
其结果运行如下：
使用自连接
select * from score sco1,score sco2;
其运行结果如下：
查询数学成绩大于物理成绩的信息
select sco1.id,sco1.`数学`,sco1.`物理` from score sco1,score sco2 where sco1.id=sco2.id and sco1.数学>sco2.物理;
其运行结果如下：
五、子查询
5.1、概念
子查询是把⼀个SELECT语句的结果当做别⼀个SELECT语句的条件，也叫嵌套查询。
5.2、语法
select * from 表1 where 列1 {= | IN} (select 列1 from 表2 where 列2 {= | IN} [(select ...)] ...
)
5.3、分类
1、单行子查询
嵌套的查询中只返回一行数据
语法
select * from 表名 where 列1 =(select 列1 from 表名 where 条件);
示例
查询数学成绩为70的同学的所有信息
select * from score where id =(select id from score where 数学=70);
其运行结果如下：
2、多行子查询
嵌套的查询中返回多⾏数据，使⽤[NOT]IN关键字
语法
select * from 表名 where 列1 [not] in(select 列1 from 表名 where 条件);
示例
查询化学成绩为90的所有同学的信息
select * from score where id in(select id from score where 化学=90);
其运行结果如下：
查询物理成绩不为80的所有同学的信息
select * from score where id not in(select id from score where 物理=80);
其运行结果如下：
其与单行子查询的书写差别是把=改成in
3、多列子查询
单行子查询和多行子查询都只返回一列数据，多列子查询中可以返回多个列的数据，外层查询与嵌套的内层查询的列要匹配
示例
新增重复的分数
insert into score (id,数学,物理,化学) values(4,70,80,90),(5,88,66,90);
查询重复录入的分数
select * from score where (数学,物理,化学) in(select 数学,物理,化学 from score group by 数学,物理,化学 having count(0)>1);
其运行结果如下：
六、合并查询
合并多个select操作返回的结果，可以使⽤集合操作符union，union all
6.1、Union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
示例
查询id为2和数学成绩为70的同学信息
select * from score where id=3 union select * from score where 数学=70;
查询id为1和数学成绩为70的同学信息
select * from score where id=1 union select * from score where 数学=70;
这里只有两条数据，是因为union有去重功能
6.2、Union all
该操作符⽤于取得两个结果集的并集。当使⽤该操作符时，不会去掉结果集中的重复⾏。
示例
查询id为1和数学成绩为70的同学信息
select * from score where id=1 union all select * from score where 数学=70;
这里使用union all并没有去重
七、复制表结构
语法
create table 新表名 like 原表名;
示例
新构建一个班级表
create table new_class like class;
查看原表结构
查看新表结构
八、复制表信息
语法
insert into 新表名 select * from 原表名;
示例
insert into new_class select * from class;
通过查询我们可以发现，新表的数据与原表相同。