心得体会

1 存储过程

1.1 概述

如果实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现，那么可以将这组复杂的SQL语句 集编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。

存储过程（PROCEDURE）是事先经过编译并存储在数据库中的一段 SQL 语句的集合。调用存储过程可 以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效 率是有好处的。

简单说就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中 的方法；存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比 触发器更加强大，触发器是某件事触发后自动调用；

1.2 特性

存储过程类似于JAVA语言中的方法，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为 IN、OUT、INOUT 类型三种类型。IN类型的参数表示接收调用者传入的数据，OUT类型的参数表示向 调用者返回数据，INOUT类型的参数既可以接收调用者传入的参数，也可以向调用者返回数据。 有输入输出参数，可以声明变量，有if/else, case when,while等控制语句，通过编写存储过程，可以实 现复杂的逻辑功能； 函数的普遍特性：模块化，封装，代码复用； 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

1.3 创建示例

DROP PROCEDURE IF EXISTS `proc_adder`;
CREATE PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
if a is null then set a = 0;
end if;
if b is null then set b = 0;
end if;
set sum = a + b;
# select a + b into sum
END;

DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER $$
CREATE PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
if a is null then set a = 0;
end if;
if b is null then set b = 0;
end if;
set sum = a + b;
END$$

其中， DELIMITER \[ 命令将语句的结束符从 ; 修改成其他符号，这里使用的是 \] ， $$ 也可以使用其 他符号代替，这样在过程和函数中的 ; 就不会被 MySQL 解释成语句的结束而提示错误。

1.4 调用示例

#设置值
set @b = 5;
#调用
call proc_adder(2, @b, @sum);
#显示
select @sum ;

1.5 java调用存储过程

public class Procedure {
    public static void main(String[] args) {
        proc();
    }
    public static void proc() {
        Connection conn = null;
        CallableStatement cst = null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/bank?useSSL=true","root", "root");
            //调用存储过程proc_adder，其中{CALL}是固定写法
            cst = conn.prepareCall("{CALL proc_adder(?,?,?)}");
            //为前两个占位符赋值
            cst.setObject(1, 158);
            cst.setObject(2, 666);
            //将第三个占位符注册为OUT类型的整型参数
            cst.registerOutParameter(3, Types.INTEGER);
            //执行存储过程
            cst.executeUpdate();
            //获取第三个OUT类型的参数
            int sum = cst.getInt(3);
            System.out.println("sum： " + sum);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭连接
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

1.6 存储过程中的控制语句

1.6.1 if

1.6.2 CASE

1.6.3 while

2 触发器

2.1 概述

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

2.2 特点

触发器具有以下特点： 1. 与表相关联 触发器定义在特定的表上，这个表称为触发器表。 2. 自动激活触发器 当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时，如果对表上的这个特定操作定义了触发 器，该触发器自动执行，这是不可撤销的。 3. 不能直接调用 与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。 4. 作为事务的一部分 触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中的任何位置回滚。

2.3 示例

CREATE TRIGGER 触发器名称 触发时机 触发事件
ON 表名称
FOR EACH ROW
BEGIN
语句
END;

DELIMITER $$
CREATE TRIGGER tr_register
AFTER INSERT ON userinfo FOR EACH ROW
BEGIN
INSERT INTO userdetails(id) VALUES(NEW.id);
END $$

3 索引

3.1 概述

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列 或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引类似于书的目录，在一本书前面加上目录，查找内容时不必逐页翻阅就能够快速地找到所需的内 容。借助索引，执行查询时不必扫描整个表就能够快速地找到所需要的数据。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

3.2 使用索引

语法：

CREATE INDEX 索引名称 ON 表名称(字段名)

创建索引

CREATE INDEX ix_UserInfo_UserName ON userinfo(userName);

修改表（添加索引）

ALTER table userinfo ADD INDEX ix_UserInfo_UserName(userName)

测试索引

EXPLAIN SELECT * FROM userinfo WHERE userName='林冲';

其中， EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句 的。分析你的查询语句或是表结构的性能瓶颈。

是EXPLAIN输出的运行结果，表中的key列表示查询语句使用的索引。发现key列的值是NULL，表 示没有使用索引，这是为什么呢？ MYSQL用不用索引其中有一个因素就是条件的选择性。UserInfo表中有3条记录，其中UserName索引 列的值都是张三，如果被查询的值在所有行中占据的比例过大，那么MYSQL就不会用索引，因为 MySQL的优化器会判断用索引和不用索引的开销，发现用了索引开销更大，所以就不用了。 另外，只有在表中的记录数量很大多时，创建索引才有意义。如果数据库中只有少量数据是没有必要创 建索引的。就像一本书只有5页，就没有必要做目录一样。

4 MySQL优化

心得体会

今天把数据库的高级操作学了，以前学过，但是遗忘很久了，而且当时也没有学懂，后面一直觉得他很神秘，但是经过今天的学习后，对他们有了一定的认识，当然还需要一些练习。