Shulou(Shulou.com)06/01 Report--

The following together to understand the MySQL implementation of multi-table query statement introduction, I believe we will certainly benefit a lot after reading, the text is not more refined, I hope MySQL implementation of multi-table query statement introduction This short content is what you want.

create tables

#Create table department (id int,name varchar(20));create table employee1 (id int primary key auto_increment,name varchar(20),sex enum ('male',' female') not null default 'male', age int,dep_id int);#insert data into department values (200,'technology'),(201,'Human resources'),(202,'Sales'),(203,'Operation'); insert into employee1 (name,sex,age,dep_id) values ('egon','male',18,200),('alex','female',48,201),('tom','male',38,201),('yuanhao','female',28,202),('lidawei',' male', 18,200),('jinkezhou',' female', 18,204);#View table mysql> select * from employee1;+---+-----------| id | name | sex | age | dep_id |+----+-----------+--------+------+--------+| 1 | egon | male | 18 | 200 || 2 | alex | female | 48 | 201 || 3 | tom | male | 38 | 201 || 4 | yuanhao | female | 28 | 202 || 5 | lidawei | male | 18 | 200 || 6 | jinkezhou | female | 18 | 204 |+----+-----------+--------+------+--------+6 rows in set (0.00 sec)mysql> select * from department;+------+--------------+| id | name |+------+--------------+| 200 |technology || 201 |human resources || 202 |sales || 203 |operation |+------+--------------+4 rows in set (0.00 sec)

Multi-table join query

cross-connect

Cross-connect: None of the matching conditions apply. generating Cartesian product

mysql> select * from employee1 ,department;

inner connection

Inner join: finding the common part of two tables is equivalent to using conditions to filter out the correct result from the Cartesian product results. (Only connect matching rows)

#Find the common part of the two tables, which is equivalent to using the condition to filter out the correct result from the Cartesian product result #department does not have the department 204, so the employee information about 204 in the employee table does not match mysql> select * from employee1,department where employee1.department_id =department.id;#The above where can be expressed by the following inner join, it is recommended to use the following method mysql> select * from employee1 inner join department on employee1.department_id =department.id;#can also be expressed like this ha mysql> select employee1.id employee1.name, employee1.age, employee1.sex department.name from employee1,department where employee1.department_id =department.id;

Left link left

Prioritize all records in the left table.

#Left link: On the basis of obtaining the common part of the two tables according to the condition of on, keep the record of the left table mysql> select * from employee1 left join department on department.id = employee1.department_id;mysql> select * from department left join employee1 on department.id = employee1.department_id;

Right link right

Prioritize all records in the right table.

#Right link: On the basis of obtaining the common part of the two tables according to the condition of on, keep the record of the right table mysql> select * from employee1 right join department on department.id = employee1.department_id;mysql> select * from department right join employee1 on department.id = employee1.department_id;

All Joins

mysql> select * from department full join employee1;

Multi-table query matching conditions

Example 1: Query the employee and department tables by inner join, and the age field value in the employee table must be greater than 25.

That is, find employees older than 25 in all departments of the company

mysql> select * from employee1 inner join department on employee1.dep_id=department.id and age>25;

Example 2: Query the employee and department tables as inner joins and display them in ascending order of age field

mysql> select * from employee1 inner join department on employee1.dep_id=department.id and age>25 and age>25 order by age asc;

subquery

#1: A subquery is a query statement nested within another query statement.# 2: Query result of inner query statement, query condition can be provided for outer query statement.# 3: Subqueries can contain keywords such as IN, NOT IN, ANY, ALL, EXISTS, and NOT EXISTS #4: Comparison operators can also be included: =,!=,> , select name from department where id in ( select dep_id from employee1 group by dep_id having avg(age) > 25 );#View technical department employee name mysql> select name from employee1 where dep_id = (select id from department where name ='technology ');#View department name of less than 2 people mysql> select name from department where id in (select dep_id from employee1 group by dep_id having count(id)

select * from department where id not in (select distinct dep_id from employee1);

After reading MySQL implementation of multi-table query statement introduction this article, many readers will definitely want to know more related content, if you need to get more industry information, you can pay attention to our industry information column.

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.