《数据库基本sel》PPT课件.ppt

熟悉SELECT语法格式掌握WHERE ORDERBY GROUPBY HAVING子句掌握基本的多表查询掌握内连接 外连接 交叉连接和联合查询了解自连接的使用掌握多行和单值子查询的使用了解嵌套子查询的使用 第4章获取数据 4 1基本SELECT查询语句 SELECT语句的执行窗口 使用SQL编辑器 数据查询 假定已建好Students Courses Enrollment三个表 并已向各个表添加了数据 见下页表 讨论一下数据查询问题 数据查询用来描述怎样从数据库中获取所需的数据 数据查询用到的语句就是查询语句 即SELECT语句 它是数据库操作中最基本 最重要的语句之一 SELECT语句的功能就是从一个或多个表或视图 一种虚拟表 中查到满足条件的数据 它的数据源是表或视图 而结果是另一个表 查询语句基本结构 语法 SELECTFROM WHERE GROUPBY HAVING ORDERBY SELECT语句说明SELECT语句包括 SELECT子句 FROM子句 可选的WHERE子句 GROUPBY子句 HAVING子句和ORDERBY子句 SELECT 称为SELECT子句 用于指定整个查询结果表中包含的列 假定已经执行完FROM WHERE GROUPBY HAVING子句 从概念上来说得到了一个表 若将该表称为T 从T表中选择SELECT子句指定的目标列组成表就为整个查询的结果表 2 FROM 称为FROM子句 用于指定整个查询语句用到的一个或多个基本表或视图 是整个查询语句的数据来源 通常称为数据源表 3 WHERE 称为WHERE子句 用于指定多个数据源表的连接条件和单个源表中行的筛选条件或选择条件 如果只有一个源表 则没有表间的连接条件 只有行的筛选条件 4 GROUPBY 称为GROUPBY子句 假定已经执行完FROM WHERE子句 则从概念上来说得到了一个表 若将该表称为T1表 则GROUPBY用于指定T1表按哪些列 称为分组列 进行分组 所有这些行组成一个表 不妨把它称为T2表 T2表实际上是一个组表 5 HAVING 称为HAVING子句 与GROUPBY子句一起使用 用于指定组表T2表的选择条件 即选择T2表中满足的行 组成一个表就是SELECT子句中提到的表T 6 ORDERBY 称为ORDERBY子句 若有ORDERBY子句 则用于指定查询结果表T中按指定列进行升序或降序排序 得到整个查询的结果表 SELECT语句包含了关系代数中的选择 投影 连接 笛卡儿积等运算 下面首先学习单表查询SELECT语句中的各个子句 4 1 1单表查询 单表查询指的是在一个源表中查找所需的数据 因此 单表查询时 FROM子句中的只要给出一个源表表名 1 SELECT子句 1 选择表中若干列在SELECT子句的中指定整个查询结果表中出现的若干个列名 各列名之间用逗号分隔 例4 1 查询全体学生的学号与姓名SELECTSno SnameFROMStudents 例4 2 查询数据库studentcourse学生基本信息表s中学生的学号 姓名 所在系 SELECT学号 姓名 系FROMs 2 选择表中所有列 无条件查询 可以在中指定表中所有列的列名 也可以用 来代替 或用 代表指定表的所有列 代表所有列 输出显示列的次序由SELECT指定 例4 1 查询所有学生可选的课程信息 SELECT FROMc 表示选择当前表的所有属性列 例4 2 查询全体学生的学号 姓名 性别 年龄 所在系SELECTSno Sname Ssex Sage SdeptFROMStudents等价于 SELECT FROMStudents 3 查询的数据可以使用表达式表达式可以是列名 常量 函数 或用列名 常量 函数等经过 加 减 乘 除 等组成的公式 例4 3 3 查询全体学生的选课情况 即学号 课程号 成绩 对成绩值都加5 SELECTSno Cno Grade 5FROMEnrollment 注意 结果中表达式列Grade 5无列名 实际上所有表达式列都没有列名 4 设置列的别名所谓别名 就是另一个名字 有时需要显示特殊的输出列名 主要是为了方便阅读 例如 select职称 高校教师职称 from教师 设置列别名的方法有 原列名 AS 列别名或者 列别名 原列名 例4 3 4 查询全体学生的学号 姓名 并为原来的英文列名设置中文别名 以下三种方法等价 SELECTSno 学号 Sname 姓名 FROMStudentsSELECTSnoAS 学号 SnameAS 姓名 FROMStudentsSELECT 学号 Sno 姓名 SnameFROMStudents 例4 3 5 查询全体学生的选课情况 其成绩列值都加5 并为各列设置中文的别名SELECTSno 学号 Cno 课程号 Grade 5 成绩 FROMEnrollment结果为 例4 4 查询表s的学号和姓名信息 其中设置学号的别名为xh 设置姓名的别名为xmSELECT学号ASxh 姓名ASxmFROMS或SELECT学号AS xh 姓名AS xm FROMS或SELECT学号AS xh 姓名AS xm FROMS或SELECT xh 学号 xm 姓名FROMS 5 使用DISTINCT消除结果表中完全重复的行 例如 SELECT职称from教师职称副教授讲师教授教授selectdistinct职称from教师 例3 3 6 显示所有选课学生的学号 SELECTSno 学号 FROMEnrollment 例4 3 7 显示所有选课学生的学号 并去掉重复行 SELECTDISTINCTSno 学号 FROMEnrollment与DISTINCT相反的是ALL ALL表示保留结果表中的重复行 默认情况下是ALL 表示保留重复行 2 FROM子句 单表查询中 源表只有一个 FROM例如 要查找学生有关的信息 用到Students表 则FROM子句为 FROMStudents例如 要查找课程有关的信息 用到Courses表 则FROM子句为 FROMCourses例如 要查找选课有关的信息 用到Enrollment表 则FROM子句为 FROMEnrollment 3 通过TOP子句限制结果集输出的行数 1 命令格式SELECT TOPn PERCENT 属性列表 2 功能n是一个正整数 如果指定PERCENT关键字 则返回前百分之n条记录 n必须介于0到100之间的正整数 比如 SELECTTOP3 表示输出查询结果集的前3行 SELECTTOP3PERCENT 表示输出查询结果集的前3 记录行 例如 selectdistincttop8籍贯from学生信息 例4 8 将课程成绩70分以下的同学的成绩提高5分 然后把结果存储到 另存 新数据表 最新成绩 中 SELECT成绩 5AS最新成绩INTO最新成绩FROMscWHERE成绩 70 4 INTO子句的使用 4 1 2WHERE子句 WHERE 只获得满足条件的数据 查询体条件中没有多表连接条件 只有一个表的行筛选条件 中常用的运算符 比较运算符和逻辑运算符 P101表4 1 常用的比较运算符有 等于 大于 大于等于 不等于 不大于 不小于 共9种 范围比较运算符 BETWEEN AND NOTBETWEEN AND集合比较运算符 IN NOTIN字符匹配运算符 LIKE NOTLIKE空值比较运算符 ISNULL ISNOTNULL条件连接运算符 AND OR NOT 基于其它运算符的查询 例4 10 在基本表S中检索1978 01 06出生的学生情况 SELECT FROMsWHERE出生日期 1978 01 06 例4 11 在基本表S中检索1978年及以后出生的学生情况 SELECT FROMsWHEREyear 出生日期 1978或SELECT FROMsWHEREyear 出生日期 1978 例4 14 列出表S中管理信息系年龄小于28岁的学生的学号和出生年份情况 SELECT学号 year 出生日期 as出生年份FROMsWHERE系 管理信息系 and year getdate year 出生日期 28 基于BETWEEN AND的查询基本形式 列名BETWEEN下限值AND上限值等价于 列名 下限值AND列名 上限值列名可以是表达式列的别名 BETWEEN AND 一般用于数值型范围的比较 表示当列值在指定的下限值和上限值范围内时 条件为TRUE 否则 为FALSE NOTBETWEEN AND 与BETWEEN AND 正好相反 表示列值不在指定的下限值和上限值范围内时 条件为TRUE 否则为FALSE 注意 列名类型要与下限值或上限值的类型一致 例4 3 10 查询学生选课成绩在80 90分之间的学生学号 课程号 成绩 SELECT FROMEnrollmentWHEREGradeBETWEEN80AND90SELECT FROMEnrollmentWHEREGrade 80ANDGrade30 基于IN的查询IN用于测试一个列值是否与常量表中的任何一个值相等 IN条件表示格式为 列名IN 常量1 常量2 常量n 当列值与IN中的任一常量值相等时 则条件为TRUE 否则为FALSE NOTIN与IN的含义正好相反 当列值与IN中的任一常量值都不相等时 则结果为TRUE 否则为FALSE 例4 3 12 查询数学系 计算机系 艺术系学生的学号 姓名 SELECTSno SnameFROMStudentsWHERESdeptIN Math Computer Art 此句等价于 SELECTSno SnameFROMStudentsWHERESdept Math ORSdept Computer ORSdept Art 例4 3 13 查询既不是数学系 计算机系 也不是艺术系学生的学号 姓名 SELECTSno SnameFROMStudentsWHERESdeptNOTIN Math Computer Art 此句等价于 SELECTSno SnameFROMStudentsWHERESdept Math ANDSdept Computer ANDSdept Art 基于LIKE的查询LIKE用于测试一个字符串是否与给定的模式匹配 所谓模式是一种特殊的字符串 其中可以包含普通字符 也可以包含特殊意义的字符 通常叫通配符 一般形式为 列名LIKE模式串中可包含四种通配符 匹配任意一个字符 注意 在这里一个汉字或一个全角字符也算一个字符 如 u 表示第二个字符为u或U 匹配任意0个或多个字符 如 S 表示以S开头的字符串 匹配 中的任意一个字符 如 SDJ 不匹配 中的任意一个字符 如 SDJ 可以用LIKE来实现模糊查询 例4 3 14 查找姓名的第二个字符是u并且只有三个字符的学生的学号 姓名 SELECTSno SnameFROMStudentsWHERESnameLIKE u 例4 3 15 查找姓名以S开头的所有学生的学号 姓名 SELECTSno SnameFROMStudentsWHERESnameLIKE S 例4 3 16 查找姓名以S D或J开头的所有学生的学号 姓名 SELECTSno SnameFROMStudentsWHERESnameLIKE SDJ 例4 3 17 查找姓名不是以S D或J开头的所有学生的学号 姓名 SELECTSno SnameFROMStudentsWHERESnameLIKE SDJ 等价于 SELECTSno SnameFROMStudentsWHERESnameNOTLIKE SDJ 例4 15 在表S中查询管理信息系学生的姓名 SELECT姓名as姓名 系as所在院系FROMSWHERE系LIKE 管理信息系 例4 16 查询陈老师所教的课程信息 SELECT FROMcWHERE教师LIKE 陈 例4 17 在基本表S中检索出生日期包含13的学生情况 SELECT FROMsWHERE出生日期like 13 例4 18 如果要找出其姓名中不含有 红 的学生情况 则在LIKE前增加NOT SELECT FROMsWHERE姓名NOTLIKE 红 例4 19 在基本表S中检索以 王 姓打头 名字由1个汉字组成的学生姓名 SELECT姓名FROMsWHERE姓名LIKE 王 空值是尚未确定或不确定的值 判断某列值是否为NULL值 不能使用比较运算符等于和不等于 而只能使用专门的判断空值的子句 如下 判断列值为空的语句格式为 列名ISNULL判断列值不为空的语句格式为 列名ISNOTNULL 基于NULL空值的查询 例4 29 查询缺少成绩的学生的学号和相应的课程号 某些学生选修课程后没有参加考试 所以有选课记录 但没有考试成绩 SELECT学号 课程号FROMscWHERE成绩ISNULL 例4 30 查询有选修成绩的学生的学号和相应的课程号 SELECT学号 课程号FROMscWHERE成绩ISNOTNULL 例4 18 查询无考试成绩的学生的学号和相应的课程号 SELECTSno CnoFROMEnrollmentWHEREGradeISNULL不等价于 SELECTSno CnoFROMEnrollmentWHEREGrade 0 例4 19 查询有考试成绩 即成绩不为空值 的学生的学号 课程号 SELECTSno CnoFROMEnrollmentWHEREGradeISNOTNULL 例4 21 在基本表S中检索1975年出生或者姓王的学生情况 SELECT FROMsWHEREyear 出生日期 1975OR姓名LIKE 王 例4 22 在基本表S中检索在1970年到1980年之间出生的学生情况 SELECT FROMsWHEREyear 出生日期 1970andyear 出生日期 1980 基于多个条件的查询 例4 23 在基本表S中检索不在管理信息系的学生情况 SELECT FROMsWHEREnot 系 管理信息系 或SELECT FROMsWHERE系 管理信息系 使用统计函数的查询统计函数也称为集合函数或聚集函数 其作用是对一组值进行计算并返回一个值 例4 22 求学生的总人数SELECTCOUNT AS 学生的总人数 FROMStudents 例4 23 求选修了课程的学生人数SELECTCOUNT DISTINCTSno AS 选修课程的总人数 FROMEnrollment 例4 24 求选修了C1课程的学生的平均成绩 SELECTAVG Grade AS 平均成绩 FROMEnrollmentWHERECno C1 例4 25 求20010102号学生的考试总成绩之和 SELECTSUM Grade AS 20010102考试总成绩 FROMEnrollmentWHERESno 20010102 例4 26 选修了C1课程的学生的最高分和最低分SELECTMAX Grade AS 最高分 MIN Grade AS 最低分 FROMEnrollmentWHERECno C1 指定整个SELECT语句的输出结果中记录的排序依据 ORDERBY ASC DESC n ASC表示按列值升序方式排序 DESC表示按列值降序方式排序 如果没有指定排序方式 则默认的排序方式为升序排序 在ORDERBY子句中 可以指定多个用逗号分隔的列名 这些列出现的顺序决定了查询结果排序的顺序 当指定多个列时 首先按最前面的列进行排序 如果排序后存在两个或两个以上列值相同的行 则对这些值相同的行再依据第二列进行排序 依此类推 4 1 3ORDERBY子句 例4 31 将所有女生按年龄升序排序 SELECT FROMsWHERE性别 女 ORDERBY出生日期ASC 例4 32 将SC数据表中的信息按学生学号升序 课程号降序排列 例4 30 查询所有学生的行 并按学生的年龄值从小到大排序 SELECT FROMStudentsORDERBYSage 例4 31 查询选修了C1课程的学生的学号和成绩 查询结果按成绩降序排列 SELECTSno GradeFROMEnrollmentWHERECno C1 ORDERBYGradeDESC 例4 32 查询全体学生信息 查询结果按所在系的系名升序排列 同一系的学生按年龄降序排列 SELECT FROMStudentsORDERBYSdeptASC SageDESC 例4 33 求选修课程大于等于2门课的学生的学号 平均成绩和选课门数 并按平均成绩降序排列 SELECTSnoAS 学号 AVG Grade AS 平均成绩 COUNT AS 修课门数 FROMEnrollmentGROUPBYSnoHAVINGCOUNT 2ORDERBYAVG Grade DESC 例 在SC表中 查询学号为1的同学获得最高成绩的课程号 例 在SC表中 找出选修了课程号为2的课程 而且课程成绩最高的前两位同学 4 1 4GROUPBY子句有时我们需要把FROM WHERE子句产生的表按某种原则分成若干组 然后再对每个组进行统计 一组形成一行 最后把所有这些行组成一个表 称为组表 GROUPBY子句在WHERE子句后边 一般形式为 GROUPBY n 其中是分组的依据 分组原则是的列值相同 就为同一组 当有多个时 则先按第一个列值分组 然后对每一组再按第二个列值进行分组 依此类推 例4 27 求选修每门课程的学生人数 SELECTCnoAS 课程号 COUNT Sno AS 选修人数 FROMEnrollmentGROUPBYCno 例4 28 输出每个学生的学号和他 她的各门课程的总成绩 SELECTSno 学号 Sum grade 总成绩 FROMEnrollmentGROUPBYSno 注意 包含GROUPBY子句的查询语句中 SELECT子句指定的列名 要么是统计函数 如上例中的COUNT Sno 要么是包含在GROUPBY子句中的列名 如上例中的Cno 否则将出错 如下列语句是错误的 SELECTSnoAS 学号 CnoAS 课程号 COUNT Sno AS 选修人数 FROMEnrollmentGROUPBYCno因为SELECT子句中的Sno列 既不是统计函数 也不是GROUPBY子句中的列名 4 1 5HAVING子句HAVING子句指定GROUPBY生成的组表的选择条件 在GROUPBY子句之后 并且必须与GROUPBY子句一起使用 一般形式为 HAVING例4 29 求选修课程大于等于2门课的学生的学号 平均成绩