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

第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，其中包括学 …… 此处隐藏：4034字，全部文档内容请下载后查看。喜欢就下载吧 ……