(一)to_char
把日期或数字转换为字符串
使用TO_CHAR函数处理数字:TO_CHAR(number, '格式')
例:TO_CHAR(salary,’$99,999.99’);
使用TO_CHAR函数处理日期:TO_CHAR(date,’格式’);
SQL> select to_char(sysdate,'YYYY-MM-DD') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DD')
-----------------------------
2016-01-27
SQL> select ename,to_char(hiredate,'YYYY-MM-DD hh24:mi:ss') from emp;
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH
---------- ------------------------------
SMITH 1980-12-17 00:00:00
ALLEN 1981-02-20 00:00:00
WARD 1981-02-22 00:00:00
JONES 1981-04-02 00:00:00
MARTIN 1981-09-28 00:00:00
BLAKE 1981-05-01 00:00:00
CLARK 1981-06-09 00:00:00
SCOTT 1987-04-19 00:00:00
KING 1981-11-17 00:00:00
TURNER 1981-09-08 00:00:00
ADAMS 1987-05-23 00:00:00
JAMES 1981-12-03 00:00:00
FORD 1981-12-03 00:00:00
MILLER 1982-01-23 00:00:00
SQL> select to_char(sysdate,'month') from dual;
TO_CHAR(SYSDATE,'MONTH')
------------------------
1月
SQL> select ename,sal, to_char(sal,'$99,999.00') salary from emp ;
ENAME SAL SALARY
---------- --------- -----------
SMITH 800.00 $800.00
ALLEN 1600.00 $1,600.00
WARD 1250.00 $1,250.00
JONES 2975.00 $2,975.00
MARTIN 1250.00 $1,250.00
BLAKE 2850.00 $2,850.00
CLARK 2450.00 $2,450.00
SCOTT 3000.00 $3,000.00
KING 5000.00 $5,000.00
TURNER 1500.00 $1,500.00
ADAMS 1100.00 $1,100.00
JAMES 950.00 $950.00
FORD 3000.00 $3,000.00
MILLER 1300.00 $1,300.00
实际位数超过格式规定位数时
SQL> select ename,sal,to_char(sal,'$999.00') from emp;
ENAME SAL TO_CHAR(SAL,'$999.00')
---------- --------- ----------------------
SMITH 800.00 $800.00
ALLEN 1600.00 ########
WARD 1250.00 ########
JONES 2975.00 ########
MARTIN 1250.00 ########
BLAKE 2850.00 ########
CLARK 2450.00 ########
SCOTT 3000.00 ########
KING 5000.00 ########
TURNER 1500.00 ########
ADAMS 1100.00 ########
JAMES 950.00 $950.00
FORD 3000.00 ########
MILLER 1300.00 ########
(二)to_date & to_number
TO_DATE
使用TO_DATE函数将字符转换为日期
TO_DATE(char[, '格式'])
TO_NUMBER
使用TO_NUMBER函数将字符转换为数字
TO_NUMBER(char[, '格式'])
(三) nvl
NVL( string1, replace_with)
如果string1为NULL,则返回replace_with的值,否则返回string1的值,如果两个参数都为NULL ,则返回NULL。
SQL> select nvl('abc',2) from dual;
NVL('ABC',2)
------------
abc
SQL> select nvl(null,'me')from dual;
NVL(NULL,'ME')
--------------
me
SQL> select ename ,sal,sal+nvl(comm,0) from emp;
ENAME SAL SAL+NVL(COMM,0)
---------- --------- ---------------
SMITH 800.00 800
ALLEN 1600.00 1900
WARD 1250.00 1750
JONES 2975.00 2975
MARTIN 1250.00 2650
BLAKE 2850.00 2850
CLARK 2450.00 2450
SCOTT 3000.00 3000
KING 5000.00 5000
TURNER 1500.00 1500
ADAMS 1100.00 1100
JAMES 950.00 950
FORD 3000.00 3000
MILLER 1300.00 1300
(四)nvl2
NVL2(expr1,expr2,expr3)
如果参数表达式expr1值为NULL,则NVL2()函数返回参数表达式expr3的值;如果参数表达式expr1值不为NULL,则NVL2()函数返回参数表达式expr2的值
SQL> select ename,sal,comm, sal+nvl2(comm,comm,0) from emp; --不为空,返回第二个参数值,为空返回第三个参数值
ENAME SAL COMM SAL+NVL2(COMM,COMM,0)
---------- --------- --------- ---------------------
SMITH 800.00 800
ALLEN 1600.00 300.00 1900
WARD 1250.00 500.00 1750
JONES 2975.00 2975
MARTIN 1250.00 1400.00 2650
BLAKE 2850.00 2850
CLARK 2450.00 2450
SCOTT 3000.00 3000
KING 5000.00 5000
TURNER 1500.00 0.00 1500
ADAMS 1100.00 1100
JAMES 950.00 950
FORD 3000.00 3000
MILLER 1300.00 1300
(五) group by
常与聚集函数一起使用
1.求Emp表中各个部门的工资之和
2.列出部门的名称
SQL> select deptno,job,sum(sal) from emp group by deptno,job;
DEPTNO JOB SUM(SAL)
------ --------- ----------
20 CLERK 1900
30 SALESMAN 5600
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 6000
3.having
group by的条件查询
4.rollup
group by后带rollup子句的功能可以理解为:先按一定的规则产生多种分组,然后按各种分组统计数据(至于统计出的数据是求和还是最大值还是平均值等这就取决于SELECT后的聚合函数)。
Group by rollup(A ,B)
DEPTNO JOB AVG(SAL)
------ --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 2916.66666
20 CLERK 950
20 ANALYST 3000
20 MANAGER 2975
20 2175
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
30 1566.66666
2073.21428
5.cube
带cube子句的group by会产生更多的分组统计数据。cube后的列有多少种组合(注意组合是与顺序无关的)就会有多少种分组。
SQL> select deptno ,job,avg(sal) from emp group by cube(deptno,job);
DEPTNO JOB AVG(SAL)
------ --------- ----------
2073.21428
CLERK 1037.5
ANALYST 3000
MANAGER 2758.33333
SALESMAN 1400
PRESIDENT 5000
10 2916.66666
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 2175
20 CLERK 950
20 ANALYST 3000
20 MANAGER 2975
30 1566.66666
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1400
(六) SQL PLUS命令
SQL> set echo on—————————————————设置运行命令是是否显示语句
SQL> set feedback on———————————————-设置显示“已选择XX行”
SQL> set colsep | —————————————————设置列与列之间的分割符号
SQL> set pagesize 10———————————————–设置每一页的行数
SQL> SET SERVEROUTPUT ON——————————-设置允许显示输出类似dbms_output.putline
SQL> set heading on————————————————设置显示列名
SQL> set timing on————————————————–设置显示“已用时间:XXXX”
SQL> set time on—————————————————–设置显示当前时间
SQL> set autotrace on———————————————–设置允许对执行的sql进行分析
SQL> set timing on
SQL> select * from dual;
DUMMY
-----
X
Executed in 0.047 seconds
SQL> set time on
0:15:07 SQL> select * from dual;
1. describe 命令
查看表的结构
格式:desc [tablename]
2 edit /ed
打开编辑器
3 run /r 或者 /
执行ed里内容
4.save
保存当前session最近的sql语句至指定的文件中
C>save g:\a.sql;
如果已经存在此文件 要加 replace
save h:\my.sql replace;
5 get
get g:\a.sql
补允:
@ g:\b.sql;也可以执行一个sql块
6.spool
sqlplus屏幕的文件输入输出命令
输入:c> spool g:\a.txt;
输出:
SQL> spool on
Started spooling to C:\Users\Administrator\Desktop\on.lst
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
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
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
14 rows selected
SQL> spool off
Stopped spooling to C:\Users\Administrator\Desktop\on.lst
7.Formatting Columns
SQL> TTITLE abc --添加页眉“abc”
SQL> BTITLE def --添加页脚“def”
SQL> TTITLE OFF --取消页眉显示
【本段来自CSDN博客,转载请标明出处:http://blog.csdn.net/qiuliangjun/archive/2009/05/05/4152402.aspx】
1、HEADING--改变字段名
SQL> COLUMN id HEADING 'XX|YY' --使用'|'可将列名显示为两行
2、FORMAT--改变字符长度 & 格式化数值
SQL> COLUMN id FORMAT a20 --此字段长20个字符,只能针对字符,若为数字则无法正常显示
SQL> COLUMN id FORMAT $999.00 --前缀$,小数点前3位,小数点后2位四舍五入
3、JUSTIFY--改变字段名显示位置
SQL> COLUMN id JUSTIFY center --居中显示,默认的都是靠右(r )显示
4、NOPRINT--不显示
SQL> COLUMN id NOPRINT --不显示出来,可用PRINT设置回来
5、NULL--设置NULL值显示
SQL> COLUMN COMM NULL 0.00 --将NULL值设置位0.00 注意必须加上COMM
6、WRAPPED--设置回绕方式
SQL> COLUMN id FORMAT a5 --设置长度为5
SQL> COLUMN id WRAPPED --表示直接按长度回绕
SQL> COLUMN id WORD_WRAPPED --按单词回绕
SQL> COLUMN id TRUNCATED --直接按长度截断
7、COLUMN--显示当前列所有属性
SQL> COLUMN id --后面不加列名时显示所有的COLUMN
8、OFF|ON--设置某一字段关闭
SQL> COLUMN id OFF
9、CLEAR--清空所有字段属性
SQL> CLEAR COLUMNS