oracle学习总结（二）

1.每个服务器上只有telnet服务，只是说某个服务是看哪个客户在哪台服务器上开通的。一个客户开通多个服务就是在多个服务器上开通了服务。

2.一个服务可以被多个客户开，但是要注意，一个服务如果开通了，它肯定是只对应一个用户！
  就像一门课确实可以被多个老师教，但在某个学期里，某一门课就绝对只被一个老师教一样。
  注：2)一个客户即一个account_id，开通多个服务的话，可以用不同的os帐号
      3)一个服务即一台服务器上的某一个os帐号
      
3.where后面跟的是条件表达式
   列名、列的表达式(能不用尽量不用，因为性能会降低)、常量
   （跟列表达式 如：年固定费为70.9，计算年在线时长
     select base_duration*12 from cost where base_cost*12 = 70.9;
    )
   比较运算符
   注：where后面不能跟列的别名，因为select ... from ... where ...的执行顺序是
       from ... where ... select ... 也即先执行的where，在select里定义的
       别名会看成未定义。 
   
  where的执行过程：
  首先将表中的数据取出到集合中，然后循环遍历该集合，符合where后条件的记录保留，其他的
  条件过滤掉，然后再对保留下来的记录进行select投影操作，也是用到循环遍历的。
  
4.由于‘’表示字符串时是大小写敏感的，如:where name = 'HARRY'要想忽略大小写，可以用到三个函数:
  upper():将字符串全部转为大写的
  lower():将字符串全部转为小写的
  initcap():首字符大写
  
5.where子句跟多个条件表达式时，用and和or连接，也可以通过()来改变顺序
      
6.SQL提供一些比较运算符：
  1)between ... and ...（闭区间）：等价于 colname >= val1 and colname <= val2 
  2)in: where colname in(1, 2, 3)等于集合中的任意的一个值 等价于colname=1 or col=2 
    or colname=3; 也等价于 colname=any(1,2,3);  
  3）like：字符串模糊匹配
     %：表示0个或多个字符
     _：表示单个字符,必须有一个字符！！
     若想表示"_"和“%”本身，需要用escape转义
     如：列名以“S_”开头
     where colname like 'S\_%' escape '\';
     escape 后面的字符表示该字符后面的字符是要转义的，所以可以随便写，比如可以
     写成like 'S/_%' escape '/'      
     注：如果字符串进行全值比较，既可以用“=“也可以用like
        如where name = 'cs' 或者where name like ‘cs’都可以
  4)is null: where colname is null;表示列值为空
     注：没有 “=null“的写法
     
  小结：
  比较运算符：> < >= <= =
  SQL的比较运算符：between...and...  in   like
  逻辑运算符：and or not
 
  运算符的否定形式：
  比较运算符：！= 、<>
  SQL的比较运算符：not between...and...  not in(所有的都不相等)等价于<>all() 、
  not like、is not null
  注：1.在用not in的时候如果列的值为空，同样不会符合这个条件！可以使用nvl()函数转换后
      再比较！
      2.如果在集合中有null，对in()没什么影响，而对not in()没什么影响！
        因为任何值=null和<>null的返回结果都是false！            
 
7.ORDER BY子句：排序
  order by是select语句里的最后一个子句，其后只能跟select后面的表达式！所以可以跟列名，别
  名，表达式，函数还有位置位置就是数字来表示的，表示的是selcet后面所有的列名项的第几个
  如：
  select unix_host,id,account_id
  from service_lin
  order by 3;
  按照第三个即account_id进行升序排列
  当然也可以指定为降序的，order by 3 desc;


  order by后面可以跟别名，说明了order by子句是在select子句之后执行的
  因为只有先把数据取出来了才能进行排序
  如果是多列 order by 列1 asc(desc)，列2 asc（desc）；
  先按列1升序(降序)排列，列1相同，再按列2升序（降序）排列，不写的话
  还是默认的升序
  空值null在升序中放在最后面，降序中放在最前面！


