MySQL：基本表查询操作汇总（单表查询+多表操作）

时间：2024-11-07 06:57:18

目录

一、单表查询整合

（一）通用模版展示

（二）举例说明

（三）注意事项

（四）Mapper简单举例

简单查询

条件查询

高级查询

表和字段取别名

二、多表操作说明

（一）多表操作的基本模版展示

外键约束模版

操作关联表模版

连接查询模版

子查询模版

（二）简单案例展示

两张表情况

三张表情况

多表情况（三张以上）

（三）注意事项

三、总结

干货分享，感谢您的阅读！介绍MySQL的单表查询和多表操作，从基本概念到具体实例，帮助读者全面理解和掌握这些关键技术。通过阅读本文，您将了解如何构建高效的查询语句、如何利用索引和外键优化查询性能，以及如何在复杂数据关系中灵活运用连接查询和子查询。同时，我们还将分享一些实用的建议和注意事项，以助您在实际应用中规避常见问题，提高数据库操作的安全性和可靠性。

无论您是初学者还是有经验的开发者，这篇文章都将为您提供有价值的参考和指导，帮助您在数据库操作中更加游刃有余。

一、单表查询整合

主要内容：简单查询+条件查询+高级查询+表和字段取别名（以及简单的mapper写法）

（一）通用模版展示

以下是MySQL单表查询的通用写法举例：

  • 简单查询：最基本的查询方式，它用于获取表中所有数据。
SELECT * FROM table_name;
  • 条件查询：根据特定的条件获取数据的方式，它使用WHERE子句来指定条件。
SELECT * FROM table_name WHERE column_name = 'value';
  • 高级查询：使用聚合函数、分组、排序等方式来获取数据的方式。
SELECT AVG(column_name) AS average_value, COUNT(*) AS total_rows FROM table_name GROUP BY column_name ORDER BY average_value DESC;
  • 表和字段取别名：可以使查询语句更加简洁明了，也可以防止字段名或表名与SQL关键字重名的问题。
SELECT t.column_name AS alias_name FROM table_name AS t WHERE t.column_name > 10;

需要注意的是，在编写查询语句时，应该根据实际情况和需求来选择合适的查询方式和语法，以提高查询效率和数据质量。同时，还应该考虑SQL注入等安全问题，使用预处理语句等方式来增强安全性。

（二）举例说明

假设有一个名为students的表，其中包含以下字段：id, name, age, gender, major, score，现在我们以该表为例，演示MySQL单表查询的操作。

  • 简单查询：获取students表中所有数据：
SELECT * FROM students;
  • 条件查询：获取students表中名字为Tom的学生数据：
SELECT * FROM students WHERE name = 'Tom';
  • 高级查询：获取students表中每个专业学生的平均成绩，并按平均成绩从高到低排序：
SELECT major, AVG(score) AS average_score FROM students GROUP BY major ORDER BY average_score DESC;
  • 表和字段取别名：获取students表中年龄大于20岁的学生的姓名和年龄，并将年龄字段取别名为age_value：
SELECT name, age AS age_value FROM students WHERE age > 20;

以上是针对students表的MySQL单表查询的具体例子，可根据实际情况和需求进行调整和优化。

（三）注意事项

在使用MySQL进行单表查询时，需要注意以下事项：

  1. 选择合适的查询方式和语法，以提高查询效率和数据质量。
  2. 注意SQL注入等安全问题，使用预处理语句等方式来增强安全性。
  3. 尽量避免使用SELECT *等通配符查询，而是应该只查询需要的字段，以提高查询效率。
  4. 使用表和字段的别名时，应该使用有意义的别名，使查询语句更加清晰明了。
  5. 在使用GROUP BY和ORDER BY等聚合函数和排序语法时，应该确保语法正确且没有歧义，以避免数据误差和查询错误。
  6. 对于大型数据表，可以使用LIMIT语法来限制返回的行数，以减少查询时间和网络带宽的消耗。
  7. 使用EXPLAIN语法可以帮助了解查询的执行计划和性能，以优化查询效率。

MySQL单表查询需要注意以上事项，以获得更好的查询效果和数据质量。

（四）Mapper简单举例

针对上面提到的单表查询的例子，下面给出对应的MyBatis Mapper的优秀写法：

简单查询

获取students表中所有数据：

<select  resultType="Student">

  SELECT * FROM students

</select>

条件查询

获取students表中名字为Tom的学生数据：

<select  parameterType="String" resultType="Student">

  SELECT * FROM students WHERE name = #{name}

</select>

高级查询

获取students表中每个专业学生的平均成绩，并按平均成绩从高到低排序：

<select  resultType="ScoreByMajor">

  SELECT major, AVG(score) AS average_score FROM students GROUP BY major ORDER BY average_score DESC

</select>

其中，ScoreByMajor是一个自定义的结果映射类，用于将查询结果封装为一个对象。

表和字段取别名

获取students表中年龄大于20岁的学生的姓名和年龄，并将年龄字段取别名为age_value：

