头歌的数据库的第三次作业的答案

目录
MySQL-安全性控制
第1关：用户和权限
第2关：用户、角色与权限
MySQL-触发器
第1关：为投资表property实现业务约束规则-根据投资种别分别引用差别表的主码
MySQL-数据的插入、修改与删除(Insert,Update,Delete)
第1关：插入多条完备的客户信息
第2关：插入不完备的客户信息
第3关：批量插入数据
第4关：删除没有银行卡的客户信息
第5关：冻结客户资产
第6关：连接更新

---

MySQL-安全性控制

第1关：用户和权限

任务形貌
本关任务： 在金融应用场景数据库环境中，创建用户，并给用户授予指定的权限。

1. # 请填写语句，完成以下功能：
2. #(1) 创建用户tom和jerry，初始密码均为'123456'；
3. create user tom identified by '123456';
4. create user jerry identified by '123456';
5. #(2) 授予用户tom查询客户的姓名，邮箱和电话的权限,且tom可转授权限；
6. grant select(c_name,c_mail,c_phone) on client to tom with grant option;
7. #(3) 授予用户jerry修改银行卡余额的权限；
8. grant update(b_balance) on bank_card to jerry;
9. #(4) 收回用户Cindy查询银行卡信息的权限。
10. revoke select on bank_card from Cindy;

复制代码

第2关：用户、角色与权限

任务形貌
本关任务： 创建角色，授予角色一组权限，并将角色代表的权限授予指定的一组用户。

1. # 请填写语句，完成以下功能：
2. # (1) 创建角色client_manager和fund_manager；
3. create user client_manager,fund_manager;
4. # (2) 授予client_manager对client表拥有select,insert,update的权限；
5. grant select,insert,update on client to client_manager;
6. # (3) 授予client_manager对bank_card表拥有查询除银行卡余额外的select权限；
7. grant select(b_number,b_type,b_c_id) on bank_card  to client_manager;
8. # (4) 授予fund_manager对fund表的select,insert,update权限；
9. grant select,insert,update on fund to fund_manager;
10. # (5) 将client_manager的权限授予用户tom和jerry；
11. grant client_manager to tom,jerry;
12. # (6) 将fund_manager权限授予用户Cindy.
13. grant fund_manager to Cindy;

复制代码

MySQL-触发器

第1关：为投资表property实现业务约束规则-根据投资种别分别引用差别表的主码

任务形貌
本关任务： 为表property(资产表)编写一个触发器，以实现以下完备性业务规则：

• 假如pro_type = 1, 则pro_pif_id只能引用finances_product表的p_id；
  
• 假如pro_type = 2, 则pro_pif_id只能引用insurance表的i_id；
  
• 假如pro_type = 3, 则pro_pif_id只能引用fund表的f_id；
  
• pro_type不接受(1,2,3)以外的值。
  

各投资品种一经贩卖，不会再改变； 也不需考虑finances_product，insurance，fund的业务规则(一经贩卖的理财、保险和基金产品信息会永久保存，不会被删除或修改，即使不再贩卖该类产品)。

1. use finance1;
2. drop trigger if exists before_property_inserted;
3. -- 请在适当的地方补充代码，完成任务要求：
4. delimiter $$
5. CREATE TRIGGER before_property_inserted BEFORE INSERT ON property
6. FOR EACH ROW
7. BEGIN
9. IF (NEW.pro_type NOT IN (1,2,3)) THEN
10.     SET @msg = CONCAT('type ', NEW.pro_type, ' is illegal!');
11.     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
12.   END IF;
14.   IF (NEW.pro_type = 1) AND (SELECT COUNT(*) FROM finances_product WHERE p_id = NEW.pro_pif_id) = 0 THEN
15.     SET @msg = CONCAT('finances product #', NEW.pro_pif_id, ' not found!');
16.     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
17.   END IF;
19.   IF (NEW.pro_type = 2) AND (SELECT COUNT(*) FROM insurance WHERE i_id = NEW.pro_pif_id) = 0 THEN
20.     SET @msg = CONCAT('insurance #', NEW.pro_pif_id, ' not found!');
21.     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
22.   END IF;
24.   IF (NEW.pro_type = 3) AND (SELECT COUNT(*) FROM fund WHERE f_id = NEW.pro_pif_id) = 0 THEN
25.     SET @msg = CONCAT('fund #', NEW.pro_pif_id, ' not found!');
26.     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
27.   END IF;
29. END$$
31. delimiter ;

复制代码

MySQL-数据的插入、修改与删除(Insert,Update,Delete)

第1关：插入多条完备的客户信息

任务形貌
本关任务：向客户表client插入数据。

1. use finance1;
2. -- 用insert语句向客户表(client)插入任务要求的3条数据:
4. insert into client values
5. (1,'林惠雯','960323053@qq.com','411014196712130323','15609032348','Mop5UPkl'),
6. (2,'吴婉瑜','1613230826@gmail.com','420152196802131323','17605132307','QUTPhxgVNlXtMxN'),
7. (3,'蔡贞仪','252323341@foxmail.com','160347199005222323','17763232321','Bwe3gyhEErJ7');
9. /* end of you code */

复制代码

第2关：插入不完备的客户信息

任务形貌
本关任务：向客户表client插入一条数据不全的记录。

1. use finance1;
2. -- 已知33号客户部分信息如下:
3. -- c_id(编号):33
4. -- c_name(名称):蔡依婷
5. -- c_phone(电话):18820762130
6. -- c_id_card(身份证号):350972199204227621
7. -- c_password(密码):MKwEuc1sc6
9. -- 请用一条SQL语句将这名客户的信息插入到客户表(client)：
11. insert into client(c_id,c_name,c_phone,c_id_card,c_password)
12. values('33','蔡依婷','18820762130','350972199204227621','MKwEuc1sc6');
14. /* the end of your code */

复制代码

第3关：批量插入数据

任务形貌
本关任务：向客户表client批量插入数据。

1. use finance1;
2. -- 已知表new_client保存了一批新客户信息，该表与client表结构完全相同。请用一条SQL语句将new_client表的全部客户信息插入到客户表(client):
4. insert into client
5. select * from new_client;
7. /* the end of your code */

复制代码

第4关：删除没有银行卡的客户信息

任务形貌
本关任务：删除在本行没有银行卡的客户信息。

1. use finance1;
2. -- 请用一条SQL语句删除client表中没有银行卡的客户信息：
4. delete from client where c_id not in(
5.     select b_c_id from bank_card
6. );
8. /* the end of your code */

复制代码

第5关：冻结客户资产

任务形貌
本关任务：冻结客户的投资资产。

1. use finance1;
2. -- 请用一条update语句将手机号码为“13686431238”的这位客户的投资资产(理财、保险与基金)的状态置为“冻结”。：
4. update property set pro_status = '冻结'
5. where pro_c_id in (select c_id from client where c_phone = 13686431238);
7. /* the end of your code */

复制代码

第6关：连接更新

任务形貌
本关任务：根据客户表的内容修改资产表的内容。

1. use finance1;
2. -- 在金融应用场景数据库中，已在表property(资产表)中添加了客户身份证列，列名为pro_id_card，类型为char(18)，该列目前全部留空(null)。
4. -- 请用一条update语句，根据client表中提供的身份证号(c_id_card)，填写property表中对应的身份证号信息(pro_id_card)。
6. update property join client as c on property.pro_c_id=c.c_id
7. set property.pro_id_card=c.c_id_card;
9. /* the end of your code */

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。