数据库中的事务和数据库对象

╰半夏微凉° 2022-11-28 00:45 141阅读 0赞

#### 一.事务 ####

事务是一组DML操作的逻辑单元，用于保证数据的一致性要么一起成功（提交）要么一起失败（回滚）

事务处理语言：TPL

结合DML使用（insert,update,delete）

*  commit : 确认提交（默认自动提交）
 *  rollback：回滚取消
 *  savepoint：保存点

**事务的开始和终止：**

*  开始：事务开始于上一个事务的结束或者第一条DML操作
 *  终止：事务终止于显示操作（commit,rollback）

**事务中数据状态：**

若多个会话操作同一张表数据的时候，当用户和服务器连接成功后，服务器和客户端建立一个会话（session）所有交互在此会话中进行。

**事务演示：**

会话默认自动提交，修改提交方式

set autocommit = 0;

**步骤：**

1.  开启会话A，创建表并插入数据不提交

create table temp(
    	id int
    );
    set atuocommit = 0;
    insert into temp values(1);
    select * from temp;

1.  开启会话B，查看表数据

select * from temp;//看不到数据，没有提交，只能看结构

1.  会话A进行提交，会话B可以查询到数据

commit;

1.  会话A更新数据不提交，会话B查看数据（看不到数据）

update temp set id = 2;

1.  会话A提交，会话B可以看到数据的修改
2.  会话A更新操作不提交，会话B删除操作不提交

update temp set id = 3;	//A 不提交
    set autocommit=0;// B 不自动提交
    delete from temp;//B 发生阻塞

1.  会话A提交，会话B结束阻塞状态
2.  会话A 更新操作不提交，回滚

update temp set id = 4;
    rollback;
    select * from temp;//3

**结论:**

*  事务内部数据的改变没有提交，当前会话可以看到数据的改变，其他会话看不到数据的改变
 *  事务会对操作的数据进行加锁，不允许其他事务操作
 *  如果commit提交，数据的改变得到确认，其他会话会看到数据的改变，数据上的锁会被释放；保存数据的临时空间得到释放
 *  rollback回滚，数据的改变会被取消，，数据上的锁会被释放，保存数据的临时空间被释放

**保存点演示：**

drop table temp;
    create table temp(
    	id int
    );
    insert into temp values(1);
    savepoint A;
    insert into temp values(2);
    savepoint B;
    insert into temp values(3);
    rollback to A;
    select * from temp;

#### 二、数据库中常用对象 ####

1.  表 table
    
    基本存储结构，二维结构，行列组成，横向为行记录，纵向为列字段
2.  视图 view
    
    视图是虚表，无数据，内容由查询定义，视图对于一条查询语句，语句得到的结果集赋予一个名字，就是视图名。
    
    因此可以像操作表一样操作视图
    
    **查询20号部门员工的员工号和姓名**
    
        select deptno,ename from emp_xu where deptno = 20;
    
    **创建视图：**
    
        create view  emp_view as
        select deptno,ename 
        from emp_xu 
        where deptno = 20;
    
    **查看视图**
    
        select * from emp_view;

**删除视图**

drop view emp_view;

说明：

使用与表相同，能够简化查询，隐藏表中不需要的列，视图不包括任何数据，视图是表数据的投影

更新表数据，查看视图

update emp_xu set ename = "郭靖1" where empo = 1004;
    select * from emp_view;

更新视图，查看表

update emp_view set ename = "郭靖" where empo=1004;	
    
    select empo,ename from emp_xu where empo=1004;

视图是表的投影，表进行DML操作会改变视图的显示，对视图进行DML操作同样改变表中的数据

1.  索引 index
    
    用于数据库中加速表查询的数据库对象，快速访问路径的方式来定位数据，减少磁盘的IO操作，提高访问性能
    
    **索引结构：数据+地址**
    
    空间换时间
    
    自动创建索引：主键约束和唯一约束
    
    手动创建索引：
    
        create index 索引名 on 表(字段)
    
    员工号查询员工
    
        select ename from emp_xu where empo=1004;
    
    查看语句的执行时间
    
        show variables like '%pro%'
        set profiling = 1;
        show profiles;
    
    添加empo索引
    
        create index empo_index on emp_xu(empno);
    
    索引使用与数据量有关；
2.  存储过程 procedure
    
    在大型数据库系统中，一组为完成特定功能的sql语句集
    
    存储过程存储在数据库中，经过一次编译后再次调用不需要编译，用户通过指定存过程的名字并给参数执行
    
    好处：
    
     *  把复杂的操作存放在处处过程中简化操作
     *  保证数据完整性
     *  简化变动时修改
     *  提高性能

**创建存储过程**

create procedure 名字（【参数】）
    begin
    ....
    end

查询员工表中员工的最高薪水

delimiter //
    create procedure maxSalary()
    begin 
    select max(salary) from emp_xu;
    end //
    delimiter ;

调用：

call maxSalary();

delimiter声明分隔符

mysql默认 “;” 为分隔符，如果没有声明分隔符，编译器会把存储过程当成sql语句去处理，则存储过程会编译出错

**创建存储过程带有输出参数**

查询员工表中员工的最高薪水，最低薪水，平均薪水

delimiter //
    create procedure empSalary(
    out maxSalary double(7,2),
    out minSalary double(7,2),
    out avgSalary double(7,2)
    )
    begin
    select max(salary) into maxSalary from emp_xu;
    select min(salary) into minSalary from emp_xu;
    select avg(ifnull(salary,0)) into avgSalary from emp_xu;
    end //
    delimiter ;

调用：

call empSalary(@maxSalary,@minSalary,@avgSalary);

查看：

select @maxSalary,@minSalary,@avgSalary;

创建存储过程带有输入输出的参数

根据输入员工号查询员工姓名

delimiter //
    create procedure getName(in dt int, out dn varchar(10))
    begin
    select ename into dn from emp_xu where empo = dt;
    end //
    delimiter ;

调用：

call getName(1004,@dn);
    select @dn;

查看

select @dn;

**管理用户（DCL）**

CREATE USER, GRANT,REVOKE

use mysql;

创建用户

create user user01@'localhost'
    identified by '1234';

给用户user01分配操作jsd中所有表的所有权限

grant all on jektong.* to user01@'localhost';

回收权限

revoke delete on jektong.* from user01@'localhost';

以user01登录操作

mysql  -h localhost -u user01 -p1234;
    select user();
    set names gbk;
    select * from emp_xu;

#### 常用的命令 ####

查看MySQL提供的所有执行引擎

show engines;

查看mysql的默认执行引擎

show variables like "%storage_engine";

##### 事务的四大特性（ACID） #####

*  原子性（Atomicity）:事务不允许分割，确保动作全部执行，否则执行不成功
 *  一致性(Consistency)：执行事务前后，数据是保持一致的，多个事务对同一个数据的读取是相同的
 *  隔离性(Isolation)：并发访问数据库的时候，一个用户的事务不被其他事务所干扰，数据库是独立的
 *  持久性(Durability)：事务提交之后，数据是持久的，即使数据库发生故障也不应该有影响