數(shù)據(jù)庫(kù)原理及應(yīng)用.實(shí)驗(yàn)4.視圖和索引的創(chuàng)建和使用

1、數(shù)據(jù)庫(kù)原理及應(yīng) 實(shí)驗(yàn)視圖和索引的創(chuàng)建和使實(shí)驗(yàn)報(bào)告實(shí) 驗(yàn) 的:實(shí) 驗(yàn) 環(huán) 境:實(shí) 驗(yàn) 內(nèi) 容 及 過 程:、創(chuàng)建課本 頁的學(xué) 課程數(shù)據(jù)庫(kù)，完成以下實(shí)驗(yàn)內(nèi)容：DROP DATABASE STCREATE DATABASE STUSE STCREATE 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 C

2、HAR(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),(201

3、215122,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)CREATE VIEW V_IS ASSELECT *

4、FROM StudentWHERE Sdept=ISV_Student_GradeCREATE VIEW V_Student_Grade ASSELECT Student.Sno AS ,Student.Sname AS ,Course.Cno AS ,Course.Cname AS ,SC.Grade AS FROM Student,Course,SCWHERE Student.Sno=SC.Sno AND Course.Cno = SC.CnoCREATE VIEW V_COUNT_AVG ASSELECT Sdept AS ,COUNT(*) AS ,AVG(Sage) AS FROM

5、StudentGROUP BY SdeptSELECT * FROM V_COUNT_AVGCREATE OR REPLACE VIEw V_BIRTH ASSELECT Sname as ,(2021-Sage) as FROM StudentSELECT * FROM V_BIRTH。CREATE VIEW V_AVG_STUDENT ASSELECT Sno AS ,COUNT(*) AS ,AVG(Grade) AS FROM SCGROUP BY SnoV_AVG_COURSE。CREATE VIEW V_AVG_COURSE ASSELECT Cno AS ,COUNT(*) AS

6、 ,AVG(Grade) AS FROM SCGROUP BY Cno;UPDATE V_ISSET Sname=WHERE Sno=201215125;SELECT * FROM V_IS;(201215126,INSERT INTO V_IS(Sno,Sname,Sage,Ssex,Sdept)VALUES (201215126,19, ,IS);SELECT * FROM V_ISWHERE Sno=201215126;(201215127,INSERT INTO V_IS(Sno,Sname,Sage,Ssex,Sdept)VALUES (201215127,19, ,MA);SELE

7、CT * FROM StudentWHERE Sno=201215127;DELETEFROM V_ISWHERE Sno=201215126;SELECT * FROM V_ISWHERE Sno=201215126;V_Student_GradeUPDATE V_Student_GradeSET=WHERE=201215122;SELECT * FROM V_Student_GradeWHERE=201215122;#可以實(shí)現(xiàn)90update ALTER VIEW V_IS ASSELECT * FROM STUDENTWHERE SDEPT=ISWITH CHECK OPTIONUPDA

8、TE V_ISSET SDEPT=MAWHERE SNO=20021512585SELECT ,Student.Sname,V_AVG_STUDENT.平均成績(jī)FROM V_AVG_STUDENT,StudentWHERE Student.Sno=V_AVG_STUDENT.學(xué)號(hào)AND 85V_AVG_COURSESELECT sc.sno ,o ,sc.grade 成績(jī)FROM sc,V_AVG_COURSEWHERE 課程號(hào)AND sc.gradeV_AVG_COURSE.平均成績(jī)80SELECT DISTINCT SDEPT,COUNT(*)SUMFROM Student,V_AVG_S

9、TUDENTWHERE Student.SNO=V_AVG_STUDENT.學(xué)號(hào)AND80GROUP BY STUDENT.SDEPTORDER BY SUM DESCDROP 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;、在學(xué) 課程數(shù)據(jù)庫(kù)上創(chuàng)建索引并使索引：INDEXstudentCREATE INDEX id_sno_sdeptON Student(Sno DESC ,Sdept ASC); snameid_sname。DROP INDEX Sname ON Student;ALTER TABLE Student ADD UNIQUE INDEX id_sname(Sname DESC);coursecname。CREATE UNIQUE INDEX id_cnameON Course(Cname);student