SQL学习笔记（4）

最新推荐文章于 2024-04-23 17:38:28 发布

墨瑶_165

最新推荐文章于 2024-04-23 17:38:28 发布

阅读量2.5k

点赞数 2

分类专栏：编程语言、命令相关文章标签： sql mysql 学习

本文链接：https://blog.csdn.net/Echo_165/article/details/123588244

版权

编程语言、命令相关专栏收录该内容

7 篇文章 3 订阅

订阅专栏

本文深入探讨了数据库中的约束概念，包括非空约束、唯一性约束和主键约束，强调了主键在数据完整性和有效性中的作用。此外，详细介绍了外键约束及其在描述‘班级和学生’信息中的应用。接着，讲解了存储引擎的重要性，特别是InnoDB和MyISAM的区别，以及如何选择和查看存储引擎。最后，重点阐述了事务的原理，包括事务的四大特性，以及如何通过提交和回滚来管理事务，确保数据安全。

摘要由CSDN通过智能技术生成

新需求：name和email两个字段联合起来具有唯一性！

unique 和not null联合

4、主键约束: primary key （简称PK）*****

（4）自动维护一个主键值的机制？auto_increment

5、外键约束：foreign key（简称FK）*****

（1）相关术语

（2）业务背景：请设计数据库表，来描述“班级和学生”的信息？

3、怎么查看mysql支持哪些存储引擎呢？

4、MySQL 常用存储引擎

（1）InnoDB存储引擎

（2）MyISAM存储引擎

（3）MEMORY存储引擎（以前被称为HEAP 引擎）

事务是怎么做到多条DML语句同时成功和同时失败的呢？

2、语句和自动提交

（1）提交事务 commit 语句

（2）回滚事务 rollback语句（回滚永远都是只能回滚到上一次的提交点！）

（3）自动提交

（4）怎么把MySQL的自动提交机制关闭？

（1）事务与事务之间的隔离级别（4个等级）：

（2）验证各种隔离级别

一、约束（非常重要！！*****）

1、什么是约束（constrain）？

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

2、非空约束：not null

（约束字段不能为NULL）

drop table if exists t_user;
create table t_user(
id int,
name varchar(255),
email char(32) not null);

