数据库原理及应用何玉洁第二版第4章

《数据库原理及应用何玉洁第二版第4章》由会员分享，可在线阅读，更多相关《数据库原理及应用何玉洁第二版第4章（67页珍藏版）》请在装配图网上搜索。

1、1第第 4 4 章章 数据操作数据操作 4.1 4.1 数据查询功能数据查询功能4.2 4.2 数据更改功能数据更改功能2一、查询语句的基本结构一、查询语句的基本结构 查询语句是从数据库中检索满足条件的数据查询语句是从数据库中检索满足条件的数据 基本结构可描述为：基本结构可描述为：SELECTSELECT -需要哪些列需要哪些列 FROM FROM -来自于哪些来自于哪些表表 WHEREWHERE -根据什么条根据什么条件件 GROUP BYGROUP BY HAVINGHAVING ORDER BYORDER BY 4.1 4.1 数据查询功能数据查询功能 3二二、简单查询、简单查询 选择表

2、中若干列选择表中若干列 1 1、查询指定的列查询指定的列 例例.查询全体学生的学号与姓名查询全体学生的学号与姓名 SELECT SELECT SnoSno,SnameSname FROM Student FROM Student Sno Sno sname sname 9512101 9512101 李勇李勇 9512102 9512102 刘晨刘晨 9512103 9512103 王敏王敏 9521101 9521101 张立张立 9521102 9521102 吴宾吴宾 9521103 9521103 张海张海 结果为结果为 ：42 2、查询全部列、查询全部列例查询全体学生的记录例查询全体

3、学生的记录SELECT SELECT Sno,Sname,Ssex,Sage,SdeptSno,Sname,Ssex,Sage,Sdept FROM Student FROM Student等价于：等价于：SELECT SELECT *FROM Student FROM Student结果为结果为 ：Sno Sno Sname Sname Ssex Ssex Sage Sage Sdept Sdept 9512101 9512101 李勇李勇 男男 1919计算机系计算机系 9512102 9512102 刘晨刘晨 男男 2020计算机系计算机系 9512103 9512103 王敏王敏 女女

4、 2020计算机系计算机系 9521101 9521101 张立张立 男男 2222信息系信息系 9521102 9521102 吴宾吴宾 女女 2121信息系信息系 9521103 9521103 张海张海 男男 2020信息系信息系 53 3、查询经过计算的列、查询经过计算的列 SELECTSELECT子句中的子句中的 可以是表可以是表中存在的属性列，也可以是表达式、常量或者中存在的属性列，也可以是表达式、常量或者函数。函数。例查询全体学生的姓名及其出生年份例查询全体学生的姓名及其出生年份 SELECT Sname,SELECT Sname,2002-Sage2002-Sage FROM

5、Student FROM Student 结果为结果为:Sname Sname（无列名（无列名 )李勇李勇 1982 1982 刘晨刘晨 1981 1981 王敏王敏 1981 1981 张立张立 1979 1979 吴宾吴宾 1980 1980 张海张海 19811981钱小平钱小平 19831983王大力王大力 1982 1982 6改变列标题的语法格式为：改变列标题的语法格式为：列名列名|表达式表达式 AS AS 列标题列标题 或：或：列标题列标题 列名列名|表达式表达式例如，对于上例可写成：例如，对于上例可写成：SELECT SELECT Sname Sname 姓名姓名,2002-S

6、age 2002-Sage 年份年份 FROM StudentFROM Student 结果为：结果为：姓名姓名年份年份李勇李勇 19821982刘晨刘晨1981 1981 王敏王敏1981 1981 张立张立19791979吴宾吴宾19801980张海张海19811981钱小平钱小平19831983王大力王大力 198219827选择表中的若干元组选择表中的若干元组 1 1、消除取值相同的行、消除取值相同的行 例在选课表例在选课表(SC)(SC)中查询有哪些学生修了课中查询有哪些学生修了课程，要求列出学生的学号。程，要求列出学生的学号。SELECT Sno FROM SCSELECT Sno

7、 FROM SC 在这个结果中有许多重复的行。在这个结果中有许多重复的行。SQLSQL中的中的DISTINCTDISTINCT关键字可以去掉结果表中的关键字可以去掉结果表中的重复行。重复行。SELECT SELECT DISTICT DISTICT Sno FROM SC Sno FROM SC 则执行结果为则执行结果为 ：SnoSno9512101951210195121029512102952110295211029521103952110382 2、查询满足条件的元组、查询满足条件的元组 查询满足条件的元组是通过查询满足条件的元组是通过WHEREWHERE子句子句实现实现的。的。WHER

8、EWHERE子句常用的查询条件如表子句常用的查询条件如表3-133-13所示。所示。查询条件查询条件 谓谓 词词 比较（比较运算符）比较（比较运算符）=,=,=,=,=,=,（或（或!=!=）NOT+NOT+上述比较运上述比较运算符算符 确定范围确定范围 BETWEEN AND,BETWEEN AND,NOT BETWEEN AND NOT BETWEEN AND 确定集合确定集合 IN,NOT IN IN,NOT IN 字符匹配字符匹配 LIKE,NOT LIKE LIKE,NOT LIKE 空值空值 IS NULL,IS NOT NULL IS NULL,IS NOT NULL 多重条件（

