MySQL数据库高级查询语句及案例_mysql连表查询高级示列

作者：逍遥Sean
简介：一个主修Java的Web网站\游戏服务器后端开发者
主页：https://blog.csdn.net/Ureliable
觉得博主文章不错的话，可以三连支持一下~ 如有需要我的支持，请私信或评论留言！

前言：

MySQL数据库高级查询包括以下几个方面：

1. 聚合函数查询：使用聚合函数如SUM、AVG、MAX、MIN等实现对数据的汇总统计。
2. 分组查询：使用GROUP BY语句对查询结果进行分组，根据指定字段对数据进行分组计算。
3. 连接查询：使用JOIN语句将多个表连接起来，实现数据的联合查询。
4. 子查询：使用SELECT语句嵌套SELECT语句实现对数据的复杂查询。
5. UNION操作符：使用UNION操作符将多个SELECT语句的结果进行合并。
6. 存储过程：使用存储过程封装SQL语句，实现对数据的复杂操作。
7. 视图：使用视图将SELECT语句的结果保存到一个虚拟表中，方便后续查询和操作。

1 聚合函数查询

1.1 概述

聚合函数是一种用于对数据集进行汇总计算的函数，如平均值、最大值、最小值、总和等。在SQL语言中，使用聚合函数可以使数据统计更加方便简单。

常见的聚合函数包括：

1. COUNT：计算数据集中行的数量；
2. SUM：计算数据集中某一列的总和；
3. AVG：计算数据集中某一列的平均值；
4. MAX：计算数据集中某一列的最大值；
5. MIN：计算数据集中某一列的最小值。

在SQL语句中，可以通过SELECT语句和聚合函数来进行聚合查询，如下所示：

SELECT COUNT(*) FROM 表名;
SELECT SUM(列名) FROM 表名;
SELECT AVG(列名) FROM 表名;
SELECT MAX(列名) FROM 表名;
SELECT MIN(列名) FROM 表名;
1
2
3
4
5

聚合函数也可以用在WHERE子句中，如下所示：

SELECT COUNT(*) FROM 表名 WHERE 条件;
SELECT SUM(列名) FROM 表名 WHERE 条件;
SELECT AVG(列名) FROM 表名 WHERE 条件;
SELECT MAX(列名) FROM 表名 WHERE 条件;
SELECT MIN(列名) FROM 表名 WHERE 条件;
1
2
3
4
5

以上SQL语句可以根据需要进行修改和组合，以实现更加复杂的聚合查询。

1.2 实例

以下是一个使用聚合函数的查询实例：

假设有一个学生表（student）包含以下字段：

• id（学生编号）
• name（学生姓名）
• age（学生年龄）
• gender（学生性别）
• score（学生成绩）

1. 求学生表中成绩的平均值

SELECT AVG(score) FROM student;
1

2. 求学生表中成绩的最大值

SELECT MAX(score) FROM student;
1

3. 求学生表中成绩的最小值

SELECT MIN(score) FROM student;
1

4. 求学生表中成绩的总和

SELECT SUM(score) FROM student;
1

5. 按性别分组，求每个性别的平均年龄和平均成绩

SELECT gender, AVG(age), AVG(score) FROM student GROUP BY gender;
1

6. 按年龄分组，求年龄小于20岁的学生的平均成绩

SELECT AVG(score) FROM student WHERE age < 20;
1

2 分组查询

2.1 概述

MySQL分组查询使用GROUP BY子句对查询结果进行分组，常用于统计分组数据并计算聚合函数。
可以使用SUM、MAX、MIN、AVG等聚合函数对分组数据进行处理。

注意，在GROUP BY子句中使用的字段必须在SELECT子句中出现或者是聚合函数的参数。

2.2 实例

假设我们有一个存储了学生分数的表，表名为scores，包含以下字段：

现在我们需要统计每个学生的平均分数，可以使用以下SQL查询语句：

SELECT name, AVG(score) as avg_score FROM scores GROUP BY name;
1

该语句会按照字段name进行分组，并计算每组的平均分数，最终得到以下结果：

3 连接查询

3.1 概述

MySQL连接查询是通过连接两个或多个表来获取结果的一种查询方式。连接可以基于相同的列或多个列。在MySQL中，有以下几种类型的连接：

1. 内连接（INNER JOIN）：只返回相互匹配的行。
2. 左连接（LEFT JOIN）：返回左表中所有的行，即使在右表中没有匹配的行。
3. 右连接（RIGHT JOIN）：返回右表中所有的行，即使在左表中没有匹配的行。
4. 全连接（FULL OUTER JOIN）：返回左右表中所有的行，匹配不上的行会用NULL填充。

3.2 实例

实例1：
假设有两个表，分别是“users”和“orders”，这两个表通过一个用户ID列进行关联。我们可以使用以下查询语句来获得用户和他们的订单信息：

SELECT users.name, orders.order_date
FROM users
INNER JOIN orders
ON users.id = orders.user_id;
1
2
3
4

在上面的查询中，我们选择了“users”表中的“name”列和“orders”表中的“order_date”列。通过使用INNER JOIN连接，我们使用“users.id”列与“orders.user_id”列进行匹配。

这是一个内连接查询，结果将只返回具有匹配“user_id”的记录。如果要使用不同类型的连接，只需更改连接类型即可。
实例2：
假设我们有两个表：
表1: customers

表2: orders

我们可以使用内连接查询来查找每个客户的所有订单和总金额：

SELECT customers.name, orders.total 
FROM customers 
INNER JOIN orders 
ON customers.id = orders.cust_id;
1
2
3
4

结果将是一个包含以下行的表格：

