Java数据库篇8——索引、视图、存储过程、触发器

朱雀 2023-10-03 16:06 109阅读 0赞

## Java数据库篇8——索引、视图、存储过程、触发器 ##

### 1、索引 ###

#### 1.1、索引是什么 ####

在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询和 运行更加高效

如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是 一个人力三轮车。拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目 录（索引）快速查找到需要的字

#### 1.2、索引分类 ####

<table> 
 <thead> 
  <tr> 
   <th>索引名称</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主键索引 (primary key)</td> 
   <td>主键是一种唯一性索引,每个表只能有一个主键, 用于标识数据表中的每一 条记录</td> 
  </tr> 
  <tr> 
   <td>唯一索引 (unique)</td> 
   <td>唯一索引指的是 索引列的所有值都只能出现一次, 必须唯一</td> 
  </tr> 
  <tr> 
   <td>普通索引 (index)</td> 
   <td>最常见的索引,作用就是 加快对数据的访问速度</td> 
  </tr> 
 </tbody> 
</table>

#### 1.3、主键索引 ####

特点: 主键是一种唯一性索引,每个表只能有一个主键,用于标识数据表中的某一条记录

一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL

#建表时创建索引
    CREATE TABLE 表名(
        -- 添加主键 (主键是唯一性索引,不能为null,不能重复,)
        字段名 类型 PRIMARY KEY,
    );
    #建表后添加索引
    ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )

#### 1.4、唯一索引 ####

特点: 索引列的所有值都只能出现一次, 必须唯一

唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了 提高访问速度，而只是为了避免数据出现重复

#建表时创建索引
    CREATE TABLE 表名(
        列名 类型(长度),
        -- 添加唯一索引
        UNIQUE [索引名称] (列名)
    );
    #建表后添加索引
    create unique index 索引名 on 表名(列名(长度))
    ALTER TABLE 表名 ADD UNIQUE ( 列名 )

#### 1.5、普通索引 ####

#建表后添加索引
    create index 索引名 on 表名(列名[长度])
    ALTER TABLE 表名 ADD INDEX 索引名 (列名)

#### 1.6、删除索引 ####

ALTER TABLE table_name DROP INDEX index_name;

#### 1.7、索引的优缺点 ####

添加索引首先应考虑在 where 及 order by 涉及的列上建立索引

**索引的优点**

*  大大的提高查询速度
 *  可以显著的减少查询中分组和排序的时间

**索引的缺点**

*  创建索引和维护索引需要时间，而且数据量越大时间越长
 *  当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度

### 2、视图 ###

*  视图是一种虚拟表
 *  视图建立在已有表的基础上, 视图赖以建立的这些表称为基表
 *  向视图提供数据内容的语句为 SELECT 语句, 可以将视图理解为存储起来的 SELECT 语句
 *  视图向用户提供基表数据的另一种表现形式
 *  就当成一个表用就可以
 *  相当于一个只读的表

#### 2.1、视图的作用 ####

权限控制：比如,某几个列可以运行用户查询,其他列不允许,可以开通视图 查询特定的列, 起到权限控制的作用

简化复杂的多表查询：视图本身就是一条查询SQL,我们可以将一次复杂的查询 构建成一张视图, 用户只要查询视图 就可以获取想要得到的信息(不需要再编写复杂的SQL) 视图主要就是为了简化多表的查询

#### 2.2、视图的创建 ####

create view 视图名 [column_list] as select语句;
    #view: 表示视图
    #column_list: 可选参数，表示属性清单，指定视图中各个属性的名称，默认情况下，与SELECT语句中查询的属性相同
    #as : 表示视图要执行的操作
    #select语句: 向视图提供数据内容

#### 2.3、视图的使用 ####

就当成一个只读表用就可以

### 3、存储过程 ###

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据 库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过 指定存储过程的名字并给定参数(需要时)来调用执行

**优点**

*  存储过程一旦调试完成后，就可以稳定运行，（前提是，业务需求要相对稳定，没有变化）
 *  存储过程减少业务系统与数据库的交互，降低耦合，数据库交互更加快捷（应用服务器，与 数据库服务器不在同一个地区）

**缺点**

*  在互联网行业中，大量使用MySQL，MySQL的存储过程与Oracle的相比较弱，所以较少使用，并且互联网行业需求变化较快也是原因之一
 *  尽量在简单的逻辑中使用，存储过程移植十分困难，数据库集群环境，保证各个库之间存储 过程变更一致也十分困难
 *  阿里的代码规范里也提出了禁止使用存储过程，存储过程维护起来的确麻烦

#### 3.1、存储过程格式 ####

DELIMITER $$ -- 声明语句结束符，可以自定义 一般使用$$
    CREATE PROCEDURE 过程名称() -- 声明存储过程
    BEGIN -- 开始编写存储过程
    -- 要执行的操作
    END $$ -- 存储过程结束

DELIMITER $$
    CREATE PROCEDURE goods_proc02(IN goods_id INT)
    BEGIN
    DELETE FROM goods WHERE gid = goods_id ;
    END $$

DELIMITER $$
    CREATE PROCEDURE orders_proc(IN o_oid INT , IN o_gid INT ,IN o_price INT, OUT
    out_num INT)
    BEGIN
    -- 执行插入操作
    INSERT INTO orders VALUES(o_oid,o_gid,o_price);
    -- 设置 num的值为 1
    SET @out_num = 1;
    -- 返回 out_num的值
    SELECT @out_num;
    END $$

### 4、触发器 ###

触发器（trigger）是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事 件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对 一个表进行操作（insert，delete， update）时就会激活它执行

#### 4.1、触发器格式 ####

delimiter $ -- 将Mysql的结束符号从 ; 改为 $,避免执行出现错误
    CREATE TRIGGER Trigger_Name -- 触发器名，在一个数据库中触发器名是唯一的
    before/after（insert/update/delete） -- 触发的时机 和 监视的事件
    on table_Name -- 触发器所在的表
    for each row -- 固定写法 叫做行触发器, 每一行受影响，触发事件都执行
    begin
    -- begin和end之间写触发事件
    end
    $ -- 结束标记