MySQL数据库：SQL语言入门 【3】（学习笔记）

目录

5，TCL —— 事务控制语言（Transaction Control Language）

（1）事务的概念+作用

（2）事务的特性

        【1】原子性

        【2】一致性

        【3】隔离性

        【4】持久性

（3）并发事务带来的问题

        【1】脏读

        【2】不可重复读

        【3】幻读

（4）事务的隔离级别 

        【1】分类

  （5）jdbc对事务的操作

数据库对象

二，视图

（1）1，

2，好处

             （1）简化用户操作：

            （2）对机密数据提供安全保护：

3，jdbc操作

三，存储过程

1，什么是存储过程

2，存储过程的优点

               （1） 提高执行性能。

              （2）可减轻网络负担。

               （3）可将数据库的处理黑匣子化。

3，jdbc操作

---

5，TCL —— 事务控制语言（Transaction Control Language）

          用于数据库的事务管理。

（1）事务的概念+作用

          事务（Transaction）指的是一个操作序列，该操作序列中的多个操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位，由DBMS（数据库管理系统）中的事务管理子系统负责事务的处理。

           保证数据库的完整性，保证一系列DML 操作要么全都执行，要么全都不执行。（要么全成功，要么全失败）

（2）事务的特性

并不是所有的操作序列都可以称为事务，这是因为一个操作序列要成为事务，必须满足事务的原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这四个特性简称为ACID特性。 【必问】

        【1】原子性

原子是自然界最小的颗粒，具有不可再分的特性。

事务中的所有操作可以看做一个原子，事务是应用中不可再分的最小的逻辑执行体。

使用事务对数据进行修改的操作序列，要么全部执行，要么全不执行。

        【2】一致性

事务开始前后，状态一致

        【3】隔离性

隔离性是指各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务，都是隔离的。（并发执行的事务之间既不能看到对方的中间状态，也不能相互影响。）

        【4】持久性

持久性指事务一旦提交，对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库，即使数据库出现故障，提交的数据也应该能够恢复。但如果是由于外部原因导致的数据库故障，如硬盘被损坏，那么之前提交的数据则有可能会丢失。

（3）并发事务带来的问题

        【1】脏读

当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

读到了别人事务中没提交的数据   ，读到的数据  就叫做 脏数据，本次读取 就叫做 脏读。    

（不可以接收）

        【2】不可重复读

 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

-- 读到了别人事务提交后   又修改后的数据 ，   

（可以接收）

        【3】幻读

 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

读到了别人事务提交后   又 添加 或者 删除 后的数据 ，

（可以接收）

不可重复度和幻读区别： 

• 不可重复读的重点是修改，幻读的重点在于新增或者删除。
• 解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表 

（4）事务的隔离级别 

MySQL数据库 解决事务并发问题  的解决方案————调整MySQL数据库的隔离级别

事务的隔离级别用于决定如何控制并发用户读写数据的操作。

数据库是允许多用户并发访问的，如果多个用户同时开启事务并对同一数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以MySQL中提供了四种隔离级别来解决上述问题。

mysql数据库默认的隔离级别是 REPEATABLE READ（可重复读取）。

        【1】分类

从低到高依次为

READ UNCOMMITTED（读取未提交的）、READ COMMITTED（读取已提交的）、REPEATABLE READ（可重复读取）以及 SERIALIZABLE，

隔离级别越低，越能支持高并发的数据库操作。

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED read uncommitted	√	√	√
READ COMMITTED  read committed	X	√	√
REPEATABLE READ  默认 repeatable read	X	X	√
SERIALIZABLE   serializable	X	X	X

                      PS：√  代表会出现问题   ， X代表不会出现问题 = 解决问题

  （5）jdbc对事务的操作

          【1】准备表

-- 创建表
create table account(id int primary key auto_increment,uname varchar(10) not null,balance double check (balance >=0)
);
-- 准备数据
insert into account values(null,'张三',400);
insert into account values(null,'李四',400);-- 修改  代码中需要
update account set balance=balance+500 where uname='李四'
update account set balance=balance-500 where uname='张三'

        【2】Java 代码  【jdbc】

测试事务：

