小白学习Java第三十天

今日内容

1. DCL  数据控制语言
2. TCL  事务控制语言
3. 视图
4. 存储过程
5. 触发器
6. 存储引擎

一．DCL

DCL (Data Control Language)数据控制语言:用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。

关键字：

1.grant：授权。

2.revoke：收回权限。

1.查看当前数据库系统中的用户：select * from mysql.user

2.创建新用户，默认没有权限

         语法：create user '用户名' @'权限' identified by '密码';

  权限说明：如果只允许从本机登陆，则填　‘localhost’ ，如果允许从远程登陆，则填 ‘%’

3.分配权限：grant 权限 on 库名.表名 to '用户名'@'权限'

权限：表示要授予什么权力，例如可以有 select ， insert ，delete，update等，如果要授予全部权力，则填 ALL。

库名.表名：表示用户的权限能用在哪个库的哪个表中，如果想要用户的权限作用于所有的数据库所有的表，则填 *.*，*是一个通配符，表示全部。

4.取消权限：revoke 权限 on 库名.表名 from '用户名'@'权限'

5.设置修改用户密码：SET PASSWORD FOR '用户名'@'权限'=PASSWORD("新密码")

6.删除用户：drop user '用户名'@'权限'

7.查看用户的权限：show grants for '用户名'@'权限'

-- DCL -- 1.查看当前有哪些数据库用户 SELECT * FROM mysql.user; -- 2.创建新用户：create user '用户名' @'权限' identified by '密码'; -- 权限:localhost(只能本地登录)   %（可以远程登录） CREATE USER 'doudou'@'%' IDENTIFIED BY '1234'; -- 3.给新创建的用户分配权限：grant 权限 on 库名.表名 to '用户名'@'权限' -- 权限 select update delete insert create drop .....  all(所有权限) GRANT SELECT ON studb.* TO 'doudou'@'%'; GRANT INSERT ON studb.* TO 'doudou'@'%'; GRANT ALL ON studb.* TO 'doudou'@'%'; GRANT ALL ON *.* TO 'doudou'@'%'; -- 4.收回权限：revoke 权限 on 库名.表名 from '用户名'@'权限' REVOKE ALL ON *.* FROM 'doudou'@'%'; REVOKE SELECT ON studb.* FROM 'doudou'@'%'; REVOKE ALL ON studb.* FROM 'doudou'@'%'; -- 5.查看当前用户的权限：show grants for '用户名'@'权限' SHOW GRANTS FOR 'doudou'@'%'; -- 6.修改用户密码：SET PASSWORD FOR '用户名'@'权限'=PASSWORD("新密码") SET PASSWORD FOR 'doudou'@'%'=PASSWORD('4321'); -- 7.删除用户 drop user 用户名 DROP USER 'doudou'@'%';

二．事务（TCL）

1.概念

事务是数据库管理系统执行过程中一个最小的不可再分的逻辑单元，通常一个事务对应一个完整的业务操作(例如：银行账户转账业务，该业务就是一个最小的工作单元)。

一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成。

1. 特性

事务的特性（ACID属性）：

1. 原子性：事务是最小的逻辑单元，不能再分。
2. 一致性：事务在执行前后，数据是保持一致的。
3. 隔离性：并发性。多个事务之间相互独立，互不干扰。
4. 持久性：事务一旦执行结束（事务提交/事务回滚），数据就保持一种永久状态。  

1. 事务的分类

自动提交事务  手动提交事务

1. 自动提交事务：默认情况下mysql中的自动事务是处理开启状态，因此当我们在执行一条DML语句时，事务是自动提交的。可以通过以下语句查看：

SHOW VARIABLES LIKE '%autocommit%';

如果不想自动提交事务，想使用手动提交事务，则使用下面语句修改：

SET SESSION autocommit = off/0;

2）手动提交事务：

start transaction

事务语句；（insert update delete）

commit/rollback

commit:成功提交

rollback：失败回滚

事务结束的标志要么提交，要么回滚。如果没有执行提交或回滚操作，如果是使用视图工具的话，当关闭会话窗口时，会自动回滚事务，也会使得事务结束。

4.MySQL的事务操作

处理事务的SQL语句：

1. start transaction  开始事务
2. commit  提交事务（所有的SQL语句都执行成功，才提交）
3. rollback  回滚事务（只要有一步操作没有执行成功，都要回滚）

