事务、视图、索引、数据备份和恢复

发布时间:2024年01月09日

本课目标

理解事务的相关概念

使用事务保证操作数据的完整性

熟练创建并使用视图

熟练创建并使用索引

掌握如何进行数据库的备份和恢复

CREATE TABLE IF NOT EXISTS mybank(
  CID CHAR(4),#卡号
	CNAME VARCHAR(30) ,#用户名
	BALANCE DECIMAL(10,2) #当前余额
)CHARSET = UTF8;
#插入数据
INSERT INTO MYBANK(CID,CNAME,BALANCE) VALUES(4001,'张三',1000);
INSERT INTO MYBANK(CID,CNAME,BALANCE) VALUES(4002,'李四',1);
SELECT * FROM MYBANK;
#模拟转账,(完美状态下)张三给李四转账500元
UPDATE MYBANK SET BALANCE= BALANCE-500 WHERE CID=4001;
UPDATE MYBANK SET BALANCE= BALANCE+500 WHERE CID=4002;
SELECT * FROM MYBANK;

一、事务

1.什么是事务

事务是一种机制、一个操作序列,包含了一组数据库操作命令,并且把所有命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库操作命令要么都执行,要么都不执行

事务是一个不可分割的工作逻辑单元

2.事务的特性(简称 :ACID)
  • 原子性(Atomicity

事务的各步操作是不可分的(原子的),事务的所有操作要么都执行,要么都不执行

  • 一致性(Consistency

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

  • 隔离性(Isolation

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

  • 持久性(Durability

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

3.MySQL事务处理

MySQL中支持事务的存储引擎有 InnoDB 和 BDB

方法1:用 BEGIN , COMMIT,ROLLBACK 来实现

方法2:直接用 SET 来改变MySQL的自动提交模式

二、视图

1.什么是视图

视图是一张虚拟表,表示一张表的部分数据或多张表的综合数据

  • 其结构和数据是建立在对表的查询基础上
  • 视图中不存放数据,数据存放在试图所引用的原始表中

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

2.视图的用途
  • 筛选表中的行
  • 防止未经许可的用户访问敏感数据
  • 将多个物理数据库表抽象为一个逻辑数据库表
3.管理视图
  • 使用SQL语句创建视图

CREATE VIEW view_name?

AS

<SELECT 语句>;

  • 使用SQL 语句查看视图

SELECT 字段1, 字段2, …… FROM view_name;

  • 使用SQL 语句删除视图

DROP VIEW [IF EXISTS] view_name;

4.使用视图的注意事项
  • 视图中可以使用多个表
  • 一个视图可以嵌套另一个视图
  • 对试图数据进行添加、更新和删除操作直接影响所引用表中的数据
  • 当视图数据来自多个表时,不允许添加和删除数据
  • 一般在实际开发中视图仅用作查询
#创建一个视图,显示学生学号、姓名、性别、年级名称、课程名称、考试时间、考试结果
CREATE VIEW VM_EXAMINFO AS
SELECT S.STUDENTNO,STUDENTNAME,SEX,GRADENAME,SUBJECTNAME,EXAMDATE,STUDENTRESULT 
FROM STUDENT S 
LEFT JOIN GRADE G ON S.GRADEID= G.GRADEID 
LEFT JOIN RESULT R ON S.STUDENTNO=R.STUDENTNO
LEFT JOIN `SUBJECT` SU ON SU.SUBJECTNO=R.SUBJECTNO
ORDER BY S.STUDENTNO
#显示结果
SELECT E.*,S.ADDRESS FROM VM_EXAMINFO E JOIN STUDENT S ON E.STUDENTNO=S.STUDENTNO;
#删除视图
DROP VIEW IF EXISTS VM_EXAMINFO;
#创建视图统计每个学生所参考课程的平均成绩。
CREATE VIEW VM_AVG_STUDENTRESULT AS
SELECT STUDENTNAME,SUBJECTNAME,AVGRESULT
FROM STUDENT S LEFT JOIN (
	SELECT STUDENTNO,SUBJECTNO,AVG(STUDENTRESULT) AVGRESULT FROM RESULT GROUP BY STUDENTNO,SUBJECTNO
) T ON S.STUDENTNO=T.STUDENTNO
LEFT JOIN `SUBJECT` SU ON SU.SUBJECTNO = T.SUBJECTNO
ORDER BY S.STUDENTNO;

三、索引

1.什么是索引

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

2.作用
  • 大大提高数据库的检索速度
  • 改善数据库性能

MySQL索引按存储类型分类

  • B-树索引:InnoDB、MyISAM 均支持
  • 哈希索引
3.常见索引类型

4.管理索引
  • 使用SQL 语句创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

ON table_name (column_name[length]…);

  • 使用SQL 语句删除视图

DROP? INDEX index_name ON table_name;

#student表的studentName列创建普通索引

CREATE? INDEX `index_student_studentname`? ON `student`(`studentname`);

5.创建索引的指导原则
按照下列标准选择建立索引的列
  • 频繁搜索的列
  • 经常用作查询选择的的列
  • 经常排序、分组的列
  • 经常用作连接的列(主键/外键)
请不要使用下面的列创建索引
  • 仅包含几个不同值的列
  • 表中仅包含几行
优化SQL语句的意识
  • 查询时减少使用 * 返回全部列,不要返回不需要的列
  • 索引应该尽量小,在字节数小的列上建立索引
  • WHERE 子句中有多个条件表达式时,包含索引列的表达式应置于其他条件表达式之前
  • 避免在ORDER BY 子句中使用表达式
  • 避免在创建了索引的列上使用表达式
  • 使用复合索引时,尽量遵守最左原则
  • 根据业务数据发生频率,定期重新生成或重新组织索引,进行碎片整理
MySQL中导致索引失效的情况
  • 模糊查询like使用“name%”索引可用,“%name”索引失效

  • 组合索引包含从左到右的字段使用索引,不包含左边的字段索引失效

  • 组合索引范围搜索,范围搜索后的字段不使用索引

  • 条件字段数据类型不匹配,导致索引失效

  • 联合查询时,字符集不匹配导致索引失效

  • 不等于导致索引失效,不等于的情况包括(!= 、<、>、not in)

  • 字段内容为null,导致索引失效

  • or前后条件都包含索引则走索引,or前后有一个不包含索引索引失效

  • 添加索引的字段上使用函数或者计算,导致索引失效,函数包括ABS,UPPER,DATE,DAY,YEAR等

#对学生姓名创建索引
CREATE INDEX INDEX_STUDENT_STUDENTNAME ON STUDENT(STUDENTNAME);
SELECT * FROM STUDENT WHERE STUDENTNAME='李文才';
DROP INDEX INDEX_STUDENT_STUDENTNAME ON STUDENT;

#复合索引
CREATE INDEX INDEX_STUDENT_STUDENTNAME_PHONE_ADDRESS ON STUDENT(STUDENTNAME,PHONE,ADDRESS);
#使用复合索引,查询数据时遵守最左原则,即查找时只有第一个存在时才进行复合索引,如果第一个不在时,进行的是全表查询
#索引失效
EXPLAIN SELECT * FROM STUDENT WHERE STUDENTNAME LIKE '%丽';
#对索引列使用函数导致索引失效
EXPLAIN SELECT * FROM STUDENT WHERE SUBSTRING(STUDENTNAME,1,1)='李'

#创建索引
CREATE INDEX INDEX_LOGINPWD ON STUDENT(LOGINPWD);
#索引失效
EXPLAIN SELECT * FROM STUDENT WHERE SUBSTRING(LOGINPWD,4,3)='123';
#修改后
EXPLAIN SELECT * FROM STUDENT WHERE LOGINPWD=CONCAT('000','123');


#索引失效
EXPLAIN SELECT * FROM STUDENT WHERE BORNDATE IN(
'2004-02-28','1999-07-20','1998-12-02'
)
#修改后
SELECT * FROM STUDENT WHERE BORNDATE='2004-02-28';
UNION
SELECT * FROM STUDENT WHERE BORNDATE='1999-07-20';
UNION
SELECT * FROM STUDENT WHERE BORNDATE='1998-12-02';

#or前后都走索引,整体才走索引,

四、MySQL数据备份

数据库备份必要性

  • 保证重要数据不丢失
  • 数据转移

MySQL数据库备份方法

  • 数据库管理工具,如SQLyog
  • mysqldump 备份工具:mysqldump -u root -p database >database_backup.sql
  • 直接拷贝数据库文件和相关配置文件
1.MySQL的恢复
  • 数据库管理工具,如SQLyog
  • mysql命令

mysql –u root –p? dbname? <? /path/db_name.sql;

文章来源:https://blog.csdn.net/wisbhxnhsj/article/details/135453782
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。