数据库原理（操纵，视图，索引）

1

1. 熟悉数据表结构及使用特点；
2. 熟悉使用Management Studio界面方式管理数据表数据；
3. 熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用；
4. 加深理解视图的定义和数据更新的作用；
5. 熟悉和掌握索引的创建和使用

以学生管理数据库为例，并依据数据表的结构创建相对应的数据表（student、course、sc），练习对表中数据的插入、修改和删除的SQL语言命令及视图和索引的创建与管理。

1.将学生、课程、选课三张表中的全部记录复制到student、course、xuanke三张表中；

select *into student from 学生
select *into course from 课程
select *into xuanke from 选课

2.用SQL语句将适量数据分别插入student、course、xuanke三张表中；

insert into student values('012','陈晓楠','20','女','计算机')
insert into course values ('c06','专业英语','大学英语')
insert into xuanke values ('012','c06','90')

3.将平均成绩低于70的学生信息存放在低于70分平均成绩表中；

select * into 低于70分平均成绩表 from student
where 学号 in (select 学号 from xuanke
group by 学号 having avg(成绩)<70 )

4.借助student、course、xuanke三张表，删除“计算机”系全体学生的选课记录及学生记录

delete from xuanke
where 学号 in (select 学号 from student
where 所在系 = '计算机')
delete from student
where 学号 in (select 学号 from student
where 所在系 = '计算机')

5.借助student、course、xuanke三张表，删除学号为“001”的相关信息（包括选课信息及学生信息）；

delete from xuanke
where 学号 = '001'
delete from student
where 学号 = '001'

6.将student表中学号为“002”的学生的学号修改为“S002”；

update student set 学号 = 's002' where 学号 = '002'

7.把平均成绩大于80分的男同学的学号和平均成绩存入另一个表S_Grade

（Sno，AVG_Grade）；
select student.学号,avg(成绩) 平均成绩 into S_grade
from student,xuanke
where student.学号=xuanke.学号 and 性别 = '男'
group by student.学号 having AVG(成绩)>80
select * from S_grade

8.把选修了课程名为“数据结构”的学生的成绩提高10%；

update xuanke set 成绩 = 成绩 * 1.1
where 课程号 in (select 课程号 from course
where 课程名 = '数据结构')

9.把选修了“c02”号课程，且成绩低于该门课程的平均成绩的学生的成绩提高5%；

update xuanke set 成绩 = 成绩 * 1.05
where 成绩<(select avg(成绩) from 选课 where 课程号 = 'c02')
select 课程号,成绩 from xuanke where 课程号 = 'c02'

10.把选修了“c02”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉；

delete  from xuanke
where 成绩<(select avg(成绩) from 选课 where 课程号 = 'c02')
select 课程号,成绩 from xuanke where 课程号 = 'c02'

11.求C01和C04课程成绩为80分以上的学生的学号及姓名；

select 学号,姓名 from student where 学号
in (select 学号 from xuanke
where 课程号 in ('c02','c04') and 成绩 > 80)

12.求选修了C1课程又选修了C2课程的学生的学号；

select 学号 from xuanke	where
课程号 = 'c01' and 学号 in (select 学号 from xuanke where 课程号 = 'c02')

13.求每个系的平均年龄，并要求将结果存入数据库中；

select 所在系,AVG(年龄) 平均年龄 into 平均年龄数据库
from student
group by 所在系
select *from 平均年龄数据库

–1

create view StulowGrade
as
select 学号,avg(成绩) as 平均成绩 from 选课 a where exists
(select * from 选课 b where 成绩<60 group by 学号 having count(学号)>2 and b.学号=a.学号)
group by 学号
select * from StulowGrade
  --2
create view Cstud
as
(select 学号,姓名,性别  from 学生)
insert into Cstud values('019','梨花','男')
select * from Cstud

–14.建立“计算机系”的学生基本情况视图ies_student_view，该视图包括计算机系所有学生的相关信息；

create view ies_student_view1
as
(select * from 学生 where 所在系='计算机')
select * from ies_student_view1

–16.建立课程C01（课编号）的学生名册的视图，该名册包括学生的学号、姓名、所在系名称和这门课的成绩；并查询结果；

create view c01
as
(select 学生.学号,姓名,所在系,成绩 from 学生,选课 where 学生.学号=选课.学号)
select * from c01

–17.建立统计不及格情况的视图，列出不及格学生的学号、姓名和不及格的课程代码。执行视图并观察结果；

create view 不及格视图
as
(select 学生.学号,姓名,课程号,成绩 from 学生,选课 where  成绩<60 and 学生.学号=选课.学号)
select * from 不及格视图

–18.修改视图ies_student_view，使该视图包括所有学生的学号、姓名、所在系。执行ies_student_view视图并观察结果；

alter view ies_student_view1
as
(select * from 学生 )
select * from ies_student_view1

–19.删除视图ies_student_view。并查询结果；

drop view ies_student_view1
select * from ies_student_view1

–20.在student_course表（学生选课表）的学生学号（s_no）列上创建索引sc_sno_index；通过SQL语句查看运行结果。

create  INDEX sc_sno_index ON 选课 (学号)
sp_helpindex 选课