MySQL----高级查询

连接两个表

1.连接查询，对列的扩展

select * from Info,Nation #形成笛卡尔积
select Info.Code,Info.Name,Info.Sex,Nation.Name,Info.Birthday from Info,Nation where Info.Nation = Nation.Code

select * from Info join Nation
select * from Info join Nation on Info.Nation = Nation.Code

笛卡尔积:

 

详情咨询 http://www.360doc.com/content/14/1229/21/7635_436727229.shtml

2.联合查询，对行的扩展

select Code,Name from Info
union
select Code,Name from Nation

3.子查询

(1)无关子查询

外层查询 (里层查询)
子查询的结果当做父查询的条件

子查询：select Code from Nation where Name='汉族'
父查询：select * from Info where Nation = ''

select * from Info where Nation = (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 )