数据库列类型与完整性约束详解-优快云博客

本文链接：https://blog.youkuaiyun.com/jjswift/article/details/119721766

1 列的定义

1.1列名

列名一般使用小写英文单词，若有多个单词，用_连接单词；不能使用数据库中的关键词

1.2列的类型

1.2.1 整数类型

整数类型	字节	无符号数取值范围	有符号数取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32767
MEDIUMINT	3	0~16777215	-8388608~8388607
INT	4	0~4294967295	-2147483648~2147483647
INTEGER	4	0~4294967295	-2147483648~2147483647
BIGINT	8	0~18446744073709551615	-9,223,372,036,854,775,808~9,223,372,036,854,775,807

1.2.2 浮点类型

类型	字节	非負数取值范围	負數取值范围
FLOAT	4	0和1.175494351E-38~3.402823466E+38	-3.402823466E+38~-1.175494351E-38
DOUBLE	8	0和2.2250738585072014E-308~1.7976931348623157E+308	-1.7976931348623157E+308~-2.2250738585072014E-308
DECIMAL(M,D)/DEC(M,D)	M+2	0和2.2250738585072014E-308~1.7976931348623157E+308	-1.7976931348623157E+308~-2.2250738585072014E-308

其中float与double类型为浮点数类型，decimal为定点数类型；

decimal括号中的M表示总长度（精度），D表示小数点后的长度（标度）；其中M范围为1~65（不填写默认为10），D范围为0-30（不填写默认是0）

float与double也可以在后面加上(M,D)，不指定时默认按照实际精度显示

1.2.3 日期与时间类型

类型	字节	取值范围	零值
YEAR	1	1901~2155	0000
DATE	4	1000-01~9999-12-31	0000-00-00
TIME	3	-838:59:59~838:59:59	000:00:00
DATETIME	8	1000-01 00:00:00~9999-12-21 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4	19700101080001~2038011911407	00000000000000

常用的时间函数：

CURDATE()    --获取当前date，可直接插入到DATE类型中
NOW()   //获取当前datetime，可直接插入到DATETIME和TIMESTAMP类型中
TIME()  //获取参数给定时间串中的时分秒，可直接插入TIME类型中
YEAR()  //获取参数给定时间串中的年份，可直接插入YEAR类型中

MONTH()、DAY()、HOUR()、MINUTE()、SECOND()
为获取参数中月、日、时、分、秒

1.2.4 字符串类型

CHAR类型为固定长度，长度可以为0~255之间的任意值，
例：char(100)，指定长度为100，不足部分以空格靠右填充

VARCHAR类型长度可变，长度可以为0~65535之间的任意值，例：varchar(100)，存储值时，值的最大范围是多少就分配多少

使用建议：
① char使用指定空间，varchar根据数据定空间
② char的查询速度大于varchar的查询速度，varchar还需通过记录数计算空间
③ 确定数据占多大空间使用char类型
④ 不确定数据有多少使用varchar类型
⑤ 数据超过255，但也不是特别大可以使用varchar类型
⑥ 字符串尾部需要留空格，必须选择varchar类型

1.2.5 text类型

类型	长度
TINYTEXT	0~255字节
TEXT	0~65535字节
MEDIUMTEXT	0~16772150字节
LONGTEXT	0~4294967295字节

TEXT中类型无法指定长度

1.2.6 ENUM和SET类型

ENUM为枚举类型，定义为此类型，在给栏位设值时只能从所给枚举内容中选择其中一个
例：ENUM(‘值1’,‘值2’,‘值3’…)

SET和ENUM类似，定义此类型，在给栏位设值时只能从所给枚举内容中选择其中一个
例：SET(‘值1’,‘值2’,‘值3’…)

2 列的完整性约束

2.1 主键约束

PRIMARY KEY 能唯一标识表中的信息，表中某个栏位中的值为特殊值，不会重复出现且能通过该栏位标识该表，一般定义为主键，主键可以定义一个或多个

用法：
①单个主键：

propName propType PRIMARY KEY

②多个主键：

propName1 propType, propName2 propType, ... 
CONSTRAINT pk_name PRIMARY KEY(propName1,propName2)

2.2 外键约束

一般在一张表中某个字段需要与另一张表中的数据有所关联时，可以在表中加一个字段表示另一张表中内容，通常把这个字段设为外键

例：有一张学生表与一张班级表，学生表中需要记录每个学生学生所在的班级

班级表：

id	名称	班主任
101	萌新一班	马老师
102	萌新二班	张老师
103	萌新三班	汪老师

学生表：

id	姓名	班级id
1	张三	102
2	李四	101
3	王五	101
4	赵龙	102

--创建班级表
create table class(id int primary key, name char(4), teacher char(3));

--创建学生表
create table student(id int primary key, name char(4), class_id int, constraint fk_stu_class foreign key(class_id) references class(id));

创建外键之栏位一般为另一张表中的主键，
格式一般为：

constraint fk_name foreign key(class_id) 
references tableName(propName)

其中 constraint fk_name 可以省略不写

2.3 非空约束

非空约束一般为根据需求，在创建栏位时在字段名和类型后可加上 NOT NULL 表示该字段不可为空

2.4 唯一约束

唯一约束一般为根据需求，在创建栏位时在字段名和类型后可加上 UNIQUE 表示该字段内容唯一，该字段在表中不可有重复数据

2.5 自动增加

auto_increment：给某列设置自动增加，每次插入数据时不给这一列指定数据则会在前一条记录（或已存在的最大值）上自动加1

只有当列定义为主键/外键/唯一约束时，才能给其设置 auto_increment

2.6 默认值

创建表时，可以给列设置默认值，在列名后或在列名类型后加上 DEFAULT defaultValue

3 插入数据

格式：

-- 给所有列插入数据
insert into tableName values(propValue1,propValue2, ..., propValuen);

-- 给部分栏位插入数据
insert into tableName(propName1,propName3,propName5)
values(propValue1,propValue3,propValue5);

4 更新数据

格式：

update tableName set propName1 = newValue1,propName2 = newValue2
where CONDITION;

其中propName为表中的某个字段名，newValue表示需要更新的值

5 删除数据

格式：

delete from tableName where CONDITION;

其中如果没有condition条件，则会将表中的数据都删除，有condition则会根据condition删除指定数据

7 查询数据

格式：

--查询tableName表中的所有数据
select * from tableName;

--查询部分列
select propName1,propName2 from tableName;

--查询满足condition的数据
select * from tableName where CONDITION;