INNER JOIN 在匹配表1 和表2 的 id 和 cust_id 列时将返回匹配项。在这个例子中，我们只查找客户的名称和他们的订单总金额。
实例3：
内连接示例：
假设有两个表A和B，其中A表有列a1、a2、a3，B表有列b1、b2、b3。要求对A表和B表做内连接，匹配条件是A表的a1列和B表的b2列相等：

SELECT A.a1, B.b2
FROM A
INNER JOIN B
ON A.a1 = B.b2;
1
2
3
4

左连接示例：

仍然假设有两个表A和B，要求对A表和B表做左连接，匹配条件是A表的a1列和B表的b2列相等：

SELECT A.a1, B.b2
FROM A
LEFT JOIN B
ON A.a1 = B.b2;
1
2
3
4

右连接示例：

仍然假设有两个表A和B，要求对A表和B表做右连接，匹配条件是A表的a1列和B表的b2列相等：

SELECT A.a1, B.b2
FROM A
RIGHT JOIN B
ON A.a1 = B.b2;
1
2
3
4

全连接示例：

仍然假设有两个表A和B，要求对A表和B表做全连接，匹配条件是A表的a1列和B表的b2列相等：

SELECT A.a1, B.b2
FROM A
FULL OUTER JOIN B
ON A.a1 = B.b2;
1
2
3
4

但需要注意的是，MySQL并没有提供FULL OUTER JOIN语法，但是可以通过UNION和LEFT JOIN、RIGHT JOIN结合起来实现FULL OUTER JOIN的效果。例如：

SELECT A.a1, B.b2
FROM A
LEFT JOIN B
ON A.a1 = B.b2
UNION
SELECT A.a1, B.b2
FROM A
RIGHT JOIN B
ON A.a1 = B.b2;
1
2
3
4
5
6
7
8
9

3 子查询

MySQL子查询是指在一个SQL语句中嵌套另一个完整的SELECT语句，以便在查询过程中使用嵌套的结果。子查询可以嵌套在SELECT、FROM、WHERE、HAVING和IN语句中。

举个例子，假设我们有两个表：学生表（students）和成绩表（grades），我们想查询所有学生的平均分数。可以使用如下的子查询语句：

SELECT AVG(grade) as avg_grade 
FROM grades 
WHERE student_id IN (SELECT id FROM students);
1
2
3

在这个例子中，子查询 SELECT id FROM students 将返回所有学生的id列表，然后主查询中的 WHERE student_id IN (SELECT id FROM students) 将查询成绩表中包含在该列表中的学生的平均成绩。

需要注意的是，子查询在某些情况下可能会影响查询性能，因此应该谨慎使用。

4 UNION操作符

4.1 概述

UNION操作符是一种SQL操作符，用于将两个或多个SELECT语句的结果合并为一个结果集。它会去除重复的行，只保留一次出现的行。 UNION操作符必须在两个SELECT语句中选择的列数和列顺序相同。例如，下面的SQL语句将两个表中的数据合并成一个结果集：

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
1
2
3

需要注意的是，UNION操作符会对结果集进行排序。如果想要控制排序顺序，可以在SELECT语句后面添加ORDER BY子句。

4.2 实例

假设有两个表格：

TableA

TableB

那么如果要将这两个表格的内容合并成一个结果集，可以使用UNION操作符，示例如下：

SELECT id, name FROM TableA
UNION
SELECT id, name FROM TableB
1
2
3

执行以上SQL语句，将会得到如下结果：

可以看到，经过UNION操作符的合并，TableA和TableB的内容被合并成了一个结果集，并且结果集中不含重复行。

5 存储过程

5.1 概述

MySQL存储过程是一种预编译的SQL代码块，可以接收参数，执行一系列操作并返回结果。存储过程可以在MySQL数据库中创建和存储，之后可以被重复调用，减少了大量重复的SQL语句。它们可以用来处理复杂的业务逻辑，提高性能和安全性。

5.2 实例

以下是一个简单的MySQL存储过程示例：

CREATE PROCEDURE `get_user`(IN id INT, OUT name VARCHAR(50), OUT email VARCHAR(50))
BEGIN
  SELECT user_name, user_email INTO name, email FROM users WHERE user_id = id;
END
1
2
3
4

这个存储过程名为“get_user”，它有一个输入参数“id”，以及两个输出参数“name”和“email”。它从名为“users”的表中检索用户的名称和电子邮件，然后将它们存储在输出参数中。

存储过程可以用存储过程语句来调用，如下所示：

CALL get_user(1, @name, @email);
1

在此示例中，存储过程使用ID“1”来获取用户的名称和电子邮件，并将它们存储在名为“@name”和“@email”的变量中。

6 视图

MySQL视图是一个虚拟的关系表，它是由一个SQL语句定义的。视图实际上并不存储数据，它只是一个SQL查询语句的封装。视图可以用作查询、过滤、聚合和组合数据的工具。使用视图可以简化复杂的查询，提高查询效率。视图可以像普通表一样使用SELECT查询，但不能对视图进行更新、删除或插入操作。
视图的语法如下：

CREATE VIEW view_name AS 
SELECT column1, column2, ... 
FROM table_name 
WHERE condition;
1
2
3
4

其中，view_name是视图名称，column1, column2等是视图中包含的列，table_name是从中创建视图的表名，condition是创建视图时使用的筛选条件。例如：

CREATE VIEW view_employee AS 
SELECT name, salary 
FROM employee 
WHERE department = 'Sales';
1
2
3
4

这将创建一个名为view_employee的视图，它只包含销售部门中的员工姓名和薪资。可以使用以下查询来访问此视图：

SELECT * FROM view_employee;
1

注意，视图只是一个SELECT语句的封装，因此它只是一个逻辑概念。当查询视图时，实际上是在执行SELECT语句，并返回查询结果。如果基础表的数据发生了变化，视图的内容也会相应地改变。