【MySQL】MySQL进阶(外键约束、多表查询、视图、备份与恢复)

发布时间:2021年10月03日 阅读:12 次

约束

外键约束

  • 外键约束概念

    • 让表和表之间产生关系,从而保证数据的准确性!

  • 建表时添加外键约束

    • 为什么要有外键约束

  -- 创建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);

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

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

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

多表设计

一对一(了解)

  • 分析

    • 人和身份证。一个人只有一个身份证,一个身份证只能对应一个人!

  • 实现原则

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

  • 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);
  • 图解

【MySQL】MySQL进阶(外键约束、多表查询、视图、备份与恢复) mysql创建数据库 数据库视图 语法分析 虚拟商品 分类数据 外键 第1张

一对多

  • 分析

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

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

  • 实现原则

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

  • 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);
  • 图解

【MySQL】MySQL进阶(外键约束、多表查询、视图、备份与恢复) mysql创建数据库 数据库视图 语法分析 虚拟商品 分类数据 外键 第2张

多对多

  • 分析

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

  • 实现原则

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

  • 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);
  • 图解

【MySQL】MySQL进阶(外键约束、多表查询、视图、备份与恢复) mysql创建数据库 数据库视图 语法分析 虚拟商品 分类数据 外键 第3张

多表查询

多表查询-数据准备

  • 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)
);-- 添加数据INSERT INTO product VALUES (1,'华为手机',1);INSERT INTO product VALUES (2,'小米手机',1);INSERT INTO product VALUES (3,'联想电脑',2);INSERT INTO product VALUES (4,'苹果电脑',2);INSERT INTO product VALUES (5,'中华香烟',3);INSERT INTO product VALUES (6,'玉溪香烟',3);INSERT INTO product VALUES (7,'计生用品',NULL);-- 中间表CREATE TABLE us_pro(
	upid INT PRIMARY KEY AUTO_INCREMENT,  -- 中间表id
	uid INT, -- 外键字段。需要和用户表的主键产生关联
	pid INT, -- 外键字段。需要和商品表的主键产生关联
	CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),	CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);-- 添加数据INSERT INTO us_pro VALUES (NULL,1,1);INSERT INTO us_pro VALUES (NULL,1,2);INSERT INTO us_pro VALUES (NULL,1,3);INSERT INTO us_pro VALUES (NULL,1,4);INSERT INTO us_pro VALUES (NULL,1,5);INSERT INTO us_pro VALUES (NULL,1,6);INSERT INTO us_pro VALUES (NULL,1,7);INSERT INTO us_pro VALUES (NULL,2,1);INSERT INTO us_pro VALUES (NULL,2,2);INSERT INTO us_pro VALUES (NULL,2,3);INSERT INTO us_pro VALUES (NULL,2,4);INSERT INTO us_pro VALUES (NULL,2,5);INSERT INTO us_pro VALUES (NULL,2,6);INSERT INTO us_pro VALUES (NULL,2,7);INSERT INTO us_pro VALUES (NULL,3,1);INSERT INTO us_pro VALUES (NULL,3,2);INSERT INTO us_pro VALUES (NULL,3,3);INSERT INTO us_pro VALUES (NULL,3,4);INSERT INTO us_pro VALUES (NULL,3,5);INSERT INTO us_pro VALUES (NULL,3,6);INSERT INTO us_pro VALUES (NULL,3,7);INSERT INTO us_pro VALUES (NULL,4,1);INSERT INTO us_pro VALUES (NULL,4,2);INSERT INTO us_pro VALUES (NULL,4,3);INSERT INTO us_pro VALUES (NULL,4,4);INSERT INTO us_pro VALUES (NULL,4,5);INSERT INTO us_pro VALUES (NULL,4,6);INSERT INTO us_pro VALUES (NULL,4,7);
  • 架构器图解

【MySQL】MySQL进阶(外键约束、多表查询、视图、备份与恢复) mysql创建数据库 数据库视图 语法分析 虚拟商品 分类数据 外键 第4张

多表查询-笛卡尔积查询(了解)

  • 有两张表,获取这两个表的所有组合情况

  • 要完成多表查询,需要消除这些没有用的数据

  • 多表查询格式

  CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)0
  • 笛卡尔积查询

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

多表查询-内连接查询

  • 查询原理

    • 内连接查询的是两张表有交集的部分数据(有主外键关联的数据)

  • 显式内连接

  CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)2
  • 隐式内连接

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

多表查询-外连接查询

  • 左外连接

    • 查询左表的全部数据,和左右两张表有交集部分的数据

    • 查询原理

    • 基本演示

  CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)4
  • 右外连接

    • 查询右表的全部数据,和左右两张表有交集部分的数据

    • 查询原理

    • 基本演示

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

多表查询-子查询

  • 子查询介绍

    • 查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询!

  • 子查询-结果是单行单列的

    • 可以作为条件,使用运算符进行判断!

    • 基本演示

  CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)6
  • 子查询-结果是多行单列的

    • 可以作为条件,使用运算符in或not in进行判断!

    • 基本演示

  CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)7
  • 子查询-结果是多行多列的

    • 可以作为一张虚拟表参与查询!

    • 基本演示

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

