4月19日 高级查询
高级查询:
1.连接查询
select*from info,nation where info.nation=nation.code
join on
select*from info join nation on info.nation=nation.code
2.联合查询
select code,name from info union select code,name from nation
3.子查询
1)无关子查询
select code from nation where name='汉族' 去nation表中查询汉族的民族代号
select*from info where nation=(民族代号) 在info表中查询民族代号为上一个查询结果的所有结果
select*from info where nation in (select code from nation where name='汉族')
2)相关子查询
select*from car where oil<(该系列的平均油耗) 查询油耗小于该系列的平均油耗
select avg(oil) from car where brand='值' 查询某系列的平均油耗
select*from car a where oil<(select avg(oil)from car b where b.brand=a.brand)