企业官网建设流程全解析

（1）第三章-文件组
（1）使用Transact-SQL语句，在SQL Server 2016实例上创建一个数据库，该数据库包括一个主数据文件、一个用户定义文件组和一个日志文件，主数据文件在主文件组中，而用户定义文件组包含两个次要数据文件。数据库名称为你的试卷代号+学号后4位，该数据库的主数据文件逻辑名称为student_data，物理文件名为student.mdf，初始大小为8MB，最大尺寸为无限大，增长速度为30%；数据库的日志文件逻辑名称为student_log，物理文件名为student.ldf，初始大小为4MB，最大尺寸为40MB，增长速度为2MB。用户定义文件组名称为你的试卷代号+学号后2位，包含的两个次要数据文件名分别为Data1和Data2，它们的初始大小都为6MB，最大尺寸都为20MB，增长速度都为2MB。要求该数据库主数据文件、两个次要数据文件，以及日志文件的物理文件都存放在d:\sql文件夹下。（10分）

CREATE DATABASE KS011234
ON PRIMARY
(
NAME = student_data,
FILENAME = 'd:\sql\student.mdf',
SIZE = 8MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 30%
),
FILEGROUP KS0134
(
NAME = Data1,
FILENAME = 'd:\sql\Data1.ndf',
SIZE = 6MB,
MAXSIZE = 20MB,
FILEGROWTH = 2MB
),
(
NAME = Data2,
FILENAME = 'd:\sql\Data2.ndf',
SIZE = 6MB,
MAXSIZE = 20MB,
FILEGROWTH = 2MB
)
LOG ON
(
NAME = student_log,
FILENAME = 'd:\sql\student.ldf',
SIZE = 4MB,
MAXSIZE = 40MB,
FILEGROWTH = 2MB
);

(2)使用 Transact-SQL 语句，在前面创建的数据库中创建 s 表和 sc 表，表结构如下：（10 分）
S表：
列名 数据类型及长度 是否为空 备注
studentno nchar(11) 否 主键
sname nchar(8) 否
sc表：
列名 数据类型及长度 是否为空 备注
studentno nchar(11) 否 主键
courseno nchar(6) 否
final numeric(6,2) 否

-- 切换数据库 USE 你的数据库名;
GO
-- 创建学生表 s
CREATE TABLE s
(
studentno NCHAR(11) NOT NULL PRIMARY KEY, -- 主键，非空
sname NCHAR(8) NOT NULL -- 姓名，非空 );
GO
-- 创建成绩表 sc
CREATE TABLE sc ( studentno NCHAR(11) NOT NULL,
courseno NCHAR(6) NOT NULL,
final NUMERIC(6,2) NOT NULL, -- 联合主键：学号+课程号
PRIMARY KEY (studentno, courseno),
-- 外键约束，关联学生表
FOREIGN KEY (studentno) REFERENCES s(studentno) );
GO

（3）第四章 -- 约束
(3)使用 Transact-SQL 语句，对 sc 表增加约束，进一步要求学生的期末成绩（final）字段取值在 0~100 之间。（10 分）
-- 切换到你的数据库 USE 你的数据库名;
GO --
给sc表添加检查约束，限制成绩0~100

ALTER TABLE sc
ADD CONSTRAINT CK_sc_final
CHECK (final >= 0 AND final <= 100);
GO

（4）第四章 insert (4) 使用 Transact-SQL 语句向 s 表和 sc 表添加以下记录。（10 分）
s表
studentno sname
15122210009 张三
15122211324 李四
sc表
studentno courseno final
15122210009 C05103 87.00
15122210009 C05109 77.00
15122211324 C05103 50.00
15122211324 C05109 70.00

-- 切换到你的数据库
USE 你的数据库名;
GO
-- 1. 向s学生表插入2条学生数据
INSERT INTO s(studentno, sname)
VALUES
('15122210009', N'张三'),
('15122211324', N'李四');
GO

-- 2. 向sc成绩表插入4条成绩数据
INSERT INTO sc(studentno, courseno, final)
VALUES
('15122210009', 'C05103', 87.00),
('15122210009', 'C05109', 77.00),
('15122211324', 'C05103', 50.00),
('15122211324', 'C05109', 70.00);
GO

（5）第五章 - 控制流语句 case
(5) 使用 Transact-SQL 语句，用 case 语句完成显示：final 字段 90 分以上显示 A，80 分以上显示 B，70 分以上显示 C，60 分以上显示 D，其它显示 E。（10 分）

USE 你的数据库名;
GO
SELECT
studentno,
courseno,
final,
-- CASE 判断成绩等级
CASE
WHEN final >= 90 THEN 'A'
WHEN final >= 80 THEN 'B'
WHEN final >= 70 THEN 'C'
WHEN final >= 60 THEN 'D'
ELSE 'E'
END AS score_level -- 新增列名为score_level（成绩等级）
FROM sc;
GO

（6）第八章 - 视图
(6) 使用 Transact-SQL 语句，在数据库中创建一个名为 V_final 的视图，查询每门课最高分的课号和成绩。（10 分）

- 切换数据库
USE 你的数据库名;
GO
-- 创建视图V_final
CREATE VIEW V_final
AS
SELECT
courseno AS 课程号,
MAX(final) AS 最高分
FROM sc
GROUP BY courseno;
GO

-- 查看视图结果
SELECT * FROM V_final;

