MySQL约束

MySQL约束

1. 概念

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

2. 作用

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

3. 分类

   1. 主键约束

      1. 概念

         • MySQL主键约束是一个列或者多个列的组合,其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行。

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

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

         • 主键约束的关键字是:primary key

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

      2. 语法

         • 添加单列主键。

           CREATE TABLE 表名(
             ……
           <字段名> <数据类型> primary key
             ……
           );

           或在定义字段之后再指定主键，语法格式如下:

           CREATE TABLE 表名(
             ……
           [constraint <约束名>] primary key(字段名)
           );

         • 添加多列联合主键。

           所谓的联合主键，就是这个主键是由一张表中多个字段组成的。

           1.当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。

           2.一张表只能有一个主键，联合主键也是一个主键

           CREATE TABLE 表名(
             ……
             primary key (字段1，字段2，..,字段n)
           );

         • 修改表结构添加主键。

           主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。

           CREATE TABLE 表名(
             ……
           );
           alter table <表名> add primary key (字段列表);

         • 删除主键。

           一个表中不需要主键约束时，就需要从表中将其删除.

           alter table <数据表名> drop primary key;

   2. 自增长约束

      1. 概念

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

         通过给字段添加auto_increment属性来实现主键自增长

      2. 语法

         1. 字段名 数据类型 primary key auto_increment

         2. 指定自增字段初始值

            如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。

            delete数据之后自动增长从断点开始，truncate数据之后自动增长从默认起始值（1）开始

            1. 创建表时指定

               CREATE TABLE t_user2 (
                 id INT PRIMARY KEY AUTO_INCREMENT,
                name VARCHAR(20)
               ) AUTO_INCREMENT = 100;

            2. 创建表之后指定

               CREATE TABLE t_user3 (
                 id INT PRIMARY KEY AUTO_INCREMENT,
                 name VARCHAR(20)
               );
               ALTER TABLE t_user3 auto_increment=200;

      3. 特点

         • 默认情况下，auto_increment的初始值是1，每新增一条记录，字段值自动加1。

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

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

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

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

   3. 非空约束

      1. 概念

         MySQL非空约束(not null）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

      2. 语法

         1. <字段名><数据类型> not null;

            CREATE TABLE t_user6(
              id INT
              name VARCHAR(20) NOT NULL,
              address VARCHAR(20) NOT NULL
            );

         2. alter table 表名 modify 字段 类型 not null;

            ALTER TABLE t_user7 MODIFY name VARCHAR(20) NOT  NULL;
            ALTER TABLE t_user7 MODIFY address VARCHAR(20) NOT  NULL;

         3. 删除非空约束

            ALTER TABLE t_user7 MODIFY name VARCHAR(20);
            ALTER TABLE t_user7 MODIFY address VARCHAR(20);

   4. 唯一约束

      1. 概念

         唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为id字段加上唯一性约束后，每条记录的id值都是唯一的，不能出现重复的情况。

         在mysql中，null和任何值都不相同，甚至和自己都不相同。

      2. 语法

         1. <字段名> <数据类型> unique

            CREATE TABLE t_user8(
              id int,
              name VARCHAR(20),
              phone_unmber varchar(20) unique
            );

         2. alter table 表名 add constraint 约束名 unique(列);

            CREATE TABLE t_user9(
              id int,
              name VARCHAR(20),
              phone_unmber varchar(20)
            );
            ALTER TABLE t_user9 CONSTRAINT unique_phone_num UNIQUE(phone_unmber);

         3. 删除唯一约束

            ALTER TABLE t_user9 drop INDEX unique_phone_num;

   5. 默认约束

      1. 概念

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

      2. 语法

         1. <字段名> <数据类型> default <默认值>;

            CREATE TABLE t_user10(
              id INT,
              name VARCHAR(20),
              address VARCHAR(20) DEFAULT('北京')
            );

         2. alter table 表名 modify 列名 类型 default 默认值;

            CREATE TABLE t_user10(
              id INT,
              name VARCHAR(20),
              address VARCHAR(20)
            );
            ALTER TABLE t_user10 MODIFY address VARCHAR(20) DEFAULT('深圳');

         3. 删除默认约束

            ALTER TABLE t_user10 MODIFY address VARCHAR(20) DEFAULT NULL;

   6. 零填充约束

      1. 概念

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

         2. zerofill默认为int(10)

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

      2. 语法

         1. 添加零填充约束

            CREATE TABLE t_user12(
                 id INT ZEROFILL,
                 name VARCHAR(20)
            );

         2. 删除

            ALTER TABLE t_user12 MODIFY id INT;