9、逻辑谓词多重条件（逻辑谓词)AND,OR AND,OR 9（1 1）比较大小）比较大小 例查询计算机系全体学生的姓名。例查询计算机系全体学生的姓名。SELECT Sname FROM Student SELECT Sname FROM Student WHERE Sdept=WHERE Sdept=计算机系计算机系 结果为结果为 ：SnameSname李勇李勇 刘晨刘晨 王敏王敏 10 Sname Sname Sage Sage 李勇李勇 1919钱小平钱小平 1818王大力王大力 1919注意：取反操作的执行效率比较低注意：取反操作的执行效率比较低例查询所有年龄在例查询所有年龄在2020岁以

10、下的学生的姓名及年龄岁以下的学生的姓名及年龄 SELECT Sname,Sage FROM Student SELECT Sname,Sage FROM Student WHERE WHERE Sage 20Sage=20NOT Sage=20结果为：结果为：11（2 2）确定范围）确定范围 BETWEENBETWEENANDAND和和NOT BETWEENNOT BETWEENANDAND是一个是一个逻辑运算符，可以用来逻辑运算符，可以用来查找属性值在或不在指查找属性值在或不在指定范围内的元组定范围内的元组。BETWEENBETWEENANDAND的格式为：的格式为：列名列名|表达式表达式

11、NOT BETWEEN NOT BETWEEN 下限值下限值 AND AND 上限值上限值 BETWEENBETWEENANDAND一般用于对一般用于对数值型数据数值型数据进行比进行比较。列名或表达式的类型要与下限值或上限值的较。列名或表达式的类型要与下限值或上限值的类型相同。类型相同。12 例查询年龄在例查询年龄在20202323岁之间的学生的姓名、岁之间的学生的姓名、所在系和年龄所在系和年龄 SELECT Sname,Sdept,Sage FROM Student SELECT Sname,Sdept,Sage FROM Student WHERE Sage WHERE Sage BETW

12、EEN 20 AND 23BETWEEN 20 AND 23 此句等价于：此句等价于：SELECT Sname,Sdept,Sage FROM Student SELECT Sname,Sdept,Sage FROM Student WHERE WHERE Sage=20 AND Sage=20 AND Sage=23 结果为：结果为：Sname Sname Sdept Sdept Sage Sage 刘晨刘晨 计算机系计算机系 20 20 王敏王敏 计算机系计算机系 20 20 张立张立 信息系信息系 2222吴宾吴宾 信息系信息系 2121张海张海 信息系信息系 202013 例查询年龄不

13、在例查询年龄不在20202323之间的学生姓名、所之间的学生姓名、所在系和年龄。在系和年龄。SELECT Sname,Sdept,Sage FROM Student SELECT Sname,Sdept,Sage FROM Student WHERE Sage WHERE Sage NOT BETWEEN 20 AND 23NOT BETWEEN 20 AND 23此句等价于：此句等价于：SELECT Sname,Sdept,Sage FROM Student SELECT Sname,Sdept,Sage FROM Student WHERE WHERE Sage 23Sage 23结果为：

14、结果为：Sname Sname Sdept Sdept Sage Sage 李勇李勇 计算机系计算机系 19 19 钱小平钱小平 数学系数学系 1818王大力王大力 数学系数学系 191914（3 3）确定集合）确定集合 ININ是一个逻辑运算符，可以用来是一个逻辑运算符，可以用来查找属性值查找属性值属于指定集合的元组属于指定集合的元组。使用。使用ININ的格式为：的格式为：列名列名 NOT IN NOT IN（常量（常量1,1,常量常量2,2,常量常量n n）ININ的含义为的含义为：当列中的值与：当列中的值与ININ中的某个常量中的某个常量值相等时，则结果为值相等时，则结果为TrueTru

