SQL学习笔记（4）

原创于 2021-10-19 11:13:38 发布 · 449 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #mysql

数据库专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍了SQL中的约束，包括非空约束、唯一性约束、主键约束和外键约束。接着讲解了存储引擎的作用、类型及选择，特别是InnoDB引擎对事务的支持。然后深入探讨了事务的概念、四大特性，以及不同隔离级别的验证。最后，阐述了索引的原理、创建与删除，以及索引失效的常见情况，强调了数据库设计的三范式及其意义。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 约束 CONSTRAINT

1.1 什么是约束

在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性。

1.2 约束包括哪些

非空约束：not null
唯一性约束：unique
主键约束：primary key
外键约束：foreign key

1.3 非空约束 not null

约束的字段不能为NULL

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) not null
);
insert into t_vip(id,name) values(1,'zhangsan');-- 成功
insert into t_vip(id,name) values(2,'lisi');-- 成功
insert into t_vip(id) values(3);-- name列不能是null，失败
ERROR 1364 (HY000): Field 'name' doesn't have a default value

1.4 唯一性约束 unique

约束的字段不能与其他行的该字段重复，但是可以为NULL

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) unique
);
insert into t_vip(id,name) values(1,'zhangsan');-- 成功
insert into t_vip(id,name) values(2,'lisi');-- 成功
insert into t_vip(id,name) values(3,'lisi');-- name字段重复，失败
ERROR 1062 (23000): Duplicate entry 'lisi' for key 'name'

insert into t_vip(id) values(4);-- 成功
insert into t_vip(id) values(5);-- unique约束可以为null，成功

多个字段联合唯一性（多个字段组合在一起是唯一的）:使用表级约束

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255),
    email varchar(255),
    unique(name,email) -- name 和 email字段联合唯一
);
insert into t_vip(id,name,email) values(1,'zhangsan','@qq.com'); -- 成功
insert into t_vip(id,name,email) values(2,'zhangsan','@163.com'); -- 成功
insert into t_vip(id,name,email) values(3,'zhangsan','@qq.com'); -- 不符合联合唯一性，失败
ERROR 1062 (23000): Duplicate entry 'zhangsan-@qq.com' for key 'name'

列级约束和表级约束：

直接添加在列后面的是列级约束，没有添加在列后面的是表级约束。

列级约束用于约束该列；表级约束用于给多个字段的联合添加约束

not null和unique同时使用

在mysql中，如果一个列同时被not null和unique约束，那么该列就成为了主键列

drop table if exists t_vip;
create table t_vip(
    id int not null unique,
    name varchar(255)
);
desc t_vip; -- 查看表结构的详细信息
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

1.5 主键约束 primary key

列级约束：单一主键（建议使用）

drop table if exists t_vip;
create table t_vip(
    id int primary key,
    name varchar(255)
);

表级约束：可用于复合主键（实际开发中不建议使用）

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255),
    primary key(id,name)
);

在一张表中主键约束只能添加一个（比如列级约束只能在一个列后面写primary key，而不能在多个列后面写primary key）

在mysql中，可以通过auto_increment自动维护一个主键值

drop table if exists t_vip;
create table t_vip(
    id int primary key auto_increment, -- 表示自增，从1开始每行增加1
    name varchar(255),
);

1.6 外键约束 foreign key

比如一个场景：设计数据库表来描述“班级和学生”

第一种方案：将班级和学生存储在一张表中

no(pk)	name	classno	classname
1	jack	100	西安一中高一一班
2	rose	100	西安一中高一一班
3	sam	101	西安一中高一二班
4	anna	101	西安一中高一二班

以上方案缺点：

数据冗余、空间浪费（比如classname列值其实有很多重复荣誉）

第二种方案：班级一张表、学生一张表

t_class班级表

classno(pk)	classname
100	西安一中高一一班
101	西安一中高一二班

t_student学生表

no(pk)	name	cno（班级编号）
1	jack	100
2	rose	100
3	sam	101
4	anna	101

存在一种可能，在t_student表中添加行时cno列值不在t_class中（比如cno为102），即该学生的班级信息不存在。针对这种问题，可以设置t_class的classno列为t_student的cno列的外键，这样给t_student表新增行时，cno列值只能是外键中的值（100或101）。

设置外键后t_class是父表，t_student是字表

