基础查询 #

-- 基本查询语法
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column
LIMIT n;

-- 示例表
CREATE TABLE employees (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    status VARCHAR(20) DEFAULT 'active'
);

INSERT INTO employees (name, department, salary, hire_date, status) VALUES
    ('Alice', 'Engineering', 80000, '2020-01-15', 'active'),
    ('Bob', 'Sales', 65000, '2019-06-20', 'active'),
    ('Carol', 'Engineering', 90000, '2018-03-10', 'active'),
    ('David', 'Marketing', 70000, '2021-02-28', 'active'),
    ('Eve', 'Engineering', 85000, '2020-08-05', 'inactive'),
    ('Frank', 'Sales', 60000, '2022-01-01', 'active'),
    ('Grace', 'HR', 55000, '2019-11-15', 'active'),
    ('Henry', 'Engineering', 95000, '2017-05-20', 'active');

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

-- 查询指定列
SELECT name, department, salary FROM employees;

-- 使用 AS 设置列别名
SELECT 
    name AS employee_name,
    department AS dept,
    salary AS monthly_salary
FROM employees;

-- 别名可以省略 AS
SELECT name employee_name, department dept FROM employees;

-- 使用表达式别名
SELECT 
    name,
    salary,
    salary * 12 AS annual_salary
FROM employees;

-- DISTINCT 去重
SELECT DISTINCT department FROM employees;

-- 多列去重
SELECT DISTINCT department, status FROM employees;

-- 计算去重数量
SELECT COUNT(DISTINCT department) AS dept_count FROM employees;

-- 等于
SELECT * FROM employees WHERE department = 'Engineering';

-- 不等于
SELECT * FROM employees WHERE status != 'inactive';
SELECT * FROM employees WHERE status <> 'inactive';

-- 大于、小于
SELECT * FROM employees WHERE salary > 80000;
SELECT * FROM employees WHERE salary >= 80000;
SELECT * FROM employees WHERE salary < 70000;
SELECT * FROM employees WHERE salary <= 70000;

-- 日期比较
SELECT * FROM employees WHERE hire_date > '2020-01-01';
SELECT * FROM employees WHERE hire_date >= '2020-01-01';

-- AND 与
SELECT * FROM employees 
WHERE department = 'Engineering' AND salary > 85000;

-- OR 或
SELECT * FROM employees 
WHERE department = 'Engineering' OR department = 'Sales';

-- NOT 非
SELECT * FROM employees 
WHERE NOT status = 'inactive';

-- 组合使用
SELECT * FROM employees 
WHERE (department = 'Engineering' OR department = 'Sales')
  AND salary > 70000;

-- BETWEEN ... AND
SELECT * FROM employees 
WHERE salary BETWEEN 60000 AND 80000;

-- 等价于
SELECT * FROM employees 
WHERE salary >= 60000 AND salary <= 80000;

-- 日期范围
SELECT * FROM employees 
WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31';

-- NOT BETWEEN
SELECT * FROM employees 
WHERE salary NOT BETWEEN 60000 AND 80000;

-- IN 列表
SELECT * FROM employees 
WHERE department IN ('Engineering', 'Sales');

-- 等价于
SELECT * FROM employees 
WHERE department = 'Engineering' OR department = 'Sales';

-- NOT IN
SELECT * FROM employees 
WHERE department NOT IN ('HR', 'Marketing');

-- 子查询 IN
SELECT * FROM employees 
WHERE department IN (
    SELECT DISTINCT department FROM employees WHERE salary > 80000
);

-- % 匹配任意字符
SELECT * FROM employees WHERE name LIKE 'A%';    -- 以A开头
SELECT * FROM employees WHERE name LIKE '%e';    -- 以e结尾
SELECT * FROM employees WHERE name LIKE '%a%';   -- 包含a

-- _ 匹配单个字符
SELECT * FROM employees WHERE name LIKE '_ob';   -- 第二个字符开始是ob
SELECT * FROM employees WHERE name LIKE 'A____'; -- A开头，共5个字符

-- NOT LIKE
SELECT * FROM employees WHERE name NOT LIKE 'A%';

-- 转义特殊字符
SELECT * FROM employees WHERE name LIKE '%\%%';  -- 包含%
SELECT * FROM employees WHERE name LIKE '%\_%';  -- 包含_

-- IS NULL
SELECT * FROM employees WHERE department IS NULL;

-- IS NOT NULL
SELECT * FROM employees WHERE department IS NOT NULL;