第七章 -- 游标
(7) 使用游标显示表 sc 中所有 16 级（即 studentno 以 16 开头）学生的所有选修信息，格式如下。（10 分）
标题：16 级学生选修信息 列：学号、课程号、成绩

USE 你的数据库名;
GO
-- 1. 声明变量接收游标每行数据
DECLARE @studentno NCHAR(11),
@courseno NCHAR(6),
@final NUMERIC(6,2);
-- 2. 声明游标，筛选学号以16开头的记录
DECLARE cur_16stu CURSOR
FOR
SELECT studentno, courseno, final
FROM sc
WHERE studentno LIKE '16%';
-- 3. 打开游标
OPEN cur_16stu;

-- 4. 读取第一行数据
FETCH NEXT FROM cur_16stu INTO @studentno, @courseno, @final;
-- 打印表头
PRINT '====================16级学生选修信息====================';
PRINT '学号 课程号 成绩';
PRINT '----------------------------------------';
-- 5. 循环遍历游标所有行
WHILE @@FETCH_STATUS = 0
BEGIN
-- 格式化输出每行信息
PRINT '| ' + CAST(@studentno AS VARCHAR) + ' | ' + CAST(@courseno AS VARCHAR) + ' | ' + CAST(@final AS VARCHAR) + ' |';
-- 读取下一条
FETCH NEXT FROM cur_16stu INTO @studentno, @courseno, @final;
END

PRINT '----------------------------------------';

-- 6. 关闭、释放游标资源
CLOSE cur_16stu;
DEALLOCATE cur_16stu;
GO

（8）第九章 -- 存储过程
(8) 使用 Transact-SQL 语句，在前面创建的数据库上创建一个存储过程 ProcAvg，对指定 “Courseno” 的课程求 final 平均成绩。“Courseno” 由输入参数给定，计算出的平均成绩通过输出参数返回。若该存储过程已存在，则删除后重建。然后执行该存储过程，显示 Courseno 为 C05103 课程的平均成绩。（10 分）

-- 切换到你的数据库
USE 你的数据库名;
GO

-- 如果存储过程ProcAvg存在，先删除
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'ProcAvg')
DROP PROCEDURE ProcAvg;
GO

-- 创建存储过程ProcAvg
CREATE PROCEDURE ProcAvg
@in_courseno NCHAR(6), -- 输入参数：课程号
@avg_score NUMERIC(6,2) OUTPUT -- 输出参数：课程平均分
AS
BEGIN
SET NOCOUNT ON;
-- 根据输入课程号计算平均分并赋值给输出参数
SELECT @avg_score = AVG(final)
FROM sc
WHERE courseno = @in_courseno;
END
GO

-- 调用存储过程，查询课程C05103平均分
DECLARE @result NUMERIC(6,2); -- 定义变量接收输出值
EXEC ProcAvg @in_courseno = 'C05103', @avg_score = @result OUTPUT;

-- 打印结果
PRINT '课程C05103的平均成绩：' + CAST(@result AS VARCHAR(10));

（9）第九章 -- 触发器
(9) 使用 Transact-SQL 语句创建一个 AFTER 触发器，要求实现以下功能：当用户删除 s 表一条学生记录时，触发器自动删除 sc 表中该学生的选课信息。（10 分）

USE 你的数据库名;
GO
-- 如果触发器已存在，先删除避免报错
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'trg_del_stu')
DROP TRIGGER trg_del_stu;
GO
-- 在s表创建AFTER DELETE触发器
CREATE TRIGGER trg_del_stu
ON s
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
-- 从sc表删除和被删除学生学号匹配的选课记录
DELETE sc
FROM sc
INNER JOIN deleted ON sc.studentno = deleted.studentno;
END
GO

（10）第七，九章 -- 游标加存储过程
(10) 使用 Transact-SQL 语句，在 s 表中增加一个课程通过门数字段，课程期末成绩（final 字段）在 60 分及以上为通过，使用游标编写存储过程，再调用存储过程，将每个同学的课程通过门数自动正确填入 s 表。最后查询验证每位学生的课程通过门数已正确填入。（10 分）

USE 你的数据库名;
GO
-- 在学生表s新增pass_count字段，记录通过课程数量
ALTER TABLE s
ADD pass_count INT DEFAULT 0;
GO
-- 如果存储过程存在先删除
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'CalcPassCount')
DROP PROCEDURE CalcPassCount;
GO
CREATE PROCEDURE CalcPassCount
AS
BEGIN
SET NOCOUNT ON;
-- 声明变量：存储学号
DECLARE @stu_no NCHAR(11);
-- 声明游标：遍历s表所有学生学号
DECLARE stu_cur CURSOR
FOR SELECT studentno FROM s;

-- 打开游标
OPEN stu_cur;
FETCH NEXT FROM stu_cur INTO @stu_no;

-- 循环读取每个学生
WHILE @@FETCH_STATUS = 0
BEGIN
-- 计算当前学生final>=60的课程门数，更新到s表pass_count
UPDATE s
SET pass_count = (
SELECT COUNT(*) FROM sc
WHERE sc.studentno = s.studentno AND final >= 60
)
WHERE studentno = @stu_no;

-- 读取下一位学生
FETCH NEXT FROM stu_cur INTO @stu_no;
END

-- 释放游标资源
CLOSE stu_cur;
DEALLOCATE stu_cur;
END
GO
EXEC CalcPassCount;
GO
-- 查询学生学号、姓名、通过课程门数验证
SELECT studentno AS 学号, sname AS 姓名, pass_count AS 课程通过门数
FROM s;
GO