SQL Server表操作 #

SQL Server 表结构
├── 列（Columns）
│   ├── 列名
│   ├── 数据类型
│   └── 约束
├── 约束（Constraints）
│   ├── 主键约束
│   ├── 外键约束
│   ├── 唯一约束
│   ├── 检查约束
│   └── 默认约束
├── 索引（Indexes）
│   ├── 聚集索引
│   └── 非聚集索引
└── 触发器（Triggers）

表命名规范：
├── 表名
│   ├── 单数名词：user, order
│   ├── 小写+下划线：user_profile
│   └── 或帕斯卡命名：UserProfile
│
├── 列名
│   ├── 小写+下划线：created_at
│   └── 有意义的名称
│
├── 主键
│   └── id 或 表名_id
│
├── 外键
│   └── 关联表名_id
│
├── 索引
│   └── ix_表名_列名
│
└── 约束
    ├── pk_表名：主键
    ├── fk_表名_关联表：外键
    ├── uq_表名_列名：唯一
    ├── ck_表名_列名：检查
    └── df_表名_列名：默认

CREATE TABLE [schema_name.]table_name
(
    column_name data_type [NULL | NOT NULL]
        [CONSTRAINT constraint_name]
        [DEFAULT default_value]
        [IDENTITY [(seed, increment)]]
        [ROWGUIDCOL]
    [, ...n]
    [, CONSTRAINT table_constraint]
)
[ON { filegroup | "default" }]
[TEXTIMAGE_ON { filegroup | "default" }];

-- 创建简单用户表
CREATE TABLE users (
    id INT IDENTITY(1,1),
    name NVARCHAR(50) NOT NULL,
    email VARCHAR(100),
    age INT,
    created_at DATETIME DEFAULT GETDATE()
);

-- 插入数据测试
INSERT INTO users (name, email, age) 
VALUES ('John', 'john@example.com', 25);

SELECT * FROM users;

-- 创建完整的用户表
CREATE TABLE dbo.users (
    id INT IDENTITY(1,1) NOT NULL,
    username NVARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash NVARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    age INT,
    status BIT DEFAULT 1,
    created_at DATETIME2 DEFAULT SYSDATETIME(),
    updated_at DATETIME2,
    
    -- 主键约束
    CONSTRAINT pk_users PRIMARY KEY (id),
    
    -- 唯一约束
    CONSTRAINT uq_users_username UNIQUE (username),
    CONSTRAINT uq_users_email UNIQUE (email),
    
    -- 检查约束
    CONSTRAINT ck_users_age CHECK (age >= 0 AND age <= 150),
    CONSTRAINT ck_users_email CHECK (email LIKE '%@%.%')
);

-- 创建索引
CREATE INDEX ix_users_email ON dbo.users(email);
CREATE INDEX ix_users_status ON dbo.users(status);

-- 创建架构
CREATE SCHEMA sales;
GO

-- 在架构中创建表
CREATE TABLE sales.orders (
    id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE DEFAULT GETDATE(),
    total_amount DECIMAL(10,2),
    status NVARCHAR(20) DEFAULT 'pending'
);

-- 创建表时定义主键
CREATE TABLE products (
    id INT PRIMARY KEY,
    name NVARCHAR(100)
);

-- 命名主键约束
CREATE TABLE products (
    id INT CONSTRAINT pk_products PRIMARY KEY,
    name NVARCHAR(100)
);

-- 复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id)
);

-- 添加主键到现有表
ALTER TABLE products
ADD CONSTRAINT pk_products PRIMARY KEY (id);

-- 删除主键
ALTER TABLE products
DROP CONSTRAINT pk_products;

-- 创建外键
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    
    CONSTRAINT fk_orders_customers 
    FOREIGN KEY (customer_id) 
    REFERENCES customers(id)
);

