数据库三范式及反三范式，事务，触发器，视图，索引，存储过程， jdbc调用存储过程，数据库连接池的作用

最新推荐文章于 2024-08-25 16:28:02 发布

静静七分醉

最新推荐文章于 2024-08-25 16:28:02 发布

阅读量1.8k

点赞数 28

分类专栏： mysql Mysql

本文链接：https://blog.csdn.net/m0_37657725/article/details/96495126

版权

Mysql 同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

mysql

2 篇文章 0 订阅

订阅专栏

1. 关系型数据库的三范式及反三范式

2.事务的四大特征

2.1 事务是什么？

2.2 Spring事务的传播特性和隔离级别

跟一般的sql语句相比,使用存储过程有哪些优点,哪些缺点

案例

5 jdbc调用存储过程

6 数据库连接池的作用

1. 关系型数据库的三范式及反三范式

范式：说白了就是规范。想要满足第二范式，你就必须要满足第一范式，然后，你想要满足第三范式，你就必须要满足第一第二范式
第一范式（1nf），列数据不可分割，是指数据库表的每一列都是不可分割的基本数据项，同一列当中不能有多个值，对应到pojo对象当中，你一个对象的属性不能有多个值。

将来的设计不能这样设计，应为地址还可以再分

但是大家需要注意的是，拆不拆要看业务，比如我们将来要按照国家和省份来分类汇总，那我们就要把它拆掉，但是如果这个字段仅仅起到一个字符串的作用，那我们就没必要拆分。
第二范式（2nf），针对主键：
1.要求数据库当中的每一行必须可以被唯一的区分，你想要给唯一的区分——加一列设成主键。
2.另外非主键的列要依赖主键,最好第二范式要让每个表只描述一件事情

实际，产品的编号和订单的编号没有明确的关系，购买日期和订单编号有关系。
价格和订单编号也没有关系，而与产品编号有关，那么我们应该拆分为

第三范式（2nf），第三范式、要求一个数据库中非主键列不依赖于其他非主键列

学号和姓名存在传递，因为（学号，姓名）——成绩，学号——成绩，姓名——成绩，冗余了，只需保留一个

反三范式：订单（总价）和订单项（单价）。我们可以通过每一个订单去算总价，这是可以的，但是每次算的时候都得合并计算，影响效率，所以有时候该反的时候就得反需求>>结构设计

2.事务的四大特征

2.1 事务是什么？

   事务是并发控制的单位，是用户定义的一个操作序列，这些操作要么做，要么不做，是一个不可分割的单位。
例如：转账---》1000 a—》b 500 a500 b1500 ；a转账成功并不代表这件事情已经成功了，也就是说，当a转账成功，b收到钱以后这个事情才算结束。
事务必须满足四大特性：原子性，一致性，隔离性，持久性（持续性）
   原子性：表示事务内操作不可分割，要么都成功，要么都失败。
   一致性：要么都成功，要么都失败，但是后面失败了的要对前面的操作进行回滚，
   隔离性：一个事务开始后，后面不能有其他事务干扰。
   持久性：表示事务开始了，就不能终止

2.2 Spring事务的传播特性和隔离级别

传播级别

1） PROPAGATION_REQUIRED ，默认的spring事务传播级别，使用该级别的特点是，如果上下文中已经存在事务，那么就加入到事务中执行，如果当前上下文中不存在事务，则新建事务执行。所以这个级别通常能满足处理大多数的业务场景。
2）PROPAGATION_SUPPORTS ，从字面意思就知道，supports，支持，该传播级别的特点是，如果上下文存在事务，则支持事务加入事务，如果没有事务，则使用非事务的方式执行。所以说，并非所有的包在transactionTemplate.execute中的代码都会有事务支持。这个通常是用来处理那些并非原子性的非核心业务逻辑操作。应用场景较少。
3）PROPAGATION_MANDATORY ，该级别的事务要求上下文中必须要存在事务，否则就会抛出异常！配置该方式的传播级别是有效的控制上下文调用代码遗漏添加事务控制的保证手段。比如一段代码不能单独被调用执行，但是一旦被调用，就必须有事务包含的情况，就可以使用这个传播级别。
4）PROPAGATION_REQUIRES_NEW ，从字面即可知道，new，每次都要一个新事务，该传播级别的特点是，每次都会新建一个事务，并且同时将上下文中的事务挂起，执行当前新建事务完成以后，上下文事务恢复再执行。
问题：如果其中一个子事务回滚了，父事务是否回滚？答案是不会，因为子事务是新建事务，父事务已经被挂起，两者不会受到影响。
再问：如果父事务回滚了，子事务是否回滚？答案是不会，同样的理由。但是可以手动控制一旦子事务回滚，父事务也回滚。

