MySQL Data operation multi-table query

One: Introduction

Theme:

Multi-Table Connection query

Qualifying Connection Query

Sub-query

Prepare table

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

View Code

Two multi-table connection query

Focus: Outer link Syntax select field List    From table 1 INNER| Left| Right JOIN table 2     = Table 2. Fields;

1, internal connection: The two tables have a corresponding relationship between the records connected to a virtual table

SELECT * FROM emp inner JOIN dep on emp.dep_id = dep.id;

#应用:
SELECT * from EMP,DEP where emp.dep_id = dep.id and Dep.name = "technology"; # do not use where to do the work of the table

SELECT * FROM emp inner JOIN dep on emp.dep_id = Dep.id
where dep.name = "Technology"
;

2, left join: On the basis of the internal connection, left no correspondence between the records
SELECT * from EMP LEFT join dep on emp.dep_id = dep.id;

3, the right connection: on the basis of the internal connection, the right to retain no correspondence between the records
SELECT * from emp right join dep on emp.dep_id = dep.id;

4, the full connection: On the basis of the internal connection, the left and right to retain no correspondence between the records
SELECT * from EMP LEFT join dep on emp.dep_id = Dep.id
Union
SELECT * from emp right join dep on emp.dep_id = dep.id;

#补充: Multiple table connections can be continuously connected to virtual tables

find the highest wage for each department select T1.  from  from== t2.ms;

Three: Meet the criteria of 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, find 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 >25     Order BY age ASC;

Four sub-query

# 1, a subquery is a query statement nested in another query statement.  #2, the query results of the inner query statement, can provide query conditions for the outer query statement.  #3, sub-query can contain: in does not EXISTS and not EXISTS and other keywords #4, can also contain comparison operators: =,! =,    >, <, etc.

1. Sub-query with in keyword

# subquery: A query statement enclosed in parentheses, as the condition of another query statement to use, called a subquery   from emp inner join DEP on emp.dep_id =dep.id where Dep.name=' technology '   from emp where dep_id = from department where name =' technology ') ;

#Check the name of the department with the average age over 25 yearsSelect Name fromDEP where IDinch(select dep_id fromEMP GROUP BY DEP_ID have AVG (age) >25);#View technical staff nameSelect Name fromEMP where dep_idinch(SELECT ID fromDEP WHERE name ='Technology');#View department names for less than 1 people (subqueries get someone's department ID)Select Name fromDEP where ID not inch(SELECT DISTINCT dep_id fromemployee);#View the newest employee in each departmentSelect T1.id,t1.name,t1.post,t1.hire_date,t2.max_date fromEMP as T1 INNER join (select Post, Max (hire_date) as Max_date fromEMP GROUP by post) as T2on T1.post=T2.postwhere t1.hire_date=t2.max_date;

2 Sub-query with comparison operator

exists keyword indicates existence. When using 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 returned to false, the outer query statement is not queried.

Judging the existence of dep_id=203 in the Department table, Trueselect* fromEmployeewhere 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 tableSELECT * fromEmployee where exists (select ID fromdepartment where id=204);

MySQL Data operation multi-table query