MySQL advanced query

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 queries

Select Code,name from Info
Union
Select Code,name from Nation

3. Sub-query

1) Unrelated subqueries

Select Code from Nation where Name = ' Han ' #去Nation表中查询汉族的民族代号

SELECT * from Info where Nation = (ethnic code) #在Info表中查询民族代号为上一个查询结果的所有信息

SELECT * from Info where Nation = (select Code from Nation where Name = ' Han ')

The results of a subquery query are used by the parent query, and the subquery can be performed independently as a non-subquery

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 advanced query