Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to achieve multi-table query operation in MySQL, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Create table department (id int,name varchar (20)); create table employee (id int primary key auto_increment,name varchar (20), sex enum ('male','female') not null default' male',age int,dep_id int); # insert data insert into department values (200Jing 'Technology'), (201Jing 'Human Resources'), (202Jing 'Sale'), (203Jing 'Operation') Insert into employee (name,sex,age,dep_id) values ('egon','male',18200), (' alex','female',48201), ('wupeiqi','male',38201), (' yuanhao','female',28202), ('nvshen','male',18200), (' xiaomage','female',18204); # View table structure and data mysql > desc department +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Id | int (11) | YES | | NULL | | name | varchar (20) | YES | | NULL | | +-+-+ 2 rows in set (0.19 sec) 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 | | +-+-- -+ 5 rows in set (0.01sec) mysql > select * from department +-+-+ | id | name | +-+-+ | 200 | Technology | | 201 | Human Resources | | 202 | sales | | 203 | Operation | +-+-+ 4 rows in set (0.02 sec) 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 | nvshen | male | 18 | 200 | 6 | xiaomage | female | 18 | 204 | +-+ 6 rows in set (0.00 sec)

Ps: looking at the two tables, it is found that there is no corresponding employee in the id=203 department in the department table in employee. It is found that the id=6 employee in the employee has no corresponding relationship in the department table.

A multi-table link query

SELECT field list

FROM Table 1 INNER | LEFT | RIGHT JOIN Table 2

ON Table 1. Field = Table 2. Field

(1) first look at the first case of cross-connection: no matching conditions are applicable. Generate Cartesian product.-- > repeat the most

Mysql > select * from employee,department +-+ | id | name | sex | age | dep_id | id | name | +- -egon | male | 18 | 200 | sales | 1 | egon | male | 18 | 200 | Operation | 2 | alex | female | 48 | 201 | Technology | 2 | alex | | female | 48 | 201,201 | Human Resources | | 2 | alex | female | 48 | 20120 | sales | 2 | alex | female | 48 | 20120 | Operation | 3 | wupeiqi | male | 38 | 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 | HR | 4 | yuanhao | female | 28 | 202 | sales | 4 | yuanhao | female | 28 | 2032 | Operation | 5 | nvshen | male | 18 | 200 | Technology | 5 | nvshen | male | 18 | 200 | 201 | HR | 5 | nvshen | male | 18 | 200 | sales | | | 5 | nvshen | male | 18 | 200 | 20 | Operation | | 6 | xiaomage | female | 18 | 20 | 20 | Technology | | 6 | xiaomage | female | 18 | 204 | 201 | Human Resources | | 6 | xiaomage | female | 18 | 204 | 202 | sales | 6 | xiaomage | female | 18 | 204 | 203 | Operation |

(2) Internal join: only the matching rows are connected, based on both parties

# finding the common part of the two tables is equivalent to using the conditions to filter out the matching results from the Cartesian product results. # department does not have a department of 204. therefore, the employee information of the employee table 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 | nvshen | 18 | male | Technology | +-+ 5 rows in set (0.00 sec) # the above sql equals mysql > select employee.id Employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id

(3) the left link of the external link: give priority to display all records in the left table.

# take the left table as standard, that is, find out all employee information, including employees without departments, of course. # the essence is: add the results that are available on the left and not on the right on the basis of the inner connection. 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 | nvshen | Technology | 2 | alex | Human Resources | | 3 | wupeiqi | Human Resources | | 4 | yuanhao | sales | | 6 | xiaomage | NULL | +-+ 6 rows in set (0.00 sec) |

(4) the right link of the external link: give priority to displaying all the records in the right table.

# based on the table on the right, that is, to find out all department information, including departments without employees # the essence is: add the results that have on the right and not on the left on the basis of the inner connection: mysql > select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id +-+ | id | depart_name | +-+ | 1 | egon | Technology | | 2 | alex | Human Resources | | 3 | wupeiqi | Human Resources | | 4 | yuanhao | sales | | 5 | nvshen | Technology | NULL | NULL | Operation | +-+ 6 rows in set (0.00 sec)

(5) full external connection: display all records of the left and right tables (understand)

# external connection: on the basis of the inner connection, add the result that the left has no right and the right has no left.

# Note: mysql does not support full external connection to full JOIN

# emphasize: mysql can use this way to indirectly achieve full external connection

Syntax: select * from employee left join department on employee.dep_id = department.id

Union all

Select * from employee right join department on employee.dep_id = department.id

Mysql > select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id +-+ | id | name | sex | age | dep_id | id | name | +- -+ | 1 | egon | male | 18 | 200 | Technology | 5 | nvshen | male | 18 | 200 | Technology | 2 | alex | female | 48 | 201 | HR | 3 | wupeiqi | male | 38 | 201 | HR | 4 | yuanhao | female | 28 | 202 | sales | 6 | | xiaomage | female | 18 | 204 | NULL | NULL | | NULL | 203 | Operation | +-+ 7 rows in set (0.01 sec) # Note the difference between union and union all: union removes the same record.

Second, join queries that meet the criteria

Query the employee and department tables in an internal join, and the value of the age field in the employee table must be greater than 25, that is, to find out the employees over the age of 25 and the department in which they work.

Select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age > 25

3. Sub-query

# 1: a subquery is to nest one query statement in another query statement.

# 2: the query result of the inner query statement can provide query conditions for the outer query statement.

# 3: subqueries can contain keywords such as IN, NOT IN, ANY, ALL, EXISTS and NOT EXISTS

# 4: can also include comparison operators: =,! =, >, 25); # View the name of the technical staff select name from employee where dep_id in (select id from department where name=' Technology'); # View the department name of less than 1 person select name from department where id not in (select dep_id from employee group by dep_id)

(2) subquery with comparison operator

# comparison operators: =,! =, >, > =, (select avg (age) from employee); +-+-+ | name | age | +-+-+ | alex | 48 | wupeiqi | 38 | +-+ # query the name and age of employees who are older than the average age in the department

Train of thought:

(1) first grouping the personnel in the staff table (employee) into groups (group by) to find out the dep_id and the average age.

(2) take the result as the temporary table, and then join the employee table and the temporary table according to the dep_id of the temporary table and the dep_id of employee as the filter condition.

(3) finally, the names and ages of employee employees whose age is higher than the average age are screened.

Mysql > select t1.namerecoveryt1.age from employee as T1 inner join (select dep_id,avg (age) as avg_age from employee group by dep_id) as T2 on t1.dep_id = t2.dep_id where t1.age > T2 avgagecompany name + | name | age | +-+-+ | alex | 48 |

(3) subquery with EXISTS keyword

The # EXISTS key word indicates that it exists. When the EXISTS keyword is used, the inner query statement does not return the record of the query. Instead, it returns a true or false value. True or False# when True is returned, the outer query statement will query When the return value is False, the outer query statement does not query the existence of dept_id=203,Turemysql > select * from employee where exists (select id from department where id=200) in the # department table +-+ | 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 | 28 | 5 | nvshen | male | 18 | 200 | 6 | xiaomage | female | 18 | 204 | +-- + # dept_id=205 exists in the department table Falsemysql > select * from employee where exists (select id from department where id=204) Empty set (0.00 sec) above is how to implement multi-table query operations in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.