第5章 SQL查询语句基础

1、第 5_2 章 SQL查询语句根底 5.1 SELECT 条件列表5.2 FROM子句5.3 WHERE子句5.4 统计5.5 利用查询结果创立新表5.6 使用UNION运算符实现多查询联合5.7 连接5.8 子查询 本章要点：掌握简单的Transact-SQL查询语句。学会对查询结果进行分组统计、合计和排序等复杂的SELECT语句操作。5.1 SELECT 条件列表一、根本结构SELECT 记录显示范围 字段列表 INTO 新表名 FROM 表名或表名列表及其连接方式 WHERE 条件表达式 GROUP BY 分组字段名列表 HAVING 分组条件表达式 ORDER BY 排序字段名列表 A

2、SC | DESC COMPUTE 集合函数(列名1) BY 列名2 n 说明：SELECT语句中各子句的顺序： SELECT 记录范围 字段列表 INTO FROM WHERE GROUP BY HAVING ORDER BY COMPUTE lFROM用于指定数据来源： 单表查询简单格式：FROM 表名 多表查询时的格式：FROM 表名列表及其连接方式lCOMPUTE子句不能与INTO子句或GROUP BY子句同时使用。SELECT 条件列表(1) SELECT子句：指明目标列字段、表达式、函数表达式、常量。根本表中相同的列名表示为：表名.列名(2) FROM子句：指明数据源。表间用“，分

3、割。数据源不在当前数据库中，使用“数据库名.表名表示。一表多用，用别名标识。定义表别名：表名别名(3) WHERE子句：元组选择条件。(4) GROUP BY子句：结果集分组。当目标列中有统计函数，那么统计为分组统计，否那么为对整个结果集统计。子句后带上HAVING子句表达组选择条件带函数的表达式。(5) ORDER BY子句：排序。当排序要求为ASC时升序排序；排序要求为DESC时降序排列。2. SELECT语句的操作符(1) 算术操作符+加号、减号、*乘号和 /除号。(2) 比较操作符=等于、大于、小于、=大于等于、!=不等于、小于大于、!不大于和 !、=、90;自连接自内连接简称自连接，

4、是一张表自己对自己的内连接，即在一张表的两个副本之间进行内连接。用自连接可以将同一个表的不同行连接起来。 使用自连接时，必须为两个副本指定别名，使之在逻辑上成为两个表。 语法格式： SELECT 列名列表 FROM 表名 AS 别名1 join 表名.别名2 ON 别名1.列名=别名2.列名查询每一门课的间接先行课。例如，课程表中的先行课是在上学期应开设的，先行课的先行课，即间接先行课应提前一学年开设。如果求查询某门课的间接先行课或全部课程的间接先行课，就需要对课程表进行自身连接。SELECT A.课程号,A.课程名,B.先行课 FROM 课程例子课程号课程名先行课C1计算机引论C2PASCA

5、L语言C1C3数据结构C2C4数据库C3C5软件工程C4课程的先行关系链为：C5C4C3C2C1，课程的间接关系链为：C5C3C1。 课程号课程名先行课课程号课程名先行课C1计算机引论C1计算机引论C2Pascal语言C1C2Pascal语言C1C3数据结构C2C3数据结构C2C4数据库C3C4数据库C3C5软件工程C4C5软件工程C4A.课程号A.课程名B.先行课C2Pascal语言C3数据结构C1C4数据库C2C5软件工程C3AB结果【例5-18】自连接虽然使用一个表但有两个拷贝，在逻辑上是两个表而且字段完全相同，因此字段列表中字段名必须加上其中一个表的别名做前缀。使用自连接会产生许多重复

6、行，一般加关键字DISTINCT过滤掉重复行。自连接默认按ON使用的连接字段排序供货商ID，货号，为了按厂家顺序再按进货日期排序，本例使用了ORDER BY指定排序。由于使用了DISTINCT，所以不允许使用字段列表没有指定的“g1.进货日期排序，也不允许使用别名“供货日期进行排序，本例使用了字段列表中的表达式。(3) 外部连接左外部连接操作是在结果集中保存连接表达式左表中的非匹配记录；右外部连接操作是在结果集中保存连接表达式右表中的非匹配记录。外部连接符号为“*=，右外部连接符号为“=*。外部连接中不匹配的分量用NULL表示。职工号姓名性别年龄所在部门部门号部门名称电话1010李勇男2011