8.单行函数：每一行都有运算结果
 1)数值类型的定义
   NUMBER:最多可以有38位
   NUMBER(7)等价于NUMBER(7, 0):最多有7位，如赋值999999.233  存储为999999
   NUMBER(6,2):保留到小数点后2位，并四舍五入处理，且最多可以保存6个数字位可以看出，整数位的
                 位数最多为4位(6-2)
                 如赋值12.3456，存储为12.35；如果赋值12345.35会报错
   NUMBER(2,-3):四舍五入到小数点前的第4位(十分位为1往右递增，个位为0，往左递
             减),除了那些0以外，最多可以有2个数字
             如赋值16765，存储为20000，如果赋值121765会报错
   NUMBER(2,4):四舍五入到小数点后第4位，且最多有2位数字位
                 如赋值0.009911，存储为0.0099；如果赋值0.123456会报错
          
 2) 四舍五入和去尾函数
round：
round（n,m）：将n这个数四舍五入到第m位，保留到个位（十分位为1往右递增，个位为0，往左递
减）
round（n）：等价于round(n, 0)，即四舍五入到个位
trunc（n，m）：将n这个数第m位后的数全部砍去，不进行四舍五入。
trunc(n)等价于trunc(n,0)

 3)日期类型数据的处理：to_date()和to_char()函数
  select sysdate FROM DUAL；
  // 注：sysdate是函数！
 session默认显示的日-月-年（省去世纪）如：02-7月-13
 alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
 以上改变session显示时间的格式
 alter session set nls_language = 'SIMPLIFIED CHINESE';
 以上改变session语言环境
 alter session set nls_territory = 'CHINA';
 以上改变地区
 
 日期的格式：
 1.yyyy:用4位数字表示年份
 2.mm:用2位数字表示月份
 3.dd:用2位数字表示天(一个月的第几天)
 4.hh24:用数字表示的24小时制的时
   hh12:用数字表示的12小时制的时
 5.mi:用数字表示的分
 6.ss:用数字表示的秒
 7.D:用数字表示的一周内的第几天(周日：1)
 8.D:用数字表示的一年内的第几天
 9.day:用全拼表达的星期几(如英文环境下的sunday)
 10.month:用全拼表达的月(如英文环境下的march)
 11.用简拼表达的月(如英文环境下的mar)
 
在进行数据的插入时，若符合系统默认格式的日期数据，可以成功的插入，否则，需要使用to_date（）方法来指定自己插入的日期数据的格式，然后用select查询时，按照默认的格式输出，如果想按照当初插入时的格式输出，需要用to_char（）函数处理
insert into test（c1） values(to_date('2013-07-02 14:34:36', 'yyyy-mm-dd hh24: mi:ss'));
select to_char(c1, 'yyyy-mm-dd hh24: mi:ss') from test;
注：1.to_char()方法中的格式前加‘fm’可以去掉前导O和两边的空格，
     where to_char(create_date, 'fmmm') = '3';
      (若不加前导fm，则等式右边只能写成‘03’)
    2.to_char()函数返回的是varchar2类型的，而不是char类型的。
    3.to_char()函数有个经典的用法，比如只想获取某个日期的年，或者月，或者日
      to_char(date, 'yyyy')，to_char(date, 'mm')，to_char(date, 'dd')，然后可以
       与数字类型的进行比较运算。
        


