数据库原理及应用第二版第4章 数据操作

1、1第 4 章 数据操作 4.1 数据查询功能4.2 数据更改功能2一、查询语句的基本结构 查询语句是从数据库中检索满足条件的数据 基本结构可描述为： SELECT - 需要哪些列 FROM - 来自于哪些表 WHERE - 根据什么条件 GROUP BY HAVING ORDER BY 4.1 数据查询功能 3二、简单查询 选择表中若干列 1、查询指定的列 例. 查询全体学生的学号与姓名 SELECT Sno,Sname FROM Student Sno sname 9512101 李勇 9512102 刘晨 9512103 王敏 9521101 张立 9521102 吴宾 9521103 张

2、海 9531101 钱小平 9531102 王大力 结果为 ：42、查询全部列例查询全体学生的记录SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student等价于：SELECT * FROM Student结果为 ：Sno Sname Ssex Sage Sdept 9512101 李勇 男 19计算机系 9512102 刘晨 男 20计算机系 9512103 王敏 女 20计算机系 9521101 张立 男 22信息系 9521102 吴宾 女 21信息系 9521103 张海 男 20信息系 9531101 钱小平 女 18数学系 9531102 王大力 男

3、 19数学系 53、查询经过计算的列 SELECT子句中的可以是表中存在的属性列，也可以是表达式、常量或者函数。例查询全体学生的姓名及其出生年份 SELECT Sname,2002 - Sage FROM Student 结果为: Sname （无列名 )李勇 1982 刘晨 1981 王敏 1981 张立 1979 吴宾 1980 张海 1981钱小平 1983王大力 1982 6改变列标题的语法格式为： 列名 | 表达式 AS 列标题 或：列标题 列名 | 表达式例如，对于上例可写成： SELECT Sname 姓名,2002-Sage 年份 FROM Student 结果为：姓名年份李勇

4、 1982刘晨1981 王敏1981 张立1979吴宾1980张海1981钱小平1983王大力 19827选择表中的若干元组 1、消除取值相同的行 例在选课表(SC)中查询有哪些学生修了课程，要求列出学生的学号。 SELECT Sno FROM SC 在这个结果中有许多重复的行。 SQL中的DISTINCT关键字可以去掉结果表中的重复行。 SELECT DISTICT Sno FROM SC 则执行结果为 ：Sno9512101951210295211029521103953110182、查询满足条件的元组 查询满足条件的元组是通过WHERE子句实现的。WHERE子句常用的查询条件如表3-13

5、所示。 查询条件 谓 词 比较（比较运算符） =, , =, , =, （或!=）NOT+上述比较运算符 确定范围 BETWEEN AND,NOT BETWEEN AND 确定集合 IN, NOT IN 字符匹配 LIKE, NOT LIKE 空值 IS NULL, IS NOT NULL 多重条件（逻辑谓词) AND, OR 9（1）比较大小 例查询计算机系全体学生的姓名。SELECT Sname FROM Student WHERE Sdept = 计算机系 结果为 ：Sname李勇 刘晨 王敏 10 Sname Sage 李勇 19钱小平 18王大力 19注意：取反操作的执行效率比较低例

6、查询所有年龄在20岁以下的学生的姓名及年龄 SELECT Sname,Sage FROM Student WHERE Sage = 20结果为： 11（2）确定范围 BETWEENAND和NOT BETWEENAND是一个逻辑运算符，可以用来查找属性值在或不在指定范围内的元组。BETWEENAND的格式为：列名 | 表达式 NOT BETWEEN 下限值 AND 上限值 BETWEENAND一般用于对数值型数据进行比较。列名或表达式的类型要与下限值或上限值的类型相同。12 例查询年龄在2023岁之间的学生的姓名、所在系和年龄 SELECT Sname, Sdept, Sage FROM Stu

