MySql学习笔记四
5.3.数据类型
- 数值型
- 整型
- 小数
- 定点数
- 浮点数
- 字符型
- 较短的文本:char, varchar
- 较长的文本:text, blob(较长的二进制数据)
- 日期型
原则:所选择类型越简单越好,能保存数值的类型越小越好
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:外键,用于限制两个表的关系,保证该字段的值必须来自主表的关联列的值,外键表中的引用的字段必须是非空或者主键
添加约束的时机:
- 创建表时
- 修改表时
约束的添加分类
- 列级约束:六大约束都可以写,但外键约束没有效果
- 表级约束:除了非空和默认,别的都支持
例:
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 回滚点名称; #回滚到保存点
保存点之前的事务正常提交