【TOM旧博客内容转移】一些简单的Oracle语句 from 20080617

 

原来TOM上的blog挂掉了，现在准备看能找到多少。。。准备把技术相关的全部转到JAVAEYE上边来。。。恨死TOM了。。

 

 

原发表日期：2008-06-17

 

 

 

 

 

一些简单的Oracle语句
   
   

在南昌闲得无聊，幸亏还有个Oracle，就算是复习拉！

 

SELECT TABLE_NAME FROM USER_TABLES;

查看用户表名。

 

层次化查询,我原来写的的博客Oracle层次化查询有地方不太正确：这个才是能用的
SELECT * FROM table_name
 START WITH start_column=?
  CONNECT BY PRIOR column_father_name=column_name;
如果要以其中的一个表为start，
那么在后边的connect by子句中在另一个表上要加上PRIOR，
PRIOR的意思是引用父行。

 

分组查询
SELECT COUNT(x), x FROMtable_name
 GROUP BY ROLLUP(x);
在最后会生成统计列。
SELECT COUNT(x), x FROMtable_name
 GROUP BY CUBE(x);
在最前会生成统计列。

 

ORACLE文字函数：

 

CONCAT合并文字
SQL> select concat('Date:',sysdate) from dual;
CONCAT('DATE:',SYSDATE)
-----------------------
Date:15-6月 -08

 

INITCAP字符串中所有单词首字母大写
SQL> select INITCAP('my heart will go on!') from dual;
INITCAP('MYHEARTWILLGOON!')
---------------------------
My Heart Will Go On!

 

INSTR查询字符在字符串的位置，从1开始，如果查找不到则返回0
SQL> SELECT INSTR('my heart will go on!','m') FROM DUAL;
INSTR('MYHEARTWILLGOON!','M')
-----------------------------
                            1
SQL> SELECT INSTR('my heart will go on!','l') FROM DUAL;
INSTR('MYHEARTWILLGOON!','L')
-----------------------------
                           12
SQL> SELECT INSTR('my heart will go on!','Z') FROM DUAL;
INSTR('MYHEARTWILLGOON!','Z')
-----------------------------
                            0

LENGTH得到字符串长度
SQL> SELECT LENGTH('my heart will go on!') FROM DUAL;
LENGTH('MYHEARTWILLGOON!')
--------------------------
                        20

LPAD如果字符串不够一定的长度则从左边填充，
长度大于指定长度的话右边的要截去。
默认最后一个参数不写的话用空格代替
SQL> SELECT LPAD('NANXIAOQIANG', 20, '*') FROM DUAL;
LPAD('NANXIAOQIANG',20,'*')
---------------------------
********NANXIAOQIANG
SQL> SELECT LPAD('NANXIAOQIANG', 5, '*') FROM DUAL;
LPAD('NANXIAOQIANG',5,'*')
--------------------------
NANXI
SQL> SELECT LPAD('NANXIAOQIANG', 20) FROM DUAL;
LPAD('NANXIAOQIANG',20)
-----------------------
        NANXIAOQIANG
RPAD同上一个函数，不过是从右至左填充
SQL> SELECT RPAD('NANXIAOQIANG', 20) FROM DUAL;
RPAD('NANXIAOQIANG',20)
-----------------------
NANXIAOQIANG
SQL> SELECT RPAD('NANXIAOQIANG', 20, '*') FROM DUAL;
RPAD('NANXIAOQIANG',20,'*')
---------------------------
NANXIAOQIANG********
SQL> SELECT RPAD('NANXIAOQIANG', 5, '*') FROM DUAL;
RPAD('NANXIAOQIANG',5,'*')
--------------------------
NANXI
SQL> SELECT RPAD('NANXIAOQIANG', 20, '<!>') FROM DUAL;
RPAD('NANXIAOQIANG',20,'<!>')
-----------------------------
NANXIAOQIANG<!><!><!

 

