SQL基础数据教程 10

任务6查询学生选课系统的多表数据——多表查询目录Contents学习目标6.1任务描述6.2知识准备6.2.1连接查询6.2.2子查询6.3任务实现6.4任务小结在任务5的学习中，数据查询都是在一张表中进行的，若要查询计算机科学与工程学院所有学生的学号、姓名、课程名程及成绩，就要涉及4张表，该如何实现呢？在实际应用中，经常会进行涉及两张表甚至多张表的数据查询，即多表连接查询。此外，为了提高查询的效率，针对涉及多个表的查询也可以采用子查询。本任务将学习如何实现多表连接查询和子查询，通过这两种方式的查询，掌握查询的语法及多表查询的基本用法，以便为后续的学习打下坚实的基础。查询学生选课系统的多表数据学习目标掌握连接查询操作，能够根据不同场景使用交叉连接查询、内连接查询和外连接查询进行多表查询；熟悉子查询的概念，能够区分每种子查询的作用；掌握子查询，能够使用子查询结合IN、EXISTS、ANY、ALL及比较运算符进行查询。查询学生选课系统的多表数据6.1任务描述在前面任务的基础上，继续对数据库scoredb中的6张表院系department、学生student、课程course、教师teacher、选修score以及讲授teaching进行查询操作，主要是涉及到对多个表的查询，例如利用SQL语句查询计算机科学与工程学院所有学生的学号、姓名、课程名程及成绩，查询结果如图6-1所示。查询学生选课系统的多表数据6.1任务描述查询学生选课系统的多表数据图6-1计算机科学与工程学院所有学生的相关信息6.2知识准备任务5对数据的查询都是基于一张数据表完成的，即单表查询，然而实际应用中业务逻辑较为复杂，表与表之间可能存在业务联系，在实际查询中，很多情况下用户所需要的数据并不是全部都在一个表中，而是存在于多个不同的表中，这时则需要基于两张或两张以上的数据表进行查询，即多表查询。在多表查询中，当两张或多张数据表中存在相同意义的字段时，如果需要同时显示多张数据表中的数据，便可以通过这些意义相同的字段将不同的数据表进行连接并对连接后的数据表进行查询，这样的查询通常称为连接查询，而有时在多表查询中，可能需要多条SQL语句查询数据，当一条查询语句嵌套在另一条语句内部的查询，且当某条语句执行所需要的过滤条件是另外一条SELECT语句的结果时，可以通过子查询实现。因此，在日常生活和工作中，我们也需要思考和处理各种复杂的问题不断进行自我学习，提升专业素养，从而更好地应对各种挑战。本章将主要针对多表查询中的连接查询和子查询进行讲解。查询学生选课系统的多表数据6.2.1连接查询在关系数据库中，如果需要同时获得多张数据表中的数据，可以将多张数据表中相关联的字段进行连接，并对连接后的数据表进行查询，这样的查询方式称为连接查询。连接查询是通过各个表之间的共同列的相关性来查询数据的，进行多表连接查询时首先要在这些表中建立连接，再在连接生成的结果集中进行筛选。在MySQL中，连接查询包括交叉连接查询、内连接查询和外连接查询，本节将讲解这几类连接查询。查询学生选课系统的多表数据6.2.1连接查询1.交叉连接查询交叉连接就是将连接的两个表的所有行进行组合，也就是将第一个表的所有行分别与第二个表的每行连接形成一个新的数据行。交叉连接查询后生成的结果集的数据行数等于两个表的行数的乘积，字段个数等于两个表的字段个数的和，其语法格式如下：SELECT*FROM表名1CROSSJOIN表名2;该交叉连接语法也可用隐式表示法替代，即使用逗号分隔两个表，所以上面后两句的可以用如下语句替换。FROM表名1,表名2;查询学生选课系统的多表数据6.2.1连接查询1.交叉连接查询下面对上述语法格式进行说明。（1）经过交叉连接查询后，SELECT后面的字段是两个表的所有字段，因此用通配符“*”表示。（2）CROSSJOIN是将两个表进行交叉连接的关键字，表名1是第一个表的名字，表名2是第二个表的名字。查询学生选课系统的多表数据6.2.1连接查询1.交叉连接查询【例6.1】查询图书馆中所有图书与所有读者的可能借阅组合，要求同时显示图书完整信息和读者完整信息。SQL语句如下：SELECT*FROMbooksCROSSJOINreaders;查询学生选课系统的多表数据6.2.1连接查询1.交叉连接查询执行结果如图6-2所示。查询学生选课系统的多表数据图6-2查询图书馆中所有图书与所有读者的可能借阅组合6.2.1连接查询1.交叉连接查询注意：交叉连接查询用来返回连接表的笛卡尔积，笛卡尔积在实际应用中一般是没有任何意义的。下面以学生表（s）和课程表（c）为例，使用交叉连接查询将学生信息与所选修课程的信息显示在同一个结果中。其中，学生表和课程表之间是多对多的关系，即一个学生可以选修多门课程，一门课程可以供多个学生选修。交叉连接查询的示意如图6-3所示。查询学生选课系统的多表数据图6-3交叉连接查询6.2.1连接查询1.交叉连接查询在图6-3

