一、事务
1.1 模拟银行转账案例
#创建表并添加数据
CREATE TABLE IF NOT EXISTS mybank(
cid CHAR(4), #卡号
cname VARCHAR(30),#用户名
balance DECIMAL(10,2) #当前余额
);
INSERT INTO mybank(cid,cname,balance)VALUES(4001,'张三',1000);
INSERT INTO mybank(cid,cname,balance)VALUES(4002,'李四',1);
SELECT * FROM MYBANK;
#转账的理想操作(张三账户少500,李四账户加500)
UPDATE MYBANK SET BALANCE = BALANCE - 500 WHERE CID = 4001;
UPDATE MYBANK SET BALANCE = BALANCE + 500 WHERE CID = 4002;
1.2 什么是事务
事务(transaction)是一种机制,一个操作序列,包含了一组数据库操作命令,并且把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库操作命令要么都执行,要么都不执行。事务是一个不可分割的工作逻辑单元。
1.3 事务的特性
实物必须具备以下四个属性,简称ACID属性:
-
原子性(Atomicity)
事务的各部操作时不可分的(原子的),要么都执行,要么都不执行
-
一致性(Consistency)
当事务完成时,数据必须处于一致状态
-
隔离性(Lsolation)
并发事物之间彼此隔离、独立,它不应以任何方式依赖于或影响其他事务
-
持久性(Durability)
事务完成后,它对数据库的修改被永久保持
1.4 MySQL事务处理
MySQL中支持事务的存储引擎有InnoDB和BDB
方法一:用BEGIN,COMMIT,ROLLBACK实现
方法二:直接用SET改变MySQL的自动提交模式
1.5 使用事务解决银行转账问题
#事务
#方法一:
BEGIN;
UPDATE MYBANK SET BALANCE=BALANCE-500 WHERE CID=4001;
UPDATE MYBANK SET BALANCE=BALANCE+500 WHERE CID=4002;
SELECT * FROM MYBANK;
#COMMIT;
ROLLBACK;
SELECT * FROM MYBANK;
#方法二:
#关闭事务自动提交
SET AUTOCOMMIT = 0;
UPDATE MYBANK SET BALANCE = BALANCE + 500 WHERE CID = 4001;
UPDATE MYBANK SET BALANCE = BALANCE + 500 WHERE CID = 4002;
#手动提交事务
COMMIT;
#回滚事务(撤回)
ROLLBACK;
#开启自动提交
SET AUTOCOMMIT = 1;
二、视图
2.1 什么是视图
视图是一张虚拟表,表示一张表的部分数据或多张表的综合数据。其结构和数据是建立在对表的查询基础上。视图中不存放数据,数据存放在视图所引用的原始表中
一个原始表,根据不同用户的不同需求(权限),可以创建不同的视图
2.2 视图的用途
- 筛选表中的行
- 防止未经许可的用户访问敏感数据
- 将多个物理数据库表抽象为一个逻辑数据库表
2.3 管理视图
使用SQL语句创建视图
使用SQL语句查看视图
使用SQL语句删除视图
#创建一个视图,显示学生的学号、姓名、性别、年级名称、课程名称、考试时间、考试结果
#创建视图
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
#删除视图
DROP VIEW IF EXISTS VM_EXAMINFO;
2.4 使用视图的注意事项
- 视图中可以使用多个表
- 一个视图可以嵌套另一个视图
- 对视图数据进行添加、更新和删除操作会直接影响所引用表中的数据
- 当视图数据来自多个表时,不允许添加和删除数据
2.5 练习:查看学生各科目考试成绩的平均分
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;
三、索引
3.1 什么是索引
汉语字典中的汉字按页存放,一般都有汉语拼音目录(索引)、偏旁部首目录等。我们可以根据拼音或偏旁部首,快速查找某个字。
索引是一种有效组合数据的方式,为快速查找到指定记录
3.2 索引的作用
- 大大提高数据库的检索速度
- 改善数据库性能
3.3 索引的分类
MySQL索引按存储类型分类
- B-树索引:InnoDB、MyISAM均支持
- 哈希索引
3.4 常见索引类型
3.5 管理索引
使用SQL语句创建索引
使用SQL语句删除视图
3.6 创建索引的指导原则
按照下列标准选择建立索引的列
- 频繁搜索的列
- 经常用做查询选择的列
- 经常排序、分组的列
- 经常用做连接的列(主键 / 外键)
不要使用下面的列创建索引
- 仅包含几个不同值得列
- 表中仅有几行
3.7 优化SQL语句的意识
- 查询时间少使用*返回全部列,不要返回不需要的列
- 索引应该尽量小,在字节数小的列上建立索引
- WHERE子句中有多个条件表达式时,包含索引列的表达式应置于其他条件表达式之前
- 避免在ORDER BY子句中使用表达式
- 避免在创建了索引的列上使用表达式
- 使用复合索引时,尽量遵循最左原则
- 根据业务数据发生频率,定期重新生成或重新组织索引,进行碎片管理
四、数据库备份
待完善……