MySQL进阶:约束:外键约束,外键级联更新和删除;多表设计,多表查询:内连接查询,外连接查询,子查询;视图,数据库备份与还原

MySQL进阶

知识点梳理

一、约束

1. 外键约束

  • 为什么要有外键约束

    • 例如：一个user表，一个orderlist

     

     

    • 如果现在想要直接删除id为1的张三，但是orderlist里还有用户id为1的订单数据，这样合理么？

    • 给uid添加外键约束之后，uid就是外键列

      • 如果与另外的一张表的主键有关联关系，那么这一列就是外键列

    • 建表语句

      -- 创建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 主表名(主表主键列名)
    constraint：约束
    foreign：外国的
    reference：引用
    简写：可以不知道外键名（默认即可）
    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);

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

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

    • 当我想把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;

二、多表设计

1. 一对一(了解)

  • 分析

    • 人和身份证。一个人只有一个身份证，一个身份证只能对应一个人！

  • 实现原则

    • 在任意一个表建立外键，去关联另外一个表的主键

  • SQL演示

-- 创建db5数据库
CREATE DATABASE db5;
-- 使用db5数据库
USE db5;
​
-- 创建person表
CREATE TABLE person(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
-- 添加数据
INSERT INTO person VALUES (NULL,'张三'),(NULL,'李四');
​
-- 创建card表
CREATE TABLE card(
    id INT PRIMARY KEY AUTO_INCREMENT,
    number VARCHAR(50),
    pid INT UNIQUE,
    CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id) -- 添加外键
);
-- 添加数据
INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',2);

  • 图解

 

 

2. 一对多

  • 分析

    • 用户和订单。一个用户可以有多个订单！

    • 商品分类和商品。一个分类下可以有多个商品！

  • 实现原则

    • 在多的一方，建立外键约束，来关联一的一方主键

  • SQL演示

/*
    用户和订单
*/
-- 创建user表
CREATE TABLE USER(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
-- 添加数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');
​
-- 创建orderlist表
CREATE TABLE orderlist(
    id INT PRIMARY KEY AUTO_INCREMENT,
    number VARCHAR(20),
    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);
​
​
/*
    商品分类和商品
*/
-- 创建category表
CREATE TABLE category(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO category VALUES (NULL,'手机数码'),(NULL,'电脑办公');
​
-- 创建product表
CREATE TABLE product(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(30),
    cid INT,
    CONSTRAINT pc_fk1 FOREIGN KEY (cid) REFERENCES category(id)  -- 添加外键约束
);
-- 添加数据
INSERT INTO product VALUES (NULL,'华为P30',1),(NULL,'小米note3',1),
(NULL,'联想电脑',2),(NULL,'苹果电脑',2);

  • 图解

 

 

3. 多对多

  • 分析

    • 学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择！

  • 实现原则

    • 需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键

  • SQL演示

-- 创建student表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');
​
-- 创建course表
CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10)
);
-- 添加数据
INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');
​
-- 创建中间表
CREATE TABLE stu_course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    sid INT, -- 用于和student表的id进行外键关联
    cid INT, -- 用于和course表的id进行外键关联
    CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束
    CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)   -- 添加外键约束
);
-- 添加数据
INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);

  • 图解

 

 

三、多表查询

1. 多表查询-数据准备

  • SQL语句

-- 创建db6数据库
CREATE DATABASE db6;
-- 使用db6数据库
USE db6;
​
-- 创建user表
CREATE TABLE USER(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 用户id
    NAME VARCHAR(20),                   -- 用户姓名
    age INT                             -- 用户年龄
);
-- 添加数据
INSERT INTO USER VALUES (1,'张三',23);
INSERT INTO USER VALUES (2,'李四',24);
INSERT INTO USER VALUES (3,'王五',25);
INSERT INTO USER VALUES (4,'赵六',26);
​
​
-- 订单表
CREATE TABLE orderlist(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 订单id
    number VARCHAR(30),                 -- 订单编号
    uid INT,    -- 外键字段
    CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (1,'hm001',1);
INSERT INTO orderlist VALUES (2,'hm002',1);
INSERT INTO orderlist VALUES (3,'hm003',2);
INSERT INTO orderlist VALUES (4,'hm004',2);
INSERT INTO orderlist VALUES (5,'hm005',3);
INSERT INTO orderlist VALUES (6,'hm006',3);
INSERT INTO orderlist VALUES (7,'hm007',NULL);
​
​
-- 商品分类表
CREATE TABLE category(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品分类id
    NAME VARCHAR(10)                    -- 商品分类名称
);
-- 添加数据
INSERT INTO category VALUES (1,'手机数码');
INSERT INTO category VALUES (2,'电脑办公');
INSERT INTO category VALUES (3,'烟酒茶糖');
INSERT INTO category VALUES (4,'鞋靴箱包');
​
​
-- 商品表
CREATE TABLE product(
    id INT PRIMARY KEY AUTO_INCREMENT,   -- 商品id
    NAME VARCHAR(30),                    -- 商品名称
    cid INT, -- 外键字段
    CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
版权声明：本文为859630097com原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/859630097com/p/14340116.html