MySQL 基础教程[3]

• 问题1
• 问题1代码
• 问题2
• 问题2代码

• 本系列MySQL 基础教程通过“问题-代码”的方式介绍各类方法，每篇设置2个MySQL综合问题，并给出解决方案。

问题1

1. 在数据库mysqltest中, 创建Dept1表: Dept1(deptno, dname, higherdeptno), 说明: 部门编号（整型) , 部门名 称 (定长字符串、长度 20), 上级部门编号（整型, 缺省值为NULL), 并建立名为fk_higher的主外键关联。
2. 现有部门表: Dept2(deptno, dname, higherdeptno) (说明: 部门编号, 部门多称, 上级部门编号), 请编写 SQL\mathrm{SQL}SQL 语句, 在Dept2中添加一杂记录, deptno为 9 , dname为 “newdept”, higherdeptno为空。
3. 学生 S(sno\mathrm{S}(\mathrm{sno}S(sno, sname, sex, age)、课程 C\mathrm{C}C (cno, cname)、选课 SC\mathrm{SC}SC (sno,cno,grade), 请编写 SQL语句, 为选修课程"JAVA"的 学生学号、姓名、课程成绩, 湕立视图SJAVA。
4. 学生 S\mathrm{S}S (sno, sname, sex, age)、课程 C\mathrm{C}C (cno, cname)、选课 SC\mathrm{SC}SC (sno,cno,grade), 请编写 SQL\mathrm{SQL}SQL 语句, 将学生李红选修的课 程DB的成绩改为 90 。
5. 创建一个多为backupdb的用户, 指定其仅在localhost上登录, 密码为 “backup”。

问题1代码

#【1】
use mysqltest;
create table if not exists Dept1(
deptno int primary key,
dname varchar(20),
higherdeptno int default NULL,
constraint fk_higher foreign key (deptno) references Dept2(deptno)
);#【2】
insert into Dept2(deptno, dname) values(9, "newdept");
#【3】
create view SJAVA as select S.sno, S.sname, SC.grade from S, C, SC  where C.cname="JAVA" and S.sno=SC.sno and C.cno=SC.cno; 
#【4】
update SC set grade=90 where SC.sno=(select sno from S where sname="李红") and cno=(select cno from C where canme="DB");
#【5】
create user 'backupdb'@'localhost' identified by 'backup'; 

问题2

现有数据库mysqltest下:

1. 现有 Customer) 表: (customerNumber int primary key, creditlimit int NOT NULL, customerLevel CHAR
   (20) DEFAULT"), 其中 customerNumber 为客户编号, creditlimiti己录了某个用户当前的信贷限额。请创建一个触发 器, 在揷入新的客户记录时, 能够根据用户的的信贷限额设置其 customerLevel; 具体规则如下: 若当前信贷限额大 品 50000 , 则用户评级为 “1st Level"; 若当前信贷限额大于等于 10000 且小于等于 50000 , 则用户评级 为“2nd Level"; 若当前信货限额小于 10000 , 则用户评级为 “3rd Level”。

2. 假设有商品表 (product) 和商品类别表 (category)：product: {pid, pname, cid, price} (字段说明: 商品编 号, 商品名, 类别编号, 价格) category: {cid, cname } (字段说明: 类别编号, 类别名称) 请创建一个存储过 程priceIncrementByCat, 根据指定的类别名称 (长度 20 的字符型) 、以及价格浮动比例 (Double型), 对该类别中 所有的商品进行价格调整 (要求在存储过程中, 只能使用一条UPDATE语句, 不能使用其他对product和 category表 进行操作的SQL语句) 。如果成功进行了相关修改, 使用ROW_COUNTO函数打印出经价格修改的商品数目; 否 则, 打印出错误信息。

问题2代码

#【1】
DELIMITER $$
CREATE TRIGGER custLevel BEFORE INSERT ON customer FOR EACH ROW
BEGIN
DECLARE creditlim int;
SET creditlim=NEW.creditlimit;
IF creditlim > 50000 THENSET NEW.customerLevel = '1st Level ';
elseif(creditlim <= 50000 && creditlim >= 10000) THENSET NEW.customerLevel = '2nd Level ';
ELSEIF creditlim < 10000 THENSET NEW.customerLevel = '3rd Level';
END if ; 
END $$
DELIMITER ;#【2】
DELIMITER $$
CREATE procedure priceIncrementByCat(in p_catname CHAR(20), in p_increment decimal(10,2))
BEGIN
UPDATE product p,category c SET p.price=p.price*(1+p_increment)
WHERE p.cid=c.cid AND c.cname=p_catname;
IF(ROW_COUNT()=0) THENSELECT '类别名称不存在、或者该类别中无商品！' AS Result;
ELSESELECT CONCAT('类别："',p_catname,'"中，有', ROW_COUNT(),'件商品价格已被更新!') AS Result;
END IF;
END $$
DELIMITER ;