目录

前言

一、简单的数据查询

(一)查询单个字段

(二)查询多个字段

(三)查询所有字段

(四)使用别名查询字段

(五)带条件的字段查询

(六)使用DISTINCT去重查询

(七)使用ORDER BY排序查询结果

(八)使用LIKE进行模糊查询

(九)使用IN进行多值查询

(十)使用BETWEEN进行范围查询

(十一)限制查询结果数量LIMIT

(十二)条件查询与逻辑操作符

二、聚合函数

(一)COUNT

(二)SUM

(三)AVG

(四)MAX

(五)MIN

(六)GROUP_CONCAT

(七)VARIANCE 和 STDDEV

三、分组查询

(一)基本语法

(二)示例

(1)简单分组查询

(2)使用多个列进行分组

(3)使用 HAVING 进行过滤

(4)使用聚合函数和分组

(5)与 WHERE 一起使用

(三)注意事项

四、分页查询

(一)基本语法

(二)示例

(1)获取前 10 条记录(第一页)

(2)获取第 2 页的 10 条记录

(3)使用分页和排序

(三)计算分页参数

(四)分页查询的优化

(1)索引优化

(2)减少offset的开销

(五)注意事项

五、连接查询

(一)内连接

(二)左连接

(三)右连接

(四)全连接

(五)交叉连接

(六)自连接

(七)多表连接

(八)注意事项


前言

上篇文章是对MySQL的简单介绍和基本操作,接下来会对MySQL数据查询最更进一步的讲解。


一、简单的数据查询

在 MySQL 中,字段查询是通过 SELECT 语句从表中检索某个或某些字段的数据。你可以通过指定字段名、条件、排序等来灵活查询表中的数据。以下是 MySQL 中常见的字段查询方式和操作示例:

(一)查询单个字段

如果只需要查询表中的某一个字段,可以在 SELECT 语句中指定该字段的名称。

语法:

SELECT 字段名 FROM 表名;

示例:

SELECT username FROM users;

此查询将从 users 表中查询所有用户的用户名。

(二)查询多个字段

可以同时查询多个字段,只需在 SELECT 语句中列出多个字段名,用逗号分隔。

语法:

SELECT 字段1, 字段2, ... FROM 表名;

示例:

SELECT username, email, age FROM users;

此查询将从 users 表中查询用户名、邮箱和年龄这三个字段的数据。

(三)查询所有字段

如果需要查询表中的所有字段,可以使用 * 来表示所有字段。

语法:

SELECT * FROM 表名;

示例:

SELECT * FROM users;

此查询将返回 users 表中的所有字段和所有数据。

(四)使用别名查询字段

使用 AS 关键字为字段指定别名,这在需要输出更加直观的结果时非常有用。

语法:

SELECT 字段名 AS 别名 FROM 表名;

示例:

SELECT username AS 用户名, email AS 邮箱 FROM users;

此查询将为 username 字段命名为 "用户名",为 email 字段命名为 "邮箱"。

(五)带条件的字段查询

通过 WHERE 子句来筛选数据,返回符合条件的字段。

语法:

SELECT 字段1, 字段2 FROM 表名 WHERE 条件;

示例:

SELECT username, email FROM users WHERE city = 'Beijing';

此查询将返回 users 表中城市为 Beijing 的用户的用户名和邮箱。

(六)使用DISTINCT去重查询

如果一个字段中存在重复值,DISTINCT 可以用来查询唯一值,去除重复记录。

语法:

SELECT DISTINCT 字段名 FROM 表名;

示例:

SELECT DISTINCT city FROM users;

此查询将返回 users 表中唯一的城市名称。

(七)使用ORDER BY排序查询结果

通过 ORDER BY 子句对查询结果进行排序,默认是升序排序,使用 DESC 关键字可以实现降序排序。

语法:

SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC|DESC];

示例:

SELECT username, age FROM users ORDER BY age DESC;

此查询将按照年龄降序排序,并返回用户名和年龄。

(八)使用LIKE进行模糊查询

LIKE 关键字用于进行模糊查询,结合 %_ 通配符使用。

  • % 表示任意数量的字符。

  • _ 表示单个字符。

语法:

SELECT 字段名 FROM 表名 WHERE 字段名 LIKE 模式;

示例:

SELECT username FROM users WHERE username LIKE 'john%';

此查询将返回用户名以 john 开头的所有用户。

(九)使用IN进行多值查询

IN 关键字用于筛选字段值等于多个给定值中的任意一个。

语法:

SELECT 字段名 FROM 表名 WHERE 字段名 IN (值1, 值2, ...);

示例:

SELECT username FROM users WHERE city IN ('Beijing', 'Shanghai');

(十)使用BETWEEN进行范围查询

BETWEEN 用于查询字段值在某个范围内的数据,通常用于数值或日期类型字段。

语法:

SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN 值1 AND 值2;

示例:

SELECT username, age FROM users WHERE age BETWEEN 20 AND 30;

此查询将返回年龄在 20 到 30 之间的用户。