删除表的顺序：先删除子表，再删除父表
创建表的顺序：先创建父表，再创建子表
删除数据顺序：先删除子表行，再删除父表行
插入数据顺序：先插入父表行，再插入子表行

语法：通过foreign key(列名) references 父表名(外键列名) 创建外键

drop table if exists t_student; -- 如果存在删除表
drop table if exists t_class;

create table t_class (
	classno int primary key,
    classname varchar(255)
);
create table t_student(
	no int primary key auto_increment,
    name varchar(255),
    cno int,
    foreign key(cno) references t_class(classno) -- 设置外键
);

insert into t_class(classno,classname) values(100,'grade 1 class 1');
insert into t_class(classno,classname) values(101,'grade 1 class 2');

insert into t_student(name,cno) values('jack',100); -- 成功
insert into t_student(name,cno) values('rose',100); -- 成功
insert into t_student(name,cno) values('sam',101); -- 成功
insert into t_student(name,cno) values('anna',101); -- 成功

insert into t_student(name,cno) values('lili',102); --由于外键中没有102值，所以失败
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`webserver`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`))

子表中的外键引用的父表中的某个字段，被引用的字段必须是主键吗？

不一定是主键，但至少有unique约束

外键可以为NULL吗？

外键值可以为NULL

2. 存储引擎

2.1 什么是存储引擎，有什么用呢？

存储引擎是mysql中特有的术语，其他数据库中不一定有。

存储引擎是一个表存储/组织数据的方式，不同存储引擎的表存储数据方式不同。

mysql中不同的表可以有不同的存储引擎

2.2 给表指定存储引擎

 CREATE TABLE `user` (
  `username` char(50) DEFAULT NULL,
  `password` char(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

创建表时在括号后面来指定：

ENGINE来指定存储引擎（默认InnoDB）
CHARSET来指定这张表的字符编码方式（gbk可以存储中文）

2.3 查看mysql支持哪些存储引擎

命令：show engines \g

mysql> show engines \g
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

2.4 mysql常用存储引擎

MyISAM存储引擎：

不支持事务机制，安全性低

InnoDB存储引擎：

支持事务，支持数据库崩溃后自动恢复机制。InnoDB最主要特点是非常安全。

InnoDB保证数据的安全，因此效率不是很高，不能很好的节省存储空间。

MEMORY存储引擎：

其数据存储在内存中（因此断电消失），且行的长度固定。这两个特点使得它非常快。

不支持事务

优点：查询效率最高

缺点：不安全，关机后数据消失。因为数据和索引都在内存中。

3. 事务（重点） TRANSACTION

3.1 什么是事务：

一个事务其实就是一个完整的业务逻辑，即一个最小的工作单元，不可再分。

假设转账，从A账户向B账户转账1000元：

将A账户的钱-1000（update语句）
将B账户的钱+1000（update语句）

这就是一个完整的业务逻辑。要么同时成功，要么同时失败，不可再分。这两个update语句要求必须同时成功或同时失败，这样才能保证钱是正确的。

因此事务就是批量的DML语句同时成功，或者同时失败

3.2 只有INSERT UPDATE DELETE 与事务有关

INSERT
UPDATE
DELETE

只有以上三个语句是数据库表中数据进行增、删、改的。

只要操作涉及数据的增删改，那么就一定要考虑安全问题，就与事务有关。

3.3 实现事务的原理：

mysql中只有InnoDB存储引擎支持事务。

因为InnoDB存储引擎提供一组用来记录事务性活动的日志文件。因此在事务的执行过程中，每一条DML操作都会记录到事务性活动的日志文件当中。我们可以提交事务，也可以回滚事务。

提交事务：

清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。

提交事务标志着事务的结束，并且是一种全部成功的结束。

回滚事务：

将之前的所有DML操作全部撤销，并且清空事务性活动的日志文件。

回滚事务标志着事务的结束，并且是一中全部失败的结束。

3.4 提交事务、回滚事务

提交事务：commit

回滚事务：rollback

MYSQL默认情况下是自动提交的。即为每执行一次DML语句提交一次，无法回滚撤销。

要将mysql自动提交关闭，需要执行start transaction;

start transaction相当于开始事务（禁用自动提交），commit或rollback相当于结束事务。

rollback回滚只能回滚到最近一次的提交处

-- rollback例子
start transaction; -- 关闭自动提交机制
insert into user(username,password) values('hhh','kkk');
insert into user(username,password) values('hhh','kkk');
insert into user(username,password) values('hhh','kkk');
-- 插入三行数据
select * from user;
rollback; -- 回滚，之前插入的三行数据被撤销不再存在。
-- 如果没有start transaction关闭自动提交，那么rollback就没有作用
select * from user;

使用 BEGIN 或 START TRANSACTION 开启一个事务之后，自动提交将保持禁用状态，直到使用 COMMIT 或 ROLLBACK 结束事务。之后，自动提交模式会恢复到之前的状态

-- commit例子
start transaction; -- 关闭自动提交机制
insert into user(username,password) values('hhh','kkk');
insert into user(username,password) values('hhh','kkk');
insert into user(username,password) values('hhh','kkk');
-- 插入三行数据
commit; -- 提交事务
select * from user;
rollback; -- 回滚，只能回滚到最近的commit处，因为commit之前的DML语句已经提交
-- 因此没有撤销任何语句，因为insert语句已经提交
select * from user;

3.5 事务的四个特性

A：原子性

说明事务是最小的工作单元，不可再分
C：一致性

在同一个事务中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。
I：隔离性

A事务和B事务之间具有一定的隔离。

在事务正确提交之前，不允许把事务对该数据的改变提供给任何其他事务，即在事务正确提交之前，它可能的结果不应该显示给其他事务。

不同事务在提交的时候，最终呈现出来的效果是串行的，换句话说，既是不同事务，按照提交的先后顺序执行，再换句话说，对于事务本身来说，它所感知的数据库，应该只有它自己在操作。

隔离性是针对数据资源的并发访问，规定了各个事务之间相互影响的程度。
D：持久性

事务最终结束的保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。

3.6 事务的隔离性

隔离级别：

隔离性相当于事务与事务之间的墙，这堵墙越厚，隔离级别就越高。

事务与事务之间的隔离级别有4种：

读未提交：read uncommitted（最低的隔离级别）

事务A可以读取到事务B未提交的数据

这种隔离级别存在的问题就是脏读（dirty read）现象。我们称读到了脏数据。

这种隔离级别一般上是理论上的，大多数的数据库隔离级别都是2档起步
读已提交：read committed

事务A只能读取到事务B提交之后的数据。

这种隔离级别解决了脏读的现象。

这种隔离级别的问题是不可重复读取数据（比如事务A开启之后第一次读到的数据是3条，但此时事务B并发的插入了一条数据并提交，那么之后事务A第二次读取数据时读到的就是4条。3！=4，这称为不可重复读取）

这种隔离级别是比较真实的数据，每一次读到的数据都绝对真实。
可重复读：repeatable read

事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改并且提交了，事务A读取到的数据还是没有发生改变。

可重复读存在的问题是，可能会出现幻影读。即每一次读取到的数据都是幻像，不够真实。

MYSQL默认事务隔离界别就是可重复读
序列化/串行化：serializable（最高的隔离级别）

最高隔离级别，效率最低。解决了所有问题

这种隔离级别表示事务排队，不能并发。在事务A执行过程中事务B不能执行，必须等到事务A结束之后事务B才能执行。

3.7 验证各种隔离级别

查看全局隔离级别

select @@global.tx_isolation; -- 看出mysql默认隔离级别是可重复读
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+

设置全局隔离级别

set global transaction isolation level 隔离级别;

3.7.1 验证read uncommitted

先设置全局隔离级别

set global transaction isolation level read uncommitted;

然后开启两个mysql连接处理一个表

事务A	事务B
start transaction;	start transaction;
select * from test_table; Empty set (0.00 sec)
	insert into test_table values(1,‘aaa’);
select * from test_table; 打印一行结果：1 aaa
	rollback;
select * from test_table; Empty set (0.00 sec)
commit;

3.7.2 验证read committed

先设置全局隔离级别

set global transaction isolation level read committed;

然后开启两个mysql连接处理一个表

事务A	事务B
start transaction;	start transaction;
select * from test_table; Empty set (0.00 sec)
	insert into test_table values(1,‘aaa’);
select * from test_table; Empty set (0.00 sec)
	commit;
select * from test_table; 打印一行结果：1 aaa
commit;

3.7.3 验证repeatable read

先设置全局隔离级别

set global transaction isolation level repeatable read;

然后开启两个mysql连接处理一个表

事务A	事务B
start transaction;	start transaction;
select * from test_table; Empty set (0.00 sec)
	insert into test_table values(1,‘aaa’);
select * from test_table; Empty set (0.00 sec)
	commit;
select * from test_table; Empty set (0.00 sec)
commit;
select * from test_table; 打印一行结果：1 aaa

3.7.4 验证serializable

先设置全局隔离级别

set global transaction isolation level serializable;

然后开启两个mysql连接处理一个表

事务A	事务B
start transaction;
	start transaction;
select * from test_table; Empty set (0.00 sec)
insert into test_table values(1,‘aaa’);
	select * from test_table; 阻塞住，直到其他事务结束后该语句返回。
insert into test_table values(2,‘bbb’); commit;
	其他事务结束，上面的语句返回。打印2行结果： 1 aaa 2 bbb
	commit；

4. 索引 INDEX

4.1 什么是索引

索引是在数据表的字段上添加的，是为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

对于一本字典，查找某个汉字有两种方式：

第一种：一页一页挨着找，直到找到为止。这种查找方式属于全字典扫描。效率比较低
第二种：先通过目录（索引）去定位一个大概的位置，然后直接定位到这个位置，做局域性扫描，缩小扫描的范围，快速的查找，这种查找方式属于通过索引检索，效率较高。

比如以下表user

id(idIndex)	name(nameIndex)
1	zhangsan
2	lisi
3	wangwu

select * from user where name = 'lisi';

以上的SQL语句会去name字段上扫描，因为查询条件是name = 'lisi'。

如果name字段上没有添加索引（目录），或者说没有给name字段创建索引，MYSQL会进行全扫描，会将name字段上的每一个值都比对一遍，效率比较低。

MYSQL在查询方面主要是两种方式：

1.全表扫描
2.根据索引检索

在实际中，汉语字典前面的目录是排序的，按照abcd…的顺序。因为只有排序才会有区间查找这一说（缩小扫描范围其实就是扫描某个区间罢了）。

**在mysql数据库当中索引也是需要排序的，并且索引的数据结构是一个自平衡的二叉树，是一个B树。**遵循左小右大原则存放，采用中序遍历方式遍历数据。

4.2 索引的实现原理

假设有一张表:user

id(PK)	name	每一行记录在硬盘上都有物理存储编号
100	zhangsan	0x1111
120	lisi	0x2222
99	wangwu	0x8888
88	jack	ox9999

在任何数据库表中主键上都会自动添加索引对象，即id字段上自动会有索引，因为id是PK。另外在mysql中，一个字段上如果有unique约束的话，也会自动创建索引对象。

在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号

在mysql中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。

一个索引的简单实现（注意这里只是用了二叉搜索树，并非B树）

在这里插入图片描述

4.3 什么条件下，我们会考虑给字段添加索引

数据量庞大
该字段经常出现在where后面，以条件形式存在，即这个字段经常被扫描
该字段很少的DML操作（update，delete，insert），因为DML之后，索引会重新排序，造成性能下降。

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

4.4 索引的创建和删除

索引创建：

create index 索引名 on 表名(列名)

如给test_table表的name字段添加索引name_index

create index name_index on test_table(name);

删除索引：

drop index 索引名 on 表名

如给test_table表删除name_index索引

drop index name_index on test_table;

4.5 索引的失效

失效的第一种情况：

select * from user where username like '%T';

username字段上即使添加了索引，也不会走索引查询而是全查询。因为模糊匹配中以’%'开头了。

因此应该尽量避免模糊查询的时候以’%'开头，这是一种优化的手段。

失效的第二种情况：

select * from user where username = 'aaa' or password = 'bbb';

使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引。

如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。

所以这就是为什么不建议使用or的原因。

针对这种情况可以使用union组合，通过组合将or拆开成单独的select语句，那么有索引的走索引查询，没索引的走全查询，这样可以提升效率。

失效的第三种情况：

使用复合索引的时候没有使用左侧的列查找，索引失效。

复合索引：两个或更多的字段联合起来添加一个索引叫做复合索引

create index emp_job_sal_index on emp(job,sal); -- 给job和sal两个字段添加复合索引

select * from emp where job = 'aaa'; -- 使用复合索引的左侧列查找，走索引查询
select * from emp where sal = 100; -- 不适用复合索引的左侧列查找，索引失效，走全查询

失效的第四种情况：

在where中索引列参加了运算，索引失效

假设sal字段添加了索引

select * from emp where sal = 100; -- 使用索引查询
select * from emp where sal + 1 = 100; -- 列参与了运算，索引失效，走全查询

失效的第五种情况：

在where当中索引列使用了函数

select * from emp where lower(name) = 'aaa'; -- 使用函数，索引失效，走全查询

4.6 索引是各种数据库优化的重要手段

优化的时候优先考虑的因素就是索引。

索引分类：

单一索引：一个字段上添加索引
复合索引：两个或以上字段添加索引
主键索引：主键上添加索引
唯一性索引：具有unique约束的字段上添加索引

注意：在唯一性较弱的字段上添加索引用处不大。因为可能存在大量重复数据，此时索引不起太大作用。越唯一效率越高。

5. 数据库设计三范式

5.1 什么是数据库设计范式

数据库表的设计依据。即为如何合理地设计数据库表的结构

任何一张表必须有主键，每一个列原子性不可再分
在第一范式基础上，要求所有非主键字段完全依赖主键，不要产生部分依赖
在第二范式基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖

设计数据库表时，按照以上三个范式，可以避免表中数据的冗余，空间的浪费。

5.2 第一范式

最核心，最重要的范式，所有表的设计都需要满足。

必须有主键，并且每一个字段都是原子性不可再分

比如如下表：

id	地址
1	陕西省西安市
2	浙江省杭州市
3	江苏省南京市

这张表就不符合第一范式：

没有主键
地址列可以再分为省份和城市

以上的表可以修改成下面的形式：

id(PK)	省份	城市
1	陕西省	西安市
2	浙江省	杭州市
3	江苏省	南京市

5.3 第二范式：处理复合主键（多对多）的情况

建立在第一范式的基础之上

要求所有非主键字段必须完全依赖主键，不要产生部分依赖

比如如下表：表示一个学生有多个老师，一个老师有多个学生

学生编号 + 教师编号（PK）	学生姓名	老师姓名
1001 001	张三	王老师
1002 002	李四	赵老师
1003 001	王五	王老师
1001 002	张三	赵老师

学生编号、教师编号两个字段联合做主键，满足第一范式，但是不满足第二范式。

'张三’依赖1001，'王老师’依赖001，并不是依赖整个主键，而是依赖了复合主键的其中一部分。显然产生了部分依赖。

部分依赖的缺点：

数据冗余，空间浪费。如表中的’王老师’、‘赵老师’、'张三’重复了

因此应该使用三张表来表示多对多的关系：

学生表：记录学生信息

学生编号（PK）	学生姓名
1001	张三
1002	李四
1003	王五

教师表：记录教师信息

教师编号（PK）	教师姓名
001	王老师
002	赵老师

学生教师关系表

id（PK）	学生编号（FK外键）	教师编号（FK外键）
1	1001	001
2	1002	002
3	1003	001
4	1001	002

当多对多的情况设计表：多对多，三张表，关系表两个外键。

5.4 第三范式

建立在第二范式的基础上，要求所有非主键字段必须直接依赖主键，不要产生传递依赖

比如如下表

学生编号（PK）	学生姓名	班级编号	班级名称
1001	张三	01	一班
1002	李四	02	二班
1003	王五	03	三班
1004	赵六	03	三班

以上表描述班级和学生的关系，因此是一对多的关系，而不是多对多的关系。

上面的表满足第一范式，有主键

上面的表满足第二范式，因为主键是单一主键而不是复合主键，没有产生部分依赖。

但是不符合第三范式：'一班’依赖01,01又依赖1001，产生了传递依赖。这样会导致数据冗余，'三班’数据冗余了。

因此使用两张表即可解决问题：

班级表：

班级编号（PK）	班级名称
01	一班
02	二班
03	三班

学生表：

学生编号（PK）	学生姓名	班级编号（FK）
1001	张三	01
1002	李四	02
1003	王五	03
1004	赵六	03

当一对多的情况下设计表：一对多，两张表，多的表加外键