中，学生表中定义了2个字段，其中，sno表示学号，name表示学生姓名，department表示所属专业。课程表中定义了2个字段，其中cno表示课程号，name表示课程名称。由查询结果可知，学生表和课程表中的全部记录都显示在一个结果中，学生表中有3条记录，课程表中有3条记录，最后的查询结果有9条记录，每条记录中含有5个字段。查询学生选课系统的多表数据6.2.1连接查询2.内连接查询通过上面案例可以发现，交叉连接查询会返回很多无效的数据，没有参考意义。为了提高查询结果的准确度，可以使用内连接查询限定查询语句的条件，从而去除无效的数据。内连接查询是将一张数据表中的每一行记录按照指定条件与另外一张数据表进行匹配，如果匹配成功，则返回参与内连接查询的两张数据表中符合连接条件的部分，如果匹配失败，则不保留数据。下面以学生表（s）和课程表（c）为例，使用内连接查询添加学生选修课程的信息，在图6-3的基础上，增加了属性sc，表示学生选修的课程信息。内连接查询的示意如图6-4所示。查询学生选课系统的多表数据图6-4内连接查询6.2.1连接查询2.内连接查询在图6-4中，学生表中的sno为3的学生，在课程表中不存在对应的cno和name，因此不在查询结果中。内连接查询用比较运算符设置连接条件，只返回满足连接条件的数据行，相当于将交叉连接查询生成的结果集再按照连接条件进行筛选。MySQL中内连接查询分为隐式内连接查询和显示内连接查询两种，具体如下。（1）隐式内连接查询隐式内连接查询语法相对简单，基本语法格式如下。SELECT*|{字段名1[,字段名2,…]}FROM表名1,表名2,…WHERE连接条件;查询学生选课系统的多表数据6.2.1连接查询2.内连接查询下面对上述语法格式中的各个部分进行详细介绍。①

