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 回滚点名称; #回滚到保存点

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

---