数据库原理及应用.实验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),
  	('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)
  

1. 定义信息系学生基本情况视图V_IS，并查看视图结构，通过该视图可以将其他系学生信息屏蔽掉。

   CREATE VIEW V_IS AS
   SELECT * FROM Student
   WHERE Sdept='IS'
   

2. 定义视图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
   

3. 将各系学生人数，平均年龄定义为视图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
   

4. 定义一个反映学生出生年份的视图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
   

5. 将各位学生学号、选修课程的门数及平均成绩定义为视图V_AVG_STUDENT。

   CREATE VIEW V_AVG_STUDENT AS
   SELECT Sno AS '学号',
   COUNT(*) AS '选修课门数',
   AVG(Grade) AS '平均成绩'
   FROM SC
   GROUP BY Sno
   

6. 将各门课程的课程号、选修人数及平均成绩定义为视图V_AVG_COURSE。

   CREATE VIEW V_AVG_COURSE AS
   SELECT Cno AS '课程号',
   COUNT(*) AS '选修人数',
   AVG(Grade) AS '平均成绩'
   FROM SC
   GROUP BY Cno
   

7. 使用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约束的条件。

8. 利用表和前面所建视图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
   

9. 利用表和前面所建视图V_AVG_COURSE，查询成绩大于课程平均成绩的学生学号、课程号和成绩。

   SELECT sc.sno 学号,sc.cno 课程号,sc.grade 成绩
   FROM sc,V_AVG_COURSE
   WHERE sc.cno=V_AVG_COURSE.课程号 AND sc.grade>V_AVG_COURSE.平均成绩
   

10. 利用表和前面所建视图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
    

11. 删除以上所有视图。

    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;
    

二、在学生-课程数据库上创建索引并使用索引：

1. 使用CREATE INDEX语句为student表创建一个组合索引id_sno_sdept，以学号降序、院系升序。

   CREATE INDEX id_sno_sdept
   ON Student(Sno DESC ,Sdept ASC);
   

2. 删除student 上原先的唯一索引，使用ALTER TABLE语句在sname列上重新建立普通降序索引 id_sname。

   DROP INDEX Sname ON Student;
   ALTER TABLE Student ADD UNIQUE INDEX id_sname(Sname DESC);
   

3. 在course表的cname列上建立唯一索引id_cname。

   CREATE UNIQUE INDEX id_cname
   ON Course(Cname);
   

4. 用三种方式查看student表中的索引。

   SHOW INDEX FROM Student;
   DESC Student;
   SHOW CREATE TABLE Student;
   

5. 用DROP INDEX语句删除索引id_sno_sdept。

   DROP INDEX id_sno_sdept ON Student;
   

6. 用ALTER TABLE语句删除索引id_sname。

   ALTER TABLE Student DROP INDEX id_Sname;
   

实 验 心 得：

　　通过本次实验，我掌握了创建（CREATE VIEW）修改视图的方法，熟悉了视图更新与基本表更新的区别与联系，了解了视图的作用。同时我还熟悉索引的作用，以及不同类型索引的区别。最后，我掌握了SQL语句对索引的创建使用和删除索引的方法。

附 录：