The way of programming: MySQL series multi-table query

Multi-Table Connection query

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

Cross Connect: No matching criteria are applied. Generate Cartesian product

Mysql> SELECT * fromemployee,department;+----+------------+--------+------+--------+------+--------------+| ID | name | sex | Age | dep_id | ID |  Name |+----+------------+--------+------+--------+------+--------------+| 1 | Egon |   Male |    18 |  200 | 200 |  Technology | | 1 | Egon |   Male |    18 |  200 | 201 |  Human Resources | | 1 | Egon |   Male |    18 |  200 | 202 |  Sales | | 1 | Egon |   Male |    18 |  200 | 203 |  Operations | | 2 | Alex |   Female |    48 |  201 | 200 |  Technology | | 2 | Alex |   Female |    48 |  201 | 201 |  Human Resources | | 2 | Alex |   Female |    48 |  201 | 202 |  Sales | | 2 | Alex |   Female |    48 |  201 | 203 |  Operations | | 3 | Wupeiqi |   Male |    38 |  201 | 200 |  Technology | | 3 | Wupeiqi |   Male |    38 |  201 | 201 |  Human Resources | | 3 | Wupeiqi |   Male |    38 |  201 | 202 |  Sales | | 3 | Wupeiqi |   Male |    38 |  201 | 203 |  Operations | | 4 | Yuanhao |   Female |    28 | 202 | 200 |  Technology | | 4 | Yuanhao |   Female |    28 |  202 | 201 |  Human Resources | | 4 | Yuanhao |   Female |    28 |  202 | 202 |  Sales | | 4 | Yuanhao |   Female |    28 |  202 | 203 |  Operations | | 5 | Liwenzhou |   Male |    18 |  200 | 200 |  Technology | | 5 | Liwenzhou |   Male |    18 |  200 | 201 |  Human Resources | | 5 | Liwenzhou |   Male |    18 |  200 | 202 |  Sales | | 5 | Liwenzhou |   Male |    18 |  200 | 203 |  Operations | | 6 | Jingliyang |   Female |    18 |  204 | 200 |  Technology | | 6 | Jingliyang |   Female |    18 |  204 | 201 |  Human Resources | | 6 | Jingliyang |   Female |    18 |  204 | 202 |  Sales | | 6 | Jingliyang |   Female |    18 |  204 | 203 | Operating |+----+------------+--------+------+--------+------+--------------+

Inner connection: Only matching rows are connected

#find two common parts of the table, equivalent to the use of conditions from the Cartesian product results to filter out the correct results#department does not have 204 this department, so the employee table about 204 of this staff information does not match outMysql> Select Employee.id,employee.name,employee.age,employee.sex,department.name fromEmployee INNER JOIN Department on Employee.dep_id=department.id;+----+-----------+------+--------+--------------+| ID | name | Age | sex |  Name |+----+-----------+------+--------+--------------+| 1 |   Egon | 18 | Male |  Technology | | 2 |   Alex | 48 | Female |  Human Resources | | 3 |   Wupeiqi | 38 | Male |  Human Resources | | 4 |   Yuanhao | 28 | Female |  Sales | | 5 |   Liwenzhou | 18 | Male | Technical |+----+-----------+------+--------+--------------+#the above SQL is equivalent toMysql> Select Employee.id,employee.name,employee.age,employee.sex,department.name fromEmployee,department where employee.dep_id=department.id;

External link Left connection: Show all records of left table first

# The left table, that is to find all employee information, of course, including no department of Staff # The essence is: On the basis of the internal connection to increase the left side has no result  from employee left JOIN department on Employee.dep_id=Department.id; +----+------------+--------------+| ID | Name       | depart_name  |+----+------------+--------------+|  1 | Egon       | technology         | |  5 | Liwenzhou  | technology         | |  2 | Alex       | Human Resources     | |  3 | Wupeiqi-    Human Resources     | |  4 | Yuanhao-    Sales         | |  6 | Jingliyang | NULL         |+----+------------+--------------+

The right connection of the outside link: first display all records of the right table

# The right table, which identifies all departmental information, including those with no employees # The essence is: On the basis of the internal connection to increase the left side of the result is not  from Employee right join department on Employee.dep_id=Department.id; +------+-----------+--------------+| ID   | name      | depart_name  |+------+-----------+--------------+|    1 | Egon      | technology         | |    2 | Alex      | Human Resources     | |    3 | Wupeiqi-   Human Resources     | |    4 | Yuanhao-   Sales         | |    5 | Liwenzhou | Technology         | | NULL | NULL      | operation         |+------+-----------+--------------+

Full outer connection: Displays all records of left and right two tables

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#emphasis: MySQL can use this method to indirectly implement a full-outer connectionSELECT * fromEmployee left JOIN Department on employee.dep_id =Department.idunionselect* fromEmployee right Join department on employee.dep_id =department.id;#View Results+------+------------+--------+------+--------+------+--------------+| ID | name | sex | Age | dep_id | ID |    Name |+------+------------+--------+------+--------+------+--------------+| 1 | Egon |   Male |    18 |  200 | 200 |    Technology | | 5 | Liwenzhou |   Male |    18 |  200 | 200 |    Technology | | 2 | Alex |   Female |    48 |  201 | 201 |    Human Resources | | 3 | Wupeiqi |   Male |    38 |  201 | 201 |    Human Resources | | 4 | Yuanhao |   Female |    28 |  202 | 202 |    Sales | | 6 | Jingliyang |   Female |    18 | 204 | NULL | NULL | | NULL | NULL | NULL |   NULL |  NULL | 203 | Operating |+------+------------+--------+------+--------+------+--------------+#Note the difference between Union and Union: Union will remove the same record

Qualifying Connection Query

# Example 1: Querying the Employee and department tables in an internal connection, and the Age field value in the Employee table must be greater than 25, that is, identify employees older than 25 years old, and the department where the employee is located  from Employee INNER JOIN Department     = department.id    >; # 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; 

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.

Sub-query with in keyword

#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 people (subqueries get someone's department ID)Select Name fromDepartment where ID not inch(SELECT DISTINCT dep_id fromEmployee);

Subqueries with comparison operators

#comparison operators: =,! =, >, >=, <, <=, <>#search for employee names and ages greater than the average age of everyoneMysql> Select Name,age fromEMP where > (select AVG (age) fromEMP);+---------+------+| name | Age |+---------+------+| Alex | 48 | | Wupeiqi | |+---------+------+rowsinchSet (0.00sec)#search for employee names and ages greater than the average age within the DepartmentSelect T1.name,t1.age fromEMP T1inner Join (select DEP_ID,AVG (age) Avg_age fromEMP Group by dep_id) T2on t1.dep_id=T2.dep_idwhere t1.age> t2.avg_age;

Subquery 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

#There are dept_id=203,ture in the Department tableMysql> SELECT * fromEmployee-where exists-(SELECT ID fromdepartment where id=200);+----+------------+--------+------+--------+| ID | name | sex | Age |  dep_id |+----+------------+--------+------+--------+| 1 | Egon |   Male |    18 |  200 | | 2 | Alex |   Female |    48 |  201 | | 3 | Wupeiqi |   Male |    38 |  201 | | 4 | Yuanhao |   Female |    28 |  202 | | 5 | Liwenzhou |   Male |    18 |  200 | | 6 | Jingliyang |   Female |    18 | 204 |+----+------------+--------+------+--------+#There are dept_id=205,false in the Department tableMysql> SELECT * fromEmployee-where exists-(SELECT ID fromdepartment where id=204); Empty Set (0.00 sec)

The way of programming: MySQL series multi-table query