数据基础及应用- 1

第2章MySQL数据库系统基础第1章数据库系统概述第3章MySQL数据库操作第5章使用SQL语句查询数据第4章MySQL数据表第6章视图和索引的使用第8章数据库的安全性管理第7章存储过程和触发器第9章数据库的备份与恢复目录目标导航重点和难点1．SELECT查询语句格式;2．分组和汇总查询；3．连接查询；4．子查询。知识目标1.掌握SELECT查询语句格式；2.掌握条件语句的格式；3.掌握分组和汇总语句的格式；4.掌握连接查询语句的格式；5.掌握子查询和联合查询语句的格式。技能目标1.具备单表数据查询的能力;2.具备多表连接查询的能力;3.具备使用子查询进行数据查询、插入、更新和删除的能力。查询是数据库系统中最常用且最重要的功能之一,它是用户快速、方便地使用数据库中数据的有效方法。在MySQL中,查询操作通过SELECT语句实现。该语句能够从服务器的数据库中检索符合用户要求的数据,并将结果以结果集的形式返回给客户端。本章主要介绍SELECT语句查询数据的具体方法及其简单应用。第5章使用SQL语句查询数据SELECT语句是从数据库中获取信息的一个基本语句。该语句可以实现从一个表或者多个表中查询信息,并将结果以二维表的形式显示,称为结果集。SELECT[ALL|DISTINCT]<选择列表>[FROM]{<表或视图名>}[,…n][WHERE]<搜索条件> [GROUPBY]{<分组表达式>}[,…n] [HAVING]<分组条件> [ORDERBY]{<字段名[ASC|DESC]>}[,…n][LIMIT{[offset,]>rowcount|row_countOFFSEToffset}]第5章使用SQL语句查询数据5.1简单查询说明：（1）用[]括起来的是可选项，SELECT是必需的。（2）选择列表指定了要返回的列。（3）WHERE子句指定限制查询的条件。（4）在搜索条件中，可以使用比较运算符、逻辑运算符等来限制返回的行数。（5）FROM子句指定了所涉及的字段所属的表。（6）DISTINCT选项从结果集中消除了重复的行。（7）GROUPBY子句是对结果集进行分组。（8）HAVING子句是在分组的时候对字段或表达式指定搜索条件。（9）ORDERBY子句对结果集按某种条件进行排序，ASC升序（默认），DESC降序（10）LIMIT限定了要返回的行数。第5章使用SQL语句查询数据5.1简单查询5.1.1带投影的查询1.选择所有列查询全部列时可以使用“*”,显示结果的顺序与表中原来的顺序一致,也可以依次列出所有字段,字段名称之间用逗号分隔。例1查询学生的基本信息(1)启动NavicatforMySQL平台,打开连接“MySQL90”,打开数据库“gradem”,单击工具栏上的“新建查询”按钮,打开一个空白的SQL脚本文件窗口,连接名称选择“MySQL90”,数据库名称选择“gradem”,输入以下SQL语句:SELECT*FROMstudent;(2)单击“运行”按钮执行SQL语句。(3)单击文件菜单,执行“另存为外部文件”命令,选择保存位置,输入文件名,保存该SQL语句,文件扩展名为“.sql”。第5章使用SQL语句查询数据5.1简单查询5.1.1带投影的查询2.选择部分列例2查询学生的学号、姓名、家庭住址和电话号码SELECTsno,sname,saddress,sphoneFROMstudent;3.给列加别名若需要改变结果集中列的名称,或为组合或计算得出的列指定一个名称时,可以为该列设置别名。给列指定别名的方法有如下几种:(1)列名或表达式AS别名(2)列名或表达式

