mysql-operation and usage of multi-table joins

• First, Introduction
• Second, multi-table connection query
• Three, meet the conditions of connection query
• Four, sub-query

First, Introduction

Topics in this section

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

Prepare table

#Build TableCREATE TABLE DEP (ID int,name varchar (20) ; CREATE table emp (ID int PRIMARY KEY auto_increment,name varchar (20), sex enum ('male','female') notNull default'male', age int,dep_id int);#Inserting DataINSERT into DEP values (200,'Technology'),(201,'Human Resources'),(202,'Sales'),(203,'Operations'INSERT INTO EMP (NAME,SEX,AGE,DEP_ID) VALUES ('Dava','male', 18,200),('She','female', 48,201),('Sanva','male', 38,201),('Shiva','female', 28,202),('Five Dolls','male', 18,200),('Six Dolls','female', 18,204);#view table structure and dataMysql>desc DEP;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID | Int (11) | YES | | NULL | | | name | varchar (20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+MySQL>desc EMP;+--------+-----------------------+------+-----+---------+----------------+| 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 * fromDEP;+------+--------------+| ID | Name |+------+--------------+| 200 | Technology | | 201 | Human Resources | | 202 | Sales | | 203 | Operating |+------+--------------+MySQL> select * fromEMP;+----+------------+--------+------+--------+| ID | name | sex | Age | dep_id |+----+------------+--------+------+--------+| 1 | Dava | Male | 18 | 200 | | 2 | Two Dolls | Female | 48 | 201 | | 3 | Three Dolls | Male | 38 | 201 | | 4 | Four Dolls | Female | 28 | 202 | | 5 | Five Dolls | Male | 18 | 200 | | 6 | Six Dolls | Female | 18 | 204 |+----+------------+--------+------+--------+

DEP table and EMP tableSecond, multi-table connection query

#重点: External 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 conditions apply. Generate Cartesian product

Mysql> SELECT * fromEMP,DEP;+----+------------+--------+------+--------+------+--------------+| ID | name | sex | Age | dep_id | ID |  Name |+----+------------+--------+------+--------+------+--------------+| 1 | Dava |   Male |    18 |  200 | 200 |  Technology | | 1 | Dava |   Male |    18 |  200 | 201 |  Human Resources | | 1 | Dava |   Male |    18 |  200 | 202 |  Sales | | 1 | Dava |   Male |    18 |  200 | 203 |  Operations | | 2 | Two Dolls |   Female |    48 |  201 | 200 |  Technology | | 2 | Two Dolls |   Female |    48 |  201 | 201 |  Human Resources | | 2 | Two Dolls |   Female |    48 |  201 | 202 |  Sales | | 2 | Two Dolls |   Female |    48 |  201 | 203 |  Operations | | 3 | Three Dolls |   Male |    38 |  201 | 200 |  Technology | | 3 | Three Dolls |   Male |    38 |  201 | 201 |  Human Resources | | 3 | Three Dolls |   Male |    38 |  201 | 202 |  Sales | | 3 | Three Dolls |   Male |    38 |  201 | 203 |  Operations | | 4 | Four Dolls |   Female |    28 |  202 | 200 | Technology | | 4 | Four Dolls |   Female |    28 |  202 | 201 |  Human Resources | | 4 | Four Dolls |   Female |    28 |  202 | 202 |  Sales | | 4 | Four Dolls |   Female |    28 |  202 | 203 |  Operations | | 5 | Five Dolls |   Male |    18 |  200 | 200 |  Technology | | 5 | Five Dolls |   Male |    18 |  200 | 201 |  Human Resources | | 5 | Five Dolls |   Male |    18 |  200 | 202 |  Sales | | 5 | Five Dolls |   Male |    18 |  200 | 203 |  Operations | | 6 | Six Dolls |   Female |    18 |  204 | 200 |  Technology | | 6 | Six Dolls |   Female |    18 |  204 | 201 |  Human Resources | | 6 | Six Dolls |   Female |    18 |  204 | 202 |  Sales | | 6 | Six Dolls |   Female |    18 |  204 | 203 | Operating |+----+------------+--------+------+--------+------+--------------+

Cross-Connect demo

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#DEP does not have 204 this department, so the EMP table about 204 This employee information does not match outMysql> Select Emp.id,emp.name,emp.age,emp.sex,dep.name fromEMP INNER JOIN DEP on emp.dep_id=dep.id;+----+-----------+------+--------+--------------+| ID | name | Age | sex |  Name |+----+-----------+------+--------+--------------+| 1 |   Dava | 18 | Male |  Technology | | 2 |   Two Dolls | 48 | Female |  Human Resources | | 3 |   Three Dolls | 38 | Male |  Human Resources | | 4 |   Four Dolls | 28 | Female |  Sales | | 5 |   Five Dolls | 18 | Male | Technical |+----+-----------+------+--------+--------------+#the above SQL is equivalent toMysql> Select Emp.id,emp.name,emp.age,emp.sex,dep.name fromEMP,DEP where emp.dep_id=dep.id;

