《计算机网络与数据库技术》-上机实验六

一.【实验目的】

(1) 掌握变量的使用方法。

(2) 掌握函数的使用方法。

(3) 掌握 Transact-SQL 编程

二、【实验准备】

(1) 已经接入局域网的网络实验室。

(2) 机器上已安装 SQL Server。

---

三.【实验内容】

(1) 用 Transact-SQL 编程：先为两个变量@x 和@y 赋值，然后求这两个变量的和、差、乘积和商。

(2)编程计算 1+2+3+4+5+...+100。

(3)编程计算 S=1+(1+3)+(1+3+5)+...+(1+3+5+...+51)。

(4)输出 100～200 之间既能被 3 整除，又能被 5 整除的数。

(5)编写程序求 2~500 之间的所有素数。

(6)创建一个标量函数 xS_jg_fun，统计“学生基本信息”表中各籍贯的人数。

(7)创建一个多语句表值函数 xS_cj_fun，返回高于给定成绩的学生的学号、姓名、课程。

(8)创建一个内联表值函数 xs_xk_fun，根据学号统计学生选修课程的信息。

名及成绩等信息。

(9)分别调用上述定义的函数，进行函数功能的验证。

(10) 修改标量函数 xS_jg_fun，统计“学生基本信息”表中男生、女生的人数。

