SQL Server 常用关键词语法汇总

一、函数

1.1 CAST

CAST ( expression AS data_type [ ( length ) ] )

• expression: 这是你想要转换的数据或表达式。
• data_type: 目标数据类型，比如 INT, VARCHAR, DATE
  等等。
• (length): 对于某些数据类型（如 CHAR, VARCHAR, BINARY,
  VARBINARY），可以指定长度。对于其他数据类型，这个参数是可选的或不适用的。

示例
字符串转换为整数

SELECT CAST('123' AS INT) AS ConvertedValue;

整数转换为字符串

SELECT CAST(123 AS VARCHAR(10)) AS ConvertedValue;

日期转换

SELECT CAST('2023-01-01' AS DATE) AS ConvertedDate;

浮点数转换为整数
需要注意的是，当将浮点数转换为整数时，SQL Server 会进行截断而不是四舍五入。

SELECT CAST(123.456 AS INT) AS ConvertedValue; -- 结果为 123

转换为布尔值
在 SQL Server 中，并没有直接的布尔类型，但是你可以使用位（bit）类型来表示布尔值。

SELECT CAST(1 AS BIT) AS ConvertedBoolean; -- 结果为 1（真）
SELECT CAST(0 AS BIT) AS ConvertedBoolean; -- 结果为 0（假）

注意事项

• 当执行数据类型转换时，如果源数据与目标数据类型不兼容，可能会导致错误。
• 在处理日期和时间数据时，确保输入的格式正确，否则可能会导致意外的结果或错误。
• 使用 CAST 时，要确保转换后的数据类型能够容纳原始数据的所有可能值，以避免数据丢失或不准确。

二、表

2.1向表中添加列

ALTER TABLE dbo.doc_exa 
ADD column_b VARCHAR(20) NULL, column_c INT NULL ;

2.2从表中删除列

ALTER TABLE dbo.doc_exb DROP COLUMN column_b;
GO

2.3将数据从一个表复制到另一个表

CREATE TABLE dbo.EmployeeSales  
( BusinessEntityID   varchar(11) NOT NULL,  
  SalesYTD money NOT NULL  
);  
GO  
INSERT INTO dbo.EmployeeSales  
    SELECT BusinessEntityID, SalesYTD   
    FROM Sales.SalesPerson;  
GO  

2.4 检索数据库的名称及其排序规则

select name , collation_name from sys.databases

• 排序规则（Collation）

排序规则定义了字符数据的比较和排序方式。排序规则影响字符串的比较、排序以及大小写敏感性等。例如，不同的排序规则可能会导致相同的字符串在不同的数据库中比较结果不同。

三、常用操作

3.1 WITH 关键词

• 用途：通过CTE将符合条件的记录集中在一个临时结果集中，以便后续操作。

DECLARE @PhoneNumber VARCHAR(50) = '18888888888';

WITH TableName AS
(
    SELECT * 
    FROM [T].[dbo].[Test] 
    WHERE ID = (SELECT ID 
                        FROM [T].[dbo].[Test]
                        WHERE Phone = @PhoneNumber)
)
DELETE FROM TableName 
WHERE Phone <> @PhoneNumber;

3.2 ROW_NUMBER()和OVER ( … )

基本语法

ROW_NUMBER() OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...
) AS alias

• ROW_NUMBER()：生成唯一的行号。
• OVER：定义窗口函数的作用范围。
• PARTITION BY：可选，用于将结果集划分为多个分区，每个分区内的行将独立编号。
• ORDER BY：必需，用于定义每个分区内行的排序顺序。
• AS alias：为生成的行号列指定一个别名。

示例

示例 1：按客户编号分组并按订单日期排序

SELECT 
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount,
    ROW_NUMBER() OVER (
        PARTITION BY CustomerID
        ORDER BY OrderDate ASC
    ) AS RowNumber
FROM 
    Orders

示例 2：按总金额降序排序并分配行号

SELECT 
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount,
    ROW_NUMBER() OVER (
        ORDER BY TotalAmount DESC
    ) AS RowNumber
FROM 
    Orders

示例 3：分页查询
假设我们想获取每个客户的最新订单，可以使用 ROW_NUMBER() 来实现分页查询：

WITH RankedOrders AS (
    SELECT 
        OrderID,
        CustomerID,
        OrderDate,
        TotalAmount,
        ROW_NUMBER() OVER (
            PARTITION BY CustomerID
            ORDER BY OrderDate DESC
        ) AS RowNumber
    FROM 
        Orders
)
SELECT 
    OrderID,
    CustomerID,
    OrderDate,
    TotalAmount
FROM 
    RankedOrders
WHERE 
    RowNumber = 1

解释
示例 1：
按 CustomerID 分组，每个分组内的行按 OrderDate 升序排列。
每个分组内的行号从 1 开始递增。
示例 2：
按 TotalAmount 降序排列，整个结果集作为一个分组。
行号从 1 开始递增。
示例 3：
使用 ROW_NUMBER() 为每个客户的订单按 OrderDate 降序排列并分配行号。
使用 WITH 子句创建一个临时结果集 RankedOrders。
从 RankedOrders 中选择 RowNumber = 1 的行，即每个客户的最新订单。
总结
ROW_NUMBER() OVER 是一个非常强大的窗口函数，可以用于多种场景，如分组排序、分页查询等。通过合理使用 PARTITION BY 和 ORDER BY 子句，可以灵活地控制行号的分配方式。希望这些示例能帮助你更好地理解和使用 ROW_NUMBER() OVER。