MySQL 06 事务、视图、索引、备份和恢复

最新推荐文章于 2023-07-14 00:30:57 发布

众生云海，一念初见

最新推荐文章于 2023-07-14 00:30:57 发布

阅读量1.6k

点赞数

分类专栏： MySQL 文章标签：数据仓库数据库开发 dba 数据库架构

本文链接：https://blog.csdn.net/qq_24431059/article/details/124456464

版权

MySQL 专栏收录该内容

9 篇文章 8 订阅

订阅专栏

MySQL 06 事务、视图、索引、备份和恢复

文章目录

MySQL 06 事务、视图、索引、备份和恢复

一、学习目标

掌握使用事务保证操纵数据的完整性的方法
掌握如何创建并使用索引
掌握如何创建并使用视图
掌握如何进行数据库的备份和恢复

二、事务

2.1什么是事务

TRANSACTION
是将一系列数据操作捆绑成为一个整体进行统一管理机制
多个操作作为一个整体向系统提交，要么都执行、要么都不执行
是一个不可分割的工作逻辑单元

2.2事务的特性

事务必须具备以下四个属性，简称ACID 属性,MySql中支持事务的存储引擎：InnoDB、BDB

1.原子性（Atomicity）

事务是一个完整的操作，事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行

2.一致性（Consistency）

当事务完成时，数据必须处于一致状态

3.隔离性（Isolation）

并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务

4.持久性（Durability）

事务完成后,它对数据库的修改被永久保持

2.3为什么需要事务

需求：

支付平台转账业务

假定资金从账户A转到账户B，至少需要两步
账户A的资金减少
账户B的资金相应增加

简单来说就是两条修改语句

示例：

假设小王的支付平台上的账户余额是5000元，小张账户余额是200元，需从小王账户中转2000元到小张的账户中。

转账前，两个账户总金额是5000+200=5200元。

分析：

实现步骤
创建该支付平台的数据库paycorp
在数据库paycorp中，创建账户信息表account
向account表中插入小王和小张的账户余额
使用SQL语句实现转账操作
小王的账户余额减少2000元
小张的账户余额增加2000元

代码：
#1.创建该支付平台的数据库paycorp
create database paycorp;
#2.在数据库paycorp中，创建账户信息表account
create table account(
	id int not null primary key auto_increment comment '主键',
accountName varchar(20) not null comment '账号',
balance float unsigned not null  comment '余额'
);
#3.向account表中插入小王和小张的账户余额
insert into account values(null,'小王',5000),(null,'小张',200);

#4.使用SQL语句实现转账操作
update account set balance=balance-2000 where accountName='小王';
update account set balance=balance+2000 where accountName='小张';
正常情况下 成功转账后，两个账户总金额是3000+2200=5200元
update account set balance=balance-2000
where accountName='小王'
> Affected rows: 1
> 时间: 0.088s
update account set balance=balance+2000 
where accountName='小张'
> Affected rows: 1
> 时间: 0.007s
#查询转账后的结果
select * from account
> OK
转账后数据表变化

问题：

在转账过程中，如果出现问题（如：断电、服务器故障）会是什么情况？如图所示

过程中发现因为中途发生故障，两人的账户总额前后相差3000元，
如何解决可能发生的数据不一致问题？

方法：

使用事务

三、使用事务

开始事务语法
begin;
#或者
start transaction;
提交事务语法：
commit;
回滚事务语法：
rollback;
关闭自动事务提交语法
set autocommit=0|1; #0:关闭自动提交，1:开启
3.1提交支付平台转账操作

示例1：

使用事务实现小王和小张之间的转账操作
use paycorp;
begin;
 update account set balance=balance-2000 where accountName='小王';
 update account set balance=balance+2000 where accountName='小张';
commit;
结论：

小王和小张的总账户余额和转账前保持一致，数据库中数据从一个一致性状态更新到另一个一致性状态

3.2回滚支付平台转账操作

示例2：

小王需要再向小张的账户上转4000元，但是小王的账户余额仅剩3000元的余额
支付平台不允许某个账户余额为负的，需要终止转账，并将相关账户的状态恢复到转账前的状态

关键代码：
use paycorp;
/*--开始事务(指定事务从此处开始,后续的sql语句是一个整体--*/
begin;
 /*实现转账操作：小王向小张的账户上转4000元*/
 #小王的账户少4000元
 update account set balance=balance-4000 where accountname='小王';
/*--由于账户余额不允许为负，撤销转账操作，恢复账户状态--*/
rollback;
3.3自动关闭和开启事务

默认情况下，每条单独的SQL语句视为一个事务
关闭默认提交状态后，可手动开启、关闭事务

