《数据库原理及应用教程-MySQL8.0》课件 第6章 数据库查询

数据库原理与应用教程-MySQL8.0第6章

数据库查询第6章

数据库查询所谓查询，就是检索数据库内数据的特定请求。数据库接受用SQL语言编写的查询，使用查询可以按照不同的方式查看和分析数据。查询设计是数据库应用程序开发的重要组成部分，因为在设计好数据库并用数据进行填充后，最常见地就是通过查询来使用数据。6.1SELECT查询语法基本语法格式：SELECT{select_list}FROM{table_list|view_list}[WHERE{search_conditions}][GROUPBY{group_by_list}][HAVING{search_conditions}][ORDERBY{order_list[ASC|DESC]}][LIMIT{n[,m]}];6.2简单查询简单查询包括投影查询、选择查询及采用聚合函数的简单计算查询。6.2.1投影查询通过SELECT语句的select_list项组成结果表的列。投影查询格式：SELECT[ALL|DISTINCT]{*|{{column_name|expression}[[AS]column_alias]}[,…n]}6.2.1投影查询1.选择一个表中指定的列使用SELECT语句选择一个表中的某些列，各列名之间要以逗号分隔，每个语句的结尾加分号。【例6-1】查询teaching数据库中学生的姓名、性别和专业。USEteaching;SELECTsname,ssex,majorFROMstudent;6.2.1投影查询【例6-2】查询course表的所有记录。USEteaching;SELECT*FROMcourse;用“*”表示表中所有的列，按用户创建表时声明列的顺序来显示所有的列。【例6-3】查询student表的专业名称，滤掉重复行。USEteaching;SELECTDISTINCTmajorFROMstudent;

DISTINCT关键字可以过滤掉查询结果中的重复行。6.2.1投影查询2.改变查询结果中的显示标题SELECT语句中可以根据实际需要对查询数据的列标题进行改变，或者为没有标题的列加上临时的标题。常用的方式：（1）用AS关键字来连接列表达式和指定的列名。

（2）在列表达式后面给出列名。6.2.1投影查询【例6-4】查询student表中所有学生的学号、姓名，结果中各列的标题分别指定为汉字学号和姓名。

USEteaching;SELECTsnoAS学号,snameAS姓名FROMstudent;或：USEteaching;SELECTsno学号,sname姓名FROMstudent;注意：列标题别名只在定义的语句中有效，即只是显示标题，对原表中列标题没有任何影响。6.2.1投影查询3.计算列值

在进行数据查询时，经常需要对查询到的数据进行再次计算处理。“计算列”并不存在于表格所存储的数据中，所以没有列名。【例6-5】查询sc表，按150分制计算成绩。USEteaching;SELECTsno,cno,score*1.50AS成绩150分制FROMsc;6.2.2选择查询投影查询是从列的角度进行的查询，一般对行不进行任何过滤（DISTINCT除外）。但是，一般的查询都不是针对表中所有行的查询，只是从表中选出满足指定条件的内容，这就要用到WHERE子句进行选择查询。选择查询的基本语法：SELECTselect_listFROMtable_listWHEREsearch_conditions6.2.2选择查询查询条件谓词比较运算符=,>,<,>=,<=,!=,<>确定范围BETWEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件AND，OR，NOT6.2.2选择查询1.使用关系表达式【例6-6】查询sc表中成绩大于等于60的学生的学号、课程号和成绩。USEteaching;SELECT*FROMscWHEREscore>=60;6.2.2选择查询2.使用逻辑表达式

