数据库SQL语句实验报告.docx

1、数据库SQL语句实验报告数据库原理及应用实验报告SQL语句部分总 成 绩 ： 实验一 熟悉SQL SERVER，数据定义实验实验一成绩： 一、实验目的1、 利用查询分析器创建数据库2、 利用查询分析器用SQL语言定义基本表、索引并进行相关操作二、实验步骤及容在以下实验中，使用学生-课程数据库，它描述了学生的基本信息、课程的基本信息及学生选修课程的信息。1创建学生课程数据库2创建学生关系表S ：学号性别年龄所在系SnoSnameSsexSagesdept3创建课程关系表C ：课程号课程名先行课学分CnoCnameCpnoccredit4创建课程关系表SC ：学号课程号成绩SnoCnograde5

2、将以上创建表S、C、SC的SQL命令以 .SQL文件的形式保存在磁盘上。6、在表S上增加“出生日期”属性列。7、删除表S的“年龄” 属性列。8、删除S列的唯一值约束。9、修改SC表成绩属性列为精确数字型。10、在表S上，按“Sno” 属性列的唯一值方式建立索引。11、删除表SC，利用磁盘上保存的.SQL文件重新创建表SC。12、创建教材P74页习题5中SPJ数据库中的四个关系：S、P、J、SPJ。三、实验结果：1创建学生课程数据库create database Studend;2创建学生关系表S ：学号性别年龄所在系SnoSnameSsexSagesdeptcreate table S( Sn

3、o CHAR(9) PRIMARY KEY, Sname CHAR(20) UNIQUE, Ssex CHAR(2), Sage smallint, Sdept char(20), );3创建课程关系表C ：课程号课程名先行课学分CnoCnameCpnoccreditcreate table C( Cno char(4) primary key, Cname CHAR(40), Cpno char(4), Ccredit SMALLINT,);4创建课程关系表SC ：学号课程号成绩SnoCnogradecreate table SC( Sno char(9), Cno char(4), Gra

4、de smallint,);5将以上创建表S、C、SC的SQL命令以 .SQL文件的形式保存在磁盘上。6、在表S上增加“出生日期”属性列。alter table S add Sbirthday datetime; 7、删除表S的“年龄” 属性列。 alter table S drop column Sage;8、删除S列的唯一值约束。alter table S drop UQ_S_7D78A4E7;9、修改SC表成绩属性列为精确数字型。alter table SC alter column Grade int;10、在表S上，按“Sno” 属性列的唯一值方式建立索引。create unique

5、 index Stusno on S(Sno);11、删除表SC，利用磁盘上保存的.SQL文件重新创建表SC。drop table SC;12、创建教材P74页习题5中SPJ数据库中的四个关系：S、P、J、SPJ。实验二 数据更新实验实验二成绩： 一、实验目的1、 熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用2、 加深理解表的定义对数据更新的作用二、实验步骤及容（一）利用查询分析器在表中插入数据打开在实验一中建立的“学生课程”数据库；在“SCDB”数据库中的student、course、sc表中插入数据；student表的容：SNO SNAME SSEX SAGE SDEPT 95

6、001 勇 男 20 CS 95002 晨 女 19 IS 95003 王敏 女 18 MA course表的容CNO CNAME CPNO CREDIT 1 数据库 5 4 2 数学 NULL 2 3 信息系统 1 4 4 操作系统 NULL 3 5 数据处理 NULL 2 sc表的容SNO CNO GRADE95001 1 9295001 2 6595001 3 8895002 2 9095002 3 80（二）修改表中的数据在插入数据后的三表S、C、SC中进行相关的更新操作：1、将student表中勇的名字改为明；2、将student表中王敏的年龄改为20岁；3、将所有学生的年龄增加1岁

7、；4、修改学生的学号95002为95005，注意表之间的关系，保持一致性；5、把选修了“2”号课程，且成绩低于70分的成绩提高5%；6、将“CS”系全体学生的成绩置零；（三）删除表中的数据1、把选修了“2”号课程，且成绩低于70分的学生的成绩删除掉；2、删除学号为“95003”的相关信息；3、删除IS系选修了2号课程的选课记录；4、删除“CS”系的全体学生的选课记录；5、删除整表的数据，注意表之间的关系，保持一致性。三、实验结果：（一）利用查询分析器在表中插入数据打开在实验一中建立的“学生课程”数据库；在“SCDB”数据库中的student、course、sc表中插入数据；student表的容

8、：SNO SNAME SSEX SAGE SDEPT 95001 勇 男 20 CS 95002 晨 女 19 IS 95003 王敏 女 18 MA course表的容CNO CNAME CPNO CREDIT 1 数据库 5 4 2 数学 NULL 2 3 信息系统 1 4 4 操作系统 NULL 3 5 数据处理 NULL 2 sc表的容SNO CNO GRADE95001 1 9295001 2 6595001 3 8895002 2 9095002 3 80insertinto S(Sno,Sname,Ssex,Sage,Sdept)values(95001,勇,男,20,CS);i