(11）用 T-SQL 命令删除 xs_xk_fun 和 xs_ej_fun 用户定义函数。

---

四．【实验预习】

一.  变量

       1.变量：分为全局变量和局部变量

        全局变量:由系统提供且预先声明，通过在名称前加两个“@”来区别于局部       变量。T-SQL全局变量作为函数引用。

        局部变量:局部变量用于保存单个数据值。

       2.局部变量的定义：

        通过DECLARE语句声明局部变量。例如：DECLARE @x INT;

        当声明局部变量后，可用SEL或SELECT语句为其赋值。例如：SET @x=1;

二.  函数

         在SQL中，函数分为系统内置函数和用户定义函数。

        系统内置函数：是SQL语言自带的函数，用于执行特定的操作或计算。这些函数通常用于处理数据类型、字符串、日期等。例如，COUNT、SUM、AVG、MAX、MIN、CONCAT、UPPER和NOW等函数都是系统内置函数。

       用户定义函数：是根据用户的需求自定义的函数。用户定义函数可以根据输入参数和返回值类型来定义，从而实现特定的功能。用户定义函数可以提高代码的可重用性和可维护性，同时也可以提高查询效率。

---

五.【实验结果】

(1)用 Transact-SQL 编程：先为两个变量@x 和@y 赋值，然后求这两个变量的和、差、乘积和商。

DECLARE @x INT = 666; 
DECLARE @y INT = 100; 
DECLARE @sum DECIMAL(10, 2); 
SET @sum= @x+@y; 
DECLARE @cha DECIMAL(10, 2); 
SET @cha=@x-@y; 
DECLARE @chengji DECIMAL(10, 2); 
SET @chengji= @x*@y; 
DECLARE @shang DECIMAL(10, 2); 
SET @shang=@x/@y;  
    --查询结果
SELECT '和' AS '运算', @sum AS '结果';  
SELECT '差' AS '运算', @cha AS '结果';  
SELECT '乘积' AS '运算', @chengji AS '结果';  
SELECT '商' AS '运算', @shang AS '结果';

(2)编程计算 1+2+3+4+5+...+100。

DECLARE @x INT
SET @x=0
DECLARE @i INT
SET @i=1
WHILE @i <= 100
BEGIN
    SET @x= @x + @i
    SET @i = @i + 1
END
SELECT @x AS '计算结果';

(3)编程计算 S=1+(1+3)+(1+3+5)+...(1+3+5+...+51)。

WITH CTE AS (  
    SELECT 1 AS num, 1 AS X  
    UNION ALL  
    SELECT num+2, X+(num+2)  
    FROM CTE  
    WHERE num<51  
)  
SELECT sum(X) AS S  
FROM CTE;

(4)输出 100～200 之间既能被 3 整除，又能被 5 整除的数。

DECLARE @x INT = 100;  
DECLARE @y BIT = 1;  
WHILE @x <= 200 
BEGIN  
    SET @y= 1;  
    IF @x%5=0 AND @x%3=0 
    BEGIN  
        SET @y = 0;  
    END  
    IF @y=0 
    BEGIN  
        PRINT @x; 
    END  
    SET @x = @x+1;  
END

(5)编写程序求 2~500 之间的所有素数。

DECLARE @x INT = 2;  
DECLARE @y BIT =1; 
print 2;
print 3;
print 5;
print 7;
print 11;
WHILE @x <= 500  
BEGIN  
    SET @y= 1;  
    IF @x%2=0 OR @x%3 = 0 OR @x % 5 = 0 OR @x % 7 = 0 OR @x % 11 = 0 
    BEGIN  
        SET @y = 0;  
    END  
    IF @y= 1  
    BEGIN  
        PRINT @x; 
    END  
    SET @x = @x+1;  
END

(6)创建一个标量函数 xS_jg_fun，统计“学生基本信息”表中各籍贯的人数。

创建“学生基本信息”表：

CREATE TABLE 学生基本信息 (  
    学号 VARCHAR(10) PRIMARY KEY,  
    姓名 VARCHAR(8),  
    籍贯 VARCHAR(50),  
    选修课程 VARCHAR(10),  
    成绩 INT,  
    性别  VARCHAR(1) NOT NULL CHECK (性别 IN ('F', 'M')) 
);

插入数据：

INSERT INTO 学生基本信息 
VALUES (1, '张三', '北京', '计算机科学', 85, 'M'),
(2, '李四', '上海', '英语', 90, 'F'),
(3, '王五', '广州', '数学', 78, 'M'),
(4, '赵六', '深圳', '物理', 82, 'F'),
(5, '陈七', '成都', '化学', 91, 'M'), 
(6, '杨八', '重庆', '生物', 88, 'F'),   
(7, '吴九', '天津', '历史', 79, 'M'),  
(8, '郑十', '武汉', '地理', 84, 'F'),  
(9, '孙一', '西安', '政治', 87, 'M')

统计人数：

CREATE FUNCTION xS_jg_fun (@籍贯 VARCHAR(50))  
RETURNS INT AS  
BEGIN  
    DECLARE @人数 INT;  
    SELECT @人数 = COUNT(*) FROM 学生基本信息 WHERE 籍贯 = @籍贯;  
    RETURN @人数;  
END;

(7)创建一个多语句表值函数 xS_cj_fun，返回高于给定成绩的学生的学号、姓名、课程。 

CREATE FUNCTION xS_cj_fun (@成绩 INT)  
RETURNS TABLE AS  
RETURN (  
    SELECT 学号, 姓名,选修课程  
    FROM 学生基本信息  
    WHERE 成绩>@成绩  
);

(8)创建一个内联表值函数 xs_xk_fun，根据学号统计学生选修课程的信息。名及成绩等信息。

CREATE FUNCTION xs_xk_fun (@学号 INT)  
RETURNS @选修课信息 TABLE (姓名 VARCHAR(8), 课程 VARCHAR(10), 成绩 INT)
AS  
BEGIN  
    INSERT INTO @选修课信息 (姓名, 课程, 成绩)  
    SELECT 姓名,选修课程,成绩  
    FROM 学生基本信息    
    WHERE 学号 = @学号  
    RETURN  
END;

(9)分别调用上述定义的函数，进行函数功能的验证。

标量函数 xS_jg_fun，统计“学生基本信息”表中各籍贯的人数。 

SELECT dbo.xS_jg_fun ('上海') as '此籍贯人数';

 

---

 多语句表值函数 xS_cj_fun，返回高于给定成绩的学生的学号、姓名、课程。

SELECT * FROM xS_cj_fun(87);

 

---

内联表值函数 xs_xk_fun，根据学号统计学生选修课程的信息。名及成绩等信息。

SELECT * FROM xs_xk_fun(2);

（10）修改标量函数 xS_jg_fun，统计“学生基本信息”表中男生、女生的人数。

 修改：

DROP FUNCTION xS_jg_fun;
GO
CREATE FUNCTION xS_jg_fun (@籍贯 VARCHAR(50))  
RETURNS TABLE AS  
RETURN (  
    SELECT   
        COUNT(CASE WHEN 性别 = 'M' THEN 1 END) AS 男生人数,  
        COUNT(CASE WHEN 性别 = 'F' THEN 1 END) AS 女生人数  
    FROM 学生基本信息  
    WHERE 籍贯 = @籍贯  
);

调用语句：

SELECT * FROM xS_jg_fun('北京');

 

(11）用 T-SQL 命令删除 xs_xk_fun 和 xs_cj_fun 用户定义函数。

DROP FUNCTION xs_xk_fun;  
DROP FUNCTION xs_cj_fun;