SQL Server数据类型 #

SQL Server 数据类型
├── 数值类型
│   ├── 整数类型
│   ├── 小数类型
│   └── 货币类型
├── 字符串类型
│   ├── 非Unicode
│   └── Unicode
├── 日期时间类型
├── 二进制类型
├── 其他类型
│   ├── uniqueidentifier
│   ├── XML
│   ├── JSON
│   └── 空间数据
└── 特殊类型
    ├── sql_variant
    ├── hierarchyid
    └── table

-- 创建表
CREATE TABLE numbers (
    tiny_col TINYINT,
    small_col SMALLINT,
    int_col INT,
    big_col BIGINT
);

-- 插入数据
INSERT INTO numbers VALUES (255, 32767, 2147483647, 9223372036854775807);

-- 查询
SELECT * FROM numbers;

tiny_col | small_col | int_col    | big_col
---------|-----------|------------|--------------------
255      | 32767     | 2147483647 | 9223372036854775807

-- DECIMAL(p, s) 或 NUMERIC(p, s)
-- p：精度（总位数），1-38
-- s：小数位数，0-p

CREATE TABLE decimals (
    price DECIMAL(10, 2),    -- 10位总长，2位小数
    rate NUMERIC(5, 4)       -- 5位总长，4位小数
);

INSERT INTO decimals VALUES (12345678.90, 1.2345);

-- 存储：根据精度自动分配
-- 1-9位：5字节
-- 10-19位：9字节
-- 20-28位：13字节
-- 29-38位：17字节

-- FLOAT(n)：近似数值，n为尾数位数
-- REAL = FLOAT(24)

CREATE TABLE floats (
    float_col FLOAT,         -- 双精度
    real_col REAL            -- 单精度
);

INSERT INTO floats VALUES (123456789.123456789, 12345.6789);

-- FLOAT：8字节，约15位精度
-- REAL：4字节，约7位精度

-- 注意：浮点数可能有精度损失
SELECT CAST(0.1 AS FLOAT) + CAST(0.2 AS FLOAT);
-- 结果可能不是精确的0.3

CREATE TABLE products (
    id INT,
    name NVARCHAR(100),
    price SMALLMONEY,
    total MONEY
);

INSERT INTO products VALUES 
    (1, 'Product A', 99.99, 999999.99),
    (2, 'Product B', 199.50, 999999999999.99);

-- 货币类型自动保留4位小数
SELECT * FROM products;

CREATE TABLE strings (
    code CHAR(10),           -- 定长，不足补空格
    name VARCHAR(50),        -- 变长，最多50
    description VARCHAR(MAX) -- 大文本
);

INSERT INTO strings VALUES 
    ('ABC', 'Product Name', 'Very long description...');

-- CHAR定长特性
SELECT LEN(code) AS '实际长度', DATALENGTH(code) AS '存储长度'
FROM strings;
-- 实际长度: 3, 存储长度: 10

CREATE TABLE users (
    id INT,
    name NVARCHAR(50),       -- 支持中文等Unicode
    address NCHAR(100)       -- 定长Unicode
);

INSERT INTO users VALUES 
    (1, N'张三', N'北京市朝阳区'),
    (2, N'李四', N'上海市浦东新区');

-- Unicode字符串前缀 N
SELECT * FROM users WHERE name = N'张三';

-- 存储：每个字符2字节
SELECT name, LEN(name) AS '字符数', DATALENGTH(name) AS '字节数'
FROM users;
-- 字符数: 2, 字节数: 4

选择建议：
├── 存储英文/数字
│   ├── 固定长度 → CHAR
│   └── 可变长度 → VARCHAR
│
├── 存储中文/多语言
│   ├── 固定长度 → NCHAR
│   └── 可变长度 → NVARCHAR
│
├── 大文本
│   ├── 英文 → VARCHAR(MAX)
│   └── 多语言 → NVARCHAR(MAX)
│
└── 避免使用 TEXT/NTEXT（已废弃）

CREATE TABLE events (
    id INT,
    event_date DATE
);

INSERT INTO events VALUES 
    (1, '2024-01-15'),
    (2, GETDATE()),
    (3, CURRENT_TIMESTAMP);

-- DATE格式
SELECT 
    event_date,
    CONVERT(VARCHAR(10), event_date, 120) AS '格式化日期',
    YEAR(event_date) AS '年',
    MONTH(event_date) AS '月',
    DAY(event_date) AS '日'
FROM events;

CREATE TABLE schedules (
    id INT,
    start_time TIME(0),      -- 秒精度
    end_time TIME(7)         -- 100纳秒精度
);

INSERT INTO schedules VALUES 
    (1, '09:00:00', '17:30:00'),
    (2, '14:30:45.1234567', '18:00:00');

