【初识Oracle】③数据库对基本数据类型进行操作的函数

--================================数值函数========================================================================SELECT Distinct ABS(-5) FROM emp;SELECT  * FROM dual;SELECT ABS(-5) FROM dual;--绝对值函数SELECT CEIL(5.8) FROM dual;--截断，取小值，只留整数位SELECT FLOOR(5.8) FROM dual;--补充，取大值，进一，只留整数位SELECT ROUND(5.8) FROM dual;SELECT ROUND(5.3) FROM dual;--四舍五入round（数字，精确度）SELECT TRUNC(5.3) FROM dual;SELECT TRUNC(5.8) FROM dual;--截断取值，后面的全都舍掉trunc（数字，精确度）--查询员工的姓名,日工资(精确地小数点后2位)SELECT ename,trunc(sal/21,2) FROM emp;SELECT ename,ROUND(sal/21,2) FROM emp;--字符串函数SELECT CONCAT('this is',' test') FROM dual;SELECT 'this is'||' test' FROM dual;SELECT ename||'属于'||d.dname AS 员工信息 FROM emp e,dept d WHERE e.deptno=d.deptno;SELECT ename||'属于'||d.dname 员工信息 FROM emp e,dept d WHERE e.deptno=d.deptno;SELECT Instr('AAAAABCCAB','B') FROM dual;--下标从1开始SELECT Instr('AAAAABCCABcB','BC',7,1) FROM dual;SELECT Instr('AAAAABCCABcB','B',7,2) FROM dual;SELECT Instr('AAAAABCCABcB','BC',7,1) FROM dual;SELECT Instr('AAAAABCCABcB','BC') FROM dual;SELECT LOWER('AAAAABCCABcB') FROM dual;--转换小写函数SELECT UPPER('This is Abc') FROM dual;--转换大写函数SELECT nls_initcap('this is abc') FROM dual;--各单词首字母转换为大写SELECT LPAD('this',10,'*') FROM dual;--如果不满位数要求，则左填充SELECT RPAD('this',10,'*') FROM dual;--如果不满位数要求，则右填充--=========================================字符函数===============================SELECT '*'||TRIM(' this is test ')||'*' FROM dual;SELECT '*'||LTRIM(' this is test ')||'*' FROM dual;SELECT '*'||RTRIM(' this is test ')||'*' FROM dual;SELECT LTRIM('!*!*this is test!*!*','!*') FROM dual;SELECT RTRIM('!*!*this is test!*!*','!*') FROM dual;SELECT TRIM('!' FROM '!!!!*!*this is test!*!*!!!!') FROM dual;--去除字符串中指定的字符SELECT substr('this is a test',6,2) FROM dual;--从身份证号中截取出生日期,显示格式yyyy年mm月dd日SELECT REPLACE('this is a string','is','was') from dual;SELECT REPLACE('this is a string','is') from dual;--替换字符串中特定字符SELECT ename,DECODE(sal,800,'低工资',3000,'正常工资',5000,'高工资',sal) FROM emp;--根据获得的sal参数内容，输出不同的数据

--========================================日期函数====================================================SELECT SYSDATE FROM dual;--获取系统当前时间SELECT add_months(SYSDATE,1) FROM dual;SELECT add_months('6-5月-2014',1) FROM dual;SELECT add_months('28-2月-2014',1) FROM dual;SELECT hiredate,add_months(hiredate,10*12) AS "10年后" FROM emp;SELECT ename,EXTRACT(YEAR FROM hiredate) 入职年份 FROM emp;--从日期中抽取年/月/日SELECT ename,ceil(months_between(SYSDATE,hiredate)/12) 入职时长 FROM emp;SELECT next_day(SYSDATE,'星期三') FROM dual;SELECT next_day(SYSDATE,'星期六') FROM dual;SELECT current_date FROM dual;--获取系统当前时间，并以2000年3月5日 12:10:10格式显示--类型转换函数SELECT EXTRACT(YEAR FROM SYSDATE)||'年'||EXTRACT(MONTH FROM SYSDATE)||'月'||EXTRACT(DAY FROM SYSDATE)||'日' FROM dual;SELECT to_char(SYSDATE,'yyyy-MM-dd hh:mm:ss') FROM dual;SELECT to_char(123.45)||'1' FROM dual;SELECT to_char(123.45)+1 FROM dual;SELECT to_char(1234567.89,'$999,999,999.99') from dual;SELECT to_char(1234567.89,'L999,999,999.99') from dual;SELECT to_date('18-4-2012','dd-mm-yyyy') from dual;SELECT to_date('2012-4-18','yyyy-mm-dd') from dual;SELECT to_date('2012-4-18 8:50:3','yyyy-MM-dd hh:mi:ss') from dual;SELECT * FROM emp WHERE hiredate>to_date('1982-1-1','yyyy-mm-dd');SELECT to_number('123.45')+2 FROM dual;SELECT to_number(' ￥1,234,567.89','L999,999,999.99')+2 FROM dual;SELECT to_number(' $1,234,567.89','$999,999,999.99')+2 FROM dual;其他函数SELECT ename, DECODE(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS','others') FROM empSELECT ename,comm,NVL(comm,-1) NVL FROM emp;SELECT COUNT(*) FROM emp;SELECT COUNT(1) FROM emp;SELECT  COUNT(DISTINCT job) FROM emp;SELECT AVG(sal) 平均工资,SUM(sal) 总工资 FROM emp;SELECT MAX(sal) 最高工资,MIN(sal) 最低工资 FROM emp;--统计基本工资超过1000的员工中,每个工种的工人数量,并且将工人数量大于等于3的信息打印出来SELECT job,COUNT(*) 数量 FROM emp WHERE sal>1000 GROUP BY job HAVING COUNT(*)>=3 ORDER BY 数量 ASC;SELECT job,COUNT(*) FROM emp  GROUP BY job HAVING COUNT(*)>=3 ;--where:对分组之前基本数据进行条件筛选--having:对分组之后的结果进行条件筛选,有having必须要有group by