SQL Server基础查询 #

SELECT [ALL | DISTINCT] column_list
[INTO new_table]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC]]
[OFFSET offset_row_count {ROW | ROWS} 
 [FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY]];

-- 创建示例表
CREATE TABLE users (
    id INT PRIMARY KEY IDENTITY(1,1),
    name NVARCHAR(50),
    email VARCHAR(100),
    age INT,
    status BIT DEFAULT 1,
    salary DECIMAL(10,2),
    department_id INT,
    created_at DATETIME2 DEFAULT SYSDATETIME()
);

INSERT INTO users (name, email, age, status, salary, department_id) VALUES
    ('John', 'john@example.com', 25, 1, 5000.00, 1),
    ('Jane', 'jane@example.com', 30, 1, 6000.00, 1),
    ('Bob', 'bob@example.com', 28, 0, 4500.00, 2),
    ('Alice', 'alice@example.com', 22, 1, 4000.00, 2),
    ('Charlie', 'charlie@example.com', 35, 0, 7000.00, 1),
    ('David', 'david@example.com', 32, 1, 5500.00, 2),
    ('Eve', 'eve@example.com', 27, 1, 4800.00, 1);

-- 查询所有列
SELECT * FROM users;

-- 结果
id | name    | email              | age | status | salary  | department_id | created_at
---|---------|--------------------|-----|--------|---------|---------------|--------------------
1  | John    | john@example.com   | 25  | 1      | 5000.00 | 1             | 2024-01-15 10:00:00
2  | Jane    | jane@example.com   | 30  | 1      | 6000.00 | 1             | 2024-01-15 11:00:00
...

-- 查询指定列
SELECT name, age, email FROM users;

-- 结果
name    | age | email
--------|-----|--------------------
John    | 25  | john@example.com
Jane    | 30  | jane@example.com
...

-- 使用AS关键字
SELECT 
    name AS user_name,
    age AS user_age,
    email AS user_email
FROM users;

-- 省略AS关键字
SELECT name user_name, age user_age FROM users;

-- 使用引号（包含空格或特殊字符）
SELECT name AS '用户名', age AS '年龄' FROM users;

-- 使用等号（SQL Server特有）
SELECT 
    user_name = name,
    user_age = age
FROM users;

-- 使用表别名
SELECT u.name, u.age, u.email
FROM users AS u;

-- 省略AS
SELECT u.name, u.age FROM users u;

-- 多表查询时使用别名
SELECT u.name, d.department_name
FROM users u
INNER JOIN departments d ON u.department_id = d.id;

-- 查询所有年龄（包含重复）
SELECT age FROM users;

-- 查询不重复的年龄
SELECT DISTINCT age FROM users;

-- 结果
age
----
22
25
27
28
30
32
35

-- 多列组合去重
SELECT DISTINCT age, status FROM users;

-- 结果
age | status
----|--------
22  | 1
25  | 1
27  | 1
28  | 0
30  | 1
32  | 1
35  | 0

-- 统计不同年龄的数量
SELECT COUNT(DISTINCT age) AS distinct_ages FROM users;

-- 统计不同年龄和状态组合的数量
SELECT COUNT(DISTINCT CONCAT(age, '-', status)) AS distinct_combinations 
FROM users;

-- 等于
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;

-- AND：同时满足多个条件
SELECT * FROM users WHERE age > 25 AND status = 1;

-- OR：满足任一条件
SELECT * FROM users WHERE age < 25 OR age > 30;

-- NOT：取反
SELECT * FROM users WHERE NOT status = 0;

-- 组合使用（注意优先级）
SELECT * FROM users 
WHERE (age < 25 OR age > 30) AND status = 1;

-- 范围查询（包含边界）
SELECT * FROM users WHERE age BETWEEN 25 AND 30;

-- 等价于
SELECT * FROM users WHERE age >= 25 AND age <= 30;

-- NOT BETWEEN
SELECT * FROM users WHERE age NOT BETWEEN 25 AND 30;

-- 日期范围
SELECT * FROM users 
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

-- 列表匹配
SELECT * FROM users WHERE age IN (25, 30, 35);

-- 等价于
SELECT * FROM users WHERE age = 25 OR age = 30 OR age = 35;

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

-- 子查询
SELECT * FROM users WHERE department_id IN (
    SELECT id FROM departments WHERE location = 'Beijing'
);

