Database section---advanced queries;

Example

1. Link query (query Multiple tables of data, there are foreign key relationships), is the extension of the post-query column

SELECT * FROM table name 1, table name 2; #会形成笛卡尔积, the data of several tables match each other, and the number of data is multiplied by the number of each sheet, resulting in redundancy;

SELECT * FROM table name 1, table name 2 where table name 1. column name = table Name 2. column name; this removes redundant data;

With the top two tables combined, there is a column with title and code duplicates, minus the extra columns: Select Table 1. Column 1, table 1. Column 2, table 1. Column 3 .... Table 2. Column 2: From table name 1, table name 2 where table name 1. column name = table Name 2. column name;

SELECT * FROM table 1 join table 2 on table 1. Column name = Table 2. Column name;

With the top two tables combined, there is a column with title and code duplicates, minus the extra columns: Select Table 1. Column 1, table 1. Column 2, table 1. Column 3 .... Table 2. Column 2: From table name 1 join table name 2 on table name 1. column name = table Name 2. column name;

2. Joint query, the extension of the query, the data of multiple tables are put together to display, the number of columns to query the same

Select Name,firm from family

Union

Select Code,name from Title

3. Sub-query

Parent Query: Outer query

Sub-query: Inner query

The subquery executes first, and the result of the subquery is the condition of the parent query

(1) Unrelated subqueries

Subqueries do not have a relationship with the parent query when executing the subquery can be executed separately

1. Inquire about all the people of the Han nationality

Parent query: SELECT * FROM info where nation= ()

Subquery: Select code from Nation where Name= ' Han ' Nation with code is a column with a foreign key relationship

The statement of the handle query is placed inside the parentheses of the parent query

Combined: SELECT * from info where nation= (select code from Nation where Name= ' Han ') equals nation= (code)

2. Search all car information in the series named BMW 5

(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 car table for fuel consumption is less than the series average fuel consumption of all vehicle information

Parent query: SELECT * from car where oil< (average fuel consumption for this series)

Subquery: Select AVG (oil) from car where brand= the series

SELECT * FROM car where oil< (select Avg. from car where brand= the series)

Since both the parent query and the subquery are a table for the query, we name the table name of the parent query query, the table name of the subquery query,

SELECT * from car as AA where oil< (select Avg. from car as BB where brand= the series)

And then determine ' the series ',

SELECT * from car as AA where oil< (select Avg. from car as BB where Bb.brand=aa.brand)

Database section---advanced queries;