数据库技术与应用 课件 项目5 检索学生成绩管理系统中的数据

项目5检索学生成绩管理系统中的数据数据库应用基础添加数据的方法有哪两种？插入一条数据的语法？如何删除表中的全部数据？INSERT[INTO]<表名>[(<列名1>[,…<列名n>])]VALUES(值1)[…,(值n)]DELETE

FROM

<表名>界面方式和命令方式复习

掌握SELECT语句的语法(重点）

掌握条件查询基本方法(重点）

掌握分组与汇总方法(重点、难点)掌握实现连接查询的方法(重点）掌握实现子查询的方法(重点、难点)本章目标

使用简单查询语句进行单表数据的检索

使用条件查询

查询排序和限制查询结果条数

使用分组和汇总查询数据

使用连接查询进行多表数据的检索

使用子查询进行数据检索、插入、更新和删除本章任务数据库管理系统的一个最重要的功能就是数据查询，查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。数据查询不只是简单查询数据库中存储的数据，还可以根据需要对数据进行筛选，以及确定数据以什么样的格式显示。MySQL提供了功能强大、灵活的语句来实现这些操作。可以使用SELECT语句从表或者视图中查询数据，SELECT语句的结果称为结果集，它是行列表，每行由相同数量的列组成。SELECT语句概述1、SELECT语句简单查询SELECT的语法格式如下：SELECT{*|<字段列名>}[FROM<表1>,<表2>…[WHERE<表达式>[GROUPBY<groupbydefinition>[HAVING<expression>[{<operator><expression>}…]][ORDERBY<orderbydefinition>][LIMIT[<offset>,]<rowcount>]]SELECT语句的语法1、SELECT语句简单查询说明：（1）{*|<字段列名>}：包含星号通配符的字段列表，表示所要查询字段的名称。（2）<表1>，<表2>…，：表1和表2表示查询数据的来源，可以是单个或多个。（3）WHERE<表达式>：是可选项，如果选择该项，将限定查询数据必须满足该查询条件。（4）GROUPBY<字段>：该子句告诉MySQL如何显示查询出来的数据，并按照指定的字段分组。（5）[ORDERBY<字段>]：该子句告诉MySQL按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC），默认情况下是升序。（6）[LIMIT[<offset>，]<rowcount>]：该子句告诉MySQL每次显示查询出来的数据条数。查询表中所有列查询表中指定列给表中字段名指定别名计算列值消除重复数据简单查询语句1、SELECT语句简单查询查询表中所有列说明：（1）*能匹配表中的所有字段名，即查询了表中所有的列。（2）表名：查询数据的来源，指数据库中的表或者视图。简单查询语句1、SELECT语句简单查询语法格式：SELECT*FROM表名；查询表中所有列简单查询语句1、SELECT语句简单查询动手操作：从学生表student中查询所有学生的信息注意：（1）使用“*”查询表中所有数据，查询结果的字段顺序与表中的字段顺序一致。（2）SELECT子句指定表中的所有字段，也能实现查询表中所有列。查询结果的字段顺序与SELECT子句字段顺序一致。查询表中指定列说明：（1）字段1,字段2...：字段名要与表名中的字段名一致。字段之间用英文逗号隔开。简单查询语句1、SELECT语句简单查询语法格式：SELECT字段1,字段2...FROM表名;查询表中指定列简单查询语句1、SELECT语句简单查询动手操作：从学生表student中查询学生的学号sno，姓名sname，性别gender给表中字段名指定别名如果要在查询的结果中显示的名字与原表中的字段名(英文)不一样，比如用中文显示，可以给列指定别名实现。说明：（1）别名：需要用引号引起来。注意：使用AS关键字指定列的别名，也可以省略。省略后需要将字段名和别名用空格隔开。简单查询语句1、SELECT语句简单查询语法格式：SELECT字段1AS别名,字段2AS别名...FROM表名;给表中字段名指定别名简单查询语句1、SELECT语句简单查询动手操作：从学生表student中查询学生的学sno，姓名sname，性别gender，查询结果中要显示别名学号、姓名和性别。计算列值查询结果中可以输出列的计算后的值，即SELECT后面可以使用表达式。表达式可以是MySQL支持的任何运算表达式说明：（1）字段参与的表达式：字段可以直接参与运算。简单查询语句1、SELECT语句简单查询语法格式：SELECT字段参与的表达式FROM表名；计算列值简单查询语句1、SELECT语句简单查询动手操作：从学生表student中查询学生的学号、姓名和年龄。消除重复数据DISTINCT

关键字的主要作用就是对数据表中一个或多个字段重复的数据进行过滤，只返回其中的一条数据说明：（1）字段名：为需要消除重复记录的字段名称，多个字段时用逗号隔开。简单查询语句1、SELECT语句简单查询语法格式：SELECTDISTINCT<字段名>FROM<表名>;消除重复数据简单查询语句1、SELECT语句简单查询动手操作：从学生表student中查询学生所属班级cnoWHERE子句紧跟在FROM子句之后，在WHERE子句中使用一个条件从FROM子句的中间结果中选取行。使用WHERE关键字的语法格式如下：WHERE<查询条件>说明：（1）查询条件：

1）带比较运算符的查询条件

2）带逻辑运算符的查询条件

3）带BETWEENAND关键字的查询条件WHERE子句2、使用条件查询说明：（1）查询条件：

4）带IN关键字的查询条件

5）带LIKE关键字的查询条件

6）带ISNULL关键字的查询条件（2）WHERE子句会根据查询条件对FROM子句中的中间结果行逐一进行判断，当条件为TRUE的时候，一行就被包含到WHERE子句的中间结果中。WHERE子句2、使用条件查询比较运算比较运算符用于比较两个表达式的值，MySQL支持的比较运算符有等于=、小于<、小于等于<=、大于>、大于等于>=、不等于<>或!=。说明：比较运算符：=、<、<=、>、>=、<>或者!=查询条件2、使用条件查询语法格式：表达式{比较运算符}表达式;比较运算查询条件2、使用条件查询动手操作：从学生表student中查询男生的学号、姓名和出生日期比较运算查询条件2、使用条件查询动手操作：从学生表student中查询年龄大于等于21岁的学生的学号、姓名。逻辑运算WHERE关键词后可以有多个查询条件，多个查询条件时用到逻辑运算符AND（&&）、OR（||）、XOR和NOT(!)隔开。说明：（1）AND：记录满足所有查询条件时，才会被查询出来。（2）OR：记录满足任意一个查询条件时，才会被查询出来。（3）XOR：记录满足其中一个条件，并且不满足另一个条件时，才会被查询出来。查询条件2、使用条件查询语法格式：条件1{AND|OR|XOR}条件2;逻辑运算查询条件2、使用条件查询动手操作：从学生表student中查询年龄大于等于21岁的男学生的学号、姓名逻辑运算查询条件2、使用条件查询动手操作：从教师表teacher中查询男性或者职称为“副教授”的教师工号、姓名范围比较使用BETWEENAND关键字的基本语法格式如下：说明：（1）NOT：可选参数，表示指定范围之外的值。如果字段值不满足指定范围内的值，则这些记录被返回。（2）取值1：表示范围的起始值。（3）取值2：表示范围的终止值。（4）BETWEENEND关键字是用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则返回TRUE这些记录被返回(包含边界值)。使用NOT时则相反。查询条件2、使用条件查询语法格式：表达式[NOT]BETWEEN取值1AND取值2;范围比较使用IN关键字的基本语法格式如下：说明：IN关键字用的最多的是子查询(后面讲解)，也可以用于OR运算。查询条件2、使用条件查询语法格式：WHERE表达式[NOT]IN(子查询|表达式1[,...表达式n]);范围比较查询条件2、使用条件查询动手操作：从教师表teacher中查询工龄为10-20的教师工号、姓名范围比较查询条件2、使用条件查询动手操作：从学生表student中查询班级号为“Soft2101“和“Soft2102”的学生学号、姓名和班级号模式匹配在MySQL中，LIKE

关键字主要用于搜索匹配字段中的指定内容实现模糊查询。说明：（1）NOT：可选参数，字段中的内容与指定的字符串不匹配时满足条件。（2）字符串：指定用来匹配的字符串。“字符串”可以是一个很完整的字符串，也可以包含通配符。查询条件2、使用条件查询语法格式：[NOT]LIKE

'字符串';模式匹配通配符是一种特殊语句，主要用来模糊查询。当不知道真正字符或者懒得输入完整名称时，可以使用通配符来代替一个或多个真正的字符。

LIKE关键字支持百分号“%”和下划线“_”通配符。查询条件2、使用条件查询注意：（1）匹配的字符串必须加单引号或双引号。（2）如果查询内容中包含通配符，可以使用“\”转义符，也可以使用ESCAPE指定转义符。模式匹配查询条件2、使用条件查询动手操作：从学生表student中查询姓“周”的学生的学号和姓名范围比较查询条件2、使用条件查询动手操作：从学生表student中查询Soft2102班的姓名为两个字的学生的学号和姓名范围比较查询条件2、使用条件查询动手操作：从学生表student中查询住“长沙”学生的学号和姓名空值比较MySQL提供了

ISNULL

关键字，用来判断字段的值是否为空值（NULL）。如果字段的值是空值，则满足查询条件，该记录将被查询出来。如果字段的值不是空值，则不满足查询条件。查询条件2、使用条件查询注意：（1）“NOT”是可选参数，表示字段值不是空值时满足条件语法格式：表达式IS[NOT]NULL;模式匹配查询条件2、使用条件查询动手操作：从学生表student中查询没有填写地址信息的学生学号和姓名使用ORDERBY子句可以让查询结果集按照一定的顺序排序。说明：（1）可以按照多个字段排序，之间用逗号隔开。（2）ASC|DESC：ASC表示升序排序，DESC表示降序排序，系统默认为ASC。（3）不同数据类型，升序的含义如下：

1）数字类型：小值在前面显示；2）日期类型：早的日期在前面显示；3）字符类型：依据字母顺序显示，a在前，z最后；4）空值：显示在最后。ORDERBY子句3、查询排序和限制查询结果条数语法格式：ORDERBY字段名[ASC|DESC],…;ORDERBY子句查询排序动手操作：从学生表student中查询学生的学号、姓名和出生日期，按照出生日期降序排序3、查询排序和限制查询结果条数ORDERBY子句查询排序动手操作：从课程表lesson中查询“必修课”课程信息，按照学分credit升序排序3、查询排序和限制查询结果条数ORDERBY子句查询排序动手操作：从教师表teacher中查询教师信息，按照职称升序排序，教师编号降序排序3、查询排序和限制查询结果条数LIMIT子句是SELECT语句的最后一个子句，用于限制SELECT语句返回的行数。SELECT返回所有匹配的行，有可能是表中所有的行，如仅仅需要返回第一行或者前几行，使用LIMIT关键字。说明：（1）初始位置：表示从哪条记录开始显示。第一条记录的位置是0，第二条记录的位置是1，后面的记录依次类推，系统默认为0。（2）记录数：表示显示记录的条数。LIMIT子句3、查询排序和限制查询结果条数语法格式：LIMIT[初始位置，]记录数;LIMIT子句限制查询结果条数动手操作：从学生表student中查询学生的学号、姓名，显示前5条数据3、查询排序和限制查询结果条数LIMIT子句限制查询结果条数动手操作：从学生表student中查询学生的学号、姓名，显示第3-5共3条数据3、查询排序和限制查询结果条数LIMIT子句限制查询结果条数动手操作：从成绩表studing中查询Le0005课程成绩排在前三名的学生学号、课程号和成绩3、查询排序和限制查询结果条数聚合函数在一个行的集合（一组行）上进行操作，对每个组给一个结果。聚合函数通常与GROUPBY子句一起使用。如果没有GROUPBY子句，聚合函数把所有的行集合当做一个组，产生一行结果；否则按照分组，每个组产生一行结果。聚合函数4、使用分组和汇总查询数据说明：（1）表达式：可以是列名、常量、函数或表达式。（2）默认情况下，函数忽略列值为NULL的行，不参与计算。（3）*：返回检索到的所有行的总数据，包含NULL值。（4）ALL|DISTINCT：ALL表示对所有值进行运算，DISTINCT表示去除重复值，默认为ALL。聚合函数4、使用分组和汇总查询数据语法格式：COUNT(*|{[ALL|DISTINCT]表达式})

MAX/MIN/SUM/AVG({[ALL|DISTINCT]表达式})COUNT()聚合函数动手操作：从学生表student中统计学生人数4、使用分组和汇总查询数据MAX()、MIN()、AVG()、SUM()聚合函数动手操作：从成绩表studing中统计Le0005课程考试成绩的最高分、最低分、平均成绩和总分4、使用分组和汇总查询数据GROUPBY

关键字可以根据一个或多个字段对查询结果进行分组,在分组的列上我们可以使用COUNT、MAX、MIN、SUM和AVG等函数。说明：（1）字段名：表示需要分组的字段名称，多个字段时用逗号隔开。（2）分组除了使用字段名外，还可以是表达式。GROUPBY子句4、使用分组和汇总查询数据语法格式：GROUPBY

<字段名>

按列名分组，单独使用GROUPBY子句动手操作：从课程表lesson中统计课程类型4、使用分组和汇总查询数据GROUP_CONCAT()GROUPBY子句动手操作：按照课程类型分组，显示出每个分组的课程名称4、使用分组和汇总查询数据与COUNT()一起使用GROUPBY子句动手操作：从课程表lesson中统计各课程类型的课程数4、使用分组和汇总查询数据按多列分组GROUPBY子句动手操作：从学生表student中统计各班级的男女生人数4、使用分组和汇总查询数据与WITHROLLUP一起使用GROUPBY子句动手操作：从学生表student中统计软件班的男女生人数、软件各班人数和软件班人数，显示班级号、性别、姓名和人数4、使用分组和汇总查询数据可以使用

HAVING

关键字对分组后的数据进行过滤，HAVING

子句不能单独使用，必须跟在GROUPBY子句后面使用。说明：（1）HAVING关键字和WHERE关键字都可以用来过滤数据，且HAVING支持WHERE关键字中所有的操作符和语法。（2）因为WHERE子句比GROUPBY先执行，而聚合函数必须在分完组之后才执行，且分完组后必须使用HAVING子句进行结果集的过滤。HAVING子句4、使用分组和汇总查询数据语法格式：HAVING<查询条件>

比较运算HAVING子句动手操作：从成绩表studing中统计每个学生的平均成绩，显示平均成绩不低于90的学号、平均成绩4、使用分组和汇总查询数据比较Having与Where子句HAVING子句动手操作：从成绩表studing中统计Le0003和Le0005课程的最高、最低成绩4、使用分组和汇总查询数据Having与Where子句一起使用HAVING子句动手操作：从学生表studing中统计湖南地区班级人数大于等于2人的班级号、人数4、使用分组和汇总查询数据连接就是将一张表中的行按照某个条件（连接条件）和另一张表中的行连接起来形成一个新行的过程。在MySQL中，根据连接条件所使用的操作符，主要有相等连接（使用等号操作符）和不等连接（不使用等号操作符）；根据连接查询返回的结果，多表查询主要有交叉连接、内连接和外连接。连接概述5、使用连接查询进行多表数据的检索交叉连接(CROSSJOIN)没有连接条件，表与表间的所有行连接。结果集中的总行数就是两张表中总行数的乘积(笛卡尔积)，一般用来返回连接表的笛卡尔积。说明：（1）字段名：需要查询的字段名称。（2）表1、表2：需要交叉连接的表名。（3）WHERE子句：用来设置交叉连接的查询条件。（4）多个表交叉连接时，在FROM后连续使用CROSSJOIN或,即可。交叉连接5、使用连接查询进行多表数据的检索语法格式：SELECT<字段名>FROM<表1>CROSSJOIN<表2>[WHERE子句]

或

SELECT<字段名>FROM<表1>,<表2>[WHERE子句]CROSSJOIN交叉连接动手操作：使用交叉连接查询班级表class和部门表department。5、使用连接查询进行多表数据的检索内连接(INNERJOIN)只返回连接表中所有满足连接条件的行。主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接。说明：（1）字段名：需要查询的字段名称。（2）表1、表2：需要内连接的表名，可以给表设置别名。（3）INNERJOIN：内连接中可以省略INNER关键字，只用关键字JOIN。（4）ON子句：用来设置内连接的连接条件。（5）USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等。（6）多个表内连接时，在FROM后连续使用INNERJOIN或JOIN即可。内连接5、使用连接查询进行多表数据的检索语法格式：SELECT<字段名>FROM<表1>INNERJOIN<表2>[ON子句|USING(字段名)）]

INNERJOIN内连接动手操作：使用内连接查询班级表class和部门表department，显示班级号、班级名称、班主任和所属部门编号、部门名称5、使用连接查询进行多表数据的检索INNERJOIN连接三张表内连接动手操作：查询“信息工程系”的学生的学号、姓名、性别、所在班级名称5、使用连接查询进行多表数据的检索INNERJOIN与GROUPBY一起使用内连接动手操作：查询每门课程的课程号、任课教师姓名以及选课人数5、使用连接查询进行多表数据的检索INNERJOIN实现自连接内连接动手操作：查询与“李林”同班的学生的姓名5、使用连接查询进行多表数据的检索内连接返回查询结果集中仅包含满足连接条件和查询条件的行，而采用外连接时不仅返回满足条件的结果，还会包含左表(左外连接)、右表(右外连接)或两个表(全外连接)中的所有数据行。在MySQL数据库中外连接分两类：左外连接、右外连接。MySQL不支持全外连接。外连接5、使用连接查询进行多表数据的检索左外连接LEFTJOIN左外连接又称为左连接，查询的结果集包含左表中的所有行，如果左表中的某行在右表中没有匹配的行，则在相关联的结果集中右表的所有选择列均为空值。说明：（1）字段名：需要查询的字段名称。（2）表1、表2：需要左连接的表名。（3）LEFTOUTERJOIN：左连接中可以省略OUTER关键字，只使用关键字LEFTJOIN。（4）ON子句：用来设置左连接的连接条件，不能省略。外连接5、使用连接查询进行多表数据的检索语法格式：SELECT<字段名>FROM<表1>LEFTOUTERJOIN<表2><ON子句>

左外连接LEFTJOIN外连接动手操作：查询所有学生的成绩情况5、使用连接查询进行多表数据的检索右外连接RIGHTJOIN右外连接又称为右连接，查询的结果集包含右表中的所有行，如果右表中的某行在左表中没有匹配的行，则在相关联的结果集中左表的所有选择列均为空值。说明：（1）字段名：需要查询的字段名称。（2）表1、表2：需要右连接的表名。（3）RIGHTOUTERJOIN：右连接中可以省略OUTER关键字，只使用关键字RIGHTJOIN。（4）ON子句：用来设置右连接的连接条件，不能省略。外连接5、使用连接查询进行多表数据的检索语法格式：SELECT<字段名>FROM<表1>RIGHTOUTERJOIN<表2><ON子句>

右外连接RIGHTJOIN外连接动手操作：查询所有课程的选修情况5、使用连接查询进行多表数据的检索全外连接全外连接，查询结果除了包含满足连接条件的记录外，还包含两个表中不满足条件的记录。当某行在另一个表中没有匹配的行时，则另一个表中的选择列均为空值。MySQL不支持全外连接，全外连接可以通过UNION组合实现。外连接5、使用连接查询进行多表数据的检索UNION运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自UNION中参与查询的提取行。说明：（1）SELECT...：SELECT列表必须在数量和对应列的数据类型上保持一致。（2）ALL|DISTINCT：UNIONALL不去掉结果集中重复的行，UNIONDISTINCT会去掉结果集中的行，默认为UNIONDISTINCT。组合(UNION)5、使用连接查询进行多表数据的检索语法格式：SELECT...

UNION[ALL|DISTINCT]

SELECT...UNION组合(UNION)动手操作：查询住“长沙”或者学号为“20211002”、“20211004”的学生的学号，姓名和性别5、使用连接查询进行多表数据的检索UNION实现全外连接组合(UNION)动手操作：查询学生表和班级表的所有数据5、使用连接查询进行多表数据的检索子查询是MySQL中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在SELECT、UPDATE和DELETE语句中使用，而且可以进行多层嵌套。单值和多值查询（1）单值查询如果子查询返回的结果是一个单一值，称为单值查询。单值查询可以直接使用关系运算符将内查询和外查询连接起来。（2）多值查询如果子查询返回的结果为一组值，称为多值查询。多值查询需要在子查询前面使用ANY、ALL、IN、NOTIN等运算符。子查询概述6、使用子查询进行数据检索、插入、更新和删除相关与非相关子查询（1）非相关子查询非相关子查询的执行过程是从内层向外层处理，即先处理最内层的子查询，但是查询的结果是不会显示出来的，而是传递给外层作为外层的条件，再执行外部查询，最后显示出查询结果。（2）相关子查询相关子查询的执行过程是子查询为外部查询的每一行执行一次，外部查询将子查询引用的外部字段的值传给子查询，进行子查询操作；外部查询根据子查询得到的结果或结果集返回满足条件的结果行。外部查询的每一行都做相同处理。外部查询每执行一行，内部查询都要从头执行到尾。其类似于编程语言的嵌套循环。子查询概述6、使用子查询进行数据检索、插入、更新和删除在SELECT语句中，子查询可以被嵌套在SELECT语句的列、表和查询条件中，即SELECT子句，FROM子句、