当前位置：首页 > news >正文

MySQL 系列：第5篇从一张表中精准取数

news 2026/6/16 10:19:01

IT策士 10余年一线大厂经验，专注 IT 思维、架构、职场进阶。我会在各个平台持续发布最新文章，助你少走弯路。

前面几篇我们建好了表、插入了数据。今天终于要回答那个核心问题：怎么把数据高效、精准地取出来？SELECT 是 SQL 中使用最频繁的语句，掌握它就意味着你能随心所欲地“问”数据库任何问题。本篇用 Python 配合实战，彻底拿下单表查询。

1. 准备数据：搭建测试环境

先用 Python 创建一个employees表并灌入足够多的测试数据，供后续所有查询使用。

importmysql.connectorimportrandomimportstring conn=mysql.connector.connect(host="127.0.0.1",port=3306,user="root",password="MyNewPass123!",database="shop")cursor=conn.cursor()# 建表cursor.execute(""" CREATE TABLE IF NOT EXISTS employees(idINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)NOT NULL, department VARCHAR(30), salary DECIMAL(10,2), age TINYINT UNSIGNED, city VARCHAR(20), hire_date DATE, is_active TINYINT DEFAULT1)ENGINE=InnoDB""")# 清空旧数据cursor.execute("TRUNCATE employees")# 批量插入 20 条测试数据departments=["技术部","产品部","市场部","财务部","人事部"]cities=["北京","上海","深圳","杭州","成都"]names=["张三","李四","王五","赵六","钱七","孙八","周九","吴十","郑一","冯二","陈三","褚四","卫五","蒋六","沈七","韩八","杨九","朱十","秦一","尤二"]data=[]foriinrange(20): name=names[i]dept=random.choice(departments)salary=round(random.uniform(8000,30000),2)age=random.randint(22,55)city=random.choice(cities)hire_date=f"202{random.randint(0,5)}-{random.randint(1,12):02d}-{random.randint(1,28):02d}"data.append((name,dept,salary,age,city,hire_date))cursor.executemany("INSERT INTO employees (name, department, salary, age, city, hire_date) VALUES (%s,%s,%s,%s,%s,%s)", data)conn.commit()print(f"✅ 插入了 {cursor.rowcount} 条测试数据")

预期输出：

2. SELECT 基础：想看什么，就写什么

2.1 最简单的查询

# 查看所有列cursor.execute("SELECT * FROM employees")rows=cursor.fetchall()print(f"共 {len(rows)} 条记录\n")# 只看特定列cursor.execute("SELECT name, department, salary FROM employees")forrowincursor.fetchmany(3):# 只取前 3 行print(row)

预期输出：

共20条记录('张三','技术部', Decimal('18500.00'))('李四','市场部', Decimal('9200.00'))('王五','财务部', Decimal('27300.00'))

SELECT *的陷阱：生产环境中尽量避免*，因为：

返回了不需要的列，浪费网络带宽
表结构变更后，列顺序可能变化，导致代码出 bug
明确列出列名，代码可读性更高

2.2 列别名（AS）

让结果更易读，同时可在 Python 中通过列名取值：

cursor.execute(""" SELECT name AS 姓名, department AS 部门, salary AS 月薪 FROM employees""")forrowincursor.fetchmany(3): print(f"{row[0]} | {row[1]} | {row[2]}")

3. WHERE：条件筛选的艺术

WHERE 是 SELECT 的灵魂——不加条件就是全表扫描，加了条件才能精准命中。

3.1 比较运算符

# 查询月薪大于 20000 的员工cursor.execute("SELECT name, salary FROM employees WHERE salary > 20000")print("高薪员工：")forrowincursor.fetchall(): print(f" {row[0]} - ¥{row[1]}")

3.2 逻辑运算符：AND、OR、NOT

# 技术部且月薪大于 15000cursor.execute(""" SELECT name, department, salary FROM employees WHERE department='技术部'AND salary>15000""")print("技术部高薪：", cursor.fetchall())# 北京或上海的员工cursor.execute(""" SELECT name, city FROM employees WHERE city='北京'OR city='上海'""")print("京沪员工：", cursor.fetchall())# 不是技术部的员工cursor.execute(""" SELECT name, department FROM employees WHERE NOT department='技术部'""")

3.3 BETWEEN：区间查询

# 月薪在 10000 ~ 20000 之间cursor.execute(""" SELECT name, salary FROM employees WHERE salary BETWEEN10000AND20000""")print("中等薪资：", cursor.fetchall())# 等同于 WHERE salary >= 10000 AND salary <= 20000

3.4 IN：列举匹配

