MSSQL数据库基础介绍

一、什么是MSSQL

MSSQL（Microsoft SQL Server）是微软公司开发的关系型数据库管理系统。它为企业级应用提供强大的数据存储、管理和分析能力。

主要版本：

• SQL Server Enterprise：企业版
• SQL Server Standard：标准版
• SQL Server Developer：开发版
• SQL Server Express：免费版

二、MSSQL体系结构

2.1 数据库组成

2.2 常用数据类型

三、T-SQL基础语法

3.1 数据库操作

-- 创建数据库
CREATE DATABASE CompanyDB;

-- 使用数据库
USE CompanyDB;

-- 删除数据库
DROP DATABASE CompanyDB;

3.2 数据表操作

-- 创建员工表
CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    BirthDate DATE,
    HireDate DATETIME DEFAULT GETDATE(),
    Salary DECIMAL(10,2),
    DepartmentID INT
);

-- 创建部门表
CREATE TABLE Departments (
    DepartmentID INT IDENTITY(1,1) PRIMARY KEY,
    DepartmentName NVARCHAR(100) NOT NULL,
    ManagerID INT
);

3.3 数据操作语言(DML)

-- 插入数据
INSERT INTO Employees (FirstName, LastName, BirthDate, Salary, DepartmentID)
VALUES ('张', '三', '1990-05-15', 5000.00, 1);

-- 查询数据
SELECT * FROM Employees;
SELECT FirstName, LastName, Salary FROM Employees WHERE Salary > 4000;

-- 更新数据
UPDATE Employees SET Salary = Salary * 1.1 WHERE DepartmentID = 1;

-- 删除数据
DELETE FROM Employees WHERE EmployeeID = 5;

四、约束和索引

4.1 常用约束

4.2 索引管理

-- 创建索引
CREATE INDEX IX_Employees_LastName ON Employees(LastName);

-- 创建唯一索引
CREATE UNIQUE INDEX IX_Employees_Email ON Employees(Email);

-- 创建复合索引
CREATE INDEX IX_Employees_DepartmentSalary ON Employees(DepartmentID, Salary DESC);

-- 删除索引
DROP INDEX IX_Employees_LastName ON Employees;

五、查询进阶

5.1 多表连接查询

-- 内连接
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- 左外连接
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- 自连接
SELECT e1.FirstName AS Employee, e2.FirstName AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

5.2 聚合函数和分组

-- 分组统计
SELECT DepartmentID, 
       COUNT(*) AS EmployeeCount,
       AVG(Salary) AS AvgSalary,
       MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;

-- 排序和分页
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

六、存储过程和函数

6.1 存储过程

-- 创建存储过程
CREATE PROCEDURE GetEmployeeByDepartment
    @DepartmentID INT
AS
BEGIN
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE DepartmentID = @DepartmentID
    ORDER BY LastName, FirstName;
END;

-- 执行存储过程
EXEC GetEmployeeByDepartment @DepartmentID = 1;

6.2 用户定义函数

-- 创建标量函数
CREATE FUNCTION dbo.CalculateAnnualSalary(@MonthlySalary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @MonthlySalary * 12;
END;

-- 使用函数
SELECT FirstName, LastName, Salary, dbo.CalculateAnnualSalary(Salary) AS AnnualSalary
FROM Employees;

七、事务处理

7.1 事务基本语法

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
    -- 错误处理
    PRINT '交易失败: ' + ERROR_MESSAGE()
END CATCH

7.2 事务隔离级别

八、MSSQL特色功能

8.1 窗口函数

-- 排名函数
SELECT EmployeeID, FirstName, LastName, Salary,
       RANK() OVER (ORDER BY Salary DESC) AS SalaryRank,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

-- 分区统计
SELECT DepartmentID, FirstName, LastName, Salary,
       AVG(Salary) OVER (PARTITION BY DepartmentID) AS DeptAvgSalary
FROM Employees;

8.2 CTE（公用表表达式）

WITH DepartmentStats AS (
    SELECT DepartmentID,
           COUNT(*) AS EmployeeCount,
           AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT d.DepartmentName, ds.EmployeeCount, ds.AvgSalary
FROM DepartmentStats ds
INNER JOIN Departments d ON ds.DepartmentID = d.DepartmentID;

九、系统视图和函数

9.1 常用系统视图

十、最佳实践

• 规范化设计：遵循第三范式，减少数据冗余
• 索引策略：为常用查询字段创建适当索引
• 事务管理：合理使用事务，避免长时间锁定
• 性能监控：使用SQL Server Profiler监控查询性能
• 备份策略：定期备份数据库，制定恢复计划