数据库难点知识

索引

​ 当表中有大量记录时,若要对表进行查询,第一种搜索信息方式是全表搜索,是将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记录,这样做会消耗大量数据库系统时间,并造成大量磁盘I/O操作;第二种就是在表中建立索引,然后在索引中找到符合查询条件的索引值,最后通过保存在索引中的ROWID(相当于页码)快速找到表中对应的记录。

​ 索引提供指向存储在表的指定列中的数据值的指针,然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值,然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快,可快速访问数据库表中的特定信息。

索引类似字典的目录,可通过索引快速查询到目标数据。

索引分类

1、普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点

2、唯一性索引:索引列中的值必须是唯一的,但是允许为空值

3、主键索引(聚簇索引):即表中主键列

4、全文索引:全文索引,只有在MyISAM引擎上才能使用,只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。(mysql数据库在5.5版本以前,使用的引擎是MylSAM;在5.5以及以上的版本,mysql的引擎使用的是InnoDB)

5、组合索引:
在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合。

如果某个字段不是经常被作为查询条件进行使用时,那么该字段尽量就不要作为索引

因为添加字段索引,就是将该字段的索引添加在服务器内存当中,如果每张表的索引过多,会对整个服务器的运行产生巨大的影响

索引创建语法

在这里插入图片描述

创建普通索引

create index index_ename on tbemp(ename);

创建唯一索引

create unique index index_username on tbuser(username);

唯一索引要求被添加索引的列值必须唯一

创建组合索引

create index index_emp on tbemp(ename,sal,age);

组合索引的使用一般在进行多条件查询时提升查询效率

在这里插入图片描述

注意事项:

任何一张数据库表都应该有一个主键列,默认数据系统对主键列增加了聚簇索引,因此针对主键列的查询速度比较快。

Mysql中的索引实现基于B+树(二叉树) 最左匹配原则

索引例题

DROP INDEX index_ename on emp;
--  对emp表中的enam字段添加索引
ALTER TABLE emp ADD INDEX index_ename (ename);

--  对emp表中ename,job,age 三个字段添加组合索引
ALTER TABLE emp ADD INDEX index_enamejobage (ename,job,age);

-- 修改索引,将上条组合索引修改为ename和job的组合
DROP INDEX index_enamejobage on emp;
ALTER TABLE emp ADD INDEX index_enamejob (ename,job);

视图

​ 视图实际上就是一张虚拟的表,视图是针对基表的一部分字段的缩影,一般用于对一些字段比较多的表,通过视图可以简化表结构;另外针对一些涉及到多表的查询操作时,建立视图可以简化查询语句;

创建语法

在这里插入图片描述

-- 创建视图
create view v$emp as select ename,job,hiredate,sal from emp;

select * from v$emp;
update v$emp set hiredate=now() where ename='孙悟空';

-- 复杂视图创建
create view v$details
as
select 
e.eno,e.ename,e.job,e.hiredate,e.age,e.sal,
d.dno,d.dname,d.tel,
s.level
from emp e,dept d,sallevel s where e.dno=d.dno and 
e.sal between s.lowsal and s.hisal;

select level from v$details where eno=6;

-- 查询研发部所有人的薪资等级和部门信息
select level,ename from v$details where dname='研发部'

注意事项:

视图不占据存储空间,只是一种逻辑存在(非物理存储);只有在使用视图时才通过视图的定义,加载对应的数据

