2016-01-07
目录
一、字符函数
二、数值函数
三、转换函数
四、正则表达式函数
五、聚合函数
六、分组语句
一、字符函数
1.ASCII(x)和CHR(x)
--获得字符x的ASCII码 select ascii('a'),ascii('A'),ascii('z'),ascii(0),ascii(9) from dual; --获得ASCII码为x的字符 select chr(97),chr(65),chr(122),chr(48),chr(57) from dual;
2.CONCAT(x,y)
--将y添加到x之后 --concat()和||操作符功能相同，||可以连接多个字符串，concat()只能连接2个字符串 select concat(first_name,last_name) from customers;
3.INITCAP(x)
--将x中每个单词的首字母转换成大写 select product_id,initcap(description) from products where product_id < 4;
4.INSTR(x,find_string[,start][,occurrence])
--在x中查找find_string，返回find_string所在位置 --start开始查找的位置 --occurrence第几次出现的位置 select name,instr(name,'Science') from products where product_id = 1; select name,instr(name,'e',1,2) from products where product_id = 1; select name,instr(name,'e',-1,3) from products where product_id = 1; select customer_id,dob,instr(dob,'JAN') from customers where customer_id = 1;
5.LENGTH(x)
--获得x中字符的个数 select name,length(name) from products; select price,length(price) from products where product_id < 3;
6.LOWER(x),UPPER(x)
--LOWER(x)将x中的字母转换成小写 --UPPER(x)将x中的字母转换成大写 select upper(first_name),lower(last_name) from customers;
7.LPAD(x,width[,pad_string]),RPAD(x,width[,pad_string])
--LPAD在x的左边补齐空格，使x的总长度达到width个字符 --RPAD在x的右边补齐空格，使x的总长度达到width个字符 select rpad(name,30,'.'),lpad(price,8,'*+') from products where product_id < 4;
8.LTRIM(x[,trim_string]),RTRIM(x[,trim_string]),TRIM(trim_char FROM x)
--LTRIM从x的左边截去字符 --RTRIM从x的右边截去字符 --TRIM从x的左、右各截去字符 select ltrim(' Hello Kathy Lindsey! '), rtrim(' Hi Doreeen Oakley!abcabc', 'abc'), trim('0' from '000Hey Steve Button!00000') from dual;
9.NVL(x,value)
--x为空，返回value，否则返回x select customer_id,nvl(phone,'Unknown Phone Number') from customers;
10.NVL2(x,value1,value2)
--x非空，返回value1;否则返回value2 select customer_id,nvl2(phone,'Known','Unknown') from customers;
11.REPLACE(x,search_string,replace_string)
--在x中查找search_string，并将其替换为replace_string select replace(name,'Science','PHYSICS') from products where product_id = 1;
12.SOUNDEX(x)
--获得包含x发音的字符串，用于对英文拼写不同发音相似的单词进行比较 select last_name from customers where soundex(last_name) = soundex('whyte'); select last_name from customers where soundex(last_name) = soundex('bloo');
13.SUBSTR(x,start[,length])
--从x中获得,从start位置开始的,长度为length的子字符串 select substr(name,2,7) from products where product_id < 4; select substr(name,-5,3) from products where product_id < 4;
14.综合
select substr('Mary had a little lamb',12,6) from dual; select name,upper(substr(name,2,8)) from products where product_id < 4;
二、数值函数
1.ABS(x)
--返回x的绝对值 select abs(10),abs(-10) from dual; select product_id,price,price - 30,abs(price - 30) from products where product_id < 4;
2.CEIL(x)
--返回大于、等于x的最小整数(上取整) select ceil(5.8),ceil(-5.2) from dual;
3.FLOOR(x)
--返回小于、等于x的最大整数(下取整) select floor(5.8),floor(-5.2) from dual;
4.GREATEST(values)
--返回一组values中的最大值 select greatest(3,4,1) from dual; select greatest(50/2,exp(2)) from dual;
5.LEAST(values)
--返回一组values中的最小值 select least(3,4,1) from dual; select least(50/2,exp(2)) from dual;
6.POWER(x,y)
--返回x的y次幂 select power(2,1),power(2,3) from dual;
7.ROUND(x,[,y])
--返回x的四舍五入值,精确到小数点后y位 select round(5.75),round(5.75,1),round(5.75,-1) from dual;
8.SIGN(x)
--x为正,返回1;x为0,返回0;x为负,返回-1 select sign(-5),sign(5),sign(0) from dual;
9.SQRT(x)
--返回x的平方根 select sqrt(25),sqrt(5) from dual;
10.TRUNC(x[,y])
--返回x在小数点后y位的截断值 select trunc(5.75),trunc(5.75,1),trunc(5.75,-1) from dual;
三、转换函数
1.ASCIISTR(x)
--将x转换为ASCII字符串 select asciistr('ABC D CDE') from dual; select asciistr(chr(128) || ' ' || chr(129)||' '|| chr(130)) from dual;
2.BIN_TO_NUM(x)
--将二进制数x转换为NUMBER类型 select bin_to_num(1,0,1),bin_to_num(1,1,0),bin_to_num(1,1,1,0) from dual;
3.CAST(x AS type)
--将x转换为由type指定的兼容数据库类型 select cast(12345.67 as varchar2(10)), cast('9A4F' as raw(2)), cast('05-JUL-07' as date), cast(12345.678 as number(10,2)) from dual; select cast(price as varchar2(10)), cast(price +2 as number(7,2)), cast(price as binary_double) from products where product_id = 1;
4.CHARTOROWID(x)
--将x转换为rowid select rowid from customers where customer_id = 1; select * from customers where rowid = chartorowid('AAAWf4AAEAAAAHjAAA');
5.COMPOSE(x)
--将x转换为Unicode字符串 select compose('o'||unistr(' 308')), compose('e'||unistr(' 302')) from dual;
6.CONVERT(x,source_char_set,dest_char_set)
--将x从source_char_set指定的字符集转换为dest_char_set指定的字符集 select convert('A B C','US7ASCII','WE8ISO8859P1') from dual;
7.DECOMPOSE(x)
--先对x进行分解，再将其转换为Unicode字符串 select decompose('oe') from dual;
8.HEXTORAW(x)
--将包含16进制数字的字符串x转换为二进制数(RAW) select utl_raw.cast_to_varchar2(hextoraw('41414743')) from dual;
9.RAWTOHEX(x)
--将RAW二进制数x转换为16进制数的VARCHAR2字符串 select rawtohex(41414743) from dual;
10.ROWIDTOCHAR(x)
--将ROWID x转换为VARCHAR2字符串 select rowid,customer_id,first_name,last_name from customers where rowidtochar(rowid) like '%KAAA';
11.TO_BINARY_DOUBLE(x)
--将x转换为BINARY_DOUBLE select to_binary_double(1087623) from dual;
12.TO_BINARY_FLOAT(x)
select to_binary_float(10623) from dual;
13.TO_CHAR(x[,format])
--将x转换为字符串 select to_char(12345.67) from dual; TO_CHAR(12345.67) ----------------- 12345.67 select to_char(12345.67,'99,999.99') from dual; TO_CHAR(12345.67,'99,999.99') ----------------------------- 12,345.67 select to_char(12345.67,'99999.99') from dual; TO_CHAR(12345.67,'99999.99') ---------------------------- 12345.67 select to_char(12345.67,'99,999.99') from dual; TO_CHAR(12345.67,'99,999.99') ----------------------------- 12,345.67 select to_char(-12345.67,'99,999.99') from dual; TO_CHAR(-12345.67,'99,999.99') ------------------------------ -12,345.67 select to_char(12345.67,'099,999.99') from dual; TO_CHAR(12345.67,'099,999.99') ------------------------------ 012,345.67 select to_char(12345.67,'99,999.9900') from dual; TO_CHAR(12345.67,'99,999.9900') ------------------------------ 12,345.6700 select to_char(12345.67,'$99,999.99') from dual; TO_CHAR(12345.67,'$99,999.99') ------------------------------ $12,345.67 select to_char(0.67,'B9.99') from dual; TO_CHAR(0.67,'B9.99') --------------------- .67 select to_char(12345.67,'C99,999.99') from dual; TO_CHAR(12345.67,'C99,999.99') ------------------------------ CNY12,345.67 select to_char(12345.67,'99999D99') from dual; TO_CHAR(12345.67,'99999D99') ---------------------------- 12345.67 select to_char(12345.67,'99999.99EEEE') from dual; TO_CHAR(12345.67,'99999.99EEEE') ------------------------------ 1.23E+04 select to_char(0012345.6700,'FM99999.99') from dual; TO_CHAR(0012345.6700,'FM99999.99') ------------------------------ 12345.67 select to_char(12345.67,'99999G99') from dual; TO_CHAR(12345.67,'99999G99') ---------------------------- 123,46 select to_char(12345.67,'L99,999.99') from dual; TO_CHAR(12345.67,'L99,999.99') ------------------------------ ￥12,345.67 select to_char(-12345.67,'99,999.99MI') from dual; TO_CHAR(-12345.67,'99,999.99MI') ------------------------------ 12,345.67- select to_char(-12345.67,'99,999.99PR') from dual; TO_CHAR(-12345.67,'99,999.99PR') ------------------------------ <12,345.67> select to_char(2007,'RN') from dual; TO_CHAR(2007,'RN') ------------------ MMVII select to_char(12345.67,'TM') from dual; TO_CHAR(12345.67,'TM') ---------------------------------------------------------------- 12345.67 select to_char(12345.67,'U99,999.99') from dual; TO_CHAR(12345.67,'U99,999.99') ------------------------------ ￥12,345.67 select to_char(12345.67,'99999V99') from dual; TO_CHAR(12345.67,'99999V99') ---------------------------- 1234567 select to_char(12345678.90,'99,999.99') from dual; TO_CHAR(12345678.90,'99,999.99') ------------------------------ ########## select product_id,'The price of this product is '|| to_char(price,'$99.99') from products where product_id < 5; PRODUCT_ID 'THEPRICEOFTHISPRODUCTIS'||TO_ --------------------------------------- ------------------------------------ 1 The price of this product is $19.95 2 The price of this product is $30.00 3 The price of this product is $25.99 4 The price of this product is $13.95
14.TO_MULTI_BYTE(x)
--将x中单字节字符转换为对应的多字节字符 select to_multi_byte('A') from dual;
15.TO_NUMBER(x[,format])
--将x转换为数字 select to_number('970.13') from dual; select to_number('970.13') + 25.5 from dual; select to_number('-$12,345.67','$99,999.99') from dual;
16.TO_SINGLE_BYTE(x)
--将x中多字节字符转换为对应的单字节字符 select to_single_byte('A') from dual;
四、正则表达式函数
1.REGEXP_LIKE(x,pattern[,match_option])
--在x中查找pattern 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');
2.REGEXP_INSTR(x,pattern[,start[,occurrence[,return_option[,match_option]]]])
--在x中查找pattern，并返回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 breaks?', 'o', 10, 2) as result from dual;
3.REGEXP_REPLACE(x,pattern[,replace_string[,start[,occurrence[,match_option]]]])
--在x中查找pattern，并将其替换为replace_string select regexp_replace('But, soft! What light through yonder window breaks?', 'l[[:alpha:]]{4}','sound') as result from dual;
4.REGEXP_SUBSTR(x,pattern[,start[,occurrence[,match_option]]])
--在x中查找pattern的子字符串 select regexp_substr('But, soft! What light through yonder window breaks?', 'l[[:alpha:]]{4}') as result from dual;
5.REGEXP_COUNT(x,pattern[,start[,occurrence[,match_option]]])
--返回pattern在x中出现的次数 select regexp_count('But, soft! What light through yonder window softly breaks?', 's[[:alpha:]]{3}') as result from dual;
五、聚合函数
1.AVG(x)
--返回x的平均值 select avg(price) from products; select avg(price + 2) from products; select avg(distinct price) from products;
2.COUNT(x)
--返回包含x的查询的行数 select count(product_id) from products; select count(rowid) from products;
3.MAX(x)、MIN(x)
--返回x的最大值、最小值 select max(price),min(price) from products; select max(name),min(name) from products; select max(dob),min(dob) from customers;
4.STDDEV(x)
--返回x的标准差 select stddev(price) from products;
5.SUM(x)
--返回x中所有值的和 select sum(price) from products;
6.VARIANCE(x)
--返回x的方差 select variance(price) from products;
六、分组语句
1.对行进行分组
select product_type_id from products group by product_type_id; select product_id,name,price from products where product_type_id is null;
2.在分组中使用多列
select product_id,customer_id from purchase group by product_id,customer_id;
3.对行组使用聚合函数
select product_type_id,count(rowid) from products group by product_type_id order by product_type_id; select product_type_id,avg(price) from products group by product_type_id order by product_type_id; select product_type_id,variance(price) from products group by product_type_id order by product_type_id; select variance(price) from products group by product_type_id order by product_type_id; select variance(price) from products group by product_type_id order by variance(price);
注意项：
(1)查询中包含聚合函数，选择列不在聚合函数中，这些列必须在GROUP BY子句中
(2)不能在WHERE子句中使用聚合函数限制行，因为WHERE子句只能对单行过滤，过滤行组用HAVING子句
(3)GROUP BY可以不与HAVING子句一起使用，但HAVING子句必须和GROUP子句一起使用
4.组合使用WHERE和GROUP BY子句
--先用WHERE子句对返回的行进行过滤，再用GROUP BY子句对保留的行进行分组 select product_type_id,avg(price) from products where price < 15 group by product_type_id order by product_type_id;
5.组合使用WHERE、GROUP BY和HAVING子句
--先用WHERE子句对返回的行进行过滤，在用GROUP BY子句对保留的行进行分组，最后HAVING子句对行组进行过滤 select product_type_id,avg(price) from products where price < 15 group by product_type_id having avg(price) > 13 order by product_type_id; select product_type_id,avg(price) from products where price < 15 group by product_type_id having avg(price) > 13 order by avg(price);
【参考资料】
[1] Jason Price.精通Oracle Database 12c SQL&PLSQL编程(第3版).[M].北京：清华大学出版社，2014