15、e，表明此记录为符合，表明此记录为符合查询条件的记录；查询条件的记录；NOT INNOT IN的含义的含义正好相反：当列中的值与某个正好相反：当列中的值与某个常量值相同时，则结果为常量值相同时，则结果为FalseFalse，表明此记录为，表明此记录为不符合查询条件的记录；不符合查询条件的记录；15例查询信息系、数学系和计算机系学生的例查询信息系、数学系和计算机系学生的姓名和性别。姓名和性别。SELECT Sname,Ssex FROM Student SELECT Sname,Ssex FROM Student WHERE Sdept WHERE Sdept IN(IN(信息系信息系,数学系数

16、学系,计算机系计算机系)此句等价于：此句等价于：SELECT Sname,Ssex FROM Student SELECT Sname,Ssex FROM Student WHERE WHERE Sdept=Sdept=信息系信息系 OR OR Sdept=Sdept=数学系数学系 OR OR Sdept=Sdept=计算机系计算机系 16（4 4）字符匹配）字符匹配 LIKELIKE用于用于查找指定列名与匹配串常量匹配的查找指定列名与匹配串常量匹配的元组元组。通配符用于表示任意的字符或字符串。在。通配符用于表示任意的字符或字符串。在LIKELIKE运算符前边也可以使用运算符前边也可以使用NO

17、TNOT运算符，表示对运算符，表示对结果取反。结果取反。LIKELIKE运算符的一般形式为：运算符的一般形式为：列名列名 NOT LIKE NOT LIKE 匹配串中可包含如下四种匹配串中可包含如下四种通配符通配符：_ _：匹配任意一个字符；：匹配任意一个字符；%：匹配：匹配0 0个或多个字符；个或多个字符；：匹配：匹配 中的任意一个字符；中的任意一个字符；：不匹配：不匹配 中的任意一个字符。中的任意一个字符。17例查询姓例查询姓张张的学生的详细信息。的学生的详细信息。SELECT SELECT*FROM Student FROM Student WHERE Sname WHERE Sname

18、 LIKE LIKE 张张%结果为：结果为：Sno Sno Sname Sname Ssex Ssex Sage Sage Sdept Sdept 9521101 9521101 张立张立 男男 2222信息系信息系 9521103 9521103 张海张海 男男 2020信息系信息系 18例查询学生表中姓例查询学生表中姓张张、姓、姓李李和姓和姓刘刘的学生的情况。的学生的情况。SELECT SELECT*FROM Student FROM Student WHERE Sname WHERE Sname LIKELIKE 张李刘张李刘%结果为：结果为：Sno Sno Sname Sname Ss

19、ex Ssex Sage Sage Sdept Sdept 9512101 9512101 李勇李勇 男男 1919计算机系计算机系 9512102 9512102 刘晨刘晨 男男2020计算机系计算机系9521101 9521101 张立张立 男男 2222信息系信息系 9521103 9521103 张海张海 男男 2020信息系信息系 19 例查询名字中第例查询名字中第2 2个字为个字为小小或或大大字的学生的姓名和学号。字的学生的姓名和学号。SELECT Sname,Sno FROM Student SELECT Sname,Sno FROM Student WHERE Sname WH

20、ERE Sname LIKE LIKE _小大小大%结果为：结果为：Sname Sname Sno Sno 钱小平钱小平 9531101 9531101 王大力王大力 9531102953110220（5 5）涉及空值的查询）涉及空值的查询 空值空值(NULL)(NULL)在数据库中有特殊的含义，它在数据库中有特殊的含义，它表示表示不确定的值不确定的值。判断某个值是否为。判断某个值是否为NULLNULL值，不值，不能使用普通的比较运算符（能使用普通的比较运算符（=、!=!=等），而只能使等），而只能使用专门的判断用专门的判断NULLNULL值值的子句来完成。的子句来完成。判断取值为空的语句格式

21、为：列名判断取值为空的语句格式为：列名IS NULLIS NULL 判断取值不为空的语句格式为：判断取值不为空的语句格式为：列名列名 IS NOT NULLIS NOT NULL例查询无考试成绩的学生的学号和相应的课程号例查询无考试成绩的学生的学号和相应的课程号 SELECT Sno,Cno FROM SC SELECT Sno,Cno FROM SC WHERE Grade WHERE Grade IS NULLIS NULL21（6 6）多重条件查询）多重条件查询 在在WHEREWHERE子句中可以使用逻辑运算符子句中可以使用逻辑运算符ANDAND和和OROR来组成多条件查询。用来组成多条

22、件查询。用ANDAND连接的条件连接的条件表示必须全部满足所有的条件的结果才为表示必须全部满足所有的条件的结果才为TrueTrue，用，用OROR连接的条件表示只要满足其中一连接的条件表示只要满足其中一个条件结果即为个条件结果即为TrueTrue。例查询计算机系年龄在例查询计算机系年龄在2020岁以下的学生姓岁以下的学生姓名。名。SELECT Sname FROM Student SELECT Sname FROM Student WHERE WHERE Sdept=CS Sdept=CS ANDAND Sage20 Sage2022对查询结果进行排序对查询结果进行排序 排序子句的格式为：排序

23、子句的格式为：ORDER BY ORDER BY ASC|DESC ASC|DESC ，n n 其中其中 为排序的依据列，可以是为排序的依据列，可以是列名或列的别名。列名或列的别名。当指定多个排序依据列时，首先按排当指定多个排序依据列时，首先按排在最前面的列进行排序，如果排序后存在在最前面的列进行排序，如果排序后存在两个或两个以上列值相同的记录，则对这两个或两个以上列值相同的记录，则对这些值相同的记录再依据排在第二位的列进些值相同的记录再依据排在第二位的列进行排序，行排序，依此类推，依此类推。23例将学生按年龄的升序排序。例将学生按年龄的升序排序。SELECT SELECT*FROM Stud

24、ent FROM Student ORDER BY SageORDER BY Sage例查询全体学生的信息，查询结果按所在例查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄系的系名升序排列，同一系的学生按年龄降序排列。降序排列。SELECT SELECT*FROM Student FROM Student ORDER BY Sdept,Sage DESCORDER BY Sdept,Sage DESC24使用聚合函数汇总数据使用聚合函数汇总数据 计算函数也称为计算函数也称为集合函数或聚合函数、聚集函集合函数或聚合函数、聚集函数数，其作用是其作用是对一组值进行计算并返回一个

25、单值对一组值进行计算并返回一个单值。SQLSQL提供的计算函数有：提供的计算函数有：COUNTCOUNT（*）：统计表中元组个数；）：统计表中元组个数；COUNTCOUNT（）：统计本列列值个数；）：统计本列列值个数；SUMSUM（）：AVGAVG（）：MAXMAX（）：）：MINMIN（）：）：上述函数中除上述函数中除COUNTCOUNT（*）外，其他函数在计算）外，其他函数在计算过程中均忽略过程中均忽略NULLNULL值。值。25例统计学生总人数。例统计学生总人数。SELECT SELECT COUNTCOUNT（*）FROM StudentFROM Student 例统计选修了课程的学生

26、的人数。例统计选修了课程的学生的人数。SELECT SELECT COUNTCOUNT（DISTINCT SnoDISTINCT Sno）FROM SCFROM SC例查询选修了例查询选修了C01C01号课程的学生的最高号课程的学生的最高分和最低分。分和最低分。SELECT SELECT MAX(Grade)MAX(Grade),MIN(Grade)MIN(Grade)FROM SC FROM SC WHERE Cno=C01 WHERE Cno=C01 注意注意：计算函数不能出现在：计算函数不能出现在WHEREWHERE子句中子句中。例：查询年龄最大的学生的姓名，如下写法是例：查询年龄最大的

27、学生的姓名，如下写法是错误错误的：的：SELECT Sname FROM Student SELECT Sname FROM Student WHERE Sage=MAX(Sage)WHERE Sage=MAX(Sage)26对查询结果进行分组计算对查询结果进行分组计算 GROUP BYGROUP BY分组的目的是分组的目的是细化计算函数的作用对细化计算函数的作用对象象。在一个查询语句中，可以使用任意多个列进行。在一个查询语句中，可以使用任意多个列进行分组。分组。需要注意的是需要注意的是：如果使用了分组子句，则查如果使用了分组子句，则查询列表中的每个列必须要么是分组依据列（询列表中的每个列必须

28、要么是分组依据列（group group by by 后边的列），要么是计算函数后边的列），要么是计算函数。使用使用GROUP BYGROUP BY时，如果在时，如果在SELECTSELECT的查询列表中的查询列表中包含计算函数，则是针对每个组计算出一个汇总包含计算函数，则是针对每个组计算出一个汇总值，从而实现对查询结果的分组统计。值，从而实现对查询结果的分组统计。分组语句的一般形式为：分组语句的一般形式为：GROUP BY GROUP BY ，n n HAVING HAVING 27使用使用GROUP BYGROUP BY例统计每门课程的选课人数，列出课程号例统计每门课程的选课人数，列出课程

29、号和人数。和人数。SELECT SELECT CnoCno as as 课程号课程号,COUNT(Sno)COUNT(Sno)as as 选课人数选课人数 FROM SC FROM SC GROUP BY CnoGROUP BY Cno 查询结果为查询结果为 ：课程号课程号 选课人数选课人数 c01 c01 3 3C02C024 4c04c042 2c05c053 3c06 c06 2 228使用使用HAVINGHAVING HAVINGHAVING子句用于对分组后的结果再进行过滤子句用于对分组后的结果再进行过滤，它的功能有点像它的功能有点像WHEREWHERE子句，但它用于组而不是对子句，但

30、它用于组而不是对单个记录。在单个记录。在HAVINGHAVING子句中可以使用计算函数，但子句中可以使用计算函数，但在在WHEREWHERE子句中则不能。子句中则不能。HAVINGHAVING通常与通常与GROUP BYGROUP BY子子句一起使用句一起使用。例查询修了例查询修了3 3门以上课程的学生的学号。门以上课程的学生的学号。SELECT Sno FROM SC SELECT Sno FROM SC GROUP BY SnoGROUP BY Sno HAVING COUNT(HAVING COUNT(*)3)3 结果为：结果为：SnoSno9521102952110229三、多表连接查

31、询三、多表连接查询 内连接内连接 只有满足连接条件的元组才能作为结果输出只有满足连接条件的元组才能作为结果输出 内连接的格式为：内连接的格式为：FROM FROM 表表1 INNER JOIN 1 INNER JOIN 表表2 ON 2 ON 连接条件的一般格式为：连接条件的一般格式为：1 2 注意注意：两个表的连接列必须是可比较的两个表的连接列必须是可比较的，即必须是语，即必须是语义相同的列，否则比较将是无意义的。义相同的列，否则比较将是无意义的。当比较运算符为等号（）时，称为当比较运算符为等号（）时，称为等值连接等值连接，使用其他运算符的连接称为使用其他运算符的连接称为非等值连接非等值连接

32、。30例查询每个学生及其修课的情况。例查询每个学生及其修课的情况。SELECT SELECT*FROM Student FROM Student INNER JOININNER JOIN SC SCON ON Student.Sno=SC.SnoStudent.Sno=SC.SnoSno Sno Sname Sname Ssex Ssex Sage Sage Sdept Sdept Sno Sno Cno Cno Grade Grade XKLB XKLB 9512101 9512101 李勇李勇 男男1919计算机系计算机系95121019512101c01c019090必修必修951210

33、1 9512101 李勇李勇 男男1919计算机系计算机系95121019512101c02c028686选修选修9512101 9512101 李勇李勇 男男1919计算机系计算机系95121019512101c06c06NULLNULL必修必修9512102 9512102 刘晨刘晨 男男2020计算机系计算机系95121029512102c02c027878选修选修9512102 9512102 刘晨刘晨 男男2020计算机系计算机系95121029512102c04c046666必修必修9512102 9512102 吴宾吴宾 女女2121信息系信息系95121029512102c01

34、c018282选修选修9512102 9512102 吴宾吴宾 女女2121信息系信息系95121029512102c02c027575选修选修9512102 9512102 吴宾吴宾 女女2121信息系信息系95121029512102c04c049292必修必修31 例去掉上例中的重复列。例去掉上例中的重复列。SELECT SELECT Student.Sno,Sname,Ssex,Sage,Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade,XKLB Sdept,Cno,Grade,XKLB FROM Student JOIN SC FROM Stud

35、ent JOIN SC ON Student.Sno=SC.Sno ON Student.Sno=SC.Sno 根据要根据要查询的列数据查询的列数据以及数据的以及数据的选选择条件所涉及的列择条件所涉及的列，可以决定要对，可以决定要对哪些表进行连接操作。哪些表进行连接操作。例查询计算机系学生的修课情况，要求列出例查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。学生的名字、所修课的课程号和成绩。SELECT Sname,Cno,Grade SELECT Sname,Cno,Grade FROM Student JOIN SC FROM Student JOIN SC ON S

36、tudent.Sno=SC.Sno ON Student.Sno=SC.Sno WHERE Sdept=WHERE Sdept=计算机系计算机系 32可以为表提供别名，其格式为：可以为表提供别名，其格式为：AS AS 例如：使用别名时上例可写为：例如：使用别名时上例可写为：SELECT Sname,Cno,Grade SELECT Sname,Cno,Grade FROM FROM Student SStudent S JOIN SC JOIN SC ON S.Sno=SC.Sno ON S.Sno=SC.Sno WHERE Sdept=WHERE Sdept=计算机系计算机系 注意注意：当为

37、表指定了别名时，在查询语句中：当为表指定了别名时，在查询语句中的其他地方，所有用到表名的地方都要使用的其他地方，所有用到表名的地方都要使用别名，而不能再使用原表名。别名，而不能再使用原表名。33自连接自连接 是一种是一种特殊的内连接特殊的内连接，它是指相互连接的表在，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。物理上为同一张表，但可以在逻辑上分为两张表。使用自连接时必须为两个表取别名使用自连接时必须为两个表取别名。例查询与刘晨在同一个系学习的学生的姓例查询与刘晨在同一个系学习的学生的姓名和所在的系。名和所在的系。SELECT S2.Sname,S2.SdeptSELECT

38、S2.Sname,S2.Sdept FROM FROM Student S1Student S1 JOIN JOIN Student S2Student S2 ON S1.Sdept=S2.Sdept ON S1.Sdept=S2.Sdept WHERE S1.Sname=WHERE S1.Sname=刘晨刘晨 AND S2.Sname!=AND S2.Sname!=刘晨刘晨 结果为：结果为：Sname Sname Sdept Sdept 李勇李勇 计算机系计算机系 王敏王敏 计算机系计算机系 34外连接外连接 外连接是只限制一张表中的数据必须满外连接是只限制一张表中的数据必须满足连接条件，而

39、另一张表中数据可以不满足足连接条件，而另一张表中数据可以不满足连接条件。连接条件。ANSIANSI方式的外连接的语法格式为：方式的外连接的语法格式为：FROM FROM 表表1 LEFT|RIGHT OUTER 1 LEFT|RIGHT OUTER JOIN JOIN 表表2 ON 2 ON 35例查询学生的修课情况，包括选修了课程的学生例查询学生的修课情况，包括选修了课程的学生 和没有修课的学生。和没有修课的学生。SELECT Student.Sno,Sname,Cno,Grade SELECT Student.Sno,Sname,Cno,Grade FROM Student FROM St

40、udent LEFT OUTER JOIN SCLEFT OUTER JOIN SCON Student.Sno=SC.SnoON Student.Sno=SC.SnoSno Sno Sname Sname Cno Cno Grade Grade 9512101 9512101 李勇李勇 c01c0190909512101 9512101 李勇李勇 c02c0286869512101 9512101 李勇李勇 c06c06NULLNULL9512102 9512102 刘晨刘晨 c02c0278789512102 9512102 刘晨刘晨 c04c04666695121039512103王敏王

41、敏NULLNULLNULLNULL95121019512101张立张立NULLNULLNULLNULL9512102 9512102 吴宾吴宾 c01c0182829512102 9512102 吴宾吴宾 c02c0275759512102 9512102 吴宾吴宾 c04c0492929512102 9512102 吴宾吴宾 c05c05505036四、子查询四、子查询 在在SQLSQL语言中，一个语言中，一个SELECTSELECTFROMFROMWHEREWHERE语语句称为一个句称为一个查询块查询块。如果一个如果一个SELECTSELECT语句是嵌套在一个语句是嵌套在一个SELECTS

42、ELECT、INSERTINSERT、UPDATEUPDATE或或DELETEDELETE语句中，则称之为语句中，则称之为子查子查询或内层查询询或内层查询；而包含子查询的语句则称；而包含子查询的语句则称为主查为主查询或外层查询询或外层查询。子查询要。子查询要写在圆括号中写在圆括号中。子查询语句通常情况下一般是子查询语句通常情况下一般是用在外层查询用在外层查询的的WHEREWHERE子句或子句或HAVINGHAVING子句中，与比较运算符或子句中，与比较运算符或逻辑运算符一起构成查询条件逻辑运算符一起构成查询条件。37 使用子查询进行基于集合的测试使用子查询进行基于集合的测试 使用子查询进行基于

43、集合的测试时，通使用子查询进行基于集合的测试时，通过运算符过运算符ININ或或NOT INNOT IN，将一个表达式的值与将一个表达式的值与子查询返回的结果集进行比较子查询返回的结果集进行比较。形式为：形式为：WHERE WHERE 表达式表达式 NOT IN(NOT IN(子查询子查询)实现步骤：实现步骤：先执行子查询先执行子查询 在子查询的结果基础上再执行外层查询在子查询的结果基础上再执行外层查询38SELECT Sno,Sname,Sdept FROM StudentSELECT Sno,Sname,Sdept FROM Student WHERE Sdept WHERE Sdept I

44、NIN(SELECT Sdept FROM Student SELECT Sdept FROM Student WHERE Sname=WHERE Sname=刘晨刘晨 )例查询与例查询与“刘晨刘晨”在同一个系学习的学生。在同一个系学习的学生。分析：分析：a)a)确定确定“刘晨刘晨”所在系所在系b)b)在子查询结果上查找所有在此系学习的学生在子查询结果上查找所有在此系学习的学生39例查询选修了例查询选修了“数据库基础数据库基础”课程的学生课程的学生的学号、姓名。的学号、姓名。SELECT Sno,Sname FROM StudentSELECT Sno,Sname FROM StudentWH

