SQL学习 DAY5

第八章 视图

8.1 创建视图

##视图 跟表格类似
use sql_invoicing;
create view sales_by_client as #as放在最后
select 
     c.client_id,
     c.name,
     sum(invoice_total) as total_sale
from clients c
join invoices i using (client_id)
group by  client_id,name #因为用了sum 故需要用到group by

8.2 更改视图 

#可更新视图的条件：无distinct/aggregate functions(min max avg..) group by/having,union
#视图的作用 原表无法更改可以创建视图对表更改

##eg 1
delete from invoices_with_balance
where invoice_id=1
##eg 2
update invoices_with_balance
set due_date=date_add(due_date,interval 2 day)
where invoice_id=2

当修改视图中的行时，结果会导致行从视图里删除 

with option check 子句：防止update、delete 语句将行从视图中删除

视图的优点：简化查询；减少数据库变动的影响；使用视图限制基础表的访问

第九章 存储过程

9.1创建存储过程

#把一个查询代码存在存储过程里
create procedure get_clients() #创建存储过程get_clients begin*****end
begin
     select *from clients;#创建语句时，每条都需要用;隔开，即使只有一条也要用";"，
end

#把所有语句打包一体，需要修改默认分隔符  （一般这样写代码）
delimiter $$##可以用$/等
create procedure get_clients() 
begin
     select * from clients;
end$$
delimiter ; #最后要把分隔符改回分号；

#call 调用存储
call get_clients()

mysql创建视图

 删除存储过程

#最好把删除和创建每个存储过程的代码存储在不同的sql文件中，并把文件放在git那样的源代码控制下
drop procedure if exists get_clients;
delimiter $$ #开始创建存储
create procedure get_clients()
begin 
select * from clients;
end$$
delimiter ;

  具体看b站视频【71】集

9.2 参数

#可使用参数为调用程序赋值 
# char指定长度的字符串 varchar 可变长度的字符串，一般用于存储姓名/电话号码/信息等
#写一个存储过程 返回指定state的数据
drop procedure if exists get_clients_by_state;
delimiter $$
create procedure get_clients_by_state
( pstate char(2))#参数写在()内，每个参数用;分隔 char(2)表示有两个字符的字符串eg：CA NY
begin 
     select * from clients
	 where state=pstate;#这里是用一个值同它本身的值做对比；右边的pstate是上面()定义的
end$$
delimiter ;

#调用建立的get_clients_by_state程序
call sql_invoicing.get_clients_by_state('ca')#()内必须填值

为系数配置默认值

CREATE PROCEDURE get_payments
#写一个存储过程name为get_payments，带两个参数设置client_id、payment_method_id
#int中有存储整数的数据类型大小
#int、tinyint用来储存整数的，后者占1字节内存可储存0-255数字，前者占四个字节可储存更大数据
( client_id int,
   payment_method_id tinyint 
   )
#当两者都是非空时 返回数据库中所有的付款记录；提供client_id则返回该客户的付款；
#若两个系数都赋值了则返回指定客户使用指定付款方式支付的所有款项
BEGIN
   select *
   from payments p 
   where p.client_id=ifnull(client_id,p.client_id) #第一个限制条件
   and p.payment_method= ifnull(payment_method_id,p.payment_method);#一定要有一个;作为结束
END

call get_payments(null,null)#赋值null：称为参数
#返回了客户的所有付款
#(1,null)只看到客户1的付款记录
#(1,2)只看到客户1，付款方式为2的记录

 9.3 参数验证

CREATE PROCEDURE make_payment(
   invoice_id int,
   payment_amount decimal(9,2),#9表示位数 2表示小数点后面的位数
   payment_date date
)

BEGIN
    #如何验证输入的参数是正确的 eg只能正数却输入了负数
    #用if 弹出错误框
	if payment_amount <= 0 then 
        signal sqlstate "22003"#网上有相应的错误符号
         set message_taxt="不合理的付款金额";
    end if;
    #开始为给定的id更新发票
    update invoices i
    set
       i.payment_total=payment_amount,
       i.payment_date=payment_date
       where i.invoice_id=invoice_id;
END

 9.4 输出参数

##看视频【75】一般尽量避免使用

#用set语句定义变量并加上@前缀，一般用于调用有输出参数的存储过程
#通过传递这些变量来获取输出参数值
set @invoices_count=0 #称为user或会话变量
#本地变量：可在存储过程或函数内定义
create procedure get_risk_factor()
begin
 #在此设定一个规则
 #在该存储过程中定义变量 eg risk_factor=invoices_total/invoices_count *5
   declare risk_factor decimal(9,2) default 0;
   #declare:声明变量,decimal最多9位数且若有小数则只能有两位小数点，default 给变量一个默认值0,不然默认值为空
   declare invoices_total decimal(9,2);
   declare invoices_count int;
   #写选择语句来设置这两个变量
   select count(*),sum(invoice_total)
   into invouces_count,invoices_total
   from invoices;
   set risk_factor=invoices_total/invoices_count*5;
   select risk_factor
end
#以上为在存储过程中声明并使用本地变量
#注意 该声明只存在与存储过程中，一旦执行完毕存储过程 它们将会被删掉

（1）函数属性
  deterministic #确定性 输入是确定的返回也是确定的
   reads sql data#配置选择语句来读取数据
    modifies sql data #修改sql数据 

CREATE FUNCTION get_risk_factor_for_client(
client_id int
)
RETURNS INTEGER
#设置函数属性
#输入客户id会返回多个不同值 因此不需要deterministic 
reads sql data
BEGIN
 declare risk_factor decimal(9,2) default 0;
   declare invoices_total decimal(9,2);
   declare invoices_count int;
   select count(*),sum(invoice_total)
   into invouces_count,invoices_total
   from invoices i
   where i.client_id=client_id;#跟上一个代码相比多了这个
   set risk_factor=invoices_total/invoices_count*5;
RETURN ifnull(risk_factor,0);
END

#调用新函数获取客户risk_factor
select 
     client_id,
     name,
     get_risk_factor_for_client(client_id)
from clients