Database--Advanced query

1. Connection query, extension to result set columns
SELECT * FROM Info

SELECT * FROM Info,nation #形成笛卡尔积
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 * FROM info join nation on Info.nation=nation.code

2. Federated queries, extension of result set rows
Select Code,name from Info
Union
Select Code,name from Nation

3.
Sub-query
Parent Query: Outer query
Sub-query: Inner query
The result of the subquery is used as a condition of the parent query.

(1) Unrelated subqueries

The subquery does not have a relationship with the parent query at execution time, and the subquery can be executed separately

1. Query the People for ' Han ' all personnel information

Parent query: SELECT * FROM info where nation= ()
Subquery: Select code from Nation where Name= ' Han '

2

(2) Related sub-query

The subquery does not have a relationship with the parent query at execution time, and the subquery cannot be executed separately
1. Check the fuel consumption in the car table is less than the average fuel consumption of the series All information
1: Parent Query: SELECT * FROM car where oil < (average fuel consumption in this series)
2: Sub-query: Select AVG (oil) from car where Brand = the series
SELECT * from car as a where oil < (select Avg.) from car as B where
BRABD=BRAMD)

Database--Advanced query