目录

1. 多表关系简介1.1 一对多1.2 多对多1.3 一对一 2. 多表查询与笛卡尔积2.1 多表查询与数据准备2.2 笛卡尔积简介 3. 内连接查询3.1 内连接的定义3.2 隐式内连接3.3 显式内链接 4. 外连接查询4.1 外连接介绍4.2 左连接4.3 右连接4.4 总结 5. 子查询5.1 子查询简介5.1.1 简介5.1.2 子查询常见分类 5.2 带From关键字的子查询5.3 带IN关键词的子查询5.4 带比较运算符的子查询5.5 with…as

前言：

本文为在霍格沃兹测试开发学社中学习到的一些技术写出来分享给大家，希望有志同道合的小伙伴可以一起交流技术，一起进步~ 😘

所有SQL语法中符号含义：

{ }：必选项；[ ]：可选项；|：或，多选一

1. 多表关系简介

1.1 一对多

定义： 主表的一条记录可以对应从表的多条记录例子： 部门表，员工表建表原则：在一对多关系中，多的表定位从表，设置外键指向主表

1.2 多对多

定义：主表的多条记录可以对应从表的多条记录例子：商品信息表，客户表，订单表建表原则：需要创建第三张表作为中间表，中间表需要包含两张表的主键。

1.3 一对一

定义：从表的一条记录对应主表的一条记录例子：员工信息表与身份证表,联系方式建表原则： 这种对应关系的数据，通常放在单表里

2. 多表查询与笛卡尔积

2.1 多表查询与数据准备

定义： 通过查询多张表格获取数据，至少涉及两张表数据准备： 创建部门表，插入三条数据创建员工信息表添加外键约束，允许级联删除，并向三个部门插入对应的员工信息

创建部门信息表和员工信息表并对员工信息表添加级联删除的外键约束：

-- 创建部门表，插入三条数据CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(20), dept_manager VARCHAR(20), dept_location VARCHAR(20) ); INSERT INTO dept VALUES(1,'研发部','张无忌','北京');INSERT INTO dept VALUES(2,'运营部','赵敏','深圳');INSERT INTO dept VALUES(3,'销售部','周芷若','成都');-- 创建员工信息表添加外键约束，允许级联删除，并向三个部门插入对应的员工信息CREATE TABLE emp_part( emp_id INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), age INT , gender VARCHAR(10),dept_id INT,salary INT,-- 添加外键约束 CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)-- 设置允许级联删除 ON DELETE CASCADE);--插入数据INSERT INTO emp_part VALUES(1,'令狐冲',25,'男','1',20000);INSERT INTO emp_part VALUES(2,'任盈盈',23,'女','1',15000); INSERT INTO emp_part VALUES(3,'岳不群',45,'男','1',40000);INSERT INTO emp_part VALUES(4,'任我行',40,'男','1',30000); INSERT INTO emp_part VALUES(5,'岳灵珊',21,'女','1',10000);INSERT INTO emp_part VALUES(6,'赵灵儿',21,'女','2',7000); INSERT INTO emp_part VALUES(7,'林月如',22,'女','2',10000); INSERT INTO emp_part VALUES(8,'阿奴',20,'女','2',7000); INSERT INTO emp_part VALUES(9,'李逍遥',25,'男','2',15000); INSERT INTO emp_part VALUES(10,'景天',28,'男','2',20000);INSERT INTO emp_part VALUES(11,'邱莹莹',21,'女','3',5000);INSERT INTO emp_part VALUES(12,'关雎尔',22,'女','3',8000);INSERT INTO emp_part VALUES(13,'曲筱绡',23,'女','3',10000);INSERT INTO emp_part VALUES(14,'樊胜美',30,'女','3',10000);INSERT INTO emp_part VALUES(15,'安迪',28,'女','3',20000);--查询表数据是否正确插入SELECT * FROM dept;SELECT * FROM emp_part;

2.2 笛卡尔积简介

定义: 笛卡尔积是一个数学概念，又称直积，它是指两个集合元素所有可能有序对的集合。例子：A={a,b},B={c,d}A*B ={(a,c),(b,c),(a,d),(b,d)}语法：select 字段名称 from 表1,表2注意：笛卡尔积是多表查询中的一个中间状态，一般很少会直接使用，直接使用会产生很多无效数据，因此一般都会结合一些条件来筛选查询结果。

-- 使用笛卡尔积查询数据SELECT * FROM dept,emp_part;

查询结果：发现使用笛卡尔积查询出来的结果存在无效数据

当我们添加where条件后可过滤冗余数据：

--查询出运营部的部门信息及该部门下的员工信息。SELECT * FROM dept,emp_part WHERE dept.id=emp_part.dept_id and dept.id=2

查询结果：

3. 内连接查询

3.1 内连接的定义

内连接（INNER JOIN）：使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。匹配上显示，匹配不上不显示。例子： 比如使用外键=主键这个条件过滤掉无效数据按语法结构分为： 隐式内连接和显式内连接

3.2 隐式内连接

在笛卡尔积的的基础上，使用where条件过滤无用的数据，这种连接方式是隐式内连接.语法：select [字段名称] from 表1,表2 where [条件]

例1： 筛选出运营部的员工的id，姓名以及所在城市

