7第七讲 SQL高级查询语句.ppt

1 第七讲SQL高级查询语句 鲁宁学时 2节 2 教学内容 7 1特殊查询语句7 2联接查询7 2 1Where联接7 2 2JOIN联接7 2 3自联接7 3嵌套查询7 4查询结果输出7 5数据定义语言 3 教学目标及重点难点 教学目标掌握特殊查询语句的使用方法掌握联接查询的使用方法掌握嵌套查询中IN关键字的使用方法了解数据表创表语句掌握视图的创建和使用方法能灵活应用SQL语句解决实际问题重点难点联接查询 子查询的理解及应用创表语句灵活使用 4 7 1 1空值查询 空值判断查询条件常用来搜索某一字段值为空值的记录 可以使用ISNULL或ISNOTNULL关键字来指定这种查询条件 注 NULL值表示字段的数据值未知或不可用 它并不表示零 数字值或二进制值 零长度的字符串或空白 字符值 Select FromClassInfoWhereClassDescisnullSelect FromClassInfoWhereClassDescisNotnull 5 7 1 2集合的并运算 Union UNION运算符将两个或多个SELECT语句的结果组合成一个结果集 使用UNION组合的结果集都必须满足下列条件 具有相同的结构字段数目相同结果集中相应字段的数据类型必须兼容UNION运算符的指定格式如下 SELECT语句UNION ALL SELECT语句 6 UNION示例 Union联接多个结果集Select FromStudScoreInfoWhereStudScore 60AndStudScore 90AndStudScore 90AndStudScore 60AndStudScore 70 7 学生各分数段等级人数 Select 优秀 Count FromStudScoreInfo WhereStudNo 99070470 AndStudScore 90 UnionAll Select 良好 Count FromStudScoreInfo WhereStudNo 99070470 AndStudScore 80AndStudScore 70AndStudScore 80 UnionAll Select 不及格 Count FromStudScoreInfo WhereStudNo 99070470 AndStudScore 60 8 7 2联接查询 当一个查询同时涉及两个以上的表时 称为联接查询 联接查询是通过各个表之间共同属性列的关联来查询数据 数据表之间的联系是通过表的字段值来体现的联接操作的目的就是通过加在联接字段上的条件将多个表联接起来 以便从多个表中查询数据 联接查询是关系数据库中最主要的查询 包括等值与非等值联接查询 自身联接查询 外联接查询等 表的联接方法有两种 方法1 表之间满足一定条件的行进行联接 此时FROM子句中指明进行联接的表名 WHERE子句指明联接的列名及其联接条件 方法2 利用关键字JOIN进行联接 9 7 2 1Where联接 SelectStudInfo StudNo StudInfo StudName StudInfo ClassID ClassInfo ClassNameFromStudInfo ClassInfoWhereStudInfo ClassID ClassInfo ClassIDAndStudInfo StudNoin 99070470 99070401 StudInfo StudNoin 99070470 99070401 为查询条件 而 StudInfo ClassID ClassInfo ClassID为联接条件 ClassID 为联接字段 联接条件的一般格式为 其中 比较运算符主要有 引用两个表中的列名相同的字段时必须用表名前缀限制字段来自于哪个表 10 Where联接 SelectS Studno S StudName C ClassName SI CourseID SI StudScoreFromStudInfoS ClassInfoC StudScoreInfoSIWhereS Studno SI StudNoAndC ClassID S ClassIDAndS StudNo 99070470 11 7 2 2JOIN联接 具体分为以下几种 INNERJOIN 显示符合条件的记录 此为默认值 LEFTJOIN 显示符合条件的数据行以及左边表中不符合条件的数据行 此时右边数据行会以NULL来显示 此称为左联接 RIGHTJOIN 显示符合条件的数据行以及右边表中不符合条件的数据行 此时左边数据行会以NULL来显示 此称为右联接 FULLJOIN 显示符合条件的数据行以及左边表和右边表中不符合条件的数据行 此时缺乏数据的数据行会以NULL来显示 当将JOIN关键词放于FROM子句中时 应有关键词ON与之相对应 以表明联接的条件 12 Join联接 SelectStudInfo StudNo StudInfo StudName StudInfo ClassID ClassInfo ClassNameFromStudInfoInnerJoinClassInfoOnStudInfo ClassID ClassInfo ClassID 等效的Where联接SelectStudNo StudName ClassInfo ClassID ClassNameFromStudInfo ClassInfoWhereStudInfo ClassID ClassInfo ClassID 13 Join联接 SelectStudInfo StudNo StudInfo StudName StudInfo ClassID ClassInfo ClassNameFromStudInfoInnerJoinClassInfoOnStudInfo ClassID ClassInfo ClassIDSelectStudInfo StudNo StudInfo StudName StudInfo ClassID ClassInfo ClassNameFromStudInfoInnerJoinClassInfoOnStudInfo ClassID ClassInfo ClassIDAndStudInfo StudNoin 99070470 99070401 14 Join联接 SELECTStudinfo studno Studinfo studname Classinfo classid Classinfo classname Courseinfo courseid Courseinfo coursename Studscoreinfo studscore FROMclassinfoINNERJOINStudInfo INNERJOINstudscoreinfoINNERJOINcourseinfo ONCourseinfo courseid Studscoreinfo courseid ONStudinfo studno Studscoreinfo studno ONClassinfo classid Studinfo classidAndStudInfo StudNo 99070470 15 Join联接 SelectStudInfo StudNo StudInfo StudName StudSex StudScoreInfo CourseID StudScoreFromStudInfoleftjoinStudScoreInfoOnStudInfo Studno StudScoreinfo StudNoSelectStudInfo StudNo StudInfo StudName StudSex StudScoreInfo CourseID StudScoreFromStudInfoRightjoinStudScoreInfoOnStudInfo Studno StudScoreinfo StudNoSelectStudInfo StudNo StudInfo StudName StudSex StudScoreInfo CourseID StudScoreFromStudInfoFulljoinStudScoreInfoOnStudInfo Studno StudScoreinfo StudNo 16 7 2 3自联接 当一个表与其自已进行联接操作时 称为表的自联接 表的自身联接主要用于同一个表中数据的比较 SelectSi StudNo Si StudName Si StudSexFromStudInfoS StudInfoSIWhereS StudNo 99070478 AndSI StudName S StudNameSelectSi StudNo Si StudName Si StudSexFromStudInfoS StudInfoSIWhereS StudNo 99070478 AndS StudSex SI StudSex 17 自联接 select fromstudinfobackSelectA StudNo A StudName A StudSex B StudNo B StudName B ManagerFromStudInfoBackA StudInfoBackBWhereA StudName B Manager 18 7 3嵌套查询 在WHERE子句中包含一个形如SELECT FROM WHERE的查询块 此查询块称为嵌套查询或子查询 包含子查询的语句称为父查询或外部查询 子查询的嵌套层次最多可达到255层嵌套查询在执行时由里向外处理 即先执行子查询再执行父查询 父查询要用到子查询的结果 1 返回一个值的子查询当子查询的返回值只有一个时 可以使用比较运算符 SelectAvg StudScore FromStudScoreInfoWhereStudNo 99070470 19 2 返回一组值的子查询 如果子查询的返回值不止一个 而是一个集合时 则不能直接使用比较运算符 可以在比较运算符和子查询之间插入ANY或ALL 其具体含义详见以下各例 1 使用ANY 含义为任意一个Select FromStudScoreInfoWhereStudScore Any SelectStudScoreFromStudScoreInfoWhereStudNo 99070470 等价于Select FromStudScoreInfoWhereStudScore SelectMin StudScore FromStudScoreInfoWhereStudNo 99070470 Select FromStudScoreInfoWhereStudScore Any SelectStudScoreFromStudScoreInfoWhereStudNo 99070470 AndStudScore 80 注意 此条是父查询中的条件 20 3 使用ALL ALL的含义为全部Select FromStudScoreInfoWhereStudScore All SelectStudScoreFromStudScoreInfoWhereStudNo 99070470 等价于Select FromStudScoreInfoWhereStudScore SelectMax StudScore FromStudScoreInfoWhereStudNo 99070470 21 4 使用IN 可以使用IN代替 ANY Select FromStudInfoWhereStudnoIN SelectStudNoFromStudScoreInfoGroupByStudNoHavingAvg StudScore 80 Select FromStudInfoWhereStudnoIN SelectStudNoFromStudScoreInfoGroupByStudNoHavingAvg StudScore 80 AndStudSex 男 22 5 使用EXISTS EXISTS用于判断子查询结果是否存在 带有EXISTS的子查询不返回任何实际数据 它只得到逻辑值 真 或 假 NOTEXISTS与此相反 含有IN的查询通常可用EXISTS表示 但反过来不一定 Select FromStudInfoWhereExist Select FromStudScoreInfoWhereStudScore 100AndStudInfo StudNo StudScoreInfo StudNo WORKHELPER办公助手提供 23 7 4查询结果输出 INTO子句 使用INTO子句 可以将查询结果输出到一个新建的数据表或一个临时表 INTOARRAYarrayname 查询结果存放在数组中INTOTABLE DBF 表示输出到数据表 INTOCURSOR 表示输出到临时表 临时表只存储在内存中 关机自动消失 示例 Select FromStudInfoIntoTableAASelect FromStudInfoWhereStudSex 男 intoarrayAMaleSelect FromStudInfoIntoCursortemp 24 TO选项 TOFILE ADDITIVE 将查询结果输出到文本文件 TOPRINT 将查询结果输出打印机 TOSCREEN 将查询结果输出VF主窗口 示例 Select FromStudInfoTOFileTResultSelect FromStudScoreInfoWhereStudNo 99070470 toScreen 25 7 5数据定义语言 SQL语言使用数据定义语言 DataDefinitionLanguage 简称DDL 实现其数据定义功能 可对数据库用户 基本表 视图 索引进行定义和撤消 26 创建数据库SQL语句 创建数据库SQL语句CreateDataBasedatabasename示例CreateDataBaseStudScoreManageDB WORKHELPER办公助手提供 27 7 5 1创建数据表 CREATETABLE命令主要用于创建一个新的数据表如果在创建数据表时已经打开了一个数据库 则所创建的数据表将自动添加到该数据库中 否则将生成自由表 语法CREATETABLE FREE 小数位 NULL NOTNULL CHECK ERROR DEFAULT PRIMARYKEY UNIQUE 功能创建数据表结构 28 说明 FREE指定创建自由表 当数据库没有打开时 不必指定该项 命令中常见的字段数据类型和字段宽度及小数位数 NULL NOTNULL表示是否允许字段值为空值 CHECK用于指定字段的有效性规则 ERROR指定当输入的字段值违反有效性规则时 显示提示信息 DEFAULT用于设置字段的默认值 PRIMARYKEY用于设置字段为主索引 一个表中只能有一个主索引 UNIQUE用于设置侯选索引 不允许出现NULL值 同一个字段不能既是主索引 又是侯选索引 也就是说 PRIMARYKEY和UNIQUE和不能同时出现在一个字段的定义中 29 数据类型 30 示例 CREATETABLEStudInfo1 StudNoC 15 PRIMARYKEYNOTNULL StudNameC 20 NOTNULL StudSexC 2 DEFAULT 男 CHECKStudSex 男 ORStudSex 女 ERROR StudSEX必须是男或女 StudBirthDayDCHECK StudBirthDay 1970 01 01 StudScoreN 5 1 LevelL StudDescM PhotoG