sql_server_数据库实验指导书

数据库原理及应用实验指导书目录实验一SQLServer2005安装和环境介绍.3实验二使用向导创建和删除数据库.14实验三使用SQL语句创建和删除数据库.19实验四用向导和SQL语句创建和删除表.23实验五修改数据库基本表的定义.28实验六创建和删除索引.34实验七简单SELECT语句.37实验八子查询.40实验九使用子句的SELECT语句.47实验十创建视图.55实验十一使用视图.61实验十二更新语句.65实验十三流控制语句.68实验十四使用游标.70实验十五数据库约束实验.72实验十六数据库设计.812数据库原理及应用实验指导书实验一SQLServer2005安装和环境介绍一实验目的与要求：1掌握SQLServer2005服务器的安装方法2了解SQLServer2005的环境3了解数据库及其对象二实验准备1了解SQLServer2005的版本2了解SQLServer2005各版本对硬件和软件的需求三实验要求1写出SQLSERVER2005的功能与特点,使用方法,应用状况的报告2完成SQLSERVER2005的安装、启动、登录。四实验内容安装SQLServer2005五实验指导实验指导安装SQLServer20051根据安装机器软硬件的要求，选择一个合适的版本，以下以开发版为例2将SQLServer2005DVD插入DVD驱动器。如果DVD驱动器的自动运行功能无法启动安装程序无法启动安装程序，请导航到DVD的根目录然后启动splash.hta。3在自动运行的对话框中，单击“运行SQLServer安装向导”。4在“最终用户许可协议”页上，阅读许可协议，再选中相应的复选框以接受许可条款和条件。接受许可协议后即可激活“下一步”按钮。若要继续，请单击“下一步”若要结束安装程序，请单击“取消”。如下图：3数据库原理及应用实验指导书5在“SQLServer组件更新”页上，安装程序将安装SQLServer2005的必需软件。有关组件要求的详细信息，请单击该页底部的“帮助”按钮。若要开始执行组件更新，请单击“安装”。更新完成之后若要继续，请单击“完成”。4数据库原理及应用实验指导书6在SQLServer安装向导的“欢迎”页上，单击“下一步”以继续安装。7在“系统配置检查(SCC)”页上，将扫描安装计算机，以检查是否存在可能妨碍安装程序的条件。5数据库原理及应用实验指导书8在“注册信息”页上的“姓名”和“公司”文本框中，输入相应的信息。若要继续，请单击“下一步”9在“要安装的组件”页上，请选择要安装的组件。选择各个组件组时，“要安装的组件”窗格中会显示相应的说明。您可以选中任意一些复选框。建议全选。若要安装单个组件，请单击“高级”。否则，请单击“下一步”继续。6数据库原理及应用实验指导书10在“实例名”页上，请为安装的软件选择默认实例或已命名的实例。计算机上必须没有默认实例，才可以安装新的默认实例。若要安装新的命名实例，请单击“命名实例”，然后在提供的空白处键入一个唯一的实例名。7数据库原理及应用实验指导书11在“服务帐户”页上，为SQLServer服务帐户指定用户名、密码和域名。您可以对所有服务使用一个帐户。12在“身份验证模式”页上，选择要用于SQLServer安装的身份验证模式。如果选择Windows身份验证，安装程序会创建一个sa帐户，该帐户在默认情况下是被禁用的。选择“混合模式身份验证”时，请输入并确认系统管理员(sa)登录名。建议选择混合模式，并输入安全的密码。8数据库原理及应用实验指导书13如果选择ReportingServices作为要安装的功能，将显示“报表服务器安装选项”页。使用单选按钮选择是否使用默认值配置报表服务器。如果没有满足在默认配置中安装ReportingServices的要求，则必须选择“安装但不配置服务器”安装选项。若要继续安装，请单击“下一步”9数据库原理及应用实验指导书14在“错误报告”页上，可以清除复选框以禁用错误报告。有关错误报告功能的详细信息，请单击该页底部的“帮助”。若要继续安装，请单击“下一步”。15在“准备安装”页上，查看要安装的SQLServer功能和组件的摘要。若要继续安装，请单击“安装”。10数据库原理及应用实验指导书16在“安装进度”页上，可以在安装过程中监视安装进度。若要在安装期间查看某个组件的日志文件，请单击“安装进度”页上的产品或状态名称。17在“完成MicrosoftSQLServer安装向导”页上，可以通过单击此页上提供的链接查看安装摘要日志。若要退出SQLServer安装向导，请单击“完成”。18如果提示您重新启动计算机，请立即重新启动。11数据库原理及应用实验指导书19如果成功安装了SQLServer2005，则在开始菜单中添加了如下程序和相应的服务：（2）SQLServer的环境介绍1.AnalysisServices提供“部署向导”，为用户提供将某个AnalysisServices项目的输出部署到某个目标服务器的功能。2.配置工具其子菜单中提供的配置管理器“SQLServerConfigulationmanager”用于查看和配置SQLServer的服务。以下是SQLServer2005系统的7个服务。12数据库原理及应用实验指导书右击某个服务名称，可以查看该服务的属性，并且可以启动、停止、暂停和重新启动相应的服务。也可以使用操作系统“我的电脑”“管理”选项，在【计算机管理】窗口中查看和启动、停止、暂停和重新启动相应的服务。3.文档和教程提供了SQLServer2005的联机帮助和示例数据库概述。4.性能工具子菜单提供了“SQLServerProfiler”和“数据库引擎优化顾问”用户数据库性能调试和优化工具。5.SQLServerBusinessIntelligenceDevelopmentStudio商务智能（BI）系统开发人员设计的集成开发环境，构建于VisualStudio2005技术之上，为商业智能系统开发人员提供了一个丰富、完整的专业开发平台，支持商业智能平台上的所有组件的调试、源代码控制以及脚本和代码的开发。6.SQLServerManagementStudio它将SQLServer早期版本中包含的企业管理器、查询分析器和分析管理器的功能组合到单一环境中，为不同层次的开发人员和管理员提供SQLServer访问能力。13数据库原理及应用实验指导书实验二使用向导创建和删除数据库一实验目的1.熟悉SQLServer2005中SQLServerManagementStudio的环境2.了解SQLServer2005数据库的逻辑结构和物理结构3.掌握使用向导创建和删除数据库的方法二实验准备1装有SQLServer2005的PC机。2.明确能够创建数据库的用户必须是系统管理员，或是被授权使用CREATEDATABASE语句的用户。三实验要求1.熟练使用企业管理器进行数据库的创建和删除操作2.完成用向导建立和删除数据库的实验报告四实验内容设有一学籍管理系统，其数据库名为“EDUC”，初始大小为10MB，最大为50MB，数据库自动增长，增长方式是按5比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名为“student_data”,物理文件名为“student_data.mdf，存放路径为“E:sql_data”。日志文件的逻辑文件名为“student_log”,物理文件名为“student_log.ldf”，存放路径为“E:sql_data”。(1)使用向导创建上诉描述的数据库。使用SQLServerManagementStudio（简称SSMS）创建数据库。(2)使用向导删除上面建立的数据库。用SSMS删除添加建立的数据库EDUC1。五实验指导实验指导使用向导创建和删除数据库(1)使用向导创建上诉描述的数据库。14数据库原理及应用实验指导书使用SQLServerManagementStudio（简称SSMS）创建数据库。1启动SSMS在开始菜单中：所有程序SQLServer2005SQLServerManagementStudio单击“连接”按钮，便可以进入【SQLServerManagementStudio】窗口。如果身份验证选择的是“混合模式”，则要输入sa的密码。15数据库原理及应用实验指导书2建立数据库在“对象资源管理器”窗口，建立上述数据库EDUC。在数据库节点上右击选择新建。同时建立一个同样属性的数据库EDUC1。3.修改数据库名，数据库逻辑名，初始大小，增长方式等16数据库原理及应用实验指导书17数据库原理及应用实验指导书（2）使用向导删除上面建立的数据库。用SSMS删除添加建立的数据库EDUC1。18数据库原理及应用实验指导书实验三使用SQL语句创建和删除数据库一.实验目的：1了解SQLServer2005数据库的逻辑结构和物理结构。2掌握使用SQL语句创建和删除数据库。二.实验准备1明确能够创建数据库的用户必须是系统管理员，或是被授权使用CREATEDATABASE语句的用户。2了解用SQL语句创建和删除数据库的基本语法。三.实验要求3熟练使用查询分析器进行数据库的创建和删除操作4完成用sql语句建立和删除数据库的实验报告四.实验内容1以下是创建数据库userdb1的SQL语句，createdatabaseuserdb1on(name=userdb4_data,/*数据文件的逻辑名称,注意不能与日志逻辑同名*/filename=d:sql_datauserdb4.mdf,/*物理名称，注意路径必须存在*/size=5,/*数据初始长度为M*/maxsize=10,/*最大长度为M*/filegrowth=1)/*数据文件每次增长M*/logon(name=userdb4_log,filename=d:sql_datauserdb4.ldf,19数据库原理及应用实验指导书size=2,maxsize=5,filegrowth=1)go运行上诉语句建立数据库userdb12.根据步骤1的sql语句，写出创建实验二中数据库EDUC的sql语句，并建立数据库EDUC.3.用SQL语句删除步骤一建立的数据库userdb1。五.实验指导实验指导使用SQL语句创建和删除数据库1.使用向导创建上诉描述的数据库。使用SQLServerManagementStudio（简称SSMS）创建数据库。步骤1:启动SSMS在开始菜单中：所有程序SQLServer2005SQLServerManagementStudio步骤2：单击“连接”按钮，便可以进入【SQLServerManagementStudio】20数据库原理及应用实验指导书窗口。如果身份验证选择的是“混合模式”，则要输入sa的密码。步骤3:选“视图”-“模板资源管理器”-Database-createdatabase,双击database步骤4：将复制代码到窗口，并把“”换成数据库名“userdb1”，然后单击“！执行”，右键点击“对象资源管理器”，单击“刷新”。2.参照1根据1的sql语句，写出创建实验二中数据库EDUC的sql语句，并建立数据库EDUC.3.用SQL语句删除步骤一建立的数据库userdb1。步骤：新建查询输入“dropdatabaseuserdb1”-执行22度允许空值主键说明数据库原理及应用实验指导书实验四用向导和SQL语句创建和删除表一实验目的1了解表的结构特点。2了解SQLServer的基本数据类型。3学会在企业管理器中创建表。4学会使用T-SQL语句创建表。二实验要求1.熟练使用企业管理器创建和删除基本表。2.是用查询分析器，完成用SQL语句创建和删除基本表。3.完成用企业管理器和SQL创建和删除基本表的实验报告。三.实验准备1.确定数据库包含包含的各表的结构，还要了解SQLServer的常用数据类型，以创建数据库的表。2.已完成实验三，成功创建了数据库EDUC。3了解常用的创建表的方法。四.实验内容1.在实验三建立的数据库EDUC中，根据分析需要如下几个表：表2.1student表（学生信息表）字段名称类型宽snochar8NOTNULL是学生学号snamechar8NOTNULL学生姓名sexchar2NULL学生性别nativechar20NULL籍贯birthdaysmalldate4NULL学生出生日期dnochar6NULL学生所在院系spnochar8NULL专业代码（外键）classnochar4NULL班级号entimesmalldate4NULL学生入校时间23数据库原理及应用实验指导书homevarchar40NULL学生家庭住址telvarchar40NULL学生联系电话表2.2course表（课程信息表）字段名称类型宽允许空值主键说明度cnochar10NOTNULL是课程编号spnochar8NULL专业代码（外键）cnamechar20NOTNULL课程名称ctnotinyint1NULL课程类型编号（外键）experimenttinyint1NULL实验时数lecturetinyint1NULL授课学时semestertinyint1NULL开课学期credittinyint1NULL课程学分表2.3student_course表（学生选课成绩表）字段名称类型宽度允许空值主键说明snochar8NOTNULL是学生学号tcidsmallint2NOTNULL是上课编号scoretinyint1NULL学生成绩表2.4teacher表（教师信息表）字段名称类型宽度允许空值主键说明tnochar8NOTNULL是教师编号tnamechar8NOTNULL教师姓名sexchar2NULL教师性别birthdaysmalldate4NULL教师出生日期dnochar6NULL教师所在院系pnotinyint1NULL教师职务或职称编号homevarchar40NULL教师家庭住址zipcodechar6NULL邮政编码telvarchar40NULL联系电话emailvarchar40NULL电子邮件表2.5teacher_course表（教师上课课表）字段名称类型宽度允许空值主键说明tcidsmallint2NOTNULL是上课编号tnochar8NULL教师编号（外键）24数据库原理及应用实验指导书spnochar8NULL专业代码（外键）classnochar4NULL班级号cnochar10NOTNULL课程编号（外键）semesterchar6NULL学期schoolyearchar10NULL学年classtimevarchar40NULL上课时间classroomvarchar40NULL上课地点weektimetinyint1NULL每周课时数2.根据如上表结构用向导创建各表。3.用企业管理器删除所建立的表Student_course,Student和Course4.在查询分析器中用sql语句删除表Tearch_course和表Teacher5.根据如上表结构用SQL语句创建各表。6.将以上创建表的SQL命令以.SQL文件的形式保存在磁盘上五.实验指导实验指导分别用向导和SQL语句创建和删除表1,2.在实验三建立的数据库EDUC中，根据分析需要建立几个表步骤：右键单击EDUC的子目录“表”，选择“新建表”，在新表中输入“字段名称”，选择“类型”与“宽度”，并设置“sno“为主键，右键单击“sno”,单击“设置主键”，关闭，保存表名为“student”25数据库原理及应用实验指导书同理可建立表student_course，teacher，teacher_course。3.根据如上表结构用SQL语句创建各表。创建student表代码如下createtablestudent(snochar(8)NOTNULL,snamechar(8)NOTNULL,sexchar(2)NULL,birthdaysmalldatetimeNULL,dnochar(6)NULL,spnochar(4)NULL,clssnochar(4)NULL,entimesmalldatetimeNULL,homevarchar(40)NULL,telvarchar(40)NULL,constraintpk_studentprimarykey(sno)26数据库原理及应用实验指导书)4.询分析器中用sql语句删除表Tearch_course和表Teacher代码：droptableeduc.dbo.studentdroptableeduc.dbo.teacher_course5.将以上创建表的SQL命令以.SQL文件的形式保存在磁盘上3.右键单击所要删除的表，选择“删除”4.在查询分析器中用sql语句删除表Tearch_course和表Teacher5根据如上表结构用SQL语句创建各表。6.将以上创建表的SQL命令以.SQL文件的形式保存在磁盘上注：46的方法已经在上次实验中介绍27数据库原理及应用实验指导书实验五修改数据库基本表的定义一实验目的1了解表的结构特点。2学会在企业管理器中修改表的定义。3学会使用SQL语句修改表的定义。二实验准备1.成功完成实验四，在EDUC数据库中已经成功建立了各表。2.了解常用的修改表定义语句。三实验要求1.完成在已经创建成功的表上修改表定义的操作2.写出实验报告。四实验内容1修改列属性（1）用企业管理器将Student表中的birthday字段设为不能为空（notnull）。（2）用SQL语句将Student表中的属性snochar(8)改成varchar(20)类型。28数据库原理及应用实验指导书2添加列（1）用企业管理器在Course表中添加一列year，类型为varchar（4），默认置为空。（2）用SQL语句在year字段添加约束，year的属性值在2004-2008之间。3删除列（1）用SQL语句将Course表中的year字段删除。五实验指导实验指导修改数据库基本表的定义1.修改列属性步骤1：用企业管理器将Student表中的birthday字段设为不能为空（notnull）。方法：右键点击表student，单击“修改”，将birthday属性的“允许空”修改为“否”步骤2：由于sno为主键，要用SQL语句将Student表中的属性snochar(8)29数据库原理及应用实验指导书改成varchar(20)类型，则需要现删除主键，修改后在添加主键代码：USEEDUCGOaltertablestudentdropconstraintPK_studentaltertablestudentaltercolumnsnovarchar(20)notnullaltertablestudentaddconstraintPK_studentprimarykey(sno)2.添加列（1）步骤：展开course子目录，右键点击-新建列，加入year和类型30数据库原理及应用实验指导书（2）步骤：新建查询，在查询编辑窗口输入：UseeducALTERTABLEcourseADDCONSTRAINTyearCHECK(yearBETWEEN2004AND2008)然后执行。31数据库原理及应用实验指导书3.删除列步骤：新建查询，在查询编辑窗口输入USEEDUCGOALTERTABLEdbo.courseDROPCONSTRAINTyear/*去掉外键约束*/altertablecoursedropcolumnyear32数据库原理及应用实验指导书33数据库原理及应用实验指导书实验六创建和删除索引一实验目的1了解索引的类型和应用。2学会用SQL语句对表创建和删除索引二实验准备1.完成实验四，成功创建数据库EDUC中各表2.了解创建和删除索引的方法。三实验要求1.了解索引类型并比较各类索引的不同之处3.完成索引的创建和删除，并提交实验报告。四实验内容1.分别建立以下索引(如果不能成功建立,请分析原因)(1)在student表的sname列上建立普通降序索引。(2)在course表的cname列上建立唯一索引。(3)在student_course表的sno列上建立聚集索引。(4)在student_course表的sno(升序),tid(升序)和score(降序)三列上建立一个普通索引。2.删除索引将student_course表的sno列上的聚集索引删掉。五实验指导实验指导创建和删除索引1.分别建立以下索引(如果不能成功建立,请分析原因)(1)在student表的sname列上建立普通降序索引。步骤1：选择表student的子目录下“索引”右键单击新建索引34数据库原理及应用实验指导书步骤2：点击“添加”选择sname步骤3：选择“降序”，单击“确定”35数据库原理及应用实验指导书(2)在course表的cname列上建立唯一索引。(3)在student_course表的sno列上建立聚集索引。(4)在student_course表的sno(升序),tid(升序)和score(降序)三列上建立一个普通索引。2.删除索引将student_course表的sno列上的聚集索引删掉。36数据库原理及应用实验指导书实验七简单SELECT语句一实验目的1.观察查询结果,体会SELECT语句实际应用；2.要求学生能够在查询分析器中使用SELECT语句进行简单查询。3.熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。二实验准备1.完成实验四，成功建立了基本表。2.了解简单SELECT语句的用法。3.比较熟悉查询分析器中的SQL脚本运行环境。三实验要求1.完成简单查询和连接查询操作，并验收实验结果提交实验报告四实验内容所有的查询全部用Transact-SQL语句实现1.简单查询操作此部分查询包括投影、选择条件表达、数据排序、使用临时表等。对EDUC数据库实现以下查询：求计算机系的学生学号和姓名；求选修了课程的学生学号；求选修C1课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列；求选修课程C1且成绩在8090之间的学生学号和成绩，并将成绩乘以系数0.75输出；求计算机系和数学系的姓张的学生的信息；求缺少了成绩的学生的学号和课程号。将2000以后的成绩大于90分的学生成绩存入永久成绩表；将200037数据库原理及应用实验指导书年以前的成绩存入临时成绩表中。2.连接查询操作对EDUC数据库实现以下查询：查询每个学生的情况以及他（她）所选修的课程；求学生的学号、姓名、选修的课程名及成绩；求选修C1课程且成绩在90分以上的学生学号、姓名及成绩；查询每一门课的间接先行课。五实验指导实验指导简单SELECT语句1.简单查询操作此部分查询包括投影、选择条件表达、数据排序、使用临时表等。对EDUC数据库实现以下查询：求计算机系的学生学号和姓名；代码：SELECTsno，snameFROMstudentWHEREdno=计算机求选修了课程的学生学号；代码：SELECTDISTINCTsnoFROMstudent_course求选修C1课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列；求选修课程C1且成绩在8090之间的学生学号和成绩，并将成绩乘以系数0.75输出；求计算机系和数学系的姓张的学生的信息；求缺少了成绩的学生的学号和课程号。将2000以后的成绩大于90分的学生成绩存入永久成绩表；将200038数据库原理及应用实验指导书年以前的成绩存入临时成绩表中。2.连接查询操作对EDUC数据库实现以下查询：查询每个学生的情况以及他（她）所选修的课程；求生的学号、姓名、选修的课程名及成绩；选修C1课程且成绩在90分以上的学生学号、姓名及成绩；询每一门课的间接先行课。39数据库原理及应用实验指导书实验八子查询一实验目的1掌握子查询的表示。2进一步掌握SQLServer查询分析器的使用方法，加深对SQL语言的嵌套查询语句的理解二实验准备1掌握SQL语句的基本语法，并熟悉查询分析器的工作环境。2完成了实验四，创建了数据库EDUC及各个基本表。3了解子查询的表示方法，熟悉IN比较符、ANY、EXISTS操作符的用法。三实验要求1.在实验开始之前做好准备工作。2.完成实验，并验收实验结果提交实验报告。四实验内容1在数据库EDUC中实现一下查询：1）求选修了高等数学的学生学号和姓名；2）求C1课程的成绩高于张三的学生学号和成绩；3）求其他系中比计算机系某一学生年龄小的学生信息（即求其它系中年龄小于计算机系年龄最大者的学生）；4）求其他系中比计算机系学生年龄都小的学生信息；5）求选修了C2课程的学生姓名；6）求没有选修C2课程的学生姓名；7）查询选修了全部课程的学生的姓名；8）求至少选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。2提高操作实验建立“工程-零件”数据库及如下4个表，并输入实验数据，用SQL语句实40列名类型长度是否可为空零件代码Char5零件名Char50规格Char10产地Char20颜色Char10列名类型长度是否可为空工程代码Char5工程名Char50负责人Char10