1200字范文 > MySQL 06 事务视图索引备份和恢复

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

时间：2021-08-23 18:04:05

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

文章目录

MySQL 06 事务、视图、索引、备份和恢复一、学习目标二、事务2.1什么是事务2.2事务的特性2.3为什么需要事务三、使用事务3.1提交支付平台转账操作3.2回滚支付平台转账操作3.3自动关闭和开启事务3.4使用事务时要遵循的原则3.4.1事务尽可能简短3.4.2事务中访问的数据量尽量最少3.4.3查询数据时尽量不要使用事务3.4.4在事务处理过程中尽量不要出现等待用户输入的操作四、索引4.1什么是索引4.2索引创建注意事项4.3索引的缺点常用索引类型：五、创建、删除索引5.1创建、删除索引5.2创建索引的指导原则5.2.1按照下列标准选择建立索引的列5.2.2请不要使用下面的列创建索引5.3使用索引注意事项：六、查看、删除索引6.1查看索引6.2删除索引七、视图7.1为什么需要视图?7.2什么是视图？7.3视图的用途：7.4创建视图、删除视图、查看视图7.5查看所有视图7.6使用经验7.7注意事项7.8演示示例八、数据库备份为什么要进行数据备份？九、mysqldump命令备份数据库9.1语法简介9.2操作示例9.3常用参数选项9.4备份文件包含的主要信息十、使用Navicat备份数据库十一、恢复数据库十二、使用source命令恢复数据库十三、使用Navicat导入数据十四、本章总结

一、学习目标

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

二、事务

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.创建该支付平台的数据库paycorpcreate database paycorp;#2.在数据库paycorp中，创建账户信息表accountcreate 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-2000where accountName='小王'> Affected rows: 1> 时间: 0.088supdate 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_nameON table_name (column_name [length] …);
示例
#为病人表的病人姓名创建普通索引create index index_patientNameon patient (patientName);#为病人表的身份证创建唯一约束create unique index index_idCardon 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 SELECTpa.patientName AS 姓名,pa.gender AS 性别,pa.birthDate AS 年龄,c.checkItemName AS 检查项目,pr.checkResult AS 检查结果,d.depName AS 检查科室,pr.examDate AS 检查日期 FROMprescription prINNER JOIN patient pa ON pr.patientID = pa.patientIDINNER JOIN department d ON pr.depID = d.depIDINNER 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\hospital0426.sql#备份多个表mysqldump -uroot -p hospital patient department >E:\DB\MySQL8.0\backup\hospital0427.sql
导出的SQL脚本中两种注释
以“–”开头：关于SQL语句的注释信息以“/!”开头, “/”结尾：是关于MySQL服务器相关的注释
注意事项：
为保证账户密码安全，命令中可不写密码但是，参数“-p”必须有，回车后根据提示写密码
9.3常用参数选项
9.4备份文件包含的主要信息
备份后文件包含信息MySQL服务器及mysqldump工具的版本号
备份账户的名称
主机信息
备份的数据库名称
SQL语句注释和服务器相关注释
CREATE和INSERT语句

十、使用Navicat备份数据库