(十一)限制查询结果数量LIMIT

LIMIT 用于限制查询返回的行数,特别适用于分页。

语法:

SELECT 字段名 FROM 表名 LIMIT 起始行, 行数;

示例:

SELECT username FROM users LIMIT 5;

(十二)条件查询与逻辑操作符

可以结合逻辑操作符 ANDOR 来进行更加复杂的条件查询。

示例:

SELECT username, email FROM users WHERE city = 'Beijing' AND age > 18;

二、聚合函数

MySQL的聚合函数用于对一组值进行计算,并返回单一的总结性结果。它们通常与 GROUP BY 子句一起使用,常用于统计数据和汇总结果。常见的聚合函数有以下几种:

(一)COUNT

  • 功能: 计算满足条件的行数。

  • 语法: COUNT(expression)

示例:

SELECT COUNT(*) FROM employees;

(二)SUM

  • 功能: 计算一组值的总和,通常用于数值列。

  • 语法: SUM(expression)

示例:

SELECT SUM(salary) FROM employees;

(三)AVG

  • 功能: 计算一组数值的平均值。

  • 语法: AVG(expression)

示例:

SELECT AVG(salary) FROM employees;

(四)MAX

  • 功能: 返回指定列的最大值。

  • 语法: MAX(expression)

示例:

SELECT MAX(salary) FROM employees;

(五)MIN

  • 功能: 返回指定列的最小值。

  • 语法: MIN(expression)

示例:

SELECT MIN(salary) FROM employees;

(六)GROUP_CONCAT

  • 功能: 将来自多行的数据连接成一个字符串,通常用于组合多个记录。

  • 语法: GROUP_CONCAT(expression [ORDER BY ...] [SEPARATOR str])

示例:

SELECT GROUP_CONCAT(name) FROM employees;

(七)VARIANCE 和 STDDEV

  • 功能: VARIANCE() 计算方差,STDDEV() 计算标准差,通常用于统计分析。

示例:

SELECT VARIANCE(salary), STDDEV(salary) FROM employees;

三、分组查询

MySQL的分组查询(GROUP BY 查询)用于将结果集按一列或多列的值进行分组,并对每组数据应用聚合函数进行计算。分组查询通常与聚合函数一起使用,用于统计或总结数据。

(一)基本语法

SELECT column1, column2, ..., aggregate_function(column)
FROM table
WHERE condition
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column;

关键点:

  1. GROUP BY: 指定按哪一列或多列进行分组。

  2. 聚合函数: 对每组应用的函数,如 COUNT()SUM()AVG()MAX()MIN() 等。

  3. HAVING: 用于过滤分组后的结果,类似 WHERE,但 WHERE 是在分组之前进行过滤,HAVING 是在分组之后应用条件。

(二)示例

(1)简单分组查询

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
  • 功能: 按 department 列进行分组,统计每个部门的员工数量。

(2)使用多个列进行分组

SELECT department, job_title, AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title;
  • 功能: 按 departmentjob_title 进行多列分组,计算每个部门每个职位的平均薪资。

(3)使用 HAVING 进行过滤

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING employee_count > 5;
  • 功能: 统计每个部门的员工数量,使用 HAVING 过滤出员工数大于 5 的部门。

(4)使用聚合函数和分组

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;
  • 功能: 按 department 分组,计算每个部门的总薪资,并按总薪资从高到低排序。

(5)与 WHERE 一起使用

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department;
  • 功能: 先使用 WHERE 过滤出入职日期在 2020 年 1 月 1 日之后的员工,再按 department 分组统计每个部门的员工数。

(三)注意事项

  1. GROUP BY 子句中的列必须出现在 SELECT 语句中,除非它是聚合函数的参数。

  2. WHERE 用于在分组之前过滤行,而 HAVING 用于在分组之后过滤组。

  3. 当你使用 GROUP BY 时,不能在 SELECT 中选择未聚合的列,除非它们在 GROUP BY 子句中指定。


四、分页查询

分页查询用于从大数据集中按页获取指定数量的记录,这对于处理大量数据时非常常见,尤其是在网页或应用程序中显示多页数据时。分页查询主要通过 LIMIT 子句来实现。

(一)基本语法

SELECT column1, column2, ...
FROM table
LIMIT offset, row_count;

参数解释:

  • offset: 要跳过的记录数(从第几条记录开始)。

  • row_count: 要返回的记录数(即每页显示多少条记录)。

(二)示例

(1)获取前 10 条记录(第一页)

SELECT * 
FROM employees 
LIMIT 0, 10;
  • 功能: 从 employees 表中返回前 10 条记录。这里 offset 为 0 表示从第一条记录开始。

(2)获取第 2 页的 10 条记录

SELECT * 
FROM employees 
LIMIT 10, 10;
  • 功能: 返回从第 11 条记录开始的 10 条记录(即第 2 页的数据)。offset 为 10 表示跳过前 10 条记录,row_count 为 10 表示获取 10 条记录。

(3)使用分页和排序