9、nsertinto S(Sno,Sname,Ssex,Sage,Sdept)values(95002,晨,女,19,IS);insertinto S(Sno,Sname,Ssex,Sage,Sdept)values(95003,王敏,男,20,CS);select *from S;insertinto C(Cno,Cname,Cpno,Ccredit)values(1,数据库,5,4);insertinto C(Cno,Cname,Cpno,Ccredit)values(2,数学,NULL,2);insertinto C(Cno,Cname,Cpno,Ccredit)values(3,信息系统

10、,1,4);insertinto C(Cno,Cname,Cpno,Ccredit)values(4,操作系统,NULL,3);insertinto C(Cno,Cname,Cpno,Ccredit)values(5,数据处理,NULL,2); insertinto SC(Sno,Cno,Grade)values(95001,1,92);insertinto SC(Sno,Cno,Grade)values(95001,2,65);insertinto SC(Sno,Cno,Grade)values(95001,3,88);insertinto SC(Sno,Cno,Grade)values(9

11、5002,2,90);insertinto SC(Sno,Cno,Grade) values(95002,3,80); （二）修改表中的数据在插入数据后的三表S、C、SC中进行相关的更新操作：1、 将student表中勇的名字改为明；update Sset Sname=明where Sno=95001; 2、 将student表中王敏的年龄改为20岁；update Sset Sage=20 where Sname=王敏;3、 将所有学生的年龄增加1岁；update S set Sage=Sage+1;4、 修改学生的学号95002为95005，注意表之间的关系，保持一致性；update Sse

12、t Sno=95005where Sno=95002; 5、 把选修了“2”号课程，且成绩低于70分的成绩提高5%；update SCset Grade=Grade*1.05where Cno=2 and Grade70; 6、 将“CS”系全体学生的成绩置零；update SCset Grade=0where CS=(select Sdept from S where S.Sno=SC.Sno); （三）删除表中的数据1、把选修了“2”号课程，且成绩低于70分的学生的成绩删除掉；delete from SCwhere Cno=2 and Grade70; 2、删除学号为“95003”的相关信

13、息；delete from Swhere Sno=95003; 3、删除IS系选修了2号课程的选课记录；delete from SCwhere Cno=2and Sno in (select Sno from S where Sdept=IS); 4、删除“CS”系的全体学生的选课记录；delete from SCwhere CS=(select Sdept from S where S.Sno=SC.Sno); 5、删除整表的数据，注意表之间的关系，保持一致性。delete from SC; 实验三 数据查询实验实验三成绩： 一、实验目的熟悉和掌握对数据表中数据的查询操作和SQL命令的使用，

14、学会灵活熟练的使用SQL 语句的各种形式；加深理解关系运算的各种操作（尤其的关系的选择，投影，连接和除运算）二、实验方法及步骤在表student，course，sc上进行简单查询；在表student，course，sc上进行连接查询；在表student，course，sc上进行嵌套查询；使用聚合函数的查询；对数据的分组查询；对数据的排序查询。三、实验容在表student，course，sc上完成以下查询：1、查询学生的基本信息；2、查询“CS”系学生的基本信息；3、查询“CS”系学生年龄不在19到21之间的学生的学号、；4、找出最大年龄；5、找出“CS”系年龄最大的学生，显示其学号、；6、统计

15、“CS”系学生的人数；7、统计各系学生的人数，结果按升序排列；8、按系统计各系学生的平均年龄，结果按降序排列；9、查询每门课程的课程名；10、统计无先修课的课程的学分总数；11、查询选修了“1”或“2”号课程的学生学号和；12、查询选修了“1”和“2”号课程的学生学号和；13、查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、和成绩；14、查询每位学生选修了课程的学生信息（显示：学号，课程号，课程名，成绩）；15、查询没有选修课程的学生的基本信息；16、查询选修了3门以上课程的学生学号；17、查询选修课程成绩至少有一门在80分以上的学生学号；18、查询选修课程平均成绩在80分以上

16、的学生学号；19、（选做）统计每位学生选修课程的门数、学分及其平均成绩；20、（选做）找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列。四、实验结果：1、查询学生的基本信息；select *from S; 2、查询“CS”系学生的基本信息；select *from Swhere Sdept=CS; 3、查询“CS”系学生年龄不在19到21之间的学生的学号、；select Sno,Snamefrom Swhere sdept=CS AND Sage not between 19 and 21; 4、找出最大年龄；select max(Sage)from S; 5、找出“CS”

17、系年龄最大的学生，显示其学号、；select Sno,Snamefrom Swhere Sage= (select max(Sage) from S where Sdept=CS); 7、 统计“CS”系学生的人数；SELECT Sdept,count(*)from Swhere Sdept=CSGROUP BY Sdept; 8、 统计各系学生的人数，结果按升序排列；SELECT Sdept,COUNT(*)FROM Sgroup by sdept; 9、 按系统计各系学生的平均年龄，结果按降序排列；select Sdept,avg(Sage)from Sgroup by Sdept ord

