Oracle Database 11g SQL 开发指南学习笔记：简单函数

 

1、字符函数

 

select ascii('a'),   --把字符转化为ascii码
       ascii('z'),
       ascii('A'),
       ascii('Z'),
       
       chr(97),      --把ascii码转化为字符
       chr(122),
       chr(65),
       chr(90)      
from dual;


select concat('abc','123'),    --连接字符串
       'abc' || '123',         --与concat函数一样
       
       --把字符串中的每个单词的首字母转化为大写
       initcap('Adescription of modern science'),
       
       --在字符串中，查找某个单词，从1开始，查找这个单词的第一次出现，默认都为1
       instr('A description of modern science','modern',1,1),
       instr('A description of modern science','modern'),
       
       --字符的个数，不是字节数
       length('A description of modern science'),
       
       lower('ALM'),   --把字符串全部转化为小写
       
       upper('aRx')    --把字符串全部转化为大写          
from dual;


select --默认情况下，在字符串的左边会填充空格，总长度为21
       lpad('modern science',21),
       
       --这个例子，在左边补齐字符串时，是从左向右边填充的：@#$，再@#$，再@#$，
       --但由于第三次太南充时超过了21个字符，所以只填充了@符号
       lpad('modern science',21,'@#$'),
       
       --默认情况下，在字符串的右边会填充空格，总长度为21
       rpad('modern science',21),
       
       --这个例子，在右边补齐字符串时，也是从左向右填充时：@#$，再@#$，再@#$，但由于第三次太南充时超过了21个字符，所以只填充了@符号
       rpad('modern science',21,'@#$')
from dual;


select --默认是截去空格
       ltrim(' good bye!'),
       
       --指定要截去*+，但下面字符串只是*+*，而不是*+*+，但最后都会把*+*都去掉
       -- good bye!
       ltrim('*+* good bye!','*+')，
       
       --这个说明只要是*+，任何一个字符要出现在左边，都会被去掉，直到出现了@符号
       -- good bye!
       ltrim('*+++* good bye!','*+')，
       
       --@*good bye!
       ltrim('*+++*@*good bye!','*+')       
from dual;


--截去右边的字符串，直到遇到非截取字符串为止
select rtrim('good bye!  '),           --默认去掉空格
       rtrim('good bye!*+*+*','*+'),   --good bye!
       rtrim('good bye!*+*+ *','*+')   --good bye!*+*+
from dual;


select --去掉左边和右边的*，这里的trim_string参数，不能超过1个字符
       trim('*' from '*+*good bye!*+**'),
       
       trim('  good bye !  ')    --默认去掉左右的空格
from dual;


select nvl(col,'Unknown'),
       
       nvl2(col,col,'Unknown')  --如果col列的值为非空，那么还是col的值，如果为空，则为Unknown
from
(
  select 'abc' as col from dual
  union all
  select '123' from dual
  union all
  select null from dual
)；


select --把science替换成physics
       replace('A description of modern science','science','physics'),
       
       --从字符串中取子串，指定从哪个位置开始，所取长度
       substr('A description of modern science',3,length('description'))
from dual;


--soundex函数用于获取某个单词的发音的字符串
select v,
       soundex(v)   --W300
from
(
  select 'white' as v from dual
  union all
  select 'sweet' from dual
)
where soundex(v) = soundex('whyte');  
 

2、数字函数

 

select --绝对值
       abs(-10.2),
       abs(100.3),
       
       sign(-3),  --如果x为负数，那么返回-1 
       sign(10),  --如果x为正数，那么返回1
       sign(0),   --如果x为0，那么返回0
       
       mod(10,3),  --取余数
       
       --大于x的最小整数
       ceil(5.8)，    -- 6
       ceil(-5.8),   -- -5
       
       --小于x的最大整数
       floor(5.8),    -- 5
       floor(-5.8)    -- -6
from dual;


