SQL培训-第二讲PPT课件.ppt

SQL培训第二讲 2011年11月3日 2020 3 18 1 主要内容 作业分析讲评SQL中的常用函数聚合分析与分组存储过程SQL触发器 2020 3 18 2 作业分析讲评 以下为一表名为 职工考核信息 的表 请按照要求使用SQL语言执行相应操作 表一 职工考核信息表 表二 销售业绩明细表 1 从表1中查询出 工号 姓名 和 职称 三列的所有数据行 2020 3 18 3 作业分析讲评 以下为一表名为 职工考核信息 的表 请按照要求使用SQL语言执行相应操作 表一 职工考核信息表 表二 销售业绩明细表 2 从表1中查询出 出生年月 在1980 01 01以后 而且 职称 为工程师或经济师的所有数据行 2020 3 18 4 作业分析讲评 以下为一表名为 职工考核信息 的表 请按照要求使用SQL语言执行相应操作 表一 职工考核信息表 表二 销售业绩明细表 3 向表1添加一条数据 2020 3 18 5 作业分析讲评 以下为一表名为 职工考核信息 的表 请按照要求使用SQL语言执行相应操作 表一 职工考核信息表 表二 销售业绩明细表 4 从表1中查询出职工的考核成绩大于70分的部门名称 并按成绩高低排序 2020 3 18 6 作业分析讲评 以下为一表名为 职工考核信息 的表 请按照要求使用SQL语言执行相应操作 表一 职工考核信息表 表二 销售业绩明细表 5 将表2的销售业绩按月份 销售数量和商品的名称排名 注 销售数量由大到小 并与表1进行关联 显示工号 姓名 职称 按工号进行排序 2020 3 18 7 作业分析讲评 以下为一表名为 职工考核信息 的表 请按照要求使用SQL语言执行相应操作 表一 职工考核信息表 表二 销售业绩明细表 6 从表2中查询姓名为 王海 的职工 如果其某个月的某种商品销售数量超过100 则对其表1中的考核成绩加5分 2020 3 18 8 作业分析讲评 以下为一表名为 职工考核信息 的表 请按照要求使用SQL语言执行相应操作 表一 职工考核信息表 表二 销售业绩明细表 7 将2月份电视机的销售数量超过50台的职工的姓名 性别和籍贯查询出来 2020 3 18 9 作业分析讲评 以下为一表名为 职工考核信息 的表 请按照要求使用SQL语言执行相应操作 表一 职工考核信息表 表二 销售业绩明细表 8 查询出1月份没有销售空调的销售人员的姓名 年龄和籍贯 2020 3 18 10 一张表有一个id字段 是integer类型的 然后要你找出id第2大的记录 select from selectrow number over orderbyiddesc asseq fromt scrap twhereseq 2 2020 3 18 11 关于groupby表内容 2005 05 09胜2005 05 09胜2005 05 09负2005 05 09负2005 05 10胜2005 05 10负2005 05 10负如果要生成下列结果 该如何写sql语句 胜负2005 05 09222005 05 1012 selectrq sum casewhenshengfu 胜 then1else0end 胜 sum casewhenshengfu 负 then1else0end 负 from tmpgroupbyrq 2020 3 18 12 表中有ABC三列 用SQL语句实现 当A列大于B列时选择A列否则选择B列 当B列大于C列时选择B列否则选择C列 select casewhena bthenaelsebend casewhenb cthenbeslecend fromtable name 2020 3 18 13 有一张表 里面有3个字段 语文 数学 英语 其中有3条记录分别表示语文70分 数学80分 英语58分 请用一条sql语句查询出这三条记录并按以下条件显示出来大于或等于80表示优秀 大于或等于60表示及格 小于60分表示不及格 显示格式 语文数学英语及格优秀不及格 select casewhen语文 80then 优秀 when语文 60then 及格 else 不及格 as语文 casewhen数学 80then 优秀 when数学 60then 及格 else 不及格 as数学 casewhen英语 80then 优秀 when英语 60then 及格 else 不及格 as英语 fromtable 2020 3 18 14 SQL函数 函数 函数执行 2020 3 18 15 两种SQL函数 函数 单行函数 多行函数 2020 3 18 16 字符函数 字符函数 LOWERUPPERINITCAP CONCATSUBSTRLENGTHINSTRLPAD RPADTRIMREPLACE 大小写控制函数 字符控制函数 2020 3 18 17 函数 结果 大小写控制函数 这类函数改变字符的大小写 LOWER SQLCourse UPPER SQLCourse INITCAP SQLCourse sqlcourseSQLCOURSESqlCourse 2020 3 18 18 大小写控制函数 显示员工Higgins的信息 SELECTemployee id last name department idFROMemployeesWHERElast name higgins norowsselected SELECTemployee id last name department idFROMemployeesWHERELOWER last name higgins 2020 3 18 19 CONCAT Hello World SUBSTR HelloWorld 1 5 LENGTH HelloWorld INSTR HelloWorld W LPAD salary 10 RPAD salary 10 TRIM H FROM HelloWorld HelloWorldHello106 2400024000 elloWorld 函数 结果 字符控制函数 这类函数控制字符 2020 3 18 20 SELECTemployee id CONCAT first name last name NAME job id LENGTH last name INSTR last name a Contains a FROMemployeesWHERESUBSTR job id 4 REP 字符控制函数 1 2 3 1 2 3 2020 3 18 21 数字函数 ROUND 四舍五入ROUND 45 926 2 45 93TRUNC 截断TRUNC 45 926 2 45 92MOD 求余MOD 1600 300 100 2020 3 18 22 SELECTROUND 45 923 2 ROUND 45 923 0 ROUND 45 923 1 FROMDUAL ROUND函数 DUAL是一个 伪表 可以用来测试函数和表达式 1 2 3 3 1 2 2020 3 18 23 SELECTTRUNC 45 923 2 TRUNC 45 923 TRUNC 45 923 2 FROMDUAL TRUNC函数 3 1 2 1 2 3 2020 3 18 24 SELECTlast name salary MOD salary 5000 FROMemployeesWHEREjob id SA REP MOD函数 2020 3 18 25 日期 Oracle内部使用数字存储日期 世纪 年 月 日 小时 分钟 秒 默认的日期格式是DD MON RR 可以只指定年的后两位在20世纪存放21世纪的日期 同样可以在21世纪存放20世纪的日期 SELECTlast name hire dateFROMemployeesWHERElast namelike G 2020 3 18 26 日期 函数SYSDATE返回 日期时间 2020 3 18 27 日期的数学运算 在日期上加上或减去一个数字结果仍为日期 两个日期相减返回日期之间相差的天数 可以用数字除24来向日期中加上或减去小时 2020 3 18 28 日期的数学运算 SELECTlast name SYSDATE hire date 7ASWEEKSFROMemployeesWHEREdepartment id 90 2020 3 18 29 日期函数 两个日期相差的月数 MONTHS BETWEEN ADD MONTHS NEXT DAY LAST DAY ROUND TRUNC 向指定日期中加上若干月数 指定日期的下一个日期 本月的最后一天 日期四舍五入 日期截断 函数 描述 2020 3 18 30 MONTHS BETWEEN 01 SEP 95 11 JAN 94 日期函数 ADD MONTHS 11 JAN 94 6 NEXT DAY 01 SEP 95 FRIDAY LAST DAY 01 FEB 95 19 6774194 11 JUL 94 08 SEP 95 28 FEB 95 2020 3 18 31 ROUND SYSDATE MONTH 01 AUG 95 ROUND SYSDATE YEAR 01 JAN 96 TRUNC SYSDATE MONTH 01 JUL 95 TRUNC SYSDATE YEAR 01 JAN 95 日期函数 AssumeSYSDATE 25 JUL 95 2020 3 18 32 转换函数 隐性 显性 数据类型转换 2020 3 18 33 隐式数据类型转换 Oracle自动完成下列转换 VARCHAR2orCHAR 源数据类型 目标数据类型 VARCHAR2orCHAR NUMBER DATE NUMBER DATE VARCHAR2 VARCHAR2 2020 3 18 34 隐式数据类型转换 表达式计算中 Oracle自动完成下列转换 VARCHAR2orCHAR 源数据类型 目标数据类型 VARCHAR2orCHAR NUMBER DATE 2020 3 18 35 显式数据类型转换 NUMBER CHARACTER TO CHAR 2020 3 18 36 TO CHAR函数对日期的转换 格式 必须包含在单引号中而且大小写敏感 可以包含任意的有效的日期格式 可以使用fm去掉多余的空格或者前导零 与日期指用逗号隔开 TO CHAR date format model 2020 3 18 37 YYYY 日期格式的元素 YEAR MM MONTH DY DAY 2004 TWOTHOUSANDANDFOUR 02 MON MONDAY JULY MON JUL DD 02 2020 3 18 38 日期格式的元素 时间格式使用双引号向日期中添加字符日期在月份中的位置 2020 3 18 39 TO CHAR函数对日期的转换 SELECTlast name TO CHAR hire date fmDDMonthYYYY ASHIREDATEFROMemployees 2020 3 18 40 TO CHAR函数对数字的转换 下面是在TO CHAR函数中经常使用的几种格式 TO CHAR number format model 9 0 L 数字 零 美元符 本地货币符号 小数点 千位符 2020 3 18 41 SELECTTO CHAR salary 99 999 00 SALARYFROMemployeesWHERElast name Ernst TO CHAR函数对数字的转换 2020 3 18 42 2020 3 18 43 TO NUMBER和TO DATE函数 使用TO NUMBER函数将字符转换成数字 使用TO DATE函数将字符转换成日期 这些函数可以使用fx修饰符 n TO NUMBER char format model TO DATE char format model 2020 3 18 44 TO NUMBER和TO DATE函数 使用TO NUMBER函数将字符转换成数字 使用TO DATE函数将字符转换成日期 这些函数可以使用fx修饰符 TO NUMBER char format model TO DATE char format model 2020 3 18 45 RR日期格式 当前年1995199520012001 日期27 OCT 9527 OCT 1727 OCT 1727 OCT 95 RR格式1995201720171995 YY格式1995191720172095 当前的年份 0 49 0 49 50 99 50 99 Thereturndateisinthecurrentcentury Thereturndateisinthecenturyafterthecurrentone Thereturndateisinthecenturybeforethecurrentone Thereturndateisinthecurrentcentury 指定的年份 2020 3 18 46 RR日期格式 SELECTlast name TO CHAR hire date DD Mon YYYY FROMemployeesWHEREhire date TO DATE 01 Jan 90 DD Mon RR 使用RR日期格式查找雇佣日期在1990年之前的员工 在1999或现在使用下面的命令会产生相同的结果 2020 3 18 47 嵌套函数 单行函数可以嵌套 嵌套函数的执行顺序是由内到外 F3 F2 F1 col arg1 arg2 arg3 步骤1 结果1 步骤2 结果2 步骤3 结果3 2020 3 18 48 SELECTlast name NVL TO CHAR manager id NoManager FROMemployeesWHEREmanager idISNULL 嵌套函数 2020 3 18 49 通用函数 这些函数适用于任何数据类型 同时也适用于空值 NVL expr1 expr2 NVL2 expr1 expr2 expr3 NULLIF expr1 expr2 COALESCE expr1 expr2 exprn 2020 3 18 50 NVL函数 将空值转换成一个已知的值 可以使用的数据类型有日期 字符 数字 函数的一般形式 NVL commission pct 0 NVL hire date 01 JAN 97 NVL job id NoJobYet 2020 3 18 51 SELECTlast name salary NVL commission pct 0 salary 12 salary 12 NVL commission pct 0 AN SALFROMemployees 使用NVL函数 1 2 1 2 2020 3 18 52 SELECTlast name salary commission pct NVL2 commission pct SAL COMM SAL incomeFROMemployeesWHEREdepartment idIN 50 80 使用NVL2函数 1 2 1 2 2020 3 18 53 SELECTfirst name LENGTH first name expr1 last name LENGTH last name expr2 NULLIF LENGTH first name LENGTH last name resultFROMemployees 使用NULLIF函数 1 2 3 1 2 3 2020 3 18 54 使用COALESCE函数 COALESCE与NVL相比的优点在于COALESCE可以同时处理交替的多个值 如果第一个表达式费空 则返回这个表达式 对其他的参数进行COALESCE 2020 3 18 55 SELECTlast name COALESCE commission pct salary 10 commFROMemployeesORDERBYcommission pct 使用COALESCE函数 2020 3 18 56 条件表达式 在SQL语句中使用IF THEN ELSE逻辑 使用两种方法 CASE表达式DECODE函数 2020 3 18 57 CASE表达式 在需要使用IF THEN ELSE逻辑时 CASEexprWHENcomparison expr1THENreturn expr1 WHENcomparison expr2THENreturn expr2WHENcomparison exprnTHENreturn exprnELSEelse expr END 2020 3 18 58 SELECTlast name job id salary CASEjob idWHEN IT PROG THEN1 10 salaryWHEN ST CLERK THEN1 15 salaryWHEN SA REP THEN1 20 salaryELSEsalaryEND REVISED SALARY FROMemployees CASE表达式 下面是使用case表达式的一个例子 2020 3 18 59 DECODE函数 在需要使用IF THEN ELSE逻辑时 DECODE col expression search1 result1 search2 result2 default 2020 3 18 60 DECODE函数 SELECTlast name job id salary DECODE job id IT PROG 1 10 salary ST CLERK 1 15 salary SA REP 1 20 salary salary REVISED SALARYFROMemployees 2020 3 18 61 DECODE函数 SELECTlast name salary DECODE TRUNC salary 2000 0 0 0 00 1 0 09 2 0 20 3 0 30 4 0 40 5 0 42 6 0 44 0 45 TAX RATEFROMemployeesWHEREdepartment id 80 使用decode函数的一个例子 2020 3 18 62 聚合分析与分组 SQL提供了一些列的聚集函数对表中的数据进行统计分析 在SQL中可以使用分组命令将列中的数据按照一定的条件进行分组 一般只将聚合函数作用在满足条件的分组上 因此聚合函数与分组命令一般结合使用 常见的聚合函数有sum max min avg count 常用的分组函数有GROUPBY 2020 3 18 63 2020 3 18 64 存储过程 将常用的或很复杂的工作 预先用SQL语句写好并用一个指定的名称存储起来 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时 只需调用execute 即可自动完成命令 2020 3 18 65 存储过程的优点 1 存储过程只在创造时进行编译 以后每次执行存储过程都不需再重新编译 而一般SQL语句每执行一次就编译一次 所以使用存储过程可提高数据库执行速度 2 当对数据库进行复杂操作时 如对多个表进行Update Insert Query Delete时 可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用 3 存储过程可以重复使用 可减少数据库开发人员的工作量 4 安全性高 可设定只有某此用户才具有对指定存储过程的使用权 2020 3 18 66 创建存储过程 语法CREATEPROC EDURE owner procedure name number parameterdata type VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION ASsql statement n 2020 3 18 67 参数owner拥有存储过程的用户ID的名称 owner必须是当前用户的名称或当前用户所属的角色的名称 procedure name新存储过程的名称 过程名必须符合标识符规则 且对于数据库及其所有者必须唯一 number是可选的整数 用来对同名的过程分组 以便用一条DROPPROCEDURE语句即可将同组的过程一起除去 例如 名为orders的应用程序使用的过程可以命名为orderproc 1 orderproc 2等 DROPPROCEDUREorderproc语句将除去整个组 如果名称中包含定界标识符 则数字不应包含在标识符中 只应在procedure name前后使用适当的定界符 2020 3 18 68 参数 parameter过程中的参数 在CREATEPROCEDURE语句中可以声明一个或多个参数 用户必须在执行过程时提供每个所声明参数的值 除非定义了该参数的默认值 或者该值设置为等于另一个参数 存储过程最多可以有2 100个参数 使用 符号作为第一个字符来指定参数名称 参数名称必须符合标识符的规则 每个过程的参数仅用于该过程本身 相同的参数名称可以用在其它过程中 默认情况下 参数只能代替常量 而不能用于代替表名 列名或其它数据库对象的名称 data type参数的数据类型 除table之外的其他所有数据类型均可以用作存储过程的参数 但是 cursor数据类型只能用于OUTPUT参数 如果指定cursor数据类型 则还必须指定VARYING和OUTPUT关键字 对于可以是cursor数据类型的输出参数 没有最大数目的限制 VARYING指定作为输出参数支持的结果集 由存储过程动态构造 内容可以变化 仅适用于游标参数 default参数的默认值 如果定义了默认值 不必指定该参数的值即可执行过程 默认值必须是常量或NULL 如果过程将对该参数使用LIKE关键字 那么默认值中可以包含通配符 和 OUTPUT表明参数是返回参数 该选项的值可以返回给EXEC UTE 使用OUTPUT参数可将信息返回给调用过程 Text ntext和image参数可用作OUTPUT参数 使用OUTPUT关键字的输出参数可以是游标占位符 2020 3 18 69 参数n表示最多可以指定2 100个参数的占位符 RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION RECOMPILE表明SQLServer不会缓存该过程的计划 该过程将在运行时重新编译 在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时 请使用RECOMPILE选项 ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE语句文本的条目 使用ENCRYPTION可防止将过程作为SQLServer复制的一部分发布 FORREPLICATION指定不能在订阅服务器上执行为复制创建的存储过程 使用FORREPLICATION选项创建的存储过程可用作存储过程筛选 且只能在复制过程中执行 本选项不能和WITHRECOMPILE选项一起使用 AS指定过程要执行的操作 sql statement过程中要包含的任意数目和类型的Transact SQL语句 但有一些限制 n是表示此过程可以包含多条Transact SQL语句的占位符 2020 3 18 70 SQL触发器 触发器是一种特殊类型的存储过程 不由用户直接调用 创建触发器时会对其进行定义 以便在对特定表或列作特定类型的数据修改时执行 CREATEPROCEDURE或CREATETRIGGER语句不能跨越批处理 即存储过程或触发器始终只能在一个批处理中创建并编译到一个执行计划中 用触发器还可以强制执行业务规则MicrosoftSQLServer 2000提供了两种主要机制来强制业务规则和数据完整性 约束和触发器 触发器是一种特殊类型的存储过程 它在指定的表中的数据发生变化时自动生效 唤醒调用触发器以响应INSERT UPDATE或DELETE语句 触发器可以查询其它表 并可以包含复杂的Transact SQL语句 将触发器和触发它的语句作为可在触发器内回滚的单个事务对待 如果检测到严重错误 例如 磁盘空间不足 则整个事务即自动回滚 2020 3 18 71 优点 触发器可通过数据库中的相关表实现级联更改 不过 通过级联引用完整性约束可以更有效地执行这些更改 触发器可以强制比用CHECK约束定义的约束更为复杂的约束 与CHECK约束不同 触发器可以引用其它表中的列 例如 触发器可以使用另一个表中的SELECT比较插入或更新的数据 以及执行其它操作 如修改数据或显示用户定义错误信息 触发器也可以评估数据修改前后的表状态 并根据其差异采取对策 一个表中的多个同类触发器 INSERT UPDATE或DELETE 允许采取多个不同的对策以响应同一个修改语句 2020 3 18 72 SQL触发器语法 CREATETRIGGERtrigger nameON table view WITHENCRYPTION FOR AFTER INSTEADOF INSERT DELETE UPDATE WITHAPPEND NOTFORREPLICATION AS IFUPDATE column AND OR UPDATE column n IF COLUMNS UPDATED updated bitmask column bitmask n sql statement n 2020 3 18 73 参数 trigger name是触发器的名称 触发器名称必须符合标识符规则 并且在数据库中必须唯一 可以选择是否指定触发器所有者名称 Table view是在其上执行触发器的表或视图 有时称为触发器表或触发器视图 可以选择是否指定表或视图的所有者名称 WITHENCRYPTION加密syscomments表中包含CREATETRIGGER语句文本的条目 使用WITHENCRYPTION可防止将触发器作为SQLServer复制的一部分发布 AFTER指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发 所有的引用级联操作和约束检查也必须成功完成后 才能执行此触发器 如果仅指定FOR关键字 则AFTER是默认设置 不能在视图上定义AFTER触发器 2020 3 18 74 参数 INSTEADOF指定执行触发器而不是执行触发SQL语句 从而替代触发语句的操作 在表或视图上 每个INSERT UPDATE或DELETE语句最多可以定义一个INSTEADOF触发器 然而 可以在每个具有INSTEADOF触发器的视图上定义视图 INSTEADOF触发器不能在WITHCHECKOPTION的可更新视图上定义 如果向指定了WITHCHECKOPTION选项的可更新视图添加INSTEADOF触发器 SQLServer将产生一个错误 用户必须用ALTERVIEW删除该选项后才能定义INSTEADOF触发器 DELETE INSERT UPDATE 是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字 必须至少指定一个选项 在触发器定义中允许使用以任意顺序组合的这些关键字 如果指定的选项多于一个 需用逗号分隔这些选项 对于INSTEADOF触发器 不允许在具有ONDELETE级联操作引用关系的表上使用DELETE选项 同样 也不允许在具有ONUPDATE级联操作引用关系的表上使用UPDATE选项 2020 3 18 75 参数 WITHAPPEND指定应该添加现有类型的其它触发器 只有当兼容级别是65或更低时 才需要使用该可选子句 如果兼容级别是70或更高 则不必使用WITHAPPEND子句添加现有类型的其它触发器 这是兼容级别设置为70或更高的CREATETRIGGER的默认行为 有关更多信息 请参见sp dbcmptlevel WITHAPPEND不能与INSTEADOF触发器一起使用 或者 如果显式声明AFTER触发器 也不能使用该子句 只有当出于向后兼容而指定FOR时 没有INSTEADOF或AFTER 才能使用WITHAPPEND 以后的版本将不支持WITHAPPEND和FOR 将被解释为AFTER NOTFORREPLICATION表示当复制进程更改触发器所涉及的表时 不应执行该触发器 AS是触发器要执行的操作 2020 3 18 76 参数 sql statement是触发器的条件和操作 触发器条件指定其它准则 以确定DELETE INSERT或UPDATE语句是否导致执行触发器操作 当尝试DELETE INSERT或UPDATE操作时 Transact SQL语句中指定的触发器操作将生效 触发器可以包含任意数量和种类的Transact SQL语句 触发器旨在根据数据修改语句检查或更改数据 它不应将数据返回给用户 触发器中的Transact SQL语句常常包含控制流语言 CREATETRIGGER语句中使用几个特殊的表 deleted和inserted是逻辑 概念 表 这些表在结构上类似于定义触发器的表 也就是在其中尝试用户操作的表 这些表用于保存用户操作可能更改的行的旧值或新值 例如 若要检索deleted表中的所有值 请使用 SELECT FROMdeleted 如果兼容级别等于70 那么在DELETE INSERT或UPDATE触发器中 SQLServer将不允许引用inserted和deleted表中的text ntext或image列 不能访问inserted和deleted表中的text ntext和image值 若要在INSERT或UPDATE触发器中检索新值 请将inserted表与原始更新表联接 当兼容级别是65或更低时 对inserted或deleted表中允许空值的text ntext或image列 将返回空值 如果这些列不可为空 则返回零长度字符串 当兼容级别是80或更高时 SQLServer允许在表或视图上通过INSTEADOF触发器更新text ntext或image列 2020 3 18 77 参数 n是表示触发器中可以包含多条Transact SQL语句的占位符 对于IF