7、dent WHERE Sage BETWEEN 20 AND 23 此句等价于：SELECT Sname, Sdept, Sage FROM Student WHERE Sage =20 AND Sage=23 结果为： Sname Sdept Sage 刘晨 计算机系 20 王敏 计算机系 20 张立 信息系 22吴宾 信息系 21张海 信息系 2013 例查询年龄不在2023之间的学生姓名、所在系和年龄。SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23此句等价于：SELECT Sname, Sde

8、pt, Sage FROM Student WHERE Sage 23结果为：Sname Sdept Sage 李勇 计算机系 19 钱小平 数学系 18王大力 数学系 1914（3）确定集合 IN是一个逻辑运算符，可以用来查找属性值属于指定集合的元组。使用IN的格式为： 列名 NOT IN （常量1, 常量2, 常量n） IN的含义为：当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录； NOT IN的含义正好相反：当列中的值与某个常量值相同时，则结果为False，表明此记录为不符合查询条件的记录；15例查询信息系、数学系和计算机系学生的姓名和性别。 SE

9、LECT Sname, Ssex FROM Student WHERE Sdept IN (信息系,数学系,计算机系)此句等价于： SELECT Sname, Ssex FROM Student WHERE Sdept = 信息系 OR Sdept = 数学系 OR Sdept = 计算机系16（4）字符匹配 LIKE用于查找指定列名与匹配串常量匹配的元组。通配符用于表示任意的字符或字符串。在LIKE运算符前边也可以使用NOT运算符，表示对结果取反。 LIKE运算符的一般形式为： 列名 NOT LIKE 匹配串中可包含如下四种通配符： _：匹配任意一个字符； %：匹配0个或多个字符； ：匹配

10、中的任意一个字符； ：不匹配 中的任意一个字符。17例查询姓张的学生的详细信息。SELECT * FROM Student WHERE Sname LIKE 张%结果为：Sno Sname Ssex Sage Sdept 9521101 张立 男 22信息系 9521103 张海 男 20信息系 18例查询学生表中姓张、姓李和姓刘的学生的情况。 SELECT * FROM Student WHERE Sname LIKE 张李刘%结果为： Sno Sname Ssex Sage Sdept 9512101 李勇 男 19计算机系 9512102 刘晨 男20计算机系9521101 张立 男 2

11、2信息系 9521103 张海 男 20信息系 19 例查询名字中第2个字为小或大字的学生的姓名和学号。 SELECT Sname, Sno FROM Student WHERE Sname LIKE _小大% 结果为： Sname Sno 钱小平 9531101 王大力 953110220（5）涉及空值的查询 空值(NULL)在数据库中有特殊的含义，它表示不确定的值。判断某个值是否为NULL值，不能使用普通的比较运算符（=、!=等），而只能使用专门的判断NULL值的子句来完成。 判断取值为空的语句格式为：列名IS NULL 判断取值不为空的语句格式为： 列名 IS NOT NULL例查询无考

12、试成绩的学生的学号和相应的课程号 SELECT Sno, Cno FROM SC WHERE Grade IS NULL21（6）多重条件查询 在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询。用AND连接的条件表示必须全部满足所有的条件的结果才为True，用OR连接的条件表示只要满足其中一个条件结果即为True。例查询计算机系年龄在20岁以下的学生姓名。 SELECT Sname FROM Student WHERE Sdept=CS AND Sage2022对查询结果进行排序 排序子句的格式为： ORDER BY ASC | DESC ， n 其中为排序的依据列，可以是列名

13、或列的别名。 当指定多个排序依据列时，首先按排在最前面的列进行排序，如果排序后存在两个或两个以上列值相同的记录，则对这些值相同的记录再依据排在第二位的列进行排序，依此类推。23例将学生按年龄的升序排序。SELECT * FROM Student ORDER BY Sage例查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。 SELECT * FROM Student ORDER BY Sdept, Sage DESC24使用聚合函数汇总数据 计算函数也称为集合函数或聚合函数、聚集函数，其作用是对一组值进行计算并返回一个单值。SQL提供的计算函数有：COUNT（ *

