SQL 字段类型属性

NULL属性

• Null/Not Null 属性来限制数据是否可以为空

• 默认为 Null

create table t_01
{
  username varchar(10) not null
  --...
}

Default属性

• 设置默认值 表字段的时候给定默认数据，在后续字段操作（数据新增）的时候系统没有检测到字段有数据的时候自动使用的值

• 默认值通常设计的是字段容易出现的数据

  • 一般字段的默认值 默认是Null

• 默认值触发

  • 在系统进行数据插入时自动检测触发

  • 主动使用default关键字触发默认值

#用户开户：银行卡账号、身份证号码、姓名、账户余额
create table test(
   account varchar(19) not null,
   id_card varchar(19) default 'nullptr' not null,
    --等价于 
    --id_card varchar(19) default 'nullptr'
    name varchar(19) not null,
    money decimal(6,2) default 0.00
)charset utf8;
​

primary key 主键

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

• 主键在一张表中只能有一个

• 主键的另外一个特性是能够提升主键字段作为查询条件的效率（索引）

• 主键不能为空：Not Null（默认）

• 逻辑主键：数据没有具体业务意义，纯粹是一种数值数据

  • 逻辑主键通常是整数：int

  • 逻辑主键目的是方便检索和数据安全（不暴露数据真实信息）

• 复合主键：多个字段共同组成不能重复的数据

  • 这个多个字段，如果 都相同 就不可以

  • primary key(字段1,字段2,…字段N)

  • 联合主键使用不多，一般也不会超过2个字段

  • primary key(id,name)；

create table test(
 id int unsigned primary key,
 --如果有id2 int unsigned primary key, error 主键是唯一的 
 name varchar(10) not null
);
​
create table test1(
 id int unsigned primary key,
 name varchar(10) not null,
 primary key(id,name) 
); #不可以主键的唯一性
​
create table test1(
 id int unsigned，
 name varchar(10) not null,
 primary key(id,name)  
);#可以的，因为只有一个 复合主键
​

主键管理

在创建表并且已经有数据后的维护

• 删除主键

  • 删除主键：主键只有一个，所以删除语法也比较特殊

  • alter table t_26 drop primary key;

• 追加主键

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

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

• 修改主键（先删除后新增）

auto_increment自增长属性

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

• 自增长只能是整数类型，而且对应的字段必须是一个索引（通常逻辑主键）

• 一张表只能有一个自动增长

• 自增长一般是配合逻辑主键实现自动增长

  • 整型字段

  • 存在索引（primary key 主键 / unique key 唯一键）

• 自增长数据可以理解为一种默认值，如果主动给值，那么自动增长不会触发

• 自增长由两个变量控制

  • 初始值：auto_increment_offset，默认是1

  • 步长：auto_increment_increment，默认值也是1

  • 查看自增长控制：show variables like 'auto_increment%';

create table t_28(
  id int unsigned primary key auto_increment
);

自增长管理

在某些特殊使用下，需要自增长按照需求实现

• 修改表中自增长的值：让下次自增长按照指定值开始

alter table t_28 auto_increment = 50;

注意：奇数会保留原值，偶数会自动加1（可能出现的情况）

• 修改自增长控制：调整自增长的步长

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

unique key唯一键

• 一个表中可以有多个唯一键

• 唯一键与主键的区别在于 唯一键允许数据为Null（而且Null的数量不限）

• 唯一键与主键一样，可以提升字段数据当做条件查询的效率（索引）

• 复合唯一键：多个字段共同组成

  • unique key(字段1,字段2,…字段N)

  • 一般不会出现，最多2个字段组成

​
create table test123(
   id int unsigned primary key auto_increment comment 'id',
   name varchar(20) not null unique key comment '名字',
   course varchar(20) not null comment '课程',
   score decimal(5,2)  default 0.00 comment '分数',
   unique key(id,name) #可以是可以但是没必要
);

foreign key 外键约束

-- 将主表的 column 约束在 从表某个 column 的范围里面
-- 如果这张表的 某个id 被某个从表使用那么就不可以对其做任何修改
create table  student_id(
    id int unsigned primary key auto_increment,    
);
​
create table student(
    id int unsigned,
    name varchar(20) not null,
    age int not null,
    -- 将id 的范围 限制在 student_id 的 id 内
    foreign key(id) references student_id(id),
    constraint mychk_age check (age >= 0 and age <= 200)
);
​

comment属性

描述：comment，是用文字描述字段的作用的

• comment代表的内容是对字段的描述

  • 方便以后自己了解字段的作用

  • 方便团队了解字段的作用

• 描述如果涉及到字符集（中文）一定要在创建表之前 设置好客户端字符集（否则会出现描述乱码）

数据库记录长度

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

• 记录长度为表中所有字段预计占用的长度之和

• 所有字段只有允许Null存在，系统就会预留一个字节存储Null（多个Null也只要一个就好）

• 因为MySQL记录长度的存在，varchar永远达不到理论长度

  • GBK存储：65535（字符） * 2 + 2 = 131072（字节）

  • UTF8存储：65535（字符） * 3 + 2 = 196607（字节）

• 一般数据长度超过255个字符都会使用 text/blob 进行存储（数据存储不占用记录长度）