—恢复内容开始—

高级查询

一、分组查询

1、group by

select name,oil,powers from car group by powers

select name,oil,powers from car group by powers,oil

2、group_concat(字段名)

select powers,group_concat(oil) from car group by powers

3、函数

select powers,count(*),max(price),min(price),avg(price) from car group by powers

4、with rollup

select powers,count(*),max(price),min(price),avg(price) from car group by powers with rollup

5、having

select powers,count(*),max(price),min(price),avg(price) from car group by powers with rollup having count(*) >=2

二、链接查询

1、内链接

select a.sid,sname,smajor,sage,ssex from studen as a,other_stu as b where a.sid = b.sid and ssex = ‘boy’ and a.sid = ‘2’

select a.sid,sname,smajor,sage,ssex from studen as a inner join other_stu as b where a.sid = b.sid and ssex = ‘boy’ and a.sid = ‘2’

2、左链接

select a.sid,sname,smajor,sage,ssex from studen as a left join other_stu as b on a.sid = b.sid and ssex = ‘boy’ and a.sid = ‘2’

3、右链接

select a.sid,sname,smajor,sage,ssex from studen as a right join other_stu as b on a.sid = b.sid and ssex = ‘boy’ and a.sid = ‘2’

4、自然链接　

select a.sID,sName,sMajor,sAge,sSex from studen as a natural join other_stu as b;
select * from studen as a natural join other_stu as b;

自动清除笛卡儿积，把重复的字段合并

5、交叉链接

select * from studen as a cross join other_stu as b;

形成笛卡儿积

三、子查询

select * from studen as a left join (select * from other_stu where ssex = ‘boy’) as b on a.sid = b.sid and a.smajor = ‘cs’

any/some

select * from studen as a where sid = some(select sid from other_stu where ssex = ‘boy’)

exists(子查询查到数据则执行外层查询，子查询没有查到数据则不执行外层查询)

select * from stude

select * from studen as a natural join other_stu as b where exists(select * from other_stu where ssex = ‘boy’) and b.ssex = ‘boy’

四、联合查询：查询时，查询的字段个数要一样

union all

select * from studen union all select * from other_stu

select * from studen union all select * from studen

把两张表拼在一起，没有去重效果

union

select * from studen union select * from studen

两张表联合查询时，如果两个字段的所有数据都相同(字段类型可以不同)会把两张表拼在一起并去重