上机 2 1、 基于第一次上机创建的银行数据库，创建一个视图 branch_detail，能够显示 所有支行的存款客户数量、存款总额、贷款客户数量、贷款总额。 2、 在 account 的 account_number 属性上建立索引，并在 account 表里插入大 量元组，比较有无索引在查询速度上的区别。 3、 创建角色银行经理 branch_manager，银行经理具有插入、删除和更新 branch 表的权限。 4、 自由练习第四章中级 SQL 的其他特性。 创建一个视图 branch_detail create view branch_detail as select * from (select branch_name as name1,count(distinct (customer_name)) as count1,sum(balance) from branch023 natural join account023 natural join depositor023 group by branch_name)as deposite, (select branch_name as name2,count(distinct (customer_name)) as count2,sum(amount) from loan023 natural join borrower023 natural join branch023 group by branch_name)as loan where name1=name2; select * from branch_detail; 

建立索引 select * from account023; create index accountnumber_index on account023(account_number); show index from account023; 创建角色银行经理 branch_manager create user 'branch_manager' identified by '1234'; grant INSERT,UPDATE,DELETE on bank023.branch023 to branch_manager; show grants for branch_manager; 创建自定义类型 create type dollars as numeric(12,2)final; 权限收回 revoke insert on branch023 from branch_manager restrict;