# 查询指定部门的员工cursor.execute(""" SELECT name, department FROM employees WHERE department IN('技术部','产品部')""")print("技术与产品：", cursor.fetchall())

IN比多个OR更简洁，而且 MySQL 会对其内部优化，将列表转为二分查找。

3.5 LIKE：模糊查询

%匹配任意多个字符
_匹配单个字符

# 查询姓“张”的员工cursor.execute("SELECT name FROM employees WHERE name LIKE '张%'")print("张姓员工：", cursor.fetchall())# 查询名字第二个字是“十”的cursor.execute("SELECT name FROM employees WHERE name LIKE '_十%'")print("第二个字是十：", cursor.fetchall())

性能警告：LIKE '%张'这种前缀模糊会导致索引失效，因为 B+Tree 索引只能从左向右匹配。后续索引章节会详解。

3.6 IS NULL / IS NOT NULL

# 查询没有填写年龄的员工（假设允许 NULL）cursor.execute("SELECT name, age FROM employees WHERE age IS NULL")

注意：不能用= NULL，必须用IS NULL。

4. DISTINCT：去重

想知道公司员工分布在哪些城市？

cursor.execute("SELECT DISTINCT city FROM employees")print("城市分布：",[row[0]forrowincursor.fetchall()])# 多列去重cursor.execute("SELECT DISTINCT department, city FROM employees")print("部门-城市组合：", cursor.fetchall())

5. ORDER BY：排序

# 按薪资降序，薪资相同则按年龄升序cursor.execute(""" SELECT name, salary, age FROM employees ORDER BY salary DESC, age ASC""")print("薪资排行榜：")forrowincursor.fetchall(): print(f" {row[0]} | ¥{row[1]} | {row[2]}岁")

ASC升序（默认），DESC降序
多列排序：先按第一列排，相同时按第二列排

6. LIMIT 与 OFFSET：分页查询

这是构建任何列表页面都少不了的技能。

6.1 基础分页

# 每页 5 条，取第 1 页page=1page_size=5offset=(page -1)* page_size cursor.execute(""" SELECT id, name, salary FROM employees ORDER BYidLIMIT %s OFFSET %s""",(page_size, offset))print(f"第 {page} 页：")forrowincursor.fetchall(): print(f" #{row[0]} {row[1]} ¥{row[2]}")

6.2 快捷写法

LIMIT offset, count是另一种写法，但不推荐，因为可读性差：

-- 这两种等价 LIMIT5OFFSET10LIMIT10,5-- 不推荐：与 LIMIT count OFFSET offset 顺序相反

6.3 分页深翻的性能问题

当OFFSET很大时（比如第 10000 页），MySQL 仍然需要扫描前面所有的行再丢弃，效率极低。解决方案后续优化篇会讲“延迟关联”等技巧。

7. 实战小工具：用 Python 封装一个灵活的查询函数

将今天学的知识点封装成可复用的工具函数：

def query_employees(filters=None,sort_by="id",sort_order="ASC",page=1,page_size=10):""" 灵活的 employee 查询器 filters: dict，如{"department":"技术部","salary_min":10000}""" conn=mysql.connector.connect(host="127.0.0.1",port=3306,user="root",password="MyNewPass123!",database="shop")cursor=conn.cursor(dictionary=True)# 返回字典格式where_clauses=["1=1"]# 占位，方便动态拼接params=[]iffilters:if"department"infilters: where_clauses.append("department = %s")params.append(filters["department"])if"city"infilters: where_clauses.append("city = %s")params.append(filters["city"])if"salary_min"infilters: where_clauses.append("salary >= %s")params.append(filters["salary_min"])if"salary_max"infilters: where_clauses.append("salary <= %s")params.append(filters["salary_max"])if"name_like"infilters: where_clauses.append("name LIKE %s")params.append(f"%{filters['name_like']}%")where_sql=" AND ".join(where_clauses)offset=(page -1)* page_size sql=f""" SELECT id, name, department, salary, city FROM employees WHERE{where_sql}ORDER BY{sort_by}{sort_order}LIMIT %s OFFSET %s""" params.extend([page_size, offset])cursor.execute(sql, params)results=cursor.fetchall()# 查询总记录数cursor.execute(f"SELECT COUNT(*) AS total FROM employees WHERE {where_sql}", params[:-2])total=cursor.fetchone()["total"]cursor.close()conn.close()returnresults, total# 使用示例results, total=query_employees(filters={"department":"技术部","salary_min":15000},sort_by="salary",sort_order="DESC")print(f"共 {total} 条，本页 {len(results)} 条")forrinresults: print(f" {r['name']} | {r['department']} | ¥{r['salary']}")