高级查询

高级查询
1.联合查询

select code,name from info
union
select code,name from nation

2.连接查询
形成笛卡尔积
select * from info,nation where info.nation=nation.code
select info.code,info.name,sex,nation.name,birthday from info,nation where info.nation=nation.code

select info.code,info.name,sex,nation.name,birthday from info join nation on info.nation=nation.code

3.子查询
子查询的结果作为父查询的条件使用

无关子查询
子查询和父查询没有关系，子查询单独拿出来可以执行
1.查找民族为“汉族”的所有人员信息
select * from info where nation =(select code from nation where name='汉族')
2.查询所有厂商是“一汽大众”的汽车信息
select * from car where brand in(select brand_code from brand where prod_code in(select prod_code from Productor where prod_name='一汽大众'))
#套子查询，从
相关子查询
查询油耗低于该系列平均油耗的汽车信息
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)