-- % 匹配任意多个字符
SELECT * FROM users WHERE name LIKE 'J%';    -- 以J开头
SELECT * FROM users WHERE name LIKE '%n';    -- 以n结尾
SELECT * FROM users WHERE name LIKE '%oh%';  -- 包含oh

-- _ 匹配单个字符
SELECT * FROM users WHERE name LIKE 'J_hn';  -- John

-- [] 匹配字符集
SELECT * FROM users WHERE name LIKE '[ABC]%';  -- 以A、B或C开头
SELECT * FROM users WHERE name LIKE '[A-D]%';  -- 以A到D开头

-- [^] 不匹配字符集
SELECT * FROM users WHERE name LIKE '[^ABC]%';  -- 不以A、B或C开头

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

-- ESCAPE转义
SELECT * FROM users WHERE email LIKE '%@%' ESCAPE '@';

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

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

-- 错误写法
SELECT * FROM users WHERE email = NULL;    -- 不会返回结果
SELECT * FROM users WHERE email != NULL;   -- 不会返回结果

-- 默认升序（ASC）
SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age ASC;

-- 降序（DESC）
SELECT * FROM users ORDER BY age DESC;

-- 先按status排序，再按age排序
SELECT * FROM users ORDER BY status ASC, age DESC;

-- 结果
id | name    | age | status
---|---------|-----|--------
2  | Jane    | 30  | 1
6  | David   | 32  | 1
1  | John    | 25  | 1
7  | Eve     | 27  | 1
4  | Alice   | 22  | 1
5  | Charlie | 35  | 0
3  | Bob     | 28  | 0

-- 按计算结果排序
SELECT name, age, age * 2 AS double_age 
FROM users 
ORDER BY double_age;

-- 按函数结果排序
SELECT * FROM users ORDER BY LEN(name);

-- 按列位置排序
SELECT name, age FROM users ORDER BY 2;  -- 按第二列（age）排序

-- 使用CASE WHEN
SELECT * FROM users 
ORDER BY CASE status
    WHEN 1 THEN 1
    WHEN 0 THEN 2
    ELSE 3
END;

-- 使用CHARINDEX
SELECT * FROM users 
ORDER BY CHARINDEX(name, 'John,Jane,Bob');

-- NULL值排在最后
SELECT * FROM users ORDER BY email DESC;

-- NULL值排在最前（SQL Server 2022+）
SELECT * FROM users ORDER BY email NULLS FIRST;

-- 使用ISNULL处理
SELECT * FROM users ORDER BY ISNULL(email, 'zzz');

-- 返回前3条
SELECT TOP 3 * FROM users;

-- 使用百分比
SELECT TOP 50 PERCENT * FROM users;

-- WITH TIES（包含并列值）
SELECT TOP 3 WITH TIES * FROM users ORDER BY salary DESC;

-- 结果可能超过3条，因为salary相同的都会返回

-- SQL Server 2012+ 推荐分页方式
-- OFFSET offset ROWS FETCH NEXT count ROWS ONLY

-- 第1页（每页3条）
SELECT * FROM users 
ORDER BY id 
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

-- 第2页
SELECT * FROM users 
ORDER BY id 
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

-- 第3页
SELECT * FROM users 
ORDER BY id 
OFFSET 6 ROWS FETCH NEXT 3 ROWS ONLY;

-- 第n页，每页m条
-- offset = (n - 1) * m

DECLARE @page INT = 2;
DECLARE @pageSize INT = 3;

SELECT * FROM users 
ORDER BY id 
OFFSET (@page - 1) * @pageSize ROWS 
FETCH NEXT @pageSize ROWS ONLY;

-- 传统分页方式（SQL Server 2005+）
WITH numbered_users AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM users
)
SELECT * FROM numbered_users
WHERE row_num BETWEEN 4 AND 6;  -- 第2页，每页3条

-- 统计行数
SELECT COUNT(*) AS total FROM users;

-- 统计非NULL值数量
SELECT COUNT(email) AS email_count FROM users;

-- 统计不重复值的数量
SELECT COUNT(DISTINCT age) AS distinct_ages FROM users;

-- 求和
SELECT SUM(salary) AS total_salary FROM users;

-- 条件求和
SELECT SUM(CASE WHEN status = 1 THEN salary ELSE 0 END) AS active_salary
FROM users;

-- 平均值
SELECT AVG(salary) AS avg_salary FROM users;

