mysql中的高级查询（联合查询、多表查询）：内连接外连接（join |（left|right）join）

1.笛卡尔积

笛卡尔积⼜称直积，表示为 XY，⽐如 A 表中的数据为 m ⾏，B 表中的数据有 n ⾏，那么 A 和 B 做笛
卡尔积，结果为 mn ⾏
1、联表查询中的no和where的区别
1.1内连接on可以省略，外连接on不能省略。
1.2 on在内外连中执行的效果不一样：left join on 查询不能过滤掉左表（主表）中的数据可以过滤次表数据，而内连接 on查询可以过滤掉全局数据
1.3 在外连接查询中，on和where的意义是完全不一同的
外连接查询时，如果有多个查询条件，真确的写法是，将查询的条件的表达式全部放在where中，而非on中，在on中一般情况只需要将笛卡尔积无效数据过滤即可。

in查询和’='查询的区别
'='查询需要一个具体的值（确定的值）
in查询可以是多个值，并且满足任意一个将返回true。

2、 内连接

内连接侧重于两个表之间的共性，它的作⽤是使⽤联接，⽐较两个（或多个）表之间的共有数据，然后进⾏返回。
一般步骤：
1.进行内连接查询
2.去掉无效数据（on 过滤掉条件）
3.表达式查询（where 过滤条件）
语法

select * from t1 [inner|cross] join t2 [on 过滤条件] [where 过滤条件]

有四种写法

1. select * from t1 join t2 [on 过滤条件] [where 过滤条件];
2. select * from t1 inner join t2 [过滤条件] [where 过滤条件];
3. select * from t1 cross join t2 [过滤条件] [where 过滤条件];
4. select * from t1,t2 [where 过滤条件];//没有on条件

示例
进行内连接（笛卡尔积查询）

 select s.*,st.* from student s join score table st;

去掉无效数据（on 过滤掉条件）

 select s.*,st.* from student s join score_table st on s.id=st.student_id;
+----+-------+----------+-----------------+----------+----+-------+------------+-----------+
| id | sn    | username | mail            | class_id | id | score | student_id | course_id |
+----+-------+----------+-----------------+----------+----+-------+------------+-----------+
|  1 | CN001 | 张
三    | zhangsan@qq.com |        1 |  1 |  90.0 |          1 |         1 |
|  1 | CN001 | 张
三    | zhangsan@qq.com |        1 |  2 |  59.0 |          1 |         2 |
|  2 | CN002 | 李四     | lisi@qq.com     |        2 |  3 |  65.0 |          2 |         1 |
|  2 | CN002 | 李四     | lisi@qq.com     |        2 |  4 |  NULL |          2 |         2 |
+----+-------+----------+-----------------+----------+----+-------+------------+-----------+

3.表达式查询（where 过滤条件）

 select s.*,st.* from student s join score_table st on s.id=st.student_id where s.username='张三';
+----+-------+----------+-----------------+----------+----+-------+------------+-----------+
| id | sn    | username | mail            | class_id | id | score | student_id | course_id |
+----+-------+----------+-----------------+----------+----+-------+------------+-----------+
|  1 | CN001 | 张三     | zhangsan@qq.com |        1 |  1 |  90.0 |          1 |         1 |
|  1 | CN001 | 张三     | zhangsan@qq.com |        1 |  2 |  59.0 |          1 |         2 |
+----+-------+----------+-----------------+----------+----+-------+------------+-----------+

3、外连接

1.外连接查询时，如果有多个查询条件，真确的写法是，将查询的条件的表达式全部放在where中，而非on中，在on中一般情况只需要将笛卡尔积无效数据过滤即可。

2.三个表以上连接时，中间表一定要尽早查询如：主表 join 中间表 join 表三
左右连接只需要可以只通过left join 实现把表的查询顺序调换就行。

左连接语法如下：

select * from t1 left join t2 [on 连接条件];

右连接语法如下：

select * from t1 right join t2 [on 连接条件];

 select s.username,s.sn,s.mail,st.score from student s left join score_table st on s.id=st.student_id;
+----------+-------+-----------------+-------+
| username | sn    | mail            | score |
+----------+-------+-----------------+-------+
| 张三     | CN001 | zhangsan@qq.com |  90.0 |
| 张三     | CN001 | zhangsan@qq.com |  59.0 |
| 李四     | CN002 | lisi@qq.com     |  65.0 |
| 李四     | CN002 | lisi@qq.com     |  NULL |
| 王五     | CN003 | wangwu@qq.com   |  NULL |
+----------+-------+-----------------+-------+

