04、MySQL（事务、权限、视图）

11、事务【重点】

11.1、模拟转账

　　生活当中转账是转账方账户扣钱，收账方账户加钱。我们用数据库操作来模拟现实转账。

CREATE TABLE account (
id INT,
`name` VARCHAR(16),
money INT
)

INSERT INTO account (id,NAME,money) VALUE (1,'xiaosan',1000);

INSERT INTO account (id,NAME,money) VALUE (2,'xiaoming',1000);

11.1.1、数据库模拟转账

#A账户转账给B账户1000元。
#A账户减1000元
UPDATE account SET MONEY = MONEY-1000 WHERE id=1;
#B账户加1000元
UPDATE account SET MONEY = MONEY+1000 WHERE id=2;

上述代码完成了两个账户之间转账的操作。

11.1.2、模拟转账错误

#A账户转账给B账户1000元。
#A账户减1000元
UPDATE account SET MONEY =MONEY-1000 WHERE id=1;

#断电、异常、出错...
#B账户加1000元
UPDATE account SET MONEY = MONEY+1000 WHERE id=2;

上述代码在减操作后过程中出现了异常或加钱语句出错，会发现，减钱仍旧是成功的，而加钱失败了!
注意: 每条SQL语句都是一个独立的操作，一个操作执行完对数据库是永久性的影响。

11.2、事务的概念

　　事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时,整个事务成功，有一个SQL语句执行失败,整个事务都执行失败。

11.3、事务的边界

开始: 连接到数据库，执行一条DML语句。上一个事务结束后，又输入了一条DML语句，即事务的开始

结束:

　　1)提交:

　　　　a.显示提交:commit;

　　　　b.隐式提交:一条创建、删除的语句,正常退出(客户端退出连接);

　　2)回滚:

　　　　a.显示回滚:rollback;

　　　　b.隐式回滚:非正常退出（断电、宕机),执行了创建、删除的语句，但是失败了，会为这个无效的语句执行回滚。

11.4、事务的原理

　　数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL语句均正常结束(commit),才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚(rollback)。

11.5、事务的特性

Atomicity(原子性)

　　表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败

Consistency(一致性)

　　表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态

lsolation(隔离性)

　　事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。

Durability(持久性)

　　持久性事务完成之后,它对于系统的影响是永久性的。

11.6、事务应用

应用环境: 基于增删改语句的操作结果（均返回操作后受影响的行数)，可通过程序逻辑手动控制事务提交或回滚

11.6.1、事务完成转账

#A账户给B账户转账。
#1.开启事务
START TRANSACTION;    
SET autocommit     =0;#禁止自动提交setAutoCommit=1;#开启自动提交
#2.事务内数据操作语句
UPDATE `account` SET MONEY = `money`-1000 WHERE ID =1;
UPDATE `account` SET MONEY = `moneys`+1000 WHERE ID = 2;
#3.事务内语句都成功了,执行 COMMIT;
COMMIT;
#4.事务内如果出现错误,执行 ROLLBACK;
ROLLBACK;

注意∶开启事务后，执行的语句均属于当前事务，成功再执行COMIIT，失败要进行ROLLBACK

12、权限管理

12.1、创建用户

　　CREATE USER 用户名 IDENTIFIED BY 密码

12.1.1、创建一个用户

#创建一个 xiaohe 用户
CREATE USER `xiaohe` IDENTIFIED BY '123456';

12.2、授权

　　GRANT ALL ON 数据库.表 TO 用户名;

12.2.1、用户授权

#将companyDB下的所有表的权限都赋给xiaohe
GRANT ALL ON companyDB.* TO 'xiaohe';

12.3、撤销权限

　　REVOKE ALL ON 数据库.表名 FROM 用户名

·注意: 撤销权限后，账户要重新连接客户端才会生效

12.3.1、撤销用户权限

