oracle 簡單備忘

標籤：start   last   資料庫操作   char   需要   不同   ber   邏輯   word   

1. 建立資料庫

備忘：

1) oracle 不同於mysql 可以直接create database

2) oracle 建立schema時對應一個使用者，即該schema的訪問使用者，與使用者一一對應；但可以存在多個訪問使用者（帶許可權控制）

1.1 建立資料庫檔案

CREATE TABLESPACE XX LOGGING DATAFILE ‘D:\app\XX\oradata\orcl\XX.dbf‘ SIZE 1000M;

create temporary tablespace XX tempfile ‘D:\app\XX\oradata\orcl\XX.dbf‘ size 1000m;

1.2 建立使用者

CREATE USER XX IDENTIFIED BY XX DEFAULT TABLESPACE XX TEMPORARY TABLESPACE XX;

1.3 授權

grant connect, resource to XX;
grant create session to XX;

 

2. 資料庫操作(預設sccot使用者)：

2.1 create

create table persons(
person_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
score NUMBER,
type VARCHAR2(20)
);

備忘：

1) ORA-02000: missing ALWAYS keyword : 在11g版本裡不用用GENERATED BY DEFAULT AS IDENTITY ，要用PRIMARY KEY
2) oracle 本來只有number類型，用作number(19,2)，即有小數位；後為了相容其他資料庫，新增int，只能是整形

2.2 insert

insert into persons values(1, ‘fred‘, ‘xu‘, 0, ‘a‘);

insert into persons values(SEQ_PERSON_ID.NEXTVAL, ‘fred3‘, ‘xu3‘, 0, ‘b‘)

備忘：

1）oracle下設定自增沒有mysql那麼簡單，步驟如下：

1.1） CREATE SEQUENCE SEQ_PERSON_ID start with 100; #建立一個序列
1.2) INSERT INTO persons VALUES(SEQ_PERSON_ID.NEXTVAL, ‘fred1‘, ‘xu1‘, 0, ‘a‘);

#此時插入persons表記錄的person_id被設定成了100

1.3）也可以採用觸發器的形式
CREATE TRIGGER persons_trigger
BEFORE INSERT ON persons
FOR EACH ROW
WHEN (new.person_id is null)
begin
select SEQ_PERSON_ID.nextval into :new.person_id from sys.dual;
end;

備忘：sys.dual 是個虛擬表，oracle保證裡面只有一條記錄
:new— 觸發器執行過程中觸發表作操作的當前行的新紀錄
:old— 觸發器執行過程中觸發表作操作的當前行的舊紀錄

在有觸發器之後，插入資料時不需要填寫主鍵 INSERT INTO persons(first_name, last_name, score, person_type) VALUES(‘fred2‘, ‘xu2‘, 0, ‘a‘);

2.3 select

select * from persons where first_name = ‘fred‘ or last_name = ‘xu1‘
select * from persons where first_name like ‘%fred%‘
select * from persons where CONCAT(first_name, last_name) like ‘%1%‘

select * from users where rownum BETWEEN 0 AND 5 #利用rownum關鍵字分頁

2.4 group by

select AVG(score), person_type from persons group by person_type
select AVG(score), person_type from persons group by person_type having person_type = ‘b‘

備忘：

1）ORA-00979: not a GROUP BY expression group by 後的列要能被處理

2.5 in

select * from persons where person_type in (‘a‘,‘b‘);

2.6 insert all

insert all
into persons(first_name, last_name, score, person_type) values(‘test‘, ‘test‘, 1, ‘c‘)
into persons(first_name, last_name, score, person_type) values(‘test1‘, ‘test1‘, 1, ‘c1‘)
into persons(first_name, last_name, score, person_type) values(‘test2‘, ‘test2‘, 1, ‘c2‘)

SELECT 1 FROM dual;

備忘：

SELECT 1 FROM dual; 最後一次select必須有

2.7 視圖

create view person_view as select * from persons where first_name like ‘%f%‘

select * from person_view;

備忘：

視圖是需表，只是邏輯定義；除非是一種物化視圖，那個才是有物理佔用

2.8 索引

create index person_index on persons (first_name);

oracle 簡單備忘