实验10 视图和索引

实验目的及要求：

（1）理解数据库中的视图和索引。

（2）具备编写和调试创建、修改、删除视图语句和更新视图语句的能力。

第一题： 对教学管理数据库JXGL进行如下查询：

1. 为课程表course的列Cname创建名为I_Cname的索引。
2. 为选课表SC的列Cno和Grade创建名为I_Cno_Grade 复合索引，其中Cno为升序，Grade为降序。

第二题：对teachmanage数据库进行如下查询：

1. 在teachmanage数据库中创建V_teachSituation视图，该视图包含teacher表的教师编号、姓名、职称、学院，course表的课程号、课程名，lecture表的上课地点。

2. 查询计算机学院教师的教师编号，姓名、课程名、上课地点。

3. 删除视图V_teachSituation。

4. 创建可更新视图V_renewTeach，该视图来源于基表：teacher，并包含teacher表中计算机学院教师信息。

5. 向V_renewTeach视图中插入一条记录：('100015','许涛','男','1990-03-07','副教授 ','计算机学院')。

6. 在V_renewTeach视图中，将教师编号为100015的记录的出生日期更新为1990-09-07。

7. 删除V_renewTeach视图中教师编号为100015的记录。

第三题：设计性实验

设计一个无法更新的视图，并验证。

CREATE DATABASE teachmanage

 
USE teachmanage
CREATE TABLE speciality
(
	specno char(6) NOT NULL PRIMARY KEY,
	specname char(16) NULL,
	)
 
USE teachmanage
CREATE TABLE student
(
	sno char(6) NOT NULL PRIMARY KEY,
	sname char(8) NOT NULL,
	ssex char(2) NOT NULL,
	sbirthday date NOT NULL,
	tc tinyint NULL,
	specno char(6) NOT NULL,
	)
 
USE teachmanage
CREATE TABLE course
(
	cno char(4) NOT NULL PRIMARY KEY,
	cname char(16) NOT NULL,
	credit tinyint NULL,
	)
 
USE teachmanage 
CREATE TABLE score
(
	sno char(6) NOT NULL ,
	cname char(16) NOT NULL,
	grade tinyint NULL,
	CONSTRAINT PK_score PRIMARY KEY(sno, cname),
	)
 
USE teachmanage
CREATE TABLE teacher
(
	tno char(6) not null primary key,
	tname char(8) not null ,
	tsex char(2) not null,
	tbirthday date not null,
	title char(12) null,
	school char(12) null
	)
 
USE teachmanage
CREATE TABLE lecture
(
	tno char(6) not null,
	cno char(4) not null,
	location char(10) null,
	constraint PK_lecture primary key(tno, cno)
	)

	--插入数据
insert into speciality
values('080701', '电子信息工程'),
('080702', '电子科学与技术'),
('080703', '通信工程'),
('080901', '计算机科学与技术'),
('080902', '软件工程'),
('080903', '网络工程');
 
insert into student
values('221001', '成远博', '男', '2002-04-17', '54', '080901'),
('221002', '傅春华', '女', '2001-10-03', '52', '080901'),
('221003', '路勇', '男', '2002-03-15', '52', '080901'),
('226001', '卫婉如', '女', '2001-08-21', '54', '080701'),
('226002', '孟茜', '女', '2002-12-19', '50', '080701'),
('226004', '夏志强', '男', '2001-09-08', '54', '080701');
 
insert into course
values('1004', '数据库系统', 4),
('1012', '计算机系统结构', 3),
('1201', '英语', 5),
('4008', '通信原理', 5),
('8001', '高等数学', 5);
 
insert into score
values('221001', '1004', 94),
('221001', '1201', 92),
('221001', '8001', 92),
('221002', '1004', 87),
('221002', '1201', 86),
('221002', '8001', 88),
('221003', '1004', 93),
('221003', '1201', 93),
('221003', '8001', 86),
('226001', '1201', 92),
('226001', '4008', 93),
('226001', '8001', 92),
('226002', '1201', NULL),
('226002', '4008', 78),
('226002', '8001', 75),
('226004', '1201', 93),
('226004', '4008', 86),
('226004', '8001', 91);
 
insert into teacher
values('100003', '杜明杰', '男', '1978-11-04', '教授', '计算机学院'),
('100018', '严芳', '女', '1994-09-21', '讲师', '计算机学院'),
('120032', '袁书雅', '女', '1991-07-18', '副教授', '外国语学院'),
('400006', '范慧英', '女', '1982-12-25', '教授', '通信学院'),
('800014', '简毅', '男', '1987-05-13', '副教授', '数学学院');
 
insert into lecture
values('100003', '1004', '2-106'),
('120032', '1201', '4-204'),
('400006', '4008', '6-114'),
('800014', '8001', '3-219');



--作业第一题
--1.为课程表course的列Cname创建名为I_Cname的索引。
use jxgl
create index I_Cname on  course(Cname)

--2.为选课表SC的列Cno和Grade创建名为I_Cno_Grade 复合索引，其中Cno为升序，Grade为降序。
create index I_Cno_Grade on sc(cno, grade desc)


--作业第二题
--1. 在teachmanage数据库中创建V_teachSituation视图，该视图包含teacher表的教师编号、
--姓名、职称、学院，course表的课程号、课程名，lecture表的上课地点。
use teachmanage
go
create view V_teachSituation
as
select teacher.tno, tname, title, school, course.cno, cname, location
from teacher, course, lecture
where teacher.tno = lecture.tno and course.cno = lecture.cno
go

--2.查询计算机学院教师的教师编号，姓名、课程名、上课地点
select tno, tname, cname, location
from V_teachSituation

--3.删除视图V_teachSituation。
drop view V_teachSituation

--4.创建可更新视图V_renewTeach，该视图来源于基表：teacher，并包含teacher
--表中计算机学院教师信息。
go
create view V_renewTeach
as
select * from teacher
go

--5. 向V_renewTeach视图中插入一条记录：('100015','许涛','男','1990-03-07','副教授 ','计算机学院')。
insert into V_renewTeach
values('100015','许涛','男','1990-03-07','副教授 ','计算机学院')

--6. 在V_renewTeach视图中，将教师编号为100015的记录的出生日期更新为1990-09-07。
select * from V_renewTeach
go
update V_renewTeach
set tbirthday = '1990-09-07'
where tno = '100015'
go

--7.删除V_renewTeach视图中教师编号为100015的记录。
delete V_renewTeach
where tno = '100015'

--第三题：设计性实验
--创建带有课程的平均学分的视图，由于含有聚集函数无法更新视图
go
create view average_scoddfre(sno, gavg)
as
select sno 学号, avg(grade) 平均成绩 
from score
group by sno 
go

select * from average_score
update average_score
set 平均成绩 = 90
where 学号 = '221001'