SQL 基础笔记(一)
本笔记整理自《SQL 基础教程》、《MySQL 必知必会》和网上资料。个人笔记不保证正确。
一、基础
SQL,即结构化查询语言,是为访问与操作关系数据库中的数据而设计的语言。
- 关系数据库以行(row)为单位读写数据
- SQL 根据功能的不同,可分为三类(其中DML用得最多,增删查改嘛)
- DDL(Data Definition Language, 数据定义语言): CREATE/DROP/ALTER
- DML(Data Manipulation Language, 数据操作语言): SELECT/INSERT/UPDATE/DELETE
- DCL(Data Control Language, 数据控制语言): COMMIT/ROLLBACK/GRANT/REVOKE
- SQL 语句要以分号结尾。换行在 SQL 中不表示结束,而等同于空格。
- SQL 不区分**关键字(Keyword)**的大小写,但是描述符就不一定了。
- 这里有个坑:MySQL 中,数据库和表其实就是数据目录下的目录和文件,因而,操作系统的敏感性决定数据库名和表名 是否大小写敏感。这就意味着数据库名和表名在 Windows
中是大小写不敏感的,而在大多数类型的 Unix/Linux 系统中是大小写敏感的。(注意仅指数据库名和表名)可通过修改配置文件的
lower_case_table_names
属性来统一这一行为。 - 而字段名、字段内容都是内部数据,是操作系统无关的。它们的大小写敏感性,由
MySQL 的的校对(COLLATE)规则来控制。该规则体现在 MySQL 的 校对字符集(COLLATION)的后缀上:比如 utf8字符集,
utf8_general_ci
表示不区分大小写,这个是 utf8 字符集默认的校对规则;utf8_general_cs
表示区分大小写,utf8_bin
表示二进制比较,同样也区分大小写 。
- 这里有个坑:MySQL 中,数据库和表其实就是数据目录下的目录和文件,因而,操作系统的敏感性决定数据库名和表名 是否大小写敏感。这就意味着数据库名和表名在 Windows
中是大小写不敏感的,而在大多数类型的 Unix/Linux 系统中是大小写敏感的。(注意仅指数据库名和表名)可通过修改配置文件的
- SQL 中的字符串和日期需要用单引号引用起来,日期有特定格式
年-月-日
修改字符集:set names <字符集名>
记住在 MySQL 中,utf-8mb4 才是完全的 utf-8字符集。
二、DDL
1. 数据库的创建和删除
- 创建数据库
|
|
|
|
2. 创建表:
关系表的设计
关系表的设计,要确保把信息分解成多个表,一类信息一个表,各表通过某些常用的,基本不会改变的值(即关系表设计中的关系,也常称为外键)互相关联。尽量不要有冗余数据。
语句:
|
|
举例:
|
|
后面的是设置引擎和默认字符集。工作上,表的设计一定要深思熟虑,因为改起来很困难。
字段类型(MySQL)
有四类数据类型:字符串、数字、日期、二进制。它们又根据数据长度的区别,下分为多个类型。
- 字符串:
- 数字
- 日期
- 二进制
约束
SQL 约束是除了数据类型之外,对列中数据追加的限定条件。
- 类型约束:
NOT NULL
、AUTO_INCREMENT
、UNSIGNED
(这个只 MySQL 支持) - 默认值:
DEFAULT
,举例<列名3> VARCHAR(32) NOT NULL DEFAULT "los angeles"
- 表约束:
PRIMARY KEY
主键约束(主键默认 UNIQUE 且 NOT NULL)
此外还有 FOREIGN KEY
和 CHECK
两个约束语句,
在进阶笔记中介绍。
P.S. 字段约束也可以写成表约束(比如主键约束),而反过来很可能不行。
3. 删除表和更新表定义
- 删除表(危险操作)
- 删除整个表:
|
|
- 只清空表内数据,但留下表:
|
|
- 更新表定义(麻烦的操作)所以所创建表前要仔细想好格式了,更新表定义是不得已才能为之。添加列定义:
|
|
删除列定义:
|
|
三、DML
万恶之源 NULL
1. 查询(重点)
- 基本语句:
|
|
- 可用
DISTINCT
修饰列名,使查询结果无重。例:SELECT DISTINCT <列名> FROM <表名>
- 过滤条件可使用比较运算(<>、=等)和逻辑运算(AND OR NOT).
- 过滤条件中,比较运算会永远忽略 NULL 值,如果需要对 NULL 值做操作,需要使用 IS NULL 或 IS NOT NULL(说忽略也许不太准确,NULL 既不为真也不为假,反正少用 NULL。。)
- 包含NULL的四则运算,得到的结果总为NULL
2. 聚合与排序(重点)
聚合函数
即对列进行统计分析的函数,主要有五个:
- COUNT:计算列的行数。(只有
COUNT(*)
会计算NULL行) - SUM:求该列之和。
- AVG:求该列的平均值。
- MAX/MIN:求该列的 最大/最小 值
NOTE:
- 聚合函数计算时会排除所有NULL行。只有COUNT(*)例外,NULL行也会被它计数。
- MAX/MIN 几乎适用于所有数据类型的列(对非数值型,以其二进制值来排序),而SUM/AVG只能用于数值类型的列。
- 聚合函数操作的列,也能用DISTINCT修饰。例:
SELECT COUNT(DISTINCT <列名>) FROM <表名>
- 聚合函数只能用于SELECT子句和还没讲的HAVING子句(以及 ORDER BY 子句)中。
分组(GROUP BY)
分组以聚合键为分类标准,将数据分为多个逻辑组,从而能分别对每个组进行聚合运算。(分组是为了分类聚合)
- 若出现了 GROUP BY 子句,查询结果一定是每一组一行。
- GROUP BY 会将 NULL 作为一组特定数据,显示为空。
聚合对SELECT子句的限制
首先要理解的是:
- 聚合函数的操作对象为某一列,而产生的结果只有一个值。
- GROUP BY 的操作对象为一列或者多列,产生的结果呢,是每一组一个值。
因此为了避免歧义,只要使用了聚合函数或 GROUP BY 二者之一,SELECT 子句就只能包含:
- 常数
- 其他聚合函数(该聚合函数的操作对象可以为其他列)
- 如果使用了 GROUP BY 子句,还能包括该子句所指定的列名。(也就是聚合键)但是绝不能包含其他的列名,因为这会有歧义。
此外,还有一个问题是由 SQL 的执行顺序引起的。应该能很容易猜到,SELECT 语句的执行顺序和书写顺序是不一致的。查询应该是从表开始,所以 FROM 语句一定先执行。然后应该要过滤(WHERE), 再是分组(GROUP BY),最后才是 SELECT 语句。(就已经学到的子句而言,顺序是这样)因此按理说,SELECT 语句 定义的别名,是不能在 GROUP BY 里使用的。(也有些DB支持该用法,但不通用)
对聚合结果进行过滤(HAVING)
从刚刚说过的SQL执行顺序可见,WHERE要比GROUP BY先执行,因此如果想过滤分组后的结果,不能用它。而应该使用 HAVING 子句。HAVING 子句和 WHERE 子句都是用来过滤的,但是执行顺序的不同也就决定了它们的用途不同。
NOTE:
- 有时候,会发现某个过滤条件,不论是先执行(就是写在WHERE子句中)还是后执行(写在HAVING 中)都没问题,这时候应该将它写在WHERE子句中,这样GROUP BY操作的数据会更少,处理更快。
- HAVING 子句的元素,也存在和 SELECT 子句同样的限制。不能使用聚合键以外的列名。
排序(ORDER BY)
ORDER BY 子句在 SELECT 子句之后执行,因此它能使用 SELECT 子句中定义的别名。(而 GROUP BY 之前已经说过不能用别名了)格式:
|
|
- 多排序键/列:指定多排序键时的排序规则为:优先使用左侧的列,如果该列存在相同值,再接着参考右侧的键,依此类推。(如果左侧键值不同,右侧的键就不会被使用了)
- NULL 值的顺序:排序键中出现了 NULL 值时,这类值会在结果的开头或结尾汇总,究竟是排在开头还是结尾,并没有特殊规定。
- ORDER BY 子句只影响结果的先后顺序,因此排序键可以是结果集以外的东西,比如其他的列,或者使用了 GROUP BY 时,还能用聚合函数。
3. 数据的增、删、改
插入(INSERT INTO) 也算用的多了
语法:
|
|
或者也可以使用 SELECT 语句来替代 VALUES 子句,达到将 SELECT 到的结果集插入某表的效果。(但是不要用ORDER BY,因为结果是集,没有顺序,排序是徒劳无功的)插入时主键不能重复,否则会报错。(因此如果需要包含重复数据,一般都会定义一个自增的id字段)
删除(DELETE)
清空表(危险操作,而且效率不如 TRUNCATE):
|
|
条件删除:
|
|
因此使用DELETE时,一定要记得带WHERE,不然就好玩了。。
更新(UPDATE)
|
|
同 DELETE 一样,不带 WHERE 子句的 UPDATE 是很危险的。
四、DCL - 事务处理(MySQL)
事务是一系列不可分割的数据库操作,也就是说,这一系列操作要么全部执行,要么全部不执行。如果执行过程中发生了问题(检查执行状态),可以通过执行 ROLLBACK 回滚到该事务执行前的状态。 (注意并不会自动回滚)
|
|
START TRANSACTION
: 标识事务的开始COMMIT
:提交事务。一旦提交,所执行过的操作就已成定论,恢复不了了。ROLLBACK
:事务回滚,只能回滚未 COMMIT 的 DML 操作!也就是说只能用在START TRANSACTION
和COMMIT
之间,并且只能回滚INSERT/UPDATE/DELETE
。(回滚 SELECT 没啥意义)SAVEPOINT <保留点>
和ROLLBACK TO <保留点>
:同样只能用在START TRANSACTION
和COMMIT
之间,其优势在于,ROLLBACK TO
可以指定回滚到某特定保留点,更灵活,而ROLLBACK
只能回滚到事务开始前。
需要注意的有:
COMMIT
和ROLLBACK
语句也是事务的结束,因此如果执行了ROLLBACK
,那它与COMMIT
之间的内容会被跳过。(在这一点上,它相当于大多数 PL 的 return)- 如果事务执行出现问题,问题行后面的所有语句都不会被执行!包括
COMMIT
和ROLLBACK
! 如果想用纯 SQL 实现事务原子性,必须使用存储过程检查执行状态!举例如下:
|
|
或者在 PL 中通过异常处理执行 ROLLBACK
。(事务虽然中止了,但并未结束!所以仍然可以ROLLBACK
或者 COMMIT
)
数据何时被提交到数据库
- 显式提交:在事务中使用
COMMIT
提交数据操作被称为显式提交 - 隐式提交:非 DML 操作会被立即提交,也就是说这些语句本身就隐含了提交语义
- 自动提交: 如果
AUTOCOMMIT
被设置为ON
,当前 session 中的 DML 语句会在执行后被自动提交(START TRANSACTION
内部的 DML 除外,在它内部必须显式COMMIT
)
所有的 DML 语句都是要显式提交的,MySQL session 的 AUTOCOMMIT
默认为 ON
,所以 DML 会被自动提交。
P.S. 许多语言的数据库 API 会定义自己的事务操作,不一定与这里一致。
相关内容
如果你觉得这篇文章对你有所帮助,欢迎评论、分享、打赏~
赞赏