mysql建表的字段类型和约束条件

您所在的位置：网站首页 › 数据表中至少包含一个字段 › mysql建表的字段类型和约束条件

mysql建表的字段类型和约束条件

2024-07-17 19:49| 来源: 网络整理| 查看: 265

一 . 常见字段类型分类常见字段类型类型分类描述数值型整型小数

①定点数

②浮点数

字符型较短的文本

①char

②varchar

较长的文本

①text

②blob(二进制数据)

日期时间型 1.1数值型 1.1.1 整型

特点：

整数类型默认是有符号，如果想设置成无符号，需要在整数类型后添加 UNSIGNED如果插入的数值超出了整型的范围，会报错（out of range）,并且插入的值是临界值(具体的看数据库的版本)如果不设置长度，会有默认的长度整型整数类型字节有符号和无符号的表示范围Tinyint1

有符号 Tinyint

-128~127无符号 Tinyint UNSIGNED 0~255Smallint2

有符号 Smallint

-32768 ~ 32767无符号 Smallint UNSIGNED 0~65535Mediumint3

有符号 Mediumint

-8388608 ~ 8388607无符号 Mediumint UNSIGNED 0 ~ 1677215

Int

integer

4有符号 Int

-2147483648 ~ 2147483647无符号 Int UNSIGNED0~ 4294967295Bigint8

有符号 Bigint

-9223372036854775808~9223372036854775807无符号 Bigint UNSIGNED0 ~ 2**64-1

1.1.1.1 设置整型字段的无符号和有符号

语法：整数类型 unsigned 如：INT UNSIGNED

1.1.1.2字段类型长度的含义

描述：建表的时候，在设置字段类型时，一般会设置字段的长度。这里指的长度并不是指该字段的数值的表示范围，字段的表示范围是由字段的类型决定的，这里的长度，实际上指的是数据的显示宽度，即宽度不够时会在数值左边添加0来填充。

如：字段类型 (长度) ZEROFILL

注：ZEROFILL 自动将数值转换为无符号的整数

如下：

1.1.2小数

特点：

M 表示：整数部分 + 小数部分D 表示：小数部位当超过范围（当整数部分长度 > M-D ），具体的报错警告得看具体的版本 mysql8版本即当整数部分长度 > M-D 就会报错M和D都可以省略。decimal省略M和D,则M默认为10，D默认为0，即只会保留整数部分；float和double则按范围来决定精度定点型的精度较高，如果要求插入的数值精度较高则使用定点型，如货币运算

1.1.2.1 浮点数类型

浮点数类型浮点数类型字节范围float(M,D)4±1.75494321E-38 ~ ±3.402823466E+38double(M,D)8±2.2250738585072014E-308 ~ ±1.7976931348623157E+308

1.1.2.2 定点数类型（精度更高）

特点：

定点数类型定点数类型字节范围

DEC(M,D)

decimal(M,D)

M+2最大取值范围与double相同，给定的小数的有效取值范围由M和D决定

1.2字符型 1.2.1 较短的文本较短文本字符类型最多字符数描述特点空间利用率时间效率char(M)

M可以省略，默认为1

M为 0~255之间的整数固定长度的字符比较消耗空间，空间利用率低高varchar(M)

M不可以省略

M为0~65535之间的整数可变长度的字符比较节省空间，空间利用率高低

1.2.1.1 char

1.2.1.2 varchar

1.2.2 较长的文本

1.2.2.1 text

1.2.2.2 blob

1.2.3 其他字符串其他字符串字符类型分类描述二进制字符串

binary

保存较短的二进制字符串

类似于char和varchar,不同的是binary,varbinary只包含二进制字符串，不包含非二进制的字符串

varbinary

保存较长的二进制字符串

enum类型具体用法例一

又称枚举类型

要求插入的值必须属于列表中指定值之一

如：列表成员为1~255，则只需要1字节存储

set类型具体用法例二

和enum类型类型类似，它可以保存0~64个成员

和enum类型的最大区别是set类型一次可以选择多个成员，而enum类型只能选择一个

根据成员个数的不同，存储占用的字节也不相同

例一：

例二：

1.3 日期时间型

日期时间型数据日期时间类型字节最小值最大值date41000-01-019999-12-31datatime81000-01-01 00:00:009999-12-31 23:59:59timestamp4197001010800012038年的某个时刻time3-838:59:59838:59:59year119012155 datetime和timestamp的区别 Timestamp支持的时间范围较小,取值范围:19700101080001 到 2038 年的某个时间；Datetime的取值范围: 1000-1-1 到 9999- 12-31 timestamp和实际时区有关,更能反映实际的日期，而datetime则只能反映出插入时的当地时区如：例一 timestamp的属性受Mysq|版本和SQLMode的影响很大

例一：

二 .约束类型

描述：在创建表结构时的一种约束条件，用于限制表中的数据，目的时为了保证插入数据的可靠性和准确性

分类（六大约束）：

NOT NULL:非空，用于保证该字段的值不能为空DEFAULT:默认，用于保证该字段有默认值PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空UNIQUE:唯一，用于保证该字段的值具有唯一-性，可以为空CHECK:检查约束[mysql中不支持]FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值

添加约束的时机：

创建表时修改表时

约束的添加分类

列级（字段）约束：六大约束在语法上都支持，但外键约束没有效果表级约束：除了非空(NOT NULL)，默认(DEFAULT)，其他都支持 CREATE TABLE 表名( 字段名字段类型列级约束, 字段名字段类型列级约束, ... 字段名字段类型列级约束, 表级约束 );

主键和唯一的区别：

