mysql中列的常用属性--约束

• 定义

  约束：constraint，实际上就是表中数据的限制条件

• 作用

  表在设计的时候加入约束的目的是为了保证表中记录的完整性和有效性，譬如用户列表中有些列的值不能为空，有些列的值（身份证号码）不能重复

• 常用的约束

  1、主键约束：primary key

  • mysql主键约束是一个列或者多个列的组合，其值能唯一地标识表中每一行，方便在RDBMS关系型数据库中快速查找。

  • 主键约束相当于：唯一约束+非空约束 ，主键约束列不允许重复，也不允许出现空值。

  • 每个表最多允许一个主键。

  • 当创建主键约束时，系统会默认在所在列和列组合上建立对应的唯一索引。

  • 语法：

    方式1：在定义字段时同时指定主键

    create table tablename(
    	<字段名> <数据类型> primary key
    );
    
    create table staff(
    	id int primary key,
    	name varchar(16),
    	department varchar(8),
    	salary double
        );
    

    方式2：在定义字段后再指定主键

    create table tablename(
    	<字段名> <数据类型> 
        ......
        [constraint <约束名>] primary key (字段名)
    );
    
    create table staff(
    	id int,
    	name varchar(16),
    	department varchar(8),
    	salary double,
        [constraint pk1] key(id)
        );
    

    方式3：通过alter关键字添加主键

    alter table tablename add primary key(字段名);
    

    联合主键只能在字段后指定：

    create table double_pri_demo(
    	id int,
    	s_id int,
    	[constraint upk] primary key(id,s_id)
    	);
    	
    insert into double_pri_demo values(1,1);
    insert into double_pri_demo values(1,2);
    insert into double_pri_demo values(2,1);
    insert into double_pri_demo values(1,1); ---报错，主键的值重复，联合主键的值不同同时相同
    insert into double_pri_demo values(null,1); ---报错，主键值不能为空
    

    删除主键：一张表中只有一个主键，因此删除主键是无需指定列，联合主键也是使用相同的方式删除

    alter table tablename drop primary key;
    

  2、自动增长约束：auto_increment

  • 定义：

    当主机被定义为自增后，这个主键的值就不需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键自动以相同的步长进行增长。

  • 特点：

    – 自动增长的列，一般配合主键使用，默认从1开始，每新增一条记录，字段值自动加1。

    – 一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，避免序号重复（即该字段为主键或主键的一部分）。

    – auto_increment约束的字段必须具备 NOT NULL属性。

    – auto_increment约束的字段只能是整数类型，如TINYINT、SMALLINT、INT、BIGINT等。

    – auto_increment约束的字段的最大值受该字段的数据类型约束，如达上限，auto_increment会失效。

  • 语法：

    create table auto_demo(
    	id int primary key auto_increment,
    	name varchar(16)
    	);
    	
    insert into auto_demo name values 'a先生','b先生','c女士'
    select * from auto_demo;
    

    

• 指定自增字段的 初始值：

  方式1：创建表时指定

  create table tablename(
  	id int primary key auto_increment,
      name varchar(16),
  	)auto_increment=1000;
  

  方式2：创建好表之后使用alter关键字指定

  create table tablename(
  	id int primary key auto_increment,
      name varchar(16),
  	);
  alter table tablename auto_increment=500;
  

• delete和truncate在删除自增列的变化

  – delete数据之后自增从断点开始（还是从删除前的最后一个值的基础上加1）

  – truncate数据之后自增从默认起始值开始（从默认值1的基础上加1）

3、非空约束（not null）–插入的值是否可以为空

• 定义：

  非空约束（not null）指字段的值不能为空。

  – null：可以为空，默认不写

  – not null：不能为空，如果插入时，某个非空约束的字段为空，会报错

• 语法

  方式1：创建表的时候指定

  create table null_demo(
  	id int primary key auto_increment,
  	name varchar(16) not null comment '姓名不能为空',
  	age tinyint(3) unsigned default 18 comment '默认值是18'
  	);
  	
  insert into null_demo values(null,null,null) ---报错，name不能为空
  

  方式2：使用modify关键字修改

  alter table tablename modify <column> <type> not null;
  

4、唯一约束：unique

• 特点：

  – unique key保证字段的值不能重复

  – 通常用在邮箱、手机号码等场景

• 语法:

  方式1：创建表时指定

  create table unique_demo(
  	id int auto_increment primary key,
  	mobile char(11) unique
  	);
  	
  insert into unique_demo set mobile=13300000000;
  insert into unique_demo set mobile=13300000000; ---再次插入已存在的手机号时会报错
  
  insert into unique_demo set mobile=null;
  insert into unique_demo set mobile=null; --唯一约束可以为空，Mysql中的null和任何值都不同，甚至和自己都不相同，所有可以插入多个null。
  

  方式2：通过add关键字

  alter table tablename add constraint 约束名 unique(column); --约束名是为了删除用
  

• 删除唯一约束:

  alter table tablename drop index column;
  alter table tablename drop index 约束名;
  

5、默认约束：default

• 特点：

  – 默认约束用来指定某列的默认值

  – 一般和not null搭档

• 语法：

  方式1：

  create table tablename(
  	id int primary key auto_increment,
  	name varchar(16) default 'admin'
  );
  

  方式2：

  alter table tablename modify column type default 默认值;
  

6、零填充约束：zerofill

• 特点：

  – 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0

  – zerofill默认为int(10)

  – 当使用zerofill时，默认会自动加unsigned（无符号）属性，使用后数值范围是原来的2倍，如有符号时-128~+127，无符号时为0~256。

• 语法：

  create table tablename(
  	id int zerofill,
  	name varchar(16)
  );
  

• 删除zerofill

  alter table tablename modify id int;
  

7、comment：字段说明，一般是用来对相应字段进行说明，通常是给开发者看的

create table comment_demo(
	ctime datetime comment '这个字段表示创建日期'
	);
	
show creat table comment_demo; ---可以查看comment的内容

8、sql注释

• 单行注释：-- 这时单行注释

• /* 这是多行注释 */

• mysql独有的注释：# 我也是单行注释；#注释在其他sql语言中没有