Java学习笔记

1、字符串函数：

     CONCAT(p1, p2)

     连接字符串

     SELECT CONCAT(ename, sal) FROM emp

     SELECT CONCAT(CONCAT(ename, ‘:’), sal) FROM emp

     连接字符串还有一种简便的方式：使用”||”，而java中”||”代表或。

     SELECT ename||’:’||sal FROM emp

2、LENGTH(p)函数

     获取字符长度

     SELECT ename,LENGTH(ename) FROM emp

3、UPPER、LOWER、INITCAP

     将字符转换为全大写，全小写，每个单词首字母大写其他字母小写

     SELECT UPPER(‘helloworld’) FROM emp   查出多条数据（emp中有几条查出几条）

     SELECT UPPER(‘helloworld’) FROM dual   dual伪表

     dual：伪表，当查询的内容与任何一张表中的数据无关时，可以查询伪表。

     SELECT LOWER(‘HELLOWORLD’) FROM dual

     SELECT INITCAP(‘hello world’) FROM dual

4、TRIM, LTRIM, RTRIM

     去除字符串两边的指定字符

     SELECT TRIM(‘e’ FROM ‘eeeliteee’) FROM dual

     SELECT LTRIM(‘eeeliteee’,’e’) FROM dual

     SELECT RTRIM(‘eeeliteee’,’e’) FROM dual

     SELECT LTRIM(‘esekeskeksliteseses’,’esk’) FROM dual    从左开始只要字符是’esk’中的任意一个都干掉，直到遇到第一个不是’esk’中的字符，后面的全保留。

5、LPAD, RPAD补位函数

     要求显示指定内容指定位数，若不足则补充若干指定字符以达到要显示的长度，若超了，从左往右截字符串，不管是LPAD还是RPAD。

     SELECT ename,LPAD(sal,5,’$’) FROM emp

6、SUBSTR截取字符串

     截取给定字符串，从指定位置开始截取指定个字符。在数据库中，下标都是从1开始的。

     SELECT SUBSTR(‘thinking in java’, 6,3) FROM dual      //ing

     SELECT SUBSTR(‘thinking in java’, -4,3) FROM dual      //java    -4从后往前数4个，向后截3个，第三个参数不能为负数，最多取到末尾

7、INSTR(char1,char2[,n[,m]])

     查找char2在char1中的位置。n为从指定位置开始查找，可以不写；m为第几次出现，可以不写；n, m不写默认都是1。

     SELECT INSTR(‘thinking in java’,’in’,4,2) FROM dual

8、SELECT ename, sal,deptno FROM emp WHERE LOWER(ename)=’smith’

     SELECT ename, sal, deptno FROM emp WHERE LENGTH(ename)=5

     SELECT ename, sal, deptno FROM emp WHERE LOWER(SUBSTR(ename,1,1))=’a’

9、数字函数

     ROUND(n[,m])  对n进行四舍五入，保留到小数点后m位。

                 m可以不写，不写默认为0。

     m为0则保留到整数位，-1为保留到10位，依此类推。

     SELECT ROUND(45.678, 2) FROM DUAL;     //45.68

     SELECT ROUND(45.678, 0) FROM DUAL;     //46

     SELECT ROUND(45.678) FROM DUAL;      //46

     SELECT ROUND(45.678,-1) FROM DUAL;     //50

     SELECT ROUND(45.678,-2) FROM DUAL;     //0

     SELECT ROUND(55.678,-2) FROM DUAL;     //100

     TRUNC()函数，参数作用与ROUND一致，只是不进行四舍五入，而是直接截取。

                  SELECT TRUNC(45.678, 2) FROMDUAL;      //45.67

                  SELECT TRUNC(45.678, 0) FROMDUAL;      //45

                  SELECT TRUNC(45.678, -1) FROMDUAL;      //40

                  SELECT TRUNC(45.678, -2) FROMDUAL;      //0

     MOD()函数是求余数

                  SELECT ename, sal, MOD(sal,1000) FROM emp;   //看每人工资千位以下的部分

     CEIL和FLOOR向上取整与向下取整

                  SELECT CEIL(45.678) FROMDUAL           //46  向上取整

                  SELECT FLOOR(45.678) FROMDUAL         //45  向下取整