对视图的操作会影响基表(物理表,实际开发中一般针对视图做查询,避免基于视图做修改
索引提高查询速度,视图简化了查询的方式

视图例题

-- 新建员工表的视图
CREATE VIEW view_emp2 AS SELECT * from emp;

-- ​		新建部门表的视图
CREATE view view_dept AS SELECT * FROM dept;

-- ​		新建获取T8薪资等,所有研发部员工的个人信息和部门名称
SELECT * FROM (SELECT e.*,d.dname FROM emp e LEFT JOIN dept d ON e.dno = d.dno) employe , sallevel s WHERE employe.sal BETWEEN s.lowsal AND  S.hisal AND employe.dname = '研发部' AND s.level='T8';

事务(Transaction)

概述

​ 事务是实际开发中,比较常见业务逻辑操作中都会存在问题,比如转账的业务,需要将一个账户的钱转向到另一个账号,此时会涉及到两个修改操作:A账户钱减少,B账户钱增加;这两个操作要保证能同时成功或者同时失败,那么这一个业务需求(逻辑单元)就称之为一个事务。

在这里插入图片描述

事务特性

数据库事务包含四大特征(ACID):

  1. 原子性:对于事务中的多次更新操作要么同时成功,要么同时失败
  2. 一致性:保证事务操作完成之后,所有的结果一致
  3. 隔离性:事物之间各自独立存在相互不影响
  4. 持久性:事务完成之后,确保所有的数据长期持久的存在

Mysql事务的使用

mysql中只有使用InnoDB引擎才支持事务;MyISAM引擎不支持事务

​ show table status where name = ‘emp’ \G

在这里插入图片描述

参考代码

-- 开启事务(事务一旦开,后续的所有更新操作都在这个事务中,直到提交后才会对物理表产生影响)
start transaction;

-- 设置保存点(在当前位置设置保存点,通过rollback to 保存点,可以回滚到该位置)
savepoint p1;

-- 回滚保存点(回滚到指定的保存点:一旦回滚,则当前保存点会撤销)
rollback to p1;

-- 回滚到事务开始的位置
rollback; 

-- 提交事务
commit;

事务隔离级别

由于实际的业务操作可能会涉及到很多事务的并发操作,因此在事务并发时可能会遇到以下问题:

  1. **丢失更新:**撤消一个事务时,把其它事务已提交的更新的数据覆盖了。

  2. 脏读:事务A读取了事务B更新的数据,而此时事务B并未提交,那么A读取到的数据是脏数据

    脏数据

  3. **不可重复读:**事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新
    并提交,导致事务A多次读取同一数据时,结果 不一致。

  4. **幻读:**系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B
    就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有
    改过来,就好像发生了幻觉一样,这就叫幻读。

对于以上可能出现的问题,数据库中引入事务隔离级别的解决方案:

在这里插入图片描述

以上是解决事务并发问题的方案,其中隔离级别从低到高,对数据操作的效率影响从低到高;Mysql中默认的隔离级别是:repeatable-read

在这里插入图片描述

事务例题

-- 修改emp表为不自动提交事务,新建事务对员工的薪资调整的完整事务
-- ​		分别对员工薪资做两次调整,并且对这两次操作分别设置保存点;
-- ​		最后回滚到第一个保存点
-- ​		并提交事务,完成对第一次薪资的修改

SET autocommit = 0;
SELECT * FROM emp  WHERE eno = 2;
START TRANSACTION;
SAVEPOINT pointer1;
UPDATE emp SET ename = '南极' WHERE eno = 2;
SAVEPOINT pointer2;
UPDATE emp SET age = 22 WHERE eno = 2;
ROLLBACK to pointer1;
COMMIT;


数据库可编程性介绍

概述

​ 在之前学习到所有有关数据库操作几乎都是一行命令解决问题,这些命令大多都是sql标准语法;但是不同的数据库管理系统对sql语句都添加了扩展支持,允许通过一些常见数据类型,运算符,分支语句,循环语句等实现一些类似常规编程语言的操作,这个概念称之为数据库的可编程性;对于不同的数据库产品对于可编程的命名也存在差异:

  1. MSSQLServer 的可编程性称之为T-SQL
  2. Oracle中的可编程性称之为PL/SQL

数据库可编程性分类

  • 存储过程(procedure)
  • 触发器(trigger)

存储过程(Procedure)

​ 存储过程(Procedure),是数据库操作语言SQL的可编程性实现,传统的SQL语句通常是在数据库服务器中执行一条命令,命令在数据库引擎内部需要经过创建与分析的过程,因此在运行时间上开销较大;而存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划,这样,后期的使用只需通过命令调用即可,因此,在执行过程时便可节省此开销。
在这里插入图片描述

--最基础简单存储过程
create procedure testP1 (a varchar(10),b varchar(30))
BEGIN
	if a is null then
		set a = "男";
		end if;
		SELECT * from emp where sex = a;
	end
	call testP1(null,"1");
-- 使用零时变量的存储过程
create procedure emp1 ($ename varchar(30))
BEGIN
	DECLARE $job varchar(30);
	SELECT job into $job from emp where ename = $ename;
	if $job is null then
		set $job = '普通员工';
		update emp set job = $job where ename = $ename;
		SELECT * from emp where ename = $ename;
		end if;
	end
	call emp1('沙和尚');
	DROP procedure emp1;

-- 查询指定部门名的员工信息?如何使用存储过程实现
create procedure sp_emp3($dname varchar(30))
begin
  -- 声明临时变量
	declare $dno int;
	-- 根据指定的部门名称查询到部门号并赋值到临时变量中
	select * from emp where dno in (select dno from dept where dname=$dname);
end

call sp_emp3('研发部');

-- 如何利用存储过程实现一个分页操作:输入一个每页数据行数和页码数,显示该页数据
create procedure sp_page(pagesize int,pagenum int)
begin
	-- 声明临时变量
	declare startNum int;
	-- 对变量赋值操作
	set startNum = (pagenum - 1) * pagesize;
	select * from emp limit startNum,pagesize;
end
call sp_page(3,3)

-- 删除存储过程
drop procedure sp_page;

存储过程优劣势:

在这里插入图片描述

存储例题

在这里插入代码-- 新建存储过程,当修改员工的薪资低于当前员工岗位等级(比如T5,T6)最低薪资时,让员工的薪资默认为最低薪资,并最终显示员工修改后的所有信息
-- 创建存储函数()里面放需要传入参数,即需要修改的员工编号和修改后的薪资
create procedure prosal($eno VARCHAR(20),$sal int)
BEGIN
--   声明变量
	DECLARE $lowsal VARCHAR(30);
-- 	将原来该指定员工所处岗位的最低工资赋值给临时变量
	select sallevel.lowsal into $lowsal from emp2,sallevel where emp2.sal BETWEEN sallevel.lowsal and sallevel.hisal and emp2.eno = $eno;
-- 	如果传入薪资参数的大小小于原本的最低薪资,就将赋值后的变量的值赋值给薪资参数
	IF $sal < $lowsal THEN
		set $sal = $lowsal;
	END IF;
-- 	将指定员工的薪资更新为参数的值
	UPDATE emp2 set emp2.sal = $sal where emp2.eno = $eno;
-- 	查看该指员工的信息
	SELECT * from emp2 where emp2.eno = $eno;
END;

call prosal(8,1350);
  • 2
    点赞
  • 2
    收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:游动-白 设计师:我叫白小胖 返回首页
评论 1

打赏作者

瘦小的花卷

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值