45、ERE Sno INWHERE Sno IN (SELECT Sno FROM SCSELECT Sno FROM SC WHERE Cno IN WHERE Cno IN (SELECT Cno FROM CourseSELECT Cno FROM Course WHERE Cname=WHERE Cname=数据库基础数据库基础 )用多表连接实现：用多表连接实现：SELECT Student.Sno,Sname FROM Student SELECT Student.Sno,Sname FROM Student JOIN SC ON Student.Sno=SC.Sno JOIN SC O

46、N Student.Sno=SC.Sno JOIN Course ON Course.Cno=SC.Cno JOIN Course ON Course.Cno=SC.Cno WHERE Cname=WHERE Cname=数据库基础数据库基础 40使用子查询进行比较测试使用子查询进行比较测试 形式为：形式为：WHERE WHERE 表达式表达式 比较运算符比较运算符 (子查询子查询)注意注意：使用子查询进行比较测试时，要求：使用子查询进行比较测试时，要求子查询语句必须是返回单值子查询语句必须是返回单值的查询语句。的查询语句。41例查询修了例查询修了c02c02课程且成绩高于此课程的课程且成绩高

47、于此课程的 平均成绩的学生的学号和成绩。平均成绩的学生的学号和成绩。分析：分析：计算计算c02c02的平均成绩的平均成绩 在子查询结果上查找成绩大于此平均成绩且在子查询结果上查找成绩大于此平均成绩且 选修选修c02c02课程的学生学号和成绩课程的学生学号和成绩 SELECT Sno,Grade FROM SCSELECT Sno,Grade FROM SC WHERE Cno=c02 WHERE Cno=c02 and and Grade Grade (SELECT AVG(Grade)from SC SELECT AVG(Grade)from SC WHERE Cno=c02 WHERE C

48、no=c02)42使用子查询进行存在性测试使用子查询进行存在性测试 使用子查询进行存在性测试时，一般使用使用子查询进行存在性测试时，一般使用EXISTSEXISTS谓词。谓词。形式为：形式为：WHERE NOT EXISTS(WHERE NOT EXISTS(子查询子查询)带带EXISTSEXISTS谓词的子查询不返回查询的数据，谓词的子查询不返回查询的数据，只产生逻辑真值和逻辑假值。只产生逻辑真值和逻辑假值。注意注意：带带EXISTSEXISTS谓词的查询是谓词的查询是先执行外层查先执行外层查询，然后再执行内层查询询，然后再执行内层查询。由外层查询的值决。由外层查询的值决定内层查询的结果；内

49、层查询的执行次数由外定内层查询的结果；内层查询的执行次数由外层查询的结果数决定。层查询的结果数决定。43例查询选修了例查询选修了c01c01号课程的学生姓名。号课程的学生姓名。SELECT Sname FROM Student SELECT Sname FROM Student WHERE WHERE EXISTSEXISTS(SELECT SELECT*FROM SC FROM SC WHERE Sno=Student.Sno WHERE Sno=Student.Sno AND Cno=c01 AND Cno=c01 )44连接查询：连接查询：SELECT Sname FROM Studen

50、t JOIN SCSELECT Sname FROM Student JOIN SC ON SC.Sno=Student.Sno where Cno=c01 ON SC.Sno=Student.Sno where Cno=c01嵌套子查询：嵌套子查询：SELECT Sname FROM StudentSELECT Sname FROM Student Where Sno In Where Sno In (SELECT Sno FROM SC WHERE Cno=c01SELECT Sno FROM SC WHERE Cno=c01)一种查询可用不同方法实现，但多表连接一种查询可用不同方法实现，

51、但多表连接查询效率查询效率 嵌套子查询效率嵌套子查询效率 相关子查相关子查询效率询效率45例查询没有选修例查询没有选修c01c01课程的学生的姓名课程的学生的姓名 和所在系和所在系1)1)多表连接实现多表连接实现 SELECT DISTINCT Sname,Sdept SELECT DISTINCT Sname,Sdept FROM Student S FROM Student S JOIN SC ON S.Sno=SC.Sno JOIN SC ON S.Sno=SC.Sno WHERE Cno!=c01 WHERE Cno!=c01462)2)嵌套子查询实现嵌套子查询实现 在子查询中否定在子