别名(用空格分隔)(3)别名=列名或表达式例3查询学生的学号、姓名、年龄和电话号码。SELECTsnoAS,snameAS,YEAR(NOW())-YEAR(sbirthday)ASage,sphoneASFROMstudent;第5章使用SQL语句查询数据5.1简单查询5.1.1带投影的查询4.去掉重复值例4查询学生所属的班级。本例从学生表中查询班级号。由于一个班级包含多名学生,查询结果中可能会出现重复的班级号。可以使用DISTINCT关键字从返回结果中去掉重复行。SELECTDISTINCTclassnoFROMstudent;第5章使用SQL语句查询数据5.1简单查询5.1.2带条件的查询在实际查询中,常常需要根据特定的条件对数据进行筛选,这时就需要使用带条件查询,也就是WHERE子句。WHERE子句能够限制查询结果的范围,其查询条件或限定条件中通常会用到比较运算符、空值运算符、模式匹配运算符(模糊查询运算符)、范围运算符、集合运算符及逻辑运算符,具体见表5-1。表5-1运算符第5章使用SQL语句查询数据5.1简单查询运算符类型运算符说明比较运算符=、>、<、>=、<=、<>、!=;比较两个表达式的大小空值判断符ISNULL、ISNOTNULL判断是否为空模糊匹配符LIKE，NOTLIKE判断是否与指定的字符串匹配逻辑运算符OR、AND、NOT用于多个条件的连接范围运算符BETWEENAND、NOTBETWEENAND判断是否在某个范围内集合运算符IN、NOTIN判断是否在列表中5.1.2带条件的查询在实际查询中,常常需要根据特定的条件对数据进行筛选,这时就需要使用带条件查询,也就是WHERE子句。WHERE子句能够限制查询结果的范围,其查询条件或限定条件中通常会用到比较运算符、空值运算符、模式匹配运算符(模糊查询运算符)、范围运算符、集合运算符及逻辑运算符,具体见表5-1。表5-1运算符第5章使用SQL语句查询数据5.1简单查询运算符类型运算符说明比较运算符=、>、<、>=、<=、<>、!=;比较两个表达式的大小空值判断符ISNULL、ISNOTNULL判断是否为空模糊匹配符LIKE，NOTLIKE判断是否与指定的字符串匹配逻辑运算符OR、AND、NOT用于多个条件的连接范围运算符BETWEENAND、NOTBETWEENAND判断是否在某个范围内集合运算符IN、NOTIN判断是否在列表中5.1.2带条件的查询1.使用比较运算符例5查询所有女生的学号和姓名。SELECTsno,snameFROMstudentWHEREssex='女';例6查询所有年龄小于25岁的学生的学号和姓名及年龄。SELECTsno,sname,YEAR(NOW())-YEAR(sbirthday)ASageFROMstudentWHEREYEAR(NOW())-YEAR(sbirthday)<25第5章使用SQL语句查询数据5.1简单查询5.1.2带条件的查询2.使用空值判断符空值在数据库中有特殊的含义,它不等同于0或空格,表示一个不确定的值。因此在空值的判断上不能用等号或不等号,而应使用ISNULL(为空)或ISNOTNULL(不为空)。例7查询电话号码为空的学生的学号和姓名。SELECTsno,snameFROMstudentWHEREsphoneISNULL;第5章使用SQL语句查询数据5.1简单查询5.1.2带条件的查询3.使用模糊查询运算符在实际查询中,有时查询条件不是具体的甚至是模糊的,这时需要在WHERE子句中使用LIKE关键字实现模糊查询。LIKE关键字用于查询与指定的字符串表达式相匹配的数据。LIKE后面的表达式必须用单引号括起来,在进行模糊匹配时要使用通配符。常用的通配符见表5-2。第5章使用SQL语句查询数据5.1简单查询表5-1通配符及含义通配符含

