关系数据库标准语言SQL（二）

1、第四章 关系数据库标准语言SQL（二）SQL的数据操纵语言DML(Data Manipulation Language) 插入/修改/删除记录DML：Insert： 插入记录Delete：删除记录Update: 修改记录Select：查询记录4.4 SQL的数据查询DQL (Data Query Language) SELECT查询结构SELECT基本查询联接查询嵌套查询查询结果的连接：并、交、差Select查询结构Select 指定希望查看的列From 指定要查询的表Where 指定查询条件Group By 指定要分组的列Having 指定分组的条件Order By 指定如何排序4.4.1

2、单表查询1 查询特定的列：查询所有学生的学号和姓名Select sno, sname From Student2 查询全部记录：查询全部的学生信息Select * From Student* 表示所有列 等同于Select sno, sname, age, sex From Student二、Select基本查询3 使用别名：查询所有学生的学号和姓名Select sno AS 学号, sname AS 姓名 From Student结果为: 学号 姓名 - 95001 李勇 95002 刘晨 95003 张立华如果别名包含空格，须使用双引号Select sno AS “Student Numb

3、er” From Student二、Select基本查询4 使用表达式：查询所有学生的学号、姓名和出生年份Select sno , sname AS 学生，2005age AS 出生年份 From Student表达式可以是算术表达式、字符型常量、函数等。结果为: Sno 学生 出生年份 - 95001 李勇 1976 95002 刘晨 1977 95003 张立华 1978Select sno, to_char(birth, mm-dd-yyyy) AS birthday From StudentSelect Count(sno) As 学生人数 From Student二、Select基本

4、查询5 消除取值重复的行：用Distinct 例：查询选修了课程的学生的学号Select Sno from SC;Select Distinct Sno from SC;结果为: Sno Sno - - 95001 95001 95001 95002 95001 95002 95002 查询学生的姓名:Select Distinct sname From Student ( Distinct只对记录有效，不针对某个特定列)Select Distinct sname, age From Student二、Select基本查询5 查询满足条件的元组：查询20岁以上的学生的学号和姓名Select s

5、no AS 学号, sname AS 姓名 From Student Where age 20无Where子句时返回全部的记录结果为: 学号 姓名 - 95001 李勇 95002 刘晨 WHERE子句中的关系运算符算术比较符：, =, =, =, 确定范围：BETWEEN AND, NOT BETWEEN AND 确定集合：IN , Not IN空值：IS NULL 和 IS NOT NULL字符匹配：LIKE, NOT LIKE多重条件：AND, OR存在谓词：EXISTS二、Select基本查询二、Select基本查询（1）比较大小：比较符：, =, =, =, ，可和NOT连用。例：查

6、询计算机系全体学生的名单。SELECT Sname FROM Student WHERE Sdept = CS ;二、Select基本查询例：查询所有年龄在20岁以下的学生姓名及年龄。SELECT Sname, Sage FROM Student WHERE Sage = 20) ;例：查询学生成绩有不及格的学生的学号。SELECT DISTINCT Sno FROM SC WHERE Grade 60 ;二、Select基本查询（2）确定范围： BETWEEN AND, NOT BETWEEN AND例：查询年龄在2023岁（包括20岁和23岁）之间的学生姓名，系别和年龄。SELECT Sn

7、ame，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23 ;二、Select基本查询例：查询年龄不在2023岁(包括20岁和23岁)之间的学生姓名，系别和年龄。SELECT Sname，Sdept，Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23 ;二、Select基本查询（3）确定集合谓词IN可以用来查找属性值属于指定集合的元组。例：查询s001,s003,s006和s008四学生的信息Select * From StudentWhere sno IN (s001,s003,s006,

8、s008)二、Select基本查询查询信息系、数学系、计算机系学生的姓名和性别。 Select Sname, Ssex From Student Where Sdept IN (IS，MA，CS)查询既不是信息系、数学系，也不是计算机系学生的姓名和性别。 Select Sname, Ssex From Student Where Sdept NOT IN (IS，MA，CS)二、Select基本查询（4）字符匹配：谓词 LIKE 可以用来进行字符串的匹配，其一般格式为： NOT LIKE ESCAPE 其含义是查找指定的属性列值与相匹配的元组。其中： %（百分号）：代表任意长度（可以为0）的字