14、 ）：统计表中元组个数；COUNT（）：统计本列列值个数；SUM（）：AVG（）：MAX（）：MIN（）： 上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值。 25例统计学生总人数。SELECT COUNT（*） FROM Student 例统计选修了课程的学生的人数。SELECT COUNT（DISTINCT Sno） FROM SC例查询选修了C01号课程的学生的最高分和最低分。 SELECT MAX(Grade) , MIN(Grade) FROM SC WHERE Cno=C01 注意：计算函数不能出现在WHERE子句中。例：查询年龄最大的学生的姓名，如下写法是错误

15、的： SELECT Sname FROM Student WHERE Sage = MAX(Sage)26对查询结果进行分组计算 GROUP BY分组的目的是细化计算函数的作用对象。在一个查询语句中，可以使用任意多个列进行分组。需要注意的是：如果使用了分组子句，则查询列表中的每个列必须要么是分组依据列（group by 后边的列），要么是计算函数。 使用GROUP BY时，如果在SELECT的查询列表中包含计算函数，则是针对每个组计算出一个汇总值，从而实现对查询结果的分组统计。 分组语句的一般形式为： GROUP BY ， n HAVING 27使用GROUP BY例统计每门课程的选课人数，列

16、出课程号和人数。SELECT Cno as 课程号,COUNT(Sno) as 选课人数 FROM SC GROUP BY Cno 查询结果为 ：课程号 选课人数 c01 3C024c042c053c06 228使用HAVING HAVING子句用于对分组后的结果再进行过滤，它的功能有点像WHERE子句，但它用于组而不是对单个记录。在HAVING子句中可以使用计算函数，但在WHERE子句中则不能。HAVING通常与GROUP BY子句一起使用。例查询修了3门以上课程的学生的学号。SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3 结果为：Sno9

17、52110229三、多表连接查询 内连接 只有满足连接条件的元组才能作为结果输出 内连接的格式为： FROM 表1 INNER JOIN 表2 ON 连接条件的一般格式为： 注意：两个表的连接列必须是可比较的，即必须是语义相同的列，否则比较将是无意义的。 当比较运算符为等号（）时，称为等值连接，使用其他运算符的连接称为非等值连接。30例查询每个学生及其修课的情况。 SELECT * FROM Student INNER JOIN SCON Student.Sno = SC.SnoSno Sname Ssex Sage Sdept Sno Cno Grade XKLB 9512101 李勇 男1

18、9计算机系9512101c0190必修9512101 李勇 男19计算机系9512101c0286选修9512101 李勇 男19计算机系9512101c06NULL必修9512102 刘晨 男20计算机系9512102c0278选修9512102 刘晨 男20计算机系9512102c0466必修9512102 吴宾 女21信息系9512102c0182选修9512102 吴宾 女21信息系9512102c0275选修9512102 吴宾 女21信息系9512102c0492必修9512102 吴宾 女21信息系9512102c0550必修9521103 张海 男20信息系9521103c02

19、68选修9521103 张海 男20信息系9521103c06NULL必修9531101钱小平 女18数学系9531101c0180选修9531101钱小平 女18数学系9531101c0595必修9531102 王大力 男19数学系9531102c0585必修31 例去掉上例中的重复列。SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade, XKLB FROM Student JOIN SC ON Student.Sno = SC.Sno 根据要查询的列数据以及数据的选择条件所涉及的列，可以决定要对哪些表进行连接操作。例查询计算机系

20、学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。 SELECT Sname, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = 计算机系32可以为表提供别名，其格式为： AS 例如：使用别名时上例可写为： SELECT Sname, Cno, Grade FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Sdept = 计算机系 注意：当为表指定了别名时，在查询语句中的其他地方，所有用到表名的地方都要使用别名，而不能再使用原表名。33自连接 是一种特殊