7、11生产科5661011刘晨女1912计划科5781012王敏女221213一车间4671014张立男211314科研所职工表 部门表 连接的结果集 职工号姓名性别年龄所在部门部门名称电话1010李勇男2011生产科5661012王敏女2212计划科5781014张立男2113一车间467职工号姓名性别年龄所在部门部门名称电话1010李勇男2011生产科5661011刘晨女191012王敏女2212计划科5781014张立男2113一车间467内连接的结果集 左外部连接的结果集 【例5-19】内连接： SELECT 职工.*，部门名称， FROM 职工，部门 WHERE 职工.所在部门= 部门

8、.部门号；左外部连接： SELECT 职工.*，部门名称， FROM 职工，部门 WHERE 职工.所在部门*= 部门.部门号；右外部连接： SELECT 职工.*，部门名称， FROM 职工，部门 WHERE 职工.所在部门 =*部门.部门号； 用SQL表达职工和部门之间的内连接、左外部连接和右外部连接的语句5.8 子查询(嵌套查询)子查询是嵌套在另一查询中的 Select-From-Where 表达式Where/HavingSQL允许多层嵌套，由内而外地进行分析，子查询的结果作为父查询的查找条件可以用多个简单查询来构成复杂查询，以增强SQL的查询能力子查询中不使用 Order By 子句，

9、Order By子句只能对最终查询结果进行排序子查询(嵌套查询)我们可能会提出这样的问题，在雇员中谁的工资最高，或者谁的工资比赵军的高。通过把一个查询的结果作为另一个查询的一局部，可以实现这样的查询功能。具体的讲：要查询工资高于赵军的雇员的名字和工资，必须通过2个步骤来完成，第一步查询雇员赵军的工资，第二步查询工资高于赵军的雇员。第一个查询可以作为第二个查询的一局部出现在第二个查询的条件中，这就是子查询。出现在其他查询中的查询称为子查询，包含其他查询的查询称为主查询。子查询(嵌套查询)子查询一般出现在SELECT语句的WHERE子句中。子查询比主查询先执行，结果作为主查询的条件。在书写上要用圆

10、括号扩起来，并放在比较运算符的右侧。子查询可以嵌套使用，最里层的查询最先执行。子查询可以在SELECT、INSERT、UPDATE、DELETE等语句中使用。查找年龄比李明大的同学SELECT 姓名,年龄 FROM 学生WHERE 年龄(SELECT 年龄 FROM 学生 WHERE 姓名=李明);执行过程分两步：1、 SELECT 年龄 FROM 学生 WHERE 姓名=李明); 得出年龄=212、SELECT 姓名,年龄 FROM 学生 WHERE 年龄21使用IN操作符的嵌套查询【例5-20】求选修了高等数学的学生学号和姓名。 SELECT 学号,姓名 FROM 学生 WHERE 学号

