1. SQL Server 2019数据表操作基础概念在数据库管理系统中数据表是最基础也是最重要的存储单元。SQL Server 2019作为微软最新的关系型数据库管理系统在数据表操作方面提供了丰富的功能和语法支持。理解数据表的基本概念是掌握SQL Server的第一步。数据表由行和列组成每一列代表一个特定的数据属性每一行则代表一条完整的记录。在SQL Server中创建和管理数据表需要遵循特定的语法规则和最佳实践。与早期版本相比SQL Server 2019在表操作方面引入了多项改进包括更高效的内存优化表、更灵活的JSON支持以及增强的临时表功能。注意在实际工作中设计数据表时需要考虑数据类型的选择、约束条件的设置以及索引策略这些因素会直接影响数据库的性能和可维护性。1.1 数据表的核心组件一个完整的数据表包含以下几个关键组成部分表名遵循SQL Server的命名规则通常使用有意义的英文单词或缩写列定义包括列名、数据类型、是否允许NULL值等属性约束如主键、外键、唯一性约束、检查约束等索引提高查询性能的数据结构触发器在特定表事件发生时自动执行的代码块1.2 SQL Server 2019中的表类型SQL Server 2019支持多种表类型每种类型适用于不同的场景普通表最基本的表类型数据持久化存储在磁盘上临时表仅在当前会话或批处理中存在的表内存优化表完全驻留在内存中的高性能表系统表SQL Server内部使用的表存储元数据信息分区表将大型表数据分散到不同文件组的表2. 数据表的创建与基本操作创建数据表是数据库开发的第一步SQL Server提供了CREATE TABLE语句来实现这一功能。掌握正确的表创建语法和技巧对于构建高效的数据库结构至关重要。2.1 CREATE TABLE语法详解最基本的创建表语法如下CREATE TABLE [schema_name.]table_name ( column1 datatype [constraints], column2 datatype [constraints], ... [table_constraints] );让我们通过一个实际的员工表示例来说明CREATE TABLE Employees ( EmployeeID INT IDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, BirthDate DATE, HireDate DATE NOT NULL, Email NVARCHAR(100) UNIQUE, Salary DECIMAL(10,2) CHECK (Salary 0), DepartmentID INT FOREIGN KEY REFERENCES Departments(DepartmentID) );在这个例子中我们创建了一个包含多个约束条件的员工表。IDENTITY属性表示自增列PRIMARY KEY设置主键NOT NULL确保字段必须有值UNIQUE保证邮箱唯一CHECK约束验证薪水必须大于0FOREIGN KEY建立与部门表的关系。2.2 表创建的高级选项SQL Server 2019提供了更多高级表创建选项文件组指定将表分配到特定文件组CREATE TABLE LargeTable (...) ON [PRIMARY];分区方案为大型表设置分区CREATE TABLE PartitionedTable (...) ON PartitionScheme(PartitionColumn);内存优化表创建高性能内存表CREATE TABLE InMemoryTable (...) WITH (MEMORY_OPTIMIZED ON);计算列基于其他列的值自动计算CREATE TABLE Products ( Price DECIMAL(10,2), TaxRate DECIMAL(5,2), TotalPrice AS (Price * (1 TaxRate)) PERSISTED );提示在SQL Server 2019中内存优化表性能显著提升特别适合高并发的OLTP场景。但需要注意内存使用情况避免过度消耗服务器资源。2.3 修改表结构随着业务需求变化经常需要修改已有表的结构。ALTER TABLE语句提供了这种灵活性添加列ALTER TABLE Employees ADD MiddleName NVARCHAR(50) NULL;修改列ALTER TABLE Employees ALTER COLUMN Email NVARCHAR(150);删除列ALTER TABLE Employees DROP COLUMN MiddleName;添加约束ALTER TABLE Employees ADD CONSTRAINT DF_HireDate DEFAULT GETDATE() FOR HireDate;删除约束ALTER TABLE Employees DROP CONSTRAINT DF_HireDate;在实际操作中修改生产环境的表结构需要特别谨慎尤其是当表中有大量数据时。建议先在测试环境验证变更并确保有完整的备份方案。3. 数据表的数据操作语言(DML)创建表结构后下一步是学习如何操作表中的数据。SQL提供了四种基本的DML操作INSERT、UPDATE、DELETE和SELECT。掌握这些操作是使用SQL Server的基础。3.1 插入数据(INSERT)向表中添加新记录使用INSERT语句-- 基本插入 INSERT INTO Employees (FirstName, LastName, HireDate, Email) VALUES (张, 三, 2023-01-15, zhangsanexample.com); -- 多行插入 INSERT INTO Employees (FirstName, LastName, HireDate) VALUES (李, 四, 2023-02-20), (王, 五, 2023-03-10); -- 从其他表插入 INSERT INTO NewEmployees (EmployeeID, FullName) SELECT EmployeeID, FirstName LastName FROM Employees WHERE HireDate 2023-01-01;SQL Server 2019增强了INSERT语句的功能特别是对JSON数据的支持-- 从JSON插入数据 INSERT INTO Employees (FirstName, LastName, Email) SELECT FirstName, LastName, Email FROM OPENJSON(jsonData) WITH ( FirstName NVARCHAR(50), LastName NVARCHAR(50), Email NVARCHAR(100) );3.2 更新数据(UPDATE)修改现有记录使用UPDATE语句-- 基本更新 UPDATE Employees SET Salary Salary * 1.1 WHERE DepartmentID 3; -- 使用JOIN更新 UPDATE e SET e.Salary e.Salary * 1.05 FROM Employees e JOIN Departments d ON e.DepartmentID d.DepartmentID WHERE d.DepartmentName 销售部; -- 使用子查询更新 UPDATE Employees SET ManagerID ( SELECT EmployeeID FROM Employees WHERE LastName 王 AND FirstName 总监 ) WHERE DepartmentID 5;在SQL Server 2019中UPDATE语句的性能得到了优化特别是对于大型表的更新操作。但更新大量数据时仍需注意锁定和日志问题。3.3 删除数据(DELETE)从表中移除记录使用DELETE语句-- 基本删除 DELETE FROM Employees WHERE EmployeeID 100; -- 使用JOIN删除 DELETE e FROM Employees e JOIN Departments d ON e.DepartmentID d.DepartmentID WHERE d.DepartmentName 已撤销部门; -- 清空表数据 TRUNCATE TABLE TempEmployees;重要区别DELETE是逐行删除并记录日志可以带WHERE条件TRUNCATE TABLE是直接释放数据页效率更高但不记录单个行删除且会重置自增列。3.4 事务处理在修改数据时使用事务可以确保操作的原子性BEGIN TRANSACTION; BEGIN TRY -- 一系列操作 UPDATE Accounts SET Balance Balance - 100 WHERE AccountID 1; UPDATE Accounts SET Balance Balance 100 WHERE AccountID 2; -- 如果执行到这里没有错误提交事务 COMMIT TRANSACTION; END TRY BEGIN CATCH -- 如果发生错误回滚事务 ROLLBACK TRANSACTION; -- 报告错误 SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; END CATCHSQL Server 2019改进了事务处理机制特别是对内存优化表的事务支持更加完善。4. 数据查询与高级表操作查询是数据库操作中最频繁的部分SQL Server提供了强大的SELECT语句来检索数据。同时掌握视图、索引等高级表操作技术可以显著提升查询效率。4.1 基本查询(SELECT)-- 查询所有列 SELECT * FROM Employees; -- 查询特定列 SELECT EmployeeID, FirstName, LastName FROM Employees; -- 使用别名 SELECT e.EmployeeID AS ID, e.FirstName e.LastName AS FullName, d.DepartmentName FROM Employees e JOIN Departments d ON e.DepartmentID d.DepartmentID; -- 条件查询 SELECT * FROM Employees WHERE Salary 5000 AND DepartmentID 3; -- 排序 SELECT * FROM Employees ORDER BY LastName ASC, FirstName ASC; -- 分组统计 SELECT DepartmentID, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalary FROM Employees GROUP BY DepartmentID HAVING COUNT(*) 5;4.2 高级查询技术SQL Server 2019引入了多项查询增强功能窗口函数强大的分析功能SELECT EmployeeID, FirstName, LastName, Salary, AVG(Salary) OVER (PARTITION BY DepartmentID) AS DeptAvgSalary, Salary - AVG(Salary) OVER (PARTITION BY DepartmentID) AS DiffFromAvg FROM Employees;JSON支持直接查询JSON数据SELECT EmployeeID, JSON_VALUE(EmployeeInfo, $.Skills[0]) AS PrimarySkill FROM Employees WHERE JSON_VALUE(EmployeeInfo, $.IsActive) true;图形查询处理关系型数据-- 创建图形表 CREATE TABLE Persons ( ID INT PRIMARY KEY, Name NVARCHAR(100) ) AS NODE; CREATE TABLE Friends ( Since DATE ) AS EDGE; -- 图形查询 SELECT p1.Name AS Person1, p2.Name AS Person2 FROM Persons p1, Friends, Persons p2 WHERE MATCH(p1-(Friends)-p2);4.3 索引优化合理的索引设计对查询性能至关重要-- 创建索引 CREATE INDEX IX_Employees_DepartmentID ON Employees(DepartmentID); -- 包含列索引 CREATE INDEX IX_Employees_Name ON Employees(LastName, FirstName) INCLUDE (Email, Phone); -- 筛选索引 CREATE INDEX IX_ActiveEmployees ON Employees(DepartmentID) WHERE IsActive 1; -- 列存储索引(适合分析查询) CREATE COLUMNSTORE INDEX CSI_Orders ON OrderDetails(OrderID, ProductID, Quantity, Price);SQL Server 2019的索引优化包括改进的统计信息自动更新更智能的查询计划选择内存优化表的索引增强列存储索引压缩率提升4.4 视图与表变量视图和表变量提供了数据访问的抽象层-- 创建视图 CREATE VIEW vw_EmployeeDetails AS SELECT e.EmployeeID, e.FirstName e.LastName AS FullName, d.DepartmentName, e.Salary FROM Employees e JOIN Departments d ON e.DepartmentID d.DepartmentID; -- 使用表变量 DECLARE TopEmployees TABLE ( EmployeeID INT, FullName NVARCHAR(101), Salary DECIMAL(10,2) ); INSERT INTO TopEmployees SELECT TOP 10 EmployeeID, FirstName LastName, Salary FROM Employees ORDER BY Salary DESC;5. 实战案例员工管理系统表操作通过一个完整的员工管理系统案例我们将综合运用前面学到的各种表操作技术。这个案例包括表设计、数据操作、查询优化等实际场景。5.1 数据库架构设计首先创建完整的数据库架构-- 创建数据库 CREATE DATABASE CompanyHR; GO USE CompanyHR; GO -- 部门表 CREATE TABLE Departments ( DepartmentID INT IDENTITY(1,1) PRIMARY KEY, DepartmentName NVARCHAR(50) NOT NULL, Location NVARCHAR(100), Budget DECIMAL(15,2), EstablishedDate DATE DEFAULT GETDATE() ); -- 职位表 CREATE TABLE Positions ( PositionID INT IDENTITY(1,1) PRIMARY KEY, Title NVARCHAR(50) NOT NULL, MinSalary DECIMAL(10,2), MaxSalary DECIMAL(10,2), Level INT CHECK (Level BETWEEN 1 AND 10) ); -- 员工表 CREATE TABLE Employees ( EmployeeID INT IDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, BirthDate DATE, HireDate DATE NOT NULL DEFAULT GETDATE(), Email NVARCHAR(100) UNIQUE, Phone NVARCHAR(20), Salary DECIMAL(10,2) CHECK (Salary 0), DepartmentID INT FOREIGN KEY REFERENCES Departments(DepartmentID), PositionID INT FOREIGN KEY REFERENCES Positions(PositionID), ManagerID INT FOREIGN KEY REFERENCES Employees(EmployeeID), IsActive BIT DEFAULT 1, LastUpdated DATETIME DEFAULT GETDATE(), CONSTRAINT CHK_Salary CHECK (Salary 1000) ); -- 员工技能表(多对多关系) CREATE TABLE Skills ( SkillID INT IDENTITY(1,1) PRIMARY KEY, SkillName NVARCHAR(50) NOT NULL, Description NVARCHAR(200) ); CREATE TABLE EmployeeSkills ( EmployeeID INT NOT NULL, SkillID INT NOT NULL, ProficiencyLevel INT CHECK (ProficiencyLevel BETWEEN 1 AND 5), AcquiredDate DATE DEFAULT GETDATE(), PRIMARY KEY (EmployeeID, SkillID), FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), FOREIGN KEY (SkillID) REFERENCES Skills(SkillID) ); -- 薪资历史表 CREATE TABLE SalaryHistory ( HistoryID INT IDENTITY(1,1) PRIMARY KEY, EmployeeID INT NOT NULL, OldSalary DECIMAL(10,2), NewSalary DECIMAL(10,2), ChangeDate DATETIME DEFAULT GETDATE(), ChangedBy NVARCHAR(100), Reason NVARCHAR(200), FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) );5.2 示例数据插入向各表插入示例数据-- 插入部门数据 INSERT INTO Departments (DepartmentName, Location, Budget) VALUES (人力资源部, 3楼东区, 500000), (财务部, 2楼西区, 800000), (技术部, 5楼南区, 1200000), (销售部, 1楼北区, 900000), (市场部, 4楼中区, 600000); -- 插入职位数据 INSERT INTO Positions (Title, MinSalary, MaxSalary, Level) VALUES (实习生, 1000, 2000, 1), (助理, 2500, 4000, 2), (专员, 4000, 6000, 3), (主管, 6000, 9000, 4), (经理, 9000, 15000, 5), (总监, 15000, 25000, 6), (副总裁, 25000, 40000, 7), (总裁, 40000, 60000, 8); -- 插入员工数据 INSERT INTO Employees (FirstName, LastName, BirthDate, Email, Salary, DepartmentID, PositionID) VALUES (张, 伟, 1980-05-15, zhangweicompany.com, 15000, 3, 5), (王, 芳, 1985-08-22, wangfangcompany.com, 12000, 1, 5), (李, 强, 1990-03-10, liqiangcompany.com, 8000, 3, 4), (赵, 敏, 1992-11-05, zhaomincompany.com, 6000, 4, 3), (刘, 洋, 1995-07-30, liuyangcompany.com, 4500, 2, 3), (陈, 晨, 1993-09-18, chenchencompany.com, 5000, 5, 3), (杨, 光, 1988-12-25, yangguangcompany.com, 18000, 3, 6), (周, 杰, 1991-04-12, zhoujiecompany.com, 7000, 4, 4); -- 更新经理关系 UPDATE Employees SET ManagerID 7 WHERE EmployeeID IN (1, 3); UPDATE Employees SET ManagerID 2 WHERE EmployeeID 5; UPDATE Employees SET ManagerID 1 WHERE EmployeeID IN (4, 6, 8); -- 插入技能数据 INSERT INTO Skills (SkillName, Description) VALUES (C#, 微软.NET平台编程语言), (SQL, 结构化查询语言), (JavaScript, 前端开发语言), (项目管理, 项目规划与执行能力), (沟通技巧, 有效沟通与表达能力), (数据分析, 数据处理与分析能力); -- 插入员工技能关联数据 INSERT INTO EmployeeSkills (EmployeeID, SkillID, ProficiencyLevel) VALUES (1, 1, 5), (1, 2, 4), (1, 6, 3), (2, 4, 5), (2, 5, 5), (3, 1, 4), (3, 3, 3), (4, 5, 4), (4, 6, 3), (5, 2, 4), (5, 6, 5), (7, 1, 5), (7, 2, 5), (7, 4, 5);5.3 实用查询示例基于这个员工数据库我们可以执行各种实用查询部门员工统计SELECT d.DepartmentName, COUNT(e.EmployeeID) AS EmployeeCount, AVG(e.Salary) AS AvgSalary, MAX(e.Salary) AS MaxSalary, MIN(e.Salary) AS MinSalary FROM Departments d LEFT JOIN Employees e ON d.DepartmentID e.DepartmentID GROUP BY d.DepartmentName ORDER BY EmployeeCount DESC;员工技能分析SELECT e.FirstName e.LastName AS EmployeeName, STRING_AGG(s.SkillName, , ) AS Skills, COUNT(es.SkillID) AS SkillCount FROM Employees e LEFT JOIN EmployeeSkills es ON e.EmployeeID es.EmployeeID LEFT JOIN Skills s ON es.SkillID s.SkillID GROUP BY e.EmployeeID, e.FirstName, e.LastName ORDER BY SkillCount DESC;薪资调整历史追踪CREATE TRIGGER trg_SalaryChange ON Employees AFTER UPDATE AS BEGIN IF UPDATE(Salary) BEGIN INSERT INTO SalaryHistory (EmployeeID, OldSalary, NewSalary, ChangedBy, Reason) SELECT i.EmployeeID, d.Salary, i.Salary, SYSTEM_USER, 定期调薪 FROM inserted i JOIN deleted d ON i.EmployeeID d.EmployeeID WHERE i.Salary d.Salary; END END;员工层级关系查询WITH EmployeeHierarchy AS ( -- 基础查询(顶级管理者) SELECT EmployeeID, FirstName LastName AS EmployeeName, ManagerID, 0 AS Level FROM Employees WHERE ManagerID IS NULL UNION ALL -- 递归查询(下属员工) SELECT e.EmployeeID, e.FirstName e.LastName, e.ManagerID, eh.Level 1 FROM Employees e JOIN EmployeeHierarchy eh ON e.ManagerID eh.EmployeeID ) SELECT REPLICATE( , Level) EmployeeName AS OrganizationChart, Level FROM EmployeeHierarchy ORDER BY Level, EmployeeName;部门预算分析SELECT d.DepartmentName, d.Budget AS TotalBudget, SUM(e.Salary) AS SalaryExpense, d.Budget - SUM(e.Salary) AS RemainingBudget, (SUM(e.Salary) / d.Budget) * 100 AS SalaryPercentage FROM Departments d JOIN Employees e ON d.DepartmentID e.DepartmentID GROUP BY d.DepartmentName, d.Budget HAVING SUM(e.Salary) 0 ORDER BY SalaryPercentage DESC;5.4 性能优化实践在实际应用中我们需要考虑查询性能优化索引策略-- 为常用查询条件创建索引 CREATE INDEX IX_Employees_DepartmentID ON Employees(DepartmentID); CREATE INDEX IX_Employees_ManagerID ON Employees(ManagerID); CREATE INDEX IX_Employees_HireDate ON Employees(HireDate); -- 为EmployeeSkills表创建覆盖索引 CREATE INDEX IX_EmployeeSkills_Covering ON EmployeeSkills(EmployeeID) INCLUDE (SkillID, ProficiencyLevel);查询重写优化-- 不推荐的写法(使用函数操作索引列) SELECT * FROM Employees WHERE YEAR(HireDate) 2023; -- 推荐的写法(允许使用索引) SELECT * FROM Employees WHERE HireDate 2023-01-01 AND HireDate 2024-01-01;统计信息更新-- 更新统计信息 UPDATE STATISTICS Employees WITH FULLSCAN; -- 创建统计信息 CREATE STATISTICS stats_Department_Salary ON Employees(DepartmentID, Salary);查询存储使用-- 启用查询存储 ALTER DATABASE CompanyHR SET QUERY_STORE ON; -- 查看性能最差的查询 SELECT qt.query_sql_text, qrs.count_executions, qrs.avg_duration, qrs.avg_logical_io_reads FROM sys.query_store_query_text qt JOIN sys.query_store_query q ON qt.query_text_id q.query_text_id JOIN sys.query_store_plan p ON q.query_id p.query_id JOIN sys.query_store_runtime_stats r ON p.plan_id r.plan_id ORDER BY qrs.avg_duration DESC;5.5 数据维护与安全确保数据安全和完整性是数据库管理的重要部分备份策略-- 完整备份 BACKUP DATABASE CompanyHR TO DISK C:\Backups\CompanyHR_Full.bak; -- 差异备份 BACKUP DATABASE CompanyHR TO DISK C:\Backups\CompanyHR_Diff.bak WITH DIFFERENTIAL; -- 事务日志备份 BACKUP LOG CompanyHR TO DISK C:\Backups\CompanyHR_Log.trn;数据加密-- 创建主密钥 CREATE MASTER KEY ENCRYPTION BY PASSWORD Complex_Password_123!; -- 创建证书 CREATE CERTIFICATE EmpDataCert WITH SUBJECT Employee Data Encryption; -- 创建对称密钥 CREATE SYMMETRIC KEY EmpDataKey WITH ALGORITHM AES_256 ENCRYPTION BY CERTIFICATE EmpDataCert; -- 加密敏感列 OPEN SYMMETRIC KEY EmpDataKey DECRYPTION BY CERTIFICATE EmpDataCert; UPDATE Employees SET Phone EncryptByKey(Key_GUID(EmpDataKey), Phone); CLOSE SYMMETRIC KEY EmpDataKey;行级安全-- 创建安全策略 CREATE SCHEMA Security; GO -- 创建谓词函数 CREATE FUNCTION Security.fn_securitypredicate(DepartmentID AS INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE DepartmentID DATABASE_PRINCIPAL_ID() OR IS_ROLEMEMBER(HR_Managers) 1; GO -- 创建安全策略 CREATE SECURITY POLICY Security.DepartmentFilter ADD FILTER PREDICATE Security.fn_securitypredicate(DepartmentID) ON dbo.Employees; GO通过这些实战案例我们展示了SQL Server 2019中表操作的完整流程从设计到实现从基础操作到高级优化涵盖了实际开发中的常见场景和需求。掌握这些技能后你将能够高效地管理和操作SQL Server数据库中的表结构。