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 会定义自己的事务操作,不一定与这里一致。
相关内容
如果你觉得这篇文章对你有所帮助,欢迎评论、分享、打赏~
赞赏