二Oracle学习笔记.pdf

OracleOracle 学习笔记学习笔记 JavaJava 相关课程系列笔记之二相关课程系列笔记之二 笔记内容说明笔记内容说明 Oracle 数据库 SQL 薛海璐老师主讲 占笔记内容 100 1 目目 录录 一 数据库介绍 1 1 1 表是数据库中存储数据的基本单位 1 1 2 数据库标准语言 1 1 3 数据库 DB 1 1 4 数据库种类 1 1 5 数据库中如何定义表 1 1 6 create database dbname 的含义 1 1 7 安装 DBMS 1 1 8 宏观上是数据 database 1 1 9 远程登录 telnet IP 地址 1 1 10 TCP IP 通信协议 2 1 11 数据库建连接必须提供以下信息 2 1 12 一台机器可跑几个数据库 主要受内存大小影响 2 1 13 源表和结果集 2 1 14 几个简单命令 2 1 15 tarena 给 jsd1304 授权 2 1 16 课程中使用的 5 个表 3 二 select from 语句 5 2 1 select 语句功能 5 2 2 select 语句基本语法 5 2 3 列别名 5 2 4 算术表达式 5 2 5 空值 null 的处理 5 2 6 nvl p1 p2 函数 5 2 7 拼接运算符 6 2 8 文字字符串 6 2 9 消除重复行 6 2 10 其他注意事项 6 三 SQL 语句的处理过程 7 3 1 SQL 语句处理过程 7 3 2 处理一条 select 语句 7 四 where 子句 8 4 1 where 子句后面可以跟什么 8 4 2 语法和执行顺序 8 4 3 字符串是大小写敏感的 在比较时严格区分大小写 8 4 4 where 子句后面可以跟多个条件表达式 8 4 5 between and 运算符 8 4 6 in 运算符 多值运算符 8 4 7 like 运算符 9 4 8 is null 运算符 9 4 9 比较和逻辑运算符 单值运算符 9 4 10 多值运算符 all any 9 2 4 11 运算符的否定形式 9 五 order by 子句 10 5 1 语法和执行顺序 10 5 2 升降序 10 5 3 null 值在排序中显示 10 5 4 order by 后面可以跟什么 10 5 5 多列排序 10 六 单行函数的使用 11 6 1 数值类型 11 6 2 日期类型 11 6 3 字符类型 13 6 4 转换函数 14 6 5 其他注意事项 14 七 SQL 语句中的分支 15 7 1 分支表达式 15 7 2 分支函数 15 八 组函数 16 8 1 报表统计常用 16 8 2 缺省情况组函数处理什么值 16 8 3 当组函数要处理的所有值都为 null 时 16 8 4 行级信息和组级信息 16 九 group by 子句 17 9 1 语法和执行顺序 17 9 2 分组过程 17 9 3 常见错误 17 9 4 多列分组 17 十 having 子句 18 10 1 语法和执行顺序 18 10 2 执行过程 18 10 3 where 和 having 区别 18 十一 非关联子查询 19 11 1 语法 19 11 2 子查询的执行过程 19 11 3 常见错误 19 11 4 子查询与空值 19 11 5 多列子查询 20 十二 关联子查询 21 12 1 语法 21 12 2 执行过程 21 12 3 exists 21 12 4 exists 执行过程 21 12 5 not exists 22 12 6 not exists 执行过程 22 12 7 in 和 exists 比较 22 3 十三 多表查询 23 13 1 按范式要求设计表结构 23 13 2 多表连接的种类 23 13 3 交叉连接 23 13 4 内连接 23 13 5 外连接 25 13 6 非等值连接 27 13 7 表连接总结 27 十四 集合 28 14 1 表连接主要解决的问题 28 14 2 集合运算 28 14 3 集合运算符 28 14 4 子查询 连接 集合总结 29 十五 排名分页问题 30 15 1 什么是 rownum 30 15 2 where rownumdatabase 开发流程 create tabale DML TCL DQL select 1 9 远程登录 telnet IP 地址 sql developer 在 linux 系统 连接 database 在 solaris 系统 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 2 1 10 TCP IP 通信协议 两台机器上的两个应用程序要通信 必须依赖网络 依赖 TCP IP 通信协议 IP IP 协议包中提供要连接机器的 IP 地址 用于标识机器 TCP TCP 协议包中提供与机器上的哪个具体应用程序通信 通过端口号实现 oracle 数据库服务缺省端口为 1521 用于标识 Oracle 此数据库应用 1 11 数据库建连接必须提供以下信息 ip 地址 确认机器 port 号 确认进程 程序 确认 Oracle SID 一个端口可以为多个 oracle 数据库提供监听 因此还需要提供具体的数据库名 确 认数据库里的哪个数据库 username password 要想访问数据库 必须是该数据库上一个有效的用户 确认身份 1 12 一台机器可跑几个数据库 主要受内存大小影响 1 13 源表和结果集 源表 被查询的表 结果集 select 语句的查询结果 1 14 几个简单命令 show user 查看当前用户 desc 表名 查看表结构 drop table 表名 purge 删除表 Oracle 中删除表不是真正的删除 而是占空间的移动到别 的地方 因为为了不占空间 真正的删除需要用 purge delete from 表名 删除表中所有值 若加上 where 列名 value 则删除某列中的值 1 15 tarena 给 jsd1304 授权 connect tarena tarena grant select on account to jsd1304 grant select on service to jsd1304 grant select on cost to jsd1304 jsd1304 select tarena 的表 connect jsd1304 jsd1304 create synonym 创建同义词 create synonym account for tarena account create synonym service for tarena service create synonym cost for tarena cost 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 3 1 16 课程中使用的 5 个表 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 4 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 5 二 二 select from 语句语句 2 1 select 语句功能 1 投影操作 结果集是源表中的部分 列 2 选择操作 结果集是源表中的部分 行 3 选择操作 投影操作 结果集是源表中的部分 行 部分 列 4 连接操作 join 多表查询 结果集来自多张表 把多张的记录按一定条件组合起来 2 2 select 语句基本语法 1 select colname 列名 from tabname 表名 2 select 中指定多个列名 则用 逗号 分隔 select colname1 colname2 from tabname 3 号表示所有列 select from tabname 4 select 语句 可有多个子句 5 select 子句 投影操作 列名 列表达式 函数 from 子句等 2 3 列别名 1 给列起一个别名 能够改变一个列 表达式的标识 2 不写的话默认都是转成大写 3 适合计算字段 4 在原名和别名之间可以使用 as 关键字 5 别名中包含空格 特数字符或希望大小写敏感的 用 双引号将其括起来 2 4 算术表达式 在 number 类型上使用算术表达式 加减乘除 eg 一个月使用了 250 小时 每种资费标准下应缴纳的费用 首次实现 select base cost 250 base duration unit cost fee from cost 2 5 空值 null 的处理 未知的 没写数 1 空值不等于 0 2 空值不等于空格 3 在算术表达式中包含空值导致结果为空 4 在算术表达式中包含空值需要用空值转换函数 nvl 处理 2 6 nvl p1 p2 函数 空值转换函数 1 两个参数类型要一致 2 参数的数据类型可以是数值 number 字符 character 日期 date 3 但 null 转成字符串 null 也要用 to char 转化 4 实现过程 if p1 is null then return p2 elase return p1 end if 5 实现空值转换 null 非 null 值 0 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 6 eg 一个月使用了 250 小时 每种资费标准下应缴纳的费用 再次实现 select nvl base cost 0 250 nvl base duration 0 nvl unit cost 0 fee from cost 2 7 拼接运算符 表达字符 串 的拼接 可以将某几列或某列与字符串拼接在一起 select colname1 colname2 from tabname 2 8 文字字符串 select 语句后面可以包含的文字值 字符 表达式 数字 1 字符常量 或字符串 必须用 单引号括起来 作为 定界符 使用 2 表达单引号本身 需要两个单引号 1 4 定界 2 3 表单引号 3 对于文字值每行输出一次 eg 显示客户姓名的身份证号是 select real name s IDCARD NO is idcard no cilent from account 4 函数转换大小写 尽量在进入数据时操作 2 9 消除重复行 distinct 去重复行 对整条记录返回的结果去重 不是对后面的某个列去重 若后面有 多列 则所有列联合起来唯一 即每列的值都可以重复 但组合不能重复 eg1 哪些 unix 服务器提供远程登录业务 select distinct unix host from service eg2 每一台 unix 服务器在哪些天开通了远程登录业务 select distinct unix host create date from service 2 10 其他注意事项 1 调常量时用单行单列的 dual 表 系统提供的表 2 invalid identifier 无效标识名 列名不 3 table or view does not exist 表名不对 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 7 三 三 SQL 语句的处理过程语句的处理过程 3 1 SQL 语句处理过程 用户进程 sqlplus 建立连接 服务进程 Server process oracleSID 创建会话 Oracle server 3 2 处理一条 select 语句 1 分析语句 搜索是否有相同语句 用 hash value 计算 select 语句是否长得一样 大小写 关键字 空格要都一样 不 一样则为两条语句 则服务进程会重新分析 若为统一语句 则直接从内存拿执行计划 计 算结果 检查语法 表名 权限 在分析过程中给对象加锁 生成执行计划 2 绑定变量 给变量赋值 3 执行语句 4 获取数据 将数据返回给用会进程 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 8 四 四 where 子句子句 用 where 子句对表里的记录进行过滤 where 子句跟在 from 子句后面 4 1 where 子句后面可以跟什么 跟条件表达式 列名 常量 比较运算符 单 多值运算符 文字值 不能跟组函数 不能跟列别名 注意事项 对列不经过运算的条件表达式效率会更高 建议在写 where 子句时尽量 不要对列进行运算 eg 一年的固定费用为 70 8 元 计算年包在线时长 select base duration 12 ann duration from cost where base cost 12 70 8 没下面效率高 select base duration 12 ann duration from cost where base cost 70 8 12 4 2 语法和执行顺序 语法顺序 select from where 执行顺序 from where select 4 3 字符串是大小写敏感的 在比较时严格区分大小写 1 upper 函数将字符串转换成大写 2 lower 函数将字符串转换成小写 3 initcap 函数将字符串转换成首字符大写 是将列中的值大小写转换然后去和等号 后的字符串比 而不是把转字符串转换去和列比 eg 哪些 unix 服务器上开通了 os 帐号 huangr select unix host os username from service where os username huangr 有结果 where lower os username HUANGR 无结果 where lower os username huangr 有结果 where upper os username HUANGR 有结果 4 4 where 子句后面可以跟多个条件表达式 条件表达式之间用 and or 连接 也可用 改变顺序 4 5 between and 运算符 表示一个范围 是闭区间 含义为大于等于并且小于等于 eg 哪些资费的月固定费用在 5 元到 10 元之间 select base duration base cost unit cost from cost where base cost 5 and base cost 10 where base cost between 5 and 10 4 6 in 运算符 多值运算符 表示一个集合 是离散值 含义为等于其中任意一个值 等价于 any eg 哪些资费的月固定费用是 5 9 元 8 5 元 10 5 元 select base duration base cost unit cost from cost where base cost 5 9 or base cost 8 5 or base cost 10 5 where base cost in 5 9 8 5 10 5 where base cost any 5 9 8 5 10 5 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 9 4 7 like 运算符 在字符串比较中 可用 like 和通配符进行模糊查找 1 通配符 表示 0 或多个字符 表示任意 一个 字符 要占位的 注意事项 若要查找 和 本身 则需要 escape 进行转移 eg 哪些 unix 服务器上的 os 帐号名是以 h 开头的 select os username from service where os username like h eg 哪些 unix 服务器上的 os 帐号名是以 h 开头的 select os username from service where os username like h escape 4 8 is null 运算符 测试 null 值需要用 is null 1 null 不能用等于号 和不等于号 跟任何值比较 包括它自身 所以不能用 和 来测试是否有空值 2 即 null null 是不成立的 null 不等于 null 也不成立 null 和任何值比较都不成立 eg 列出月固定费用是 5 9 元 8 5 元 10 5 元或者没有月固定费 select base duration base cost unit cost from cost where base cost in 5 9 8 5 10 5 null 错误 where base cost in 5 9 8 5 10 5 or base cost is null 正确 4 9 比较和逻辑运算符 单值运算符 1 比较运算符 all 大于所有的 等价于 select max 2 any 大于任意的 等价于 select min 4 11 运算符的否定形式 1 比较运算符 2 SQL 比较运算符 not between and not in not like is not null 注意事项 in 相当于 or or or 等价于 any not in 等价于 and and and 等价于all not between and 小于下界 or 大于上界 集合中有 null 对 in 无影响 但对 not in 有影响 有一个就没有返回值 eg 哪些资费信息的月固定费用不是 5 9 元 8 5 元 10 5 元 select base duration base cost unit cost from cost where nvl base cost 0 5 9 and nvl base cost 0 8 5 and nvl base cost 0 10 5 where nvl base cost 0 not in 5 9 8 5 10 5 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 10 五 五 order by 子句子句 select 语句输出的结果安记录在表中的存储顺序显示 order by 子句能够改变记录的输出 顺序 order by 子句对查询出来的结果集进行排序 即对 select 子句的计算结果排序 5 1 语法和执行顺序 语法顺序 select from where order by 执行顺序 from where select order by 5 2 升降序 ASC 升序 可以省略 默认值 DESC 降序 order by nvl base cost 0 order by unix host create date desc 注意事项 order by 是 select 语句中最后一个子句 5 3 null 值在排序中显示 1 被排序的列如果包含 null 值 用 ASC 方式 null 值的在最后 2 用 DESC 方式 null 在最前面 5 4 order by 后面可以跟什么 可以跟列名 列别名 列位置 数字 表达式 函数 order by 1 表示列位置为 1 的列 select 1 from 表示常量 1 eg 按年固定费用从大到小的顺序显示资费信息 方式一 select id base cost 12 ann cost base duration ann duration from cost order by base cost desc 方式二 select id base cost ann cost base duration ann duration from cost order by base cost 12 desc 排序的效果和上面是一样的 但前一个效率高 5 5 多列排序 order by 子句后面可以跟多列 而 order by 后面的列可以不出现在 select 后面 结果集先 按第一列升序排列 若列值一样 再按第二列降序排列 eg 按 unix 服务器 ip 地址升序 开通时间降序显示业务帐号信息 select id unix host os username create date from service order by unix host create date desc 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 11 六 六 单行函数的使用单行函数的使用 SQL 函数的两种类型 单行函数 多行函数 组函数 单行函数 数值类型 日期类型 字符类型 转换函数 处理一列数据 返回一个结果 6 1 数值类型 1 定义 create table tabname c1 number c2 number 6 c3 number 4 3 c4 number 3 3 c5 number 2 4 2 数值类型说明 number 不写数值 表可写 38 位数 number 6 6 位整数 999999 1 999999 number 4 3 数字 4 位 小数点占 3 位 四舍五入 1 234567 1 235 number 3 3 小数点前三位不写数 四舍五入 然后有效位 3 位 1234 1000 number 2 4 小数点后 4 位 有效位 2 位 0 00991 0 0099 3 数值函数 参数类型为 number round 四舍五入函数 缺省转成数字 也可对日期 trunc 截取函数 不管多大值直接舍去 也可对日期 eg round 和 trunc round 45 923 2 45 92 round 45 923 0 46 round 45 923 1 50 trunc 45 923 2 45 92 trunc 45 923 45 trunc 45 923 1 40 6 2 日期类型 1 Oracle 用 7 个字节来存储日期和时间 世纪 年 月 日 时 分 秒 Date 不存在 定宽度 就是 7 个字节 2 缺省 默认 日期格式为 DD MON RR 格式敏感 3 sysdate 是一个系统函数 返回当前系统时间和日期 4 改变 session 会话 中的日期格式 session 和 connection 是同时建立的 两者是对 同一件事情的不同层次的描述 connection 是物理上的客户机同服务器端的通信链路 session 是逻辑上的用户同服务器的通信交互 SQL 语句的运行环境 eg 显示的日期包含世纪 年 月 日 时 分 秒 alter session set nls date format yyyy mm dd hh24 mi ss 5 日期格式 yyyy 用数字表达的四位年 2013 年 mm 用数字表达的两位月 01 月 dd 用数字表达的两月日 01 日 hh24 用数字表达的 24 进制的小时 20 点 h12 用数字表达的 12 进制的小时 8 点 mi 用数字表达的分钟 30 分 ss 用数字表达的小时 30 秒 D 用数字表达的一周内的第几天 周日 1 day 用全拼表达的星期几 sunday month 用全拼表达的月 march mon 用简拼表达的月 mar 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 12 eg 案例 select to char sysdate DDD from dual 年中的第几天 select to char sysdate DD from dual 月中的第几天 select to char sysdate D from dual 星期中的第几天 6 在数据库中如何处理日期类型 create table test c1 date insert into test values 01 JAN 08 insert into test values 2008 08 08 报错 insert into test values to date 2008 08 08 yyyy mm dd select c1 from test select to char c1 yyyy mm dd from test 在 create table 中定义日期类型 date 时一定不能指定宽度 日期在数据库中用固定的 7 个 字节存储 表示世纪 年 月 日 时 分 秒 缺省的日期格式为 DD MON RR 01 JAN 08 符合缺省日期格式可以插入表中 因为系统会自动调用 to date 函数将它转成日期 2008 08 08 插入时报错 原因是不符合缺省格式 需要手工使用函数 to date 对字符串的 格式进行说明 如 2008 08 08 的格式说明串为 yyyy mm dd select 时日期按缺省日期格式显 示 若用指定日期格式 需要使用 to char 函数 7 日期与字符串相互转换 to date char date 函数 将字符串转换成一个日期值 对应 java 中 parse to char date char 函数 第一个参数为要处理的日期 第二个参数为格式 可获取一 个日期的任意一部分信息 对应 java 中 format eg 创建一张表 包含 date 类型的列 插入 2008 年 8 月 8 日 8 点 8 分 8 秒并显示 insert into test values to date 2008 08 08 08 08 08 yyyy mm dd hh24 mi ss select to char c1 yyyy mm dd hh24 mi ss from test 注意事项 格式必须用单引号括起来 并且大小写敏感 必须是有效的日期格式 fm 能去掉前导 0 和两端的空格 对日期去重复问题 to char 获取当天日期即可 时分秒忽略 加上 distinct 即可做到 eg 案例 where to char create date mm 03 若等式右边写成 3 03 3 不成立 需要在 mm 前增加 fm where to char create date fmmm 3 where to number to char create date mm 3 若等式右边写成 3 03 3 成立 03 是字符类型 3 是数字类型 等式两边相 等 说明系统做了隐式数据转换 缺省做法将字符转化为 number 8 日期函数 参数类型为 date add months 一个日期加 减一个月 months between 两个日期之间相差多少个月 last day 同一个月的最后一天 next day 根据参数 出现下一个的日期 eg1 昨天 今天 明天 alter session set nls date format yyyy mm dd hh24 mi ss select sysdate 1 sysdate sysdate 1 from dual 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 13 eg2 十分钟之后 alter session set nls date format yyyy mm dd hh24 mi ss select sysdate sysdate 1 144 from dual eg3 每台 unix 服务器上的 os 帐号开通了多长时间 以天为单位 select unix host os username create date round sysdate create date days from service eg4 上个月的今天 今天 下个月的今天 alter session set nls date format yyyy mm dd hh24 mi ss select add months sysdate 1 sysdate add months sysdate 1 from dual eg5 当前月的最后一天 alter session set nls date format yyyy mm dd hh24 mi ss select last day sysdate from dual eg6 用户注册多长时间了 select trunc months between sysdate create date from service 6 3 字符类型 1 定义 create table tabname c1 char 10 c2 varchar2 10 2 char 和 varchar2 区别 varchar2 必须定义长度 按字符串的实际长度存 最大长度 4000 字节 更省空间 char 可以不定义长度 默认为 1 按定义长度存 最大长度 2000 字节 操作更快 列的取值是定长 定义成 char 类型 列的取值长度不固定 定义成 varchar2 注意事项 在字符串比较中 varchar2 按实际字符串比 对空格是敏感的 对大小些 敏感 char 会将短字符串补齐后 再与字符串比 对空格不敏感 varchar 类型是 ANSI 定义的 varchar2 类型是 Oracle 定义的 目前是等价 的 但如果 ANSI 对 varchar 类型定义有变化 则 Oracle varchar2 类型不变 eg 案例 varchar2 10 abc abc yes abc abc no char 10 abc abc yes abc abc yes 3 字符函数 参数类型为字符 upper 函数将字符串转换成大写 lower 函数将字符串转换成小写 initcap 函数将字符串转换成首字符大写 是将列中的值大小写转换然后去和等 号后的字符串比 而不是把转字符串转换去和列比 length 字符串的长度 rpad lpad 将字符补成同样长度 l 和 r 表左右 rtrim ltrim 压缩字符 l 和 r 表左右 与 fm 相同效果 concat 拼接函数与 相似 substr 求子串函数 eg 相关操作 select rpad FEBRARY 9 from dual where to char create date fmMONTH MARCH where rtrim to char create date MONTH MARCH select concat ab c from dual 从左往右 从右往左 select os username substr os username 1 2 substr os username 2 2 from service 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 14 6 4 转换函数 1 to number 函数 将字符 串 转换成 number 数值类型 这也是系统的缺省做法 即 to number 03 3 注意事项 若 to number 函数处理的字符串为 ab 则系统报错 若转换后的值是十 进制的 则要求字符串必须是数字字符 select to number ab from dual 报错 invalid number 2 to char date char 函数 第一个参数为要处理的日期 第二个参数为格式 可获取一 个日期的任意一部分信息 对应 java 中 format 3 函数格式说明 9 代表数字位 0 定义宽度大于实际宽度时 0 会被强制显示在前面 以补齐位数 美元符号 L 本地货币符号 小数点 每千位显示一个逗号 注意事项 如果显示位数不足 定义宽度小于实际宽度 用 代替 eg1 相关操作 select to char base cost L99 99 from cost select to char base cost L00 00 from cost select to char base cost 00 00 from cost eg2 显示月固定费用 单位费用 单位费用为 null 显示 no unit cost select base cost nvl to char unit cost no unit cost unit cost 3 to date char date 函数 将字符串转换成一个日期值 对应 java 中 parse 4 number 字符 data 间的转化 to char number 字符 date 字符 to number 字符 number to date 字符 date 5 显式隐式转换 隐式数据类型转换 系统调用转换函数 where create date like 3 隐式 显式数据类型转换 用户调用转换函数 where to char create date mm 03 显式 6 5 其他注意事项 1 insert into 表名 values 1 2 3 4 null 有多列时 插入值必须都写 没值的也要写 null 2 insert into 表名 C5 values 1234 表名最多 30 个字符且不能有特殊字符 3 alter session set nls language AMERICAN 28 MAY 13 alter session set nls language SIMPLIFIED CHINESE 28 5 月 13 4 alter session set nls territory AMERICA alter session set nls territory CHINA 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 15 七 七 SQL 语句中的分支语句中的分支 7 1 分支表达式 1 case when then 用于解决不同记录需要不同处理方式的问题 when 后面跟条件表 达式 当所有 when 条件都不满足时 若有 else 表达式的返回结果为其后的值 否则返回 null 值 2 寻找 when 的优先级 从上到下再多的 when 也只有一个出口 即其中有一个满足 了表达式 expr 就马上退出 case 3 else expr 和 return expr 的数据类型必须相同 eg 当月包在线时长为 20 小时 单位费用涨 5 分 为 40 小时涨 3 分 其他不变 用 CASE WHEN 实现 select base duration unit cost case when base duration 20 then unit cost 0 05 when base duration 40 then unit cost 0 03 else unit cost end new nuit cost from cost 7 2 分支函数 decode 是简版的 case when 1 decode value if1 then1 if2 then2 else 标识如果 value 等于 if1 时 返回 then1 如 果不等于任何一个 if 值 则返回 else eg 当月包在线时长为 20 小时 单位费用涨 5 分 为 40 小时涨 3 分 其他不变 用 decode 实现 select base duration unit cost decode base duration 20 unit cost 0 05 40 unit cost 0 03 unit cost n base cost from cost 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 16 八 八 组函数组函数 操作在一组行 记录 上 每组返回一个结果 8 1 报表统计常用 1 avg distinct all n 平均值 参数类型只能为 number 2 sum distinct all n 求和 参数类型只能为 number 3 count distinct all expr 计数 参数类型为 number 字符 date 4 max distinct all expr 最大值 参数类型为 number 字符 date 5 min distinct all expr 最小值 参数类型为 number 字符 date 注意事项 distinct 去重复时 会保留一个 select count distinct base duration from cost 4 distinct 保留一个空 但 count 统计时不算 count 不管 null 统计 记录 数 count 列名 返回的是列中非 null 值的数量 8 2 缺省情况组函数处理什么值 所有的非空值 8 3 当组函数要处理的所有值都为 null 时 count 函数返回 0 其他函数返回 null 8 4 行级信息和组级信息 返回的结果集包含多条记录 是行级信息 返回的结果集包含一条记录 是统计汇总信 息 是组级别的信息 两者不能同时显示出来 处理方式 将行级信息变成组标识或进行组函数处理 eg1 单位费用的总和 平均值 最大值 最小值个数 select sum unit cost sum1 avg unit cost avg1 max unit cost max1 min unit cost min1 count unit cost cnt from cost eg2 若 null 值参与运算 必须将 null 值转换成非 null 值 select avg nvl unit cost 0 sum unit cost count from cost eg3 若 unit cost 列中参与运算的数据都为 null avg unit cost 的函数值为 null count unit cost 的函数值为 0 select avg unit cost count unit cost from cost where unit cost is null eg4 每台 unix 服务器上开通的 os 帐号数即开户数 select unix host count os username from service group by unix host eg5 tarena26 192 168 0 26 上开通的 os 帐号数即开户数 select max unix host count os username cnt from service where unix host 192 168 0 26 用 min unix host 也可 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 17 九 九 group by 子句子句 将表中的记录进行分组 9 1 语法和执行顺序 语法顺序 select from where group by order by 执行顺序 from where group by select order by 9 2 分组过程 根据 group by 子句指定的表达式 将要处理的数据分成若干组 若有 where 子句即为通 过条件过滤后的数据 每组有唯一的组标识 组内有若干条记录 根据 select 后面的组函数 对每组的记录进行计算 每组对应一个返回值 9 3 常见错误 若没有 group by 子句 select 后面有一个是组函数 则其他都必须是组函数 记录 行 信息和组信息不能放一起 要么都是组函数 要么都是单行函数 若有 group by 子句 select 后面跟 group by 后面跟的表达式以及组函数 其他会报错 9 4 多列分组 包含多列用 分开 分组的个数多了 每组的记录少了 eg 根据 unix 服务器 ip 地址 开通时间统计开通的 os 帐号数即开户数 select unix host to char create date yyyymmdd create date count os username cnt from service group by unix host to char create date yyyymmdd 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 18 十 十 having 子句子句 对分组过滤 10 1 语法和执行顺序 语法顺序 select from where group by having order by 执行顺序 from where group by having select order by 10 2 执行过程 行被分组 将 having 子句的条件应用在每个分组上 只有符合 having 条件的组被保留 再应用 select 后面的组函数对每组的数据进行处理 10 3 where 和 having 区别 1 where 过滤的是行 记录 后面可跟任意列名 单行函数 不能跟组函数 无法对 应到具体记录 先执行 不允许用列别名 2 having 过滤的是分组 组标识 每组数据的聚合结果 后面只能包含 group by 后 面的表达式和组函数 能表达组信息的 后执行 不允许用列别名 eg1 哪些 unix 服务器开通的 os 帐号数即开户数多于 2 个 select unix host count os username cnt from service group by unix host having count os username 2 eg2 哪些 unix 服务器在哪几天的开户数多于 1 个 select unix host to char create date yyyymmdd create date count os username cnt from service group by unix host to char create date yyyymmdd having count os username 1 勿传网上 严禁谋利 Oracle 学习笔记 常彦博 19 十一 十一 非关联子查询非关联子查询 子查询就是在一条 SQL DDL DML TCL DQL DCL 语句中嵌入 select 语句 11 1 语法 select colname from tabname where expr operator select colname2 from subtabname 11 2 子查询的执行过程 先执行子查询 子查询的返回结果作为主查询的条件 再执行主查询 子查询只执行一 遍 若子查询的返回结果为多个值 Oracle 会自动去掉重复值后 再将结果返回给主查询 注意事项 不需要 distinct 会自动去重的 eg1 哪些 os 帐号的开通时间是最早的 select unix host os username create date from service where create date select min create date from service eg2 哪些 os 帐号的开通时间比 unix 服务器 192 168 0 26 上的 huangr 晚 select unix host create date os username from service where create date select create date from service where os username huangr and unix host 192 168 0 26 eg3 哪些 os 帐号的开通时间比 huangr 晚 多台 unix 服务器上都有名为 huangr 的 os 帐号 select unix host create date os username from service where create date all select create date from service wher