SQL基础概念与SQL Server代码实例

一、索引

1.1 聚集索引

聚集索引根据键值对数据行进行排序和存储，每个表只能有一个聚集索引。

-- 创建一个以'Id'列为键的聚集索引
CREATE CLUSTERED INDEX IX_Employee_Id
ON Employee(Id);

• CREATE CLUSTERED INDEX：创建一个聚集索引。
• IX_Employee_Id：索引名称。
• ON Employee(Id)：在'Employee'表的'Id'列上创建索引。

1.2 非聚集索引

非聚集索引将索引结构与实际表数据分开存储，并创建指向数据行的指针。

-- 在'Employee'表的'Name'列上创建一个非聚集索引
CREATE NONCLUSTERED INDEX IX_Employee_Name
ON Employee(Name);

• CREATE NONCLUSTERED INDEX：创建一个非聚集索引。
• IX_Employee_Name：索引名称。
• ON Employee(Name)：在'Employee'表的'Name'列上创建索引。

二、表操作

2.1 创建表

创建一个表时，需要定义列和它们的数据类型。

-- 创建一个'Employee'表
CREATE TABLE Employee (
    Id INT PRIMARY KEY,             -- 主键列
    Name NVARCHAR(50),              -- 员工姓名
    DepartmentId INT,               -- 部门ID
    HireDate DATE                   -- 入职日期
);

2.2 插入多行数据

通过一个查询插入多行数据。

-- 向'Employee'表插入多行数据
INSERT INTO Employee (Id, Name, DepartmentId, HireDate)
VALUES
(1, 'Alice', 1, '2021-01-01'),
(2, 'Bob', 2, '2021-02-01'),
(3, 'Charlie', 1, '2021-03-01');

• INSERT INTO Employee (Id, Name, DepartmentId, HireDate)：指定插入的表和列。
• VALUES：定义插入的具体数据。

2.3 修改表

通过添加或修改列来修改现有的表。

-- 向'Employee'表添加一个新的'薪资'列
ALTER TABLE Employee
ADD Salary DECIMAL(10, 2);

• ALTER TABLE Employee：修改'Employee'表。
• ADD Salary DECIMAL(10, 2)：添加一个'薪资'列，类型为DECIMAL，精度为10，小数位数为2。

2.4 更新行数据

更新表中某一行的数据。

-- 更新Id为1的员工的薪资
UPDATE Employee
SET Salary = 70000
WHERE Id = 1;

• UPDATE Employee：指定要更新的表。
• SET Salary = 70000：将薪资更新为70000。
• WHERE Id = 1：仅更新Id为1的员工。

2.5 重命名表

重命名一个已存在的表。

-- 将'Employee'表重命名为'Staff'
EXEC sp_rename 'Employee', 'Staff';

• EXEC sp_rename：执行重命名操作。
• 'Employee'：原表名。
• 'Staff'：新表名。

2.6 删除行

根据条件删除表中的特定行。

-- 删除Id为2的员工
DELETE FROM Employee
WHERE Id = 2;

• DELETE FROM Employee：指定删除的表。
• WHERE Id = 2：删除Id为2的行。

2.7 删除表

删除整个表及其所有数据。

-- 删除'Employee'表
DROP TABLE Employee;

• DROP TABLE Employee：删除'Employee'表及其数据。

2.8 清空表

从表中删除所有行，但不记录每行的删除。

-- 清空'Employee'表
TRUNCATE TABLE Employee;

• TRUNCATE TABLE Employee：清空表的所有数据。

三、游标

游标用于逐行检索结果集中的行。

DECLARE @EmployeeId INT;
-- 声明一个游标，用于选择'Employee'表中的Id
DECLARE employee_cursor CURSOR FOR
SELECT Id FROM Employee;

OPEN employee_cursor;  -- 打开游标
FETCH NEXT FROM employee_cursor INTO @EmployeeId;  -- 获取下一行数据

WHILE @@FETCH_STATUS = 0  -- 当有数据时
BEGIN
    -- 对每一行进行处理
    PRINT @EmployeeId;
    FETCH NEXT FROM employee_cursor INTO @EmployeeId;  -- 获取下一行数据
END;

CLOSE employee_cursor;  -- 关闭游标
DEALLOCATE employee_cursor;  -- 释放游标资源

四、视图

视图是基于查询的虚拟表。

-- 创建一个显示员工姓名和入职日期的视图
CREATE VIEW EmployeeView AS
SELECT Name, HireDate
FROM Employee
WHERE HireDate > '2021-01-01';