-- TIME精度
SELECT 
    start_time,
    end_time,
    DATEPART(HOUR, start_time) AS '小时',
    DATEPART(MINUTE, start_time) AS '分钟'
FROM schedules;

CREATE TABLE orders (
    id INT,
    order_date DATETIME2(3),  -- 毫秒精度
    created_at DATETIME2      -- 默认7位精度
);

INSERT INTO orders VALUES 
    (1, '2024-01-15 10:30:45.123', '2024-01-15 10:30:45.1234567'),
    (2, SYSDATETIME(), SYSDATETIME());

-- 日期时间函数
SELECT 
    order_date,
    SYSDATETIME() AS '当前时间',
    SYSUTCDATETIME() AS 'UTC时间',
    GETDATE() AS 'DATETIME类型'
FROM orders;

CREATE TABLE meetings (
    id INT,
    meeting_time DATETIMEOFFSET
);

INSERT INTO meetings VALUES 
    ('2024-01-15 10:00:00 +08:00'),  -- 北京时间
    ('2024-01-15 02:00:00 +00:00');  -- UTC时间

-- 时区转换
SELECT 
    meeting_time,
    SWITCHOFFSET(meeting_time, '+00:00') AS 'UTC',
    SWITCHOFFSET(meeting_time, '+08:00') AS '北京时间'
FROM meetings;

DECLARE @dt DATETIME = '2024-01-15 10:30:45';

-- CONVERT格式化
SELECT 
    CONVERT(VARCHAR(30), @dt, 120) AS 'YYYY-MM-DD HH:MI:SS',
    CONVERT(VARCHAR(10), @dt, 23) AS 'YYYY-MM-DD',
    CONVERT(VARCHAR(8), @dt, 108) AS 'HH:MI:SS',
    CONVERT(VARCHAR(30), @dt, 101) AS 'MM/DD/YYYY',
    CONVERT(VARCHAR(30), @dt, 103) AS 'DD/MM/YYYY';

-- FORMAT函数（SQL Server 2012+）
SELECT 
    FORMAT(@dt, 'yyyy-MM-dd') AS '日期',
    FORMAT(@dt, 'HH:mm:ss') AS '时间',
    FORMAT(@dt, 'yyyy年MM月dd日') AS '中文格式';

CREATE TABLE files (
    id INT,
    hash BINARY(32),           -- MD5哈希
    data VARBINARY(MAX)        -- 文件数据
);

-- 插入二进制数据
INSERT INTO files (id, hash, data)
VALUES (
    1, 
    HASHBYTES('MD5', 'test'), 
    CAST('Hello World' AS VARBINARY(MAX))
);

-- 读取
SELECT 
    id,
    CONVERT(VARCHAR(64), hash, 1) AS 'Hash',
    CONVERT(VARCHAR(MAX), data) AS 'Data'
FROM files;

-- GUID类型，16字节
CREATE TABLE sessions (
    id UNIQUEIDENTIFIER DEFAULT NEWID(),
    user_id INT,
    created_at DATETIME DEFAULT GETDATE()
);

-- 插入
INSERT INTO sessions (id, user_id)
VALUES (NEWID(), 1);

-- 或使用默认值
INSERT INTO sessions (user_id) VALUES (2);

-- 查询
SELECT * FROM sessions;

-- NEWID() vs NEWSEQUENTIALID()
-- NEWID(): 随机GUID
-- NEWSEQUENTIALID(): 顺序GUID（更适合索引）

CREATE TABLE docs (
    id INT,
    content XML
);

-- 插入XML
INSERT INTO docs VALUES 
    (1, '<root><item id="1">Item 1</item></root>');

-- 查询XML
SELECT 
    content,
    content.query('/root/item') AS 'Item',
    content.value('(/root/item/@id)[1]', 'INT') AS 'ItemID'
FROM docs;

-- XML方法
DECLARE @xml XML = '<root><item id="1">A</item><item id="2">B</item></root>';

SELECT 
    @xml.query('/root/item') AS 'query',
    @xml.value('(/root/item)[1]', 'VARCHAR(10)') AS 'value',
    @xml.exist('/root/item[@id="1"]') AS 'exist',
    @xml.nodes('/root/item') AS T(c);

-- SQL Server 2016+ 支持JSON
-- 使用NVARCHAR存储JSON

CREATE TABLE settings (
    id INT,
    config NVARCHAR(MAX)
);

-- 插入JSON
INSERT INTO settings VALUES 
    (1, '{"name": "test", "values": [1, 2, 3]}');

-- 解析JSON
SELECT 
    config,
    JSON_VALUE(config, '$.name') AS 'Name',
    JSON_QUERY(config, '$.values') AS 'Values'
FROM settings;

-- OPENJSON解析
DECLARE @json NVARCHAR(MAX) = '[{"id":1,"name":"A"},{"id":2,"name":"B"}]';