9、符串； _（下划线）：代表任意单个字符。 例：查询姓名的第一个字母为R的学生Select * From Student Where Sname LIKE R% ;例：查询姓名的第一个字母为R并且倒数第二个字母为S的学生Select * From Student Where Sname LIKE R%S_ ;例：查询学号为“95001”的学生Select * From Student Where Sno LIKE 95001(或 WHERE Sno=95001)；二、Select基本查询 例: 查姓“欧阳”且全名为3个汉字的学生的姓名。 SELECT Sname, Sno FROM Studen

10、t WHERE Sname LIKE 欧阳_ _;二、Select基本查询二、Select基本查询例: 查所有姓刘的学生的姓名、学号和性别。 SELECT Sname, Sdept, Ssex FROM Student WHERE Sname LIKE 刘%;例: 查名字中第二字为“阳”字的学生的姓名和学号。 SELECT Sname, Sno FROM Student WHERE Sname LIKE _ _阳%;例: 查所有不姓刘的学生姓名。 SELECT Sname FROM Student WHERE Sname NOT LIKE 刘%; 二、Select基本查询例: 查DB_Desi

11、gn课程的课程号和学分。 SELECT Cno，Ccredit FROM course WHERE Cname LIKE DB_Design ESCAPE ;例: 查以“DB_”开头，且倒数第3个字符为i的课程的详细情况。 WHERE Cname LIKE DB_%i_ _ ESCAPE ; 二、Select基本查询二、Select基本查询（5）涉及空值的查询：谓词IS NOT NULL可用来查询空值和非空值： (这里的IS不能用等号)查询缺少年龄数据的学生Select * From Student Where age IS NULL ;查询所有有成绩记录的学生学号和课程号。Select Sn

12、o，Cno From SC Where Grade IS NOT NULL; （6）多重条件查询： 可用 NOT、AND 和 OR 连接例：查年龄为空且姓名第一个字母为R的学生 Select * From Student Where age IS NULL and sname LIKE R%二、Select基本查询二、Select基本查询查CS系年龄在20岁以下的学生姓名 Select Sname From Student Where Sdept=CS and Sage 5 使用聚集函数 WHERE子句与HAVING短语的根本区别在于作用对象不同。 WHERE子句作用于基本表或视图，从中选择满

13、足条件的元组。HAVING短语作用于组，从中选择满足条件的组。使用聚集函数Having子句中必须是聚集函数的比较式，而且聚集函数的比较式也只能通过Having子句给出Having中的聚集函数可与Select中的不同例:查询人数在60以上的各个班级的学生平均年龄Select class, AVG(age) From StudentGroup By classHaving COUNT(*) 60 (12)4.4.2 连接查询一个查询同时涉及两个以上的表，称为连接查询包括等值连接、自然连接、非等值连接查询、自身连接查询、外连接查询和符合条件连接查询。查询结果返回两个表中与联接条件相互匹配的记录，不返

14、回不相匹配的记录。连接查询示例中用到的表SnoSnameAge01Sa2002Sb2103sc21SnoCnoScore01C18001C28502C189Student表SC表(sno是外键, cno是外键)cnoCnamecreditC1Ca3C2Cb4C3Cc3.5Course表一、等值与非等值连接查询连接条件（连接谓词）一般格式为： . 其中主要有：=、=、=、!=连接谓词还可以使用下面形式： . BETWEEN . AND .说明：当连接运算符为=时为等值连接，其他为非等值连接；连接谓词中的列名为连接字段，其类型必须是可比的，但不必是相同的。（1）联接查询例子查询每个学生的学号，姓名

15、和所选课程号Select student.sno，student.sname，oFrom student，scWhere student.sno = sc.sno 联接条件若存在相同的列名，须用表名做前缀 查询学生的学号，姓名，所选课程号和课程名Select student.sno，student.sname， o，ameFrom student，sc，courseWhere student.sno = sc.sno and o = o 联接条件（2）使用表别名查询姓名为sa的学生所选的课程号和课程名Select o, ameFrom student a, sc b, course cWher

