MySQL 基础教程[3]
- 问题1
- 问题1代码
- 问题2
- 问题2代码
- 本系列MySQL 基础教程通过“问题-代码”的方式介绍各类方法，每篇设置2个MySQL综合问题，并给出解决方案。
问题1
- 在数据库mysqltest中, 创建Dept1表: Dept1(deptno, dname, higherdeptno), 说明: 部门编号（整型) , 部门名 称 (定长字符串、长度 20), 上级部门编号（整型, 缺省值为NULL), 并建立名为fk_higher的主外键关联。
- 现有部门表: Dept2(deptno, dname, higherdeptno) (说明: 部门编号, 部门多称, 上级部门编号), 请编写
, sname, sex, age)、课程
- 学生
(cno, cname)、选课
- 创建一个多为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下:
- 现有 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”。 - 假设有商品表 (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 THEN
SET NEW.customerLevel = '1st Level ';
elseif(creditlim <= 50000 && creditlim >= 10000) THEN
SET NEW.customerLevel = '2nd Level ';
ELSEIF creditlim < 10000 THEN
SET 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) THEN
SELECT '类别名称不存在、或者该类别中无商品！' AS Result;
ELSE
SELECT CONCAT('类别："',p_catname,'"中，有', ROW_COUNT(),'件商品价格已被更新!') AS Result;
END IF;
END $$
DELIMITER ;
