MySQL 数据库之数据查询

作者：乌龟哥哥

2022 年 8 月 28 日
上海
本文字数：6149 字
阅读完需：约 20 分钟

1.集函数查询

1.1 集合

在数学的概念中，指定的某些对象的全体称为一个集合。在 MySQL 中的集合是指查询结果中的全体记录。

1.2 函数

计算机中的函数是实现固定运算功能的一个程序段或子程序。计算机中函数调用格式:函数名(参数 1，参数 2，...…)

1.3 集合函数查询

集合函数查询是指对查询的结果记录集针对某个或某几个列利用某个函数实施相应的运算，并输出运算结果，一般用于对某个或某几个列的值进行统计计算操作。如:COUNT(student_id)、MAX)、MIN()、AVG()、SUM()等等

1.4 MySQL 常用的集函数及功能

已知数据表 department (department_id, department_name, department_dean,teacher_num, class_num, school_id)的信息如下:

1.5 COUNT ()函数使用说明

COUNT(*)
返回数据表中的记录数
COUNT(column_name)
返回指定列的值的个数
COUNT(DISTINCT column_name)
返回指定列的不同值的个数
说明
在使用 COUNT 进行统计计数时，空值(NULL)不进行统计

1.6 举例

1.6.1 举例 1

问题

利用 department 现有信息统计有多少个系。

查询指令

SELECT COUNT(*)AS系数FROM department

复制代码

查询结果

以下是==SELECT COUNT(*)AS 教师人数 FROM departmen==t 的执行结果集

虚表不能进行编辑、导出 SQL 操作

1.6.2 举例 2

问题

在 department 中查找教师人数最多是多少。

查询指令

SELECT MAX(teacher_num)AS教师人数FROM department

复制代码

查询结果

以下是SELECT MAX(Teacher_num)AS教师人数FROM department的执行结果集

虚表不能进行编辑、导出 SQL 操作

1.6.3 举例 3

问题

在数据表 class(class_id, class_name, student_num, monitor, major,department_id)中统计全体学生人数。

查询指令

SELECT SUM(student_num)AS全体学生数FROM class

复制代码

查询结果

以下是SELECT SUM(student_num)AS全体学生数FROM class的执行结果集

虚表不能进行编辑、导出 SQL 操作

2.简单查询

所有列查询是指查询结果返回数据源的所有字段，而指定列查询是指只返回数据源查询的部分字段

2.1 所有列查询

所有列查询不会对数据源进行任何筛选和限制，直接返回数据源的所有行和所有列，所以我们可以用*来代表所有行和所有列

2.1.1 语句格式

SELECT *[AII] ][DISTINCT]FROM <table_name >

复制代码

2.1.2 功能

返回<table_name>表的所有记录的所有列。

2.1.3 举例 1

已知数据表 student，使用如下 SQL 查询语句可以返回 student 表的所有列。

查询指令

SELECT * FROM student

复制代码

查询结果

在 GaussDB(for MySQL)管理控制平台执行上述 SQL 查询命令，结果如下图所示。

2.2 指定列查询

2.2.1 语句格式

SELECT {[DISTINCT] / <column1>,<column2>...}FROM <table name >

复制代码

2.2.2 功能

返回< table _name >表所有记录的< column1 >,< column2 >.…..列。

2.2.3 举例 2

问题

根据数据表 student，查看全体学生学号、姓名、籍贯等信息。

查询指令

SELECT student_id, student_name, birthplace FROM student

复制代码

结果

2.3 指定行查询

2.3.1 定义

指在查询命令中添加了查询条件设置，查询时，只有数据集中满足条件的记录才会出现在查询结果集中，返回的记录将包含所有列。

2.3.2 语句格式

SELECT [All] |[DISTINCT] FROM <table name >WHERE <expression>

复制代码

2.3.3 说明

1.WHERE:查询条件的引导关键字，查询条件必须以 WHERE 开头。2.< expression >:查询条件表达式，一般由比较表达式或逻辑表达式组成。

