旗下导航:搜·么
当前位置:网站首页 > MySQL教程 > 正文

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

作者:搜教程发布时间:2019-11-27分类:MySQL教程浏览:22评论:0


导读:本篇文章给人人带来的内容是引见MySQL怎样完成多表查询?MySQL多表查询的语句。有肯定的参考价值,有须要的朋侪能够参考一下,愿望对你们有所协助。建立表#建立表c...
本篇文章给人人带来的内容是引见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网别的相干文章!

标签:MySQL多表查询


欢迎 发表评论: