SQL Server 常用操作命令及内置函数大全

sql
-- 1. 查询表中所有数据（全列）
SELECT * FROM 表名;

-- 2. 查询指定字段数据
SELECT 字段1, 字段2, 字段3 FROM 表名;

-- 3. 字段设置别名（AS可省略）
SELECT 字段1 AS 别名1, 字段2 别名2 FROM 表名;

-- 4. 去重查询（去除重复数据行）
SELECT DISTINCT 字段1, 字段2 FROM 表名;

-- 5. 限制查询行数（TOP N）
SELECT TOP 10 * FROM 表名; -- 查询前10行数据
SELECT TOP 50 PERCENT * FROM 表名; -- 查询前50%数据

sql
-- 基础条件查询
SELECT * FROM 表名 WHERE 字段 = '值'; -- 等于
SELECT * FROM 表名 WHERE 字段 > 100; -- 大于
SELECT * FROM 表名 WHERE 字段 <> '值'; -- 不等于（!= 等效）

-- 范围查询 BETWEEN ... AND ...
SELECT * FROM 表名 WHERE 数字字段 BETWEEN 10 AND 50;

-- 包含查询 IN / NOT IN
SELECT * FROM 表名 WHERE 字段 IN ('值1','值2','值3');
SELECT * FROM 表名 WHERE 字段 NOT IN ('值1','值2');

-- 空值判断 IS NULL / IS NOT NULL
SELECT * FROM 表名 WHERE 字段 IS NULL; -- 查询空值数据
SELECT * FROM 表名 WHERE 字段 IS NOT NULL; -- 查询非空数据

-- 模糊查询 LIKE（%匹配任意字符，_匹配单个字符）
SELECT * FROM 表名 WHERE 字段 LIKE '张%'; -- 以张开头
SELECT * FROM 表名 WHERE 字段 LIKE '%三'; -- 以三结尾
SELECT * FROM 表名 WHERE 字段 LIKE '%六%'; -- 包含六
SELECT * FROM 表名 WHERE 字段 LIKE '李_'; -- 李+单个字符

sql
-- 排序：ASC升序（默认），DESC降序
SELECT * FROM 表名 ORDER BY 字段1 ASC, 字段2 DESC;

-- 分页查询：跳过N行，取M行
-- 示例：跳过0行，取10行（第一页，每页10条）
SELECT * FROM 表名 ORDER BY 主键
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

-- 第二页：跳过10行，取10行
SELECT * FROM 表名 ORDER BY 主键
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

sql
-- 基础分组统计
SELECT 分组字段, COUNT(*) 总数, SUM(数字字段) 总和, AVG(数字字段) 平均值
FROM 表名
GROUP BY 分组字段;

-- 分组后条件过滤（HAVING）
SELECT 分组字段, COUNT(*) 数量
FROM 表名
GROUP BY 分组字段
HAVING COUNT(*) > 5; -- 筛选分组数量大于5的组

-- 分组+排序
SELECT 分组字段, MAX(数字字段) 最大值
FROM 表名
GROUP BY 分组字段
ORDER BY 最大值 DESC;

sql
-- 1. 内连接 INNER JOIN（只返回两表匹配数据）
SELECT a.*, b.字段 FROM 表A a
INNER JOIN 表B b ON a.关联字段 = b.关联字段;

-- 2. 左连接 LEFT JOIN（左表所有数据，右表匹配数据，无匹配则为空）
SELECT a.*, b.字段 FROM 表A a
LEFT JOIN 表B b ON a.关联字段 = b.关联字段;

-- 3. 右连接 RIGHT JOIN（右表所有数据，左表匹配数据）
SELECT a.*, b.字段 FROM 表A a
RIGHT JOIN 表B b ON a.关联字段 = b.关联字段;

-- 4. 全连接 FULL JOIN（返回两表所有数据，无匹配则为空）
SELECT a.*, b.字段 FROM 表A a
FULL JOIN 表B b ON a.关联字段 = b.关联字段;

sql
-- 1. 单行子查询
SELECT * FROM 员工表
WHERE 部门ID = (SELECT 部门ID FROM 部门表 WHERE 部门名='技术部');

-- 2. 多行子查询（IN匹配多个结果）
SELECT * FROM 员工表
WHERE 部门ID IN (SELECT 部门ID FROM 部门表 WHERE 部门状态='正常');

-- 3. exists子查询（判断子查询是否有结果，效率高于IN）
SELECT * FROM 员工表 a
WHERE EXISTS (SELECT 1 FROM 工资表 b WHERE a.员工ID = b.员工ID);

sql
-- 1. 插入数据
-- 全字段插入
INSERT INTO 表名 VALUES ('值1','值2',100);
-- 指定字段插入
INSERT INTO 表名(字段1,字段2) VALUES ('值1','值2');