隐式转换：1.where to_number(to_char(create_date, 'mm')) = 3
             即to_number('03') = 3；
             注：to_number()方法要求字符串里的内容必须是数字！默认使用的十进制
                 的转换方式，也可以自己指定16进制的方式进行转换
               to(‘ab’, 'xx'); "xx"就对应的那个十六进制的数的各个数字，
                 转化结果为：10*16+11=171
           2.where create_date like '%MAY%';
               即where to_char(create_date) like '%MAY%'；
             to_char()如果第二个参数不写，则按照默认的格式转化
             3.系统也可能进行错误的隐式转换，如nvl(base_cost, 'No Value')
               base_cost是number类型的，由于nvl()函数要求两个参数类型必须一致，
               于是系统进行隐式类型转换，将varchar类型的转换为number(系统在碰到这两种类型
               的时候倾向于将varchar转为number)，于是就会报错，可以人为将number类型的转化
               为varchar型的，nvl(to_char(base_cost), 'No Value')
          
             
  附：char和varchar2类型
  1)varchar2必须定义长度，char可以不定义长度，默认长度1
  2)存储时varchar2按照字符串的实际长度存储，而char按照定义的长度存储
  3)如果列值是定长的，定义为char型的，变长的定义为varchar2型的
    如id号一般都是定长位的，名字都不定长的，每个人的名字长度都不一样
  4)在字符串比较时，varchar2按照实际字符串比较，所以对空格是敏感的；
    char是将短字符串补起后再与长字符串比较，所以对空格不敏感
  5)目前认为varchar2和varchar是等价的


4）几个小函数
   1)lpad(String s1, int n, char c)
     rpad(String s1, int n, char c):将String s1里的内容向左（右）填充成n表示的长度
      如rpad('zhang', 10, '*') --->  zhang*****
   2)rtrim(String s)
     ltrim(String s)
     trim(String s):将s左边(右边)(两边)的空格去掉
   3)length(String s):求长度
   
5）to_char()函数(续)：
    之前的to_char()函数将日期类型的转换为字符串类型的，此处不再说。
    也可以将数值类型的转换为字符串类型的，to_char(12) ---> '12'
    几种常见格式：
    1）9：数字位。如果定义长读大于实际长度，右边补零，左边不补，如果小于，则四舍五入截取。
         如果定义的整数位长度小于实际整数位长度，则用‘#’代替(小数点也替换)
       如to_char(123.45, '9999.999') ---> 123.450
        to_char(123.45, '9999.9') ---> 123.5
        to_char(123.45, '99.9')  ---> #####
    2）0：比‘9’就多了一个定义长度大于实际长度时，左右两边都补零。
    3）$：美元的货币符号
    4）L：本地的货币符号
    5），：每三个位数显示一个','
            


9.日期类型的运算
  A.普通运算
  1）对日期加减相当于多少天以后的日期，多少天以前的日期
  2）两个日期相减表示两个日期之间相差多少天
  
  B.日期函数
   1）months_between('日期d1', '日期d2'):日期d1和d2之间隔多少个月。返回值为number
    2）add_months('日期d', int n):d1加减一个n个月，正数为加，负数为减。返回值为date
    3）next_day('日期d', '星期几'):以d表示的时间为准，出现下一次‘星期几’的日期。返回值为
                                 date
                                      如next('2013-07-26', 'FRIDAY') ---> 2013-08-02
                                   next('2013-07-26', 'MONDAY') ---> 2013-07-29
    4)last_day(date d):和日期d同一个月的最后一天。返回值为date
 

10.SQL语句的分支

  1）case表达式：用于对不同的数据情况采取不同的处理方法
  既然是一个表达式，就有返回值，就像一个函数一样，
  case when 表达式1 then 返回值1
       when 表达式2 then 返回值2...
       else 返回值3 
  end 
  如：当月包在线时长为20小时，单位费用涨5分，为40小时涨3分，其它不变
  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 add_unit_cost
  from cost_bs;
  注：如果没有else，则返回值为空，所以else保证了非空值处理后返回值不为空。
  2）decode()函数：decode(colname1, value1, 返回值1，
                                   value2, 返回值2，... 
                                                 返回值3)
     表示当某一列等于value1时，函数返回值为返回值1，等于value2时，返回值为返回值2，其他
     情况下返回值为3，此处的最后一个返回值也不能省，否则返回值也为空。
     所以decode是一个简洁版的case表达式，它在case的表达式中都是colname=value时用。

 

