oracle的簡單學習

1.獲得當前的日期時間

select sysdate from dual;

2.擷取當前系統的日期和時間

select to_char(sysdate,'yyyy-MM-dd hh:mm:ss') from dual;

3.MONTHS_BETWEENdate1，date2）：返回date1和date2之間的月份數量，結果為正數或負數

select months_between(sysdate,to_date('2013-01-01','yyyy-MM-dd')) from dual;

4.add_MONTHS(date,n)：向date加上n個月，n必須是正整數

select add_months(sysdate,2) from dual;

5.next_day(date,char)：求出date之後一周內某天char的日期，char是一個有效表示星期幾的數字或字串

select next_day(sysdate,7) from dual;

6.last_day(date)：求出date所在月的最後一天

select last_day(sysdate) from dual;

一：等值查詢

1.輸出每人所在的部門及部門所在地：

select ename,dname,loc from emp,dept where emp.deptno = dept.deptno;

2.查詢deptno為10的員工及部門所在地：

select ename,dname,loc from emp,dept where emp.deptno = dept.deptno and dept.deptno = 10;

二：外部串連

外部串連運算子(+)只能放在串連條件運算式的一側】

外部串連條件中不能使用in操作符，也不能用or運算子與其他運算子相連】

1.顯示員工對應的部門，並顯示所有部門：

select ename,dept.dname,emp.empno,dept.deptno from emp,dept where emp.deptno(+) = dept.deptno;

2.顯示員工對應的部門，並顯示所有的員工：

select ename,dept.dname,emp.empno,dept.deptno from emp,dept where emp.deptno = dept.deptno(+);

三：標準串連查詢

1.內串連：select ename,dname from emp inner join dept on emp.deptno = dept.deptno;

2.左外串連： select ename,dname from emp left join dept on emp.deptno = dept.deptno;

3.右外串連：select ename,dname from emp right join dept on emp.deptno = dept.deptno;

四：子查詢

1.顯示比id為7655銷售額高的記錄：

select * from emp where sal > (select sal from emp where empno = 7566);

2.顯示和ID為7369相同工作並且工資大於7876的員工資訊：

select ename from emp where job = (select job from emp where empno = 7369) and sal > (select sal from emp where empno = 7876);

3.顯示工資最低的員工資訊：

select * from emp where sal = (select min(sal) from emp);

說明：

1.子查詢要使用括弧括起來

2.將子查詢放在比較子的右邊

3.不要在子查詢中使用order by字句，select語句中只能有一個order by字句，並且它只能是主select語句的最後一個字句

五：擷取序列資訊

select sequence_name,max_value,min_value,increment_by,last_number from user_sequences;

1.sequence_name：序列名稱

2.max_value：最大的值

3.min_value：最小的值

4.increment_by：增長量

5.last_number：使用的或緩衝的最後一個序號，一般大於緩衝區中的最後一個值

六：查詢索引

select * from user_indexes;

七：分頁

分頁查詢格式：

SELECT * FROM ( SELECT A.*, ROWNUM RN

FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40 ) WHERE RN >= 21

其中最內層的查詢SELECT * FROM TABLE_NAME表示不進行翻頁的原始查詢語句。ROWNUM <= 40和RN >= 21控制分頁查詢的每頁的範圍。

上面給出的這個分頁查詢語句，在大多數情況擁有較高的效率。分頁的目的就是控制輸出結果集大小，將結果儘快的返回。在上面的分頁查詢語句中，這種考慮主要體現在WHERE ROWNUM <= 40這句上。

選擇第21到40條記錄存在兩種方法，一種是上面例子中展示的在查詢的第二層通過ROWNUM <= 40來控制最大值，在查詢的最外層控制最小值。而另一種方式是去掉查詢第二層的WHERE ROWNUM <= 40語句，在查詢的最外層控制分頁的最小值和最大值。這是，查詢語句如下：

SELECT * FROM ( SELECT A.*, ROWNUM RN

FROM (SELECT * FROM TABLE_NAME) A ) WHERE RN BETWEEN 21 AND 40

對比這兩種寫法，絕大多數的情況下，第一個查詢的效率比第二個高得多。

這是由於CBO最佳化模式下，Oracle可以將外層的查詢條件推到內層查詢中，以提高內層查詢的執行效率。對於第一個查詢語句，第二層的查詢條件WHERE ROWNUM <= 40就可以被Oracle推入到內層查詢中，這樣Oracle查詢的結果一旦超過了ROWNUM限制條件，就終止查詢將結果返回了。

而第二個查詢語句，由於查詢條件BETWEEN 21 AND 40是存在於查詢的第三層，而Oracle無法將第三層的查詢條件推到最內層即使推到最內層也沒有意義，因為最內層查詢不知道RN代表什麼）。因此，對於第二個查詢語句，Oracle最內層返回給中介層的是所有滿足條件的資料，而中介層返回給最外層的也是所有資料。資料的過濾在最外層完成，顯然這個效率要比第一個查詢低得多。

eg.

select * from (select A.*,rownum rn from (select * from tbl_pay_taobaoshop) A where rownum < 40 ) where rn > 21

select * from (select A.*,rownum rn from (select * from tbl_pay_taobaoshop) A ) where rn between 21 and 40

本文出自 “我的JAVA世界” 部落格，請務必保留此出處http://hanchaohan.blog.51cto.com/2996417/1300613