数据库中多表查询中的内连接查询语句（idea实现）

，代码小白

于 2024-08-15 12:14:14 发布

阅读量1k

点赞数 28

文章标签：数据库 intellij-idea

本文链接：https://blog.csdn.net/2301_76231651/article/details/141217125

版权

多表查询：从两个或者两个以上的表中查询数据，并且消除无效数据

案例：现在有员工表和部门表，此时你要实现员工表和部门表的合并，使得每一行既有员工信息也有部门信息

部门表属性有： id (部门id),name(部门名) ,create_time(部门表创建时间)，update_time （部门更新时间）

员工表属性有：

id（员工id）, username(用户名), password（密码）, name（员工姓名）, gender（员工性别）, image（员工照片）, job（员工职位）, entrydate（员工入职时间）,dept_id（员工所属的部门id ）, create_time(员工信息的注册时间), update_time（员工信息的修改时间）

两个表的共有属性是部门id,通过共有属性部门id将两个表连接实现将两张表合成一张表展示出俩

1.右键db04中new->Query console新建查询控制台

2.将以下建表和插入数据的代码粘贴到查询控制台

-- 部门管理
create table tb_dept(
                        id int unsigned primary key auto_increment comment '主键ID',
                        name varchar(10) not null unique comment '部门名称',
                        create_time datetime not null comment '创建时间',
                        update_time datetime not null comment '修改时间'
) comment '部门表';

insert into tb_dept (id, name, create_time, update_time) values(1,'学工部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()), (4,'就业部',now(),now()),(5,'人事部',now(),now());



-- 员工管理
create table tb_emp (
                        id int unsigned primary key auto_increment comment 'ID',
                        username varchar(20) not null unique comment '用户名',
                        password varchar(32) default '123456' comment '密码',
                        name varchar(10) not null comment '姓名',
                        gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
                        image varchar(300) comment '图像',
                        job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
                        entrydate date comment '入职时间',
                        dept_id int unsigned comment '部门ID',
                        create_time datetime not null comment '创建时间',
                        update_time datetime not null comment '修改时间'
) comment '员工表';

INSERT INTO tb_emp
(id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES

                                                                                                    (1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
                                                                                                    (2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
                                                                                                    (3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
                                                                                                    (4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
                                                                                                    (5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
                                                                                                    (6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
                                                                                                    (7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
                                                                                                    (8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
                                                                                                    (9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
                                                                                                    (10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
                                                                                                    (11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,now(),now()),
                                                                                                    (12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,now(),now()),
                                                                                                    (13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,now(),now()),
                                                                                                    (14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
                                                                                                    (15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
                                                                                                    (16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2007-01-01',2,now(),now()),
                                                                                                    (17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());

3. 然后Ctrl+A选中代码，点击左上角的绿色三角运行

4.运行完成后表就建立好了(员工表有17个员工数据，部门表有5个部门的数据)

5.查询两张表链接之后的结果（一开始我是这样想的但是不太对）：

select *
from tb_dept,tb_emp
;

调用以上代码之后你会发现：咦？为什么查询结果是85行呢？不要慌张，这段代码相当于笛卡尔积运算结果（笛卡尔积就是将集合A和集合B 的每一个元素都进行组合，部门表有5个元素，emp表有17个元素，5*17刚好等于85，哦，这就对了，但是我们要实现的是一个员工只匹配一个部门呀），如下图所展示，每个员工都匹配了5个员工。

所以我们就要用多表查询中的内连接查询，在笛卡尔积运算之后去除无效数据（格式如下）通过连接公共字段来实现去除无效数据（员工表和部门表的公共字段是部门 id，但是两张表里面的部门id该怎么连接呢？条件里面放连接）

格式1：（隐式内连接）

select 字段列表
from 表1,表2（这里表的顺序就是将来显示字段的顺序）
where 条件
;

OK，我们通过表名1.字段名1=表名2.字段名2来连接，选中以下代码运行，

select *
from tb_emp,tb_dept
where tb_emp.dept_id=tb_dept.id;

咦，你又发现虽然员工和部门一一对应了，但是为什么少了一个员工呢，不是17个吗？

这是因为呀我们使用的是内连接查询，内连接查询查询的是两个集合的交集，员工表中某些人的部门id 为null（id为空，要进行连接必须两个表中都能一一对应起来但是为空了这怎么对应呢，对应不了，那就没办法显示）

内连接查询格式2：（显式内连接）

和格式1 相比相当于把where改成了on, 把表1和表2中间的，改成了on

select 字段列表
from 表1【inner】 join 表2
on 连接条件（消除无效笛卡尔积）;

中括号内的inner可以省略

ok:根据上述所学尝试两个练习：

练习1：# A.查询员工的姓名,及所属部门名称（隐式内连接实现）

直接把上面的* 改成员工表.员工姓名，部门表.部门名称（因为员工表的name 和部门表的name 他都叫name 所以要在前面补上他来自哪个表）表名.字段名

select tb_emp.name,tb_dept.name
from tb_dept,tb_emp
where tb_dept.id=tb_emp.dept_id;

运行结果如下：

练习2：# B.查询员工的姓名，以及所属员工的部门名称（显示内连接）

隐式变成显式

，把表名之间的,改成 jion ,把where改成 on 其他的都不用动

代码如下：

select tb_emp.name,tb_dept.name
from tb_emp join tb_dept
on tb_dept.id=tb_emp.dept_id
;

选中代码运行之后结果如下和上面也是一模一样哈

但是这样写着好像还是有点复杂，如果多个字段的连接查询，那表名那么多，写着有点麻烦

简化一下：我们给表起个别名，tb_emp+空格 +别名，tb_dept +空格+别名

然后再把上面表名相同的都该成对应的别名就好了

select e.name,d.name
from tb_emp e,tb_dept d
where d.id=e.dept_id;

运行结果如下：

OK，加油了兄弟！！！