数据库原理及应⽤.实验4.视图和索引的创建和使⽤实验报告
  课程名称：数据库原理及应⽤
  实验项⽬名称：视图和索引的创建和使⽤
  实验时间：2021年5⽉10⽇
实验⽬的:
  (1)掌握创建视图的SQL语句的⽤法。
  (2)掌握修改视图的⽅法。
  (3)熟悉视图更新与基本表更新的区别与联系；认识视图的作⽤。
  (4)熟悉索引的作⽤，以及不同类型索引的区别。
  (5)掌握SQL语句对索引的创建使⽤和删除索引
实验环境:
  MySQL、SQLyog
实验内容及过程:
⼀、创建课本P79页的学⽣-课程数据库，完成以下实验内容：
复制DROP DATABASE ST
CREATE DATABASE ST
USE ST
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40) NOT NULL,
Cpno CHAR(4),
Ccredit SMALLINT
#FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
INSERT Student(Sno, Sname, Ssex, Sage, Sdept)
VALUES (201215121,'李勇','男',20,'CS'),
(201215122,'刘晨','⼥',19,'CS'),
(201215123,'王敏','⼥',18,'MA'),
(201215125,'张⽴','男',19,'IS')
INSERT Course(Cno,Cname,Cpno,Ccredit)
VALUES ('1','数据库','5',4),
foreign key references用法
('2','数学',' ',2),
('3','信息系统','1',4),
('4','操作系统','6',3),
('5','数据结构','7',4),
('6','数据处理',' ',2),
('7','PASCAL语⾔','6',4)
INSERT SC(Sno, Cno, Grade)
VALUES (201215121,1,92),
(201215121,2,85),
(201215121,3,88),
(201215122,2,90),
(201215122,3,80)
定义信息系学⽣基本情况视图V_IS，并查看视图结构，通过该视图可以将其他系学⽣信息屏蔽掉。
复制CREATE VIEW V_IS AS
SELECT * FROM Student
WHERE Sdept='IS'
定义视图V_Student_Grade，能够显⽰学⽣学号，姓名，课程号，课程名，成绩。
复制CREATE VIEW V_Student_Grade AS
SELECT Student.Sno AS '学号',
Student.Sname AS '姓名',
Course.Cno AS '课程号',
Course.Cname AS '课程名',
SC.Grade AS '成绩'
FROM Student,Course,SC
WHERE Student.Sno=SC.Sno AND Course.Cno = SC.Cno
将各系学⽣⼈数，平均年龄定义为视图V_COUNT_AVG，并查看视图创建信息。
复制CREATE VIEW V_COUNT_AVG AS
SELECT Sdept AS '系名',
COUNT(*) AS '⼈数',
AVG(Sage) AS '平均年龄'
FROM Student
GROUP BY Sdept
SELECT * FROM V_COUNT_AVG
定义⼀个反映学⽣出⽣年份的视图V_BIRTH，使⽤CREATE OR REPLACE
VIEW语句创建，多次执⾏该创建语句并查看结果。
复制CREATE OR REPLACE VIEw V_BIRTH AS
SELECT Sname as '姓名',
(2021-Sage) as '出⽣年份'
FROM Student
SELECT * FROM V_BIRTH
将各位学⽣学号、选修课程的门数及平均成绩定义为视图V_AVG_STUDENT。
复制CREATE VIEW V_AVG_STUDENT AS
SELECT Sno AS '学号',
COUNT(*) AS '选修课门数',
AVG(Grade) AS '平均成绩'
FROM SC
GROUP BY Sno
将各门课程的课程号、选修⼈数及平均成绩定义为视图V_AVG_COURSE。
复制CREATE VIEW V_AVG_COURSE AS
SELECT Cno AS '课程号',
COUNT(*) AS '选修⼈数',
AVG(Grade) AS '平均成绩'
FROM SC
GROUP BY Cno
使⽤SQL语句完成以下视图操作，查看结果，并分析原因。
①通过视图V_IS，将学号为"201215125"的学⽣姓名更改为"张⼩⽴"，并查看结果;
复制UPDATE V_IS
SET Sname='张⼩⽴'
WHERE Sno=201215125;
SELECT * FROM V_IS;
②通过视图V_IS，新增加⼀个学⽣记录 ('201215126','黄笑',19,
'男','IS')，并查看结果。
复制INSERT INTO V_IS(Sno,Sname,Sage,Ssex,Sdept)
VALUES (201215126,'黄笑',19, '男','IS');
SELECT * FROM V_IS
WHERE Sno=201215126;
③通过视图V_IS，新增加⼀个学⽣记录 ('201215127','李霞',19,'⼥','MA')，并查看结果。
复制INSERT INTO V_IS(Sno,Sname,Sage,Ssex,Sdept)
VALUES ('201215127','李霞',19, '⼥','MA');
SELECT * FROM Student
WHERE Sno=201215127;
④通过视图V_IS，删除学号为"201215126"的学⽣信息，并查看结果。
复制DELETE
FROM V_IS
WHERE Sno='201215126';
SELECT * FROM V_IS
WHERE Sno=201215126;
⑤通过视图V_Student_Grade，将学号"201215122"的姓名改为"刘晓晨"，能否实现？若⽆法实现说明原因。
复制UPDATE V_Student_Grade
SET 姓名='刘晓晨'
WHERE 学号=201215122;
SELECT * FROM V_Student_Grade
WHERE 学号=201215122;
#可以实现
⑥通过视图V_AVG_STUDENT，将学号"201215121"的平均成绩改为90，能否实现？若⽆法实现
说明原因。
⽆法实现，MySQL⽆法修改各科数据使其平均成绩为90
⑦修改视图V_IS定义，添加WITH CHECK OPTION语句，然后⽤update语句修改"201215125" 的学⽣所在系，改为'MA'，能否实现？若⽆法实现说明原因。
复制ALTER VIEW V_IS AS
SELECT * FROM STUDENT
WHERE SDEPT='IS'
WITH CHECK OPTION
UPDATE V_IS
SET SDEPT='MA'
WHERE SNO='200215125'
⽆法实现此操作，原因是⽬标视图所跨越的某⼀视图指定了WITH CHECK
OPTION，⽽该操作的⼀个或多个结果⼜不符合WITH CHECK OPTION约束的条件。
利⽤表和前⾯所建视图V_AVG_STUDENT，查询平均成绩为85分以上的学⽣学号、姓名和成绩。
复制SELECT V_AVG_STUDENT.学号,Student.Sname,V_AVG_STUDENT.平均成绩
FROM V_AVG_STUDENT,Student
WHERE Student.Sno=V_AVG_STUDENT.学号 AND V_AVG_STUDENT.平均成绩>85
利⽤表和前⾯所建视图V_AVG_COURSE，查询成绩⼤于课程平均成绩的学⽣学号、课程号和成绩。
复制SELECT sc.sno 学号,sco 课程号,sc.grade 成绩
FROM sc,V_AVG_COURSE
WHERE sco=V_AVG_COURSE.课程号 ade>V_AVG_COURSE.平均成绩
利⽤表和前⾯所建视图V_AVG_STUDENT，按系分组统计平均成绩80分以上的⼈数，降序排列。
复制SELECT DISTINCT SDEPT,COUNT(*)SUM
FROM Student,V_AVG_STUDENT
WHERE Student.SNO=V_AVG_STUDENT.学号 AND 平均成绩>80
GROUP BY STUDENT.SDEPT
ORDER BY SUM DESC
删除以上所有视图。
复制DROP VIEW V_IS;
DROP VIEW V_Student_Grade;
DROP VIEW V_COUNT_AVG;
DROP VIEW V_BIRTH;
DROP VIEW V_AVG_STUDENT;
DROP VIEW V_AVG_COURSE;
⼆、在学⽣-课程数据库上创建索引并使⽤索引：
使⽤CREATE INDEX语句为student表创建⼀个组合索引id_sno_sdept，以学号降序、院系升序。
复制CREATE INDEX id_sno_sdept
ON Student(Sno DESC ,Sdept ASC);
删除student 上原先的唯⼀索引，使⽤ALTER TABLE语句在sname列上重新建⽴普通降序索引 id_sname。
复制DROP INDEX Sname ON Student;
ALTER TABLE Student ADD UNIQUE INDEX id_sname(Sname DESC);
在course表的cname列上建⽴唯⼀索引id_cname。
复制CREATE UNIQUE INDEX id_cname
ON Course(Cname);
⽤三种⽅式查看student表中的索引。
复制SHOW INDEX FROM Student;
DESC Student;
SHOW CREATE TABLE Student;
⽤DROP INDEX语句删除索引id_sno_sdept。
复制DROP INDEX id_sno_sdept ON Student;
⽤ALTER TABLE语句删除索引id_sname。
复制ALTER TABLE Student DROP INDEX id_Sname;
实验⼼得：
  通过本次实验，我掌握了创建（CREATE VIEW）修改视图的⽅法，熟悉了视图更新与基本表更新的区别与联系，了解了视图的作⽤。同时我还熟悉索引的作⽤，以及不同类型索引的区别。最后，我掌握了SQL语句对索引的创建使⽤和删除索引的⽅法。
附录：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。