1、任何含有空值的运算表达式最后的结果都是空值,此时需要用到nvl函数如果是空值的话转换成0
SQL> select ename,sal*12+comm from emp
2 ;
SQL> select ename,sal*12+comm "total money" from emp;
ENAME total money
---------- -----------
SMITH
ALLEN 19500
WARD 15500
JONES
MARTIN 16400
BLAKE
CLARK
SCOTT
KING
TURNER 18000
ADAMS
JAMES
FORD
MILLER
14 rows selected
SQL> select ename,sal*12+nvl(comm,0)from emp;
ENAME SAL*12+NVL(COMM,0)
---------- ------------------
SMITH 9600
ALLEN 19500
WARD 15500
JONES 35700
MARTIN 16400
BLAKE 34200
CLARK 29400
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
JAMES 11400
FORD 36000
MILLER 15600
14 rows selected
2、临时表dual(只有一个字段,用于计算)
SQL> select 3*4+2 from dual;
3*4+2
----------
14
3、字符串连接符||
SQL> select ename||sal from emp;
ENAME||SAL
--------------------------------------------------
SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK2450
SCOTT3000
KING5000
TURNER1500
ADAMS1100
JAMES950
FORD3000
MILLER1300
14 rows selected
4、字符串的表示用单引号表示,如果字符串里面本来就有单引号,可以用两个单引号来替代里面的单引号
5、常用的函数
lower 字符转换成小写
upper 字符转换成大写
substring(x,int i,int j) 把x从i位开始截取,一共截取j位
chr 把ASCII码转成字符
ASCII 把字符转换成ASCII码
round 四舍五入
round(x,int i) 从小数点后面第几位四舍五入
to_char(x,'具体的格式') 一般用于对对齐数字
SQL> select ename||sal from emp;
ENAME||SAL
--------------------------------------------------
SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK2450
SCOTT3000
KING5000
TURNER1500
ADAMS1100
JAMES950
FORD3000
MILLER1300
14 rows selected
SQL> select ename,to_char(hiredate,'YYYY-MM-DD HH-MI-SS') from emp;
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH
---------- ------------------------------
SMITH 1980-12-17 12-00-00
ALLEN 1981-02-20 12-00-00
WARD 1981-02-22 12-00-00
JONES 1981-04-02 12-00-00
MARTIN 1981-09-28 12-00-00
BLAKE 1981-05-01 12-00-00
CLARK 1981-06-09 12-00-00
SCOTT 1987-04-19 12-00-00
KING 1981-11-17 12-00-00
TURNER 1981-09-08 12-00-00
ADAMS 1987-05-23 12-00-00
JAMES 1981-12-03 12-00-00
FORD 1981-12-03 12-00-00
MILLER 1982-01-23 12-00-00
14 rows selected
to_date
SQL> select ename,to_char(hiredate,'YYYY-MM-DD HH-MI-SS') from emp where hiredate>
to_date('1982-3-12 13:24:34','YYYY-MM-DD HH24-MI-SS');
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH
---------- ------------------------------
SCOTT 1987-04-19 12-00-00
ADAMS 1987-05-23 12-00-00
to_number
alter user scott account unlock