52、查询中否定 SELECT Sname,Sdept FROM Student SELECT Sname,Sdept FROM Student WHERE Sno WHERE Sno ININ （SELECT Sno FROM SCSELECT Sno FROM SC WHERE Cno!=c01 WHERE Cno!=c01 ）在外层查询中否定在外层查询中否定 SELECT Sname,Sdept FROM Student SELECT Sname,Sdept FROM Student WHERE Sno WHERE Sno NOT INNOT IN （SELECT Sno FROM SCSEL

53、ECT Sno FROM SC WHERE Cno=c01 WHERE Cno=c01 ）473)3)相关子查询实现相关子查询实现 在子查询中否定在子查询中否定 SELECT Sname,Sdept FROM Student SELECT Sname,Sdept FROM Student WHERE WHERE EXISTSEXISTS （SELECT SELECT*FROM SC FROM SC WHERE Sno=Student.Sno AND Cno!=c01 WHERE Sno=Student.Sno AND Cno!=c01）在外层查询中否定在外层查询中否定 SELECT Sname

54、,Sdept FROM Student SELECT Sname,Sdept FROM Student WHERE WHERE NOT EXISTSNOT EXISTS （SELECT SELECT*FROM SC FROM SC WHERE Sno=Student.Sno AND Cno!=c01 WHERE Sno=Student.Sno AND Cno!=c01）48 将否定放置在外层查询中时结果正确，原因将否定放置在外层查询中时结果正确，原因在于不同的查询执行的机制不同。在于不同的查询执行的机制不同。l对多表连接查询对多表连接查询，所有的条件是在连接之后的，所有的条件是在连接之后的结果

