首页 » Oracle » oracle数据库中的函数和对应的表关系

oracle数据库中的函数和对应的表关系

原文 http://blog.csdn.net/lei_1994/article/details/76724467

2017-08-05 18:20:14阅读(585)

--1、字符函数
--Lower():将指定的字段或者查询的的指定列的字段转为小写;
--一次只能指定一条内容 SELECT * FROM EMP T;
SELECT LOWER('ASDFGGH') FROM EMP T;
SELECT LOWER(T.ENAME) FROM EMP T;
SELECT LOWER(T.JOB) FROM EMP T;


--Upper():将指定的字段或者查询的的指定列的字段转为大写;
SELECT UPPER('aaaaaa') FROM EMP T;
SELECT UPPER(T.ENAME) FROM EMP T;


--Initcap():首字母转为大写,后面位上的字母转为小写
SELECT INITCAP('aAAAAAA') FROM EMP T;
SELECT INITCAP(T.ENAME||T.JOB) FROM EMP T;


--Concat():字符串的连接,将两个字段放在一起
--只有两个参数;
SELECT CONCAT('AAAAA','BBBBB') LIANJIE FROM EMP T;
SELECT CONCAT(T.ENAME,T.JOB)  CONTACT FROM EMP T;


--Substr():截取指定的字符串,含有三个参数
--超出的则不对其做处理;
--*如果指定的一个开始参数是负数,则是从后面往前面进行截取,但是获取的结果顺序不变;
--*如果开始或结束的参数只有一个的情况,正数是从指定的开始位数到末尾;负数是从后面开始截取指定的位数;
SELECT SUBSTR('ABCDEFG',1,3) FROM EMP T;
SELECT SUBSTR('ABCDEFG',3) FROM EMP T;
SELECT SUBSTR('ABCDEFG',-3) FROM EMP T;
SELECT SUBSTR(T.ENAME,2,5) FROM EMP T;
--SELECT SUBSTR('ABCDEFG',1,-3) FROM EMP T;
SELECT SUBSTR(T.ENAME,-2,5) FROM EMP T;


--length():获取指定字符串的个数(可以是数字)
SELECT LENGTH('abcdef') FROM EMP T;
SELECT LENGTH(T.ENAME) FROM EMP T;
SELECT LENGTH(T.ENAME||T.JOB) FROM EMP T;
SELECT LENGTH(T.MGR) FROM EMP T;


--NVL():空值置换,含有两个参数;
SELECT NVL(T.COMM,0) FROM EMP T;




--2、数值函数


--Round():对数字进行四舍五入,含有两个参数,第二位参数为0,表示保留整数部分,不做处理;
--1表示保留个分位,-1表示保留个位;
SELECT ROUND(123.123,0) FROM EMP T;
SELECT ROUND(123.456,1) FROM EMP T;
SELECT ROUND(123.456,-1) FROM EMP T;
SELECT ROUND(T.SAL,-3) FROM EMP T;


--Trunc():获取不进行四舍五入的运算,含有两个参数
--*小数部分不做处理,整数部分直接省去指定位数上的值
SELECT TRUNC(129.431,0) FROM EMP T;
SELECT TRUNC(129.431,2) FROM EMP T;
SELECT TRUNC(129.431,-2) FROM EMP T;


--Mod():取余数,含有两个参数
--???
--*如果除数是负数,结果按照正数进行输出;
--*只有被除数是负数的情况下,运算结果才为负数,且不管除数的正负;
--使用单引号括数字不影响结果;
SELECT MOD(-123.456,2) FROM EMP T;
SELECT MOD(123.456,36) FROM EMP T;
SELECT MOD(T.EMPNO,30) FROM EMP T;
SELECT MOD(T.EMPNO,'-30') FROM EMP T;


--3、日期函数