#将xiaohe的companyDB的权限撤销
REVOKE ALL ON companyDB.* FROM `xiaohe`;

12.4、删除用户

DROP USER 用户名

12.4.1、删除用户

#删除用户 xiaohe
DROP USER `xiaohe`;

13、视图

13.1、概念

　　视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

13.2、视图特点

优点

　　简单化，数据所见即所得。

　　安全性，用户只能查询或修改他们所能见到得到的数据。

　　逻辑独立性，可以屏蔽真实表结构变化带来的影响。

·缺点

　　性能相对较差,简单的查询也会变得稍显复杂。

　　修改不方便,特变是复杂的聚合视图基本无法修改。

13.3、视图的创建

13.3.1、创建视图

语法: CREATE VIEW 视图名 AS 查询数据源表语句;

#创建 t_empInfo 的视图，其视图从 t_employees 表中查询到员工编号、员工姓名、员工邮箱、工资
CREATE VIEW t_empInfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY FROM t_employees;

13.3.2、使用视图

#查询t_empInfo视图中编号为101的员工信息
SELECT * FROM t_empInfo WHERE employee_id = '101';

13.4、视图的修改

方式一: CREATE OR REPLACE VIEW 视图名 AS 查询语句

方式二: ALTER VIEW 视图名 AS 查询语句

13.4.1、修改视图

#方式1:如果视图存在则进行修改,反之，进行创建
CREATE OR REPLACE VIEW t_empInfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME, LAST_NAME ,EMAIL ,SALARY ,DEPARTMENT_ID FROM t_employees;

#方式2:直接对已存在的视图进行修改
ALTER VIEW t_empInfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY FROM t_employees;

13.5、视图的删除

　　DROP VIEW 视图名

13.5.1、删除视图

#删除t_empInfo视图
DROP VIEW t_empInfo;

注意: 删除视图不会影响原表

13.6、视图的注意事项

注意:

　　视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。

　　如果视图包含以下结构中的一种,则视图不可更新

　　　　聚合函数的结果

　　　　DISTINCT 去重后的结果

　　　　GROUP BY分组后的结果

　　　　HAVING筛选过滤后的结果

　　　　UNION、 UNION ALL 联合后的结果

14、SQL语言分类

14.1、SQL语言分类

　　数据查询语言DQL (Data Query Language): select、where、order by、group by、having 。

　　数据定义语言DDL (Data Definition Language) : create、alter、drop。

　　数据操作语言DML (Data Manipulation Language) : insert、update、delete 。

　　事务处理语言TPL (Transaction Process Language) : commit、rollback 。

　　数据控制语言DCL (Data Control Language): grant、revoke。

15、综合练习

15.1、数据库表

#创建用户表
CREATE TABLE USER(
userId INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR (20)NOT NULL,
PASSWORD VARCHAR(18)NOT NULL,
address VARCHAR(100),
phone VARCHAR(11)
);

#创建分类表
CREATE TABLE category(
cid VARCHAR(32) PRIMARY KEY ,
cname VARCHAR(100) NOT NULL#分类名称
);

#商品表
CREATE TABLE `products`(
pid VARCHAR(32) PRIMARY KEY,
`name` VARCHAR(40),
price DOUBLE(7,2),
category_id VARCHAR(32),
CONSTRAINT fk_products_category_id FOREIGN KEY(category_id) REFERENCES category(cid)
);

#订单表
CREATE TABLE `orders`(
`oid` VARCHAR(32) PRIMARY KEY,
`totalprice` DOUBLE(12,2), #总计
`userId`  INT,
CONSTRAINT fk_orders_userId FOREIGN KEY(userId) REFERENCES USER(userId)#外键
);

#订单项表
CREATE TABLE orderitem(
oid VARCHAR(32),#订单id
pid VARCHAR(32),#商品id
num INT, #购买商品数量
PRIMARY KEY(oid, pid),#主键
CONSTRAINT fk_orderitem_oid FOREIGN KEY(oid) REFERENCES orders(oid),
CONSTRAINT fk_orderitem_pid FOREIGN KEY(pid) REFERENCES products(pid)
);

