完整性约束条件

主键

• 习惯上 使用无实际意义的字段作为主键

    use db_imist;

    -- 测试主键;

    CREATE TABLE test_prinary_key(
      id INT UNSIGNED PRIMARY KEY, -- 设置为主键不允许许为null
      username VARCHAR(20)
    );

    desc test_prinary_key;

    INSERT test_prinary_key(id,username) VALUES(1,'imist'); -- 如果不设置主键会报错的

    INSERT test_prinary_key(username) VALUES('tom'); -- 如果不设置主键会报错的 `Field 'id' doesn't have a default value`

    INSERT test_prinary_key(id,username) VALUES(1,'tom'); -- 如果主键重复会报错的 'Duplicate entry '1' for key 'PRIMARY''

    -- 在下方指定主键也是可以的，这种情况适合于复合主键
    -- 一张表中多主键是不被允许的，复合主键的使用两个或者多个字段共同定义主键，当两条记录同时相同，才代表同一条记录

    CREATE TABLE test_prinary_key2(
      id INT UNSIGNED  PRIMARY KEY, -- 设置为主键不允许许为null
      courceId INT UNSIGNED  PRIMARY KEY,
      username VARCHAR(20)
    ); -- 多主键不被允许

    -- DROP TABLE  test_prinary_key3;

    -- 复合主键
    CREATE TABLE test_prinary_key3(
      id INT UNSIGNED,
      courceId VARCHAR(20),
      username VARCHAR(20),
      email VARCHAR(50),
      PRIMARY KEY (id,courceId)
    );
    -- primary key 1-a ,两个字段构成唯一值即可,且单独一字段可以重复,两个字段联合快速定位一条数据
    INSERT test_prinary_key3(id, courceId, username, email)  VALUES(1,'a','imist','az460024285@qq.com') ;
    -- primary key 1-b
    INSERT test_prinary_key3(id, courceId, username, email)  VALUES(1,'b','imist','az460024285@qq.com') ;
    -- primary key 2-a
    INSERT test_prinary_key3(id, courceId, username, email)  VALUES(2,'a','imist','az460024285@qq.com') ;
    -- 复合主键重复 ,数据无法插入 primary key1-a `Duplicate entry '1-a' for key 'PRIMARY'`
    INSERT test_prinary_key3(id, courceId, username, email)  VALUES(1,'a','imist','az460024285@qq.com') ;

    select * FROM test_prinary_key3;

    -- 测试 auto_increment 自动增长，从1开始 ,一般配合主键索引使用
    -- 不使用主键单独使用自增长 `Incorrect table definition; there can be only one auto column and it must be defined as a key`
    CREATE TABLE test_auto_increment(
      id INT UNSIGNED AUTO_INCREMENT,
      username VARCHAR(20)
    );

    CREATE TABLE test_auto_increment(
      id INT UNSIGNED KEY AUTO_INCREMENT,-- 类型为int的无符号数值 ,并且是自增长的主键
      username VARCHAR(20)
    );
    DESC test_auto_increment;

    INSERT test_auto_increment(username) VALUES ('iMist');
    INSERT test_auto_increment(username) VALUES ('Tom');
    INSERT test_auto_increment(username) VALUES ('Qiang');
    INSERT test_auto_increment(username) VALUES ('Gang');
    -- 这种自增长的情况下，没有指定id的话 就是自增长的,已有编号的最大值+1
    SHOW CREATE TABLE test_auto_increment;
    INSERT test_auto_increment(id,username) VALUES (100,'小雾');
    INSERT test_auto_increment(username) VALUES ('小赖');
    -- 若是指定了id ,就从已有id的最大值开始，可以跳跃，但是不可以重复
    INSERT test_auto_increment(id,username) VALUES (NULL ,'小人'); -- Null的情况下可以实现自增长
    INSERT test_auto_increment(id,username) VALUES (DEFAULT ,'小可'); -- Default也可以自增长
    INSERT test_auto_increment(id,username) VALUES ('' ,'小可'); -- 在idea的数据库工具,也可以自增长，MySql命令行没有试过

    SHOW CREATE TABLE test_auto_increment;

    SELECT * FROM test_auto_increment;

    -- 测试NOT NULL
    CREATE TABLE test_not_null(
      a VARCHAR(20),
      b VARCHAR(20) NOT NULL

    );
    desc test_not_null;
    Show CREATE TABLE test_not_null;

    INSERT test_not_null(a,b) VALUES ('',''); -- 这种情况可以被允许,因为不为null,而是空字符串;
    INSERT test_not_null(a,b) VALUES (null ,null ); -- b字段不可以为null `Column 'b' cannot be null`
    INSERT test_not_null(a,b) VALUES (null ,'' ); -- 成功插入一条数据
    INSERT test_not_null(a,b) VALUES (null ,'abc' ); -- 成功插入一条数据
    INSERT test_not_null(a) VALUES ('abc'); -- `Field 'b' doesn't have a default value`

    SELECT * FROM test_not_null;

    -- 测试默认值
    -- DEFAULT一般和NOT NULL 配合使用，若是字段不可以为空那么可以给一个DEFAULT ，若是字段可以为null 那么就默认为null了

    CREATE TABLE test_default(
      id INT UNSIGNED AUTO_INCREMENT KEY ,
      username VARCHAR(20) NOT NULL ,
      age TINYINT UNSIGNED DEFAULT 18,
      email VARCHAR(50) NOT NULL DEFAULT '462541258@qq.com'
    );
    DESC test_default;

    INSERT test_default (username) VALUES('iMist'); -- 主键自增长 age 和email采用了默认值
    INSERT test_default (username, age, email) VALUES ('Tom',20,'4600202585@qq.com'); -- 插入一条记录为username age ,email赋值
    INSERT test_default (username, age, email) VALUES ('jony',null ,'4600202585@qq.com');-- 插入一条记录为username ,email赋值 age设置null
    INSERT test_default (username, age, email) VALUES ('jony',20 ,null );-- 虽然有默认值 ，但是对设置了NOT NULL的字段设置为null就会报错的  `Column 'email' cannot be null`
    INSERT test_default (username, age, email) VALUES ('jony',20 ,DEFAULT ); -- 若是该字段设置了默认值，就使用默认值为该字段赋值
    INSERT test_default (username, age, email) VALUES ('jony',20 ,'' ); -- 有默认值的情况下赋值''字符 插入''字符串
    INSERT test_default (username, age, email) VALUES (DEFAULT ,20 ,'4600202585@qq.com' );
    -- 若是该字段没有设置默认值的情况下使用了Default那么就会报错 `Field 'username' doesn't have a default value`

    SELECT * FROM test_default;

    -- 测试枚举类型的默认值
    CREATE TABLE test_default1(
      id INT UNSIGNED AUTO_INCREMENT KEY ,
      sex ENUM('a','b','c') NOT NULL DEFAULT 'a'
      -- sex ENUM('a','b','c') NOT NULL DEFAULT 'd' 此条语句不合法，因为枚举类型的默认值必须在枚举数据集中
    );
    desc test_default1;

    INSERT test_default1(sex) VALUES (null ); -- 不可以为null ` Column 'sex' cannot be null`
    INSERT test_default1(sex) VALUES (''); -- 数据不合法，因为默认值不在枚举类型定义的数据集之内`Data truncated for column 'sex' at row 1`
    INSERT test_default1(sex) VALUES (DEFAULT ); -- 插入一条记录，sex字段为默认值
    INSERT test_default1(id) VALUES (3); -- 插入一条记录，sex字段没有设置，采用默认值

    SELECT * FROM  test_default1;

    -- UNIQUE KEY 唯一性索引,可以有多个， 和主键类似，值不能重复，但是可以有多个Null值

    `</pre>

    ### 唯一约束

    <pre>`   -- 测试UNIQUE KEY 唯一约束
       use db_imist;
       CREATE TABLE test_unique(
         id INT UNSIGNED AUTO_INCREMENT KEY ,
         username VARCHAR(20) NOT NULL UNIQUE KEY ,
         email VARCHAR(50) UNIQUE ,
         card CHAR(18) UNIQUE -- 注意写法KEY这里可以省略
       );

       INSERT test_unique (username, email, card) VALUES ('A','A@qq.com','1');

       INSERT test_unique (username, email, card) VALUES ('A','462258485@qq.com','2');-- 这里报错 `Duplicate entry 'A' for key 'username'`

       INSERT test_unique (username, email, card) VALUES ('B',NULL ,NULL );-- 这里可以插入

       INSERT test_unique (username, email, card) VALUES ('C',NULL ,NULL );-- 这里可以插入 Null在这里可以重复

       SELECT * FROM test_unique;

       -- 重新创建用户表 ,放在反引号``之间更加规范，防止和关键字起冲突
       -- 用COMMENT '用户编号' 来给字段添加注释
       DROP TABLE IF EXISTS table_user  ;
       CREATE TABLE IF NOT EXISTS `table_user`(
         `id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户编号',
         `username` VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
         `password` CHAR(32) NOT NULL COMMENT '密码',
         `email` VARCHAR(50) NOT NULL COMMENT '邮箱',
         `age` TINYINT UNSIGNED NOT NULL  DEFAULT  18 COMMENT '年龄',
         `sex` ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
         `tel` CHAR(11) NOT NULL UNIQUE COMMENT '电话',
         `addr` VARCHAR(50) NOT NULL DEFAULT '北京' COMMENT '地址',
         `card` CHAR(18) NOT NULL UNIQUE COMMENT '身份证号',
         `marry` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 代表未结婚，1代表已结婚',
         `salary` FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
       )ENGINE = INNODB DEFAULT CHARSET = UTF8;

       DESC `table_user`;

       SHOW CREATE TABLE `table_user`;

总结：

* 唯一约束的KET关键字可以省略,NULL值是可以重复的
* `数据库名` ,`表名`,`字段名` 等用户自定义名称用``包裹，可以防止关键词冲突
* COMMENT '字段备注或者注释'