关闭/开启自动提交状态
SET autocommit = 0|1;
值为0：关闭自动提交
值为1：开启自动提交

注意事项：

关闭自动提交后，从下一条SQL语句开始将会开启新事务，需使用COMMIT或ROLLBACK语句结束该事务

示例：

通过设置autocommit=0开启事务操作，实现需求
小王向小张的账户转账2000，操作成功
小王再向小张的账户转账4000，为避免账户余额为负，撤销此操作
use paycorp;
set autocommit = 0; 
/*实现转账操作：小王向小张的账户上转2000元*/
update account set balance=balance-2000 where accountName='小王';
update account set balance=balance+2000 where accountName='小张';
commit; 
/*实现转账操作：小王向小张的账户上转4000元*/
update account set balance=balance-4000 where accountname='小王';
rollback; 
set autocommit = 1;
3.4使用事务时要遵循的原则

3.4.1事务尽可能简短

事务启动至结束后在数据库管理系统中保留大量资源，以保证事务的原子性、一致性、隔离性和持久性
如果在多用户系统中，较大的事务将会占用系统的大量资源，使得系统不堪重负，会影响软件的运行性能，甚至导致系统崩溃

3.4.2事务中访问的数据量尽量最少

当并发执行事务处理时，事务操作的数据量越少，事务之间对操作数据的争夺就越少

3.4.3查询数据时尽量不要使用事务

对数据进行浏览查询操作并不会更新数据库的数据时，尽量不使用事务查询数据，避免占用过量的系统资源

3.4.4在事务处理过程中尽量不要出现等待用户输入的操作

处理事务的过程中，如果需要等待用户输入数据，那么事务会长时间占用资源，有可能造成系统阻塞

四、索引

4.1什么是索引

索引是一种有效组合数据的方式，为快速查找到指定记录。

索引是创建在数据库表对象上,由表中的一个字段或多个字段生成的键组成

是对数据库表中一列或多列值进行排列的一种结构

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

4.2索引创建注意事项

创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

以下几种情况索引不会被使用

不等于操作不能用于索引
经过普通或者函数运算的索引列不能使用索引
含前向模糊查询（通配符%在搜索词首出现），比如“like %王xx”【反向键索引：reverse】
索引列为空，或包含空值
数值比较时左右类型不同，相当于做了隐式类型转换
给索引查询的值是未知字段，而不是已知数

4.3索引的缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

MySQL索引按存储类型分类

B-树索引：InnoDB、MyISAM均支持
哈希索引

常用索引类型：

1、普通索引

基本索引类型

允许在定义索引的列中插入重复值和空值

2、唯一索引：

索引数据不能重复

允许有空值

3、主键索引：

主键中的每一个值是非空、唯一的

一个主键将自动创建主键索引

4、复合索引

将多个列组合作为索引

5、全文索引：(MyISAM)

支持值的全文查找

允许重复值与空值

6、空间索引

对空间数据的列建立索引

五、创建、删除索引

5.1创建、删除索引

创建索引语法
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
       ON table_name (column_name [length] …);
示例
#为病人表的病人姓名创建普通索引
create index index_patientName
	on patient (patientName);
	
#为病人表的身份证创建唯一约束	
create unique index index_idCard
	on patient (idCard);	
注意事项：

如果创建索引是未指定创建索引的类型，则创建的索引为普通索引
通过CREATE INDEX语句无法创建主键索引，主键索引的创建语句
alter table tableName add primary key(column)
删除索引语法
DROP INDEX index_name ON Table_Name;
示例
#删除病人表的索引
drop index index_patientName on patient;
注意事项：

注意：删除表时，该表的所有索引同时会被删除。

5.2创建索引的指导原则

5.2.1按照下列标准选择建立索引的列

频繁搜索的列
经常用作查询选择的列
经常排序、分组的列
经常用作连接的列（主键/外键）

5.2.2请不要使用下面的列创建索引

仅包含几个不同值的列
表中仅包含几行

5.3使用索引注意事项：

查询时减少使用*返回全部列，不要返回不需要的列
索引应该尽量小，在字节数小的列上建立索引
Where子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前
避免在Order By子句中使用表达式
根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理

六、查看、删除索引

6.1查看索引

查看已创建的索引信息

SHOW INDEX FROM table_name;

查看hospital数据库中patient表的索引信息

use hospital;
show index from patient;

在这里插入图片描述

6.2删除索引

语法：

DROP INDEX index_name ON table_name;

示例：

drop index index_patientName ON patient;

注意：

