第11章 数据库应用实践指导 - 图文

第11章 数据库应用实验指导

第2篇 实验与课程设计指导 第11章 数据库应用实验指导

数据库应用实践对于学生素质能力的培养非常重要。主要包括SQL Server2012的安装及功能、数据库中数据定义与操作、SQL语言编程、安全性授权、故障恢复，以及数据库系统维护等方面内容的可操作性实验。所有实验都是针对数据库重要操作内容，提供了专门的同步练习和上机实验，还为每个设计的实验提供了详细的实验指导。

教学目标

● 熟练掌握SQL Server 2012的界面及功能操作 ● 掌握关系型数据库的关系模式及模型应用

● 掌握SQL定义语言、常用数据及数据库操作、T-SQL结构、存储过程及触发器

SQL Server 2012的安全体系及设置 ● 掌握关系模型的完整性约束机制，

● 了解SQL Server 2012的分布式数据库应用 ● 掌握初步的数据库系统管理技术

11.1实验一 SQL Server 2012界面及功能

11.1.1实验目的

（1）掌握SQL Server Management Studio（SSMS）的启动和登陆。

（2）熟悉SQL Server Management Studio的基本菜单功能和界面。 （3）掌握SQL Server 2012的启动、服务器注册等功能。

（4）掌握新建数据库的方法、并熟练使用分离及附加数据库、备份及还原数据库功能。 11.1.2 实验内容及步骤

1．SQL Server 2012的安装

1）SQL Server 2012安装前准备

（1）SQL Server 2012 的各版本之间选择。SQL Server 2012分为三个版本，从高到低分别是企业版、商业智能版和标准版。其中企业版是全功能版本，而其它两个版本则分别面向工作组和中小企业，所支持的机器规模和扩展数据库功能都不一样，价格方面是根据处理器核心数量而定，以下是有关功能和价格的详细资料列表：

除SQL Server企业版、SQL Server商业智能，和SQL Server 标准版外，SQL Server 2012 还包括 SQL Server 2012的延伸版如Developer Edition 和Express Edition。

SQL Server 2012支持包括，Windows 7 SP1、Windows Server 2008 R2、Windows Server 2008 Service Pack 2和Windows Vista Service Pack 2。

（2）软硬件要求。网络软件要求：独立的命名实例和默认实例支持以下网络协议：Shared Memory、Named Pipes、TCP/IP、VIA 。

Internet要求：Microsoft 管理控制台 (MMC)、SQL Server Data Tools (SSDT)、Reporting Services 的报表设计器组件和 HTML 帮助都需要 Internet Explorer 7 或更高版本。

安装程序支持软件：SQL Server 安装程序需要 Microsoft Windows Installer 3.1 或更高版本以及 Microsoft 数据访问组件 (MDAC) 2.8 SP1 或更高版本。您可以从此 Microsoft 网站下载 MDAC 2.8 SP1。

数据库原理及应用学习与实践指导 SQL Server 2012

SQL Server 安装程序安装该产品所需的以下软件组件： ? Microsoft Windows .NET Framework 4.0 ? Microsoft SQL Server 本机客户端

? Microsoft SQL Server 安装程序支持文件

硬件要求：x86处理器1.0 GHz，建议2 GHz或更高，内存最小1 GB，建议4 GB。 2） SQL Server 2012的安装

在微软网站上下载列表最下面的CHSx86SQLFULL_x86_CHS_Core.box、CHSx86 SQLFULL_x86_CHS_Intall.exe和CHSx86SQLFULL_x86_CHS_Lang.box三个安装包。放在同一个目录下，并双击打开可执行文件CHSx86SQLFULL_ x86 _CHS_Intall.exe。系统解压缩之后打开另 外一个安装文件夹SQLFULL_x86_CHS。打开该文件夹，并双击SETUP.EXE，开始安装SQL Server 2012。其余按安装步骤一步一步执行。如图11-1至11-21所示。

图11-1 SQL Server2012安装界面

图11-2 安装程序支持规则 图11-3产品密钥

图11-4 产品许可检查界面 图11-5 安装程序支持文件

第11章 数据库应用实验指导

图11-6 设置角色 图11-7 功能选择

图11-8 安装规则 图11-9 实例配置

图11-10 磁盘空间要求 图11-11 服务器配置

数据库原理及应用学习与实践指导 SQL Server 2012

图11-12 数据库引擎配置 图11-13 分析服务配置

图11-14 数据库引擎配置 图11-15 分析服务配置

图11-16 数据库引擎配置 图11-17 分析服务配置

第11章 数据库应用实验指导

图11-18 数据库引擎配置 图11-19 分析服务配置

图11-20完成安装 图11-21 安装完毕后添加的程序和服务

在安装完SQL Server 2012后，开始菜单中添加了如图11-21中程序和相应的服务。 2．SQL Server2012服务器配置

SQLServer2012数据库使用前必须启动数据库服务器，数据库服务器的配置和管理是使用SQLServer2012的首要任务，启动，暂停和停止服务的方法很多，这里主要介绍SQL Server 配置管理器完成这些操作，其操作步骤如下：

单击“开始”│“Microsoft SQL Server 2012”│“配置工具”，选择“SQL Server Configuration Manager”，打开SQL Setver配置管理器，如图11-22所示。单击“SQL Server 2012服务”选项，在右边的对话框里可以看到本地所有的SQL Server服务，包括不同实例的服务，如图11-23所示。

如果要启动、停止、暂停SQL Server服务的话，鼠标指向服务名称，单击右键，在弹出的快捷键菜单里选择“启动”、“停止”、“暂停”即可。

服务器注册主要为注册本地或者远程SQL Server服务器。打开SQL Server 2012下Management Studio，进行服务器注册。注册步骤如下：

数据库原理及应用学习与实践指导 SQL Server 2012

图11-22 SQL Server 配置服务管理器（一）

图11-23 SQL Server 配置管理器（二）

在视图菜单中单击“已注册的服务器”菜单选项显示已注册的服务器，如图11-24所示。

图11-24 已注册服务器

在右上角已注册的服务器中，选择注册类型进行相应服务类型注册。

在选定的服务类型的树型架构的根部单击鼠标右键，选择“新建服务器组…”进行组的建立，如图11-25所示。

第11章 数据库应用实验指导

图11-25 选择服务器组菜单

输入服务器组名称，单击保存即可，如图11-26所示。

图11-26 新建服务器组 图11-27 新建服务器注册

在新建的服务器组下面注册服务器，在新建服务器节点处单击鼠标右键，弹出菜单，选择新建选项下面的服务器注册选项，进行服务器注册，如图11-27所示。填写服务器名称，选择相应的认证方式，输入用户名及密码，完成注册。

3．SQL Server Management Studio的使用

SQL Server Management Studio（可称为SQL Server集成管理器，简写为Management Studio，缩写为SSMS）是为SQL Server数据库管理员和开发人员提供的新工具。此工具由Visual Studio内部承载，它提供了用于数据库管理的图形工具和功能丰富的开发环境。Management Studio将SQL Server 2012企业管理器、Analysis Manager和SQL 查询分析器的功能集于一身，还可用于编写MDX、XMLA和XML语句。