【例6-7】查询student表中计算机科学与技术专业的男生的信息。USEteaching;SELECT*FROMstudentWHEREmajor='计算机科学与技术'ANDssex='男';【例6-8】查询计算机科学与技术专业或男生信息。USEteaching;SELECT*FROMstudentWHEREmajor='计算机科学与技术'ORssex='男';6.2.2选择查询3.使用BETWEEN关键字使用BETWEEN关键字可以更方便地限制查询数据的范围。使用BETWEEN表达式进行查询的效果完全可以用含有“>=”和“<=”的逻辑表达式来代替，使用NOTBETWEEN进行查询的效果完全可以用含有“>”和“<”的逻辑表达式来代替。6.2.2选择查询【例6-9】查询sc表中成绩在80到90之间的学生的学号、课程号和成绩。USEteaching;SELECT*FROMscWHEREscoreBETWEEN80AND90;或：USEteaching;SELECT*FROMscWHEREscore>=80ANDscore<=90;

6.2.2选择查询4.使用IN（属于）关键字

同BETWEEN关键字一样，IN的引入也是为了更方便地限制检索数据的范围。语法格式为：表达式[NOT]IN(表达式1,表达式2[,…,表达式n])【例6-11】查询student表中计算机科学与技术和通信工程专业的学生的姓名、学号和专业。USEteaching;SELECTsname,sno,majorFROMstudentWHEREmajorIN('计算机科学与技术','通信工程');6.2.2选择查询5.使用LIKE关键字使用LIKE关键字的查询又叫模糊查询，LIKE关键字搜索与指定模式匹配的字符串、日期或时间值。通配符“%”：为包含零个或多个字符的任意字符串；通配符“_”：为任意单个字符。【例6-12】通配符的示例

LIKE'AB%'返回以“AB”或“ab”开头的任意字符串。LIKE'%abc'返回以“ABC”或“abc”结尾的任意字符串。LIKE'%abc%'返回包含“ABC”或“abc”的任意字符串。LIKE'_12'返回以“12”结尾的三个字符的字符串。6.2.2选择查询【例6-13】查询student表中所有姓张的学生的信息。USEteaching;SELECT*FROMstudentWHEREsnamelike'张%';6.2.2选择查询6.IS[NOT]NULL(是[否]为空)查询在WHERE子句中不能使用比较运算符对空值进行判断，只能使用空值表达式来判断某个列值是否为空值。语法格式：表达式IS[NOT]NULL【例6-14】查询sc表中所有成绩为空值的学生的学号、课程号和成绩。USEteaching;SELECT*FROMscWHEREscoreISNULL;6.2.2选择查询7.复合条件查询在WHERE子句中可以使用逻辑运算符把若干个搜索条件合并起来，组成复杂的复合搜索条件。逻辑运算符优先级从高到低依次是NOT、AND、OR。【例6-15】从student表中查询所有计算机科学与技术和通信工程专业的女生的信息。USEteaching;SELECT*FROMstudentWHERE(major='计算机科学与技术'ORmajor='通信工程')ANDssex='女';

6.2.3聚合函数查询MySQL提供了一系列聚合函数，这些函数把数据描述为一个整体而不是一行行孤立的记录，实现数据集合的汇总或是求平均值等各种运算。函数名功能sum(列名)返回一个数字列的总和avg(列名)对一个数字列计算平均值min(列名)返回一个数字、字符串或日期列的最小值max(列名)返回一个数字、字符串或日期列的最大值count(列名)返回一个列的数据项数count(*)返回找到的行数6.2.3聚合函数查询【例6-16】查询sc表中成绩的平均值，平均值显示列标题为平均成绩。USEteaching;SELECTavg(score)AS平均成绩FROMsc;【例6-17】从student表中查询专业的种类个数（相同的按一种计算）。

USEteaching;SELECTcount(DISTINCTmajor)AS专业种类数FROMstudent;6.2.3聚合函数查询【例6-18】在sc表中查询2021010001号学生的平均成绩和最高成绩。USEteaching;SELECTavg(score)AS平均成绩,max(score)AS最高成绩FROMscWHEREsno='2021010001';6.3分组查询使用聚合函数返回的是所有行数据的统计结果。如果需要按某一列数据的值进行分类，在分类的基础上再进行查询，就要使用GROUPBY子句了。GROUPBY子句的语法：[GROUPBY[ALL]group_by_expression[,…n][HAVINGsearch_conditions]]6.3.1简单分组如果在GROUPBY子句中没有使用HAVING关键字，那么表示这种分组的技术是简单分组技术。【例6-19】查询student表中男生和女生的人数。USEteaching;SELECTssex,count(ssex)人数

