MySql学习笔记四

5.3.数据类型

1. 数值型
   • 整型
   • 小数
     • 定点数
     • 浮点数
2. 字符型
   • 较短的文本：char, varchar
   • 较长的文本：text, blob(较长的二进制数据)
3. 日期型

原则：所选择类型越简单越好，能保存数值的类型越小越好

5.3.1.数值型

5.3.1.1.整型

Tinyint： 一个字节

Smallint：两个字节

Mediumint：三个字节

Int, Integer：四个字节

Bigint：八个字节

默认是有符号，无符号整型是在类型后加unsigned

超出范围时是距离范围最近的临界值

如果不设置长度，会有默认长度，长度只跟查询结果的显示宽度有关

zerofill会根据长度来补充0，但是如果使用该关键字会自动设置为无符号整型

5.3.1.2.小数

5.3.1.2.1.浮点型

float(M, D)：四个字节

double(M, D)：八个字节

M表示小数和整数的位数和，D表示小数点后的位数

不加(M, D)则会根据插入数值的精度来决定精度

5.3.1.2.2.浮点型

DEC(M, D)或DECTMAL(M, D) ： M个字符，最大取值范围与double相同，比浮点型准确

不加(M, D)默认是(10, 0)

5.3.2.字符型

较短的文本：char, varchar(还有binary和varbinary 存储较短的二进制类型)

较长的文本：text

较大的二进制：blob

char, varchar

char(M)：M个字符，0 <= M <= 255

varchar(M)：M个字符，0 <= M <= 65535

char代表固定长度字符，varchar代表可变长度字符，char的效率高于varchar的效率

ENUM, Set

ENUM('str1', 'str2',...) ：枚举类型，不区分大小写

Set跟ENUM相似，但是能选择多个成员插入，成员间用,隔开

5.3.3.日期型

date: 四字节，只保存年月日，范围 1000-01-01 至 9999-12-31

datetime: 八字节，保存年月日时分秒，范围 1000-01-01 00:00:00 至 9999-12-31 23:59:59

timestamp: 四字节，取值范围19700101080001至2038年某个时间，timestamp和实际时区有关，受Mysql版本和SQLmode影响较大

---

5.4.约束

约束是一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠

六大约束：

• NOT NULL：非空，用于保证该字段的值不为空
• DEFAULT：默认，给字段设置默认值
• PRIMARY KEY：主键，保证字段的值具有唯一性，并且非空
• UNIQUE：唯一，用于保证字段的值具有唯一性，可以为空，但只能插入一个null
• CHECK：检查约束（Mysql不支持）
• FOREIGN KEY：外键，用于限制两个表的关系，保证该字段的值必须来自主表的关联列的值，外键表中的引用的字段必须是非空或者主键

添加约束的时机：

1. 创建表时
2. 修改表时

约束的添加分类

• 列级约束：六大约束都可以写，但外键约束没有效果
• 表级约束：除了非空和默认，别的都支持

例：

create table 表名(

	字段名 字段类型 (列级约束),

    ...

    表级约束

)

5.4.1.创建表时添加约束

5.4.1.1.添加列级约束

示例：

create table 表名(

    字段名 字段类型 primary key, #主键

    字段名 字段类型 not null, #非空约束

    字段名 字段类型 check(字段条件), #检查约束

    字段名 字段类型 unique, #唯一约束

    字段名 字段类型 default 默认值, #默认约束 mysql不支持

    字段名 字段类型 references 外键表名(字段名) #外键约束无效

)

5.4.1.2.添加表级约束

(constraint 约束名) 约束类型(字段名)

constraint 约束名 可以省略

示例

create table 表名(

    字段名1 字段类型1,

    字段名2 字段类型2,

    ...

    字段名n 字段类型n,

    constraint pk primary key(字段名), #主键约束

    constraint uq unique(字段名), #唯一约束

    constraint ck check(字段条件), #检查约束

    constraint fk foreign key(字段名) references 外键表名(字段名) #外键约束

)