1）启动Management Studio 在“开始”菜单上，依次指向“所有程序”、SQL Server 2012，再单击Management Studio。出现如下11-28的展示屏幕。

接着打开Management Studio窗体，并首先弹出“连接到服务器”对话框（如图11-29）。在“连接到服务器”对话框中，采用默认设置（Windows身份验证），再单击“连接”。

数据库原理及应用学习与实践指导 SQL Server 2012

默认情况下，Management Studio 中将显示三个组件窗口，如下图11-30。

图11-28 SQL Server 2012展示屏幕 图11-29 打开时的SQL Server Management Studio

标准工具栏 已注册的服务器 属性窗口 对象资源管理器 文档窗口

图11-30 SQL Server Management Studio的窗体布局

2）使用SQL用户验证登录

许多初次使用SQL Server 2012的SQL验证登入时，例如使用sa账户登入会出错，解决办法如下：右键单击服务器，在菜单中选择“属性”。弹出如下图11-31所示对话框，选择“SQL Server 和Windows身份验证模式”。

图11-31服务器属性--选择“SQL Server 和Windows身份验证模式”局

第11章 数据库应用实验指导

再依次单击“安全性”，”登录名”，双击要改变登录属性的数据库用户名，例如sa，弹出如下对话框，选择“状态”，更改成如下样子即可。如图11-32所示。

选择确定按钮后，弹出“重启服务器”对话框，返回到主界面。再右键单击服务器，在菜单中选择“重新启动”，则服务器重新启动。

图11-32登录属性--选择启用登录 图11-33 登录属性—常规中设置sa用户密码

接着单击服务器的“安全性”，右键单击“登录名”中的sa，在菜单中选择“属性”，弹出“登录属性对话框”，选择“常规”选项，设置sa用户的登录密码和确认密码“123456”，如图11-33所示。再选择“状态”选项，选择“启用”登录。按确定按钮即完成用户sa的登录设置。如图11-34所示。

图11-34登录属性—状态中设置启用登录 图11-35连接到服务器界面

断开服务器连接后，再次建立连接服务器，打开如图11-35所示的“连接到服务器对话框，选择身份验证为”SQL Server身份验证“，设置”登录名“为sa，输入密码”123456“，即进入主界面。

11.2实验二 关系模式及模型应用 11.2.1实验目的

（1）学会使用Sybase公司的PowerDesigner 12.5建模工具绘制概念模型图。 （2）学会使用Sybase公司的PowerDesigner 12.5建模工具生成物理模型图。

数据库原理及应用学习与实践指导 SQL Server 2012

（3）学会使用Sybase公司的PowerDesigner 12.5建模工具生成SQL Server数据库对应的SQL脚本。 11.2.2 实验指导及步骤

使用PowerDesigner 12.5制做概念模型图。 随着数据库应用系统的广泛使用，各大数据库厂商和第三方合作开发了智能化的数据库建模工具，如Sybase公司的PowerDesigner、RATIONAL公司的Rational Rose、Oracle公司的CASE*METHOD等，它们是同一类型的计算机辅助软件工程（CASE）工具。CASE工具把开发人员从繁重的劳动中解脱出来，大大地提高了数据库应用系统的开发质量。

PowerDesigner是Sybase公司的数据库建模工具，使用它可以方便地对管理信息系统进行分析设计，它几乎包括了数据库模型设计的全过程。利用PowerDesigner可以制作数据流程图、概念数据模型、物理数据模型，可以生成多种客户端开发工具的应用程序，可为数据仓库制作结构模型，还可以对团队设计模型进行控制。

PowerDesigner 是唯一结合了下列几种标准建模技术的建模工具套件：使用 UML 的应用程序建模、业务流程建模和传统数据库建模技术，从而为您提供了高度集成、基于知识库、可自定义、图形化、直观并易于使用的工具集。作为功能强大的全部集成的建模和设计解决方案，PowerDesigner 可使企业快速、高效并一致地构建自己的信息系统。PowerDesigner 提供大量角色功能，从而区分企业内部不同职责。PowerDesigner 使用中央企业知识库提供高级的协同工作和元数据的管理，并且十分开放，支持所有主流开发平台。

PowerDesigner 支持以下技术：

（1）数据建模：PowerDesigner 支持基于信息工程或 IDEF 1/x 标记的概念层、逻辑层和物理层数据建模。

（2）应用程序建模：PowerDesigner 支持全部 UML 图表并提供高级对象/关系映射以持久实施管理。PowerDesigner 还支持链接到 UML 和数据建模的特定 XML 建模。

（3）业务流程建模：PowerDesigner 支持直观、通俗的业务流程说明和定义图表。 （4）集成建模：PowerDesigner 模型完全集成在一起：使用 PowerDesigner 的链接和同步技术。PowerDesigner 模型将元数据集成到所有模型类型。

对所有主流开发平台的开放支持：支持超过 45 种 RDBMS、主流应用程序开发平台（如 Java J2EE、Microsoft .NET、Web Services 和 PowerBuilder）以及流程执行语言（如 ebXML 和 BPEL4WS）。

可自定义：PowerDesigner 提供完全脚本化的 MDA 支持、UML 框架的高级支持，通过脚本语言提供常规任务自动化，以及通过模板和脚本代码生成器提供完全可自定义的 DDL 或生成代码。

参考步骤：绘制概念模型的步骤如下：

（1）启动PowerDesigner，如图11-36所示。

（2）新建概念模型图。概念模型图类似于E-R图，只是模型符号略有不同。在打开的窗口中，选择菜单：File→New，出现如图11-37所示的新建文件对话框，选择“Conceptual Data

Model”，然后单击“确定”按钮，将创建概念模型图。图11-36 PowerDesigner启动程序

第11章 数据库应用实验指导

图11-37 新建对话框

单击确定后，出现如图11-38所示的窗口。左方的浏览窗口用于浏览各种模型图，右方为绘图窗口，可以从绘图工具栏中选择各种模型符号来绘制E-R图，下方为输出窗口，显示各种输出结果。

浏览窗绘图窗口 输出窗口

图11-38 浏览窗口

（3）添加实体。在绘图工具栏中选择“实体”图标，鼠标变成图标形状，在设计窗口的适当位置单击鼠标，将出现一个实体符号，如图11-39所示。

图11-39 添加实体

在绘图窗口的空白区域，单击右键使得光标变为正常的箭头形状。然后选中该实体并双击，打开如图11-40所示的实体属性窗口。

数据库原理及应用学习与实践指导 SQL Server 2012

图11-40 实体属性窗口

其中General选项卡中主要选项的含义如下：

Name：实体的名字，一般输入中文。 Code：实体代号，一般输入英文。

Comment：注释，输入对此实体更加详细的说明。

（4）添加属性。不像标准的E-R图中使用椭圆表示属性，在PowerDesigner中添加属性只需打开Attributes（属性）选项卡，如图11-41所示。

