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现有信息统计有多少个系。

查询指令

SELECTCOUNT(*)AS系数FROM department

查询结果

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

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

1.6.2 举例2

问题

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

查询指令

SELECTMAX(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)中统计全体学生人数。

查询指令

SELECTSUM(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_name1>,<table_name2>[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, student

WHERE class.class_id=student.class_id

查询结果

3.2 多表列查询

3.2.2 定义

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

3.2.3 语句格式

SELECT{[AII][DISTINCT]|<column1>,<column2>,<column n>}FROM<table_name1>,......,<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,score

WHERE 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_name1>,<table_name2>

[WHERESELECT......]

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_idAND class.class_id lN (SELECTDISTINCT class_id FROM student );

查询结果

4.2 多表嵌套查询

4.2.1 定义

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

4.2.2 语句格式

SELECT{[AlI][[DISTINCT]|<column1>,<column2>,<column n>}FROM<table_name1>,.......<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,assignment

WHERE 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_name1>,......<table_name n>

[WHEREIN<expression>[HAVING<expression>[{<operator><expression>}.....….]][ORDERBY<orderby 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_name1>,......<table_name n>

[WHEREALL<expression>

[HAVING<expression>[f<operator><expression>}.... ..]][ORDERBY<orderby definition>]

5.2.3 说明

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

5.2.4 举例2

问题

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

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

查询指令

SELECT*FROM score

WHERE score >ALL

(SELECTMIN(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_name1>,......<table_name n>

[WHERE ANY <expression>

[HAVING<expression>[{f<operator><expression>}......]][ORDERBY<orderby definition>]

5.3.3 说明

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

5.3.4 举例3

问题

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

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

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

查询指令

SELECT*

FROM student

WHERE gender='男'AND birth < ANY(SELECTMIN(birth)FROM studentWHERE gender='女');

结果