oracle基础积累-常用知识

场景:
        基础知识积累.
环境:
        Oracle Database 11g; PL/SQL Developer
1.单表多次子查询关联

SELECT WENDU.SENSOR_ID,
   WENDU.DATA_DATE,
   WENDU.REGION,
   DECODE(WENDU.S1, NULL, 0, WENDU.S1) AS WENDU1,
   DECODE(WENDU.S1, NULL, 0, WENDU.S2) AS WENDU2,
   DECODE(WENDU.S1, NULL, 0, WENDU.S3) AS WENDU3,
   DECODE(SHIDU.S1, NULL, 0, SHIDU.S1) AS SHIDU1,
   DECODE(SHIDU.S1, NULL, 0, SHIDU.S1) AS SHIDU2,
   DECODE(SHIDU.S1, NULL, 0, SHIDU.S3) AS SHIDU3
FROM (SELECT * FROM SENSOR_COLLECT_DATA WHERE DATA_TYPE = 1) WENDU
LEFT JOIN (SELECT *FROM SENSOR_COLLECT_DATA WHERE DATA_TYPE = 2) SHIDU
ON WENDU.SENSOR_ID = SHIDU.SENSOR_ID
AND WENDU.DATA_DATE = SHIDU.DATA_DATE;

2.截取时间

SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL;
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;
SELECT TRUNC(SYSDATE, 'DD') FROM DUAL;
SELECT TRUNC(SYSDATE, 'HH') FROM DUAL;
SELECT TRUNC(SYSDATE, 'MI') FROM DUAL;
SELECT TRUNC(SYSDATE - 1) FROM DUAL;

3.取上个上个月下个月

SELECT TRUNC(ADD_MONTHS(SYSDATE,-1),'MM') FROM DUAL;
SELECT TRUNC(ADD_MONTHS(SYSDATE,1),'MM') FROM DUAL;
--上月1号,上月最后一天,本月1号
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1),TRUNC(SYSDATE, 'MM') - 1,TRUNC(SYSDATE, 'MM') FROM DUAL;

4.时间转字符串

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF') FROM DUAL;

5.用户授权

GRANT UPDATE,SELECT  ON  TABLE_A  TO USER_ZBZ;  
GRANT INSERT,UPDATE,SELECT  ON  TABLE_A  TO USER_ZBZ; 

6.取出最大值

SELECT GREATEST(SUM(A.S1),SUM(A.S2),SUM(A.S3) FROM SENSOR_COLLECT_DATA  A;

7.查询某个用户表和视图列表

SELECT * FROM ALL_VIEWS WHERE OWNER='USER_ZBZ';
SELECT * FROM ALL_TABLES WHERE OWNER='USER_ZBZ';

8.查看分区文件名

SELECT * FROM DBA_DATA_FILES;

9.查看分区

SELECT * FROM  ALL_TAB_PARTITIONS;

10.查出一个表数据导入另一个表,两表字段相同

INSERT  INTO SENSOR_COLLECT_DATA_A  SELECT *  FROM SENSOR_COLLECT_DATA_B;

11.LEFT函数使用

SELECT * FROM SENSOR_COLLECT_DATA A WHERE LEFT(A.REGION,2)='11';
SELECT COUNT(1)  FROM SENSOR_COLLECT_DATA A WHERE LEFT(A.REGION,2)='11';

以上,感谢.