(1)
CREATE TABLE Staff
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20),
Sage SMALLINT,
SJob CHAR(10),
Sal INT,
Dno CHAR(20)
)
CREATE TABLE Department
(Dno CHAR(20) PRIMARY KEY,
Dname CHAR(20),
Manager CHAR(20),
Tel CHAR(10)
)
(2)
CREATE TABLE Department
(Dno CHAR(20) PRIMARY KEY,
Dname CHAR(20),
Manager CHAR(20),
Tel CHAR(10)
)
CREATE TABLE Staff
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20),
Sage SMALLINT,
SJob CHAR(10),
Sal INT,
Dno CHAR(20),
FOREIGN KEY(Dno) REFERENCES Department(Dno)
)
(3)
CREATE TABLE Department
(Dno CHAR(20) PRIMARY KEY,
Dname CHAR(20),
Manager CHAR(20),
Tel CHAR(10)
)
CREATE TABLE Staff
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20),
Sage SMALLINT CHECK(Sage<=60),
SJob CHAR(10),
Sal INT,
Dno CHAR(20),
FOREIGN KEY(Dno) REFERENCES Department(Dno)
)
(1)
先在数据库的course表和sc表中添加关于离散数学的相关内容。
首先建立一个表存放执行后的结果
写存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `countSoreP`()
BEGIN
declare less60 smallint default 0; /*x <= 60*/
declare b60a70 smallint default 0;
declare b70a80 smallint default 0;
declare b80a90 smallint default 0;
declare more90 smallint default 0;
declare countcno char(4) default '****';
SELECT cno
INTO countcno FROM course
WHERE cname = '离散数学';/*确定course表中有离散数学这门课*/
SELECT COUNT(*)
INTO less60 FROM sc
WHERE cno = '8' AND grade < 60;
SELECT COUNT(*)
INTO b60a70 FROM sc
WHERE cno = '8' AND grade >= 60 AND grade < 70;
SELECT COUNT(*)
INTO b70a80 FROM sc
WHERE cno = '8' AND grade >= 70 AND grade < 80;
SELECT COUNT(*)
INTO b80a90 FROM sc
WHERE cno = '8' AND grade >= 80 AND grade < 90;
SELECT COUNT(*)INTO more90 FROM sc
WHERE cno = '8' AND grade >= 90;
create table countScore( scorestage char(10), number smallint);
insert into countscore values('x<60', less60);
insert into countscore values('60<=x<70', b60a70);
insert into countscore values('70<=x<80', b70a80);
insert into countscore values('80<=x<90', b80a90);
insert into countscore values('x>=90', more90);
END
(2)统计任意一门课的平均成绩
创建表结构,用来存储执行结果
CREATE DEFINER=root@localhost PROCEDURE scoreavgP()
BEGIN
declare curname char(40) default ‘*‘;
declare curcno char(4) default ‘**‘;
declare curavg char(4);
declare mycursor cursor for
select cno, cname from course;
open mycursor;
loop
fetch mycursor into curcno, curname;
select avg(grade) into curavg from sc
where cno = curcno;
insert into scoreavg values(curcno, curname, curavg);
end loop;
close mycursor;
END
(3)
CREATE PROCEDURE `changeStageP` ()
BEGIN
declare chgrade char(1) default 'A';
declare grade smallint(6) default 0;
declare ID int default 0;
declare mycursor2 cursor for
select grade from sc;
alter table sc add column(newgrade char(1));
open mycursor2;
loop /*当游标打开时进行下列循环处理*/
fetch mycursor2 into grade; /*游标推进一行取结果送变量*/
if grade < 60 then
chgrade = 'E';
elseif grade < 70 then
chgrade = 'D';
elseif grade < 80 then
chgrade = 'C';
elseif grade < 90 then
chgrade = 'B';
elseif
chgrade = 'A';
update id set id = id + 1 where id < 9
update sc(id) set newgrade = chgrade;
end loop;
close mycursor;
END