Muliminty Note

02-数据查询基础

7分钟阅读

数据查询基础

SELECT 是 SQL 中最常用的语句，用于从数据库中检索数据。

准备测试数据

-- 创建示例表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    age INT,
    hire_date DATE
);
 
-- 插入测试数据
INSERT INTO employees (name, department, salary, age, hire_date) VALUES
('张三', '技术部', 15000.00, 28, '2020-03-15'),
('李四', '技术部', 18000.00, 32, '2019-07-01'),
('王五', '市场部', 12000.00, 26, '2021-01-10'),
('赵六', '市场部', 14000.00, 30, '2020-06-20'),
('钱七', '人事部', 10000.00, 25, '2022-02-28'),
('孙八', '技术部', 20000.00, 35, '2018-11-05'),
('周九', '财务部', 13000.00, 29, '2020-09-12'),
('吴十', '技术部', 16000.00, 27, '2021-04-18');

SELECT 基础语法

查询所有列

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

查询指定列

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

使用别名 (AS)

-- 给列起别名
SELECT 
    name AS 姓名,
    department AS 部门,
    salary AS 薪资
FROM employees;
 
-- AS 可以省略
SELECT name 姓名, department 部门 FROM employees;

去重查询 (DISTINCT)

-- 查询所有不重复的部门
SELECT DISTINCT department FROM employees;
 
-- 多列去重
SELECT DISTINCT department, age FROM employees;

WHERE 条件过滤

比较运算符

运算符含义示例
=等于age = 28
<> 或 !=不等于age <> 28
>大于salary > 15000
>=大于等于salary >= 15000
<小于age < 30
<=小于等于age <= 30
-- 查询薪资大于 15000 的员工
SELECT * FROM employees WHERE salary > 15000;
 
-- 查询技术部的员工
SELECT * FROM employees WHERE department = '技术部';

逻辑运算符

-- AND：同时满足多个条件
SELECT * FROM employees 
WHERE department = '技术部' AND salary > 15000;
 
-- OR：满足任一条件
SELECT * FROM employees 
WHERE department = '技术部' OR department = '市场部';
 
-- NOT：取反
SELECT * FROM employees 
WHERE NOT department = '技术部';

BETWEEN 范围查询

-- 查询薪资在 12000 到 18000 之间的员工
SELECT * FROM employees 
WHERE salary BETWEEN 12000 AND 18000;
 
-- 等价于
SELECT * FROM employees 
WHERE salary >= 12000 AND salary <= 18000;

IN 列表查询

-- 查询技术部和市场部的员工
SELECT * FROM employees 
WHERE department IN ('技术部', '市场部');
 
-- 等价于
SELECT * FROM employees 
WHERE department = '技术部' OR department = '市场部';
 
-- NOT IN
SELECT * FROM employees 
WHERE department NOT IN ('技术部', '市场部');

LIKE 模糊查询

通配符含义示例
%匹配任意多个字符'%三' 匹配以”三”结尾
_匹配单个字符'张_' 匹配”张X”
-- 查询姓张的员工
SELECT * FROM employees WHERE name LIKE '张%';
 
-- 查询名字包含"三"的员工
SELECT * FROM employees WHERE name LIKE '%三%';
 
-- 查询名字是两个字的员工
SELECT * FROM employees WHERE name LIKE '__';

NULL 值判断

-- 查询部门为空的员工
SELECT * FROM employees WHERE department IS NULL;
 
-- 查询部门不为空的员工
SELECT * FROM employees WHERE department IS NOT NULL;
 
-- 注意：不能使用 = NULL
-- 错误写法：WHERE department = NULL

ORDER BY 排序

单列排序

-- 按薪资升序排列（默认 ASC）
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 * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

LIMIT 分页查询

MySQL / PostgreSQL 语法

-- 获取前 5 条记录
SELECT * FROM employees LIMIT 5;
 
-- 跳过前 2 条，获取 3 条（分页）
SELECT * FROM employees LIMIT 3 OFFSET 2;
 
-- MySQL 简写形式
SELECT * FROM employees LIMIT 2, 3;  -- LIMIT offset, count

分页公式

-- 每页显示 pageSize 条，获取第 pageNum 页
-- LIMIT pageSize OFFSET (pageNum - 1) * pageSize
 
-- 示例：每页 3 条，获取第 2 页
SELECT * FROM employees 
LIMIT 3 OFFSET 3;

计算列与函数

算术运算

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

常用函数

-- 字符串函数
SELECT 
    name,
    UPPER(name) AS upper_name,       -- 转大写
    LENGTH(name) AS name_length,     -- 字符串长度
    CONCAT(department, '-', name) AS full_info  -- 字符串拼接
FROM employees;
 
-- 数值函数
SELECT 
    salary,
    ROUND(salary / 12, 2) AS monthly,  -- 四舍五入
    CEIL(salary / 1000) AS ceil_val,   -- 向上取整
    FLOOR(salary / 1000) AS floor_val  -- 向下取整
FROM employees;
 
-- 日期函数
SELECT 
    name,
    hire_date,
    YEAR(hire_date) AS hire_year,     -- 提取年份
    MONTH(hire_date) AS hire_month,   -- 提取月份
    DATEDIFF(CURDATE(), hire_date) AS days_worked  -- 日期差
FROM employees;

综合示例

-- 查询技术部薪资前三名的员工信息
SELECT 
    name AS 姓名,
    salary AS 薪资,
    DATEDIFF(CURDATE(), hire_date) AS 入职天数
FROM employees
WHERE department = '技术部'
ORDER BY salary DESC
LIMIT 3;
 
-- 查询 2020 年入职且薪资大于 12000 的员工
SELECT * FROM employees
WHERE YEAR(hire_date) = 2020 
  AND salary > 12000
ORDER BY hire_date;

查询语句执行顺序

FROM → WHERE → SELECT → DISTINCT → ORDER BY → LIMIT
  1. FROM: 确定数据来源表
  2. WHERE: 过滤行
  3. SELECT: 选择列
  4. DISTINCT: 去重
  5. ORDER BY: 排序
  6. LIMIT: 限制返回行数

总结

子句功能是否必须
SELECT指定要查询的列是
FROM指定数据来源表是
WHERE过滤条件否
ORDER BY排序否
LIMIT限制返回行数否

上一节：01-SQL简介与环境搭建 下一节：03-数据操作语言 — INSERT、UPDATE、DELETE

sql select 查询

关系图谱

反向链接