文章目录
高级查询的目的是让我们在操作数据库时,不仅能进行基本的增删改查,还可以处理更复杂的数据需求。通过高级查询,我们能够计算数据统计(聚合函数)、按条件分组(分组查询)、合并多张表中的数据(多表连接)、嵌套使用查询结果(子查询)、以及创建虚拟表(视图)。这些操作让我们能更高效地组织和查询信息,尤其在需要从大量数据中筛选特定信息或分析特定趋势时,非常有用。
一、聚合函数:COUNT
、SUM
、AVG
、MIN
、MAX
聚合函数在数据库查询中非常有用,能让我们快速得到数据的总结和汇总结果。比如,想知道一群学生的平均年龄、总人数,甚至是年龄最大和最小的学生,这时候就需要聚合函数来帮忙了。MySQL 里有几个常见的聚合函数:COUNT
、SUM
、AVG
、MIN
、MAX
。我们来一一看看它们的用法。
1. 统计总数:COUNT
COUNT
函数用于计算记录的数量。比如,如果想知道 students
表中有多少个学生,可以用:
SELECT COUNT(*) AS total_students FROM students;
这个查询会返回一个结果,比如 total_students
是15,就代表我们有15个学生。
你还可以加条件,比如想知道年级是Junior
的学生有多少个:
SELECT COUNT(*) AS juniors FROM students WHERE grade = 'Junior';
2. 计算总和:SUM
SUM
函数用于计算某一列值的总和。假设有一个 salary
列存放学生的兼职收入,想知道所有学生收入的总和,可以用:
SELECT SUM(salary) AS total_salary FROM students;
注意,SUM
只能对数值类型的列(比如 INT
或 FLOAT
)进行求和,不能对字符串求和哦。
3. 计算平均值:AVG
AVG
函数用于求平均值。比如,如果想知道学生的平均年龄,可以这样查询:
SELECT AVG(age) AS average_age FROM students;
假设 average_age
是 21,那就表示学生的平均年龄为 21 岁。
和 SUM
一样,AVG
也只能用于数值类型的列。
4. 找最小值:MIN
MIN
函数可以帮你找到某列中最小的值。比如,想知道学生中最小的年龄,可以用:
SELECT MIN(age) AS youngest_age FROM students;
查询结果是 youngest_age
等于 19,就表示最小的学生年龄是 19 岁。
5. 找最大值:MAX
MAX
函数和 MIN
相反,它返回某列中的最大值。比如,想知道学生中最高的年龄是多少岁,可以用:
SELECT MAX(age) AS oldest_age FROM students;
假设 oldest_age
的值是 24,那就说明年龄最大的学生是 24 岁。
综合使用聚合函数的例子
假如我们想要统计每个年级的学生人数、平均年龄和年龄范围,就可以把这些聚合函数组合起来:
SELECT
grade,
COUNT(*) AS num_students, -- 统计每个年级的学生数量
AVG(age) AS avg_age, -- 计算每个年级的平均年龄
MIN(age) AS youngest_age, -- 找出每个年级最小的年龄
MAX(age) AS oldest_age -- 找出每个年级最大的年龄
FROM students
GROUP BY grade;
这个查询会按 grade
分组,返回每个年级的学生数量、平均年龄、最小年龄和最大年龄。比如输出:
grade | num_students | avg_age | youngest_age | oldest_age |
---|---|---|---|---|
Freshman | 5 | 19.8 | 18 | 21 |
Sophomore | 4 | 20.5 | 20 | 22 |
Junior | 3 | 21.3 | 20 | 22 |
Senior | 2 | 23 | 23 | 24 |
这样,我们就对每个年级有了更深入的了解,是不是很方便?
小结
COUNT
:用来统计记录数。SUM
:计算总和,只能用于数值。AVG
:计算平均值,只能用于数值。MIN
:找最小值,可以用于数值和字符串(如按字母顺序)。MAX
:找最大值,可以用于数值和字符串。
掌握这些聚合函数,可以让你更轻松地从数据中找到有用的信息。
二、分组查询——GROUP BY
和 HAVING
在数据库查询中,有时我们不光需要单条记录,还需要对类别或组做统计,比如统计每个年级有多少人,或者每个班级的平均分。这时,GROUP BY
和 HAVING
就是你不可或缺的好帮手了。它们让我们能按某列的值分组,再应用聚合函数(如 COUNT
、SUM
、AVG
等)得到更有意义的结果。
1. 按组来统计——GROUP BY
GROUP BY
的作用就是把数据分成一组一组的,并对每一组应用聚合函数。例如,假设我们想统计每个年级的学生人数。
例子:统计每个年级的学生数量
SELECT grade, COUNT(*) AS student_count
FROM students
GROUP BY grade;
这个查询会按 grade
字段对 students
表进行分组,统计每个年级有多少人。
示例结果:
grade | student_count |
---|---|
Freshman | 5 |
Sophomore | 4 |
Junior | 3 |
Senior | 2 |
也就是说,GROUP BY
把数据按 grade
列的值分为几个组,每个年级的数据汇总在一起,再统计出每组中的记录数量。
2. 为分组结果加条件——HAVING
有时候,我们只想要满足某些条件的分组结果,比如只想知道人数大于 3 的年级。这时,就可以用 HAVING
来限定分组后的结果,它专门用来为分组后的结果加条件。
例子:只显示人数多于 3 的年级
SELECT grade, COUNT(*) AS student_count
FROM students
GROUP BY grade
HAVING student_count > 3;
这里,HAVING
条件作用于分组后的结果。假如结果中有几个年级的 student_count
大于 3,那么只会返回这些年级的数据:
grade | student_count |
---|---|
Freshman | 5 |
Sophomore | 4 |
小提示:不同于
WHERE
,HAVING
是在分组和聚合之后起作用的,常用来筛选聚合结果。
GROUP BY
和 HAVING
的组合使用
假如我们有以下需求:
- 想知道每个年级中学生的平均年龄。
- 只显示那些平均年龄大于 21 岁的年级。
查询语句:
SELECT grade, AVG(age) AS avg_age
FROM students
GROUP BY grade
HAVING avg_age > 21;
在这个例子中,GROUP BY
把学生按年级分组,AVG(age)
计算每个年级的平均年龄,而 HAVING
则用来过滤平均年龄大于 21 的年级。
示例结果:
grade | avg_age |
---|---|
Senior | 23 |
Junior | 21.5 |
小结
GROUP BY
:把数据分组,可以配合聚合函数进行统计。HAVING
:为分组后的结果加条件,类似WHERE
,但WHERE
是作用于单条记录,而HAVING
是作用于分组后的结果。
掌握 GROUP BY
和 HAVING
,你可以轻松地按类别对数据进行统计、汇总,再为汇总后的结果加条件筛选出你想要的信息!
三、多表查询(JOIN)
1、内连接(INNER JOIN)
在实际的数据库操作中,我们常常需要从多个表中获取相关的数据。比如,有一个 students
表存着学生的信息,另一个 courses
表存着学生选课的信息。那么,如何把这两个表中的数据关联起来呢?这就是多表查询的作用,而 INNER JOIN
就是最常用的一种。
什么是内连接(INNER JOIN)
INNER JOIN
是一种只显示两个表中有匹配关系的记录的连接方式。可以理解为,如果我们要从两个表中找数据,INNER JOIN
会挑出在两个表中都有关联的那部分数据进行展示。
例子:学生和课程表
假设我们有两个表:
-
students
表:记录每个学生的基本信息student_id
:学生IDname
:学生名字age
:学生年龄
-
enrollments
表:记录每个学生选课的情况student_id
:学生ID(关联students
表)course_name
:课程名称
假设表的数据是这样的:
students 表
student_id | name | age |
---|---|---|
1 | 张三 | 20 |
2 | 李四 | 22 |
3 | 王五 | 21 |
enrollments 表
student_id | course_name |
---|---|
1 | 数学 |
1 | 英语 |
2 | 物理 |
3 | 化学 |
3 | 数学 |
我们现在想要查出每个学生选了哪些课。因为 enrollments
表和 students
表的 student_id
是关联的,所以我们可以用 INNER JOIN
来实现。
使用 INNER JOIN 查询
SELECT students.name, students.age, enrollments.course_name
FROM students
INNER JOIN enrollments ON students.student_id = enrollments.student_id;
解释:
FROM students
:选择以students
表为主表。INNER JOIN enrollments ON students.student_id = enrollments.student_id
:让students
和enrollments
表通过student_id
字段进行连接。SELECT students.name, students.age, enrollments.course_name
:指定要查询的字段,包括学生的名字、年龄和课程名称。
这个查询结果会返回以下表格:
name | age | course_name |
---|---|---|
张三 | 20 | 数学 |
张三 | 20 | 英语 |
李四 | 22 | 物理 |
王五 | 21 | 化学 |
王五 | 21 | 数学 |
注意:如果
enrollments
表里某个student_id
在students
表中找不到,或者反之,那么这条记录将不会被显示出来。这就是INNER JOIN
,它只显示两边都有匹配的记录。
内连接的应用场景
内连接适合查询两个表之间有明确关联的数据。例如:
- 查询员工的部门信息(
employees
表和departments
表)。 - 查找订单的客户信息(
orders
表和customers
表)。 - 查询学生的选课情况(如上例)。
小结
INNER JOIN
是用于获取两个表中都存在关联关系的记录。- 连接条件常用
ON
关键字指定两个表的关联字段。 - 如果某表中某行数据没有对应的另一表的记录,那么这行数据不会显示在结果中。
通过 INNER JOIN
,我们能有效地把不同表的数据按需要关联起来,从而更方便地进行数据分析和管理。
2、外连接:LEFT JOIN 和 RIGHT JOIN
在多表查询中,有时候我们希望得到的数据不只是完全匹配的部分,还包括在一个表里有、但另一个表里没有的数据。这时候,LEFT JOIN
和 RIGHT JOIN
就派上用场了。它们可以帮我们找到不完全匹配的数据,满足我们更全面的数据需求。
什么是 LEFT JOIN 和 RIGHT JOIN
- LEFT JOIN:以左边的表为主,显示左表中所有的数据,不论右表中有没有匹配项。
- RIGHT JOIN:以右边的表为主,显示右表中所有的数据,不论左表中有没有匹配项。
这两种 JOIN 都叫“外连接”,因为它们不仅仅连接两表中的匹配数据,还会把没有匹配的数据包含进来,让查询结果更加全面。
举例:学生和课程表
还是用学生和选课的例子来理解 LEFT JOIN
和 RIGHT JOIN
。
-
students
表:记录每个学生的信息。student_id
:学生IDname
:学生名字
-
enrollments
表:记录学生选课情况。student_id
:学生ID(关联students
表)course_name
:课程名称
假设有如下数据:
students 表
student_id | name |
---|---|
1 | 张三 |
2 | 李四 |
3 | 王五 |
4 | 赵六 |
enrollments 表
student_id | course_name |
---|---|
1 | 数学 |
1 | 英语 |
2 | 物理 |
3 | 化学 |
students
表有四个学生,其中“赵六”没有选课记录;而 enrollments
表只有三位学生的课程信息。
使用 LEFT JOIN 查询
假如我们想知道每个学生选了哪些课,即使有的学生没有选课,我们也希望在结果中看到他们的信息。这时候就可以用 LEFT JOIN
。
SELECT students.name, enrollments.course_name
FROM students
LEFT JOIN enrollments ON students.student_id = enrollments.student_id;
解释:
FROM students
:以students
表作为主表。LEFT JOIN enrollments ON students.student_id = enrollments.student_id
:显示所有学生的信息,即使某学生在enrollments
表里没有选课记录。
查询结果:
name | course_name |
---|---|
张三 | 数学 |
张三 | 英语 |
李四 | 物理 |
王五 | 化学 |
赵六 | NULL |
在结果中可以看到“赵六”这一行,即使他没有课程信息,他的名字也会显示,course_name
列则显示 NULL
,表示没有匹配的课程记录。
使用 RIGHT JOIN 查询
如果我们希望以课程表为主,显示每门课程的信息,即便有的课程没人选,可以用 RIGHT JOIN
。
假设我们有如下 enrollments
表,新增了一门没人选的课程“历史”:
enrollments 表
student_id | course_name |
---|---|
1 | 数学 |
1 | 英语 |
2 | 物理 |
3 | 化学 |
NULL | 历史 |
用 RIGHT JOIN
查询学生和课程信息:
SELECT students.name, enrollments.course_name
FROM students
RIGHT JOIN enrollments ON students.student_id = enrollments.student_id;
查询结果:
name | course_name |
---|---|
张三 | 数学 |
张三 | 英语 |
李四 | 物理 |
王五 | 化学 |
NULL | 历史 |
可以看到“历史”这一行,即使没人选,课程名称仍然会显示出来,而 name
列显示为 NULL
,因为没有对应的学生信息。
总结
- LEFT JOIN:以左表为主,显示左表中的所有数据,如果右表中没有匹配数据,用
NULL
补上。 - RIGHT JOIN:以右表为主,显示右表中的所有数据,如果左表中没有匹配数据,用
NULL
补上。
在实际操作中,选择 LEFT JOIN
或 RIGHT JOIN
取决于你想要的数据主表是哪个,这样可以确保所需信息无遗漏。
3、交叉连接与自连接:CROSS JOIN 和 SELF JOIN
在多表查询中,有一些特殊的连接类型,像 CROSS JOIN
和 SELF JOIN
,它们并不在乎表之间的关系,而是有各自的用途。CROSS JOIN
会产生笛卡尔积,列出所有可能的组合;而 SELF JOIN
则是用来让同一个表和自己“对话”,从而实现一些更灵活的查询。
什么是 CROSS JOIN(交叉连接)
交叉连接(CROSS JOIN) 是将两个表的所有行按顺序完全组合。也就是说,每一个左表的行都会与右表的每一行组合一次,这种连接方式会返回一个笛卡尔积,行数是两个表行数的乘积。
适用场景:假设我们要生成一个“所有可能的组合”列表,比如将颜色与产品组合、生成每位学生的时间表等等,这种完全匹配的列表,就适合使用 CROSS JOIN
。
举个例子:颜色和尺寸的组合
假设我们有两个表:
-
colors
表:包含几种颜色。color_name
:颜色名称
-
sizes
表:包含几种尺寸。size_name
:尺寸名称
colors 表
color_name |
---|
红色 |
蓝色 |
绿色 |
sizes 表
size_name |
---|
S |
M |
L |
如果我们想要列出所有颜色和尺寸的组合,可以用 CROSS JOIN
。
SELECT colors.color_name, sizes.size_name
FROM colors
CROSS JOIN sizes;
结果是每种颜色都与每种尺寸进行了组合,输出如下:
color_name | size_name |
---|---|
红色 | S |
红色 | M |
红色 | L |
蓝色 | S |
蓝色 | M |
蓝色 | L |
绿色 | S |
绿色 | M |
绿色 | L |
在这个例子中,CROSS JOIN
返回了三种颜色和三种尺寸的所有可能组合。
什么是 SELF JOIN(自连接)
自连接(SELF JOIN) 是让一个表和自己进行连接,用于在同一个表中找到特定关系,比如比较两行的数据或找出相互关联的数据。其实就是把一个表临时复制一份,然后“左手和右手握手”。
适用场景:比如在员工表中找出同部门的员工对,或找出用户之间的某种关系时。
举个例子:查找员工的上下级关系
假设我们有一个员工表 employees
:
employee_id
:员工编号name
:员工名字manager_id
:直接主管的编号
employees 表
employee_id | name | manager_id |
---|---|---|
1 | 张三 | NULL |
2 | 李四 | 1 |
3 | 王五 | 1 |
4 | 赵六 | 2 |
在这里,manager_id
表示员工的直接主管。张三是李四和王五的主管,而李四是赵六的主管。假如我们想列出每位员工的名字和他们主管的名字,就可以用 SELF JOIN
:
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;
在这段查询中,我们用 e1
和 e2
作为 employees
表的两个实例。e1
代表的是员工本人,而 e2
代表的是他们的主管。ON e1.manager_id = e2.employee_id
的条件让每个员工和他们的主管匹配在一起。
查询结果如下:
employee | manager |
---|---|
张三 | NULL |
李四 | 张三 |
王五 | 张三 |
赵六 | 李四 |
在这个结果中,我们列出了每位员工的主管,即使有的员工没有主管(例如张三),也显示了 NULL。这个查询帮助我们从单个表里找到一对一的上下级关系。
总结
- CROSS JOIN:产生两个表的笛卡尔积,适合列出所有组合。
- SELF JOIN:让表和自己关联,适合查询同一表中某些数据之间的关系,比如上下级或配对关系。
这些连接方式在处理特殊场景时非常实用,让查询的灵活性更高。
四、子查询:单行、多行、嵌套子查询
子查询其实就是在一个查询里再嵌入另一个查询。它帮助我们先找到一些数据,再用这些数据来完成主查询。子查询有不同的形式,像单行子查询、多行子查询和嵌套子查询。让我们一起来看看它们是什么、怎么用,配上简单的例子,帮助更好地理解。
单行子查询
单行子查询就是子查询只返回一行数据的情况。在这种情况下,子查询的结果通常用在主查询的WHERE
或SELECT
部分,用来帮助过滤数据或计算一些值。
举个例子:找出工资最高的人的名字
假设我们有一个员工表 employees
:
id | name | salary |
---|---|---|
1 | 张三 | 5000 |
2 | 李四 | 7000 |
3 | 王五 | 8000 |
我们想要知道谁的工资最高。我们可以通过子查询找到最高的工资,再用它来过滤出对应的名字。
SELECT name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
这里,子查询 (SELECT MAX(salary) FROM employees)
会返回一个值 8000
,也就是最高工资,主查询接着用 WHERE salary = 8000
过滤出工资为 8000 的员工——王五。
多行子查询
多行子查询就是子查询返回多行数据的情况。通常情况下,我们会用 IN
、ANY
或 ALL
等关键字来让主查询匹配子查询的多个结果。
举个例子:找出工资高于某些特定职位的员工
假设我们有两个表:
employees
表:列出所有员工的名字和工资。positions
表:列出特定职位的最低工资要求。
employees 表
id | name | salary |
---|---|---|
1 | 张三 | 5000 |
2 | 李四 | 7000 |
3 | 王五 | 8000 |
positions 表
position_id | min_salary |
---|---|
1 | 6000 |
2 | 7500 |
我们想找出所有工资比任何一个职位最低工资都高的员工。这时可以用多行子查询:
SELECT name
FROM employees
WHERE salary > ANY (SELECT min_salary FROM positions);
在这个查询中,(SELECT min_salary FROM positions)
会返回一个列表 [6000, 7500]
。主查询就会找出比这个列表中任何一个数字大的工资,比如张三和李四的工资都满足条件。
嵌套子查询
嵌套子查询指的是在子查询里面再嵌入子查询,让查询变得层层深入。嵌套子查询通常用在更复杂的查询里,比如想要多步过滤数据的情况。
举个例子:找到某部门工资最高的员工
假设我们再加一个部门表 departments
,然后员工表里也有部门信息:
departments 表
department_id | department_name |
---|---|
1 | 人事部 |
2 | 技术部 |
employees 表
id | name | salary | department_id |
---|---|---|---|
1 | 张三 | 5000 | 1 |
2 | 李四 | 7000 | 2 |
3 | 王五 | 8000 | 2 |
4 | 赵六 | 5500 | 1 |
我们现在要找出每个部门工资最高的员工。在这里,我们可以通过嵌套子查询先找到每个部门的最高工资,再用这些结果过滤出对应的员工。
SELECT name, department_id
FROM employees
WHERE salary IN (SELECT MAX(salary)
FROM employees
GROUP BY department_id);
在这里,最内层的子查询 (SELECT MAX(salary) FROM employees GROUP BY department_id)
会先按照每个部门返回最高工资。然后,主查询会筛选出符合这个最高工资的员工。
最终结果可能是:
name | department_id |
---|---|
赵六 | 1 |
王五 | 2 |
总结
- 单行子查询:返回单个值,用来直接对比。
- 多行子查询:返回多个值,用
IN
、ANY
或ALL
进行多重匹配。 - 嵌套子查询:子查询中套子查询,适合更复杂的多步过滤操作。
子查询是一种强大的工具,能够在一个查询中分步处理数据。掌握这些概念后,就可以实现更灵活的数据筛选。
五、视图(VIEW)
视图(VIEW)在数据库里有点像一个“假窗户”。你可以通过视图“看到”一部分数据,但是这些数据其实还是存在于原始的表里,并没有被复制。视图可以让查询更简洁,还能让我们控制数据访问,更灵活地管理权限。视图的操作基本上包括创建视图、更新视图里的数据,以及删除视图。
什么是视图
视图是一个基于查询结果的“虚拟表”。它本身不存储数据,而是保存一个查询。当我们对视图做查询时,它会把视图里的查询运行一遍,然后返回结果。想象一下,我们有一个大的员工表 employees
,包含很多信息,比如姓名、年龄、工资、部门等。你可能只需要查看员工的姓名和工资,并不想每次都写一长串的查询条件。那么我们就可以用视图来搞定这件事。
创建视图:CREATE VIEW
要创建视图,我们用 CREATE VIEW
语句,并给视图起一个名字。然后我们写出视图的查询逻辑,也就是告诉数据库:从哪些表里选数据、需要哪些列。
举个例子
假设我们有这样一个员工表 employees
:
id | name | salary | department |
---|---|---|---|
1 | 张三 | 5000 | 技术部 |
2 | 李四 | 7000 | 人事部 |
3 | 王五 | 8000 | 技术部 |
如果我们只想要看员工的 name
和 salary
,可以创建一个视图 employee_salary
来简化查询:
CREATE VIEW employee_salary AS
SELECT name, salary
FROM employees;
这样我们每次查询 employee_salary
视图时,就相当于执行了 SELECT name, salary FROM employees
。我们只需写一句简单的 SELECT * FROM employee_salary
就能看到员工的名字和工资!
视图的优点
- 简化查询:像上面的例子一样,把复杂的查询简化成一个视图。
- 数据安全:视图可以限制用户只能看到一部分数据,比如上例中创建了一个不含部门信息的视图,敏感数据得到了保护。
- 动态更新:视图本质上是一个查询,所以它的内容会跟着原始表的变化而变化。如果员工的工资发生变化,再查询视图时得到的也是最新数据。
更新视图里的数据
有时我们希望通过视图直接更新数据,比如在视图中修改某员工的工资。在大多数数据库中,这是可以实现的,但是也有一些限制。通常,视图能更新的前提是:
- 视图基于单个表创建,而不是多表的复杂查询。
- 视图不包含聚合操作(比如
SUM
、AVG
等)。
举个例子
假设我们需要通过 employee_salary
视图更新李四的工资:
UPDATE employee_salary
SET salary = 7500
WHERE name = '李四';
这个更新会直接影响到 employees
表里的数据,因为视图 employee_salary
是基于 employees
表的。所以更新视图里的数据就像在修改原始表里的数据一样。
删除视图:DROP VIEW
当我们不再需要某个视图时,可以用 DROP VIEW
删除它。这个操作只是删除了视图本身,并不会影响原始表的数据。
删除视图的例子
如果不再需要 employee_salary
视图,可以删除它:
DROP VIEW employee_salary;
删除后,employee_salary
就不存在了,当然也无法再查询它了。
小结
视图是数据库管理中的好帮手,可以帮助我们简化查询、保护数据和动态更新。简单来说:
- 创建视图:用
CREATE VIEW
把常用查询封装成一个虚拟表。 - 更新视图:在满足条件的情况下,可以通过视图更新原始数据。
- 删除视图:用
DROP VIEW
删除不再需要的视图。
掌握视图之后,我们的数据库查询操作会更高效,也更灵活。
标签:JOIN,name,students,高级,视图,查询,MySQL,SELECT From: https://blog.csdn.net/m0_63141213/article/details/143311874