最简单的：
转换大小写和去除空格
其中TRIM会把Tab和空格全部去掉
SQL> SELECT LOWER('NANXIAOQIANG') FROM DUAL;
LOWER('NANXIAOQIANG')
---------------------
nanxiaoqiang
SQL> SELECT UPPER('nanxiaoqiang') FROM DUAL;
UPPER('NANXIAOQIANG')
---------------------
NANXIAOQIANG
SQL> SELECT TRIM('  nanxiaoqiang            ') FROM DUAL;
TRIM('NANXIAOQIANG')
--------------------
nanxiaoqiang


这个是TRIM的另外一种用法
SQL> SELECT TRIM('A' FROM 'ASDZXCCXZDSA') FROM DUAL;
TRIM('A'FROM'ASDZXCCXZDSA')
---------------------------
SDZXCCXZDS


以下两个其实是去除最左边和最右边的定义字符串
SQL> SELECT RTRIM('ASDZXCZXCASD','ASD') FROM DUAL;
RTRIM('ASDZXCZXCASD','ASD')
---------------------------
ASDZXCZXC
SQL> SELECT LTRIM('ASDZXCZXCASD','ASD') FROM DUAL;
LTRIM('ASDZXCZXCASD','ASD')
---------------------------
ZXCZXCASD

 

很诡异的一个方法，能分辨发音，
不过仅仅限于英文。。。中文还是没有办法的
SQL> SELECT SOUNDEX('SHEEP'), SOUNDEX('SHIP') FROM DUAL;
SOUNDEX('SHEEP') SOUNDEX('SHIP')
---------------- ---------------
S100             S100
SQL> SELECT SOUNDEX('朱'), SOUNDEX('猪') FROM DUAL;
SOUNDEX('朱') SOUNDEX('猪')
------------- -------------

什么都查不出来

 


查询时间的函数


总共有三个：CURRENT_DATE、CURRENT_TIMESTAMP、SYSDATE
CURRENT_DATE默认不会显示完整很奇怪
SQL> SELECT CURRENT_DATE FROM DUAL;
CURRENT_DATE
------------
2008-6-16 8:
用左补全也不行
SQL> SELECT LPAD(CURRENT_DATE, 20) FROM DUAL;
LPAD(CURRENT_DATE,20)
----------------------------------------
          16-6月 -08
只能用AS一个字段名称的方法，注意如果有空格的话要用双引号
SQL> SELECT CURRENT_DATE AS "THIS IS CURRENT DATE FEOM DUAL" FROM DUAL;
THIS IS CURRENT DATE FEOM DUAL
------------------------------
2008-6-16 8:48:32
CURRENT_TIMESTAMP：这个比较全，最后的+08:00应该是指的是时区
SQL> SELECT CURRENT_TIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP
--------------------------------------------------------------------------------
16-6月 -08 08.48.47.937000 上午 +08:00
这个是最常用的，一般在学的时候老师都是讲这个的：
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
-----------
2008-6-16 8
查找时区，不知道为什么，我查出来的总是+00:00，可是CURRENT_TIMESTAMP上边显示的是+08:00：
SQL> SELECT DBTIMEZONE FROM DUAL;
DBTIMEZONE
----------
+00:00


得到本地的时间：
SQL> SELECT LOCALTIMESTAMP FROM DUAL;
LOCALTIMESTAMP
--------------------------------------------------------------------------------
16-6月 -08 09.09.08.843000 上午

 

数字函数：


ROUND和TRUNC的区别：
SQL> SELECT ROUND(11.345,2), ROUND(123456,-2), TRUNC(12.345,2), TRUNC(123456,-2) FROM DUAL;
ROUND(11.345,2) ROUND(123456,-2) TRUNC(12.345,2) TRUNC(123456,-2)
--------------- ---------------- --------------- ----------------
          11.35           123500           12.34           123400

 

系统环境
SQL> SELECT UID, USER FROM DUAL;
       UID USER
---------- ------------------------------
        57 SCOTT