5）PROPAGATION_NOT_SUPPORTED ，这个也可以从字面得知，not supported ，不支持，当前级别的特点就是上下文中存在事务，则挂起事务，执行当前逻辑，结束后恢复上下文的事务。
这个级别有什么好处？可以帮助你将事务极可能的缩小。我们知道一个事务越大，它存在的风险也就越多。所以在处理事务的过程中，要保证尽可能的缩小范围。比如一段代码，是每次逻辑操作都必须调用的，比如循环1000次的某个非核心业务逻辑操作。这样的代码如果包在事务中，势必造成事务太大，导致出现一些难以考虑周全的异常情况。所以这个事务这个级别的传播级别就派上用场了。用当前级别的事务模板抱起来就可以了。
6）PROPAGATION_NEVER ，该事务更严格，上面一个事务传播级别只是不支持而已，有事务就挂起，而PROPAGATION_NEVER传播级别要求上下文中不能存在事务，一旦有事务，就抛出runtime异常，强制停止执行！这个级别上辈子跟事务有仇。
7）PROPAGATION_NESTED ，字面也可知道，nested，嵌套级别事务。该传播级别特征是，如果上下文中存在事务，则嵌套事务执行，如果不存在事务，则新建事务。Propagation_required_new 总是开启一个新的事务，如果有一个事务存在，就将已存在的事务挂起

隔离级别

脏读 :所谓的脏读，其实就是读到了别的事务回滚前的脏数据。比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。
不可重复读 ：不可重复读字面含义已经很明了了，比如事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。
幻读：事务A首先根据条件索引得到10条数据，然后事务B改变了数据库一条数据，导致也符合事务A当时的搜索条件，这样事务A再次搜索发现有11条数据了，就产生了幻读。

3.触发器的使用场景？

触发器：触发器需要有触发条件，当条件满足之后做相应的操作。比如：学校的校园网，开心网，facebook，当你发一个日志，会自动通知好友，本质就是当你增加一个日志的时候做了一个后触发，再向数据库表当中写入相应的操作。

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

其中：
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。
好处：效率高。在贴吧发一条帖子，都希望在页面当中展现我们的最后发帖时间，帖子总数（保证他们的同步更新。）这个时候就可以用到触发器而且效率非常高。

4. sql组件

4.1 视图

视图（view）也被称作虚表，就是虚拟的表，是一组数据的逻辑表示，
视图对应一条select语句，结果集被赋予一个名字，即视图的名字；视图本身不包括任何数据，他只包含映射到基表（原始的物理表）的一个查询语句，当基表发生数据变化，视图的数据也发生变化。

视图的应用场景：

如果需要经常执行某项复杂查询，可以基于这个复杂查询建立一个视图，此后查询此视图即可，就是简化复杂查询
视图本质上就是一条select语句，所以当访问视图时，只能访问到所对应的select语句中涉及到的列，对基表中的其他列起到安全和保密的作用，就是限制数据访问

4.2 索引

索引的原理

索引是一种允许直接访问数据表中某一数据行的树形结构，为了提高查询效率引入，
索引是独立表的对象，可以存放在与表不同的表空间中。
索引记录中存有索引关键字和指向表中数据的指针（地址）对索引进行io操作要比对表的操作要少
索引一旦被建立就将被系统自动维护，查询语句中不用指定使用哪个索引
索引是一种提高查询效率的机制,合理使用索引提升查询效率

