Oracle数据库基础SQL语句(二)—函数篇

Oracle数据库基础SQL语句(二)—函数篇

一：数值函数

1：四舍五入函数 ROUND(n, m)
n表示要进行四舍五入的值，m代表小数点后几位（m>0）或前几位(m<0),m缺省默认为0；

SELECT ROUND(SAL,-2) from EMP ORDER BY SAL ASC;

2：取整函数 CEIL是上限，有小数部分直接加1（跟四舍五入不同），FLOOR是下限，有小数部分直接去掉，
注：会生成名为CEIL(N)和floor(n)两列，分别对n进行值的改变并显示

SELECT CEIL(SAL),FLOOR(SAL) FROM EMP;

3：常用计算：

绝对值 ABS(n)  有问题 不显示
SELECT abs(SAL) from EMP;
取余 mod（m,n）  m/n 当n=null，结果为null
SELECT mod(SAL,10)FROM EMP;
平方根 SQRT(n)  结果是一串数字（为什么）
SELECT SQRT(DEPTNO) from EMP;
幂函数 POWER(n1, n2) n1的n2次幂
SELECT POWER(DEPTNO,2) from EMP;
三角函数 sin(n) asin(n) 反正弦  cos(n) acos(n) tan(n) atan(n).....  
这里的n代表的是弧度
有结果 显示一长串数字
SELECT sin(DEPTNO) from EMP;

二：字符函数

1：大小写转换函数（应用于注册用户时不区分大小写，但在输出时进行统一）

OPPER(char)  把小写转化成大写 
语法错误
SELECT OPPER(ENAME) from EMP;
LOWER(ch) 把大写转小写
select LOWER(JOB) from EMP;
INITCAP(ch) 把首字母进行大写转换
首字母为大写 其他变成了小写（对于后边的大写字母进行了转换）
select INITCAP(JOB) from EMP;

2:截取子字符串函数

SUBSTR(ch, pos, length) ch(源字符串)，pos(取字符串开始的地方)，length（截取字符串的位数，可省略代表截取到最后）
SELECT SUBSTR(JOB,2,3) FROM EMP;
注：pos=0(从首字母开始截取)，pos<0(从尾部开始截取)
SELECT SUBSTR(JOB,-2,3) FROM EMP;

3:获取字符串长度的函数

LENGTH(ch)  注：字符串中带有空格也算长度
select JOB FROM EMP WHERE LENGTH(JOB)>6;

4:字符串的连接函数

CONCAT(ch1, ch2) 与||操作符的作用一样
SELECT CONCAT(JOB,ENAME)from EMP;

5:去除子串函数

TRIM(c2 from c1) 从字符串c1中去除字符c2（注：c2是一个字符长度，即一个字符）
TRIM(trim_source)只有一个参数时，代表去除首尾的空格
SQL 中的 TRIM 函数是用来移除掉一个字串中的字头或字尾至不与去除的字符不同的字母前为止（如果开头和结尾相同，
则去除时一同去除,如果开头有两个或多个相同的字符相连，则去除时都去除）。
SELECT TRIM('C'from JOB)FROM EMP;
这个函数在不同的资料库中有不同的名称：
MySQL: TRIM( ), RTRIM( ), LTRIM( )
Oracle: RTRIM( ), LTRIM( )
SQL Server: RTRIM( ), LTRIM( )
LTRIM(c1,c2) 去除c1头部的c2（不一定去除一个，具体看上边）只有一个参数c1时，代表去除开头的空格
SELECT LTRIM(JOB,'S')FROM EMP;
RTRIM(c1,c2) 去除c1尾部的c2（不一定去除一个，具体看上边）只有一个参数c1时，代表去除结尾的空格

6：替换函数

REPLACE(char s1 s2) s2可省略 则将char中s1替换成空格（在开头或者结尾的话就成了删除）
SELECT REPLACE(JOB,'CLERK','a')from EMP;

**

三：日期函数

**
1：系统时间 SYSDATE

SELECT SYSDATE from EMP;

2:日期操作函数

ADD_MONTHS(date, int) 返回在指定日期上添加的月份
int如果是小数，则自动截取整数部分，如果int为负数，则相当于在原日期上减去的月份
SELECT ADD_MONTHS(SYSDATE, 3),ADD_MONTHS(HIREDATE, -3) FROM EMP;

NEXT_DAY(date, ch) 如果ch的值是"星期一"（中文状态）/"monday"()英文状态，则返回指定日期date的下周一是
哪天？
时区或编码引起错误，想到阿拉伯数据是全世界通用的，那应该可以用1--7代替星期几。结果是对的。
SELECT NEXT_DAY(SYSDATE, 4)FROM EMP;

LAST_DAY(date) 返回date月的最后一天（用于比如查看有没有31号）
SELECT LAST_DAY(HIREDATE)FROM EMP;

MONTHS_BETWEEN(date1, date2) 表示date1和date2相隔的月份（可以用相减代替）
SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE),HIREDATE from EMP;

EXTRACT(time_unit FROM expr) 如果time_unit是year/month/day,则返回expr的年份/月份/天 
SELECT EXTRACT(year from SYSDATE) from EMP;

问题：获取小时/分钟/秒呢？

**

四：转换函数

**
1：日期转字符串函数

TO_CHAR(date[,fmt[,params]]) date:要转换的日期，fmt:转换的格式，params：日期的语言，一般根据操作系统默认，一般不写
SELECT TO_CHAR(SYSDATE)FROM EMP;
默认格式 
year：YY（两位数的年） YYYY（四位数的年）
month：MM
day：DD
hour：HH24  HH12/HH
MI
SS
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')FROM EMP;
SELECT TO_CHAR(SYSDATE,'YY-MM-DD HH:MI:SS')FROM EMP;
尝试了一下换字母，这是错的
SELECT TO_CHAR(SYSDATE,'ZZZZ-MM-DD HH24:MI:SS')FROM EMP;

2:字符转换成日期的函数

TO_DATE(ch, fmt[,parasm]) 注：TO_DATE(ch, fmt)按照系统默认格式显示日期(这个YYYY...不写还错)
SELECT TO_DATE('2019-07-25','YYYY-MM-DD')FROM EMP;

3：数字转字符的函数

TO_CHAR(number[,fmt])
格式fmt
9:显示数字并忽略前面的0；
0：显示数字，位数不足，用0补足；
.或者D：显示小数点
,或者G：显示千位符  注：不能.和G混搭的来，会报错
$:美元符号
S:加正负号（前后都可以(加号在前或者在后)，但不能同时加）
SELECT TO_CHAR(12345.67,'$99,999.999')FROM EMP;
SELECT TO_CHAR(12345.67,'S99G999D999')FROM EMP;
SELECT TO_CHAR(12345.67,'99G999D999S')FROM EMP;

4：字符转数字的函数

TO_NUMBER(expr, fmt)
SELECT TO_NUMBER('12,345','99999')FROM EMP;
SELECT TO_NUMBER('$12,345','$99999')FROM EMP;
S是错的
SELECT TO_NUMBER('S12,345','S99999')FROM EMP;