SQL课件--七数据查询.ppt

第七章数据查询目标单表查询按列查询、查询经过计算的值、消除取值重复的行、条件查询、对查询结果排序、使用集函数、对查询结果分组连接查询等值与非等值连接查询、自身连接、复合条件连接嵌套查询带有IN谓词的子句查询、带有比较运算符的子查询集合查询本章重点:数据查询--select本章难点:1.连接查询2.嵌套查询重点和难点查询的一般格式为：select[all|distinct]目标列表达式1[,目标列表达式2]…from表名或视图名1[,表名或视图名2]…[where条件表达式][groupby列名1[having条件表达式]][orderby列名2[asc|desc]]Select子句指定要显示的列,from子句列出查询的对象表SQL数据查询基本结构基本结构:selectA1,A2,…,Anfromr1,r2,…,rmwhereP∏A1,A2,…,An(p(r1r2…rm))单表查询一．按列查询１．查询指定列例1：查询所有学生的学号和姓名select学号,姓名from学生表例2：查询所有学生的姓名，学号和专业select姓名,学号,专业from学生表２．查询全部列例3：查询全部学生的所有信息select*from学生表select学号,姓名,性别,年龄,专业from学生表“*”-------表示“所有的属性”目标列可以为列名，*，算术表达式，聚集函数例：查询全部学生的姓名和出生年份select姓名,2000-年龄from学生表select姓名,2000-年龄’YearofBirth’from学生表查询经过计算的值select姓名,2000-年龄’YearofBirth’,lower(专业)from学生表select姓名name,2000-年龄’YearofBirth’,lower(专业)departmentfrom学生表假如SC表有表1中的数据例1：select学号from学生课程表-----语句执行的结果为表2。例2：selectdistinct学号from学生课程表-----语句执行的结果为表3。SnoCnoGrade950011929500128595002298Sno950019500195002Sno9500195002表1表2表3缺省为保留重复元组，也可用关键字all显式指明。若要去掉重复元组，可用关键字distinct消除取值重复的行条件查询常用的查询条件查询条件谓词比较确定范围BETWEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件AND，OR比较条件查询例1：查询ceac系全体学生的名单.select姓名from学生表where专业=‘ceac’例2：查询所有年龄在20岁以下学生姓名及其年龄.select姓名,年龄from学生表where年龄20例3：查考试成绩有不及格的学生的学号.selectdistinct学号from学生课程表where成绩60Between..and…:Notbetween..and…:判断表达式的值是否在某范围内.例1:查询年龄在20至23岁之间的学生的姓名、系别、和年龄.select姓名,系别,年龄from学生表where年龄between20and23确定范围的条件查询例2：查询年龄不在20至23岁之间的学生姓名、系别和年龄.select姓名,系别,年龄from学生表where年龄notbetween20and23确定集合的条件查询谓词：IN值表,NOTIN值表判断表达式的值是否在子查询的结果中.例1：查询专业是ceac,wz或者wj学生的姓名.select姓名from学生表where专业in(‘ceac’,’wz’,’wj’)例2：求专业不是ceac,wz和wj的同学的姓名和性别.select姓名from学生表where专业notin(‘ceac’,’wz’,’wj’)字符匹配的条件查询谓词like的一般语法格式为：[not]like‘匹配串’[‘escape’换码字符]------其含义是查找指定的属性列值与匹配串相匹配的元组。------匹配串可以是一个完整的字符串，也可以含有通配符%和_。匹配规则•“%”-----匹配零个或多个字符•“＿”-----匹配任意单个字符•escape-----定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待.-----如escape’\’，定义’\’作为转义字符，则可用\%去匹配%，用\＿去匹配_.思考：用什么去匹配’\’？例：查找姓刘的学生的姓名,年龄,性别．select姓名,年龄,性别from学生表where姓名like‘刘%’例1：查询姓周的并且姓名为三个字的同学的学号和姓名．select姓名from学生表where姓名like‘周__’换码字符(转义符)的使用例1.查询课程名以”ch_”开头的所有课程的信息.select*from课程表where课程名like‘ch\_%’escape‘\’escape：定义转义字符，用它来去掉特殊字符的特定含义，使其被作为普通字符看待.在此例中，这时\后面的字符_就不再具有通配符的含义．例2.查询课程名以”sql_”开头，且倒数第3个字符为v的课程和它的详细情况。select*from课程表where课程名like‘sql\_%v__’escape‘\’涉及空值的条件查询例1：查缺少成绩的学生的学号和相应的课程号.select学号,课程号from学生选课表where成绩isnull例2：查所有有成绩的记录的学生学号和课程号select学号,课程号from学生选课表where成绩isnotnull注意事项:除is[not]null之外，空值不满足任何查找条件.如果null参与算术运算，则该算术表达式的值为null.如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null.如果null参与比较运算，则结果可视为false。在SQL-92中可看成unknown.多重条件查询例1：查找是ceac专业并且年龄在22岁以下的学生的姓名.select姓名from学生表where专业=‘ceac’and年龄22例2：查询’ceac’或‘wz’或‘wj’专业的同学的姓名和性别.select姓名,性别from学生表where专业=‘ceac’or专业=‘wz’or专业=‘wj’注意:例2语句下面语句具有相同的执行结果.select姓名,性别from学生表where学号in(‘ceac’,’wz’,’wj’)对查询结果排序OderBy子句可以按一个或多个属性列排序选择表中的若干元祖升序：ASC；降序：DESC；缺省值为升序聚合函数当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示对查询结果排序例1:求选修了3号课程的学生的学号,成绩,并以成绩的降序排列.select学号,成绩from学生选课表where课程号=‘3’orderby成绩desc注意：在空值上的排序例2:查询全体学生情况，查询结果按所在专业升序排列，对同一专业中的学生按年龄降序排列.select*from学生表orderby专业,年龄desc使用集函数主要集函数：count（[distinct|all]*）------统计元组个数count（[distinct|all]列名）------统计一列中值的个数sum（[distinct|all]列名）------计算一列值的总和avg（[distinct|all]列名）------计算一列值的平均值max（[distinct|all]列名）------求一列值中的最大值min（[distinct|all]列名）------求一列值中的最小值注意:count（*）与count（列名）的差别示例例1：查询学生总人数.selectcount(*)from学生表例2：查询选修了课程的学生人数.selectcount(distinct学号)from学生课程表例3：计算选修了1号课程的学生平均成绩.selectavg(成绩)from学生选课表where课程号=‘01’例4：查询选修了2号课程的学生最高分数.selectmax(成绩)from学生选课表where课程号=‘02’对查询结果分组分组命令:groupby列名[having条件表达式]groupby------将表中的元组按指定列上值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值.(注:分组后的集函数应作用于每一个组,既每一个组有一个函数值)Having------则对分组进行选择，只将聚集函数作用到满足条件的分组上.CnoCOUNT(Sno)123225333440例1：找出各个课程号与相应的选课人数.select课程号,count(学号)from学生课程表groupby课程号该语句对查询结果按课程号分组,所有具有相同课程号的元组为一组,然后对每一组作用集函数count计算,以求得该组的学生人数.查询的结果可能为：例2:查询选修了1门以上课程的学生的学号.select学号from学生选课表groupby学号havingcount(课程号)1例3：查询是ceac专业,并且选修了1门以上课程的学生的学号.select学号from学生选课表where学号in(select学号from学生表where专业=‘ceac’)groupby学号havingcount(课程号)1Computecompute：生成合计作为附加的汇总列出现在结果集的最后，当与BY一起使用时，COMPUTE子句在结果集内生成控制中断和小计。注意：1、由于包含COMPUTE的语句生成表并且这些表的汇总结果不存储在数据库中，因此在SELECTINTO语句中不能使用COMPUTE。2、当与BY一起使用时，必须指定排序Compute实例selecttype,price,advancefromtitlescomputesum(price),avg(advance)selecttype,price,advancefromtitlesorderbytpyecomputesum(price),avg(advance)bytype连接查询比较连接查询自身连接外连接复合条件连接比较连接查询连接查询中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为：[表名1.]列名1比较运算符[表名2.]列名2其中比较运算符主要有：=、、、=、=、!=。当连接运算为=时，称为等值连接。使用其他运算符称为非等值连接。连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但不必是相同的。连接运算中有两种特殊情况：广义笛卡尔积是不带连接谓词的连接，两个表的广义笛卡尔积即是两表中元组的交叉乘积，其连接的结果会产生一些没有意义的元组，所以这种运算实际很少使用。自然连接，若在等值连接中把目标列中重复的属性列去掉则为自然连接。示例查询每个学生及其选修课程情况.select学生表.*,学生选课表.*from学生表,学生选课表where学生表.学号=学生选课表.学号select学生表.学号,姓名,年龄,专业,课程号,成绩from学生表,学生选课表where学生表.学号=学生选课表.学号自身连接一个表与其自己进行连接选择表中的若干列需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀例：查询每一门课的间接先修课程号(先修课的先修课).selectA.课程号,B.先修课程号from课程表A,课程表BwhereA.先修课程号=B.课程号外连接外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出左外连接左外连接，LEFTOUTERJOIN，告诉DBMS生成的结果表中，除了包括匹配行外，还包括JOIN关键字（FROM子句中）左边表的不匹配行。左外连接实际上可以表示为左外连接=内连接+左边表中失配的元组，缺少的右边表中的