--Months_Between(date1,date2) 计算两个日期之间的相差的月份
--多出的天数是按照多出天数除以31所得;
SELECT T.HIREDATE+60 ,T.HIREDATE ,MONTHS_BETWEEN( T.HIREDATE+60,T.HIREDATE) FROM EMP T;
--不能使用别名参与运算
--SELECT T.HIREDATE+62 hh ,T.HIREDATE kk ,MONTHS_BETWEEN( hh,kk) FROM EMP T;


--可以结合函数TO_DARE():转换日期的格式;
SELECT MONTHS_BETWEEN(TO_DATE( '2017-08-01','yyyy-mm-dd'),TO_DATE('2017-07-16','yyyy-mm-dd')) FROM EMP T;


--不能直接写上时间,计算机不能识别当前格式,需要转换为时间格式;
SELECT MONTHS_BETWEEN('2017-08-01','2017-07-16') FROM EMP T;


--Add_Months():加月份,直接加在月份数,含有两个参数;
SELECT ADD_MONTHS(TO_DATE('2017-08-01','yyyy-mm-dd'),5) FROM EMP T;
SELECT T.HIREDATE, ADD_MONTHS(T.HIREDATE,6) FROM EMP T;


--Next_Day():得到下一个星期几所对应的日期
--将指定的星期需
--*第二个参数指定数字是以星期天为第一天开始,并且只能是1——7的数字;
--第二个参数为中文;
SELECT NEXT_DAY(TO_DATE('2017-08-01','yyyy-mm-dd'),1) FROM EMP T;
SELECT NEXT_DAY(TO_DATE('2017-08-01','yyyy-mm-dd'),'星期一') FROM EMP T;
SELECT TO_DATE('2017/08/01','yyyy.mm.dd') FROM EMP T;
--此处会报错;
SELECT NEXT_DAY(TO_DATE('2017-08-01','yyyy-mm-dd'),'friday') FROM EMP T;


--Last_Day():获取指定日期所在月份的最后一天的日期,含有一个参数;
SELECT LAST_DAY(TO_DATE('2017-02-01','yyyy-mm-dd')) FROM EMP T;
SELECT T.HIREDATE, LAST_DAY(T.HIREDATE) FROM EMP T;


--Round():对指定日期进行四舍五入,含有两个参数;
--第二个参数指定的是年则操作月,指定月则操作日
--入的规则:月大于等于7进一年;日大于等于16进一月,(DAY)周是周三和周三之后进一周,以周日为进退日;舍则相反
SELECT ROUND(TO_DATE('2017-07-01','yyyy-mm-dd'),'YEAR') FROM EMP T;
SELECT T.HIREDATE, ROUND(T.HIREDATE,'YEAR') FROM EMP T;
SELECT T.HIREDATE, ROUND(T.HIREDATE,'MONTH') FROM EMP T;
SELECT T.HIREDATE, ROUND(T.HIREDATE,'DAY') FROM EMP T;
SELECT ROUND(TO_DATE('2017-08-01','yyyy-mm-dd'),'DAY') FROM EMP T;


--4、转换函数
--*TO_NUMBER():转为数字格式,在字符串中注意的是可以转为数字的才可以进行转换
--日期格式不能转为数字
SELECT TO_NUMBER(TO_DATE('2017-07-01','yyyy-mm-dd')) FROM EMP T;
SELECT TO_NUMBER('000012000') FROM EMP T;
--出现错误字符
SELECT TO_NUMBER('$12345.678') FROM EMP T;
--可以实现进制转换,下是十六进制转为十进制
SELECT TO_NUMBER('88f','xxxx') FROM EMP T;


--TO_DATE():转换日期格式,含有两个参数
--两个参数的格式要一致;
SELECT TO_DATE('2017-08-01','yyyy-mm-dd') FROM EMP T;
SELECT TO_DATE('2017-08-01 21:14:30','yyyy-mm-dd hh24:mi:ss') FROM EMP T;


