数据库实验二SQL查询语句实践

昆明理工大学信息工程与自动化学院学生实验报告（2012 —2013学年第一学期）课程名称：数据库系统教程开课实验室：信自楼445 2011年 11 月 21日年级、专业、班计科学号姓名成绩实验项目名称SQL查询语句实践指导教师丁教师评语教师签名：年月日注：报告内容按实验须知中七点要求进行。

一、实验目的（1）创建数据库、基本表的创建、修改、和撤销（2）SQL的数据更新（数据的插入、删除、修改）（3）创建视图、修改、删除视图、通过视图修改数据（4）对基本表建立主键、外键约束二、所用仪器、材料（设备名称、型号、规格等）PC：win7兼容性计算机一台软件：Microsoft SQL Server 2008三、实验方法、步骤（1）创建数据库、基本表的创建、修改、和撤销（2）SQL的数据更新（数据的插入、删除、修改）（3）创建视图、修改、删除视图、通过视图修改数据（4）对基本表建立主键、外键约束（5）修改表中相关数据验证数据完整性四、实验过程原始记录(数据、图表、计算等)（1）创建数据库、基本表的创建、修改、和撤销①创建数据库、基本表的创建CREATE DATABASE WANGJIUSE WANGJICREATE TABLE S(S#char(15)not null,SNAME char(10)not null,AGE int,SEX char(2),SDEPT char(4),PRIMARY KEY(S#))CREATE TABLE C(C#varchar(50)not null,CNAME varchar(50)not null,TEACHER varchar(50),Ccredit int not null,PRIMARY KEY(C#))CREATE TABLE SC(S#char(15)not null,C#varchar(50)not null,GRADE int,PRIMARY KEY(S#,C#),FOREIGN KEY(S#)REFERENCES S(S#),FOREIGN KEY(C#)REFERENCES S(C#))②基本表的修改、和撤销1.在S表中增加一个地址（ADDRESS）列，可用如下语句：ALTER TABLE S ADD ADDRESS VARCHAR(50)2.在S表中删除一个地址（ADDRESS）列，可用如下语句：ALTER TABLE S DROP ADDRESS CASCADE3.如果是多列，还要为每一列取一个名字：SELECT人数,平均分,最低分FROM(SELECT S.S#,COUNT(*)人数,AVG(GRADE)平均分,MIN(GRADE)最低分FROM SC,SWHERE S.S#=SC.S#AND SEX='男'GROUP BY S.S#HAVING COUNT(*)>='5'AND MIN(GRADE)>='65'AND AVG(GRADE)>='70')M4.把表中姓张的查出来(有两种写法)：SELECT*FROM S--WHERE SNAME LIKE '张%'WHERE LEFT(SNAME,1)='张'5.求每个同学的平均分，学号，姓名,结果按平均分降序：SELECT S.S#,SNAME,AVG(GRADE)FROM SC,SWHERE S.S#=SC.S#GROUP BY S.S#,SNAMEORDER BY AVG(GRADE)DESC6.查询选修了2号和4号课程的学生学号和姓名：SELECT S.S#,SNAMEFROM S,SC X,SC YWHERE S.S#=X.S#AND X.S#=Y.S#AND X.C#='2'AND Y.C#='4'7.查询学习全部程的学生学号，姓名：SELECT S#,SNAMEFROM SWHERE NOT EXISTS(SELECT*FROM C WHERE NOT EXISTS(SELECT*FROM SC WHERE SC.S#=S.S#AND SC.C#=C.C#))8.查询选修了3门以上且每门成绩都在70分以上的学生的学号、姓名和总分:SELECT S.S#,SNAME,SUM(GRADE)总分FROM S,SCWHERE S.S#=SC.S#--AND GRADE>=70GROUP BY S.S#,SNAMEHAVING COUNT(*)>=3 AND MIN(GRADE)>=70ORDER BY SUM(GRADE)DESC9.查询学生选课情（没选课的也要显示）：SELECT S.*,SC.*FROM S LEFT OUTER JOIN SC ON (S.S#=SC.S#)（2）SQL的数据更新（数据的插入、删除、修改）1.向SC中插入两个数据：INSERT INTO SC(S#,C#,GRADE)VALUES('201010405300','DB','75'),('201000000000','MATH','80')2.册除SC中学号为201010405300的学生的信息：DELETE FROM SCWHERE S#='201010405300'AND C#='DB'3.修改SC中学号为201000000000成绩为75分：UPDATE SCSET GRADE=75WHERE S#='201000000000'（3）创建视图、修改、删除视图、通过视图修改数据1. 创建一个STUDENT_GRADE(S#,SNAME,CNAME,GRADE) 视图：CREATE VIEW STUDENT_GRADE(S#,SNAME,CNAME,GRADE)ASSELECT S.S#,SNAME,CNAME,GRADE FROM S,SC,CWHERE S.S#=SC.S#AND SC.C#=C.C#SELECT*FROM STUDENT_GRADE2.修改STUDENT_GRADE视图：ALTER VIEW STUDENT_GRADE(S#,SNAME,CNAME)ASSELECT S.S#,SNAME,CNAME FROM S,SC,CWHERE S.S#=SC.S#AND SC.C#=C.C#SELECT*FROM STUDENT_GRADE3.删除STUDENT_GRADE视图：DROP VIEW STUDENT_GRADE3、通过视图修改数据CREATE VIEW S_MALE ASSELECT S#,SNAME,AGE FROM SWHERE SEX='男'SELECT*FROM S_MALEINSERT INTO S_MALEVALUES('102','朱启凯',20)4、对基本表建立主键、外键约束ALTER TABLE SC ADD CONSTRAINT PK_SC PRIMARY KEYCLUSTERED (S#,C#)ALTER TABLE SC ADD CONSTRAINT FK_SC_C FOREIGN KEY(C#)REFERENCES C(C#)5、修改表中相关数据验证数据完整性(1)、实体完整性规则ALTER TABLE SC ADD CONSTRAINT PK_SC PRIMARY KEYCLUSTERED (S#,C#)INSERT INTO SCVALUES(NULL,NULL,89)（2）、参照完整性规则如果属性集k是关系模式r1的主键，k也是关心模式r2中的外键，那么在r2的关系中，k的取值只有两种可能，或者为空，或者等于r1中的某个主键值。

这条规则的实质是不允许引用不存在的实体五、实验结果、分析和结论（误差分析与数据处理、成果总结等。

其中，绘制曲线图时必须用计算纸）通过本次试验我学会了创建视图、修改、删除视图、通过视图修改数据、对基本表建立主键、外键约束等。

也遇到了很多问题，比如其他同学的数据库能备份，能导入，而我也是同样的方法却不能。

后来问了很多同学也都说不知道，问了老师我才知道是数据库的拥有者不同，这也是我深刻的记忆。

还有各式各样的上机题，让我明白写出来的查询语句对不对，只有在电脑上自己实践才知道，而且记忆更加牢固、长久。

也让我知道再对数据库进行任何操作之前，都要对数据库进行备份，因为这是一个良好的习惯，也是对自己的一个保证。

学习数据库这一门课程，我感觉非常有用，在javaee课程编程中就得到了运用，而且学习这一门课很有趣，感觉能学到很多知识。

在今后的学习中，我会更加努力学习数据库，对今后的工作和学习做应该的准备，希望自己学有所成。

六、教学参考书：1、施伯乐、丁宝康、汪卫.数据库系统教程（第3版）.高等教育出版社，2008年7月2、王珊、萨师煊.数据库系统概论（第四版）.高等教育出版社，2006年7月3、胡孔法编著. 数据库原理及应用. 北京：机械工业出版社，20084、崔巍编著数据库系统及应用（第二版），高等教育出版社，2003.6教师必须按照上述各项内容严格要求，认真批改和评定学生成绩。