@Testpublic void test1() throws SQLException {DBHelper db=new DBHelper();Connection con=db.getcon();PreparedStatement ps=null;try {con.setAutoCommit(false);//取消jdbc自动提交ps = con.prepareStatement("update account set balance=banlance+500 where uname ='李四'");ps.execute();ps.execute("update account set balance=banlance-500 where uname ='张三'");con.commit();//提交System.out.println("转账成功");} catch (SQLException e) {System.out.println(e);}finally {System.out.println("转账失败，操作回滚！");con.rollback();//回滚db.closeAll(con,ps,null);}}

[PS：测试用的单元测试，如果有异常，可以改用 main方法运行]

工具类 DBHelper   ————注意调用的库名！！！

package mysql3;import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;/*** 工具类*/
public class DBHelper {/*** 获得数据源，获得连接对象** @return*/public Connection getcon() {Connection con = null;try {Class.forName("com.mysql.cj.jdbc.Driver");String url = "jdbc:mysql://127.0.0.1:3306/demodvd?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";String username = "root";String password = "root";con = DriverManager.getConnection(url, username, password);} catch (ClassNotFoundException | SQLException e) {e.printStackTrace();}return con;}/*** 关闭资源** @param con* @param ps* @param rs*/public void closeAll(Connection con, PreparedStatement ps, ResultSet rs) {try {if (con != null) {con.close();}if (ps != null) {ps.close();}if (rs != null) {rs.close();}} catch (SQLException e) {e.printStackTrace();}}/*** 增加，删除，修改 ---- 方法汇总** @param sql* @param arrs* @return*/public int update(String sql, Object... arrs) {Connection con = getcon();PreparedStatement ps = null;int count = 0;try {ps = con.prepareStatement(sql);for (int i = 0; i < arrs.length; i++) {ps.setObject((i + 1), arrs[i]);}count = ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();}return count;}public List query(String sql, Class cla, Object... arrs) {List list = new ArrayList<>();Connection con = getcon();PreparedStatement ps = null;ResultSet rs = null;try {ps = con.prepareStatement(sql);for (int i = 0; i < arrs.length; i++) {ps.setObject((i + 1), arrs[i]);}rs = ps.executeQuery();while (rs.next()) {Object obj = cla.newInstance();Field[] fs = cla.getDeclaredFields();for (Field f : fs) {f.setAccessible(true);Object value = rs.getObject(f.getName());f.set(obj, rs.getObject(f.getName()));}list.add(obj);}} catch (SQLException e) {e.printStackTrace();} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} finally {closeAll(con, ps, rs);}return list;}}

---

数据库对象

事务，视图，存储过程，函数，DCL(数据控制语言)，索引，触发器

二，视图

（1）1，

视图（view）是一个从单张或多张基础数据表或其他视图中构建出来的虚拟表。

同基础表一样，视图中也包含了一系列带有名称的列和行数据，但是数据库中只是存放视图的定义，也就是动态检索数据的查询语句，而并不存放视图中的数据，这些数据依旧存放于构建视图的基础表中，只有当用户使用视图时才去数据库请求相对应的数据，

即视图中的数据是在引用视图时动态生成的。因此视图中的数据依赖于构建视图的基础表，如果基本表中的数据发生了变化，视图中相应的数据也会跟着改变。

PS:视图本质上就是：一个查询语句，是一个虚拟的表，不存在的表，你查看视图，其实就是查看视图对应的sql语句

2，好处

             （1）简化用户操作：

                          视图可以使用户将注意力集中在所关心地数据上，而不需要关心数据表的结构、与其他表的关联条件以及查询条件等。

            （2）对机密数据提供安全保护：

                      有了视图，就可以在设计数据库应用系统时，对不同的用户定义不同的视图，避免机密数据（如，敏感字段“salary”）出现在不应该看到这些数据的用户视图上。这样视图就自动提供了对机密数据的安全保护功能

3，jdbc操作

• 创建视图   

-- 创建视图   多表create view v_eds
as
select e.*,d.dname,d.loc,s.*
from emp e inner join dept d on d.deptno=e.deptnoinner join salgrade s on s.losal<=e.sal and s.hisal>=e.sal-- 使用视图select * from v_eds where deptno=10============================================================================-- 创建视图     单表
create view v_emp
as
select empno,ename,job,deptno from emp;-- 使用视图
select * from v_emp where deptno=10

• Java 代码  【jdbc】   （多表）

public static void main(String[] args) throws SQLException {DBHelper db = new DBHelper();Connection con = db.getcon();PreparedStatement ps = con.prepareStatement("select * from v_eds where deptno=?");ps.setInt(1, 0);ResultSet rs = ps.executeQuery();while (rs.next()) {System.out.println(rs.getString("ename")+ "\t" + rs.getString("sal")+ "\t" + rs.getString("dname")+ "\t" + rs.getString("grade"));}db.closeAll(con, ps, rs);}

---

三，存储过程

1，什么是存储过程

                存储过程就是数据库中保存(Stored)的一系列SQL命令（Procedure）的集合。也可以将其看作相互之间有关系的SQL命令组织在一起形成的一个小程序。

2，存储过程的优点

               （1） 提高执行性能。

                          存储过程执行效率之所高，在于普通的SQL语句，每次都会对语法分析，编译，执行，而存储过程只是在第一次执行语法分析，编译，执行，以后都是对结果进行调用。

              （2）可减轻网络负担。

                        使用存储过程，复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端(或应用程序)传递给数据库必要的参数就行，比起需要多次传递SQL命令本身，这大大减轻了网络负担。

               （3）可将数据库的处理黑匣子化。

                         应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用哪个存储过程就可以了

3，jdbc操作

• 创建函数

-- 创建函数 
create procedure proemp1(name varchar(10))
beginif name is null or name = "" thenselect * from emp;elseselect * from emp where ename like concat('%',name,'%');end if;    
end;-- 使用
call proemp1('A')
call proemp1(null)

• Java 操作

 @Testpublic void test3() throws SQLException {mysql3.DBHelper db = new mysql3.DBHelper();Connection con = db.getcon();CallableStatement cs = con.prepareCall("{call proemp1(?)}");cs.setString(1, "A");ResultSet rs = cs.executeQuery();while (rs.next()) {System.out.println(rs.getInt("empno")+ "\t" + rs.getString("ename")+ "\t" + rs.getString("job")+ "\t" + rs.getInt("deptno"));}rs.close();cs.close();con.close();}