<select  parameterType="int" resultType="Student">

  SELECT name, age AS age_value FROM students WHERE age > #{age}

</select>

需要注意的是，以上是一些简单的查询示例，实际情况下可能会涉及到更复杂的查询，需要根据具体的业务需求和数据结构来调整查询语句和Mapper的写法。另外，在使用Mapper时也需要注意SQL注入等安全问题，以及使用缓存等技术来提高查询效率和性能。

二、多表操作说明

主要内容：外键+操作关联表+连接查询+子查询

（一）多表操作的基本模版展示

结合具体的例子，给出多表操作的通用模版，并分析每个模版的作用。

外键约束模版

CREATE TABLE table1 (

  id INT PRIMARY KEY,

  ...

) ENGINE=InnoDB;



CREATE TABLE table2 (

  id INT PRIMARY KEY,

  table1_id INT,

  ...

  FOREIGN KEY (table1_id) REFERENCES table1(id)

) ENGINE=InnoDB;

外键约束模版中，我们可以定义一个外键，将一个表的某个字段设置为另一个表的主键。这样，当我们在更新或删除一张表的记录时，会自动对应更新或删除另一张表中的相关记录。在创建表时，我们需要注意两点：

  • 必须将引擎设置为 InnoDB，才能使用外键约束。
  • 外键所在表（table2）的字段必须是另一张表（table1）的主键。

操作关联表模版

SELECT t1.column1, t2.column2

FROM table1 t1, table2 t2

WHERE  = t2.table1_id;

操作关联表模版中，我们需要用到 JOIN 关键字，将两张表的记录连接起来。常见的 JOIN 类型有 INNER JOIN、LEFT JOIN、RIGHT JOIN 等。上面的模版使用的是 INNER JOIN，它只返回两张表中都存在的记录。在实际应用中，我们需要根据具体的业务需求来选择合适的 JOIN 类型。

连接查询模版

SELECT t1.column1, t2.column2

FROM table1 t1

JOIN table2 t2 ON  = t2.table1_id;

连接查询模版和操作关联表模版类似，但是它使用了 ON 关键字来指定连接条件，更加清晰明了。在实际应用中，我们可以根据实际情况选择操作关联表模版或连接查询模版。

子查询模版

SELECT column1, column2, ...

FROM table1

WHERE column1 IN (SELECT column1 FROM table2 WHERE ...);

子查询模版中，我们在 WHERE 子句中使用了一个子查询来获取 table2 表中符合条件的数据，并将其作为查询条件之一。子查询可以用在 SELECT、INSERT、UPDATE 和 DELETE 等语句中，是一种非常强大的查询手段。

（二）简单案例展示

两张表情况

假设我们有两个表，一个是用户表（user），另一个是订单表（order），其中订单表中包含了用户id的外键（user_id）。

我们需要查询出所有已完成的订单及对应的用户名和手机号。可以使用连接查询来实现：

SELECT o.order_id, u.user_name, u.phone_number

FROM `order` o

INNER JOIN `user` u ON o.user_id = u.user_id

WHERE o.order_status = 'completed';

在上面的查询中，我们使用了 INNER JOIN 将订单表和用户表连接起来，连接条件是订单表的 user_id 字段与用户表的 user_id 字段相等。在 SELECT 子句中，我们指定了要查询的字段，注意使用了别名来区分两个表中相同字段名的情况。在 WHERE 子句中，我们过滤出订单状态为已完成的记录。

如果我们需要查询某个用户的订单及对应的用户名和手机号，可以使用子查询来实现：

SELECT o.order_id, u.user_name, u.phone_number

FROM `order` o

INNER JOIN (

    SELECT user_id, user_name, phone_number

    FROM `user`

    WHERE user_id = 123

) u ON o.user_id = u.user_id

WHERE o.order_status = 'completed';

在上面的查询中，我们使用了子查询来获取指定用户的用户名和手机号，并将其命名为 u 表。在连接查询中，我们使用了 u 表来代替用户表，以此来获取指定用户的订单信息。

现在我们想查询每个用户的订单总数，并按照订单总数从高到低进行排序。我们可以使用以下SQL查询：

SELECT , COUNT() as order_count 

FROM users u 

LEFT JOIN orders o ON  = o.user_id 

GROUP BY  

ORDER BY order_count DESC;

在这个查询中，我们使用了LEFT JOIN将users表和orders表关联起来。然后我们使用了COUNT函数来计算每个用户的订单数量，并将其重命名为order_count。最后，我们使用GROUP BY对用户进行分组，并使用ORDER BY按订单总数从高到低排序。

这个查询可以帮助我们了解哪些用户下单最频繁，并根据这些数据做出更明智的业务决策。

三张表情况

假设我们有三个表，一个是订单表（orders），另一个是商品表（products），第三个是订单商品关联表（order_products），用来记录每个订单中包含了哪些商品，其结构如下：

orders：

Field

Type

Null

Key

Default

Extra

order_id

int

NO

PRI

NULL

auto_increment

user_id

int

NO

NULL

order_date

datetime

NO

NULL

status

varchar(20)

NO