FROMstudentGROUPBYssex;6.3.1简单分组注意：使用GROUPBY子句时，选择（SELECT）列表中任何非聚合表达式内的所有列都应该包含在GROUPBY列表中（不能使用列别名），或者说GROUPBY表达式应该与选择列表表达式完全匹配，否则查询结果可能无法理解。【例6-20】查询sc表中每个学生的平均成绩。USEteaching;SELECTsno,avg(score)平均成绩FROMscGROUPBYsno;6.3.2HAVING的应用可以使用HAVING关键字来对查询和统计的结果进行进一步的筛选。【例6-21】在sc表中查询选修了3门及以上课程的学生学号和选课数。USEteaching;SELECTsno,COUNT(cno)选修课程数FROMscGROUPBYsnoHAVINGCOUNT(cno)>=3;

6.3.2HAVING的应用HAVING与WHERE子句的区别：WHERE子句是对源表中数据筛选满足条件的行；而HAVING子句是对GROUPBY分组查询后产生的组再加条件，筛选出满足条件的组。另外，HAVING中条件一般都直接使用聚合函数，WHERE中条件不能直接使用聚合函数。在MySQL系统中，一般情况下可以在3个地方使用聚合函数，即SELECT子句、HAVING子句和ORDERBY子句中。6.4连接查询在实际应用中，经常需要同时从两个表或两个以上表中检索数据。实现从两个或两个以上表中检索数据且结果集中出现的列来自于两个或两个以上表中的检索操作被称为连接技术。在MySQL系统中，这种连接操作又可以细分为交叉连接、内连接、外连接等。6.4.1内连接

内连接把两个表中的数据连接生成第三个表，第三个表中仅包含那些满足连接条件的数据行。内连接的语法：SELECTselect_listFROMtable1INNERJOINtable2ONjoin_condition或SELECTselect_listFROMtable1,table2WHEREjoin_condition连接条件格式为：[<table1>.]<column1><compareoperator>[<table2>.]<column2>6.4.1内连接

【例6-22】查询每个学生的姓名、课程号和成绩。

USEteaching;SELECTstudent.sname,o,sc.scoreFROMstudentINNERJOINscONstudent.sno=sc.sno;也可以利用下面的程序来实现：

SELECTstudent.sname,o,sc.scoreFROMstudent,scWHEREstudent.sno=sc.sno;

注意：当从多个表中查询的列名相同时，列名前必须加表名；列名不同时，列名前可以不加表名，但有时也会加上表名，以增强可读性。6.4.1内连接

【例6-23】查询计算机科学与技术专业的学生所选的每门课的平均分。USEteaching;SELECTo,avg(b.score)as平均成绩FROMstudentaINNERJOINscbONa.sno=b.snoanda.major='计算机科学与技术'GROUPBYo;为了简化输入，可以在SELECT查询的FROM子句中为表定义一个临时别名，在查询中引用，以缩写表名。6.4.1内连接

【例6-24】查询成绩在75分以上的学生的学号、姓名，选修课的课程号、课程名、成绩。USEteaching;SELECTo,ame,a.sno,a.sname,b.scoreFROMstudentASaJOINscASbONa.sno=b.snoANDb.score>75JOINcourseAScono=o;

