SELECT查询基础

点击勘误issues (opens new window)，哪吒感谢大家的阅读

SELECT语句概述

SELECT语句是SQL中最重要和最常用的语句，用于从数据库表中检索数据。它的基本语法结构如下：

SELECT 列名1, 列名2, ...
FROM 表名
[WHERE 条件]
[GROUP BY 列名]
[HAVING 条件]
[ORDER BY 列名]
[LIMIT 数量];

基本查询

1. 查询所有列

使用星号(*)可以查询表中的所有列：

-- 查询employees表的所有数据
SELECT * FROM employees;

2. 查询指定列

-- 查询员工的姓名和薪资
SELECT name, salary FROM employees;

-- 查询多个指定列
SELECT id, name, email, hire_date FROM employees;

3. 使用别名

可以为列或表指定别名，使查询结果更易读：

-- 列别名
SELECT 
    name AS 员工姓名,
    salary AS 薪资,
    hire_date AS 入职日期
FROM employees;

-- 简化写法（省略AS关键字）
SELECT 
    name 员工姓名,
    salary 薪资
FROM employees;

-- 表别名
SELECT e.name, e.salary
FROM employees AS e;

计算字段

1. 数学运算

-- 计算年薪
SELECT 
    name,
    salary,
    salary * 12 AS annual_salary
FROM employees;

-- 计算税后薪资（假设税率20%）
SELECT 
    name,
    salary,
    salary * 0.8 AS after_tax_salary
FROM employees;

2. 字符串连接

-- MySQL使用CONCAT函数
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    email
FROM employees;

-- SQL Server使用+操作符
SELECT 
    first_name + ' ' + last_name AS full_name,
    email
FROM employees;

3. 条件表达式

-- 使用CASE WHEN
SELECT 
    name,
    salary,
    CASE 
        WHEN salary >= 10000 THEN '高薪'
        WHEN salary >= 5000 THEN '中薪'
        ELSE '低薪'
    END AS salary_level
FROM employees;

去重查询

1. DISTINCT关键字

-- 查询所有不重复的部门
SELECT DISTINCT department FROM employees;

-- 查询不重复的薪资等级
SELECT DISTINCT 
    CASE 
        WHEN salary >= 10000 THEN '高薪'
        WHEN salary >= 5000 THEN '中薪'
        ELSE '低薪'
    END AS salary_level
FROM employees;

2. 多列去重

-- 查询不重复的部门和职位组合
SELECT DISTINCT department, position FROM employees;

限制结果数量

1. LIMIT子句（MySQL、PostgreSQL）

-- 查询前5名员工
SELECT * FROM employees LIMIT 5;

-- 分页查询：跳过前10条，取5条
SELECT * FROM employees LIMIT 5 OFFSET 10;

-- MySQL简化写法
SELECT * FROM employees LIMIT 10, 5;

2. TOP子句（SQL Server）

-- 查询前5名员工
SELECT TOP 5 * FROM employees;

-- 查询前10%的员工
SELECT TOP 10 PERCENT * FROM employees;

3. ROWNUM（Oracle）

-- 查询前5名员工
SELECT * FROM employees WHERE ROWNUM <= 5;

常用函数

1. 聚合函数

-- 统计员工总数
SELECT COUNT(*) AS total_employees FROM employees;

-- 计算平均薪资
SELECT AVG(salary) AS avg_salary FROM employees;

-- 查找最高和最低薪资
SELECT 
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees;

-- 计算薪资总和
SELECT SUM(salary) AS total_salary FROM employees;

2. 字符串函数

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

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

-- 字符串截取
SELECT 
    name,
    SUBSTRING(name, 1, 3) AS short_name
FROM employees;

3. 日期函数

-- 当前日期和时间
SELECT 
    NOW() AS current_datetime,
    CURDATE() AS current_date,
    CURTIME() AS current_time;

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

-- 计算工作年限
SELECT 
    name,
    hire_date,
    DATEDIFF(CURDATE(), hire_date) / 365 AS years_worked
FROM employees;

实战示例

假设我们有一个员工表，包含以下数据：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

INSERT INTO employees VALUES
(1, '张三', 'IT', '程序员', 8000.00, '2020-01-15'),
(2, '李四', 'HR', '招聘专员', 6000.00, '2019-03-20'),
(3, '王五', 'IT', '架构师', 15000.00, '2018-06-10'),
(4, '赵六', 'Finance', '会计', 7000.00, '2021-02-01'),
(5, '钱七', 'IT', '程序员', 9000.00, '2020-08-15');

示例查询

-- 1. 查询所有IT部门员工的基本信息
SELECT name, position, salary 
FROM employees 
WHERE department = 'IT';

-- 2. 查询薪资最高的3名员工
SELECT name, salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 3;

-- 3. 计算每个员工的年薪和工作年限
SELECT 
    name,
    salary,
    salary * 12 AS annual_salary,
    ROUND(DATEDIFF(CURDATE(), hire_date) / 365, 1) AS years_worked
FROM employees;

-- 4. 统计各部门的员工数量和平均薪资
SELECT 
    department,
    COUNT(*) AS employee_count,
    ROUND(AVG(salary), 2) AS avg_salary
FROM employees 
GROUP BY department;

-- 5. 查询薪资等级分布
SELECT 
    CASE 
        WHEN salary >= 10000 THEN '高薪(>=10K)'
        WHEN salary >= 7000 THEN '中薪(7K-10K)'
        ELSE '低薪(<7K)'
    END AS salary_level,
    COUNT(*) AS count
FROM employees 
GROUP BY 
    CASE 
        WHEN salary >= 10000 THEN '高薪(>=10K)'
        WHEN salary >= 7000 THEN '中薪(7K-10K)'
        ELSE '低薪(<7K)'
    END;

性能优化提示

1. 避免SELECT *

-- 不推荐：查询所有列
SELECT * FROM employees;

-- 推荐：只查询需要的列
SELECT name, salary FROM employees;

2. 使用索引

-- 在经常查询的列上创建索引
CREATE INDEX idx_department ON employees(department);
CREATE INDEX idx_salary ON employees(salary);

3. 合理使用LIMIT

-- 大表查询时使用LIMIT限制结果数量
SELECT * FROM large_table LIMIT 100;

常见错误

1. 列名错误

-- 错误：列名不存在
SELECT name, salery FROM employees;  -- salery拼写错误

-- 正确
SELECT name, salary FROM employees;

2. 表名错误

-- 错误：表名不存在
SELECT * FROM employee;  -- 应该是employees

-- 正确
SELECT * FROM employees;

3. 聚合函数使用错误

-- 错误：非聚合列没有包含在GROUP BY中
SELECT department, name, COUNT(*) 
FROM employees 
GROUP BY department;

-- 正确：要么包含在GROUP BY中，要么使用聚合函数
SELECT department, COUNT(*) 
FROM employees 
GROUP BY department;

总结

SELECT查询是SQL的核心，掌握了基本的SELECT语法，就可以进行大部分的数据查询操作。关键要点：

1. 明确查询目标，只选择需要的列
2. 合理使用别名提高可读性
3. 善用函数进行数据处理
4. 注意性能优化
5. 避免常见语法错误

下一步学习

• WHERE条件查询
• JOIN表连接
• 聚合函数详解