1 .连表查询

为什么需要连表查询?

1.如果查询得结果在一张表中无法获取，需要关联多张表，那么这时就需要连表查询。

2.连表查询大多数都作用在外键得基础上。— 表与表之间有关联。

1.1 表与表之间存在得关系

1. 一对多: 在多得一方添加外键列

2. 多对多: 需要在创建一个中间表，该表中至少有两个外键列。

1.3 内连接:

连接演示—结果都是一样，只是语法不同。看个人习惯用哪个?

1．查询每一个员工的姓名，及关联的部门的名称〔隐式内连接实现)

2．查询每一个员工的姓名，及关联的部门的名称〔显式内连接实现)

DROP TABLE IF EXISTS `tb_emp`;
CREATE TABLE `tb_emp`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '员工年龄',
  `job` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '岗位',
  `salary` int(11) NULL DEFAULT NULL COMMENT '薪水',
  `entrydate` date NULL DEFAULT NULL COMMENT '员工入职时间',
  `managerid` int(11) NULL DEFAULT NULL COMMENT '员工领导编号',
  `dept_id` int(11) NULL DEFAULT NULL COMMENT '员工所在部门编号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `tb_emp` VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5);
INSERT INTO `tb_emp` VALUES (2, '张无忌', 20, '项目经理', 12500, '2005-12-01', 1, 1);
INSERT INTO `tb_emp` VALUES (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1);
INSERT INTO `tb_emp` VALUES (4, '韦一笑', 48, '开发', 11000, '2002-03-05', 2, 1);
INSERT INTO `tb_emp` VALUES (5, '常豫川', 43, '开发', 10500, '2004-09-01', 2, 1);
INSERT INTO `tb_emp` VALUES (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);
INSERT INTO `tb_emp` VALUES (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3);
INSERT INTO `tb_emp` VALUES (8, '周芷若', 60, '会计', 48000, '2006-06-01', 7, 3);
INSERT INTO `tb_emp` VALUES (9, '丁敏君', 19, '出纳', 4000, '2009-06-01', 7, 3);
INSERT INTO `tb_emp` VALUES (10, '赵敏', 23, '市场总监', 14000, '2009-06-01', 1, 2);
INSERT INTO `tb_emp` VALUES (11, '鹿仗客', 56, '职员', 3750, '2009-06-01', 10, 2);
INSERT INTO `tb_emp` VALUES (12, '鹤比翁', 19, '职员', 3750, '2009-06-01', 10, 2);
INSERT INTO `tb_emp` VALUES (13, '方东白', 19, '职员', 3750, '2009-06-01', 10, 2);
INSERT INTO `tb_emp` VALUES (14, '张三丰', 88, '销售总监', 14000, '2004-06-01', 1, 4);
INSERT INTO `tb_emp` VALUES (15, '玉莲舟', 38, '销售', 4600, '2009-06-01', 14, 4);
INSERT INTO `tb_emp` VALUES (16, '宋远桥', 40, '销售', 4600, '2009-06-01', 14, 4);
INSERT INTO `tb_emp` VALUES (17, '陈友谅', 42, NULL, 2000, '2022-04-25', 1, NULL);
DROP TABLE IF EXISTS `tb_dept`;
CREATE TABLE `tb_dept`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_dept
-- ----------------------------
INSERT INTO `tb_dept` VALUES (1, '研发部');
INSERT INTO `tb_dept` VALUES (2, '市场部');
INSERT INTO `tb_dept` VALUES (3, '财务部');
INSERT INTO `tb_dept` VALUES (4, '销售部');
INSERT INTO `tb_dept` VALUES (5, '总经办');

SET FOREIGN_KEY_CHECKS = 1;

-- 隐式查询 select 列名.... from 表1,表2 where 连表得条件。
-- 连表查询时，如果不使用连表条件则出现笛卡尔集。
-- 所谓笛卡尔集 就是A表中每一条记录关联B中中得每条记录
1．查询每一个员工的姓名，及关联的部门的名称〔隐式内连接实现)
select * from tb_emp,tb_dept where tb_emp.dept_id=tb_dept.id;
 -- 如果表的名字很长 可以为表起别名
select * from tb_emp e, tb_dept d where e.dept_id=d.id;
 

-- 显示连接: A表 inner join B表 on 连表条件。 
2．查询每一个员工的姓名，及关联的部门的名称〔显式内连接实现)
select * from tb_emp  inner join tb_dept on tb_emp.dept_id=tb_dept.id;
select * from tb_emp e inner join tb_dept d on e.dept_id=d.id;

-- 上面的 inner可以省略。
select * from tb_emp e  join tb_dept d on e.dept_id=d.id;

1.4 外连接

外连接演示

–1.查询emp表的所有数据, 和对应的部门信息(左外连接)

–2．查询dept表的所有数据，和对应的员工信息(右外连接)

-- 语法: select 查询列集 from A表 left join B表 on 连表条件
-- 1.查询emp表的所有数据, 和对应的部门信息(左外连接)
select * from tb_emp e left outer join tb_dept d on e.dept_id = d.id;
select * from tb_emp e left join tb_dept d on e.dept_id=d.id;

-- 2．查询dept表的所有数据，和对应的员工信息(右外连接)
select * from tb_emp e right join tb_dept d on e.dept_id=d.id;

1.5 自联查询

自己和自己相连接查询。

select * from A表 join A表 on 连表条件。

-- 1．查询员工及其所属领导的名字。你要查询的结果再一张表中，但是还不能使用单表查询得到结果。
select a.name,b.name from tb_emp a join tb_emp b on a.managerid=b.id;
 
-- 2．查询所有员工 emp及其领导的名字emp ,如果员工没有领导，也需要查询出来
select a.name,b.name from tb_emp a left join tb_emp b on a.managerid=b.id;

2. 子查询—嵌套查询

一个查询的结果 作为另一个查询的条件 或者 临时表。
-- 查询市场部的员工信息-----
-- 子查询返回的结果一列一条记录。 这个时候可以用=
select * from tb_emp where dept_id=(select id from tb_dept where name='市场部')

-- 查询市场部和研发部员工的信息。in 
 -- -- 查询市场部和研发部员工的信息。
   -- a) 查询市场部和研发部的编号
	 select id from tb_dept where name in('市场部','研发部')

   -- b) 再员工表中根据部门编号查询员工信息
	 select * from tb_emp where dept_id in (select id from tb_dept where name in('市场部','研发部'))


-- 查询在“方东白”入职之后的员工信息
select * from tb_emp where entrydate>(select entrydate from tb_emp where name='方东白')

-- -- 查询比财务部所有人工资都高的员工信息。
  a)求出财务部中最高的工资。
	select max(salary) from tb_emp e join tb_dept d on e.dept_id=d.id where d.name='财务部'
	
	b) 根据财务部最高工资查询其他员工信息
	select * from tb_emp where salary>(	select max(salary) from tb_emp e join tb_dept d on e.dept_id=d.id where d.name='财务部')

3. 组合查询

多个查询的结果 组合到一起。
sql union sql --->把这两条sql查询的结果组合到一起。如果有重复记录则合并成一条。
sql union all sql--->把这两条sql查询的结果组合到一起。如果有重复记录，不合并。 

注意: 这两条sql返回的字段必须一样。

	
	select name from tb_emp where salary>8000
	UNION 
	select * from tb_emp where age>40;