6.4.2自连接连接操作不仅可以在不同的表上进行，而且在同一张表内可以进行自身连接，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。必须为表指定两个别名，使之在逻辑上成为两张表。【例6-25】查询同名学生的信息。USEteaching;SELECT*FROMstudentaINNERJOINstudentbONa.sname=b.snameANDa.sno<>b.sno;6.4.3外连接外连接一般只限制其中一个表的数据行，而不限制另外一个表中的数据。这种连接形式在许多情况下是非常有用的，例如在连锁超市统计报表时，不仅要统计那些有销售量的超市和商品，而且还要统计那些没有销售量的超市和商品。在MySQL系统中，可以使用的两种外连接关键字，即LEFTOUTERJOIN和RIGHTOUTERJOIN。①左外连接是对连接条件中左边的表不加限制。②右外连接是对连接条件中右边的表不加限制。6.4.3外连接【例6-26】查询每个学生及其选修课程的成绩情况（含未选课的学生信息）。USEteaching;SELECTstudent.*,o,sc.scoreFROMstudentLEFTJOINscONstudent.sno=sc.sno;【例6-27】查询学生选修课程的情况(含未被选修的课程信息)。USEteaching;SELECTsc.sno,sc.score,course.*FROMscRIGHTJOINcourseONo=o;如果想查询成绩最高的学生的学号和姓名。SELECTstudent.sno,sname,scoreFROMstudent,scWHEREscore=MAX(score)Andstudent.sno=sc.sno注意：where条件当中不能直接使用聚合函数。所以，以上语句是错误的，而这样的查询需要用子查询来完成。SELECT语句可以嵌套在其他许多语句中，这些语句包括SELECT、INSERT、UPDATE及DELETE等，这些嵌套的SELECT语句就被称为子查询。6.5子查询6.5子查询当一个查询依赖于另外一个查询结果时，那么可以使用子查询（一般为查询条件不已知）。在某些查询中，查询语句比较复杂不容易理解，因此为了把这些复杂的查询语句分解成多个比较简单的查询语句形式时也常使用子查询方式。使用子查询方式完成查询操作的技术是子查询技术。子查询可以分为无关子查询（嵌套子查询）和相关子查询。6.5.1无关子查询无关子查询的执行不依赖于外部查询。无关子查询在外部查询之前执行，然后返回数据供外部查询使用，无关子查询中不包含对于外部查询的任何引用。1.比较子查询使用子查询进行比较测试时，通过等于（=）、不等于（<>）、小于（<）、大于（>）、小于或等于（<=）以及大于或等于（>=）等比较运算符，将一个表达式的值与子查询返回的单值进行比较。6.5.1无关子查询【例6-29】查询与2021010001号学生在同一个专业学习的学生的学号、姓名和专业。USEteaching;SELECTsno,sname,majorFROMstudentWHEREmajor=(SELECTmajorFROMstudentWHEREsno='2021010001');6.5.1无关子查询【例6-30】查询C001号课考试成绩比刘玲玲高的学生的学号和姓名。USEteaching;SELECTstudent.sno,snameFROMstudent,scWHEREstudent.sno=sc.snoandcno='C001'andscore>(SELECTscoreFROMscWHEREcno='C001'andsno=(SELECTsnoFROMstudent

WHEREsname='刘玲玲'));6.5.1无关子查询2.SOME、ANY、ALL和IN子查询ALL和ANY操作符的常见用法是结合一个比较运算符对一个数据列子查询的结果进行测试，测试比较值是否与子查询所返回的全部或一部分值匹配。【例6-31】查询计算机科学与技术专业年龄最大的学生的学号和姓名。SELECTsno,snameFROMstudentWHEREsbirthday<=ALL(SELECTsbirthdayFROMstudentWHEREmajor='计算机科学与技术')ANDmajor='计算机科学与技术';6.5.1无关子查询【例6-32】查询与任何一个通信工程专业学生同龄的学生的信息。USEteaching;SELECT*FROMstudentWHEREyear(birthday)=ANY(SELECTyear(birthday)FROMstudentWHEREspecialty='通信工程');6.5.1无关子查询【例6-33】查询选修了C001号课程的学生姓名和所在专业。SELECTsname,majorFROMstudentWHEREsnoIN(SELECTsnoFROMscWHEREcno='C001');6.5.1无关子查询3.子查询结果作为主查询的查询对象【例6-34】查询有2个以上学生平均成绩超过80分的班级（用年级和专业表示）。USEteaching;SELECTgrade,majorFROMstudents,(SELECTsnoFROMSCGROUPBYsnoHAVINGAVG(score)>=80)ssWHEREs.sno=ss.snoGROUPBYgrade,majorHAVINGCOUNT(*)>=2;6.5.1无关子查询USEteaching;SELECTgrade,majorFROMstudentWHEREsnoIN(SELECTsnoFROMSCGROUPBYsnoHAVINGAVG(score)>=80)GROUPBYgrade,majorHAVINGCOUNT(*)>=2;6.5.2相关子查询在相关子查询中，子查询的执行依赖于外部查询，多数情况下是子查询的WHERE子句中引用了外部查询的表。无关子查询中子查询只执行一次，而相关子查询中的子查询需要重复地执行。

