数据库的查询课件

1、sql数据查询数据查询 基本结构：基本结构：select a1 , a2 , , anfrom r1 , r2 , , rmwhere p语句格式语句格式select all|distinct *|,from ， where group by having order by asc|desc ；学生学生-课程数据库课程数据库 学生表：学生表：student(sno，sname，ssex，sage， sdept) 课程表：课程表：course(cno，cname，semester，ccredit) 学生选课表：学生选课表：sc(sno，cno，grade) select语句的含义语句的含义 结果

2、集结果集 查询操作的对象是关系，结果还是一个关系，查询操作的对象是关系，结果还是一个关系，是一个结果集，是一个动态数据集是一个结果集，是一个动态数据集select子句 用以列出查询结果集中的期望属性用以列出查询结果集中的期望属性. select sno, sname from studentselect子句子句 *与属性列表与属性列表 星号星号 * 表示所有属性表示所有属性 星号星号 * ：按关系模式中属性的顺序排列，：按关系模式中属性的顺序排列，并具有一定的逻辑数据独立性并具有一定的逻辑数据独立性 显式列出属性名：按用户顺序排列显式列出属性名：按用户顺序排列select * from stu

3、dent 等价于：等价于：select sno, sname，ssex,sage，sdept from studentselect子句子句更名更名 为结果集中的某个属性改名为结果集中的某个属性改名 使结果集更具可读性使结果集更具可读性 select sno as stu_no，cno as course_no， grade from sc select sno，sname，2009 - sage as birthday from studentselect子句子句重复元组重复元组 select 子句的缺省情况是保留重复元组子句的缺省情况是保留重复元组（ all ），可用），可用 distinc

4、t 去除重复元去除重复元组组 select distinct sdept from student select all sdept from student 去除重复元组需要临时表的支持去除重复元组需要临时表的支持where 子句子句 查询满足指定条件的元组可以通过查询满足指定条件的元组可以通过where子句来实现子句来实现 使使where子句中的逻辑表达式返回子句中的逻辑表达式返回true值的元组，是符合要求的元组，将被选值的元组，是符合要求的元组，将被选择出来择出来where 子句子句运算符运算符 比较：比较：、=、=、not 确定范围：确定范围： between a and b、not

5、 between a and b 确定集合：确定集合：in、not in 字符匹配：字符匹配：like，not like 空值：空值：is null、is not null 多重条件：多重条件：and、or、notwhere 子句子句比较比较 例，查询所有年龄在例，查询所有年龄在20岁以下的学生姓岁以下的学生姓名及其年龄。名及其年龄。 select sname,sage from student where sage =70 and grade80 练习，检索选修练习，检索选修1号或号或2号课程号课程,且成绩高且成绩高于于80分的学生号分的学生号,课程号和分数课程号和分数 from 子句子句

6、列出将被扫描的关系（表）列出将被扫描的关系（表） 例：列出所有学生的学号、姓名、课号、例：列出所有学生的学号、姓名、课号、成绩。成绩。select student.sno，sname，sc.cno，gradefrom student，scwhere student.sno = sc.snofrom 子句子句元组变量元组变量 为为 from 子句中的关系定义元组变量子句中的关系定义元组变量 方便关系名的引用方便关系名的引用 例：列出与例：列出与s1同岁的同学的学号同岁的同学的学号,姓名姓名,年龄年龄select t.sno,t.sname,t.sagefromstudent as t,stude

7、nt as swhere s.sno = s1 andt.sage = s.sageorder by子句子句 指定结果集中元组的排列次序指定结果集中元组的排列次序 asc（缺省）、（缺省）、desc、未选中的属性、未选中的属性 例：列出例：列出cs系中的男生的学号、姓名、系中的男生的学号、姓名、年龄，并按年龄进行排列（升序）年龄，并按年龄进行排列（升序） select sno, sname,sage from student where sdept = cs order by sage asc 集函数集函数 计数计数 count（distinct|all *） count（distinct|a

8、ll ） 计算总和计算总和 sum（distinct|all ） 计算平均值计算平均值 avg（distinct|all ） 求最大值求最大值 max（distinct|all ） 求最小值求最小值 min（distinct|all ） 例例, 查询学生总人数。查询学生总人数。 select count(*) from student; 例例, 查询选修了课程的学生人数。查询选修了课程的学生人数。 select count(distinct sno) from sc; 例例, 计算计算1号课程的学生总分和平均成绩。号课程的学生总分和平均成绩。select sum(grade) 总分总分, av

9、g(grade) 平均分平均分 from sc where cno= 1; 例例, 查询选修查询选修3号课程的学生最高分数与最低分号课程的学生最高分数与最低分的差。的差。 select max(grade)-min(grade) from sc where cno= 3; group by子句子句 使用使用group by子句对查询结果分组子句对查询结果分组 细化集函数的作用对象细化集函数的作用对象 未对查询结果分组，集函数将作用于整个未对查询结果分组，集函数将作用于整个查询结果查询结果 对查询结果分组后，集函数将分别作用于对查询结果分组后，集函数将分别作用于每个组每个组 例例, 求各个课程号