/*删除表时，该表的所有索引将同时被删除*/
/*删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除*/
/*如果组成索引的所有列都被删除，则整个索引将被删除*/

七、视图

7.1为什么需要视图?

在实际工作中，不同身份的用户所关注的数据库数据可能也有所不同。

例如，企业的员工信息表中保存了该企业所有员工的详细信息，不同职位的人员对该表中查询的数据范围可能是不同的。

根据企业的人力资源管理制度要求，企业的老板关注企业员工的全部信息，他可以浏览全体员工的全部记录；

企业人力资源主管主要是查询全体员工目前的岗位、薪金和绩效；

企业出纳员只能查询每个员工的薪金，不能也无权看到企业员工的其他信息；

而作为这家企业的一名员工，只能查看本人记录，不得查看其他员工的任何信息。

如图所示，只能查看本人记录，不得查看其他员工的任何信息

7.2什么是视图？

1、视图是一张虚拟表

表示一张表的部分数据或多张表的综合数据

其结构和数据是建立在对表的查询基础上

2、视图中不存放数据

数据存放在视图所引用的原始表中

3、一个原始表，根据不同用户的不同需求，可以创建不同的视图

7.3视图的用途：

筛选表中的行
防止未经许可的用户访问敏感数据
降低数据库的复杂程度
将多个物理数据库抽象为一个逻辑数据库

7.4创建视图、删除视图、查看视图
#创建：一般以view_xxx或v_xxx格式命名
CREATE VIEW view_name  
   AS
    <SELECT 语句>;
    
#删除：删除前先判断视图是否存在
DROP VIEW [IF EXISTS] view_name;

#查看：
SELECT 字段1, 字段2, …… FROM view_name;
7.5查看所有视图
USE information_schema;
SELECT * FROM views\G;
7.6使用经验

使用视图修改数据会有许多限制，一般在实际开发中**视图仅用作查询**

7.7注意事项

视图中可以使用多个表
一个视图可以嵌套另一个视图
对视图数据进行添加、更新和删除操作直接影响所引用表中的数据
当视图数据来自多个表时，不允许添加和删除数据

7.8演示示例

hospital数据库中，创建一个视图，用于展示处方表的详细信息

实现步骤

使用join关联处方表、病人表、科室表和检查项目表
创建视图
调用视图，查询视图中的数据
USE hospital;
/*创建视图*/
CREATE VIEW v_prescription 
AS 
SELECT
	pa.patientName AS 姓名,
	pa.gender AS 性别,
	pa.birthDate AS 年龄,
	c.checkItemName AS 检查项目,
	pr.checkResult AS 检查结果,
	d.depName AS 检查科室,
	pr.examDate AS 检查日期 
FROM
	prescription pr
	INNER JOIN patient pa ON pr.patientID = pa.patientID
	INNER JOIN department d ON pr.depID = d.depID
	INNER JOIN checkitem c ON pr.checkItemID = c.checkItemID;
#查询视图中的数据
SELECT * FROM v_prescription;
#删除视图
DROP VIEW IF EXISTS v_prescription;

八、数据库备份

为什么要进行数据备份？

可能导致数据丢失的意外状况

数据库故障

突然断电

病毒入侵

人为误操作

程序错误

运算错误

磁盘故障

灾难（如火灾、地震）和盗窃

……

如何避免意外状况导致的数据丢失？

数据备份与恢复

数据备份

是容灾的基础

指为防止系统出现操作失误或系统故障导致数据丢失，而将全部或部分数据集合从应用主机的硬盘或

阵列复制到其他的存储介质的过程

MySQL数据备份的常用方法

mysqldump备份数据库

Navicat备份数据库

数据恢复

是指通过技术手段，将保存在硬盘等存储介质上的丢失的数据进行抢救和恢复的技术

九、mysqldump命令备份数据库

9.1语法简介

mysqldump命令——MySQL的客户端常用逻辑备份工具

将CREATE和INSERT INTO语句保存到文本文件
属于DOS命令

备份语法：
mysqldump [options] database [table1,[table2]…] > [path]/filename.sql
options的选项参数
–u username: 表示用户名
–h host: 表示登录的主机名称，如本机为主机可省略
–ppassword: 表示登录密码

mysqldump是DOS系统下的命令在使用时，无须进入mysql命令行；否则，将无法执行

9.2操作示例

以root账户登录到MySQL服务器，使用mysqldump命令备份hospital数据库，将SQL脚本保存到E:\DB\MySQL8.0\backup中
#备份整个库
mysqldump -uroot -p hospital>E:\DB\MySQL8.0\backup\hospital.sql
#备份单表
mysqldump -uroot -p hospital patient>E:\DB\MySQL8.0\backup\hospital20220426.sql
#备份多个表
mysqldump -uroot -p hospital patient department >E:\DB\MySQL8.0\backup\hospital20220427.sql
导出的SQL脚本中两种注释