-- NULL 比较注意
SELECT * FROM employees WHERE department = NULL;  -- 错误！不会返回结果
SELECT * FROM employees WHERE department IS NULL; -- 正确

-- COALESCE 处理 NULL
SELECT 
    name,
    COALESCE(department, 'Unassigned') AS dept
FROM employees;

-- IFNULL 处理 NULL
SELECT 
    name,
    IFNULL(department, 'Unassigned') AS dept
FROM employees;

-- 升序 (默认)
SELECT * FROM employees ORDER BY salary;
SELECT * FROM employees ORDER BY salary ASC;

-- 降序
SELECT * FROM employees ORDER BY salary DESC;

-- 多列排序
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;

-- 按列位置排序
SELECT name, salary FROM employees 
ORDER BY 2 DESC;  -- 按第二列排序

-- 按表达式排序
SELECT name, salary, salary * 12 AS annual_salary
FROM employees
ORDER BY salary * 12 DESC;

-- 使用别名排序
SELECT name, salary, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

-- NULL 值排序
-- 默认: 升序时 NULL 在前，降序时 NULL 在后

-- 使用 IS NULL 控制顺序
SELECT * FROM employees 
ORDER BY department IS NULL, department;

-- 或使用 COALESCE
SELECT * FROM employees 
ORDER BY COALESCE(department, 'ZZZ');

-- 限制返回行数
SELECT * FROM employees LIMIT 5;

-- 跳过前N行
SELECT * FROM employees LIMIT 3, 5;  -- 跳过前3行，返回5行

-- 推荐写法
SELECT * FROM employees LIMIT 5 OFFSET 3;  -- 跳过前3行，返回5行

-- 分页查询
-- 第1页 (每页10条)
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 0;

-- 第2页
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 10;

-- 第N页
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET (N-1)*10;

-- 传统分页 (大数据量时性能差)
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 1000000;

-- 优化方式1: 使用 WHERE 条件
SELECT * FROM employees 
WHERE id > 1000000 
ORDER BY id LIMIT 10;

-- 优化方式2: 使用子查询
SELECT * FROM employees 
WHERE id IN (
    SELECT id FROM employees 
    ORDER BY id LIMIT 10 OFFSET 1000000
)
ORDER BY id;

-- 优化方式3: 记住上一页最后ID
SELECT * FROM employees 
WHERE id > ?  -- 上一页最后ID
ORDER BY id LIMIT 10;

-- 获取总记录数
SELECT COUNT(*) AS total FROM employees;

-- 分页查询带总数
SELECT 
    (SELECT COUNT(*) FROM employees) AS total,
    e.*
FROM employees e
ORDER BY id
LIMIT 10 OFFSET 0;

-- COUNT 计数
SELECT COUNT(*) AS total_count FROM employees;
SELECT COUNT(department) AS dept_count FROM employees;  -- 不计NULL
SELECT COUNT(DISTINCT department) AS unique_depts FROM employees;

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

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

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

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

-- 组合使用
SELECT 
    COUNT(*) AS total,
    SUM(salary) AS total_salary,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees;

-- 按部门分组统计
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department;

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

-- 使用表达式分组
SELECT 
    CASE 
        WHEN salary < 60000 THEN 'Low'
        WHEN salary < 80000 THEN 'Medium'
        ELSE 'High'
    END AS salary_level,
    COUNT(*) AS count
FROM employees
GROUP BY 
    CASE 
        WHEN salary < 60000 THEN 'Low'
        WHEN salary < 80000 THEN 'Medium'
        ELSE 'High'
    END;

-- HAVING 过滤分组结果
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

-- HAVING vs WHERE
-- WHERE 过滤行，HAVING 过滤分组
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'  -- 先过滤行
GROUP BY department
HAVING AVG(salary) > 70000;  -- 再过滤分组

-- 复杂 HAVING 条件
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 1 AND AVG(salary) > 60000;

-- 分组汇总
SELECT 
    department,
    status,
    COUNT(*) AS count,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department, status WITH ROLLUP;

-- 结果包含:
-- 每个部门每种状态的统计
-- 每个部门的小计 (status 为 NULL)
-- 总计 (department 和 status 都为 NULL)

-- 字符串拼接
SELECT CONCAT(name, ' - ', department) AS info FROM employees;
SELECT CONCAT_WS(' - ', name, department) AS info FROM employees;

-- 大小写转换
SELECT UPPER(name), LOWER(name) FROM employees;

