1. 在Oracle数据库中新建用户,用户名为你的姓名(例如:zhangsan),密码为123。
2. 为新建用户授权,包括:建表、建视图、建触发器、建存储过程等权限。
3. 利用新建用户连接数据库,并在新用户(如:zhangsan)模式下,按照要求及说明建立下列所示的表。(10分)
学校表School:
字段 | 类型 | 说明 |
Id | Number | 主键 |
Name | varchar2(100) | 学校名称 |
Address | varchar2(200) | 地址(默认”北京“) |
学生表Student:
字段 | 类型 | 说明 |
Id | Number | 主键 |
Name | varchar2(12) | 学生姓名(非空) |
School_id | Number | 所属学校ID |
Department | varchar2(100) | 所属院系名称 |
Grade | Number | 年级(1800-2800之间) |
Birthday | Date | 生日 |
Code | varchar2(12) | 学号 |
添加测试数据:
begin
insert intoschool values(1,'北京大学',default);
insert intoschool values(2,'清华大学',default);
insert intoschool values(3,'北京理工大学',default);
insert intoschool values(4,'人民大学',default);
end;
begin
insert intostudent values(1,'李勇',1,'信息学院',2016,'11-6月-1999','2016001');
insert intostudent values(2,'李明',1,'信息学院',2016,'19-5月-1999','2016002');
insert intostudent values(3,'张新',1,'生命科学学院',2015,'11-2月-1999','2016003');
insert intostudent values(4,'李斯',2,'信息学院',2016,'11-6月-1999','2016001');
insert intostudent values(5,'李丽',2,'信息学院',2016,'19-5月-1999','2016002');
insert intostudent values(6,'赵雨',2,'生命科学学院',2015,'11-2月-1998','2015001');
insert intostudent values(7,'李力',2,'生命科学学院',2013,'11-2月-1996','2013002');
insert intostudent values(8,'刘同',3,'信息学院',2016,'11-6月-1999','2016001');
insert intostudent values(9,'曾明',3,'信息学院',2016,'19-5月-1999','2016002');
insert intostudent values(10,'李鑫',4,'生命科学学院',2016,'11-2月-1999','2016001');
insert intostudent values(11,'李鑫',4,'信息学院',2016,'11-2月-1999','2016002');
end;
查询表数据:
设置显示格式:
sql>SETLINESIZE 300;
sql>SETPAGESIZE 30;
sql>colname for A10;
sql>coladdress for A10;
sql>coldepartment for A20;
sql>colcode for A12;
查询当前表数据:
sql>select* from school;
sql>select* from student;
创建表:
创建school 表
create table school (
id number primary key,
name varchar2(100),
address varchar2(200) default '北京'
);
创建 student 表
create table student (
id number primary key,
name varchar2(12) not null,
school_id number,
department varchar2(100),
grade number,
birthday date,
code varchar2(12)
);
导入数据:
我们用图形界面来做.(我在创建表的时候表名写错了。你们在创建的时候注意一下)
完成图:
4. 修改学生表,为学生表增加外键约束,即学生的所属学校应参照学校表中的学校ID。
alter table student
add constraint student_school_fk
foreign key(school_id)
references school(id);
5. 在学生表中添加一条你自己的信息。
insert into student values(
13,
'田贵',
4,
'信息科学与工程学院',
1612,
'22-3月-97',
201612160227
);
6. 将学校编号为1的学生“李勇” 所在学院改为“环境工程学院”。
update student
set department='环境工程学院'
where school_id=1 and name='李勇'
7. 删除地址为空的学校记录信息。
delete from school where address is null;
8. 查询所有地址所在地为“北京”的学生;
select * from
student inner join school
on student.school_id=school.id
where school.ADDRESS='北京';
9. 查询所有年龄大于20的学生信息;
select * from
student inner join school
on student.school_id=school.id
where to_char(sysdate,'yyyy')-to_char(birthday,'yyyy') >20;
10. 查询清华大学所有年龄大于20的“王”姓学生;
select * from
student inner join school
on student.school_id=school.id
where to_char(sysdate,'yyyy')-to_char(birthday,'yyyy') >20
and school.name='清华大学' and student.name like '王%'
11. 统计每个学校的学生人数,查询结果包括学校编号、学生人数;
select school_id,count(*)from
student group by school_id;
12. 创建一个视图,统计每个学校、各个年级的学生人数,视图内容包括学校名称、年级、学生人数。
create view school_view as
select school.name 学校名称,grade 年级,count(*) 学生人数
from SCHOOL inner join STUDENT on
school.id=student.school_id
group by school.name ,grade;
13. 通过视图查询2015级人数超过3人的学校名称。
select 学校名称 from school_view
where "年级"=2015 and "学生人数">3;
14. 创建一个存储过程,根据学校ID和年级统计每个学校该年级的学生人数,并执行该存储过程。
输出形式:学校ID:1 年级:2016 人数:2
create or replace procedure my_pro(xxbh number,nj number)
as
rs int;
xx_name school.name%type;
begin
select school.name,count(*) into xx_name,rs
from student inner join school on school_id =school.id
where school_id = xxbh and grade=nj
group by school.name;
dbms_output.put_line('学校名称:'||xx_name||',年级:'||nj||',人数:'||rs);
end;
--执行:
set serveroutput on;
begin
my_pro(1,2016);
end;
15. 创建一个触发器,当删除学校表中的记录信息时,将学生表中对应该学校的学生一并删除。(10分)
create or replace trigger del_trgbefore delete on school
for each row
begin
delete from student where school_id=:old.id;
end;
--验证触发器:
delete from school where id=3;