Oracle学习(四)_SQL函数
--第一部分:SQL基础 --ch1 简单查询 --ch2 查询基本概念 --ch3 数据过滤 --第二部分:多表操作 --ch4 集合理论 --ch5 内连接 --ch6 外连接 --ch7 子查询 --第三部分:数据分组 --ch8 简单统计 --ch9 数据分组 --ch10 分组数据过滤 --第四部分:SQL函数 --ch11 内置函数 --ch12 case表达式 --第五部分:DML语句 --ch13 插入数据 --ch14 修改数据 --ch15 删除数据 -------------------------------------------------------------------------- --第四部分:SQL函数 【ch11 内置函数】 --11.1使用单行函数 /* 字符函数 数字函数 转换函数 日期函数 */ --字符函数 --ascii(x)返回字符x的ascii码 SELECT ascii(‘a‘), ascii(‘A‘), ascii(‘z‘), ascii(‘Z‘), ascii(0), ascii(9) FROM dual; --chr(x)返回ascii码为x的字符 SELECT CHR(97),CHR(65),CHR(122),CHR(90) FROM dual; --concat(x,y)将y附加在x上,并返回附加后的字符串 SELECT first_name,last_name,concat(first_name, last_name) allname FROM customers; --initcap(x)将x中的每个单词的首字母转换为大写 SELECT product_id,description, initcap(description) ad FROM products WHERE product_id < 4; --instr(x,find_str,star,occur) --在x中查找字符串find_str,然后返回find_str所在的位置。 --start指定哪个位置开始查找,occur指定find_str出现的次数 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 customer_id ,dob,INSTR(dob,‘01‘) FROM customers WHERE customer_id = 1; --可以指定occur为负数,表示从字符串尾部开始向左查找 SELECT NAME, instr(NAME, ‘n‘, -2, 2) FROM products WHERE product_id = 1; --length(x)返回x中字符的个数 SELECT NAME, length(NAME) FROM products; --lower(x)将x中的字母转换为小写 --upper(x)将x中的字母转换为大写 SELECT first_name,upper(first_name), last_name,lower(last_name) FROM customers; --lpad(x,width,pad_str)在x的左边补齐pad_str字符使得其长度为width --rpad(x,width,pad_str)在x的右边补齐pad_str字符使得其长度为width SELECT name,rpad(NAME, 30, ‘.‘),price, lpad(price, 8, ‘*+‘) FROM products WHERE product_id < 4; --ltrim(x,trim_str)从x中的左边截去一些字符,直到x中有字符不出现在trim_str中为止 --rtrim(x,trim_str)从x中的右边截去一些字符,直到x中有字符不出现在trim_str中为止 --trim(leading|trailing|both y from x)从x中截去单个字符y SELECT ltrim(‘ Hello Gail Seymour!‘), rtrim(‘Hi doreen Oakley!abcabc‘, ‘akb!c‘), TRIM(TRAILING ‘0‘ FROM ‘000Hey Steve Button!00000‘) FROM dual; --nvl(x,value)若x为空,则返回value,否则返回x --nvl2(x,v1,v2)若x为空,则返回v2,否则返回v1 SELECT customer_id, phone, nvl(phone, ‘Unkown phone number‘), dob, nvl(dob, SYSDATE), phone, nvl2(phone, ‘Known‘, ‘Unknown‘) FROM customers; --replace(x,search_str,replace_str)在x中查找search_str,并将其替换为replace_str SELECT name,REPLACE(NAME, ‘Science‘, ‘Physics‘) FROM products WHERE product_id = 1; --substr(x,start,length)返回x中的一个子字符串,子字符串从start开始,子字符串长度为length SELECT name, substr(NAME, 2, 7), substr(NAME, -5, 2) FROM products WHERE product_id < 4; --11.2函数组合 SELECT NAME, upper(substr(NAME, 2, 8)) FROM products WHERE product_id < 4; --11.3数字函数 --abs(x)返回x的绝对值 SELECT ABS(-1.222) FROM dual; SELECT product_id, price, price - 30, abs(price - 30) FROM products WHERE product_id < 4; --ceil(x)返回大于或等于x的最小整数 SELECT ceil(5.8), ceil(-5.2) FROM dual; --floor(x)返回小于或等于x的最大整数 SELECT floor(5.8), floor(-5.2) FROM dual; --mod(x,y)返回x除以y的余数 SELECT MOD(8, 3), MOD(8, 4) FROM dual; --power(x,y)返回x的y次幂 SELECT POWER(2,3) FROM dual; --round(x,y)返回对x进行取整的结果:若y不指定,对小数点后第一位四舍五入,若y>0,对第y+1位小数四舍五入;若y<0,对小数点左边的y位四舍五入 SELECT round(5.75), round(5.75, 1), round(15.75, -1) FROM dual; --sign(x)返回x的符号。若x<0,返回-1;若x=0,返回0;若x>0,返回1 SELECT sign(-5), sign(0), sign(5) FROM dual; --trunc(x,y)用于计算对x进行截断的结果:若y不指定,对小数点后第一位截断,若y>0,对第y+1位小数截断;若y<0,对小数点左边的y位截断 SELECT trunc(5.75), trunc(5.76, 1), trunc(15.75, -1) FROM dual; --11.4转换函数 --to_char(x,format)用于将x转换为一个字符串。可以用format指定格式 SELECT to_char(123.456) FROM dual; SELECT to_char(12345.67, ‘99,999.99‘) FROM dual; --to_number(x,format)用于将x转换为一个数字。可以用format指定格式 SELECT to_number(‘9455.335‘) FROM dual; SELECT to_number(‘-$12,345.67‘, ‘$99,999.99‘) FROM dual; --使用to_char()和to_date()转换时间值 --to_char(x,format)将数字或时间值x转换为字符串。 --to_date(x,format)将字符串x转换为date类型 --使用to_char()将时间值转换为字符串 SELECT sysdate,to_char(SYSDATE,‘yyyy-mm-dd‘) FROM dual; SELECT customer_id, to_char(dob, ‘MONTH DD,YYYY‘) FROM customers; SELECT to_char(SYSDATE,‘MONTH DD,YYYY,HH24:MI:SS‘) FROM dual; /*常用format参数 YYYY四位年份 MM二位月份 MONTH月份的全拼,全部大写 MON月份的前3个字母 WW本年中的第几周 W本月中的第几周 DD本月中的第几天 D本周中的第几天 DAY周几的全名,全部大写 HH24 24小时格式小时数 HH 12小时格式小时数 MI分钟 SS秒数 FF[1..9]带有小数的秒数 分隔符:-/,.;: "text" */ SELECT to_char(SYSDATE,‘D‘) FROM dual; --使用to_date()将字符串转换为时间值 SELECT to_date(‘5月 2,2006‘, ‘month dd,yyyy‘) FROM dual; SELECT to_date(‘20120808‘,‘yyyy-mm-dd‘) FROM dual; --11.5使用时间值函数 --add_months(x,y)返回x加上y个月后的结果,若y为负值,则从x中减去y个月 SELECT add_months(SYSDATE, 3), add_months(trunc(SYSDATE), -3) FROM dual; --last_day(x)返回包含x月的最后一天 SELECT last_day(DATE‘2012-12-1‘) FROM dual; --months_between(x,y)返回x和y之间有几个月,如果x比y晚,就返回正数,否则为负数 SELECT months_between(SYSDATE + 1000, SYSDATE) FROM dual; --next_day(x,day)返回从x开始,下一个day的时间值 SELECT next_day(DATE‘2012-5-23‘,‘星期一‘) FROM dual; SELECT next_day(DATE‘2012-5-23‘,2) FROM dual; --round(x,unit)对x进行取整。 SELECT round(SYSDATE, ‘YYYY‘) FROM dual; SELECT round(SYSDATE, ‘MM‘) FROM dual; --sysdate()返回数据库所在操作系统中当前设置的时间值 SELECT SYSDATE FROM dual; --trunc(x,unit)对x进行截断。 /* YYYY四位年份 MM二位月份 WW本年中的第几周 W本月中的第几周 DD本月中的第几天 D本周中的第几天 HH24 24小时格式小时数 HH 12小时格式小时数 MI分钟 */ SELECT trunc(SYSDATE, ‘Y‘) FROM dual; SELECT trunc(SYSDATE, ‘MM‘) FROM dual; SELECT SYSDATE+7,trunc(SYSDATE+7, ‘W‘) FROM dual; SELECT trunc(SYSDATE, ‘DD‘) FROM dual; SELECT trunc(SYSDATE+7, ‘D‘) FROM dual; SELECT trunc(SYSDATE, ‘HH‘) FROM dual; SELECT trunc(SYSDATE, ‘MI‘) FROM dual; --11.6使用translate()函数 --translate(x,from_str,to_str)函数在x中查找from_str中的字符,并将其转换成to_str中对应的字符 SELECT product_id, NAME, translate(NAME, ‘ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz‘, ‘EFGHIJKLMNOPQRSTUVWXYZABCDefghijklmnopqrstuvwxyzabcd‘) FROM products; SELECT translate(‘I am Chinese, I love China‘, ‘China‘, ‘中国‘) "Translate example" FROM dual; SELECT translate(‘我是中国人,我爱中国‘, ‘中国‘, ‘China‘) "Translate example" FROM dual; --11.7使用decode()函数 --decode(value,search_value,result,default_value)对value与search_value进行比较。如果这两个值相等 --则返回result,否则返回default_value --相当于在SQL中执行if语句 SELECT DECODE(1,1,2,3) FROM dual; SELECT DECODE(1,2,1,3) FROM dual; SELECT prd_id, available, decode(available, ‘Y‘, ‘Product is available‘, ‘Product is not available‘) FROM more_products; --可以向decode传递多个参数 SELECT product_id, product_type_id, decode(product_type_id, 1, ‘Book‘, 2, ‘Video‘, 3, ‘DVD‘, 4, ‘CD‘, ‘Magazine‘) FROM products; 【ch12 case表达式】 --与decode类似,相当于在SQL中执行if语句 CASE 表达式有两种格式: CASE 简单表达式,它通过将表达式与一组简单的表达式进行比较来确定结果。 CASE 搜索表达式,它通过计算一组逻辑表达式来确定结果。 这两种格式都支持可选的 ELSE 参数。 CASE 可用于允许使用有效表达式的任意语句或子句。 例如,可以在 SELECT、UPDATE、DELETE 和 SET 等语句以及 select_list、IN、WHERE、ORDER BY 和 HAVING 等子句中使用 CASE。 --case简单表达式: CASE input_expression WHEN when_expression1 THEN result_expression1 WHEN when_expression2 THEN result_expression2 ... [ ELSE else_result_expression ] END SELECT product_id, product_type_id, CASE product_type_id WHEN 1 THEN ‘Book‘ WHEN 2 THEN ‘Video‘ WHEN 3 THEN ‘DVD‘ WHEN 4 THEN ‘CD‘ ELSE ‘Magazine‘ END casen FROM products; --使用case搜索表达式 CASE WHEN Logical_expression1 THEN result_expression1 WHEN Logical_expression2 THEN result_expression2 ... [ ELSE else_result_expression ] END SELECT product_id, product_type_id, CASE WHEN product_type_id = 1 THEN ‘Book‘ WHEN product_type_id = 2 THEN ‘Video‘ WHEN product_type_id = 3 THEN ‘DVD‘ WHEN product_type_id = 4 THEN ‘CD‘ ELSE ‘Magazine‘ END casen FROM products;
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。