MySQL基础|完整性约束条件

主键

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

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值

唯一约束

   -- 测试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 ‘字段备注或者注释’