16、e a.sno=b.sno and o=o and a.sname=sa表别名可以在查询中代替原来的表名使用（2）使用表别名联接查询与基本查询结合：查询男学生的学号，姓名和所选的课程数，结果按学号升序排列Select a.sno, a.sname, count(o) as c_countFrom student a, sc bWhere a.sno = b.sno and a.sex=MGroup By a.sno, a.snameOrder By student.snoGroup By子句将SELECT中除聚集函数外的属性a.sno, a.sname必须列在其中二、自身连接 连接操作不仅可以

17、在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接。 例 查询每一门课的间接先修课(即先修课的先修课)。 SELECT FIRST.Cno，SECOND.CpnoFROM Course FIRST，Course SECONDWHERE FIRST.Cpno=SECOND.Cno; 、外连接 在通常的连接操作中，有满足连接条件的元组才能作为结果输出。在特殊的连接操作中，即使没有满足连接条件的元组也需要作为结果输出，就需要使用外连接(Outer Join)。外连接的运算符通常为*。有的关系数据库中也用十。 例33 查询每个学生及其选修课程的情况 SELECT Student

18、.Sno，Sname， FROM Student，SC WHERE Student.Sno=SC.Sno(*); 外连接符若出现在连接运算符的右边，称其为左外连接。相应地，如果外连接符出现在连接运算符的左边，则称为右外连接。四.复合条件连接 WHERE子句中有多个条件的连接操作，称为复合条件连接。例 查询选修2号课程且成绩在90分以上的所有学生。 SELECT Student.Sno，Sname FROM Student，SC WHERE Student.Sno=SC.Sno AND SC.Cno=2 AND SC.Grade 90; 4.复合条件连接 例 查询每个学生选修的课程名及其成绩。

19、SELECT Student.Sno，Sname， Course.Cname，SC.Grade FROM Student，SC，Course WHERE Student.Sno=SC.Sno and SC.Cno=Course.Cno; 4.4.3 嵌套查询在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。在一个查询语句中嵌套了另一个查询语句（即：将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询） *子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY子句永远只能对最终查询结果排序。嵌套查询的求解方法

20、是由里向外处理。 无关子查询相关子查询联机视图一.带有IN谓词的子查询 带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中。 例 查询与“刘晨”在同一个系学习的学生。 分步来完成此查询 : 确定“刘晨”所在系名 查找所有在IS系学习的学生。 子查询 ：SELECT Sno，Sname，SdeptFROM StudentWHERE Sdept IN (SELECT Sdept FROM Student Where Sname=刘晨); 自身连接查询：SELECT Sl.Sno，Sl.Sname，Sl.SdeptFROM Student Sl，Stude

21、nt S2WHERE Sl.Sdept=S2.Sdept AND s2.sname=刘晨； 二者查询效率不同 例查询选修了课程名为信息系统的学生学号和姓名。 SELECT Sno，SnameFROM StudentWHERE Sno IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course Where cname=信息系统)；本查询同样可以用连接查询实现 SELECT Student.Sno，Sname FROM Student，SC，Course WHERE Student.Sno=SC.Sno AND SC，Cno=COUrSe

