Mysql Basic Advanced Query

Both car and nation represent table names in the West content

1. Use Select for both advanced and simple queries. From.. The statement from the following table name allows a table to be separated by commas between multiple table tables and tables

2. Simple query and advanced query is not a separate advanced query can also use simple query

3. Simple query and complex query contact: Simple query in the following conditions are unknown, you need to use another query instead of this becomes an advanced query

4. The difference between a linked query and a federated query: 1. Link query connect more than two tables output is displayed in a table; a union query outputs the contents of only one table as a condition of another table

Note related queries When federated queries: subqueries and parent queries when a condition of a parent query is the same as the condition of a subquery subquery

2. When the same point is associated, the foreign key relationship must be established;

A. Link query

1. Extension of the link query to the result set column

Select*from Table name

querying more than one table Query Results Show in a table

SELECT * from info,nation # to form Cartesian product disadvantage query slow (generate large amount of redundant data)

SELECT * from table 1 names , table 1 where table 1 name . Column Name = Table 2 name . Column Name

SELECT * from Info,nation where info.code=nation.code

Select Info.code, info. Name, birthday from info,nation where Info.code=nation.code

because The birthday is not duplicated in the two tables, it can be written directly (such as simple query inside)

SELECT * FROM info join Nation on Info.nation=nation.code

Two Union query Union

Select Column name , list name from table 1

Union

Select Column name , list name from table 2

three. subquery (query efficiency is important)

Parent Query : outer query

Sub-query: innermost Query

Sub-query The query results as a condition of the parent query

1. unrelated subqueries

the subquery has no relation to the parent query subqueries can be executed separately

Parent query:Select *from info where nation= ()

subquery : Select code from Nation where Name= ' Han '

Select *from Info where nation=(select code from Nation where Name= ' Han ')

②. Search all car information in series named ' BMW 5 system '
SELECT * FROM car where brand= (select Brand_Code from brand where Brand_name= ' BMW 5 Series ')

1. Related sub-query

when the subquery is executed   and the parent query about the Department subqueries cannot be executed individually

1. Check the car table for fuel consumption less than the average fuel consumption of all vehicle information

Parent Query: Car Information: select * from car where oil< average fuel consumption

Sub-query:  average fuel consumption ; Select AVG (oil) from car where brand = the series

SELECT * FROM car as a where oil< (select Avg. from car as b where b.brand=a . Brand)

Mysql Basic Advanced Query