SQL> SELECT * FROM emp WHERE NOT(comm IS NOT NULL AND (sal BETWEEN 1500 AND 3000));
SQL> SELECT * FROM emp WHERE NOT(sal>1000 AND (job LIKE 'C_ER%'));
SQL> SELECT * FROM emp WHERE comm IS NOT NULL AND (sal BETWEEN 1500 AND 3000);
SELECT * FROM emp WHERE NOT((comm BETWEEN 300 AND 500) OR (job LIKE 'C_ER%'));
SELECT * FROM emp WHERE NOT((comm BETWEEN 300 AND 500) AND (job LIKE 'C_ER%'));
SELECT * FROM emp WHERE NOT(job IN ('CLERK','SALESMAN') AND (sal BETWEEN 1000 AND 3000) OR comm IS NOT NULL) ;
SELECT * FROM emp WHERE NOT(job IN ('CLERK','SALESMAN') AND (sal BETWEEN 1000 AND 3000) AND comm IS NOT NULL) ;
SELECT * FROM emp WHERE hiredate BETWEEN '1-1月-81' AND '31-12月-81' ;
SHOW USER;
SELECT * FROM emp ;
SELECT * FROM tab ;
SELECT sal 工资,ename 名字, job 工作 FROM emp ;
SELECT DISTINCT job 工作 FROM emp ;
SELECT DISTINCT job 工作,ename 姓名 FROM emp ;
SELECT ' 姓名是:'||ename ||' 职位是:'||job ||' 年薪是:'||sal*12||' 入职时间是:'||hiredate FROM emp ;
中间最好加空格，以便显示更清晰可视。
SELECT ename 姓名,job 职位,sal*12 年薪 FROM emp ;
clear scr ;
清除屏幕
SELECT SYSDATE FROM DUAL;
显示当前时间
SELECT empno,ename,ROUND((SYSDATE-hiredate)/30) FROM emp ORDER BY hiredate desc;
ORDER BY 行名称 ASC或DESC ，以此行升幂（低到高排序ASC,默认）DES
C
ORDER BY 要在语句的末尾使用，
SELECT * FROM emp WHERE sal>1500 ORDER BY sal ;
SELECT empno , ename, SYSDATE+60 两个月工资, sal*2 工资总数 from emp ;
SELECT empno,ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp ORDER BY hiredate DESC ;
MONTHS_BETWEE 今天至指定时间之间的月数。
SELECT ADD_MONTHS(SYSDATE,4) FROM dual;
和SELECT SYSDATE FROM dual; 做对比 发现多了4个月份；
ADD_MONTHS
此函数表示在指定的日期上 添加指定的月数(SYSDATE,4)
**********其中MONTHS_BETWEEN/ADD_MONTHS 支持“-”（SYSDATE,-4）**********
SELECT SYSDATE,ROUND(MONTHS_BETWEEN('1-1月-90',hiredate) FROM emp;
SElECT SYSDATE,NEXT_DAY(SYSDATE,'星期五') FROM dual;
可以理解成从指定sysdate时间的下一个星期五是什么日期。
SELECT SYSDATE,LAST_DAY('1-1月-90') FROM dual;
90年1月的 最后一天的 日期是。
SELECT ename,empno,TO_CHAR(hiredate,'yyyy'),TO_CHAR(hiredate,'mm'),TO_CHAR(hiredate,'dd') FROM emp;
把原来hiredate的 显示方式改变成“年 月 日 （1981 04 09）”等同于SELECT ename,empno,TO_CHAR(hiredate,'yyyy mm dd') FROM emp;
SELECT ename,empno,TO_CHAR(hiredate,'yyyy*mm*dd') FROM emp; 1986*04*09
SELECT ename,empno,TO_CHAR(hiredate,'yyyy-mm-dd') FROM emp; 1986-04-09
SELECT ename,empno,TO_CHAR(hiredate,'yyyy/mm/dd') FROM emp; 1986/04/09
SELECT ename,empno,TO_CHAR(hiredate,'fmyyyy/mm/dd') FROM emp; 1986/4/9 去掉前导0
TO_CHAR还 可以用作金钱
SELECT ename,job,TO_CHAR(sal,'99,999.99') FROM emp;
其中"99,999.99"表示万位，“.”后面表示查看两位小数，1,650.00
SELECT ename,job,TO_CHAR(sal,'L99,999.99') FROM emp; L会按照使用的语言环境变成金钱的表示方式，"$ 美元" "￥ 人民币" 自动识别添加。
SELECT TO_NUMBER('123'+'123') FROM dual;
等同于SELECT TO_NUMBER('123')+TO_NUMBER('123') FROM dual;
SELECT TO_DATE('2009/04/04','yyyy/mm/dd') FROM dual;
SELECT TO_DATE('2009/04/04','yyyy-mm-dd') FROM dual;结果都是默认"04-4月-09"
练习：
1 SELECT * FROM emp WHERE deptno=30 ;
2 SELECT empno,ename,deptno FROM emp WHERE job=UPPER('clerk') ; job='CLERK' (UPPER)
3 SELECT * FROM emp WHERE comm>sal;
4 SELECT * FROM emp WHERE comm>(sal*0.6);(显示奖金高于薪金60%的员工信息)
5 SELECT * FROM emp WHERE (deptno=10 AND job=UPPER('MANAGER')) OR (deptno=20 AND job=UPPER('clerk'));
6 **************错误答案 SELECT * FROM emp WHERE (deptno=10 AND job=UPPER('manager')) OR (deptno=20 AND job=UPPER('clerk')) OR sal>=2000; 三个条件
****************正确答案 SELECT * FROM emp WHERE (deptno=10 AND job=UPPER('manager')) OR (deptno=20 AND job=UPPER('clerk')) OR (NOT(job=UPPER('manager') OR job=UPPER('clerk')) AND sal>=2000);
等同于SElECT * FROM emp WHERE (depno=10 AND job=UPPER('manager')) OR (deptno=20 AND job=UPPER('clerk')) OR ( job NOT IN(UPPER('manager'),UPPER('clerk')) AND sal>=2000);
7 SElECT DISTINCT job FROM emp WHERE comm IS NOT NULL ;
查看有奖金的职位
8 SElECT * FROM emp WHERE comm IS NULL OR comm>100;
9 SELECT * FROM emp WHERE LAST_DAY(hiredate)-2=hiredate ;
显示每个月倒数第三天入职的人信息
10 SELECT * FROM emp WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/12 > 12 ;
显示入职12年以上的员工信息。
11 SELECT INITCAP(ename) FROM emp ;
12 SELECT ename FROM emp WHERE LENGTH(ename)=5 ;LENGTH统计字符数量
13 SELECT SUUBSTR(ename,0,3) FROM emp ; SUBSTR截取名字的前三位，并显示
14 SELECT REPLACE(ename,'a','A') FROM emp ;REPLACE替换，a替换成A
15 SElECT ename,hiredate FROM emp WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/12 > 10 ;
16 SELECT * FROM emp ORDER BY ename DESC; 降幂
17 SELECT ename,hiredate FROM emp ORDER BY hiredate ASC ;
18
19 SELECT ename,ROUND(sal/30) 日薪 FROM emp ;
20 SELECT ename,TO_CHAR(hiredate,'yyyy') 年份, TO_CHAR(hiredate,'mm') 月份 FROM emp ORDER BY 月份 ASC,年份DESC;
显示员工入职年份与月份，按月份升幂，如月份相同则按照年份将幂排序。
21 SELECT ename,hiredate FROM emp WHERE TO_CHAR(hiredate,'fmmm')=2 ;
显示2月入职的员工，不论年头。fm表示去除前导零,TO_CHAR把日期转换并拆分yyyy mm dd
22 SELECT ename,hiredate,ROUND(SYSDATE-hiredate FROM emp ;
23 SELECT ename FRoM emp WHERE ename LIKE '%A%';
24 SELECT ename,TO_CHAR(hiredate,'yyyy-mm-dd'),TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) FROM emp;
其中显示入职年限应该是小数计算，不能四舍五入，**********所以用TRUNC去整数不进位。
SELECT ename,TRUNC(MOD(MOD(SYSDATE-hiredate,365),30)) FROM emp ;
SELECT ename 名字,TO_CHAR(hiredate, 'yyyy-mm-dd') 入职时间,
TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year,
MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12) month,
TRUNC(MOD(MOD(SYSDATE-hiredate,365),30)) day FROM emp ;
