项目CJGL数据库的数据查询PPT课件

项目6CJGL数据库的数据查询 技能目标 在CJGL数据库中能根据按照指定的要求灵活 快速地查询相关信息 知识目标 掌握SELECT语句语法格式 掌握最基本的查询技术 掌握条件查询技术 掌握多重条件查询技术 掌握连接查询技术 掌握嵌套查询 SELECT语句的语法格式 SELECT ALL DISTINCT TOPn TOPnPERCENT INTO FROM数据源列表 n WHERE GROUPBY HAVING ORDERBY ASC DESC SELECT语句各子句的功能 1 SELECT子句 指定查询返回的列 2 INTO子句 将检索结果存储到新表或视图中 3 FROM子句 用于指定查询列所在的表和视图 4 WHERE子句 指定用于限制返回的行的搜索条件 4 GROUPBY子句 指定用来放置输出行的组 并且如果SELECT子句中包含聚合函数 则计算每组的汇总值 5 HAVING子句 指定组或聚合的搜索条件 HAVING通常与GROUPBY子句一起使用 6 ORDERBY子句 指定结果集的排序 简单查询 SELECT子句 1 查询所有的列 使用 例 从学生表中查询所有学生的信息 Select from学生表2 查询指定的列 例 从学生表中检索所有学生的学号 姓名 性别和政治面貌 Select学号 姓名 性别 政治面貌from学生表3 使用TOP关键字 例 检索学生表中前3位学生的学号 姓名和性别 Selecttop3学号 姓名 性别from学生表4 使用DISTINCT关键字 例 从学生表中查询所有学生的班级信息 并消去重复记录 Selectdistinct班级编号from学生表 简单查询 SELECT子句 5 更改列标题方法 采用 列标题 列名 的格式采用 列名列标题 的格式采用 列名as列标题 的格式 例 查询每个学生的学号和姓名 并在标题上显示 学生学号 和 学生姓名 Select学号as学生学号 姓名学生姓名from学生表 简单查询 SELECT子句 6 使用计算列 例 查询成绩表中所有学生成绩在提高10 后的信息 将提高后的成绩列标题为 提高后成绩 Select学号 成绩原成绩 成绩 1 1as提高后成绩from成绩表7 使用聚合函数sum average max min 例 统计学生表有多少名学生 Selectcount from学生表 简单查询 INTO子句 INTO子句用于将查询的结果插入新表中 其语法格式如下 INTO新表名 例 使用INTO子句创建一个包含学号和姓名 并命名为new student的新表 USECJGLGOSELECT学号 姓名INTOnew studentFROM学生表GO 简单查询 WHERE子句 使用WHERE子句的目的是为了从表格的数据集中过滤出符合条件的行 其语法格式如下 SELECT INTO FROM WHERE 简单查询 WHERE子句 1 比较运算符 2 逻辑运算符 and or not3 字符串运算符 like notlike abc def Like后要跟通配符 包括 表示若干字符 表示单个字符 表示字符范围如 1 课程名包含 数据库 课程名称like 数据库 2 课程名倒数第2个字为 数 课程名称like 数 3 姓名的第一个字在a d之间 姓名like a d 4 所有不姓李的学生 姓名like 李 简单查询 WHERE子句 4 列表运算符 in notin如 学号为001 003 005的学生信息 学号in 001 003 005 5 范围运算符 not betweenand如 成绩在0 100之间 成绩between0and1006 空值运算符 null notnull 简单查询 WHERE子句 实例 1 查询在 1989 1 1 之后出生的学生信息 列出所有字段 Select from学生表where出生日期 1989 1 1 2 查询政治面貌为党员 族别为少数民族的学生信息 列出所有字段 Select from学生表where政治面貌 党员 and族别 汉族 3 查询所有姓 李 和姓 张 的学生信息 列出所有字段 Select from学生表where姓名like 李 张 简单查询 ORDERBY子句 语法格式 ORDERBY 排序项 ASC DESC 排序项 ASC DESC n 例 按班级编号降序显示学生的学号 姓名 班级和出生日期 班级相同时按年龄由大到小排序 SELECT学号 姓名 班级 出生日期FROM学生表ORDERBY班级编号DESC 出生日期ASC 简单查询 GROUPBY子句 如果需要按某一列数据的值进行分类 在分类的基础上再进行查询 就要使用GROUPBY子句 它的语法格式如下 GROUPBY如 1 在课程表中查询不同考核类型的平均学时 select考核类型 avg 学时 平均学时from课程表groupby考核类型 简单查询 GROUPBY子句 2 在学生表中查询各民族学生的平均年龄 结果要求给出民族和平均年龄 select族别 avg datediff yy 出生日期 getdate 平均年龄from学生表groupby族别其中 datediff 是系统函数 返回两个指定日期间的时间间隔数目 共有三个参数 datediff datepart startdate enddate 1 datepart参数可以是下列的值 年yy yyyy 季度qq q 月mm m 日dd d 周wk ww 星期dw w 小时hh 分钟mi n 秒ss s 毫秒ms 微妙mcs 纳秒ns2 startdate和enddate参数是合法的日期表达式 getdate 返回当前的日期和时间 简单查询 HAVING子句 HAVING子句指定组或聚合的搜索条件 HAVING通常在GROUPBY子句中使用 如果不使用GROUPBY子句 则HAVING的行为与WHERE子句一样 它的语法格式如下 HAVING搜索条件如 查询 信息工程系 班级号第4 5位为 04 所有班级中 人数少于30人的班级编号和各班班级人数Select班级编号 count 学号 班级人数From学生表Wheresubstring 班级编号 4 2 04 Groupby班级编号havingcount 学号 30 简单查询 COMPUTE子句 COMPUTE子句可以用于SELECT语句中既可查看明细行 又可查看汇总行 可以计算分组的汇总值 也可以计算整个结果集的汇总值 语法格式如下 COMPUTE 聚合函数名 列名 N BY表达式 N 如 查询课程表中考核类型为考试的各课程信息 并计算总学时 Select From课程表Where考核类型 考试 Computesum 学时 多表查询 某些查询涉及的数据可能来自不同的表 此类查询称为多表查询 包括联合查询和连接查询 联合查询格式 SELECTFROMUNIONSELECTFROM如 分别查询部门表和学生表 给出系部编号 系部名称 学生的学号 姓名和性别 并将结果联合输出 select学号 姓名 性别from学生表unionselect系部编号 系部名称from部门表 连接查询 连接的类型分为内连接 外连接和交叉连接 连接的格式有两种如下 格式一 SELECTFROM ON 格式二 SELECTFROM WHERE 交叉连接 交叉连接又称笛卡儿积 返回两个表的乘积 例 表A有10行数据 表B有20行数据 那么表A和表B交叉连接的结果记录集有200行 10 20 数据 交叉连接使用CROSSJOIN关键字来创建 说明 交叉连接只是用于测试一个数据库的执行效率 在实际应用中是无意义的 交叉连接的使用是比较少的 交叉连接不需要连接条件 交叉连接 续 如 查询成绩表与学生表的所有组合 SELECT成绩表 学生表 FROM成绩表CROSSJOIN学生表 内连接 内连接把两个表中的数据 通过相同的列 连接生成第3个表 仅包含那些满足连接条件的数据行 内连接可使用 INNER JOIN关键字来创建 如 1 查询学生记录 列出学号 姓名 所属班级名称select学号 姓名 班级名称from学生表asSjoin班级表asConS 班级编号 C 班级编号2 查询课程表中课程名称包含 数据库 的平均分数selectavg 成绩 平均分from成绩表S 课程表CwhereS 课程编号 C 课程编号and课程名称like 数据库 说明 在多表查询中 Select子句或Where子句中的列名前都加上了表名作为前缀 这样可避免来自不同表中相同属性名发生混淆 由于多次用到表名 为了便于阅读和书写 可以使用表的别名形式 内连接 3 查询 信息工程系 所有班级信息 列出班级名称与人数select班级名称 count 学号 学生人数from学生表S 班级表C 部门表DwhereS 班级编号 C 班级编号andC 系部编号 D 系部编号and系部名称 信息工程系 groupby班级名称 外连接 分类 左外连接 右外连接和全外连接 左外连接 只包括左表的所有行 不包括右表的不匹配行的外连接 右外连接 只包括右表的所有行 不包括左表的不匹配行的外连接 完整外部连接 既包括左表不匹配的行 也包括右表的不匹配的行的连接 左外连接 语法如下 SELECTFROM左表名LEFT OUTER JOIN右表名ON连接条件 例 查询是否所有的学生均有成绩 包括学生的姓名和成绩相关信息 SELECT姓名 成绩表 FROM学生表LEFTJOIN成绩表on学生表 学号 成绩表 学号 右外连接 右外连接的语法如下 SELECTFROM左表名RIGHT OUTER JOIN右表名ON连接条件 例 查询是否所有的学生均有成绩 包括学生的姓名和成绩相关信息 SELECT成绩表 姓名FROM成绩表RIGHTJOIN学生表on成绩表 学号 学生表 学号 全外连接 全外连接的语法如下 SELECTFROM左表名FULL OUTER JOIN右表名ON连接条件包括所有连接表中的所有记录 不论它们是否匹配 混合连接 可将内连接和外连接灵活混合使用 如 查询 0900401001 班级中所有学生的成绩 包括班级编号 班级名称 学号 姓名 成绩 如果没有成绩 也要给出学生姓名 外连接 SelectC 班级编号 班级名称 S1 学号 姓名 成绩From班级表Cjoin学生表S1onC 班级编号 S 班级编号leftjoin成绩表S2onS1 学号 S2 学号WhereC 班级编号 0900401001 子查询 在SQL中 将一条SELECT语句作为另一条SELECT语句的一部分称为子查询 外层的SELECT语句被称为外部查询或父查询 内层的SELECT语句成为内部查询或子查询 子查询如下 SELECT 外层查询或父查询 FROMWHEREIN SELECT 内层查询或子查询 FROMWHERE 单值嵌套 单值嵌套就是通过子查询返回一个单一的数据 当子查询返回的是单值 可以使用 或等比较运算符参加相关表达式的运算 单值嵌套 续 例 查找学生 张三 所在的班级名称 步骤1 查询学生 张三 所在的班级编号 查询语句如下 SELECT班级编号FROM学生表WHERE姓名 张三 得到的结果为0900401001步骤2 查询班级编号为0900401001的班级名称 SELECT班级名称FROM班级表WHERE班级编号 0900401001 得到结果为 11网络技术 组合 SELECT班级名称FROM班级表WHERE班级编号 SELECT班级编号FROM学生表WHERE姓名 张三 单值嵌套 续 如 1 查找年龄最大的学生信息 select from学生表where出生日期 selectmin 出生日期 from学生表 2 查询学生表中比学号 002 的学生年龄大的学生记录select from学生表where出生日期any select出生日期from学生表 多值嵌套 子查询的返回结果是一列值的嵌套查询称为多值嵌套查询 多值嵌套查询经常使用IN操作符 IN操作符使用的格式如下 表达式 NOT IN 子查询 例 查询没有录入成绩的课程信息 select from课程表where课程编号notin select课程编号from成绩表 相关子查询 在相关子查询中会用到关键字EXISTS引出子查询 EXISTS用于测试子查询的结果集中是否存在行 如果EXISTS操作符后查询的结果集不为空 则产生逻辑真值 true 否则产生逻辑假值 false 它的格式如下 NOT EXISTS 子查询 如 查询系统表SYS OBJECTS 如果存在名称为 users 的用户表 则删除它 ifexists selectnamefromsys ob