MYSQL约束

今日内容–mysql约束

约束

• 概念：

  对表中的数据进行限定，从而保证数据的正确性，有效性，完整性。
  

• 分类：

  1.主键约束：primary key
  2.非空约束：not null
  3.唯一约束：unique
  4.外键约束：foreign key
  

1. 非空约束

• 语法

  not null
  

• 使用

  1.在创建表时添加约束
  

  create table stu(
  	id int,
      `name` varchar(20) not null
  );
  

  • 删除非空约束

    -- modify更改
    alter table stu modify name varchar(20);
    

  2.创建表之后，添加约束

      alter table stu modify name varchar(20) not null;
  

2. 唯一约束

• 语法

  unique -- 某一列的值不能重复
  

• 使用

  -- 1.创建表时添加唯一约束
  create table stu1 (
  	id int,
      phone_number varchar(20) unique 
  );
  -- 2.删除唯一约束
  alter table stu modify phone_number varchar(20); -- 错误写法，不会真的删除
  alter table stu drop index phone_number; -- 删除phone_number的索引
  
  
  -- 3.创建表完成后，添加唯一约束
  alter table stu modify phone_number varchar(20) unique;
  

• 补充：

  index索引：
  1.一张表的索引可以看成是一本书的目录，添加索引可以提高查找效率。
  2.索引就是一张小表，用于存储某列值的地址值
  3.一张表中索引太多反而会增加查询工作量，延长时间，降低效率
  4.我们可以定义这个索引具有唯一性，所以唯一约束是通过索引index来实现的。
  

3.主键约束

 注意
  1.主键含义：非空 且 唯一
  2.一张表只能有一个主键
  3.主键就是表中记录的唯一标识

• 补充：

  1.主键是一种特殊的唯一索引
  2.主键列不允许空值，而唯一性索引列允许空值
  

• 使用

  -- 1.在创建表时添加主键
  create table stu(
  	id int primary key,
      name varchar(20)
  );
  
  -- 2.删除主键
  alter table stu modify id int;-- 错误写法，不会真的删除主键
  alter table stu drop primary key; -- 删除主键，无需指定列，因为一个表只有一个主键
  
  
  -- 3.创建完表后，添加主键
  alter table stu modify id int primary key;
  

3.1 主键约束–自增长

• 概念：如果某一列是数值类型，使用auto_increment 可以完成值的自动增长
  

• 使用

  -- 1.创建表时，设置主键自增长,默认从1开始自增长
  create table stu(
  	id int primary key auto_increment,
      name varchar(20)
  );
  
  -- 2.创建表时，设置主键自增长,并指定自增长起始值为6
  create table stu(
  	id int primary key auto_increment,
      name varchar(20)
  )auto_increment=6;
  
  
  
  
  -- 3.删除自增长，不会删除主键
  alter table stu modify id int;
  
  -- 4.创建表完成后，添加自增长
  ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;-- 注意：此次不能再指定主键，因为主键已经存在
  
  -- 5.创建表完成后，设置主键自增长的初始值--前提是表中已经设定过主键自增长
  ALTER TABLE stu AUTO_INCREMENT=100;
  

4. 外键约束

1. 创建员工部门表，记录员工的详细信息

   -- 创建员工部门表
   create table emp_dept(
   	id int primary key auto_increment,
       name varchar(20) not null,
       age int ,
       dept_name varchar(20),-- 所属公司部门名称
       dept_loc varchar(20) -- 所属公司部门地址
   );
   
   -- 插入数据
   insert into emp_dept values(null,'张三',18,'研发部','广州');
   insert into emp_dept values(null,'李四',19,'研发部','广州');
   insert into emp_dept values(null,'王五',20,'研发部','广州');
   insert into emp_dept values(null,'大王',21,'销售部','武汉');
   insert into emp_dept values(null,'小王',22,'销售部','武汉');
   insert into emp_dept values(null,'王炸',23,'销售部','武汉');
   
   

2. 观察数据发现问题：存在数据冗余。解决办法：拆分成两个表

   -- 先删除员工部门表
   drop table emp_dept;
   
   -- 创建部门表
   create table dept (
   	id int primary key auto_increment,
       dept_name varchar(20),
       dept_loc varchar(20)
   );
   
   -- 创建员工表
   create table emp(
   	id int primary key auto_increment,
       name varchar(20) not null,
       age int ,
       dept_id int
   );
   
   -- 插入dept表数据
   insert into dept values(null,'研发部','广州');
   insert into dept values(null,'销售部','武汉');
   
   -- 插入emp表数据
   insert into emp values(null,'张三',18,1);
   insert into emp values(null,'李四',19,1);
   insert into emp values(null,'王五',20,1);
   insert into emp values(null,'大王',21,2);
   insert into emp values(null,'小王',22,2);
   insert into emp values(null,'王炸',23,2);
   

3. 操作数据发现问题：

   1. 删除部门表数据，会造成员工表中相对应的数据失去意义
   2. 添加新员工时，可以随意填写不存在的部门编号

• 实际情况：

  1. 公司要解散某个部门，必须先安排好该部门的所有员工
  2. 公司要组建一个新部门，也要先成立部门，再招员工

• 解决办法：

  • 添加外键约束–关联两个表

  -- 删除员工表
  drop table emp;
  
  -- 重新创建员工表
  create table emp(
  	id int primary key auto_increment,
      name varchar(20) not null,
      age int,
      dept_id int ,
      constraint emp_dept_fk foreign key(dept_id) references dept(id)
  );
  -- 插入emp表数据
  insert into emp values(null,'张三',18,1);
  insert into emp values(null,'李四',19,1);
  insert into emp values(null,'王五',20,1);
  insert into emp values(null,'大王',21,2);
  insert into emp values(null,'小王',22,2);
  insert into emp values(null,'王炸',23,2);
  

• 删除外键

  alter table emp drop foreign key emp_dept_fk;-- 更改表，删除外键emp_dept_fk
  

• 创建表之后，添加外键

  alter table emp add constraint emp_dept_fk foreign key (dept_id) references dept(id);
  

扩充

4.1 外键约束–级联操作

• 提出问题：如何将部门表dept中的部门编号1修改为5

  -- 1.先把员工表中的dept_id 修改为 null
  update emp set dept_id =null where dept_id=1;
  
  -- 2.再手动修改部门编号为5
  
  -- 3.将员工表中的dept_id 修改为5
  update emp set dept_id=5 where dept_id is null;
  

• 有更简单的实现吗？-- 在设置外键时，设置级联操作

  • 查看 架构设计图

  • 级联修改

    -- 先删除外键
    alter table emp drop foreign key emp_dept_fk;-- 更改表，删除外键emp_dept_fk
    
    -- 重新添加外键，并设置级联修改--末尾追加 on update cascade
    alter table emp add constraint emp_dept_fk foreign key (dept_id) references dept(id) on update cascade;
    

  • 级联删除

    -- 先删除外键
    alter table emp drop foreign key emp_dept_fk;-- 更改表，删除外键emp_dept_fk
    
    -- 重新添加外键，并设置级联修改和级联删除 
    alter table emp add constraint emp_dept_fk foreign key (dept_id) references dept(id) on update cascade on delete cascade;
    

• 补充

  1.级联更新和级联删除可以一起使用，也可以单个使用，级联删除需要谨慎使用
  2.级联的理解：单向--> 父表在更新或者删除时，更新或者删除子表对应记录