\documentclass[全部作业]{subfiles}
\input{mysubpreamble}

% 不知道为什么这里又变成不用framed才能不出现换页问题了
% \BeforeBeginEnvironment{minted}{}
% \AfterEndEnvironment{minted}{}
% 这样好像不能替换掉原来的，那就只能把framed移动到需要的部分里了

\begin{document}
\setcounter{chapter}{5}
\chapter{第六次作业}
\begin{enumerate}
    \item \choice[1]{关于游标，选项中说法错误的是（）
    A
    游标可以定在查询结果集的特定行，也可以从结果集的当前行检索一行或多行

    B
    通常我们并不使用游标，但是需要逐条处理数据的时候，游标显得十分重要

    C
    游标使用时必须在完成后关闭，以释放资源

    D
    游标的 SELECT 语句中可以使用 INTO 子句来创建新表

    }{4}
    \item \choice[1]{下列关于触发器的描述正确的是（）
    A
    MySql的触发器只支持行级出发，不支持语句级触发

    B
    触发器可以调用将数据返回客户端的存储程序

    C
    在触发器中可以使用显式或者隐式方式开始或结束事务的语句

    D
    在MySql中，不可使用new和old引用触发器中发生的记录内容
    }{1}
    \item \choice[1]{关于before和after触发器的说法中，哪一项是正确的？（）
    A
    在 BEFORE 触发器中，可以对 INSERT 和 UPDATE 的 NEW 值进行修改；而在 AFTER 触发器中，无法修改 NEW 值

    B
    在 AFTER 触发器中，可以修改 NEW 值，但在 BEFORE 触发器中不能修改

    C
    BEFORE 触发器不能用于 UPDATE 操作

    D
    AFTER 触发器可以对 INSERT 和 UPDATE 的 NEW 值进行修改


    }{1}
    \item \choice[1]{关于 MySQL 触发器中 SELECT 语句的使用，以下哪种说法是正确的？（）
    A
    触发器中可以包含 SELECT 语句，并且可以返回结果集
    
    B
    触发器中可以包含 SELECT 语句，但不能返回结果集
    
    C
    触发器中必须包含 SELECT 语句，以便执行其他操作
    
    D
    触发器中可以使用 SELECT 语句来更新表的记录
    
    }{2}
    \item \choice[1]{关于存储过程的说法中，哪一项是正确的？（）
    A
    存储过程可以通过 SELECT 语句直接返回结果集给调用者
    
    B
    存储过程只能接受一个输入参数，并且不能返回任何值
    
    C
    存储过程可以使用 BEGIN ... END 块来定义多个 SQL 语句的执行逻辑
    
    D
    存储过程的定义必须包含 CREATE FUNCTION 关键字
    
    }{3}
    \questionandanswer[]{
        创建一个名为 check_student_age_trigger 的触发器，该触发器用于在 students 表中插入新记录之前进行年龄检查。要求如下：

触发器在执行 INSERT 操作之前触发。

如果新插入的学生年龄（age 列）小于 18 岁，触发器应该将 age 设置为 18。

如果新插入的学生年龄大于 120 岁，触发器应该将 age 设置为 120。

触发器应该记录所有插入操作的原始年龄值到一个名为 age_log 的表中，表结构如下：

log_id: INT, 主键，自增

student_id: INT, 学生学号

original_age: INT，原始年龄值

log_time: DATETIME，记录插入的时间

要求：

（1）编写 SQL 语句创建 age_log 表。

（2）编写 SQL 语句创建 check_student_age_trigger 触发器。

提示：

使用 NEW 关键字访问将要插入的记录中的字段。

使用 INSERT INTO 语句将原始年龄记录到 age_log 表中。



示例：

如果插入一条记录：INSERT INTO students (name, age) VALUES ('Alice', 16);

触发器将插入 age 为 18，并在 age_log 表中记录 original_age 为 16。
    }{}
    {\kaishu
        \begin{minted}{SQL}
create table age_log
(
    log_id       int primary key auto_increment,
    student_id   int comment '学生学号',
    original_age int comment '原始年龄值',
    log_time     datetime comment '记录插入的时间'
);

create trigger check_student_age_trigger
    before insert
    on students
    for each row
begin
    insert into age_log(student_id, original_age, log_time) value (new.id, new.age, now());
    case
        when (new.age < 18) then set new.age = 18;
        when (new.age > 120) then set new.age = 120;
        end case;
end;
        \end{minted}
    }
    \begin{verification}
        {\small
        \begin{minted}{SQL}
drop table if exists students;
drop table if exists age_log;

create table students
(
    id   int primary key,
    name varchar(100) comment '姓名',
    age  int comment '年龄'
);

create table age_log
(
    log_id       int primary key auto_increment,
    student_id   int comment '学生学号',
    original_age int comment '原始年龄值',
    log_time     datetime comment '记录插入的时间'
);

create trigger check_student_age_trigger
    before insert
    on students
    for each row
begin
    insert into age_log(student_id, original_age, log_time) value (new.id, new.age, now());
    case
        when (new.age < 18) then set new.age = 18;
        when (new.age > 120) then set new.age = 120;
        end case;
end;

insert into students(id, name, age)
values (10001, 'Alice', 16),
       (10002, 'Bob', 150);
        \end{minted}
        }

        students:
        \begin{csv}