55、表上进行，且是逐行判断，一旦有满足要结果表上进行，且是逐行判断，一旦有满足要求的数据，则此行作为结果输出。求的数据，则此行作为结果输出。l对含有嵌套子查询对含有嵌套子查询，首先执行子查询，再在子，首先执行子查询，再在子查询结果基础上执行外层查询，而在子查询中查询结果基础上执行外层查询，而在子查询中也是逐行判断，当有满足条件的数据时，即将也是逐行判断，当有满足条件的数据时，即将此行数据作为外层查询的一个比较条件。此行数据作为外层查询的一个比较条件。通常情况，通常情况，对于否定条件的查询都应使用子对于否定条件的查询都应使用子查询实现，且将否定放在外层查询实现，且将否定放在外层。494.2 4.2

56、数据更改功能数据更改功能 一、插入数据一、插入数据 插入数据的插入数据的INSERTINSERT语句的格式为：语句的格式为：INSERT INTO INSERT INTO ()VALUES VALUES（值列表）（值列表）使用插入语句时应注意：使用插入语句时应注意：值列表中的值与列名表中的列按位置顺序对应，值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致。它们的数据类型必须一致。如果如果 后边没有指明列名，则新插入记录后边没有指明列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且的值的顺序必须与表中列的定义顺序一致，且每一个列均有值（可以为空）。每一个列均有值（可以为空

