sql函数及正则表达式

 
/*正则表达式
regexp_like() 查询1965-1968年之间的顾客
c:区分大小写 I不区分大小写
n:允许使用可以匹配任意zifu m:将x作为一个包含多行的字符串*/
select customer_id,first_name,last_name,dob from customers
where regexp_like(to_char(dob,'YYYY'),'^196[5-8]$');


select customer_id,first_name,last_name,dob
from customers
where regexp_like(first_name,'^j','i');


regexp_instr():返回pattern出现的位置
select 
  regexp_instr('But, soft! What light through yonder window breaks','l[[:alpha:]]{4}') as result
from dual;


select
  regexp_instr('But, soft! What light through yonder window softly  breaks','s[[:alpha:]]{3}',1,2) as result
from dual;


select
  regexp_instr('But, soft! What light through yonder window softly breaks','o[[:alpha:]]',10,2) as result
from dual;
--返回第二次匹配字符的位置


regexp_replace()--在1位置的字符中查找2位置的字符，将其替换为3位置的字符
select
  regexp_replace('But, soft! What light through yonder window softly breaks','l[[:alpha:]]{4}','sound') as result
from dual;


regexp_substr()--返回符合条件的子字符串
select
  regexp_substr('But, soft! What light through yonder window softly breaks','l[[:alpha:]]{4}') as result
from dual;


regexp_count()--返回匹配字符在字符中出现的次数
select 
  regexp_count('But, soft! What light through yonder window softly breaks','t[[:alpha:]]{6}') as result
from dual;


聚合函数
  注意点：聚合函数会忽略空值，有空值存在不适合使用聚合函数
        distinct关键字可以和聚合函数一起使用


select avg(distinct price) from products;
select stddev(price) from products;
--计算标准差
select stddev(price),variance(price) from products;
--计算方差 方差是一个统计函数 定义为一组样本数据的偏离度 方差=标注差的平方


select * from purchases;
select product_id,count(*) from products group by PRODUCT_ID;


select product_id,customer_id from purchases
group by product_id,customer_id;


聚合函数的错误用法：
select product_type_id,avg(price) from products;
ORA-00937: ????????
00937. 00000 -  "not a single-group group function"
*Cause:    
*Action:
行 59 列 8 出错
alter session set nls_lang='American_america_AL32UTF8';


select product_type_id,avg(price) from products
group by product_type_id having avg(price)>20;


日期和时间的存储与处理


insert into customers(customer_id,first_name,last_name,dob,phone)
values(7,'Steve','Purple',DATE '1972-10-25','800-555-1215');


select * from customers;
rollback;


to_char
select customer_id,to_char(dob,'MM DD,YYYY') from customers;
select to_char(sysdate,'MM DD,YYYY,HH24:MI:SS') FROM DUAL;


select to_date('11 4, 2007','MM DD,YYYY') from dual;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29749380/viewspace-1347199/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29749380/viewspace-1347199/