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