深入浅出 SQL

参考文章: 图解SQL

在 SQL 中，一切皆关系。

SQL 是以关系模型设计的数据编程接口, 而关系类数据库在设计的时候, 也是以关系模型作为设计标准的.
因此大多数情况下数据库是否支持SQL是评价一个数据库是否是关系类数据库的事实标准.
那么关系模型到底是什么呢?

关系模型

关系模型（Relational model）由 E.F.Codd 博士于 1970 年提出，以集合论中的关系概念为基础；无论是现实世界中的实体对象还是它们之间的联系都使用关系表示。我们在数据库系统中看到的关系就是二维表（Table），由行（Row）和列（Column）组成。因此，也可以说关系表是由数据行构成的集合。
关系模型由数据结构、关系操作、完整性约束三部分组成。

  • 关系模型中的数据结构就是关系表，包括基础表、派生表（查询结果）和虚拟表（视图）。
  • 常用的关系操作包括增加、删除、修改和查询（CRUD），使用的就是 SQL 语言。其中查询操作最为复杂，包括选择（Selection）、投影（Projection）、并集（Union）、交集（Intersection）、差集（Exception）以及笛卡儿积（Cartesian product）等。
  • 完整性约束用于维护数据的完整性或者满足业务约束的需求，包括实体完整性（主键约束）、参照完整性（外键约束）以及用户定义的完整性（非空约束、唯一约束、检查约束和默认值）。

SQL 类型

SQL 从数据操作层面可以分为三个层面 DDL, DML, DCL

1. DML

DML 是 Data Manipulation Language (数据操纵语言). 一般指的是对数据表内的数据进行的操作. 比如常用的 INSERT、DELETE、UPDATE、SELECT 增删改查数据行的操作.

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
DELETE FROM 表名称 WHERE 列名称 = 值
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
SELECT 列名称 FROM 表名称

2. DDL

DDL 是 Data Define Language (数据定义语言).一般指的是对数据表级别的数据进行的操作.

  • 　　CREATE：创建
  • 　　 ALTER：修改表结构
  • 　　 RENAME：修改表名或列名
  • 　　 DROP：删除表中的数据和结构，删除后不能回滚
  • 　　 TRUNCATE：删除表中的数据不删除表结构，删除后不能回滚，效率比DELETE高
CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)
ALTER TABLE 表名称
ALTER COLUMN 列名称 数据类型
DROP TABLE 表名称
DROP DATABASE 数据库名称

3. DCL

DCL 是 Data Control Language (数据库控制语言). 用来设置或更改数据库用户或角色权限的语句如：

  • 　GRANT：授权

  • 　 REVOKE ：回收权限

SQL 字段讲解

SQL 是一种面向集合的编程语言，它的操作对象是集合，操作的结果也是集合。

在关系数据库中，关系、表、集合三者通常表示相同的概念。

SELECT

SELECT 操作对象是 列, SELECT 抽出数据源的列，重新组装成一个集合。
image.png

SELECT支持子查询. SELECT查询结果可以继续用作数据源进行子查询/嵌套查询

代码演示

@iframe

WHERE

WHERE 操作对象是 行, WHERE 过滤出符合条件的数据源的行，重新组装成一个集合。

image.png

ORDER BY

ORDER BY 用于对查询的结果进行排序，示意图如下：

ORDER BY

LIMIT OFFSET

LIMIT 和 OFFSET 经常一起使用, 用做分页.
LIMIT 用来限制返回集合条数, OFFSET用来过滤掉前面的条数

GROUP BY

分组（ GROUP BY）操作和 ORDER BY 比较类似, 相当于一种变形的排序.
image.png

代码演示

@iframe

UNION INTERSECT EXCEPT/MINUS

SQL 面向集合特性最明显的体现就是 UNION（并集运算）、INTERSECT（交集运算）和 EXCEPT/MINUS（差集运算）。
这些集合运算符的作用都是将两个集合并成一个集合，因此需要满足以下条件：

  • 两边的集合中字段的数量和顺序必须相同；
  • 两边的集合中对应字段的类型必须匹配或兼容。
    具体来说，UNION 和 UNION ALL 用于计算两个集合的并集，返回出现在第一个查询结果或者第二个查询结果中的数据。它们的区别在于 UNION 排除了结果中的重复数据，UNION ALL 保留了重复数据。
    下面是 UNION 操作的示意图：
    image.png

INTERSECT 操作符用于返回两个集合中的共同部分，即同时出现在第一个查询结果和第二个查询结果中的数据，并且排除了结果中的重复数据。INTERSECT 运算的示意图如下：
image.png

EXCEPT 或者 MINUS 操作符用于返回两个集合的差集，即出现在第一个查询结果中，但不在第二个查询结果中的记录，并且排除了结果中的重复数据。EXCEPT 运算符的示意图如下：
image.png

DISTINCT

DISTINCT 运算符用于消除重复数据，也就是排除集合中的重复元素。

JOIN

在 SQL 中，不仅实体对象存储在关系表中，对象之间的联系也存储在关系表中。因此，当我们想要获取这些相关的数据时，需要使用到另一个操作：连接查询（JOIN）。
常见的 SQL连接查类型包括内连接、外连接、交叉连接等。其中，外连接又可以分为左外连接、右外连接以及全外连接。
内连接（Inner Join）返回两个表中满足连接条件的数据，内连接的原理如下图所示

image.png：

左外连接（Left Outer Join）返回左表中所有的数据；对于右表，返回满足连接条件的数据；如果没有就返回空值。左外连接的原理如下图所示：
image.png

右外连接（Right Outer Join）返回右表中所有的数据；对于左表，返回满足连接条件的数据，如果没有就返回空值。右外连接与左外连接可以互换，以下两者等价：
t1 RIGHT JOIN t2
t2 LEFT JOIN t1
全外连接（Full Outer Join）等价于左外连接加上右外连接，同时返回左表和右表中所有的数据；对于两个表中不满足连接条件的数据返回空值。全外连接的原理如下图所示：

image.png

交叉连接也称为笛卡尔积（Cartesian Product）。两个表的交叉连接相当于一个表的所有行和另一个表的所有行两两组合，结果的数量为两个表的行数相乘。交叉连接的原理如下图所示：
image.png

其他类型的连接还有半连接（SEMI JOIN）、反连接（ANTI JOIN）。

评论栏