MySql学习笔记四

时间:2022-01-23 17:00:43

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

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