数据库系统原理与应用--综合练习（1）

╰半橙微兮° 2022-05-22 11:17 185阅读 0赞

\--1.在Oracle数据库中新建用户，用户名为你的姓名，密码为和用户名相同  
create user tiangui01 identified by tiangui01;  
\--为新建用户授权，包括：建表、建视图、建触发器、建存储过程等权限

grant resource,create any view,connect to tiangui01;

3.           利用新建用户连接数据库，并在新用户（如：zhangsan）模式下，按照要求及说明建立下列所示的表。（4\*3分）。

**学生表student**

<table style="background:#FFFFFF;"> 
 <tbody> 
 <tr> 
 <td>字段</td> 
 <td>类型</td> 
 <td>说明</td> 
 </tr> 
 <tr> 
 <td>学号</td> 
 <td>Varchar2(6)</td> 
 <td>主键</td> 
 </tr> 
 <tr> 
 <td>姓名</td> 
 <td>varchar2(20)</td> 
 <td>非空</td> 
 </tr> 
 <tr> 
 <td>性别</td> 
 <td>varchar2(3)</td> 
 <td>性别（取值为“男”或“女”）</td> 
 </tr> 
 <tr> 
 <td>年龄</td> 
 <td>Number</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td>民族</td> 
 <td>varchar2(20)</td> 
 <td>默认“汉”</td> 
 </tr> 
 </tbody> 
</table>

create table student(
    xh varchar2(6) primary key,
    xm varchar2(20) not null,
    xb varchar2(2) check(xb='男' or xb='女'),
    nl number,
    mz varchar(20) default '汉'
    );

**社团表club**

create table club(
    bh varchar2(6) primary key,
    stmc varchar2(12) unique,
    hddd varchar2(30)
    );

**参加社团登记表enter**

create table enter(
    xh varchar2(6),
    stbh varchar2(6),
    cjsj date,
    foreign key(xh) references student(xh)
    );

添加测试数据：

社团表测试数据：

begin

insert into club values('c01','篮球','篮球馆');

insert into club values('c02','国际象棋',NULL);

insert into club values('c03','排球','体育馆');

insert into club values('c04','桥牌',NULL);

end;

学生表测试数据：

begin  
  
  
insert into student values('s01','李勇','男',19,'汉族');  
  
  
insert into student values('s02','李明','男',20,'汉族');  
  
  
insert into student values('s03','张新','男',20,'汉族');  
  
  
insert into student values('s04','李斯','女',20,'汉族');  
  
  
insert into student values('s05','李丽','女',19,'汉族');  
  
  
insert into student values('s06','赵雨','女',21,'回族');  
  
  
insert into student values('s07','李力','男',21,'汉族');  
  
  
insert into student values('s08','刘同','男',18,'汉族');  
  
  
insert into student values('s09','曾明','男',19,'汉族');  
  
  
insert into student values('s10','李鑫','男',19,'汉族');  
  
  
insert into student values('s11','李鑫','女',24,'汉族');  
  
  
insert into student values('s12','李蕊','女',22,'汉族');  
  
  
end;

参加社团登记表测试数据：

begin

insert into enter values('s01','c01','5-9月-16');

insert into enter values('s02','c01','12-9月-15');

insert into enter values('s03','c01','5-9月-15');

insert into enter values('s04','c02','5-3月-16');

insert into enter values('s05','c02','5-9月-16');

insert into enter values('s06','c03','5-3月-13');

insert into enter values('s07','c03','3-9月-15');

insert into enter values('s08','c04','5-3月-13');

insert into enter values('s09','c04','5-9月-15');

insert into enter values('s10','c04','2-9月-13');

insert into enter values('s11','c01','5-9月-10');

insert into enter values('s01','c02','5-9月-16');

insert into enter values('s02','c03','12-9月-15');

insert into enter values('s01','c03','5-9月-16');

insert into enter values('s05','c04','5-9月-16');

end;

\--4.修改enter表，为enter表增加外键约束，即社团编号参照club表的编号。（5分）  
  
alter table enter add constraint e\_fk foreign key(stbh)references club(bh);  
  
\--5.在student表以及enter表中分别添加一条考生自己的信息及参与c01号社团的信息，其中参加社团时间为当前系统时间。(5分)  
begin  
insert into student values('s13','李开','男',22,'汉族');  
insert into enter values('s06','c01',sysdate);  
end;

\--6.查询所有姓“李”的女生信息；（5分）

（可以直接查询student 表）

select \* from   
student inner join enter on student.xh=enter.xh   
inner join club on club.bh=enter.stbh  
where xb='女' and xm like '李%';

\--7.查询每个社团的人数，结果显示社团编号及人数；（5分）

思路：分组查询 center表

select stbh 编号,count(stbh) 人数 from enter  
group by stbh;

\--8. 查询参加“篮球”社团的学生编号、姓名及参加日期，并将查询结果按参加日期降序排列。（5分）  
select student.xh,student.xm,cjsj from  
enter inner join student   
on enter.xh=student.xh   
inner join club  
on club.bh=enter.stbh

order by cjsj desc;

法二：

select student.学号,姓名,参加时间 fromstudent,enter,club

where student.学号=enter.学号 and enter.社团编号=club.编号 and 社团名称='篮球'

order by 参加时间 desc；

\--9. 修改club表的数据，将活动地点为空的记录改为“学生活动中心”。（5分）

update club set hddd='学生活动中心' where hddd is null;

\--10. 删除表enter中参与社团时间超过5年（含5年）的登记记录。（5分）

delete from enter where (sysdate-cjsj)/365>=5;

\--11. 创建一个关于“学生参与社团情况”的视图，包括参与社团的学生的姓名、学号、社团的名称及未参加社团活动的活动学生信息。（5分）  
  
create view 学生参与社团情况 as select xm 姓名,student.xh 学号,stmc 社团名称   
from student left outer join enter on student.xh=enter.xh

left outer join club on club.bh=enter.stbh;

\--12. 查询参与2个（含2个）及以上社团的学生学号及参与社团的数量。（5分）

select xh 学号,count(stbh) 社团数量 from enter  
group by xh  
having count(stbh)>=2;

\--13. 查询每个社团中参加社团时间最长的学生姓名及所在社团名。（5分）  
select xm,stmc,cjsj from club,enter e,student  
where student.xh=e.xh and e.stbh=club.bh  
and cjsj=(select min(cjsj) from enter where enter.stbh=e.stbh);  
  
\--14. 创建一个存储过程，根据社团编号，输出该社团中参与的学生姓名，学号、参与时间及社团名称。并执行该存储过程（以c01为例）（10分）  
create or replace procedure pro\_select(cid club.bh%type) as  
    cursor mycur is select xm,student.xh,cjsj,stmc  
    from enter,club,student  
    where enter.xh=student.xh and enter.stbh=club.bh and enter.stbh=cid;  
begin  
    for x in mycur  
    loop  
    dbms\_output.put\_line('姓名:'||x.xm||'学号:'||x.xh||'参与时间:'||x.cjsj||'社团名称:'||x.stmc);  
    end loop;  
end pro\_select;  
set serveroutput on;  
\--执行存储过程  
exec pro\_select('c01');  
  
\-- 15. 创建一个触发器，当删除学生表中的记录信息时，将参与社团登记表（enter）中对应的学生登记记录一并删除。并对触发器验证（10分）

create or replace trigger test\_tri  
after delete  
on student  
for each row  
begin  
delete from enter where xh=:old.xh;  
end;  
\--验证触发器：   
delete from student where xh='s01';