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

#建表create Table Department (ID int,name varchar); CREATE TABLE employee (ID int primary KEY auto_increment,name varchar ( (), Sex enum (' Male ', ' female ') not null 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 | Operation |+------+--------------+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 |+----+------------+--------+------+--------+

Two multi-table connection query

#重点: Outer link Syntax select field list from    table 1 inner| Left| Right JOIN table 2 on    table 1. field = table 2. field;

1 Cross connect: No matching criteria are applicable. 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 |  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 |      Operation   || 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

#找两张表共有的部分, which is equivalent to using conditions to filter out the correct results from the Cartesian product #department there is no 204 in this department, so the employee table about 204 of this staff information does not match 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 | Human Resources     | |  3 | Wupeiqi   |   38 | Male-   human Resources     | |  4 | Yuanhao   |   28 | Female | Sales         | |  5 | Liwenzhou |   18 | Male   | technical         |+----+-----------+------+--------+--------------+ #上述sql等同于mysql > select Employee.id, Employee.name,employee.age,employee.sex,department.name from Employee,department where employee.dep_id= Department.id;

3 Left Link: Priority display all records of left table

#以左表为准, that is, to find out 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 results mysql> select Employee.id,employee.name,department.name As Depart_name 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

#以右表为准, that is, identifying all departmental information, including those with no employees. The essence is: Add the right side to the left without the results on the INNER join mysql> select Employee.id,employee.name,department.name as Depart_name 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 on the basis of the inner connection and the right side has no left result # Note: MySQL does not support full external connection. join# Stress: MySQL can use this method to indirectly implement a full-outer connection select * from the employee left JOIN Department on employee.dep_id = department.idunionselect * FROM employee right JOIN department on employee.dep_id = Depart Ment.id, #查看结果 +------+------------+--------+------+--------+------+--------------+| 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 |+------+------------+--------+------+--------+------+--------------+ #注意 Union and Union The difference between all: Union will remove the same record 

Three qualified connection queries

#示例1: Query 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, find employees in all departments of the company who are older than 25 years old, select Employee.Name, Employee.age from employee,department    where employee.dep_id = Department.id and age    > 25;# Example 2: Querying the Employee and department tables in an in-connection manner, and displaying select Employee.id,employee.name,employee.age,department.name in ascending order of the age field From Employee,department    where employee.dep_id = Department.id and Age    > ORDER by age    ASC;

Four sub-query

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

1 sub-query with in keyword

#查询employee表, but dep_id must appear in the Department table in the SELECT * from Employee    where dep_id in        (select ID from department);

2 Sub-query with comparison operator

#比较运算符: =,! =, >, >=, <, <=, <> #查询平均年龄在25岁以上的部门名select id,name from department    where ID in         ( Select dep_id from the employee GROUP by DEP_ID have AVG (age) >, #查看技术部员工姓名select name from employee    where dep_id In         (the Select ID from department where name= ' technology '); #查看不足1人的部门名select name from department    where ID in         (select D EP_ID from employee 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

#department表中存在dept_id =203,turemysql> SELECT * FROM Employee     , where exists         (SELECT ID From department where id=200), +----+------------+--------+------+--------+| ID | Name       | sex | Age  | dep_id |+----+------------+--------+------+--------+|  1 | Egon       | male   |   |    | |  2 | Alex       | female |   |    201 | |  3 | Wupeiqi    | male   |   |    201 | |  4 | Yuanhao    | female |   |    202 | |  5 | Liwenzhou  | male   |   |    | |  6 | Jingliyang | Female |   |    204 |+----+------------+--------+------+--------+ #department表中存在dept_id =205,falsemysql> SELECT * FROM employee     , where exists         (select ID from department where id=204); Empty Set (0.00 sec)

Five Comprehensive ExercisesInit.sql

#准备表, record mysql> CREATE database db1;mysql> use db1;mysql> source/root/init.sql

！！！ The most important: be sure to understand the order of execution of SQL logical query statements before practicing

Links: http://www.cnblogs.com/liluning/p/7490116.html

Topics

Reference Answer: http://www.cnblogs.com/wupeiqi/articles/5748496.html

mysql-Multi-Table query