10、及相应的选课人数。求各个课程号及相应的选课人数。 select cno, count(sno) from sc group by cno; 注意：使用注意：使用group by子句后，子句后，select子句的列名列表中只能出现分组属性和集子句的列名列表中只能出现分组属性和集函数函数group by子句子句having短语短语 使用使用having短语设定筛选条件短语设定筛选条件 例例, 查询选修了查询选修了3门以上课程的学生学号，门以上课程的学生学号，及选修的课程数。及选修的课程数。 select sno, count(*) from sc group by sno having count

11、(*) 3; having短语短语 例例, 查询有查询有2门以上课程是门以上课程是90分以上的分以上的学生的学号及（学生的学号及（90分以上的）课程数分以上的）课程数 select sno, count(*) from sc where grade=90 group by sno having count(*)=2; having短语与短语与where子句的区别：子句的区别： 作用对象不同作用对象不同 where子句作用于基表或视图，从中选择子句作用于基表或视图，从中选择满足条件的元组。满足条件的元组。 having短语作用于组，从中选择满足条件短语作用于组，从中选择满足条件的组。的组。 练习

12、，求各门课程的平均分练习，求各门课程的平均分, ,最高分最高分, ,最低分及各门课程最低分及各门课程的选修人数的选修人数. . select cno as 课号课号, avg(grade) as 平均分平均分, max(grade) as 最高分最高分, min(grade) as 最低分最低分, count(sno) as 人数人数from scgroup by cno;连接查询连接查询 同时涉及多个表的查询称为连接查询同时涉及多个表的查询称为连接查询 用来连接两个表的条件称为连接条件或连接谓用来连接两个表的条件称为连接条件或连接谓词词 连接查询包括内连接、外连接和交叉连接等。连接查询包括内

13、连接、外连接和交叉连接等。 连接查询中用于连接两个表的条件称为连接条连接查询中用于连接两个表的条件称为连接条件或连接谓词。件或连接谓词。 一般格式：一般格式： . . 比较运算符：比较运算符：=、=、=80 and y.grade=80连接查询连接查询 自连接自连接 是一种特殊的内连接。是一种特殊的内连接。 相互连接的表物理上为同一张表。相互连接的表物理上为同一张表。 必须为两个表取别名，使之在逻辑上成必须为两个表取别名，使之在逻辑上成为两个表。为两个表。 查询与刘晨在同一个系学习的学生的姓查询与刘晨在同一个系学习的学生的姓名和所在的系。名和所在的系。 select s2.sname, s2.

14、sdept from student s1 join student s2 on s1.sdept = s2.sdept where s1.sname = 刘晨刘晨 and s2.sname != 刘晨刘晨 外连接外连接 只限制一张表中的数据必须满足连接条件，而另只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。一张表中数据可以不满足连接条件。 ansi方式的外连接的语法格式为：方式的外连接的语法格式为：from 表表1 left | right outer join 表表2 on theta方式的外连接的语法格式为：方式的外连接的语法格式为： 左外连接： from 表

15、1, 表2 where 表1.列名(+) 表2.列名 右外连接： from 表1, 表2 where 表1.列名 表2.列名(+) 例例,查询学生的选课情况。查询学生的选课情况。 select student.*,cno,grade from student left outer join sc on (student.sno=sc.sno)或者：或者：select student.*,cno,gradefrom student,scwhere student.sno*=sc.sno 查询哪些课程没有人选，列出课程名。查询哪些课程没有人选，列出课程名。 select cname from co

16、urse c left join sc on c.cno = sc.cno where sc.cno is null使用使用top限制结果集限制结果集 在进行查询时有时只希望列出结果集中的前几在进行查询时有时只希望列出结果集中的前几个结果，而不是全部结果。个结果，而不是全部结果。 例如，竞赛时可能只取成绩最高的前三名例如，竞赛时可能只取成绩最高的前三名. 可以使用可以使用top谓词限制输出的结果。格式如下谓词限制输出的结果。格式如下:top n percent with ties n为非负整数。为非负整数。 top n：表示取查询结果的前：表示取查询结果的前n行；行； top n percne

17、t：表示取查询结果前：表示取查询结果前n%行；行； with ties：表示包括并列的结果。：表示包括并列的结果。 查询年龄最大的三个学生的姓名、年龄及查询年龄最大的三个学生的姓名、年龄及所在的系。所在的系。select top 3 sname, sage, sdept from student order by sage desc 若包括年龄并列第若包括年龄并列第3名的学生，则：名的学生，则：select top 3 with ties sname, sage, sdept from student order by sage desc 查询查询vb课程考试成绩前三名的学生的姓课程考试成绩前

