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,结果为 ：,4,2、查询全部列 例查询全体学生的记录 SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student 等
2、价于：SELECT * FROM Student 结果为 ：,5,3、查询经过计算的列SELECT子句中的可以是表 中存在的属性列，也可以是表达式、常量或者 函数。 例查询全体学生的姓名及其出生年份SELECT Sname,2002 - Sage FROM Student 结果为:,6,改变列标题的语法格式为：列名 | 表达式 AS 列标题或：列标题 列名 | 表达式 例如，对于上例可写成：SELECT Sname 姓名,2002-Sage 年份 FROM Student 结果为：,7,选择表中的若干元组 1、消除取值相同的行例在选课表(SC)中查询有哪些学生修了课 程，要求列出学生的学号。
3、SELECT Sno FROM SC 在这个结果中有许多重复的行。SQL中的DISTINCT关键字可以去掉结果表中的 重复行。SELECT DISTICT Sno FROM SC 则执行结果为 ：,8,2、查询满足条件的元组查询满足条件的元组是通过WHERE子句实现 的。WHERE子句常用的查询条件如表3-13所示。,9,（1）比较大小例查询计算机系全体学生的姓名。SELECT Sname FROM Student WHERE Sdept = 计算机系结果为 ：,10,注意：取反操作的执行效率比较低,例查询所有年龄在20岁以下的学生的姓名及年龄SELECT Sname,Sage FROM St
4、udent WHERE Sage = 20 结果为：,11,（2）确定范围BETWEENAND和NOT BETWEENAND是一个 逻辑运算符，可以用来查找属性值在或不在指 定范围内的元组。BETWEENAND的格式为： 列名 | 表达式 NOT BETWEEN 下限值 AND 上限值BETWEENAND一般用于对数值型数据进行比 较。列名或表达式的类型要与下限值或上限值的 类型相同。,12,例查询年龄在2023岁之间的学生的姓名、 所在系和年龄SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23此句等价于：SE
5、LECT Sname, Sdept, Sage FROM Student WHERE Sage =20 AND Sage=23结果为：,13,例查询年龄不在2023之间的学生姓名、所在系和年龄。 SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23 此句等价于： SELECT Sname, Sdept, Sage FROM Student WHERE Sage 23 结果为：,14,（3）确定集合IN是一个逻辑运算符，可以用来查找属性值属于指定集合的元组。使用IN的格式为：列名 NOT IN （常量1,
6、常量2, 常量n）IN的含义为：当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录；NOT IN的含义正好相反：当列中的值与某个常量值相同时，则结果为False，表明此记录为不符合查询条件的记录；,15,例查询信息系、数学系和计算机系学生的 姓名和性别。SELECT Sname, Ssex FROM Student WHERE Sdept IN (信息系,数学系,计算机系) 此句等价于：SELECT Sname, Ssex FROM Student WHERE Sdept = 信息系 OR Sdept = 数学系 OR Sdept = 计算机系,16,（4）
7、字符匹配LIKE用于查找指定列名与匹配串常量匹配的 元组。通配符用于表示任意的字符或字符串。在 LIKE运算符前边也可以使用NOT运算符，表示对 结果取反。LIKE运算符的一般形式为：列名 NOT LIKE 匹配串中可包含如下四种通配符：_：匹配任意一个字符；%：匹配0个或多个字符； ：匹配 中的任意一个字符； ：不匹配 中的任意一个字符。,17,例查询姓张的学生的详细信息。SELECT * FROM Student WHERE Sname LIKE 张% 结果为：,18,例查询学生表中姓张、姓李和姓刘的学生的情况。SELECT * FROM Student WHERE Sname LIKE
8、张李刘% 结果为：,19,例查询名字中第2个字为小或大 字的学生的姓名和学号。SELECT Sname, Sno FROM Student WHERE Sname LIKE _小大%结果为：,20,（5）涉及空值的查询空值(NULL)在数据库中有特殊的含义，它 表示不确定的值。判断某个值是否为NULL值，不 能使用普通的比较运算符（=、!=等），而只能使 用专门的判断NULL值的子句来完成。判断取值为空的语句格式为：列名IS NULL判断取值不为空的语句格式为：列名 IS NOT NULL,例查询无考试成绩的学生的学号和相应的课程号SELECT Sno, Cno FROM SC WHERE G
9、rade IS NULL,21,（6）多重条件查询在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询。用AND连接的条件表示必须全部满足所有的条件的结果才为True，用OR连接的条件表示只要满足其中一个条件结果即为True。,例查询计算机系年龄在20岁以下的学生姓名。SELECT Sname FROM Student WHERE Sdept=CS AND Sage20,22,对查询结果进行排序 排序子句的格式为：ORDER BY ASC | DESC ， n 其中为排序的依据列，可以是列名或列的别名。当指定多个排序依据列时，首先按排在最前面的列进行排序，如果排序后存在两个或两个以
10、上列值相同的记录，则对这些值相同的记录再依据排在第二位的列进行排序，依此类推。,23,例将学生按年龄的升序排序。 SELECT * FROM Student ORDER BY Sage,例查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。SELECT * FROM StudentORDER BY Sdept, Sage DESC,24,使用聚合函数汇总数据 计算函数也称为集合函数或聚合函数、聚集函 数，其作用是对一组值进行计算并返回一个单值。 SQL提供的计算函数有： COUNT（ * ）：统计表中元组个数； COUNT（）：统计本列列值个数； SUM（）： AV
11、G（）： 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子句中。 例：查询年龄最大的学生的姓名，如下写法是 错误的：SELECT Sname FROM Student WH
12、ERE Sage = MAX(Sage),26,对查询结果进行分组计算 GROUP BY分组的目的是细化计算函数的作用对 象。在一个查询语句中，可以使用任意多个列进行 分组。需要注意的是：如果使用了分组子句，则查 询列表中的每个列必须要么是分组依据列（group by 后边的列），要么是计算函数。使用GROUP BY时，如果在SELECT的查询列表中 包含计算函数，则是针对每个组计算出一个汇总 值，从而实现对查询结果的分组统计。分组语句的一般形式为：GROUP BY ， n HAVING ,27,使用GROUP BY 例统计每门课程的选课人数，列出课程号 和人数。 SELECT Cno as
13、课程号,COUNT(Sno) as 选课人数 FROM SC GROUP BY Cno查询结果为 ：,28,使用HAVINGHAVING子句用于对分组后的结果再进行过滤， 它的功能有点像WHERE子句，但它用于组而不是对 单个记录。在HAVING子句中可以使用计算函数，但 在WHERE子句中则不能。HAVING通常与GROUP BY子 句一起使用。 例查询修了3门以上课程的学生的学号。SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3 结果为：,29,三、多表连接查询 内连接只有满足连接条件的元组才能作为结果输出内连接的格式为：FROM 表1 I
14、NNER JOIN 表2 ON 连接条件的一般格式为： 注意：两个表的连接列必须是可比较的，即必须是语义相同的列，否则比较将是无意义的。当比较运算符为等号（）时，称为等值连接，使用其他运算符的连接称为非等值连接。,30,例查询每个学生及其修课的情况。SELECT * FROM Student INNER JOIN SCON Student.Sno = SC.Sno,31,例去掉上例中的重复列。SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade, XKLB FROM Student JOIN SC ON Student.Sno = S
15、C.Sno,根据要查询的列数据以及数据的选 择条件所涉及的列，可以决定要对 哪些表进行连接操作。,例查询计算机系学生的修课情况，要求列出 学生的名字、所修课的课程号和成绩。SELECT Sname, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.SnoWHERE Sdept = 计算机系,32,可以为表提供别名，其格式为： AS 例如：使用别名时上例可写为：SELECT Sname, Cno, Grade FROM Student S JOIN SC ON S.Sno = SC.SnoWHERE Sdept = 计算机系注意：当为表指定
16、了别名时，在查询语句中的其他地方，所有用到表名的地方都要使用别名，而不能再使用原表名。,33,自连接是一种特殊的内连接，它是指相互连接的表在 物理上为同一张表，但可以在逻辑上分为两张表。 使用自连接时必须为两个表取别名。例查询与刘晨在同一个系学习的学生的姓 名和所在的系。SELECT S2.Sname, S2.SdeptFROM Student S1 JOIN Student S2 ON S1.Sdept = S2.SdeptWHERE S1.Sname = 刘晨AND S2.Sname != 刘晨结果为：,34,外连接外连接是只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连
17、接条件。 ANSI方式的外连接的语法格式为：FROM 表1 LEFT|RIGHT OUTER JOIN 表2 ON ,35,例查询学生的修课情况，包括选修了课程的学生和没有修课的学生。 SELECT Student.Sno, Sname, Cno, Grade FROM Student LEFT OUTER JOIN SC ON Student.Sno = SC.Sno,36,四、子查询 在SQL语言中，一个SELECTFROMWHERE语 句称为一个查询块。如果一个SELECT语句是嵌套在一个SELECT、 INSERT、UPDATE或DELETE语句中，则称之为子查 询或内层查询；而包含子
18、查询的语句则称为主查 询或外层查询。子查询要写在圆括号中。子查询语句通常情况下一般是用在外层查询 的WHERE子句或HAVING子句中，与比较运算符或 逻辑运算符一起构成查询条件。,37,使用子查询进行基于集合的测试 使用子查询进行基于集合的测试时，通 过运算符IN或NOT IN，将一个表达式的值与 子查询返回的结果集进行比较。 形式为：WHERE 表达式 NOT IN (子查询) 实现步骤： 先执行子查询 在子查询的结果基础上再执行外层查询,38,SELECT Sno, Sname, Sdept FROM StudentWHERE Sdept IN( SELECT Sdept FROM St
19、udent WHERE Sname = 刘晨 ),例查询与“刘晨”在同一个系学习的学生。 分析： 确定“刘晨”所在系 在子查询结果上查找所有在此系学习的学生,39,例查询选修了“数据库基础”课程的学生 的学号、姓名。SELECT Sno, Sname FROM StudentWHERE Sno IN ( SELECT Sno FROM SCWHERE Cno IN( SELECT Cno FROM CourseWHERE Cname = 数据库基础 ) ),用多表连接实现：SELECT Student.Sno, Sname FROM Student JOIN SC ON Student.Sno
20、 = SC.SnoJOIN Course ON Course.Cno = SC.CnoWHERE Cname = 数据库基础,40,使用子查询进行比较测试 形式为：WHERE 表达式 比较运算符 (子查询)注意：使用子查询进行比较测试时，要求 子查询语句必须是返回单值的查询语句。,41,例查询修了c02课程且成绩高于此课程的平均成绩的学生的学号和成绩。 分析： 计算c02的平均成绩 在子查询结果上查找成绩大于此平均成绩且选修c02课程的学生学号和成绩,SELECT Sno , Grade FROM SCWHERE Cno = c02 and Grade ( SELECT AVG(Grade)
21、from SC WHERE Cno = c02 ),42,使用子查询进行存在性测试使用子查询进行存在性测试时，一般使用 EXISTS谓词。 形式为：WHERE NOT EXISTS (子查询)带EXISTS谓词的子查询不返回查询的数据， 只产生逻辑真值和逻辑假值。,注意：带EXISTS谓词的查询是先执行外层查 询，然后再执行内层查询。由外层查询的值决 定内层查询的结果；内层查询的执行次数由外 层查询的结果数决定。,43,例查询选修了c01号课程的学生姓名。 SELECT Sname FROM Student WHERE EXISTS( SELECT * FROM SCWHERE Sno = S
22、tudent.Sno AND Cno = c01 ),44,连接查询：SELECT Sname FROM Student JOIN SCON SC.Sno=Student.Sno where Cno=c01嵌套子查询：SELECT Sname FROM StudentWhere Sno In( SELECT Sno FROM SC WHERE Cno=c01 ),一种查询可用不同方法实现，但多表连接查询效率 嵌套子查询效率 相关子查询效率,45,例查询没有选修c01课程的学生的姓名和所在系 1)多表连接实现SELECT DISTINCT Sname,Sdept FROM Student SJO
23、IN SC ON S.Sno=SC.SnoWHERE Cno !=c01,46,2)嵌套子查询实现在子查询中否定SELECT Sname,Sdept FROM Student WHERE Sno IN（ SELECT Sno FROM SCWHERE Cno !=c01 ）在外层查询中否定SELECT Sname,Sdept FROM Student WHERE Sno NOT IN（ SELECT Sno FROM SCWHERE Cno =c01 ）,47,3)相关子查询实现在子查询中否定SELECT Sname,Sdept FROM Student WHERE EXISTS（ SELEC
24、T * FROM SCWHERE Sno=Student.Sno AND Cno !=c01）在外层查询中否定SELECT Sname,Sdept FROM Student WHERE NOT EXISTS（ SELECT * FROM SCWHERE Sno=Student.Sno AND Cno !=c01）,48,将否定放置在外层查询中时结果正确，原因 在于不同的查询执行的机制不同。 对多表连接查询，所有的条件是在连接之后的结果表上进行，且是逐行判断，一旦有满足要求的数据，则此行作为结果输出。 对含有嵌套子查询，首先执行子查询，再在子查询结果基础上执行外层查询，而在子查询中也是逐行判断，
25、当有满足条件的数据时，即将此行数据作为外层查询的一个比较条件。通常情况，对于否定条件的查询都应使用子 查询实现，且将否定放在外层。,49,4.2 数据更改功能,一、插入数据 插入数据的INSERT语句的格式为：INSERT INTO () VALUES （值列表）使用插入语句时应注意： 值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致。 如果后边没有指明列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且每一个列均有值（可以为空）。,50,例将新生记录（95020，陈冬，男，信息 系，18岁）插入到Student表中。INSERT INTO Student VALUES
26、(9521105,陈冬,男,18,信息系),例在SC表中插入一新记录，成绩暂缺。INSERT INTO SC(Sno, Cno) VALUES (9521105, c01),51,二、更新数据 UPDATE语句的语法格式为：UPDATE SET , n WHERE ,1、无条件更新例将所有学生的年龄加1。UPDATE Student SET Sage = Sage + 1,52,2、有条件更新一种是基于本表条件的更新一种是基于其他表条件的更新,(1)基于本表条件的更新。 例将9512101学生的年龄改为21岁。 UPDATE Student SET Sage = 21 WHERE Sno =
27、9512101,53,(2)基于其他表条件的更新 例将计算机系全体学生的成绩加5分。 用子查询实现UPDATE SC SET Grade = Grade + 5WHERE Sno IN(SELECT Sno FROM StudentWHERE Sdept = 计算机系) 用多表连接实现UPDATE SC SET Grade = Grade + 5FROM SC JOIN Student ON SC.Sno = Student.SnoWHERE Sdept= 计算机系,54,三、删除数据 DELETE语句的语法格式为： DELETE FROM WHERE ,无条件删除无条件删除是删除表中全部数据
28、，但保留表的结构。 例1删除所有学生的选课记录。DELETE FROM SC - SC成空表,55,有条件删除 分为两种情况： 一种是基于本表条件的删除， 另一种是基于其他表条件的删除。 (1)基于本表条件的删除。 例2删除所有不及格学生的修课记录。DELETE FROM SC WHERE Grade 60,56,(2)基于其他表条件的删除 例3删除计算机系不及格学生的修课记录 用子查询实现DELETE FROM SCWHERE Grade 60 AND Sno IN (SELECT Sno FROM Student WHERE Sdept = 计算机系) 用多表连接实现DELETE FROM
29、 SC FROM SC JOIN Student ON SC.Sno = Student.SnoWHERE Sdept = 计算机系AND Grade 60,57,小结,SQL中的数据查询功能。在查询语句部分我们介绍了单表查询和多表连接查询，包括无条件查询、有条件查询、分组查询以及排序等功能。多表连接查询介绍了内连接、自连接、左外连接和右外连接。对条件查询介绍了多种实现方法，包括用子查询实现和用连接查询实现等。 数据的更改操作，包括数据的插入、修改和删除。对删除和更新操作，介绍了无条件的操作和有条件的操作，对有条件的删除和更新操作我们又介绍了用多表连接实现和用子查询实现两种方法。,58,嵌入式
30、SQL语言,SQL语言使用方式： 直接使用方式，即用户在系统终端设备上键入SQL语句，以直接交互的方式使用数据库系统。 嵌入式使用方式，即允许用户在程序设计语言中潜入SQL语句，调用DBMS的功能。 第二中方式使用的SQL语言称为嵌入式SQL语言，相应的允许潜入SQL语句的程序设计语言被称为宿主语言。 下面以C语言为宿主语言，介绍使用嵌入式SQL语言。,59,1、嵌入SQL语句的宿主语言源程序的编译执行宿主语言编译器不能识别和接受SQL语句， 为了在处理宿主语言的过程中能区别C语言和 SQL语句，在宿主语言源程序中，每个SQL语句 需在其句首加EXEC SQL，在其句尾加“；”。编译执行过程如
31、下如所示。,60,预编译,编译,连接装配,执行,包含SQL语句的宿主语言源程序,包含SQL函数调用,目标程序,可执行程序,SQL 函数库,61,2、宿主语言与SQL语言之间的数据传输数据传输通过宿主变量实现。宿主变量是 嵌入式SQL语言中可以引用的C语言变量。在SQL 语句中引用宿主变量时，其前须加特殊符号， 如“:”。所有SQL语句中用到的宿主变量，都 必须在宿主语言源程序前面加以说明。嵌入式SQL语言的说明部分为：EXEC SQL BEGIN DECLARE SECTION;EXEC SQL END DECLARE SECTION;,62,3、嵌入式SQL语句嵌入式SQL语句除了前面加EX
32、EC SQL外，与 标准的SQL语言完全相同。例：向关系Course（Cno，Cname， Ccredit，Semester）中插入一条记录。EXEC SQL INSERT INTO Course(Cno,Cnamw,Ccredit,Semester)VALUES(:CNO,:CNAME,:CREDIT,:S);,63,4、集合查询结果的传递宿主变量仅能保存一个数据，如果要 把查询结果赋予宿主变量，需使用一个特 殊的存储结构-游标(Cursor)，用它来 存储查询结果集合。每个查询语句可以对 应一个游标。,64,游标使用过程为： (1) 定义游标EXEC SQL DECLARE CURSOR
33、FOR ; (2) 打开游标EXEC SQL OPEN ; (3) 从游标读取查询结果EXEC SQL FETCH INTO ; (4) 关闭游标EXEC SQL CLOSE ;,65,例 使用Student关系，打印计算机系所 有学生的姓名。EXEC SQL BEGIN DECLARE SECTION;Char name10;Char dept20;EXEC SQL END DECLARE SECTION;dept=“计算机系”;EXEC SQL CONNECT;EXEC SQL DECLARE ST CURSOR FOR SELECT Sname from Studentwhere Sdept=:dept;EXEC SQL OPEN ST;,66,While (true) EXEC SQL FETCH ST INTO :name;IF (SQLCA.SQLCODE=100)BREAK;PRINTF(“%SN”,ST) EXEC SQL CLOSE ST;,67,作业(教材p-61 )习题4、6、9、13、14（3）、15、16、18 补充作业：使用C语言和嵌入式SQL语言编写一 段程序，打印Course数据库中每门课程 的课程名、课程号。,
