最全 SQL 字段类型(4种)、属性(6种)总结

字段类型

字段类型:MySQL中用来规定实际存储的数据格式

  • 字段类型在定义表结构时设定
  • 设定好字段类型后,插入数据时必须与字段类型对应,否则数据错误
  • 字段类型的作用就是强制规范录入的数据格式
    • 规范数据的格式
    • 保证数据的有效性
  • MySQL有四大数据类型
    • 整数类型:只能存储整数
    • 小数类型:可以存储有效数值
    • 字符串类型:存储字符串数据
    • 时间日期类型:存储时间日期格式数据

整数类型

整数类型:有效的整数数据

  • MySQL中为了数据空间的有效使用,设定了五种整数类型
    • 迷你整型:tinyint,使用1个字节存储整数,最多存储256个整数(-128~127)
    • 短整型:smallint,使用2个字节存储整数
    • 中整型:mediumint,使用3个字节存储整数
    • 标准整型:int,使用4个字节存储整数
    • 大整型:bigint,使用8个字节存储
  • 数值型存储在MySQL中分为有符号(有负数)和无符号(纯正数)需要unsigned 修饰整型

示例:设计一个表记录个人信息:年龄、头发数量

# 年龄:没有负数,正常年龄也不超过200岁,迷你整型无符号即可
# 头发数量:没有负数,大概在几百万根,所以标准整型无符号即可
create table t_7(
	age tinyint unsigned, # unsigned修饰整数,表示无符号(从0开始)
    haircount int unsigned
)charset utf8;

显示宽度:int(L),整数在数据库中显示的符号(数字+符号)个数

  • 显示宽度一般是类型能表示的最大值对应的数字个数(通过desc查看表字段显示)
  • 显示宽度包含符号(如果允许为负数,-负号会增加一个宽度)
  • 显示宽度可以主动控制:创建字段时加括号确定
  • 显示宽度不会影响类型能表示的最大数值
  • 可以通过zerofill让不够宽度的数值补充到对应宽度:在字段类型后使用zerofill

小数类型(2种)

1、浮点型float / double,存储不是特别精确的数值数据

  • 浮点数又称之为精度数据,分为两种
    • 单精度:float,使用4个字节存储,精度范围为6-7位有效数字
    • 双精度:double,使用8个字节存储,精度范围为14-15位有效数字
  • 浮点数超过精度范围会自动进行四舍五入
  • 精度可以指定整数和小数部分
    • 默认不指定,整数部分不超过最大值,小数部分保留2位
    • 可以指定:float/double(总长度,小数部分长度)
  • 可以使用科学计数法插入数据:AEB,A * 10 ^ B
  • 因为浮点数会自动四舍五入,所以不要使用浮点数来存储对精度要求较高的数值

示例:记录商品的价格

# 商品名字字符串
# 商品价格一般都允许带小数
create table t_11(
	goods_name varchar(20),
    goods_price float
)charset utf8;
insert into t_11 values('Nokia3310',199.99);
insert into t_11 values('Nokia6100',1999.9999);

2、定点型decimal,能够保证精度的小数

  • 定点数的存储模式不是固定长度,所以数据越大占用的存储空间越长
  • 每9个数字使用4个字节存储
  • 定点型可以指定整数部分长度和小数部分长度
    • 默认不指定,10位有效整数,0位小数
    • 可以指定:decimal(有效数位,小数部分数位)
    • 有效数位不超过65个
  • 数据规范
    • 整数部分超出报错
    • 小数部分超出四舍五入

示例:记录个人资产情况:资产和负债

# 资产和负债应该都是精确的,小数部分可以到分
create table t_12(
    money decimal(14,2),
    bet decimal(10,2)
)charset utf8;

insert into t_12 values(1111111111.12,1111111.999);
insert into t_12 values(1111111111.12,99999999.999); # 错误:进位导致正数部分超过指定范围

字符串类型(5种)

