《数据库原理及应用》实验指导书

《数据库原理及应用》实验指导书实验1数据库模式设计及建立一、实验目的掌握数据库模式设计，依据实际要求设计表结构，建立表的关系；比较SQL命令方式和可视化环境管理器方式设计的异同点。二、实验内容1.用sql查询分析器设计学生基本信息表student、课程表course和选课表sc，各表中字段及数据分别如表1、表2及表3所示。表1Student表学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept200215121200215122200215123200515125李勇刘晨王敏张立男女女男20191819计算机系计算机系数学系信息系表2Course表课程号Cno课程名Cname先行课Cpno学分CcreditC1C2C3C4C5C6C7数据库高等数学信息系统操作系统数据结构数据处理PASCAL语言C5C1C6C7C64243424表3SC表学号Sno课程号Cno成绩Grade200215121200215121200215121200215122200215122200215122C1C2C3C1C2C39285888090802.用企业管理器设计学生基本信息表student、课程表course和选课表sc。三、操作步骤1.熟悉SQLSERVER的企业管理器和事件探查器2.创建学生选课数据库3.建立新表的SQL语句；注意约束条件及表间关系4.启动“查询分析器”。5.连接SQLSERVER；选择数据库6.在“查询分析器”中输入已编写好的SQL命令7.执行SQL命令8.启动企业管理器9.在控制台中选择先前建立的数据库10.建立学生基本信息表student、课程表course和选课表sc11.设定表关系实验2数据库的简单查询和连接查询实验一、实验目的掌握SQLServer查询分析器的使用方法，加深对SQL和SQL语言的查询语句的理解。熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。二、实验内容1．简单查询操作。该实验包括投影、选择条件表达，数据排序，使用临时表等。具体完成以下题目，将它们转换为SQL语句表示，在学生选课数据库中实现其数据查询操作。1)查询数学系学生的学号和姓名2)查询选修了课程的学生学号提示：selectdistinctsnofromsc;3)查询选修课程号为‘C1’的学生号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。4)查询选修课程号为‘C1’且成绩在80~90之间的学生学号和成绩，并成绩乘以0.8输出。5)查询数学系或计算机系姓张的学生的信息。6)查询缺少了成绩的学生的学号和课程号2．连接查询操作。该实验包括等值连接、自然连接、求笛卡儿积、一般连接、外连接、内连接、左连接、右连接和自连接等。1)查询每个学生的情况以及他所选修的课程2)查询学生的学号、姓名、选修的课程及成绩3)查询选修课程号为‘C1’且成绩在90以上的学生学号、姓名和成绩4)查询每一门课程的间接先行课（即先行课的先行课）提示：SelectFirst.Cno,Second.CpnoFromCourseFirst,CourseSecondWhereFirst.Cpno=Second.Cno;三、实验方法将查询需求用SQL语言表示；在SQLServer查询分析器的输入区中输入SQL查询语句；设置查询分析器的结果区为StandardExecute(标准执行)或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。实验3数据库的嵌套查询实验一、实验目的进一步掌握SQLServer查询分析器的使用方法，加深SQL语言的嵌套查询语句的理解。二、实验内容在SQLServer查询分析器中使用IN、比较符、ANY或ALL和EXISTS操作符进行嵌套查询操作。具体完成以下例题。将它们用SQL语句表示，在学生选课中实现其数据嵌套查询操作。例1查询选修了高等数学的学号和姓名例2查询C1课程的成绩高于刘晨的学生学号和成绩提示：selectsno,gradefromscwheregrade(selectgradefromsc,studentwheresc.sno=student.snoandsc.cno='C1'andstudent.sname='刘晨')andCno='C1';例3查询其他系中比计算机系某一学生年龄小的学生（即年龄小于计算机系年龄最大者的学生）select*fromstudentwheresage(selectmax(sage)fromstudentwheresdept='计算机系');andsdept'计算机系'例4查询其他系中比计算机系学生年龄都小的学生select*fromstudentwheresageall(selectsagefromstudentwheresdept='计算机系')andsdept'计算机系';例5查询选修了C2课程的学生姓名selectsnamefromstudentwhereexists(select*fromscwheresno=student.snoandcno='C1');例6查询没有选修C2课程的学生姓名selectsnamefromstudentwherenotexists(select*fromscwheresno=student.snoandcno='C1');例7查询至少选修了学号为“200215122”的学生所选修全部课程的学生学号和姓名selectsno,snamefromstudentwheresnoin(selectsnofromscscxwherenotexists(select*fromscscywherescy.sno='200215122'andnotexists(select*fromscsczwherescz.sno=scx.snoandscz.sno=scy.sno)));三、实验方法将查询需求用SQL语言表示：在SQLServer查询分析器的输入区中输入SQL查询语句：设置查询分析器的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。实验4数据库的集合查询和统计查询实验一、实验目的熟练掌握SQLServer查询分析器的使用方法，加深SQL语言查询语句的理解。熟练掌握数据查询中的分组、统计、计算和集合的操作方法。二、相关知识(1)分组和SQL函数查询SQL函数是综合信息的统计函数，包括计数、求最大值、最小值、平均值、和值等，较详细的SQL函数及功能见第三章3.6节（SQL查询用的函数）。SQL函数可作为列标识符出现在SELECT子句的目标列或HAVING子句的条件中。在SQL查询语句中，如果有GROUPBY分组子句，则语句中的函数为分组统计函数；如果没有GROUPBY分组子句，则语句中的函数为全部结果集的统计函数。表4基本的SQL函数函数功能AVG（数值表达式）求与字段相关的数值表达式的平均值SUM（数值表达式）求与字段相关的数值表达式的和值MIN（字段表达式）求字段表达式的最小值MAX（字段表达式）求字段表达式的最大值COUNT（*|字段）求记录行数(*)，或求不是NULL的字段的行数(2)集合查询每一个SELECT语句都能获得一条或一组元组。若要把多个SELECT语句的结果合并为一个结果，可用集合来完成。集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。标准SQL语言中提供了并操作UNION，没有直接提供交操作INTERSECT和差操作MINUS，但可以用其他方法来实现，具体实现方法依查询不同而不同。三、实验内容本实验的主要内容是：a)分组查询实验。该实验包括分组条件表达、选择组条件表达的方法。b)使用函数查询的实验。该实验包括统计函数和分组统计函数的使用方法。c)集合查询实验。该实验并操作UNION、交操作INTERSECT和差操作MINUS的实现方法。具体完成以下例题，将它们用SQL语句表示，在学生选课中实现其数据查询操作。例1查询学生的总人数例2查询选修了课程的学生人数例3查询课程和选修了该课程的学生人数例4查询选修超过3门课的学生学号提示：selectsno,count(cno)as选修门数fromscgroupbysnohavingcount(cno)=3;例5查询计算机系的学生及年龄不大于19岁的学生提示：UNION例6查询计算机系的学生与年龄不大于19岁的学生的交集例7查询计算机系的学生与年龄不大于19岁的学生的差集例8查询选修课程‘C1’的学生集合与选修课程‘C2’的学生集合的交集提示：selectsnofromscwherecno='C1'andsnoin(selectsnofromscwherecno='C2');例9查询选修课程‘C1’的学生集合与选修课程‘C2’的学生集合的差集提示：selectsnofromscwherecno='C1'andsnonotin(selectsnofromscwherecno='C2');四、实验步骤将查询需求用SQL语言表示：在SQLServer查询分析器的输入区中输入SQL查询语句：设置查询分析器的结果区为StandardExecute（标准执行）或ExecutetoGrid（网格执行）方式；发布执行命令，并在结果区中查看查询结果；如果结果不正确，要进行修改，直到正确为止。实验5数据库的更新实验一、实验目的熟练掌握使用SQL和通过SQLServer企业管理器向数据库输入数据、修改数据和删除数据的操作。二、相关知识SQLServer提供了很多方法更新表中数据。以插入记录为例，可以使用INSERT语句、VALUES子句将特定值集合插入一行；可以使用数据库接口API（ADO、OLEDB、ODBC和DB-Library）中的函数进行数据插入等。本实验主要掌握INSERT、UPDATE和DELETE语句的基本用法。1.插入操作，其语句的一般格式为INSERTINTOtable_or_view[(colum_list)]VALUES(date_values);此语句是使date_values作为一行数据记录插入已命名的表或视图table_or_view中。Column_list是由逗号分隔的table_or_view的列名列表，用来指定为其提供数据的列。如果没有指定column_list，表或视图中的所有列都将接收数据。如果column_list没有为表或视图中的所有列命名，将在列表中没有命名的任何列中插入一个NULL值（或者在默认情况下为这些列定义的默认值）。在列的列表中没有指定的所有列都必须允许NULL值或者指定的默认值。否则SQLServer将返回一个错误。2.修改操作语句的一般格式为UPDATE表名SET列名=表达式[,列名=表达式]……[WHERE条件];其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句用于指定修改方法，即用表达式的值取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。3.删除语句的一般格式为DELETEFROM表名[WHERE条件];其功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句，则表示要删除表中的所有元组，但表的定义仍在字典中。三、实验内容1.通过企业管理器，在学生选课数据库的学生、课程和选课3个表中各输入10条记录。要求记录不仅满足数据约束要求，还要有表间关联的记录。2.具体完成以下例题。将它们转换为SQL语句表示，在学生选课库中实现其数据更新操作。例1将一新学生记录(学号：20051501；姓名：陈冬；性别：男；年龄：18；所在系：计算机系)插入学生表中。例2插入一条选课记录(‘20051501’,’C1’)。例3对每一个系，求学生的平均年龄，并把结果存入数据库。对于这道题，首先要