以“–”开头：关于SQL语句的注释信息
以“/!”开头, “/”结尾：是关于MySQL服务器相关的注释

注意事项：

为保证账户密码安全，命令中可不写密码
但是，参数“-p”必须有，回车后根据提示写密码

9.3常用参数选项

参数描述
-add-drop-table 在每个CREATE TABLE语句前添加DROP TABLE语句，默认是打开的，可以用-skip-add-drop-table取消
–add-locks 该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句
好处：防止记录被再次导入时，其他用户对表进行的操作
-t或-no-create-info 只导出数据，而不添加CREATE TABLE语句
-c或–complete-insert 在每个INSERT语句的列上加上列名，在数据导入另一个数据库时有用
-d或–no-data 不写表的任何行信息，只转储表的结构

9.4备份文件包含的主要信息

备份后文件包含信息MySQL服务器及mysqldump工具的版本号

备份账户的名称

主机信息

备份的数据库名称

SQL语句注释和服务器相关注释

CREATE和INSERT语句

参数	描述
-add-drop-table	在每个CREATE TABLE语句前添加DROP TABLE语句，默认是打开的，可以用-skip-add-drop-table取消
–add-locks	该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句
好处：防止记录被再次导入时，其他用户对表进行的操作
-t或-no-create-info	只导出数据，而不添加CREATE TABLE语句
-c或–complete-insert	在每个INSERT语句的列上加上列名，在数据导入另一个数据库时有用
-d或–no-data	不写表的任何行信息，只转储表的结构

十、使用Navicat备份数据库

Navicat也可以用于导出数据库的备份脚本

操作

右键点选数据库->转储SQL文件->结构和数据…

在这里插入图片描述

选择导出文件的保存路径和文件名后，便可导出数据库脚本

在这里插入图片描述

查看导出的备份脚本文件