相关子查询的执行过程：(1)子查询为外部查询的每一行执行一次，外部查询将子查询引用的列的值传给子查询。(2)如果子查询的任何行与其匹配，外部查询就返回结果行。(3)再回到第一步，直到处理完外部表的每一行。6.5.2相关子查询1.比较子查询【例6-35】查询成绩比该课的平均成绩低的学生的学号、课程号、成绩。USEteaching;SELECTsno,cno,scoreFROMscaWHEREscore<(SELECTavg(score)FROMscbWHEREo=o);6.5.2相关子查询2.带有EXISTS的子查询（存在性测试）使用子查询进行存在性测试时，通过逻辑运算符EXISTS或NOTEXISTS，检查子查询所返回的结果集是否有行存在。使用逻辑运算符EXISTS时，如果在子查询的结果集内包含有一行或多行，则存在性测试返回TRUE；否则返回FALSE。在EXISTS前加上NOT时，将对存在性测试结果取反。6.5.2相关子查询【例6-37】查询所有选修了C004号课程的学生的学号和姓名。USEteaching;SELECTsno,snameFROMstudentWHEREEXISTS(SELECT*FROMscWHEREsno=student.snoANDcno='C004');

6.5.2相关子查询由EXISTS引出的子查询，其目标属性列表达式一般用*表示，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。使用子查询时要注意以下几点：(1)子查询需要用括号()括起来。(2)子查询可以嵌套。(3)子查询返回的结果的数据类型必须匹配外围查询WHERE语句的数据类型。(4)子查询中不能使用ORDERBY子句。6.6其它查询6.6.1限制查询的行数使用LIMIT子句可以选择只显示一个表中连续的某些行数据。语法格式：select_statementLIMITn[,m];【例6-38】查询student表，只显示前5行数据。USEteaching;SELECT*FROMstudentLIMIT5;6.6.1限制查询的行数【例6-39】查询student表，只显示第6到8行数据。USEteaching;SELECT*FROMstudentLIMIT5,3;注意：初始记录行的偏移量是0而不是1。6.6.2联合查询联合查询即集合的并，是指将两个或两个以上的SELECT语句通过UNION运算符连接起来的查询，联合查询可以将两个或更多查询的结果组合为单个结果集，该结果集包含联合查询中所有查询的全部行。使用UNION组合两个查询的结果集的两个基本规则：

所有查询中的列数和列的顺序必须相同；数据类型必须兼容。6.6.2联合查询【例6-40】查询选修了课程C001和课程C004的学生的课程号和姓名。USEteaching;SELECTcno,snameFROMsc,studentWHEREcno='C001'andsc.sno=student.snoUNIONSELECTcno,snameFROMsc,studentWHEREcno='C004'andsc.sno=student.sno;6.6.3对查询结果排序在使用SELECT语句时，排序是一种常见的操作。排序是指按照指定的列或其他表达式对结果集进行排列顺序的方式。SELECT语句中的ORDERBY子句负责完成排序操作。【例6-41】查询teaching库中女生的学号、姓名和专业，并按学号升序排列。USEteaching;SELECTsno,sname,majorFROMstudentWHEREssex='女'ORDERBYsnoASC;

6.6.3对查询结