/*正则表达式
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/