多表查询练习

  • 查询用户的编号、姓名、年龄。订单编号

  CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)9
  • 查询所有的用户。用户的编号、姓名、年龄。订单编号

  -- 创建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='王五';0
  • 查询所有的订单。用户的编号、姓名、年龄。订单编号

  -- 创建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='王五';1
  • 查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号

  -- 创建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='王五';2
  • 查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号

  -- 创建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='王五';3
  • 查询商品分类的编号、分类名称。分类下的商品名称

  -- 创建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='王五';4
  • 查询所有的商品分类。商品分类的编号、分类名称。分类下的商品名称

  -- 创建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='王五';5
  • 查询所有的商品信息。商品分类的编号、分类名称。分类下的商品名称

  -- 创建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='王五';6
  • 查询所有的用户和所有的商品。显示用户的编号、姓名、年龄。商品名称

  -- 创建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='王五';7
  • 查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称

  -- 创建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='王五';8

多表查询-自关联查询

  • 自关联查询介绍

    • 同一张表中有数据关联。可以多次查询这同一个表!

  • 自关联查询演示

  -- 创建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='王五';9

视图

视图的概念

  • 视图是一种虚拟存在的数据表

  • 这个虚拟的表并不在数据库中实际存在

  • 作用是将一些比较复杂的查询语句的结果,封装到一个虚拟表中。后期再有相同复杂查询时,直接查询这张虚拟表即可

  • 说白了,视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中,所以我们在创建视图的时候,工作重心就要放在这条SELECT查询语句上

视图的好处

  • 简单

    • 对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集

  • 安全

    • 视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集

  • 数据独立

    • 一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响

视图数据准备

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

视图的创建

  • 创建视图语法

-- 标准语法ALTER TABLE 表名 DROP FOREIGN KEY 外键名;-- 删除外键ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;1
  • 普通多表查询,查询城市和所属国家

-- 标准语法ALTER TABLE 表名 DROP FOREIGN KEY 外键名;-- 删除外键ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;2
  • 创建视图基本演示

-- 标准语法ALTER TABLE 表名 DROP FOREIGN KEY 外键名;-- 删除外键ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;3
  • 创建视图并指定列名基本演示

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

视图的查询

  • 查询视图语法

-- 标准语法ALTER TABLE 表名 DROP FOREIGN KEY 外键名;-- 删除外键ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;5
  • 查询视图基本演示

-- 标准语法ALTER TABLE 表名 DROP FOREIGN KEY 外键名;-- 删除外键ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;6
  • 查询视图创建语法

-- 标准语法ALTER TABLE 表名 DROP FOREIGN KEY 外键名;-- 删除外键ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;7
  • 查询视图创建语句基本演示

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

视图的修改

  • 修改视图表中的数据

-- 标准语法ALTER TABLE 表名 DROP FOREIGN KEY 外键名;-- 删除外键ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;9
  • 修改视图表结构

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

视图的删除

  • 删除视图

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

视图的总结

  • 视图是一种虚拟存在的数据表

  • 这个虚拟的表并不在数据库中实际存在

  • 说白了,视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中,所以我们在创建视图的时候,工作重心就要放在这条SELECT查询语句上

  • 视图的好处

    • 简单

    • 安全

    • 数据独立

备份与还原

命令行方式

  • 备份

    • 使用SecureCRT工具连接到Linux系统,输入:mysqldump -u root -p 数据库名称 > 文件保存路径

-- 标准语法ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);-- 添加外键约束ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);2
  • 进入文件保存路径,查看文件是否存在

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

    • 登录mysql数据库

-- 标准语法ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);-- 添加外键约束ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);4
  • 删除已经备份的数据库

-- 标准语法ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);-- 添加外键约束ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);5
  • 重新创建名称相同的数据库

-- 标准语法ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);-- 添加外键约束ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);6
  • 使用该数据库

-- 标准语法ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);-- 添加外键约束ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);7
  • 导入文件执行:source 备份文件路径;

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

图形化界面方式

  • 备份

【MySQL】MySQL进阶(外键约束、多表查询、视图、备份与恢复) mysql创建数据库 数据库视图 语法分析 虚拟商品 分类数据 外键 第5张

【MySQL】MySQL进阶(外键约束、多表查询、视图、备份与恢复) mysql创建数据库 数据库视图 语法分析 虚拟商品 分类数据 外键 第6张

【MySQL】MySQL进阶(外键约束、多表查询、视图、备份与恢复) mysql创建数据库 数据库视图 语法分析 虚拟商品 分类数据 外键 第7张

  • 恢复

【MySQL】MySQL进阶(外键约束、多表查询、视图、备份与恢复) mysql创建数据库 数据库视图 语法分析 虚拟商品 分类数据 外键 第8张

【MySQL】MySQL进阶(外键约束、多表查询、视图、备份与恢复) mysql创建数据库 数据库视图 语法分析 虚拟商品 分类数据 外键 第9张

【MySQL】MySQL进阶(外键约束、多表查询、视图、备份与恢复) mysql创建数据库 数据库视图 语法分析 虚拟商品 分类数据 外键 第10张

【MySQL】MySQL进阶(外键约束、多表查询、视图、备份与恢复) mysql创建数据库 数据库视图 语法分析 虚拟商品 分类数据 外键 第11张


Tag:mysql创建数据库 数据库视图 语法分析 虚拟商品 分类数据 外键
相关文章

发表评论: