由04年下半年数工下午第二题引发的系列实验6--日期有关

SQL*Plus: Release 10.1.0.2.0 - Production on 星期三 4月 27 15:53:38 2011 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options --刚学的一个函数，当做小礼品 SQL> ed Wrote file afiedt.buf 1 SELECT sys_context('USERENV','db_name') 2* FROM dual SQL> / SYS_CONTEXT('USERENV','DB_NAME') -------------------------------------------------------------------------------- orcl SQL> ed Wrote file afiedt.buf 1 SELECT sys_context('USERENV','language') 2* FROM dual SQL> / SYS_CONTEXT('USERENV','LANGUAGE') -------------------------------------------------------------------------------- SIMPLIFIED CHINESE_CHINA.ZHS16GBK --dupEmp 是 经典的emp表的duplicate SQL> select * from dupEmp 2 / EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 14 rows selected. --sysdate 这个函数很常用 SQL> select sysdate from dual 2 / SYSDATE -------------- 27-4月 -11 --转化函数to_char 可以显示更多的形式 SQL> ed Wrote file afiedt.buf 1 SELECT to_char(sysdate, 'yy-mm-dd hh24:mi:ss') 2* FROM dupEmp SQL> / TO_CHAR(SYSDATE,'Y ------------------ 11-04-27 16:03:21 11-04-27 16:03:21 11-04-27 16:03:21 11-04-27 16:03:21 11-04-27 16:03:21 11-04-27 16:03:21 11-04-27 16:03:21 11-04-27 16:03:21 11-04-27 16:03:21 11-04-27 16:03:21 11-04-27 16:03:21 TO_CHAR(SYSDATE,'Y ------------------ 11-04-27 16:03:21 11-04-27 16:03:21 11-04-27 16:03:21 14 rows selected. SQL> ed Wrote file afiedt.buf 1 SELECT to_char(sysdate, 'yy-mm-dd hh24:mi:ss') 2* FROM dual SQL> / TO_CHAR(SYSDATE,'Y' ------------------ 11-04-27 16:03:49 --查找8个月前入职的员工 SQL> ed Wrote file afiedt.buf 1 SELECT * 2 FROM dupEmp 3* WHERE sysdate>add_months(hiredate, 8) SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 14 rows selected. SQL> ed Wrote file afiedt.buf 1 SELECT * 2 FROM dupEmp 3* WHERE sysdate>add_months(hiredate, 300) SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ---------- 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 12 rows selected. -- 别名可以加“” 也可以不加 -- 但不可以是‘’ SQL> ed Wrote file afiedt.buf 1 SELECT sysdate-hiredate "加入天数" 2* FROM dupEmp SQL> SQL> / 加入天数 ---------- 11088.6715 11023.6715 11021.6715 10982.6715 10803.6715 10953.6715 10914.6715 8774.67153 10753.6715 10823.6715 8740.67153 加入天数 ---------- 10737.6715 10737.6715 10686.6715 14 rows selected. SQL> ed Wrote file afiedt.buf 1 SELECT sysdate-hiredate 加入天数 2* FROM dupEmp SQL> / 加入天数 ---------- 11088.6716 11023.6716 11021.6716 10982.6716 10803.6716 10953.6716 10914.6716 8774.67163 10753.6716 10823.6716 8740.67163 加入天数 ---------- 10737.6716 10737.6716 10686.6716 14 rows selected. SQL> ed Wrote file afiedt.buf 1 SELECT sysdate-hiredate '加入天数' 2* FROM dupEmp SQL> / SELECT sysdate-hiredate '加入天数' * ERROR at line 1: ORA-00923: FROM keyword not found where expected --找出各月倒数第三天受雇的所有员工 SQL> ed Wrote file afiedt.buf 1 SELECT hiredate, ename 2 FROM dupEmp 3* WHERE last_day(hiredate)-2 = hiredate SQL> / HIREDATE ENAME -------------- ---------- 28-9月 -81 MARTIN SQL> set linesize 100 SQL> set pagesize 100 SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 14 rows selected. --找出在1987年受雇的员工 SQL> ed Wrote file afiedt.buf 1 SELECT * 2 FROM dupEmp 3* WHERE to_char(hiredate,'yyyy') ='1987' SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 --其实1989 不加‘’也是OK的 SQL> ed Wrote file afiedt.buf 1 SELECT * 2 FROM dupEmp 3* WHERE to_char(hiredate,'yyyy') =1987 SQL> / EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 --为什么1987 不加‘’也是OK呢？ 因为隐式转化 SQL> ed Wrote file afiedt.buf 1* CREATE TABLE t1(id NUMBER) SQL> / Table created. SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO t1 3* VALUES('10') SQL> / 1 row created. SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO t1 3* VALUES('ab') SQL> / VALUES('ab') * ERROR at line 3: ORA-01722: invalid number SQL> ed Wrote file afiedt.buf 1* CREATE TABLE t2(id VARCHAR2(10)) SQL> / Table created. SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO t2 3* VALUES(1) SQL> / 1 row created. --现在实验 插入date数据 SQL> desc testDate Name Null? Type ----------------------------------------------------- -------- ------------------------------- D DATE SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO testDate 3* VALUES('1991-05-15') SQL> / VALUES('1991-05-15') * ERROR at line 3: ORA-01861: literal does not match format string --要用这种默认格式 SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO testDate 3* VALUES('15-5月-1991') SQL> / 1 row created. --当然用to_date这个转化函数就灵活了~ SQL> ed Wrote file afiedt.buf 1 INSERT 2 INTO testDate 3* VALUES(to_date('1991-05-15','yyyy-mm-dd')) SQL> / 1 row created.