1 把当前会话的日期改成 American的日期格式
SQL> select sysdate from dual;
SYSDATE
--------------
04-5月 -08
SQL> alter session
2 set NLS_DATE_LANGUAGE='AMERICAN';
会话已更改。
SQL> SELECT sysdate from dual;
SYSDATE
------------
04-MAY-08
2 用distinct关键字来过滤不同的行
SQL> select distinct deptno from emp;
DEPTNO
----------
30
20
10
3 在查询select中的字句where 中的字符串是区分大小写的
SQL> select empno,ename,job from emp
2 where job='salesman';
未选定行
SQL> select empno,ename,job from emp
2 where job='SALESMAN';
EMPNO ENAME JOB
---------- ---------- ---------
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7654 MARTIN SALESMAN
7844 TURNER SALESMAN
4 转义escape操作符
SQL> select * from dept_temp where loc='beijing';
DEPTNO DNAME LOC
---------- -------------- -------------
88 IT_dept beijing
SQL> select * from dept_temp
2 where dname like 'IT\_%' escape '\';
DEPTNO DNAME LOC
---------- -------------- -------------
88 IT_dept beijing
SQL> select * from dept_temp
2 where dname like 'IT*_%' escape '*';
DEPTNO DNAME LOC
---------- -------------- -------------
88 IT_dept beijing
SQL> select * from dept_temp
2 where dname like 'ITm_%' escape 'm';
DEPTNO DNAME LOC
---------- -------------- -------------
88 IT_dept beijing
5 只列出前三行查询数据用rownum,order by 排序时要放在最后
SQL> select * from dept_temp where rownum<=3 order by deptno desc ;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
6 sqlplus当前会话默认的显示屏宽为80个字符,更改为100。
SQL> show line
linesize 80
SQL> set line 100;
SQL> show line;
linesize 100
7 把SQL缓冲区的内容存入指定脚本
SQL> save g:\SelectEmp.sql
已创建 file g:\SelectEmp.sql
运行脚本
SQL> @g:\SelectEmp.sql
编辑脚本
SQL> edit g:\SelectEmp.sql
查看该脚本语句
SQL> get g:\SelectEmp.sql
1 select empno,ename,job,sal
2 from emp
3 where sal>=1500
4* order by job,sal desc
8 用spool产生报表
SQL> spool g:\output20080504
SQL> select empno,ename,job,sal
2 from emp;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7934 MILLER CLERK 1300
已选择12行。
SQL> spool off;
在g盘产生一个output20080504.LST文件,该文件保存查询的内容.
Oracle9i之前的版本日期类型为“DD-MON-YY”,Oracle9i之后的版本日期类型为“DD-MON-RR”
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12778571/viewspace-258800/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12778571/viewspace-258800/