《数据分析与可视化实践（第三版）》数据查询

3．4数据查询3．4．1SQL语言概述1.SQL语言的产生及发展由Boyce等人在1974年提出结构化查询语言（structuredquerylanguage,SQL）经国际标准化组织（InternationalOrganizationforStandardization,ISO）指定为国际标准2.SQL语言的组成及特点（1）SQL语言的组成数据定义语言（datadefinitionlanguage,DDL），用于定义数据库的逻辑结构，包括基本表、索引和视图数据操纵语言（datamanipulationlanguage,DML），用于数据查询和数据更新（插入、删除和修改）数据控制语言（datacontrollanguage,DCL），用于对基本表和视图的授权、事务控制语句等（2）SQL语言的特点集数据的定义、操纵和控制功能于一体；面向集合的操作方式；高度非过程化，路径选择及处理过程由系统自动完成；既可独立使用，又可嵌入到高级语言中使用；只含九条核心语句，结构化很强；类似自然语言，易学易用。2.SQL语言的组成及特点3.SQL对关系模型的支持SQL视图1视图2基表1基表2基表3基表4存储文件1存储文件2外模式内模式模式注意：不同的RDBMS对于SQL的支持在具体方式上有所不同3．4．2SQL数据定义SQL的数据定义功能主要包括定义基表和定义索引。1.定义基表定义基表就是定义一个表（关系）的数据结构和完整性约束，包括指定表的名称、表的属性名称、属性的数据类型以及完整性约束条件。定义基表使用SQL中的CREATETABLE语句，其一般形式如下：CREATETABLE<表名>(

<列名1><数据类型>[<列级完整性约束条件>][,<列名2><数据类型>[<列级完整性约束条件>]]……[,<列名n><数据类型>[<列级完整性约束条件>]][,<表级完整性约束条件>]）；常用SQL数据类型完整性约束条件例3-13：创建“学生管理”数据库，包含STU、CLASS和SGRADE表，其中：STU表由学号、姓名、性别、系别、生日5个属性组成，主键为学号，姓名不能为空；CLASS表由课程编号、课程名称、先修课程编号、学时、学分5个属性组成，主键为课程编号，课程名称唯一；SGRADE表由学号、课程编号和成绩3个属性组成，主键为（学号，课程编号）。①创建STU表的SQL语句

CREATETABLESTU(

学号TEXT(7)PRIMARYKEY,

姓名TEXT(16)NOTNULL,

性别TEXT(2),

系别TEXT(10),

生日DATE);②创建CLASS表的SQL语句CREATETABLECLASS(

课程编号TEXT(3)PRIMARYKEY,

课程名称TEXT(20)UNIQUE,

先修课程编号TEXT(3),

学时SMALLINT,

学分SMALLINT);③创建SGRADE表的SQL语句CREATETABLESGRADE(

学号TEXT(7)REFERENCESSTU(学号),

课程编号TEXT(3)REFERENCESCLASS(课程编号),

成绩SMALLINT,PRIMARYKEY(学号,课程编号));添加自定义完整性约束条件的方法2.修改基表修改基表使用SQL中的ALTERTABLE语句。例3-14：对例3-13所创建的数据库做如下修改：（1）向STU表中添加联系电话字段；ALTERTABLESTUADD联系电话INT;（2）将联系电话字段的数据类型修改为字符串类型；ALTERTABLESTUALTER联系电话CHAR(11);（3）删除刚才添加的联系电话字段；ALTERTABLESTUDROP联系电话;注意：在对基表结构进行修改时需要先将基表关闭

3.删除基表

使用SQL中的DROPTABLE语句可以删除不需要的基表如果所要删除的基表中含有被其他基表引用的字段，需要先将其他基表中的REFERENCES约束删除，才能进行基表的删除DROPTABLE语句的一般形式为：

DROPTABLE<表名>;

例3-15：删除SGRADE表。

DROPTABLESGRADE;4.定义索引为了加快查询速度和有序输出，可以在一个基表上建立一个或多个索引（index）索引属于物理存储路径的概念，RDBMS在存取数据时会自动选择合适的索引作为存取路径。定义索引使用SQL中的CREATEINDEX语句，其一般形式如下：

CREATE[UNIQUE]INDEX<索引名>ON<基表名>(<列名1>[<次序>][,<列名2>[<次序>]]…);说明：（1）UNIQUE表示每一个索引值只对应唯一的数据记录；（2）<次序>用于指定索引的排列次序为升序还是降序，其中ASC为升序，DESC为降序，缺省值为ASC；（3）索引可以建立在表的一列或多列之上，各个列之间用逗号分隔开；（4）不必对主键建立索引。例3-16：在例3-13所创建的表上创建索引，使STU表中的记录按生日从小到大排序，使SGRADE表中的成绩按从大到小排序。CREATEINDEXS_生日ONSTU(生日);CREATEUNIQUEINDEXS_成绩ONSGRADE(成绩DESC);5.删除索引删除索引使用SQL中的DROPINDEX语句，其一般形式如下：

DROPINDEX<索引名>;例3-17：删除STU表上的索引S_生日。

DROPINDEXS_生日;3．4．3SQL数据查询数据查询是指从数据库中检索满足需要的数据。查询是数据库的核心操作。数据查询使用SQL中的SELECT语句，其一般形式如下：SELECT[ALL|DISTINCT]<目标列表达式1>[,<目标列表达式2>]…FROM<基表名或视图名1>[,<基表名或视图名2>]…[WHERE<记录过滤条件>][GROUPBY<列名1>[,<列名2>]…[HAVING<小组过滤条件>]][ORDERBY<列名1>[ASC|DESC][,<列名2>[ASC|DESC]]…];1.单表查询（1）选择表中的若干列（投影）例3-18：查询STU表中全体学生的全部信息。SELECT*FROMSTU;等价于：SELECT学号,姓名,性别,系别,生日FROMSTU;例3-19：查询学生的学号和性别。SELECT学号,性别FROMSTU;例3-20：查询学生的姓名、性别和年龄。SELECT姓名,性别,Year(Now())-Year(生日)AS

年龄FROMSTU;（2）选择表中的若干行（选择）选择表中的指定元组通过在SQL中的WHERE子句中设定记录过滤条件实现记录过滤条件是由逻辑运算符AND、OR、NOT连接的关系表达式例3-21：查询所有女生信息。SELECT*FROMSTUWHERE性别=“女”;例3-22：查询年龄在18岁以上的女生信息。SELECT*FROMSTUWHERE性别=“女”AND(Year(Now())-Year(生日))>18;例3-23：查询学时数介于40～60之间的课程编号和课程名称。SELECT课程编号,课程名称FROMCLASSWHERE学时BETWEEN40AND60;等价于：SELECT课程编号,课程名称FROMCLASSWHERE学时>=40AND学时<=60;【例3-24】查询学时数大于60或小于40的课程编号和课程名称。SELECT课程编号,课程名称FROMCLASSWHERE学时NOTBETWEEN40AND60;等价于：SELECT课程编号,课程名称FROMCLASSWHERE学时<40OR学时>60;【例3-25】查询至少选修了1号、3号或5号课程中任意一门课程的学生的学号。SELECT学号FROMSGRADEWHERE课程编号IN(“1”,”3”,”5”);等价于：SELECT学号FROMSGRADEWHERE课程编号=“1”OR课程编号=“3”OR课程编号=“5”;【例3-26】查询既不是计算机系也不是物理系和电子系的学生的学号和姓名。SELECT学号,姓名FROMSTUWHERE系别<>’物理’AND系别<>’电子’AND系别<>’计算机’;等价于：SELECT学号,姓名FROMSTUWHERE系别NOTIN(’物理’,’电子’,’计算机’);思考：查询没有选修1号、3号或5号课程中任何一门的学生的学号，能用下面的语句选择么？SELECT学号FROMSGRADEWHERE课程编号NOTIN(‘1’,’3’,’5’);【例3-27】查询系别中含有“理”字的学生的学号、姓名和系别。SELECT学号,姓名,系别FROMSTUWHERE系别LIKE'*理*';说明：在SQL语句中用LIKE运算符进行模糊查找，一般结合通配符使用。在Access中的通配符有两种：?（问号）匹配任意单个字符。*（星号）匹配任意长度字符。【例3-28】查询无需先修课程的课程的编号和名称。SELECT课程编号,课程名称FROMCLASSWHERE先修课程编号ISNULL;（3）排序查询【例3-29】：查询选修了4号课程的学生的学号和成绩，查询结果按成绩降序排列。SELECT学号,成绩FROMSGRADEWHERE课程编号=’4’ORDERBY成绩DESC;（4）屏蔽重复项查询【例3-30】：查询所有被选修了的课程的课程编号。SELECTDISTINCT课程编号FROMSGRADE;（5）数据常量使用说明在Access系统中：①字符类型的数据常量在使用时两端加英文单引号或双引号皆可；②日期型和时间型常量使用时两段加#号。例如：#2006-3-8#，#18:15:00#;③货币类型常量使用时和数值型常量相同，不需添加标记。2.使用集函数和分组查询（1）集函数【例3-31】：查询男生总人数。SELECTCOUNT(*)AS男生人数FROMSTUWHERE性别=’男’;【例3-32】：查询4号课程的最高分、最低分和平均分。SELECTMAX(成绩),MIN(成绩),AVG(成绩)FROMSGRADEWHERE课程编号=’4’;（2）分组查询【例3-33】：查询男生和女生的人数。SELECT性别,COUNT(*)AS人数FROMSTUGROUPBY性别;【例3-34】：查询不同系别的男、女生人数。SELECT系别,性别,COUNT(*)AS人数FROMSTUGROUPBY系别,性别;（3）HAVING小组筛选【例3-35】：查询选修了2门以上课程的学生的学号。SELECT学号FROMSGRADEGROUPBY学号HAVINGCOUNT(课程编号)>=2;【例3-36】：查询平均分在90以上且每门功课的成绩都在80分以上的学生的学号。SELECT学号FROMSGRADEGROUPBY学号HAVINGAVG(成绩)>90ANDMIN(成绩)>=80;【例3-37】：查询不及格门数在2门以上的学生的学号。SELECT学号FROMSGRADEWHERE成绩<60GROUPBY学号HAVINGCOUNT(课程编号)>=2;3.连接查询（1）使用场合当查询条件或结果涉及到多个表时，需要将多个表连接起来进行联合查询。（2）连接条件两个表进行连接只有当公共列存在时才具有实际意义。通常情况下，两表通过外键和被参照表的主键发生联系。如果外键和被参照表的主键同名，为示区别，引用时必须在列名前加上表名作为前缀，例如

STU.学号

SGRADE.学号。（3）执行过程将表1的每一个元组与表2的每一个元组逐一交叉匹配，满足连接条件时将两表元组拼接，形成临时表，再对该临时表用单表查询的方法进行查询。（4）一般形式连接查询的一般形式如下：SELECT[ALL|DISTINCT]<目标列表达式1>[,<目标列表达式2>]…FROM<基表名或视图名1>INNERJOIN<基表名或视图名2>ON连接条件…[WHERE<记录过滤条件>]或者用下面的形式：SELECT[ALL|DISTINCT]<目标列表达式1>[,<目标列表达式2>]…FROM<基表名或视图名1>,<基表名或视图名2>[,<基表名或视图名3>]…WHERE<连接条件>【例3-38】：查询所有选修了课程的学生的学号、姓名、课程编号和成绩。SELECTSTU.学号,姓名,课程编号,成绩FROMSTUINNERJOINSGRADEONSTU.学号=SGRADE.学号;

也可以用下面的形式表述连接条件：SELECTSTU.学号,姓名,课程编号,成绩FROMSTU,SGRADEWHERESTU.学号=SGRADE.学号;【例3-39】：查询选修了“操作系统”课程的学生的姓名和该门课的成绩。SELECT姓名,成绩FROMSTUINNERJOIN(CLASSINNERJOINSGRADEONCLASS.课程编号=SGRADE.课程编号)

ONSTU.学号=SGRADE.学号WHERE课程名称="操作系统";也可以用下面的形式：SELECT姓名,成绩FROMSTU,CLASS,SGRADEWHERECLASS.课程编号=SGRADE.课程编号ANDSTU.学号=SGRADE.学号

AND课程名称="操作系统";例3-40：查询选修了3门以上课程的学生的姓名。SELECT姓名FROMSTUINNERJOINSGRADEONSTU.学号=SGRADE.学号GROUPBYSTU.姓名HAVINGCount(课程编号)>=3;4.嵌套查询在一个SELECT语句的FROM、WHERE或HAVING子句中嵌入另一个SELECT语句，称为嵌套查询或子查询。外层的查询称为父查询，内层的查询称为子查询：嵌套查询执行时由内向外进行，即把子查询运行结果作为父查询的数据源或查询条件；嵌套查询可以多层嵌套，适合用于解决复杂的查询问题，体现了“结构化”的特点；子查询向父查询返回结果时，根据实际需要，可以只返回一次结果值，也可以反复执行。（1）子查询处理单次例3-43：查询选修了课程的学生总数。为避免重复统计选课人数，需要在计数时使用DISTINCT短语，但在Access中不支持集函数中的DISTINCT短语，所以不能用以下语句：SELECTCOUNT(DISTINCT学号)AS选课人数FROMSGRADE

而要使用如下的子查询：SELECTCOUNT(学号)AS选课人数FROM(SELECTDISTINCT学号FROMSGRADE)例3-44：查询选修了3门以上课程的学生的姓名。SELECT姓名FROMSTUWHERE学号IN(SELECT学号FROMSGRADEGROUPBY学号HAVINGCount(课程编号)>=3;)例3-45：选修了“操作系统”课程的学生的姓名。SELECT姓名FROMSTUWHERE学号IN(SELECT学号

FROMSGRADEWHERE课程编号=(SELECT课程编号FROMCLASSWHERE课程名称="操作系统"))注意：涉及多表查询时，有时既可以用连接查询，也可以用嵌套查询，例3-40和例3-44就是同一个题目的两种不同解法。但是当查询结果所需字段来自多个表时，就不能用子查询而只能用连接查询，比较例3-45和例3-39，后者就不能用子查询实现。另一些查询则不能用连接查询而只能用子查询。例如，查询没有选修1号、3号或5号课程中任何一门的学生的姓名，就不能用连接查询，而只能用如下的子查询：SELECT姓名FROMSTUWHERE学号NOTIN(SELECT学号FROMSGRADEWHERE课程编号IN("1","3","5"))3．4．4SQL数据更新1．插入数据插入数据使用SQL中的INSERT语句，其一般形式如下：INSERTINTO<表名>[(<属性列1>[,<属性列2>…])]VALUES(<常量1>[,<常量2>…]);（1）插入一个元组例：将一条学生记录（0601025，李铭，男，地理，1987-7-5）插入STU表。INSERTINTOSTUVALUES("0601025","李铭","男","地理",#87-7-5#)例：如图所示的记录插入CLASS表：INSERTINTOCLASS(课程编号,课程名称,学时,学分)VALUES("2","高等数学",108,6);

或者采用下面的语句插入:INSERTINTOCLASSVALUES("2","高等数学",NULL,108,6);（2）插入子查询结果INSERT语句可以与查询语句结合起来，将数据成批地插入到表中，一般格式如下：INSERTINTO<表名>[(<属性列1>[,<属性列2>…])]子查询;例：创建一个平均成绩表T_AVG，包含学生学号（长度为7个字符）和平均成绩（单精度数值型），并插入相应数据。首先创建平均成绩表：CREATETABLET_AVG(学号TEXT(7)，平均成绩SINGLE);再用子查询插入数据值：INSERTINTOT_AVG(学号,平均成绩)SELECT学号,AVG(成绩)FROMSGRADEGROUPBY学号;2.删除数据删除数据使用SQL中的DELETE，一般格式如下：DELETEFROM<表名>[WHERE<条件>]

注意，在SQL中不存在“逻辑删除”和“物理删除”，DELETE语句所做的就是真正的删除。（1）删除一个元组例：从SGRADE表中删除0601025学生1号选修课记录。DELETEFROMSGRADEWHERE学号=”0601025”AND课程编号=”1”（2）删除多个元组例：从SGRADE表中删除所有1号课程的相关记录。DELETEFROMSGRADEWHERE课程编号=”1”（3）用子查询表达删除条件例3-55：删除SGRADE表中“数据结构”课程的所有选课记录。DELETEFROMSGRADEWHERE课程编号=(SELECT课程编号FROMCLASSWHERE课程名称=”数据结构”)3.修改数据修改数据使用SQL中的UPDATE，一般格式如下：

UPDATE<表名> SET<列名1>=<表达式1>[,<列名2>=<表达式2>…] [WHERE<条件>]

（1）修改一个元组例：将0601025学生3号选修课成绩改为99分。UPDATESGRADESET成绩=99WHERE学号=”0601025”AND课程编号=”3”（2）修改多个元组例：将所有课程的学时加2学时。UPDATECLASSSET学时=学时＋2（3）用子查询表达修改条件例3-58：将所有学生的“高等数学”课程的成绩设置为零。UPDATESGRADESET成绩=0WHERE课程编号=(SELECT课程编号FROMCLASSWHERE课程名称=”高等数学”);3．4．4其他SQL功能（1）视图的定义SQL语言中的视图定义语句的一般格式为：CREATEVIEW<视图名>[(<列名1>[,<