mysql数据类型、约束、标识列（自增长）

目录

数据类型

一、整型

二、小数

三、字符型

四、日期和时间型

约束

一、创建表时添加约束

二、修改表时添加约束 //以stuinfo为例

三、修改表时删除约束

标识列

---

数据类型

常见的数据类型

数值型：

整型

小数：

定点数

浮点数

字符型：

较短的文本：char、varchar

较长的文本：text、blob（较长二进制数据）

日期型：

整型：

一、整型

整数类型	字节	范围
Tinyint	1	有符号-128~127 无符号0~255
Smallint	2	有符号-32768~32767
Mediumint	3	有符号-8388608~8388607 无符号0~1677215
Int、integer	4	很大很大
Bigint	8	特别大特别大

特点：

#1如果不设置有无符号，默认有符号，添加 unsigned关键字后为无符号，插入超范围则报错

#2如果不设置长度，会有默认的长度

//补充例如INT(7) ZEROFILL代表显示7最大宽度不够的用0填充且为无符号

二、小数

1、浮点型

float(M,D)

double(M,D)

2、定典型

dec(M,D)

decimal(M,D)

浮点数类型	字节	范围
float	4	很大
double	8	很大
定点数类型	字节	范围
DEC(M,D) DECTMAL(M,D)	M+2	最大取值范围与double相同，给定decimal的有效值范围由M和D决定

特点：

#1M和D

M代表整数部位+小数部位

D代表小数部位

如果超过范围，则插入临界值

#2M和D都可以省略

如果是decimal，则M默认为10，D默认为0

float和double则会根据插入数值自动变换精度

#3定典型的精度较高。

原则：

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

三、字符型

较短的文本：char、varchar

字符串类型	最多字符数	描述及存储需求
char(M)	M	M为0~255间的整数
varchar(M)	M	M为0~65535间的整数

特点：M代表最大字符数，char默认为1，但是varchar必须加M。

char是固定长度字符，效率较高。

varchar是可变长度字符（随着输入的字节数变化但不超过最大值）较节省空间但效率低。

枚举类型ENUM（不区分大小写都是小写）

用法ENUM(可用字符,,,,)

位类型

bit(M) 1~8字节 范围Bit(1)~Bit(8)

binary和varbinary类型类似与char、varchar但是它们包含二进制字符串而不包含非二进制字符串。

Set类型（不区分大小写都是小写）

和枚举Enum类似里面可以保存0~64个成员区别是Set可以一次选取多个成员而Enum只能选一个根据成员个数不同，存储所占字节数也不同，大概是一个字节数可以容纳8个成员。

例子插入多个 VALUES(‘a,b,c’)

较长的文本：text、blob（较长二进制数据）

后面再说到

四、日期和时间型

日期和时间类型	字节	最小值	最大值
date 只保存日期	4	1000-01-01	9999-12-31
datetime 保存日期+时间	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp 保存日期+时间	4	19700101080001	2038年的某个时刻
time 只保存时间	3	-838:59:59	838:59:59
yaer 只保存年	1	1901	2155

timestamp和实际时区有关，更能反映实际日期，而datetime只能反映插入时的当地时区

timestamp的属性受mysql版本和sqlmode的影响很大。

SET time_zone=”;可以更改时区

约束

常见约束

含义：一种限制，用于限制表中的数据，为保证表中数据的准确性和可靠性

CREAT TABLE 表名(

字段名 字段类型 列级约束

表级约束

)

分类：6大约束

NOT NULL:非空，用于保证该字段的值不能为空

DEFAULT:默认约束，用于保证该字段有默认值

PRIMARY KEY:主键,用于保证该字段的值具有唯一性，且非空

UNIQUE:唯一，用于保证该字段有唯一性，但是可以为空

CHECK:检查约束(mysql不支持)用于加限制比如性别限制为男和女

FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值

在从表添加外键约束，用于引用主表中的某列值

添加约束的时机：

1、创建表时

2、修改表时

约束的添加分类

列级约束：

6大约束都可以写，但外键约束没有效果。

表级约束：

除了非空、默认、其他的都支持。

一、创建表时添加约束

约束可以添加多个。

1、添加列级约束

语法：

直接在字段名和类型后面追加约束类型即可，

只支持：默认、非空、主键、唯一