10、日期类型

     DATE：7个字节，保存时间，年月日时分秒

     TIMESTAMP：时间戳，比DATE多4个字节，可以保存秒以下的精度，前7个字节与DATE一致。

     关键字：

     SYSDATE：对应一个内置函数，返回一个表示当前系统时间的DATE类型值。

     SYSTIMESTAMP：同样的，返回的是表示当前系统时间的时间戳类型的值。

     SELECT SYSDATE FROM dual

     SELECT SYSTIMESTAMP FROM dual

     DATE默认只显示”DD-MON-RR”。

     时间戳显示内容较多。

 

     TO_DATE函数

     按照给定的日期格式将字符串解析为DATE类型值。

     SELECT TO_DATE(‘1992-09-01 23:22:11’, ‘YYYY-MM-DD HH24:MI:SS’) FROM dual

     SELECT TO_DATE(‘1992年09月01日 23:22:11’, ‘YYYY”年”MM”月”DD”日” HH24:MI:SS’) FROM dual

     在日期格式字符串中，凡不是英文、符号和数字的其他字符都需要使用双引号括起来。

 

     DATE是可以比较大小的，越晚的越大，越早的越小。

     查看82年以后入职的员工

     SELECT ename, hiredate FROM emp WHERE hiredate>TO_DATE(‘1982-01-01’,‘YYYY-MM-DD’)

     DATE之间可以做减法，差为相差的天数

     查看每个员工至今为止入职多少天了

     SELECT ename, TRUNC(SYSDATE-hiredate) FROM emp  //SYSDATE-hiredate值为小数，TRUNC截取为整数

     SELECT TRUNC(SYSDATE-TO_DATE(‘1990-10-03’,’YYYY-MM-DD’)) FROMdual  //生日到现在多少天

     DATE可以和一个数字进行加减运算，相当于加减了指定的天数，返回值为对应的日期。

     7天后是哪天？

     SELECT SYSDATE+7 FROM dual

 

     TO_CHAR

     常用于转换日期，可以将日期按照指定的日期格式转换为字符串。

     SELECT TO_CHAR(SYSDATE,’YYYY-MM-DD HH24:MI:SS’) FROM dual

     将SYSDATE转换为’YYYY-MM-DDHH24:MI:SS’格式输出。

 

     RR转换二位年份：

USER                 SYS

0-49

50-99

0-49

本世纪

下世纪

50-99

上世纪

本世纪

     SELECT TO_CHAR(TO_DATE(’99-09-01’,’YY-MM-DD’),’YYYY-MM-DD’) FROMdual   //2099

     SELECT TO_CHAR(TO_DATE(’99-09-01’,’RR-MM-DD’),’YYYY-MM-DD’) FROMdual   //1999

 

     LAST_DAY(date)

     该函数返回给定日期所在月的最后一天。

     查看当月月底

     SELECT LAST_DAY(SYSDATE) FROM dual

     查看每个员工入职所在月的月底

     SELECT ename,LAST_DAY(hiredate) FROM emp

 

     ADD_MONTHS(date,i)

     对给定的日期加上指定的月数，若i为正数，则为加月份，i为负数，则为减月份。

     查看每个员工入职20周年的纪念日

     SELECT ename,ADD_MONTHS(hiredate,12*20) FROM emp

 

     MONTH_BETWEEN(date1,date2)

     计算两个日期之间相差的月，计算方式是使用date1-date2的结果换算的。

     查看每个员工至今为止入职多少个月

     SELECT ename, TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp

 

     NEXT_DAY(date,i)

     返回给定日期之后一周内的周几（还没过的最近周几）

     SELECT NEXT_DAY(SYSDATE,2) FROM dual

 

     LEAST, GREATEST

     求最小值与最大值。这组函数的参数不限制数量，两个以上即可。

     SELECT LEAST(SYSDATE,TO_DATE(’2008-10-10’,’YYYY-MM-DD’)) FROM dual

     查看82年以后入职的员工的入职日期，若是82年以前入职的，则显示为1982-01-01

     SELECT ename, GREATEST(hiredate,TO_DATE(‘1982-01-01’,’YYYY-MM-DD’))FROM emp

 

     EXTRACT()

     提取指定日期指定时间分量的值

     SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL

     查看81年入职的员工

     SELECT ename,hiredate FROM emp WHERE EXTRACT(YEAR FROMhiredate)=1981

 

     CREATE TABLE student (id NUMBER(4), name CHAR(20), gender CHAR(1));

     INSERT INTO student VALUES(1000,’李莫愁’,’F’);

     INSERT INTO student VALUES(1001,’林平之’,NULL);         //显式插入NULL值

     INSERT INTO student (id,name) VALUES(1002,’张无忌’);     //隐式插入NULL值

     UPDATE student SET gender=’M’ WHERE gender IS NULL

     判断一个字段的值是否为空，要使用：IS NULL或IS NOT NULL

 

     NULL的运算

     NULL与字符串连接等于什么都没做；NULL与数字运算结果还是NULL。

     NVL(p1,p2)

     若p1为NULL，函数返回p2；若不为NULL，函数返回p1自身。

     所以该函数的作用是将NULL值替换为非NULL值。

     查看每个员工的收入

     SELECT ename,sal,comm. sal+NVL(comm.,0) FROM emp    //奖金是NULL，则替换为0

     查看每个员工的奖金情况：

     若奖金不为NULL的则显示为“有奖金”，为NULL的则显示为“没有奖金”。

     NVL2(p1,p2,p3)

     当p1不为NULL，函数返回p2；当p1为NULL，函数返回p3。

     SELECT ename, comm, NVL2(comm.,’有奖金’,’没有奖金’) FROMemp

     NVL2可以完全实现NVL功能，但是NVL不能实现NVL2的功能。

     SELECT ename,NVL2(comm.,sal+comm,sal) FROM emp