,id,name,age
1,10001,Alice,18
2,10002,Bob,120
        \end{csv}

        age_log:
        \begin{csv}
,log_id,student_id,original_age,log_time
1,1,10001,16,2024-11-01 15:39:52
2,2,10002,150,2024-11-01 15:39:52
        \end{csv}
    \end{verification}
    \questionandanswer[]{
        假设有以下两个表：

        \noindent\includegraphics[width=1\linewidth]{imgs/2024-11-01-16-47-16.png}

        创建一个名为 withdraw_funds 的存储过程，用于从银行账户中提款。要求如下：

存储过程接受两个参数：

account_id: INT, 要提款的账户的 ID

amount: DECIMAL(10, 2), 要提款的金额

在提款前检查该账户的余额是否足够支付提款金额。

如果余额不足，存储过程应返回一个错误消息。

如果提款成功，更新账户余额，并在 transactions 表中记录提款信息（转出账户为提款账户，转入账户为 NULL，金额为提款金额）。

    }{}
    {\kaishu
        \begin{minted}{SQL}
create procedure withdraw_funds(in _account_id int, in amount decimal(10, 2))
begin
    start transaction;
    -- 如果账户不存在呢？好像没有交代应该怎么处理
    if (select accounts.balance from accounts where accounts.account_id = _account_id) < amount then
        rollback ;
        signal sqlstate '45000' set message_text = '余额不足';
    end if;
    update accounts set balance = balance - amount where accounts.account_id = _account_id;
    insert into transactions(from_account_id, to_account_id, amount) value (_account_id, null, amount);
    commit;
end;
        \end{minted}
    }
    \begin{verification}
        {\small
        \begin{minted}{SQL}
drop table if exists accounts;
drop table if exists transactions;

create table accounts
(
    -- 自增好像是从1开始的
    account_id     int primary key auto_increment comment '账户唯一标识符',
    account_holder varchar(100) comment '账户持有者的姓名',
    balance        decimal(10, 2) comment '账户余额'
);

create table transactions
(
    transaction_id   int primary key auto_increment comment '交易的唯一标识符',
    from_account_id  int comment '转出账户的ID',
    to_account_id    int comment '转入账户的ID',
    amount           decimal(10, 2) comment '转账金额',
    transaction_time datetime default current_timestamp comment '转账时间'
);

insert into accounts(account_holder, balance)
values ('a', 10),
       ('b', 100);

drop procedure if exists withdraw_funds;
create procedure withdraw_funds(in _account_id int, in amount decimal(10, 2))
begin
    start transaction;
    -- 如果账户不存在呢？好像没有交代应该怎么处理
    if (select accounts.balance from accounts where accounts.account_id = _account_id) < amount then
        rollback ;
        signal sqlstate '45000' set message_text = '余额不足';
    end if;
    update accounts set balance = balance - amount where accounts.account_id = _account_id;
    insert into transactions(from_account_id, to_account_id, amount) value (_account_id, null, amount);
    commit;
end;

call withdraw_funds(2, 10);
call withdraw_funds(1, 10);
call withdraw_funds(1, 10);
        \end{minted}
        }

        \begin{minted}{text}