用例：CREATE TABLE stuinfo (

id INT PRIMARY KEY,#主键

stuName VARCHAR ( 20 ) NOT NULL,#非空

gender CHAR ( 1 ) CHECK ( gender = ‘男’ OR gender = ‘女’ ),#检查CHECK mysql不支持这里给出语法

seat INT UNIQUE,#唯一

age INT DEFAULT 18,#默认约束

majorId INT //这里给出外键约束的写法major INT REFERENCES 表名(列名)

);

可以通过

DESC 表名 查看表结构

SHOW INDEX FROM 表名来查看表中所有的索引，包括主键、外键、唯一

2、添加表级约束

语法：

在各个字段的最下面

【CONSTRAINT 约束名】（可以不起） 约束类型（字段名）

用例（与上面的效果一致）：

注意：如果不起名，会有个默认名

CREATE TABLE stuinfo(

id INT,

stuname VARCHAR(20),

gender CHAR(1),

seat int,

majorId int,

CONSTRAINT pk PRIMARY KEY(id), //pk为约束名（）内是被添加约束字段名 CONSTRAINT是表级约束语句

CONSTRAINT uq UNIQUE(seat),

CONSTRAINT ck CHECK(gender=’男’OR gender=’女’),

CONSTRAINT fk_stuinfomajor FOREIGN KEY (majorId) REFERENCES major(id) //外键

);

注意：在mysql中主键名改名了也没效果

通用的写法用（stuinfo例子）

CREATE TABLE IF NOT EXISTS stuinfo(

id INT PRIMARY KEY,

stuName VARCHAR ( 20 ) NOT NULL,

sex CHAR(1),

seat INT UNIQUE,

majorId INT,

CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorId) REFERENCES major(id) //外键

注意：fk是FOREIGN KEY的缩写stuinfo是当前表名major是主表名。

主键和唯一键的对比

主键和唯一键都有唯一性，主键不允许为空，一个表至多一个，可以组合（不推荐）

主键允许为空，一个表可以有多个，可以组合（不推荐）

组合方式PRIMARY KEY(id,stuname)将id和stuname字段组合成一个主键，当输入两个相同的id和stuname的行时会报错

唯一键的组合方式一致。

外键的特点：

1、要求在从表设置外键关系

2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求

3、主表中的关联列必须是一个key（一般是主键或唯一键）

4、插入数据时，先插入主表，再插入从表

删除数据，先删除从表，再删除主表

二、修改表时添加约束 //以stuinfo为例

1、添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

注：删除约束时将NOT NULL改为NULL即可

2、添加默认约束 略

3、主键和唯一键的两种添加方法

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

ALTER TABLE ADD PRIMARY KEY(id);

添加列级约束

ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;

添加表级约束;

ALTER TABLE 表名 ADD 【CONSTRAINT 约束吗】约束类型(字段名) 【外键的引用】;

三、修改表时删除约束

1、删除非空约束

ALTER TABLE 表名 MODIFY 字段名 字符类型 NULL;

2、删除默认约束

ALTER TABLE 表名 MODIFY 字段名 字符类型 ;

3、删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

4、删除唯一

ALTER TABLE 表名 DROP INDEX 字段名;

5、删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 字段名

补充内容：删除有外键关联的数据

方式一：级联删除

添加级联删除：在创建外键时在后面加上ON DELETE CASCADE

用DELETE删除主表中有从表依赖的外键的记录时会一同删除从表中依赖于该主键的行。

方式二：级联置空

添加级联置空：在创建外键时在后面加上ON DELETE SET NULL

删除时从表不会删除整行而是将外键列置NULL。

标识列

又称：自增长

含义：可以不用手动的插入值，系统提供默认的序列值

一、创建表时设置标识列

在KEY(必须要有)后面添加 AUTO_INCREMENT

例如：

id INT PRIMARY KEY AUTO_INCREMENT,

查看可设置的自增长选项

SHOW VARIABLES LIKE ‘%auto_increment%’;

结果：

incr1表示步长（每次增长值），offs表时起始值（mysql不支持设置）

可以用SET auto_increment_increment=;改变步长

注意：第一个插入的值可以作为起始值，其他值不填，这样可以变相修改起始值

特点：

1、使用标识列必须有一个key

2、一个表最多一个标识列

3、标识列类型只能是字符型

二、修改表时设置标识列

ALTER TABLE 表名 MODIFY COLUMN 字段名 类型 KEY; AUTO_INCREMENT

三、修改表时删除标识列

ALTER TABLE 表名 MODIFY COLUMN 字段名 类型;