-- 2. 更新数据（务必加WHERE，否则更新全表）
UPDATE 表名 SET 字段1='新值',字段2=200 WHERE 条件;

-- 3. 删除数据
DELETE FROM 表名 WHERE 条件; -- 删除指定数据
TRUNCATE TABLE 表名; -- 清空全表数据（不可回滚、速度快、自增主键重置）

sql
-- 1. 创建数据表
CREATE TABLE 学生表(
    学生ID INT PRIMARY KEY IDENTITY(1,1), -- 自增主键
    姓名 NVARCHAR(20) NOT NULL,
    年龄 INT DEFAULT 18,
    手机号 VARCHAR(11) UNIQUE,
    入学时间 DATETIME
);

-- 2. 修改表结构
ALTER TABLE 表名 ADD 新字段 NVARCHAR(50); -- 新增字段
ALTER TABLE 表名 ALTER COLUMN 字段 INT; -- 修改字段类型
ALTER TABLE 表名 DROP COLUMN 字段名; -- 删除字段

-- 3. 删除数据表
DROP TABLE 表名;

sql
-- 统计总行数
SELECT COUNT(*) FROM 表名;
-- 统计指定字段非空行数
SELECT COUNT(字段) FROM 表名;
-- 统计去重后行数
SELECT COUNT(DISTINCT 字段) FROM 表名;

-- 求和、平均值、最大值、最小值
SELECT
SUM(数字字段) AS 总和,
AVG(数字字段) AS 平均值,
MAX(数字字段) AS 最大值,
MIN(数字字段) AS 最小值
FROM 表名;

sql
-- 1. 字符串拼接
SELECT CONCAT('张三','-','25岁') AS 结果; -- 支持多参数，自动忽略NULL

-- 2. 截取字符串
SELECT LEFT('SQLServer',3); -- 从左截取3位：SQL
SELECT RIGHT('SQLServer',6); -- 从右截取6位：Server
SELECT SUBSTRING('SQLServer',4,3); -- 从第4位开始截取3位：Ser

-- 3. 字符串长度
SELECT LEN('测试文本'); -- 获取字符长度
SELECT DATALENGTH('测试文本'); -- 获取字节长度

-- 4. 大小写转换
SELECT UPPER('sql'); -- 转大写：SQL
SELECT LOWER('SQL'); -- 转小写：sql

-- 5. 去空格
SELECT LTRIM('  测试'); -- 去除左空格
SELECT RTRIM('测试  '); -- 去除右空格
SELECT TRIM('  测试  '); -- 去除首尾空格（2017+版本支持）

-- 6. 替换、查找字符串
SELECT REPLACE('abc123','123','456'); -- 替换：abc456
SELECT CHARINDEX('123','abc123'); -- 查找字符位置，无则返回0

sql
-- 1. 获取当前时间
SELECT GETDATE(); -- 当前系统时间（datetime）
SELECT GETUTCDATE(); -- 世界标准时间
SELECT SYSDATETIME(); -- 高精度当前时间

-- 2. 提取日期部分
SELECT YEAR(GETDATE()) AS 年;
SELECT MONTH(GETDATE()) AS 月;
SELECT DAY(GETDATE()) AS 日;

-- 3. 日期加减
SELECT DATEADD(DAY,7,GETDATE()); -- 当前时间+7天
SELECT DATEADD(MONTH,1,GETDATE()); -- 当前时间+1月
SELECT DATEADD(YEAR,-1,GETDATE()); -- 当前时间-1年

-- 4. 日期差值计算
SELECT DATEDIFF(DAY,'2025-01-01',GETDATE()); -- 天数差
SELECT DATEDIFF(MONTH,'2025-01-01',GETDATE()); -- 月份差

-- 5. 日期格式化
SELECT CONVERT(VARCHAR(10),GETDATE(),23); -- 格式：yyyy-MM-dd
SELECT CONVERT(VARCHAR(19),GETDATE(),120); -- 格式：yyyy-MM-dd HH:mm:ss

sql
SELECT ABS(-10); -- 绝对值：10
SELECT CEILING(3.2); -- 向上取整：4
SELECT FLOOR(3.8); -- 向下取整：3
SELECT ROUND(3.1415,2); -- 四舍五入保留2位小数：3.14
SELECT RAND(); -- 生成0-1随机小数
SELECT POWER(2,3); -- 幂运算：2的3次方=8
SELECT SQRT(16); -- 开平方：4

sql
-- CAST 转换（通用转换）
SELECT CAST('123' AS INT); -- 字符串转数字
SELECT CAST(123 AS VARCHAR(10)); -- 数字转字符串

-- CONVERT 转换（支持日期格式化，专用）
SELECT CONVERT(VARCHAR,123456); -- 数字转字符串
SELECT CONVERT(DATETIME,'2025-01-01'); -- 字符串转日期