--要筛选的字段：员工id，姓名，所在城市--从哪个表中筛选：员工表，部门表--连接条件：从表外键==主表主键--查询条件：运营部SELECT emp_id,ename,dept_location FROM emp_part,dept WHERE dept_id=id and dept_name="运营部";

查询结果：

3.3 显式内链接

显式内连接： 使用了关键字 inner join语法：select [字段名称] from [表1] inner join [表2] on [条件]

例2： 用显式内连接查询运营部的员工的id，姓名以及所在城市

SELECT emp_id,dept_location,ename FROM emp_part INNER JOIN dept ON dept_id=id AND dept_name="运营部"

4. 外连接查询

4.1 外连接介绍

外连接查询：查询多个表中相关联的行，有时候需要包含没有关联的行中数据，即返回查询结果集合中不仅包含符合连接条件的行，还包括左表（左连接）、右表（右连接）中的所有数据行。左外连接 , 使用 LEFT OUTER]JOIN , OUTER 可以省略右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略

示例：

4.2 左连接

左连接：以左表****为基准匹配右表的数据，右表中没有的项，显示为空。想要展示出哪个表的所有信息就把那个表当做左表。语法：SELECT [字段] FROM [左表] LEFT JOIN [右表] ON [条件]

例子：公司新成立人力资源部，还未招聘员工，请使用左连接查询方式查询出公司所有部门员工的员工号，姓名，性别以及他们所在的部门名称和城市

-- 向部门表中插入人力资源部INSERT INTO dept VALUES(4,'人力资源部','甄嬛','北京');-- 查询出需要的数据SELECT emp_id,ename,gender,dept_name,dept_location FROM dept LEFT JOIN emp_part ON dept.id=emp_part.dept_id

查询结果：

展示出左表dept中所有的数据，即时右表emp_part中没有匹配的数据。

4.3 右连接

右连接：以右表为基准匹配左表的数据，左表中没有的项，显示为空语法：SELECT [字段] FROM [左表] RIGHT JOIN [右表] ON [条件]

例子：使用右连接的方式查询出所有员工信息以及他们所在的部门名称和城市

-- 向部门表中插入人力资源部INSERT INTO empt_part(ename,age,gender,salary) VALUES('Tom',26,'male',5000);-- 查询出需要的数据SELECT emp_id,ename,age,gender,salary,dept_name,dept_location FROM dept RIGHT JOIN emp_part ON dept.id=emp_part.dept_id

查询结果：只展示了右表中的所有数据（没有显示行政部和人力资源部的部门信息），左表中没有匹配的数据不显示

4.4 总结

内连接: inner join左连接: left join右连接: right join内连接和左连接使用居多

5. 子查询

5.1 子查询简介

5.1.1 简介

定义：子查询指一个查询语句嵌套在另一个查询语句内部，在SELECT子句中先计算子查询，子查询的结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。 这个特性从MySQL 4.1开始引入。子查询作为过滤条件时需要用()包裹

5.1.2 子查询常见分类

From型子查询：将子查询的结果作为父查询的表来使用in/not in 型子查询：子查询的结果是单列多行，作为where的过滤条件where型子查询：查询结果作为过滤条件出现在比较运算符的一端

5.2 带From关键字的子查询

子查询是一张多行多列的表，将子查询作为父查询的表来嵌套查询子查询语句必须用()包裹且需要有别名

例：计算出各部门性别为男性的员工人数

select dept_name,count(emp_id) from (select dept_name, emp_id,ename,gender from dept inner join emp_partwhere id=dept_id and gender='男')b group by dept_name;

5.3 带IN关键词的子查询

将子查询作为where语句后的过滤条件，常用于子查询结果是单列多行的情况子查询语句必须用()包裹in/not in

例：查询出北京地区所有的员工信息

SELECT *FROM emp_partWHERE dept_id IN (SELECT id FROM dept WHERE dept_location = '北京')

5.4 带比较运算符的子查询

将子查询的结果作为过滤条件，放在比较运算符的一端常用于子查询结果为单个结果的情况子查询语句必须用()包裹

--查询出薪资大于公司平均薪资的员工id,姓名及薪资SELECT emp_id,ename,salary FROM emp_part WHERE salary > (SELECT AVG(salary) FROM emp_part);

5.5 with…as

如果一整句查询语句中，某个子查询的结果会被多个父查询引用，通常建议将共用的子查询用简写表示出来语法：with [表名] as (select…)

--查询出部门平均薪资大于公司平均薪资的部门名称，部门主管，所在地及部门平均薪资--不使用 with ...asselect dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join (select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b on id=dept_id and avg_salary >(select avg(avg_salary) from (select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b);--使用 with ...aswith dept_avg as (select dept_id,avg(salary) avg_salary from emp_part group by dept_id) select dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join dept_avg on id=dept_id and avg_salary > (select avg(avg_salary) from dept_avg);

文末说明：

接口测试中我们很容易混淆Session、cookie和token，你知道他们有什么区别吗？

快来跟我一起看，一篇文章让你了解三者的区别。😎

⬇⬇⬇⬇⬇⬇⬇

👍👍👍：接口测试经典面试题：Session、cookie、token有什么区别？