图11-41 Attributes（属性）选项卡

其中Attributes（属性）选项卡中主要的选项的含义如下： Name：属性名，一般使用中文表示 Code：属性代号，一般用英文表示 Data Type：数据类型

Domain：域，表示此属性取值的范围

M：即Mandatory，强制属性，表示该属性必填，不能为空。

P：即Primary Identifier，是否是主标识符，表示实体唯一的标识符。对应常说的主键。

D：即Displayed，表示在实体符号中是否显示。 单击DataType下方的按钮可以选择数据类型，如

图11-42所示。 图11-42 数据库类型对话框

（5）添加实体之间的关系。同理，请添加课程实体，并添加相应的属性，如图11-43~11-44所示。

第11章 数据库应用实验指导

图11-43 添加属性 图11-44 添加属性

现在，添加上述两个实体之间的关系。如果两个实体间是多对多的关系的话,可以有两种方法建立关系,一种是从绘图工具栏选择Relationship（关系）图标,直接建立多对多的关系,第二种是先添加association联系对象,再通过两个实体分别与联系对象通过Association Link图标建立关系，可在association联系对象上添加额外的属性，可自行实验。

从绘图工具栏选择Relationship（关系）图标。

单击第一个实体“学生”，保持左键按下的同时把光标拖拽到第二个实体“课程”上，然后释放左键，一个默认的关系就建立了，如图11-45所示。

选中图图11-45中定义的关系，双击将打开图11-46所示的Relationship Properties（关系属性）对话框。在General选项卡中定义关系的常规属性，修改关系的名称和代号。

图11-45 建立关系 图11-46关系属性对话框

两个实体间的影射基数需要在Details选项卡中详细定义。假定一个学生可以有多门课程的成绩，即一对多的关系，如图11-47所示。

数据库原理及应用学习与实践指导 SQL Server 2012

图11-47 映射基数详细定义

（6）单击保存按钮图标，保存为“学生选课概念模型图”，文件后缀名默认为“*.CDM”。 （7）检查概念模型。选择菜单：Tools→Check Model，出现如图11-48所示的检查窗口。单击“确定”按钮后出现检查结果，如图11-49所示。如果有错误，将在Result List中出现错误列表，用户可以根据这些错误提示进行改正，直到出现“0error(s)”的信息。

图11-48 检查概念模型 图11-49 检查结果

（8）生成物理模型图。绘制出概念模型图并经过项目小组和客户讨论决定后，可以进一步选择具体的数据库，生成物理模型图。选择菜单：Tools→Generate Physical Data Model，出现如图11-50所示的窗口。单击“保存”图标，保存为“teachingSystem”，后缀名默认为“*.PDM”，保存后如图11-51所示。

第11章 数据库应用实验指导 图11-50生成物理模型图

图11-51 生成物理模型图的视图窗口

（9）生成SQL数据库脚本。单击菜单：Database→Generate Database,出现如图11-52所示的窗口。

图11-52生成SQL数据库脚本对话框

输入SQL脚本文件名，单击“确定”，将自动生成对应数据库的SQL脚本。如图11-53所示。

图11-53生成的SQL数据库脚本

说明：PowerDesigner生成的SQL Sever脚本没有建库语句，只有建表语句。建库语句需要人工添加。

下面验证由PowerDesigner生成的SQL Sever脚本是否可行，可先建立一个数据库，然后单击“新建查询”，将脚本的语句复制到新建查询窗口中，选择好刚刚建立的数据库，单击“执行”，结果如图11-54所示。

数据库原理及应用学习与实践指导 SQL Server 2012

图11-54验证SQL数据库脚本

9.2.3 实验练习

本次实验是基于某学校教务系统数据库进行建模操作，该数据库有6个数据表，其中4个实体表和2个关系表，实体表为：学院表（department）、学生表（student）、老师表（teacher）、课程表（course）；关系表为：老师开课表（teacher_course），学生选课表（student_teacher_course）。

通过分析数据表单及业务功能，可得出初步模型图，如图11-55所示：

图11-55 某学校教务系统数据模型图

使用前面讲的方法建立学院（department）、学生（student）、老师（teacher）、课程（course）4 个Entity 对象，及老师开课（teacher_course），学生选课（student_teacher_course）两个association联系对象

。

第11章 数据库应用实验指导

具体要求如下：

添加每个实体的属性。

添加各个实体之间的关系。

绘制完毕后对概念模型图进行检查。 选择SQLServer数据库生成物理模型图。 最后生成SQL Sever对应的SQL脚本。

建立好一个数据库，验证SQL脚本的正确性。 实验注意事项

（1）让学生认真熟悉PowerDesigner 12.5建模工具的使用方法。 （2）正确添加每个实体及其它们的属性。 （3）正确建立各个实体之间的关系。

（4）学生应在老师的指导和检查下按时完成上机任务。

11.3实验三 SQL常用数据操作 11.3.1实验目的

（1） 理解SQL语言概念和特点； （2）熟悉SQL2012功能； （3）掌握SQL数据类型及应用； （4）熟悉表的创建与管理；

（5）熟练掌握数据查询方法和数据编辑 11.3.2 实验内容

（1）创建数据库和修改数据库 （2）创建数据库表和修改数据库表 （3）插入数据库记录和修改数据库记录 （4）数据查询方法 11.3.3实验步骤

1．创建数据库和修改数据库

（1）创建一个teachingSystem数据库，该数据库的主数据文件逻辑名称为teachingSystem，物理文件名为teachingSystem.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为teachingSystem_log，物理文件名为teachingSystem_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。

注意：数据文件应该尽量不保存在系统盘上并与日志文件保存在不同的磁盘区域。 实验操作步骤；

建立数据库有两种方法，一种是使用T-SQL语句，另一种是通过SSMS图形界面来实现，下面就两种方法分别进行操作。

使用T-SQL语言：单击SSMS工具栏的“新建查询”，打开查询窗口，输入下列SQL语句，并在工具栏上单击“执行”按钮，即可建立要求的数据库。

CREATE DATABASE teachingSystem

ON PRIMARY --建立主数据文件 ( NAME = 'teachingSystem', --逻辑文件名

FILENAME='E:\\ teachingSystem.mdf', --物理文件路径和名字 SIZE=10240KB, --初始大小

MAXSIZE = UNLIMITED, --最大尺寸为无限大 FILEGROWTH = 10%) --增长速度为% LOG ON

数据库原理及应用学习与实践指导 SQL Server 2012

( NAME='teachingSystem_log', --建立日志文件

FILENAME='E:\\teachingSystem_log.ldf', --物理文件路径和名字 SIZE=1024KB,

MAXSIZE = 5120KB, FILEGROWTH = 1024KB )

使用SSMS图形界面方法：在“对象资源管理器”窗口中，右击“数据库”文件夹，从弹出的快捷菜单中选择“新建数据库”选项，如下图11-56所示。

图11-56 新建数据库