2.3.4 举例 3

问题

在数据表 class(class_id,class_name, student_num, monitor， major, department_id)查看 A101 系各班级的信息。

查询指令

SELECT* FROM class WHERE department_id='A101'

复制代码

查询结果

2.4 指定行和列查询

2.4.1 定义

指在查询命令中添加了查询条件设置，查询时，只有数据集中满足条件的记录才会出现在查询结果集中，返回的记录只包含指定的列。

2.4.2 语句格式

SELECT {[ DISTINCT] | <column1>,<column2> ...}FROM <table_name> WHERE <expression>

复制代码

2.4.3 说明

1.DISTINCT:重复记录只保留一条，可选项。2.<column1>,<column2>>.…:指定查询结果中各记录返回的列名称。3.WHERE:查询条件的引导关键字，查询条件必须以 WHERE 开头。4.<expression>:查询条件条件表达式，一般由比较表达式或逻辑表达式组成。

2.4.4 举例 4

问题

在数据表 class(Class_id, Class_name, Student_num, Monitor,Major,Department_id)查看 A101 系各班级的部分信息(班级名称，班级人数，系编号)。

查询指令

SELECT class_name, student_num,department_idFROM class WHERE department_id='A101'

复制代码

查询结果

3.多表查询

前面我们所学的查询语句中，查询源都只有一个，在实际 1 应用中，为了便于后期数据的维护，在进行数据库逻辑设计时，我们往往把不同主题的信息分别存放在不同的数据表中，在需要时从不同的数据表中提取出来。

3.1 两表查询

3.1.1 定义

指查询的数据源（记录源)不只一个数据表，而是两个数据表，查询结果集中的列也分别是来自这两个表的列或新增的计算列。

3.1.2 语句格式

SELECT{[AIl] [[DISTINCT] | <column1>,<column2>,column n>}FROM <table_name 1>, <table_name 2>[WHERE<expression>]

复制代码

3.1.3 说明

<table_name 1>,<table_name 2>:查询的两个数据源（两个数据表)，表名称在命令中的排列不分先后。
当查询结果中返回的列在两个数据源中都存在且名称相同时，在命令中需要指明该列来自哪个数据表。引用形式为“数据表名称.列名称(class.Class_id)”。
为确保查询结果的正确性，查询的两个数据表必须能够建立关系。

3.1.4 举例 1

问题

根据数据表 student 和 class 的信息，查看各班级的学生部分信息，并返回“班级名称”，“学号”，“姓名”，“性别”和“出生年月”。

student 表

class 表

查询指令

SELECT class_name, student_id, student_name, gender, birthFROM class, studentWHERE class.class_id=student.class_id

复制代码

查询结果

3.2 多表列查询

3.2.2 定义

指查询的数据源（记录源）是多个数据表，查询结果集中的列也分别是来自这些表的列或新增的计算列。

3.2.3 语句格式

SELECT {[AII] [DISTINCT] |<column1>,<column2>,<column n> }FROM<table_name 1>,......,<table_name n>[WHERE<expression>]

复制代码

3.2.4 说明

<table_name 1>........<table_name n>:指定查询的多个数据源（数据表名称)。
多个数据表名称在命令中的排列次序不分先后。
多个数据表之间能够根据主、外键建立关系。

3.2.5 举例 2

问题

根据数据库表 student、表 course 和表 score 的信息，查看部分学生成绩（学号，姓名，课程名称和成绩)。