义%任意多个字符（包括0个）__任意一个字符例如:LIKE'张%'表示以“张”开头的任意字符串。LIKE'%数据库%'表示包含“数据库”的任意字符串。LIKE'张_'表示以“张”开头且后面是一个字符的字符串。5.1.2带条件的查询3.使用模糊查询运算符例8查询所有姓“张”的学生的学号和姓名。SELECTsno,snameFROMstudentWHEREsnameLIKE'张%';例9查询姓“张”且名字为两个字的学生的学号和姓名。SELECTsno,snameFROMstudentWHEREsnameLIKE'张_';第5章使用SQL语句查询数据5.1简单查询5.1.2带条件的查询3.使用模糊查询运算符例10查询不姓“张”的学生的学号和姓名SELECTsno,snameFROMstudentWHEREsnameNOTLIKE'张%';例11查询电话号码中含有“_”开头的学生的学号、姓名和电话SELECTsno,sname,sphoneFROMstudentWHEREsphoneLIKE'%$-%'ESCAPE'$';第5章使用SQL语句查询数据5.1简单查询5.1.2带条件的查询4.使用逻辑运算符连接多个条件当有多个条件时,需要在WHERE子句中使用逻辑运算符将多个条件连接起来。常用的逻辑运算符有AND、OR和NOT。AND运算符表示逻辑“与”,只有连接的两个条件都为真时结果才为真;OR运算符表示逻辑“或”,连接的两个条件只要有一个为真结果就为真;NOT运算符表示逻辑“非”,表示对后面的条件取反。例12查询家住大连的女生的学号、姓名及家庭住址。SELECTsno,sname,saddressFROMstudentWHEREsaddressLIKE'%大连市%'ANDssex='女';例13查询“20160101”和“20160102”班的学生的学号和姓名SELECTsno,snameFROMstudentWHEREclassno='20160101'ORclassno='20160102';第5章使用SQL语句查询数据5.1简单查询5.1.2带条件的查询5.使用范围运算符进行查询查询某个范围内的数据可以使用BETWEEN…AND,该语句一般用于比较数值类型的数据,BETWEEN后面是范围的下限,AND后面是范围的上限,下限值不能大于上限值,查询范围包括边界。例14查询年龄在24~26岁的学生的学号和姓名。SELECTsno,snameFROMstudentWHEREYEAR(NOW())-YEAR(sbirthday)BETWEEN24AND26;第5章使用SQL语句查询数据5.1简单查询5.1.2带条件的查询6.使用列表运算符进行查询在WHERE子句中,使用IN搜索条件检索指定列表值的匹配行,多个值之间用逗号分开,等价于OR连接的多个条件。NOTIN表示不在指定列表中。例15查询20160101、20160102和20160202班的学生的学号和姓名。SELECTsno,snameFROMstudentWHEREclassnoIN('20160101','20160102','20160202');或者SELECTsno,snameFROMstudentWHEREclassno='20160101'ORclassno='20160102'ORclassno='20160202';第5章使用SQL语句查询数据5.1简单查询5.1.3聚合函数查询在实际应用中检索并不是简单地查询,而是对数据表中的数据进行统计工作,例如学生处要统计全校男、女生人数,这需要进行分组汇总。分组需使用groupby子句,在进行汇总时需使用聚合函数。聚合函数能够基于列进行计算,并返回单个数值,常用的聚合函数有SUM、AVG、MAX、MIN、COUNT。聚合函数不能用于WHERE子句中。SUM和AVG后的列或表达式的值必须为数值型;除COUNT()函数外,若没有满足WHERE子句的行,则所有聚合函数都将返回一个空值,而COUNT返回0。常用聚合函数见表5-3。第5章使用SQL语句查询数据5.1简单查询表5-3常用的聚合函数聚合函数名说明SUM()计算列值或表达式中所有值的总和。AVG()计算列值或表达式的平均值MAX()计算列值或表达式的最大值。MIN()计算列值或表达式的最小值。COUNT()统计记录个数。5.1.3聚合函数查询例16查询选修“001”课程的成绩的最高分、最低分和平均分。SELECTMAX(grade)最高分,MIN(grade)最低分,AVG(grade)平均分FROMscWHEREcno='001';例17查询学生总人数。SELECTCOUNT(*)总人数FROMstudent;例18查询学号为“2016010101”的学生的总成绩。SELECTSUM(grade)总成绩FROMscWHEREsno='2016010101';第5章使用SQL语句查询数据5.1简单查询5.1.4分组和汇总查询例19查询每名学生的总成绩,结果显示学号和总成绩。SELECTsno学号,SUM(grade)AS总成绩FROMscGROUPBYsno；例20查询每名学生的平均分,结果显示学号和平均分。SELECTsno学号,AVG(grade)AS平均分FROMscGROUPBYsno;例21查询每门课程的最高成绩和最低成绩,结果显示课程号、最高成绩和最低成绩。SELECTcno课程号,MAX(grade)AS最高成绩,MIN(grade)AS最低成绩FROMscGROUPBYcno;例22查询男、女生人数。SELECTssex性别,COUNT(*)AS人数FROMstudentGROUPBYssex;例23查询平均分大于85分的课程号和平均分。SELECTcno课程号,AVG(grade)AS平均分FROMscGROUPBYcnoHAVINGAVG(grade)>85;第5章使用SQL语句查询数据5.1简单查询5.1.5对查询结果排序ORDERBY用于对查询结果进行排序。列名后加排序方式:默认升序排列(ASC),DESC表示降序。ORDERBY后面可以加多个列,列之间用逗号分开,但排序方式必须分别指定,首先按第一列排序,第一列相同再按第二列进行排序。ORDERBY后可以是列名、函数或表达式。在使用ORDERBY进行排序时空值被视为最小值。例24查询学生的学号、姓名和出生日期,结果按出生日期降序排列。SELECTsno,sname,sbirthdayFROMstudentORDERBYsbirthdayDESC;例25查询年龄最小的前两名学生的学号、姓名和出生日期。LIMIT语句可以用于限制查询的数量,后面可以有1个或2个数字参数,参数必须为整数常量。只定义1个参数表示返回的最大记录条数。定义2个参数,第1个参数指定返回记录行的偏移量,第2个参数指定返回记录行的最大数目,初始记录行的偏移量为0而不是1。例如:LIMIT2,5表示从表中第3条记录开始的5条记录,也可以写成LIMIT5OFFSET2。SELECTsno,sname,sbirthdayFROMstudentORDERBYsbirthdayDESCLIMIT2;第5章使用SQL语句查询数据5.1简单查询在关系型数据库管理系统中为了减少数据的冗余以及避免各种操作的异常，经常把一个实体的所有信息存放在一个表中，把相关数据分散到不同的表中。在检索数据时通过连接操作可以查询出存放在不同表中的实体的信息。一般情况下，SQL语言的SELECT语句通过在WHERE条件中指定连接属性的匹配来实现连接操作，每两个参与连接的表需要指定一个连接条件，连接查询的结果为一个表，这使得用户能将连接结果再与其它表进行连接，从而实现多表之间的连接。连接查询主要包括内连接、外连接、交叉连接。第5章使用SQL语句查询数据5.2多表连接查询5.2.1内连接内连接是最常用的一种连接形式，两个表的内连接查询是指从两个表中的相关字段中提取信息作为查询条件，如果满足条件就从两个表中选择相应信息置于查询结果集中。内连接的语法格式如下（INNER可以省略）：SELECT列名列表FROM表1[INNER]JOIN表2ON连接条件表达式或SELECT列名列表FROM表1,表2WHERE连接条件表达式第5章使用SQL语句查询数据5.2多表连接查询5.2.1内连接例26查询学生的学号、姓名和辅导员。SELECTsno,sname,headerFROMstudent,classWHEREstudent.classno=student.classno;例27查询选修“计算机应用基础”课程的学生的学号、姓名和成绩。SELECTstudent.sno,sname,gradeFROMstudentJOINscONstudent.sno=sc.snoJOINcourseONo=oWHEREcname='计算机应用基础';第5章使用SQL语句查询数据5.2多表连接查询5.2.2自连接在某些查询中,虽然查询的内容只与一个表有关,但需要对表中不同的记录进行对比,这时就要在同一个表上进行自连接,因此必须给两个副本定义不同的别名。在生成连接结果时,表中的每一行都会和自身进行连接,生成重复的记录,可以使用WHERE子句来消除这些重复的记录。例28查询与“王丽”在同一班级的学生的姓名。SELECTa.snameFROMstudenta,studentbWHEREa.classno=b.classnoandb.sname='王丽'ANDa.sname!='王丽';第5章使用SQL语句查询数据5.2多表连接查询5.2.3外连接内连接返回查询结果集中仅包含满足连接条件和查询条件的行,而采用外连接时,不仅会返回满足条件的结果,还会包含左表(左外连接)、右表(右外连接)中的所有数据行。(1)左外连接:查询的结果集包含左表中的所有行,如果左表中的某行在右表没有相匹配行,则在相关联的结果集行中右表的所有选择列均为空值。语法格式如下:SELECT列名列表FROM表1LEFT[OUTTER]JOIN表2ON连接条件表达式(2)右外连接:查询的结果集包含右表中的所有行,如果右表中的某行在左表没有相匹配行,则在相关联的结果集行中左表的所有选择列均为空值。语法格式如下:SELECT列名列表FROM表1RIGHT[OUTTER]JOIN表2ON连接条件表达式第5章使用SQL语句查询数据5.2多表连接查询5.2.3外连接例29使用左外连接查询所有学生的成绩情况,结果显示学号、姓名、课程号和成绩。SELECTstudent.sno,sname,cno,gradeFROMstudentLEFTJOINscONstudent.sno=sc.sno;例30使用右外连接查询所有课程的选修情况。SELECTsc.sno,o,sc.grade,ameFROMscRIGHTJOINcourseONo=o;第5章使用SQL语句查询数据5.2多表连接查询5.2.4交叉连接交叉连接就是将连接的两个表的所有行进行组合,即将左表中的每一行与右表中的所有行一一组合。结果集的列数为两个表列数的和,行数为两个表行数的乘积。语法格式如下:SELECT列名列表FROM表1CROSSJOIN表2例31使用交叉连接查询学生表和成绩表。SELECT*FROMstudentCROSSJOINsc;第5章使用SQL语句查询数据5.2多表连接查询5.3.1子查询子查询是指包含在某个SELECT、INSERT、UPDATE或DELETE语句中的SELECT查询。部分子查询和连接查询可以相互替代,使用子查询也可以替代表达式。通过子查询可以把一个复杂的查询分解成一系列的步骤,解决复杂的查询问题。子查询也称为嵌套查询。子查询的分类方法有多种:(1)按查询执行的过程可分为非相关子查询和相关子查询两种。①非相关子查询非相关子查询的执行过程是从内层向外层处理,即先处理最内层的子查询,但是查询的结果不会被显示出来,而是传递给外层,作为外层的条件,再执行外部查询,最后显示出查询结果。第5章使用SQL语句查询数据5.3子查询和联合查询5.3.1子查询若子查询返回的结果是一个单一值,则称为单值子查询。单值子查询可以直接使用关系运算符连接内查询和外查询。若子查询返回的结果为一组值,则称为多值子查询。多值子查询需要在子查询前使用ANY、ALL、IN、NOTIN等运算符。ANY:将一个表达式的值与子查询返回的一组值中的每一个值进行比较。只要有一个运算结果为True,则ANY测试返回True;如果每次比较结果都为False,则ANY测试返回False。ALL:将一个表达式的值与子查询返回的一组值中的每一个值进行比较。若每次比较的结果都为True,则ALL测试返回True;只要有一次比较结果为False,则ALL测试返回False。第5章使用SQL语句查询数据5.3子查询和联合查询5.3.1子查询②相关子查询相关子查询的执行过程:子查询为外部查询的每一行执行一次,外部查询将子查询引用的外部字段的值传给子查询,进行子查询操作;外部查询根据子查询得到的结果或结果集返回满足条件的结果行。外部查询的每一行都做相同的处理。外部查询每取一行,都会把该行的相关列值传给子查询,子查询再基于这一行重新执行一次,类似于编程语言的嵌套循环。一般情况下,包含子查询的查询语句可以写成连接查询的方式。第5章使用SQL语句查询数据5.3子查询和联合查询5.3.1子查询（2)按照返回结果可分为表子查询、行子查询、列子查询、标量子查询。①表子查询:返回的结果集由多行数据组成,使用表子查询时必须为其设置别名,通常出现在父查询的FROM子句中。语法格式如下:SELECT列名列表FROM(SELECT列名列表FROM表名)AS表别名②行子查询:返回结果集由单行数据组成,,单行数据里可以包含多列,通常用于与父查询WHERE子句中的行构造器进行比较。语法格式如下:SELECT列名列表FROM表名WHERE(列名1,列名2,…)=(SELECT列名1,列名2,…FROM表名

[WHERE条件])③列子查询:返回的结果集由多行单列数据组成,常与IN、ANY、ALL等集合操作符配合使用。语法格式如下:SELECT列名列表FROM表名WHERE列名操作符(SELECT列名FROM表名

[WHERE条件])④标量子查询:返回的结果集是一行一列的单个值。任何标量子查询必然是行子查询和列子查询,但反过来不成立。行子查询和列子查询也都可以看作特殊的表子查询,反之亦不成立。第5章使用SQL语句查询数据5.3子查询和联合查询5.3.1子查询例32查询选修“001”号课程且成绩低于该门课程平均成绩的学生学号。SELECTsnoFROMscWHEREcno='001'ANDgrade<(SELECTAVG(grade)FROMscWHEREcno='001');例33查询选修了“计算机基础”课程的学生的学号及成绩。SELECTsno,gradeFROMscWHEREcno=(SELECTcnoFROMcourseWHEREcname='计算机基础');第5章使用SQL语句查询数据5.3子查询和联合查询5.3.1子查询例34查询年龄比20160101班所有学生年龄都大的学生的学号和姓名。SELECTsno,snameFROMstudentWHEREYEAR(NOW())-YEAR(sbirthday)>ALL(SELECTYEAR(NOW())-YEAR(sbirthday)FROMstudentWHEREclassno='20160101');例35