/*
 Navicat Premium Data Transfer

 Source Server         : T147MySql
 Source Server Type    : MySQL
 Source Server Version : 80026
 Source Host           : localhost:3306
 Source Schema         : t147hospital

 Target Server Type    : MySQL
 Target Server Version : 80026
 File Encoding         : 65001

 Date: 27/04/2022 16:56:59
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for checkitem
-- ----------------------------
DROP TABLE IF EXISTS `checkitem`;
CREATE TABLE `checkitem`  (
  `checkItemID` int(0) NOT NULL AUTO_INCREMENT COMMENT '检查项目编号',
  `checkItemName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '检查项目名称',
  `checkItemCost` float NOT NULL COMMENT '检查项目价格',
  PRIMARY KEY (`checkItemID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '检查项目表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of checkitem
-- ----------------------------
INSERT INTO `checkitem` VALUES (1, '血常规', 28);
INSERT INTO `checkitem` VALUES (2, '尿常规', 20);
INSERT INTO `checkitem` VALUES (3, '血脂、血糖检查', 25);
INSERT INTO `checkitem` VALUES (4, '凝血五项', 50);
INSERT INTO `checkitem` VALUES (5, '肺炎支、衣原体（快速）', 66);
INSERT INTO `checkitem` VALUES (6, 'CT', 110);

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `depID` int(0) NOT NULL AUTO_INCREMENT COMMENT '科室编号',
  `depName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '科室名称',
  PRIMARY KEY (`depID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '科室表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES (1, '急诊科');
INSERT INTO `department` VALUES (2, '呼吸科');
INSERT INTO `department` VALUES (3, '内科');

-- ----------------------------
-- Table structure for department_checkitem
-- ----------------------------
DROP TABLE IF EXISTS `department_checkitem`;
CREATE TABLE `department_checkitem`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '关系编号',
  `depID` int(0) NOT NULL COMMENT '科室编号',
  `checkItemID` int(0) NOT NULL COMMENT '检查项目编号',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_depID`(`depID`) USING BTREE,
  INDEX `fk_checkItemID`(`checkItemID`) USING BTREE,
  CONSTRAINT `fk_checkItemID` FOREIGN KEY (`checkItemID`) REFERENCES `checkitem` (`checkItemID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_depID` FOREIGN KEY (`depID`) REFERENCES `department` (`depID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '科室可开检查项目关系表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of department_checkitem
-- ----------------------------
INSERT INTO `department_checkitem` VALUES (1, 1, 1);
INSERT INTO `department_checkitem` VALUES (2, 1, 2);
INSERT INTO `department_checkitem` VALUES (3, 2, 1);
INSERT INTO `department_checkitem` VALUES (4, 2, 5);
INSERT INTO `department_checkitem` VALUES (5, 3, 1);
INSERT INTO `department_checkitem` VALUES (6, 3, 2);
INSERT INTO `department_checkitem` VALUES (7, 3, 3);

-- ----------------------------
-- Table structure for patient
-- ----------------------------
DROP TABLE IF EXISTS `patient`;
CREATE TABLE `patient`  (
  `patientID` int(0) NOT NULL AUTO_INCREMENT COMMENT '病人编号',
  `password` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '登录密码',
  `birthDate` date NULL DEFAULT NULL COMMENT '出生日期',
  `gender` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '男' COMMENT '性别',
  `patientName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '病人姓名',
  `phoneNum` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系电话',
  `email` varchar(70) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邮箱',
  `identityNum` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '身份证号',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '地址不详' COMMENT '地址',
  PRIMARY KEY (`patientID`) USING BTREE,
  INDEX `index_patientName`(`patientName`) USING BTREE COMMENT '病人姓名普通索引',
  UNIQUE INDEX `index_identityNum`(`identityNum`) USING BTREE COMMENT '身份证唯一索引'
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '病人表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of patient
-- ----------------------------
INSERT INTO `patient` VALUES (1, '123456', '1985-06-07', '女', '夏颖', '13800000001', 'ying.xia@qq.com', '110000198506071100', '厦门市');
INSERT INTO `patient` VALUES (2, '234567', '1985-06-08', '男', '李政', '13800000002', 'lizheng@163.com', '210000198506082100', '长春市');
INSERT INTO `patient` VALUES (3, '345678', '2010-03-02', '女', '李沁', '13800000003', 'liqin@sohu.com', '120000201003021200', '合肥市');
INSERT INTO `patient` VALUES (4, '456789', '1999-01-02', '女', '李思雨', '13800000004', 'siyu.li@hotmail.com', '150000199901021500', '北京市');
INSERT INTO `patient` VALUES (5, '567890', '2008-10-17', '男', '夏天', '13800000005', 'tian.xia@qq.com', '210000200810172100', '长春市');
INSERT INTO `patient` VALUES (6, '678901', '1999-03-08', '男', '刘占波', '13800000006', 'zhanbo@163.com', '210000199903082101', '惠州市');
INSERT INTO `patient` VALUES (7, '789012', '1987-05-02', '女', '廖慧颖', '13800000007', 'huiying@qq.com', '220000198705022200', '广州市');
INSERT INTO `patient` VALUES (8, '890123', '1975-03-02', '男', '李伟忠', '13800000008', 'wz@qq.com', '230000197503022300', '沈阳市');
INSERT INTO `patient` VALUES (9, '901234', '1986-10-11', '男', '姚维新', '13800000009', 'ywx@hotmail.com', '310000198610113100', '北京市');
INSERT INTO `patient` VALUES (10, '012345', '1975-03-04', '男', '陈建', '13800000010', 'cz@qq.com', '320000197503043200', '北京市');
INSERT INTO `patient` VALUES (11, '098765', '1992-01-01', '女', '林永清', '13800000011', 'yongqing@qq.com', '330000199201013300', '长春市');
INSERT INTO `patient` VALUES (12, '987654', '1993-03-02', '女', '李亚', '13800000012', 'liya@qq.com', '340000199303023400', '保定市');
INSERT INTO `patient` VALUES (13, '909000', '1995-02-12', '女', '李菲', '13887676500', 'fei.zhang@qq.com', '610000199502126100', '北京市');
INSERT INTO `patient` VALUES (14, '123456', '1996-11-11', '男', '姚维新1', '13800000009', 'ywx@hotmail.com', '310000198610113101', '北京市');

-- ----------------------------
-- Table structure for patient_address
-- ----------------------------
DROP TABLE IF EXISTS `patient_address`;
CREATE TABLE `patient_address`  (
  `patientName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '病人姓名',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '地址不详' COMMENT '地址'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of patient_address
-- ----------------------------
INSERT INTO `patient_address` VALUES ('夏颖', '厦门市');
INSERT INTO `patient_address` VALUES ('李政', '长春市');
INSERT INTO `patient_address` VALUES ('李沁', '合肥市');
INSERT INTO `patient_address` VALUES ('李思雨', '北京市');
INSERT INTO `patient_address` VALUES ('夏天', '长春市');
INSERT INTO `patient_address` VALUES ('刘占波', '惠州市');
INSERT INTO `patient_address` VALUES ('廖慧颖', '广州市');
INSERT INTO `patient_address` VALUES ('李伟忠', '沈阳市');
INSERT INTO `patient_address` VALUES ('姚维新', '北京市');
INSERT INTO `patient_address` VALUES ('陈建', '北京市');
INSERT INTO `patient_address` VALUES ('林永清', '长春市');
INSERT INTO `patient_address` VALUES ('李亚', '保定市');
INSERT INTO `patient_address` VALUES ('李菲', '北京市');

-- ----------------------------
-- Table structure for prescription
-- ----------------------------
DROP TABLE IF EXISTS `prescription`;
CREATE TABLE `prescription`  (
  `examID` int(0) NOT NULL AUTO_INCREMENT COMMENT '检查编号',
  `patientID` int(0) NOT NULL COMMENT '病人编号',
  `depID` int(0) NOT NULL COMMENT '开处方的科室编号',
  `checkResult` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '检查结果',
  `checkItemID` int(0) NOT NULL COMMENT '检查项目编号',
  `examDate` datetime(0) NOT NULL COMMENT '检查日期',
  PRIMARY KEY (`examID`) USING BTREE,
  UNIQUE INDEX `examID`(`examID`) USING BTREE,
  INDEX `fk_prescription_department`(`depID`) USING BTREE,
  INDEX `fk_patientID`(`patientID`) USING BTREE,
  CONSTRAINT `fk_patientID` FOREIGN KEY (`patientID`) REFERENCES `patient` (`patientID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_prescription_department` FOREIGN KEY (`depID`) REFERENCES `department` (`depID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '处方表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of prescription
-- ----------------------------
INSERT INTO `prescription` VALUES (1, 1, 1, '正常', 1, '2020-01-02 00:00:00');
INSERT INTO `prescription` VALUES (2, 1, 1, '正常', 2, '2020-01-02 00:00:00');
INSERT INTO `prescription` VALUES (3, 3, 2, '肺炎支原体阳性', 5, '2020-04-05 00:00:00');
INSERT INTO `prescription` VALUES (4, 1, 1, '正常', 1, '2020-02-06 00:00:00');
INSERT INTO `prescription` VALUES (5, 8, 3, '正常', 4, '2020-03-02 00:00:00');
INSERT INTO `prescription` VALUES (6, 8, 3, '血糖偏高', 3, '2020-03-02 00:00:00');
INSERT INTO `prescription` VALUES (7, 8, 3, '正常', 1, '2020-03-02 00:00:00');
INSERT INTO `prescription` VALUES (8, 10, 3, '正常', 3, '2020-03-02 00:00:00');
INSERT INTO `prescription` VALUES (9, 1, 1, '白细胞数量偏高', 1, '2020-07-08 00:00:00');
INSERT INTO `prescription` VALUES (12, 4, 3, '正常', 4, '2021-11-22 00:00:00');

-- ----------------------------
-- Table structure for subsidy
-- ----------------------------
DROP TABLE IF EXISTS `subsidy`;
CREATE TABLE `subsidy`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `patientId` int(0) NOT NULL COMMENT '病人编号',
  `income` float NOT NULL COMMENT '年收入',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of subsidy
-- ----------------------------
INSERT INTO `subsidy` VALUES (1, 1, 4800);
INSERT INTO `subsidy` VALUES (2, 8, 32000);

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `testId` int(0) NOT NULL AUTO_INCREMENT,
  `testVal` float NULL DEFAULT NULL,
  PRIMARY KEY (`testId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1101 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, 19);
INSERT INTO `test` VALUES (2, 23);
INSERT INTO `test` VALUES (3, 46);

-- ----------------------------
-- View structure for v_prescription
-- ----------------------------
DROP VIEW IF EXISTS `v_prescription`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_prescription` AS select `pa`.`patientName` AS `姓名`,`pa`.`gender` AS `性别`,`pa`.`birthDate` AS `年龄`,`c`.`checkItemName` AS `检查项目`,`pr`.`checkResult` AS `检查结果`,`d`.`depName` AS `检查科室`,`pr`.`examDate` AS `检查日期` from (((`prescription` `pr` join `patient` `pa` on((`pr`.`patientID` = `pa`.`patientID`))) join `department` `d` on((`pr`.`depID` = `d`.`depID`))) join `checkitem` `c` on((`pr`.`checkItemID` = `c`.`checkItemID`)));

-- ----------------------------
-- Procedure structure for proc_getLastExamDate
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_getLastExamDate`;
delimiter ;;
CREATE PROCEDURE `proc_getLastExamDate`(in in_name varchar(20),in in_depID int,out out_lastDate datetime)
begin
	declare pId int;#声明局部用户变量
	
	#设置病人编号的值
	select patientID into pId from patient 
	where patientName=in_name;
	#查询处方表
	#最后一次的检查时间
	select max(examDate) into out_lastDate from prescription
	where patientID=pId
	and depID=in_depID;

end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_getPatient
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_getPatient`;
delimiter ;;
CREATE PROCEDURE `proc_getPatient`()
begin
	select * from patient;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_income_calSubsidy
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_income_calSubsidy`;
delimiter ;;
CREATE PROCEDURE `proc_income_calSubsidy`(in i_patientid int ,in i_year varchar(10), out o_subsidy float)
begin
	declare t_totalCost float;
	declare t_income float default -1;
	select sum(checkItemCost) into t_totalCost 
	from prescription p1 
	inner join checkitem on p1.checkItemID = checkitem.checkItemID 
	where patientID = i_patientID 
	and examDate >= concat(i_year,'-01-01') 
	and examDate <= concat(i_year,'-12-31');
	
	select income into t_income from subsidy 
	where patientID = i_patientID;
#根据规则计算返还金额
	if t_income >=0 and t_income < 5000 then 
		set o_subsidy = t_totalcost * 0.2;
	elseif t_income >= 5000 and t_income < 10000 then 
		set o_subsidy = t_totalcost * 0.15;
	elseif t_income >= 10000 and t_income < 30000 then 
		set o_subsidy = t_totalcost * 0.05;
	else 
		set o_subsidy = 0;
	end if;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_iterateInsert
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_iterateInsert`;
delimiter ;;
CREATE PROCEDURE `proc_iterateInsert`(in i int)
begin
	declare randNum int;
  loop_label: while i>0 do
			set randNum = floor(10+rand()*(99-10));
			if randNum>50
				then iterate loop_label;
			end if;
			
			insert into test values(null,randNum);
			set i=i-1;
	end while loop_label;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_LevelScore
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_LevelScore`;
delimiter ;;
CREATE PROCEDURE `proc_LevelScore`(in score int)
begin
	declare levelStr varchar(20) default '待合格';
	
	if score>=90 and score<=100 
		then set levelStr='优秀';
	elseif score>=80 and score<90
		then set levelStr='良好';
	elseif score>=60 and score<80
		then set levelStr='合格';
	else 
	 	set levelStr='待合格,继续努力';
	end if;
	select levelStr as '你的分数评级';
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_LevelScore2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_LevelScore2`;
delimiter ;;
CREATE PROCEDURE `proc_LevelScore2`(in score int)
begin
	declare levelStr varchar(20) default '待合格';
case
	when score>=90 and score<=100 
		then set levelStr='优秀';
	when score>=80 and score<90
		then set levelStr='良好';
	when score>=60 and score<80
		then set levelStr='合格';
	else 
	 	set levelStr='待合格,继续努力';
end case;
	select levelStr as '你的分数评级';
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_loopInsert
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_loopInsert`;
delimiter ;;
CREATE PROCEDURE `proc_loopInsert`(in i int)
begin
	loop_lable: loop
			insert into test
			values(null,rand()*1000);
			set i =i-1;
			
			if i=0 then 
				leave loop_lable;
			end if;
	end loop;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_patient_totalCount
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_patient_totalCount`;
delimiter ;;
CREATE PROCEDURE `proc_patient_totalCount`()
begin
	select count(0) as '病人总人数' 
	from patient;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_patient_totalCount2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_patient_totalCount2`;
delimiter ;;
CREATE PROCEDURE `proc_patient_totalCount2`(out totalCount int)
begin
	#如何将查询到的结果赋值给输出参数？
	#使用关键字 into 
	select count(0) into totalCount from patient;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_repeatInsert
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_repeatInsert`;
delimiter ;;
CREATE PROCEDURE `proc_repeatInsert`(in i int)
begin
	repeat
			insert into test values(null,rand()*1000);
			set i =i-1;	
			until i=0
	end repeat;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_test1
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_test1`;
delimiter ;;
CREATE PROCEDURE `proc_test1`()
begin
		set @stuName='刘东东';
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_test2
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_test2`;
delimiter ;;
CREATE PROCEDURE `proc_test2`()
begin
		select concat('您的姓名为：',@stuName);
 end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_test_iterateInsert
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_test_iterateInsert`;
delimiter ;;
CREATE PROCEDURE `proc_test_iterateInsert`(in i int)
begin
	declare randNum int;
	label_loop: while  i>0 do
		set randNum=floor(10+rand()*(99-10));
		if randNum>=50
			then iterate label_loop;
		end if;	
		
		insert into test  values(null,randNum);
		
		set i=i-1;
	end while label_loop;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_test_loopInsert
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_test_loopInsert`;
delimiter ;;
CREATE PROCEDURE `proc_test_loopInsert`(in i int)
begin
	 label_loop:loop
			#rand()生成0-1之间的数
			insert into test values(null,rand());
			#迭代修改循环条件
			set i=i-1;
			#控制循环的终止条件 leave
			if i=0
				then leave label_loop;#终止循环
			end if;	
	 end loop;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_test_repeatInsert
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_test_repeatInsert`;
delimiter ;;
CREATE PROCEDURE `proc_test_repeatInsert`(in i int)
begin
	   repeat 
			#rand()生成0-1之间的数
			insert into test values(null,rand());
		 
			set i=i-1;
			
			until i=0
		 
		 end repeat;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_test_whileInsert
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_test_whileInsert`;
delimiter ;;
CREATE PROCEDURE `proc_test_whileInsert`(in i int)
begin
	while i>0 do
		insert into test values(null,rand());
		set i=i-1;
	end while;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for proc_whileInsert
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc_whileInsert`;
delimiter ;;
CREATE PROCEDURE `proc_whileInsert`(in i int)
begin
	while i>0 do
			insert into test
			values(null,rand());
			set i =i-1;
	end while;
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for p_score_leavl
-- ----------------------------
DROP PROCEDURE IF EXISTS `p_score_leavl`;
delimiter ;;
CREATE PROCEDURE `p_score_leavl`(in score int,out levelStr varchar(20))
begin
	if score>=90 and score<=100 then
		set levelStr='优秀';
	elseif score>=75 and score<90 then
		set levelStr='良好';
	elseif score>=60 and score<75 then
		set levelStr='合格';
	else
		set levelStr='待合格';
	end if;
	
	#select levelStr as '分数等级';
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for p_score_leavl2
-- ----------------------------
DROP PROCEDURE IF EXISTS `p_score_leavl2`;
delimiter ;;
CREATE PROCEDURE `p_score_leavl2`(in score int,out levelStr varchar(20))
begin
	case 
	 when score>=90 and score<=100 then
		set levelStr='优秀';
	 when score>=75 and score<90 then
		set levelStr='良好';
	 when score>=60 and score<75 then
		set levelStr='合格';
	 when core<60 then
		set levelStr='待合格';
	end case;
	
	#select levelStr as '分数等级';
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for p_while
-- ----------------------------
DROP PROCEDURE IF EXISTS `p_while`;
delimiter ;;
CREATE PROCEDURE `p_while`()
begin
declare i int default 10;
while i>0 do
	insert into test
	value(null,rand());
	set i=i-1;
end while;
end
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;

十一、恢复数据库

在需要恢复数据库数据时，对导出的SQL备份脚本执行导入操作

导入方法

使用mysql命令
使用source命令
使用Navicat导入数据

用mysql命令恢复数据库

mysql为DOS命令
mysql –u username –p [dbname] < filename.sql
注意事项：

在执行该语句之前，必须在MySQL服务器中创建新数据库

因为导出的备份文件中只包含表的备份，而不包含创建的库的语句，因此执行导入操作时必须指定数据库名，且该数据库必须存在

示例：
C:\WINDOWS\system32>mysql -uroot -p hospitalDB >E:\DB\MySQL8.0\backup\t147hospital.sql
Enter password: ****
ERROR 1049 (42000): Unknown database 'hospitaldb'

C:\WINDOWS\system32>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database hospitaldb;
Query OK, 1 row affected (0.01 sec)

mysql> quit
Bye

C:\WINDOWS\system32>mysql -uroot -p hospitalDB >E:\DB\MySQL8.0\backup\t147hospital.sql
Enter password: ****

十二、使用source命令恢复数据库

除了在命令行中导入数据以外，还可以在数据库已连接状态下导入数据

语法：

source filename;

示例：

CREATE DATABASE hospitalDB;  #创建数据库
USE hospitalDB; #选择要导入数据库的数据库
source E:\DB\MySQL8.0\backup\t147hospital.sql#导入数据,后面不要加字符“;”

注意：

登录MySQL服务后使用
执行该命令前，先创建并选择恢复后的目标数据库
SQL脚本文件后面不要加字符“; ”

十三、使用Navicat导入数据

Navicat中导入数据的操作步骤

右键单击要导入数据的数据库
右键单击“运行SQL文件…”快捷菜单项
在导入窗口，选择要运行的SQL文件
点击“开始”按钮开始导入数据

十四、本章总结

众生云海，一念初见

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录