MySQL怎样完成多表查询？MySQL多表查询的语句【MySQL教程】,MySQL,多表查询

本篇文章给人人带来的内容是引见MySQL怎样完成多表查询？MySQL多表查询的语句。有肯定的参考价值，有须要的朋侪能够参考一下，愿望对你们有所协助。

建立表

# 建立表
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 into department values(200,'手艺'),(201,'人力资源'),(202,'贩卖'),(203,'运营');

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);

# 检察表
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 | 手艺       |
|  201 | 人力资源   |
|  202 | 贩卖       |
|  203 | 运营       |
+------+--------------+
4 rows in set (0.00 sec)

多表衔接查询

交织衔接

交织衔接：不实用任何婚配前提。生成笛卡尔积

mysql> select * from employee1 ,department;

内衔接

内衔接：找两张表共有的部份，相当于应用前提从笛卡尔积效果中挑选出了准确的效果。（只衔接婚配的行）

# 找两张表共有的部份，相当于应用前提从笛卡尔积效果中挑选出了准确的效果
#department没有204这个部门，因此employee表中关于204这条员工信息没有婚配出来
mysql> select * from employee1,department where employee1.dep_id=department.id;

#上面用where示意的能够用下面的内衔接示意，发起运用下面的那种要领
mysql> select * from employee1 inner join department on employee1.dep_id=department.id;

# 也能够如许示意哈
mysql> select employee1.id,employee1.name,employee1.age,employee1.sex,department.name from employee1,department where employee1.dep_id=department.id;

左衔接left

优先显现左表悉数纪录。

#左链接：在根据on的前提取到两张表配合部份的基础上，保存左表的纪录
mysql> select * from employee1 left join department on department.id=employee1.dep_id;

mysql> select * from department left join  employee1 on department.id=employee1.dep_id;

右衔接right

优先显现右表悉数纪录。

#右链接：在根据on的前提取到两张表配合部份的基础上，保存右表的纪录
mysql> select * from employee1 right join department on department.id=employee1.dep_id;
mysql> select * from department right join employee1 on department.id=employee1.dep_id;

悉数衔接join

mysql> select * from department full join employee1;

相符前提多表查询

示例1：之内衔接的体式格局查询employee和department表，而且employee表中的age字段值必需大于25,
即找出公司一切部门中岁数大于25岁的员工

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

示例2：之内衔接的体式格局查询employee和department表，而且以age字段的升序体式格局显现

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

子查询

#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询效果，能够为外层查询语句供应查询前提。
#3：子查询中能够包括：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还能够包括比较运算符：= 、 !=、> 、<等

示例：

# 查询均匀岁数在25岁以上的部门名
mysql> select name from department where id in ( select dep_id from employee1 group by dep_id having avg(age) > 25 );

# 检察手艺部员工姓名
mysql> select name from employee1 where dep_id = (select id from department where name='手艺');

# 检察小于2人的部门名
mysql> select name from department where id in (select dep_id from employee1 group by dep_id having count(id) < 2) union select name from department where id not in (select distinct dep_id from employee1);
# 提取空部门                              
#有人的部门
mysql> select * from department where id not in (select distinct dep_id from employee1);

以上就是MySQL怎样完成多表查询？MySQL多表查询的语句的细致内容，更多请关注ki4网别的相干文章！