使用场景

为经常出现在where子句中列创建索引
为经常出现在order by，distinct后面的字段建立索引
如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致
为经常作为表的链接条件的列上创建索引
不要在经常做dml操作的表上建立索引
不要小表上建立索引
限制表上的索引数据，索引并不是越多越好
删除很少被使用，不合理的索引

4.3 存储过程(mysql)

存储过程(stored procedure)是在大型数据库的系统中为了完成特殊的功能而编写的一组sql语句集,存储过程在数据库中编译,用户通过指定的存储过程名,调用存储过程,如果存储过程带参数,在调用存储过程时传递参数

跟一般的sql语句相比,使用存储过程有哪些优点,哪些缺点

存储过程的优点:

减少了脚本的执行环节,缩短了获取数据时间。存储过程只在创建时编译,在调用使用时直接执行,不需要编译，一般的sql语句每条需要编译(执行计划)一次,所以存储过程效率高些
减少网络传输量,提高了传输速度。存储过程编译后存储在数据库服务器上，使用的时候只需要指定存储过程的名称,如果有,并给出参数，而一般的sql语句需要执行语句字符串传输到数据库服务器上
相比于存储过程而言,向数据库服务端传送字符串长度比较大，安全性较高,为存储过程参数赋值只能用?号占位符,避免sql注入

存储过程创建一次可以多次调用，可以减少我们开发人员的工作量。
安全性高，存储过程可以屏蔽对底层数据库对象的直接访问。
现在的数据库支持我们的存储过程，IBM DB2，oracle。Mysql（5.0） ACCESS。

存储过程的缺点:

如果一个程序系统中大量的使用存储过程,当程序交付使用的时候,随客户端的需求增加会导致数据结构复杂,存储过程修改的代价会越来越大
应用程序对于数据库特性依赖过高,存储过程就是属于数据库特性范畴

存储过程的语法:

create procedure 存储过程的名称([[in|out|inout]参数名参数类型...])
begin
存储过程的sql语句块
end

案例

1. 没有任何输入和输出参数的存储过程

delimiter $$
create procedure `testdb`.`proc_noparam`()
  begin
  select
        id ,username
      from t_user;
  end$$
delimiter ;

2. 只有两个输入参数的存储过程

delimiter $$
create procedure `testdb`.`proc_intwoparams`(in uname varchar(20),in uaddress varchar(20))
  begin
  select *
      from t_user
      where
         username like concat('%',uname,'%') and
         address like concat('%',uaddress,'%');
  end$$
delimiter ;

3. 只有两个输出参数

delimiter $$
create procedure `testdb`.`proc_outtwoparams`(out uname varchar(20),out uaddress varchar(20))
begin
select username into uname from t_user where id=1;
   select address into uaddress from t_user where id=11;
   /*不能上面的两个语句合并成一条语句
   select username into uname,address into uaddress from t_user where id=1;
   */
end$$
delimiter ;

4. 输入参数一个,输出参数一个

delimiter $$
create procedure `testdb`.`proc_inoneparamandoutoneparam`(in uname varchar(20),out uid int)
  begin
    select
       id
    from t_user
    where username=uname
    into uid;
    /*
       select id into uid from t_user where username=uname
    */
  end$$
delimiter ;

5. 输入参数即输出参数

delimiter $$
create procedure `testdb`.`proc_inoutparam`(inout uname varchar(20))
  begin
    select username into uname from t_user
    where username like concat('%',uname,'%')
    order by username desc
    limit 1;
    /*上面的语句可以替换成下面的写法
    select username from t_user
    where username like concat('%',uname,'%')
    order by username desc
    limit 1
    into uname;
    */
  end$$
delimiter ;

5. 存储过程中有两个结果集

delimiter $$
create procedure `testdb`.`proc_tworesultset`()
  begin
    select id,username from t_user;
    select id,age,address from t_user;
  end$$
delimiter ;