SQL Server基础语法 #

-- 这是单行注释
SELECT * FROM users;  -- 行尾注释

-- 查询所有用户
SELECT * FROM users;

/*
 * 这是多行注释
 * 可以跨越多行
 * 用于详细说明
 */
SELECT * FROM users;

/*
    功能：查询活跃用户
    作者：Admin
    日期：2024-01-15
*/
SELECT * FROM users WHERE status = 1;

/*
 * ============================================
 * 存储过程：usp_GetUserInfo
 * 功能：获取用户信息
 * 参数：@UserId - 用户ID
 * 返回：用户详细信息
 * 创建：2024-01-15
 * 修改：2024-01-20 添加状态过滤
 * ============================================
 */
CREATE PROCEDURE usp_GetUserInfo
    @UserId INT
AS
BEGIN
    -- 验证参数
    IF @UserId IS NULL
    BEGIN
        -- 参数无效，返回错误
        RAISERROR('用户ID不能为空', 16, 1);
        RETURN;
    END
    
    -- 查询用户信息
    SELECT * FROM users WHERE id = @UserId;
END

-- 常规标识符规则：
-- 1. 以字母（a-z, A-Z）或下划线(_)开头
-- 2. 后续可以是字母、数字、下划线、@、$
-- 3. 不能是保留关键字
-- 4. 长度1-128个字符

-- 正确的标识符
CREATE TABLE users (
    id INT,
    user_name VARCHAR(50),
    _status BIT,
    email_address VARCHAR(100)
);

-- 变量标识符（以@开头）
DECLARE @userId INT;
DECLARE @userName NVARCHAR(50);

-- 临时表标识符（以#开头）
CREATE TABLE #temp_users (id INT, name VARCHAR(50));

-- 全局临时表（以##开头）
CREATE TABLE ##global_temp (id INT);

-- 使用方括号 [] 或双引号 ""
-- 用于包含特殊字符或关键字的标识符

-- 使用方括号（推荐）
CREATE TABLE [user] (
    [id] INT,
    [name] VARCHAR(50),
    [create date] DATETIME,  -- 包含空格
    [select] INT             -- 使用关键字
);

-- 使用双引号（需设置QUOTED_IDENTIFIER）
SET QUOTED_IDENTIFIER ON;
CREATE TABLE "order" (
    "id" INT,
    "order date" DATETIME
);

-- 查询时也需要使用分隔符
SELECT [id], [name], [create date] FROM [user];

推荐命名规范：
├── 表名
│   ├── 使用单数名词：user, order, product
│   ├── 小写字母+下划线：user_profile
│   └── 或帕斯卡命名：UserProfile
│
├── 列名
│   ├── 小写字母+下划线：user_name
│   ├── 或驼峰命名：userName
│   └── 有意义的名称：created_at, is_active
│
├── 主键
│   └── 表名_id 或 id：user_id, id
│
├── 外键
│   └── 关联表名_id：order_id, product_id
│
├── 索引
│   └── ix_表名_列名：ix_users_email
│
├── 约束
│   └── ck_表名_列名：ck_users_age
│
├── 存储过程
│   └── usp_功能描述：usp_get_user_info
│
├── 视图
│   └── vw_功能描述：vw_user_orders
│
└── 触发器
    └── tr_表名_操作：tr_users_insert

-- 以下是SQL Server保留关键字，不能直接用作标识符
-- 如需使用，必须使用分隔符

-- 常见保留关键字
SELECT, FROM, WHERE, INSERT, UPDATE, DELETE
CREATE, ALTER, DROP, TABLE, VIEW, INDEX
AND, OR, NOT, IN, LIKE, BETWEEN
JOIN, INNER, LEFT, RIGHT, OUTER, ON
GROUP, BY, HAVING, ORDER, ASC, DESC
UNION, ALL, DISTINCT, TOP
NULL, DEFAULT, PRIMARY, KEY, FOREIGN
CONSTRAINT, CHECK, UNIQUE, IDENTITY
PROCEDURE, FUNCTION, TRIGGER
BEGIN, END, IF, ELSE, WHILE, RETURN
DECLARE, SET, PRINT, EXEC
COMMIT, ROLLBACK, TRANSACTION

