数据库系统原理数据库语言new.ppt

1、第3章 数据库语言sql3.1 sql语言概况3.2 sql数据定义语言3.3 sql数据查询语言3.4 sql数据操纵语言3.5 sql视图3.6 sql数据控制语言3.7 嵌入式sql3.8 动态sql3.9 sql存储过程3.1 sql语言概况1.sql简介 结构化查询语言sql（structured query language）是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的标准语言。 sql语言的版本包括：sql-89，sql-92，sql3。2.sql特点 sql语言之所以能够为用户和

2、业界所接受，成为国际标准，是因为它是一个综合的、通用的、功能极强同时又简洁易学的语言。 sql语言集数据查询（data query）、数据操纵（data manipulation）、数据定义（data definition）和数据控制（data control）功能于一体，充分体现了关系数据语言的特点和优点。3.1 sql语言概况3. sql特点(续)1. 综合统一 参见2. 高度非过程化 参见3. 面向集合的操作方式 参见4. 以同一种语法结构提供两种使用方式 参见 5. 语言简洁，易学易用 参见3.1 sql语言概况4. sql的基本概念 sql语言支持关系数据库三级模式结构。其中外模式对

3、应于视图（view）和部分基本表（base table），模式对应于基本表，内模式对应于存储文件。 基本表是本身独立存在的表，在sql中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。 存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 用户可以用sql语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。 3.

4、2 sql数据定义语言1. 定义基本表一般格式如下：create table ( 列级完整性约束条件列级完整性约束条件 ， 列级完整性约束条件列级完整性约束条件.) , ;语法图参见其中是所要定义的基本表的名字，它可以由一个或多个属性（列）组成。建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由dbms自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。 3.2 sql数据定义语言例1：创建student,course,sc三个基表 cre