11、IN ( SELECT 学号 FROM 选课 WHERE 课程号 IN ( SELECT 课程号 FROM 课程 WHERE 课程名=高等数学);该题也可以使用下面的连接查询表达。 SELECT 学生.学号，姓名 FROM 学生，课程，选课 WHERE 学生.学号=课程.学号 AND 课程.课程号=选课.课程号 AND 课程.课程名=高等数学；(2) 使用比较符的嵌套查询【例5-21】求C1课程的成绩高于张三的学生学号和成绩。SELECT 学号,成绩 FROM 选课 WHERE 课程号=C1 AND 成绩 ( SELECt 成绩 FROM 选课 WHERE 课程号=C1 AND 学号= (SE

12、LECT 学号 FROM 学生 WHERE 姓名=张三);(3) 使用ANY或ALL操作符的嵌套查询格式为：字段比较符ANY|ALL子查询操作符语意ANY大于子查询结果中的某个值，即表示大于查询结果中最小值ALL大于子查询结果中的所有值，即表示大于查询结果中最大值ANY小于子查询结果中的某个值，即表示小于查询结果中最大值=ANY大于等于子查询结果中的某个值，即表示大于等于结果集中最小值=ALL大于等于子查询结果中的所有值，即表示大于等于结果集中最大值=ANY小于等于子查询结果中的某个值，即表示小于等于结果集中最大值=ALL小于等于子查询结果中的所有值，即表示小于等于结果集中最小值=ANY等于子

13、查询结果中的某个值，即相当于IN=ALL等于子查询结果中的所有值(通常没有实际意义)!=(或)ANY不等于子查询结果中的某个值，!=(或)ALL不等于子查询结果中的任何一个值，即相当于NOT IN例子【例5-22】求其他系中比计算机系某一学生年龄小的学生。 SELECT * FROM 学生 WHERE 年龄 ANY (SELECT 年龄 FROM 学生 WHERE 所在系=计算机系) AND 所在系计算机系;【例5-23】求其他系中比计算机系学生年龄都小的学生SELECT * FROM 学生 WHERE 年龄 ALL (SELECT 年龄 FROM 学生 WHERE 所在系=计算机) AND

14、所在系 计算机;嵌套查询分类不相关子查询子查询的查询条件不依赖于父查询相关子查询子查询的查询条件依赖于父查询嵌套查询求解方法不相关子查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。相关子查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，假设WHERE子句返回值为真，那么取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止引出子查询的谓词带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询一、带有IN谓词的子查询【例5-20】求选修了

15、高等数学的学生学号和姓名。 SELECT 学号,姓名 FROM 学生 WHERE 学号 IN ( SELECT 学号 FROM 选课 WHERE 课程号 IN ( SELECT 课程号 FROM 课程 WHERE 课程名=高等数学);不相关子查询四、带有EXISTS谓词的子查询1. EXISTS谓词存在量词 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true或逻辑假值“false。假设内层查询结果非空，那么返回真值假设内层查询结果为空，那么返回假值由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2. NOT

16、 EXISTS谓词(4) 使用EXISTS操作符的嵌套查询【例5-24】求选修了C2课程的学生姓名。 SELECT 姓名 FROM 学生 WHERE EXISTS (SELECT * FROM 选课 WHERE 学生.学号=学号 AND 课程号=C2)思路分析： 本查询涉及学生和选课关系。 在学生中依次取每个元组的学号的值，用此值去检查选课关系。 假设选课中存在这样的元组，其学号的值等于此学生.学号值，并且其课程号= C2，那么取此学生.姓名送入结果关系。【例5-25】求没有选修C2课程的学生姓名。 SELECT 姓名 FROM 学生 WHERE NOT EXISTS ( SELECT * F

17、ROM 选课 WHERE =学号 AND 课程号=C2);用等值连接实现SELECT 姓名 FROM 学生 WHERE EXISTS (SELECT * FROM 选课 WHERE 学生.学号=学号 AND 课程号=C2);3. 不同形式的查询间的替换一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。例：例37查询与“李明在同一个系学习的学生。可以用带EXISTS谓词的子查询替换：SELECT 学号,姓名,所在系 FROM 学生 s1 WHERE exists (SE

18、LECT * FROM 学生 s2 WHERE s2.所在系 = s1.所在系 AND s2.姓名=李明)SELECT s1.学号,s1.姓名,s1.所在系 FROM 学生 s1, 学生 s2 WHERE s2.所在系 = s1.所在系 AND s2.姓名=李明例子【例5-26】查询选修了全部课程的学生的姓名。 SELECT 姓名 FROM 学生 WHERE NOT EXISTS (SELECT * FROM 课程 WHERE NOT EXISTS (SELECT * FROM 选课 WHERE 学生.学号=学号 AND 课程.课程号=课程号)；第一个not exists表示不在这些课程记录,

19、第二个not exists表示不存在这样的选课记录演变成:查询这样的学生姓名,没有一门课是他不选的.SELECT * FROM 选课 xk ,学生 xs,课程 kc WHERE xs.学号=xk.学号 AND kc.课程号=xk.课程号例子【例5-27】求选修了学号为“S2的学生所选修的全部课程的学生学号和姓名。 SELECT 学号，姓名 FROM 学生 WHERE NOT EXISTS (SELECT * FROM 选课 选课1 WHERE 选课1.学号=S2 AND NOT EXISTS (SELECT * FROM 选课 选课2 WHERE 学生.学号=选课2.学号 AND 选课2 .课

20、程号=选课1.课程号)；查询学生X选修的课程Z和S2学生选修的课程Y,并要求Z中包括全部的Y.不存在这样的课程y，学生S2选修了y，而学生x没有选。5.4 统计 把一列中的值进行聚合运算，返回单值的函数五个预定义的聚合函数平均值：AvgALL|DISTINCT总和： SumALL|DISTINCT最小值：MinALL|DISTINCT最大值：MaxALL|DISTINCT计数： CountALL|DISTINCTCount(*)、Count(Distinct)在SELECT语句中，可以使用聚合函数、行聚合函数、GROUP BY子句和COMPUTE子句对查询结果进行统计。GROUP BY子句可与

21、行聚合函数或聚合函数一起使用。COMPUTE子句只能与行聚合函数一起使用。GROUP BY子句ROUP BY子句将一列或多列定义为一组，使得组内所有的行在某些列中的数值都相同。查询有些什么系?SELECT 所在系 FROM 学生 group by 所在系例如在SELECT语句中，也可以单纯使用聚合函数而不使用GROUP BY子句和COMPUTE子句进行统计，这时，它将所有符合条件的数据统计在一起，形成一行统计数据，这种统计方法叫做标量统计。【例5-31】求学生的总人数。 SELECT COUNT (*) FROM 学生;【例5-32】求选修了课程的学生人数。 SELECT COUNT(DIST

22、INCT 学号) FROM 选课;例：李明同学的成绩最高分，最低分，总分和平均分。SELECT max(成绩),min(成绩),avg(成绩) FROM 学生 st ,选课 kc where st.学号=kc.学号 and 姓名=李明组函数分组函数中SUM和AVG只应用于数值型的列;MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值。统计函数使用GROUP BY 从句可以对数据进行分组。所谓分组就是按照列的相同内容，将记录划分成组，对组可以应用组函数。在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非NULL值可重复进行运算COUNT除外。DIST

23、INCT 表示对每一个非NULL值，如果存在重复值组函数只运算一次。如果不指明上述关键字，默认为ALL。 分组统计结果限定 在使用GROUP BY子句时，还可以用HAVING子句为分组统计进一步设置统计条件，HAVING子句与GROUP BY子句的关系和WHERE子句与SELECT子句的关系类似。HAVING子句可以参照选择列表中的任一项，在HAVING子句中还可以使用逻辑运算符连接多个条件。最多为128个。HAVING从句过滤分组后的结果，HAVING从句只能出现在GROUP BY从句之后。而WHERE从句要出现在GROUP BY从句之前。WHERE 是在对记录分组之前过滤不满条件的记录HA

24、VING是过滤掉整个分组,不满足条件的组不要分组统计【例5-33】求课程和选修该课程的人数。 SELECT 课程号,COUNT(学号) as 选修课程人数 FROM 选课 GROUP BY 课程号;【例5-34】求选修课超过3门课的学生学号。 SELECT 学号 FROM 选课 GROUP BY 学号 HAVING COUNT(*)2;训练查找图书类别，要求类别中最高图书定价不低于全部按类别分组的平均定价的2倍。 SELECT A.* FROM 图书 AGROUP BY A.类别 HAVING MAX(A.定价)=ALL(SELECT 2*AVG(B.定价) FROM 图书 B GROUP B

25、Y B.类别)训练SELECT count (*) , 类别 FROM 图书 GROUP BY 类别 SELECT AVG(定价) FROM 图书 GROUP BY 类别SELECT count (*) , avg(定价),类别 FROM 图书 GROUP BY 类别 SELECT count (*) , avg(定价), 类别 FROM 图书 GROUP BY 类别 having avg(定价)202求机械工业出版社出版的各类图书的平均定价，用GROUP BY表示。SELECT 类别,AVG(定价)平均价 FROM 图书WHERE 出版社=机械工业出版社GROUP BY 类别 ORDER B

26、Y 类别 ASC使用compute格式：compute 集合函数(列名1), By 列名2 ,.功能：先按列名2分类显示参加汇总记录的详细信息，再在附加行中显示对列名1的汇总值(单用集合函数或GROUP BY仅显示统计汇总值)。COMPUTE子句可以指定多个集合函数，但不允许指定列别名。SELECT 指定的字段列表是显示详细信息使用的字段，必须包含COMPUTE子句集合函数使用的列名1，与BY分组字段列名2无关，也可以使用*表示全部字段。COMPUTE子句不带BY表示对全部记录统计，相当于在SELECT查询结果后面带一个统计值的后缀。本卷须知COMPUTE子句带BY子句时表示分组统计，必须配合

27、ORDER BY排序子句使用，且紧跟ORDER BY之后。BY后的列名2是要分组的字段相当于GROUP BY，可以不在SELECT指定的字段中，但必须包含在ORDER BY子句中，而且必须是第一顺序。BY指定多个字段分组时，也必须与ORDER BY的第一顺序一致。COMPUTE子句不能与INTO子句或GROUP BY子句同时使用。一个SELECT语句中可使用多个COMPUTE子句，一个子句显示一个附加行，多个子句时BY分组字段必须一致，且与ORDER BY一致，子句之间不能使用逗号。select sum(定价) from 图书 group by 类别select * from 图书 compu

28、te sum(定价) select * from 图书 order by 类别 compute sum(定价) select * from 图书 order by 类别 compute sum(定价) by 类别3列出计算机类图书的书号、名称及价格，最后求出册数和总价格SELECT 书号,书名,定价 FROM 图书WHERE 类别=计算机 ORDER BY 书号 ASCCOMPUTE count (书号),SUM(定价)列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册数和总价格。SELECT书号，书名，定价 FROM 图书WHERE 类别=计算机类ORDER B

29、Y出版社CONPUTE COUNT*，SUM定价BY 出版社COMPUTE COUNT*，SUM定价6.6 使用UNION运算符实现多查询联合UNION运算符可以将两个或两个以上的查询结果合并成一个结果集合显示。UNION运算符的语法格式为： 查询1 UNION ALL 查询n ORDER BY 子句COMPUTE 子句其中查询的格式为： SELECT select_listINTO子句FROM子句WHERE子句 GROUP BY 子句HAVING 子句操作描述UNION并集，合并两个操作的结果，去掉重复的部分。UNION ALL并集，合并两个操作的结果，保留重复部分。(所有的)MINUS差集

30、，从前面的操作结果中去掉与后面操作结果相同的部分。INTERSECT交集，取两个操作结果中的相同的部分。UNION 运算符使用准那么在使用 UNION 运算符组合的语句中，所有选择列表的表达式数目必须相同列名、算术表达式、聚合函数等。在使用 UNION 组合的结果集中的相应列、或个别查询中使用的任意列的子集必须具有相同数据类型，并且两种数据类型之间必须存在可能的隐性数据转换，或提供了显式转换。例如，在 datetime 数据类型的列和 binary 数据类型的列之间不可能存在 UNION 运算符，除非提供了显式转换，而在 money 数据类型的列和 int 数据类型的列之间可以存在 UNION

31、 运算符，因为它们可以进行隐性转换。用 UNION 运算符组合的各语句中对应的结果集列出现的顺序必须相同，因为 UNION 运算符是按照各个查询给定的顺序逐个比较各列。4. 组合查询【例5-28】求选修了C1课程或选修了C2课程的学生学号。SELECT 学号 FROM 选课 WHERE 课程号=C1 UNION SELECT 学号 FROM 选课 WHERE 课程号=C2【例5-29】求选修C1课程，并且也选修C2课程的学生学号。 SELECT 学号 FROM 选课 WHERE 课程号=C1 INTERSECT SELECT 学号 FROM 选课 WHERE 课程号=C2；查询计算机类和机械工

32、业出版社出版的图书。SELECT * FROM 图书WHERE 类别=计算机类UNION ALLSELECT * FROM 图书WHERE 出版社=机械工业出版社【例5-30】求选修了C1课程但没有选修C2课程的学生学号。SELECT 学号 FROM 选课 WHERE 课程号=C1 MINUSSELECT 学号 FROM 选课 WHERE 课程号=C2；本例也可以用下面的EXISTS嵌套查询表示。 SELECT 学号 FROM 选课 选课1 WHERE 课程号=C1 AND NOT EXISTS (SELECT 学号 FROM 选课 选课2 WHERE 选课1.学号=选课2.学号 AND 选课

33、2.课程号=C2);5.4 数据更新语句5.4.1 数据插入语句1. 使用常量插入单个元组格式为： INSERT INTO表名(属性列1，属性列2) VALUES (常量1，常量2)；例子【例5-35】将一个新学生记录(学号：98010，姓名：张三，年龄：20，所在系：计算机系 )插入到学生表中。INSERTINTO 学生VALUES (98010，张三，20，计算机系)；【例5-36】插入一条选课记录(学号：98011，课程号：C10，成绩不详)。 INSERT INTO 选课 (学号，课程号) VALUES (98011，C10)；2. 在表中插入子查询的结果集INSERT INTO表名(

34、属性列1，属性列2) 子查询；【例5-37】求每个系学生的平均年龄，把结果存入数据库中。 CREATE TABLE 系平均年龄 (系名称 CHAR(20), 平均年龄 SMALLINT); INSERT INTO 系平均年龄 SELECT 所在系,AVG(年龄) FROM 学生 GROUP BY 所在系;select * from 系平均年龄5.4.2 数据修改语句UPDATE表名SET列名=表达式，列名=表达式，nWHERE条件；【例5-38】将学生表中全部学生的年龄加上2岁。 UPDATE 学生 SET 年龄=年龄+2；【例5-39】将选课表中的数据库课程的成绩乘以。 UPDATE 选课 SET 成绩= 成绩*1.2 WHERE 课程号= (SELECT 课程号 FROM 课程 WHERE 课程