--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;
转载请注明原文地址: https://www.6miu.com/read-26719.html