5、ate table student (sno char(7) not null, sname varchar(10) not null, sex char(1) not null, bdate date not null, height dec(5,2) default 00.0, primary key(sno); /定义主键 create table course (cno char(6) not null, cname varchar(30) not null, lhour smallint not null, credit dec(1,0) not null, semester cha

6、r(2) not null, primary key(cno); /定义主键3.2 sql数据定义语言例1：创建student,course,sc三个基表 create table sc (sno char(7) not null, cno char(6) not null, grade dec(4,1) default null, primary key(sno,cno), /定义主键 foreign key(sno) /定义外键 references student on delete cascade, foreign key(cno) /定义外键 references course on

7、 delete restrict)3.2 sql数据定义语言定义表的各个属性时需要指明其数据类型及长度。不同的数据库系统支持的数据类型不完全相同，例如ibm db2 sql主要支持以下数据类型： smallint 半字长二进制整数。 integer或int 全字长二进制整数。 decimal(p,q)或dec(p,q) 压缩十进制数，共p位，其中小 数点后有q位。0qp15，q=0时可 以省略。 float 双字长浮点数。 charter(n)或char(n) 长度为n的定长字符串。 varchar(n) 最大长度为n的变长字符串。 graphic(n) 长度为n的定长图形字符串。 vargr

8、aphic(n) 最大长度为n的变长图形字符串。 date 日期型，格式为yyyy-mm-dd。 time时间型， 格式为hh.mm.ss。 timestamp 日期加时间。3.2 sql数据定义语言2.修改基本表一般格式为： alter table add 完整性约束完整性约束 drop modify ; 其中指定需要修改的基本表，add子句用于增加新列和新的完整性约束条件，drop子句用于删除指定的完整性约束条件，modify子句用于修改原有的列定义。3.2 sql数据定义语言例2:向student表增加“入学时间” (scome)列，其数据类型为日期型 alter table stude

9、nt add scome date; 不论基本表中原来是否已有数据，新增加的列一律为空值。 例3:将学生姓名sname的长度增加到30 alter table student modify sname varchar(30); 修改原有的列定义有可能会破坏已有数据。 例4： 删除关于学号为主键的约束 alter table student drop primary key; 3.2 sql数据定义语言3. 删除基本表一般格式为： drop table 例5： 删除student表 drop table student 基本表定义一旦删除，表中的数据、在此表上建立的索引都将自动被删除掉，而建立在

10、此表上的视图虽仍然保留，但已无法引用。因此执行删除操作一定要格外小心。 3.2 sql数据定义语言4.建立索引一般格式为：create unique cluster index on ( ,.); 其中，指定要建索引的基本表的名字。索引可以建在该表的一列或多列上，各列名之间用逗号分隔。每个后面还可以用指定索引值的排列次序，包括asc（升序）和desc（降序）两种，缺省值为asc。3.2 sql数据定义语言例6： 为学生-课程数据库中的student、couse、sc三个表建立索引。其中student表按学号升序建唯一索引，couse表按课程号升序建唯一索引，sno、cno表按学号升序和课程号降

11、序建唯一索引。 create unique index stusno on student(sno);create unique index coucno on couse(cno); create unique index scno on sc(sno asc,cno desc); 5.删除索引一般格式 drop index 3.3 sql数据查询语言1.基本查询语句基本查询语句一般格式：一般格式： select all|distinct,. from , . where group by having order by asc|desc; 语法图语法图 整个整个select语句的含义是，根

12、据语句的含义是，根据where子句的条件表达式，子句的条件表达式，从从from子句指定的基本表或视图中找出满足条件的元组，再子句指定的基本表或视图中找出满足条件的元组，再按按select子句中的目标列表达式，选出元组中的属性值形成子句中的目标列表达式，选出元组中的属性值形成结果表。如果有结果表。如果有group子句，则将结果按子句，则将结果按的值进行分的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中作用集函数。如果一条记录。通常会在每组中作用集函数。如果group子句带子句带having短语，则只有满

13、足指定条件的组才予输出。如果有短语，则只有满足指定条件的组才予输出。如果有order子句，则结果表还要按子句，则结果表还要按的值的升序或降序排序。的值的升序或降序排序。3.3 sql数据查询语言2.单表查询单表查询(1)选择表中的若干列选择表中的若干列 1) 查询指定列查询指定列例例1: 查询全体学生的学号与姓名查询全体学生的学号与姓名 select sno,sname from student;说明：说明： 中各个列的先后顺序可以与表中的顺中各个列的先后顺序可以与表中的顺序不一致。也就是说，用户在查询时可以根据应用的需要改变序不一致。也就是说，用户在查询时可以根据应用的需要改变列的显示顺序。

14、列的显示顺序。例例2: 查询全体学生的姓名、学号、所在系查询全体学生的姓名、学号、所在系 select sname, sno, sdept from student;说明：这时结果表中的列的顺序与基表中不同，是按查询要求，说明：这时结果表中的列的顺序与基表中不同，是按查询要求，先列出姓名属性，然后再列学号属性和所在系属性。先列出姓名属性，然后再列学号属性和所在系属性。3.3 sql数据查询语言2) 查询全部列查询全部列例例3：查询全体学生的详细记录：查询全体学生的详细记录 select * from student;说明：该说明：该select语句实际上是无条件地把语句实际上是无条件地把stu

15、dent表的全部信表的全部信息都查询出来，所以也称为全表查询，这是最简单的一种查询。息都查询出来，所以也称为全表查询，这是最简单的一种查询。3) 查询经过计算的值查询经过计算的值 select子句的子句的不仅可以是表中的属性列，也不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。算后列出结果。 3.3 sql数据查询语言例例4:查全体学生的姓名及其出生年份查全体学生的姓名及其出生年份 select sname, 1996-sage from student;不仅可以是算术表达式，还可以是字符串常量

16、、函数不仅可以是算术表达式，还可以是字符串常量、函数等等例例5：查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系：查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名名 select sname, year of birth:, 1996-sage, islower(sdept) from student；用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用。例如对于上例，可以如式、常量、函数名的目标列表达式尤为有用。例如对于上例，可以如下定义列别名：下定义列别名：

