SQL 必知必会 | 接近风的地方

type

status

date

slug

summary

一. 前言

在数据库中，SQL 语句分为四大类

DML(Data Manipulation Language)：select, insert, update, delete

DDL(Data Definition Language)：create, alter, drop, truncate

DCL(Data Control Language)：grant, revoke

TCL(Transaction Control Language)：commit, rollback, savepoint

SQL的三种注释方法

数据库的创建与使用

二. 创建表

三. 修改表

添加列

删除列

删除表

重命名表

四. 插入数据

插入完整的行

插入检索出的数据

复制一个表的内容到新表

五. 更新和删除数据

更新数据

删除数据

清空表

更新和删除数据一定要带 WHERE 语句，否则容易破坏表中数据

六. 检索数据

SELECT ... FROM

DISTINCT 相同值只会出现一次，多列情况时，所有列的值都相同才算相同

LIMIT 限制检索行数，第一个参数+1为起始行，第二个参数为检索行数如果存在 ORDER BY ，LIMIT必须放其后面

七. 排序检索到的数据

ASC：升序（默认）；DESC：降序

八. 过滤数据

BETWEEN...AND... 指定一个由低到高的范围

IS NULL

AND 和 OR 用于连接多个过滤条件。优先处理 AND，也可以使用 () 来决定优先级

IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值

NOT 操作符用于否定一个条件，有时候对复杂子句取反可以使过滤条件变简单

九. 使用通配符过滤

% 匹配多个字符；_ 匹配单个字符；正则表达式过滤条件用 LIKE 连接

通配符会降低检索效率，不能滥用通配符

十. 计算字段

计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式

连接字段使用 CONCAT() ，许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格

十一. 函数

聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和
文本处理	ㅤ
函数	说明
--	--
LEFT()	左边的字符
RIGHT()	右边的字符
LOWER()	转换为小写字符
UPPER()	转换为大写字符
LTRIM()	去除左边的空格
RTRIM()	去除右边的空格
LENGTH()	长度
SOUNDEX()	转换为语音值
日期和时间处理	ㅤ

日期格式：YYYY-MM-DD

时间格式：HH:MM:SS

函数	说明
ADDDATE()	增加一个日期（天、周等）
ADDTIME()	增加一个时间（时、分等）
CURDATE()	返回当前日期
CURTIME()	返回当前时间
DATE()	返回日期时间的日期部分
DATADIFF()	计算两个日期之差
DATE_ADD()	高度灵活的日期运算函数
DATE_FORMAT()	返回一个格式化的日期或时间串
DAY()	返回一个日期的天数部分
DAYOFWEEK()	对于一个日期，返回对应的星期几
HOUR()	返回一个时间的小时部分
MINUTE()	返回一个时间的分钟部分
MONTH()	返回一个时间的月份部分
NOW()	返回当前日期和时间
SECOND()	返回一个时间的秒部分
TIME()	返回一个日期时间的时间部分
YEAR()	返回一个日期的年份部分

数值处理

函数	说明
SIN()	正弦
COS()	余弦
TAN()	正切
ABS()	绝对值
SQRT()	平方根
MOD()	余数
EXP()	指数
PI()	圆周率
RAND()	随机数

十二. 分组数据

GROUP BY 可以把具有相同的数据值的行放在同一组中，从而进行下一步操作：对同一分组的数据使用聚集函数进行处理，如 COUNT(*) 计算分组后各类的数量可以在分组前使用 WHERE 条件过滤数据，分组后使用 HAVING 条件过滤数据最后可以对结果进行 ORDER BY 排序

分组规定

GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前

除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出

NULL 的行会单独分为一组

大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型

十三. 子查询

子查询只能返回一个字段的数据

方式一：利用子查询过滤数据，即将子查询的结果作为 WHERE 语句的过滤条件

方式二：利用子查询创建计算字段，即将子查询的结果作为 SELECT 语句的一个计算字段

十四. 联结表

内联结（INNER JOIN）

自联结（内联结的一种）

外联结（LEFT JOIN 和 RIGHT JOIN）左外联结就是以左表为主表，保留左表没有关联的行，右外同理

MySQL不支持 FULL JOIN，所以直接使用 UNION 连接左外表和右外表就可以达到FULL JOIN效果

十五. 组合查询

使用 UNION 来组合两个查询的行，两个查询的字段和聚集函数要相同默认会去除相同行，要保留则使用 UNION ALL 只能使用一次 ORDER BY 语句，且位于语句最后

十六. 视图

视图是虚拟的表，本质是一段写好的 SQL 语句，如把一个复杂的联结表包装成视图，就可以直接对视图进行操作，操作方式与普通表一样，一般用来检索数据

创建视图

视图具有以下好处

可以方便的重用 SQL 语句，简化复杂的 SQL 操作

可以使用表的一部分而非整个表

给用户访问视图的权限，保护数据安全

更改数据表示格式

十七. 存储过程

存储过程就是为以后的使用而保存的多条 SQL 语句的集合，可以看作为一系列 SQL 操作的批处理，存储过程的优点是简单、安全、高性能

创建存储过程

调用存储过程

十八. 游标

游标在存储过程中使用，可以对一个结果集进行移动遍历。

使用游标的四个步骤：

声明游标，这个过程没有实际检索出数据；

打开游标；

取出数据；

关闭游标；

十九. 触发器

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

INSERT 触发器包含一个名为 NEW 的虚拟表。

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

二十. 事务管理

基本术语

事务（transaction）指一组 SQL 语句

回退（rollback）指撤销指定 SQL 语句的过程

提交（commit）指将未存储的 SQL 语句结果写入数据库表

保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）

MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。