10-28 程宗武

10-28(Mysql进阶)

1. 存储过程

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

1.1 建存储过程

#创建存储过程的语法格式 
create procedure 存储过程名(参数1,参数2,…) 
begin 
存储过程语句块; 
end;
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;

1.2 存储过程的调用

#设置值
SET @b = 5;
#调用
CALL proc_adder(2,@b,@sum);
#显示
SELECT @sum #7

1.3 java调用存储过程

    @Test
    public void proc() {
        Connection conn = null;
        try {
            conn = DBHelper.getConn();
            //调用存储过程
            CallableStatement call = conn.prepareCall("{CALL proc_adder(?,?,?)}");
            //为前两个占位符赋值
            call.setObject(1, 222);
            call.setObject(2, 333);
            //将第三个占位符注册为OUT类型的整型参数
            call.registerOutParameter(3, Types.INTEGER);
            //执行存储过程
            call.executeUpdate();
            //获取第三个OUT类型的参数
            int sum = call.getInt(3);
            System.out.println(sum);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭数据库连接
            DBHelper.closeConn();
        }
    }

1.4 存储过程中的语句

#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

2.触发器

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

2.1 创建触发器

CREATE TRIGGER 触发器名称 触发时机 触发事件 
ON 表名称 
FOR EACH ROW 
BEGIN 
语句 
END;
在触发器中可以使用两个特殊的临时表,即OLD表和NEW表。OLD表用于存储UPDATE语句和DELETE 语句中影响的记录,NEW表用于存储INSERT语句和UPDATE语句影响的记录。
当用户注册时,将用户信息添加到UserInfo表,然后MYSQL会自动创建NEW表,NEW表的结构和 UserInfo表结构完全相同,NEW表里面存储的记录就是向UserInfo表中新增的记录。

image-20201028171624855

当删除用户时,将被删除用户的记录从UserInfo表中删除,删除成功后MYSQL会自动创建OLD表, OLD表的结构与UserInfo表结构完全相同,OLD表里面存储的记录就是被删除的UserInfo表中的记录, 如下图

image-20201028171645022

当更新用户信息时,MYSQL会自动创建NEW表和OLD表,OLD表和NEW表的结构与UserInfo表的结构 完全相同。OLD表中存储的是更新前的UserInfo表中的记录,NEW表中存储的是更新后的UserInfo表中 的记录。例如将林冲的密码100更改为10,如下图所示:

image-20201028171736658

2.2 使用触发器实现用户注册业务

DELIMITER $$ 
CREATE TRIGGER tr_register 
AFTER INSERT ON userinfo FOR EACH ROW 
BEGIN  
    INSERT INTO userdetails(id) VALUES(NEW.id); 
END $$
#向UerInfo表中插入一条数据
insert into userinfo (userName, userPass) values('张三', '123456'); #UserDetails也会做出更改

image-20201028172358534

image-20201028172415894

注意,如果给一个表添加了一个触发器,那么触发器中的内容不能再对这张表进行操作了,否则会报错。

3. 索引

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

3.1 索引的数据结构B-Tree

下图是B-Tree数据结构的示意图,分为上下两部分,下部分数据页,上部分是索引页。数据页中 存储的是表中的记录,索引页存储的是索引数据。

image-20201028172945287

3.2 使用索引

3.2.1 创建索引

CREATE INDEX ix_UserInfo_UserName ON userinfo(userName);

3.2.2 修改表(添加索引)

ALTER table userinfo ADD INDEX ix_UserInfo_UserName(userName);

3.2.3 测试索引

EXPLAIN SELECT * FROM UserInfo WHERE userName='张三';

image-20201028174432992

3.2.4 索引的优缺点

-- 优点
索引由数据库中一列或多列组合而成,其作用是提高对表中数据的查询速度 索引的优点是可以提高检索数据的速度 
-- 缺点
索引的缺点是创建和维护索引需要耗费时间
索引可以提高查询速度,会减慢写入速度。索引并不是越多越好,索引固然可以提高相应的 select 的效 率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样 建索引需要慎重考虑,视具体情况而定

标签

评论

this is is footer