《第3章SQL语言ppt课件》由会员分享，可在线阅读，更多相关《第3章SQL语言ppt课件（84页珍藏版）》请在金锄头文库上搜索。
1、第第3章章SQL语言语言1本章概要本章概要SQL是结构化查询语言（是结构化查询语言（StructuredQueryLanguage）的缩写，其功能包括数据查询、数据操纵、的缩写，其功能包括数据查询、数据操纵、数据定义和数据控制四个部分。数据定义和数据控制四个部分。SQL语言简洁、方便实用、功能齐全，已成为目前应语言简洁、方便实用、功能齐全，已成为目前应用最广的关系数据库语言。用最广的关系数据库语言。22.1SQL语言的基本概念与特点语言的基本概念与特点一、主要特点一、主要特点1.SQL语言类似于英语的语言类似于英语的自然语言自然语言，简洁易用简洁易用。2.SQL语语言言是是一一种种非非过过程程
2、语语言言，即即用用户户只只要要提提出出“干干什什么么”即即可可，不不必必管管具具体体操操作作过过程程，也也不不必必了了解解数数据据的的存取路径，只要指明所需的数据即可。存取路径，只要指明所需的数据即可。3.SQL语语言言是是一一种种面面向向集集合合的的语语言言，每每个个命命令令的的操操作作对对象是一个或多个关系，结果也是一个关系。象是一个或多个关系，结果也是一个关系。4.SQL语语言言既既是是自自含含式式语语言言，又又是是嵌嵌入入式式语语言言。可可独独立立使用，也可嵌入到宿主语言中。使用，也可嵌入到宿主语言中。自含式语言可以独立使用交互命令，适用于终端用户、应用程序员和DBA；嵌入式语言使其嵌
3、入在高级语言中使用，供应用程序员开发应用程序。3二、基本概念二、基本概念基基本本表表（BASETABLE）：是是独独立立存存在在的的表表，不不是是由由其其它它的的表表导导出出的的表表。一一个个关关系系对对应应一一个个基基本本表表，一一个个或多个基本表对应一个存储文件。或多个基本表对应一个存储文件。视视图图（VIEW）：是是一一个个虚虚拟拟的的表表，是是从从一一个个或或几几个个基基本本表表导导出出的的表表。它它本本身身不不独独立立存存在在于于数数据据库库中中，数数据据库库中中只只存存放放视视图图的的定定义义而而不不存存放放视视图图对对应应的的数数据据，这这些些数数据据仍仍存存放放在在导导出出视视
4、图图的的基基本本表表中中。当当基基本本表表中中的的数数据据发发生生变变化化时时，从从视视图图中中查查询询出出来来的的数数据据也也随随之之改改变。变。4SQL视图1视图2基本表1基本表2基本表3基本表4存储文件1存储文件2外模式模式内模式图2.1SQL语言支持的关系数据库的三级逻辑结构SQL语言支持数据库的三级模式结构，如图语言支持数据库的三级模式结构，如图2.1所示。所示。其中其中外模式外模式对应于视图和部分基本表，对应于视图和部分基本表，模式模式对应于基对应于基本表，本表，内模式内模式对应于存储文件。对应于存储文件。55.SQL语言具有语言具有:数据查询（数据查询（QUERY）数据定义（数据
5、定义（DEFINITION）数据操纵（数据操纵（MANIPULATION）数据控制（数据控制（CONTROL）四种语言一体化的功能。四种语言一体化的功能。62.2SQL数据定义数据定义SQL语语言言使使用用数数据据定定义义语语言言（DATA DEFINITIONLANGUAGE，简简称称DDL）实实现现其其数数据据定定义义功功能能，可可对数据库对数据库用户用户、基本表基本表、视图视图、索引索引进行定义和撤消。进行定义和撤消。72.3SQL数据查询数据查询2.3.1SELECT命令的格式与基本使用命令的格式与基本使用数据查询是数据库中最常见的操作。数据查询是数据库中最常见的操作。SQL语语言言提
6、提供供SELECT语语句句，通通过过查查询询操操作作可可得得到到所所需的信息。需的信息。SELECT语句的一般格式为：语句的一般格式为：SELECT列名，列名FROM表名或视图名，表名或视图名WHERE检索条件GROUPBYHAVINGORDERBYASC|DESC;8SELECT语句的格式：语句的格式：SELECTALL|DISTINCTTOPNPERCENTWITHTIES列名1AS别名1,列名2AS别名2INTO新表名FROM表名1AS表1别名INNER|RIGHT|FULL|OUTEROUTERJOIN表名2AS表2别名ON条件9查询的结果是仍是一个表。查询的结果是仍是一个表。SELE
7、CT语句的执行过程是语句的执行过程是:根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表。如果有GROUP子句，则将查询结果按照相同的值进行分组。如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组。如果有ORDER子句，查询结果还要按照的值进行排序。10例例1查询全体学生的学号、姓名和年龄。查询全体学生的学号、姓名和年龄。SELECTSNO,SN,AGEFROMS例例2查询学生的全部信息。查询学生的全部信息。SELECT*FROMS用用*表示表示S表的全部列名，而不必逐一列出。表的全部列名
8、，而不必逐一列出。例例3查询选修了课程的学生号。查询选修了课程的学生号。SELECTDISTINCTSNOFROMSC查询结果中的重复行被去掉查询结果中的重复行被去掉11另另外外，利利用用投投影影查查询询可可控控制制列列名名的的顺顺序序，并并可可通通过过指指定定别名别名改变查询结果的列标题的名字。改变查询结果的列标题的名字。例例4查询全体学生的姓名、学号和年龄。查询全体学生的姓名、学号和年龄。SELECTSNAMENAME,SNO,AGEFROMS其中，其中，NAME为为SNAME的别名的别名122.3.2条件查询条件查询当当要要在在表表中中找找出出满满足足某某些些条条件件的的行行时时，则则需
9、需使使用用WHERE子句指定查询条件。子句指定查询条件。WHERE子句中，条件通常通过三部分来描述：子句中，条件通常通过三部分来描述：1列名；2比较运算符；3列名、常数。运算符含义=,=,85142.3.2.2多重条件查询多重条件查询当当WHERE子子句句需需要要指指定定一一个个以以上上的的查查询询条条件件时时，则则需需要要使使用用逻逻辑辑运运算算符符AND、OR和和NOT将将其其连连结结成成复复合的逻辑表达式。合的逻辑表达式。其其优优先先级级由由高高到到低低为为：NOT、AND、OR，用用户户可可以以使用括号改变优先级。使用括号改变优先级。例例7查查询询选选修修C1或或C2且且分分数数大大于
10、于等等于于85分分学学生生的的的的学学号号、课程号和成绩。课程号和成绩。SELECTSNO，CNO，SCOREFROMSCWHERE（CNO=C1ORCNO=C2）ANDSCORE=85152.3.2.3确定范围确定范围例例8查查询询工工资资在在1000至至1500之之间间的的教教师师的的教教师师号号、姓姓名名及职称。及职称。SELECTTNO,TN,PROFFROMTWHERESALBETWEEN1000AND1500等价于等价于SELECTTNO,TN,PROFFROMTWHERESAL=1000ANDSAL=150016例例9查查询询工工资资不不在在1000至至1500之之间间的的教教师
11、师的的教教师师号号、姓姓名及职称。名及职称。SELECTTNO,TN,PROFFROMTWHERESALNOTBETWEEN1000AND15002.2.2.4确定集合确定集合利用利用“IN”操作可以查询属性值属于指定集合的元组。操作可以查询属性值属于指定集合的元组。例例10查询选修查询选修C1或或C2的学生的学号、课程号和成绩。的学生的学号、课程号和成绩。SELECTSNO,CNO,SCOREFROMSCWHERECNOIN(C1,C2)此语句也可以使用逻辑运算符此语句也可以使用逻辑运算符“OR”实现。实现。17SELECTSNO,CNO,SCOREFROMSCWHERECNO=C1ORCN
12、O=C2利用利用“NOTIN”可以查询指定集合外的元组。可以查询指定集合外的元组。例例11查查询询没没有有选选修修C1，也也没没有有选选修修C2的的学学生生的的学学号号、课课程号和成绩。程号和成绩。SELECTSNO,CNO,SCOREFROMSCWHERECNONOTIN(C1,C2)等价于：等价于：SELECTSNO,CNO,SCOREFROMSCWHERECNO!=C1ANDCNO!=C2182.3.2.5部分匹配查询部分匹配查询上上例例均均属属于于完完全全匹匹配配查查询询，当当不不知知道道完完全全精精确确的的値値时时，用用户户还还可可以以使使用用LIKE或或NOTLIKE进进行行部部分
13、分匹匹配配查查询询（也称模糊查询）。（也称模糊查询）。LIKE定义的一般格式为：定义的一般格式为：LIKE属性名必须为字符型，字符串常量的字符可以包含如下两个特殊符号：%：表示任意知长度的字符串；_：表示任意单个字符。例例12查询所有姓张的教师的教师号和姓名。查询所有姓张的教师的教师号和姓名。SELECTTNO,TNFROMTWHERETNLIKE张%19例例13查询姓名中第二个汉字是查询姓名中第二个汉字是“力力”的教师号和姓名。的教师号和姓名。SELECTTNO,TNFROMTWHERETNLIKE_力%注：一个汉字占两个字符。注：一个汉字占两个字符。2.3.2.6空值查询空值查询某个字段没
14、有值称之为具有空值（某个字段没有值称之为具有空值（NULL）。）。通常没有为一个列输入值时，该列的值就是空值。通常没有为一个列输入值时，该列的值就是空值。空值不同于零和空格，它不占任何存储空间。空值不同于零和空格，它不占任何存储空间。例如，某些学生选课后没有参加考试，有选课记录，例如，某些学生选课后没有参加考试，有选课记录，但没有考试成绩，考试成绩为空值，这与参加考试，但没有考试成绩，考试成绩为空值，这与参加考试，成绩为零分的不同。成绩为零分的不同。20例例14查询没有考试成绩的学生的学号和相应的课程号。查询没有考试成绩的学生的学号和相应的课程号。SELECTSNO,CNOFROMSCWHER
15、ESCOREISNULL注注 意意 ： 这这 里里 的的 空空 值值 条条 件件 为为 IS NULL， 不不 能能 写写 成成SCORE=NULL。212.2.2常用库函数及统计汇总查询常用库函数及统计汇总查询SQL提供了许多提供了许多库函数库函数，增强了基本检索能力。，增强了基本检索能力。常用的库函数，如表常用的库函数，如表3.2所示所示函数名称功能AVG按列计算平均值SUM按列计算值的总和MAX求一列中的最大值MIN求一列中的最小值COUNT按列值计个数22例例15求学号为求学号为S1学生的总分和平均分。学生的总分和平均分。SELECT SUM(SCORE) AS TotalScore,
16、 AVG(SCORE)ASAveScoreFROMSCWHERE(SNO=S1)注意：函数注意：函数SUM和和AVG只能对只能对数值型数值型字段进行计算。字段进行计算。23例例16求求选选修修C1号号课课程程的的最最高高分分、最最低低分分及及之之间间相相差差的的分分数数SELECT MAX(SCORE) AS MaxScore, MIN(SCORE)ASMinScore,MAX(SCORE)-MIN(SCORE)ASDiffFROMSCWHERE(CNO=C1)例例17求计算机系学生的总数求计算机系学生的总数SELECTCOUNT(SNO)FROMSWHEREDEPT=计算机24例例18求学校
17、中共有多少个系求学校中共有多少个系SELECTCOUNT(DISTINCTDEPT)ASDeptNumFROMS注注意意：加加入入关关键键字字DISTINCT后后表表示示消消去去重重复复行行，可可计算字段计算字段“DEPT“不同值的数目。不同值的数目。COUNT函数对函数对空值空值不计算，但对不计算，但对零零进行计算。进行计算。例例19统计有成绩同学的人数统计有成绩同学的人数SELECTCOUNT(SCORE)FROMSC上例中成绩为零的同学计算在内，没有成绩（即为空值）的不计上例中成绩为零的同学计算在内，没有成绩（即为空值）的不计算。算。25例例20利用特殊函数利用特殊函数COUNT(*)求
18、计算机系学生的总数求计算机系学生的总数SELECTCOUNT(*)FROMSWHEREDEPT=计算机COUNT（*）用来统计元组的个数用来统计元组的个数不消除重复行，不允许使用不消除重复行，不允许使用DISTINCT关键字。关键字。262.3.3分组查询分组查询GROUPBY子子句句可可以以将将查查询询结结果果按按属属性性列列或或属属性性列列组组合合在在行行的的方方向向上上进进行行分分组组，每每组组在在属属性性列列或或属属性性列列组组合合上具有相同的值。上具有相同的值。例例22查询各位教师的教师号及其任课的门数。查询各位教师的教师号及其任课的门数。SELECTTNO,COUNT(*)ASC_
19、NUMFROMTCGROUPBYTNOGROUPBY子子句句按按TNO的的值值分分组组，所所有有具具有有相相同同TNO的的元元组组为为一一组组，对对每每一一组组使使用用函函数数COUNT进进行行计计算算，统计出各位教师任课的门数。统计出各位教师任课的门数。27若在分组后还要按照一定的条件进行筛选，则需使用若在分组后还要按照一定的条件进行筛选，则需使用HAVING子句。子句。例例23查询选修两门以上课程的学生学号和选课门数查询选修两门以上课程的学生学号和选课门数SELECTSNO,COUNT(*)ASSC_NUMFROMSCGROUPBYSNOHAVINGCOUNT(*)=2GROUPBY子子句
20、句按按SNO的的值值分分组组，所所有有具具有有相相同同SNO的的元元组组为为一一组组，对对每每一一组组使使用用函函数数COUNT进进行行计计算算，统计出每位学生选课的门数。统计出每位学生选课的门数。HAVING子句去掉不满足子句去掉不满足COUNT（*）=2的组。的组。28当当在在一一个个SQL查查询询中中同同时时使使用用WHERE子子句句，GROUPBY 子子句句和和HAVING子子句句时时，其其顺顺序序是是WHEREGROUPBYHAVING。WHERE与与HAVING子句的根本区别在于子句的根本区别在于作用对象作用对象不同。不同。WHERE子句作用于基本表或视图，从中选择满足条件的元组；
21、HAVING子句作用于组，选择满足条件的组，必须用于GROUPBY子句之后，但GROUPBY子句可没有HAVING子句。292.3.5查询的排序查询的排序当需要对查询结果排序时，应该使用当需要对查询结果排序时，应该使用ORDERBY子句子句ORDERBY子句必须出现在其他子句之后子句必须出现在其他子句之后排排序序方方式式可可以以指指定定，DESC为为降降序序，ASC为为升升序序，缺缺省省时为升序时为升序例例24查询选修查询选修C1的学生学号和成绩，并按成绩降序排列。的学生学号和成绩，并按成绩降序排列。SELECTSNO,SCOREFROMSCWHERECNO=C1ORDERBYSCOREDES
22、C30例例25查查询询选选修修C2、C3、C4或或C5课课程程的的学学号号、课课程程号号和和成成绩绩，查询结果按学号升序排列，学号相同再按成绩降序排列。查询结果按学号升序排列，学号相同再按成绩降序排列。SELECTSNO,CNO,SCOREFROMSCWHERECNOIN(C2,C3,C4,C5)ORDERBYSNO,SCOREDESC例例26求求选选课课在在三三门门以以上上且且各各门门课课程程均均及及格格的的学学生生的的学学号号及及其其总成绩，查询结果按总成绩降序列出。总成绩，查询结果按总成绩降序列出。SELECTSNO,SUM(SCORE)ASTotalScoreFROMSCWHERESC
23、ORE=60GROUPBYSNOHAVINGCOUNT(*)=3ORDERBYSUM(SCORE)DESC31此语句为此语句为分组排序分组排序，执行过程如下：，执行过程如下：1.（FROM）取出整个SC2.（WHERE）筛选SCORE=60的元组3.（GROUPBY）将选出的元组按SNO分组4.（HAVING）筛选选课三门以上的分组5.（SELECT）以剩下的组中提取学号和总成绩6.（ORDERBY）将选取结果排序ORDERBYSUM(SCORE)DESC可以改写成可以改写成ORDERBY2DESC2代表查询结果的第二列。代表查询结果的第二列。322.3.6数据表连接及连接查询数据表连接及连接
24、查询数数据据表表之之间间的的联联系系是是通通过过表表的的字字段段值值来来体体现现的的，这这种种字段称为字段称为连接字段连接字段。连连接接操操作作的的目目的的就就是是通通过过加加在在连连接接字字段段的的条条件件将将多多个个表连接起来，以便从多个表中查询数据。表连接起来，以便从多个表中查询数据。前前面面的的查查询询都都是是针针对对一一个个表表进进行行的的，当当查查询询同同时时涉涉及及两个以上的表时，称为两个以上的表时，称为连接查询连接查询。表的连接方法有两种：表的连接方法有两种：方法1：表之间满足一定的条件的行进行连接，此时FROM子句中指明进行连接的表名，WHERE子句指明连接的列名及其连接条件
25、。方法2：利用关键字JOIN进行连接。33具体分为以下几种：具体分为以下几种：INNERJOIN：显示符合条件的记录，此为显示符合条件的记录，此为默认值默认值；LEFT（OUTER）JOIN：显显示示符符合合条条件件的的数数据据行行以以及及左左边边表表中中不不符符合合条条件件的的数数据据行行，此此时时右右边边数数据据行行会会以以NULL来来显显示示，此此称称为为左连接左连接；RIGHT（OUTER）JOIN：显显示示符符合合条条件件的的数数据据行行以以及及右右边边表表中中不不符符合合条条件件的的数数据据行行，此此时时左左边边数数据据行行会会以以NULL来来显显示示，此此称称为为右连接；右连接；
26、FULL（OUTER）JOIN：显显示示符符合合条条件件的的数数据据行行以以及及左左边边表表和和右右边边表表中中不不符符合合条条件件的的数数据据行行，此此时时缺缺乏乏数数据据的的数数据据行行会会以以NULL来显示；来显示；CROSSJOIN：会会将将一一个个表表的的每每一一笔笔数数据据和和另另一一表表的的每每笔笔数数据据匹匹配成新的数据行。配成新的数据行。当当将将JOIN关关键键词词放放于于FROM子子句句中中时时，应应有有关关键键词词ON与与之之相相对对应，以表明连接的条件。应，以表明连接的条件。342.3.6.1等值连接与非等值连接等值连接与非等值连接例例27查询刘伟老师所讲授的课程。查询
27、刘伟老师所讲授的课程。方法方法1：SELECTT.TNO,TN,CNOFROMT,TCWHERE(T.TNO=TC.TNO)AND(TN=刘伟刘伟)这里，TN=刘伟为查询条件，而T.TNO=TC.TNO为连接条件，TNO为连接字段。连接条件的一般格式为：.其中,比较运算符主要有：、！。当比较运算符为“时，称为等值连接，其他情况为非等值连接。35引用列名TNO时要加上表名前缀，是因为两个表中的列名相同，必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。如果列名是唯一的，比如TN，就不必须加前缀。上面的操作是将T表中的TNO和TC表中的TNO相等的行连接，同时选取TN为“刘伟“的行，然后再在
28、TN，CNO列上投影，这是连接、选取和投影的操作组合。方法方法2：SELECTT.TNO,TN,CNOFROMTINNERJOINTCONT.TNO=TC.TNOANDT.TN=刘伟36方法方法3：SELECTR2.TNO,R2.TN,R1.CNOFROM(SELECTTNO,CNOFROMTC)ASR1INNERJOIN(SELECTTNO,TNFROMTWHERETN=刘伟)ASR2ONR1.TNO=R2.TNO37例例28查询所有选课学生的学号、姓名、选课名称及成绩。查询所有选课学生的学号、姓名、选课名称及成绩。SELECTS.SNO,SN,CN,SCOREFROMS,C,SCWHERE
29、S.SNO=SC.SNOANDSC.CNO=C.CNO本本例例涉涉及及三三个个表表，WHERE子子句句中中有有两两个个连连接接条条件件。当当有有两两个以上的表进行连接时，称为个以上的表进行连接时，称为多表连接多表连接。382.3.6.2自身连接自身连接当当一一个个表表与与其其自自已已进进行行连连接接操操作作时时，称称为为表表的的自自身身连连接接。例例29查查询询所所有有比比刘刘伟伟工工资资高高的的教教师师姓姓名名、性性别别、工工资资和和刘伟的工资。刘伟的工资。要要查查询询的的内内容容均均在在同同一一表表T中中，可可以以将将表表T分分别别取取两两个个别别名名，一一个个是是X，一一个个是是Y。将将
30、X,Y中中满满足足比比刘刘伟伟工工资资高的行连接起来。这实际上是同一表高的行连接起来。这实际上是同一表T的的自身连接。自身连接。方法方法1：SELECTX.TN,X.SALASSAL_a,Y.SALASSAL_bFROMTASX,TASYWHEREX.SALY.SALANDY.TN= 刘伟39方法方法2 2：SELECTX.TN,X.SAL,Y.SALFROMTASXINNERJOINTASYONX.SALY.SALANDY.TN= 刘伟方法方法3 3：SELECTR1.TN,R1.SAL,R2.SALFROM(SELECTTN,SALFROMT)ASR1INNERJOIN(SELECTSAL
31、FROMTWHERETN=刘伟)ASR2ONR1.SALR2.SAL40例例30检索所有学生姓名，年龄和选课名称。检索所有学生姓名，年龄和选课名称。方法方法1：SELECTSN,AGE,CNFROMS,C,SCWHERES.SNO=SC.SNOANDSC.CNO=C.CNO方法方法2：SELECTR3.SNO,R3.SN,R3.AGE,R4.CNFROM(SELECTSNO,SN,AGEFROMS)ASR3INNERJOIN(SELECTR2.SNO,R1.CNFROM(SELECTCNO,CNFROMC)ASR1INNERJOIN(SELECTSNO,CNOFROMSC)ASR2ONR1.C
32、NO=R2.CNO)ASR4ONR3.SNO=R4.SNO412.2.3.6.3 3.6.3 外连接外连接在在上上面面的的连连接接操操作作中中，不不满满足足连连接接条条件件的的元元组组不不能能作作为为查查询询结结果果输出。输出。如如例例2828的的查查询询结结果果只只包包括括有有选选课课记记录录的的学学生生，而而不不会会有有吴吴丽丽同同学学的信息。若将例的信息。若将例2828改成：改成：例例3131 查查询询所所有有学学生生的的学学号号、姓姓名名、选选课课名名称称及及成成绩绩。（没没有有选选课课的的同学的选课信息显示为空）则应写成如下的同学的选课信息显示为空）则应写成如下的SQLSQL语句。语
33、句。SELECTS.SNO,SN,CN,SCOREFROMSLEFTOUTERJOINSCONS.SNO=SC.SNOLEFTOUTERJOINCONC.CNO=SC.CNO则则查查询询结结果果只只包包括括所所有有的的学学生生，没没有有选选课课的的吴吴丽丽同同学学的的选选课课信信息息显示为空。显示为空。422.3.7 2.3.7 子查询子查询在在WHEREWHERE子子句句中中包包含含一一个个形形如如SELECT-FROM-WHERESELECT-FROM-WHERE的的查查询询块块，此此查查询询块块称称为为子子查查询询或或嵌嵌套套查查询询，包包含含子子查查询询的的语语句称为句称为父查询父查询
34、或或外部查询外部查询。嵌嵌套套查查询询可可以以将将一一系系列列简简单单查查询询构构成成复复杂杂查查询询，增增强强查查询能力。询能力。子子查查询询的的嵌嵌套套层层次次最最多多可可达达到到255255层层，以以层层层层嵌嵌套套的的方方式式构造查询充分体现了构造查询充分体现了SQLSQL“结构化结构化”的特点。的特点。嵌嵌套套查查询询在在执执行行时时由由里里向向外外处处理理，每每个个子子查查询询是是在在上上一一级外部查询处理之前完成，父查询要用到子查询的结果。级外部查询处理之前完成，父查询要用到子查询的结果。432.3.7.12.3.7.1 返回一个值的子查询返回一个值的子查询当当子子查查询询的的返
35、返回回值值只只有有一一个个时时，可可以以使使用用比比较较运运算算符符（=, , =, , =, ANY(SELECTSALFROMTWHEREDEPT=计算机)ANDDEPT!=计算机 /*注意：此行是父查询中的条件*/先执行子查询，找到计算机系中所有教师的工资集合先执行子查询，找到计算机系中所有教师的工资集合(1500,900)(1500,900)；再执行父查询，查询所有不是计算机系且工资高于再执行父查询，查询所有不是计算机系且工资高于15001500或或900900的教师姓名和工资。的教师姓名和工资。48此查询也可以写成：此查询也可以写成：SELECTTN,SALFROMTWHERESAL
36、(SELECTMIN(SAL)FROMTWHEREDEPT=计算机)ANDDEPT!=计算机先先执执行行子子查查询询，利利用用库库函函数数MINMIN找找到到计计算算机机系系中中所所有有教教师的最低工资师的最低工资900900；再再执执行行父父查查询询，查查询询所所有有不不是是计计算算机机系系且且工工资资高高于于900900的教师。的教师。492. 2. 使用使用ININ可以使用可以使用ININ代替代替“=“=ANY”ANY”。例例3535（题目同（题目同3333）SELECTTNFROMTWHERETNOIN(SELECTTNOFROMTCWHERECNO=C5)503. 3. 使用使用AL
37、LALLALLALL的含义为的含义为全部全部。例例3636 查查询询其其他他系系中中比比计计算算机机系系所所有有教教师师工工资资都都高高的的教教师师的的姓姓名名和和工工资。资。SELECTTN,SALFROMTWHERESALALL(SELECTSALFROMTWHEREDEPT=计算机)ANDDEPT!=计算机子查询找到计算机系中所有教师的工资集合子查询找到计算机系中所有教师的工资集合(1500,900)(1500,900)；父查询找到所有不是计算机系且工资高于父查询找到所有不是计算机系且工资高于15001500的教师姓名和工资。的教师姓名和工资。51此查询也可以写成：此查询也可以写成：SE
38、LECTTN,SALFROMTWHERESAL(SELECTMAX(SAL)FROMTWHEREDEPT=计算机)ANDDEPT!=计算机库库函函数数MAXMAX的的作作用用是是找找到到计计算算机机系系中中所所有有教教师师的的最最高高工工资资15001500。52例例3737 查询不讲授课程号为查询不讲授课程号为C5C5的教师姓名。的教师姓名。SELECTDISTINCTTNFROMTWHEREC5!=ALL(SELECTCNOFROMTCWHERETNO=T.TNO)!=ALL!=ALL的的含含义义为为不不等等于于子子查查询询结结果果中中的的任任何何一一个个值值，也也可使用可使用NOT IN
39、NOT IN代替代替!=!=ALLALL。子查询包含子查询包含普通子查询普通子查询和和相关子查询相关子查询。前前面面所所讲讲的的子子查查询询均均为为普普通通子子查查询询，而而本本例例中中子子查查询询的的查查询询条条件件引引用用了了父父查查询询表表中中的的属属性性值值（T T表表的的TNOTNO值值），我们把这类查询称为，我们把这类查询称为相关子查询相关子查询。53二者的执行方式不同：二者的执行方式不同：二者的执行方式不同：二者的执行方式不同：普通子查询的执行顺序是：普通子查询的执行顺序是： 首首先先执执行行子子查查询询，然然后后把把子子查查询询的的结结果果作作为为父父查查询询的的查查询询条件的
40、值。条件的值。 普普通通子子查查询询只只执执行行一一次次，而而父父查查询询所所涉涉及及的的所所有有记记录录行行都都与其查询结果进行比较以确定查询结果集合。与其查询结果进行比较以确定查询结果集合。相关子查询的执行顺序是：相关子查询的执行顺序是： 首首先先选选取取父父查查询询表表中中的的第第一一行行记记录录，内内部部的的子子查查询询利利用用此此行中相关的属性值进行查询，行中相关的属性值进行查询， 然然后后父父查查询询根根据据子子查查询询返返回回的的结结果果判判断断此此行行是是否否满满足足查查询询条条件件。如如果果满满足足条条件件，则则把把该该行行放放入入父父查查询询的的查查询询结结果果集集合合中中
41、。重重复复执执行行这这一一过过程程，直直到到处处理理完完父父查查询询表表中中的的每每一一行数据。行数据。由由由由此此此此可可可可以以以以看看看看出出出出，相相相相关关关关子子子子查查查查询询询询的的的的执执执执行行行行次次次次数数数数是是是是由由由由父父父父查查查查询询询询表表表表的的的的行行行行数数数数决决决决定定定定的。的。的。的。54如如上上例例表表T T中中每每的的一一行行即即每每个个教教师记录都都要要执行行一一次次子子查询以以确确定定该教教师是是否否讲授授C5C5这门课，当当 C5C5不不是是教教师的任一的任一门课时，则该教教师被被选取。取。 以下几例均为相关子查询。以下几例均为相关
42、子查询。4. 4. 使用使用EXISTSEXISTSEXISTSEXISTS表表示示存存在在量量词词，带带有有EXISTSEXISTS的的子子查查询询不不返返回回任任何实际数据，它只得到逻辑值何实际数据，它只得到逻辑值“真真”或或“假假”。当当子子查查询询的的的的查查询询结结果果集集合合为为非非空空时时，外外层层的的WHEREWHERE子子句返回真值，否则返回假值。句返回真值，否则返回假值。NOT EXISTSNOT EXISTS与此相反。与此相反。含有含有ININ的的查询通常可用通常可用EXISTSEXISTS表示，但反表示，但反过来不一定。来不一定。 55例例3838（题目同（题目同333
43、3）略）略SELECTTNFROMTWHEREEXISTS(SELECT*FROMTCWHERETNO=T.TNOANDCNO=C5)当当子子查查询询TCTC表表存存在在一一行行记记录录满满足足其其WHEREWHERE子子句句中中的的条条件件时时，则则父父查查询询便便得得到到一一个个TNTN值值，重重复复执执行行以以上上过过程程，直到得出最后结果。直到得出最后结果。56例例3939 查询选修所有课程的学生姓名查询选修所有课程的学生姓名SELECT SN FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FRO
44、M SC WHERE SNO = S.SNO AND CNO=C.CNO)选出这样一些学生名单，在选出这样一些学生名单，在SCSC表中不存在他们没有选表中不存在他们没有选修课程的记录。修课程的记录。572.2.4 4 SQLSQL数据更新数据更新SQLSQL语语言言的的数数据据更更新新语语句句DMLDML主主要要包包括括插插入入数数据据、修修改改数据和删除数据三种语句。数据和删除数据三种语句。2.4.1 2.4.1 插入数据记录插入数据记录插插入入数数据据是是把把新新的的记记录录插插入入到到一一个个存存在在的的表表中中。插插入入数据使用语句数据使用语句INSERT INTOINSERT INT
45、O，可分为以下几种情况。可分为以下几种情况。2.4.1.1 2.4.1.1 插入一行新记录插入一行新记录语法格式为：语法格式为：INSERT INTO INSERT INTO (,) 2) VALUES(VALUES()其中，其中， 是指要插入新记录的表是指要插入新记录的表 是可选项，指定待添加数据的列是可选项，指定待添加数据的列 VALUESVALUES子句指定待添加数据的具体值。子句指定待添加数据的具体值。58列名的排列顺序不一定要和表定义时的顺序一致。列名的排列顺序不一定要和表定义时的顺序一致。但当指定列名表时但当指定列名表时VALUESVALUES子句值的排列顺序必须和列子句值的排列顺
46、序必须和列名表中的列名排列顺序一致，个数相等，数据类型一名表中的列名排列顺序一致，个数相等，数据类型一一对应。一对应。例例6060 在在S S表表中中插插入入一一条条学学生生记记录录（学学号号：S7S7；姓姓名名：郑冬；性别：女；年龄：郑冬；性别：女；年龄：2121；系别：计算机）。；系别：计算机）。INSERTINTOSVALUES(s7,郑冬,女,21,计算机)注意：注意：必须用必须用逗号逗号将各个数据分开，字符型数据要用将各个数据分开，字符型数据要用单引号单引号括起来。括起来。INTOINTO子句中没有指定列名，则新插入的记录必须在每子句中没有指定列名，则新插入的记录必须在每个属性列上均
47、有值，且个属性列上均有值，且VALUESVALUES子句中值的排列顺序要子句中值的排列顺序要和表中各属性列的排列顺序一致。和表中各属性列的排列顺序一致。592.4.1.2 2.4.1.2 插入一行的部分数据值插入一行的部分数据值例例61 61 在在SCSC表中插入一条选课记录（表中插入一条选课记录（S7,C1S7,C1）。）。INSERTINTOSC(SNO,CNO)VALUES(s7,c1)将将VALUESVALUES子句中的值按照子句中的值按照INTOINTO子句中指定列名的顺序子句中指定列名的顺序插入到表中插入到表中对于对于INTOINTO子句中没有出现的列，则新插入的记录在这子句中没有
48、出现的列，则新插入的记录在这些列上将取空值，如上例的些列上将取空值，如上例的SCORESCORE即赋空值。即赋空值。但在表定义时有但在表定义时有NOT NULLNOT NULL约束的属性列不能取空值。约束的属性列不能取空值。602.4.1.3 2.4.1.3 插入多行记录插入多行记录用用于于表表间间的的拷拷贝贝，将将一一个个表表中中的的数数据据抽抽取取数数行行插插入入另另一表中，可以通过子查询来实现。一表中，可以通过子查询来实现。插入数据的命令语法格式为：插入数据的命令语法格式为：INSERT INTO (,)子查询例例6262 求求出出各各系系教教师师的的平平均均工工资资，把把结结果果存存放
49、放在在新新表表AVGSALAVGSAL中。中。首先建立新表AVGSAL，用来存放系名和各系的平均工资CREATE TABLE AVGSAL(DEPARTMENT VARCHAR(20),AVGSAL SMALLINT) 61然然后后利利用用子子查查询询求求出出T T表表中中各各系系的的平平均均工工资资，把把结结果果存存放在新表放在新表AVGSALAVGSAL中。中。INSERTINTOAVGSALSELECTDEPT,AVG(SAL)FROMTGROUPBYDEPT622.4.22.4.2修改数据记录修改数据记录SQLSQL语语言言可可以以使使用用UPDATEUPDATE语语句句对对表表中中的
50、的一一行行或或多多行行记记录录的某些列值进行修改，其语法格式为：的某些列值进行修改，其语法格式为：UPDATE SET = ,=WHERE 其中：其中： 是指要修改的表是指要修改的表SETSET子句给出要修改的列及其修改后的值子句给出要修改的列及其修改后的值WHEREWHERE子子句句指指定定待待修修改改的的记记录录应应当当满满足足的的条条件件，WHEREWHERE子句省略时，则修改表中的所有记录。子句省略时，则修改表中的所有记录。632.4.2.1 2.4.2.1 修改一行修改一行例例6363 把刘伟教师转到信息系。把刘伟教师转到信息系。UPDATE T SET DEPT=信息 WHERE
51、TN=刘伟2.4.2.2 2.4.2.2 修改多行修改多行例例6464 将所有学生年龄增加将所有学生年龄增加1 1岁岁UPDATE S SET AGE=AGE+164例例6565 把把教教师师表表中中工工资资小小于于等等于于10001000元元的的讲讲师师的的工工资资提提高高20%20%。UPDATE T SET SAL=1.2*SALWHERE PROF=讲师 AND SAL =1000652.4.2.32.4.2.3用子查询选择要修改的行用子查询选择要修改的行例例6666 把讲授把讲授C5C5课程的教师的岗位津贴增加课程的教师的岗位津贴增加100100元。元。UPDATETSETCOMN=
52、COMN+100WHERETNOIN(SELECTT.TNOFROMT,TCWHERET.TNO=TC.TNOANDTC.CNO=C5)子查询的作用是得到讲授子查询的作用是得到讲授C5C5课程的教师号。课程的教师号。662.4.2.4 2.4.2.4 用子查询提供要修改的值用子查询提供要修改的值例例6767 把所有教师的工资提高到平均工资的把所有教师的工资提高到平均工资的1.21.2倍倍UPDATETSETSAL=(SELECT1.2*AVG(SAL)FROMT)子查询的作用是得到所有教师的平均工资。子查询的作用是得到所有教师的平均工资。672.4.3 2.4.3 删除数据记录删除数据记录使使
53、用用DELETEDELETE语语句句可可以以删删除除表表中中的的一一行行或或多多行行记记录录，其其语法格式为：语法格式为：DELETE FROMWHERE 其中，其中，是指要删除数据的表。WHERE子句指定待删除的记录应当满足的条件，WHERE子句省略时，则删除表中的所有记录。682.4.3.1 2.4.3.1 删除一行记录删除一行记录例例6868 删除刘伟教师的记录。删除刘伟教师的记录。DELETE FROM T WHERE TN=刘伟2.4.3.2 2.4.3.2 删除多行记录删除多行记录例例6969 删除所有教师的授课记录删除所有教师的授课记录DELETE FROM TC执执行行此此语语
54、句句后后，TCTC表表即即为为一一个个空空表表，但但其其定定义义仍仍存存在在数据字典中。数据字典中。692.4.3.3 2.4.3.3 利用子查询选择要删除的行利用子查询选择要删除的行例例70 70 删除刘伟教师授课的记录。删除刘伟教师授课的记录。DELETEFROM TC WHERE TNO= (SELECT TNO FROM T WHERE TN= 刘伟)702.5 2.5 视图视图视视图图是是虚虚表表，其其数数据据不不存存储储，其其记记录录来来自自基基本本表表，只只在数据库中在数据库中存储其定义存储其定义。视视图图在在概概念念上上与与基基本本表表等等同同，用用户户可可以以在在视视图图上上
55、再再定定义视图，可以对视图进行查询删除更新等操作。义视图，可以对视图进行查询删除更新等操作。2.5.1 2.5.1 定义和删除视图定义和删除视图2.5.1.1 2.5.1.1 定义视图定义视图定义视图使用语句定义视图使用语句CREATE VIEWCREATE VIEW，其语法格式为：其语法格式为：CREATE VIEW ()AS 其中，其中， 为可可选项，省略，省略时，视图的列名由的列名由子子查询的的结果决定。果决定。 71以下两种情况下，视图列名不可省略：以下两种情况下，视图列名不可省略：1.视图由多个表连接得到，在不同的表中存在同名列，则需指定列名；2.当视图的列名为表达式或库函数的计算结
56、果时，而不是单纯的属性名时，则需指明列名。在子查询中不许使用在子查询中不许使用ORDER BY ORDER BY 子句和子句和DISTINCTDISTINCT短语，短语，如果需要排序，则可在视图定义后，对视图查询时再如果需要排序，则可在视图定义后，对视图查询时再进行排序。进行排序。例例7171 创建一个计算机系教师情况的视图创建一个计算机系教师情况的视图SUB_TSUB_T。CREATEVIEWSUB_TASSELECTTNO,TN,PROFFROMTWHEREDEPT=计算机72其中：其中：视图名字为SUB_T，省略了视图列表。视图由子查询中的三列TNO,TN,PROF组成。视图创建后，对视
57、图SUB_T的数据的访问只限制在计算机系内，且只能访问TNO,TN,PROF三列的内容，从而达到了数据保密的目的。视图创建后，只在数据字典中存放视图创建后，只在数据字典中存放视图的定义视图的定义，而其中的子查询而其中的子查询SELECTSELECT语句并不执行。语句并不执行。只有当用户对视图进行操作时，才按照视图的只有当用户对视图进行操作时，才按照视图的定义将数据从基本表中取出。定义将数据从基本表中取出。73例例7272 创建一学生情况视图创建一学生情况视图S_SC_CS_SC_C（包括学号、包括学号、姓名、课程名及成绩）。姓名、课程名及成绩）。CREATEVIEWS_SC_C(SNO,SN,
58、CN,SCORE)ASSELECTS.SNO,SN,CN,SCOREFROMS,C,SCWHERES.SNO=SC.SNOANDSC.CNO=C.CNO此视图由三个表连接得到，在此视图由三个表连接得到，在S S表和表和SCSC表中均表中均存在存在SNOSNO列，则需指定视图列名。列，则需指定视图列名。74例例7373 创建一学生平均成绩视图创建一学生平均成绩视图S_AVGS_AVGCREATEVIEWS_AVG(SNO,AVG)ASSELECTSNO,AVG(SCORE)FROMSCGROUPBYSNO此视图的列名之一此视图的列名之一AVGAVG为库函数的计算结果，则在定义为库函数的计算结果，
59、则在定义时需指明列名。时需指明列名。752.5.1.2 2.5.1.2 删除视图删除视图视图定义后可随时删除，删除视图的语法格式为：视图定义后可随时删除，删除视图的语法格式为：DROP VIEW 例例7474 删除计算机系教师情况的视图删除计算机系教师情况的视图SUB_TSUB_T。DROP VIEW SUB_T视图删除后，只会删除该视图在数据字典中的定义，视图删除后，只会删除该视图在数据字典中的定义，而与该视图有关的基本表中的数据不会受任何影响，而与该视图有关的基本表中的数据不会受任何影响，由此视图导出的其他视图的定义不会删除，但已无任由此视图导出的其他视图的定义不会删除，但已无任何意义。用
60、户应该把这些视图删除。何意义。用户应该把这些视图删除。762.5.2 2.5.2 查询视图查询视图视图定义后，对视图的查询操作如同对基本表的查询视图定义后，对视图的查询操作如同对基本表的查询操作一样。操作一样。例例7575查找视图查找视图SUB_TSUB_T中职称为教授的教师号和姓名。中职称为教授的教师号和姓名。SELECT TNO,TNFROM SUB_T WHERE PROF=教授77此查询的执行过程是系统此查询的执行过程是系统首先首先从数据字典中找到从数据字典中找到SUB_TSUB_T的定义，的定义，然后然后把此定义和用户的查询结合起来，转换把此定义和用户的查询结合起来，转换成等价的对基
61、本表成等价的对基本表T T的查询，这一转换过程称为的查询，这一转换过程称为视图消视图消解解（View ResolutionView Resolution）, ,相当于执行以下查询：相当于执行以下查询：SELECT TNO,TNFROM T WHERE DEPT =计算机AND PROF=教授由上例可以看出，当对一个基本表进行复杂的查询时，由上例可以看出，当对一个基本表进行复杂的查询时，可以先对基本表建立一个视图，然后只需对此视图进可以先对基本表建立一个视图，然后只需对此视图进行查询，这样就不必再键入复杂的查询语句，而将一行查询，这样就不必再键入复杂的查询语句，而将一个复杂的查询转换成一个简单的
62、查询，从而简化了查个复杂的查询转换成一个简单的查询，从而简化了查询操作。询操作。782.5.3 2.5.3 更新视图更新视图由于视图是一张虚表，所以对视图的更新，最由于视图是一张虚表，所以对视图的更新，最终实际上是转换成对基本表的更新。终实际上是转换成对基本表的更新。其更新操作包括其更新操作包括插入插入、修改修改和和删除删除数据，数据，其语法格式如同对基本表的更新操作一样。其语法格式如同对基本表的更新操作一样。有些更新在理论上是不可能的，有些实现起来有些更新在理论上是不可能的，有些实现起来比较困难，以下仅考虑可以更新的视图。比较困难，以下仅考虑可以更新的视图。792.5.3.1 2.5.3.1
63、 插入（插入（INSERTINSERT）例例2.762.76 向向计计算算机机系系教教师师视视图图SUB_TSUB_T中中插插入入一一条条记记录录（教教师师号号：T6T6；姓姓名名：李李丹丹；职职称称：副副教教授）。授）。INSERTINTOSUB_TVALUES(T6,李丹,副教授)系统在执行此语句时，首先从数据字典中找到系统在执行此语句时，首先从数据字典中找到SUB_TSUB_T的定义，然后把此定义和插入操作结合的定义，然后把此定义和插入操作结合起来，转换成等价的对基本表起来，转换成等价的对基本表T T的插入。相当的插入。相当于执行以下操作：于执行以下操作：INSERT INTO TVAL
64、UES (T6, 李丹，副教授，计算机)802.5.3.2 2.5.3.2 修改（修改（UPDATEUPDATE）例例2.772.77 将计算机系教师视图将计算机系教师视图SUB_TSUB_T中刘伟的职称改为中刘伟的职称改为“副教授副教授“。UPDATESUB_TSETPROF=副教授WHERETN=刘伟转换成对基本表的修改操作：转换成对基本表的修改操作：UPDATE TSET PROF=副教授WHERE TN=刘伟AND DEPT=计算机812.5.3.3 2.5.3.3 删除（删除（DELETEDELETE）例例2.782.78 删除计算机系教师视图删除计算机系教师视图SUB_TSUB_T
65、中刘伟教师的记录。中刘伟教师的记录。DELETE FROM SUB_TWHERE TN=刘伟转换成对基本表的删除操作：转换成对基本表的删除操作：DELETE FROM TWHERE TN=刘伟 AND DEPT=计算机由于视图中的数据不是存放在视图中的，即视图没有由于视图中的数据不是存放在视图中的，即视图没有相应的存储空间，对视图的一切操作最终都要转换成相应的存储空间，对视图的一切操作最终都要转换成对基本表的操作，这样看来使操作更加复杂，那么为对基本表的操作，这样看来使操作更加复杂，那么为什么还要使用视图呢？什么还要使用视图呢？82使用视图有如下几个优点：使用视图有如下几个优点：1. 利于数据
66、保密利于数据保密利于数据保密利于数据保密，对不同的用户定义不同的视图，使用户只能看到与自己有关的数据。例如,对教师表创建了计算机系视图，本系教师只能使用此视图，而无法访问其他系教师的数据。2. 简化查询操作简化查询操作简化查询操作简化查询操作，为复杂的查询建立一个视图，用户不必键入复杂的查询语句，只需针对此视图做简单的查询即可。如例2.75。3. 保证数据的逻辑独立性保证数据的逻辑独立性保证数据的逻辑独立性保证数据的逻辑独立性。对于视图的操作，比如查询，只依赖于视图的定义。当构成视图的基本表要修改时，只需修改视图定义中的子查询部分。而基于视图的查询不用改变。这就是第一章介绍过的外模式与模式之间
67、的独立性，即数据的逻辑独立性。83小小 结结本章详细介绍了本章详细介绍了SQLSQL语言的使用方法。语言的使用方法。在在讲讲解解SQLSQL语语言言的的同同时时，进进一一步步介介绍绍了了关关系系数数据据库库的的有有关关概概念念，如如索引和视图的概念及其作用。索引和视图的概念及其作用。SQLSQL语语言言具具有有数数据据定定义义、数数据据查查询询、数数据据更更新新、数数据据控控制制四四大大功功能能。其全部功能可以用表其全部功能可以用表2.32.3的的9 9个动词概括出来。个动词概括出来。表表2.3 2.3 SQLSQL语言的动词语言的动词SQL功能动词数据定义CREATE,DROP,ALTER数据查询SELECT数据操纵INSERT,UPDATE,DELETE数据控制GRANT,REVOKE其中：其数据查询功能最为丰富和复杂，也非常重要，其中：其数据查询功能最为丰富和复杂，也非常重要，初学者掌握起来有一定的困难，应反复上机加强练习。初学者掌握起来有一定的困难，应反复上机加强练习。84
