MySQL学习笔记4

数据库视图

视图的含义：
视图是一个虚拟表，是从数据库中一个或者多个表中导出来的表。视图还可以从已存在的视图的基础上定义。
图解

视图的作用

• 对复杂的sql语句进行封装
• 保护敏感信息

视图的特性

• 简单性
• 安全性
• 逻辑数据独立性

创建视图

语法

CREATE VIEW 视图名 AS <select statement>

示例

CREATE VIEW 视图名 AS select 字段名1，字段名2，字段名N from 表名

查看试图

DESCRIBE语句查看视图的基本信息

• DESCRIBE 视图名;

SHOW TABLE STATUS语句查看视图基本信息

• SHOW TABLE STATUS LIKE 视图名;

SHOW CREATE VIEW语句查看视图详细信息

• SHOW CREATE VIEW 视图名;

在VIEWS表中查看视图详细信息
在MySQL中，information_schema数据库下的view表中存储了所有视图的定义。通过对VIEW表的查询，可以查看数据库中所有视图的详细信息。

• select * from information_schema.views;

修改视图

ALTER 语句修改试图

ALTER VIEW 视图名 AS <select statement>;

更新视图

更新试图是指通过插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。通过视图更新的时候都是转到基本表进行更新的，如果对视图增加或者删除记录，实际上是对其基本表增加或者删除记录。

删除视图

删除视图时必须拥有DROP权限

DROP VIEW [IF EXISTS]视图名1,视图名2...;

自己实践

create view view_1 as
    select c.c_name as 商品名称,c.c_madein as 商品产地,c.c_outprice as 商品售价,c.c_num as 商品库存,ct.ct_name as 商 品种类,c.c_id as 商品ID
    from commoditytype as ct inner join commodity as c
    on ct.ct_id = c.c_type and c.c_outprice is not null;
create view view_2 as
    select cu.cu_name as 客户名称,cu.cu_phone as 客户号码,cu.cu_address as 客户地址,o.o_num as 订单数量,o.o_cid as 商品ID
    from `order` as o inner join customer as cu
    on o.o_cuid=cu.cu_id;
create view view_3 as
    select o.客户名称,o.客户号码,o.客户地址,o.订单数量,c.商品名称,c.商品产地,c.商品种类
    from view_2 as o inner join view_1 as c
    on o.商品ID = c.商品ID;

效果

总结

视图是不存在于内存中的虚拟的表，方便了我们查看和使用。修改视图中的数据会修改原先表中的数据。

触发器

触发器创建语法四要素

1. 监视地点(table)
2. 监视事件(insert/update/delete)
3. 触发时间(after/before)
4. 触发事件(insert/update/delete)

语法

create trigger tri_test1
after insert on 表名
for each row
begin
update 表明 set num=num-new.字段名 where 字段名 = new.字段名;
end

总结

触发器 new old

• insert语句，只有new是合法的。
• delete语句，只有old是合法的。
• update语句，new和old可以同时使用

示例

CREATE TRIGGER tr1
BEFORE UPDATE ON t22
FOR EACH ROW
BEGIN
SET @old = OLD.s1;
SET @new = NEW.S1;
END;
//现在如果t21表中的s1列的值是55，那么执行了“UPDATE t21 SET s1=s1+1”之后@old的值会变成55，而@new的值将会变成56.

注意：
如果你在触发器里面对刚刚插入的数据进行insert/update, 则出现Can’t update table ‘customer’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger。因为会造成循环的调用.比如

create trigger test
before update on test
for each row
update test set NEW.updateTime = NOW() where
id=NEW.ID;
END

应该使用set操作，而不是在触发器里使用 update,比如

create trigger test
before update on test
for each row
set NEW.updateTime = NOW();
END

自己实践

create trigger tri_1
	after insert on `order`
	for each row
    begin
    update commodity set c_num=c_num-new.o_num where c_id=new.o_cid;
    end $
    create trigger tri_3
    before update on `order`
    for each row
    begin
    update commodity set c_num=c_num-(new.o_num-old.o_num) where o_cid=c_id;
    end$
    create trigger tri_4
    after delete on `order`
    for each row
    begin
    update commodity set c_num=c_num+old.o_num where old.o_cid=c_id;
    end$

删除触发器

drop trigger tri_name;

存储过程

概念

• 存储过程能完成一定操作的一组SQL语句

作用

• 大大提高效率（存储过程本身执行速度非常快，调用存储过程大大减少数据库交互次数）；提高代码重用性

创建存储过程：

create procedure sp_name(proc_parameter[...])
begin
[characteristic...] routine_body
end
// sp_name:存储过程名称
// proc_parameter:存储过程的参数列表
// characteristic:存储过程的特性
// routine_body:SQL语句的内容，可以用begin...end来标志SQL语句的开始和结束

创建不带参数的存储过程

CREATE procedure pro1()
BEGIN 
select studentName from studentinfo;
END

创建带参存储过程

//IN参数示例
create procedure pro2(in n int)
begin
select studentName from studentinfo
where gradeID=n;
END
//OUT参数示例
create procedure pro3(out p_out int)
begin
select p_out;
set p_out=2;
select p_out;
end
//INOUT参数示例
CREATE PROCEDURE pro4(INOUT p_inout int)
BEGIN
select p_inout;
set p_inout=2;
select p_inout;
END

存储过程的调用

CALL SP_NAME([parameter[....]]);
//sp_name:存储过程名称
//proc_parameter:存储过程的参数列表

查看存储过程

• 通过show procedure status;查看存储过程和函数状态
• 通过show create procedure 查看存储过程和函数定义
• 通过information_schema.Routines查看存储过程和函数信息

删除存储过程

drop procedure sp_name;

自己实践

create procedure pro_4(in id int(11), in cuid int(11), in cid int(11), in num int(11), out flag int(11))
begin
set autocommit=0;
start transaction;
insert into `order` (o_id,o_cuid,o_cid,o_num) values (id,cuid,cid,num);
set @cnum =0;
set @cnum =(select c_num  from commodity where c_id=cid);
if @cnum >=0 then
commit;
set flag=1;
else
rollback;
set flag=0;
end if;
set autocommit=1;
end$