17、例例6： select sname name, year of birth: birth, 1996-sage birthday, islower(sdept) department from student;(软件学院软件学院 3月月6日日)3.3 sql数据查询语言(2) 选择表中的若干元组选择表中的若干元组 1). 消除取值重复的行消除取值重复的行例例7: 查所有选修过课的学生的学号查所有选修过课的学生的学号 select sno from sc; 假设假设sc表中有下列数据表中有下列数据 sno cno grade- - - 95001 1 92 95001 2 85 95001 3

18、88 95002 2 90 95002 3 80 3.3 sql数据查询语言 例例7 (续续)：执行上面的：执行上面的select语句后，结果为：语句后，结果为： sno - 95001 95001 95001 95002 95002 该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必该查询结果里包含了许多重复的行。如果想去掉结果表中的重复行，必须指定须指定distinct短语：短语： select distinct sno from sc; 执行结果为：执行结果为： sno - 95001 95002 3.3 sql数据查询语言(2) 选择表中的若干元组选择表中的若干元组 1)

19、查询满足条件的元组查询满足条件的元组 查询满足指定条件的元组可以通过查询满足指定条件的元组可以通过where子句实现。子句实现。where子句常用的查询条件如表子句常用的查询条件如表3-3所示。所示。 表表3-3 常用的查询条件常用的查询条件 查询条件查询条件 谓谓 词词 - 比较比较 上述比较运算符上述比较运算符 确定范围确定范围 between and, not between and 确定集合确定集合 in, not in 字符匹配字符匹配 like, not like 空值空值 is null, is not null 多重条件多重条件 and, or3.3 sql数据查询语言 2).

20、查询满足条件的元组查询满足条件的元组例例8: 查计算机系全体学生的名单查计算机系全体学生的名单 select sname from student where sdept = cs; 例例9: 查所有年龄在查所有年龄在20岁以下的学生姓名及其年龄岁以下的学生姓名及其年龄 select sname, sage from student where sage = 20; 3.3 sql数据查询语言例例10:查考试成绩有不及格的学生的学号查考试成绩有不及格的学生的学号 select distinct sno from course where grade 60; 这里使用了这里使用了distinct

21、短语，当一个学生有多门课程不及格，他短语，当一个学生有多门课程不及格，他的学号也只列一次。的学号也只列一次。例例11: 查询年龄在查询年龄在20至至23岁之间的学生的姓名、系别、和岁之间的学生的姓名、系别、和年龄年龄 select sname, sdept, sagefrom student where sage between 20 and 23; 与与between.and.相对的谓词是相对的谓词是not between.and.。 3.3 sql数据查询语言例例12:查询年龄不在查询年龄不在20至至23岁之间的学生姓名、系别和年龄。岁之间的学生姓名、系别和年龄。 select sname

22、, sdept, sage from studentwhere sage not between 20 and 23; 例例13: 查信息系（查信息系（is）、数学系（）、数学系（ma）和计算机科学系（）和计算机科学系（cs）的学）的学生的姓名和性别生的姓名和性别 select sname, ssex from student where sdept in (is, ma, cs) 与与in相对的谓词是相对的谓词是not in，用于查找属性值不属于指定集合的元，用于查找属性值不属于指定集合的元组。组。 3.3 sql数据查询语言例例13:查既不是信息系、数学系，也不是计算机科学系的查既不是信息

23、系、数学系，也不是计算机科学系的学生的姓名和性别学生的姓名和性别 select sname, ssex from student where sdept not in (is, ma, cs) ; 谓词谓词like可以用来进行字符串的匹配。其一般语法格可以用来进行字符串的匹配。其一般语法格式如下：式如下： not like escape 其含义是查找指定的属性列值与其含义是查找指定的属性列值与相匹配的元相匹配的元组。组。可以是一个完整的字符串，也可以含有可以是一个完整的字符串，也可以含有通配符通配符%和和_。%(百分号百分号) 代表任意长度（长度可以为代表任意长度（长度可以为0）的字符串。）的

