用实验课速通SQLSever期末考点一

本学期开设“数据库原理与应用”实验课，要求使用SQL Sever 2017因此以下所有内容均以该版本为准。实验环境：操作系统Windows10/11，工具SQL Server Management Studio（SSMS）17 及以上版本。

一、实验内容

SQL Server 2017 环境下“教学信息管理系统”数据库与核心数据表的 DDL（数据定义语言）实现。

二、实验目的

1. 掌握 SQL Server 中 DDL 核心语句的用法，包括CREATE DATABASE（创建数据库）、CREATE TABLE（创建数据表）；

2. 理解数据库文件（主数据文件、日志文件）的配置参数（初始大小、增长幅度、存储路径）；

3. 学会为数据表定义合理的字段、数据类型及完整性约束（主键、外键、非空、CHECK、DEFAULT、UNIQUE）；
   基于教学信息管理系统业务场景，完成数据库与核心表（学生表、教师表、课程表、选课表）的 DDL 实现，为后续数据操作奠定基础。

三、实验任务

使用CREATE DATABASE创建 “教学信息管理系统” 数据库

• 数据库名称：TeachingSysDB；

• 配置主数据文件（.mdf）：逻辑名TeachingSysDB_Data，初始大小 15MB，最大大小无限制，增长幅度 5MB；

• 配置日志文件（.ldf）：逻辑名TeachingSysDB_Log，初始大小 8MB，最大大小 100MB，增长幅度 3MB；
  指定存储路径（需与 SQL Server 实例的数据文件默认路径匹配，避免权限问题）。

CREATE DATABASE TeachingSysDB
ON PRIMARY
(
NAME='TeachingSysDB_Data',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TeachingSysDB_Data.mdf',
SIZE=15MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=5MB
)
LOG ON
(
NAME='TeachingSysDB_Log',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TeachingSysDB_Log.ldf',
SIZE=8MB,
MAXSIZE=100MB,
FILEGROWTH=3MB
);
GO
-- 验证数据库创建结果：查询系统表sys.databases，确认TeachingSysDB存在
SELECT name AS '数据库名称', database_id AS '数据库ID', create_date AS '创建时间'
FROM sys.databases
WHERE name = 'TeachingSysDB';
GO

使用CREATE TABLE创建核心数据表

基于教学信息管理系统业务，创建 4 张核心表，需明确字段、数据类型及完整性约束：

• 学生表（Student）：存储学生基本信息，含学号（主键）、姓名（非空）、性别（仅 “男 / 女”）、年龄（15-30 岁）、所在院系；

• 教师表（Teacher）：存储教师基本信息，含教师编号（主键）、姓名（非空）、性别（仅 “男 / 女”）、职称（默认 “讲师”）、所在院系（非空）；

• 课程表（Course）：存储课程基本信息，含课程编号（主键）、课程名称（非空且唯一）、学分（1-6 分）、授课教师编号（外键，关联教师表）；

• 选课表（SC）：存储学生选课及成绩信息，含学号（外键，关联学生表）、课程编号（外键，关联课程表）、成绩（0-100 分，可空），复合主键（学号 + 课程编号）。

