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

sql函数及正则表达式

最新文章