22、.Cno AND Course.Cname=信息系统; 二.带有比较运算符的子查询 当用户能确切知道内层查询返回的是单值时，可以用,=,=,!=或等比较运算符。 例37 查询与“刘晨”在同一个系学习的学生。 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept= (SELECT Sdept FROM Student Where Sname=刘晨); 例 查询选修了课程名为信息系统的学生学号和姓名。用=运算符和IN谓词共同完成 SELECT Sno，Sna FROM Student WHERE Sno IN (SELECT Sno FROM SC WHE

23、RE Cno= (SELECT Cno FROM Course Where cname=信息系统)；三.带有ANY或ALL谓词的子查询 ANY 大于子查询结果中的某个值=ANY大于等于子查询结果中的某个值=ANY小于等于子查询结果中的某个值=ANY等于子查询结果中的某个值!=ANY或ANY 不等于子查询结果中的某个值ALL大于子查询结果中的所有值=ALL大于等于子查询结果中的所有值=ALL小于等于子查询结果中的所有值=ALL 等于子查询结果中的所有值(通常没有实际意义，!=ALL或ALL不等于子查询结果中的任何一个值 例 查询其他系中比IS系任一学生年龄小的学生名单。SELECT Sname，

24、SageFROM StudentWHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept=IS) AND Sdept ISORDER BY Sage DESC; 本查询实际上也可以用集函数实现 SELECTSname，Sage FROMStudent WHERESage (SELECTMAX(Sage) FROMStudent WHERESdept=IS) ANDSdeptIS ORDER BY Sage DESC; 例 查询其他系中比IS系所有学生年龄都小的学生名单。 SELECT Sname，Sage FROM Student WHERE S

25、age ALL (SELECT Sage FROM Student WHERE Sdept=IS) AND SdeptIS ORDER BY Sage DESC; 用集函数实现 SELECT Sname，Sage FROM Student Where sage (SELECT MIN(Sage) FROM Student WHERE Sdept=IS) AND SdeptIS ORDER BY Sage DESC; 表4-6 ANY,ALL谓词与集函数及lN谓词的等价转换关系 四. 带EXISTS谓词的子查询 EXISTS代表存在量词。带有EXISTS谓词的子查询不返回任何实际数据，它只产生逻

26、辑真值“true”。或逻辑假值“false “。例41 查询所有选修了1号课程的学生姓名。 SELECTSname FROMStudent WHEREEXISTS (SELECT * FROMSC WHERESno=Student.SnoAND Cno=1)； 无关子查询举例父查询与子查询相互独立，子查询语句不依赖父查询中返回的任何记录，可以独立执行。查询没有选修课程的所有学生的学号和姓名Select sno，snameFrom studentWhere sno NOT IN ( select distinct sno From sc)；子查询返回选修了课程的学生学号集合，它与外层的查询无依赖

27、关系，可以单独执行无关子查询一般与IN一起使用，用于返回一个值列表相关子查询举例相关子查询的结果依赖于父查询的返回值查询选修了课程的学生学号和姓名Select sno, snameFrom studentWhere EXISTS (Select * From sc Where sc.sno = student.sno)相关子查询的查询条件依赖于外层父查询的某个属性值。处理过程 ：首先取外层查询中Student表的第一个元组，根据它与内层查询相关的属性值(即Sno值)处理内层查询，若WHERE子句返回值为真(即内层查询结果非空)，则取此元组放入结果表;然后再检查Student表的下一个元组;重复

28、这一过程，直至Student表全部检查完毕为止。 相关子查询不可单独执行，依赖于外层查询EXISTS（子查询）：当子查询返回结果非空时为真，否则为假执行分析：对于student的每一行，根据该行的sno去sc中查找有无匹配记录例 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)；此例用连接运算难于实现 不同形式的查询间的替换 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词