57、）。50例将新生记录（例将新生记录（9502095020，陈冬，男，信息，陈冬，男，信息系，系，1818岁）插入到岁）插入到StudentStudent表中。表中。INSERT INTO Student VALUES INSERT INTO Student VALUES (9521105,(9521105,陈冬陈冬,男男,18,18,信息系信息系)例在例在SCSC表中插入一新记录，成绩暂缺。表中插入一新记录，成绩暂缺。INSERT INTO SC(Sno,Cno)INSERT INTO SC(Sno,Cno)VALUES(9521105,c01)VALUES(9521105,c01)51二、更

58、新数据二、更新数据 UPDATEUPDATE语句的语法格式为：语句的语法格式为：UPDATE UPDATE SET SET ,n n WHERE WHERE 1 1、无条件更新、无条件更新 例将所有学生的年龄加例将所有学生的年龄加1 1。UPDATE Student SET Sage=Sage+UPDATE Student SET Sage=Sage+1 1522 2、有条件更新、有条件更新 一种是基于本表条件的更新一种是基于本表条件的更新 一种是基于其他表条件的更新一种是基于其他表条件的更新(1)(1)基于本表条件的更新。基于本表条件的更新。例将例将95121019512101学生的年龄改为

59、学生的年龄改为2121岁。岁。UPDATE Student SET Sage=21UPDATE Student SET Sage=21WHERE Sno=9512101WHERE Sno=951210153(2)(2)基基于其他表条件的更新于其他表条件的更新例将计算机系全体学生的成绩加例将计算机系全体学生的成绩加5 5分。分。u用子查询实现用子查询实现 UPDATE SC SET Grade=Grade+5UPDATE SC SET Grade=Grade+5 WHERE Sno IN WHERE Sno IN (SELECT Sno FROM Student(SELECT Sno FROM

60、Student WHERE Sdept=WHERE Sdept=计算机系计算机系)u用多表连接实现用多表连接实现 UPDATE SC SET Grade=Grade+5UPDATE SC SET Grade=Grade+5 FROM SC JOIN Student FROM SC JOIN Student ON SC.Sno=Student.Sno ON SC.Sno=Student.Sno WHERE Sdept=WHERE Sdept=计算机系计算机系 54三、删除数据三、删除数据DELETEDELETE语句的语法格式为：语句的语法格式为：DELETE FROM DELETE FROM W

61、HERE WHERE 无条件删除无条件删除 无条件删除是删除表中全部数据，但保留无条件删除是删除表中全部数据，但保留表的结构。表的结构。例例1 1删除所有学生的选课记录。删除所有学生的选课记录。DELETE FROM SCDELETE FROM SC-SC-SC成空表成空表55有条件删除有条件删除分为两种情况：分为两种情况：v一种是基于本表条件的删除，一种是基于本表条件的删除，v另一种是基于其他表条件的删除。另一种是基于其他表条件的删除。(1)(1)基于本表条件的删除。基于本表条件的删除。例例2 2删除所有不及格学生的修课记录。删除所有不及格学生的修课记录。DELETE FROM SC WHE

62、RE Grade 60DELETE FROM SC WHERE Grade 6056(2)(2)基于其他表条件的删除基于其他表条件的删除例例3 3删除计算机系不及格学生的修课记录删除计算机系不及格学生的修课记录u用子查询实现用子查询实现 DELETE FROM SCDELETE FROM SC WHERE Grade 60 AND Sno IN WHERE Grade 60 AND Sno IN (SELECT Sno FROM Student (SELECT Sno FROM Student WHERE Sdept=WHERE Sdept=计算机系计算机系)u用多表连接实现用多表连接实现 D

63、ELETE FROM SC DELETE FROM SC FROM SC JOIN Student FROM SC JOIN Student ON SC.Sno=Student.Sno ON SC.Sno=Student.Sno WHERE Sdept=WHERE Sdept=计算机系计算机系 AND Grade 60 AND Grade 6057小结小结 SQLSQL中的中的数据查询数据查询功能。在查询语句部分我们介绍功能。在查询语句部分我们介绍了单表查询和多表连接查询，包括无条件查询、有了单表查询和多表连接查询，包括无条件查询、有条件查询、分组查询以及排序等功能。多表连接查条件查询、分组查

64、询以及排序等功能。多表连接查询介绍了内连接、自连接、左外连接和右外连接。询介绍了内连接、自连接、左外连接和右外连接。对条件查询介绍了多种实现方法，包括用子查询实对条件查询介绍了多种实现方法，包括用子查询实现和用连接查询实现等。现和用连接查询实现等。数据的数据的更改操作更改操作，包括数据的插入、修改和删除。，包括数据的插入、修改和删除。对删除和更新操作，介绍了无条件的操作和有条件对删除和更新操作，介绍了无条件的操作和有条件的操作，对有条件的删除和更新操作我们又介绍了的操作，对有条件的删除和更新操作我们又介绍了用多表连接实现和用子查询实现两种方法。用多表连接实现和用子查询实现两种方法。58嵌入式嵌

65、入式SQL语言语言SQLSQL语言使用方式：语言使用方式：l 直接使用方式直接使用方式，即用户在系统终端设备上键，即用户在系统终端设备上键入入SQLSQL语句，以直接交互的方式使用数据库系语句，以直接交互的方式使用数据库系统。统。l 嵌入式使用方式嵌入式使用方式，即允许用户在程序设计语，即允许用户在程序设计语言中潜入言中潜入SQLSQL语句，调用语句，调用DBMSDBMS的功能。的功能。l 第二中方式使用的第二中方式使用的SQLSQL语言称为语言称为嵌入式嵌入式SQLSQL语语言言，相应的允许潜入，相应的允许潜入SQLSQL语句的程序设计语言语句的程序设计语言被称为被称为宿主语言宿主语言。l

66、下面以下面以C C语言为宿主语言，介绍使用嵌入式语言为宿主语言，介绍使用嵌入式SQLSQL语言。语言。591 1、嵌入、嵌入SQLSQL语句的宿主语言源程序的编译执行语句的宿主语言源程序的编译执行 宿主语言编译器不能识别和接受宿主语言编译器不能识别和接受SQLSQL语句，语句，为了在处理宿主语言的过程中能区别为了在处理宿主语言的过程中能区别C C语言和语言和SQLSQL语句，在宿主语言源程序中，语句，在宿主语言源程序中，每个每个SQLSQL语句语句需在其句首加需在其句首加EXEC SQLEXEC SQL，在其句尾加，在其句尾加“；”。编译执行过程如下如所示。编译执行过程如下如所示。60预编译预编译编译编译连接装配连接装配执行执行包含包含SQL语句的语句的宿主语言源程序宿主语言源程序包含包含SQL函数调用函数调用目标程序目标程序可执行程序可执行程序SQL函数库函数库612 2、宿主语言与、宿主语言与SQLSQL语言之间的数据传输语言之间的数据传输 数据传输通过数据传输通过宿主变量宿主变量实现。宿主变量是实现。宿主变量是嵌入式嵌入式SQLSQL语言中可以引用的语言中可以引用的C C语言变量