数据库实践 Hw05

1.1

使用create view语句来创建视图语法格式为：

create
   [or replace]
   [algorithm={undefined |merge | temptable }]
   view view_name [(column_list)]
   as select_statement
   [with [cascaded | local] check option]
1
2
3
4
5
6

下列有关语法说明错误的是： B

• A、
or replace 语句用于替换数据库中已有的同名视图，但需要在该视图上具有DROP权限。
• B、
algorithm子句 是MySQL对标注SQL的扩展，规定了MySQL处理视图的算法。Algorithm可取三个值：undefined，merge、template。Create view语句默认算法为template。
• C、
select_statement：用于指定创建视图的select语句，这个select语句给出了视图的定义，它可以用于查询多个基础表或者源视图
• D、
with check option：该可选子句用于指定在可更新视图上所进行的修改都需要符合select_statement中所指定的限制条件

1.2

下列关于创建视图的说法正确的是：

A、
select语句可以引用系统或者用户变量

B、
select语句可以引用预处理语句参数

C、
运行创建视图的语句需要用户具有创建视图的权限

D、
在定义中可以引用temporary表且可以创建temporary视图

正确答案： C

1.3

下列SQL语句中，不属于查看视图语句的是：

A、
Describe/dec view_name

B、
show table status like ‘view_name’

C、
drop view view_name

D、
select * from information_schema.views where table_name=’view_name’

正确答案： C

2.1

请编写SQL语句，创建一个查询acc_transaction表中所有交易的交易历史编号、交易金额以及交易类型编码的视图,并用describe语句查看视图

create view  trade(trade_id, trade_amount, trade_type)
as select txn_id, amount, txn_type_cd
from acc_transaction;
desc trade;
1
2
3
4

2.2

查询视图acc_view1中交易金额大于等于10000并且小于等于50000的交易历史编号、交易类型编码

select trade_id, trade_type from acc_view1
where trade_amount between 10000 and 50000;
1
2

2.3

使用alter view修改视图acc_view1的列名为交易历史编号、交易金额以及交易类型编码

alter view acc_view1(交易历史编号, 交易金额, 交易类型编码)
as select txn_id, amount, txn_type_cd
from acc_transaction;
1
2
3

2.4

创建函数emp_cnt：以支行名称(branch.NAME)为输入参数，返回该支行员工总人数

DROP FUNCTION IF EXISTS emp_cnt;
CREATE FUNCTION emp_cnt(brh VARCHAR(20)) RETURNS int
Reads SQL data
BEGIN
       DECLARE cnt INT;
       SELECT COUNT(employee.EMP_ID) INTO cnt FROM employee, branch
       WHERE employee.ASSIGNED_BRANCH_ID = branch.BRANCH_ID and branch.NAME = brh;
       RETURN cnt;
END;
1
2
3
4
5
6
7
8
9

set global log_bin_trust_function_creators=TRUE;
delimiter //
create function emp_cnt(NAME varchar(20)) returns int
begin
 declare sum int;
    select count(*)    
    from branch join employee
    where branch.name = NAME 
    and branch_id = assigned_branch_id
    into sum;
    return sum;
end;//

1
2
3
4
5
6
7
8
9
10
11
12
13

2.5

利用上述函数，查询上海市内每间支行的员工总数

select name, emp_cnt(name)

from branch

where city = "上海市";
1
2
3
4
5

2.6

创建函数get_acc_type,以账户余额(account.AVAIL_BALANCE为输入，根据该账户的余额返回该账户的类型。类型划分标准为：

若余额大于100000，账户类型为’P1’
若余额大于10000，账户类型为’P2’
否则账户类型为’P3’

DROP FUNCTION IF EXISTS emp_cnt;
CREATE FUNCTION emp_cnt(brh VARCHAR(20)) RETURNS int
Reads SQL data
BEGIN
       DECLARE cnt INT;
       SELECT COUNT(employee.EMP_ID) INTO cnt FROM employee, branch
       WHERE employee.ASSIGNED_BRANCH_ID = branch.BRANCH_ID and branch.NAME = brh;
       RETURN cnt;
END;
DROP FUNCTION IF EXISTS get_acc_type;
CREATE FUNCTION get_acc_type(acc_avail DECIMAL(12, 4)) RETURNS VARCHAR(5)
READS SQL DATA
BEGIN
       DECLARE acc_type VARCHAR(5);
       IF acc_avail > 100000 THEN
              SET acc_type = 'P1';
       ELSEIF acc_avail BETWEEN 10000 AND 100000 THEN
              SET acc_type = 'P2';
       ELSE
              SET acc_type = 'P3';
       END IF;
       RETURN  (acc_type);
 
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

desc account;//
create function get_acc_type (avail decimal) returns char(2)
begin
 if avail > 100000 then return 'P1';
    elseif avail > 10000 then return 'P2';
    else return 'P3';
    end if;
end;//
select avail_balance, get_acc_type(avail_balance)
from account;


1
2
3
4
5
6
7
8
9
10
11
12

2.7

创建并执行存储过程acc_process,创建一个视图acc_type，并选择该视图的前10行数据。 acc_type包含两个列：账户编号(ACCOUNT_ID)和账户属性(ACCOUNT_TYPE)。

DROP PROCEDURE IF EXISTS acc_process;
CREATE PROCEDURE acc_process()
BEGIN
      
       DROP VIEW IF EXISTS acc_type;
       CREATE VIEW acc_type(ACCOUNT_ID, ACCOUNT_TYPE)
       AS SELECT account.ACCOUNT_ID, get_acc_type(account.AVAIL_BALANCE)
       FROM account ;
      
       SELECT * FROM acc_type LIMIT 10;
 
END;
 
CALL acc_process;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

create procedure acc_process()
begin
 create view acc_type(ACCOUNT_ID, ACCOUNT_TYPE)
    as select ACCOUNT_ID, TXN_TYPE_CD
    from acc_transaction;
    
    select * from acc_type limit 0, 10;

end;//
DROP PROCEDURE ACC_PROCESS;
//
call acc_process();


1
2
3
4
5
6
7
8
9
10
11
12
13
14