-- 保留小数位
SELECT ROUND(AVG(salary), 2) AS avg_salary FROM users;

-- 条件平均
SELECT AVG(CASE WHEN status = 1 THEN salary END) AS avg_active_salary
FROM users;

-- 最大值
SELECT MAX(salary) AS max_salary FROM users;

-- 最小值
SELECT MIN(salary) AS min_salary FROM users;

-- 同时获取
SELECT 
    MAX(salary) AS max_salary, 
    MIN(salary) AS min_salary,
    MAX(salary) - MIN(salary) AS salary_range
FROM users;

SELECT 
    COUNT(*) AS total,
    SUM(salary) AS total_salary,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    STDEV(salary) AS salary_stdev
FROM users;

-- 按状态分组统计
SELECT status, COUNT(*) AS count
FROM users
GROUP BY status;

-- 结果
status | count
-------|-------
0      | 2
1      | 5

-- 按多列分组
SELECT 
    department_id,
    status,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM users
GROUP BY department_id, status;

SELECT 
    department_id,
    COUNT(*) AS employee_count,
    SUM(salary) AS total_salary,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM users
GROUP BY department_id;

-- 多个分组集合
SELECT 
    department_id,
    status,
    COUNT(*) AS count,
    SUM(salary) AS total_salary
FROM users
GROUP BY GROUPING SETS (
    (department_id, status),
    (department_id),
    (status),
    ()
);

-- 等价于多个GROUP BY UNION ALL

-- ROLLUP：层次分组
SELECT 
    department_id,
    status,
    COUNT(*) AS count
FROM users
GROUP BY ROLLUP (department_id, status);

-- CUBE：所有组合
SELECT 
    department_id,
    status,
    COUNT(*) AS count
FROM users
GROUP BY CUBE (department_id, status);

-- WHERE：过滤行，在分组前执行
SELECT status, COUNT(*) AS count
FROM users
WHERE age > 25
GROUP BY status;

-- HAVING：过滤分组，在分组后执行
SELECT status, COUNT(*) AS count
FROM users
GROUP BY status
HAVING COUNT(*) > 1;

-- 筛选平均工资大于5000的部门
SELECT department_id, AVG(salary) AS avg_salary
FROM users
GROUP BY department_id
HAVING AVG(salary) > 5000;

-- 筛选人数大于2的部门
SELECT department_id, COUNT(*) AS count
FROM users
GROUP BY department_id
HAVING COUNT(*) > 2;

SELECT 
    department_id,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM users
WHERE age >= 20
GROUP BY department_id
HAVING COUNT(*) > 1 AND AVG(salary) > 4500
ORDER BY avg_salary DESC;

SELECT DISTINCT column_list
INTO new_table
FROM table_source
JOIN another_table ON condition
WHERE row_condition
GROUP BY column_list
HAVING group_condition
ORDER BY column_list
OFFSET offset ROWS FETCH NEXT count ROWS ONLY;

1. FROM        → 确定数据来源
2. JOIN        → 连接表
3. ON          → 连接条件
4. WHERE       → 过滤行
5. GROUP BY    → 分组
6. HAVING      → 过滤分组
7. SELECT      → 选择列
8. DISTINCT    → 去重
9. ORDER BY    → 排序
10. OFFSET/FETCH → 分页

SELECT department_id, COUNT(*) AS count, AVG(salary) AS avg_salary
FROM users
WHERE age >= 20
GROUP BY department_id
HAVING count > 1
ORDER BY avg_salary DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

-- 执行顺序：
-- 1. FROM users          → 获取users表数据
-- 2. WHERE age >= 20     → 过滤年龄>=20的行
-- 3. GROUP BY department_id → 按department_id分组
-- 4. HAVING count > 1    → 过滤分组
-- 5. SELECT ...          → 选择列并计算聚合
-- 6. ORDER BY avg_salary → 排序
-- 7. OFFSET/FETCH        → 分页

-- NOLOCK（脏读）
SELECT * FROM users WITH (NOLOCK);

-- UPDLOCK（更新锁）
SELECT * FROM users WITH (UPDLOCK) WHERE id = 1;

-- XLOCK（排他锁）
SELECT * FROM users WITH (XLOCK) WHERE id = 1;

-- 强制使用索引
SELECT * FROM users WITH (INDEX(ix_users_email))
WHERE email = 'john@example.com';

-- 强制扫描
SELECT * FROM users WITH (INDEX(0));