银行转账：

CREATE TABLE account(

id INT PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(30) NOT NULL,

balance DOUBLE UNSIGNED

);

SELECT * FROM account;

INSERT INTO account(username,balance)VALUES('张三',1000);

INSERT INTO account(username,balance)VALUES('李四',2000);

TRUNCATE TABLE account;

-- 张三给李四转1000

-- 1.张三的钱减少1000

UPDATE account SET balance=balance-1000 WHERE username='张三';

-- 2.李四的钱增加1000

UPDATE account SET balance=balance+1000 WHERE username='李四';

-- 事务：

-- 开始事务

START TRANSACTION;

-- 1.张三的钱减少1000

UPDATE account SET balance=balance-1000 WHERE username='张三';

-- 2.李四的钱增加1000

UPDATE account SET balance=balance+1000 WHERE username='李四';

-- 提交事务

COMMIT;

-- 回滚事务

ROLLBACK;

2.事务并发操作问题（理解）

事务的并发操作会带来哪些问题?
1.脏读

2.不可重复读

3.幻读

1.A事务做了一个查询操作，此时查询到1号的年龄是16，这时事务B做了一个修改操作，将其年龄改成18，但并未提交事务，这时，事务A又做了一次查询操作，这时就会读到事务B修改了但未提交的数据18。一个事务读到了另外一个事务未提交的数据叫脏读。

 

2.A事务做了一个查询操作，此时查询到1号的年龄是16，这时事务B做了一个修改操作，将其年龄改成18，同时提交事务，这时，事务A又做了一次查询操作，就会得到与第一次查询不一样的结果18。一个事务读到了另外一个事务已提交的数据叫不可重复读。

 

3.事务A做了一个范围查询，查询到1条记录，这时事务B做了一个添加操作并提交事务，当事务A再次执行相同的查询时，发现多了一条记录，好像产生了幻觉，这种叫幻读。

 

三种情况的区别：

脏读读的是未提交的数据，即内存中的数据；不可重复读和幻读读的都是已提交的数据，而不可重复读与幻读之间的区别在于，不可重复读主要针对update/delete操作，而幻读主要针对insert操作。

3.事务的隔离级别（理解）

事务并发的三大问题其实都是数据库读一致性的问题，那么如何解决这个问题呢？

读一致性指的是在一个事务中，无论读多少次，得到的结果都应该是相同的。

事务的隔离级别:

事务隔离级别	脏读	不可重复读	幻读
读未提交（read uncommitted）RU	是	是	是
不可重复读（read committed）RC	否	是	是
可重复读（repeatable read）  RR	否	否	对于InnoDB否
串行化（serializable）         S	否	否	否

RU是最低的事务隔离级别，可以读到一个事务未提交的数据，什么问题都没有解决，无法实现并发操作；

RC只能读取到已提交的数据，解决了脏读问题，但是存在不可重复读问题，多次读取的数据可能不一样；

RR可重复读，解决了脏读和不可重复读的问题，但在添加操作上会存储在幻读问题，但在MySQL中可以通过间隙锁和临键锁解决此问题，因此在InnoDB类型的表中不会产生幻读问题，RR也是MySQL中的默认隔离级别，无论从效率上还是并发性上都得到了更好的解决。S是最高的隔离级别，要求事务必须排队执行，所有问题都能解决，但是效率最低，已不再支持并发操作。

三．视图

（一）概念

1. 视图是简化查询过程，提高数据库安全性的虚拟表。
2. 视图中保存的仅仅是一条select语句，保存的是视图的定义，并没有保存真正的数据。视图中的源数据都来自于数据库表，数据库表称为基本表或者基表，视图称为虚拟表。

（二）作用

1. 防止未经许可的用户访问敏感数据，确保数据的安全性
2. 封装sql语句，简化查询过程
3. 视图可对用户屏蔽真实表结构

（三）语法

1. 创建视图
    语法： CREATEA VIEW 视图名   AS  SELECT 语句;

2. 查询视图
    语法：SELECT * FROM 视图名；

3. 查看视图结构
    语法：desc 视图名

4. 查看创建视图的文本信息
    语法：SHOW   CREATE  VIEW  视图名;

5. 修改视图
    语法： ALTER VIEW 视图名 AS  SELECT 语句 ;