--TO_CHAR():将日期格式转为字符串;
SELECT TO_CHAR(TO_DATE('2017-08-01 23:12:30','yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') FROM EMP T;


--5分组函数
--注意:WHERE后面不能加分组函数,要想使用分组函数作为条件,必须在GROUP BY后面加HAVING;
--AVG(DISTINCT|ALL):求平均值


--group by 后面只能是列名
--GROUP BY子句中若出现多列时是按照多列联合唯一进行分组
--HAVING后面可以跟分组中的字段名和分组函数,不在分组中字段名,不能跟在HAVING后面
--一般在在select后面指定了列,最好在分组中使用该列,在分组中使用的列则建议在select后面出现
SELECT T.DEPTNO,AVG(T.SAL) FROM EMP T GROUP BY T.Deptno;
SELECT T.DEPTNO,AVG(DISTINCT T.SAL) FROM EMP T GROUP BY T.Deptno;
SELECT T.DEPTNO,AVG(ALL T.SAL) FROM EMP T GROUP BY T.Deptno;


SELECT T.DEPTNO,AVG(ALL T.SAL) AVGSAL 
FROM EMP T 
WHERE T.DEPTNO>10
 GROUP BY T.Deptno
-- HAVING T.DEPTNO>10;
--having后面不能是别名进行运算
HAVING AVG(T.SAL)>1000
ORDER BY AVGSAL;




--MAX (DISTINCT|ALL)  求列的最大值
SELECT T.DEPTNO,MAX(DISTINCT T.SAL) FROM EMP T GROUP BY T.Deptno;
SELECT T.DEPTNO,MAX(ALL T.SAL) FROM EMP T GROUP BY T.Deptno;


SELECT T.DEPTNO,MAX(ALL T.SAL) MAXSAL 
FROM EMP T 
WHERE T.DEPTNO>10
 GROUP BY T.Deptno
-- HAVING T.DEPTNO>10;
--having后面不能是别名进行运算
HAVING MAX(T.SAL)>1000
ORDER BY MAXSAL;




--MIN (DISTINCT|ALL):  求列的最小值
SELECT T.DEPTNO,MIN(DISTINCT T.SAL) FROM EMP T GROUP BY T.Deptno;
SELECT T.DEPTNO,MIN(ALL T.SAL) FROM EMP T GROUP BY T.Deptno;


SELECT T.DEPTNO,MIN(ALL T.SAL) MINSAL 
FROM EMP T 
WHERE T.DEPTNO>10
 GROUP BY T.Deptno
-- HAVING T.DEPTNO>10;
--having后面不能是别名进行运算
HAVING MIN(T.SAL)<1000
ORDER BY MINSAL;


--SUM (DISTINCT|ALL):  求列的总和
SELECT T.DEPTNO,SUM(DISTINCT T.SAL) FROM EMP T GROUP BY T.Deptno;
SELECT T.DEPTNO,SUM(ALL T.SAL) FROM EMP T GROUP BY T.Deptno;


SELECT T.DEPTNO,SUM(ALL T.SAL) SUMSAL 
FROM EMP T 
WHERE T.DEPTNO>10
 GROUP BY T.Deptno
-- HAVING T.DEPTNO>10;
--having后面不能是别名进行运算
HAVING MAX(T.SAL)>100
ORDER BY SUMSAL;


--6多表查询:
--基本语法:
--SELECT  table.column, table.column
--FROM  table1, table2
--WHERE  table1.column1 = table2.column2;


SELECT T1.EMPNO,T2.DEPTNO 
FROM EMP T1,DEPT T2
 WHERE T1.EMPNO!=T2.DEPTNO;




--避免笛卡尔积
SELECT * FROM EMP T,DEPT


--采用连接查询
--通常采用主键和外键进行表的连接
--有重复的列名一定要通过表名分隔,建议重不重复都加上表名,可以大幅度地提高性能
--可以为表名提供别名,操作更加方便一旦给表创建了别名,标识列时只能通过表的别名,不能是表的真名




--外连接  (+)
--外连接,如果匹配不到数据,就以空值补充,要想再添加条件约束,可以在where后面直接添加
--左外连接等同于在“=”右边加“+”,将“=”左边表中未匹配的记录也查找出来
--右外连接等同于在“=”左边加“+”,将“=”右边表中未匹配的记录也查找出来
--外连接只能出现在表达式的一边,以等于号这边为主,(+)为次
--包含外连接的条件不允许  使用in 操作符  使用or操作符连接到另外一个条件




SELECT T1.EMPNO,T2.DEPTNO 
FROM EMP T1,DEPT T2
 WHERE T1.EMPNO=T2.DEPTNO(+)
 ORDER BY T1.EMPNO;
 
 SELECT T1.DEPTNO EM,T2.DEPTNO DE, T2.DNAME DNA
 FROM EMP T1,DEPT T2
WHERE T1.DEPTNO = T2.DEPTNO (+)
ORDER BY EM;




--内连接:inner join
SELECT * FROM EMP E INNER JOIN DEPT T ON E.DEPTNO=T.DEPTNO;


--全连接:full outer join
SELECT * FROM EMP E FULL OUTER JOIN DEPT T ON E.DEPTNO=T.DEPTNO;


--Join()join语法:
select column ... from table1 [left | right]join table2 on table1.id=table2.id;
--如果要加条件,可以在后面添加where 条件  连接后面需要加on


--右连接,是以后面的那张表为主,前面的为此,在对应关系中如果后表有对应的内容,前表没有,则补空;
SELECT T.EMPNO,T.ENAME,D.DEPTNO,D.DNAME FROM EMP T RIGHT JOIN DEPT D ON T.DEPTNO=D.DEPTNO WHERE D.DEPTNO>10;


--左连接则相反,有就对应,没有则为空
SELECT T.EMPNO,T.ENAME,D.DEPTNO,D.DNAME FROM EMP T LEFT JOIN DEPT D ON T.DEPTNO=D.DEPTNO WHERE D.DEPTNO>10;


 


--自连接:
--一张表保存的数据,有上下级关系,可以把表分成两张表来做关联
SELECT WORKER.EMPNO,WORKER.ENAME,WORKER.MGR
FROM (SELECT * FROM EMP T) WORKER,(SELECT * FROM DEPT T) MGR
WHERE WORKER.MGR=MGR.EMPNO AND MGR.EMPNO=7839




--集合运算符
--union:在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,
--删除重复的记录再返回结果。(将两条查询的结果放在一起)
--删除掉重复的记录是整条重复的
--集合运算符是对两个集合操作的,两个集合列表中的列的个数要保持一致,,
--列表中根据顺序,列的字段类型要保持一致至少能隐式转换),
--最终输出的集合的列名由第一个集合的列名来确定。(可以用来连接多个结果);
--集合运算对行进行比较时,认为两个NULL值相等。
SELECT * FROM EMP T WHERE T.MGR=7902
UNION
SELECT * FROM  EMP T WHERE  T.MGR=7698




--union all:将两个结果合并后就返回且不进行排序。
--这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据。


SELECT T.EMPNO,T.ENAME FROM EMP T
UNION ALL
SELECT T1.DEPTNO,T1.DNAME FROM DEPT T1
UNION ALL
SELECT T2.EMPNO,T2.ENAME FROM EMP T2




--minus:两个结果集的差操作,第一个结果集减去第二个结果集中的记录(在第一个结果集也存在的公共记录),
--按默认规则排序,返回的结果集中不包括重复行。
SELECT T.EMPNO,T.ENAME FROM EMP T
MINUS
SELECT T1.EMPNO,T1.ENAME FROM EMP T1 WHERE T1.EMPNO=1587;











最新发布

CentOS专题

关于本站

5ibc.net旗下博客站精品博文小部分原创、大部分从互联网收集整理。尊重作者版权、传播精品博文,让更多编程爱好者知晓!

小提示

按 Ctrl+D 键,
把本文加入收藏夹