MySQL 数据库管理系统(八）——使用视图

1.创建视图

    create view productcustomer as select cust_name,cust_contact,prod_id from customers,orders,orderitems where customers.cust_id=orders.cust_id and orderitems.order_num=orders.order_num;

2.使用视图查询

    select cust_name,cust_contact from productcustomer where prod_id='FB';

3.创建存储过程
使用DELIMITER临时改变命令行的语句分隔符

    delimiter //
    create procedure productpricing() begin select avg(prod_price) as priceaverage from products;
    end//
    delimiter ;

4.使用存储过程

    call productpricing();

5.删除存储过程

    drop procedure productpricing;

6存储使用参数

    create procedure productpricing(
    out pl decimal(8,2),
    out ph decimal(8,2),
    out pa decimal(8,2))
    begin select min(prod_price) into pl from products; select max(prod_price) into ph from products; select avg(prod_price) into pa from products; end//

7.使用储存参数

    call productpricing (@pricelow,@pricehigh,@priceaverage);

8.输出结果

    select @pricelow,@pricehigh,@priceaverage;

储存案例
获得合计，把营业税有条件地添加到合计，返回合计

    create procedure ordertotal( in onumber int,in taxable boolean,out ototal decimal(8,2)) comment 'obtain order total ,optionally adding tax'
    begin declare total decimal(8,2); declare taxrate int default 6; select sum(item_price*quantity) from orderitems where order_num=onumber into total;
    if taxable then
      select total+(total/100*taxrate) into total;
    end if;
    select total into ototal;
    end;

没有营业税

    call ordertotal(20005,0,@total);
    select @total;

有营业税

      call ordertotal(20005,1,@total);
        select @total;

9.使用游标

    CREATE PROCEDURE processorders()
    BEGIN
    declare done boolean default 0;
      declare o int;
      declare t decimal(8,2);
      declare ordernumbers cursor 
      for 
      select order_num from orders;
      declare continue handler for sqlstate '02000' set done=1;
      create table if not exists ordertotals (order_num int,total decimal(8,2));
      open ordernumbers;
      repeat 
        fetch ordernumbers into o;
        call ordertotal (o,1,t);
        insert into ordertotals(order_num,total) values(o,t);
      until done end repeat;
      close ordernumbers;
    END

查看 ordertotals表

    select*from ordertotals;
   10.触发器
```sql
     create trigger tg2
    after insert on m
    for each row 
    begin
    update g set num=num-new.much where id=new.gid;
    end;

这时候我们只要执行：

    insert into m(gid,much) values(2,3);

删除触发器：

drop trigger tg2;

11,事务的处理
事务（transaction）是指一组SQL语句
回退（rollback）指撤销指定SQL语句的过程
提交（commit）指将未储存的SQL语句结果写入数据表；
保留点（savepoint）指事务处理中设置的临时占位符。

    START TRANSACTION;
    DELETE FROM orderitems WHERE order_num=20010;
    DELETE FROM orders WHERE order_num=20010;
    COMMIT;

表留点

savepoint deletel;
rollback TO deletel;

    START TRANSACTION;
    savepoint deletel;
    select * from ordertotals;
    delete from ordertotals; 
    rollback TO deletel;

更改默认提交

`set autocommit=0;`           //指示MySQL不自动提交更改。