MySQL Advanced Query

Advanced Query

1. Connection queries (extensions to columns)

The first form of
SELECT * FROM Info,nation #会形成笛卡尔积

SELECT * from info,nation where info.nation = Nation.code #加入筛选条件

Select Info.code,info.name,sex,nation.name from info,nation where info.nation = Nation.code
#查询指定列

Select Info.code as ' codename ', Info.name as ' name ', sex as ' gender ', nation.name as ' national ', Birthday as ' birthday ' from info,nation where info.na tion = Nation.code
#换表头

The second form of:
SELECT * from Info join Nation #join连接, the formation of Cartesian product query is very slow
SELECT * from Info join Nation on info.nation = Nation.code #join关键字

2. Federated queries (expansion of rows)

SELECT * from Info where Nation = ' n002 '
Union #关键字
SELECT * from Info where Code = ' p002 '

3. Subquery (unrelated subquery) If the subquery statement can be executed separately, it is irrelevant query
There are two subqueries in an SQL statement, and one of the results of a query is a second

Select query condition, a becomes the inner query, B is the outer query or the parent query

Query people for the ' Han Nationality ' information:
SELECT * from Info where Nation = (select Code from Nation where Name = ' Han ')

Query people for ' Han ' or ' hui ' personnel information

SELECT * from Info where Nation in (select Code from Nation where name = ' Han ' or name = ' hui ')

SELECT * from Info where Nation not in (select Code from Nation where name = ' Han ' or name = ' hui ')
#in keyword not in the table

4. Sub-query (related subquery)

Query the same series of fuel consumption than the average fuel consumption of car information
Sub-query
Select AVG (oil) from Car where Brand = "
Parent Query
SELECT * from Car where oil< average fuel consumption

SELECT * from Car a where A.oil < (select AVG (b.oil) from car b where B.brand = A.brand)

#b. Brand is the search condition, A.brand is the information

MySQL Advanced Query