11.单行函数小结

  数据类型：number、varchar2和char、date
  1）字符函数：参数类型 字符
     upper、lower、initcap、length、lpad(rpad)、trim(ltrim,rtrim)、contact(
     拼接)、substring(s, n1, n2):如果n1>0,则从左往右，n2表示第n2位；如果n1<0,则
     从优往左，n2表示截出几个字符
  2）数值函数：参数类型 number
     round trunc
  3）日期函数：参数类型 date
     add_months months_between last_day next_day
  4）数据类型转换函数：参数类型 3种都可能
     to_date to_number to_char
  5）一般函数
     nvl
     
二、组函数：操作在一组行（记录）上，每组返回一个结果
1.常用组函数
avg    平均值   参数类型  number
sum    求和     参数类型  number
count    计数   参数类型  number 字符 date
max     最大值  参数类型  number 字符 date
min      最小值  参数类型  number 字符 date


注：1.默认情况下，组函数处理所有非空值；而且都默认省略了关键字ALL
    2.如果处理的所有值都是null，则count()返回0，其他函数返回null
    3.一般情况下，空值处理函数nvl对sum()和max()没影响，对avg()、count()、min()才有影响
   4.count(*)不管记录是不是null，都将记录，即使某条记录列值全部为null
    5.加distinct是处理去重之后的非空值
    
2.GROUP BY和HAVING子句（位于where和order by之间：先取记录然后分组、排序）
  1)GROUP BY子句
  例：每台服务器上开通的账户数
  select unix_host, count(os_username)
  from service_lin
  group by unix_host;
  
  如果group by子句包含多列，则把这多列值完全一样的记录分为一组，分组的粒度细了，每组的
  的记录少了，但组多了。
  例：根据unix服务器ip地址、开通时间统计os帐号数
  select unix_host, to_char(create_date, 'yyyy-mm-dd'),count(os_username)
  from service_lin
  group by unix_host,to_char(create_date, 'yyyy-mm-dd');
  注：此处的to_char()函数必须有，因为如果单按date类型分组的话，那么要精确到时分秒完全一样的
      才能分为一组，我们的要求是只要同一天开户的就分为一组
  
  
  执行流程：首先from确定从service_lin表中取数据，然后通过where过滤数据，然后根据group by
            后面的分组标识来进行分组，有多少个标识就分几个组(其内部是用的distinct去重的)，分
            完组后再进行select选择，第一个unix_host是组标识，所以选出来的是distinct去重之
            后的，然后执行count函数计数，每一组都进行计数,每组只有一个返回值。在整个过程
            中，分了多少个组，count函数就执行了多少遍。组函数的含义就在这里，专门是处理每个分
            组里的记录的！
            
            注：在每个分组里，仍然有源表里所有的列信息，每组里所有的记录组标识对应的列值
                相等！(这是理所当然的)
  
  附：GROUP BY 常见错误：
     1.如果没有group by子句，如果select后面出现了一个组函数，其他的也必须是组函数！
     2.如果有group by子句，select后面可以跟group by后面跟的表达式(组标识)，其他的
       都会报错！
       
  2）HAVING子句
     HAVING子句用于过滤分组的，所以位置为
     where 条件表达式 group by 表达式 having 条件表达式 order by 表达式
      注：1.此处有group by子句，所以用order by排序和having过滤分组时，因为操作的都是分
          组，order by后面和having后面只能跟group by后面的表达式或者是组函数，表示对组进
          行排序。
          2.显然，对组进行过滤的话，不能用where，因为where是对单个的记录过滤！
          
  小结：
  书写顺序：select --> from --> where --> group by -->  having --> order by
  执行顺序:from --> where --> group by --> having --> select --> order by
  
  where和having子句的区别：
  1）where子句过滤的是行（记录）
  2）having子句过滤的是分组（组标识）
  3）where子句后面可以跟任意列名，单行函数，但不能跟组函数
     having子句只能包含group by后面的表达式和组函数
  4）where子句执行在前，having子句执行在后
  5）where子句和having子句都不允许用列别名