18、er by avg(Sage) desc; 10、 查询每门课程的课程名；select Cnamefrom Cgroup by Cname; 10、统计无先修课的课程的学分总数； select sum(Ccredit)from Cwhere Cpno is null; 11、查询选修了“1”或“2”号课程的学生学号和；select S.Sno,Snamefrom S,SCWHERE S.Sno=SC.Sno AND (Cno=1 or Cno=2); 12、查询选修了“1”和“2”号课程的学生学号和；select S.Sno,Snamefrom SC,Swhere Cno=1 AND S.Sn

19、o=SC.Sno and SC.Sno IN (select Sno from SC where Cno=2); 13、查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、和成绩； select S.Sno,Sname,Gradefrom S,SC,Cwhere SC.Sno=S.Sno and C.Cno=SC.Cno and Cname=数据库 and Grade3; 17、查询选修课程成绩至少有一门在80分以上的学生学号；select distinct S.Snofrom S,SCWHERE S.Sno=SC.Sno and Grade80; 18、查询选修课程平均成绩在80

20、分以上的学生学号；select distinct S.Snofrom S,SCwhere SC.Sno=S.Sno and SC.Sno in (select Sno from SC GROUP BY Sno having avg(Grade)80); 19、（选做）SC20、（选做）找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列。实验四 视图的定义、使用实验实验四成绩： 一、实验目的熟悉和掌握对数据表中视图的定义操作和SQL命令的使用；熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的

21、区别与联系；学习灵活熟练的进行视图的操作，认识视图的作用；二、实验方法及步骤视图的定义视图的查询视图的修改三、实验容：以students，courses，sc表为基础完成以下视图定义（使用SQL和企业管理器两种方法）及使用1.定义“IS”系学生基本情况视图V_IS 并查询结果；2.将students，courses，sc表中学生的学号，课程号，课程名，成绩定义为视图V_S_C_G并查询结果;3.将各系学生人数，平均年龄定义为视图V_NUM_AVG并查询结果;4.定义一个反映学生出生年份的视图V_YEAR并查询结果;5.将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果;6

22、.建立视图V_AVG平均成绩为90分以上的学生学号；7.通过视图V_IS，分别将学号为“95001”和“95004”的学生更改为“S0001”,”S0004”并查询结果;8.通过视图V_IS，新增加一个学生记录 (S0012,YAN XI,19,IS)，并查询结果；9.通过视图V_IS，新增加一个学生记录 (S0013,YAN XI,19,MA)，并查询结果；10.通过视图V_IS，删除学号为“S0012”和“95003”的学生信息，并查询结果；四、实验结果以students，courses，sc表为基础完成以下视图定义（使用SQL和企业管理器两种方法）及使用1.定义“IS”系学生基本情况视图

23、V_IS 并查询结果；create view V_ISasselect *from S where Sdept=IS;2.将students，courses，sc表中学生的学号，课程号，课程名，成绩定义为视图V_S_C_G并查询结果;create view V_S_C_Gasselect S.Sno,Sname,C.Cno,Cname,Gradefrom S,SC,Cwhere S.Sno=SC.Sno and SC.Cno=C.Cno; 3.将各系学生人数，平均年龄定义为视图V_NUM_AVG并查询结果;create view V_NUM_AVG(Sdept,Sdept_count,Sage

24、_avg)asselect Sdept,count(Sdept),avg(Sage)from Sgroup by Sdept; 4.定义一个反映学生出生年份的视图V_YEAR并查询结果;create view V_YEAR(Sname,Sbirthday)asselect Sname,2013-Sagefrom S; 5.将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G并查询结果;create view V_AVG_S_G(Sno,Cno_count,Grade_avg)asselect Sno,count(Cno),avg(Grade)from SCgroup by Sno;

25、6.建立视图V_AVG平均成绩为90分以上的学生学号；create view V_AVG(Grade_over90_Sno)asselect S.Snofrom S,SCwhere SC.Sno=S.Sno and SC.Sno in (select Sno from SC group by Sno having avg(Grade)90);7.通过视图V_IS，分别将学号为“95001”和“95004”的学生更改为“S0001”,”S0004”并查询结果;update V_ISset Sname=王明where Sno=95001;update V_ISset Sname=王强where S

26、no=95002; 8.通过视图V_IS，新增加一个学生记录 (S0012,YAN XI,19,IS)，并查询结果；insertinto V_IS(Sno,Sname,Sage,Sdept)VALUES (95005,宋明,19,IS);9.通过视图V_IS，新增加一个学生记录 (S0013,YAN XI,19,MA)，并查询结果；insertinto V_IS(Sno,Sname,Sage,Sdept)VALUES (95006,王芳,19,MA); 10.通过视图V_IS，删除学号为“S0012”和“95003”的学生信息，并查询结果；deletefrom V_ISwhere Sno=95005 or Sno=95006;