1 .连表查询

为什么需要连表查询?

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

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

1.1 表与表之间存在得关系

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

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

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, '-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, '-09-01', 2, 1);INSERT INTO `tb_emp` VALUES (6, '小昭', 19, '程序员鼓励师', 6600, '-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, '-06-01', 7, 3);INSERT INTO `tb_emp` VALUES (9, '丁敏君', 19, '出纳', 4000, '-06-01', 7, 3);INSERT INTO `tb_emp` VALUES (10, '赵敏', 23, '市场总监', 14000, '-06-01', 1, 2);INSERT INTO `tb_emp` VALUES (11, '鹿仗客', 56, '职员', 3750, '-06-01', 10, 2);INSERT INTO `tb_emp` VALUES (12, '鹤比翁', 19, '职员', 3750, '-06-01', 10, 2);INSERT INTO `tb_emp` VALUES (13, '方东白', 19, '职员', 3750, '-06-01', 10, 2);INSERT INTO `tb_emp` VALUES (14, '张三丰', 88, '销售总监', 14000, '-06-01', 1, 4);INSERT INTO `tb_emp` VALUES (15, '玉莲舟', 38, '销售', 4600, '-06-01', 14, 4);INSERT INTO `tb_emp` VALUES (16, '宋远桥', 40, '销售', 4600, '-06-01', 14, 4);INSERT INTO `tb_emp` VALUES (17, '陈友谅', 42, NULL, 2000, '-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>8000UNION select * from tb_emp where age>40;