MySQL 多表查询

 

union 结果集合并

使用多个select分别查询不同的表，把多个select查到的记录合并在一起

一个select查到m条记录，另一个select查到n条记录，合并之后就是m+n条记录

 

#查询全校师生的id、name，使用2个select分别从tb_teacher、tb_student中查，然后将记录合并

select id，name from tb_student union [distinct] select id,name from tb_teacher;  #如果合并后的结果集中有重复的记录，默认只保留其中一个。union默认会去重，distinct可以省略不写

select id，name from tb_student union all select id,name from tb_teacher;   #如果有重复的记录，全都保留

#2个select选中的列（2个结果集中的列），列的名称、个数、数据类型都要相同，这样才能合并



#要合并多个select的结果集，两两之间用union连接即可

select语句1 union [distinct|all] select语句2 union [distinct|all] select语句3 ...

select都可以带子句，后续不再说明

 

 

---

 

 

 

子查询

一个select中要使用其他select查询的结果

#在where中使用子查询

#eg.有2张表，tb_dep 一条记录即一个系的信息，tb_student 一条记录即一个学生的信息，查询计算系所有学生的学号、姓名

#思路：先从tb_dep中查出计算机系的id，再从tb_student查出dep_id是该id的的学生

select id,name from tb_student where dep_id=(select id from tb_dep where dep_name='计算机系'); #子查询要用()括起来


