Database---Table---multi-table query

Multi-Table Query

• Multi-Table Connection query
• Compound conditional Join Query
• Sub-query

First, prepare the table

#建表create 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); #插入数据insert 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); #查看表结构和数据mysql>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 *From department;+------+--------------+| ID | Name |+------+--------------+| 200 | Technology | | 201 | Human Resources | | 202 | Sales | | 203 | Operating |+------+--------------+MySQL> select *from employee;+----+------------+--------+------+--------+| 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 |+----+------------+--------+------+--------+Table department and employee

Create a table, insert data

Second, multi-table connection query

Grammar:

#重点: External link Syntax select field List    From table 1 INNER| Left| Right JOIN table 2     = Table 2. Fields;

2.1 Cross-connect: No matching conditions are applied to generate Cartesian product

Mysql> SELECT *From employee,department; +----+------------+--------+------+--------+------+--------------+        | ID | name | sex | Age | dep_id | ID |        name |  +----+------------+--------+------+--------+------+--------------+        | 1 | Egon |   Male |    18 |  200 | 200 |        Technology |  | 1 | Egon |   Male |    18 |  200 | 201 |        HR |  | 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 |        HR |  | 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 |        HR |  | 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 |        HR |  | 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 |        HR |  | 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 |        HR |  | 6 | Jingliyang |   Female |    18 |  204 | 202 |        Sales |  | 6 | Jingliyang |   Female |    18 |  204 | 203 |        Operations | +----+------------+--------+------+--------+------+--------------+ Cross Connect

does not apply any matching criteria. Generate Cartesian product

2.2 Internal 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 out

 mysql> Select Employee.id,employee.name,employee.age,employee.sex,department.name from employee inner JOIN         Department on Employee.dep_id=department.id; +----+-----------+------+--------+--------------+        | ID | name | Age | sex |        name |  +----+-----------+------+--------+--------------+        | 1 |   Egon | 18 | Male |        Technology |  | 2 |   Alex | 48 | Female |        HR |  | 3 |   Wupeiqi | 38 | Male |        HR |  | 4 |   Yuanhao | 28 | Female |        Sales |  | 5 |   Liwenzhou | 18 | Male |        Technology | +----+-----------+------+--------+--------------+ #上述sql等同于 Mysql> Select Employee.id,employee.name,employee.age,employee.sex,department.name from Employee,department where Employee.dep_id=department.id; inner connection  

Internal Connection

2.3 Left connection: First show 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

Mysql> Select Employee.id,employee.name,department.name as Depart_name from the 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         | |  NULL         | +----+------------+--------------+

left Connection

2.4

　　

　　

Database---Table---multi-table query