SQL事务、SQL权限管理、SQL视图

一、事务

start transaction;#开始事务
commit;#提交
rollback;#回滚

为什么使用事务

CREATE TABLE account(
	id INT,
	money INT
)CHARSET=utf8;
INSERT INTO account(id,money) VALUES(1,10000);
INSERT INTO account(id,money) VALUES(2,1000);

SELECT *FROM account;

#模拟转账，账户1给2转1000
UPDATE account SET money = money -1000 WHERE id = 1;
UPDATE account SET money = money +1000 WHERE id = 2;

以上代码如果在给账户1的减1000后但是给账户2加1000的操作由错误

使用需要事务

事务是一个原子操作，在一个事务中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败

DROP TABLE account;

START TRANSACTION;
CREATE TABLE account(
	id INT,
	money INT
)CHARSET=utf8;
INSERT INTO account(id,money) VALUES(1,10000);
INSERT INTO account(id,money) VALUES(2,1000);

SELECT *FROM account;

#模拟转账，账户1给2转1000
UPDATE account SET money = money -1000 WHERE id = 1;
UPDATE account SET money = money +1000 WHERE id = 2;

COMMIT;
ROLLBACK;

rollback如果有SQL语句执行失败，则所有操作回到最初的状态

二、权限管理

create user 'zhangsan' identified by '1234';

上述语句执行成功后
新建连接mysql2
就可以用；用户名：zhangsan 密码：1234 端口号：3308重新登录一个
但是没有查看权限

#创建用户
CREATE USER 'zhangsan' IDENTIFIED BY '1234';
#授权
GRANT ALL ON companydb.* TO 'zhangsan';
#撤销权限
REVOKE ALL ON companydb.* FROM 'zhangsan';
#删除用户
DROP USER 'zhangsan';

三、视图概念、创建、使用

#创建视图
CREATE VIEW t_empinfo
AS
SELECT employee_id,first_name,last_name,email,salary 
FROM t_employees;
#使用视图
SELECT * FROM t_empinfo WHERE employee_id = '100';
#如果视图存在则修改，反之，则进行创建
CREATE OR REPLACE VIEW t_empinfo 
AS
SELECT employee_id,first_name,last_name,email,salary,department_id
FROM t_employees;
#直接对已存在的视图修改
ALTER VIEW t_empinfo
AS 
SELECT employee_id,first_name,last_name,email,salary,department_id
FROM t_employees;