• CREATE VIEW EmployeeView AS：创建一个名为EmployeeView的视图。
• SELECT Name, HireDate：从'Employee'表选择姓名和入职日期。
• WHERE HireDate > '2021-01-01'：只包含入职日期晚于2021年1月1日的员工。

五、触发器

触发器是一种特殊的存储过程，当数据库中发生某些事件时会自动运行。

-- 创建一个在'Employee'表插入数据后打印消息的触发器
CREATE TRIGGER trg_AfterInsertEmployee
ON Employee
AFTER INSERT
AS
BEGIN
    PRINT '新员工已插入！';
END;

• CREATE TRIGGER trg_AfterInsertEmployee：创建名为trg_AfterInsertEmployee的触发器。
• ON Employee：触发器作用于Employee表。
• AFTER INSERT：在插入操作之后触发。
• PRINT '新员工已插入！'：打印消息。

六、公共表表达式 (CTE)

CTE用于创建一个临时结果集，可以在SELECT、INSERT、UPDATE或DELETE语句中引用。

WITH EmployeeCTE AS (
    SELECT Name, Salary
    FROM Employee
    WHERE Salary > 60000
)
SELECT *
FROM EmployeeCTE;

• WITH EmployeeCTE AS：定义一个名为EmployeeCTE的CTE。
• SELECT Name, Salary：选择姓名和薪资。
• WHERE Salary > 60000：筛选薪资大于60000的员工。

七、连接操作

7.1 内连接

内连接返回在两个表中至少有一个匹配的行。

-- 内连接'Employee'和'Department'表
SELECT e.Name, d.DepartmentName
FROM Employee e
INNER JOIN Department d
ON e.DepartmentId = d.Id;

• INNER JOIN Department d：连接Department表。
• ON e.DepartmentId = d.Id：连接条件，基于DepartmentId和Id。

7.2 左连接

左连接返回左表中的所有行和右表中的匹配行。未匹配的行将返回NULL。

-- 左连接'Employee'和'Department'表
SELECT e.Name, d.DepartmentName
FROM Employee e
LEFT JOIN Department d
ON e.DepartmentId = d.Id;

• LEFT JOIN Department d：左连接Department表。
• ON e.DepartmentId = d.Id：连接条件，基于DepartmentId和Id。

7.3 右连接

右连接返回右表中的所有行和左表中的匹配行。未匹配的行将返回NULL。

-- 右连接'Employee'和'Department'表
SELECT e.Name, d.DepartmentName
FROM Employee e
RIGHT JOIN Department d
ON e.DepartmentId = d.Id;

• RIGHT JOIN Department d：右连接Department表。
• ON e.DepartmentId = d.Id：连接条件，基于DepartmentId和Id。

7.4 自连接

自连接是将表与自身进行连接。

-- 在'Employee'表上自连接，查找员工及其经理
SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerId = e2.Id;

• LEFT JOIN Employee e2：将Employee表与自身连接。
• ON e1.ManagerId = e2.Id：连接条件，基于ManagerId和Id。

7.5 交叉连接

交叉连接返回两个表的笛卡尔积，每一行都与另一表中的每一行组合。

-- 交叉连接'Employee'和'Department'表
SELECT e.Name, d.DepartmentName
FROM Employee e
CROSS JOIN Department d;

• CROSS JOIN Department d：交叉连接Department表。

7.6 Cross Apply

CROSS APPLY类似于内连接，但用于将表与表值函数连接。

-- Cross Apply示例
SELECT e.Name, sub.TopDepartment
FROM Employee e
CROSS APPLY (
    SELECT TOP 1 d.DepartmentName AS TopDepartment
    FROM Department d
    WHERE d.Id = e.DepartmentId
) sub;

• CROSS APPLY：将Employee表与一个子查询结果连接。
• SELECT TOP 1 d.DepartmentName：选择DepartmentName的前1个结果。

八、排名函数

8.1 ROW_NUMBER()

ROW_NUMBER()函数为结果集中的行分配唯一的递增整数。

-- 根据薪资分配行号
SELECT
    Name,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber
FROM
    Employee;

• ROW_NUMBER() OVER (ORDER BY Salary DESC)：根据薪资降序分配行号。

8.2 RANK()

RANK()函数为每一行分配排名，有重复时会有排名间隙。

-- 根据薪资分配排名
SELECT Name,
       Salary,
       RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employee;

• RANK() OVER (ORDER BY Salary DESC)：根据薪资降序分配排名。

8.3 DENSE_RANK()

DENSE_RANK()函数为每一行分配排名，没有排名间隙。

-- 根据薪资分配密集排名
SELECT
    Name,
    Salary,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM
    Employee;

• DENSE_RANK() OVER (ORDER BY Salary DESC)：根据薪资降序分配密集排名。