查询所有选修“001”号课程的学生的学号和姓名。SELECTsno,snameFROMstudentWHEREsnoIN(SELECTsnoFROMscWHEREcno='001')第5章使用SQL语句查询数据5.3子查询和联合查询5.3.1子查询例36查询没有选修“001”号课程的学生的学号和姓名。SELECTsno,snameFROMstudentWHEREsnoNOTIN(SELECTsnoFROMscWHEREcno='001')例37查询成绩高于其选修课程平均分的学生的学号和课程号。SELECTsno,cnoFROMscaWHEREgrade>(SELECTAVG(grade)FROMscbWHEREo=o);第5章使用SQL语句查询数据5.3子查询和联合查询5.3.1子查询例38查询选修了全部课程的学生姓名。SELECTsnameFROMstudentWHERENOTEXISTS(SELECT*FROMcourseWHERENOTEXISTS(SELECT*FROMscWHEREo=oANDstudent.sno=sc.sno));EXISTS谓词用于判断子查询结果是否为空。若子查询结果不为空,则返回TRUE,否则返回FALSE。EXISTS还可与NOT结合使用。在使用EXISTS查询时一般只关心子查询是否有结果,而不关心查询出的结果,因此子查询的SELECT语句后一般用“*”。第5章使用SQL语句查询数据5.3子查询和联合查询5.3.1子查询例39查询所有女生的姓名、学号,以及与“2016010101”号学生的年龄差距。SELECTsno学号,sname姓名,YEAR(sbirthday)-YEAR((SELECTsbirthdayFROMstudentWHEREsno='2016010101'))AS年龄差距FROMstudentWHEREssex='女';例40查询与“2016010101”号学生性别相同、出生日期相同的学生的学号和姓名。SELECTsno学号,sname姓名FROMstudentWHER'E(ssex,sbi'rthday)=(SELECTssex,sbirthdayFROMstudentWHEREsno='2016010101')ANDsno<>2016010101;第5章使用SQL语句查询数据5.3子查询和联合查询5.3.1子查询例41将学生“白沧铭”的成绩全部提高5分。UPDATEscSETgrade=grade+5''WHEREsno=(SELEC