MySQL Database Advanced Query

1. Connection Query
SELECT * FROM Info,nation #笛卡尔积
SELECT * FROM Info,nation where Info.nation=nation.code

Join on connection
SELECT * from Info join Nation #join连接
SELECT * from Info join Nation on Info.nation=nation.code

2. Joint Inquiry
Select Code,name from Info
Union
Select Code,name from Nation

3, sub-query
1) Unrelated subqueries
SELECT * from Nation where name= ' Han ' #去Nation表中查名族代号
SELECT * from Info where nation= (ethnic code) #在Info表中查询民族代号为上一个查询结果的所有信息
SELECT * from Info where Nation = (SELECT * from Nation where name= ' Han ')
The results of a subquery query are queried using a subquery that can be executed independently of the sub-query

2) Related sub-query

SELECT * from Car where oil< (average fuel consumption for this series) #查询油耗小于该系列平均油耗
Select AVG (oil) from Car where Brand = "value" #查询某系列的平均油耗
SELECT * from Car a where oil< (select Avg. from car b where B.brand =a.brand)

MySQL Database Advanced Query