[2024-11-01 16:35:33] [45000][1644] 余额不足
        \end{minted}

        accounts:
        \begin{csv}
,account_id,account_holder,balance
1,1,a,0.00
2,2,b,90.00
        \end{csv}
        \vspace{1em}

        transactions:\vspace{1em}\\
        \small\begin{csv}
,transaction_id,from_account_id,to_account_id,amount,transaction_time
1,1,2,,10.00,2024-11-01 16:35:33
2,2,1,,10.00,2024-11-01 16:35:34
        \end{csv}
    \end{verification}

    \questionandanswer[]{
        请根据下图所示的银行数据库，编写一个触发器来执行下列操作：在删除一个账户时，检查该账户的拥有者是否还有其他账户，如果没有，则将其从depositor关系中删除。
        \includegraphics[width=1\linewidth]{imgs/2024-11-01-21-57-55.png}
    }{}
    {\kaishu
        这里为什么要检测是否有其他账户，删除一个账户时不管这个人有没有其他账户都需要从depositor里删除account_number对应的行吧，那删除最后一个账户时depositor里自然也删完了。
        \begin{minted}{SQL}
create trigger delete_account
    after delete
    on account
    for each row
begin
    delete from depositor where account_number = old.account_number;
end;
        \end{minted}
    }
    \begin{verification}
        \begin{minted}{SQL}
drop table if exists account;
drop table if exists depositor;

create table account
(
    account_number int primary key,
    branch_name    varchar(100),
    balance        int
);

create table depositor
(
    customer_name  varchar(100),
    account_number int references account.account_number
);

insert into account
values (1, 'a', 100),
       (2, 'b', 200);

insert into depositor
values ('c', 1),
       ('c', 2);

create trigger delete_account
    after delete
    on account
    for each row
begin
    delete from depositor where account_number = old.account_number;
end;

delete from account where account_number = 1;
select * from depositor;
        \end{minted}
        \begin{csv}
,customer_name,account_number
1,c,2
        \end{csv}
        \begin{minted}{SQL}
delete from account where account_number = 2;
select * from depositor;
        \end{minted}
        \begin{csv}
,customer_name,account_number
,,
        \end{csv}
    \end{verification}
    
    \questionandanswer[]{
        假设有一个名为 employees 的员工表和一个名为 departments 的部门表，结构如下：
        \includegraphics[width=1\linewidth]{imgs/2024-11-02-11-15-08.png}
        （1）存储过程：名为 increase_salary，用于根据部门名称增加员工工资。要求：

 接受两个参数：dept_name（部门名称）和 percentage（增加的百分比）。

 如果部门不存在，返回错误消息。

 如果部门存在，更新该部门所有员工的工资，并返回更新的员工数量。

    }{}
    {\kaishu
        \begin{minted}{SQL}
create procedure increase_salary(in dept_name varchar(100), in percentage int)
begin
    if (select count(*) from departments where dept_name = departments.department_name) <= 0 then
        signal sqlstate '45000' set message_text = '部门不存在';
    end if;
    update employees join departments using (department_id)
    set salary = salary * (1 + percentage / 100)
    where department_name = dept_name;
    -- 存储过程怎么返回更新的员工数量
    -- 30 ms 中有 2 行受到影响 返回的影响行数应该就能反映更新的员工数量了
end;
        \end{minted}
    }
    \questionandanswer[]{
（2）函数：名为 get_average_salary，用于获取指定部门的平均工资。要求：

接受一个参数：dept_id（部门 ID）。

返回该部门员工的平均工资，如果没有员工，则返回 0。
    }{}
    {\kaishu
        \begin{minted}{SQL}
create function get_average_salary(dept_id int) returns decimal(10, 2)
begin
    declare result decimal(10, 2);
    if (select count(*) from employees where department_id = dept_id) <= 0 then
        set result = 0;
    else
        select avg(employees.salary) into result from employees where department_id = dept_id;
    end if;
    return result;
end;
        \end{minted}
    }
    \begin{verification}
        \begin{minted}{SQL}
