Oracle开发部分简单查询.ppt

Oracle开发部分课件 第一部分 表和约束 在关系型数据库中表是用来存储数据的数据库对象 Oracle中所有信息都存放在表中 表的逻辑结构是由列组成 每个列必须有一个唯一的名字同时分配一个数据类型和列的长度 Oracle中数据库对象的命名规则 1 必须由字母开始长度在1 30个字符之间 2 名字中只能包含字母 数字 下划线 和 3 同一个Oracle服务器的用户所拥有的对象名不能重复 4 名字不能为Oracle的关键字和保留字 5 建议大家用描述性的名字为表和其他数据库对象命名 如为一个员工信息表命名不建议用t1 而用employee或employees 6 名字是不区分大小写的 如Employee和employee是表示同一个对象 为了创建表 用户必须有创建表的权限 Oracle中常用的数据类型 1 数值型 number p s p表示精度 为数据的总长度 取值范围是1 38 s表示小数取值范围从 84 127 Oracle中没有专门的整型数据可将s设为0或缺省来表示整数 2 字符型 char size 固定长度的字符串 长度的大小以字节为单位默认最小字符数1 最大字符数为2000 varchar2 size 可变长度的字符数据 最小字符数1 最大字符数4000 3 日期型date 日期时间类型 4 其他类型 long最大2G的可变字符数据 clob最大可存储4G的字符数据 rowid十六进制串 表示在表中行的地址 第一部分 表和约束 Oracle中表的分类 1 用户表 由用户创建和维护的表的集合 包含用户使用的数据 2 数据字典 由Oracle服务器创建和维护的表的集合 它包含数据库信息如表的定义 数据库结构信息等 可以把它理解为表的表 由Oracle服务器创建和维护数据字典表中的表告诉数据库在数据库中存储的是何种数据 存储在什么地方以及数据库如何使用这些数据 如当前用户使用的表都在数据字典中的user tables中 如查看当前用户的所有表 selecttable namefromuser tables select fromtab 创建表 1 语法 createtable schema table name column namedatatype defaultexpress 说明 createtable 创建表的关键字 schema 表示对象的拥有者 即一个用户可以为schema创建表 column name为列名datatype为列的数据类型 defalut为列的默认值属性 第一部分 表和约束 练习 1 以Scott用户连接数据库创建studentinfo表 字段包括 stu id stu name stu sex birthday address tel 通过企业管理器打开表并添加数据 并查看表结构 注意date类型数据的添加 2 以system用户连接数据库 给scott用户创建表course 字段有课程号 课程名 3 创建客户表 根据业务需求设计表结构关于几种常用数据类型的总结 1 字符或文本类型 有两种 varchar2和char 都可以保存字符串 但二者有明显的区别 2 数值类型 number3 日期类型 date oracle中日期型数据存储格式和显示格式有明显差异 默认的显示格式为 dd mon rr 即添加时应按03 5月 1992 4 伪列 rowid是表中虚拟的列 由系统自动产生每行都有rowid 这表示这一行的唯一地址 rowid标识Oracle如何定位行 通过rowid能快速的定位行 可用来定义行变量 第一部分 表和约束 表中的约束 1 数据完整性 数据完整性是指业务数据的正确性和完备性 包括实体完整性 域完整性 参照完整性 在Oracle中用cnstraints来防止无效数据的输入 具体如下 2 约束的命名规则 所有的约束定义存储在数据字典中 如给约束一个有意义的名字约束易于维护 约束命名必须遵守对象命名规则 如可以采用这样的命名习惯 表名 字段名 约束类型 如果没起名Oracle中按默认的格式SYS Cn产生一个名字 n是一个整数值 用来保证唯一性 强烈建议主键和外键约束必须起名 3 实现的方法 定义性 创建表时 确定字段的约束 过程性 编写数据库服务器短的程序 过程 触发器 约束的语法 createtable schema table name column namedatatype defaultexpress column constraint table constraint 其中column constraint是列级完整性约束 table constraint是表级完整性约束 约束通常是在表创建时被同时创建 也可以通过修改表添加约束 并且约束还可以被临时禁用 第一部分 表和约束 表级约束和列级约束 对于约束只涉及单独的一个列 在该列上可以使用约束 称为列级约束 列级约束能定义数据完整性的任何约束的类型 表级约束时指约束涉及一列或多列时 约束放在所有列定义之后 称为表级约束 如 createtablecountry country codenumber 6 country namevarchar2 10 notnull constraintscountry code pkprimarykey country code 约束的类型1 NOTNULL约束 NOTNULL约束定义列的非空约束 在默认情况下 列可以为空 NOTNULL约束只能定义列级约束 不能定义表级约束 例如 定义客户表客户的姓名不能为空 略2 UNIQUE约束 唯一值约束 要求列或者列的组合中的每个值具有唯一性 注意如果对于没有notnull约束的列unique约束允许空值 对于无notnull约束的列 unique约束中允许存在多行空值的列 因为空值不等于任何值 注意 Oracle在unique列自动隐式创建一个索引 例如 创建shopping表 购物者卡号具有唯一性 创建一个学生选课的表 一个学生可以选多门课但是不能重复的选一门课 第一部分 表和约束 PRIMARYKEY约束 主键约束 为表创建一个主键 每个表中只能含有一个主键 PRIMARYKEY约束是对表中行唯一标识的列或列的组合 该约束强制列或列的组合的值具有唯一性并强制非空 实际上可以理解为notnull unique例如创建客户表 客户编号为主键 创建shopping表一个客户可以买多种商品 使用客户编号和商品编号做主键FOREIGNKEY约束 外键约束 用来定义引用完整性 指明一个列或者列的组合为外键 外键是建立两个表或者多个表之间关连的字段 外键通常是参考不同表的主键或者唯一值字段 定义外键的表一般称为子表或者外键表 被引用字段的表称为父表或主键表 注意主键表必须优先外键表创建 在一个表中可以存在多个外键 1 实现外键约束的语法 createtable schema table name column namedatatype defaultexpress table constraint constraintstname cname fkforeignkey colum1 colum2 referencesp tablename colum1 colum2 注意 在定义外键时可以加ondeletecascade选项表示删除主键表中某一行时 级连删除外键表中外键值与主键表中删除行主键值对应的所有行 ondeletesetnull表示将对应主键表删除行的主键值在外键表都设为空 第一部分 表和约束 1 实例 createtablet1 t idnumber 6 primarykey t namevarchar2 20 createtablet2 t2 idnumber 6 primarykey t2 namevarchar2 20 t idnumber 6 constraintt2 t id fkforeignkey t id referencest1 t id ondeletecascade 例如 创建商品类别 categories 表和商品 productsinfo 表实现外键关系2 创建外键的注意问题 1 主键表的关键字段必须具有唯一值特性 2 关系表之间的关键字段数据类型必须完全相同 3 外键约束创建在外间表中 4 外键的字段数量 顺序 数据类型要与主键表的关键字段对应 复合主键 5 先创建主键表 先删除外键表 先给主键表添加数据 先删除外键表数据 第一部分 表和约束 CHECK约束 定义每行都必须满足的条件该条件可以用查询条件一样的结构 需要一定条件判断等 1 Oracle中与check有关运算符 1 关系运算符 2 betweenand 3 like 与like一起共同使用的 是一种模糊条件的限定 也可以与 连用 4 逻辑运算符and or not 第一部分 表和约束 修改表 在创建表后 比如由于业务需要 来修改表的结构 以满足业务需求 修改表的语法为 altertabletable name修改细节 1 添加新的列 不能指定列的位置 只能追加 altertabletable nameaddcolumn namedatatype defaultexpress constraints column namedatatype 例如给客户表添加主页的列2 修改列的数据类型 altertabletable namemodifycolumn namedatatype defaultexpress column namedatatype 例如 修改客户姓名的数据类型为char 30 3 删除列altertabletable namedrop column name 例如 删除客户表中新添加的主页字段 第一部分 表和约束 4 修改已经存在的列的注意事项 1 可以增加数字类型和字符类型字段的长度 2 是否可以减小数字类型和字符类型字段的长度 要依赖于表中的数据 3 如果列中值包含空值或者或者表中没有行时 刻减少列的宽度 4 如果列中只包含空值时刻以更改列的数据类型 5 当列中有空值时 或者不改变大小时可以将char n 转换到varchar2 n 也可将varchar2 n 转换到char n 6 对默认值的改变仅影响以后的输入的列的值 5 删除列要注意的事项 1 列可以有也没有数据 2 表中至少要保留一列 3 列被删除后不能恢复 4 被外键引用的列通常不能删除除非指定级连删除 修改表中的约束 1 添加约束 altertabletable nameaddconstraintconstraint nameconstraints如给主页字段添加约束 要求以http 开头 2 删除约束 altertabletable namedropconstraintconstraint name例如删除员工年龄的限制 第一部分 表和约束 重命名表和删除表1 删除表 语法 droptabletable tablename注意如果删除的表是主键表 那么应先删除外键表在删除主键表 2 重命名表 语法 renameold nametonew name综合练习 设计一个小型超市管理系统的表如图所示 第二部分 编写简单的查询语句 SQL语言简介 SQL语言是结构化查询语言 是标准的RDBMS的语言 是绝大多数DB产品支持的工业标准 主要包括DML 数据的操作语言 DDL 数据的定义语言 DCL 数据的控制语言 等 DML select insertinto update delete DDL create alter dropDCL grant关于本章节采用的案例表说明 除了安装时scott用户表外 还要导入一个pubs dmp文件中的表 导入的操作系统命令是在DOS下 impuserid system systemfile e pubs dmpfromuser satouser scott介绍导入的表及数据基本的查询语句 1 select distinct 列名 表达式 列别名 from表名 1 select from表名 表示查询表中所有的列例如 查询员工表中所有员工的信息 select fromemp 2 select列名1 列名2 from表名 查询特定的列例如如查询titles表中部分信息selecttitle id title type pricefromtitles 3 表达式 在查询时可以用表中列参与算术运算例如 查询员工工号 姓名和资增长400后的工资selectempno ename sal sal 400fromemp 第二部分 编写简单的查询语句 4 列别名的使用 在查询时 为了使查询的信息表达的更清晰 友好这是可以给被查询的列起别名 在显示信息的时候采用别名 例如 查询员工工资增加400后新工资 包括员工号 员工姓名 员工新旧工资信息 selectempno ename sal sal 400newsalfromemp 5 空值的使用 Null在SQL中是一个值得重视的对象 在数学运算中如果没有考虑Null值经常会出现一些预想不到的结果 在Oracle官方文档中规定 空值就是不可用的 未分配的 空值不等于0或者空格 在查询中和null值运算的结果仍为null值例如 有一个员工表关于员工工资的计算应该为 基本工资 岗位津贴 奖金 但是有的员工由于工龄短 没有岗位津贴 有的员工由于业务差而没有奖金 月底结算员工工资总额时出现了问题 创建表empinfo 包括工号 姓名 基本工资 津贴 奖金 通过企业管理器添加数据然后使用select语句求员工工资总额 select工号 姓名 基本工资 津贴 奖金工资总额fromempinfo 6 distinct关键字 Distinct关键字是用来去掉重复行的 例如查询图书表中图书的类型信息 selectdistincttypefromtitles 7 在查询中也可以使用字符串连接符 例如将将书号书名了类型信息连成一列显示出来 selecttitle id title type fromtitles 第二部分 编写简单的查询语句 条件查询和排序数据1 条件查询 基本查询语句是将表中的全部记录的全部信息或者部分信息查询出来 但是有的时候表中的全部记录不是全部需要的 这是就要进行满足条件的的记录的筛选语法格式是 select distinct 列名 表达式 列别名 from表名where条件说明 在where语句可以使用的运算符有 关系运算符和特殊的比较运算符以及逻辑运算符 1 关系运算符 2 特殊的比较运算符 between and notbetween and in like isnull isnotnull 3 逻辑运算符 and or not实例 第二部分 编写简单的查询语句 排序 按照一定的顺序排序结果集 用于排序结果集的关键字是 orderby 具体语法格式如下 select distinct 列名 表达式 列别名 from表名where条件orderby列名 表达式 别名asc desc例如 查询图书的类型和价格按价格的降序排序selecttitle id type pricefromtitleswheretype business orderbypricedesc1 语法解释 orderby后可写列名 表达式或者是列的别名 asc为升序排序 是默认的排序方式 desc为降序 列的别名是很有用排序参照 表达式也同样很有用具体的操作见后面的实例 2 排序规则 以升序为例 1 数字小值在前 大值在后 数字按由小到大顺序 2 日期排序按较早时间在前 较晚时间在后 3 字符按字母由小到大的顺序即A Z a z 中文按字典顺序 4 NULL值升序在最后 降序在最前 3 orderby的特殊使用 1 orderby子句后的列可以是Select子句中没有出现过的 2 orderby子句的列名可以用数字来代替 这个或这些数字是select语句后列的顺序号 4 注意 多排序字段 分为主 次排序字段 先按照主排序字段排序 当主排序字段无法派序 出现重复值 再按照次排序字段排序 越靠近orderby关键字的为主排序字段 第二部分 编写简单的查询语句 简单查询的综合实例 1 查询所有没有定价的图书信息 包括书名 类型 出版日期信息 2 查询business类型 而且在1991年6月出版 价格低于等于10或者高于等于15的图书信息 包括书名 出版日期 价格信息 3 显示员工的基本信息 按员工工资的降序排列 但员工的工资不显示 4 查询所有图书信息 包括书名 价格 类型和出版日期信息5 查询工资高于150 小于200的员工信息 包括姓名 工资 雇用日期6 查询1994年之前销售的图书信息7 查询所有没有定价的图书信息8 查询business类型图书信息 按照图书价格降序排序9 查询所有销售量超过20的销售记录信息 包括图书号 销售日期 销售量 按照销售日期降序排序罗列10 查询business类型图书信息 按照图书类型升序 图书价格降序排序11 查询并计算所有图书打9折之后的价格信息 12 查看员工信息 按员工工资的降序排列 13 查看员工的信息按月薪总额的升序排序 14 查看图书的销售信息包括订单号 销售日期 销售总量 按销售日期的升序 销售总量的降序排列 第三部分 Oracle中常用的单行函数和分组查询 Oracle中的单行要对表中每一行记录进行操作而且只返回一个结果语法如下1 函数名 参数列表 其中参数可以是 用户自定义变量 PL SQL 变量 列名 表达式2 特征 对单行进行操作 返回一个结果 返回值可能与参数类型不一致 有些函数没有参数 没有参数的函数不能有小括号 有些可能由一个或多个参数 函数可以嵌套 单行函数可写在select where orderby子句中 函数 函数执行 第三部分 Oracle中常用的单行函数和分组查询 3 单行函数可分为 字符函数 数字函数 日期函数 转换函数 通用函数 转换 字符 数字 日期 通用 单行函数 第三部分 Oracle中常用的单行函数和分组查询 4 字符函数 字符函数 LOWERUPPERINITCAP CONCATSUBSTRLENGTHINSTRLPAD RPADTRIMREPLACE 大小写处理函数 字符处理函数 第三部分 Oracle中常用的单行函数和分组查询 1 大小写处理函数 这类函数改变字符的大小写 例如 查询Lname为accorti的员工的信息 select fromemployeewherelname accorti 没有结果select fromemployeewherelower lname accorti selectinitcap IloveSQL fromdual selectupper china fromdual DUAL是一个 伪表 可以用来测试函数和表达式 函数 结果 LOWER SQLCourse UPPER SQLCourse INITCAP SQLCourse sqlcourseSQLCOURSESqlCourse 第三部分 Oracle中常用的单行函数和分组查询 2 字符处理函数如 selectconcat fname lname name job id length lname instr lname a Contains a fromemployee CONCAT Hello World SUBSTR HelloWorld 1 5 LENGTH HelloWorld INSTR HelloWorld W LPAD salary 10 RPAD salary 10 TRIM H FROM HelloWorld REPLACE thanks s you HelloWorldHello106 2400024000 elloWorldThankyou 函数 结果 第三部分 Oracle中常用的单行函数和分组查询 5 数字函数ROUND 四舍五入ROUND 45 926 2 45 93TRUNC 截断TRUNC 45 926 2 45 92MOD 求余MOD 1600 300 100 1 round函数 selectround 45 926 2 round 45 926 0 round 45 926 1 fromdual 2 trunc函数 selecttrunc 45 926 2 trunc 45 926 trunc 45 926 2 fromdual 3 mod函数 selectename sal mod sal 100 fromemp 第三部分 Oracle中常用的单行函数和分组查询 6 日期函数 在Oracle中 日期类型对于格式要求很严格 默认的日期格式是 DD MON RR 其中DD表示代表本月中第几天 MON代表月份 英文前3位 中文是数字 月 RR代表两位年份 RR也支持4位年分 1 日期类型的算术运算 A 日期型列或者表达式可以加减数字 功能是在该日期上加减对应的天数 如 15 4月 07 10结果是 25 4月 07 B 日期型列或表达式之间可以进行减操作 功能是计算两个日期隔了多少天 如 25 4月 07 15 4月 07 结果是10 2 日期型函数对日期变量或表达式进行操作 完成各种日期类型的运算工作 常用的函数有 SYSDATE MONTHS BETWEEN ADD MONTHS NEXT DAY LAST DAY ROUND TRUNC EXTRACT 1 SYSDATE函数 用来返回当前数据库服务器的系统时间 返回日期类型 如selectsysdatefromdual 2 MONTHS BETWEEN函数 主要计算两个日期之间隔多少月包括两个参数都是日期型数据 如selectmonths between 15 4月 2007 15 2月 2007 fromdual 3 ADD MONTHS函数 在指定的日期上加上月数 获得月数以后的时间 包括两个参数如 selectadd months 15 4月 2007 2 fromdual 第三部分 Oracle中常用的单行函数和分组查询 4 NEXT DAY函数 返回某一个日期的下一个指定日期 包含两个参数 第一个是指定的日期或列名 第二个参数是字符型数字或星期几 如 selectnext day sysdate 星期一 fromdual 5 LAST DAY函数 用来求指定日期的当月的最后一天日期 包含一个日期型参数 如selectlast day sysdate fromdual 6 ROUND函数 对具体的日期进行四舍五入的操作 具体的按YEAR MONTH DAY进行 如 selectround sysdate MONTH fromdual 7 TRUNC函数 对指定的日期进行截断 具体的按YEAR MONTH DAY进行操作 如selecttrunc sysdate year fromdual 如果按年截断表示当前年的第一天 按月截断表示当前月的第一天 8 EXTRACT函数 返回从日期类型中取出指定的年月日等 返回数字 如selectextract yearfromsysdate fromdual selectextract monthfromsysdate fromdual selectextract dayfromsysdate fromdual 第三部分 Oracle中常用的单行函数和分组查询 7 转换函数 在Oracle中很多情况下要进行数据类型的转换 比如在数据库中日期型数据格式默认是 dd mon rr 但是实际中日期型数据是多种多样的 而默认的只有一种 因此要通过类型转换来实现这些功能 数据类型的转换在Oracle中有两种方式 即隐性转换和显性转换 1 隐性转换 在数据操库操作过程中 很多时候数据类型转换是在不知情的情况下由Oracle自动完成 经常在赋值语句和表达式中使用 赋值语句中实现隐性转换的数据类型及方向如下 1 变长和定长字符型转数字类型select20 30 fromdual select fromempwheredeptno 10 2 变长和定长字符型转为日期型selectsysdate 30 fromdual 3 数字类型转为变长字符类型select Hello 30fromdual 4 日期类型转为变长字符类型select Nowthetimeis sysdatefromdual 在实际应用中不建议大家使用隐性转换 因为隐性转换可读性差 而且Oracle的版本还可能改变 从执行效率上看隐性转换的效率不高 第三部分 Oracle中常用的单行函数和分组查询 2 显性转换 在Oracle中通常字符类型 日期类型和数字类型转换用显性转换实现 主要有3个显性函数来完成相应的功能 1 TO CHAR函数 主要完成将日期型和数字型数据转换城变长字符类型 A 将日期转换为变长字符型 在Oracle中默认的日期显示格式是固定的 要想显示其他格式如年 月 日需要用这个函数 例如 selectord date to char ord date yyyy mm dd fromsales 日期的格式有多种 YYYY 4位数字表示年份YY 2位数字表示年份 无世纪转换RR 2位数字表示年份 有世纪转换YEAR 年份的英文拼写MM 2位数字表示月份MONTH 月份的英文拼写DY 表示星期的英文前三位DAY 表示星期的英文拼写 D 表示一个星期的第几天DD 表示一个月中的第几天DDD 表示一年中的第几天 RR和YY格式的区别 RR可以根据年份自动进行世纪转换 而YY不能根据世纪变更而转换 这样就造成混乱 如2000年1月1日出生的孩子 如果采用RR格式认为是1900年1月1日出生的 练习测试两个格式的区别 第三部分 Oracle中常用的单行函数和分组查询 当前年1995199520012001 日期27 8月 9527 8月 1727 8月 1727 8月 95 RR格式1995201720171995 YY格式1995191720172095 当前的年份 0 49 0 49 50 99 50 99 返回当前世纪 返回上一个世纪 返回当前世纪 指定的年份 RR与YY日期格式 返回下一个世纪 RR日期格式操作方式 第三部分 Oracle中常用的单行函数和分组查询 时间的格式 AM或PM表示上下午HH或HH12或HH24表示小时MI 表示分钟SS 表示秒特殊格式 TH表示英文序数词 如DDTH表示天数的序数词 SP表示数字的英文拼写 SPTH表示数字的序数词的拼写 字符串 如果在格式中显示字符串两端要加双引号 练习 1 显示员工的姓名 入职时间 其中入职的时间的格式为年 月 日 2 查询1998年11月出版的图书的信息 包括书号类型出版日期和价格 3 RR和YY格式的区别selectto char sysdate dd mon yyyy todayfromdual selectto char to date 12 2月 96 dd mon yy dd mon yyyy yy96 to char to date 12 2月 96 dd mon rr dd mon yyyy rr96 to char to date 12 2月 08 dd mon yy dd mon yyyy yy08 to char to date 12 2月 08 dd mon rr dd mon yyyy rr08fromdual 注意 有些格式只有在英文环境下才好用 将当前回话修改成英文模式的命令是 altersessionsetnls language american 当断开连接时命令失效 如 Selectord num qty to char ord date Day the ddspth of YYYYHH24 MI SS ord datefromsaleswheretitle id BU1032 第三部分 Oracle中常用的单行函数和分组查询 2 将数字转换成字符型数据 具体格式如下 9 表示一位数字0 表示一位数字或前导0 表示美元符号 L 表示按区域设置本地货币符号 表示小数点 表示千位分割符如 selecttitle id price to char price 7 5 L999 00 fromtitleswheretype business 3 TO NUMBER和TO DATE函数 TO CHAR函数的逆操作 A TO NUMBER函数将字符型数字转换成数字类型 转换时也可以带格式 格式同上 如 selectto number 333 fromdual selectto number 333 12 999 00 fromdual B TO DATE函数主要功能把字符类型转成日期类型 格式和TO CHAR格式相同 显式数据类型转换 NUMBER CHARACTER TO CHAR 第三部分 Oracle中常用的单行函数和分组查询 8 通用函数 通用函数主要是与null相关的函数完成对null值的操作 1 nvl函数 selectempno ename sal nvl comm 0 incomefromemp 2 nvl2函数 selectempno ename nvl2 comm sal comm sal fromemp 3 nullif函数 完成两个参数的比较 当两个参数不等是返回第一个值 当两个参数相等时返回null selectnullif ddd fff fromdual 4 coalesce函数 对nvl函数的扩展 参数个数不受限制 返回第一个不为空的值 第三部分 Oracle中常用的单行函数和分组查询 在SQL语句中使用IF THEN ELSE逻辑有两种方法 CASE表达式 DECODE函数1 CASE表达式 1 语法 CASEexprWHENcomparison expr1THENreturn expr1 WHENcomparison expr2THENreturn expr2WHENcomparison exprnTHENreturn exprnELSEelse expr END例如 SELECTtitle id title type price CASEtype when business then 商务类 when mod cook then 美食类 when trad cook then 传统饮食 when UNDECIDED then 确定中 else 理工类 ENDFROMtitles 第三部分 Oracle中常用的单行函数和分组查询 2 练习 公司根据最近的部门的业绩情况决定给10号部门的员工奖金数额为200 20号部门员工为260 30号部门由于业绩太差决定扣发奖金 查询员工的工号 姓名 工资 奖金 selectempno ename sal commcasedeptnowhen10thennvl comm 0 200when20thennvl comm 0 260when30thennvl comm 0 0endnew commfromemp 3 独立练习 10号部门员工名后加空格和小写字母a 20号部门员工名后加空格和小写字母r 30号部门员工名后加空格和小写字母s 第三部分 Oracle中常用的单行函数和分组查询 2 DECODE函数 1 语法 DECODE col expression search1 result1 search2 result2 default 例如 selectempno ename sal comm decode deptno 10 nvl comm 0 200 20 nvl comm 0 260 30 nvl comm 0 0 new commfromemp 练习 将上页 3 用decode函数改写 小练习 1 显示系统时间2 查询员工号 姓名 工资 以及工资提高百分之20 后的结果 newsalary 3 将员工的姓名按首字母排序 并写出姓名的长度 length 4 查询各员工的姓名 并显示出各员工截止1990年之前在公司工作的月份数 worked month 5 查询员工的姓名和工资 按下面的形式显示NAMESALARYSMITH 8006 查询员工的姓名 以及截止1990年之前在公司工作的月份数 worked month 并按月份数降序排列7 做一个查询 产生下面的结果earnsmonthlybutwantsDreamSalarySMITHearns 800monthlybutwants 2400 8 做一个查询 产生下面的结果NAMEHiredatereviewSMITH17 12月 19801980年12月17日星期三9 使用decode函数 按照下面的条件 jobgradePRESIDENTAANALYSTBMANAGERCSALESMANDCLERKE产生下面的结果ENAMEJOBGRADEkingPRESIDENTA10 将第9题的查询用case函数再写一遍 第三部分 Oracle中常用的单行函数和分组查询 小结通过单行函数部分学习 您应该学会 使用函数对数据进行计算 使用函数修改数据 使用函数控制一组数据的输出格式 使用函数改变数据的显示格式 使用函数改变数据类型 使用NVL函数 使用IF THEN ELSE逻辑 第三部分 Oracle中常用的单行函数和分组查询 分组查询 分组查询是对表中一组记录进行操作 每组只返回一个结果 即首先对表中的记录进行分组 然后作汇总操作 每组返回一个结果 分组是根据条件可能分成多组 也可能整个表份一个组 在实际工作中人们更加关心的是分组汇总后的记录 如 公司中谁的工资最高 部门平均工资 公司中程序员有多少人 以及公司员工总人数等等 完成这些功能的函数称为分组函数或聚合函数 如图1 分组函数 聚合函数 分类 1 min 功能求最小值 2 max 求最大值 3 sum 求总和函数 4 avg 求平均值 5 count 返回每组记录的条数 6 count 包含空值记录的条数 EMPLOYEES 表EMPLOYEES中的工资最大值 第三部分 Oracle中常用的单行函数和分组查询 1 对数值型数据使用avg sum max和min函数 min和max函数可以应用任何数据类型 例如 SELECTAVG sal MAX sal MIN sal SUM sal FROMempWHEREdeptno 10 2 count函数 1 count 求表中记录总数 例如求emp表中员工总数selectcount fromemp 例如 求10号部门员工数 selectcount fromempwheredeptno 10 2 count column name 求表中该列非空的记录数 selectcount price fromtitles 3 count distinctcolumn name 求表中非空的且不重复的记录数例如 求emp表中员工共几个部门中工作 selectcount distinctdeptno fromemp 3 分组函数忽略空值 在分组函数中使用nvl函数则不能忽略空值 如 selectavg price avg pricefromtitles 如 selectavg nvl price 0 avg pri