其中各数据表的字段(列）分布如下

student 表

class 表

score 表

查询指令

SELECT student.student_id, student_name, course_name, scoreFROM student, course,scoreWHERE score.student_id=student.student_idAND score.course_id=course.course_id

复制代码

查询结果

4.多表嵌套查询

嵌套查询指在查询中再包含查询，其中包含查询的查询称为主查询，被包含的查询称为子查询。

4.1 两表嵌套查询

4.1.1 定义

两表嵌套查询是指一个表的查询(子查询）结果作为数据源或查询条件应用到另一个查询(主查询)中。

4.1.2 语句格式

SELECT {[AII] [DISTINCT] |<column1>,<column2>,...... column n > }FROM<table_name 1>,<table_name 2>[WHERE SELECT......]

复制代码

4.1.3 说明

WHERE SELECT.....: WHERE 所引导的查询为子查询，其查询结果为主查询的条件。
子查询的使用方法与主查询相似。
子查询的 SELECT....…..语句在使用时用圆括号括起来，形如:(SELECT...... ) 。
FROM 之后也可以跟子查询。

4.1.4 举例 1

问题

根据数据表 class 和表 student 的信息，查看部分班级学生部分信息（班级名称，学号，姓名，性别、出生年月)。其中 class 和 student 的字段和数据分布如下

department 表

class 表

查询指令

SELECT class_name, student_id, student_name, gender, birthFROM class, student WHERE class.class_id = student.class_id AND class.class_id lN ( SELECT DISTINCT class_id FROM student );

复制代码

查询结果

4.2 多表嵌套查询

4.2.1 定义

指在“两表嵌套查询”的基础上增加更多的数据源，其他要求与“两表嵌套查询”相似。

4.2.2 语句格式

SELECT {[AlI] [[DISTINCT] | <column1>,<column2>,<column n> }FROM<table_name 1>,....... <table_name n>[WHEERE SELECT.....]

复制代码

4.2.3 说明

<table_name 1>,......,<table_name n>:指定查询的多个数据源（数据表名称)。
子查询的 SELECT.....语句在使用时用圆括号括起来，形如:（ SELECT......) 。

4.2.4 举例 2

问题

根据数据库表 teacher、表 assignment 和表 course 的信息，查看部分教师讲授的课程信息（教师姓名，课程名称，学分)。其中 teacher、assignment 和 course 的数据分布如左图，字段分布如下图。

查询指令

SELECT TA.teacher_name, course_name, creditFROMcourse,(SELECT teacher_name, course_idFROMteacher,assignmentWHERE teacher.teacher_id=assignment.teacher _id ) AS TAWHERE course.course_id=TA.course_id

复制代码

查询结果

5. 子查询

能够引导子查询的关键字有很多,我们主要学习IN,ALL,ANY.

5.1 带`IN`关键字的子查询

5.1.1 适用情形

IN 关键字主要适用于验证主查询条件值是否位于某个可枚举的数据集合中。

5.1.2 语句格式

