MySQL 数据库之数据查询

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 说明

1. <table_name 1>,<table_name 2>:查询的两个数据源（两个数据表)，表名称在命令中的排列不分先后。

2. 当查询结果中返回的列在两个数据源中都存在且名称相同时，在命令中需要指明该列来自哪个数据表。引用形式为“数据表名称.列名称(class.Class_id)”。

3. 为确保查询结果的正确性，查询的两个数据表必须能够建立关系。

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 说明

1. <table_name 1>........<table_name n>:指定查询的多个数据源（数据表名称)。

2. 多个数据表名称在命令中的排列次序不分先后。

3. 多个数据表之间能够根据主、外键建立关系。

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 说明

1. WHERE SELECT.....: WHERE 所引导的查询为子查询，其查询结果为主查询的条件。

2. 子查询的使用方法与主查询相似。

3. 子查询的 SELECT....…..语句在使用时用圆括号括起来，形如:(SELECT...... ) 。

4. 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 说明

1. <table_name 1>,......,<table_name n>:指定查询的多个数据源（数据表名称)。

2. 子查询的 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='女');

结果