21、的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。使用自连接时必须为两个表取别名。 例查询与刘晨在同一个系学习的学生的姓名和所在的系。 SELECT S2.Sname, S2.Sdept FROM Student S1 JOIN Student S2 ON S1.Sdept = S2.Sdept WHERE S1.Sname = 刘晨 AND S2.Sname != 刘晨 结果为：Sname Sdept 李勇 计算机系 王敏 计算机系 34外连接 外连接是只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。ANSI方式的外连接的语法格式为： FRO

22、M 表1 LEFT|RIGHT OUTER JOIN 表2 ON 35例查询学生的修课情况，包括选修了课程的学生 和没有修课的学生。SELECT Student.Sno, Sname, Cno, Grade FROM Student LEFT OUTER JOIN SCON Student.Sno = SC.SnoSno Sname Cno Grade 9512101 李勇 c01909512101 李勇 c02869512101 李勇 c06NULL9512102 刘晨 c02789512102 刘晨 c04669512103王敏NULLNULL9512101张立NULLNULL951210

23、2 吴宾 c01829512102 吴宾 c02759512102 吴宾 c04929512102 吴宾 c05509521103 张海 c02689521103 张海 c06NULL9531101钱小平 c01809531101钱小平 c05959531102 王大力 c058536四、子查询 在SQL语言中，一个SELECTFROMWHERE语句称为一个查询块。 如果一个SELECT语句是嵌套在一个SELECT、INSERT、UPDATE或DELETE语句中，则称之为子查询或内层查询；而包含子查询的语句则称为主查询或外层查询。子查询要写在圆括号中。 子查询语句通常情况下一般是用在外层查询的

24、WHERE子句或HAVING子句中，与比较运算符或逻辑运算符一起构成查询条件。37使用子查询进行基于集合的测试 使用子查询进行基于集合的测试时，通过运算符IN或NOT IN，将一个表达式的值与子查询返回的结果集进行比较。形式为： WHERE 表达式 NOT IN (子查询)实现步骤： 先执行子查询在子查询的结果基础上再执行外层查询38SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname = 刘晨 ) 例查询与“刘晨”在同一个系学习的学生。分析：确定“刘晨”所在系在

25、子查询结果上查找所有在此系学习的学生39例查询选修了“数据库基础”课程的学生的学号、姓名。SELECT Sno, Sname FROM StudentWHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN ( SELECT Cno FROM Course WHERE Cname = 数据库基础 ) )用多表连接实现： SELECT Student.Sno, Sname FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON Course.Cno = SC.Cno WHERE Cname = 数

26、据库基础40使用子查询进行比较测试 形式为： WHERE 表达式 比较运算符 (子查询) 注意：使用子查询进行比较测试时，要求子查询语句必须是返回单值的查询语句。41例查询修了c02课程且成绩高于此课程的 平均成绩的学生的学号和成绩。分析：计算c02的平均成绩在子查询结果上查找成绩大于此平均成绩且 选修c02课程的学生学号和成绩 SELECT Sno , Grade FROM SC WHERE Cno = c02 and Grade ( SELECT AVG(Grade) from SC WHERE Cno = c02 )42使用子查询进行存在性测试 使用子查询进行存在性测试时，一般使用EXI

27、STS谓词。形式为： WHERE NOT EXISTS (子查询) 带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值和逻辑假值。 注意：带EXISTS谓词的查询是先执行外层查询，然后再执行内层查询。由外层查询的值决定内层查询的结果；内层查询的执行次数由外层查询的结果数决定。43例查询选修了c01号课程的学生姓名。 SELECT Sname FROM Student WHERE EXISTS ( SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = c01 )44连接查询： SELECT Sname FROM Student JOIN S

28、C ON SC.Sno=Student.Sno where Cno=c01嵌套子查询： SELECT Sname FROM Student Where Sno In ( SELECT Sno FROM SC WHERE Cno=c01 ) 一种查询可用不同方法实现，但多表连接查询效率 嵌套子查询效率 相关子查询效率45例查询没有选修c01课程的学生的姓名 和所在系1)多表连接实现 SELECT DISTINCT Sname,Sdept FROM Student S JOIN SC ON S.Sno=SC.Sno WHERE Cno !=c01462)嵌套子查询实现 在子查询中否定 SELECT

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

