MySQL基础--复合查询

连接查询

意义:在用户查看数据的时候，需要显示的数据来自多张表

内连接：[inner] join

从左表中取出每一条记录,去右表中与所有的记录进行匹配: 匹配必须是某个条件在左表中与右表中相同最终才会保留结果,否则不保留.

外连接查询

以某张表为主,取出里面的所有记录, 然后每条与另外一张表进行连接: 不管能不能匹配上条件,最终都会保留: 能匹配,正确保留; 不能匹配,其他表的字段都置空NULL。

左连接

left join: 左外连接(左连接), 以左表为主表
基本语法: from 左表 left join 右表 on 左表.字段 = 右表.字段;

左表不管能不能匹配上条件，最终都会保留：能匹配，正常的保留；若不能匹配，右表的字段都置为null;

右连接

right join: 右外连接(右连接), 以右表为主表
基本语法: from 左表 right join 右表 on 左表.字段 = 右表.字段;
右表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留; 若不能匹配,左表的字段都置为NULL。

联合查询

联合查询结果是将多个select语句的查询结果合并到一块因为在某种情况下需要将几个select语句查询的结果合并起来显示。比如现在需要查询两个公司的所有员工的信息，这就需要从甲公司查询所有员工信息，再从乙公司查询所有的员工信息，然后将两次的查询结果进行合并。可以使用union和union all关键字进行操作

语法格式如下:

select 语句1
union[union 选项]
select 语句2
union|[union 选项]
select 语句n

其中union选项有两个选项可选
all：表示无论重复都输出
distinct: 去重(整个重复)（默认的）

联合查询只要求字段一样, 跟数据类型和顺序无关

select id,addrid,sex,score 
from student 
union all 
select sex,addrid,id,score 
from student;

联合查询的意义:

1. 查询同一张表,但是需求不同 如查询学生信息, 男生身高升序, 女生身高降序
2. 多表查询: 多张表的结构是完全一样的,保存的数据(结构)也是一样的.

联合查询order by的使用
在联合查询中: order by不能直接使用（不能出现两次）,需要对查询语句使用括号才行；

select *from student 
where sex="woman" 
order by score
union
select *from 
student where sex="man" 
order by score;
#这种情况会报错的，因为语句中不允许出现两个order by

select *from student where sex="woman" 
union
select *from student where sex="man" order by score;
#如果只有一个order by，意义就变成了合并之后在进行排序，因为又把前边的sex分好的类打乱了


(select *from student 
where sex="woman" 
order by score )
union
(select *from 
student where sex="man" 
order by score;)
#这种方式目的是让两个结果集分别进行order by ，然后在对两个结果集进行union。但是你会发现这种方式虽然不报错了，但是连个order by并  没有产生  最后的效果


应该改成
select *from
(select *from student 
where sex="woman" 
order by score)student
union
select *from
(select *from student 
where sex="man" 
order by score)student ;

也就是说，order by不能直接出现在union的子句中，但是可以出现在子句的子句中。

子查询

在查询的SQL中嵌套查询，称为子查询。子查询通常会使复杂的查询变得简单，但是相关的子查询要对基础表的每一条数据都进行子查询的动作，所以当表单中数据过大时，一定要慎重选择

带in关键字的子查询

使用in关键字可以将原表中特定列的值与子查询返回的结果集中的值进行比较
如果某行的特定列的值存在，则在select语句的查询结果中就包含这一行。

例:查询成绩大于80的学生的所有信息，
先在子查询中查出成绩大于80的结果集，然后将原成绩表中的成绩与结果集进行比较，如果存在，就输出这条学生的记录。

select *
from student 
where score in
(select score from student where score>80);

带比较运算符的子查询

如果可以确认子查询返回的结果只包含一个单值，那么可以直接使用比较运算符连接子查询。
经常使用的比较运算符包括等于（=）、不等于（<>或!=）、小于（<）、大于（>）、小于等于（<=）和大于等于（>=）。

查询奖学金等级为1的学生信息

select *
from student 
where score> 
(select score 
from scholarship
where dengji=1);

带exists的子查询

exists: 是否存在的意思, exists子查询就是用来判断某些条件是否满足(跨表),
exists是接在where之后
exists返回的结果只有0和1.

例：如果存在成绩大于90的人则列出整个表的记录

select *
from student 
where exists
(select *from student where score>90);

带any关键字的子查询

any关键字表示满足其中的任意一个条件，使用any关键字时，只要满足内层查询语句结果的的任意一个，就可以通过该条件来执行外层查询语句。

select *
from student 
where addrid<any
(select addrid 
from addr);

带all关键字的子查询

all和any刚好是相反的，all关键字表示满足所有结果，使用all关键字，要满足内层查询语句的所有结果，才可以通过该条件来执行外层查询语句。

select *
from student 
where addrid>all
(select addrid 
from addr);

内连接和外连接的区别

内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。

外连接：连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。

• 左外连接：左边表数据行全部保留，右边表保留符合连接条件的行。
• 右外连接：右边表数据行全部保留，左边表保留符合连接条件的行。
• 全外连接：左外连接 union 右外连接。

 

union和union all的区别

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

但要注意的是：union 是有去重功能的，而or没有，使查询的数据出现错误。

不仅俩个数据集间重复的数据会被去重，而且单个数据集里重复的数据也会被去重。
有重复数据的数据集采用union 后得到的数据与预期会不一样。
解决方法：在去重前加入一个可以唯一标识各行的列就行。保证正确的去重。
 

union和or的区别和联系

union：用于连接两个 select 语句，查询出的字段，必须相同；

or：用于 where 后，意思是查询 age 大于30或者 age 不等于11的数据，只要符合其中一条判断，就返回；

联系：如果两个 select 查询的是同一张表的内容，并且判断的是同一个字段，那么 union 和 or 可以相互转换；

区别：

1. union 用于连接两个sql，所以可以查询返回列相同的 select 语句，所以表可以不是同一张，而 or 的对象只能是同一张表；
2. union 可以实现将判断不同字段的两个sql 联合查询，or 固然也可以实现，但是很容易让人绕进去，没有 union 那么通俗易懂。