1、定长型char(L),指定固定长度的存储空间存储字符串

  • 定长是指定存储长度
  • 定长的长度是字符而不是字节
    • L的最大值是255
    • 实际存储空间:L字符数 * 字符集对应字节数
  • 定长里存储的数据不能超过指定长度,但是可以小于指定长度
  • 字符串数据使用单引号或者双引号包裹
  • 定长的访问效率较高,但是空间利用率较低

示例:记录个人信息:身份证信息和手机号码

# 身份证为固定长度18位(数字)
# 手机号码是11位固定长度(数字)
create table t_13(
	id_number char(18),
    phone_number char(11)
)charset utf8;
insert into t_13 values('440111999912120304','13512345678');

2、变长型varchar(L),根据实际存储的数据变化存储空间

  • 变长型的存储空间是由实际存储数据决定的
  • 变长型的L也是指字符而不是字节
    • L指定的是最大存储的数据长度
    • L最大值理论是65535
    • 变长需要额外产生1-2个字节,用来记录实际数据的长度
      • 数据长度小于256个,多1个字节
      • 数据长度大于256个,多2个字节
    • 实际存储空间:实际字符数 * 字符集对应字节数 + 记录长度
  • 变长数据不能超过定义的最大长度
  • 变长字符串在读取时需要进行长度计算,所以效率没有定长字符串高
  • 变长字符串能够更好的利用存储空间

示例:记录个人信息:用户名、密码、姓名、身份证

# 用户名不确定长度,最长不超过50个字符
# 密码不确定长度,最长超过15个字符
# 姓名不确定长度,最长不超过10个字符
# 身份证固定长度,18个字符
create table t_14(
	username varchar(50),
    password varchar(15),
    name varchar(10),
    id_number char(18)
)charset utf8;

insert into t_14 values('username','password','name','444111999912121111');

3、文本字符串text/blob,专门用来存储较长的文本

  • 文本字符串通常在超过255个字符时使用
  • 文本字符串包含两大类
    • text:普通字符
      • tinytext:迷你文本,不超过2 ^ 8 -1个字符
      • text:普通文本,不超过 2 ^ 16 - 1个字符
      • mediumtext:中型文本,不超过 2 ^ 24 - 1 个字符
      • longtext:长文本,不超过 2 ^ 32 - 1 个字符(4G)
    • blob:二进制字符(与text类似)
      • tinyblob
      • blob
      • mediumblob
      • longblob
  • 文本字符串会自动根据文本长度选择适合的具体类型
  • 一般在文本超过255个字符时,都会使用text(blob现在极少使用)

示例:记录新闻信息:标题、作者和内容

# 标题一般不会超过50个字符,varchar
# 作者一般不会超过10个字符:varchar
# 内容通常都很长,使用text
create table t_15(
	author varchar(10),
    title varchar(50),
    content text
)charset utf8;

insert into t_15 values('佚名','给联合国的一封信','给联合国的一封信...');

4、枚举型enum, 一种映射存储方式,以较小的空间存储较多的数据

  • 枚举是在定义时确定可能出现的可能,而后数据只能出现定义时其中的一种的数据类型
  • 枚举类似一种单选框
  • 枚举使用1-2个字节存储,最多可以设计65535个选项
  • 枚举实际存储是使用数值,映射对应的元素数据,从1开始
  • 枚举语法:enum(元素1,元素2,...元素N)
  • 使用枚举的作用:
    • 规范数据模型
    • 优化存储空间

示例:记录人群类型:小朋友、少年、青年、中年、老年,每个人实际只属于一种类别

# 要保证未来数据只能出现在某种可能中,所以要先列出来,可以使用enum
create table t_16(
	type enum('小朋友','少年','青年','中年','老年')
)charset utf8;

insert into t_16 values('少年');
insert into t_16 values('仙人');	# 不存在的数据不能插入

枚举定义原理:

枚举数据映射值
数据11
数据22
数据NN(小于65535)

数据存储(读取反过来):

指令开始
插入数据
读取映射关系
元素==数值
数值==数值
数值存储到字段
结束