#查询计算机系、数学系、物理系三个系所有学生的学号、姓名
select id,name from tb_student where dep_id in (select id from tb_dep where dep_name in ('计算机系','数学系','物理系'); 

#在having中使用子查询

select * from tb_score group by score having score>(select avg(score) from tb_score)；  #查询成绩大于平均分的所有学生信息。

#group by 主键，按主键分组，一个分组就是一条记录，对分组的过滤就是对记录的过滤





#where、having的条件过滤中有2个常用关键字：any、all

where xxx > any(子查询)  #any表示任何一个，大于结果集中的任何一个元素即可

where xxx > all(子查询)  #all表示所有，大于结果集中的所有元素才行

#在from中使用子查询

select 列名 from (select子查询);  #先用子查询对原表中的记录过滤一遍，把子查询的结果集作为一张临时表，从临时表中查数据（再次过滤）。列名是临时表中的列名

select 列名 from （select子查询）[别名] [子句]； #临时表和原表的使用方式完全一样，可以起别名，可以带子句，可以直接使用临时表中的列名

 

 

 

---

 

 

 

join  连接查询

1、笛卡尔积

2个集合相乘，得到的是笛卡尔积，比如：

{a,b,c} * {a,b,c} = { (a,a)，(a,b)，(a,c)，(b,a)，(b,b)，(b,c)，(c,a)，(c,b)，(c,c) }    #用一个集合的每个元素去乘另一个集合的每个元素

 

 

2、连接查询

一个select查多张表

 

sql 99中连接查询的语法：

select  表1、表2中的列名  from

表1 [别名]  连接类型 join  表2 [别名]

on 连接条件

[where子句]

[group by子句]

[having子句]

[order by子句]

[limit子句]

sql 99的连接查询都带关键字join，称之为显式写法

 

在sql 99之前还有一个sql语法版本：sql 92，顾名思义，1992年制定的标准

sql 92的连接查询不带join，也具有相同的功能，现在还能用，称之为隐式写法

 

 

连接查询根据连接类型可分为4大类：

• 交叉连接
• 内连接：又分为等值连接、非等值连接、自连接
• 外连接：又分为左外连接（左连接）、右外连接（右连接）、全外连接（mysql不支持，oracle支持）
• 自然连接

 

 

#交叉连接，得到的是笛卡尔积

select * from tb_student cross join tb_score;

select st.id,st.name,sc.score from tb_student st cross join tb_score sc;

  #假设tb_student有30条记录，tb_score有20条记录（10人因缺考、缓考等原因暂时没有成绩），

  #2张表以学号（id）为主键，或者tb_student以学号为主键、tb_score以外键形式关联tb_student的主键

  #把2张表作为集合，一条记录即集合中的一个元素，2张表相乘得到笛卡尔积（30*20条记录）

  #返回600条记录，这肯定不是我们想要的。基本不使用交叉连接

#交叉连接的隐式写法

select * from tb_student,tb_score; 

select st.id,st.name,sc.score from tb_student st,tb_score sc; 

 

#内连接

select * from tb_student st [inner] join tb_score sc on st.id=sc.id;

select stu.id,stu.name,sc.chinese,sc.math,sc.english from tb_student st [inner] join tb_score sc on st.id=sc.id;

#join默认就是内连接，inner可以省略不写

#内连接是以记录少的为准进行筛选（2张表都要有）。结果集中只有20条记录，成绩为空的10人不要了

#on指定2张表的连接条件（关联关系），使用on对笛卡尔积中的记录进行筛选。如果是单张表的条件，比如st.id>10，写在where中

#查询2个表中的所有列，只写一个*,不能写成*,*的形式, 如果要查询一个表的所有列、另一个表的部分列，写成 表1.*,表2.列名 的形式

#等值连接：on中使用等号进行筛选、判断，故而称为

#非等值连接：on中使用>、>=、<、<=、between and等连接2张表（不使用等号）

#内连接的隐式写法

select * from tb_student st,tb_score sc where st.id=sc.id;  #在where中指定连接条件

select st.id,st.name,sc.score from tb_student st,tb_score sc where st.id=sc.id;

  #内连接之自连接

  #顾名思义，自己连接自己进行查询。有时候表是关联到自身的，比如员工表，我和我的组长都是员工，都在一张表里：

  #id，name，.... ，superior_id，superior_id字段是此员工直接上司的id。

  select e.*，m.name as superior_name from tb_employees e [inner] join tb_employees m where e.superior_id=m.id; #查询员工的信息及其直属上司的姓名(id已近有了)

  #如果要查询某一员工的，后面加 and e.id=1 或者 and e.name='张三'，通过工号或者姓名来查询

 

  #都是同一张表，取不同的别名来区分

 

###外连接###

select * from tb_student st left [outer] join tb_score sc on st.id=sc.id;

select stu.id,stu.name,sc.chinese,sc.math,sc.english from tb_student st left [outer] join tb_score sc on st.id=sc.id;

#outer可以省略不写，left是左外连接，right是右外连接，full是全外连接（mysql不支持，oracle支持）

#内连接以少的表为准（主表），左外连接以join左边的表为准（主表），右连接是以join右边的表为准（主表），full是2张表都保留（在另一张表中找不到对应|关联的记录都会被保留）。

#eg. tb_student left join tb_score，以左边的为准，结果集有30条记录，成绩为空的学生记录会保留

#eg. tb_student right join tb_score，以右边的为准，结果集中有20记录，成绩为空（找不到对应成绩）的学生信息不要了

#自然连接

select * from tb_student natural join tb_score;

select st.id,st.name,sc.score from tb_student st natural join tb_score sc;

#自然连接算是一种特殊的等值连接，作用和等值连接相同，但不需要使用on指定连接条件，会自动把2张表中相同的列（列名、数据类型都要相同）作为连接条件

#比如两张表都有id字段，会自动加 on 表1.id=表2.id，不需要我们手动加





#多张表，写多个join就行

select 表1的字段,表2的字段,表3的字段 from 表1 [别名]
left join 表2 [别名] on 表1.id=表2.id
left join 表3 [别名] on 表2.id=表3.id

 

  #多张表的隐式写法

  select 表1的字段,表2的字段,表3的字段 from 表1 [别名]，表2 [别名]，表3 [别名] where 表1.id=表2.id and 表2.id=表3.id