在窗口中根据提示输入该数据库的相关内容，如数据库名称、所有者、文件初始大小、自动增长值和保存路径等。

数据库名称：可以使用字母、数字、下划线或短线。例如：teachingSystem

所有者：数据库的所有者可以是任何具有创建数据库权限的登录名。例如：选择其为<默认值>账户，该账户是当前登录到SQL Server上的账户。

文件名（窗口右侧没显示出的部分）：用于存储数据库中数据的物理文件的名称，默认情况下，SQL Server用数据库名称来创建物理文件名。例如：teachingSystem

数据库文件逻辑名称：引用文件时使用。

文件类型：显示文件是数据文件，还是日志文件，数据文件用来存放数据，而日志文件用来存放对数据所做操作的记录。

文件组：为数据库中的文件指定文件组，主文件组（PRIMARY）或任一辅助文件组（SECONDARY）。所有数据库都必须有一个主文件组。

初始大小：数据库的初始大小至少是MODEL数据库的大小。例如：3MB。

自动增长：显示SQL Server是否能在数据库到达其初始大小极限时自动应对。单击右边带有省略号（?）的命令按钮，如下图所示，设置是否启动自动，文件增长方式，最大文件大小。默认是“不限制文件增长”，其好处是可以不必过分担心数据库的维护，但如果一段“危险”的代码引起了数据的无限循环，硬盘可能会被填满。因此，当一个数据库系统要应用到生产环境中时，应设置“限制文件增长(MB)”选项以防止出现上述的情形。

可以创建次数据文件来分担主数据文件的增长。

例如：文件按10%的比例增长，限制最大文

件大小为10MB。如图11-57所示。 图11-57 更改Test的自动增长设置

第11章 数据库应用实验指导

路径：数据库文件存放的物理位置，默认的路径是C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data。单击右边带有省略号（?）的命令按钮，打开一个资源管理器风格的对话框，可以在该对话框中更改数据库文件的位置。

在选项页框中，可设置数据库的排序规则，恢复模式，兼容级别以及其他一些选项设置。 在文件组页框中，可设置或添加数据库文件和文件组的属性，如是否只读、默认值等 单击“确定”按钮，系统开始创建数据库，创建成功后，当回到SSMS中的对象资源管理器时，刷新其中的内容，在“对象资源管理器”的“数据库”节点中就会显示新创建的数据库Test.

（2）使用SSMS查看或修改数据库。右击所要修改的数据库，从弹出的快捷菜单中选择“属性”选项，出现如下图所示的数据库属性设置对话框。可以看到，修改或查看数据库属性时，属性页框比创建数据库时多了两个，即选项和权限页框。可以分别在常规、文件、文件组、选项和权限对话框里根据要求来查看或修改数据库的相应设置。

(3)使用T-SQL语句将两个数据文件添加到teachingSystem数据库中。

ALTER DATABASE teachingSystem

