Mysql进阶学习(九)TCL语言(事务)&视图&变量
TCL语言(事务)
TCL:Transaction Control Language
事务控制语言
事务:
一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。
事务:事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态;如果单元中的所有SQL语句均执行成功,则事物被顺利执行。
存储引擎
SHOW ENGINES;
案例:转账
张三丰 1000
郭襄 1000
update 表 set 张三丰的余额=500 where name='张三丰'
意外
update 表 set 郭襄的余额=1500 where name='郭襄'
上述发生意外,会导致数据错误
因此事务用在此类地方
事务的特性:
ACID
原子性:一个事务不可再分割,要么都执行要么都不执行
一致性:一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性:一个事务的执行不受其他事务的干扰
持久性:一个事务一旦提交,则会永久的改变数据库的数据.
事务的操作步骤概述
事务的创建
隐式事务:事务没有明显的开启和结束的标记
比如insert、update、delete
语句
delete from 表 where id =1;
显式事务:事务具有明显的开启和结束的标记
前提:必须先设置自动提交功能为禁用
set autocommit=0;
步骤1:开启事务
set autocommit=0;
start transaction;可选的
步骤2:编写事务中的sql语句(select insert update delete)
语句1;
语句2;
…
步骤3:结束事务
commit;提交事务
rollback;回滚事务
savepoint 节点名;设置保存点
脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段.
之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段.
之后, T1再次读取同一个字段, 值就不同了.
幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.
事务的隔离
数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.
数据库的隔离级别
一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱
Oracle 支持的 2 种事务隔离级别:READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED
Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ
read committed
read-committed
serializable
事务的隔离级别:
脏读 | 不可重复读 | 幻读 | |
---|---|---|---|
read uncommitted: | √ | √ | √ |
read committed: | × | √ | √ |
repeatable read: | × | × | √ |
serializable | × | × | × |
mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;
开启事务的语句;
update 表 set 张三丰的余额=500 where name='张三丰'
update 表 set 郭襄的余额=1500 where name='郭襄'
结束事务的语句;
SHOW VARIABLES LIKE 'autocommit';
SHOW ENGINES;
1.演示事务的使用步骤
开启事务
SET autocommit=0;
START TRANSACTION;
编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';
结束事务
ROLLBACK;
#commit;
SELECT * FROM account;
2.演示事务对于delete和truncate的处理的区别
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
delete 支持回滚
SET autocommit = 0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
trauncate 不支持回滚
SET autocommin = 0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;
3.演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点
SELECT * FROM account;
视图
含义:虚拟表,和普通表一样使用
mysql5.1版本出现的新特性,是通过表动态生成的数据
比如:临时舞蹈班和普通班级的对比
视图与表的对比
创建语法的关键字 | 是否实际占用物理空间 | 使用 | |
---|---|---|---|
视图 | create view | 只是保存了sql逻辑 | 增删改查,只是一般不能增删改 |
表 | create table | 保存了数据 | 增删改查 |
案例:查询姓张的学生名和专业名
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 '张%';
1. 创建视图
语法:
create view 视图名
as
查询语句;
USE myemployees;
1.查询姓名中包含a字符的员工名、部门名和工种信息
①创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
2.查询各部门的平均工资级别
①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;
②使用
SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
3.查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
4.查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;
2.视图的修改
方式一:
create or replace view 视图名
as
查询语句;
SELECT * FROM myv3
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
方式二:
语法:
alter view 视图名
as
查询语句;
ALTER VIEW myv3
AS
SELECT * FROM employees;
3.删除视图
语法:drop view 视图名,视图名,…;
DROP VIEW emp_v1,emp_v2,myv3;
4.查看视图
DESC myv3;
SHOW CREATE VIEW myv3;
5.视图的更新
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
SELECT * FROM myv1;
SELECT * FROM employees;
1.插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com');
2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';
4.具备以下特点的视图不允许更新
①包含以下关键字的sql语句:分组函数、distinct、group by、having、union
或者union all
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;
SELECT * FROM myv1;
更新
UPDATE myv1 SET m=9000 WHERE department_id=10;
代码不允许更新
查询:UPDATE myv1 SET m=9000 WHERE department_id=10错误代码: 1288
The target table myv1 of the UPDATE is not updatable
执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0.009 sec
②常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
SELECT * FROM myv2;
更新
UPDATE myv2 SET NAME='lucy';
代码不允许更新
查询:UPDATE myv2 SET NAME='lucy'错误代码: 1288
The target table myv2 of the UPDATE is not updatable
执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec
③Select中包含子查询
CREATE OR REPLACE VIEW myv3
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;
SELECT * FROM myv3;
#更新
UPDATE myv3 SET 最高工资=100000;
代码不允许更新
查询:UPDATE myv3 SET 最高工资=100000错误代码: 1288
The target table myv3 of the UPDATE is not updatable
执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0.001 sec
④join
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;
SELECT * FROM myv4;
更新
UPDATE myv4 SET last_name = '张飞' WHERE last_name='Whalen';
INSERT INTO myv4 VALUES('陈真','xxxx');
不允许更新或插入
查询:UPDATE myv4 SET last_name = '张飞' WHERE last_name='Whalen'错误代码: 1146
Table 'myemployees.myv4' doesn't exist
执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0.001 sec
--------------------------------------------------
查询:INSERT INTO myv4 VALUES('陈真','xxxx')错误代码: 1146
Table 'myemployees.myv4' doesn't exist
执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0 sec
⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;
#查询myv5中数据
SELECT * FROM myv5;
更新
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
不允许更新
查询:UPDATE myv5 SET 最高工资=10000 WHERE department_id=60错误代码: 1288
The target table myv5 of the UPDATE is not updatable
执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0.001 sec
⑥where子句的子查询引用了from子句中的表
案例:查询领导姓名,邮箱,工资
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL
);
# 查询myv6表中数据
SELECT * FROM myv6;
更新
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
6. 案例解析
1、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees
WHERE phone_number LIKE '011%';
2、创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;
SELECT d.*,m.mx_dep
FROM departments d
JOIN emp_v2 m
ON m.department_id = d.`department_id`;
变量
变量分类
系统变量: 全局变量、 会话变量
自定义变量: 用户变量、 局部变量
1.系统变量
说明:变量由系统定义,不是用户定义,属于服务器层面
注意:全局变量需要添加global关键字,会话变量需要添加session关键字,如果不写,默认会话级别
使用步骤:
1、查看所有系统变量
show global|【session】variables;
2、查看满足条件的部分系统变量
show global|【session】 variables like '%char%';
3、查看指定的系统变量的值
select @@global|【session】系统变量名;
4、为某个系统变量赋值
方式一:
set global|【session】系统变量名=值;
方式二:
set @@global|【session】系统变量名=值;
1.1 全局变量
作用域:针对于所有会话(连接)有效,但不能跨重启
①查看所有全局变量
SHOW GLOBAL VARIABLES;
②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
③查看指定的系统变量的值
SELECT @@global.autocommit;
④为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
1.2 会话变量
作用域:针对于当前会话(连接)有效
①查看所有会话变量
SHOW SESSION VARIABLES;
②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
③查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
④为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';
2. 自定义变量
说明:变量由用户自定义,而不是系统提供的
使用步骤:
1、声明
2、赋值
3、使用(查看、比较、运算等)
2.1 用户变量
作用域:针对于当前会话(连接)有效,作用域同于会话变量
赋值操作符:=或:=
①声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
②赋值(更新变量的值)
方式一:
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
方式二:
SELECT 字段 INTO @变量名
FROM 表;
③使用(查看变量的值)
SELECT @变量名;
2.2 局部变量
作用域:仅仅在定义它的begin end块中有效
应用在 begin end中的第一句话
①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;
②赋值(更新变量的值)
方式一:
SET 局部变量名=值;
SET 局部变量名:=值;
SELECT 局部变量名:=值;
方式二:
SELECT 字段 INTO 具备变量名
FROM 表;
③使用(查看变量的值)
SELECT 局部变量名;
案例:声明两个变量,求和并打印
用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;
局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;
2.3 用户变量和局部变量的对比
作用域 | 定义位置 | 语法 | |
---|---|---|---|
用户变量 | 当前会话 | 会话的任何地方 | 加@符号,不用指定类型 |
局部变量 | 定义它的BEGIN END中 | BEGIN END的第一句话 | 一般不用加@,需要指定类型 |