MySQL database from delete library to run MySQL multi-table query

An introduction

Topics in this section

Multi-Table Connection query

Compound conditional Join Query

Sub-query

Prepare table

Company.employee
Company.department

#Build TableCREATE TABLE Department (ID int,name varchar (20) ; CREATE table employee (ID int primary KEY auto_increment,name varchar (20), sex enum ('male','female') notNull default'male', age int,dep_id int);#Inserting DataINSERT INTO department values (200,'Technology'),(201,'Human Resources'),(202,'Sales'),(203,'Operations') insert into employee (NAME,SEX,AGE,DEP_ID) VALUES ('Egon','male', 18,200),('Alex','female', 48,201),('Wupeiqi','male', 38,201),('Yuanhao','female', 28,202),('Liwenzhou','male', 18,200),('Jingliyang','female', 18,204);#view table structure and dataMysql>desc Department;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID | Int (11) | YES | | NULL | | | name | varchar (20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+MySQL>DESC employee;+--------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+-----------------------+------+-----+---------+----------------+| ID | Int (11) | NO | PRI | NULL | auto_increment | | name | varchar (20) | YES | | NULL | | | sex | Enum'male','female') | NO | | Male | | | Age | Int (11) | YES | | NULL | | | dep_id | Int (11) | YES | | NULL | |+--------+-----------------------+------+-----+---------+----------------+MySQL> select * fromDepartment;+------+--------------+| ID | Name |+------+--------------+| 200 | Technology | | 201 | Human Resources | | 202 | Sales | | 203 | Operating |+------+--------------+MySQL> select * fromemployee;+----+------------+--------+------+--------+| 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 |+----+------------+--------+------+--------+

Two 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: No matching criteria are applicable. 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 |+----+------------+--------+------+--------+------+--------------+

2 Internal connections: 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;

3 Left Link: Priority display all records of left table

# 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         |+----+------------+--------------+

4 Link Right Connection: Priority display all records of 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         |+------+-----------+--------------+

5 All-out 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#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

Three qualified connection queries

# 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; 

Four sub-query

#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

#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)

MySQL database from delete library to run MySQL multi-table query