sql权限控制习题-爱代码爱编程
SQL触发器:删除学生数据
#创建触发器,删除学生数据时,将删除的数据备份到数据表student_bf,以备误删除时进行数据恢复。
1、student(学生表):
SNO学号CHAR(7)
SNAME姓名CHAR(10)
SSEX性别CHAR(2)
SAGE年龄SMALLINT
SDEPT所在系 VARCHAR(20)
2、course(课程表)
CNO课程号CHAR(10)
CNAME课程名VARCHAR(20)
CCREDIT学分SMALLINT
SEMSTER学期SMALLINT
PERIOD学时SMALLINT
3、sc(选课表)
SNO 学号CHAR(7)
CNO 课程号CHAR(10)
GRADE 成绩 SMALLINT
[注意:SQL表名请用小写]
delimiter $
CREATE TRIGGER TRG_DELE
BEFORE DELETE ON student
for each row
begin
insert into student_bf
select * from student
where sno=old.sno;
end $
delimiter;
SQL触发器:创建成绩表插入触发器
#创建触发器,插入学生成绩时,限制必须在0-100之间。如果不在0-100之间,设置为0.
1、student(学生表):
SNO学号CHAR(7)
SNAME姓名CHAR(10)
SSEX性别CHAR(2)
SAGE年龄SMALLINT
SDEPT所在系 VARCHAR(20)
2、course(课程表)
CNO课程号CHAR(10)
CNAME课程名VARCHAR(20)
CCREDIT学分SMALLINT
SEMSTER学期SMALLINT
PERIOD学时SMALLINT
3、sc(选课表)
SNO 学号CHAR(7)
CNO 课程号CHAR(10)
GRADE 成绩 SMALLINT
[注意:SQL表名请用小写]
delimiter $
create trigger ss
before insert on sc
for each row
begin
if new.grade<0 or new.grade>100 then
set new.grade=0;
end if;
end $
delimiter;
SQL存储过程:建立存储过程,查询课程的成绩表
#建立存储过程show_grade:根据学号,课程名参数,返回学生的成绩。
学生数据库db_student包括三个数据表student(学生表)、course(课程表)和sc(选课表)。表结构如下:
1、student(学生表):
SNO学号CHAR(7)
SNAME姓名CHAR(10)
SSEX性别CHAR(2)
SAGE年龄SMALLINT
SDEPT所在系 VARCHAR(20)
2、course(课程表)
CNO课程号CHAR(10)
CNAME课程名VARCHAR(20)
CCREDIT学分SMALLINT
SEMSTER学期SMALLINT
PERIOD学时SMALLINT
3、sc(选课表)
SNO 学号CHAR(7)
CNO 课程号CHAR(10)
GRADE 成绩 SMALLINT
[注意:SQL表名请用小写]
delimiter $
create procedure show_grade(in ssno char(7),in scname varchar(20),out gg smallint)
begin
select grade from sc,course
where sno=ssno and sc.cno = course.cno and cname=scname
into gg;
end $
DELIMITER;
存储过程:返回学生总数,男生人数,女生人数
#存储过程 tj_rs:根据系名,统计该系学生总数,男生人数,女生人数。
[注意:SQL表名请用小写]
学生数据库db_student包括三个数据表student(学生表)、course(课程表)和sc(选课表)。表结构如下:
1、student(学生表):
SNO学号CHAR(7)
SNAME姓名CHAR(10)
SSEX性别CHAR(2)
SAGE年龄SMALLINT
SDEPT所在系 VARCHAR(20)
2、course(课程表)
CNO课程号CHAR(10)
CNAME课程名VARCHAR(20)
CCREDIT学分SMALLINT
SEMSTER学期SMALLINT
PERIOD学时SMALLINT
3、sc(选课表)
SNO 学号CHAR(7)
CNO 课程号CHAR(10)
GRADE 绩 SMALLINT
delimiter $$
create procedure tj_rs(
in SDEPT_0 VARCHAR(20),out total int,out gender_M int,out gender_F int)
begin
select count(*) into total
from student
where SDEPT_0= SDEPT;
select count(*) into gender_M
from student
where SDEPT_0=SDEPT and SSEX='男';
select count(*) into gender_F
from student
where SDEPT_0=SDEPT and SSEX='女';
end
$$
创建触发器,当更改商品价格(price列)时,记录价格
创建触发器,当更改商品product表的价格(price列)时,要将老的价格记录在一个名为price_log的表中。
相关表结构:
1、商品表:product
2、商品价格记录表:price_log
delimiter $
CREATE TRIGGER BEFORE_update_price
BEFORE UPDATE
ON product
FOR EACH ROW
BEGIN
if(old.price<>new.price) THEN
INSERT price_log(product_id,price)
VALUES(old.product_id,old.price);
END IF;
END $
delimiter ;
SQL存储过程:建立存储过程,查询学生的平均分
#建立存储过程show_grade:根据学号,返回学生的平均成绩(保留整数)。
学生数据库db_student包括三个数据表student(学生表)、course(课程表)和sc(选课表)。表结构如下:
1、student(学生表):
SNO学号CHAR(7)
SNAME姓名CHAR(10)
SSEX性别CHAR(2)
SAGE年龄SMALLINT
SDEPT所在系 VARCHAR(20)
2、course(课程表)
CNO课程号CHAR(10)
CNAME课程名VARCHAR(20)
CCREDIT学分SMALLINT
SEMSTER学期SMALLINT
PERIOD学时SMALLINT
3、sc(选课表)
SNO 学号CHAR(7)
CNO 课程号CHAR(10)
GRADE 成绩 SMALLINT
[注意:SQL表名请用小写]
CREATE PROCEDURE show_grade (
IN SNO1 CHAR(7),
OUT GRADE1 SMALLINT
)
SELECT AVG(GRADE) INTO GRADE1 FROM sc
WHERE SNO = SNO1
GROUP BY SNO;
SQL触发器:插入新员工时,同步更新部门表相应人数
创建触发器,插入新员工时,同步更新部门表相应人数。
相关表结构:
1、员工表:employee
2、部门表:department
delimiter $$
create TRIGGER t1
AFTER INSERT on employee
FOR EACH ROW
BEGIN
update department
set emp_num = coalesce(emp_num,0)+1 where dept_id = new.dept_id;
END $$
delimiter;
存储过程:根据部门编号,返回该部门的员工总数,男员工数,女员工数
创建存储过程tj_rs:根据部门编号(int),返回该部门的员工总数,男员工数,女员工数(注意参数顺序必须与题目一致)。
相关表结构:
1、员工表:employee
2、部门表:department
delimiter $$
create procedure tj_rs(in deptid int,out count1 int,out count2 int,out count3 int)
begin
select count(*)into count1 from employee where dept_id=deptid;
select count(*) into count2 from employee where dept_id=deptid and gender='男';
select count(*) into count3 from employee where dept_id=deptid and gender='女';
end
$$
SQL存储过程:查询订单
#创建存储过程:total_order(IN orderid INT , OUT totals NUMERIC(9,2), OUT cid INT)
要求:根据输入的订单编号(order_id),返回订单总金额(total_money),顾客编号(customer_id)。
订单表:orders
delimiter $
create procedure total_order(in orderid int,out totals numeric(9,2),out cid int)
begin
select total_money,customer_id from orders
where order_id=orderid
into totals,cid;
end $
delimiter ;