连接条件：由于内连接查询是对两张数据表进行操作，在查询数据时，为了避免字段重名出现错误，需要在连接条件中指定所操作的字段来源于哪一张数据表，因此，字段前必须加表名，中间用“.”分隔，格式为：数据表.字段名称。例如，学生表student和院系表department中都有deptno字段，在使用deptno字段时，必须加上表名，写成“student.deptno”或“department.deptno”。使用非共有的字段时，可以直接写字段名称。②指定连接条件时，WHERE字句后必须包含连接条件表达式，即两个表公共字段的匹配规则，内连接仅返回所有匹配成功的数据。查询学生选课系统的多表数据6.2.1连接查询2.内连接查询③连接条件：语法格式如下为[表名1.]字段名称1比较运算符[表名2.]字段名称2，用到的比较运算符:“=”“<”“<=”“>”“>=”“!=”等。注意：进行多表连接查询时，首先要确定哪几个表需要进行连接查询。进行连接查询的表要能够包括输出的所有字段，并且用到的表的数量要尽可能地少。进行连接的表之间要有含义相同的字段。查询学生选课系统的多表数据6.2.1连接查询2.内连接查询【例6.3】查询所有未归还的图书信息，显示书名、书号和借书日期。分析：若只显示未归还的图书，则字段return_date的值是ISNULL，显示的字段为title,bookid,borrow_date。SQL语句如下。SELECTbooks.titleAS'书名',books.bookidAS'书号',borrow_records.borrow_dateAS'借书日期'FROMbooks,borrow_recordsWHEREbooks.bookid=borrow_records.bookidandborrow_records.return_dateISNULL;查询学生选课系统的多表数据6.2.1连接查询2.内连接查询执行结果如图6-5所示。查询学生选课系统的多表数据图6-5使用隐式内连接查询所有未归还的图书信息注意：在多表查询中，若不同表存在相同名称的字段，应使用“表名.字段名”的格式明确指定该字段所属的表，以避免歧义。6.2.1连接查询2.内连接查询（2）显示内连接查询显式内连接查询在查询多张表时执行速度比隐式内连接查询快，基本语法格式如下：SELECT*|{字段名1[,字段名2,…]}FROM表名1[INNER]JOIN表名2ON连接条件;其中，[INNER]JOIN用于连接两张数据表，INNER可以省略。查询学生选课系统的多表数据6.2.1连接查询2.内连接查询【例6.4】使用显示内连接查询改写例6.3。SQL语句如下。SELECTbooks.titleAS'书名',books.bookidAS'书号',borrow_records.borrow_dateAS'借书日期'FROMbooksJOINborrow_recordsONbooks.bookid=borrow_records.bookidandborrow_records.return_dateISNULL;执行结果与例6.3的结果相同，如图6-6所示。查询学生选课系统的多表数据6.2.1连接查询2.内连接查询执行结果如图6-6所示。查询学生选课系统的多表数据图6-6使用显式内连接查询所有未归还的图书信息6.2.1连接查询3.外连接查询外连接查询与内连接不同，其在查询时所用的表有主从之分。进行外连接时，以主表中每行数据去匹配从表中的数据行，如果符合连接条件则将其返回结果集中；如果没有找到匹配行则在结果集中仍然保留主表的行，相对应地，从表中的字段将被填入NULL值。外连接的语法格式如下所示：SELECT*|{字段名1[,字段名2,…]}FROM表名1LEFTJOIN|RIGHTJOIN|FULLJOIN表名2ON表名1.字段名1比较运算符表名2.字段名2;