5.4.2.修改表时添加约束

• 添加非空约束

alter table 表名 modify column 列名 新类型 not null;

alter table 表名 modify column 列名 新类型 (null); #删除

• 添加默认约束

alter table 表名 modify column 列名 新类型 default 默认值;

alter table 表名 modify column 列名 新类型 ; #删除

• 添加主键约束

alter table 表名 modify column 列名 新类型 Primary key; #列级约束

alter table 表名 add (constraint 约束名) Primary key(列名); #表级约束

alter table 表名 drop primary key; #删除

• 添加唯一约束

alter table 表名 modify column 列名 新类型 UNIQUE; #列级约束

alter table 表名 add (constraint 约束名) UNIQUE(列名); #表级约束

alter table 表名 drop index 约束名; #删除

• 添加外键

alter table 表名 add (constraint 约束名) foreign key(字段名) references 外键表名(字段名); #表级约束

alter table 表名 drop foreign key 约束名; #删除

---

5.5.标识列

又称自增长列，可以不用手动的插入值，系统提供默认的序列值

一个表最多只能有一个标识列

标识列的类型只能是数值型

create table 表名(

    字段名1 字段类型1 auto_increment,

    ...

    ) #创建

alter table 表名 modify column 列名 新类型 约束 auto_increment; #修改

alter table 表名 modify column 列名 新类型; #删除

---

6.TCL（事务控制语言）

特性：ACID

• 原子性(Atomicity)：事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
• 一致性(Consistency)：事务必须使数据库从一个一致性状态变换到另一个一致性状态
• 隔离性(Isolation)：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰
• 持久性(Durability)：一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

6.1.事务的创建

隐式事务：事务并没有明显的开启和结束的标记,比如insert, delete, update

显式事务：事务有明显的开始和结束的标记，前提必须设置自动提交功能为禁用

示例：

set autocommit = 0; #开启事务

(start transaction)

sql语句1

sql语句2

...

commit; #提交事务

rollback; #回滚事务

---

6.2.隔离级别

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时。如果没有采取必要的隔离机制，就会导致各种并发问题：

• 脏读：对于两个事务T1,T2，T1已经读取了T2更新但还没被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的。

步骤	事务1	事务2
1	开始事务
2		开始事务
3	读取A表中的字段A1
4	将A表中的字段A1更改
5		读取A表中的字段A1
6		将A表中的字段A1更改
7	回滚
8

• 不可重复读：对于两个事务T1,T2, T1读取了一个字段，然后T2更新了该字段之后，T1再读取同一个字段，值就不同了

步骤	事务1	事务2
1	开始事务
2	查询A表中的字段A1(并未提交事务)
3		开始事务
4		将A表中的字段A1更改
5		提交
6	查询A表中的字段A1(并未提交事务)
7	两次查询A表中的A1不一致

• 幻读：对于两个事务T1,T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，之后如果T1再次读取同一个表，就会多出几行。

步骤	事务1	事务2
1	开始事务
2	查询A表中的数据(结果为多行，并未提交事务)
3		开始事务
4		插入数据
5		提交
6	查询A表中的数据(结果为多行，并未提交事务)
7	两次查询A表中数据行数不一致

数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题

有一个事务与去其他事务隔离的程度称为隔离级别，数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性就也好，但并发性越弱。

	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	X	√	√
repeatable read	X	X	√
serializable	X	X	X

mysql中默认repeatable read

查询隔离级别

select @@tx_isolation;

select @@transaction_isolation; #Mysql8值后换为这个

设置隔离级别

set session|global transaction isolation level 隔离级别;

---

6.3.回滚点

示例：

set autocommit = 0; #开启事务

(start transaction)

sql语句1

savepoint 回滚点名称

sql语句2

...

rollback to 回滚点名称; #回滚到保存点

保存点之前的事务正常提交

---