SQL Server 2019 数据表的操作 —语法详解与实战案例一、SQL Server 2019 数据库对象概览在SQL Server中数据库对象包括对象类型说明表Table存储数据的核心结构由行和列组成视图View虚拟表基于SELECT语句的结果集索引Index加速数据检索的结构存储过程Stored Procedure预编译的T-SQL代码块触发器Trigger在数据变更时自动执行的代码函数Function返回值的可重用代码块约束Constraint保证数据完整性的规则主键、外键、唯一、检查、默认⭐ 本章重点表Table的创建、修改、管理二、创建数据表CREATE TABLE2.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/nullUNIQUEIDENTIFIER存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 TABLE3.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_EmailDEFAULTnoemailexample.comFOREmail;GO-- 3. 添加备注列允许空ALTERTABLEStudentsADDRemarks NVARCHAR(500)NULL;GO-- 4. 删除 Remarks 列谨慎数据丢失ALTERTABLEStudentsDROPCOLUMNRemarks;GO-- 5. 修改列名使用系统存储过程EXECsp_renameStudents.Email,EmailAddress,COLUMN;GO-- ✅ 验证结构EXECsp_columnsStudents;-- 或SELECTCOLUMN_NAME,DATA_TYPE,IS_NULLABLE,COLUMN_DEFAULTFROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_NAMEStudents;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_idOBJECT_ID(Students)ANDparent_column_idCOLUMNPROPERTY(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.COLUMNSANSI标准列信息sys.columns,sys.objects,sys.types系统目录视图sp_helpconstraint table_name显示约束信息 案例6查看表结构与约束-- 1. 查看 Students 表完整信息EXECsp_helpStudents;GO-- 2. 只查看列EXECsp_columnsStudents;GO-- 3. 查看约束EXECsp_helpconstraintStudents;GO-- 4. 查询系统视图更灵活SELECTt.nameASTableName,c.nameASColumnName,ty.nameASDataType,c.max_length,c.is_nullable,dc.definitionASDefaultDefinitionFROMsys.columnscJOINsys.tablestONc.object_idt.object_idJOINsys.typestyONc.user_type_idty.user_type_idLEFTJOINsys.default_constraints dcONc.default_object_iddc.object_idWHEREt.nameStudents;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)DEFAULT2025春季学期,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,xiaomingexample.com),(S20250002,李小红,F,2008-03-22,2,xiaohongexample.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_helpStudents;EXECsp_helpEnrollments;GO-- 7. 修改表添加新列 ALTERTABLEStudentsADDAddress NVARCHAR(200)NULL;GO-- 设置默认值ALTERTABLEStudentsADDCONSTRAINTDF_Students_AddressDEFAULT地址未填写FORAddress;GO-- 8. 查询数据验证 SELECTs.NameAS学生姓名,c.ClassNameAS班级,co.CourseNameAS课程,e.GradeAS成绩FROMEnrollments eJOINStudents sONe.StudentIDs.StudentIDJOINClasses cONs.ClassIDc.ClassIDJOINCourses coONe.CourseIDco.CourseID;GO-- 9. 清理删除所有表按依赖顺序 DROPTABLEIFEXISTSEnrollments;DROPTABLEIFEXISTSStudents;DROPTABLEIFEXISTSCourses;DROPTABLEIFEXISTSClasses;GOPRINT✅ 综合案例执行完成所有表已清理。; 案例9动态建表模板带参数化和错误处理-- 创建一个可重用的建表脚本模板USESchoolDB;GODECLARETableNameSYSNAMEProducts;DECLARESQLNVARCHAR(MAX);-- 如果表存在则删除IFOBJECT_ID(TableName,U)ISNOTNULLBEGINSETSQLDROP TABLE QUOTENAME(TableName);EXECsp_executesqlSQL;PRINT️ 旧表 TableName 已删除;END-- 创建新表SETSQL 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_executesqlSQL;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、索引优化打下坚实基础