20201028黄春跃

知识点

存储过程

如果实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现，那么可以将这组复杂的SQL语句
集编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。
存储过程（PROCEDURE）是事先经过编译并存储在数据库中的一段 SQL 语句的集合。调用存储过程可
以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效
率是有好处的。
简单说就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中
的方法；存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比
触发器更加强大，触发器是某件事触发后自动调用；

有哪些特性

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

创建存储过程

#创建存储过程的语法格式
create procedure 存储过程名（参数1，参数2，…）
begin
存储过程语句块;
end;

存储过程 proc_adder 功能很简单，两个整型输入参数a和b，一个整型输出参数sum，功能就是计算输
入参数a和b的结果，赋值给输出参数sum；

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 解释成语句的结束而提示错误。

MySQL存储过程的调用

调用存储过程的语法格式：
call 存储过程名()
调用 proc_adder

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

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();
       }
     }
   }
 }
}

存储过程中的控制语句

if

DROP PROCEDURE IF EXISTS `proc_if`;
CREATE  PROCEDURE `proc_if`(IN type int)
BEGIN
  DECLARE c varchar(500);
  IF type = 0 THEN
    set c = 'param is 0';
  ELSEIF type = 1 THEN
    set c = 'param is 1';
  ELSE
  set c = 'param is others, not 0 or 1';
  END IF;
select c;
END;

case

DROP PROCEDURE IF EXISTS `proc_case`;
CREATE PROCEDURE `proc_case`(IN type int)
BEGIN
  DECLARE c varchar(500);
  CASE type
  WHEN 0 THEN
    set c = 'param is 0';
  WHEN 1 THEN
    set c = 'param is 1';
  ELSE
    set c = 'param is others, not 0 or 1';
  END CASE;
select c;
END;

while

DROP PROCEDURE IF EXISTS `proc_while`;
CREATE PROCEDURE `proc_while`(IN n int)
BEGIN
  DECLARE i int;
  DECLARE s int;
  SET i = 0;
  SET s = 0;
  WHILE i <= n DO
    set s = s + i;
    set i = i + 1;
  END WHILE;
SELECT s;
END

触发器

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

1. 与表相关联
   触发器定义在特定的表上，这个表称为触发器表。
2. 自动激活触发器
   当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时，如果对表上的这个特定操作定义了触发
   器，该触发器自动执行，这是不可撤销的。
3. 不能直接调用
   与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。
4. 作为事务的一部分
   触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中的任何位置回滚。
   1.3 创建触发器
   触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：
   1.监视地点(table)
   2.监视事件(insert/update/delete)
   3.触发时间(after/before)
   4.触发事件(insert/update/delete)
   语法：

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

Mysql优化

1.1 对查询进行优化，应尽量避免全表扫描，首先应考虑在
where 及 order by 涉及的列上建立索引。
1.2 应尽量避免在 where 子句中对字段进行 null 值判断，
否则将导致引擎放弃使用索引而进行全表扫描.
如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
1.3 应尽量避免在 where 子句中使用!=或<>操作符，否则
引擎将放弃使用索引而进行全表扫描。
1.4 应尽量避免在 where 子句中使用or 来连接条件，否则
将导致引擎放弃使用索引而进行全表扫描
如：select id from t where num=10 or num=20
应该为：
select id from t where num=10
union all
select id from t where num=20
1.5 in 和 not in 也要慎用，否则会导致全表扫描
如：select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了
应该为:
select id from t where num between 1 and 3
1.6 下面的查询也将导致全表扫描：
select id from t where name like '李%'
若要提高效率，可以考虑全文检索。
1.7 如果在 where 子句中使用参数，也会导致全表扫描。
因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在
编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的
输入项。如下面语句将进行全表扫描：select id from t where num=@num可以改为强制查询使用索
引：select id from t with(index(索引名)) where num=@num
1.8 应尽量避免在 where 子句中对字段进行表达式操作，
这将导致引擎放弃使用索引而进行全表扫描。
如：select id from t where num/2=100
应改为:
select id from t where num=100*2
1.9 应尽量避免在where子句中对字段进行函数操作，这将
导致引擎放弃使用索引而进行全表扫描。
如：select id from t where substring(name,1,3)='abc' ，
name以abc开头的id
应改为:
select id from t where name like 'abc%'
1.10 不要在 where 子句中的“=”左边进行函数、算术运算
或其他表达式运算，否则系统将可能无法正确使用索引。
1.11 在使用索引字段作为条件时，如果该索引是复合索
引，那么必须使用到该索引中的第一个字段作为条件时才
能保证系统使用该索引，否则该索引将不会被使用，并且
应尽可能的让字段顺序与索引顺序相一致。
1.12 不要写一些没有意义的查询
如需要生成一个空表结构：select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table ...
1.13 很多时候用 exists 代替 in 是一个好的选择
如:select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
1.14 并不是所有索引对查询都有效
SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，
如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作
用。
1.15 索引并不是越多越好
索引固然可 以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或
update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好
不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
1.16 应尽可能的避免更新 clustered 索引数据列
因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺
序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否
应将该索引建为 clustered 索引。
1.17 尽量使用数字型字段
若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是
因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够
了。

总结

今天晚自习做了作业，然后就没什么时间了，编程练习还没来得及做。

继续加油