11、查看每个部门的平均工资，前提是该部门平均工资高于2000

     SELECT AVG(sal),deptno FROM emp WHERE AVG(sal)>2000 GROUP BY deptno

     WHERE中不允许使用聚合函数作为过滤条件，原因在于过滤时机不同。

     WHERE的过滤时机是在第一次从表中检索数据时添加过滤条件，用来确定哪些数据可以被查询出来，以确定结果集。

     HAVING子句

     HAVING子句必须跟在GROUP BY子句之后，可以使用聚合函数作为过滤条件。使之可以对分组进行过滤，将满足条件的分组保留，不满足的去掉。

     SELECT AVG(sal),deptno FROM emp GROUP BY deptno HAVING AVG(sal)>2000

     查看平均工资高于2000的部门的最高工资和最低工资

     SELECT MAX(sal),MIN(sal),deptno FROM emp GROUP BY deptno HAVINGAVG(sal)>2000

     WHERE和HAVING的区别：WHERE是在数据查询过程中进行的，HAVING是在数据查询出来并分好组之后进行的。

12、关联查询

     查询的结果集中字段来自多张表，联合多张表查询数据就是关联查询。在关联查询中需要在过滤条件中添加两张表中记录之间的对应关系，这样的条件称为连接条件。N张表联合查询至少要有N-1个连接条件，不添加连接条件会产生笛卡尔积，除特殊情况下，该结果集通常是无意义的，并且由于数据量大，对系统资源消耗有巨大影响。

     查看每个员工的名字以及所在部门名字

     SELECT ename,dname FROM emp,dept WHERE emp.deptno=dept.deptno

     在关联查询中的过滤条件应当与连接条件同时成立。

     查看SALES部门的员工信息

     SELECT ename,dname FROM emp,dept

            WHERE emp.deptno=dept.deptno AND dept.dname=’SALES’

     当查询过程中，出现字段在两张表中都有的情况时，必须明确该字段属于哪张表。

     SELECT e.ename,e.deptno,d.dname FROM emp e,dept d WHEREe.deptno=d.deptno

     SELECT d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND e.ename=’SMITH’

     SELECTe.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND d.loc=’NEW YORK’

     查看平均工资高于2000的部门所在地

     SELECT AVG(e.sal),d.dname,d.loc FROM emp e, dept d WHEREe.deptno=d.deptno

            GROUP BY d.dname,d.loc HAVING AVG(e.sal)>2000

 

     内连接

     关联查询的另一种写法

     查看每个员工以及所在部门

     SELECT e.ename,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno

     查看SALES部门的员工信息

     SELECT e.ename,d.dname FROM emp e

           JOIN dept d ON e.deptno=d.deptno WHERE d.dname=’SALES’

 

     外连接

     外连接在进行关联查询时可以将不满足连接条件的记录也查询出来。外连接分为：左外连接、右外连接、全外连接。

     左外连接：以JOIN左侧表作为驱动表，该表中所有记录全部会列出来，那么当某条记录不满足连接条件时，该记录中来自右侧表中字段的值为NULL。

     右外连接：以JOIN右侧表作为驱动表，该表中所有记录全部会列出来，那么当某条记录不满足连接条件时，该记录中来自左侧表中字段的值为NULL。

     全外连接：左右侧表，没有的字段补NULL。

     SELECT e.ename,d.dname FROM emp e LEFT OUTER JOIN dept d ONe.deptno=d.deptno

                                   RIGHT OUTERJOIN

                                   FULL OUTER JOIN

     SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno(+)=d.deptno 

      //左边表补NULL

     SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno(+)

      //右边表补NULL

  

     自连接

     自连接指的是自己表中的记录对应自己表的多条记录。建立的关联查询就是自连接。

     自连接设计用于解决数据相同，但是存在上下级关系的树状结构使用。

     SELECT empno,ename,mgr FROM emp

     查看每个员工以及其上司的名字

     SELECT e.ename,m.ename FROM emp e,emp m WHERE e.mgr=m.empno(+)

     查看每个领导手下多少人

     SELECT COUNT(*),m.ename FROM emp e, emp m

     WHERE e.mgr=m.empno GROUP BY m.ename

     查看JONES的上司在哪个城市工作

     SELECT e.ename,m.ename,d.loc FROM emp e,emp m,dept d

            WHERE e.mgr=m.empno AND m.deptno=d.deptno AND e.ename=’JONES’