在上述语法中，关键字左边的表（表名1）为左表，右边的表（表名2）为右表，因此，外连接查询只适用于两个表。外连接包括3种类型：左外连接、右外连接和全外连接，具体介绍如下：查询学生选课系统的多表数据6.2.1连接查询3.外连接查询（1）左外连接：左外连接关键字为LEFTJOIN，左边的表表1为主表，右边的表表2为从表。将左表中的所有数据行与右表中的每行按连接条件进行匹配，结果集中包括左表中所有的数据行。左表中那些与右表没有相匹配的行，在结果集中右表中的字段都以NULL来填充。BIT类型不允许为NULL，以0填充。下面以学生表（s）和课程表（c）为例，使用左外连接查询学生选修课程的信息，即使某个学生没有选修课程，也要包含在查询结果中。左外连接查询的示意如图6-7所示。查询学生选课系统的多表数据6.2.1连接查询3.外连接查询在图6-7中，学生表中的sno为3的学生没有选修课程，所以在查询结果中它的cno和name显示未NULL。查询学生选课系统的多表数据图6-7左外连接查询6.2.1连接查询3.外连接查询【例6.5】查询所有读者（包括未借书的）的借阅记录，显示读者姓名和借书日期。做查询之前，首先向读者表中录入一条数据，SQL语句如下。INSERTINTOreadersVALUES('R004','赵敏',);此时，执行如下的查询SQL语句，读者表readers中的数据如图6-8所示。SELECT*FROMreaders;查询学生选课系统的多表数据图6-8查询读者表的全部信息6.2.1连接查询3.外连接查询【例6.5】查询所有读者（包括未借书的）的借阅记录，显示读者姓名和借书日期。查询所有读者（包括未借书的）的借阅记录的SQL语句如下。SELECTreaders.reader_nameAS'读者姓名',borrow_records.borrow_dateAS'借书日期'FROMreadersLEFTJOINborrow_recordsONreaders.reader_id=borrow_records.reader_id;执行结果如图6-9所示。查询学生选课系统的多表数据6.2.1连接查询3.外连接查询执行结果如图6-9所示。查询学生选课系统的多表数据图6-9查询所有读者（包括未借书的）的借阅记录特点：保留左表（readers）所有行，未借书的读者显示为NULL。6.2.1连接查询3.外连接查询（2）右外连接：右外连接关键字为RIGHTJOIN。右边的表表2为主表，左边的表表1为从表。将右表中的所有数据行与左表中的每行按连接条件进行匹配，结果集中包括右表中所有的数据行。右表中那些与左表没有相匹配的行，在结果集中左表中的字段都以NULL来填充。右连接的特点是保留右表（borrow_records）所有行，读者信息缺失的记录显示为NULL。下面以学生表（s）和课程表（c）为例，使用右外连接查询选修课程的学生信息，即使某个课程没有学生选修，也要包含在查询结果中。右外连接查询的示意如图6-10所示。查询学生选课系统的多表数据6.2.1连接查询3.外连接查询查询学生选课系统的多表数据图6-10右外连接查询在图6-10中，课程表中的cno为44的课程没有对应的学生选修，所以在查询结果中它的sno、name、department、sc显示未NULL。6.2.1连接查询3.外连接查询（3）全外连接：全外连接关键字为FULLJOIN。该连接查询结果集中包括两个表的所有的数据行，若左表中某一行在右表中有匹配数据，则结果集中对应的右表的字段填入相应数据，否则填入NULL；若右表中某一行在左表中没有匹配数据，则结果集中对应的左表中的字段填入NULL。查询学生选课系统的多表数据6.2.2子查询在多表查询中，若需在一个查询的执行过程中引用另一个查询的结果，可通过子查询实现。由于查询时需要多条SQL语句实现，即在WHERE子句中包含另一个SELECT语句，使用包含在内部的SELECT语句进行的查询称为子查询，同时这种将一个查询嵌套在另一个查询的WHERE子句中的查询也称为嵌套查询，包含子查询的SELECT语句称为父查询。含有子查询的语句执行时由内向外，首先执行子查询，子查询得到的结果集不显示出来，而是传给父查询，作为父查询的条件使用，然后执行父查询，并显示查询结果。子查询一般是嵌套在SELECT查询语句中，按查询结果来说主要分为两种形式：结果为单个值的子查询和结果为多个值的子查询。然而，有时候子查询的结果可能为空值，从而会影响到SELECT字句中的具体字段的表示，所以在子查询前面加入了EXISTS谓词，用来测试子查询的结果是否为空，接下来分别进行一一介绍。查询学生选课系统的多表数据6.2.2子查询1.子查询结果为单个值子查询结果为单个值时，父查询条件中一般使用“=”“>”“<”“<=”“>=”“!=”等比较运算符，其语法格式如下：SELECT*|{字段名[,…]}FROM表名1,表名2WHERE字段名比较运算符(SELECT*|{字段名[,…]}FROM表名1,表名2WHERE条件表达式);查询学生选课系统的多表数据6.2.2子查询1.子查询结果为单个值【例6.6】查询库存数量高于所有图书平均库存量的图书信息，SQL语句如下。SELECTbookidAS'书号',titleAS'书名',quantityAS'库存量'FROMbooksWHEREquantity>(SELECTAVG(quantity)FROMbooks);查询学生选课系统的多表数据6.2.2子查询1.子查询结果为单个值分析：（1）子查询：SELECTAVG(quantity)FROMbooks用来计算books表的平均库存数量。（2）比较运算符：父查询的库存数量>子查询结果（平均库存数量）。（3）执行过程：先计算平均值，再筛选quantity>平均库存数量的图书记录。（4）子查询位置：在WHERE子句中作为条件值使用。（5）适用场景：需要基于聚合值（平均、最大、最小等）进行筛选时。执行结果如图6-11所示。查询学生选课系统的多表数据6.2.2子查询1.子查询结果为单个值查询学生选课系统的多表数据图6-11查询库存数量高于所有图书平均库存量的图书信息6.2.2子查询2.子查询结果为多个值的集合子查询结果为多个值时，父查询条件中一般使用的是IN、ANY、ALL等运算符，其语法格式如下：SELECT*|{字段名[,…]}FROM表名1,表名2WHERE字段名IN|ANY|ALL(SELECT*|{字段名[,…]}FROM表名1,表名2WHERE条件表达式);查询学生选课系统的多表数据6.2.2子查询2.子查询结果为多个值的集合其中，IN运算符用于判断父查询中某个属性列值是否在子查询的结果中；在子查询前面使用ANY谓词时，会使用指定的比较运算符将一个表达式的值或字段的值与子查询返回值中的每一个进行比较，只要有一次比较的结果为TRUE，则整个表达式的值为TRUE，否则为FALSE；在子查询前面使用ALL谓词时，会使用指定的比较运算符将一个表达式的值或字段的值与子查询返回值中的每一个进行比较，只有当所有比较的结果都为TRUE时，整个表达式的值才为TRUE，否则为FALSE。（1）使用IN运算符【例6.7】查询库存量属于"红楼梦"或"西游记"库存量的其他图书，SQL语句如下。查询学生选课系统的多表数据6.2.2子查询2.子查询结果为多个值的集合SELECTtitleAS'书名',quantityAS'库存量'FROMbooksWHEREquantityIN(SELECTquantityFROMbooksWHEREtitleIN('红楼梦','西游记'));查询学生选课系统的多表数据6.2.2子查询2.子查询结果为多个值的集合分析：（1）子查询：返回红楼梦和西游记的库存量，结果为[30,50]。（2）父查询：使用IN判断其他图书的库存量是否在这两个值中。（3）结果：返回库存量为30或50的所有图书（包括红楼梦和西游记本身）。执行结果如图6-12所示。查询学生选课系统的多表数据图6-12查询库存量属于“红楼梦”或“西游记”库存量的图书信息6.2.2子查询2.子查询结果为多个值的集合（2）使用ANY运算符【例6.8】查询库存量大于任意一本"三联书店"出版图书库存量的图书，SQL语句如下。SELECTtitle,quantityFROMbooksWHEREquantity>ANY(SELECTquantityFROMbooksWHEREpublisher='三联书店');查询学生选课系统的多表数据6.2.2子查询2.子查询结果为多个值的集合执行结果如图6-13所示。查询学生选课系统的多表数据图6-13查询库存量大于任意一本"三联书店"出版图书库存量的图书信息6.2.2子查询2.子查询结果为多个值的集合（3）使用ALL运算符【例6.9】查询库存量大于所有"中华书局"出版图书库存量的图书，SQL语句如下。SELECTtitle,quantityFROMbooksWHEREquantity>ALL(SELECTquantityFROMbooksWHEREpublisher='中华书局');查询学生选课系统的多表数据6.2.2子查询2.子查询结果为多个值的集合执行结果如图6-14所示。查询学生选课系统的多表数据图6-14查询库存量大于所有"中华书局"出版图书库存量的图书信息6.2.2子查询3.EXISTS子查询EXISTS子查询是指在子查询前面加上EXISTS或NOTEXISTS谓词。EXISTS和后面的子查询构成EXISTS表达式。EXISTS用于测试子查询的结果是否为空，如果结果不为空，则返回TRUE；否则返回FALSE，其语法格式如下：SELECT*|{字段名[,…]}FROM表名1,表名2WHEREEXISTS|NOTEXISTS(SELECT*|{字段名[,…]}FROM表名1,表名2WHERE条件表达式);查询学生选课系统的多表数据6.2.2子查询3.EXISTS子查询（1）EXISTS子查询示例【例6.10】查询有借阅记录的所有图书信息，SQL语句如下。SELECTbookidAS'书号',titleAS'书名'FROMbooksbWHEREEXISTS(SELECT*FROMborrow_recordsbrWHEREbr.bookid=b.bookid);查询学生选课系统的多表数据6.2.2子查询3.EXISTS子查询执行结果如图6-15所示。查询学生选课系统的多表数据图6-15查询有借阅记录的所有图书信息6.2.2子查询3.EXISTS子查询分析：①EXISTS作用：检查子查询是否至少返回一行数据，若有则返回TRUE，否则返回FALSE。②执行逻辑：对books表的每行，子查询检查该书号是否存在于borrow_records中。③B004未出现：因为“水浒传“在borrow_records表中没有匹配记录，子查询返回空集，EXISTS判断为FALSE，故该行数据不被输出。④性能优势：相较于IN子查询，EXISTS通常更高效，尤其在数据量大时，因为它在找到第一条匹配记录后即停止扫描，无需生成完整结果集。查询学生选课系统的多表数据6.2.2子查询3.EXISTS子查询（2）NOTEXISTS子查询示例【例6.11】查询从未被借过的图书，SQL语句如下。SELECTbookidAS'书号',titleAS'书名'FROMbooksbWHERENOTEXISTS(SELECT*FROMborrow_recordsbrWHEREbr.bookid=b.bookid);查询学生选课系统的多表数据6.2.2子查询3.EXISTS子查询（2）NOTEXISTS子查询示例执行结果如图6-16所示。查询学生选课系统的多表数据图6-16查询从未被借过的图书信息6.3任务实现在本章中，主要完成3个子任务，分别是连接查询、子查询、带外键约束的查询操作，下面以“学生选课系统”数据库举例进行详细介绍。6.3.1练习连接查询操作1.交叉连接查询【例6.12】将教师表teacher和课程表course交叉连接，查询出教师和课程所有可能得组合。分析：教师表teacher中含18条记录、课程表course中含29条记录，根据交叉连接（笛卡尔积）规则，结果集记录条数为18*29=522条。实际业务中，一个教师不可能讲授所有的课程，所以结果集中许多记录没有意义，实际应用中很少用到交叉连接。SQL语句如下：SELECT*FROMteacher,course;查询学生选课系统的多表数据6.3任务实现6.3.1练习连接查询操作执行结果如图6-17所示。查询学生选课系统的多表数据图6-17例6.12执行结果6.3任务实现6.3.1练习连接查询操作2.内连接查询（1）隐式内连接查询【例6.13】在scoredb数据库的基础上，查询计算机科学与工程学院所有学生的学号、姓名、院系名称、课程名称及成绩。分析：查询结果需要输出sno（学号）、name（姓名）、deptname（院系名称）、cname（课程名称）和grade（成绩）5个字段，在scoredb数据库中，没有任何一个表同时包含这5个字段，这时就需要进行多表连接查询。查询学生选课系统的多表数据6.3任务实现6.3.1练习连接查询操作在表student和score中有sno（学号）字段，在student表中有name（姓名）字段，在department表中有deptname（院系名称）字段，在course表中有cname（课程名称）字段，在score表中有grade（成绩）字段。由此可知，要查询并输出指定的5个字段，需要用共同的字段deptno（院系代码）、sno（学号）来连接department、student、course、score4个表。SQL语句如下：SELECTstudent.sno,sname,deptname,cname,gradeFROMdepartment,student,course,scoreWHEREdeptname="计算机科学与工程学院"anddepartment.deptno=student.deptnoanddepartment.deptno=course.deptnoando=oandstudent.sno=score.sno查询学生选课系统的多表数据6.3任务实现6.3.1练习连接查询操作执行结果如图6-18所示。查询学生选课系统的多表数据图6-18例6.13执行结果6.3任务实现6.3.1练习连接查询操作【例6.14】在scoredb数据库的基础上，显示所有学生的学号、姓名和成绩信息，并按照成绩降序排序。分析：查询结果需要输出sno（学号）、name（姓名）和grade（成绩）3个字段，在scoredb数据库中，没有任何一个表同时包含这3个字段，这时就需要进行多表连接查询。在表student和score中有sno（学号）字段，在student表中有name（姓名）字段，在score表中有grade（成绩）字段。由此可知，要查询并输出指定的3个字段，需要用共同的字段为sno（学号）来连接student和score这两个表。SQL语句如下：查询学生选课系统的多表数据6.3任务实现6.3.1练习连接查询操作SELECTstudent.sno,sname,gradeFROMstudent,scoreWHEREstudent.sno=score.snoORDERBYgradeDESC;执行结果如图6-19所示。查询学生选课系统的多表数据图6-19例6.14执行结果6.3任务实现6.3.1练习连接查询操作2.内连接查询（2）显示内连接查询【例6.15】统计各个院系的学生的人数，并按照院系代码（deptno）升序排序。分析：查询结果需要输出3个字段，分别是deptno（院系代码）、deptname（院系名称）、count(sno)（学生人数），在scoredb数据库中，没有任何一个表同时包含这3个字段，这时就需要进行多表连接查询。查询学生选课系统的多表数据6.3任务实现6.3.1练习连接查询操作在本例中，在department表和student表中有deptno（院校代码）字段，在department表中有deptname（院系名称）字段。由此可知，要查询并输出指定的3个字段，需要department表和student这两个表，这两个表可以用来进行连接的共同的字段为deptno（院校代码）。SQL语句如下：selectdepartment.deptno,deptname,count(sno)fromdepartmentINNERJOINstudentondepartment.deptno=student.deptnogroupbydeptnameorderbydeptno;查询学生选课系统的多表数据6.3任务实现6.3.1练习连接查询操作执行结果如图6-20所示。查询学生选课系统的多表数据图6-20例6.15执行结果6.3任务实现6.3.1练习连接查询操作【例6.16】查询出选修了“数据结构”的学生的学号、姓名、班级、课程名称及成绩信息。分析：查询结果需要输出5个字段，在scoredb数据库中，没有任何一个表同时包含这5个字段，这时就需要进行多表连接查询。在本例中，在student表和score表中有sno（学号）字段，在student表中有sname（姓名）、classno（班级）字段，在score表中有grade（成绩）字段，在course表和score表中有cno（课程编号）字段，在course表中有cname（课程名称）字段。由此可知，要查询并输出指定的5个字段，需要student表、score表和course表三个表，这三个表可以用来进行连接的共同的字段为sno（学号）、cno（课程编号）。查询学生选课系统的多表数据6.3任务实现6.3.1练习连接查询操作对应的SQL语句如下：selectstudent.sno,sname,classno,cname,gradefromstudentINNERJOINcourseINNERJOINscoreoncname="数据结构"ando=oandstudent.sno=score.sno;执行结果如图6-21所示。查询学生选课系统的多表数据图6-21例6.16执行结果6.3任务实现6.3.1练习连接查询操作3.外连接查询（1）左外连接【例6.17】在scoredb数据库表deptname的基础上，首先利用SQL语句插入一条数据：insertintodepartmentvalues('68001','汽车学院','张丽');然后输出所有院系的学生信息，没有学生的院系也要列出。分析：要输出所有院系的学生信息，需关联department表和student表，因需保留无学生的院系记录，故department表是左表（主表）。查询学生选课系统的多表数据6.3任务实现6.3.1练习连接查询操作3.外连接查询对应的SQL语句如下：SELECT*FROMdepartmentLEFTJOINstudentONdepartment.deptno=student.deptno;执行结果如图6-22所示。查询学生选课系统的多表数据图6-22例6.17执行结果（只截取了部分数据）6.3任务实现6.3.1练习连接查询操作3.外连接查询（2）右外连接【例6.18】在scoredb数据库表student的基础上，首先利用SQL语句插入一条数据：insertintostudentvalues(20231125,'张三','材料化学1班','68005');然后输出所有学生院系的信息。分析：要输出所有学生院系名称的信息，说明需要department表和student表，输出所有学生院系的信息，说明student表是主表。查询学生选课系统的多表数据6.3任务实现6.3.1练习连接查询操作3.外连接查询对应的SQL语句如下：SELECT*FROMdepartmentRIGHTJOINstudent