MySQL database multi-table query

I. Multi-table Connection query

# Focus: External link syntax SELECT Field List    From table 1 INNER| Left| Right JOIN table 2     = Table 2. Fields;

1. Cross- connect cross-fork connection: No matching conditions apply. Generate Cartesian product

Syntax: Mysql>select * from Table1,table2;

2. Internal connection: Find two common parts of the table, equivalent to the use of conditions from the Cartesian product results filtered out the correct results

3. External link to the left connection: priority to display the left table all records

The essence is: on the basis of the internal connection to increase the left side has no result

4. Link to the right connection: priority to display all records of the right table

The essence is: on the basis of the internal connection to increase the left side of the result is not

5. Full outer connection: show left and right two tables all records

Full outer connection: Add the left side without the right side and the left side without a result on the inner connection

Note: MySQL does not support full out-of-band join

Application Examples:

 from employee left JOIN department on employee.dep_id = from employee right join Department on E mployee.dep_id = department.id# Note the difference between the Union and UNION all: Union will remove the same record

Two. Qualified connection query

# Example 1: Querying the Employee and department tables in an in-connection manner, and the age field value in the Employee table must be greater than 25, identifying employees in all departments of the company older than 25 years old  from employee,department     = department.id    and age >;  # Example 2: Querying the Employee and department tables in an in-connection manner and displaying them in ascending order of the age field  from employee,department     = department.id    and age > ORDER by age    ASC; 

Three. Sub-query

# 1: A subquery is a query statement that is nested within another query statement.  #2: Query result of inner query statement, can provide query condition for outer query statement.  #3: Subqueries can contain: In, not in, any, all, EXISTS, and not EXISTS keywords #4: You can also include comparison operators: =,! =, >, <, etc.

1. Sub-query with in keyword

 from Employee     inch From department);          

2. Subqueries with comparison operators

#comparison operators: =,! =, >, >=, <, <=, <>#Check the department name of the average age over 25 yearsSelect Id,name fromdepartment where IDinch(select dep_id fromEmployee GROUP BY DEP_ID have AVG (age) > 25);#View technical staff nameSelect Name fromEmployee where dep_idinch(SELECT ID fromdepartment where Name='Technology');#View department names for less than 1 peopleSelect Name fromdepartment where IDinch(select dep_id fromEmployee GROUP BY DEP_ID have count (id) <=1);

3. Sub-query with exists keyword

The EXISTS key word indicates existence. When you use the EXISTS keyword, the inner query statement does not return a record of the query.
Instead, it returns a true and False value. True or False
When True is returned, the outer query statement is queried, and when the return value is false, the outer query statement does not query

# Example  from Employee     ,     where exists              , from department where id=204); Empty Set (0.00 sec)

Four. Order of execution of SQL logical query statements

Definition order of 1.SELECT statement keywords

SELECT DISTINCT <select_list><left_table><join_type> join <right_table>  <join_condition><where_condition><group_by_list><order_by_condition><limit_number>

2. Order of execution of SELECT statement keywords

(7)     SELECT (8)     DISTINCT <select_list>(1) from     <left_table>(3)     <join _type> JOIN <right_table>(2) on     <join_condition>(4)     WHERE <where_ Condition>(5)     GROUP by <group_by_list>(6) have     (9)     ORDER by <order_by_condition>    LIMIT <limit_number>

MySQL database multi-table query