13、子查询

     子查询是一条查询语句，其嵌套在其他SQL语句中，作用是为外层的SQL语句提供数据的。

     和JAMES相同部门的员工

     SELECT deptno FROM emp WHERE ename=’JAMES’

     SELECT ename,deptno FROM emp WHERE deptno=30

     SELECT ename,deptno FROM emp WHERE deptno=(

     SELECT deptno FROM emp WHERE ename=’JAMES’)

     子查询除了常用于DQL之外，也可以在DDL与DML中使用。

     在DDL中应用：使用子查询的结果快速创建一张表。

     CREATE TABLE myemployee

      AS

         SELECT e.empno, e.ename, e.job, e.sal,e.deptno, d.dname, d.loc FROM emp e, dept d

              WHERE e.deptno=d.deptno

  

     DESC myemployee     //查看表结构

     若子查询中查询的内容是函数或表达式，那么该字段必须给别名。

 

     DDL中使用子查询：

     删除和JAMES相同部门的所有员工

     DELETE FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename=’JAMES’)

     把SMITH所在部门的所有员工工资提高10%

     UPDATE emp SET sal=sal*1.1 WHERE deptno=(SELECT deptno FROM emp WHEREename=’SMITH’)

 

     子查询根据查询结果集不同通常分为：

     单行单列：常用在WHERE中（配合=，>，>=等等）

     多行单列：常用在WHERE中（配合IN,ANY,ALL）

     多行多列：常用在FROM中作为表看待

 

     查看比CLERK和SALESMAN工资都高的员工

     SELECT ename,sal FROM emp WHERE sal>ALL(

           SELECT sal FROM emp WHERE job=’SALESMAN’ OR job=’CLERK’)

     查看和CLERK相同部门的其他职位员工

     SELECT ename,job,deptno FROM emp WHERE deptno IN (

           SELECT deptno FROM emp WHERE job=’CLERK’) AND job<>’CLERK’;

 

     EXISTS关键字

     用在WHERE中，其后要跟一个子查询，作用是若子查询至少可以查询出一条记录，那么EXISTS表达式返回真。NOT EXISTS则起到相反的作用，查询不到数据返回真。

     查看有员工的部门

     SELECTd.deptno, d.dname, d.loc FROM dept d WHERE EXISTS(

           SELECT * FROM emp e WHERE e.deptno=d.deptno)

     查看没有下属的员工

     SELECT m.ename FROM emp m WHERE NOT EXISTS(

           SELECT * FROM emp e WHERE e.mgr=m.empno)

     查询列出最低薪水高于部门30的最低薪水的部门的最低薪水

     SELECTe.sal FROM emp e WHERE e.sal>ANY(

           SELECT MIN(sal),deptno FROM emp GROUP BY deptno HAVING

                   MIN(sal)>(SELECTMIN(sal) FROM emp WHERE deptno=30)

     查看高于自己所在部门平均工资的员工信息

     SELECTe.ename,e.deptno,e.sal FROM emp e,(

          SELECT AVG(sal) avg_sal, deptno FROM emp GROUP BY deptno) t

                  WHERE e.deptno=t.deptno AND e.sal>t.avg_sal

     在SELECT子句中使用子查询，通常是外连接的一种写法。

     SELECT e.ename,e.sal,

          (SELECT d.dname FROM dept d WHERE d.deptno=e.deptno) dname

                 FROM emp e

 

14、分页查询

     通常一个查询语句查询的数据量过大时，都会使用分页机制。分页就是将数据分批查询出来，一次只查询部分内容。这样的好处可以减少系统响应时间，减少系统资源开销。分页由于在标准SQL中没有定义，所以不同的数据库语法不相同（方言）。

     ORACLE使用ROWNUM这个伪列来实现分页。ROWNUM，该列不存在于数据库任何表中，但是任何表都可以查询该列，该列在结果集中的值是每条记录的行号，行号从1开始。编号是在查询的过程中进行的，只要可以从表中查询出一条数据，那么该条记录的ROWNUM字段值即为这条记录的行号。

     SELECTROWNUM,ename,job,sal,deptno FROM emp

     在使用ROWNUM对结果集编号的查询过程中不要使用ROWNUM做>1以上数字的判断，否则查询不到任何数据。

     查询6到10条

     SELECT ROWNUM,ename,job,sal,deptno FROM emp WHERE ROWNUM BETWEEN 6 AND10

     //没有任何结果

     SELECT * FROM

           (SELECT ROWNUM rn,ename,job,sal,deptno FROM emp)

                   WHERE rn BETWEEN 6 AND 10

      //显示6到10行，先把号编好，再分页

     若对查询内容有排序需求时，要先进行排序操作。

     取公司工资排名的6-10

     SELECT* FROM(

           SELECT ROWNUM rn, t.* FROM(

                  SELECT ename,sal,job FROM emp ORDER BY sal DESC) t)

                          WHERE rn BETWEEN 6 AND 10

     SELECT* FROM(

           SELECT ROWNUM rn, t.* FROM(

                 SELECTename,sal,job FROM emp ORDER BY sal DESC) t WHERE ROWNUM<=10) WHERErn>=6       //效率更高

  

     换算范围值

     PageSize:每页显示的条数

     Page:页数

     start=(Page-1)*PageSize+1

     end=PageSize*Page

 

     int page = 2;

     int pageSize = 5;

     int start = (page-1)*pageSize+1;

     int end = pageSize*page;

     String sql = “SELECT *”

           +”FROM (SELECT ROWNUM rn, t.*”

           +”FROM (SELECT ename,sal,job”

           +”FROM emp”

           +”ORDER BY sal DESC) t”

           +”WHERE ROWNUM<=”+end+”)”

           +”WHERE rn>=”+start;

 

15、DECODE函数：

      SELECT ename,job,sal,

      DECODE(job,

              ‘MANAGER’,sal*1.2,

              ‘ANALYST’,sal*1.1,

              ‘SALESMAN’,sal*1.05,

              sal) bonus

     FROM emp

 

     CASE语句：

     SELECT ename,job,sal

           CASE job WHEN ‘MANAGER’ THEN sal*1.2

                  WHEN ‘ANALYST’ THEN sal*1.1

                  WHEN ‘SALESMAN’ THEN sal*1.05

           ELSE sal END

        bonus

     FROM emp;

 

     统计人数，将职位是’ANALYST’和’MANAGER’看作一组，将其他职位看作另一组。

     SELECT COUNT(*),

           DECODE(job,

                ‘ANALYST’,’VIP’,

                ‘MANAGER’,’VIP’,

                ‘OTHER’)

     FROM emp

     GROUP BY DECODE(job,

                 ‘ANALYST’,’VIP’,

                 ‘MANAGER’,’VIP’,

                 ‘OTHER’)

     /**GROUP BY子句中的内容必须在SELECT语句中出现**/

     SELECT deptno,dname,loc FROM dept

           ORDER BYDECODE (dname,’OPERATIONS’,1,’ACCOUNTING’,2,’SALES’,3)    //自定义排序