drop table if exists employees;
drop table if exists departments;
drop procedure if exists increase_salary;
drop function if exists get_average_salary;

create table employees
(
    employee_id   int primary key auto_increment comment '员工唯一标识符',
    employee_name varchar(100) comment '员工姓名',
    department_id int comment '部门ID',
    salary        decimal(10, 2) comment '员工工资'
);
create table departments
(
    department_id   int primary key auto_increment comment '部门唯一标识符',
    department_name varchar(100) comment '部门名称'
);
        \end{minted}
        \quad
        % 大概知道了，只要在前一页结束前的位置加一个minted分段，并且在这个分段前面或后面存在一个字符，即使是空白的\quad 也可以，就不会出现分页异常的问题了
        \begin{minted}{SQL}
create procedure increase_salary(in dept_name varchar(100), in percentage int)
begin
    if (select count(*) from departments where dept_name = departments.department_name) <= 0 then
        signal sqlstate '45000' set message_text = '部门不存在';
    end if;
    update employees join departments using (department_id)
    set salary = salary * (1 + percentage / 100)
    where department_name = dept_name;
    -- 存储过程怎么返回更新的员工数量
    -- 30 ms 中有 2 行受到影响 返回的影响行数应该就能反映更新的员工数量了
end;

create function get_average_salary(dept_id int) returns decimal(10, 2)
begin
    declare result decimal(10, 2);
    if (select count(*) from employees where department_id = dept_id) <= 0 then
        set result = 0;
    else
        select avg(employees.salary) into result from employees where department_id = dept_id;
    end if;
    return result;
end;

insert into departments
values (101, 'aaa'),
       (102, 'bbb');

insert into employees(employee_name, department_id, salary)
values ('a', 101, 10),
       ('b', 101, 100),
       ('c', 102, 1000);
        \end{minted}
        \begin{minted}{SQL}
select get_average_salary(101);
        \end{minted}
        \begin{csv}
,get_average_salary(101)
1,55.00
        \end{csv}
        \begin{minted}{SQL}
select get_average_salary(103);
        \end{minted}
        \begin{csv}
,get_average_salary(103)
1,0.00
        \end{csv}
        \begin{minted}{SQL}
call increase_salary('aaa', 50);
        \end{minted}
        \begin{minted}{text}
[2024-11-02 11:13:13] 30 ms 中有 2 行受到影响
        \end{minted}
        \begin{minted}{SQL}
select * from employees;
        \end{minted}
        \begin{csv}
,employee_id,employee_name,department_id,salary
1,1,a,101,15.00
2,2,b,101,150.00
3,3,c,102,1000.00
        \end{csv}
        \begin{minted}{SQL}
call increase_salary('ccc', 100);
        \end{minted}
        \begin{minted}{text}
[2024-11-02 11:22:51] [45000][1644] 部门不存在
        \end{minted}
    \end{verification}

    \questionandanswer[]{
        存储过程、函数和触发器的区别？
    }{
        存储过程不能使用return语句返回结果，只能通过传入参数设置为out来返回结果；而函数可以直接使用return语句返回结果。
        触发器没有传入参数也不能返回结果，只能通过old和new获取更新前后的值。
    }

    \questionandanswer[]{
        触发器的作用？Mysql表中允许有多少个触发器？
    }{
        触发器定义了一系列操作，这一系列操作称为触发程序，当触发事件发生时，触发程序会自动运行。
        触发器主要用于监视某个表的插入（insert）、更新（update）和删除（delete）等更新操作，这些操作可以分别激活该表的insert、update和delete类型的触发程序运行，从而实现数据的自动维护。

        在5.7.2版本以前，同一个表不能创建两个相同触发时间、触发事件的触发程序，那么就是6个触发器：
        before insert、after insert、before update、after update、before delete、after delete。
        
        在5.7.2版本之后没有此限制，那么对触发器的数量就没有限制了。
    }
\end{enumerate}
\end{document}