Sql基礎

數據庫語言

數據操縱語言:DML (data manipulation language)
select insert update delete merge
數據定義語言:DDL(data definition language)
create alter drop truncate
事務控制語言:TCL (transaction control language)
commit rollback savepoint
數據控制語言:DCL (Data Control Language)
grant revoke

數據類型

Sql的數據類型:

字符型
char固定字符,最長2000個
varchar2可變長，最長4000最小值是1

數值型
number類型

日期型
date timestamp

大對象型
clob(存儲單字節數據，文本數據)
blob(存儲二進制數據)

常用數據類型轉換

SELECT TO_DATE('2015-08-19','YYYY-MM-DD')AS A_DAY FROM DUAL; SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD')AS TODAY FROM DUAL; SELECT TO_TIMESTAMP('2015-08-19 17:40:32.11','YYYY-MM-DDHH24:MI:SS.FF') AS A_DAY FROM DUAL; SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF')AS TODAY FROM DUAL; SELECT TO_NUMBER(REPLACE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'),'-')) FROM DUAL;

集合查詢

交叉連接(笛卡爾積)

等值、非等值連接

內連接

外連接(左外、右外、全連接)

自連接

自然連接(隱含鏈接條件，自動匹配鏈接自動)

集合運算
union(求合集重復記錄只顯示一次)
union all(求合集顯示所有記錄信息)
intersect(求交集，顯示公共的數據部分)
minus(集合相減，哪個表在前面以哪個表的數據為主)

CREATE TABLE A(ID INT,NAME VARCHAR2(10)); CREATE TABLE B(ID INT,NAME VARCHAR2(10)); INSERT INTO A VALUES(1,'張三'); INSERT INTO A VALUES(2,'李四'); INSERT INTO B VALUES(3,'王五'); INSERT INTO B VALUES(2,'李四');--intersect SELECT * FROM A INTERSECT SELECT * FROM B;

--minus(A為主表) SELECT * FROM A MINUS SELECT * FROM B;

--minus(B為主表) SELECT * FROM B MINUS SELECT * FROM A;

子查詢

非關聯子查詢:主查詢和子查詢是相對獨立的,唯一的,子查詢查詢結果和主查詢進行比較
SELECT A.ENAME,A.SAL FROM EMP A WHERE A.DEPTNO=(SELECT B.DEPTNO FROM DEPTB WHERE B.LOC= 'NEW YORK');

關聯子查詢:主查詢和子查詢是產生關聯關系的主查詢的一個列字段代入到子查詢中進行比較
SELECT A.DEPTNO,(SELECT B.LOC FROM DEPT B WHERE B.DEPTNO=A.DEPTNO) FROM EMP A;

IN和EXISTS，IN是做全表掃描，EXISTS是做是否存在，非全表掃描。

查詢屬于領導(大小領導都算)的員工:

SELECT * FROM employees a WHERE EXISTS (SELECT 1 FROM employees b WHERE a.employee_id=b.manager_id);

查詢哪個部門不存在員工的部門信息:

SELECT * FROM departments t WHERE NOT EXISTS(SELECT 1 FROM employees b WHERE b.department_id=t.department_id);

sql高級

表復制語句

CT: create table <new table> as select * from <exists table>
要求目標表不存在，因為在插入時會自動創建表，并將查詢表中指定字段數據復制到新建的表中
lS: insert into table2 (f1,f2,.….) select v1,v2,... from table1
要求目標表table2必須存在，由于目標表table2已經存在，所以我們除了插入源表table1的字段外，還可以插入常量

merge用法

merge into 表A using 與表A產生關聯字段值 on 和表A關聯的條件when matched then...when not matched then...

遞歸查詢

START WITH CONNECT BY。是oracle提供的遞歸查詢（分層查詢）函數，我們在進行遞歸遍歷樹形結構的時候可以使用。
??start with(從某個節點id開始)
??connect by prior (子節點id和父節點pid直接的關系需要)
形如:

SELECT *FROM EMP START WITH EMPNO=7369 CONNECT BY PRIOR MGR=EMPNO;(父節點=子節點向上查詢，反之向下查詢)

可以添加WHERE條件限制。
可以指定多個起始節點查詢。
可以進行排序。

sql分析函數

數據準備

over (partition by…order by)函數

-- 分月份和地區統計最高值，最低值，平均值，總額，并按月份排序 SELECT DISTINCT EARNMONTH 月份,AREA 地區,MAX(PERSONINCOME) OVER(PARTITION BY EARNMONTH, AREA) 最高值,MIN(PERSONINCOME) OVER(PARTITION BY EARNMONTH, AREA) 最低值,AVG(PERSONINCOME) OVER(PARTITION BY EARNMONTH, AREA) 平均值,SUM(PERSONINCOME) OVER(PARTITION BY EARNMONTH, AREA) 總額FROM EARNINGS;

rollup函數

--按照月份，地區統計收入，分開統計,不同字段在前會有不同的結果 SELECT EARNMONTH, AREA, SUM(PERSONINCOME)FROM EARNINGSGROUP BY ROLLUP(EARNMONTH, AREA);

cube函數

--按照月份，地區統計收入，全部統計 SELECT EARNMONTH, AREA, SUM(PERSONINCOME)FROM EARNINGSGROUP BY CUBE(EARNMONTH, AREA)ORDER BY EARNMONTH, AREA;

grouping函數

在以上例子中，是用rollup和cuba函數都會對結果集產生null，這時候可用grouping函數來確認該記錄是由哪個字段得出來的

示例1：

--grouping函數用法，帶一個參數，參數為字段名，如果是該字段就返回0，如果不是該字段結果返回1 SELECT EARNMONTH,grouping(EARNMONTH)FROM EARNINGSGROUP BY ROLLUP(EARNMONTH);

示例2：

SELECT EARNMONTH,(CASEWHEN ((GROUPING(AREA) = 1) AND (GROUPING(EARNMONTH) = 0)) THEN'月份小計'WHEN ((GROUPING(AREA) = 1) AND (GROUPING(EARNMONTH) = 1)) THEN'總計'ELSEAREAEND) AREA,SUM(PERSONINCOME)FROM EARNINGSGROUP BY ROLLUP(EARNMONTH, AREA);

rank / dense_rank / row_number

• rank:排名會有并列，產生跳躍排名

SELECT EARNMONTH 月份,AREA 地區,SNAME 打工者,PERSONINCOME 收入,RANK() OVER(PARTITION BY EARNMONTH, AREA ORDER BY PERSONINCOME DESC) 排名FROM EARNINGS;

• dense_rank:排名會有并列，不產生跳躍排名

SELECT EARNMONTH 月份,AREA 地區,SNAME 打工者,PERSONINCOME 收入,DENSE_RANK() OVER(PARTITION BY EARNMONTH, AREA ORDER BY PERSONINCOME DESC) 排名FROM EARNINGS;

• row_number:排名沒有并列，不產生跳躍排名

SELECT EARNMONTH 月份,AREA 地區,SNAME 打工者,PERSONINCOME 收入,ROW_NUMBER() OVER(PARTITION BY EARNMONTH, AREA ORDER BY PERSONINCOME DESC) 排名FROM EARNINGS;

總結

以上是生活随笔為你收集整理的oracle—高级查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。