insert into t_user(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_user(id,email) values(2,'lily@123.com');
insert into t_user values(3,'lisi');

not null只有列级约束，没有表级约束！

3、唯一性约束: unique

（唯一性约束unique约束的字段不能重复，但是可以为NULL）

drop table if exists t_user;
create table t_user(
id int,
name varchar(255) unique,
email char(32) );

insert into t_user(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_user(id,email) values(2,'lily@123.com');
insert into t_user values(3,'wangwu','wangwu@123.com');
insert into t_user values(4,'wangwu','xiaoxiao@123.com');

新需求：name和email两个字段联合起来具有唯一性！

drop table if exists t_user;
create table t_user(
id int,
name varchar(255),
email char(36),
unique(name,email));

insert into t_user values(3,'wangwu','wangwu@123.com');
insert into t_user values(4,'wangwu','xiaoxiao@123.com');
insert into t_user values(3,'wangwu','wangwu@123.com');

（约束没有添加在列的后面，这种约束被称为表级约束）

unique 和not null联合

drop table if exists t_user;
create table t_user(
id int,
name varchar(255) not null unique,
email char(32) );

insert into t_user(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_user(id,email) values(2,'lily@123.com');
insert into t_user values(3,'wangwu','wangwu@123.com');
insert into t_user values(4,'wangwu','xiaoxiao@123.com');

在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段

（注意：oracle中不一样！）

4、主键约束: primary key （简称PK）*****

（1）相关术语：

主键约束：一种约束
主键字段：该字段上添加了主键约束
主键值：主键字段上的每个值
单一主键：一个字段做主键
复合主键：几个字段联合起来做主键
自然主键：主键值是一个自然数，和业务没关系。
业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

主键值是每一行记录的重要标识，主键值是每一行记录的身份证号。

每一个表都应该有主键，没有主键表无效。

主键的特征：not null + unique (主键值不能为null，也不能重复）

（2）主键约束 primary key

① 列级约束

drop table if exists t_user;
create table t_user(
id int,
name varchar(255) primary key,
email char(32) );

② 表级约束

drop table if exists t_user;
create table t_user(
id int,
name varchar(255) ,
email char(32) ,
primary key(name,email));

insert into t_user(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_user(id,email) values(2,'lily@123.com');
insert into t_user values(3,'wangwu','wangwu@123.com');
insert into t_user values(4,'wangwu','xiaoxiao@123.com');
insert into t_user values(5,'wangwu','wangwu@123.com');

（复合主键比较复杂，不建议使用）

（3）注意：

一个表中，主键约束只能有一个
主键值建议使用：int , bigint , char 等类型
不建议使用 varchar 做主键，因为主键值一般是数字，一般是定长的

在实际开发中使用业务主键多，还是使用自然主键多一些？

自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义。
业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，可能会影响到主键值，所以业务主键不建议使用。尽量使用自然主键。

（4）自动维护一个主键值的机制？auto_increment

auto_increment :表示自增，从1开始，以1递增

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

insert into t_user(name) values('zhangsan');
insert into t_user(name) values('zhangsan');
insert into t_user(name) values('zhangsan');
insert into t_user(name) values('zhangsan');
insert into t_user(name) values('zhangsan');
insert into t_user(name) values('zhangsan');

5、外键约束：foreign key（简称FK）*****

（1）相关术语

外键约束：一种约束
外键字段：该字段添加了外键约束
外键值：外键字段当中的每一个值（外键值可以为NULL）
主键表（父表）：以公共关键字为主键的表（不一定是主键，但至少要有unique约束）
外键表（子表）：以公共关键字为外键的表

注意：

在一张表中可以有多个外键存在
删除表的顺序？先删子表，再删父表。
创建表的顺序？先创建父表，再创建子表。
删除数据的顺序？先删子表，再删父表。
新建数据的顺序？先新建父表，再新建子表。

（2）业务背景：请设计数据库表，来描述“班级和学生”的信息？

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

缺点：数据冗余，空间浪费

第二种方案：班级一张表（父表），学生一张表（子表）

为了保证clsaano字段中的值都是100和101，需要给classno字段添加外键约束。

（3）创建外键

外键的使用需要满足下列的条件：

两张表必须都是InnoDB表，并且它们没有临时表。
建立外键关系的对应列必须具有相似的InnoDB内部数据类型。
建立外键关系的对应列必须建立了索引。

创建外键的两种方式：

方式一：在创建表的时候进行添加
方式二：表已经创建好了，继续修改表的结构来添加外键。

① 在创建表的时候进行添加

create table stuInfo(    
Scode int primary key,
Sname char(10), 
Saddress varchar(50),
Sgrade int,   
Semail varchar(50),
Sbrith date    
)DEFAULT CHARSET='utf8';    

create table score(
studentID    int,
coureseID int,    
score int,    
scoreID int primary key,    
foreign    key(studentID) references stuInfo(Scode))
DEFAULT charset='utf8';

② 建表以后添加外键

alter table stuInfo	add foreign key (scode) references score(studentID);

（4）删除外键

语法：alter table 表名称 drop foreign key 外键名称;

alter table score drop foreign key score_ibQk_1;

注意：如果没有在建表的时候标明外键名称，可以先查看外键名称

查看创表语句

语法： show create table 表名;

show create table t_student;

二、存储引擎（了解）

1、什么是存储引擎？存储引擎有什么用？

存储引擎是一个表存储/组织数据的方式。
不同的存储引擎，表存储数据的方式不同。
存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）

mysql默认的存储引擎是：InnoDB
mysql默认的字符编码方式是：utf8

2、指定存储引擎

建表时指定存储引擎，以及字符编码方式。

在建表的时候可以在最后小括号的")"的右边使用：

engine 来指定存储引擎
charset 来指定这张表的字符编码方式

create table t_product(
id int primary key,
name varchar(255)
)engine=InnoDB default charset=gbk;

3、怎么查看mysql支持哪些存储引擎呢？

show engines \G

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

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.00 sec)

MySQL 支持九大存储引擎，当前版本8.0.28支持8个，版本不同支持情况不同。

4、MySQL 常用存储引擎

（1）InnoDB存储引擎

MySQL默认的存储引擎，同时也是重量级存储引擎

特点：

非常安全（最主要）
支持事务，支持数据库崩溃后自动恢复机制（以保障数据的安全）
不能压缩，不能转换为只读，不会很好的存储空间。

InnoDB管理的表具有以下特征：

每个 InnoDB 表在数据库目录中以.frm 格式文件表示
InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引）
提供一组用来记录事务性活动的日志文件
用 COMMIT(提交)、SAVEPOINT 、ROLLBACK(回滚)支持事务处理
提供全 ACID 兼容
在 MySQL 服务器崩溃后提供自动恢复
多版本（MVCC）和行级锁定
支持外键及引用的完整性，包括级联删除和更新

（2）MyISAM存储引擎

特点：

MyISAM不支持事务机制，安全性低。
可被转换为压缩、只读表来节省空间（该存储引擎的优势）

MyISAM存储引擎管理的表具有以下特征：

使用三个文件表示每个表：

格式文件 — 存储表结构的定义（mytable.frm）
数据文件 — 存储表行的内容（mytable.MYD）
索引文件 — 存储表上索引（mytable.MYI）

索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。可被转换为压缩、只读表来节省空间，对于一张表来说，只要是主键，或者加有unique约束的字段上会自动创建索引。

（3）MEMORY存储引擎（以前被称为HEAP 引擎）

特点：使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定。

这两个特点使得 MEMORY 存储引擎非常快。

优点：查询效率最高，不需要与硬盘交互

缺点：不安全，关机后数据消失（因为数据存储在内存中）

MEMORY 存储引擎管理的表具有下列特征：

在数据库目录内，每个表均以.frm 格式的文件表示。
表数据及索引被存储在内存中。（目的就是快，查询快！）
表级锁机制。
不能包含 TEXT 或 BLOB 字段。

三、事务*****

1、什么是事务 transaction

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

什么是一个完整的业务逻辑？

比如转账业务，A账户像B账户汇款10000元

将A账户减去10000元（update语句）

将B账户加上10000元（update语句）

以上操作是最小的工作单元，不可再分，要么同时实现，要么同时失败。

只有 DML（数据操作语言）才有事务之说

insert dalete update
因为只有以上的三个语句是对数据库表中数据进行增、删、改的。
只要你的操作涉及到数据的增、删、改，就一定要考虑安全问题。数据安全第一位！！！
一个事务其实就是多条DML语句同时成功，或者同时失败！

事务是怎么做到多条DML语句同时成功和同时失败的呢？

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

2、语句和自动提交

（1）提交事务 commit 语句

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

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

（2）回滚事务 rollback语句（回滚永远都是只能回滚到上一次的提交点！）

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

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

（3）自动提交

MySQL 默认的事务行为，每执行完DML语句提交一次

不符合我们开发习惯，为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条
就提交一条。

（4）怎么把MySQL的自动提交机制关闭？

先执行这个命令：

start transaction;

3、演示事务

① 回滚事务 rollback

mysql> select * from t_student;
+-----+--------+---------+
| cno | sname  | classno |
+-----+--------+---------+
|   1 | 张三   |     100 |
|   2 | 李四   |     100 |
+-----+--------+---------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_student values
    -> (3,'王五',100),
    -> (4,'小二',200);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_student;
+-----+--------+---------+
| cno | sname  | classno |
+-----+--------+---------+
|   1 | 张三   |     100 |
|   2 | 李四   |     100 |
|   3 | 王五   |     100 |
|   4 | 小二   |     200 |
+-----+--------+---------+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_student;
+-----+--------+---------+
| cno | sname  | classno |
+-----+--------+---------+
|   1 | 张三   |     100 |
|   2 | 李四   |     100 |
+-----+--------+---------+
2 rows in set (0.00 sec)

② 提交事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_student;
+-----+--------+---------+
| cno | sname  | classno |
+-----+--------+---------+
|   1 | 张三   |     100 |
|   2 | 李四   |     100 |
+-----+--------+---------+
2 rows in set (0.00 sec)

mysql> insert into t_student values
    -> (3,'王五',100),
    -> (4,'小二',200);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_student;
+-----+--------+---------+
| cno | sname  | classno |
+-----+--------+---------+
|   1 | 张三   |     100 |
|   2 | 李四   |     100 |
|   3 | 王五   |     100 |
|   4 | 小二   |     200 |
+-----+--------+---------+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_student;
+-----+--------+---------+
| cno | sname  | classno |
+-----+--------+---------+
|   1 | 张三   |     100 |
|   2 | 李四   |     100 |
|   3 | 王五   |     100 |
|   4 | 小二   |     200 |
+-----+--------+---------+
4 rows in set (0.00 sec)

4、事务的特性

原子性：事务是最小的工作单元，不可再分
一致性：在同一事务当中，所有操作必须同时成功或者同时失败，以保证事务的一致性。
隔离性：A事务和B事务之间具有一定的隔离。（A事务在操作一张表的时候，另一个事务B也操作这张表会那样？？？）
持久性：事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上！

5、事务的隔离性

A教室和B教室中间有一道墙，这道墙可以很厚，也可以很薄。这就是事务的隔离级别。
这道墙越厚，表示隔离级别就越高。

（1）事务与事务之间的隔离级别（4个等级）：

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

事务A可以读到事务B未提交的数据（没有提交就读到了）
存在的问题：脏读现象（我们称读到的数据为脏数据）
这种隔离级别一般是理论上的

读已提取：read committed （oracle数据库默认的隔离级别）

事务A只能提取到事务B提交的数据
解决了脏读现象
问题：不可重复提取数据（在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，第二次再读取的时候，读到的数据是4条，3不等于4称为不可重复读取）
每次读到的数据绝对真实

可重复读：repeatable read （MySQL的默认隔离级别）

事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的。
即使事务B将数据已经修改，并且提交了，事务A读取到的数据还是没有发生改变《提交之后也读不到，永远读取的都是刚开启事务时的数据》
解决了不可重复读取数据的问题
问题：可能出现幻影读（每次读取的数据都不够真实的）例如：早上启动一个事务，到下午还没结束事务，读到的数据就还是原来的，不够真实。

序列化/串行化：serializable（最高的隔离级别）

表示事务排队，不能并发
效率最低，解决了所有问题
每次读到的数据都是最真实的

（2）验证各种隔离级别

（被测试的表：t_user）

验证：read uncommitted

use bjpowernode;
                            use bjpowernode;
start transaction;
select * from t_user;
                            start transaction;
                            insert into t_user values('zhangsan');
select * from t_user;;

验证：read committed

use bjpowernode;
                            use bjpowernode;
start transaction;
                            start transaction;
select * from t_user;
                            insert into t_user values('zhangsan');
select * from t_user;
                            commit;
select * from t_user;

验证 repeatable read

use bjpowernode;
						use bjpowernode;
start transaction;
						start transaction;
select * from t_user;
						insert into t_user values('lisi');
						insert into t_user values('wangwu');
						commit;
select * from t_user;

验证 serializable

use bjpowernode;
									use bjpowernode;
start transaction;
									start transaction;
select * from t_user;
insert into t_user values('abc');
                                    select * from t_user;