MySQL-02-笔记

1.约束的概念和分类

• 约束的概念
  对表中的数据进行限定，保证数据的正确性、有效性、完整性！
• 约束的分类

约束	说明
PRIMARY KEY	主键约束
PRIMARY KEY AUTO_INCREMENT	主键、自动增长
UNIQUE	唯一约束
NOT NULL	非空约束
FOREIGN KEY	外键约束
FOREIGN KEY ON UPDATE CASCADE	外键级联更新
FOREIGN KEY ON DELETE CASCADE	外键级联删除

2.主键约束

• 主键约束特点

  • 主键约束包含：非空和唯一两个功能
  • 一张表只能有一个列作为主键
  • 主键一般用于表中数据的唯一标识

• 建表时添加主键约束

  -- 标准语法
  CREATE TABLE 表名(
  	列名 数据类型 PRIMARY KEY,
      列名 数据类型,
      ...
  );
  
  -- 创建student表
  CREATE TABLE student(
  	id INT PRIMARY KEY  -- 给id添加主键约束
  );
  
  -- 添加数据
  INSERT INTO student VALUES (1),(2);
  -- 主键默认唯一，添加重复数据，会报错
  INSERT INTO student VALUES (2);
  -- 主键默认非空，不能添加null的数据
  INSERT INTO student VALUES (NULL);
  
  -- 查询student表
  SELECT * FROM student;
  -- 查询student表详细
  DESC student;
  

• 删除主键

  -- 标准语法
  ALTER TABLE 表名 DROP PRIMARY KEY;
  
  -- 删除主键
  ALTER TABLE student DROP PRIMARY KEY;
  

• 建表后单独添加主键

  -- 标准语法
  ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;
  
  -- 添加主键
  ALTER TABLE student MODIFY id INT PRIMARY KEY;
  

3.主键自动增长约束

• 建表时添加主键自增约束

  -- 标准语法
  CREATE TABLE 表名(
  	列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
      列名 数据类型,
      ...
  );
  
  -- 创建student2表
  CREATE TABLE student2(
  	id INT PRIMARY KEY AUTO_INCREMENT    -- 给id添加主键自增约束
  );
  
  -- 添加数据
  INSERT INTO student2 VALUES (1),(2);
  -- 添加null值，会自动增长
  INSERT INTO student2 VALUES (NULL),(NULL);
  
  -- 查询student2表
  SELECT * FROM student2;
  -- student2表详细
  DESC student2;
  

• 删除自动增长

  -- 标准语法
  ALTER TABLE 表名 MODIFY 列名 数据类型;
  
  -- 删除自动增长
  ALTER TABLE student2 MODIFY id INT;
  

• 建表后单独添加自动增长

  -- 标准语法
  ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
  
  -- 添加自动增长
  ALTER TABLE student2 MODIFY id INT AUTO_INCREMENT;
  

4.唯一约束

• 建表时添加唯一约束

  -- 标准语法
  CREATE TABLE 表名(
  	列名 数据类型 UNIQUE,
      列名 数据类型,
      ...
  );
  
  -- 创建student3表
  CREATE TABLE student3(
  	id INT PRIMARY KEY AUTO_INCREMENT,
  	tel VARCHAR(20) UNIQUE    -- 给tel列添加唯一约束
  );
  
  -- 添加数据
  INSERT INTO student3 VALUES (NULL,'18888888888'),(NULL,'18666666666');
  -- 添加重复数据，会报错
  INSERT INTO student3 VALUES (NULL,'18666666666');
  
  -- 查询student3数据表
  SELECT * FROM student3;
  -- student3表详细
  DESC student3;
  

• 删除唯一约束

  -- 标准语法
  ALTER TABLE 表名 DROP INDEX 列名;
  
  -- 删除唯一约束
  ALTER TABLE student3 DROP INDEX tel;
  

• 建表后单独添加唯一约束

  -- 标准语法
  ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;
  
  -- 添加唯一约束
  ALTER TABLE student3 MODIFY tel VARCHAR(20) UNIQUE;
  

5.非空约束

