北京哪家做网站优化,网站建设35类,faq wordpress,wordpress修改幻灯片引言
本篇文章承接《数据库与SQL语句》专栏#xff0c;进入DQL的重要环节#xff0c;可以说#xff0c;这一部分的内容应该占据SQL语言的大部分使用场景。
本篇的连接查询知识#xff0c;和后面的一些重要的查询知识总结#xff0c;共同构成了在工作中80%的MySQL应用场景…引言
本篇文章承接《数据库与SQL语句》专栏进入DQL的重要环节可以说这一部分的内容应该占据SQL语言的大部分使用场景。
本篇的连接查询知识和后面的一些重要的查询知识总结共同构成了在工作中80%的MySQL应用场景。应该算是基础且重要的部分。
同时我还希望能够通过更加简洁的语言总结和归纳出从需求快速定位SQL模板的口诀比如有一个非常复杂的查询需求那么如何通过有效的思考路径快速写出准确无误的SQL语句也将是查询语句知识总结的重点
一、笛卡尔积
首先多表存储的目的是为了节省存储空间避免不必要的重复数据存储于多个表中同时便于维护。因此往往会通过“相同字段”相互关联的方式设计数据库表这种关联是人为定义的并不需要MySQL来进行某种支持。
基于此在数学中有个叫做笛卡尔积的数学概念它指的是一个集合中的元素和另一个集合中的元素分别匹配组成不同的条目从而构成更大的集合。
在多表查询的时候如何完全不加任何条件只是单纯的将两表数据进行查询就会出现笛卡尔乘积现象即一张表中的所有数据去逐条匹配另一张表中的所有数据其最终结果的记录条数一定是两表的记录数的乘积。比如
SELECT * FROM student, class
上述SQL语句会将两个表的所有记录全部匹配。
二、连接查询的分类
SQL标准根据不同的年份分别推出了两套标准SQL92 和 SQL 99。
SQL921992年推出它包含的连接有 内连接等值、非等值、自连接 外连接废弃 SQL991999年推出它包含的连接有 交叉连接笛卡尔积 自然连接不推荐 内连接等值、非等值、自连接 外连接最常用左外连不常用右外连MySQL不支持全外连 各连接查询结果示意图红色部分代表查询结果 2.1 SQL92等值连接
等值连接是内连接的一种。是基于笛卡尔积实现的一种最基本的多表查询方式。如
SELECT * FROM emp e, dept d WHERE e.dept_id d.id;
其特点是先通过笛卡尔积将两表相乘然后通过筛选条件进行等值筛选。 但通过等值筛选最好指定具体的查询列表否则会将含义重复的列都查询出来如上图中的 dept_id 和 id 都代表部门 id。查询列表的字段可以不指定表名但效率低另外注意如果两表某个字段名相同必须指定表名。形式是“表名.字段名”。
2.2 SQL92非等值连接
非等值连接也是内连接的一种。同样基于笛卡尔积。如有 emp 左和 salary_grade 薪水级别右表 筛选薪水在 B 级别以上的员工
SELECT e.*,s.grade_name,s.salary 标准
FROMemp e,salary_grade s
WHERE e.salary s.salary AND s.grade_name B ; 非等值连接用于表与表之间没有明确的对应关系且通常会进行一个范围的筛选的情况。
2.3 SQL92自连接
自连接也是内连接的一种其含义是表与其自身做笛卡尔积。emp 表如下 其中 manager_id 代表上级领导的 emp_id 查询员工及其上级领导的信息
SELECT e.emp_id AS 员工id,e.emp_name AS 员工姓名,m.emp_name AS 上级姓名,m.emp_id AS 上级id,e.salary AS 员工薪水,e.dept_id AS 部门id
FROMemp e,emp m
WHERE e.manager_id m.emp_id ; 自连接通常在表中的记录本身存在级联关系的情况下使用如省市表、员工表等。
2.4 SQL92 外连接
SQL92标准的外连接目前已经基本废弃简单了解即可mysql无法执行这样的语句。
-- 左外连
SELECT * FROM emp e, dept d
WHERE e.dept_id d.id();-- 右外连
SELECT * FROM emp e, dept d
WHERE e.dept_id() d.id;
2.5 SQL99 交叉连接
交叉连接是SQL99 标准下的笛卡尔积实现采用 CROSS JOIN 关键字
SELECT * FROM emp e CROSS JOIN dept d;
等价于
SELECT * FROM emp e, dept d;
同时SQL99 的连接使用 ON 关键字进行等值筛选
SELECT *
FROMemp e CROSS JOIN dept d ON e.dept_id d.id ; 2.6 SQL99 自然连接
自然连接关键字是 NATURAL JOIN 会按照同名、同值字段自动进行等值连接。但是限制较多且经常需要配合 USING 关键字指定具体字段来使用这里忽略介绍不推荐使用。
2.7 SQL99 内连接
SQL99 对使用 INNER JOIN 来描述等值、非等值和自连接其中 INNER 可以省略。连接条件必须使用 ON 来约束下面以自连接进行举例等值与非等值省略。表结构见2.3 节
SELECT e.dept_id,e.emp_name,m.emp_name,m.emp_id,e.salary,e.dept_id
FROMemp e INNER JOIN emp m ON e.manager_id m.emp_id ; 2.8 SQL99 外连接重点
SQL99 的外连接总共分为三类左外连接、右外连接、全外连接。使用关键字 OUTER JOIN 连接两表其中 OUTER 可以省略。
左外联使用 LEFT JOIN ... ON ... 来关联两表右外联使用 RIGHT JOIN ... ON ... 来关联两表全外联使用 FULL JOIN ... ON ...
来关联两表但是全外联 MySQL不支持可以通过左外连 UNION 右外连 替代。
左外连会将 JOIN 左边的表作为主表将右边的表中与主表有关联的数据查询出来没有关联关系的则不查询右外连正好相反。来看下面的两张表还是 emp (员工表) 和 dept (部门表) 左外连
SELECT *
FROMemp e LEFT JOIN dept d ON e.dept_id d.id ; emp 作为主表被全查了出来并且将 dept 中相关联记录查询出没有关联的两个部门财务、市场部并没有被查询出来。
右外连
SELECT *
FROMemp e RIGHT JOIN dept d ON e.dept_id d.id ; dept 作为主表自然也是全部查出就连没有员工的两个部门财务、市场部也一并被查询出来。
因此我们可以看出一般情况下只用 LEFT JOIN 就可以完成两种不同的效果只需要将表的前后位置调换即可。在实际开发当中 LEFT JOIN 也是要比 RIGHT JOIN 使用频率更多。
总结
1、笛卡尔积是实现连接查询的数学模型它代表两表相乘。
2、SQL92语法可以查询最简单的等值、非等值和自连接三者都被称为内连接即两表集合的交集。
3、SQL99语法全面支持了外连接可读性更强其中左外连接查询是学习重点。
4、SQL99 的连接查询必须通过 ON 关键字来指定连接条件与SQL99 的连接查询不同将连接条件从 WHERE 子句中剥离出来是SQL 99 语法的重要标志。
鸣谢
《SQL92SQL99实现多表查询》
《MySQL基础系列教程》
《Mysql实现全外部连接mysql无法使用full join的解决办法》
《MySql十二Sql92和Sql99的区别》
《sql92和sql99》
