MySQL 字段类型和常见约束

前言

建立表结构时，需定义好每个字段的类型。

一、字段类型

1）整型

2）浮点型

3）字符型

4）日期型

5）完整笔记

一、数值型
整型、小数（定点数、浮点数）

1. 整型：int(默无符号)、int UNSIGNED（设置有符号）
   注：长度表示不满长度是否用0补全，如int(7) ZEROFILL
2. 小数
   1）浮点数：DOUBLE(M,D); FLOAT(M,D);
   2）定点数:
   DEC(M,D) M代表整数部位和小数部位个数和，D代表小数部位个数,如果超过则插入临界值。
   DECIMAL(M,D)
   注：（默认情况下）浮点数的精度随插入数据的进度来确定精度，定点数默认（10,0）

二、字符型

1. 较短的文本：char(M,默认为1)、varchar(M，不可省略)
2. 较长的文本：text、blob（较大二进制存储）
3. 区别：
   1）char固定长度字符，varchar非固定。
   2）char在JDBC取出来是固定长度字符的，所以字符可能不相等。
   3）char正因为固定长度处理，所以效率高一点。

三、枚举型create table student( sex ENUM('男','女') )

四、SETcreate table tabSet( s SET('a','b','c') ); insert into tabSet values('a,b');# 可插入set中的多个

五、日期型

CREATE TABLE tab_date ( t1 datetime, # 和timestamp一样，为时间加日期.但其表达范围广
t2 TIMESTAMP # 可根据不同时区做相应变化，而datetime不行
);

六、对比与原则

1. 定点型浮点型对比
   定点运算，精度高，比如货币计算。
2. 选择类型的原则
   所选择的类型越简单越好，越节约空间越好

二、常见约束

一、常见约束

1. 含义
   用来限制字段，来保证字段的准确和可靠性。（比如主键不可重复，如学号不可重复）
2. 常见约束
   1）not null，非空约束，该字段不能为空，比如主键不能为空，如学号。
   2）DEFAULT,保证该字段有默认值，比较友好，然后去update就可以了。
   3）UNIQUE,字段不能重复,可为空，用来那种要么写就写对，要么就不写为null.
   4）primary key,主键， 能够用来区分不同的记录，等于not null+UNIQUE
   5）CHECK,检查约束，如检查性别字段是否为男或女.[MySQL不支持]
   6）FOREIGN key,用于限制两个表的关系，要求该字段来自主表的字段，类型必须相同且主表中必须有该主键值存在。（如从表学生表的班级id一定来自于主表班级表已存在的id）
3. 语法

create table 表名(
 字段 字段类型 列级约束，
 ...，
 表级约束
)

4. 两种约束类型
   1）列级约束，六大约束都可以作为列级约束，但外键约束没有效果，笔记外键代表的是表与表之间的联系，该字段的限定跟主表有关。
   语法）加在后面
   2）表级约束，除了非空、默认，其它都支持.
   语法）constraint 名字 约束类型
5. 例子

DROP DATABASE students
IF
	exist students;
CREATE DATABASE students;
USE students;
CREATE TABLE stuInfo (
	id INT PRIMARY KEY,#主键
	stuName VARCHAR ( 20 ),# 姓名
	seat INT UNIQUE,# 座位
	age INT DEFAULT 18， # 年龄
	gender CHAR ( 1 ) CHECK ( gender = '男' OR gender = '女' ),# 性别
	majorId INT,#专业ID
	CONSTRAINT fk FOREIGN KEY ( majorId ) REFERENCES major ( id ) # 表级约束，外键
	
	) CREATE TABLE major (
	id INT,
	majorName VARCHAR ( 20 ),
	CONSTRAINT pk PRIMARY KEY ( id ),
	CONSTRAINT uq UNIQUE ( majorName ),
CONSTRAINT nn NOT NULL ( majorName ) 
)

二、辅助操作

1. 查看表结构DESC 表名
2. 查看表中的所有索引，包括主键、外键、唯一值。
   show index from 表名

三、主键和唯一的区别

一、主键与唯一的区别

1. 是否为空，主键不能为空，唯一可以为空。
2. 能否重复，主键和唯一都不能重复
3. 个数，主键只能有0或1个，唯一可以有多个。
4. 是否允许组合，字段可以组合为1个主键，字段也可以组合限定这个组合不为空。

四、外键的特点

一、外键的特点

1. 要求从表设置外键关系到主表
2. 从表的外键类型与主表关联的类型要求一致且主表中存在该值。
3. 主表的关联列一般是主键或唯一字段。
4. 插入数据有关联时，先插入主表记录，再插入从表记录关联；删除数据时，先删从表关联记录，再删主表关联记录。

五、修改约束

一、外键的特点

1. 要求从表设置外键关系到主表
2. 从表的外键类型与主表关联的类型要求一致且主表中存在该值。
3. 主表的关联列一般是主键或唯一字段。
4. 插入数据有关联时，先插入主表记录，再插入从表记录关联；删除数据时，先删从表关联记录，再删主表关联记录。

二、修改表约束

1. 添加非空约束alter table student modify column stuName VARCHAR(20) NOT NULL

2. 删除非空约束
   alter table student modify column stuName VARCHAR(20) NULL
3. 添加默认约束
   alter table student modify column age int DEFAULT 18
4. 删除默认约束
   alter table student modify column age int
5. 添加主键
   1） 列级约束
   alter table student modify column id int primary key
   2）表级约束
   alter table student add primary key(id)
6. 删除主键
   1）列级
   alter table student modify column id int
   2）表级
   alter table student drop primary key
7. 添加唯一键
   1）列级
   ·alter table student modify column seat UNIQUE·
   2)表级
   alter table student add UNIQUE(seat)
8. 删除唯一键
   1）列级
   alter table student modify column seat
   2）表级
   alter table student drop index seat
9. 添加外键
   alter table student add constrain pk foreign key(majorId) references major(id)
10. 删除外键
    alter table student drop index pk

六、标识列

一、标识列
概念）又称自增长列，不用手动插入值，系统提供默认的自增序列
语法）field 类型 约束 auto_Increment
注意）

1. 如果手动插入一个值，则起始值变为设置的值
2. 该字段必须是一个key,且只能有一个自增长列
3. 该字段的类型只能是数值型。
4. 可以设置它的步长

二、辅助操作

1. 查看系统变量
   show variables like '%auto_increment%'
2. 设置系统变量（有些不可设置）
   set auto_increament_increment = 2；# 设置步长

总结

1）字段类型
2）常见约束
3）主键与唯一
4）外键特点
5）修改约束
6）标识列

参考文献

[1] MySQL 尚硅谷