24、字符串。_(下横线下横线) 代表任意单个字符代表任意单个字符。3.3 sql数据查询语言例例14: 查所有姓刘的学生的姓名、学号和性别查所有姓刘的学生的姓名、学号和性别 select sname, sno, ssexfrom student where sname like 刘刘%; 例例15: 查姓查姓“欧阳欧阳”且全名为三个汉字的学生的姓名且全名为三个汉字的学生的姓名 select sname from student where sname like 欧阳欧阳_; 注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需注意，由于一个汉字占两个字符的位置，所以匹配串欧阳后面需要跟个要跟个

25、_。 3.3 sql数据查询语言例例16: 查名字中第二字为查名字中第二字为“阳阳”字的学生的姓名和学号字的学生的姓名和学号 select sname, sno from student where sname like _阳阳%; 例例17: 查所以不姓刘的学生姓名查所以不姓刘的学生姓名 select sname, sno, ssex from student where sname not like 刘刘%; 如果用户要查询的匹配字符串本身就含有如果用户要查询的匹配字符串本身就含有%或或_，比如要查名字为，比如要查名字为db_design的课程的学分，应如何实现呢？这时就要使用的课程的学分

26、，应如何实现呢？这时就要使用escape 短语对通配符进行转义了。短语对通配符进行转义了。 (计算机计算机01-05 3月月7日）日）3.3 sql数据查询语言例例18: 查查db_design课程的课程号和学分课程的课程号和学分 select cno, ccredit from course where cname like db_design escape 说明：说明：escape 短语表示短语表示为换码字符，这样匹配串中紧跟在为换码字符，这样匹配串中紧跟在后面的字符后面的字符”_”不再具有通配符的含义，而是取其本身含义，被不再具有通配符的含义，而是取其本身含义，被转义为普通的转义为普通的

27、”_”字符。字符。例例19：查以：查以”db_”开头，且倒数第三个字符为开头，且倒数第三个字符为i的课程的详细情况的课程的详细情况 select * from course where cname like db_%i_ escape ;3.3 sql数据查询语言例例20:某些学生选修某门课程后没有参加考试，所以有选课记录，但某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应没有考试成绩，下面我们来查一下缺少成绩的学生的学号和相应的课程号的课程号 select sno, cno from scwhere grade is null;

28、注意：这里的注意：这里的is不能用等号（不能用等号（=） 代替。代替。例例21:查所有有成绩的记录的学生学号和课程号查所有有成绩的记录的学生学号和课程号 select sno, cno from sc where grade is not null; 3.3 sql数据查询语言例例22:查查cs系年龄在系年龄在20岁以下的学生姓名岁以下的学生姓名 select sname from student where sdept=cs and sage20; 例例12 中的中的in谓词实际上是多个谓词实际上是多个or运算符的缩写，因此运算符的缩写，因此例例12中的查询也可以用中的查询也可以用or运算符

29、写成如下等价形式：运算符写成如下等价形式： select sname, ssex from student where sdept=is or sdept=ma or sdept=cs; 3.3 sql数据查询语言(3) 对查询结果排序对查询结果排序 如果没有指定查询结果的显示顺序，如果没有指定查询结果的显示顺序，dbms将按其最方便的顺序将按其最方便的顺序（通常是元组在表中的先后顺序）输出查询结果。用户也可以用（通常是元组在表中的先后顺序）输出查询结果。用户也可以用order by子句指定按照一个或多个属性列的升序（子句指定按照一个或多个属性列的升序（asc）或降序）或降序（desc）重新排

30、列查询结果，其中升序）重新排列查询结果，其中升序asc为缺省值。为缺省值。例例23:查询选修了查询选修了3号课程的学生的学号及其成绩，查询结果按分数号课程的学生的学号及其成绩，查询结果按分数的降序排列的降序排列 select sno, grade from sc where cno=1 order by grade desc; 前面已经提到，可能有些学生选修了前面已经提到，可能有些学生选修了3号课程后没有参加考试，即号课程后没有参加考试，即成绩列为空值。用成绩列为空值。用order by子句对查询结果按成绩排序时，若按子句对查询结果按成绩排序时，若按升序排，成绩为空值的元组将最后显示，若按降序

31、排，成绩为空值升序排，成绩为空值的元组将最后显示，若按降序排，成绩为空值的元组将最先显示。的元组将最先显示。3.3 sql数据查询语言例例24: 查询全体学生情况，查询结果按所在系升序排列，对同一系中查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列的学生按年龄降序排列 select * from student order by sdept, sage desc;(4) 使用集函数使用集函数 为了进一步方便用户，增强检索功能，为了进一步方便用户，增强检索功能，sql提供了许多集函数，提供了许多集函数，主要包括：主要包括： count(distinct|all *) 统

32、计元组个数统计元组个数 count(distinct|all ) 统计一列中值的个数统计一列中值的个数 sum(distinct|all ) 计算一列值的总和（此列计算一列值的总和（此列必须是数值型）必须是数值型） avg(distinct|all ) 计算一列值的平均值（此计算一列值的平均值（此列必须是数值型）列必须是数值型）max(distinct|all ) 求一列值中的最大值求一列值中的最大值 min(distinct|all ) 求一列值中的最小值求一列值中的最小值3.3 sql数据查询语言例例25:查询学生总人数查询学生总人数 select count(*) from studen

33、t; 例例26: 查询选修了课程的学生人数查询选修了课程的学生人数 select count(distinct sno) from sc;说明：学生每选修一门课，在说明：学生每选修一门课，在sc中都有一条相应的记录，而一中都有一条相应的记录，而一个学生一般都要选修多门课程，为避免重复计算学生人数，必个学生一般都要选修多门课程，为避免重复计算学生人数，必须在须在count函数中用函数中用distinct短语。短语。例例27：计算：计算1号课程的学生平均成绩号课程的学生平均成绩 select avg(grade) from sc where cno=1; 3.3 sql数据查询语言例例28：查询学

34、习：查询学习1号课程的学生最高分数号课程的学生最高分数 select max(grade)from sc where cno=1; (5) 对查询结果分组对查询结果分组 group by子句可以将查询结果表的各行按一列或多列取值相等的原子句可以将查询结果表的各行按一列或多列取值相等的原则进行分组。对查询结果分组的目的是为了细化集函数的作用对象。则进行分组。对查询结果分组的目的是为了细化集函数的作用对象。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都结果只有一个函

35、数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。有一个函数值。 例例29：查询各个课程号与相应的选课人数：查询各个课程号与相应的选课人数 select cno, count(sno) from sc group by cno;如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用指定条件的组，则可以使用having短语指定筛选条件。短语指定筛选条件。3.3 sql数据查询语言例例30：查询信息系选修了：查询信息系选修了3门以上课程的学生的学号门以上课程的学生的学号 select sno fro

36、m sc where sno in (select sno from students where sdept=is) group by sno having count(*)3;3.3 sql数据查询语言2.连接查询连接查询一个数据库中的多个表之间一般都存在某种内在联系，它们共同提供有用的信一个数据库中的多个表之间一般都存在某种内在联系，它们共同提供有用的信息。前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，息。前面的查询都是针对一个表进行的。若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询主要包括等值连接、非等值连接查询、自身连接则称之为连接查询。连接查询主要包括

37、等值连接、非等值连接查询、自身连接查询（连接查询（连接 二、自身连接）、外连接查询（连接二、自身连接）、外连接查询（连接 三、外连接）和复合条件连三、外连接）和复合条件连接查询（连接接查询（连接 四、复合条件连接）。四、复合条件连接）。（1） 等值连接与非等值连接查询等值连接与非等值连接查询 用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：. . 其中比较运算符主要有：其中比较运算符主要有：=、=、=、!=、!= 此外连接谓词词还可以使用下面形式：此外连接谓词词还可以使用下面形式：. between . and . 当连接运算

38、符为当连接运算符为=时，称为等值连接。使用其它运算符称为非等值连接。时，称为等值连接。使用其它运算符称为非等值连接。 详细说明详细说明3.3 sql数据查询语言例例32：查询每个学生及其选修课程的情况：查询每个学生及其选修课程的情况 select student.*, sc.* from student, sc where student.sno=sc.sno 连接运算中有两种特殊情况，一种称为卡氏积连接，另一种称连接运算中有两种特殊情况，一种称为卡氏积连接，另一种称为自然连接。卡氏积是不带连接谓词的连接。两个表的卡氏积为自然连接。卡氏积是不带连接谓词的连接。两个表的卡氏积即是两表中元组的交叉

39、乘积，也即其中一表中的每一元组都要即是两表中元组的交叉乘积，也即其中一表中的每一元组都要与另一表中的每一元组作拼接，因此结果表往往很大。与另一表中的每一元组作拼接，因此结果表往往很大。 如果是按照两个表中的相同属性进行等值连接，且目标列中去如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列，则称之为掉了重复的属性列，但保留了所有不重复的属性列，则称之为自然连接。自然连接。例例33：自然连接：自然连接student和和sc表表 select student.sno, sname, ssex, sage, sdept, cno, grade from

40、 student, sc where student.sno=sc.sno; 3.3 sql数据查询语言(2) 自连接自连接连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自连接。连接，这种连接称为表的自连接。例例34:查询每一门课的间接先修课（即先修课的先修课）查询每一门课的间接先修课（即先修课的先修课） 完成该查询的完成该查询的sql语句为：语句为： select first.cno, second. pcno from course first, course second where firs

41、t.pcno=second.cno; (3) 外连接外连接 在通常的连接操作中，只有满足连接条件的元组才能作为结果在通常的连接操作中，只有满足连接条件的元组才能作为结果输出，如在例输出，如在例32和例和例33的结果表中没有关于的结果表中没有关于95003和和95004两个两个学生的信息，原因在于他们没有选课，在学生的信息，原因在于他们没有选课，在sc表中没有相应的元表中没有相应的元组。但是有时我们想以组。但是有时我们想以student表为主体列出每个学生的基本情表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，则只输出其基本情况况及其选课情况，若某个学生没有选课，则只输出其基本情

42、况信息，其选课信息为空值即可，这时就需要使用外连接信息，其选课信息为空值即可，这时就需要使用外连接（outer join）。外连接的运算符通常为）。外连接的运算符通常为*。有的关系数据库中。有的关系数据库中也用也用+。这样，我们就可以如下改写例。这样，我们就可以如下改写例33：3.3 sql数据查询语言 select student.sno, sname, ssex, sage, sdept, cno, grade from student, sc where student.sno=sc.sno(*); 上例中外连接符上例中外连接符*出现在连接运算符的右边，所以也称其为右出现在连接运算符的右

43、边，所以也称其为右外连接。相应地，如果外连接符出现在连接运算符的左边，则外连接。相应地，如果外连接符出现在连接运算符的左边，则称为左外连接。称为左外连接。 (4) 复合条件连接复合条件连接上面各个连接查询中，上面各个连接查询中，where子句中只有一个条件，即用于子句中只有一个条件，即用于连接两个表的谓词。连接两个表的谓词。where子句中有多个条件的连接操作，子句中有多个条件的连接操作，称为复合条件连接。称为复合条件连接。例例35:查询选修查询选修2号课程且成绩在号课程且成绩在90分以上的所有学生分以上的所有学生 select student.sno, sname from student,

44、 sc where student.sno=sc.sno and sc.cno=2 and sc.grade90; 3.3 sql数据查询语言连接操作除了可以是两表连接，一个表与其自身连接外，还可连接操作除了可以是两表连接，一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接。以是两个以上的表进行连接，后者通常称为多表连接。例例36:查询每个学生及其选修的课程名其及成绩查询每个学生及其选修的课程名其及成绩 select student.sno, sname, course.cname, sc.grade from student, sc, course where stud

45、ent.sno=sc.sno and sc.cno=course.cno; 3.3 sql数据查询语言3. 嵌套查询嵌套查询在在sql语言中，一个语言中，一个select-from-where语句语句称为一个查询块。将一个查询块嵌套在另一个查询称为一个查询块。将一个查询块嵌套在另一个查询块的块的where子句或子句或having短语的条件中的查询称短语的条件中的查询称为嵌套查询或子查询。例如：为嵌套查询或子查询。例如： select sname from studentwhere sno in select sno from sc where cno=2;3.3 sql数据查询语言(1). 带

46、有带有in谓词的子查询谓词的子查询带有带有in谓词的子查询是指父查询与子查询之间用谓词的子查询是指父查询与子查询之间用in进行连接，判断某进行连接，判断某个属性列值是否在子查询的结果中。由于在嵌套查询中，子查询的结个属性列值是否在子查询的结果中。由于在嵌套查询中，子查询的结果往往是一个集合，所以谓词果往往是一个集合，所以谓词in是嵌套查询中最经常使用的谓词。是嵌套查询中最经常使用的谓词。例例37 :查询与查询与“刘晨刘晨”在同一个系学习的学生在同一个系学习的学生 select sno, sname, sdeptfrom student where sdept in select sdept f

47、rom student where sname=刘晨刘晨; 本例中的查询也可以用我们前面学过的表的自身连接查询来完本例中的查询也可以用我们前面学过的表的自身连接查询来完成：成：select sno, sname, sdept from student s1, student s2 where s1.sdept = s2.sdept and s2.sname=刘晨刘晨; 3.3 sql数据查询语言例例38:查询选修了课程名为查询选修了课程名为信息系统信息系统的学生学号和姓名的学生学号和姓名 select sno, sname from student where sno in ( select

48、sno from sc where cno in (select cno from course where cname=信息系统信息系统);本查询同样可以用连接查询实现：本查询同样可以用连接查询实现：select sno, snamefrom student, sc, course where student.sno=sc.sno and sc.cno=course.cno and course.cname=信息系统信息系统;3.3 sql数据查询语言(2) 带有比较运算符的子查询带有比较运算符的子查询带有比较运算符的子查询是指父查询与子查询之间用比较运算带有比较运算符的子查询是指父查询与子

49、查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单值时，可符进行连接。当用户能确切知道内层查询返回的是单值时，可以用以用、 =、=、!=或或等比较运算符。等比较运算符。(3) 带有带有any或或all谓词的子查询谓词的子查询子查询返回单值时可以用比较运算符。而使用子查询返回单值时可以用比较运算符。而使用any或或all谓词谓词时则必须同时使用比较运算符。时则必须同时使用比较运算符。例例39 :查询其他系中比查询其他系中比is系任一学生年龄小的学生名单系任一学生年龄小的学生名单 select sname, sage from student where sage any (sele

50、ct sage from student where sdept=is ) and sdept is order by sage desc;3.3 sql数据查询语言本查询实际上也可以用集函数实现。本查询实际上也可以用集函数实现。select sname, sage from student where sage (select max(sage) from student where sdept=is) and sdept is order by sage desc; (4) 带有带有exists谓词的子查询谓词的子查询exists代表存在量词彐。带有代表存在量词彐。带有exists谓词的子

51、查询不返回任何实际谓词的子查询不返回任何实际数据，它只产生逻辑真值数据，它只产生逻辑真值“true或逻辑假值或逻辑假值“false。例例40: 查询所有选修了查询所有选修了1号课程的学生姓名号课程的学生姓名 select snamefrom student swhere exists (select * from sc where sno=s.sno and cno=1); 使用存在量词使用存在量词exists后，若内层查询结果非空，则外层的后，若内层查询结果非空，则外层的where子子句返回真值，否则返回假值句返回真值，否则返回假值 (软件学院软件学院 3月月18日）日）3.3 sql数据查

52、询语言例例41:查询所有未修查询所有未修1号课程的学生姓名号课程的学生姓名 select sname from student where not exists (select * from sc where sno=student.sno and cno=1); 例例42: 查询选修了全部课程的学生姓名查询选修了全部课程的学生姓名 select sname from student swhere not exists (select * from course c where not exists (select * from sc where s.sno=sc.sno and c.cno=

53、c.cno); 3.3 sql数据查询语言例例43:查询至少选修了学生查询至少选修了学生95002选修的全部课程的学生号码。选修的全部课程的学生号码。 select distinct sno from sc scx 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) ); 3.3 sql数据查询语言4. 集合查询集合查询每一个每一个select语句都能获得一个或一组元组

54、。若要把多个语句都能获得一个或一组元组。若要把多个select语句的结果合并为一个结果语句的结果合并为一个结果,可用集合操作来完成。集可用集合操作来完成。集合操作主要包括并操作合操作主要包括并操作union、交操作、交操作intersect和差操作和差操作minus。 例例45：查询计算机科学系的学生及年龄不大于：查询计算机科学系的学生及年龄不大于19岁的学生岁的学生 select * from student where sdept=cs union select * from student where sage=19；3.4 sql数据操纵语言1. insert语句语句(1) 插入单个元

55、组插入单个元组 插入单个元组的插入单个元组的insert语句的格式为：语句的格式为：insert into (,.) values ( ,.) 例例1:将一个新学生记录（学号：将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：；姓名：陈冬；性别：男；所在系： is；年龄：；年龄：18岁）插入岁）插入student表中表中 insert into student values (95020, 陈冬陈冬, 男男, is, 18); 例例2: 插入一条选课记录插入一条选课记录(95020，1) insert into sc(sno, cno) values (95020, 1); 新

56、插入的记录在新插入的记录在grade列上取空值。列上取空值。 3.4 sql数据操纵语言(2) 插入子查询结果插入子查询结果插入子查询结果的插入子查询结果的insert语句的格式为：语句的格式为： insert into ( ,.) 子查询；子查询；其功能是以批量插入，一次将子查询的结果全部插入指定表中。其功能是以批量插入，一次将子查询的结果全部插入指定表中。例例3 :对每一个系，求学生的平均年龄，并把结果存入数据库对每一个系，求学生的平均年龄，并把结果存入数据库 create table deptage (sdept char(15) , avgage smallint); insert i

57、nto deptage(sdept, avgage) select sdept, avg(sage) from student group by sdept;3.4 sql数据操纵语言2. update语句语句 一般格式为：一般格式为： update set =,=. where ;其功能是修改指定表中满足其功能是修改指定表中满足where子句条件的元组。其中子句条件的元组。其中set子句用于指定修改方法，即用子句用于指定修改方法，即用的值取代相应的属的值取代相应的属性列值。如果省略性列值。如果省略where子句，则表示要修改表中的所有元子句，则表示要修改表中的所有元组。组。(1) 修改某一个

58、元组的值修改某一个元组的值例例4: 将学生将学生95001的年龄改为的年龄改为22岁岁 update student set sage=22 where sno=95001; 3.4 sql数据操纵语言(2) 修改多个元组的值修改多个元组的值例例5: 将所有学生的年龄增加将所有学生的年龄增加1岁岁 update student set sage=sage+1; (3) 带子查询的修改语句带子查询的修改语句子查询也可以嵌套在子查询也可以嵌套在update语句中，用以构造执行修改操作语句中，用以构造执行修改操作的条件。的条件。例例6:将计算机科学系全体学生的成绩置零将计算机科学系全体学生的成绩置零

59、 update sc set grade=0 where sno in (select sno from student where sdept=sc)3.4 sql数据操纵语言(4) 修改操作与数据库的一致性修改操作与数据库的一致性update语句一次只能操作一个表。这会带来一些问题。语句一次只能操作一个表。这会带来一些问题。例如，学号为例如，学号为95007的学生因病休学一年，复学后需要将其学的学生因病休学一年，复学后需要将其学号改为号改为96089，由于，由于student表和表和sc表都有关于表都有关于95007的信息，的信息，因此两个表都需要修改，这种修改只能通过两条因此两个表都需要

60、修改，这种修改只能通过两条update语句语句进行。进行。第一条第一条update语句修改语句修改student表：表： update student set sno=96089 where sno=95007;第二条第二条update语句修改语句修改sc表：表： update sc set sno=96089 where sno=95007;3.4 sql数据操纵语言在执行了第一条在执行了第一条update语句之后，数据库中的数据已处于不语句之后，数据库中的数据已处于不一致状态，因为这时实际上已没有学号为一致状态，因为这时实际上已没有学号为95007的学生了，但的学生了，但sc表中仍然记录着