主键和唯一的区别约束类型保证唯一性是否允许为空是否有多个是否允许组合（联合键）主键 PRIMARY KEY√×至多有一个主键字段可以（但不推荐使用）唯一 UNIQUE√√可以有多个唯一字段可以（但不推荐使用）

2.1 列级约束

语法：

CREATE TABLE 表名( 字段名字段类型列级约束列级约束列级约束..., 字段名字段类型列级约束列级约束列级约束..., ... 字段名字段类型列级约束列级约束列级约束..., );

注：

只支持非空，默认，唯一，主键一个字段可以有多个约束

例一：

CREATE TABLE stuinfo( id INT PRIMARY KEY, #主键 stuname VARCHAR(20) NOT NULL,#非空 gender CHAR(1) CHECK(gender="男" OR gender="女"),#检查 seat INT UNIQUE,#唯一 age TINYINT DEFAULT 18,#默认 majorid TINYINT REFERENCES major(id) #外键 ); CREATE TABLE major( id TINYINT PRIMARY KEY,#主键 majorname VARCHAR(20) );

2.2 表级约束

语法：

CREATE TABLE 表名( 字段名字段类型, 字段名字段类型, ... 字段名字段类型, 【CONSTRAINT 约束名】约束类型(字段名), 【CONSTRAINT 约束名】约束类型(字段名), ... 【CONSTRAINT 约束名】约束类型(字段名), 【CONSTRAINT 约束名】 FOREIGN KEY(被关联的表名) REFERENCES 被关联的表名(被关联的字段名) #外键 );

例一：

CREATE TABLE stuinfo( id INT, stuname VARCHAR(20) NOT NULL,#非空 gender CHAR(1), seat INT, age TINYINT DEFAULT 18, #默认 majorid TINYINT, CONSTRAINT pk PRIMARY KEY(id),#主键 CONSTRAINT ck CHECK(gender="男" OR gender="女"),#检查 CONSTRAINT uq UNIQUE(seat),#唯一 CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键 );

2.2.1 联合主键

联合主键：两个及以上的字段共同组成一个主键.

CREATE TABLE stuinfo ( id INT, stuname VARCHAR ( 20 ) NOT NULL,#非空 gender CHAR ( 1 ), seat INT, age TINYINT DEFAULT 18,#默认 majorid TINYINT, PRIMARY KEY (id,stuname),#联合主键 CHECK ( gender = "男" OR gender = "女" ),#检查 UNIQUE ( seat ),#唯一 FOREIGN KEY ( majorid ) REFERENCES major ( id ) #外键 );

如何判断主键是否重复

2.2.2 联合唯一键 CREATE TABLE stuinfo ( id INT, stuname VARCHAR ( 20 ) NOT NULL,#非空 gender CHAR ( 1 ), seat INT, age TINYINT DEFAULT 18,#默认 majorid TINYINT, PRIMARY KEY (id),#主键 CHECK ( gender = "男" OR gender = "女" ),#检查 CONSTRAINT 联合唯一键 UNIQUE (seat,stuname),#联合唯一键 FOREIGN KEY ( majorid ) REFERENCES major ( id ) #外键 );

2.2.3 外键

特点：

要求在从表设置外键关系从表外键字段类型要和主表关联字段类型一致或兼容，但从表外键字段名可以和主表关联字段名不同主表的关联字段必须时一个key(主键或唯一)插入数据时，先插入主表，再插入从表删除数据时，先删除从表，再删除主表 2.3 修改表时添加约束类型

语法：

①列级约束

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

如： ALTER TABLE 表名 MODIFY COLUMN id PRIMARY KEY; #添加主键约束

②表级约束

ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】约束类型(字段名);

如：ALTER TABLE 表名 ADD PRIMARY KEY(id); #添加主键约束

2.3.1 添加非空约束 ALTER TABLE 表名 MODIFY COLUMN 字段名字段类型 NOT NULL; 2.3.2 添加默认约束 ALTER TABLE 表名 MODIFY COLUMN 字段名字段类型 DEFAULT 默认值; 2.3.3 添加主键约束

①列级约束

ALTER TABLE 表名 MODIFY COLUMN 字段名 PRIMARY KEY;

②表级约束

ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 PRIMARY KEY(字段名); 2.3.4 添加唯一约束

①列级约束

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

②表级约束

ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 UNIQUE(字段名); 2.3.5 添加外键 ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 FOREIGN KEY (主表名) REFERENCES 主表名( 关联字段名 ); 2.4 修改表时删除约束类型 2.4.1 删除非空约束 ALTER TABLE 表名 MODIFY COLUMN 字段名字段类型 NULL; 2.4.2 删除默认约束 ALTER TABLE 表名 MODIFY COLUMN 字段名字段类型; 2.4.3 删除主键 ALTER TABLE 表名 DROP PRIMARY KEY 2.4.4 删除唯一键 ALTER TABLE 表名 DROP INDEX 唯一键字段名; 2.4.5 删除外键 ALTER TABLE 表名 DROP FOREIGN KEY 外键名三 .级联删除与级联置空 3.1 级联删除

在删除有外键的表时，一般先删除从表的相关数据，再删除主表的相关数据。

但硬要先删除主表的相关数据，则可以为从表设置级联关系

语法一：

级联删除：在删除主表相关数据的同时，从表相应的数据也会被删除

ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY(主表名) REFERENCES 主表名(外键字段) ON DELETE CASCADE; 3.2 级联置空

级联置空：在删除主表相关数据的同时，从表相应的数据不会被删除

ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY(主表名) REFERENCES 主表名(外键字段) ON DELETE SET NULL;

【本文地址】

公司简介

联系我们

今日新闻

推荐新闻

专题文章