29、、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。 例：查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换： SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = 刘晨 ；用EXISTS/NOT EXISTS实现全称量词(难点)SQL语言中没有全称量词 （For all）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词： (x)P ( x( P) 带有EX

30、ISTS谓词的子查询(续）例 查询选修了全部课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno）；带有EXISTS谓词的子查询(续） 用EXISTS/NOT EXISTS实现逻辑蕴函(难点)SQL语言中没有蕴函(Implication)逻辑运算可以利用谓词演算将逻辑蕴函谓词等价转换为： p q pq 带有EXISTS谓词的子查询(续） 例44 查

31、询至少选修了学生95002选修的全部课程的学生号码。解题思路：用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要95002学生选修了课程y，则x也选修了y。形式化表示：用P表示谓词 “学生95002选修了课程y”用q表示谓词 “学生x选修了课程y”则上述查询为: (y) p q 带有EXISTS谓词的子查询(续）等价变换： (y)p q (y (p q ) (y ( p q) y(pq)变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。带有EXISTS谓词的子查询(续）用NOT EXISTS谓词表示： SELECT DISTINCT Sno FROM SC SCX

32、WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 95002 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)；4.4.4 集合查询 集合操作主要包括：并操作：UNION、UNION ALL交操作：INTERSECT差操作：EXCEPT （集合查询是对查询结果的连接）（1）Union和Union All例44求计算机科学系的所有学生及年龄不大于19岁的学生SELECT*FROMStudentWHERESdept=CSUNION

33、SELECT*FROMStudentWHERESage=19; （1）Union和Union All查询课程平均成绩在90分以上或者年龄小于20的学生学号（Select sno From student where age90) ）UNION操作自动去除重复记录UNION All操作不去除重复记录（2）Except操作：差查询未选修课程的学生学号（Select sno From Student） Except （Select distinct sno From SC）(3)Intersect操作返回两个查询结果的交集查询课程平均成绩在90分以上并且年龄小于20的学生学号（Select sno

34、From student where age90) ）例45查询选修了课程1或者选修了课程 的学生。SELECTSno FROMSC WHERECno=1 UNION SELECTSno FROMSC Wherecno=；例49 查询选修课程1的学生集合与选修课程2的学生集合的差集。本例实际上是查询选修了课程1但没有选修课程2的学生。 SELECT Sno FROM SC WHERE Cno= AND Sno NOT IN ( SELECT Sno FROM SC Where cno=2 )；（3）联接视图子查询出现在From子句中作为表使用查询只选修了1门或2门课程的学生学号和课程数Sele

35、ct sno，count_cnoFrom (Select s.sno as sno, count(sc.sno) as count_cno From student s, sc Where s.sno=sc.sno Group by s.sno) SC2, studentWhere sc2.sno = student.sno and (count_cno=1 OR count_cno=2)联机视图可以和其它表一样使用4.4.5 SELECT语句的一般格式SELECT ALL|DISTINCT 别名 ， 别名 FROM 别名 ， 别名 WHERE GROUP BY ， .HAVING ORDER

36、 BY ASC|DESC ， ASC|DESC ；目标列表达式目标列表达式格式(1) . *(2) .，. ：由属性列、作用于属性列的集函数和常量的任意算术运算（+，-，*，/）组成的运算公式。集函数格式 COUNT SUM AVG (DISTINCT|ALL ) MAX MIN COUNT (DISTINCT|ALL *)条件表达式格式（1） ANY|ALL (SELECT语句)条件表达式格式 （2） NOT BETWEEN AND (SELECT (SELECT 语句) 语句)条件表达式格式 （3） (， ) NOT IN (SELECT语句)条件表达式格式 (4) NOT LIKE (5

37、) IS NOT NULL (6) NOT EXISTS (SELECT语句)条件表达式格式 （7） AND AND OR OR4.5 数 据 更 新数据更新操作有3个：向表中添加若干行数据修改表中的数据删除表中的若干行数据4.5.1 插入数据1、插入元组到基本表中 格式： Insert Into (列名1，列名2，列名n) Values（值1，值2，值n）例1：Insert Into Student（Sno, Sname, Age, Sex）Values（s001，John，21，M）Create Table Student( Sno Varchar2(10) Constraint PK P

38、rimary Key, Sname Varchar2(20), Age Number(3), Sex Char(1) DEFAULT F) INTO子句指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致指定部分属性列：插入的元组在其余属性列上取空值 VALUES子句 提供的值必须与INTO子句匹配值的个数值的类型（1）Insert其它例子例2：Insert Into StudentValues（s002，Mike，21，M）如果插入的值与表的列名精确匹配（顺序，类型），则可以省略列名表例3：Insert

39、 Into Student（sno, sname）Values（s003，Mary ）如果列名没有出现在列表中，则插入记录时该列自动以默认值填充，若没有默认值则设为空SnoSnameAgeSexs003MaryF（2）日期数据的插入例4：Alter Table Student Add birth Date;Insert Into StudentValues（s004，Rose, 22, F, to_date(11/08/1981, dd/mm/yyyy)）;Insert Into StudentValues（s005，Jack, 22, M, to_date(12-08-1981, dd-mm-yyyy)）;使用To_Date函数插入日期型2. 插入子查询结果语句格式 INSERT INTO ( ， ) 子查询；功能 将子查询结果插入指定表中插入子查询结果（续）例3 对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表 CREATE TABLE Deptage (Sde