12_第07章 数据库对象管理.ppt

第7章数据库对象管理 表的管理 查看表查看表结构创建表创建约束修改表增加字段 改字段名 改字段类型和长度 增加约束 删除约束 设置字段不可用 删除字段删除表 Review 第7章数据库对象管理 Review Createtable schema table name columndatatype default expression column constraint table constraint tablespacetablespace name 约束类型 Notnull只能定义在列级UniqueCheckPrimarykeyForeignkey CONSTRAINTemp dept fkFOREIGNKEY department id REFERENCESdepartments department id 第7章数据库对象管理 Review 修改表Altertabletable nameAddcolumndatatype Modify columndatatype Drop unused column column name1 column name1 renamecolumncolumn nametonewname setunused column name1 column name1 Add CONSTRAINTconstraint name Constraint type col1 col2 condition 添加和删除NOTNULL约束只能用modify子句 第7章数据库对象管理 删除约束 ALTERTABLEStudentsDropCONSTRAINTCHK SSEX ALTERTABLECoursesDropUNIQUE Cname ALTERTABLECoursesDropPrimaryKeyCascade ALTERTABLEStudentsModifySageNULL 第7章数据库对象管理 本章主要内容 7 1表管理7 2数据查询7 3视图管理7 4索引管理7 5序列管理 第7章数据库对象管理 7 2数据查询 SELECT语句的基本应用定义显示标题设置查询条件对查询结果排序使用统计函数连接查询 第7章数据库对象管理 SELECT语句的基本应用 SELECT语句的基本语法结构如下 SELECT DISTINCT 子句 INTO子句 FROM子句 WHERE子句 GROUPBY子句 HAVING子句 ORDERBY子句 第7章数据库对象管理 SELECT语句的基本应用 SELECT语句中各子句的说明 第7章数据库对象管理 SELECT语句执行环境 iSQL PLUS SELECT语句 查询结果 第7章数据库对象管理 SELECT语句执行环境 SQL PLUS SELECT语句 查询结果 第7章数据库对象管理 定义显示标题 在SELECT语句中设置列标题 第7章数据库对象管理 SELECT查询 单表查询多表连接查询子查询组合查询 第7章数据库对象管理 第7章数据库对象管理 SELECT查询 单表查询 单表查询指的是在一个源表中查找所需的数据 因此 单表查询时 FROM子句中的只需要给出一个源表表名 1 SELECT子句 1 选择表中若干列在SELECT子句的中指定整个查询结果表中出现的若干个列名 各列名之间用逗号分隔 例 查询全体学生的学号与姓名SELECTSno SnameFROMStudents 第7章数据库对象管理 2 选择表中所有列可以在中指定表中所有列的列名 也可以用 来代替 或用 代表指定表的所有列 例 查询全体学生的学号 姓名 性别 年龄 所在系 SELECTSno Sname Sname Ssex Sage SdeptFROMStudents等价于 SELECT FROMStudents SELECT查询 单表查询 第7章数据库对象管理 3 使用表达式表达式可以是列名 常量 函数 或用列名 常量 函数等经过 加 减 乘 除 等组成的公式 例 查询全体学生的选课情况 即学号 课程号 成绩 对成绩值都加5 SELECTSno Cno Grade 5FROMEnrollment 注意 结果中表达式列Grade 5无列名 SELECT查询 单表查询 第7章数据库对象管理 4 设置列的别名所谓别名 就是另一个名字 主要是为了方便阅读 设置列别名的方法有 原列名 AS 列别名 例 查询全体学生的学号 姓名 并为原来的英文列名设置中文别名 以下两种方法等价 SELECTSno学号 Sname姓名FROMStudentsSELECTSnoAS学号 SnameAS姓名FROMStudents SELECT查询 单表查询 第7章数据库对象管理 例 查询全体学生的选课情况 其成绩列值都加5 并为各列设置中文的别名 SELECTSno学号 Cno课程号 Grade 5成绩FROMEnrollment结果为 SELECT查询 单表查询 第7章数据库对象管理 5 使用DISTINCT消除结果表中完全重复的行 例 显示所有选课学生的学号 SELECTSno学号FROMEnrollment 例 显示所有选课学生的学号 并去掉重复行 SELECTDISTINCTSno学号FROMEnrollment SELECT查询 单表查询 第7章数据库对象管理 2 FROM子句 单表查询中 源表只有一个 因此 FROM子句FROM例如 要查找学生有关的信息 用到Students表 则FROM子句为 FROMStudents例如 要查找课程有关的信息 用到Courses表 则FROM子句为 FROMCourses例如 要查找选课有关的信息 用到Enrollment表 则FROM子句为 FROMEnrollment SELECT查询 单表查询 第7章数据库对象管理 3 WHERE子句 WHERE查询体条件中没有多表连接条件 只有一个表的行筛选条件 中常用的运算符 比较运算符和逻辑运算符 比较运算符用于比较两个数值之间的大小是否相等 常用的比较运算符有 等于 大于 大于等于 不等于 SELECT查询 单表查询 第7章数据库对象管理 逻辑运算符主要有 范围比较运算符 BETWEEN AND NOTBETWEEN AND集合比较运算符 IN NOTIN字符匹配运算符 LIKE NOTLIKE空值比较运算符 ISNULL ISNOTNULL条件连接运算符 AND OR NOT下面举例说明 第7章数据库对象管理 1 基于比较运算符的查询SELECT FROMEnrollmentWHEREGrade 80说明 可以将其他比较运算符号代替大于符号 执行相应的查询 分析结果 从而学会所有比较运算符号的使用方法 例 查询数学系全体学生的学号 姓名 SELECTSno SnameFROMStudentsWHERESdept Math SELECT查询 单表查询 第7章数据库对象管理 2 基于BETWEEN AND的查询基本形式 列名BETWEEN下限值AND上限值等价于 列名 下限值AND列名 上限值列名可以是表达式列的别名 BETWEEN AND 一般用于数值型范围的比较 表示当列值在指定的下限值和上限值范围内时 条件为TRUE 否则 为FALSE NOTBETWEEN AND 与BETWEEN AND 正好相反 表示列值不在指定的下限值和上限值范围内时 条件为TRUE 否则为FALSE 注意 列名类型要与下限值或上限值的类型一致 SELECT查询 单表查询 第7章数据库对象管理 例 查询学生选课成绩在80 90分之间的学生学号 课程号 成绩 SELECT FROMEnrollmentWHEREGradeBETWEEN80AND90SELECT FROMEnrollmentWHEREGrade 80ANDGrade30 SELECT查询 单表查询 第7章数据库对象管理 3 基于IN的查询IN用于测试一个列值是否与常量表中的任何一个值相等 IN条件表示格式为 列名IN 常量1 常量2 常量n 当列值与IN中的任一常量值相等时 则条件为TRUE 否则为FALSE NOTIN与IN的含义正好相反 当列值与IN中的任一常量值都不相等时 则结果为TRUE 否则为FALSE SELECT查询 单表查询 第7章数据库对象管理 例 查询数学系 计算机系 艺术系学生的学号 姓名 SELECTSno SnameFROMStudentsWHERESdeptIN Math Computer Art 此句等价于 SELECTSno SnameFROMStudentsWHERESdept Math ORSdept Computer ORSdept Art SELECT查询 单表查询 第7章数据库对象管理 例 查询既不是数学系 计算机系 也不是艺术系学生的学号 姓名 SELECTSno SnameFROMStudentsWHERESdeptNOTIN Math Computer Art 此句等价于 SELECTSno SnameFROMStudentsWHERESdept Math ANDSdept Computer ANDSdept Art SELECT查询 单表查询 第7章数据库对象管理 4 基于LIKE的查询LIKE用于测试一个字符串是否与给定的模式匹配 所谓模式是一种特殊的字符串 其中可以包含普通字符 也可以包含特殊意义的字符 通常叫通配符 LIKE运算符的一般形式为 列名LIKE模式串中可包含如下通配符 它们的含义是 匹配任意一个字符 注意 在这里一个汉字或一个全角字符也算一个字符 如 u 表示第二个字符为u或U 第一 第三个字符为任意字符的字符串 匹配任意0个或多个字符 如 S 表示以S开头的字符串 SELECT查询 单表查询 第7章数据库对象管理 可以用LIKE来实现模糊查询 例 查找姓名的第二个字符是u并且只有三个字符的学生的学号 姓名 SELECTSno SnameFROMStudentsWHERESnameLIKE u 例 查找姓名以S开头的所有学生的学号 姓名 SELECTSno SnameFROMStudentsWHERESnameLIKE S SELECT查询 单表查询 第7章数据库对象管理 5 基于NULL空值的查询空值是尚未确定或不确定的值 判断某列值是否为NULL值 不能使用比较运算符等于和不等于 而只能使用专门的判断空值的子句 如下 判断列值为空的语句格式为 列名ISNULL判断列值不为空的语句格式为 列名ISNOTNULL SELECT查询 单表查询 第7章数据库对象管理 例 查询无考试成绩的学生的学号和相应的课程号 SELECTSno CnoFROMEnrollmentWHEREGradeISNULL不等价于 SELECTSno CnoFROMEnrollmentWHEREGrade 0 例 查询有考试成绩 即成绩不为空值 的学生的学号 课程号 SELECTSno CnoFROMEnrollmentWHEREGradeISNOTNULL SELECT查询 单表查询 第7章数据库对象管理 6 基于多个条件的查询可以使用AND OR逻辑谓词来连接多个条件 构成一个复杂的查询条件 使用格式为 ANDAND 或OROR 用AND连接的所有的条件都为TRUE时 整个查询条件才为TRUE 用OR连接的条件中 只要其中任一个条件为TRUE 整个查询条件就为TRUE SELECT查询 单表查询 第7章数据库对象管理 例 查询计算机系年龄在18岁以上的学生学号 姓名SELECTSno SnameFROMStudentsWHERESdept Computer ANDSage 18 例 求选修了C1课程或C2课程的学生学号 成绩 SELECTSno GradeFROMEnrollmentWHERECno C1 ORCno C2 第7章数据库对象管理 7 使用统计函数的查询统计函数也称为集合函数或聚集函数 其作用是对一组值进行计算并返回一个值 SELECT查询 单表查询 第7章数据库对象管理 例 求学生的总人数SELECTCOUNT AS学生的总人数FROMStudents 例 求选修了课程的学生人数SELECTCOUNT DISTINCTSno AS选修课程的总人数FROMEnrollment 例 求选修了C1课程的学生的平均成绩 SELECTAVG Grade AS平均成绩FROMEnrollmentWHERECno C1 第7章数据库对象管理 例 求20010102号学生的考试总成绩 SELECTSUM Grade AS考试总成绩FROMEnrollmentWHERESno 20010102 例 选修了C1课程的学生的最高分和最低分SELECTMAX Grade AS最高分 MIN Grade AS最低分FROMEnrollmentWHERECno C1 第7章数据库对象管理 4 GROUPBY子句有时我们需要把FROM WHERE子句产生的表按某种原则分成若干组 然后再对每个组进行统计 GROUPBY子句在WHERE子句后边 一般形式为 GROUPBY n 其中是分组的依据 分组原则是的列值相同 就为同一组 当有多个时 则先按第一个列值分组 然后对每一组再按第二个列值进行分组 依此类推 SELECT查询 单表查询 第7章数据库对象管理 例 求选修每门课程的学生人数 SELECTCno课程号 COUNT Sno 选修人数FROMEnrollmentGROUPBYCno 例 输出每个学生的学号和他 她的各门课程的总成绩 SELECTSno学号 Sum grade 总成绩FROMEnrollmentGROUPBYSno 第7章数据库对象管理 5 HAVING子句HAVING子句指定GROUPBY生成的组表的选择条件 它的一般形式为 HAVINGHAVING子句在GROUPBY子句之后 并且必须与GROUPBY子句一起使用 例 求选修课程大于等于2门课的学生的学号 平均成绩 选修的门数 SELECTSno AVG Grade 平均成绩 COUNT AS选修门数FROMEnrollmentGROUPBYSnoHAVINGCOUNT 2 SELECT查询 单表查询 第7章数据库对象管理 6 ORDERBY子句指定整个SELECT语句的输出结果中记录的排序依据 ORDERBY排序子句的格式为 ORDERBY ASC DESC n 其中指定排序的依据 ASC表示按列值升序方式排序 DESC表示按列值降序方式排序 如果没有指定排序方式 则默认的排序方式为升序排序 在ORDERBY子句中 可以指定多个用逗号分隔的列名 这些列出现的顺序决定了查询结果排序的顺序 当指定多个列时 首先按最前面的列进行排序 如果排序后存在两个或两个以上列值相同的行 则对这些值相同的行再依据第二列进行排序 依此类推 SELECT查询 单表查询 第7章数据库对象管理 例 查询所有学生的行 并按学生的年龄值从小到大排序 SELECT FROMStudentsORDERBYSage 例 查询选修了C1课程的学生的学号和成绩 查询结果按成绩降序排列 SELECTSno GradeFROMEnrollmentWHERECno C1 ORDERBYGradeDESC 第7章数据库对象管理 例 查询全体学生信息 查询结果按所在系的系名升序排列 同一系的学生按年龄降序排列 SELECT FROMStudentsORDERBYSdeptASC SageDESC 例 求选修课程大于等于2门课的学生的学号 平均成绩和选课门数 并按平均成绩降序排列 SELECTSno学号 AVG Grade AS平均成绩 COUNT AS修课门数FROMEnrollmentGROUPBYSnoHAVINGCOUNT 2ORDERBYAVG Grade DESC 第7章数据库对象管理 多表查询指的是从多个源表中检索数据 因此 多表查询时 FROM子句中的要给出所有源表表名 各个表名之间要用逗号分隔 1 多表查询的FROM子句格式FROM例如 若一个查询用到三个表 表名分别为Students Enrollment Courses 则FROM子句为 FROMStudents Enrollment Courses SELECT查询 多表连接查询 第7章数据库对象管理 2 多表查询中的SELECT子句与单表查询的SELECT子句功能基本相同 也是用来指定查询结果表中包含的列名 不同的是 如果多个表中有相同的列名 则需要用 来限定列是那个表的列 例如 Students表和Enrollment表中都有Sno列 为了在结果表中包含Students表的Sno列 则要用Students Sno表示 还可用 表示指定的表中的所有列 例如Students 表示Students表的所有列 SELECT查询 多表连接查询 第7章数据库对象管理 3 多表查询中的GROUPBY HAVING ORDERBY子句与单表查询中的用法相同 不同的是 如果列名有重复 则要用 来限定列是那个表的列 SELECT查询 多表连接查询 第7章数据库对象管理 4 多表查询中的WHERE子句与单表查询中的用法差别较大 多表查询中往往要有多表的连接条件 当然还有表的一个或多个行选择条件 两者用AND操作符组合 这里着重介绍多表的连接条件 按连接条件的不同 连接分为 内连接 外连接 外连接又分为左外连接 右外连接 SELECT查询 多表连接查询 第7章数据库对象管理 1 内连接等值连接与自然连接等值连接 即根据两个表的对应列值相等的原则进行连接 连接条件的形式往往是 主键 外键 即按一个表的主键值与另一个表的外键值相同的原则进行连接 常用的等值连接条件形式 SELECT查询 多表连接查询 第7章数据库对象管理 例 查询每个学生的基本信息以及他 她选课的情况 SELECTStudents Enrollment FROMStudents EnrollmentWHEREStudents Sno Enrollment Sno 第7章数据库对象管理 上述结果表中含有Students和Enrollment表的所有列 存在一个重复的列Sno 这说明是等值连接 如果要去掉重复列 就要用SELECT子句指定结果表中包含的列名 这样就成为自然连接 如下 SELECTStudents Sno Sname Ssex Sage Sdept Cno GradeFROMStudents EnrollmentWHEREStudents Sno Enrollment Sno 第7章数据库对象管理 例 查询每个学生的学号 姓名 选修的课程名 成绩 SELECTStudents Sno Sname Cname GradeFROMStudents Courses EnrollmentWHEREStudents Sno Enrollment SnoANDCourses Cno Enrollment Cno 第7章数据库对象管理 例 查询选修了C2且成绩大于90分的学生的学号 姓名 成绩 SELECTStudents Sno Sname GradeFROMStudents EnrollmentWHEREStudents Sno Enrollment SnoANDCno C2 ANDGrade 90这里 用AND将一个连接条件和两个行选择条件组合成为查询条件 第7章数据库对象管理 例 求计算机系选修课程大于等于2门课的学生的学号 姓名 平均成绩 并按平均成绩从高到低排序 SELECTStudents Sno Sname AVG Grade AverageFROMStudents EnrollmentWHEREStudents Sno Enrollment SnoANDSdept Computer GROUPBYStudents Sno SnameHAVINGCOUNT 2ORDERBYAVG Grade DESC 第7章数据库对象管理 2 自身连接自身连接是一种特殊的内连接 可以看作