数据库 存储过程+视图+索引+触发器

目录

存储过程

定义

语法：

 视图

定义

语法

索引

定义

语法

触发器

定义

例子

---

存储过程

定义

什么是存储过程：相当于java的方法（重用）

存储过程的优势：

1：重用

2：速度快（不同于sql语句，预编译执行的，在服务器上）

3：安全性高（看不到sql语句、需要权限）

存储过程的不足：

存储过程依赖于sql服务器，不能实现迁移！

语法：

创建存储过程：

 Create procedure 存储过程名称（参数列表： 输入参数或输出参数）

输入参数：方法的参数

输出参数：返回值。

删除存储过程：

Drop procedure 存储过程名称;

代码

#删除存储过程
drop procedure if exists stu_list;
#创建存储过程
CREATE procedure stu_list()
select * from student
#调用存储过程
call stu_list();

#将添加和修改合并
DELIMITER //
CREATE procedure save_student(in $id int,in $name varchar(20),in $gender int, in $age int)
begin
    if $id is null then
		  INSERT into student(name,gender,age) values($name,$gender,$age);
		ELSE
      update student set name=$name,gender=$gender,age=$age where id=$id;
		end if;
end
call save_student(null,'abc',1,44);
call save_student(62,'abc',1,55);

call stu_list();

输出参数的使用

drop procedure stu_count;
CREATE PROCEDURE stu_count(in $gender int,out $count int)
   SELECT  count(id) into $count from student where gender=$gender
#测试输出参数的存储过程
call stu_count(1,@num);
select @num;

 视图

定义

视图的本质是为查询起了一个别名

视图的作用：

1：简化查询

2：屏蔽了敏感字段

语法

创建视图的语法： create view 视图名称 as 查询语句（指定列）

create view stu_1 as
select id,name,age from student;
#使用
select * from stu_1;

视图是基于原表的虚拟表，注意我们也可以通过视图对原表的数据进行修改和删除！

索引

定义

索引的目的：提高查询效率

缺点：占用过多的硬盘的存储空间： “空间换时间”

一个表可以创建多个索引（每个索引就是一个新表，该表按照指定字段的索引规则创建：排好序）。

语法

创建索引的语法：create index 索引名称 on 表名(字段名称 asc|desc)

create index stu_age on student(age); //默认升序
create index stu_age1 on student(age desc);//年龄的降序排列

删除索引： drop index 索引名称 on 表;

drop index stu_age1 on student;

索引的类型：普通索引、 唯一索引、多列索引、全文索引

触发器

定义

触发器的特点：

1：自动执行无需调用（特殊的存储过程！）

2：本身就是一个事务（保证数据的完整性、一致性）

注意：触发器只是针对: insert、update和delete ，select没有触发器

关于old 和 new：

OLD 删除时、更新时

New: 插入数据时

#利用触发器，自动实现对删除数据的备份，将student的删除的数据插入到备份表。
drop trigger t_del;
DELIMITER //
create TRIGGER t_del BEFORE  delete on student for each row
begin
	  insert into student_bak(id,name,gender,age) values(OLD.id,OLD.NAME,OLD.GENDER,OLD.AGE);
end //

例子

Update的操作会用到：OLD 和NEW

#银行卡信息表
drop table if exists t_card;
CREATE table if not exists t_card(
  cardid char(11) not null PRIMARY key,
	balance DECIMAL not null
);
#交易明细表
drop table if exists t_tran;
CREATE table if not exists t_tran(
  id int auto_increment primary key,
  cardid char(11) not null,
	type varchar(10) not null,
	money DECIMAL not null,
	t_time datetime not null
);
#插入测试数据
insert into t_card(cardid,balance) values('6221 1001',1000);
insert into t_card(cardid,balance) values('6221 1002',100);
#没有触发器的情况下：
#update t_card set balance= balance -100 where cardid='6221 1001';
#insert t_tran(cardid,money,type,t_time) values('6221 1001',100,'',now());
#创建自动跟踪用户存取款的触发器，自动记录交易信息
delimiter //
CREATE TRIGGER t_update BEFORE update on t_card for each row
begin
     #交易类型的判断：
		 DECLARE m1 DECIMAL;
		 DECLARE m2 DECIMAL;
		 DECLARE type varchar(10);
		 #获取交易前的余额
		 select OLD.balance into m1 from t_card where cardid=OLD.cardid;
		 #获取交易后的余额
		 select NEW.balance into m2 from t_card where cardid=OLD.cardid;
		 if m2 > m1 then
		     set type='存款'; 
		 else
		     set type='取款';
		 end if;
		 INSERT into t_tran(cardid,type,money,t_time)
		 values(OLD.cardid,type,ABS(m2-m1),now());
end //
#测试取钱
update t_card set balance = balance-100 where cardid='6221 1001';
#测试存款
update t_card set balance = balance+600 where cardid='6221 1002';
select * from t_card;
select * from t_tran;