《AI+MySQL数据库案例教程》课件 任务6-11 查询学生选课系统的多表数据 -综合项目实训

任务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语句如下。SELECT

books.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语句如下。SELECT

books.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语句如下。SELECT

readers.reader_nameAS'读者姓名',

borrow_records.borrow_dateAS'借书日期'FROMreadersLEFTJOINborrow_records

ONreaders.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语句如下。SELECT

bookidAS'书号',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语句如下。SELECT

bookidAS'书号',titleAS'书名'FROMbooksbWHEREEXISTS(SELECT*FROMborrow_records

br

WHEREbr.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语句如下。SELECT

bookidAS'书号',titleAS'书名'FROMbooksbWHERENOTEXISTS(SELECT*FROMborrow_records

br

WHEREbr.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,grade

FROMdepartment,student,course,scoreWHEREdeptname="计算机科学与工程学院"anddepartment.deptno=student.deptno

anddepartment.deptno=course.deptnoando=o

andstudent.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,grade

FROMstudent,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*FROMdepartmentRIGHTJOINstudentONdepartment.deptno=student.deptno;执行结果如图6-23所示。查询学生选课系统的多表数据图6-23例6.18执行结果（只截取了部分数据）6.3任务实现6.3.2练习子查询操作1.子查询结果为单个值【例6.19】查询scoredb数据库，输出成绩大于平均成绩的学生的学号和姓名。分析：题目中的字段涉及到了学生表student和选修表score，先用子查询从表score中计算出平均成绩，并查找出“成绩”大于平均成绩的学生学号，再用父查询查找出学号等于子查询找到的学号的那些数据行以及学生的姓名，输出这些行的学号和姓名。对应的SQL语句如下：SELECTstudent.sno,snameFROMstudent,scoreWHEREstudent.sno=score.snoandscore.grade>(SELECTAVG(grade)FROMscore);查询学生选课系统的多表数据6.3任务实现6.3.2练习子查询操作1.子查询结果为单个值执行结果如图6-24所示。查询学生选课系统的多表数据图6-24例6.8执行结果6.3任务实现6.3.2练习子查询操作2.子查询结果为多个值的集合【例6.20】查询scoredb数据库，输出成绩大于且等于80分以上的学生姓名。分析：题目中的字段涉及到了学生表student和选修表score，先用子查询从表score中查找出“成绩”大于且等于80分以上的学生的学号，再用父查询查找出学号等于子查询找到的学号的那些数据行以及学生的姓名，输出这些行的姓名即可。对应的SQL语句如下：SELECTsnameAS成绩大于且等于80分以上的学生FROMstudentWHEREsnoIN(SELECTsnoFROMscoreWHEREgrade>=80);查询学生选课系统的多表数据6.3任务实现6.3.2练习子查询操作2.子查询结果为多个值的集合执行结果如图6-25所示。查询学生选课系统的多表数据图6-25例6.9执行结果6.3任务实现6.3.2练习子查询操作3.EXISTS子查询【例6.21】查询scoredb数据库，输出成绩大于且等于80分以上的学生姓名。分析：题目中的字段涉及到了学生表student和选修表score，先用子查询从表score中查找出“成绩”大于且等于80分以上的所有字段，若存在这样的学生，则使用EXISTS谓词，再用父查询查找出学号等于子查询找到的学号的那些数据行以及学生的姓名，输出这些行的学号和姓名即可。查询学生选课系统的多表数据6.3任务实现6.3.2练习子查询操作3.EXISTS子查询对应的SQL语句如下：SELECTsno,snameFROMstudentWHEREEXISTS(SELECT*FROMscoreWHEREstudent.sno=score.snoANDgrade>=80);执行结果如图6-26所示。查询学生选课系统的多表数据图6-26例6.21执行结果6.4任务小结本任务主要介绍了多表查询的基础知识，而连接查询和子查询是用于实现多表查询的常用方式，连接查询包括交叉连接查询、内连接查询和外连接查询，子查询包括结果为单个值、结果为多个值以及EXISTS子查询三种情况。本任务的主要知识点如图6-27所示。查询学生选课系统的多表数据6.4任务小结查询学生选课系统的多表数据图6-27主要知识点总结点此添加标题谢谢观看！任务7优化学生选课系统查询操作和性能——创建索引与视图目录Contents学习目标7.1任务描述7.2知识准备7.2.1索引7.2.2视图7.3任务实现7.4任务小结如果使用前面讲的SELECT语句在100万条记录的学生基本信息表student中查询学号为11101001的学生的信息，大概用时30s，响应时间较长，为了快速访问数据表中的特定信息，提高系统的响应速度，可以建立索引加快数据查询效率。另外，使用数据库时，不仅需要提高对数据的查询效率，还需要考虑数据的安全问题，此时在MySQL中可以创建一种叫做视图的虚拟表，让使用的用户只能访问被允许访问的结果集，从而提高数据的安全性。通过创建索引和视图，了解索引和视图的概念，理解索引的分类，掌握索引和视图的查看、删除等操作，掌握视图数据的操作。优化学生选课系统查询操作和性能学习目标掌握连接查询操作，能够根据不同场景使用交叉连接查询、内连接查询和外连接查询进行多表查询；了解索引的概念，能够说出索引的作用；理解索引的分类，能够说出主要有哪几种索引；掌握索引的创建，能够在创建和修改数据表的同时创建索引、在已有的数据表上创建索引；掌握索引的查看，能够通过SHOW语句查看数据表中索引的信息；掌握索引的删除，能够使用ALTERTABLE语句和DROPINDEX语句删除索引；了解视图的概念，能够说出视图的优点；掌握视图的管理，能够创建、查看、修改和删除视图；掌握视图数据操作，能够通过视图添加、修改和删除基本表中的数据。优化学生选课系统查询操作和性能7.1任务描述首先在新建的数据库stu_course中创建一个没有任何索引的学生表stu，该表中包含sno、sname、sgender、sbirth、sclass和sresume六个字段，然后通过SQL语句分别对stu表的字段sname创建普通索引、sno创建唯一索引、

sresume创建全文索引、

sname和sclass创建多列索引，最后通过索引查看语句SHOWINDEX语句查看索引的创建情况，结果如图7-1所示。优化学生选课系统查询操作和性能图7-1使用SHOWINDEX语句查看stu表的结构7.1任务描述在数据库stu_course中再创建一个教师表teacher_1，该表中包含tno、tname、tsex、tage、tedu和tpro六个字段，接着基于该表创建视图teacher_view并输出所有教师的姓名tname、性别tsex和职称tpro信息，最后使用DESCRIBE语句查看视图

teacher_view的基本信息，结果如图7-2所示。优化学生选课系统查询操作和性能图7-2使用DESCRIBE查看视图teacher_view7.2知识准备数据库技术经历了多年的发展，其功能已经不仅仅是存储和管理数据，还可以通过很多易用的特性来提高用户体验和帮助企业节约资源。在关系数据库中，视图和索引主要起到输助查询和组织数据的功能，可以大大提高查询数据的效率。通过对视图的操作，不但可以实现查询的简化，而且还会提高安全性。通过索引，可以快速查询数据表中某一特定的记录，本章将从索引和视图的概念出发，主要介绍索引的创建、查看和删除方法，视图的创建、查看、修改和删除方法，以及通过视图添加、修改和删除基本表中的数据。优化学生选课系统查询操作和性能7.2.1索引在数据库操作中，经常需要查找特定的数据，而索引则用来快速地寻找那些具有特定值的记录。例如，当执行“SELECT*FROMstudentWHEREsno=’11101001’”语句时，如果没有索引，MySQL数据库必须从第一个记录开始扫描表，直至找到sno是“11101001”的记录。表里面的记录数量越多，这个操作的代价就越高。如果在作为搜索条件的列上创建了索引，MySQL在查找特定值的记录时，则无须扫描任何记录即可迅速得到目标记录所在的位置，这可以大大提高查找的效率。优化学生选课系统查询操作和性能7.2.1索引1.索引的概念索引是一个单独的、物理的、存储在磁盘上的数据库表结构，其属性由数据表中的一列或多列字段组合而成。在索引表中，首先按照索引属性进行排序，然后属性值与记录行之间按照一定的顺序建立关系，即用指针指向记录的位置，因此，索引的属性实际包含两个方面，一是从数据表生成的键，即数据表中的一列或多列字段的组合，二是映射到指定记录行的存储位置指针，如图7-3所示。若此时对课程表course的课程编号cno字段创建索引后，索引表的属性由数据表course的cno字段（该字段在索引表中按照大小已排好序）和映射到指定记录行的存储位置指针构成，当查询课程编号为10500132的记录时，首先在索引表中找到10500132，然后通过指针直接找到课程表course中对应的记录（10500132，Web前端开发，64），而不用从第1条记录扫描，大大提高了查询速度。优化学生选课系统查询操作和性能7.2.1索引1.索引的概念优化学生选课系统查询操作和性能图7-3索引示例7.2.1索引1.索引的概念通过图7-3可知，当通过索引查询表中的数据时，只需要查询索引项，不需要遍历所有数据库中的所有数据，就能快速查询表中的特定记录，这样会提高查询效率，可以把索引比作书中的目录，读者可以通过目录快速找到想要查看章节的页码，然后翻到对应的页码查看具体的内容，此外，还可以把索引比作新华字典的音序表，例如，要查“库”字，若不使用音序，即不创建索引，就需要从字典的400页中逐页来找，如果提取拼音出来，构成音序表，即创建索引，就只需要从10多页的音序表中直接查找，大大节省了时间。图7-3的数据较少，建立索引后，查询速度提高不明显，当数据表中的数据非常多时，例如新华字典，创建索引后（音序表）查询速度会显著提高。优化学生选课系统查询操作和性能7.2.1索引1.索引的概念虽然使用索引可以提高数据库的性能，但是索引表需要占用磁盘的存储空间，需要注意避免在一个表上创建大量的索引，因为索引会降低更新表中数据的速度，当更新表中的数据时，系统会自动更新索引页面的数据，可能需要重新组织索引，这些都需要消耗时间。因此，索引设计不合理可能会影响系统的性能，高效、合理的索引对数据库系统获得良好的性能非常重要，设计索引时，需要遵循以下几个原则。（1）只为需要频繁查询的字段创建索引选择经常被查询的字段来建立索引可以提高查询效率。（2）数据量较小的表最好不要创建索引由于数据较少，查询花费的时间可能比遍历索引的时间还要短，因此索引可能不会产生优化效果。优化学生选课系统查询操作和性能7.2.1索引1.索引的概念（3）在不同值少的字段上不要建立索引尽量对不同值较多的字段创建索引，如“姓名”字段，因为大部分人的姓名都不同；不要对不同值很少的字段创建索引，如“性别”字段，因为性别只有“男”与“女”两个不同值，无须建立索引，如果建立索引，则不但不会提高查询效率，而且会严重降低更新速度。（4）一张表中的索引不是越多越好，需要限制索引的数量一个表中有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能。因为在更改表中数据的同时，索引也会进行调整和更新。（5）避免对经常更新的表建立过多的索引对于需要频繁进行插入、删除、修改操作的数据表，创建的索引越多，更新表所耗费的时间越长。优化学生选课系统查询操作和性能7.2.1索引2.索引的类型分类标准不同，索引的种类也不同，主要有两种分类标准，分别是按用途分类和按索引列的个数分类。根据索引的用途不同，MySQL中常见的索引大致分为5种，具体介绍如下。（1）普通索引（INDEX）普通索引是MySQL中的最基本的索引类型，由关键字KEY或INDEX定义，但INDEX是最常用的关键字，没有唯一性的限制，允许在定义索引的列中插入重复值和空值，该类型索引可以创建在任何数据类型中。（2）唯一索引（UNIQUE）唯一索引与普通索引的区别仅在于索引列的值必须是唯一的，不能重复，但允许有空值。创建唯一索引的关键字是UNIQUE。优化学生选课系统查询操作和性能7.2.1索引2.索引的类型（3）主键索引（PRIMARYKEY）主键索引是一种特殊的唯一索引，一个表只能有一个主键索引，与唯一索引的不同在于其索引列的值不能为空。在创建表时定义主键后自动创建主键索引，也可通过修改表的方法增加主键。创建主键索引的关键字是PRIMARYKEY，即主键。（4）全文索引（FULLTEXT）全文索引是指在定义索引的列上支持值的全文查找，允许在这些素引列中插入重复值和空值，但只能在CHAR、VARCHAR或TEXT类型的字段上创建全文索引。创建全文索引的关键字是FULLTEXT。（5）空间索引（SPATIAL）空间索引是指只能在空间数据类型的列上建立的索引。MySQL中空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。需要注意的是，创建空间索引的字段优化学生选课系统查询操作和性能7.2.1索引2.索引的类型必须声明为NOTNULL。创建空间索引的关键字是SPATIAL。根据索引列的个数不同，MySQL中的索引大致分为2种，具体介绍如下。（1）单列索引单列索引是指在表的单列上创建的索引，即单列索引只包含表中的一个列。一个表上可以建立多个单列索引。在表中的单个列上创建索引，单列索引只根据该列索引。单列索引可以是普通索引、唯一索引或全文索引，只要保证该索引只对应一个列即可。（2）多列索引多列索引是指在表的多个列上创建的索引。多列索引也称组合索引、复合索引，该索引指向创建时对应的多个列，可以通过这几个列进行查询。如果唯一索引是组合索引，则多个列的组合必须是唯一的。优化学生选课系统查询操作和性能7.2.1索引3.创建索引（1）使用SQL语句创建索引要想使用索引提高数据表的访问速度，首先必须创建索引，创建索引对MySQL数据库的高效运行来说是很重要的。创建索引是指在某个表的一列或多列上建立一个索引，MySQL提供了3种创建索引的方法，具体介绍如下。优化学生选课系统查询操作和性能7.2.1索引3.创建索引①

创建表的同时创建索引使用CREATETABLE语句在创建数据表的同时创建索引，其语法格式如下。CREATETABLE表名(

字段名1数据类型和长度1[完整性约束条件],…字段名n数据类型和长度n,[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[索引名](字段名[(长度)])[ASC|DESC])；优化学生选课系统查询操作和性能7.2.1索引3.创建索引参数说明如下。UNIQUE：表示创建唯一索引，在索引列中不能有相同的值存在。FULLTEXT：表示创建全文索引。SPATIAL：表示创建空间索引。[UNIQUE|FULLTEXT|SPATIAL]：方括号中的内容可以省略，表示创建普通索引；若不省略的话，UNIQUE|FULLTEXT|SPATIAL表示三者选一即可。INDEX|KEY：INDEX和KEY为同义词，表示索引，二者选一即可。别名：表示创建索引的名称。方括号中的内容可以省略，若不加此参数，则默认用创建索引的字段名作为该索引名称。优化学生选课系统查询操作和性能7.2.1索引3.创建索引参数说明如下。长度：表示字段中用于创建索引的长度。方括号中的内容可以省略，不加此参数，则默认用整个字段内容创建索引。ASC]DESC：表示创建索引时的排序方式，其中，ASC为升序排列，DESC为降序排列，默认为ASC。为便于说明，首先创建数据库lib_db，指定其字符集为utf8，排序规则为utf8_general_ci，随后在该数据库中创建对应的表。优化学生选课系统查询操作和性能7.2.1索引3.创建索引【例7.1】创建图书表books的同时创建普通索引。SQL语句如下。CREATETABLEbooks(

bookidVARCHAR(20)PRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(50)NOTNULL,publisherVARCHAR(50),quantityINTNOTNULL,INDEXidx_title(title)--普通索引：在title字段创建索引，加速按书名查询图书的速度)ENGINE=InnoDBCOMMENT='图书表';优化学生选课系统查询操作和性能7.2.1索引3.创建索引为便于说明，将例7.1中创建的book表删除。【例7.2】创建图书表books的同时创建唯一索引。SQL语句如下。CREATETABLEbooks(

bookidVARCHAR(20)PRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(50)NOTNULL,

isbnVARCHAR(20)NOTNULL,--假设添加ISBN字段publisherVARCHAR(50),quantityINTNOTNULL,UNIQUEINDEXidx_unique_isbn(isbn),--唯一索引：确保ISBN号不重复)ENGINE=InnoDBCOMMENT='图书表';优化学生选课系统查询操作和性能7.2.1索引3.创建索引为便于说明，将例7.2中创建的book表删除。【例7.3】创建图书表books的同时创建全文索引。SQL语句如下。CREATETABLEbooks(

bookidVARCHAR(20)PRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(50)NOTNULL,descriptionTEXT,--图书简介publisherVARCHAR(50),quantityINTNOTNULL,--全文索引：支持对书名、作者、简介进行全文检索FULLTEXTINDEXidx_fulltext_search(title,author,description))ENGINE=InnoDBCOMMENT='图书表';优化学生选课系统查询操作和性能7.2.1索引3.创建索引【例7.4】创建借阅记录表borrow_records的同时创建多列索引。SQL语句如下。CREATETABLEborrow_records(

recordidVARCHAR(20)PRIMARYKEY,

bookidVARCHAR(20)NOTNULL,

reader_idVARCHAR(20)NOTNULL,

borrow_dateDATENOTNULL,

return_dateDATE,--多列索引（组合索引）：加速按"书号+读者"的联合查询INDEXidx_composite_book_reader(bookid,reader_id),INDEXidx_borrow_date(borrow_date)--普通索引：加速按借阅日期查询)ENGINE=InnoDBCOMMENT='借阅记录表';优化学生选课系统查询操作和性能7.2.1索引3.创建索引②在已存在的表上创建索引使用CREATEINDEX语句在已经存在的表上创建索引，其语法格式如下。CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名ON表名(字段名[(长度)])[ASC|DESC])；优化学生选课系统查询操作和性能7.2.1索引3.创建索引为便于说明，将例7.3中创建的book表删除。【例7.5】图书表books表已存在，为加速按作者查询图书，在author字段上创建普通索引。SQL语句如下。图书表books已经用下面语句创建好。CREATETABLEbooks(

bookidVARCHAR(20)PRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(50)NOTNULL,publisherVARCHAR(50),quantityINTNOTNULL,INDEXidx_title(title))ENGINE=InnoDBCOMMENT='图书表';优化学生选课系统查询操作和性能7.2.1索引3.创建索引【例7.5】图书表books表已存在，为加速按作者查询图书，在author字段上创建普通索引。SQL语句如下。然后，在author字段上创建普通索引。CREATEINDEXidx_authorONbooks(author);优化学生选课系统查询操作和性能7.2.1索引3.创建索引【例7.6】读者表readers表已存在，为确保每个读者的联系电话不重复，在phone字段上创建唯一索引。SQL语句如下。读者表readers已经用下面语句创建好。CREATETABLEreaders(

reader_idVARCHAR(20)PRIMARYKEYCOMMENT'读者编号',

reader_nameVARCHAR(50)NOTNULLCOMMENT'读者姓名',phoneVARCHAR(20)COMMENT'联系电话（可选）')COMMENT='读者表';然后，在phone字段上创建唯一索引。CREATEUNIQUEINDEXidx_unique_phone

ONreaders(phone);优化学生选课系统查询操作和性能7.2.1索引3.创建索引为便于说明，将例7.4中创建的borrow_records表删除。【例7.7】borrow_records表已存在，为优化“按书号和读者联合查询”的性能，创建组合索引。SQL语句如下。借阅记录表borrow_records已经用下面语句创建好。CREATETABLEborrow_records(

recordidVARCHAR(20)PRIMARYKEY,

bookidVARCHAR(20)NOTNULL,

reader_idVARCHAR(20)NOTNULL,

borrow_dateDATENOTNULL,

return_dateDATE)ENGINE=InnoDBCOMMENT='借阅记录表';优化学生选课系统查询操作和性能7.2.1索引3.创建索引【例7.7】borrow_records表已存在，为优化“按书号和读者联合查询”的性能，创建组合索引。SQL语句如下。然后，在按书号和读者编号上创建组合索引。CREATEINDEXidx_book_reader

ONborrow_records(bookid,reader_id);优化学生选课系统查询操作和性能7.2.1索引3.创建索引③修改数据表的同时创建索引使用ALTERTABLE语句对已经存在的表进行修改的同时创建索引，其语法格式如下。ALTERTABLE表名ADD[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名(字段名[(长度)][ASC|DESC])；优化学生选课系统查询操作和性能7.2.1索引3.创建索引【例7.8】为加速按出版社查询图书，在图书表publisher字段上创建普通索引。SQL语句如下。ALTERTABLEbooksADDINDEXidx_publisher(publisher);说明：创建后，执行SELECT*FROMbooksWHEREpublisher='人民文学出版社';将利用该索引提升查询速度。优化学生选课系统查询操作和性能7.2.1索引3.创建索引（2）使用Navicat创建索引【例7.9】使用Navicat为分别图书分类表category的分类名称字段、书号创建唯一索引、普通索引。图书分类表结构如表7-4所示。优化学生选课系统查询操作和性能7.2.1索引3.创建索引（2）使用Navicat创建索引使用Navicat为图书分类表创建索引的步骤如下。步骤1：在

Navicat

左侧窗格中，打开对应连接“test”和数据库“library”，在library列表中可以看到“表”选项。步骤2：右击“表”选项，在弹