30、否定 SELECT Sname,Sdept FROM Student WHERE NOT EXISTS （ SELECT * FROM SC WHERE Sno=Student.Sno AND Cno !=c01）48 将否定放置在外层查询中时结果正确，原因在于不同的查询执行的机制不同。对多表连接查询，所有的条件是在连接之后的结果表上进行，且是逐行判断，一旦有满足要求的数据，则此行作为结果输出。对含有嵌套子查询，首先执行子查询，再在子查询结果基础上执行外层查询，而在子查询中也是逐行判断，当有满足条件的数据时，即将此行数据作为外层查询的一个比较条件。 通常情况，对于否定条件的查询都应使用子查询实

31、现，且将否定放在外层。494.2 数据更改功能 一、插入数据 插入数据的INSERT语句的格式为： INSERT INTO () VALUES （值列表） 使用插入语句时应注意：值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致。如果后边没有指明列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且每一个列均有值（可以为空）。50例将新生记录（95020，陈冬，男，信息系，18岁）插入到Student表中。 INSERT INTO Student VALUES (9521105,陈冬,男,18,信息系)例在SC表中插入一新记录，成绩暂缺。 INSERT INTO SC(Sno

32、, Cno) VALUES (9521105, c01)51二、更新数据 UPDATE语句的语法格式为： UPDATE SET , n WHERE 1、无条件更新 例将所有学生的年龄加1。 UPDATE Student SET Sage = Sage + 1522、有条件更新 一种是基于本表条件的更新 一种是基于其他表条件的更新(1)基于本表条件的更新。例将9512101学生的年龄改为21岁。UPDATE Student SET Sage = 21WHERE Sno = 951210153(2)基于其他表条件的更新例将计算机系全体学生的成绩加5分。用子查询实现 UPDATE SC SET Gr

33、ade = Grade + 5 WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept = 计算机系)用多表连接实现 UPDATE SC SET Grade = Grade + 5 FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Sdept= 计算机系54三、删除数据DELETE语句的语法格式为：DELETE FROM WHERE 无条件删除 无条件删除是删除表中全部数据，但保留表的结构。例1删除所有学生的选课记录。 DELETE FROM SC- SC成空表55有条件删除分为两种情况：一种是基于

34、本表条件的删除，另一种是基于其他表条件的删除。(1)基于本表条件的删除。 例2删除所有不及格学生的修课记录。 DELETE FROM SC WHERE Grade 6056(2)基于其他表条件的删除例3删除计算机系不及格学生的修课记录用子查询实现 DELETE FROM SC WHERE Grade 60 AND Sno IN (SELECT Sno FROM Student WHERE Sdept = 计算机系)用多表连接实现 DELETE FROM SC FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Sdept = 计算机系 AND

35、Grade 6057小结 SQL中的数据查询功能。在查询语句部分我们介绍了单表查询和多表连接查询，包括无条件查询、有条件查询、分组查询以及排序等功能。多表连接查询介绍了内连接、自连接、左外连接和右外连接。对条件查询介绍了多种实现方法，包括用子查询实现和用连接查询实现等。数据的更改操作，包括数据的插入、修改和删除。对删除和更新操作，介绍了无条件的操作和有条件的操作，对有条件的删除和更新操作我们又介绍了用多表连接实现和用子查询实现两种方法。58嵌入式SQL语言SQL语言使用方式：直接使用方式，即用户在系统终端设备上键入SQL语句，以直接交互的方式使用数据库系统。嵌入式使用方式，即允许用户在程序设计语言中潜入SQL语句，调用DBMS的功能。第二中方式使用的SQL语言称为嵌入式SQL语言，相应的允许潜入SQL语句的程序设计语言被称为宿主语言。下面以C语言为宿主语言，介绍使用嵌入式SQL语言。591、嵌入SQL语句的宿主语言源程序的编译执行 宿主语言编译器不能识别和接受SQL语句，为了在处理宿主