SQL Server使用记录

SQL Server实际工作使用记录

创建表结构

除了极个别表字段, 跟mysql其实差别不大

CREATE TABLE TableName(
    -- 主键id
    id CHAR(36) NOT NULL PRIMARY KEY,
    
    -- 二维码编号
    qrCodeNumber NVARCHAR(255),
    
    -- 设备型号Id
    deviceModelId CHAR(36) NOT NULL,
    
    -- 设备编号
    deviceNumber NVARCHAR(255) NOT NULL,
    
    -- 批次号
    batchNumber NVARCHAR(255),
    
    -- 备注
    remark NVARCHAR(3000),
    
    -- 创建时间
    createdAt DATETIME2 NOT NULL,
    
    -- 创建人
    createdBy CHAR(36) NOT NULL,
    
    -- 更新时间
    updatedAt DATETIME2,
    
    -- 更新人
    updatedBy CHAR(36),
    
    -- 更新人账号
    updatedAccount NVARCHAR(255),
    
    -- 设备设置Id
    deviceConfigId CHAR(36) NOT NULL,
    
    -- 商户Id
    merchantId CHAR(36),
    
    -- 商户名称
    merchantName NVARCHAR(255),
    
    -- 设备型号名称
    deviceModelName NVARCHAR(255) NOT NULL,
    
    -- 资产id
    assetId CHAR(36),
    
    -- 资产状态
    assetStatus NVARCHAR(255),
    
    -- 资产编号
    assetNumber NVARCHAR(255),
    
    -- 服务开始时间
    serviceStartTime DATETIME2,
    
    -- 服务结束时间
    serviceEndTime DATETIME2,
    
    -- 用户id
    userId CHAR(36),
    
    -- 用户名称
    userName NVARCHAR(255),
    
    -- 电话
    phone NVARCHAR(255),
    
    -- 邮箱
    email NVARCHAR(255),
    
    -- 账号
    account NVARCHAR(255),
    
    -- 用户和车辆关联时间
    userAndVehicleAssociationTime DATETIME2,
    
    -- 车辆名称
    vehicleName NVARCHAR(255),
    
    -- 用户和车辆关联状态 0 false为未关联 1 true为已关联
    isUserAndVehicleAssociationStatus BIT DEFAULT 0,
    
    -- 电池电压
    batteryVoltage DECIMAL(10, 2),
    
    -- 电池类型
    batteryType NVARCHAR(255),
    
    -- iccid
    iccid NVARCHAR(255),
    
    -- msisdn
    msisdn NVARCHAR(255),
    
    -- 设备实时状态Id
    deviceStatusModelId CHAR(36)
)

CREATE INDEX IX_DeviceReadModel_qrCodeNumber ON TableName(qrCodeNumber)

删除表结构

DROP TABLE tableName;

-- 多表删除
DROP TABLE Employees, Departments;

创建内存优化表

ALTER DATABASE YugpsWarehouse ADD FILEGROUP YugpsWarehouseMemoryOptimizedFG CONTAINS MEMORY_OPTIMIZED_DATA;


ALTER DATABASE test ADD FILE (NAME = N'MemoryOptimizedFile', FILENAME = N'/var/opt/mssql/data/memoryoptimizedfile') TO FILEGROUP YugpsWarehouseMemoryOptimizedFG;

CREATE TABLE tableName (
    -- 主键id
    id CHAR(36) PRIMARY KEY NONCLUSTERED,
    
    -- 设备硬件编号
    deviceCode NVARCHAR(255) NOT NULL,
    
    -- 定位状态
    isPositioningStatus BIT DEFAULT 0,
    
    -- 通讯时间
    receiveTime DATETIME,
    
    -- 定位时间
    positioningTime DATETIME,
    
    -- bms编号
    bmsNumber NVARCHAR(255),
    
    -- iccid
    iccid NVARCHAR(255),
		INDEX idx_deviceCode (deviceCode)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

字符串前缀N, 防止乱码

在 SQL Server 中，前缀 N 用于表示字符串是 Unicode 字符串。N 实际上是 National Language Character Set 的缩写，表示该字符串使用的是国家/地区字符集。

分页查询

好像必须先排序才能分页

select * from tableName ORDER BY receiveTime desc OFFSET 0 rows fetch next 100 rows ONLY

创建索引

-- 非聚集索引
CREATE INDEX idx_iccid ON DeviceReadModel(iccid);

-- 创建聚集索引
CREATE CLUSTERED INDEX idx_iccid ON DeviceReadModel(iccid);

-- 包含列
CREATE NONCLUSTERED INDEX idx_iccid ON DeviceReadModel(iccid) INCLUDE (msisdn);

删除索引

DROP INDEX idx_iccid ON DeviceReadModel;

时间函数

在 SQL Server 中，你可以使用 GETDATE() 函数获取当前的日期和时间，GETUTCDATE() 函数获取当前的 UTC 日期和时间，DATEADD() 函数来对日期和时间进行加减操作。在这个例子中，DATEADD(MINUTE, -7.5, GETUTCDATE()) 将从当前的 UTC 日期和时间减去 7.5 分钟。

SELECT * 
FROM DeviceReadModel a 
JOIN DeviceStatusModel b 
ON a.deviceStatusModelId = b.id 
WHERE serviceEndTime < GETDATE() 
AND b.receiveTime < DATEADD(MINUTE, -7.5, GETUTCDATE());

ps: select GETDATE(); 类似mysql select now() from dual;

批量更新sql语句写法

UPDATE d
SET d.msisdn = s.msisdn
FROM DeviceReadModel d
INNER JOIN SimCard s ON d.iccid = s.iccid;

-- 更新未匹配到的iccid, 把msisdn改为null
UPDATE d
SET d.msisdn = NULL
FROM DeviceReadModel d
LEFT JOIN SimCard s ON d.iccid = s.iccid
WHERE s.iccid IS NULL;