sqlserver实验

实验1SqlServer数据库的安装1．实验目的（1）通过安装来了解，感受SQLServer2005（2）了解SQLServer2005所支持的多种形式的管理架构，并确定此次安装的管理架构形式（3）熟悉安装SQLServer2005的各种版本所需的软、硬件要求，确定要安装的版本（4）熟悉SQLServer2005支持的身份验证种类（5）掌握SQLServer服务的几种启动方法（6）正确配置客户端和服务器端网络连接的方法（7）掌握SQLServerManagementStudio的常规使用2．实验准备（1）了解SQLServer2005的各种版本及所需的软、硬件要求（2）了解SQLServer2005支持的验证种类（3）了解SQLServer2005各组件的主要功能（4）掌握在查询分析器中执行SQL语句的方法3．实验内容（1）安装SQLServer2005，并在安装时将登录身份验证模式设置为“SQLServer”和“Windows”验证，其他选择默认，并记住Sa的密码。（2）利用SQLServerConfigurationManager配置SQLServer2005服务器（3）利用SQLServer2005创建的默认帐户，通过注册服务器向导首次注册服务器（4）试着创建一些由SQLServer2005验证的用户，删除第一次注册的服务器后用新建的帐户来注册服务器。（5）为某一个数据库服务器指定服务器别名，后通过服务器别名注册该数据库服务器（6）熟悉和学习使用SQLServer2005的SQLServerManagementStudio实验2创建数据库和表1．实验目的（1）了解SQLServer数据库的逻辑结构和物理结构（2）了解表的结构和特点（3）了解SQLServer的基本数据类型（4）掌握在SQLServerManagementStudio中创建数据库和表的方法（5）掌握使用T-SQL语句创建数据库和表的方法2．实验准备（1）要明确能够创建数据库的用户必须是系统管理员或被授权使用Createdatabase的用户（2）创建数据库必须要确定数据库名、所有者、数据库大小（最初大小、最大大小、是否允许增长和增长的方式）和存储数据的文件（3）确定数据库包含哪些表及包含的表结构，还要了解掌握在SQLServerManagement的常用数据类型，以创建数据库的表（4）了解常用的创建数据库和表的方法3．实验内容（1）数据库分析①创建用于学生选课管理的数据库，数据库名为“student”，初始大小20M，最大50M，数据库自动增长，增长方式是按15%；日志文件大小5M，最大25M，按5M增长。数据库的逻辑文件名和物理文件名均采用默认值②Student数据库包含学生和教师的信息、教学 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载 信息、课程信息、教师任课信息等。数据库关系图如下图所示。（2）在对象资源管理器中创建和删除数据库和表①在对象资源管理器中创建student数据库②在对象资源管理器中删除student数据库③在对象资源管理器中分别创建“学生”和“教师”表④在对象资源管理器中分别删除“学生”和“教师”表（3）在查询分析器中创建和删除数据库和表①用T-SQL语句创建student数据库createdatabasestudentonprimary(name=student_db_data,filename='E:\db\student_db_data.mdf',size=20mb,maxsize=50mb,filegrowth=15%)logon(name=student_db_log,filename='E:\db\student_db_data.mdf',,size=5mb,maxsize=25mbfilegrowth=5mb)collateChinese_PRC_CI_ASgo②用T-SQL语句创建“学生”和“教师”表数据库中各表的创建：USEstudentGOCREATETABLE系部(系部代码char(2)CONSTRAINTpk_xbdmPRIMARYKEY,系部名称varchar(30)NOTNULL,系主任char(8))GOCREATETABLE专业(专业代码char(4)CONSTRAINTpk_zydmPRIMARYKEY,专业名称varchar(20)NOTNULL,系部代码char(2)CONSTRAINTfk_zyxbdmREFERENCES系部(系部代码))GOCREATETABLE班级(班级代码char(9)CONSTRAINTpk_bjdmPRIMARYKEY,班级名称varchar(20),专业代码char(4)CONSTRAINTfk_bjzydmREFERENCES专业(专业代码),系部代码char(2)CONSTRAINTfk_bjxbdmREFERENCES系部(系部代码),备注varchar(50))GOCREATETABLE学生(学号char(12)CONSTRAINTpk_xhPRIMARYKEY,姓名char(8),性别char(2),出生日期datetime,入学时间datetime,班级代码char(9)CONSTRAINTfk_xsbjdmREFERENCES班级(班级代码),系部代码char(2)CONSTRAINTfk_xsxbdmREFERENCES系部(系部代码),专业代码char(4)CONSTRAINTfk_xszydmREFERENCES专业(专业代码))GOCREATETABLE课程(课程号char(4)CONSTRAINTpk_kcPRIMARYKEY,课程名char(20)NOTNULL,学分smallint)GOCREATETABLE教师(教师编号char(12)CONSTRAINTpk_jsbhPRIMARYKEY,姓名char(8)NOTNULL,性别char(2),出生日期datetime,学历char(10),职务char(10),职称char(10),系部代码char(2)CONSTRAINTfk_jsxbdmREFERENCES系部(系部代码),专业char(20),备注varchar(50))GOCREATETABLE教学计划(课程号char(4)CONSTRAINTpk_jxjhchREFERENCES课程(课程号),专业代码char(4)CONSTRAINTpk_jxjhzydmREFERENCES专业(专业代码),专业学级char(4),课程类型char(8),开课学期tinyint,学分tinyint)GOCREATETABLE教师任课(教师编号char(12)CONSTRAINTfk_jsrkjsbhREFERENCES教师(教师编号),课程号char(4)CONSTRAINTfk_jsrkchREFERENCES课程(课程号),专业学级char(4),专业代码char(4)CONSTRAINTfk_jsrkzydmREFERENCES专业(专业代码),学年char(4),学期tinyint,学生数smallint)GOCREATETABLE课程注册(注册号bigintidentity(0,1)notforreplicationCONSTRAINTpk_zchPRIMARYKEY,学号char(12)CONSTRAINTfk_kczcxhREFERENCES学生(学号),课程号char(4)CONSTRAINTfk_kczckchREFERENCES课程(课程号),教师编号char(12)CONSTRAINTfk_kczcjsbhREFERENCES教师(教师编号),专业代码char(4)CONSTRAINTfk_kczczydmREFERENCES专业(专业代码),专业学级char(4),选课类型char(8),学期tinyint,学年char(4),成绩tinyint,学分tinyint)GO实验3表的基本操作1．实验目的（1）能够在资源管理器中对表数据进行插入、修改和删除等操作（2）能使用T-SQL语句对表数据进行插入、修改和删除等操作2．实验准备（1）了解表数据的插入、修改和删除操作，对表数据的更新操作可以在对象资源管理器中进行，也可用T-SQL语句完成（2）掌握使用T-SQL语句对表数据进行插入、修改和删除等操作的用法3．实验内容（1）在对象资源管理器中向student数据库中的表插入数据（2）使用T-SQL命令向studen数据库中的表插入数据a)向系部表中插入数据('01','计算机系','徐才智')b)向系部表中插入数据('02','经济管理系','张博')c)向系部表中插入数据('03','数学系','徐裕光')d)向系部表中插入数据('04','外语系','李溅波')UsestudentGoINSERTinto系部(系部代码,系部名称,系主任)VALUES('01','计算机系','徐才智')GOINSERTinto系部(系部代码,系部名称,系主任)VALUES('02','经济管理系','张博')GOINSERTinto系部(系部代码,系部名称,系主任)VALUES('03','数学系','徐裕光')GOINSERTinto系部(系部代码,系部名称,系主任)VALUES('04','外语系','李溅波')（3）GO在对象资源管理器中修改studenti数据库中的表数据（4）使用T-SQL命令修改studen数据库中的表数据a)将系部表中计算机系的系主任改为‘张中裕’Update系部set系主任=’张中裕’where系部名称=’计算机系’b)将系部表中的系部名称列中的‘系’全部更改为‘科学系’(使用replace 函数 excel方差函数excelsd函数已知函数     2 f x m x mx m      2 1 4 2拉格朗日函数pdf函数公式下载 ，用法：replace(串1，串2,串3)，其功能是将串1中的串2替换为串3)Update系部set系部名称=replace(系部名称,’系’,’科学系’)（5）在对象资源管理器中删除studen数据库中的表数据（6）使用T-SQL命令删除student数据库中的表数据a)在系部表中删除系主任姓‘张’的系部数据Deletefrom系部where系主任like‘张%’实验4数据查询1．实验目的（1）掌握Select语句的基本语法（2）掌握Insert语句的基本语法（3）掌握连接查询的基本方法（4）掌握子查询的基本方法2．实验准备（1）了解Select语句的执行方法（2）了解基本聚合函数的作用（3）了解Select语句的groupby和having子句的使用（4）了解Insert语句的基本语法 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 （5）了解连接查询的表示方法（6）了解子查询的表示方法3．实验内容（1）用Select语句进行简单查询①根据前面实验给出的数据表结构，查询每个学生的上机号、姓名、上机所剩余额等信息Select上机号,姓名,余额from上机卡②查询上机号为“03”的学生的姓名和余额Select姓名,余额from上机卡where上机号=’03’③查询所有姓“王”的学生的上机号、余额和上机密码Select上机号,余额,上机密码from上机卡where姓名like‘王%’④查询所有余额不足5元的学生的上机号Select上机号from上机卡where余额<5⑤查询所有上机日期在2008-3-1到2008-3-3之间的学生的上机号Select上机号from上机记录where上机日期betweenconvert（datetime,‘2008-3-1’)）andconvert（datetime,‘2008-3-3’)）（2）用Select语句进行高级查询①查询班级名称为“03计算机教育班”的学生的上机号和姓名Select上机号,姓名from上机卡where班级代码in(select班级代码from班级where班级名称=’03计算机教育班’)②查询所有余额不足5元的学生的上机号、姓名和班级名称Select上机号,姓名,班级名称from上机卡a,班级bwherea.班级代码=b.班级代码anda.余额<5③查询所有余额不足30元的学生的总人数Selectcount(*)总人数asfrom上机卡where余额<30④求每一天的上机总人数Selectcount(distinct上机号)as总人数from上机记录groupby上机日期⑤查询上机日期在2008-3-1到2008-8-3之间的各个班级的上机总人数Select班级代码,count（distinct上机号）a..from上机记录a,上机卡bwherea.上机号=b.上机号groupbyb.班级代码⑥将学生的上机号、姓名按余额的多少由高到低排序Select上机号,姓名from上机卡orderby余额desc4．计费数据库的数据结构：数据表的创建脚本：USEjifeiGOCREATETABLE班级(班级代码char(10)CONSTRAINTpk_bjdmPRIMARYKEY,班级名称char(30))GOCREATETABLE上机卡(上机号char(13)CONSTRAINTpk_sjhPRIMARYKEY,姓名char(8),班级代码char(10)CONSTRAINTfk_bjdmREFERENCES班级(班级代码),上机密码varchar(30),管理密码varchar(30),余额money,备注varchar(50))GOCREATETABLE上机记录(上机号char(13)CONSTRAINTfk_sjjlsjhREFERENCES上机卡(上机号),上机日期datetime,开始时间datetime,结束时间datetime,上机状态bit)GOCREATETABLE管理员(管理员代码char(20)CONSTRAINTpk_glydmPRIMARYKEY,姓名char(8),密码char(10))GO实验5数据完整性1．实验目的要求学生能够使用T-SQL语句的primarykey、check、foreignkey……references、notnull、unique等关键字实现SQLServer的实体完整性、参照完整性及用户定义的完整性2．实验准备（1）了解数据完整性的概念（2）了解约束的类型（3）了解创建约束和删除约束的语法（4）了解创建 规则 编码规则下载淘宝规则下载天猫规则下载麻将竞赛规则pdf麻将竞赛规则pdf 和删除规则的语法（5）了解绑定规则和解绑规则的语法（6）了解创建默认对象和删除默认对象的语法（7）了解绑定默认对象和解绑默认对象的语法3．实验内容（1）建表时创建约束。在student数据库中使用createtable语句创建表stu1，其结构如下：学号:char(12)，姓名:char(8)，性别:char(2)，出生日期:datetime，住址:char(40)，备注text。在建表时，创建所需约束。要求如下：将学号设为主键，主键名为pk_xuehao；为姓名添加唯一约束，约束名为uk_xingming；为性别添加默认约束，默认名为de_xingbie为出生日期添加check约束，约束名为ck_csrq，条件为：（出生日期>’01/01/1986’）Createtablestu1(学号char(12)constraintpk_xuehaoprimarykey,姓名char(8)constraintuk_xingmingunique,性别char(2)constraintde_xingbiedefault'男',出生日期datetimeconstraintck_csrqcheck(出生日期>convert(datetime,'1/1/1986',101)),住址char(40),备注text)（2）在查询分析器中删除上面所建的约束Altertablestu1Dropconstraintpk_xuehaoAltertablestu1Dropconstraintuk_xingmingAltertablestu1Dropconstraintde_xingbieAltertablestu1Dropconstraintck_csrq（3）基于学生选课管理系统中的student数据库中的表建立外键约束、规则、默认对象，进行绑定和解绑，最后删除所建的约束createruleck_csrqas@rq>convert(datetime,'1/1/1986',101)execsp_bindrule'ck_csrq','stu1.出生日期'execsp_unbindrule'stu1.出生日期'createdefaultde_xingbieas'男'execsp_bindefault'de_xingbie','stu1.性别'execsp_unbindefault'stu1.性别'实验6索引的应用1．实验目的（1）掌握创建索引的命令（2）掌握使用资源管理器创建索引的方法（3）掌握查看索引的系统存储过程的用法（4）掌握索引分析与维护的常用方法2．实验准备（1）了解聚集索引和非聚集索引的概念（2）了解创建索引的SQL语句（3）了解使用资源管理器创建索引的步骤（4）了解索引更名的存储过程的用法（5）了解删除索引的SQL命令的用法（6）了解索引分析与维护的常用方法3．实验内容（1）完成例 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 中索引的创建例：为student数据库中的“教师”表创建基于“专业”列的非聚集索引js_zy_index。代码如下：USEstudentGOCREATEINDEXjs_zy_indexON教师(专业)GO（2）为student数据库中课程注册表的成绩字段建立一个非聚集索引，名为kczccj_indexUsestudentgoCreateindexkczccj_index课程注册on(成绩)go（3）使用sp_helpindex查看课程注册表上的索引信息Usestudentgoexecsp_helpindex课程注册go（4）使用sp_rename将索引kczccj_index改为kcvc_cj_indexUsestudentGoExecsp_renamekczccj_index,kcvc_cj_indexgo（5）使用student数据库中的课程注册表，查询所有课程注册信息，同时显示查询处理过程中磁盘活动的统计信息UsestudentGoShowplan_allonGoSelect*from课程注册Go（6）用SQL语句删除kcvc_cj_indexUsestudentGoDropindexkcvc_cj_indexgo实验7视图的应用1．实验目的（1）掌握创建视图的命令（2）掌握使用资源管理器创建视图的方法（3）掌握查看视图的系统存储过程的用法2．实验准备（1）了解创建视图的方法（2）了解修改视图的SQL语句（3）了解视图更名的存储过程的用法（4）了解删除视图的SQL命令的用法3．实验内容（1）在student数据库中以学生表为基础，建立一个名为经济管理系学生的视图，显示学生表中的所有字段UsestudentGoCreateview经济管理系学生asSelect*from学生where专业代码in(selectdistinct专业代码from专业Where专业名称=’经济管理’)go（2）使用经济管理系学生视图查询专业代码为“0201”的学生select*from经济管理系学生where专业代码='0201'go（3）将经济管理系学生视图改名为v_经济管理系学生UsestudentGoExecsp_renamest_jjgl,v_经济管理系学生go（4）修改v_经济管理系学生视图的内容，使得该视图能查询到经济管理系所有的女生UsestudentGoAlterview经济管理系学生v_asSelect*from学生where性别=’女’and专业代码in(selectdistinct专业代码from专业Where专业名称=’经济管理’)go（5）用SQL语句删除v_经济管理系学生视图UsestudentGoDropviewv_经济管理系学生go实验8存储过程与触发器1．实验目的（1）掌握存储过程和触发器创建的方法和步骤（2）掌握存储过程和触发器的使用方法2．实验准备（1）了解存储过程和触发器的基本概念和类型（2）了解创建存储过程和触发器的SQL语句的基本语法（3）了解查看、执行、修改和删除存储过程的SQL语句的用法（4）了解查看、执行、修改和删除触发器的SQL语句的用法1．实验内容（1）存储过程的使用：①在student数据库中的学生、课程注册、课程表中创建一个带参的存储过程cjcx。其功能是：当任意输入一个学生的姓名时，返回该学生的学号、选修的课程名和课程成绩。CreatePROCEDURE[dbo].[cjcx]@axmchar(8)ASBEGINSELECT学生.学号,课程.课程名,课程注册.成绩from学生,课程,课程注册where学生.学号=课程注册.学号and课程注册.课程号=课程.课程号and姓名=@axmEND②执行存储过程cjcx，查询“周红瑜”的学号、选修的课程名和课程成绩。Execcjcx@axm=’周红瑜’③使用存储过程sp_helptext查看存储过程cjcx的文本信息Execsp_helptextcjcx（2）触发器的使用：①在jifei数据库中创建一个名为insert_sjkh的insert触发器，存储在“上机记录”表中。其作用是：当用户在“上机记录”表中插入记录时，若“上机卡”表中没有该上机号，则提示用户不能插入，否则，提示记录插入成功。UsejifeiGoCreatetriggerinsert_sjkhondbo.上机记录forinsertAsDeclare@asjkhchar(13)Declare@acountintSelect@asjkh=上机号frominsertedSelect@acount=count(*)from上机卡where上机号=@asjkhIf@acount=0Print'上机卡中无此卡号，不能插入'ElsePrint'数据插入成功'②在jifei数据库中创建一个名为del_sjh的delete触发器。其作用是：禁止删除“上机卡”表中的记录。UsejifeiGocreatetriggerdel_sjhondbo.上机卡insteadofdeleteAsPrint'不能删除上机卡中的记录'③在jifei数据库中创建一个名为update_sjh的update触发器。其作用是：禁止更新“上机卡”表中的上机号内容。UsejifeiGocreatetriggerupdate=_sjhondbo.上机卡insteadofupdateAsPrint'不能更新上机卡中的记录'④删除update_sjh触发器。UsejifeiGoDroptriggerupdate_sjhgo实验9函数的应用2．实验目的（1）掌握SQLServer常用系统函数的使用（2）掌握SQLServer三类用户自定义函数的创建方法（3）掌握SQLServer用户自定义函数的修改及删除方法3．实验准备（1）了解各类常用系统函数的功能及其参数的意义（2）了解SQLServer三类用户自定义函数的区别（3）了解SQLServer三类用户自定义函数的语法（4）了解对SQLServer自定义函数进行修改及删除的语法4．实验内容（3）SQLServer常用系统函数的使用：①统计教学计划中，第一学期所开设的课程总数Selectcount(*)from教学计划where开课学期=1②统计计算机系学生大学语文的平均分、最低分和最高分usestudentgoSelectavg(成绩),min(成绩),max(成绩)from课程注册Where专业代码in(Selectdistinct专业代码from专业where系部代码in(select系部代码from系部where系部名称='计算机系'))And课程号in(Selectdistinct课程号from课程where课程名='大学语文')（4）SQLServer三类用户自定义函数的创建：①创建一个自定义函数department()，根据系部代码返回该系部学生总人数createfunctiondepartment(@axbdmchar(2))returnsintasbegindeclare@acountintselect@acount=count(*)from学生where系部代码=@axbdmreturn@acountend②创建一个自定义函数teacher_info()，根据教师编号返回该该教师任课基本信息UsestudentGocreatefunctionteacher_info(@ajsbhchar(12))returnstableasreturn(select*from教师任课where教师编号=@ajsbh)（5）对SQLServer自定义函数进行修改及删除UsestudentGoDropfunctionteacher_infoDropfunctiondepartmentgo实验10T-SQL程序设计1．实验目的（1）掌握程序中的批处理、脚本和注释的基本概念和使用方法（2）掌握事务的基本语句的使用（3）掌握程序中的流程控制语句2．实验准备（1）理解程序中的批处理、脚本和注释的语法格式（2）理解事务的基本语句的使用方法（3）了解流程控制语句begin……end、if……else、case、waitfor、while语句的使用3．实验内容（1）编写程序，求2+3+4+......500declare@iintdeclare@asumintbeginset@i=2set@asum=0while@i<=500beginset@asum=@i+@asumset@i=@i+1endprint@asumend