ADD FILE --添加两个次数据文件 (NAME=teachingSystem1,

FILENAME='E:\\ teachingSystem1.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB),

(NAME=teachingSystem2,

FILENAME='E:\\ teachingSystem2.ndf', SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 1MB) GO

（4）删除数据库。在“对象资源管理器”窗口中，在目标数据库上单击鼠标右键，弹出快捷菜单，选择“删除”命令。出现“删除对象”对话框，确认是否为目标数据库，并通过选择复选框决定是否要删除备份以及关闭已存在的数据库连接，单击“确定”按钮，完成数据库删除操作。

T-SQL语句删除数据库是：DROP DATABASE teachingSystem

2．创建数据库表和修改数据库表

在teaching数据库中，创建系部表（department），课程表（course），学生表（student）和教师表（teacher）,教师开课表（teacher_course）,学生选课表（student_teacher_course）。教务管理系统的数据模型：

系部表（系部编号，系部名称，系部领导，系部电话，系部地址），主键：系部编号。如图11-58所示。

图11-58 系部表结构 图11-59 课程表结构

课程表（课程编号，系部编号，课程名称），主键：课程编号；外键：系部编号。如图11-59所示。

学生表(学生编号, 系部编号,姓名, 性别，出生日期，地址，总分，民族，年级，学院，专业)，主键: 学生编号，外键：系部编号。如图11-60所示。

数据库原理及应用学习与实践指导 SQL Server 2012

图11-60 学生表结构 图11-61 教师表结构

教师表（教师编号，系部编号，教师姓名，职称），主键：教师编号，外键：系部编号。如图11-61所示。

教师开课表（教师编号，课程编号，学期），主键：教师编号，课程编号，外键分别是：教师编号，课程编号。如图11-62所示。

图11-62 教师开课表结构 图11-63 学生选课表结构

学生选课表（学生编号，课程编号，教师编号，学期，成绩），主键：学生编号, 课程编号,教师编号，外键：学生编号和课程编号，教师编号。如图11-63所示。

（1）创建数据库表。使用SSMS图形界面方法：在“对象资源管理器”窗口中，右击指定数据库teachingSystem的“表”文件夹，从弹出的快捷菜单中选择“新建表”选项，

依次输入字段名称和该字段的数据类型,以及允许空或非空的设置，即可创建数据库表，如图11-64所示。

图11-64 创建数据库表

使用命令行方法：选择teachingSystem数据库，在“新建查询”窗口中输入下列SQL语句，每输入一条SQL命令，单击一下“执行”即可：

create table department (

dept_id char(6) not null, dept_name char(20) null, dept_head char(6) null, dept_phone char(12) null, dept_addr char(40) null,

constraint PK_DEPARTMENT primary key nonclustered (dept_id) /* 主键添加*/

第11章 数据库应用实验指导

)

go

create table course (

course_id char(6) not null, dept_id char(6) not null, course_name char(20) null,

constraint PK_COURSE primary key nonclustered (course_id) ) Go

create table student (

stu_id char(6) not null, dept_id char(6) not null, name char(8) null, sex char(2) null, birthday datetime null, address char(40) null, totalscore int null, nationality char(8) null, grade char(2) null, school char(20) null, class char(16) null, major char(30) null,

constraint PK_STUDENT primary key nonclustered (stu_id) ) go

create table teacher (

teacher_id char(6) not null, dept_id char(6) not null, teacher_name char(8) null, rank char(6) null,

constraint PK_TEACHER primary key nonclustered (teacher_id) ) Go

create table teacher_course (

teacher_id char(6) not null, course_id char(6) not null, term_id char(2) null,

constraint PK_TEACHER_COURSE primary key (teacher_id, course_id) ) Go

create table student_teacher_course (

course_id char(6) not null, stu_id char(6) not null, teacher_id char(6) not null, term char(2) null, score int null,

constraint PK_STUDENT_TEACHER_COURSE primary key (course_id, stu_id, teacher_id) ) Go

alter table course /*修改表结构，添加一个外键*/

add constraint FK_COURSE_DEPARTMENT foreign key (dept_id) references department (dept_id) go

alter table course

add constraint FK_COURSE_DEPARTMEN_DEPARTME foreign key (dept_id) references department (dept_id) go

alter table student

add constraint FK_STUDENT_DEPARTMEN_DEPARTME foreign key (dept_id) references department (dept_id) go

alter table student_teacher_course

add constraint FK_STUDENT__STUDENT_T_COURSE foreign key (course_id) references course (course_id) go

alter table student_teacher_course

数据库原理及应用学习与实践指导 SQL Server 2012

add constraint FK_STUDENT__STUDENT_T_STUDENT foreign key (stu_id) references student (stu_id) go

alter table student_teacher_course

add constraint FK_STUDENT__STUDENT_T_TEACHER foreign key (teacher_id) references teacher (teacher_id) go

alter table teacher

add constraint FK_TEACHER_DEPARTMEN_DEPARTME foreign key (dept_id) references department (dept_id) go

alter table teacher_course

add constraint FK_TEACHER__TEACHER_C_TEACHER foreign key (teacher_id) references teacher (teacher_id) go

alter table teacher_course

add constraint FK_TEACHER__TEACHER_C_COURSE foreign key (course_id) references course (course_id) go

（2）修改表：把表student_teacher_course 中term列删除，并将score的数据类型改为float。实验操作步骤；在“新建查询”窗口中输入下列SQL语句：

USE teachingSystem GO

ALTER TABLE student_teacher_course DROP COLUMN term GO

ALTER TABLE student_teacher_course ALTER COLUMN score float GO

（3）删除表：使用SSMS删除表。在“对象资源管理器”窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，展开“表”节点，右键要删除的表，选择“删除”命令或按下“DELETE”键。

使用T-SQL语句删除表

在数据库teachingSystem中建一个表Test1，然后删除。

USE teachingSystem GO

DROP TABLE Test1

说明：在删除表的时候可能会出现?删除对象?的对话框，如删除department表。这是因为所删除的表中拥有被其他表设置了外键约束的字段，如果删除了该表，必然对其他表的外键约束造成影响，数据库系统禁止删除被设置了外键的表。如图11-65所示。

图11-65 删除对象对话框

3．插入数据库记录和修改数据库记录

1）使用SSMS和T-SQL添加记录 给系部表（department），课程表（course），学生表（student）和教师表（teacher）,教

第11章 数据库应用实验指导

师开课表（teacher_course）,学生选课表（student_teacher_course）添加适当的记录。注意先后次序。先给无外键约束的表进行添加记录，然后再给有外键的表添加，否则无法添加。

使用SSMS添加记录：在“对象资源管理器”窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，展开“表”节点，右键要插入纪录的表，选择“编辑前200行”命令，即可输入纪录值和修改记录。

使用T-SQL添加记录：如给教务管理系统数据库的表添加适量记录：

USE teachingSystem GO

/*添加课程表记录*/

INSERT course (course_id,dept_id,course_name) VALUES ('100001', '1001 ', '数据库原理')

INSERT course (course_id,dept_id,course_name VALUES (N'100002', N'1001', N'面向对象程序设计') /*添加系部表记录*/

INSERT department(dept_id, dept_name, dept_head, dept_phone, dept_addr) VALUES ('1001', '电子信息学院', '王老师', '1391001011', '图书馆7楼')

INSERT department(dept_id, dept_name, dept_head, dept_phone, dept_addr)VALUES ('1002', '机械学院', '刘老师', '1891020202', '文理大楼2楼')

INSERT department(dept_id, dept_name, dept_head, dept_phone, dept_addr)VALUES ('1003', '电气学院', '张老师', '1893774737', '华宁路2332号') /*添加学生表记录*/

INSERT student(stu_id, dept_id, name,sex, birthday, address, totalscore, nationality, grade, school, class, major) VALUES ('1201', N'1001', '高燕', '女', CAST(0x0000806800000000 AS DateTime),'上海', NULL, '汉族', '1 ', '电子信息', '1001', '计算机')

INSERT student(stu_id, dept_id, name,sex, birthday, address, totalscore, nationality, grade, school, class, major) VALUES ('1202', '1001', '马冰峰', '男', CAST(0x000081F400000000 AS DateTime), '安徽', NULL, '汉族', '1 ', '电子信息', '1001', '计算机') /*添加老师表记录*/

INSERT teacher (teacher_id, dept_id, teacher_name, rank) VALUES ('30101 ', '1001', '沈学东', '副教授')

INSERT teacher (teacher_id, dept_id, teacher_name, rank) VALUES ('30102 ', '1001', '贾铁军', '教授') /*添加老师开课表记录*/

INSERT teacher_course (teacher_id, course_id, term_id) VALUES ('30101 ', '100001', '1') INSERT teacher_course (teacher_id, course_id, term_id) VALUES ('30102 ', '100002', '1') /*添加学生选课表记录*/

INSERT student_teacher_course (course_id, stu_id, teacher_id, score) VALUES ('100001', '1201 ', '30101 ', 90)

INSERT student_teacher_course (course_id, stu_id, teacher_id, score) VALUES ('100002', '1201', '30102 ', 85) GO

2) 编写脚本程序来实现上述数据库及表 操作步骤；在“新建查询”窗口中输入下列SQL语句，并另存为test.sql文件，单击“执行”按钮即自动运行该脚本程序，实现数据库及相关表和记录的创建。

use master go

if exists (select * from sysdatabases where name='teachingSystem') --判断teachingSystem数据库是否存在，如果是就进行删除 drop database teachingSystem go

create database teachingSystem --创建数据库 on primary (

name=' teachingSystem',--主数据文件的逻辑名

fileName='D:\\ teachingSystem.mdf',--主数据文件的物理名 size=10MB,--初始大小 filegrowth=10% --增长率 )

log on (

name= ‘teachingSystem_log',--日志文件的逻辑名 fileName='D:\\ teachingSystem.ldf',--日志文件的物理名 size=1MB,

数据库原理及应用学习与实践指导 SQL Server 2012

maxsize=20MB,--最大大小 filegrowth=10% ) go

use teachingSystem go

if exists (select * from sysobjects where name='department')--判断是否存在此表 drop table department go

create table department

( …… --此处省略，同上； ) Go

if exists (select * from sysobjects where name= ‘course’)--判断是否存在此表 drop table course go

create table course

( …… --此处省略，同上； ) Go

--此处省略另外4个表的创建代码

INSERT course (course_id,dept_id,course_name) VALUES ('100001', '1001 ', '数据库原理') ...... –省略其他数据的创建

INSERT department(dept_id, dept_name, dept_head, dept_phone, dept_addr) VALUES ('1001 ', '电

子信息学院', '王老师', '1391001011', '图书馆7楼')

...... –省略其他数据的创建

INSERT student(stu_id, dept_id, name,sex, birthday, address, totalscore, nationality, grade, school,

class, major) VALUES ('1201', N'1001', '高燕', '女', CAST(0x0000806800000000 AS DateTime),'上海', NULL, '汉族', '1 ', '电子信息', '1001', '计算机')

...... –省略其他数据的创建 GO

4．数据查询方法

从数据库中检索出所需要的数据和实现方法。如何使用SQL的SELECT语句的WHERE子句进行比较，BETWEEN、LIKE关键字的查询，使用ORDER BY子句对SELECT语句检索出来的数据进行排序，并用GROUP BY、HAVING子句和函数进行分组汇总。

在SQL Server Management Studio中执行SELECT查询语句，如图11-66所示。 启动“SQL Server Management Studio”，并在“树”窗格中单击“表”节点，数据库中的所有的表对象将显示在内容窗格中。右击“对象资源管理器”的“数据库”中的某一表，在菜单中选择“打开表”命令 。查询设计器中的四个窗格：

关系图窗格：用于向选择查询中添加表或视图对象以及选择输出字段，并允许相关联的表连接起来。如果看不到该窗口，请单击工具栏上的“显示/隐藏关系图”按钮。

条件窗格：用于设置显示字段、排序结果、搜索以及分组结果的选项。如果看不到该窗口，请单击工具栏上的“显示/隐藏条件窗格”按钮。

SQL窗格：用于输入和编辑所有的SELECT语句。如果看不到该窗口，请单击工具栏上的“显示/隐藏SQL窗格”按钮。

第11章 数据库应用实验指导

图11-66 SQL查询

结果窗格：结果窗格用于显示SELECT语句执行的结果，并允许添加、修改以及删除记录。如果看不到该窗口，请单击工具栏上的“显示/隐藏结果窗格”按钮。

1）投影部分列

从教务信息数据库teachingSystem的学生表student中查询出学生的编号、姓名和地址是“上海”的前三列的记录。

实验操作步骤：在SQL Server Management Studio中执行SELECT查询语句。

USE teachingSystem GO

SELECT stu_id, name,address FROM student Where address ='上海'

查询结果如图11-67所示：

图11-67 投影部分列查询结果

从教务管理数据库teachingSystem的学生表student中查询出前5条纪录。

USE teachingSystem GO

SELECT TOP 5 * FROM student GO

从教务管理数据库teachingSystem的学生表student中查询出班级的名称。

USE teachingSystem GO

SELECT DISTINCT Class FROM student GO

2）投影所有列

从教务管理数据库teachingSystem的学生表student中查询所有纪录。

USE teachingSystem

数据库原理及应用学习与实践指导 SQL Server 2012

SELECT * FROM student

3）字段函数（列函数）运用：

从教务管理数据库teachingSystem中学生选课表student_teacher_course中查询出成绩的最高分、最低价、平均分和总分。

USE teachingSystem GO

SELECT MAX(score) AS 最高分,MIN(score) AS 最低分,AVG(score) AS 平均分,SUM(score) AS

总分

FROM student_teacher_course GO

查询学生选课表中最低分的学生编号和课程编号（提示用子查询结构）。 USE teachingSystem GO

SELECT stu_id AS 学生编号,course_id AS 课程编号 FROM student_teacher_course

WHERE score=(SELECT MIN(score) FROM student_teacher_course) GO

查询结果如图11-68所示：

图11-68 投影所有列查询结果

4）FROM子句连接查询

从教务管理数据库teachingSystem的教师表teacher中查询出教师的编号、姓名和系部名称。

USE teachingSystem GO

SELECT teacher_id,teacher_name,dept_name FROM teacher ,department

WHERE teacher.dept_id=department.dept_id 或采用表的别名： USE teachingSystem GO

SELECT teacher_id,teacher_name,dept_name FROM teacher X ,department Y WHERE Y.dept_id==Y.dept_id

查询结果如图11-69所示：

第11章 数据库应用实验指导

图11-69 FROM子句连接查询查询结果

5）比较、逻辑判断、模糊匹配查询

查找学生表中年龄不满20岁，专业名称带有“机械”两个字的学生信息。

USE teachingSystem GO

SELECT * FROM student

WHERE not(year(getdate())-year(birthday)+1>20) and major LIKE '%机械%' GO

6）分组查询

查找学生表中每个系部的平均总分大于550的系部编号、平均总分。

USE teachingSystem GO

SELECT dept_id, AVG(totalscore) as ?平均总分? FROM student GROUP BY dept_id

HAVING AVG(totalscore)>550 GO

11.4实验四 索引及视图操作 11.4.1实验目的 （1）了解SQL Server 2012中索引的定义、类型及其作用； （2）掌握创建索引、编辑索引以及删除索引的方法 （3）熟悉视图常用操作、视图创建，修改，删除操作。 （4）熟悉使用视图访问数据。 11.4.2 实验内容及步骤 1.索引操作

本章在学习SQL Server 2012索引的基础知识之后，主要练习对索引的使用，如创建索引、编辑索引以及删除索引等。

（1） 在SQL Server 2012中teachingSystem数据库中Student表，选择stu_id来创建一个惟一聚集索引，如图11-70所示。

实验操作步骤 ：

使用图形界面进行操作：单击相应表左边的“＋”号，右击“索引”节点，选择“新建索引”命令，如图11-71所示。

数据库原理及应用学习与实践指导 SQL Server 2012

图11-70 新建索引 图11-71 新建索引对话框设置信息 图11-72 选择要建立索引的字段

在弹出的“新建索引”对话框中设置要创建索引的名称、类型，添加索引键列。如图11-72所示.

使用T-SQL语句建立索引：在新建查询窗口中输入下列语句,并单击“执行”按钮。

USE teachingSystem GO

CREATE UNIQUE CLUSTERED INDEX student_index1 ON student(stu_id ASC) GO

(2) 使用SQL Server Management Studio 查询窗口在student表中新建一个惟一非簇索引，命名为student_index2，使用字段stu_id。

实验操作步骤：

在查询窗口输入下列SQL语句：

USE teachingSystem GO

CREATE UNIQUE NONCLUSTERED INDEX student_index2 ON student (stu_id ASC)

(3)通过新建学院College表添加主键约束来使SQL Server 2012自动为该表生成一个惟一性的簇索引。

操作步骤：

在SQL Server Management Studio 查询窗口中键入以下语句：

use teachingSystem create table College (

col_ID smallint primary key, col_name char(8), ) GO

(4)使用SQL Server Management Studio向导删除Reader表中的student_index2索引. 操作步骤：

1）启动SQL Server Management Studio查询窗口。 2）在查询窗口中键入以下SQL语句：

use teachingSystem

DROP INDEX student.student_index2 GO

2．视图操作

在熟悉了本章SQL Server 2012中关系和视图的基础知识之后，主要练习建立、修改和删除视图以及视图的应用等。

（1）使用SSMS创建视图。以创建学生表的视图为例。

在“对象资源管理器”中，右键单击teachingSystem数据库的“视图”节点或该节点中的任何视图，从快捷菜单中选择“新建视图”，如图11-73所示。

第11章 数据库应用实验指导

图11-73 打开新建视图 图11-74 添加表

在弹出“添加表”对话框中选择所需的表student或视图等，再单击“添加”；如图11-74所示。

在“视图设计器”中选择要投影的列，选择条件等，如图11-75所示。

图11-75 视图设计器选择投影列及条件

执行该SQL语句，运行正确后保存该视图View_student1。 （2）使用T-SQL语句创建上述视图。

CREATE VIEW View_student2 AS SELECT * FROM dbo.student

WHERE (dept_id = ?1001?)

（3）定义视图View_student3，课程成绩大于85分的学生学号，姓名,性别，出生日期

CREATE VIEW View_student3 AS

SELECT student.stu_id,student.name,student.sex，student.birthday FROM student INNER JOIN

student_teacher_course ON student.stu_id = student_teacher_course.stu_id WHERE (student_teacher_course.score > 85) 或者

CREATE VIEW View_student3 AS

SELECT student.stu_id, student.name, student.sex，student.birthday FROM student，student_teacher_course

WHERE student.stu_id = student_teacher_course.stu_id AND (dbo.student_teacher_course.score> 85)

(4) 创建一个新视图从原来视图View_student3中查询1990年以后出生的学生信息。

数据库原理及应用学习与实践指导 SQL Server 2012

CREATE VIEW View_student4 AS

SELECT *

FROM View_student3

WHERE (YEAR(birthday) >= 1990)

(5)通过视图对基本表进行插入、修改、删除行的操作，有一定的限制条件.在视图View_student1中插入一条新的记录，其各字段的值分别为'1220', N'1001', '陈静', '女', '1993-1-1' ,'上海', NULL, '汉族', '1 ', '电子信息', '1001', '计算机'。

USE teachingSystem GO

INSERT INTO View_student1

(stu_id, dept_id, name,sex, birthday, address, totalscore, nationality, grade, school, class, major)

VALUES ('1220', N'1001', '陈静', '女', '1993-1-1' ,'上海', NULL, '汉族', '1 ', '电子信息', '1001', '计算机') GO

修改记录:将视图View_student1中的学生姓名为“张思文”的出生日期改为’1988-4-27’。

USE teachingSystem

GO

UPDATE View_student1 SET birthday =? 1988-4-27? WHERE name=? 张思文? GO

（7）删除视图View_BOOKS

使用T-SQL语句

DROP VIEW View_student1 GO

实验小结

使用CREATE VIEW语句建立视图，使用ALTER VIEW语句修改视图，使用DROP VIEW语句删除视图。如果在一个视图中存在一个计算列，则不允许使用INSERT语句，除非在基本表或视图中没有缺省值的非空列都被包含在添加新记录行的视图中，才允许使用INSERT语句。

11.5实验五 T-SQL程序结构 11.5.1实验目的 （1）掌握T-SQL程序结构：会用注释和变量、运算符及表达式、函数，熟悉程序结构的流程控制。

（2）掌握系统内置函数的概念及其应用；

（3）通过定义和使用用户自定义函数，掌握自定义函数的概念及其应用。 11.5.2 实验内容与步骤 1. 变量、常用的标准函数与流程控制语句的使用

常用标准函数主要介绍：数学函数、字符串函数、日期/时间函数。 T-SQL的几个常用语句如下：

IF…ELSE、语句。

WHILE、BREAK和CONTINUE语句。 RETURN语句。

(1)变量的使用

① 对于teachingSystem 给出的student数据库表结构，定义一个名为female 的变量，并在SELECT 语句中使用它查找女学生的编号和姓名。

USE teachingSystem GO

DECLARE @female char(2) SET @female = '女' SELECT stu_id, name FROM student

第11章 数据库应用实验指导

WHERE sex = @female

② 定义一个变量，用于获取号码为1212 的学生的家庭住址,并将该家庭住址的学生编号和姓名显示出来。

DECLARE @maddress char(40)

SELECT @maddress = (SELECT address FROM student WHERE stu_id = '1212') SELECT stu_id, name FROM student

WHERE address = @maddress

③使用CASE 语句对student 表按所在部门（dept_id）进行分类 SELECT stu_ID, Name, Address, dept_id = CASE dept_id

WHEN 1001 THEN '电子信息学院' WHEN 1002 THEN '机械学院部' WHEN 1003 THEN '电气学院' END

FROM student

(2)IF?ELSE语句

IF?ELSE条件控制语句是在执行T-SQL语句时强加条件。如果条件满足(布尔表达式返回TRUE时)，则在IF关键字及其条件之后执行T-SQL语句。可选的ELSE关键字引入备用的T-SQL语句，当不满足IF条件时(布尔表达式返回FALSE)，就执行这个语句。

若存在学号为“1212”的学生，则显示已存在的信息，否则插入该学生的记录。 要查询学号为“1212”的学生，可以使用SELECT语句和EXISTS函数完成， 具体实现步骤如下：

“查洵编辑框”窗口中输入以下实现上述功能的SQL程序：

USE teachingSystem GO

SET QUOTED_IDENTIFIER ON GO

SET ANSI_NULLS ON GO

IF EXISTS(SELECT stu_id FROM student WHERE stu_id='1212')

PRINT '已存在学号为1212的学生' ELSE

Insert into student(stu_id, dept_id, name,sex, birthday, address, totalscore, nationality, grade, school, class, major) VALUES ('1212', N'1001', '陈静', '女', '1993-1-1' ,'上海', NULL, '汉族', '1 ', '电子信息', '1001', '计算机')

②执行上述命令就可实现以上功能，图11-76是学生情况表中存在学号为'1212'的学生时的情况：可以从结果窗格中看到输出的信息。 图11-76 IF ELSE语句执行结果

（3）WHILE、BREAK和CONTINUE语句

WHILE语句一般用来设置重复执行SQL语句或语句块的条件。只要指定的条件为真，就重复执行WHILE中的循环体。可以使用BREAK和CONTINUE关键字在循环内部设置条件来达到控制WHILE循环中语句的执行。

2.用户自定义函数的应用

用户可以使用CREATE FUNCTION语句编写自己的函数，以满足特殊需要。用户自定

数据库原理及应用学习与实践指导 SQL Server 2012

义函数，可以传递0个或多个参数，并返回一个简单的数值，一般来说返回的都是数值或字符型的数据。

（1）定义一个自定义函数，实现从出生年月到年龄的计算。自定义函数如下： create function re_year

(@vardate datetime,@curdate datetime) returns tinyint

as begin return datediff(yyyy,@vardate,@curdate) end

具体实现步骤为：

进入SSMS，选择要操作的服务器和数据库，单击“可编程性”下的“函数”，选择后单击鼠标右键，在弹出的快捷菜单中选择“新建”菜单并展开后选择“标量值函数”。

出现的“查询”窗口的“文本”输入框中输入需创建的用户定义函数，如图11-77所示。

图11-77 用户定义函数

（3）单击“执行”按钮，则系统在该数据库中创建了—个名为“Re_Year”的用户自定义函数。

（4）函数定义后，就可以在SQL语句中调用用户定义的函数完成指定的功能。

（5）进入SQL Server查询分析器界面，在“查询编辑框”窗口中输入如下的SQL语句：

---将用户定义函数Re_Year用在查询?student?中， ---直接给出学生情况的年龄。 USE teachingSystem GO

SELECT stu_id,name,sex,

dbo.Re_Year(birthday,GETDATE()) As 年龄 FROM student GO

第11章 数据库应用实验指导

图11-78 使用用户定义函数

⑺单击工具栏上的运行按钮执行上述SQL语句。语句执行后，从结果窗格中可以检索到学生情况表中的数据。

从运行结果中可以看出，每个学生通过用户定义函数Re_Year求得一个年龄。 上述过程也可以直接用SQL命令来完成“Re_Year”自定义函数的建立。

进入SQL Server查询分析器界面，在“查询编辑框”窗口中输入如下的SQL语句：

Use teachingSystem

if exists(SELECT * FROM dbo.sysobjects WHERE id=Object_id(N'[dbo].[Re_Year]') and xtype in

(N'FN',N'IF',N'TF'))

drop function [dbo].[Re_Year] GO

SET QUOTED_IDENTIFIER OFF GO

SET ANSI_NULLS OFF GO

---创建?Re_Year?用户定义函数，该函数是将由所给出的日期（参数） ---计算出该日期与当前时间之间的年数(返回值) create function re_year

(@vardate datetime,@curdate datetime) returns tinyint as begin return datediff(yyyy,@vardate,@curdate) end GO

SET QUOTED_IDENTIFIER OFF GO

SET ANSI_NULLS OFF GO

---将该用户定义函数用在查询?学生情况表?中，直接给出学生情况的年龄 USE teachingSystem GO

SELECT stu_id,name,sex,

dbo.Re_Year(birthday,GETDATE()) As 年龄 FROM student GO

用户的自定义函数不再使用时，可以使用DROP FUNCTION语句或在企业管理器中将其删除。

（2）删除上述案例中创建的名为“Re_Year”的用户自定义函数。

(1)进入SSMS，选择要操作的服务器和数据库，单击“可编程性”下的“函数”，选择

数据库原理及应用学习与实践指导 SQL Server 2012

“标量值函数”下面要删除的用户自定义函数，这里选择“Re_Year”。

(2)单击鼠标右键，在弹出的快捷菜单中选择“删除”，出现“删除对象”对话框。 (3)单击“确定”按钮，完成指定用户自定义函数的删除。 3.自行练习内容

（1）如果student表中有入校时间在2006年以后的学生，把该学生的学号，姓名和入学时间查询出来，否则输出“没有在2006年以后入学的学生”。（if..else）

（2）如果student表中有名叫“张思文”的学生，就把他的名字修改为“张思武”，并输出修改前后的学号，姓名，性别信息，否则输出“没有张思文这个人，所以无法修改啦！”

（3）查询student表，只要有年龄小于20岁的学生，就将每个学生的出生日期都加1个月，如此循环下去，直到所有的学生的年龄都不小于20岁。（while 循环）

（4）使用WHILE语句求1到100之间的累加和并输出 （5）定义一个用户自定义的函数Score_ReChange，将成绩从百分制转化为五级记分制。将该用户定义的函数用在查询每个学生的成绩中，给出五级记分制的成绩。

（6）定义一个用户自定义的函数，完成如下功能：如果学生有不及格的成绩，则在学生情况表的备注列中输入“有不及格的成绩”，否则输入“没有不及格的成绩”。

*11.6实验六 存储过程及触发器 11.6.1实验目的 （1）掌握SQL Server编程结构； （2）掌握数据存储过程及触发器使用； 11.6.2 实验内容及步骤 对teachingSystem数据库，编写存储过程，完成下面功能： 1．使用T-SQL语句创建存储过程

1）创建不带参数存储过程

（1）创建一个从student表查询学号为1202学生信息的存储过程proc_1，其中包括学号、姓名、性别、出生日期、系别等；调用过程proc_1查看执行结果。

use teachingSystem go

create proc proc_1 as

select stu_id,name, sex,birthday,dept_id from student

where sno=?1202? 执行： exec proc_1

（2）在teachingSystem数据库中创建存储过程proc_2，要求实现如下功能：查询学分为4的课程学生选课情况列表，其中包括学号、姓名、性别、课程号、学分、系别等。调用过程proc_2查看执行结果。

use teachingSystem go

create proc proc_2 as

select A.stu_id,name,sex,B.course_id,B.credit, B.dept_id from student A,course B, student_teacher_course C

where A.stu_id=C. stu_id and C.course_id=B.course_id and B.credit=4; 执行： exec proc_2

2）创建带参数存储过程

创建一个从student表中按学生学号查询学生信息的存储过程proc_3.其中包括：学号、姓名、性别、出生日期、系别等。查询学号通过执行语句中输入。

第11章 数据库应用实验指导

use teachingSystem go

create proc proc_3 @sno char(6) as

select stu_id,name,sex,birthday,dept_id from student

where stu_id=@sno 执行：

use teachingSystem go

exec proc_3 ?1212?

3）创建带输出参数存储过程

创建存储过程，比较两个学生的实际总分，若前者高就输出0，否则输出1

CREATE PROCEDURE PROC4

(@ID1 char(6), @ID2 char(6),@result int out ) AS BEGIN

DECLARE @SR1 int, @SR2 int

SET @SR1=(select totalscore FROM student WHERE stu_id= @ID1) SET @SR2=(select totalscore FROM student WHERE stu_id = @ID2) IF @SR1 > @SR2 SET @result = 0 ELSE

SET @result = 1 END

执行该存储过程，并查看结果 DECLARE @result int

EXEC PROC4 '1201', '1202', @result OUTPUT SELECT @result

2．使用T-SQL语句查看、修改和删除存储过程 （1）查看存储过程proc_2、proc_4定义

Exec sp_helptext proc_2

Exec sp_helptext proc_4

（2）删除存储过程proc_1 Drop proc proc_1

3．使用T-SQL语句实现触发器定义。

（1）为表student_teacher_course创建一个插入触发器，当向表student_teacher_course中插入一条数据时，通过触发器检查记录的stu_id值在表student中是否存在，若不存在，则取消插入操作,并检查course_id在表course中是否存在，若不存在也取消插入操作。

create trigger credit_insert on student_teacher_course for insert, UPDATE as

IF (SELECT stu_id FROM inserted) NOT IN (SELECT stu_id FROM student) BEGIN ROLLBACK END

IF (SELECT course_id FROM inserted) NOT IN (SELECT course_id FROM course) BEGIN ROLLBACK END

执行：

insert into student_teacher_course(course_id,teacher_id,stu_id,score) values('100001','30102','1205',90)

（2）为表student创建一个删除触发器，当删除表student中一个学生的资料时，将表sc中相应的成绩数据删除。

use teachingSystem go

If exists(select name from sysobjects where name= 'student_delete'and type='tr') Drop trigger student_delete

本文来源：https://www.bwwdw.com/article/zr5x.html