Internal Connection Demo

3, the external link to the left connection: priority to display the left table all records

# 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 EMP left join DEP on emp.dep_id=dep.id; +----+------------+--------------+| ID | Name       | depart_name  |+----+------------+--------------+|  1 | Dava       | technology         | |  5 | Five Baby       | technology         | |  2 | Second Baby       | |  3 | Sanva-       Human Resources     | |  4 | Shiva-       Sales         | |  6 | Six Dolls       | NULL         |+----+------------+--------------+

Left Connection Demo

4, the right connection of the outside Link: priority to 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 EMP Right join DEP on emp.dep_id=dep.id; +------+-----------+--------------+| ID   | name      | depart_name  |+------+-----------+--------------+|    1 | Dava       | technology         | |    2 | Second Baby       | |    3 | Sanva-       Human Resources     | |    4 | Shiva-       Sales         | |    5 | Five Baby       | technology         | | NULL | NULL       | operation         |+------+-----------+--------------+

Right Connect demo

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 * fromEmp left join dep on emp.dep_id =Dep.idunionselect* fromEMP Right join dep on emp.dep_id =dep.id;#View Results+------+------------+--------+------+--------+------+--------------+| ID | name | sex | Age | dep_id | ID |    Name |+------+------------+--------+------+--------+------+--------------+| 1 | Dava |   Male |    18 |  200 | 200 |    Technology | | 5 | Five Dolls |   Male |    18 |  200 | 200 |    Technology | | 2 | Two Dolls |   Female |    48 |  201 | 201 |    Human Resources | | 3 | Three Dolls |   Male |    38 |  201 | 201 |    Human Resources | | 4 | Four Dolls |   Female |    28 |  202 | 202 |    Sales | | 6 | Six Dolls |   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

Full External demoThree, meet the conditions of connection query

# Example 1: Querying the EMP and DEP tables in an internal connection, and the Age field value in the EMP table must be greater than 25, identifying employees older than 25 and the department where the employee is located  from emp INNER JOIN DEP     = dep.id    >; # Example 2: Querying the EMP and DEP tables in an internal connection and displaying them in ascending order of the age field  from EMP,DEP     = dep.id    and age > ORDER by age    ASC; 

Concrete Connection DemoFour, 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 keywords such as: In, not in, any, all, EXISTS, and not EXISTS

#4: You can also include comparison operators: =,! =, >, <, and so on

1. Sub-query with in keyword

#查询平均年龄在25岁以上的部门名select Id,name from DEP    where ID in         (select dep_id from EMP Group by DEP_ID, have avg (age) > #查看技术部员工姓名select name from    the EMP where dep_id in         (the Select ID from dept where name= ' technology '); #查看不足1人的部门名 (subqueries get someone's department Door ID)
Select name from DEP where ID not in (SELECT DISTINCT dep_id from EMP);

2 Sub-query with comparison operator

#比较运算符: =,! =, >, >=, <, <=, <> #查询大于所有人平均年龄的员工名与年龄mysql > select name,age from emp where Age > (select AVG (age) from EMP) +---------+------+| name | Age |+---------+------+| Dava | 48 | | Three Dolls | |+---------+------+2 rows in Set (0.00 sec) #查询大于部门内平均年龄的员工名, age select T1.name,t1.age from emp t1inner join (SELECT Dep_i D,avg (age) Avg_age from EMP Group by dep_id) T2on t1.dep_id = T2.dep_idwhere t1.age > t2.avg_age;

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

#dept表中存在dept_id =203,turemysql> SELECT * from emp     , where exists         , select ID from DEP where ID=200) +----+------------+--------+------+--------+| ID | Name       | sex | Age  | dep_id |+----+------------+--------+------+--------+|  1 | Dava       | male   |   |    | |  2 | Two Dolls       | female |   |    201 | |  3 | Sanva       | male   |   |    201 | |  4 | Shiva       | female |   |    202 | |  5 | Five Dolls       | male   |   |    | |  6 | Six Doll       | female |   |    204 |+----+------------+--------+------+--------+ #dep表中存在dept_id =205,falsemysql> select * from emp     where exists         (select ID from DEP where id=204); Empty Set (0.00 sec)

mysql-operation and usage of multi-table joins