NULL

products：

Field

Type

Null

Key

Default

Extra

product_id

int

NO

PRI

NULL

auto_increment

name

varchar(50)

NO

NULL

description

text

NO

NULL

price

decimal(10,2)

NO

NULL

order_products：

Field

Type

Null

Key

Default

Extra

order_id

int

NO

MUL

NULL

product_id

int

NO

MUL

NULL

quantity

int

NO

NULL

unit_price

decimal(10,2)

NO

NULL

现在我们需要查询用户最近一个月的订单中，包含哪些商品，以及每个商品的销售数量、单价和总价。可以使用以下 SQL 语句来实现：

SELECT op.product_id, , op.unit_price, SUM() AS sales_volume, SUM( * op.unit_price) AS sales_amount

FROM orders o

INNER JOIN order_products op ON o.order_id = op.order_id

INNER JOIN products p ON op.product_id = p.product_id

WHERE o.user_id = 123

AND o.order_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)

GROUP BY op.product_id, , op.unit_price;

在上面的查询中，我们使用了三个表的连接查询。在 WHERE 子句中，我们过滤出了用户最近一个月的订单记录，并指定了用户id为123。在 SELECT 子句中，我们指定了要查询的字段，其中 SUM 函数用来计算每个商品的销售数量和销售金额，GROUP BY 子句用来分组计算。

注意，在使用 GROUP BY 子句时，必须将所有非聚合字段都列出来，否则会报错。在本例中，我们需要将商品名称和单价列出来。

多表情况（三张以上）

当涉及到5个或更多的表时，通常会使用更高级的查询技术，例如子查询和嵌套查询。以下是一个类似的示例，假设我们有6个表：users、orders、order_items、products、categories和suppliers，每个表的结构如下：

  • users: id, name, email, password
  • orders: id, user_id, created_at
  • order_items: id, order_id, product_id, quantity, price
  • products: id, name, category_id, supplier_id
  • categories: id, name
  • suppliers: id, name, email, phone

现在，我们想要查找所有已下订单的用户的姓名、订单号、订单创建时间、订单中的产品名称、产品所属类别、产品供应商名称、产品数量和价格。

这个查询涉及了6个表，我们需要使用多个JOIN子句来将它们连接起来，同时使用子查询和嵌套查询来过滤结果。

SELECT 

   AS user_name,

   AS order_id,

  o.created_at AS order_created_at,

   AS product_name,

   AS category_name,

   AS supplier_name,

   AS product_quantity,

   AS product_price

FROM users u

JOIN orders o ON  = o.user_id

JOIN order_items oi ON  = oi.order_id

JOIN products p ON oi.product_id = 

JOIN categories c ON p.category_id = 

JOIN suppliers s ON p.supplier_id = 

WHERE o.created_at BETWEEN '2022-01-01' AND '2022-12-31'

  AND  IN (SELECT user_id FROM orders WHERE created_at BETWEEN '2022-01-01' AND '2022-12-31')

ORDER BY , o.created_at;

这个查询使用了多个JOIN子句，将6个表连接起来。我们还使用了一个子查询来获取在指定时间范围内下单的用户的ID，并使用IN运算符将其与外部查询中的用户ID进行匹配。我们还使用了WHERE子句来过滤指定时间范围内下单的订单，并使用ORDER BY将结果按用户姓名和订单创建时间进行排序。

当涉及到多个表时，保持代码的可读性和可维护性非常重要。使用良好的命名约定和注释，以及遵循最佳实践和代码风格指南，可以使代码更易于理解和维护。

（三）注意事项

在进行多表操作时，需要注意以下几点：

  1. 多表连接可能会产生笛卡尔积，需要注意去重或限制返回结果数量。
  2. 在进行关联查询时，尽量使用索引字段作为关联条件，以提高查询效率。
  3. 多表查询涉及到数据的读写，为了保证数据的一致性，应该使用事务进行处理。
  4. 在多表关联查询中，使用子查询时要注意子查询的效率，尽量使用连接查询代替子查询。
  5. 多表操作可能会涉及到数据的修改和删除，因此需要谨慎处理，避免误操作导致数据的丢失。
  6. 在进行多表查询时，应该选择适当的查询方式和语句结构，以提高查询效率。同时，还需要根据实际情况进行调整和优化。

三、总结

MySQL单表查询和多表操作是数据库管理中常见且重要的任务。通过掌握简单查询、条件查询、高级查询、表和字段取别名等单表查询技巧，可以有效地获取和操作单表数据。而在多表操作中，理解外键约束、操作关联表、连接查询和子查询等技术，可以实现复杂的数据关联和查询需求。

在实际应用中，选择合适的查询方式和语法至关重要，既能提高查询效率，又能保证数据的质量和安全性。特别是在多表操作中，使用索引、优化查询语句和使用事务等手段，可以显著提升查询性能和数据一致性。

通过本文的介绍，相信读者能够对MySQL的单表查询和多表操作有更深入的理解，并能在实际工作中灵活运用这些技术，提高数据管理和处理的效率。

相关文章