索引
当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘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)：
- 原子性：对于事务中的多次更新操作要么同时成功，要么同时失败
- 一致性：保证事务操作完成之后，所有的结果一致
- 隔离性：事物之间各自独立存在相互不影响
- 持久性：事务完成之后，确保所有的数据长期持久的存在
Mysql事务的使用
mysql中只有使用InnoDB引擎才支持事务；MyISAM引擎不支持事务
show table status where name = ‘emp’ \G
参考代码
-- 开启事务(事务一旦开，后续的所有更新操作都在这个事务中，直到提交后才会对物理表产生影响)
start transaction;
-- 设置保存点(在当前位置设置保存点，通过rollback to 保存点，可以回滚到该位置)
savepoint p1;
-- 回滚保存点（回滚到指定的保存点：一旦回滚，则当前保存点会撤销）
rollback to p1;
-- 回滚到事务开始的位置
rollback;
-- 提交事务
commit;
事务隔离级别
由于实际的业务操作可能会涉及到很多事务的并发操作，因此在事务并发时可能会遇到以下问题：
-
**丢失更新：**撤消一个事务时，把其它事务已提交的更新的数据覆盖了。
-
脏读：事务A读取了事务B更新的数据，而此时事务B并未提交，那么A读取到的数据是脏数据
脏数据
-
**不可重复读：**事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新
并提交，导致事务A多次读取同一数据时，结果 不一致。 -
**幻读：**系统管理员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语句都添加了扩展支持，允许通过一些常见数据类型，运算符，分支语句，循环语句等实现一些类似常规编程语言的操作，这个概念称之为数据库的可编程性；对于不同的数据库产品对于可编程的命名也存在差异：
- MSSQLServer 的可编程性称之为T-SQL
- 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);片
扫一扫
1017
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