5、集合型set,一种映射存储方式,以较小的空间存储较多的数据

  • 集合是在定义时确定可能出现的元素进行穷举,而后数据只能出现定义时其中的元素(可以是多个
  • 集合类似一种多选框
  • 集合使用1-8个字节存储数据,最多可以设计64个元素
  • 集合实际存储是使用数值(二进制位),映射对应的元素数据,每个元素对应一个比特位
    • 数据存在:对应位为 1
    • 数据不存在:对应位为 0
  • 集合语法:set(元素1,元素2,...元素N)
  • 使用集合的作用:
    • 规范数据模型
    • 优化存储空间

示例:记录个人的球类爱好,有篮球、足球、羽毛球、网球、乒乓球、排球、台球、冰球

# 爱好可以是多种,并非固定的,但是只能从规定的类型中选择
create table t_17(
	hobby set('足球','篮球','羽毛球','网球','乒乓球','排球','台球','冰球')
)charset utf8;

insert into t_17 values('足球');
insert into t_17 values('冰球,台球,篮球');

集合定义原理:

集合数据映射位
数据100000001
数据200000010
数据810000000

数据存储(读取反过来):

指令开始
插入数据
读取映射关系
元素选中==位值为1
元素未选中==位值为0
转化成十进制存储
结束

时间日期类型(5种)

1、年year,MySQL中用来存储年份的类型

  • MySQL中使用1个字节存储年份
  • year能够表示的范围是1901-2155年(256年)
    • year的特殊值是:0000
  • year允许用户使用两种方式设计(效果一样)
    • year
    • year(4)
  • 因为year字段表示的范围有限,所以通常会使用字符串来存储(牺牲空间换安全)

示例:记录个人的出生年份

create table t_18(
	y1 year,
    y2 year(4)
)charset utf8;
insert into t_18 values(1901,2155);

2、时间戳timestamp,基于格林威治时间的时间记录

  • MySQL中时间戳表现形式不是秒数,而是年月日时分秒格式
    • YYYY-MM-DD HH:II::SS
    • YYYYMMDDHHIISS
  • timestamp使用4个字节存储
    • 表示范围是 1971年1月1日0时0分0秒-2155年12月31日23是59分59秒
    • timestamp可以使用 0000-00-00 00:00:00
  • 所对应的记录不论哪个字段被更新,该字段都会更新到当前时间
  • 但在MySQL8中需要主动使用on update current_timestamp才会自动更新

示例:记录商品库存的最后更新时间

create table t_19(
	goods_name varchar(10),
    goods_inventory int unsigned,
    change_time timestamp
)charset utf8;

insert into t_19 values('Nokia3110',100,'1971-01-01 00:00:00');
insert into t_19 values('Nokia7100',100,'19710101000000');

注意:在MySQL8以后,取消了timestamp的默认自动更新,如果需要使用,需要额外使用属性: on update current_timestamp

alter table t_19 add c_time timestamp on update current_timestamp;

update t_19 set goods_inventory = 80;

3、日期date,用来记录年月日信息

  • 使用3个字节存储数据
  • 存储日期的格式为:YYYY-MM-DD
  • 存储的范围跨度很大,存储区间是1000 - 9999年:1001-01-01~9999-12-31

示例:记录个人生日

create table t_20(
	name varchar(10),
    birth date
)charset utf8;

insert into t_20 values('Jim','2000-12-12');
insert into t_20 values('Tom','10011212');

4、日期时间datetime,用来综合存储日期和时间

  • 使用8个字节存储数据
  • 存储格式为:YYYY-MM-DD HH:II:SS
  • 存储区间为:1000-01-01 00:00:00 到9999-12-31 23:59:59

示例:记录个人具体的出生时间

create table t_21(
	name varchar(10),
    birth datetime
)charset utf8;

insert into t_21 values('Jim','2000-12-12 12:12:12');
insert into t_21 values('Tom','10011212182323');

5、时间time,用来记录时间或者时间段

  • 使用3个字节存储数据
  • 数据范围是 -838:59:59 - 838:59:59
  • 数据插入的格式分为两种
    • 时间格式:[H]HH:II:SS([ ]表示可以没有)
    • 时间段格式:D HH:II:SS(D表示天)
    • time类型通常被用来做时间段计算:如多少天后的什么时间点(可以理解为过期检查)

示例:记录用户登录的具体时间

# 具体登录时间可以使用时间戳(包含年月日时分秒信息)
# 也可以时间datetime格式,或者date+time双字段格式(具体后面学习范式时会知道该怎么用)
create table t_22(
	login_time1 int unsigned,
    login_time2 datetime,
    login_date date,
    login_time3 time
)charset utf8;

insert into t_22 values(12345678,'2000-12-12 12:12:12','2000-12-12','12:12:12');
insert into t_22 values(1234567,'2000-12-12 12:12:12','2000-12-12','3 12:12:12');

属性

属性:建立在字段类型之后,对字段除类型之外的其他约束

  • 属性是在定义表字段的时候针对每个字段进行属性设定,是MySQL用来增加字段规范和约束
  • 设定好的属性可以通过查看表字段desc进行查看
  • 数据在进行增删改(写)操作时需要在满足字段类型属性的要求
  • 用好属性能够提升数据的有效性,方便未来进行数据操作和数据分析(数据真实性和有效性)

查看表属性:desc 表名;

desc t_1;
# Field:字段名字
# Type:数据类型
# Null:是否为空(属性)
# Key:索引类型(属性)
# Default:默认值(属性)
# Extra:额外属性

NULL属性

NULL:数据是否允许为空

  • Null/Not Null属性 是用来限定数据是否为Null值

  • 默认情况下数据是允许为Null的,不为空设计:Not Null

  • 一般有效的数据都必须设定为Not Null来保证数据的有效性,数据为空一般不具备运算和分析价值

示例:用户信息表:用户名、密码、姓名、年龄、注册时间

create table t_23(
	username varchar(50) not null,
    password char(32) not null,
    name varchar(20),
    age tinyint unsigned,
    reg_time int unsigned not null
)charset utf8;

Default属性

默认值default,在设计表字段的时候给定默认数据,在后续字段操作(数据新增)的时候系统没有检测到字段有数据的时候自动使用的值

  • 默认值在字段设置的时候使用(默认值需要满足数据类型规范)
  • 默认值通常设计的是字段容易出现的数据
    • 一般字段的默认值 默认是Null
  • 默认值触发
    • 在系统进行数据插入时自动检测触发
    • 主动使用default关键字触发默认值

示例:用户开户:银行卡账号、身份证号码、姓名、账户余额

create table t_24(
	account varchar(19) not null,
    id_card char(18) not null,
    name varchar(20) not null,
    money decimal(16,2) default 0.00 not null
)charset utf8;

主键

主键primary key,用来保证整张表中对应的字段永远不会出现重复数据(唯一性

  • 主键在一张表中只能有一个
  • 主键的另外一个特性是能够提升主键字段作为查询条件的效率(索引)
  • 主键不能为空:Not Null(默认)
  • 逻辑主键:数据没有具体业务意义,纯粹是一种数值数据
    • 逻辑主键通常是整数:int
    • 逻辑主键目的是方便检索和数据安全(不暴露数据真实信息)
  • 复合主键:多个字段共同组成不能重复的数据
    • primary key(字段1,字段2,…字段N)
    • 联合主键使用不多,一般也不会超过2个字段

示例:银行账户信息:账户、姓名、余额

# 银行账户具有唯一性,不能重复,也不允许为空
create table t_25(
    account varchar(17) primary key,
    name varchar(20) not null,
    money decimal(16,2) not null default 0.00
)charset utf8;

# 复合主键
create table t_26(
	account varchar(17),
    name varchar(20),
    money decimal(16,2) not null default 0.00,
    primary key(account,name)
)charset utf8;

# 一般使用逻辑主键
create table t_27(
	id int unsigned primary key,
    account varchar(17) not null,
    name varchar(20) not null,
    money decimal(16,2) not null default 0.00
)charset utf8;

主键管理:在创建表并且已经有数据后的维护

  • 删除主键
  • 追加主键
  • 修改主键(先删除后新增)

1、删除主键:主键只有一个,所以删除语法也比较特殊

alter table t_26 drop primary key;

2、后期新增主键:如果是针对业务主键需要保证字段数据没有Null数据且没有数据重复(一般主键都会在表创建时维护好)

alter table t_26 add primary key(account,name);

自增长属性

自增长auto_increment,被修饰的字段在新增时,自动增长数据

  • 自增长只能是整数类型,而且对应的字段必须是一个索引(通常逻辑主键)
  • 一张表只能有一个自动增长
  • 自增长一般是配合逻辑主键实现自动增长
    • 整型字段
    • 存在索引(主键)
  • 自增长数据可以理解为一种默认值,如果主动给值,那么自动增长不会触发
  • 自增长由两个变量控制
    • 初始值:auto_increment_offset,默认是1
    • 步长:auto_increment_increment,默认值也是1
    • 查看自增长控制:show variables like 'auto_increment%';

示例:记录学生信息:学号和姓名

# 学生信息:学号自动增长
create table t_28(
    id int primary key auto_increment,
	stu_no int(8) zerofill not null,
    stu_name varchar(20) not null
)charset utf8;

自增长管理:在某些特殊使用下,需要自增长按照需求实现

  • 修改表中自增长的值:让下次自增长按照指定值开始
  • 修改自增长控制:调整自增长的变化

1、修改表中自增长的值:跳过一些值,直接从下次开始按照新的目标值出现

alter table t_28 auto_increment = 50;

注意:奇数会保留原值,偶数会自动加1(可能出现的情况)

2、修改自增长控制:步长和起始值(修改针对的是整个数据库,而非单张表)

set auto_increment_increment = 2;	# 当前用户当前连接有效(局部)
set @auto_increment_increment = 2;	# 所有用户一直有效(全局)

唯一键

唯一键unique key,用来维护数据的唯一性

  • 一个表中可以有多个唯一键
  • 唯一键与主键的区别在于 唯一键允许数据为Null(而且Null的数量不限)
  • 唯一键与主键一样,可以提升字段数据当做条件查询的效率(索引)
  • 复合唯一键:多个字段共同组成
    • unique key(字段1,字段2,…字段N)
    • 一般不会出现,最多2个字段组成

示例:学生成绩表:一个学生只能有一个学科成绩,但是可以有多个学科

# 学号和学科编号共同组成唯一
create table t_30(
	id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2),
    unique key(stu_name,course)
)charset utf8;

insert into t_30 values(null,'Jim','Math',50);
insert into t_30 values(null,'Jim','English',80);

唯一键管理:在表创建后对唯一键的管理

  • 删除唯一键相对麻烦,一张表中不止一个唯一键

  • 新增唯一键要保证字段里的数据具有唯一性

  • 删除唯一键:alter table 表名 drop index 唯一键名字;

  • 新增唯一键: alter table 表名 add unique key(字段列表);

1、删除表中已有的唯一键

alter table t_30 drop index stu_name;

2、追加唯一键

alter table t_30 add unique key stu_course (stu_name,course);

comment属性

描述comment,是用文字描述字段的作用的

  • comment代表的内容是对字段的描述
    • 方便以后自己了解字段的作用
    • 方便团队了解字段的作用
  • 描述如果涉及到字符集(中文)一定要在创建表之前 设置好客户端字符集(否则会出现描述乱码)

示例:学生成绩表

# 学生成绩表中通常是存储学生学号
# 学科通常也是学科代码
create table t_31(
	id int primary key auto_increment,
    stu_no varchar(10) not null comment '学号',
    course_no varchar(10) not null comment '课程号',
    score decimal(5,2) comment '考试成绩',
    unique key stu_course (stu_no,course_no) comment '学号和课程号组成唯一键'
)charset utf8;

数据库记录长度

数据库记录长度:MySQL中规定一条记录所占用的存储长度最长不超过65535个字节

  • 记录长度为表中所有字段预计占用的长度之和
  • 所有字段只有允许Null存在,系统就会预留一个字节存储Null(多个Null也只要一个就好)
  • 因为MySQL记录长度的存在,varchar永远达不到理论长度
    • GBK存储:65535(字符) * 2 + 2 = 131072(字节)
    • UTF8存储:65535(字符) * 3 + 2 = 196607(字节)
  • 一般数据长度超过255个字符都会使用 text/blob 进行存储(数据存储不占用记录长度)

1、GBK表能存储的最大varchar字符串长度

create table t_32(
	content varchar(65535)
)charset gbk;	# 错误

create table t_32(
	content varchar(32767)
)charset gbk;	# 错误

create table t_32(
	content varchar(32766)
)charset gbk;	

2、UTF8表能存储的最大varchar字符串长度

create table t_33(
	content varchar(65535)
)charset utf8;	# 错误

create table t_33(
	content varchar(21844)
)charset utf8;

3、Null也要占用一个字节

create table t_34(
    id tinyint,
	content varchar(21844)
)charset utf8;	# 错误

create table t_34(
    id tinyint not null,
	content varchar(21844) not null
)charset utf8;
### 回答1: PostgreSQL 可以通过使用内置函数jsonb_set来更新JSON类型字段的指定属性,例如:UPDATE table SET json_column = jsonb_set(json_column, '{key1, key2}', '"value"') WHERE id = 1; ### 回答2: 对于PostgreSQL中的JSON类型字段,可以使用`UPDATE`语句来更新指定属性。 首先,假设我们有一个表`my_table`,其中包含一个名为`data`的JSON类型字段。 要更新`data`字段中的特定属性,我们可以使用`jsonb_set`函数来实现。 例如,假设我们想要更新`data`字段中的`name`属性为新的值`"John"`,可以使用以下语句: ```sql UPDATE my_table SET data = jsonb_set(data, '{name}', '"John"') WHERE <条件>; ``` 其中,`jsonb_set`函数的第一个参数是要更新的JSON对象,第二个参数是要更新的属性路径,第三个参数是新的属性值。 如果`data`字段是JSONB类型而不是JSON类型,则可以使用`jsonb_set`函数。如果`data`字段是JSON类型,可以使用`json_set`函数,用法相同。 如果要更新多个属性,可以在`jsonb_set`函数中传递多个属性路径和对应的新值。 注意,`WHERE`子句用于指定更新的行,以满足特定的条件。可以根据实际情况进行调整。 ### 回答3: 在pgsql中,可以使用`UPDATE`语句来更新json类型字段的指定属性。 首先,使用`UPDATE`语句选择要更新的表,并使用`SET`关键字指定要更新的字段和更新后的值。 然后,在设置新值时,使用`jsonb_set()`函数来更新jsonb类型字段。该函数接受三个参数:目标jsonb字段属性路径和新的值。 属性路径可以通过使用`->`运算符来指定具体的属性。例如,要更新名为`data`的jsonb字段属性`name`,可以使用以下语法: ```sql UPDATE 表名 SET jsonb字段名 = jsonb_set(jsonb字段名, '{属性路径}', 新值) WHERE 条件 ``` 请注意,在属性路径中,`'{属性路径}'`应该是一个以大括号包围的文本数组,数组中的每个元素表示路径上的一个属性。例如,`'{属性1,属性2}'`表示`属性1`下的`属性2`。 下面是一个示例,假设有一个名为`users`的表,其中有一个名为`info`的jsonb字段,我们要更新该字段的`name`属性: ```sql UPDATE users SET info = jsonb_set(info, '{name}', '"新的名字"') WHERE id = 1; ``` 这将更新`id`为1的行的`info`字段中的`name`属性为`新的名字`。 总结来说,要在pgsql中更新json类型字段的指定属性,可以使用`jsonb_set()`函数和`->`运算符来更新字段属性路径,并使用`UPDATE`语句来执行更新操作。
评论 9
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值