USE TeachingSysDB;
GO
CREATE TABLE Student
(
Sno CHAR(10) PRIMARY KEY,  -- 学号：主键（唯一标识学生，如2023010101）
    Sname VARCHAR(20) NOT NULL,  -- 姓名：非空（学生必须有姓名）
    Ssex CHAR(2) CHECK (Ssex IN ('男', '女')),  -- 性别：仅允许“男”或“女”
    Sage INT CHECK (Sage BETWEEN 15 AND 30),  -- 年龄：15-30岁（符合本科学生年龄范围）
    Sdept VARCHAR(30)  -- 所在院系：如“计算机科学与技术学院”“数学与统计学院”
);
GO
CREATE TABLE Teacher
(
Tno CHAR(8) PRIMARY KEY,  -- 教师编号：主键（如T2023001）
    Tname VARCHAR(20) NOT NULL,  -- 姓名：非空
    Tsex CHAR(2) CHECK (Tsex IN ('男', '女')),  -- 性别约束
    Ttitle VARCHAR(20) DEFAULT '讲师',  -- 职称：默认值“讲师”（未指定时自动填充）
    Tdept VARCHAR(30) NOT NULL  -- 所在院系：非空（教师必须归属院系）
);
GO
CREATE TABLE Course
(
Cno CHAR(6) PRIMARY KEY,  -- 课程编号：主键（如CS101，CS代表计算机类课程）
    Cname VARCHAR(30) NOT NULL UNIQUE,  -- 课程名称：非空且唯一（避免重复课程）
    Ccredit INT CHECK (Ccredit BETWEEN 1 AND 6),  -- 学分：1-6分（符合高校学分设置）
    Tno CHAR(8) REFERENCES Teacher(Tno),  -- 授课教师编号：外键（关联Teacher表的Tno，确保教师存在）
    CONSTRAINT CK_Course_Ccredit CHECK (Ccredit > 0)  -- 自定义约束名：确保学分大于0（与BETWEEN约束互补，增强可读性）
);
GO
CREATE TABLE SC
(
Sno CHAR(10) NOT NULL,  -- 学号：外键（关联Student表）
    Cno CHAR(6) NOT NULL,  -- 课程编号：外键（关联Course表）
    Grade INT CHECK (Grade BETWEEN 0 AND 100),  -- 成绩：0-100分（可空，未考试时为NULL）
    -- 复合主键：学号+课程编号（确保1名学生不可重复选同一门课）
    PRIMARY KEY (Sno, Cno),
    -- 外键约束：关联Student表的Sno
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    -- 外键约束：关联Course表的Cno
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
GO
-- 验证表创建结果：查询当前数据库中的所有表
SELECT name AS '数据表名称', create_date AS '创建时间'
FROM sys.tables;
GO
-- 查看单表结构详情（以Student表为例，含字段、数据类型、约束）
EXEC sp_help 'Student';
GO

验证DDL执行结果

通过 SSMS 查看TeachingSysDB数据库的文件配置（大小、路径、增长方式）；查看 4 张核心表的结构（字段名、数据类型、约束类型），验证约束有效性（如插入不符合 CHECK 约束的数据是否报错）。

-- 验证约束：插入不符合规则的数据，观察是否报错（DDL约束生效验证）
USE TeachingSysDB;
GO
-- 1. 验证Student表的CHECK约束（性别输入“未知”，应报错）
INSERT INTO Student(Sno, Sname, Ssex, Sage, Sdept)
VALUES ('2023010101', '张三', '未知', 20, '计算机科学与技术学院');  -- 报错：违反CHECK约束
GO
-- 2. 验证Teacher表的DEFAULT约束（不指定职称，自动填充“讲师”）
INSERT INTO Teacher(Tno, Tname, Tsex, Tdept)
VALUES ('T2023001', '李四', '男', '数学与统计学院');  -- 职称字段自动填充“讲师”
SELECT Tno, Tname, Ttitle FROM Teacher WHERE Tno = 'T2023001';  -- 查看结果
GO
-- 3. 验证Course表的UNIQUE约束（插入重复课程名称，应报错）
INSERT INTO Course(Cno, Cname, Ccredit, Tno)
VALUES ('CS101', '数据库原理', 4, 'T2023001');
INSERT INTO Course(Cno, Cname, Ccredit, Tno)
VALUES ('CS102', '数据库原理', 3, 'T2023001');  -- 报错：违反UNIQUE约束
GO
-- 4. 验证SC表的外键约束（插入不存在的学号，应报错）
INSERT INTO SC(Sno, Cno, Grade)
VALUES ('2023010199', 'CS101', 85);  -- 学号2023010199未在Student表中，报错：违反外键约束
GO

四、实验结果与分析

1. 数据库创建结果：在 SSMS “对象资源管理器” 中展开 “数据库” 节点，可看到TeachingSysDB已成功创建；右键点击该数据库→“属性”→“文件”，能查看主数据文件（TeachingSysDB_Data）和日志文件（TeachingSysDB_Log）的配置：初始大小、增长幅度、存储路径均与CREATE DATABASE语句一致，无配置偏差。

2. 数据表创建结果：展开TeachingSysDB的 “表” 节点，可看到dbo.Student、dbo.Teacher、dbo.Course、dbo.SC四张表；通过sp_help '表名'命令查询，确认各表字段的 “数据类型”“是否允许空”“约束类型” 均符合设计要求（如Student.Sname的 “是否允许空” 为 “否”，Teacher.Ttitle的 “默认值” 为 “讲师”）。

3. 约束有效性结果：插入不符合CHECK约束的数据（如学生性别 “未知”）时，SQL Server 抛出 “违反 CHECK 约束” 错误；未指定DEFAULT约束字段（如教师职称）时，数据自动填充默认值 “讲师”；插入违反UNIQUE约束（重复课程名称）或外键约束（无效学号）的数据时，均触发报错，证明 DDL 定义的完整性约束有效，能保障数据合法性。

五、实验总结

1. 本次实验掌握了 SQL Server 2017 中 DDL 核心语句的用法：CREATE DATABASE用于创建数据库并配置文件参数，CREATE TABLE用于定义表结构及完整性约束，理解了 “逻辑名”、“物理路径”、“自动增长” 等参数的实际意义；

2. 明确了完整性约束的设计原则：主键确保实体唯一性，外键确保表间关联一致性，CHECK/DEFAULT/UNIQUE约束确保字段值合法性，这些约束是后续数据操作（增删改）的基础；

3. 遇到的问题：创建数据库时物理路径错误（与 SQL Server 实例路径不匹配），通过 “SSMS→数据库→右键属性→文件” 查看默认路径解决；创建 Course 表时外键Tno关联的 Teacher 表未先创建，导致报错，调整表创建顺序（先创建父表 Teacher，再创建子表 Course）后解决。