4、内外连接on区别

left join on 查询不能过滤掉左表（主表）中的数据可以过滤次表数据，而内连接 on查询可以过滤掉全局数据
外连接不可以省略on否则报错，内连接可以省略。

select s.username,s.sn,s.mail,st.score from student s  join score_table st on s.id=st.student_id and score>60;
+----------+-------+-----------------+-------+
| username | sn    | mail            | score |
+----------+-------+-----------------+-------+
| 张三     | CN001 | zhangsan@qq.com |  90.0 |
| 李四     | CN002 | lisi@qq.com     |  65.0 |
+----------+-------+-----------------+-------+

 select s.username,s.sn,s.mail,st.score from student s left join score_table st on s.id=st.student_id and score>60;
+----------+-------+-----------------+-------+
| username | sn    | mail            | score |
+----------+-------+-----------------+-------+
| 张三     | CN001 | zhangsan@qq.com |  90.0 |
| 李四     | CN002 | lisi@qq.com     |  65.0 |
| 王五     | CN003 | wangwu@qq.com   |  NULL |
+----------+-------+-----------------+-------+

5、自查询

join通过内连接自己来实现（自己和自己进行查询）
1.实例：
查询英语成绩>计算机成绩的数据
实现思路
1.先根据科目名称查询出来科目id
2.自查询（笛卡尔积）
3.去除笛卡尔积中的无效数据
4.设置where条件让表一查询英语成绩，让表二查询计算机成绩
5…设置where多条件查询，让英语成绩>计算机成绩
1.

select * from score_table st1,score_table st2 where st1.student_id=st2.student_id;
+----+-------+------------+-----------+----+-------+------------+-----------+
| id | score | student_id | course_id | id | score | student_id | course_id |
+----+-------+------------+-----------+----+-------+------------+-----------+
|  1 |  90.0 |          1 |         1 |  1 |  90.0 |          1 |         1 |
|  2 |  59.0 |          1 |         2 |  1 |  90.0 |          1 |         1 |
|  1 |  90.0 |          1 |         1 |  2 |  59.0 |          1 |         2 |
|  2 |  59.0 |          1 |         2 |  2 |  59.0 |          1 |         2 |
|  3 |  65.0 |          2 |         1 |  3 |  65.0 |          2 |         1 |
|  4 |  NULL |          2 |         2 |  3 |  65.0 |          2 |         1 |
|  3 |  65.0 |          2 |         1 |  4 |  NULL |          2 |         2 |
|  4 |  NULL |          2 |         2 |  4 |  NULL |          2 |         2 |
+----+-------+------------+-----------+----+-------+------------+-----------+

 select * from score_table st1,score_table st2 where st1.student_id=st2.student_id and st1.course_id=2 and st2.course_id=1;
+----+-------+------------+-----------+----+-------+------------+-----------+
| id | score | student_id | course_id | id | score | student_id | course_id |
+----+-------+------------+-----------+----+-------+------------+-----------+
|  2 |  59.0 |          1 |         2 |  1 |  90.0 |          1 |         1 |
|  4 |  NULL |          2 |         2 |  3 |  65.0 |          2 |         1 |
+----+-------+------------+-----------+----+-------+------------+-----------+

 select * from score_table st1,score_table st2 where st1.student_id=st2.student_id and st1.course_id=2 and st2.course_id=1 and st1.score<st2.score;
+----+-------+------------+-----------+----+-------+------------+-----------+
| id | score | student_id | course_id | id | score | student_id | course_id |
+----+-------+------------+-----------+----+-------+------------+-----------+
|  2 |  59.0 |          1 |         2 |  1 |  90.0 |          1 |         1 |
+----+-------+------------+-----------+----+-------+------------+-----------+

select st1.student_id as 'ID', st1.score as '英语成绩',st2.score as '计算机成绩' from score_table st1,score_table st2 where st1.student_id=st2.student_id and st1.course_id=2 and st2.course_id=1 and st1.score<st2.score;
+----+----------+------------+
| ID | 英语成绩 | 计算机成绩 |
+----+----------+------------+
|  1 |     59.0 |       90.0 |
+----+----------+------------+

6、子查询（嵌套查询）

以上一条查询的结果做为下一条查询的条件

select * from  表名1 where 列名=（select 列名 from 表2 where 列名='条件'）

7、合并查询
用于合并结果集相同的多张表
两种方式
1、union
2、union all
union 结果集合并，并去重
union 不会去重