SELECT {[AII] [[DISTINCT] | <column1>,<column2>.......,<column n> }FROM<table_name 1>,...... <table_name n>[WHERE IN <expression>[HAVING <expression> [{<operator> <expression>}.....….]][ORDER BY <order by definition>]

复制代码

5.1.2 说明

IN< expression >: expression 在查询命令中需要用圆括号括起来。

5.1.3 举例 1

问题

根据数据表 course 的信息，查看 1、4 学期的课程信息。

其中 course 的字段和数据分布如下

course ( course_id, course_name, period, credit, term ),对应的中文列名称为 course (课程编号，课程名称，学时，学分，学期)

查询指令

SELECT *FROM course WHERE term IN ('1';4')

复制代码

等价于

SELECT * FROM course WHERE term='1' or term='4’

复制代码

查询结果

5.2 带 ALL 关键字的子查询

5.2.1 定义

将主查询的某个值与子查询返回的一组值进行比较，根据比较结果决定条件是否满足。必须所有比较满足才算满足。例如:x >ALL(...)

5.2.2 语句格式

SELECT {[AlI] ][DISTINCT] |<column1>,<column2>......., column n> }FROM<table_name 1>,......<table_name n>[WHERE ALL <expression>[HAVING <expression> [f<operator> <expression>}.... ..]][ORDER BY <order by definition>]

复制代码

5.2.3 说明

ALL:比较运算包括=、<>、>=、<、<=等等。

5.2.4 举例 2

问题

根据数据表 score 的信息，查看所有高于“01-03"课程最低分的课程成绩信息。

其中 score 的字段和数据分布 score ( student_ id, course_id, score )

查询指令

SELECT*FROM scoreWHERE score > ALL(SELECT MIN(score) FROM scoreWHERE course_id='01-03')

复制代码

查询结果

5.3 带 ANY 关键字的子查询

5.3.1 定义

将主查询的某个值与子查询返回的一组值进行比较，根据比较结果决定条件是否满足，只要比较的任何一个满足就算满足。例如:x>ANY(...)

5.3.2 语句格式

SELECT {[AlI] ][DISTINCT] |<column1>,<column2>,.......<column n>}FROM <table_name 1>,...... <table_name n>[WHERE ANY <expression>[HAVING<expression> [{f<operator> <expression>}......]][ORDER BY <order by definition>]

复制代码

5.3.3 说明

ANY:比较运算包括=、<>、>=、<、<=等等。

5.3.4 举例 3

问题

根据数据表 student 的信息，查询比任意女同学年龄大的男同学信息。.

其中 student 的字段和数据分布如下

student ( student_id, student_name, gender, birth, birthplace,class_id )

查询指令

SELECT*FROM studentWHERE gender='男'AND birth < ANY(SELECT MIN(birth)FROM studentWHERE gender='女');

复制代码

结果

6. 华为云 RDS 数据库使用

6.1 数据库购买流程

打开华为云数据库控制台(https://console.huaweicloud.com/rds)

首先在左上角选择自己需要购买的资源的所在区域，我这里以上海二为例

点击右上角购买数据库实例

可以根据自己的需要选择包年包月或者按需计费

不同的数据库引擎价格和功能也不一样，可以根据需要选取，我选择的为 mysql

云数据库 RDS for MySQL
MySQL 是目前最受欢迎的开源数据库之一，其性能卓越，搭配 LAMP（Linux + Apache + MySQL + Perl/PHP/Python），成为 WEB 开发的高效解决方案。云数据库 RDS for MySQL 拥有即开即用、稳定可靠、安全运行、弹性伸缩、轻松管理、经济实用等特点。1、架构成熟稳定，支持流行应用程序，适用于多领域多行业；支持各种 WEB 应用，成本低，中小企业首选。2、管理控制台提供全面的监控信息，简单易用，灵活管理，可视又可控。3、随时根据业务情况弹性伸缩所需资源，按需开支，量身订做。
云数据库 RDS for PostgreSQL
PostgreSQL 是一个开源对象关系型数据库管理系统，并侧重于可扩展性和标准的符合性，被业界誉为“最先进的开源数据库”。云数据库 RDS for PostgreSQL 面向企业复杂 SQL 处理的 OLTP 在线事务处理场景，支持 NoSQL 数据类型（JSON/XML/hstore），支持 GIS 地理信息处理，在可靠性、数据完整性方面有良好声誉，适用于互联网网站、位置应用系统、复杂数据对象处理等应用场景。1、支持 postgis 插件，空间应用卓越，达到国际标准。降低“O”迁移复杂度 100%。2、适用场景丰富，费用低，随时可以根据业务情况弹性伸缩所需的资源，按需开支，量身订做。
云数据库 RDS for SQLServer
Microsoft SQL Server 是老牌商用级数据库，成熟的企业级架构，轻松应对各种复杂环境。一站式部署、保障关键运维服务，大量降低人力成本。根据华为国际化安全标准，打造安全稳定的数据库运行环境。被广泛应用于政府、金融、医疗、教育和游戏等领域。云数据库 RDS for SQLServer 具有即开即用、稳定可靠、安全运行、弹性伸缩、轻松管理和经济实用等特点。拥有高可用架构、数据安全保障和故障秒级恢复功能，提供了灵活的备份方案。