Oracle資料庫筆記整理

Oracle資料庫

函數

大小寫轉換

SELECT ename, LOWER(ename) FROM emp;
SELECT ename, UPPER(ename) FROM emp;           

截取

SELECT ename, SUBSTR(ename,1,2) FROM emp;           

四舍五入

SELECT ROUND(3.54159) FROM dual;
SELECT ROUND(3.14159,3) FROM dual;           

日期轉字元

SELECT TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mi:ss') FROM dual;
SELECT TO_CHAR(SYSDATE, 'yyyy-MM-dd HH24:mi:ss') FROM dual;           

字元格式轉換

SELECT TO_CHAR(sal, '99,999.9999') FROM emp;           

字元轉日期

SELECT TO_DATE('2019-06-11 15:08:00','yyyy-MM-dd HH24:mi:ss') FROM dual;           

解決NULL空值問題

SELECT ename, NVL(comm,0) FROM emp;           

表連接配接

• 左連接配接:

  left join

  ,傳回左表中所有的資料和右表中滿足條件的資料
• 右連接配接:

  right join

  ,傳回右表中所有的資料和左表中滿足條件的資料
• 内連接配接:

  inner join

  ,傳回左表和右表同時滿足條件的資料

分頁

在MySQL分頁LIMIT，在ORACLE不支援LIMIT，引入ROWNUM僞列

如：查詢某表前5條資料

SELECT * FROM 表名 WHERE ROWNUM <=5;           

但是ROWNUM不支援

> >= =

，故需要使用子查詢實作查詢。

如：

SELECT ename, deptno
FROM (SELECT ename, deptno, rownum AS rn FROM emp) t 
WHERE t.rn > 10           

視圖

視圖就當做一張虛标，資料來源于真實的表。

建立視圖文法：

CREATE VIEW  視圖名
AS  SQL語句;           

視圖需要建立視圖權限，登陸管理者賬号執行授權指令

GRANT CREATE TABLE, CREATE VIEW TO scott;           

資料字典

表、索引、視圖等資訊儲存在ORACLE資料庫的表中，這些表稱為資料字典。

序列

自增長

• MySQL：

  auto_increment

• ORACLE:不支援

  auto_increment

實作自增長原理

擷取目前的id，自增1 id++，作為下一條資料的id插入。

id為主鍵，id實作自增長。

建立序列文法：

CREATE SEQUENCE 序列名;           

序列本身是一個對象，有兩個屬性。每次調用nextval，會自增長1個

例子：

建表

CREATE TABLE weibo(
    id NUMBER PRIMARY KEY,
    user_id VARCHAR2(20),
    content VARCHAR2(1024),
    count NUMBER
);           

建序列

CREATE SEQUENCE seq_weibo;           

怼資料

INSERT INTO weibo(id, user_id, content, count)
VALUES (seq_weibo.nextval, 'admin', '21213131ea', 1);
INSERT INTO weibo(id, user_id, content, count)
VALUES (seq_weibo.nextval, 'admin', '21213131ea', 1);           

觀察id，每次調用

nextval

會自增長1個。

SELECT * FROM weibo;           

索引

目的：為了加快查詢效率

建立主鍵時，會預設建立一個主鍵的索引。查詢索引字段效率高，非索引字段預設全表掃描。

單字段建立索引

CREATE INDEX 索引名
ON 表名(字段名)           

例：

CREATE INDEX index_user_gameid
ON t_user_fee(gameid);           

多字段建立索引

CREATE INDEX 索引名
ON 表名(字段1, 字段2, ...)           

例:

CREATE INDEX index_user_gameid_msisdn
ON t_user_fee(msisdn, gameid);           

注意： 一旦給索引的字段添加了函數，則該索引失效。