SELECT *
FROM OPENJSON(@json)
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name'
);

-- GEOMETRY：平面空间数据
-- GEOGRAPHY：地理空间数据

CREATE TABLE locations (
    id INT,
    point GEOMETRY,
    area GEOGRAPHY
);

-- 插入空间数据
INSERT INTO locations VALUES 
    (1, 
     GEOMETRY::Point(1, 1, 0),
     GEOGRAPHY::Point(39.9, 116.4, 4326));  -- 北京坐标

-- 空间方法
SELECT 
    id,
    point.ToString() AS 'Point',
    point.STX AS 'X',
    point.STY AS 'Y',
    area.Lat AS 'Latitude',
    area.Long AS 'Longitude'
FROM locations;

-- 可以存储各种类型的数据
CREATE TABLE mixed (
    id INT,
    value SQL_VARIANT
);

INSERT INTO mixed VALUES 
    (1, 100),
    (2, 'Hello'),
    (3, GETDATE()),
    (4, 123.45);

SELECT 
    id,
    value,
    SQL_VARIANT_PROPERTY(value, 'BaseType') AS 'Type',
    SQL_VARIANT_PROPERTY(value, 'MaxLength') AS 'Length'
FROM mixed;

-- 表示层次结构
CREATE TABLE employees (
    id INT,
    name NVARCHAR(50),
    org_path HIERARCHYID
);

-- 插入层级数据
INSERT INTO employees VALUES 
    (1, 'CEO', HIERARCHYID::GetRoot()),
    (2, 'Manager', '/1/'),
    (3, 'Employee', '/1/1/');

-- 层级查询
SELECT 
    id,
    name,
    org_path.ToString() AS 'Path',
    org_path.GetLevel() AS 'Level'
FROM employees;

-- 表类型（用于表值参数）
CREATE TYPE UserTableType AS TABLE (
    id INT,
    name NVARCHAR(50)
);

-- 在存储过程中使用
CREATE PROCEDURE usp_InsertUsers
    @users UserTableType READONLY
AS
BEGIN
    INSERT INTO users (id, name)
    SELECT id, name FROM @users;
END

-- CAST：ANSI标准
SELECT CAST(123.45 AS INT) AS 'Int';
SELECT CAST('2024-01-15' AS DATE) AS 'Date';
SELECT CAST(123 AS VARCHAR(10)) AS 'String';

-- CONVERT：SQL Server特有，支持格式
SELECT CONVERT(VARCHAR(30), GETDATE(), 120) AS 'DateTime';
SELECT CONVERT(INT, '123') AS 'Int';
SELECT CONVERT(DECIMAL(10,2), '123.456') AS 'Decimal';

-- 转换失败返回NULL而不是错误
SELECT TRY_CAST('abc' AS INT) AS 'Result';  -- NULL
SELECT TRY_CAST('123' AS INT) AS 'Result';  -- 123

SELECT TRY_CONVERT(INT, 'abc') AS 'Result';  -- NULL
SELECT TRY_CONVERT(DATE, '2024-01-15') AS 'Result';  -- 2024-01-15

-- 安全转换
SELECT 
    value,
    TRY_CAST(value AS INT) AS 'IntValue',
    CASE 
        WHEN TRY_CAST(value AS INT) IS NULL AND value IS NOT NULL 
        THEN 'Invalid'
        ELSE 'Valid'
    END AS 'Status'
FROM (VALUES ('123'), ('abc'), (NULL)) AS T(value);

-- SQL Server自动转换
DECLARE @int INT = 123;
DECLARE @varchar VARCHAR(10) = '456';

-- 隐式转换
SELECT @int + @varchar;  -- 579（varchar转int）

-- 优先级规则
-- DATETIME > FLOAT > DECIMAL > INT > VARCHAR

-- NULL表示未知或缺失
-- NULL不等于任何值，包括NULL本身

SELECT 
    CASE WHEN NULL = NULL THEN 'Equal' ELSE 'Not Equal' END AS 'Result';
-- Result: Not Equal

SELECT 
    CASE WHEN NULL <> NULL THEN 'Not Equal' ELSE 'Equal' END AS 'Result';
-- Result: Equal

-- ISNULL：替换NULL
SELECT ISNULL(NULL, 'Default');  -- Default
SELECT ISNULL(name, 'Unknown') FROM users;

-- COALESCE：返回第一个非NULL值
SELECT COALESCE(NULL, NULL, 'Third', 'Fourth');  -- Third

-- NULLIF：相等返回NULL
SELECT NULLIF(10, 10);  -- NULL
SELECT NULLIF(10, 20);  -- 10

-- IIF：条件表达式
SELECT IIF(1 = 1, 'True', 'False');  -- True