-- 外键选项
CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    
    CONSTRAINT fk_order_items_orders 
    FOREIGN KEY (order_id) 
    REFERENCES orders(id)
    ON DELETE CASCADE      -- 删除时级联删除
    ON UPDATE CASCADE,     -- 更新时级联更新
    
    CONSTRAINT fk_order_items_products 
    FOREIGN KEY (product_id) 
    REFERENCES products(id)
    ON DELETE NO ACTION    -- 删除时报错
    ON UPDATE NO ACTION
);

-- 添加外键
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id) REFERENCES customers(id);

-- 删除外键
ALTER TABLE orders
DROP CONSTRAINT fk_orders_customers;

-- 创建唯一约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    username NVARCHAR(50) UNIQUE,
    email VARCHAR(100) CONSTRAINT uq_users_email UNIQUE
);

-- 添加唯一约束
ALTER TABLE users
ADD CONSTRAINT uq_users_phone UNIQUE (phone);

-- 复合唯一约束
ALTER TABLE user_roles
ADD CONSTRAINT uq_user_roles UNIQUE (user_id, role_id);

-- 删除唯一约束
ALTER TABLE users
DROP CONSTRAINT uq_users_email;

-- 创建检查约束
CREATE TABLE products (
    id INT PRIMARY KEY,
    name NVARCHAR(100),
    price DECIMAL(10,2),
    stock INT,
    
    CONSTRAINT ck_products_price CHECK (price > 0),
    CONSTRAINT ck_products_stock CHECK (stock >= 0)
);

-- 复杂检查约束
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name NVARCHAR(50),
    birth_date DATE,
    hire_date DATE,
    
    CONSTRAINT ck_employees_dates 
    CHECK (hire_date > birth_date AND hire_date <= GETDATE())
);

-- 添加检查约束
ALTER TABLE products
ADD CONSTRAINT ck_products_status 
CHECK (status IN ('active', 'inactive', 'discontinued'));

-- 删除检查约束
ALTER TABLE products
DROP CONSTRAINT ck_products_price;

-- 创建默认约束
CREATE TABLE orders (
    id INT PRIMARY KEY,
    status NVARCHAR(20) DEFAULT 'pending',
    created_at DATETIME DEFAULT GETDATE(),
    is_active BIT DEFAULT 1
);

-- 添加默认约束
ALTER TABLE users
ADD CONSTRAINT df_users_status DEFAULT 1 FOR status;

-- 使用函数作为默认值
CREATE TABLE audit_log (
    id INT PRIMARY KEY,
    action NVARCHAR(50),
    action_date DATETIME2 DEFAULT SYSDATETIME(),
    user_name NVARCHAR(100) DEFAULT SUSER_NAME()
);

-- 删除默认约束
ALTER TABLE users
DROP CONSTRAINT df_users_status;

-- 查看表约束
SELECT 
    name AS [约束名],
    type_desc AS [类型]
FROM sys.objects
WHERE parent_object_id = OBJECT_ID('users');

-- 使用sp_helpconstraint
EXEC sp_helpconstraint 'users';

-- 禁用约束（外键和检查约束）
ALTER TABLE orders NOCHECK CONSTRAINT fk_orders_customers;

-- 启用约束
ALTER TABLE orders CHECK CONSTRAINT fk_orders_customers;

-- 禁用所有约束
ALTER TABLE orders NOCHECK CONSTRAINT ALL;

-- 启用所有约束
ALTER TABLE orders CHECK CONSTRAINT ALL;

-- 添加单列
ALTER TABLE users ADD phone VARCHAR(20);

-- 添加多列
ALTER TABLE users ADD
    address NVARCHAR(200),
    city NVARCHAR(50),
    country NVARCHAR(50) DEFAULT 'China';

-- 添加带约束的列
ALTER TABLE users ADD
    age INT CONSTRAINT ck_users_age CHECK (age >= 0);

-- 修改数据类型
ALTER TABLE users ALTER COLUMN name NVARCHAR(100);

-- 修改为NOT NULL
ALTER TABLE users ALTER COLUMN email VARCHAR(100) NOT NULL;