18、三名的学生的姓名和成绩。名和成绩。select top 3 with ties sname, grade from student s join sc on s.sno = sc.sno join course c on c.cno = sc.cno where cname = vb order by grade desc子查询子查询 在一个在一个select-from-where语句的语句的where子句中存在另一个子句中存在另一个select语句，语句，称作子查询。称作子查询。 select sname外外层查询层查询/父父查询查询 from student where sno in (s

19、elect sno 内层查询内层查询/子子查询查询 from sc where cno= 2 );嵌套查询求解方法嵌套查询求解方法 不相不相关关子子查询查询: :子子查询查询的的查询条查询条件不依件不依赖赖于父于父查询查询 是由里向外逐是由里向外逐层处层处理。即每理。即每个个子子查询查询在上一在上一级查询处级查询处理之前理之前求解，子求解，子查询查询的的结结果用于建立其父果用于建立其父查询查询的的查查找找条条件件 相相关关子子查询查询: :子子查询查询的的查询条查询条件依件依赖赖于父于父查询查询 首先取外首先取外层查询层查询中表的第一中表的第一个个元元组组，根据，根据它与内层查询它与内层查询相

20、相关关的的属属性性值处值处理理内层查询内层查询，若，若wherewhere子句返回子句返回值为真值为真，则则取此取此元元组组放入放入结结果表；然后再取外果表；然后再取外层层表的下一表的下一个个元元组组；重；重复这复这一一过过程，直至外程，直至外层层表全部表全部检查检查完完为为止。止。 。 嵌套查询嵌套查询1.子查询的结果返回的值为单值。子查询的结果返回的值为单值。 where子句中的子查询由子句中的子查询由 比较符比较符 ,=, 引导引导例，找出年龄小于李芳的学生姓名例，找出年龄小于李芳的学生姓名select sname from student where sage (select sage

21、 from student where sname=李芳李芳);嵌套查询嵌套查询例，查询与例，查询与“刘晨刘晨”在同一个系学习的学生。在同一个系学习的学生。select sno,sname,sdept from student where sdept =(select sdept from student where sname= 刘晨刘晨 );嵌套查询嵌套查询 用自身连接完成本查询要求用自身连接完成本查询要求select s1.sno,s1.sname,s1.sdeptfrom student s1,student s2where s1.sdept = s2.sdept and s2.sna

22、me = 刘刘晨晨;嵌套查询嵌套查询2.子查询返回的结果为一组值子查询返回的结果为一组值 在在where子句中使用谓词有子句中使用谓词有in, any, all, exists 等等(1)in谓词的用法谓词的用法意义意义:判断判断where表达式的值是否在子查表达式的值是否在子查询的结果集合里询的结果集合里,可在可在in前加前加not表示相反表示相反的意义的意义.嵌套查询嵌套查询 例例:找出学习找出学习2号课程的学生姓名号课程的学生姓名. select sname from student where sno in( select sno from sc where cno=2)嵌套查询嵌套查

23、询 例例:查询选修了课程名为查询选修了课程名为“信息系统信息系统”的的学生学号和姓名学生学号和姓名 (select cno from course where cname= 信息系统信息系统);(select sno from sc where cno inselect sno,sname from student where sno in嵌套查询（子查询）嵌套查询（子查询）(2)any（some）谓词的用法）谓词的用法意义意义:where表达式的值与子查询结果集表达式的值与子查询结果集合中任何一个值比较的结果为真合中任何一个值比较的结果为真,整个条整个条件为真件为真.和和any配合使用的比较

24、符有配合使用的比较符有:(!=), =, , =, any (select grade from sc where cno=02);嵌套查询嵌套查询 例例:求选修求选修1号课程的学生的学号号课程的学生的学号,成绩成绩,这这些学生的成绩比选修些学生的成绩比选修2号课程的最低成绩号课程的最低成绩要高要高嵌套查询嵌套查询 例例, 查询其他系中比信息系任意一个查询其他系中比信息系任意一个(其其中某一个中某一个)学生年龄小的学生姓名和年龄学生年龄小的学生姓名和年龄select sname,sage from student where sage any (select sage from student

25、 where sdept= is) and sdept is ; 嵌套查询嵌套查询(3)all谓词的用法谓词的用法意义意义:where表达式的值与子查询结果集表达式的值与子查询结果集合中每一个值比较的结果为真合中每一个值比较的结果为真,整个条件整个条件为真为真. 和和all配合使用的比较符有配合使用的比较符有: (!=), =, , =, =all (select grade from sc where cno=2)嵌套查询嵌套查询 any和和all谓词有时可以用集函数实现谓词有时可以用集函数实现 any与与all与集函数的对应关系与集函数的对应关系 = 或或!= =any in - maxmin= minall - not in minmax= max嵌套查询嵌套查询例例 ：用集函数实现：用集函数实现any例子例子sage (select max(sage) from student where sdept= is); select sname,sage fr