• 建表时添加非空约束

  -- 标准语法
  CREATE TABLE 表名(
  	列名 数据类型 NOT NULL,
      列名 数据类型,
      ...
  );
  
  -- 创建student4表
  CREATE TABLE student4(
  	id INT PRIMARY KEY AUTO_INCREMENT,
  	NAME VARCHAR(20) NOT NULL    -- 给name添加非空约束
  );
  
  -- 添加数据
  INSERT INTO student4 VALUES (NULL,'张三'),(NULL,'李四');
  -- 添加null值，会报错
  INSERT INTO student4 VALUES (NULL,NULL);
  

• 删除非空约束

  -- 标准语法
  ALTER TABLE 表名 MODIFY 列名 数据类型;
  
  -- 删除非空约束
  ALTER TABLE student4 MODIFY NAME VARCHAR(20);
  

• 建表后单独添加非空约束

   -- 标准语法
   ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;
   
   -- 添加非空约束
   ALTER TABLE student4 MODIFY NAME VARCHAR(20) NOT NULL;
   ```
  

6.外键约束

• 外键约束概念

  让表与表之间产生关系，从而保证数据的准确性！

• 建表时添加外键约束

  • 为什么要有外键约束

     -- 创建db2数据库
     CREATE DATABASE db2;
     -- 使用db2数据库
     USE db2;
     
     -- 创建user用户表
     CREATE TABLE USER(
     	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
     	NAME VARCHAR(20) NOT NULL             -- 姓名
     );
     -- 添加用户数据
     INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五');
     
     -- 创建orderlist订单表
     CREATE TABLE orderlist(
     	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
     	number VARCHAR(20) NOT NULL,          -- 订单编号
     	uid INT                               -- 订单所属用户
     );
     -- 添加订单数据
     INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
     (NULL,'hm003',2),(NULL,'hm004',2),
     (NULL,'hm005',3),(NULL,'hm006',3);
     
     -- 添加一个订单，但是没有所属用户。这合理吗？
     INSERT INTO orderlist VALUES (NULL,'hm007',8);
     -- 删除王五这个用户，但是订单表中王五还有很多个订单呢。这合理吗？
     DELETE FROM USER WHERE NAME='王五';
     
     -- 所以我们需要添加外键约束，让两张表产生关系
    

  • 外键约束格式

     CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
    

  • 创建表添加外键约束

     -- 创建user用户表
     CREATE TABLE USER(
     	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
     	NAME VARCHAR(20) NOT NULL             -- 姓名
     );
     -- 添加用户数据
     INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五');
     
     -- 创建orderlist订单表
     CREATE TABLE orderlist(
     	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
     	number VARCHAR(20) NOT NULL,          -- 订单编号
     	uid INT,                              -- 订单所属用户
     	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)   -- 添加外键约束
     );
     -- 添加订单数据
     INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
     (NULL,'hm003',2),(NULL,'hm004',2),
     (NULL,'hm005',3),(NULL,'hm006',3);
     
     -- 添加一个订单，但是没有所属用户。无法添加
     INSERT INTO orderlist VALUES (NULL,'hm007',8);
     -- 删除王五这个用户，但是订单表中王五还有很多个订单呢。无法删除
     DELETE FROM USER WHERE NAME='王五';
    

• 删除外键约束

  -- 标准语法
  ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
  
  -- 删除外键
  ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
  

• 建表后添加外键约束

  -- 标准语法
  ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
  
  -- 添加外键约束
  ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);
  

7.外键的级联更新和级联删除(了解)

• 什么是级联更新和级联删除

  • 当想把user用户表中的某个用户删掉，我希望该用户所有的订单也随之被删除
  • 当想把user用户表中的某个用户id修改，我希望订单表中该用户所属的订单用户编号也随之修改

• 添加级联更新和级联删除

  -- 添加外键约束，同时添加级联更新  标准语法
  ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE;
  
  -- 添加外键约束，同时添加级联删除  标准语法
  ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON DELETE CASCADE;
  
  -- 添加外键约束，同时添加级联更新和级联删除  标准语法
  ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE ON DELETE CASCADE;
  
  
  -- 删除外键约束
  ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
  
  -- 添加外键约束，同时添加级联更新和级联删除
  ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE;
  
  -- 将王五用户的id修改为5    订单表中的uid也随之被修改
  UPDATE USER SET id=5 WHERE id=3;
  
  -- 将王五用户删除     订单表中该用户所有订单也随之删除
  DELETE FROM USER WHERE id=5;