-- 错误：直接使用关键字
-- CREATE TABLE select (id INT);  -- 语法错误

-- 正确：使用分隔符
CREATE TABLE [select] (
    [id] INT,
    [from] VARCHAR(50),
    [where] VARCHAR(100)
);

-- 查询
SELECT [id], [from], [where] FROM [select];

-- 创建数据库
CREATE DATABASE mydb;

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY IDENTITY(1,1),
    name NVARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 0 AND age <= 150),
    created_at DATETIME DEFAULT GETDATE()
);

-- 创建索引
CREATE INDEX ix_users_email ON users(email);

-- 创建视图
CREATE VIEW vw_active_users AS
SELECT id, name, email FROM users WHERE status = 1;

-- 创建存储过程
CREATE PROCEDURE usp_get_user
    @id INT
AS
BEGIN
    SELECT * FROM users WHERE id = @id;
END

-- 修改表结构
ALTER TABLE users ADD phone VARCHAR(20);

ALTER TABLE users ALTER COLUMN name NVARCHAR(100);

ALTER TABLE users DROP COLUMN phone;

-- 修改存储过程
ALTER PROCEDURE usp_get_user
    @id INT,
    @status BIT = 1
AS
BEGIN
    SELECT * FROM users WHERE id = @id AND status = @status;
END

-- 删除表
DROP TABLE IF EXISTS users;

-- 删除数据库
DROP DATABASE IF EXISTS mydb;

-- 删除存储过程
DROP PROCEDURE IF EXISTS usp_get_user;

-- 删除视图
DROP VIEW IF EXISTS vw_active_users;

-- 基本查询
SELECT * FROM users;

-- 条件查询
SELECT * FROM users WHERE age > 20;

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 分页
SELECT * FROM users 
ORDER BY id 
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

-- TOP限制
SELECT TOP 10 * FROM users;

-- 插入单条
INSERT INTO users (name, email, age)
VALUES ('John', 'john@example.com', 25);

-- 插入多条
INSERT INTO users (name, email, age)
VALUES 
    ('Jane', 'jane@example.com', 30),
    ('Bob', 'bob@example.com', 28);

-- 从查询插入
INSERT INTO users_backup
SELECT * FROM users WHERE status = 0;

-- 更新数据
UPDATE users SET age = 26 WHERE name = 'John';

-- 更新多列
UPDATE users 
SET age = 26, email = 'john_new@example.com'
WHERE id = 1;

-- 使用表达式更新
UPDATE users SET age = age + 1 WHERE status = 1;

-- 删除数据
DELETE FROM users WHERE id = 1;

-- 条件删除
DELETE FROM users WHERE status = 0;

-- 删除所有数据
DELETE FROM users;
-- 或
TRUNCATE TABLE users;

-- 授予SELECT权限
GRANT SELECT ON users TO user1;

-- 授予多个权限
GRANT SELECT, INSERT, UPDATE ON users TO user1;

-- 授予所有权限
GRANT ALL ON users TO user1;

-- 授予执行存储过程权限
GRANT EXECUTE ON usp_get_user TO user1;

-- 撤销SELECT权限
REVOKE SELECT ON users FROM user1;

-- 撤销所有权限
REVOKE ALL ON users FROM user1;

-- 拒绝权限（优先级高于GRANT）
DENY SELECT ON users TO user1;

-- 开始事务
BEGIN TRANSACTION;
-- 或
BEGIN TRAN;

-- 带名称的事务
BEGIN TRANSACTION myTransaction;

BEGIN TRANSACTION;

INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');

-- 提交事务
COMMIT TRANSACTION;
-- 或
COMMIT;

BEGIN TRANSACTION;

INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');

-- 回滚事务
ROLLBACK TRANSACTION;
-- 或
ROLLBACK;