-- 修改列属性
ALTER TABLE users ALTER COLUMN phone VARCHAR(50) NULL;

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 删除有约束的列（需先删除约束）
ALTER TABLE users DROP CONSTRAINT df_users_status;
ALTER TABLE users DROP COLUMN status;

-- 使用sp_rename
EXEC sp_rename 'users.name', 'user_name', 'COLUMN';

-- 或者
EXEC sp_rename 
    @objname = 'users.name', 
    @newname = 'user_name', 
    @objtype = 'COLUMN';

-- 重命名表
EXEC sp_rename 'users', 'sys_users';

-- 或者
EXEC sp_rename 
    @objname = 'users', 
    @newname = 'sys_users', 
    @objtype = 'OBJECT';

-- 删除表
DROP TABLE users;

-- 安全删除（SQL Server 2016+）
DROP TABLE IF EXISTS users;

-- 删除多个表
DROP TABLE users, orders, products;

-- 先删除外键约束
ALTER TABLE orders DROP CONSTRAINT fk_orders_users;

-- 再删除表
DROP TABLE users;

-- 或者使用级联删除（需谨慎）
DROP TABLE users CASCADE;  -- SQL Server不支持此语法

-- 使用DELETE（记录日志，可回滚）
DELETE FROM users;

-- 使用TRUNCATE（不记录日志，更快）
TRUNCATE TABLE users;

-- TRUNCATE与DELETE区别
/*
TRUNCATE:
- 不记录日志，速度快
- 不能用于有外键引用的表
- 重置IDENTITY值
- 不能触发触发器

DELETE:
- 记录日志，可回滚
- 可以有WHERE条件
- 不重置IDENTITY值
- 触发触发器
*/

-- 创建本地临时表（#前缀）
CREATE TABLE #temp_users (
    id INT,
    name NVARCHAR(50)
);

-- 插入数据
INSERT INTO #temp_users
SELECT id, name FROM users WHERE status = 1;

-- 使用临时表
SELECT * FROM #temp_users;

-- 临时表在会话结束时自动删除
-- 也可以手动删除
DROP TABLE #temp_users;

-- 创建全局临时表（##前缀）
CREATE TABLE ##global_temp (
    id INT,
    value NVARCHAR(100)
);

-- 所有会话都可以访问
INSERT INTO ##global_temp VALUES (1, 'Test');

-- 查询
SELECT * FROM ##global_temp;

-- 所有会话结束时自动删除
DROP TABLE ##global_temp;

-- 声明表变量
DECLARE @temp_users TABLE (
    id INT,
    name NVARCHAR(50),
    PRIMARY KEY (id)
);

-- 插入数据
INSERT INTO @temp_users
SELECT id, name FROM users WHERE status = 1;

-- 使用表变量
SELECT * FROM @temp_users;

-- 表变量在批处理结束时自动销毁

-- 创建计算列
CREATE TABLE products (
    id INT PRIMARY KEY,
    name NVARCHAR(100),
    price DECIMAL(10,2),
    quantity INT,
    total AS price * quantity  -- 计算列
);

-- 持久化计算列
CREATE TABLE products (
    id INT PRIMARY KEY,
    name NVARCHAR(100),
    price DECIMAL(10,2),
    quantity INT,
    total AS price * quantity PERSISTED  -- 存储计算结果
);

-- 可以在持久化计算列上创建索引
CREATE INDEX ix_products_total ON products(total);

-- 添加计算列
ALTER TABLE products ADD
    discount_price AS price * 0.9;

-- 添加持久化计算列
ALTER TABLE products ADD
    total_value AS price * quantity PERSISTED;

-- 创建标识列
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,  -- 种子=1，增量=1
    name NVARCHAR(50)
);

-- 自定义种子和增量
CREATE TABLE orders (
    id INT IDENTITY(1000, 10) PRIMARY KEY,  -- 从1000开始，每次增10
    order_date DATE
);

-- 插入数据（自动生成ID）
INSERT INTO users (name) VALUES ('John');
INSERT INTO users (name) VALUES ('Jane');

