20201028黄春跃

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 尽量使用数字型字段
若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是
因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够
了。

总结

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

继续加油

标签

评论

this is is footer