BEGIN TRY
    BEGIN TRANSACTION;
    
    -- 操作1
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    
    -- 操作2
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    
    -- 提交事务
    COMMIT TRANSACTION;
    PRINT '事务成功';
END TRY
BEGIN CATCH
    -- 回滚事务
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    
    PRINT '事务失败: ' + ERROR_MESSAGE();
END CATCH

-- GO 用于分隔批处理
-- 每个批处理独立编译和执行

-- 批处理1
SELECT * FROM users;
GO

-- 批处理2
SELECT * FROM orders;
GO

-- 创建存储过程需要单独批处理
CREATE PROCEDURE usp_test
AS
BEGIN
    SELECT * FROM users;
END
GO

-- 执行存储过程
EXEC usp_test;
GO

-- 规则1：CREATE PROCEDURE/FUNCTION/TRIGGER/VIEW
-- 必须是批处理中的第一条语句

-- 错误示例
SELECT * FROM users;
CREATE PROCEDURE usp_test AS SELECT 1;
GO

-- 正确示例
SELECT * FROM users;
GO
CREATE PROCEDURE usp_test AS SELECT 1;
GO

-- 规则2：某些命令不能在同一批处理中
-- 如：USE database 后不能直接创建对象

USE master;
GO
CREATE DATABASE testdb;
GO

-- 声明变量（以@开头）
DECLARE @name NVARCHAR(50);
DECLARE @age INT;
DECLARE @status BIT = 1;  -- 声明并赋值

-- 赋值
SET @name = 'John';
SET @age = 25;

-- 使用SELECT赋值
SELECT @name = name FROM users WHERE id = 1;

-- 使用变量
SELECT * FROM users WHERE name = @name AND age = @age;

-- 打印变量
PRINT @name;
PRINT 'Name: ' + @name;

-- 声明表变量
DECLARE @temp_users TABLE (
    id INT,
    name NVARCHAR(50),
    email VARCHAR(100)
);

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

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

-- 全局变量（以@@开头）由系统定义

-- 当前版本
SELECT @@VERSION;

-- 受影响的行数
INSERT INTO users (name) VALUES ('Test');
SELECT @@ROWCOUNT AS '受影响行数';

-- 事务计数
SELECT @@TRANCOUNT;

-- 最后插入的标识值
INSERT INTO users (name) VALUES ('Test');
SELECT @@IDENTITY AS '最后ID';

-- 错误号
SELECT @@ERROR;

SELECT 10 + 5 AS '加法';    -- 15
SELECT 10 - 5 AS '减法';    -- 5
SELECT 10 * 5 AS '乘法';    -- 50
SELECT 10 / 5 AS '除法';    -- 2
SELECT 10 % 3 AS '取模';    -- 1

-- 等于
SELECT * FROM users WHERE age = 25;

-- 不等于
SELECT * FROM users WHERE age != 25;
SELECT * FROM users WHERE age <> 25;

-- 大于/小于
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age < 25;
SELECT * FROM users WHERE age >= 25;
SELECT * FROM users WHERE age <= 25;

-- 安全等于（可比较NULL）
SELECT * FROM users WHERE email <=> NULL;

-- AND
SELECT * FROM users WHERE age > 20 AND status = 1;

-- OR
SELECT * FROM users WHERE age < 20 OR age > 50;

-- NOT
SELECT * FROM users WHERE NOT status = 0;

-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- IN
SELECT * FROM users WHERE age IN (20, 25, 30);

-- LIKE
SELECT * FROM users WHERE name LIKE 'J%';

-- IS NULL
SELECT * FROM users WHERE email IS NULL;

-- 使用 + 连接字符串
SELECT 'Hello' + ' ' + 'World';

-- 处理NULL
SELECT 'Hello' + ISNULL(name, 'Guest') FROM users;

-- CONCAT函数（自动处理NULL）
SELECT CONCAT('Hello', ' ', NULL, 'World');  -- Hello World