SELECT * FROM users;
-- id | name
-- 1  | John
-- 2  | Jane

-- 获取最后插入的标识值
INSERT INTO users (name) VALUES ('Bob');
SELECT @@IDENTITY AS [最后ID];

-- 更安全的方式（不受触发器影响）
SELECT SCOPE_IDENTITY() AS [当前作用域ID];

-- 获取表的标识值
SELECT IDENT_CURRENT('users') AS [表最后ID];

-- 重置标识值
DBCC CHECKIDENT ('users', RESEED, 0);

-- 启用标识插入
SET IDENTITY_INSERT users ON;

INSERT INTO users (id, name) VALUES (100, 'Test');

SET IDENTITY_INSERT users OFF;

-- 查看结果
SELECT * FROM users;

-- 创建序列（SQL Server 2012+）
CREATE SEQUENCE seq_user_id
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 1000000
    CYCLE;  -- 到达最大值后循环

-- 创建不循环序列
CREATE SEQUENCE seq_order_id
    AS BIGINT
    START WITH 1000
    INCREMENT BY 1;

-- 获取下一个值
SELECT NEXT VALUE FOR seq_user_id;

-- 插入时使用序列
INSERT INTO users (id, name)
VALUES (NEXT VALUE FOR seq_user_id, 'John');

-- 获取当前值（不增加）
SELECT CURRENT_VALUE FROM sys.sequences WHERE name = 'seq_user_id';

-- 重置序列
ALTER SEQUENCE seq_user_id RESTART WITH 1;

-- 创建分区函数
CREATE PARTITION FUNCTION pf_orders_by_date (DATE)
AS RANGE RIGHT FOR VALUES 
    ('2023-01-01', '2024-01-01', '2025-01-01');

-- 创建分区方案
CREATE PARTITION SCHEME ps_orders_by_date
AS PARTITION pf_orders_by_date
TO (fg_2022, fg_2023, fg_2024, fg_2025);

-- 创建分区表
CREATE TABLE orders (
    id INT IDENTITY(1,1),
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10,2)
) ON ps_orders_by_date(order_date);

-- 使用sp_help
EXEC sp_help 'users';

-- 使用sp_columns
EXEC sp_columns 'users';

-- 查询系统视图
SELECT 
    c.name AS [列名],
    t.name AS [数据类型],
    c.max_length AS [最大长度],
    c.is_nullable AS [允许NULL],
    c.is_identity AS [标识列]
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('users')
ORDER BY c.column_id;

-- 查看约束
SELECT 
    o.name AS [约束名],
    o.type_desc AS [类型],
    c.name AS [列名]
FROM sys.objects o
LEFT JOIN sys.columns c ON o.parent_object_id = c.object_id
WHERE o.parent_object_id = OBJECT_ID('users');

-- 使用sp_helpconstraint
EXEC sp_helpconstraint 'users';

-- 查看表依赖
SELECT 
    OBJECT_NAME(referencing_id) AS [引用对象],
    referenced_entity_name AS [被引用对象]
FROM sys.sql_expression_dependencies
WHERE referenced_id = OBJECT_ID('users');

表设计最佳实践：
├── 选择合适的数据类型
│   ├── 使用最小够用的类型
│   └── 避免过度使用NVARCHAR
│
├── 合理使用NULL
│   ├── 必填字段设置NOT NULL
│   └── 可选字段允许NULL
│
├── 主键设计
│   ├── 推荐使用INT IDENTITY
│   └── 或UNIQUEIDENTIFIER
│
├── 外键设计
│   ├── 确保数据完整性
│   └── 考虑级联操作
│
└── 索引设计
    ├── 主键自动创建聚集索引
    └── 外键列考虑创建索引

-- 使用适当的数据类型
-- 好
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    status TINYINT,  -- 0-255足够
    name NVARCHAR(50)  -- 限制长度
);

-- 避免
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    status INT,  -- 浪费空间
    name NVARCHAR(MAX)  -- 无法创建索引
);