MySQL 多表查询

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