select exp(2),     --e的x次幂
       
       power(10,2),  --10的2次方
       
       sqrt(4),      --4的平方根
       
       log(2,4),     --以2为底4的对数，就是4开2次方，就是2
       ln(10)        --以e为底10的对数，就是10开e次方
from dual;


--位与，输入的参数必须是10进制数，但这个函数又是对数字进行位与
select bitand(
               bin_to_num(1,0,1),  --2进制数101转换为10进制5
               bin_to_num(0,1,0)   --2进制数10，也就是10进制数2
             )
from dual;


--注意：5.58有左边的第0位，第1位，也有右边的第0位，第1位
select round(5.58,0),    -- 6   四舍五入，保留0位小数
       round(5.58,1),    -- 5.6 保留1位小数
       round(5.58,-1),   -- 10  保留1位整数,也就是去掉第0位的整数
       round(5.58,-0),   -- 6   保留0位整数，也就是去掉小数部分
       
       round(4.58,-1),   -- 0

       
       --截断数字
       trunc(5.58),    -- 5
       trunc(5.58,1),  -- 5.5
       trunc(5.58,-1), -- 0
       trunc(4.48,-1)  -- 0
from dual;

3、转换函数

 

select asciistr(12345),   --把数字12345转为化ascii字符串'12345'
       
       bin_to_num(1,1,1)  --把3个1转为化number，也就是7
from dual;


--转为化字符串
select to_char(12345.67),
       
       to_char(12345.67,'99,999.99'),     --指定第一个参数所指定的值，要转换的格式
       
       to_char(12345678.90,'99,999.99'）, --当格式所包含的位数，少于指定值时，会返回由#组成的字符串
       
       to_char(12345.89,'$99,999.99')     
from dual;


--转化为字符串
select to_number('970.123'),
       
       to_number('970.123') + 25.5,
       
       to_number('-$970,970.123','$999,999.999')   --指定了如何理解要转化的字符串
from dual;


--cast(x as type)把x转化为由type指定的兼容数据库类型
select cast(12345.67 as varchar(10)),

       cast('9A4F' as raw(2)),
       
       cast(to_date('05-jun-07','DD-MON-YY') as date),
       
       cast(12345.678 as number(10,2))
from dual;

4、正则表达式函数

 

--^表示从什么开始，$表示以上面为结尾，[]表示匹配指定范围内的任意一个字符
select customer_id,first_name,last_name,dob
from customers
where regexp_like(to_char(dob,'yyyy'),'^196[5-8]$');


--'i'表示不区分大小写
select customer_id,first_name,last_name,dob
from customers
where regexp_like(first_name,'^j','i');


--[:alpha:]表示可以匹配字符A-Z和a-z，{n}表示恰好匹配一个字符n次，n是一个整数
select regexp_instr(v,'l[[:alpha:]]{4}'),

       regexp_instr(v,'s[[:alpha:]]{3}',1,2), --从第1个字符开始，第2次出现
       
       regexp_instr(v,'o',10,2)  --从第10个字符开始，第2次出现
from
(
  select 'But, soft! What light through yonder window softly breaks?' as v
  from dual
);


--把匹配到的字符串，替换成 sound
select regexp_replace(v,'l[[:alpha:]]{4}','sound')
from
(
  select 'But, soft! What light through yonder window softly breaks?' as v
  from dual
);


--取出匹配的子串
select regexp_substr(v,'l[[:alpha:]]{4}')
from
(
  select 'But, soft! What light through yonder window softly breaks?' as v
  from dual
);



--匹配模式的字符串，出现的次数
select regexp_count(v,'s[[:alpha:]]{3}')
from
(
  select 'But, soft! What light through yonder window softly breaks?' as v
  from dual
);
 

5、聚合函数

 

select max(price),
       min(price),
       
       count(*),
       
       sum(price),
       
       avg(price),
       
       sum(price) / count(*) 
from products;


select product_type_id,

       max(price),
       min(price),
       
       count(*),
       
       sum(price),
       
       avg(price),
       
       sum(price) / count(*) 
from products

group by product_type_id;