SQL Server 2019 数据表的操作 —语法详解与实战案例
一、SQL Server 2019 数据库对象概览
在SQL Server中，数据库对象包括：
| 对象类型 | 说明 |
|---|---|
| 表（Table） | 存储数据的核心结构，由行和列组成 |
| 视图（View） | 虚拟表，基于SELECT语句的结果集 |
| 索引（Index） | 加速数据检索的结构 |
| 存储过程（Stored Procedure） | 预编译的T-SQL代码块 |
| 触发器（Trigger） | 在数据变更时自动执行的代码 |
| 函数（Function） | 返回值的可重用代码块 |
| 约束（Constraint） | 保证数据完整性的规则（主键、外键、唯一、检查、默认） |
⭐ 本章重点：表（Table）的创建、修改、管理
二、创建数据表（CREATE TABLE）
2.1 数据类型（Data Types）
▶ 常用数据类型分类：
| 类别 | 类型 | 说明 | 示例 |
|---|---|---|---|
| 整数型 | INT,BIGINT,SMALLINT,TINYINT | 存储整数 | INT（-2^31 ~ 2^31-1） |
| 浮点型 | FLOAT,REAL,DECIMAL(p,s),NUMERIC(p,s) | 存储小数 | DECIMAL(10,2)表示最多10位，2位小数 |
| 字符型 | CHAR(n),VARCHAR(n),NCHAR(n),NVARCHAR(n),TEXT,NTEXT（已弃用） | 存储字符串 | VARCHAR(50)最大50字符，变长；NVARCHAR支持Unicode |
| 日期时间型 | DATE,TIME,DATETIME,DATETIME2,SMALLDATETIME,DATETIMEOFFSET | 存储日期/时间 | DATETIME2(3)精确到毫秒 |
| 二进制型 | BINARY(n),VARBINARY(n),IMAGE（弃用） | 存储图片、文件等 | VARBINARY(MAX)最大2GB |
| 其他 | BIT,UNIQUEIDENTIFIER,XML,JSON（通过NVARCHAR存储） | 特殊用途 | BIT存储0/1/null；UNIQUEIDENTIFIER存GUID |
💡 推荐：
- 字符串用
NVARCHAR（支持中文）- 小数用
DECIMAL（精确计算，避免FLOAT误差）- 日期用
DATETIME2- 大文本/二进制用
VARCHAR(MAX)/VARBINARY(MAX)
2.2 使用对象资源管理器（SSMS图形界面）创建表
操作步骤：
- 连接数据库 → 展开目标数据库 → 右键“表” → “新建表”
- 在设计视图中输入列名、数据类型、是否允许NULL
- 设置主键：选中列 → 右键“设置主键”或点击工具栏钥匙图标
- 设置默认值、标识列（自增）、检查约束等（在列属性面板）
- 保存 → 输入表名（如
Students）→ 回车
✅ 适合初学者，但不利于版本控制和批量部署 → 推荐学习T-SQL脚本
2.3 使用 Transact-SQL 创建数据表（语法详解 + 案例）
▶ 基础语法：
CREATETABLE[schema_name.]table_name(column_name data_type[NULL|NOTNULL][IDENTITY(seed,increment)]-- 自增列[CONSTRAINTconstraint_name][DEFAULT|CHECK|UNIQUE|PRIMARYKEY|REFERENCES...],...[CONSTRAINTconstraint_namePRIMARYKEY(col1,col2...)],[CONSTRAINTconstraint_nameFOREIGNKEY(col)REFERENCESother_table(col)],...);📌 案例1：创建学生表（含主键、自增、默认值、非空约束）
-- 创建 Students 表CREATETABLEStudents(StudentIDINTIDENTITY(1,1)PRIMARYKEY,-- 自增主键，从1开始，步长1Name NVARCHAR(50)NOTNULL,-- 姓名，不允许为空GenderCHAR(1)CHECK(GenderIN('M','F')),-- 性别，只能是 M 或 FBirthDateDATE,-- 出生日期EnrollDate DATETIME2DEFAULTGETDATE(),-- 入学日期，默认当前时间ClassIDINTNULL-- 班级ID，允许为空（外键待后续添加）);GO-- ✅ 注释：-- IDENTITY(1,1) 表示自增，第一个1是起始值，第二个1是步长-- PRIMARY KEY 可直接写在列后（列级约束），也可单独写（表级约束）-- CHECK 约束限制取值范围-- DEFAULT 设置默认值，插入时可省略该列📌 案例2：创建课程表（含复合主键、唯一约束）
-- 创建 Courses 表CREATETABLECourses(CourseIDINTIDENTITY(100,10)PRIMARYKEY,-- 从100开始，步长10CourseCodeVARCHAR(10)NOTNULLUNIQUE,-- 课程代码，唯一CourseName NVARCHAR(100)NOTNULL,CreditHoursTINYINTCHECK(CreditHoursBETWEEN1AND6),-- 学分1~6Department NVARCHAR(50)DEFAULT'计算机系');GO-- 创建选课表（Enrollments），含复合主键和外键CREATETABLEEnrollments(StudentIDINTNOTNULL,CourseIDINTNOTNULL,GradeDECIMAL(5,2)CHECK(GradeBETWEEN0AND100ORGradeISNULL),-- 成绩0~100或未录入EnrollDateDATEDEFAULTGETDATE(),-- 表级约束：复合主键CONSTRAINTPK_EnrollmentsPRIMARYKEY(StudentID,CourseID),-- 表级约束：外键CONSTRAINTFK_Enrollments_StudentFOREIGNKEY(StudentID)REFERENCESStudents(StudentID),CONSTRAINTFK_Enrollments_CourseFOREIGNKEY(CourseID)REFERENCESCourses(CourseID));GO-- ✅ 注释：-- UNIQUE 约束确保 CourseCode 不重复-- 复合主键：多个列组合成主键（一个学生一门课只能选一次）-- 外键约束确保引用完整性（不能插入不存在的学生或课程）三、管理数据表（ALTER TABLE）
3.1 修改数据表的字段（列）
▶ 语法：
ALTERTABLEtable_name {ADDcolumn_name data_type[constraints]-- 添加列|DROPCOLUMNcolumn_name-- 删除列|ALTERCOLUMNcolumn_name new_data_type[NULL|NOTNULL]-- 修改列类型/空值约束};⚠️ 注意：
- 修改列类型时，若数据不兼容会失败
- 不能直接修改列名 → 需使用
sp_rename- 删除列会丢失数据！
📌 案例3：添加、修改、删除列
-- 1. 为 Students 表添加 Email 列ALTERTABLEStudentsADDEmail NVARCHAR(100)NULL;GO-- 2. 修改 Email 列为 NOT NULL，并添加默认值ALTERTABLEStudentsALTERCOLUMNEmail NVARCHAR(100)NOTNULL;ALTERTABLEStudentsADDCONSTRAINTDF_Students_EmailDEFAULT'noemail@example.com'FOREmail;GO-- 3. 添加备注列（允许空）ALTERTABLEStudentsADDRemarks NVARCHAR(500)NULL;GO-- 4. 删除 Remarks 列（谨慎！数据丢失！）ALTERTABLEStudentsDROPCOLUMNRemarks;GO-- 5. 修改列名（使用系统存储过程）EXECsp_rename'Students.Email','EmailAddress','COLUMN';GO-- ✅ 验证结构：EXECsp_columns'Students';-- 或：SELECTCOLUMN_NAME,DATA_TYPE,IS_NULLABLE,COLUMN_DEFAULTFROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_NAME='Students';3.2 修改数据表的约束
▶ 添加/删除约束语法：
-- 添加约束ALTERTABLEtable_nameADDCONSTRAINTconstraint_name constraint_definition;-- 删除约束ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name;📌 案例4：添加、删除检查约束和默认约束
-- 1. 为 Students 表添加年龄检查约束（假设添加 Age 列）ALTERTABLEStudentsADDAgeTINYINTNULL;GOALTERTABLEStudentsADDCONSTRAINTCK_Students_AgeCHECK(AgeBETWEEN15AND60);GO-- 2. 删除年龄检查约束ALTERTABLEStudentsDROPCONSTRAINTCK_Students_Age;GO-- 3. 添加默认约束（已演示）-- 4. 删除默认约束（需先知道约束名）-- 查看约束名：SELECTnameFROMsys.default_constraintsWHEREparent_object_id=OBJECT_ID('Students')ANDparent_column_id=COLUMNPROPERTY(OBJECT_ID('Students'),'EmailAddress','ColumnId');-- 假设查到约束名为：DF__Students__EmailA__3A81B905ALTERTABLEStudentsDROPCONSTRAINTDF__Students__EmailA__3A81B905;GO-- 5. 重新添加更好的默认约束ALTERTABLEStudentsADDCONSTRAINTDF_Students_EmailAddressDEFAULTN'未提供邮箱'FOREmailAddress;GO📌 案例5：添加外键约束（表已存在时）
-- 假设之前创建 Students 时未加外键，现在补加ALTERTABLEStudentsADDCONSTRAINTFK_Students_ClassFOREIGNKEY(ClassID)REFERENCESClasses(ClassID);-- ⚠️ 需先创建 Classes 表（假设已存在）3.3 查看表中有关信息
▶ 常用系统视图/存储过程：
| 方法 | 用途 |
|---|---|
sp_help 'table_name' | 显示表结构、约束、索引等 |
sp_columns 'table_name' | 显示列信息 |
INFORMATION_SCHEMA.COLUMNS | ANSI标准列信息 |
sys.columns,sys.objects,sys.types | 系统目录视图 |
sp_helpconstraint 'table_name' | 显示约束信息 |
📌 案例6：查看表结构与约束
-- 1. 查看 Students 表完整信息EXECsp_help'Students';GO-- 2. 只查看列EXECsp_columns'Students';GO-- 3. 查看约束EXECsp_helpconstraint'Students';GO-- 4. 查询系统视图（更灵活）SELECTt.nameASTableName,c.nameASColumnName,ty.nameASDataType,c.max_length,c.is_nullable,dc.definitionASDefaultDefinitionFROMsys.columnscJOINsys.tablestONc.object_id=t.object_idJOINsys.typestyONc.user_type_id=ty.user_type_idLEFTJOINsys.default_constraints dcONc.default_object_id=dc.object_idWHEREt.name='Students';GO3.4 删除数据表（DROP TABLE）
⚠️ 删除表将永久删除所有数据和结构！外键引用的表需先删除或解除外键！
▶ 语法：
DROPTABLE[IFEXISTS][schema_name.]table_name;📌 案例7：安全删除表（处理外键依赖）
-- 1. 先删除子表（有外键引用的表）DROPTABLEIFEXISTSEnrollments;GO-- 2. 再删除主表DROPTABLEIFEXISTSStudents;DROPTABLEIFEXISTSCourses;GO-- 或者：先删除外键约束，再删表-- ALTER TABLE Enrollments DROP CONSTRAINT FK_Enrollments_Student;-- ALTER TABLE Enrollments DROP CONSTRAINT FK_Enrollments_Course;-- DROP TABLE Enrollments;-- DROP TABLE Students;-- DROP TABLE Courses;四、综合性实战案例
🎯 案例8：学校教务系统完整建表脚本（含错误处理、注释）
USEmaster;GO-- 创建数据库（如不存在）IFDB_ID('SchoolDB')ISNULLBEGINCREATEDATABASESchoolDB;PRINT'✅ 数据库 SchoolDB 创建成功';ENDGOUSESchoolDB;GO-- ========== 1. 创建班级表 ==========IFOBJECT_ID('Classes','U')ISNOTNULLDROPTABLEClasses;GOCREATETABLEClasses(ClassIDINTIDENTITY(1,1)PRIMARYKEY,ClassName NVARCHAR(50)NOTNULLUNIQUE,-- 班级名称唯一GradeLevelTINYINTCHECK(GradeLevelBETWEEN1AND12),-- 年级1-12TeacherName NVARCHAR(50),CreatedDate DATETIME2DEFAULTGETDATE());GO-- ========== 2. 创建学生表 ==========IFOBJECT_ID('Students','U')ISNOTNULLDROPTABLEStudents;GOCREATETABLEStudents(StudentIDINTIDENTITY(1,1)PRIMARYKEY,StudentNoCHAR(10)NOTNULLUNIQUE,-- 学号，唯一Name NVARCHAR(50)NOTNULL,GenderCHAR(1)CHECK(GenderIN('M','F','U')),-- M男 F女 U未知BirthDateDATE,ClassIDINTNULL,Email NVARCHAR(100)NULL,PhoneVARCHAR(20)NULL,EnrollDate DATETIME2DEFAULTGETDATE(),-- 外键约束CONSTRAINTFK_Students_ClassFOREIGNKEY(ClassID)REFERENCESClasses(ClassID)ONDELETESETNULL-- 班级删除时，学生班级设为NULLONUPDATECASCADE-- 班级ID更新时，学生表同步更新);GO-- ========== 3. 创建课程表 ==========IFOBJECT_ID('Courses','U')ISNOTNULLDROPTABLECourses;GOCREATETABLECourses(CourseIDINTIDENTITY(100,1)PRIMARYKEY,CourseCodeVARCHAR(10)NOTNULLUNIQUE,CourseName NVARCHAR(100)NOTNULL,CreditHoursTINYINTDEFAULT3CHECK(CreditHoursBETWEEN1AND6),Description NVARCHAR(500)NULL);GO-- ========== 4. 创建选课表（多对多关系） ==========IFOBJECT_ID('Enrollments','U')ISNOTNULLDROPTABLEEnrollments;GOCREATETABLEEnrollments(EnrollmentIDBIGINTIDENTITY(1,1)PRIMARYKEY,-- 单列主键更常用StudentIDINTNOTNULL,CourseIDINTNOTNULL,GradeDECIMAL(5,2)NULLCHECK(GradeBETWEEN0AND100),Semester NVARCHAR(20)DEFAULT'2025春季学期',EnrollDateDATEDEFAULTGETDATE(),-- 唯一约束：一个学生同一门课只能选一次CONSTRAINTUQ_Enrollment_Student_CourseUNIQUE(StudentID,CourseID),-- 外键CONSTRAINTFK_Enrollments_StudentFOREIGNKEY(StudentID)REFERENCESStudents(StudentID)ONDELETECASCADE,-- 学生删除，选课记录也删除CONSTRAINTFK_Enrollments_CourseFOREIGNKEY(CourseID)REFERENCESCourses(CourseID)ONDELETECASCADE-- 课程删除，选课记录也删除);GO-- ========== 5. 插入测试数据 ==========INSERTINTOClasses(ClassName,GradeLevel,TeacherName)VALUES('高三(1)班',12,'张老师'),('高二(3)班',11,'李老师');INSERTINTOStudents(StudentNo,Name,Gender,BirthDate,ClassID,Email)VALUES('S20250001','王小明','M','2007-05-15',1,'xiaoming@example.com'),('S20250002','李小红','F','2008-03-22',2,'xiaohong@example.com');INSERTINTOCourses(CourseCode,CourseName,CreditHours)VALUES('CS101','计算机基础',3),('MATH201','高等数学',4);INSERTINTOEnrollments(StudentID,CourseID,Grade)VALUES(1,100,85.5),(1,101,92.0),(2,100,78.0);GO-- ========== 6. 查看表结构 ==========PRINT'========== 表结构信息 =========='EXECsp_help'Students';EXECsp_help'Enrollments';GO-- ========== 7. 修改表：添加新列 ==========ALTERTABLEStudentsADDAddress NVARCHAR(200)NULL;GO-- 设置默认值ALTERTABLEStudentsADDCONSTRAINTDF_Students_AddressDEFAULT'地址未填写'FORAddress;GO-- ========== 8. 查询数据验证 ==========SELECTs.NameAS学生姓名,c.ClassNameAS班级,co.CourseNameAS课程,e.GradeAS成绩FROMEnrollments eJOINStudents sONe.StudentID=s.StudentIDJOINClasses cONs.ClassID=c.ClassIDJOINCourses coONe.CourseID=co.CourseID;GO-- ========== 9. 清理：删除所有表（按依赖顺序） ==========DROPTABLEIFEXISTSEnrollments;DROPTABLEIFEXISTSStudents;DROPTABLEIFEXISTSCourses;DROPTABLEIFEXISTSClasses;GOPRINT'✅ 综合案例执行完成！所有表已清理。';🎯 案例9：动态建表模板（带参数化和错误处理）
-- 创建一个可重用的建表脚本模板USESchoolDB;GODECLARE@TableNameSYSNAME='Products';DECLARE@SQLNVARCHAR(MAX);-- 如果表存在则删除IFOBJECT_ID(@TableName,'U')ISNOTNULLBEGINSET@SQL='DROP TABLE '+QUOTENAME(@TableName);EXECsp_executesql@SQL;PRINT'🗑️ 旧表 '+@TableName+' 已删除';END-- 创建新表SET@SQL=' CREATE TABLE '+QUOTENAME(@TableName)+' ( ProductID INT IDENTITY(1,1) PRIMARY KEY, ProductName NVARCHAR(100) NOT NULL, Price DECIMAL(10,2) NOT NULL CHECK (Price > 0), Category NVARCHAR(50) DEFAULT ''未分类'', CreateTime DATETIME2 DEFAULT GETDATE() );';BEGINTRYEXECsp_executesql@SQL;PRINT'✅ 表 '+@TableName+' 创建成功！';-- 插入测试数据INSERTINTOProducts(ProductName,Price)VALUES('笔记本电脑',5999.00),('无线鼠标',89.50);PRINT'📄 插入测试数据完成。';ENDTRYBEGINCATCHPRINT'❌ 创建失败：'+ERROR_MESSAGE();ENDCATCH GO✅ 本章核心语法速查表
| 操作 | 语法 | 说明 |
|---|---|---|
| 创建表 | CREATE TABLE ... (列定义, 约束...) | 支持主键、外键、默认、检查等约束 |
| 添加列 | ALTER TABLE ... ADD column ... | 可加约束 |
| 修改列 | ALTER TABLE ... ALTER COLUMN ... | 改类型或NULL/NOT NULL |
| 改列名 | EXEC sp_rename '表.旧列', '新列', 'COLUMN' | 系统存储过程 |
| 添加约束 | ALTER TABLE ... ADD CONSTRAINT ... | 主键、外键、检查、默认 |
| 删除约束 | ALTER TABLE ... DROP CONSTRAINT 名称 | 需先查约束名 |
| 删除列 | ALTER TABLE ... DROP COLUMN 列名 | 数据丢失！ |
| 查看结构 | sp_help '表名' | 最常用 |
| 删除表 | DROP TABLE [IF EXISTS] 表名 | 外键依赖需先处理 |
📌学习建议：
- 动手执行所有案例，观察每一步结果
- 学会使用
sp_help和系统视图查看元数据 - 修改表结构前先备份！
- 生产环境避免直接删列/改类型 → 建议新建表迁移数据
- 外键的
ON DELETE/UPDATE行为要根据业务谨慎选择
📘 本章掌握后，你已具备独立设计和管理数据库表结构的能力，为后续数据操作（INSERT/UPDATE/DELETE）、查询（SELECT）、索引优化打下坚实基础！