#初始化数据
#用户表添加数据
INSERT INTO USER(username ,PASSWORD, address, phone) VALUES('张三','123','北京昌平沙河','13812345678');
INSERT INTO USER(username , PASSWORD, address, phone) VALUES('王五', '5678','北京海淀','13812345141');
INSERT INTO USER(username , PASSWORD , address , phone)VALUES('赵六','123','北京朝阳','13812340987');
INSERT INTO USER(username , PASSWORD , address , phone) VALUES('田七', '123','北京大兴','13812345687');

#给分类表初始化数据
INSERT INTO category VALUES( 'c001','电器');
INSERT INTO category VALUES( 'c002','服饰');
INSERT INTO category VALUES('c003','化妆品');
INSERT INTO category VALUES( 'c004','书籍');

#给商品表初始化数据
INSERT INTO products(pid , NAME , price,category_id) VALUES( 'p001','联想' , 5000, 'c001');
INSERT INTO products(pid , NAME, price,category_id) VALUES('p002','海尔', 3000 , 'c001');
INSERT INTO products(pid , NAME ,price,category_id) VALUES( 'p003','雷神',5000, 'c001');
INSERT INTO products(pid , NAME,price,category.id) VALUES('p004','JACK JONES' , 800  'c002');
INSERT INTO products(pid , NAME , price,category_id) VALUES( 'p005','真维斯',200, 'c002');
INSERT INTO products(pid , NAME , price, category_id) VALUES( 'p006','花花公子',440, 'c002');
INSERT INTO products(pid ,NAME,price,category_id) VALUES( 'p007','劲霸' ,2000,'c002');
INSERT INTO products(pid,NAME, price,category_id) VALUES( 'p008','香奈儿' ,800,'c003');
INSERT INTO products(pid , NAME , price,category_id) VALUES( 'p009','相宜本草' ,200,'c003');
INSERT INTO products(pid , NAME ,price,category_id) VALUES( 'p010','梅明子',200,NULL);

#添加订单
INSERT INTO orders VALUES ( 'o6100' , 18000.50,1);
INSERT INTO orders VALUES( 'o6101',7200.35,1);
INSERT INTO orders VALUES( 'o6102',600.00,2);
INSERT INTO orders VALUES ( 'o6103' , 1300.26,4);

#订单详情表
INSERT INTO orderitem VALUES( 'o6100','p001',1), ( 'o6100','p002',1),( 'o6101','p003',1);

15.2、综合练习1-【多表查询】

15.2.1、查询所有用户的订单

SELECT o.oid, o.totalprice,u.userId, u.username , u.phone
FROM orders o 
INNER JOIN USER u ON o.userId=u.userId;

15.2.2、查询用户id为1的所有订单详情

SELECT o.oid,o.totalprice,u.userId,u.username,u.phone,oi.pid
FROM orders o 
INNER JOIN USER u ON o.userId = u.userId
INNER JOIN orderitem oi ON o.oid=oi.oid
WHERE u.userid=1;

15.3、综合练习2-【子查询】

15.3.1、查看用户为张三的订单

SELECT * FROM orders WHERE userId=(SELECT userid FROM USER WHERE username='张三');

15.3.2、查询出订单的价格大于800的所有用户信息。

SELECT * FROM USER WHERE userId IN(SELECT DISTINCT userId FROM orders WHERE totalprice> 800);

15.4、综合练习3-【分页查询】

15.4.1、查询所有订单信息,每页显示5条数据

#查询第一页
SELECT * FROM orders LIMIT 0,5;

posted @ 2021-07-09 22:44 菜鸟的道路阅读(52) 评论(0) 收藏举报

刷新页面返回顶部

Loading

菜鸟的道路