数据库原理实验报告的整合。

简介：
《数据库原理及应用》课程实验实验1 创建数据库和数据表一、实验目的熟悉SQL Server Management Studio界面；掌握通过图形化向导和执行Transact-SQL语句创建数据库的方法。二、实验环境Windows7操作系统，SQL Server软件。三、实验课时2课时。四、实验内容SQL Server中的一个数据库必须至少包含一个数据文件和一个事务日志文件，所以创建数据库就是建立主数据文件和日志文件。在SQL Server中创建数据库的方法主要有两种：一是在SQL Server Management Studio窗口中使用可视化界面，通过方便的图形化向导创建，二是通过执行Transact-SQL语句创建。（一）向导方式创建1、从“开始”菜单选择“所有程序”→“Microsoft SQL Server ”，打开“SQL Server Management Studio”窗口，使用Windows或SQL Server身份验证建立连接。2、在“对象资源管理器”窗格中展开服务器，选择“数据库”节点右击，从弹出的快捷菜单中选择“新建数据库”命令，打开“新建数据库”窗口。3、该窗口中有3个页，分别是“常规”、“选项”和“文件组”，完成这3个页的内容即可完成数据库的创建。这里，我们仅设置“常规”的相应内容，其他2个页的内容按照默认设置即可。（1）“数据库名称”文本框中输入数据库的名称，如“student”，再输入该数据库的所有者，这里使用默认值即可。（也可以通过单击文本框右边的“浏览”按钮选择所有者。（2）“数据库文件”列表中包括两行，一个是数据文件，一个是日志文件。通过单击下面相应的按钮可以添加或删除相应的数据文件。 逻辑名称：指定该文件的文件名。 文件类型：用于区别当前文件是数据文件还是日志文件。 文件组：显示当前数据库文件所属的文件组。 初始大小：指定该文件的初始容量。在SQL Server 中数据文件的默认值为3MB，日志文件的默认值为1MB。 自动增长：用于设置在文件容量不够用时，文件根据何种增长方式自动增长。 路径：指定存放在文件的目录。默认情况下，SQL Server 将存放路径设置为其安装目录下的data子目录，单击该列中的按钮可以在打开的“定位文件夹”对话框中更改数据库的存储路径。完成上述操作后，单击“确定”按钮关闭“新建数据库”窗口，即完成了数据库的创建，可以在“对象资源管理器”窗格中看到新建的数据库。（二）语言方式创建SQL Server 使用的Transact-SQL语言是标准SQL的增强版本，使用它提供的Create Database语句同样可以完成对数据库的建立（参考内容见附录）。1、单击“新建查询”按钮，创建一个查询输入窗口将附录中的语句复制到该窗口。选择创建数据库的命令，单击工具栏中的“分析”按钮，可以检查语法错误；单击“执行”按钮即可执行语句，在查询窗口内的“查询”窗格中可以看到“命令已成功完成”的提示消息。在“对象资源管理器”窗格中刷新，即可看到新建的数据库。2、继续执行Create Database之后的语句，在“student”数据库中用Create Table命令依次建立S、C和SC这三个数据表，各表字段如下：STUDENT(SNO,SNAME,SEX,DOB,DEPT,TELNO) COURSE(CNO,CNAME,CPNO,TEACHER,CREDIT) SC(SNO,CNO,SCORE)表1：STUDENT表列名 类型 长度 约束 NULL PK FK CHECKsno char 10 N Y sname varchar 8 N sex Char 2 N ‘男’ or ‘女’DOB datetime dept varchar 10 telno char 11 N 表2：COURSE表列名 类型 长度 约束 NULL PK FK CHECKcno char 5 N Y cname varchar 10 N Cpno char 5 teacher varchar 8 N credit smallint 表3：SC表列名 类型 长度 约束 NULL PK FK CHECKsno char 10 N Y References s(sno) cno char 5 N References c(cno) score decimal(6,2) 0-100要求：当主表STUDENT中的主码SNO和COURSE中的主码CNO发生修改或删除时，SC表中的相应外码做CASCADE操作。（三）知识巩固用SQL-DDL语言创建“产品订购”数据库，包含四个数据表S、P、J和SPJ，表结构及内容如下（下划线为主码）：S（SNO，SNAME， ADDRESS，TEL）释义：供应商（供应商编号，姓名，地址，电话）J（JNO，JNAME，LEADER，BG）释义：项目（编号，项目名，负责人，预算）P（PNO，PNAME，SPEC，CITY，COLOR）释义：零件（编号，零件名，规格，生产地，颜色）SPJ（SNO，JNO，PNO，QTY）释义：供应（供应商编号，项目编号，零件编号，数量）其中，sno，jno，pno分别为对应S表，J表，P表的外码。S表列名 类型 长度 约束 NULL PK FK CHECKsno char 10 N Y sname varchar 8 N address char 10 N tel char 11 N J表列名 类型 长度 约束 NULL PK FK CHECKjno char 10 N Y jname varchar 8 N leader varchar 8 N bg int N P表列名 类型 长度 约束 NULL PK FK CHECKpno char 10 N Y pname varchar 8 N spec char 10 N city char 10 N color char 10 N SPJ表列名 类型 长度 约束 NULL PK FK CHECKsno char 10 N Y References s(sno) jno char 10 N References j(cno) pno char 10 N References p(pno) qty int 实验2 数据库的简单查询一、实验目的掌握单表查询中各个子句（Select、From、Where、Group、Having、Order）的用法，特别要掌握比较运算符和逻辑运算符的使用。二、实验环境Windows7操作系统，SQL Server软件。三、实验课时2课时。四、实验内容在已建立的学生数据库和数据表的基础上，用Select语句完成下列查询操作。STUDENT(SNO,SNAME,SEX,DOB,DEPT,TELNO) COURSE(CNO,CNAME,CPNO,TEACHER,CREDIT) SC(SNO,CNO,SCORE)1、 查询全体学生的学号和姓名。Select sno,snameFrom s2、 查询全体学生的所有基本信息（仅针对S表）。Select *From S3、 对SC表查询全体学生的选课记录，并对score列增加5分。Select sno,cno,score=score+5From SC4、 查询全体学生的学号和姓名，将原来的英文列名设置中文别名。Select sno,snamefrom5、 显示所有选课学生的学号，去掉重复结果。Select distinct snoFrom sc6、 查询成绩在80分以上的学生选课记录。Select *From scWhere score>807、 查询经济系所有学生的学号和姓名。Select sno,snameFrom S8、 查询成绩在80～90分的学生选课记录.9、 查询年龄不在22～25的学生记录。10、 查询所有姓“刘”的学生基本信息。11、 查询无考试成绩（成绩为空值）的学号、课程号。12、 查询考试成绩非空值的学号、课程号。13、 查询数学系年龄小于20岁的学生记录。14、 查询选修了‘101’或‘102’课程的选课记录。15、 查询学生总人数。16、 查询选修了课程的学生人数。17、 查询选修了‘101’课程的学生的平均成绩。18、 查询学号为‘01003’的学生的考试总成绩。19、 查询‘101’课程的最高分和最低分。20、 查询每门课程的选课人数。21、 查询每个学生的学号、选课数、平均成绩和总成绩。22、 查询选课数超过2门的学生学号及其选课数。23、 查询所有学生信息，查询结果按年龄降序排列。（针对S表操作）24、 查询所有学生信息，查询结果按系名升序，同系学生按年龄降序排列。25、查询选课数超过2门的学生学号及其选课数，查询结果按选课数降序排列。 实验3 数据库的多表查询一、实验目的继续熟练SQL-SELECT语句的使用；理解多表查询的概念；掌握多表连接查询中各个子句的用法，特别要比较内连接和外连接的结果。掌握非相关子查询、相关子查询的用法，理解它们的执行过程。二、实验环境Windows7操作系统，SQL Server软件。三、实验课时2课时。四、实验内容（一）、在已建立的STUDENT数据库和数据表的基础上，用Select语句的连接查询完成下列操作。1、查询每个学生的基本信息及其选课情况。select *from s,scwhere s.sno=sc.sno 2、查询选修‘a’课程的学生学号、姓名和成绩。select sno,sname,scorefrom s,scwhere cno=’a’and s.sno=sc.sno 3、查询与‘李四’在同一个系学习的学生记录。select from where 4、查询与‘李平’年龄相同的学生记录。select from where 5、查询选修课程名为“高数”的选课记录。select sno,scorefrom c,scwhere cname=’高数’and c.cno=sc.cno 6、查询选修课程名为“高数”的学生记录。select s.sno,snamefrom s,c,scwhere cname=高数and s.sno=sc.snoand c.cno=sc.cno 7、查询选修‘a’课程且成绩大于等于90的学号、姓名和成绩。select s.sno,sname,scorefrom s,scwhere cno=aand score>90and s.sno=sc.sno 8、查询‘吴十’的所有选课记录。select cnofrom s,scwhere sname=’吴十’and s.sno=sc.sno 9、查询计算机系学生选修的课程号，要求结果中去掉重复记录。select distinct cnofrom s,scwhere dept=’计算机’and s.sno=sc.sno 10、查询计算机系选修课程数大于等于2的学号、姓名及平均成绩，查询结果按平均成绩降序。select sno,sname,avg(score)平均成绩from s,scwhere scand s.sno=sc.snoorder by avg(score)11、查询每个学生的学号、选修课程号、课程名及成绩。select s.sno,c.cno,cname,scorefrom s,c,scwhere s.sno=sc.snoand c.cno=sc.cno 12、查询所有学生的选修情况（包括选课和未选课的学生），要求显示学号、姓名、课程号和成绩。select sno,sname,cno,scorefrom where （二）、在已建立的数据库和数据表的基础上，用Select语句的嵌套查询完成下列操作。1、查询选修‘a’课程且成绩大于等于90的学号、姓名。select sno,snamefrom swhere sno=(select snofrom scwhere cno=aand score>90) 2、查询‘李四’的所有选课记录。select cnofrom scwhere sno=(select snofrom swhere sname=’李四’) 3、查询与‘李平’在同一个系学习的学生记录。select from where 4、查询与‘李平’年龄相同的学生记录。select from where 5、查询选修课程名为‘高数’的选课记录，输出结果包括学号和成绩。select sno,scorefrom scwhere cno=(select cnofrom cwhere cname=’ 高数’) 6、查询选修课程名为‘高数’的学生记录，输出结果包括学号、姓名和所在系。select sno,sname,deptfrom swhere sno=(select snofrom scwhere cno=(select cnofrom cwhere cname=’高数’))7、查询学号为‘1’学生的选修课程号和课程名。select cno,cnamefrom cwhere sno=(select from where 8、查询没有选修‘101’课程的学生学号和姓名。select from where 9、查询选修‘101’课程或‘102’课程的学生姓名。select from where 10、查询选修‘101’课程和‘102’课程的学生学号。select from where 11、查询选修‘101’课程但没选修‘102’课程的学生学号。select from where 12、查询没有选修任何课程的学生记录，输出结果包括学号、姓名和所在系。select from where 13、查询数学系学生选修的课程号，要求结果中去掉重复记录。select from where 14、查询选修课程至少包含‘01003’选修课程的学生学号。select from where 实验4 数据库更新、视图定义及使用一、实验目的掌握Insert、Update、Delete语句的使用；对于Insert语句，要求理解默认值约束、空值约束在插入记录时所起的作用。理解视图的概念，掌握Create View、Drop View语句的使用；掌握基于视图的查询语句的使用。二、实验环境Windows7操作系统，SQL Server软件。三、实验课时2课时。四、实验内容（一）、在已建立的数据库和数据表的基础上，向数据库中添加一个临时表ST，其结构与S表结构相同。用Insert语句向临时表输入数据，输入有误时用Update语句进行修改。再用三个更新语句完成下列操作。1、向S表添加若干新记录，内容自定。（观察已定义的表的约束情况）2、向SC表添加新记录，内容自定，不低于10条（注意不能违反参照完整性）。3、为临时表ST添加记录。4、把S表的所有行一次性地加到临时表ST中。select * into ST from S; 5、在SC表中把所有学生的成绩加2分。update scset score=case when score+2>100then 100else isnull(score,0)+2end; 6、在ST表中把所有学生的年龄增加1。7、在ST表中把‘李四’的所在系改为‘计算机’。update stset dept=计算机where sname=李四8、在SC表中将选修课程‘高数’的学生成绩加2分。update scset score=score+2from sc,cwhere sc.cno=c.cno and cname=高数 9、在SC表中删除所有成绩为空值的选修记录。delete scwhere score is null 10、删除计算机系选修成绩不及格的选课记录。delete scfrom s,scwhere s.sno=sc.sno and score<60 and dept=计算机 （二）、在已建立的数据库和数据表的基础上，完成下列操作。1、建立数学系学生的视图MAST。create view MASTas select sno,sname,deptfrom swhere dept=数学2、建立计算机系选修课程名为‘高数’的学生视图，视图名：CSTVIEW，该视图中应包括属性列：学号、姓名、成绩。create view CSTVIEWas select s.sno,sname,scorefrom s,sc,cwhere s.sno=sc.sno andc.cno=sc.cno anddept=计算机 andcname=高数3、创建一个名为STSUMVIEW的视图，包括所有学生的学号和总成绩。create view STSUMVIEWas select s.sno,scorefrom s,scwhere s.sno=sc.sno ands.sno=sc.sno4、建立学生选课视图SCVIEW，包括所有学生的学号、姓名、课程号、课程名和成绩。create view SCVIEWas select s.sno,sname,c.cno,cname,scorefrom s,sc,cwhere s.sno=sc.sno andc.cno=sc.cno5、通过MAST视图查询学生基本信息。 6、通过SCVIEW查询成绩大于90分的学生的学号和成绩。select sno,scorefrom SCVIEWwhere score>90 7、查询计算机系选修课程名为“高数”并且成绩大于70分的学生的学号和成绩。select sno,scorefrom SCVIEWwhere cname=高数 and score>70 8、通过MAST视图将学号为‘01008’学生的年龄修改为21岁。9、通过MAST视图将学号为‘2’学生所在系改为‘经济’，是否能成功执行？若不能成功请说明理由；若修改成功，请再次通过MAST视图查询学生基本信息，是否能查询到该生？若不能查询请说明理由。能成功执行以下语句：update MASTset dept=经济where sno=2不能查到该生，因为在MAST视图下他的条件已经不符合系名为数学。10、通过SCVIEW视图将学号为‘1’学生的总成绩修改为380分，是否能成功执行？若不能成功请说明理由。 实验5 数据库的安全与保护一、实验目的理解SQL Server的用户与权限管理机制，掌握用对象资源管理器创建服务器登录帐号，并同时建立用户。掌握如何为给定的用户分配权限。二、实验环境Windows7操作系统，SQL Server软件。三、实验课时2课时。四、实验内容用户与权限管理（1）打开“SQL Server Management Studio”窗口，使用对象资源管理器创建两个服务器登录帐号Test1和Test2，并在“sample_st”数据库中创建两个对应的同名用户。设置Test1用户和Test2用户的默认架构为guest。（2）使用Test1身份登录，并为Test2分配创建数据表的权限；用Test2身份登录来验证权限分配成功。（3）使用Test1身份登录，收回刚刚分配给Test2的创建数据表的权限；用Test2身份登录来验证权限回收成功。五、实验步骤1、使用对象资源管理器创建两个服务器登录帐号。以系统管理员身份登录SQL Server。打开“SQL Server Management Studio”窗口，在对象资源管理器列表中，打开展开“安全性”文件夹，选择“登录名”图标，单击鼠标右键，在弹出的菜单中选择“新建登录名”。在打开的对话框中依次建立Test1和Test2，选择“SQL Server身份验证”，同时还要输入密码，默认数据库为“sample_st”。（2）在“用户映射”中列出了当前登录帐号可以选择访问的数据库如“sample_st”，在其左侧的复选框中打勾，表示当前登录帐号可以访问对应的数据库，默认用户名与登录帐号相同。（3）单击“确定”按钮完成创建。2、创建新的数据库用户。在对象资源管理器中选中要访问操作的数据库，展开“安全性”文件夹，在“用户”文件夹中查找是否已建立与登录名Test1和Test2同名的数据库用户，若已建立，则在属性中修改其默认架构为guest；否则右击鼠标在菜单中选择“新建用户”选项，出现“数据库用户-新建”对话框，建立相应的用户。3、用Grant、Revoke命令实现对用户的授权和收权。4、用Create命令创建数据表验证授权和收权是否成功。5、以系统管理员身份登录SQL Server。使用：“GRANT SELECT ON SC TO Test1 WITH GRANT OPTION”命令，再分别以Test1和Test2身份登录，进行授权及查询数据表，验证“WITH GRANT OPTION”能否成功执行。6、以系统管理员身份使用“REVOKE”命令回收上述授权。