6.删除视图
    语法： DROP VIEW 视图名;

代码：

-- 创建视图：create view v_student as  select...; CREATE VIEW v_student AS SELECT * FROM student; -- 查看视图 SELECT * FROM `v_student`; -- 查看创建视图的语句 SHOW CREATE VIEW v_student; -- 查看视图结构 DESC v_student; -- 删除视图 DROP VIEW v_student;

四．存储过程

（一）概念

1. 存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。类似于java中的方法。
2. 存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
3. 存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

（二）优点

1. 存储过程在创建的时候直接编译，而sql语句每次使用都要编译，提高执行效率。
2. 一个存储过程可以被重复使用(SQL语句也可复用，优势不明显)。
3. 一条sql语句，可能需要访问几张表，对数据库连接好几次，存储过程只会连接一次。
4. 存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

（三）缺点

1. 可移植性太差了
2. 对于简单的sql语句，毫无意义
3. 对于只有一类用户的系统安全性毫无意义
4. 团队开发，标准不定好的话，后期维护很麻烦
5. 对于开发和调试都很不方便
6. 复杂的业务逻辑，用存储过程还是很吃力的

（四）案例使用

创建存储过程：

语法：

DELIMITER //

CREATE PROCEDURE sp_name (参数类型 参数名  数据类型 ，...)

BEGIN          

SQL语句集合；                          

END//

特殊说明：

1.mysql中是以 ; 表示SQL语句结束，遇到分号编译器会立刻执行相关语句。而存储过程体中会有多条SQL语句，每一条都会以;号结束，但我们希望整个过程体语句必须完整执行完才能去做执行操作。因此存储过程在定义时的分界符需要自定义，关键字是DELIMITER，分界符号可自定义。

例如：

DELIMITER $$

或

DELIMITER //

2.参数类型有三种：in  out  inout

in 输入参数，表示在调用该存储过程时需要传入的参数

out 输出参数，表示调用存储过程后需要返回的参数

inout 输入输出参数，表示即可以传入又可返回的参数

调用存储过程：

call 存储过程名(参数);

1. 创建无参数存储过程

-- 1.无参 DELIMITER // CREATE PROCEDURE sp_01() BEGIN          SELECT * FROM student; END// -- 调用 CALL sp_01();

2. IN(输入)参数存储过程

说明： IN 表示这个存储过程需要的输入参数

DELIMITER // CREATE PROCEDURE sp_02(IN uname VARCHAR(30)) BEGIN          SELECT * FROM student WHERE sname  = uname; END// -- 调用 CALL sp_02('李四');

3. out（输出）参数存储过程

说明：out表示这个存储过程需要的输出参数

DELIMITER // CREATE PROCEDURE sp_03(IN uname VARCHAR(30),OUT sage INT) BEGIN          SELECT age INTO sage FROM student WHERE sname = uname; END// -- 调用 CALL sp_03('王二小',@sage); SELECT @sage;

4. inout参数存储过程说明

说明：inout是可以接受一个参数并输出一个参数

DELIMITER // CREATE PROCEDURE sp_04(INOUT temp VARCHAR(30)) BEGIN          SELECT degree INTO temp FROM student WHERE sname = temp; END// -- 调用 SET @temp = '王二小'; CALL sp_04(@temp); SELECT @temp;

5. 删除存储过程

语法：  DROP PROCEDURE 存储过程名;

DROP PROCEDURE p_selectstucount;

五．触发器

（一）概念

1. 触发器的这种特性可以协助应用在数据库端确保数据的完整性。也可以把触发器理解成一个特殊的存储过程，不需要显示调用，是自动被调用的存储过程。
2. 监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（insert，delete， update）时就会激活它执行。

（二）语法

DELIMITER //
CREATE TRIGGER 触发器名 触发时机（BEFORE|AFTER） 触发事件（INSERT|UPDATE|DELETE）
ON 表名 FOR EACH ROW
BEGIN
    执行语句列表;
END //
​
说明：
- BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后。
- FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次。
- 触发事件参数详解：
  1. INSERT型触发器：插入某一行时激活触发器，可能通过INSERT、REPLACE 语句触发；
  2. UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发；
  3. DELETE型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发。

（三）六种触发器的介绍

