数据库与Mysql - 10

TCL语言

Transaction Control Language 事物控制语言

事务

一条或一组SQL语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行;
-----------------------
在Mysql中用的最多的存储引擎有:Innodb Myisam Memory
其中Innodb支持事务 而Myisam Memory等不支持事务.

事务的ACID属性:

#1.原子性
#2.一致性
#3.隔离性
#4.持久性

事务的创建

事务的分类

隐式事务

事务没有明显的开启和结束标记
比如:INSERT UPDATE DELETE语句;

显示事务

事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用 ---> SET autocommit = 0;
-----------------------------------
STEP1:开启事务
	SET autocommit = 0;
	[START transaction;可选的]
STEP2:编写事务中的SQL语句(SELECT INSERT UPDATE DELETE)
	语句1;
	语句2;
	.....
[设置回滚点]
SAVEPOINT 回滚点名;
STEP3:结束事务
COMMIT;//提交事务
ROLLBACK;//回滚事务
ROLLBACK TO 回滚点名;//回滚到指定的地方
------------------------------------
***演示事物的使用步骤
#开启事务
SET autocommit = 0;
#编写一组事务的语句
UPDATE account SET balance = 500 WHERE username= '张无忌';
UPDATE account SET balance = 1500 WHERE username= '赵敏';
#结束事务
COMMIT;
(ROLLBACK);
-------------------------------------

事务的并发

事务的并发

1、事务的并发问题是如何发生的？
多个事务 同时 操作 同一个数据库的相同数据时
2、并发问题都有哪些？
脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据
不可重复读：一个事务多次读取，结果不一样
幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据
3、如何解决并发问题
通过设置隔离级别来解决并发问题
4、隔离级别
							 脏读		  不可重复读		    幻读
read uncommitted:读未提交     ×                ×              ×        
read committed：读已提交      √                ×              ×
repeatable read：可重复读     √                √              ×
serializable：串行化          √                √              √

视图

定义

视图是一种虚拟存在的表,行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的
只保存SQL逻辑,不保存查询结果

应用场景

- 多个地方用到同样的查询结果
- 该查询结果使用的SQL语句较复杂

案例:查询姓张的学生名和专业名

SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m
ON s.majorid = m.id
WHERE s.stuname LIKE "张%";
----------------------
使用视图功能后
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m
ON s.majorid = m.id;

SELECT * FROM v1 WHERE stuname LIKE '张%';

创建视图

语法

CREATE VIEW 视图名
AS
查询语句;

修改视图

方式一

CREATE OR REPLACE VIEW  视图名
AS
查询语句;
-----------------------------

方式二

ALTER VIEW 视图名
AS
查询语句;
------------------------------

删除视图

语法

DROP VIEW 视图名,视图名,.....;

查看视图

语法

DESC 视图名;
--------------------
SHOW CREATE VIEW 视图名;

视图的更新

插入

INSERT INTO 视图名 VALUES(内容,内容,....);

修改

UPDATE 视图名
SET 修改语句
WHERE 筛选语句;

删除

DELETE FROM 视图名 
WHERE 筛选条件;

具备以下特点的视图不允许更新

#1.包含以下关键字的SQL语句:
	分组函数、DISTINCT、GROUP BY 、HAVING、UNION 或者UNION ALL
#2.常量视图
#3.SELECT中包含子查询
#4.包含JOIN
#5.FROM一个不能更新的视图
#6.WHERE子句的子查询引用了FROM子句中的表

DELETE 和TRUNCATE在事务使用时的区别

演示DELETE

SET autocommit = 0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

演示TRUNCATE

TRUNCATE 不支持回滚语句 
-----------------------
SET autocommit = 0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

测试题

#1.创建表BOOK表,字段如下:

bid 整形 ,要球主键
bName 字符型,要求设置唯一键,并非空
price 浮点型 要求有默认值10
btypeId 类型编号 要求引用bookType表的 id字段

已知bookType表(不用创建),字段如下
id
name
------------------------------------------------
CREATE TABLE book(
	bid int primary key,
	bName VARCHAR UNIQUE NOT NULL,
	price FLOAT DEAULT 10,
	btypeId INT,
	FOREIGN KEY(btypeId) REFERENCE bookType(id)
);

#2.开启事务,向表中插入1行数据,并结束

SET autocommit = 0;
INSERT INTO book(bid,bName,price,btypeId)
VALUES(1,'小李飞刀',100,1)
COMMIT;

#3.创建视图、实现查询价格大于100的书名和类型名

CREATE VIEW myv1
AS
SELECT bName,name
FROM book b
JOIN bookType t 
ON b.btyoeId = b.id
WHERE price>100;

#4.修改视图,实现查询价格90-120之间的书名和价格

CREATE OR REPLACE VIEW myv1
AS
SELECT bName,price
FROM book
WHERE price BETWEEN 90 AND 120;

#6.删除刚才建的视图

DROP VIEW myv1;