SELECT * 
FROM employees 
ORDER BY hire_date DESC 
LIMIT 20, 10;
  • 功能: 先按 hire_date 降序排序,再从第 21 条记录开始,获取 10 条记录(即第 3 页的数据)。

(三)计算分页参数

当需要显示分页数据时,通常需要通过页码来计算 offset
例如,假设每页显示 10 条记录:

  • 第 1 页: LIMIT 0, 10 (offset = (1 - 1) * 10 = 0)

  • 第 2 页: LIMIT 10, 10 (offset = (2 - 1) * 10 = 10)

  • 第 3 页: LIMIT 20, 10 (offset = (3 - 1) * 10 = 20)

(四)分页查询的优化

分页查询的优化主要从两个方面入手,一个是索引优化,另一个是减少offset的开销,详细如下:

(1)索引优化

大数据集分页时,尽量使用索引列进行排序和查询,如通过 ORDER BY 指定索引列,可以加快查询速度。

示例:

SELECT * 
FROM employees 
WHERE id > 100 
ORDER BY id 
LIMIT 10;

这种方式通过索引查找特定的 id 后直接获取分页数据,避免了从头遍历的性能开销。

(2)减少offset的开销

offset 非常大时,如 LIMIT 1000000, 10,查询性能可能会变慢。可以通过子查询或调整逻辑来优化大分页问题。

示例:

SELECT * 
FROM employees 
WHERE id > (SELECT id FROM employees ORDER BY id LIMIT 999990, 1) 
LIMIT 10;

这种方法避免了大 offset 带来的性能问题。

(五)注意事项

  • offset 越大,查询越慢: 因为 MySQL 会跳过 offset 数量的行数,这意味着在大的数据集下,分页查询的性能会下降。

  • 优化查询: 使用 WHERE 和合适的索引可以提升分页查询的性能,尤其是在处理大量数据时。


五、连接查询

MySQL的连接查询用于从多个表中查询相关数据。在数据库设计中,通常会把相关的数据分布在不同的表中,连接查询能够把这些表的数据组合起来,实现跨表查询。MySQL 支持几种类型的连接查询,常见的有内连接、外连接(左连接和右连接)、交叉连接等。

(一)内连接

内连接返回两个表中匹配的记录,只有在两个表中都有对应的匹配数据时才会返回结果。

语法:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

示例:

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;
  • 功能: 查询员工及其所在的部门名称。只有当员工和部门表的 department_id 匹配时,才会返回结果

(二)左连接

左连接返回左表的所有记录,即使右表没有匹配的记录。对于没有匹配的右表记录,结果中对应的列会显示为 NULL

语法:

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

示例:

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

(三)右连接

右连接返回右表的所有记录,即使左表没有匹配的记录。对于没有匹配的左表记录,结果中对应的列会显示为 NULL

语法:

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

示例:

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

(四)全连接

MySQL 本身不直接支持 FULL OUTER JOIN,但可以通过使用 UNION 来模拟全连接。全连接返回两个表中所有的记录,不论是否有匹配。

语法:

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column = table2.column
UNION
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

示例:

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id
UNION
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;
  • 功能: 查询所有员工和所有部门信息。包括那些没有匹配的员工或部门,未匹配的部分将显示为 NULL

(五)交叉连接

交叉连接会返回两个表的笛卡尔积,即两个表中的每一条记录都会和另一个表的所有记录进行组合。除非有特殊需求,否则交叉连接通常会产生大量数据,不常使用。

语法:

SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;

示例:

SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;
  • 功能: 返回所有员工和所有部门的所有可能组合,不管他们是否有实际关系。

(六)自连接

自连接是指在同一个表中进行连接查询,通常用于比较同一表中不同记录之间的关系。

语法:

SELECT a.column1, b.column2
FROM table a, table b
WHERE a.some_column = b.some_column;

示例:

SELECT e1.name AS employee_name, e2.name AS manager_name
FROM employees e1,employees e2
ON e1.manager_id = e2.employee_id;
  • 功能: 查询员工及其经理姓名。这里员工表通过自连接实现了员工与经理的对应关系。

(七)多表连接

MySQL 支持在一个查询中连接多个表,通过多个 JOIN 子句可以实现多表连接。

语法:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column
LEFT JOIN table3 ON table2.column = table3.column;

示例:

SELECT e.name, d.department_name, p.project_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
LEFT JOIN projects p ON e.project_id = p.project_id;
  • 功能: 查询员工的姓名、部门名称及其参与的项目。如果某个员工没有对应的项目,项目名将显示为 NULL

(八)注意事项

  • 连接查询性能在大数据量时可能会受到影响,优化时可以通过添加索引来提高查询速度。

  • 确保 ON 条件中的列有适当的匹配,以避免查询返回错误的数据集或产生过多的空值(NULL)。

  • 在编写复杂连接查询时,应尽量简化表之间的关系,避免产生不必要的笛卡尔积。

点赞(0) 打赏

评论列表 共有 0 条评论

暂无评论

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部