-- 字符串长度
SELECT name, LENGTH(name), CHAR_LENGTH(name) FROM employees;

-- 截取字符串
SELECT SUBSTRING(name, 1, 3) FROM employees;  -- 前3个字符
SELECT LEFT(name, 3), RIGHT(name, 3) FROM employees;

-- 查找替换
SELECT REPLACE(name, 'a', 'A') FROM employees;
SELECT INSTR(name, 'a') FROM employees;  -- 查找位置

-- 去除空格
SELECT TRIM(name), LTRIM(name), RTRIM(name) FROM employees;

-- 四舍五入
SELECT ROUND(salary, 0) FROM employees;
SELECT ROUND(salary, -3) FROM employees;  -- 千位四舍五入

-- 向上/向下取整
SELECT CEILING(salary), FLOOR(salary) FROM employees;

-- 取模
SELECT id, id % 2 FROM employees;

-- 绝对值
SELECT ABS(-10);

-- 幂运算
SELECT POWER(2, 10);  -- 2的10次方

-- 随机数
SELECT RAND();
SELECT FLOOR(RAND() * 100);  -- 0-99随机数

-- 当前日期时间
SELECT NOW(), CURRENT_TIMESTAMP();
SELECT CURRENT_DATE(), CURRENT_TIME();

-- 日期提取
SELECT 
    hire_date,
    YEAR(hire_date) AS year,
    MONTH(hire_date) AS month,
    DAY(hire_date) AS day,
    DAYOFWEEK(hire_date) AS weekday,
    DAYNAME(hire_date) AS weekday_name,
    WEEK(hire_date) AS week,
    QUARTER(hire_date) AS quarter
FROM employees;

-- 日期计算
SELECT 
    hire_date,
    DATE_ADD(hire_date, INTERVAL 1 YEAR) AS one_year_later,
    DATE_SUB(hire_date, INTERVAL 1 MONTH) AS one_month_ago,
    DATEDIFF(NOW(), hire_date) AS days_employed
FROM employees;

-- 日期格式化
SELECT 
    hire_date,
    DATE_FORMAT(hire_date, '%Y年%m月%d日') AS formatted_date
FROM employees;

-- 日期比较
SELECT * FROM employees 
WHERE hire_date > DATE_SUB(NOW(), INTERVAL 2 YEAR);

-- IF 函数
SELECT 
    name,
    salary,
    IF(salary > 70000, 'High', 'Low') AS salary_level
FROM employees;

-- IFNULL 函数
SELECT 
    name,
    IFNULL(department, 'Unassigned') AS dept
FROM employees;

-- NULLIF 函数
SELECT NULLIF(1, 1);  -- 返回 NULL
SELECT NULLIF(1, 2);  -- 返回 1

-- CASE 表达式
-- 简单 CASE
SELECT 
    name,
    department,
    CASE department
        WHEN 'Engineering' THEN 'Tech'
        WHEN 'Sales' THEN 'Business'
        ELSE 'Other'
    END AS dept_type
FROM employees;

-- 搜索 CASE
SELECT 
    name,
    salary,
    CASE 
        WHEN salary < 60000 THEN 'Low'
        WHEN salary < 80000 THEN 'Medium'
        ELSE 'High'
    END AS salary_level
FROM employees;

-- 创建索引
CREATE INDEX idx_department ON employees(department);
CREATE INDEX idx_salary ON employees(salary);

-- 使用索引的查询
SELECT * FROM employees WHERE department = 'Engineering';
SELECT * FROM employees WHERE salary > 80000;

-- 查看执行计划
EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

-- 避免 SELECT *
SELECT name, department, salary FROM employees;

-- 使用覆盖索引
CREATE INDEX idx_dept_salary ON employees(department, salary);

SELECT department, salary FROM employees WHERE department = 'Engineering';

-- 避免在索引列上使用函数
-- 不推荐
SELECT * FROM employees WHERE UPPER(department) = 'ENGINEERING';
-- 推荐
SELECT * FROM employees WHERE department = 'Engineering';

-- 使用索引提示
SELECT /*+ USE_INDEX(employees idx_department) */ 
* FROM employees WHERE department = 'Engineering';

-- 强制使用索引
SELECT /*+ FORCE_INDEX(employees idx_department) */ 
* FROM employees WHERE department = 'Engineering';

-- 忽略索引
SELECT /*+ IGNORE_INDEX(employees idx_department) */ 
* FROM employees WHERE department = 'Engineering';