BEFORE INSERT：在添加之前激活触发器
BEFORE DELETE：在删除之前激活触发器
BEFORE UPDATE：在修改之前激活触发器
AFTER INSERT：在添加之后激活触发器
AFTER DELETE：在删除之后激活触发器
AFTER UPDATE：在修改之后激活触发器

（四）案例

-- 用户表 CREATE TABLE users( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), add_time DATETIME ); -- 日志表 CREATE TABLE LOGS( id INT PRIMARY KEY AUTO_INCREMENT, LOG VARCHAR(255), modify_time DATETIME ); SELECT * FROM users; SELECT * FROM LOGS; -- 1.添加触发器:向users表添加记录时同时向logs表插入日志，非语句直接点击修改也符合 DELIMITER // CREATE TRIGGER t_insert AFTER INSERT ON users FOR EACH ROW BEGIN          INSERT INTO LOGS(LOG,modify_time)VALUES(CONCAT(new.name,'添加成功'),NOW()); END// -- 测试 INSERT INTO users(NAME,add_time)VALUES('张三',NOW()); -- 2.修改触发器：修改用户的姓名同时向logs表中插入日志 DELIMITER // CREATE TRIGGER t_update BEFORE UPDATE ON users FOR EACH ROW BEGIN          INSERT INTO LOGS(LOG,modify_time)VALUES(CONCAT(old.name,'修改成'),NOW()); END// -- 测试 UPDATE users SET NAME = '李四' WHERE NAME='张三'; -- 3.删除触发器 DELIMITER // CREATE TRIGGER t_delete AFTER DELETE ON users FOR EACH ROW BEGIN          INSERT INTO LOGS(LOG,modify_time)VALUES(CONCAT(old.name,'删除成功'),NOW()); END// -- 测试 DELETE FROM users WHERE id = 1;

1. NEW与OLD介绍

         MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

　　① 在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

　　② 在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；

　　③ 在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；

使用方法：

         NEW.columnName （columnName为相应数据表某一列名）

2. 查看触发器

         语法： SHOW TRIGGERS；

3. 删除触发器

         语法 ： DROP TRIGGER 触发器名

六．存储引擎原理

（一）思考

我们在mysql中创建的数据库、表以及表中的数据是保存在哪里的？

答：都是以文件的形式存储在硬盘上的。

（二）概述

1. 在关系型数据库中每一个数据表就对应一个文件，而这些文件是以什么方式存储在硬盘上的就取决于选择的存储引擎类型，不同的存储引擎存储文件的方式也是不一样的。

2. 我们可以认为数据库存储引擎是规定数据表如何存储数据，如何为存储的数据建立索引以及如何支持更新、查询等技术的实现。

3. 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

4. 由于在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型，也就是说存储引擎是针对表而言的。

5. 查看MySQL支持的存储引擎类型：SHOW ENGINES

 

6. 查看当前默认的存储引擎类型：SHOW VARIABLES LIKE '%storage_engine%'

（三）存储引擎详解

1. MyISAM存储引擎

特点：

         1. 在做插入、查询时速度快，性能高

         2. 支持全文索引，表级锁

         3. 不支持事务和外键

         4. 数据的物理组织形式是非聚簇表。数据和索引分开存储，顶级节点只存索引，数据都存储在B+树的叶子节点

文件存储格式：

         1. tb01.frm：存储tb01表结构信息（表中有哪些列，数据类型等）

         2. tb01.MYI：MY表示MYISAM存储引擎，I是index索引，这里存储tb01的索引信息。

         3. tb01.MYD：MY表示MYISAM存储引擎，D是data数据，这里存储tb01的数据信息（即表中的记录）

2. InnoDB存储引擎

特点：

         1. 支持外键、支持事务

         2. 支持行级锁，因此在高并发量的情况下效率高

         3. 不支持全文索引

         4. 其数据的物理组织形式是聚簇表。数据和索引放在一块，都位于B+树的叶子节点上。

文件存储格式：

         1. xx.frm:同MYISAM存储引擎，也是用来存储表结构信息。

         2. ibdata1:共享表空间，且来存储所有InnoDB数据表的数据信息，包含索引信息

3. 指定存储引擎建表

在建表时指定

         create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM;

         create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;

         #也可以使用alter table语句，修改一个已经存在的表的存储引擎。

         alter table 表名 engine = innodb;

在配置文件中指定

         #my.ini文件

         [mysqld]

         default-storage-engine=INNODB