Navicat也可以用于导出数据库的备份脚本
操作
右键点选数据库->转储SQL文件->结构和数据… 选择导出文件的保存路径和文件名后，便可导出数据库脚本
查看导出的备份脚本文件
/*Navicat Premium Data TransferSource Server : T147MySqlSource Server Type : MySQLSource Server Version : 80026Source Host : localhost:3306Source Schema : t147hospitalTarget Server Type : MySQLTarget Server Version : 80026File Encoding : 65001Date: 27/04/ 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@', '110000198506071100', '厦门市');INSERT INTO `patient` VALUES (2, '234567', '1985-06-08', '男', '李政', '13800000002', 'lizheng@', '210000198506082100', '长春市');INSERT INTO `patient` VALUES (3, '345678', '-03-02', '女', '李沁', '13800000003', 'liqin@', '12000003021200', '合肥市');INSERT INTO `patient` VALUES (4, '456789', '1999-01-02', '女', '李思雨', '13800000004', 'siyu.li@', '150000199901021500', '北京市');INSERT INTO `patient` VALUES (5, '567890', '-10-17', '男', '夏天', '13800000005', 'tian.xia@', '21000010172100', '长春市');INSERT INTO `patient` VALUES (6, '678901', '1999-03-08', '男', '刘占波', '13800000006', 'zhanbo@', '210000199903082101', '惠州市');INSERT INTO `patient` VALUES (7, '789012', '1987-05-02', '女', '廖慧颖', '13800000007', 'huiying@', '220000198705022200', '广州市');INSERT INTO `patient` VALUES (8, '890123', '1975-03-02', '男', '李伟忠', '13800000008', 'wz@', '230000197503022300', '沈阳市');INSERT INTO `patient` VALUES (9, '901234', '1986-10-11', '男', '姚维新', '13800000009', 'ywx@', '310000198610113100', '北京市');INSERT INTO `patient` VALUES (10, '012345', '1975-03-04', '男', '陈建', '13800000010', 'cz@', '320000197503043200', '北京市');INSERT INTO `patient` VALUES (11, '098765', '1992-01-01', '女', '林永清', '13800000011', 'yongqing@', '33000019913300', '长春市');INSERT INTO `patient` VALUES (12, '987654', '1993-03-02', '女', '李亚', '13800000012', 'liya@', '340000199303023400', '保定市');INSERT INTO `patient` VALUES (13, '909000', '1995-02-12', '女', '李菲', '13887676500', 'fei.zhang@', '610000199502126100', '北京市');INSERT INTO `patient` VALUES (14, '123456', '1996-11-11', '男', '姚维新1', '13800000009', 'ywx@', '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, '-01-02 00:00:00');INSERT INTO `prescription` VALUES (2, 1, 1, '正常', 2, '-01-02 00:00:00');INSERT INTO `prescription` VALUES (3, 3, 2, '肺炎支原体阳性', 5, '-04-05 00:00:00');INSERT INTO `prescription` VALUES (4, 1, 1, '正常', 1, '-02-06 00:00:00');INSERT INTO `prescription` VALUES (5, 8, 3, '正常', 4, '-03-02 00:00:00');INSERT INTO `prescription` VALUES (6, 8, 3, '血糖偏高', 3, '-03-02 00:00:00');INSERT INTO `prescription` VALUES (7, 8, 3, '正常', 1, '-03-02 00:00:00');INSERT INTO `prescription` VALUES (8, 10, 3, '正常', 3, '-03-02 00:00:00');INSERT INTO `prescription` VALUES (9, 1, 1, '白细胞数量偏高', 1, '-07-08 00:00:00');INSERT INTO `prescription` VALUES (12, 4, 3, '正常', 4, '-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)begindeclare pId int;#声明局部用户变量#设置病人编号的值select patientID into pId from patient where patientName=in_name;#查询处方表#最后一次的检查时间select max(examDate) into out_lastDate from prescriptionwhere patientID=pIdand depID=in_depID;end;;delimiter ;-- ------------------------------ Procedure structure for proc_getPatient-- ----------------------------DROP PROCEDURE IF EXISTS `proc_getPatient`;delimiter ;;CREATE PROCEDURE `proc_getPatient`()beginselect * 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)begindeclare 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)begindeclare randNum int;loop_label: while i>0 doset randNum = floor(10+rand()*(99-10));if randNum>50then 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)begindeclare levelStr varchar(20) default '待合格';if score>=90 and score<=100 then set levelStr='优秀';elseif score>=80 and score<90then set levelStr='良好';elseif score>=60 and score<80then 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)begindeclare levelStr varchar(20) default '待合格';casewhen score>=90 and score<=100 then set levelStr='优秀';when score>=80 and score<90then set levelStr='良好';when score>=60 and score<80then 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)beginloop_lable: loopinsert into testvalues(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`()beginselect 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)beginrepeatinsert into test values(null,rand()*1000);set i =i-1;until i=0end repeat;end;;delimiter ;-- ------------------------------ Procedure structure for proc_test1-- ----------------------------DROP PROCEDURE IF EXISTS `proc_test1`;delimiter ;;CREATE PROCEDURE `proc_test1`()beginset @stuName='刘东东';end;;delimiter ;-- ------------------------------ Procedure structure for proc_test2-- ----------------------------DROP PROCEDURE IF EXISTS `proc_test2`;delimiter ;;CREATE PROCEDURE `proc_test2`()beginselect 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)begindeclare randNum int;label_loop: while i>0 doset randNum=floor(10+rand()*(99-10));if randNum>=50then 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)beginlabel_loop:loop#rand()生成0-1之间的数insert into test values(null,rand());#迭代修改循环条件set i=i-1;#控制循环的终止条件 leaveif i=0then 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)beginrepeat #rand()生成0-1之间的数insert into test values(null,rand());set i=i-1;until i=0end 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)beginwhile i>0 doinsert 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)beginwhile i>0 doinsert into testvalues(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))beginif score>=90 and score<=100 thenset levelStr='优秀';elseif score>=75 and score<90 thenset levelStr='良好';elseif score>=60 and score<75 thenset levelStr='合格';elseset 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))begincase when score>=90 and score<=100 thenset levelStr='优秀';when score>=75 and score<90 thenset levelStr='良好';when score>=60 and score<75 thenset levelStr='合格';when core<60 thenset levelStr='待合格';end case;#select levelStr as '分数等级';end;;delimiter ;-- ------------------------------ Procedure structure for p_while-- ----------------------------DROP PROCEDURE IF EXISTS `p_while`;delimiter ;;CREATE PROCEDURE `p_while`()begindeclare i int default 10;while i>0 doinsert into testvalue(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.sqlEnter password: ****ERROR 1049 (42000): Unknown database 'hospitaldb'C:\WINDOWS\system32>mysql -uroot -pEnter password: ****Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 33Server version: 8.0.26 MySQL Community Server - GPLCopyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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> quitByeC:\WINDOWS\system32>mysql -uroot -p hospitalDB >E:\DB\MySQL8.0\backup\t147hospital.sqlEnter password: ****

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

除了在命令行中导入数据以外，还可以在数据库已连接状态下导入数据
语法：
source filename;
示例：
CREATE DATABASE hospitalDB; #创建数据库USE hospitalDB; #选择要导入数据库的数据库source E:\DB\MySQL8.0\backup\t147hospital.sql#导入数据,后面不要加字符“;”
注意：
登录MySQL服务后使用执行该命令前，先创建并选择恢复后的目标数据库SQL脚本文件后面不要加字符“; ”