sql
-- ISNULL 空值替换（字段为空则替换为指定值）
SELECT ISNULL(字段,'默认值') FROM 表名;

-- NULLIF 相等则返回空，否则返回原值
SELECT NULLIF(字段,'0'); -- 字段值为0则返回NULL

-- CASE 多条件判断（万能逻辑判断）
SELECT
    姓名,
    年龄,
    CASE
        WHEN 年龄 < 18 THEN '未成年'
        WHEN 年龄 BETWEEN 18 AND 35 THEN '青年'
        ELSE '中年及以上'
    END AS 年龄段
FROM 员工表;

sql
-- 查看数据库列表
SELECT name FROM sys.databases;

-- 查看当前数据库所有表
SELECT name FROM sys.tables;

-- 查看表结构
SP_HELP '表名';

-- 查看SQL执行计划（优化查询）
SET SHOWPLAN_XML ON;

-- 清除缓存、优化执行
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

sql
-- 方式一：CTE递归生成1-100序号（最通用、推荐、无依赖）
WITH NumSeq AS (
    SELECT 1 AS SerialNum
    UNION ALL
    SELECT SerialNum + 1 FROM NumSeq WHERE SerialNum < 100
)
SELECT SerialNum FROM NumSeq OPTION (MAXRECURSION 100);

-- 方式二：系统表快速生成（简洁高效，无需递归配置）
SELECT TOP 100 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS SerialNum
FROM sys.all_columns a CROSS JOIN sys.all_columns b;

-- 方式三：创建临时表存储1-100序号（可反复调用）
-- 生成序号并存入临时表
WITH NumSeq AS (
    SELECT 1 AS SerialNum
    UNION ALL
    SELECT SerialNum + 1 FROM NumSeq WHERE SerialNum < 100
)
SELECT SerialNum INTO #Temp_Num FROM NumSeq OPTION (MAXRECURSION 100);

-- 查询临时表序号数据
SELECT * FROM #Temp_Num;

-- 使用完毕删除临时表
DROP TABLE IF EXISTS #Temp_Num;

sql
-- 对比表A、表B总行数
SELECT
 (SELECT COUNT(*) FROM 表A) AS 表A总行数,
 (SELECT COUNT(*) FROM 表B) AS 表B总行数;

sql
-- 方式1：EXCEPT 极简差异对比（推荐，自动比对所有字段）
-- 查询：表A存在、表B不存在的数据
SELECT * FROM 表A
EXCEPT
SELECT * FROM 表B;

-- 查询：表B存在、表A不存在的数据
SELECT * FROM 表B
EXCEPT
SELECT * FROM 表A;

-- 方式2：EXISTS 精准比对（指定主键/唯一键，效率更高，大数据量推荐）
-- 主键不一致数据：表A独有数据
SELECT A.* FROM 表A A
WHERE NOT EXISTS(SELECT 1 FROM 表B B WHERE A.主键 = B.主键);

-- 主键不一致数据：表B独有数据
SELECT B.* FROM 表B B
WHERE NOT EXISTS(SELECT 1 FROM 表A A WHERE A.主键 = B.主键);

sql
-- 仅对比核心字段，查询数据不一致记录
SELECT A.主键, A.金额 AS A表金额, B.金额 AS B表金额, A.状态 AS A表状态, B.状态 AS B表状态
FROM 表A A
INNER JOIN 表B B ON A.主键 = B.主键
WHERE A.金额 <> B.金额 OR A.状态 <> B.状态;

sql
-- 替换 表A、表B 为需要对比的表名
SELECT
 ISNULL(A.COLUMN_NAME,B.COLUMN_NAME) AS 字段名,
 A.DATA_TYPE AS A表数据类型,
 A.CHARACTER_MAX_LENGTH AS A表字段长度,
 A.IS_NULLABLE AS A表是否为空,
 B.DATA_TYPE AS B表数据类型,
 B.CHARACTER_MAX_LENGTH AS B表字段长度,
 B.IS_NULLABLE AS B表是否为空,
 CASE
  WHEN A.COLUMN_NAME IS NULL THEN 'B表独有字段'
  WHEN B.COLUMN_NAME IS NULL THEN 'A表独有字段'
  WHEN A.DATA_TYPE<>B.DATA_TYPE OR A.CHARACTER_MAX_LENGTH<>B.CHARACTER_MAX_LENGTH OR A.IS_NULLABLE<>B.IS_NULLABLE THEN '结构不一致'
  ELSE '结构一致'
 END AS 差异状态
FROM
(SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAX_LENGTH,IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='表A') A
FULL JOIN
(SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAX_LENGTH,IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='表B') B
ON A.COLUMN_NAME = B.COLUMN_NAME
ORDER BY 字段名;