SELECT LOWER('SQL: Structural Query Language')
from dual;
//dual 是一个虚表(伪表)。
UPPER(列名|表达式)
SELECT UPPER('sql is used exclusively in rdbmses')
from dual;
SELECT INITCAP('sql is an ENGLISH LIKE language')
from dual;
Output: Sql Is An English Like Language
select concat('SQL allows you to manipulate the data in DB',
' without any programming knowledge')
from dual;
select substr('',14)
from dual;
4-4
//TODO
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> select Lower('SQL:Structural Quary Languae')
2 from dual;
LOWER('SQL:STRUCTURALQUARYLANG
------------------------------
sql:structural quary languae
SQL>
SQL> select uper('sql is used exclusively inrdbmses')
2 from dual;
select uper('sql is used exclusively inrdbmses')
from dual
ORA-00904: "UPER": 标识符无效
SQL>
SQL> select upper('sql is used exclusively inrdbmses')
2 from dual;
UPPER('SQLISUSEDEXCLUSIVELYINR
---------------------------------
SQL IS USED EXCLUSIVELY INRDBMSES
SQL>
SQL> select initcap('sql is used exclusively inrdbmses')
2 from dual;
INITCAP('SQLISUSEDEXCLUSIVELYI
---------------------------------
Sql Is Used Exclusively Inrdbmses
SQL> select concat('i love','baobao')
2 from dual;
CONCAT('ILOVE','BAOBAO')
------------------------
i lovebaobao
SQL> select substr('i need a job',2,5)
2 from dual;
SUBSTR('INEEDAJOB',2,5)
-----------------------
need
SQL> select length('i need a job')
2 from dual;
LENGTH('INEEDAJOB')
-------------------
12
SQL> select instr('i need a job','e')
2 from dual;
INSTR('INEEDAJOB','E')
----------------------
4
SQL> select instr('i need a job','a')
2 from dual;
INSTR('INEEDAJOB','A')
----------------------
8
SQL> select instr('i need a job','z')
2 from dual
3 ;
INSTR('INEEDAJOB','Z')
----------------------
0
SQL> select instr('i need a job','a')
2 from dual
3 ;
INSTR('INEEDAJOB','A')
----------------------
8
SQL> select instr('i need a job','a',7)
2 from dual;
INSTR('INEEDAJOB','A',7)
------------------------
8
SQL> select instr('i need a job','a',11)
2 from dual;
INSTR('INEEDAJOB','A',11)
-------------------------
0
SQL> trim('?'FROM'?SQL*PLUSISTHESQLIMPLEMENTATIONUSEDINANORACLERDBMSORORDBMS.');
trim('?'FROM'?SQL*PLUSISTHESQLIMPLEMENTATIONUSEDINANORACLERDBMSORORDBMS.')
ORA-00900: 无效 SQL 语句
SQL> select trim('?'FROM'?SQL*PLUSISTHESQLIMPLEMENTATIONUSEDINANORACLERDBMSORORDBMS.')
2 from dual;
TRIM('?'FROM'?SQL*PLUSISTHESQL
----------------------------------------------------------
SQL*PLUSISTHESQLIMPLEMENTATIONUSEDINANORACLERDBMSORORDBMS.
SQL> select trim('?' from 'It can process data insetsof rows??')
2 from dual
3 ;
TRIM('?'FROM'ITCANPROCESSDATAI
---------------------------------
It can process data insetsof rows
SQL> select trim('s' from 'It can process data insetsof rows??')
2 from dual;
TRIM('S'FROM'ITCANPROCESSDATAI
-----------------------------------
It can process data insetsof rows??
SQL> select trim('s' from 'It can process data insetsof rows??')
2 from dual;
TRIM('S'FROM'ITCANPROCESSDATAI
-----------------------------------
It can process data insetsof rows??
SQL> select trim(trailing's' from 'It can process data insetsof rows??')
2 from dual;
TRIM(TRAILING'S'FROM'ITCANPROC
-----------------------------------
It can process data insetsof rows??
SQL>
SQL> select trim(trailing's' from 'sIt can process data insetsof rows??')
2 from dual;
TRIM(TRAILING'S'FROM'SITCANPRO
------------------------------------
sIt can process data insetsof rows??
SQL> select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
2 from dual;
3 ;
4 select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
5 from dual;
select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
from dual;
;
select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
from dual
ORA-00907: 缺失右括号
SQL>
SQL> select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
2 from dual;
3 select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
4 from dual;
select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
from dual;
select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
from dual
ORA-00907: 缺失右括号
SQL> select replace('sql*plus supports loops or if statements','suppoerts','does not support')
2 from dual;
REPLACE('SQL*PLUSSUPPORTSLOOPS
----------------------------------------
sql*plus supports loops or if statements
SQL> select replace('sql*plus supports loops or if statements','suppoerts','does not support')
2 from dual;
REPLACE('SQL*PLUSSUPPORTSLOOPS
----------------------------------------
sql*plus supports loops or if statements
SQL>
SQL> select replace('sql*plus supports loops or if statements','supports','does not support')
2 from dual
3 ;
REPLACE('SQL*PLUSSUPPORTSLOOPS
------------------------------------------------
sql*plus does not support loops or if statements
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
12 rows selected
SQL> select empno,ename,job
2 from emp
3 where job='salesman';
EMPNO ENAME JOB
----- ---------- ---------
SQL> select empno,ename,job
2 where job='salesman';
select empno,ename,job
where job='salesman'
ORA-00923: 未找到要求的 FROM 关键字
SQL> select empno,ename,job
2 from emp
3 where job='SALESMAN';
EMPNO ENAME JOB
----- ---------- ---------
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7654 MARTIN SALESMAN
7844 TURNER SALESMAN
SQL> select concat(ename,job)"Employee",substr(job,1,5)"Title",length(ename)"Length",instr(job,'M')
2 from emp
3 where lower(job)="SALESMAN";
select concat(ename,job)"Employee",substr(job,1,5)"Title",length(ename)"Length",instr(job,'M')
from emp
where lower(job)="SALESMAN"
ORA-00904: "SALESMAN": 标识符无效
SQL> select concat(ename,job)"Employee",substr(job,1,5)"Title",length(ename)"Length",instr(job,'M')
2 from emp
3 where lower(job)="salesman";
select concat(ename,job)"Employee",substr(job,1,5)"Title",length(ename)"Length",instr(job,'M')
from emp
where lower(job)="salesman"
ORA-00904: "salesman": 标识符无效
SQL> select concat(ename,job)"Employee",substr(job,1,5)"Title",length(ename)"Length",instr(job,'M')
2 from emp
3 where lower(job)='salesman';
Employee Title Length INSTR(JOB,'M')
------------------- ---------- ---------- --------------
ALLENSALESMAN SALES 5 6
WARDSALESMAN SALES 4 6
MARTINSALESMAN SALES 6 6
TURNERSALESMAN SALES 6 6
SQL> select round(166.888,1),trunc(166.88,1)
2 from dual;
ROUND(166.888,1) TRUNC(166.88,1)
---------------- ---------------
166.9 166.8
SQL> select round(166.888,2),trunc(166.88,2)
2 from dual;
ROUND(166.888,2) TRUNC(166.88,2)
---------------- ---------------
166.89 166.88
SQL> select round(166.448,2),trunc(166.448,2)
2 from dual;
ROUND(166.448,2) TRUNC(166.448,2)
---------------- ----------------
166.45 166.44
SQL> select round(166.444,2),trunc(166.444,2)
2 from dual;
ROUND(166.444,2) TRUNC(166.444,2)
---------------- ----------------
166.44 166.44
SQL> select round(166.888),trunc(166.888)
2 from dual;
ROUND(166.888) TRUNC(166.888)
-------------- --------------
167 166
SQL> select round(166.888,-1),trunc(166.888,-1)
2 from dual;
ROUND(166.888,-1) TRUNC(166.888,-1)
----------------- -----------------
170 160
SQL> select mod(1900,400)
2 from dual;
MOD(1900,400)
-------------
300
SQL> select mod(200,400)
2 from dual;
MOD(200,400)
------------
200
SQL> alter session set NLS_DATE_LANGUAGE='AMERICAN';
Session altered
SQL> COMMIT;
Commit complete
SQL> select sysdate
2 from dual;
SYSDATE
-----------
2009-8-24 1
SQL> select sysdate - 10
2 from dual;
SYSDATE-10
-----------
2009-8-14 1
SQL> select sysdate + 10
2 from dual;
SYSDATE+10
-----------
2009-9-3 14
SQL> select TO_DATE('2009-10-1')-sysdate
2 from dual;
select TO_DATE('2009-10-1')-sysdate
from dual
ORA-01861: 文字与格式字符串不匹配
SQL> select TO_DATE('15-jul-2010')-sysdate
2 from dual;
TO_DATE('15-JUL-2010')-SYSDATE
------------------------------
324.378356481481
SQL> select sysdate - 1/24
2 from dual;
SYSDATE-1/24
------------
2009-8-24 13
SQL> select sysdate - 24/24
2 from dual;
SYSDATE-24/24
-------------
2009-8-23 14:
SQL> select empno,ename,job,sal,(sysdate-hiredate)/365 "years"
2 from emp
3 where job like'SAL%';
EMPNO ENAME JOB SAL years
----- ---------- --------- --------- ----------
7499 ALLEN SALESMAN 1600.00 28.5277370
7521 WARD SALESMAN 1250.00 28.5222576
7654 MARTIN SALESMAN 1250.00 27.9249973
7844 TURNER SALESMAN 1500.00 27.9797918
SQL> select months_between('01-jul-99','03-feb-98')
2 from dual;
MONTHS_BETWEEN('01-JUL-99','03
------------------------------
16.9354838709677
SQL> select add_months('15-oct-01',8)
2 from dual;
ADD_MONTHS('15-OCT-01',8)
-------------------------
2002-6-15
SQL> select add_months('15-oct-01',2)
2 from dual;
ADD_MONTHS('15-OCT-01',2)
-------------------------
2001-12-15
SQL> select next_day(sysdate,'monday')
2 from dual;
NEXT_DAY(SYSDATE,'MONDAY')
--------------------------
2009-8-31 15:01:14
SQL> select last-day(sysdate)
2 from dual;
select last-day(sysdate)
from dual
ORA-00904: "DAY": 标识符无效
SQL> select last_day(sysdate)
2 from dual;
LAST_DAY(SYSDATE)
-----------------
2009-8-31 15:02:2
SQL> select ename,hiredate,last_day(hiredate),next_day(hiredate,'sunday'),months_between(sysdate,hiredate)"months",add_months(hiredate , 3)"review"
2 from emp;
ENAME HIREDATE LAST_DAY(HIREDATE) NEXT_DAY(HIREDATE,'SUNDAY') months review
---------- ----------- ------------------ --------------------------- ---------- -----------
SMITH 1980-12-17 1980-12-31 1980-12-21 344.246053 1981-3-17
ALLEN 1981-2-20 1981-2-28 1981-2-22 342.149279 1981-5-20
WARD 1981-2-22 1981-2-28 1981-3-1 342.084762 1981-5-22
JONES 1981-4-2 1981-4-30 1981-4-5 340.729924 1981-7-2
MARTIN 1981-9-28 1981-9-30 1981-10-4 334.891214 1981-12-28
BLAKE 1981-5-1 1981-5-31 1981-5-3 339.762182 1981-8-1
CLARK 1981-6-9 1981-6-30 1981-6-14 338.504117 1981-9-9
KING 1981-11-17 1981-11-30 1981-11-22 333.246053 1982-2-17
TURNER 1981-9-8 1981-9-30 1981-9-13 335.536375 1981-12-8
JAMES 1981-12-3 1981-12-31 1981-12-6 332.697666 1982-3-3
FORD 1981-12-3 1981-12-31 1981-12-6 332.697666 1982-3-3
MILLER 1982-1-23 1982-1-31 1982-1-24 331.052504 1982-4-23
12 rows selected
SQL> select ename,hiredate,last_day(hiredate),next_day(hiredate,'星期日'),months_between(sysdate,hiredate)"months",add_months(hiredate , 3)"review"
2 from emp;
select ename,hiredate,last_day(hiredate),next_day(hiredate,'星期日'),months_between(sysdate,hiredate)"months",add_months(hiredate , 3)"review"
from emp
ORA-01846: 周中的日无效
SQL> select round('28-oct-01','month')
2 from dual;
select round('28-oct-01','month')
from dual
ORA-01722: 无效数字
SQL> select round(to_date('28-oct-01'),'month')
2 from dua;
select round(to_date('28-oct-01'),'month')
from dua
ORA-00942: 表或视图不存在
SQL> select round(to_date('28-oct-01'),'month')
2 from dual;
ROUND(TO_DATE('28-OCT-01'),'MO
------------------------------
2001-11-1
SQL> select round(to_date('28-oct-01'),'year')
2 from dual;
ROUND(TO_DATE('28-OCT-01'),'YE
------------------------------
2002-1-1
SQL> select round(to_date('28-may-01'),'year')
2 from dual;
ROUND(TO_DATE('28-MAY-01'),'YE
------------------------------
2001-1-1
SQL> select round(to_date('28-june-01'),'year')
2 from dual;
ROUND(TO_DATE('28-JUNE-01'),'Y
------------------------------
2001-1-1
SQL> select round(to_date('28-step-01'),'year')
2 from dual;
select round(to_date('28-step-01'),'year')
from dual
ORA-01843: 无效的月份
SQL> select round(to_date('28-july-01'),'year')
2 from dual;
ROUND(TO_DATE('28-JULY-01'),'Y
------------------------------
2002-1-1
SQL> select trunc(to_date('28-step-01'),'year')
2 from dual;
select trunc(to_date('28-step-01'),'year')
from dual
ORA-01843: 无效的月份
SQL> select round(to_date('28-july-01'),'year')
2 select trunc(to_date('28-step-01'),'year')
3
SQL> select trunc(to_date('28-july-01'),'year')
2 from dual;
TRUNC(TO_DATE('28-JULY-01'),'Y
------------------------------
2001-1-1
SQL> select trunc(to_date('28-feb-01'),'year')
2 from dual;
TRUNC(TO_DATE('28-FEB-01'),'YE
------------------------------
2001-1-1
SQL> select trunc(to_date('28-oct-01'),'year')
2 from dual;
TRUNC(TO_DATE('28-OCT-01'),'YE
------------------------------
2001-1-1
SQL> select ename,hiredate,round(hiredate,'year'),trunc(hiredate,'year'),round(hiredate,'month'),turnc(hiredate,'month')
2 from emp
3 where hiredate like"%81";
select ename,hiredate,round(hiredate,'year'),trunc(hiredate,'year'),round(hiredate,'month'),turnc(hiredate,'month')
from emp
where hiredate like"%81"
ORA-00904: "%81": 标识符无效
SQL> select ename,hiredate,round(hiredate,'year'),trunc(hiredate,'year'),round(hiredate,'month'),turnc(hiredate,'month')
2 from emp
3 where hiredate like'%81';
select ename,hiredate,round(hiredate,'year'),trunc(hiredate,'year'),round(hiredate,'month'),turnc(hiredate,'month')
from emp
where hiredate like'%81'
ORA-00904: "TURNC": 标识符无效
SQL> select ename,hiredate,round(hiredate,'year'),trunc(hiredate,'year'),round(hiredate,'month'),trunc(hiredate,'month')
2 from emp
3 where hiredate like'%81';
ENAME HIREDATE ROUND(HIREDATE,'YEAR') TRUNC(HIREDATE,'YEAR') ROUND(HIREDATE,'MONTH') TRUNC(HIREDATE,'MONTH')
---------- ----------- ---------------------- ---------------------- ----------------------- -----------------------
ALLEN 1981-2-20 1981-1-1 1981-1-1 1981-3-1 1981-2-1
WARD 1981-2-22 1981-1-1 1981-1-1 1981-3-1 1981-2-1
JONES 1981-4-2 1981-1-1 1981-1-1 1981-4-1 1981-4-1
MARTIN 1981-9-28 1982-1-1 1981-1-1 1981-10-1 1981-9-1
BLAKE 1981-5-1 1981-1-1 1981-1-1 1981-5-1 1981-5-1
CLARK 1981-6-9 1981-1-1 1981-1-1 1981-6-1 1981-6-1
KING 1981-11-17 1982-1-1 1981-1-1 1981-12-1 1981-11-1
TURNER 1981-9-8 1982-1-1 1981-1-1 1981-9-1 1981-9-1
JAMES 1981-12-3 1982-1-1 1981-1-1 1981-12-1 1981-12-1
FORD 1981-12-3 1982-1-1 1981-1-1 1981-12-1 1981-12-1
10 rows selected
SQL> select ename,to_char(hiredate,'dd/mm/yy')
2 from emp
3 where hiredate like'%82';
ENAME TO_CHAR(HIREDATE,'DD/MM/YY')
---------- ----------------------------
MILLER 23/01/82
SQL> select ename,to_char(hiredate,'mm/dd/yy')
2 form emp
3 where hiredate like '%82';
select ename,to_char(hiredate,'mm/dd/yy')
form emp
where hiredate like '%82'
ORA-00923: 未找到要求的 FROM 关键字
SQL> select ename,to_char(hiredate,'mm/dd/yy')
2 from emp
3 where hiredate like '%82';
ENAME TO_CHAR(HIREDATE,'MM/DD/YY')
---------- ----------------------------
MILLER 01/23/82
SQL>
SQL> select to_char(sysdate,'fmdd month year')
2 from dual;
TO_CHAR(SYSDATE,'FMDDMONTHYEAR
-----------------------------------------------------------
24 august two thousand nine
SQL> select ename"name",sal"salary",
2 to_char(hiredate,'fmDdspth "of" Month year fmHH:MI:SS AM') hiredate
3 from emp;
name salary HIREDATE
---------- --------- --------------------------------------------------------------------------------
SMITH 800.00 Seventeenth of December nineteen eighty 12:00:00 AM
ALLEN 1600.00 Twentieth of February nineteen eighty-one 12:00:00 AM
WARD 1250.00 Twenty-Second of February nineteen eighty-one 12:00:00 AM
JONES 2975.00 Second of April nineteen eighty-one 12:00:00 AM
MARTIN 1250.00 Twenty-Eighth of September nineteen eighty-one 12:00:00 AM
BLAKE 2850.00 First of May nineteen eighty-one 12:00:00 AM
CLARK 2450.00 Ninth of June nineteen eighty-one 12:00:00 AM
KING 5000.00 Seventeenth of November nineteen eighty-one 12:00:00 AM
TURNER 1500.00 Eighth of September nineteen eighty-one 12:00:00 AM
JAMES 950.00 Third of December nineteen eighty-one 12:00:00 AM
FORD 3000.00 Third of December nineteen eighty-one 12:00:00 AM
MILLER 1300.00 Twenty-Third of January nineteen eighty-two 12:00:00 AM
12 rows selected
SQL> select ename "Name",to_char(sal*12,'$99,999.00') "Annual Salary"
2 from emp;
Name Annual Salary
---------- -------------
SMITH $9,600.00
ALLEN $19,200.00
WARD $15,000.00
JONES $35,700.00
MARTIN $15,000.00
BLAKE $34,200.00
CLARK $29,400.00
KING $60,000.00
TURNER $18,000.00
JAMES $11,400.00
FORD $36,000.00
MILLER $15,600.00
12 rows selected
SQL> select ename "Name",to_char(sal*12,'L99,999.00') "Annual Salary"
2 from emp;
Name Annual Salary
---------- --------------------
SMITH ￥9,600.00
ALLEN ￥19,200.00
WARD ￥15,000.00
JONES ￥35,700.00
MARTIN ￥15,000.00
BLAKE ￥34,200.00
CLARK ￥29,400.00
KING ￥60,000.00
TURNER ￥18,000.00
JAMES ￥11,400.00
FORD ￥36,000.00
MILLER ￥15,600.00
12 rows selected
SQL> select ename "Name",to_char(sal*12,'L9,999.00') "Annual Salary"
2 from emp;
Name Annual Salary
---------- -------------------
SMITH ￥9,600.00
ALLEN ###################
WARD ###################
JONES ###################
MARTIN ###################
BLAKE ###################
CLARK ###################
KING ###################
TURNER ###################
JAMES ###################
FORD ###################
MILLER ###################
12 rows selected
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between '01-feb-81' and '31-dec-81'
4 order by hiredate;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
ALLEN SALESMAN 1600.00 1981-2-20
WARD SALESMAN 1250.00 1981-2-22
JONES MANAGER 2975.00 1981-4-2
BLAKE MANAGER 2850.00 1981-5-1
CLARK MANAGER 2450.00 1981-6-9
TURNER SALESMAN 1500.00 1981-9-8
MARTIN SALESMAN 1250.00 1981-9-28
KING PRESIDENT 5000.00 1981-11-17
FORD ANALYST 3000.00 1981-12-3
JAMES CLERK 950.00 1981-12-3
10 rows selected
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between '01-feb-81' and '31-dec-81'
4 order by hiredate desc;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
JAMES CLERK 950.00 1981-12-3
FORD ANALYST 3000.00 1981-12-3
KING PRESIDENT 5000.00 1981-11-17
MARTIN SALESMAN 1250.00 1981-9-28
TURNER SALESMAN 1500.00 1981-9-8
CLARK MANAGER 2450.00 1981-6-9
BLAKE MANAGER 2850.00 1981-5-1
JONES MANAGER 2975.00 1981-4-2
WARD SALESMAN 1250.00 1981-2-22
ALLEN SALESMAN 1600.00 1981-2-20
10 rows selected
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between '01-feb-81' and '31-dec-81'
4 order by hiredate desc;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
JAMES CLERK 950.00 1981-12-3
FORD ANALYST 3000.00 1981-12-3
KING PRESIDENT 5000.00 1981-11-17
MARTIN SALESMAN 1250.00 1981-9-28
TURNER SALESMAN 1500.00 1981-9-8
CLARK MANAGER 2450.00 1981-6-9
BLAKE MANAGER 2850.00 1981-5-1
JONES MANAGER 2975.00 1981-4-2
WARD SALESMAN 1250.00 1981-2-22
ALLEN SALESMAN 1600.00 1981-2-20
10 rows selected
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between '01-1月-81' and '31-12月-81'
4 order by hiredate desc;
select ename "Name",job,sal as "Salary",hiredate
from emp
where hiredate between '01-1月-81' and '31-12月-81'
order by hiredate desc
ORA-01843: 无效的月份
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from em
3 where hiredate between to_date('01-jan-81',dd-mm-yy) and to_date('31-dec-81','dd-mm-yy')
4 order by hiredate;
select ename "Name",job,sal as "Salary",hiredate
from em
where hiredate between to_date('01-jan-81',dd-mm-yy) and to_date('31-dec-81','dd-mm-yy')
order by hiredate
ORA-00942: 表或视图不存在
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81',dd-mm-yy) and to_date('31-dec-81','dd-mm-yy')
4 order by hiredate;
select ename "Name",job,sal as "Salary",hiredate
from emp
where hiredate between to_date('01-jan-81',dd-mm-yy) and to_date('31-dec-81','dd-mm-yy')
order by hiredate
ORA-00904: "YY": 标识符无效
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81',dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
4 order by hiredate;
5
6 where hiredate between to_date('01-jan-81',dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
7
SQL>
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81',dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
4 order by hiredatel
5 ;
6 ;
7
8
9
10
11
12
13
14 fda
15
16 fda
17
18
19
20
21 fa
22 ;
23 ;;
24
25
26
27 select ename "Name",job,sal as "Salary",hiredate
28 from emp
29 where hiredate between to_date('01-jan-81',dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
30 order by hiredate;
select ename "Name",job,sal as "Salary",hiredate
from emp
where hiredate between to_date('01-jan-81',dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
order by hiredatel
;
;
fda
fda
fa
;
;;
select ename "Name",job,sal as "Salary",hiredate
from emp
where hiredate between to_date('01-jan-81',dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
order by hiredate
ORA-00907: 缺失右括号
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81','dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
4 order by hiredate;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
SQL> select todate('01-jan-81','dd-mm-yy')
2 from dual;
select todate('01-jan-81','dd-mm-yy')
from dual
ORA-00904: "TODATE": 标识符无效
SQL> select to_date('01-jan-81','dd-mm-yy')
2 from dual;
TO_DATE('01-JAN-81','DD-MM-YY'
------------------------------
2081-1-1
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from dual;
select ename "Name",job,sal as "Salary",hiredate
from dual
ORA-00904: "HIREDATE": 标识符无效
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81','dd-mon-yy') and to_date('31-dec-81','dd-mon-yy')
4 order by hiredate;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
SQL> select hiredate from emp;
HIREDATE
-----------
1980-12-17
1981-2-20
1981-2-22
1981-4-2
1981-9-28
1981-5-1
1981-6-9
1981-11-17
1981-9-8
1981-12-3
1981-12-3
1982-1-23
12 rows selected
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81') and to_date('31-dec-81')
4 order by hiredate;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
ALLEN SALESMAN 1600.00 1981-2-20
WARD SALESMAN 1250.00 1981-2-22
JONES MANAGER 2975.00 1981-4-2
BLAKE MANAGER 2850.00 1981-5-1
CLARK MANAGER 2450.00 1981-6-9
TURNER SALESMAN 1500.00 1981-9-8
MARTIN SALESMAN 1250.00 1981-9-28
KING PRESIDENT 5000.00 1981-11-17
FORD ANALYST 3000.00 1981-12-3
JAMES CLERK 950.00 1981-12-3
10 rows selected
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81','dd-mon-rr') and to_date('31-dec-81','dd-mon-rr')
4 order by hiredate;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
ALLEN SALESMAN 1600.00 1981-2-20
WARD SALESMAN 1250.00 1981-2-22
JONES MANAGER 2975.00 1981-4-2
BLAKE MANAGER 2850.00 1981-5-1
CLARK MANAGER 2450.00 1981-6-9
TURNER SALESMAN 1500.00 1981-9-8
MARTIN SALESMAN 1250.00 1981-9-28
KING PRESIDENT 5000.00 1981-11-17
FORD ANALYST 3000.00 1981-12-3
JAMES CLERK 950.00 1981-12-3
10 rows selected
SQL>
from dual;
//dual 是一个虚表(伪表)。
UPPER(列名|表达式)
SELECT UPPER('sql is used exclusively in rdbmses')
from dual;
SELECT INITCAP('sql is an ENGLISH LIKE language')
from dual;
Output: Sql Is An English Like Language
select concat('SQL allows you to manipulate the data in DB',
' without any programming knowledge')
from dual;
select substr('',14)
from dual;
4-4
//TODO
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> select Lower('SQL:Structural Quary Languae')
2 from dual;
LOWER('SQL:STRUCTURALQUARYLANG
------------------------------
sql:structural quary languae
SQL>
SQL> select uper('sql is used exclusively inrdbmses')
2 from dual;
select uper('sql is used exclusively inrdbmses')
from dual
ORA-00904: "UPER": 标识符无效
SQL>
SQL> select upper('sql is used exclusively inrdbmses')
2 from dual;
UPPER('SQLISUSEDEXCLUSIVELYINR
---------------------------------
SQL IS USED EXCLUSIVELY INRDBMSES
SQL>
SQL> select initcap('sql is used exclusively inrdbmses')
2 from dual;
INITCAP('SQLISUSEDEXCLUSIVELYI
---------------------------------
Sql Is Used Exclusively Inrdbmses
SQL> select concat('i love','baobao')
2 from dual;
CONCAT('ILOVE','BAOBAO')
------------------------
i lovebaobao
SQL> select substr('i need a job',2,5)
2 from dual;
SUBSTR('INEEDAJOB',2,5)
-----------------------
need
SQL> select length('i need a job')
2 from dual;
LENGTH('INEEDAJOB')
-------------------
12
SQL> select instr('i need a job','e')
2 from dual;
INSTR('INEEDAJOB','E')
----------------------
4
SQL> select instr('i need a job','a')
2 from dual;
INSTR('INEEDAJOB','A')
----------------------
8
SQL> select instr('i need a job','z')
2 from dual
3 ;
INSTR('INEEDAJOB','Z')
----------------------
0
SQL> select instr('i need a job','a')
2 from dual
3 ;
INSTR('INEEDAJOB','A')
----------------------
8
SQL> select instr('i need a job','a',7)
2 from dual;
INSTR('INEEDAJOB','A',7)
------------------------
8
SQL> select instr('i need a job','a',11)
2 from dual;
INSTR('INEEDAJOB','A',11)
-------------------------
0
SQL> trim('?'FROM'?SQL*PLUSISTHESQLIMPLEMENTATIONUSEDINANORACLERDBMSORORDBMS.');
trim('?'FROM'?SQL*PLUSISTHESQLIMPLEMENTATIONUSEDINANORACLERDBMSORORDBMS.')
ORA-00900: 无效 SQL 语句
SQL> select trim('?'FROM'?SQL*PLUSISTHESQLIMPLEMENTATIONUSEDINANORACLERDBMSORORDBMS.')
2 from dual;
TRIM('?'FROM'?SQL*PLUSISTHESQL
----------------------------------------------------------
SQL*PLUSISTHESQLIMPLEMENTATIONUSEDINANORACLERDBMSORORDBMS.
SQL> select trim('?' from 'It can process data insetsof rows??')
2 from dual
3 ;
TRIM('?'FROM'ITCANPROCESSDATAI
---------------------------------
It can process data insetsof rows
SQL> select trim('s' from 'It can process data insetsof rows??')
2 from dual;
TRIM('S'FROM'ITCANPROCESSDATAI
-----------------------------------
It can process data insetsof rows??
SQL> select trim('s' from 'It can process data insetsof rows??')
2 from dual;
TRIM('S'FROM'ITCANPROCESSDATAI
-----------------------------------
It can process data insetsof rows??
SQL> select trim(trailing's' from 'It can process data insetsof rows??')
2 from dual;
TRIM(TRAILING'S'FROM'ITCANPROC
-----------------------------------
It can process data insetsof rows??
SQL>
SQL> select trim(trailing's' from 'sIt can process data insetsof rows??')
2 from dual;
TRIM(TRAILING'S'FROM'SITCANPRO
------------------------------------
sIt can process data insetsof rows??
SQL> select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
2 from dual;
3 ;
4 select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
5 from dual;
select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
from dual;
;
select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
from dual
ORA-00907: 缺失右括号
SQL>
SQL> select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
2 from dual;
3 select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
4 from dual;
select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
from dual;
select replace('sql*plus supports loops or if statements','suppoerts','doesn't support')
from dual
ORA-00907: 缺失右括号
SQL> select replace('sql*plus supports loops or if statements','suppoerts','does not support')
2 from dual;
REPLACE('SQL*PLUSSUPPORTSLOOPS
----------------------------------------
sql*plus supports loops or if statements
SQL> select replace('sql*plus supports loops or if statements','suppoerts','does not support')
2 from dual;
REPLACE('SQL*PLUSSUPPORTSLOOPS
----------------------------------------
sql*plus supports loops or if statements
SQL>
SQL> select replace('sql*plus supports loops or if statements','supports','does not support')
2 from dual
3 ;
REPLACE('SQL*PLUSSUPPORTSLOOPS
------------------------------------------------
sql*plus does not support loops or if statements
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
12 rows selected
SQL> select empno,ename,job
2 from emp
3 where job='salesman';
EMPNO ENAME JOB
----- ---------- ---------
SQL> select empno,ename,job
2 where job='salesman';
select empno,ename,job
where job='salesman'
ORA-00923: 未找到要求的 FROM 关键字
SQL> select empno,ename,job
2 from emp
3 where job='SALESMAN';
EMPNO ENAME JOB
----- ---------- ---------
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7654 MARTIN SALESMAN
7844 TURNER SALESMAN
SQL> select concat(ename,job)"Employee",substr(job,1,5)"Title",length(ename)"Length",instr(job,'M')
2 from emp
3 where lower(job)="SALESMAN";
select concat(ename,job)"Employee",substr(job,1,5)"Title",length(ename)"Length",instr(job,'M')
from emp
where lower(job)="SALESMAN"
ORA-00904: "SALESMAN": 标识符无效
SQL> select concat(ename,job)"Employee",substr(job,1,5)"Title",length(ename)"Length",instr(job,'M')
2 from emp
3 where lower(job)="salesman";
select concat(ename,job)"Employee",substr(job,1,5)"Title",length(ename)"Length",instr(job,'M')
from emp
where lower(job)="salesman"
ORA-00904: "salesman": 标识符无效
SQL> select concat(ename,job)"Employee",substr(job,1,5)"Title",length(ename)"Length",instr(job,'M')
2 from emp
3 where lower(job)='salesman';
Employee Title Length INSTR(JOB,'M')
------------------- ---------- ---------- --------------
ALLENSALESMAN SALES 5 6
WARDSALESMAN SALES 4 6
MARTINSALESMAN SALES 6 6
TURNERSALESMAN SALES 6 6
SQL> select round(166.888,1),trunc(166.88,1)
2 from dual;
ROUND(166.888,1) TRUNC(166.88,1)
---------------- ---------------
166.9 166.8
SQL> select round(166.888,2),trunc(166.88,2)
2 from dual;
ROUND(166.888,2) TRUNC(166.88,2)
---------------- ---------------
166.89 166.88
SQL> select round(166.448,2),trunc(166.448,2)
2 from dual;
ROUND(166.448,2) TRUNC(166.448,2)
---------------- ----------------
166.45 166.44
SQL> select round(166.444,2),trunc(166.444,2)
2 from dual;
ROUND(166.444,2) TRUNC(166.444,2)
---------------- ----------------
166.44 166.44
SQL> select round(166.888),trunc(166.888)
2 from dual;
ROUND(166.888) TRUNC(166.888)
-------------- --------------
167 166
SQL> select round(166.888,-1),trunc(166.888,-1)
2 from dual;
ROUND(166.888,-1) TRUNC(166.888,-1)
----------------- -----------------
170 160
SQL> select mod(1900,400)
2 from dual;
MOD(1900,400)
-------------
300
SQL> select mod(200,400)
2 from dual;
MOD(200,400)
------------
200
SQL> alter session set NLS_DATE_LANGUAGE='AMERICAN';
Session altered
SQL> COMMIT;
Commit complete
SQL> select sysdate
2 from dual;
SYSDATE
-----------
2009-8-24 1
SQL> select sysdate - 10
2 from dual;
SYSDATE-10
-----------
2009-8-14 1
SQL> select sysdate + 10
2 from dual;
SYSDATE+10
-----------
2009-9-3 14
SQL> select TO_DATE('2009-10-1')-sysdate
2 from dual;
select TO_DATE('2009-10-1')-sysdate
from dual
ORA-01861: 文字与格式字符串不匹配
SQL> select TO_DATE('15-jul-2010')-sysdate
2 from dual;
TO_DATE('15-JUL-2010')-SYSDATE
------------------------------
324.378356481481
SQL> select sysdate - 1/24
2 from dual;
SYSDATE-1/24
------------
2009-8-24 13
SQL> select sysdate - 24/24
2 from dual;
SYSDATE-24/24
-------------
2009-8-23 14:
SQL> select empno,ename,job,sal,(sysdate-hiredate)/365 "years"
2 from emp
3 where job like'SAL%';
EMPNO ENAME JOB SAL years
----- ---------- --------- --------- ----------
7499 ALLEN SALESMAN 1600.00 28.5277370
7521 WARD SALESMAN 1250.00 28.5222576
7654 MARTIN SALESMAN 1250.00 27.9249973
7844 TURNER SALESMAN 1500.00 27.9797918
SQL> select months_between('01-jul-99','03-feb-98')
2 from dual;
MONTHS_BETWEEN('01-JUL-99','03
------------------------------
16.9354838709677
SQL> select add_months('15-oct-01',8)
2 from dual;
ADD_MONTHS('15-OCT-01',8)
-------------------------
2002-6-15
SQL> select add_months('15-oct-01',2)
2 from dual;
ADD_MONTHS('15-OCT-01',2)
-------------------------
2001-12-15
SQL> select next_day(sysdate,'monday')
2 from dual;
NEXT_DAY(SYSDATE,'MONDAY')
--------------------------
2009-8-31 15:01:14
SQL> select last-day(sysdate)
2 from dual;
select last-day(sysdate)
from dual
ORA-00904: "DAY": 标识符无效
SQL> select last_day(sysdate)
2 from dual;
LAST_DAY(SYSDATE)
-----------------
2009-8-31 15:02:2
SQL> select ename,hiredate,last_day(hiredate),next_day(hiredate,'sunday'),months_between(sysdate,hiredate)"months",add_months(hiredate , 3)"review"
2 from emp;
ENAME HIREDATE LAST_DAY(HIREDATE) NEXT_DAY(HIREDATE,'SUNDAY') months review
---------- ----------- ------------------ --------------------------- ---------- -----------
SMITH 1980-12-17 1980-12-31 1980-12-21 344.246053 1981-3-17
ALLEN 1981-2-20 1981-2-28 1981-2-22 342.149279 1981-5-20
WARD 1981-2-22 1981-2-28 1981-3-1 342.084762 1981-5-22
JONES 1981-4-2 1981-4-30 1981-4-5 340.729924 1981-7-2
MARTIN 1981-9-28 1981-9-30 1981-10-4 334.891214 1981-12-28
BLAKE 1981-5-1 1981-5-31 1981-5-3 339.762182 1981-8-1
CLARK 1981-6-9 1981-6-30 1981-6-14 338.504117 1981-9-9
KING 1981-11-17 1981-11-30 1981-11-22 333.246053 1982-2-17
TURNER 1981-9-8 1981-9-30 1981-9-13 335.536375 1981-12-8
JAMES 1981-12-3 1981-12-31 1981-12-6 332.697666 1982-3-3
FORD 1981-12-3 1981-12-31 1981-12-6 332.697666 1982-3-3
MILLER 1982-1-23 1982-1-31 1982-1-24 331.052504 1982-4-23
12 rows selected
SQL> select ename,hiredate,last_day(hiredate),next_day(hiredate,'星期日'),months_between(sysdate,hiredate)"months",add_months(hiredate , 3)"review"
2 from emp;
select ename,hiredate,last_day(hiredate),next_day(hiredate,'星期日'),months_between(sysdate,hiredate)"months",add_months(hiredate , 3)"review"
from emp
ORA-01846: 周中的日无效
SQL> select round('28-oct-01','month')
2 from dual;
select round('28-oct-01','month')
from dual
ORA-01722: 无效数字
SQL> select round(to_date('28-oct-01'),'month')
2 from dua;
select round(to_date('28-oct-01'),'month')
from dua
ORA-00942: 表或视图不存在
SQL> select round(to_date('28-oct-01'),'month')
2 from dual;
ROUND(TO_DATE('28-OCT-01'),'MO
------------------------------
2001-11-1
SQL> select round(to_date('28-oct-01'),'year')
2 from dual;
ROUND(TO_DATE('28-OCT-01'),'YE
------------------------------
2002-1-1
SQL> select round(to_date('28-may-01'),'year')
2 from dual;
ROUND(TO_DATE('28-MAY-01'),'YE
------------------------------
2001-1-1
SQL> select round(to_date('28-june-01'),'year')
2 from dual;
ROUND(TO_DATE('28-JUNE-01'),'Y
------------------------------
2001-1-1
SQL> select round(to_date('28-step-01'),'year')
2 from dual;
select round(to_date('28-step-01'),'year')
from dual
ORA-01843: 无效的月份
SQL> select round(to_date('28-july-01'),'year')
2 from dual;
ROUND(TO_DATE('28-JULY-01'),'Y
------------------------------
2002-1-1
SQL> select trunc(to_date('28-step-01'),'year')
2 from dual;
select trunc(to_date('28-step-01'),'year')
from dual
ORA-01843: 无效的月份
SQL> select round(to_date('28-july-01'),'year')
2 select trunc(to_date('28-step-01'),'year')
3
SQL> select trunc(to_date('28-july-01'),'year')
2 from dual;
TRUNC(TO_DATE('28-JULY-01'),'Y
------------------------------
2001-1-1
SQL> select trunc(to_date('28-feb-01'),'year')
2 from dual;
TRUNC(TO_DATE('28-FEB-01'),'YE
------------------------------
2001-1-1
SQL> select trunc(to_date('28-oct-01'),'year')
2 from dual;
TRUNC(TO_DATE('28-OCT-01'),'YE
------------------------------
2001-1-1
SQL> select ename,hiredate,round(hiredate,'year'),trunc(hiredate,'year'),round(hiredate,'month'),turnc(hiredate,'month')
2 from emp
3 where hiredate like"%81";
select ename,hiredate,round(hiredate,'year'),trunc(hiredate,'year'),round(hiredate,'month'),turnc(hiredate,'month')
from emp
where hiredate like"%81"
ORA-00904: "%81": 标识符无效
SQL> select ename,hiredate,round(hiredate,'year'),trunc(hiredate,'year'),round(hiredate,'month'),turnc(hiredate,'month')
2 from emp
3 where hiredate like'%81';
select ename,hiredate,round(hiredate,'year'),trunc(hiredate,'year'),round(hiredate,'month'),turnc(hiredate,'month')
from emp
where hiredate like'%81'
ORA-00904: "TURNC": 标识符无效
SQL> select ename,hiredate,round(hiredate,'year'),trunc(hiredate,'year'),round(hiredate,'month'),trunc(hiredate,'month')
2 from emp
3 where hiredate like'%81';
ENAME HIREDATE ROUND(HIREDATE,'YEAR') TRUNC(HIREDATE,'YEAR') ROUND(HIREDATE,'MONTH') TRUNC(HIREDATE,'MONTH')
---------- ----------- ---------------------- ---------------------- ----------------------- -----------------------
ALLEN 1981-2-20 1981-1-1 1981-1-1 1981-3-1 1981-2-1
WARD 1981-2-22 1981-1-1 1981-1-1 1981-3-1 1981-2-1
JONES 1981-4-2 1981-1-1 1981-1-1 1981-4-1 1981-4-1
MARTIN 1981-9-28 1982-1-1 1981-1-1 1981-10-1 1981-9-1
BLAKE 1981-5-1 1981-1-1 1981-1-1 1981-5-1 1981-5-1
CLARK 1981-6-9 1981-1-1 1981-1-1 1981-6-1 1981-6-1
KING 1981-11-17 1982-1-1 1981-1-1 1981-12-1 1981-11-1
TURNER 1981-9-8 1982-1-1 1981-1-1 1981-9-1 1981-9-1
JAMES 1981-12-3 1982-1-1 1981-1-1 1981-12-1 1981-12-1
FORD 1981-12-3 1982-1-1 1981-1-1 1981-12-1 1981-12-1
10 rows selected
SQL> select ename,to_char(hiredate,'dd/mm/yy')
2 from emp
3 where hiredate like'%82';
ENAME TO_CHAR(HIREDATE,'DD/MM/YY')
---------- ----------------------------
MILLER 23/01/82
SQL> select ename,to_char(hiredate,'mm/dd/yy')
2 form emp
3 where hiredate like '%82';
select ename,to_char(hiredate,'mm/dd/yy')
form emp
where hiredate like '%82'
ORA-00923: 未找到要求的 FROM 关键字
SQL> select ename,to_char(hiredate,'mm/dd/yy')
2 from emp
3 where hiredate like '%82';
ENAME TO_CHAR(HIREDATE,'MM/DD/YY')
---------- ----------------------------
MILLER 01/23/82
SQL>
SQL> select to_char(sysdate,'fmdd month year')
2 from dual;
TO_CHAR(SYSDATE,'FMDDMONTHYEAR
-----------------------------------------------------------
24 august two thousand nine
SQL> select ename"name",sal"salary",
2 to_char(hiredate,'fmDdspth "of" Month year fmHH:MI:SS AM') hiredate
3 from emp;
name salary HIREDATE
---------- --------- --------------------------------------------------------------------------------
SMITH 800.00 Seventeenth of December nineteen eighty 12:00:00 AM
ALLEN 1600.00 Twentieth of February nineteen eighty-one 12:00:00 AM
WARD 1250.00 Twenty-Second of February nineteen eighty-one 12:00:00 AM
JONES 2975.00 Second of April nineteen eighty-one 12:00:00 AM
MARTIN 1250.00 Twenty-Eighth of September nineteen eighty-one 12:00:00 AM
BLAKE 2850.00 First of May nineteen eighty-one 12:00:00 AM
CLARK 2450.00 Ninth of June nineteen eighty-one 12:00:00 AM
KING 5000.00 Seventeenth of November nineteen eighty-one 12:00:00 AM
TURNER 1500.00 Eighth of September nineteen eighty-one 12:00:00 AM
JAMES 950.00 Third of December nineteen eighty-one 12:00:00 AM
FORD 3000.00 Third of December nineteen eighty-one 12:00:00 AM
MILLER 1300.00 Twenty-Third of January nineteen eighty-two 12:00:00 AM
12 rows selected
SQL> select ename "Name",to_char(sal*12,'$99,999.00') "Annual Salary"
2 from emp;
Name Annual Salary
---------- -------------
SMITH $9,600.00
ALLEN $19,200.00
WARD $15,000.00
JONES $35,700.00
MARTIN $15,000.00
BLAKE $34,200.00
CLARK $29,400.00
KING $60,000.00
TURNER $18,000.00
JAMES $11,400.00
FORD $36,000.00
MILLER $15,600.00
12 rows selected
SQL> select ename "Name",to_char(sal*12,'L99,999.00') "Annual Salary"
2 from emp;
Name Annual Salary
---------- --------------------
SMITH ￥9,600.00
ALLEN ￥19,200.00
WARD ￥15,000.00
JONES ￥35,700.00
MARTIN ￥15,000.00
BLAKE ￥34,200.00
CLARK ￥29,400.00
KING ￥60,000.00
TURNER ￥18,000.00
JAMES ￥11,400.00
FORD ￥36,000.00
MILLER ￥15,600.00
12 rows selected
SQL> select ename "Name",to_char(sal*12,'L9,999.00') "Annual Salary"
2 from emp;
Name Annual Salary
---------- -------------------
SMITH ￥9,600.00
ALLEN ###################
WARD ###################
JONES ###################
MARTIN ###################
BLAKE ###################
CLARK ###################
KING ###################
TURNER ###################
JAMES ###################
FORD ###################
MILLER ###################
12 rows selected
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between '01-feb-81' and '31-dec-81'
4 order by hiredate;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
ALLEN SALESMAN 1600.00 1981-2-20
WARD SALESMAN 1250.00 1981-2-22
JONES MANAGER 2975.00 1981-4-2
BLAKE MANAGER 2850.00 1981-5-1
CLARK MANAGER 2450.00 1981-6-9
TURNER SALESMAN 1500.00 1981-9-8
MARTIN SALESMAN 1250.00 1981-9-28
KING PRESIDENT 5000.00 1981-11-17
FORD ANALYST 3000.00 1981-12-3
JAMES CLERK 950.00 1981-12-3
10 rows selected
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between '01-feb-81' and '31-dec-81'
4 order by hiredate desc;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
JAMES CLERK 950.00 1981-12-3
FORD ANALYST 3000.00 1981-12-3
KING PRESIDENT 5000.00 1981-11-17
MARTIN SALESMAN 1250.00 1981-9-28
TURNER SALESMAN 1500.00 1981-9-8
CLARK MANAGER 2450.00 1981-6-9
BLAKE MANAGER 2850.00 1981-5-1
JONES MANAGER 2975.00 1981-4-2
WARD SALESMAN 1250.00 1981-2-22
ALLEN SALESMAN 1600.00 1981-2-20
10 rows selected
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between '01-feb-81' and '31-dec-81'
4 order by hiredate desc;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
JAMES CLERK 950.00 1981-12-3
FORD ANALYST 3000.00 1981-12-3
KING PRESIDENT 5000.00 1981-11-17
MARTIN SALESMAN 1250.00 1981-9-28
TURNER SALESMAN 1500.00 1981-9-8
CLARK MANAGER 2450.00 1981-6-9
BLAKE MANAGER 2850.00 1981-5-1
JONES MANAGER 2975.00 1981-4-2
WARD SALESMAN 1250.00 1981-2-22
ALLEN SALESMAN 1600.00 1981-2-20
10 rows selected
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between '01-1月-81' and '31-12月-81'
4 order by hiredate desc;
select ename "Name",job,sal as "Salary",hiredate
from emp
where hiredate between '01-1月-81' and '31-12月-81'
order by hiredate desc
ORA-01843: 无效的月份
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from em
3 where hiredate between to_date('01-jan-81',dd-mm-yy) and to_date('31-dec-81','dd-mm-yy')
4 order by hiredate;
select ename "Name",job,sal as "Salary",hiredate
from em
where hiredate between to_date('01-jan-81',dd-mm-yy) and to_date('31-dec-81','dd-mm-yy')
order by hiredate
ORA-00942: 表或视图不存在
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81',dd-mm-yy) and to_date('31-dec-81','dd-mm-yy')
4 order by hiredate;
select ename "Name",job,sal as "Salary",hiredate
from emp
where hiredate between to_date('01-jan-81',dd-mm-yy) and to_date('31-dec-81','dd-mm-yy')
order by hiredate
ORA-00904: "YY": 标识符无效
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81',dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
4 order by hiredate;
5
6 where hiredate between to_date('01-jan-81',dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
7
SQL>
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81',dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
4 order by hiredatel
5 ;
6 ;
7
8
9
10
11
12
13
14 fda
15
16 fda
17
18
19
20
21 fa
22 ;
23 ;;
24
25
26
27 select ename "Name",job,sal as "Salary",hiredate
28 from emp
29 where hiredate between to_date('01-jan-81',dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
30 order by hiredate;
select ename "Name",job,sal as "Salary",hiredate
from emp
where hiredate between to_date('01-jan-81',dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
order by hiredatel
;
;
fda
fda
fa
;
;;
select ename "Name",job,sal as "Salary",hiredate
from emp
where hiredate between to_date('01-jan-81',dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
order by hiredate
ORA-00907: 缺失右括号
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81','dd-mm-yy') and to_date('31-dec-81','dd-mm-yy')
4 order by hiredate;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
SQL> select todate('01-jan-81','dd-mm-yy')
2 from dual;
select todate('01-jan-81','dd-mm-yy')
from dual
ORA-00904: "TODATE": 标识符无效
SQL> select to_date('01-jan-81','dd-mm-yy')
2 from dual;
TO_DATE('01-JAN-81','DD-MM-YY'
------------------------------
2081-1-1
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from dual;
select ename "Name",job,sal as "Salary",hiredate
from dual
ORA-00904: "HIREDATE": 标识符无效
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81','dd-mon-yy') and to_date('31-dec-81','dd-mon-yy')
4 order by hiredate;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
SQL> select hiredate from emp;
HIREDATE
-----------
1980-12-17
1981-2-20
1981-2-22
1981-4-2
1981-9-28
1981-5-1
1981-6-9
1981-11-17
1981-9-8
1981-12-3
1981-12-3
1982-1-23
12 rows selected
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81') and to_date('31-dec-81')
4 order by hiredate;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
ALLEN SALESMAN 1600.00 1981-2-20
WARD SALESMAN 1250.00 1981-2-22
JONES MANAGER 2975.00 1981-4-2
BLAKE MANAGER 2850.00 1981-5-1
CLARK MANAGER 2450.00 1981-6-9
TURNER SALESMAN 1500.00 1981-9-8
MARTIN SALESMAN 1250.00 1981-9-28
KING PRESIDENT 5000.00 1981-11-17
FORD ANALYST 3000.00 1981-12-3
JAMES CLERK 950.00 1981-12-3
10 rows selected
SQL> select ename "Name",job,sal as "Salary",hiredate
2 from emp
3 where hiredate between to_date('01-jan-81','dd-mon-rr') and to_date('31-dec-81','dd-mon-rr')
4 order by hiredate;
Name JOB Salary HIREDATE
---------- --------- --------- -----------
ALLEN SALESMAN 1600.00 1981-2-20
WARD SALESMAN 1250.00 1981-2-22
JONES MANAGER 2975.00 1981-4-2
BLAKE MANAGER 2850.00 1981-5-1
CLARK MANAGER 2450.00 1981-6-9
TURNER SALESMAN 1500.00 1981-9-8
MARTIN SALESMAN 1250.00 1981-9-28
KING PRESIDENT 5000.00 1981-11-17
FORD ANALYST 3000.00 1981-12-3
JAMES CLERK 950.00 1981-12-3
10 rows selected
SQL>
