oracle（2）oracle的基礎入門

標籤：file   bsp   das   cursor   cot   報錯   不能   修改   小數   

1.sys與system

system系統管理使用者，sys超級使用者，sys帳號比system賬戶的許可權更多，在oracle11以後沒有了scott使用者

 

2.表的匯入匯出

imp 使用者名稱/密碼@服務名 file=資料庫表.dmp存放的地址   ignore=y  full=y //匯入

exp 使用者名稱/密碼@服務名 tables=表名 file=存入本地dmp目標地址 log=存入本地log目標地址 owner=要匯出的使用者名稱//匯出

PL/SQL Developer工具匯出： 功能表列---->Tools---->Export Tables//匯出

PL/SQL Developer工具匯出： 功能表列---->Tools---->import Tables //匯入

 

3.oracle對大小寫以及大於等於

sql 語言大小寫不敏感（例如關鍵字，欄位名，表名（mysql這個預設是敏感的）），sql 可以寫在一行或者多行，關鍵字不能被縮寫也不能分行，sql只有<=沒有>=,只有>，但是對例如欄位內容敏感（mysql預設是不敏感的）

 

4.null代表的含義（與mysql一樣）

空值是無效的，空值不是空格或者0，任何一個值與空運算結果都是null

 

 5.‘’與""

字元，日期只能是單引號，別名對於特殊（欄位名，關鍵字等）需要使用雙引號。

 

6.||

||符類似concat或者java中的+

 

7.escape

escape逸出字元，類似java與mysql中的\；

 

8.時間函數

next_day（date，week）  //指定日期的下一個星期對應的日期，日期預設格式是dd-mm -yyyy

months_between（date1，date2） // 兩個日期相差的月數

last_day（date）  //本月的最後一天

add_months（date，month）//向指定日期中加上若干月數

to_char(date, ‘format_model‘)  //轉換時間： yyyy年， mm月，dd日，day星期 ，hh小時，mi分鐘 ，ss 秒；轉換成數字：9數字//存在則取代，不存在則沒有 ，0零//不存在用0取代，$美元符，L本地貨幣符號，.小數點，,千位符

trunc (sysdate ,‘year‘) //日期截斷，

to_date (char, ‘format_model‘)//格式需要滿足一致yyyy年， mm月，dd日，day星期 ，hh小時，mi分鐘 ，ss 秒

備忘：日期上加上或減去一個數字結果仍為日期，兩個日期相減返回日期之間相差的天數。兩個日期之間不能做加法。

 

9.其他一些函數

lpad(數值,10,字元)  //不足10位左邊補字元，10自訂

rpad(數值, 10, 字元) //不足10位右邊補字元，10自訂

trim(‘h‘ from‘helloworld‘) //去除頭和尾的字元h

replace（‘abcd’,’b’,’m’) //將所有的b替換成m

trunc(45.926, 2) //保留兩位小數，取整

round(45.926, 2) //保留兩位小數，四捨五入

to_number(char, ‘format_model‘)//格式需要滿足一致，$,L可做加法運算

nvl (expr1, expr2)  //如果不為空白那麼是其本身expr1，為空白那麼是expr2，注意oracle中需要expr1和expr2的欄位類型一致，否則需要轉型，類似mysql的ifnull

nvl2 (expr1, expr2, expr3)//滿足條件expr1則為expr2，否則為expr3，類似mysql的if

nullif (expr1, expr2)// expr1與expr2相等返回null，不等返回expr1

coalesce (expr1, expr2, ..., exprn) //coalesce表示expr1,為buul則expr12依次下去

 

10.條件迴圈

條件運算式同mysql；

case when  then  同mysql；

while 條件 loop  迴圈體 end；//迴圈

 

11.連表查詢基本同mysql，cross join  交叉串連同where  e，t；using 子句指定等值串連中需要用到的列（相當於where e.deptno=d.deptno）；

 

12.建立表和修改表

1）表名和列名:命名規則

必須以字母開頭

必須在 1–30 個字元之間

必須只能包含 a–z, a–z, 0–9, _, $, 和 #

必須不能和使用者定義的其他對象重名

必須不能是oracle 的保留字

資料類型number為取代mysql的int，double等

varchar2類似於mysql的varchar，也有char

blob 二進位最大4g

clob 字元資料最大4g

long 可變長字元資料，最大2g

date  日期

 

2）重新命名資料表的列

modify，add同mysql

alter  table 表名 drop column 列名

alter  table  表名 rename  column 舊列名 to 新列名

truncate table 表名 //清空資料，不能復原，delete能復原

rename 表名 to 新表名;

 

13.事務

commit開始 或 rollback復原語句，復原點為最近的一次commit

savepoint  復原點名  ，rollback  to  復原點，可以回到相應的復原位置

備忘：

ddl語句（自動認可），不會復原，復原是對於dml語句而言，添加事務之後dml所涉及的行被鎖定，類似mysql的innodb，在沒有commit之前其他使用者不能見到自己的修改，自己能見到是讀己之所寫，類似mysql，oracle預設不會自動認可dml語句，需要使用使用commit（mysql預設自動認可）

select * from 表名  for update；//查詢更新後的表，假如這個查詢結果中有被鎖，那麼會被處於阻塞狀態

 

14.視圖

視圖的建立同mysql，修改沒有的列會產生錯誤與mysql不同（mysql無效，但不會報錯）

組函數,group by子句,distinct 關鍵字,rownum 偽列,列的定義為運算式不能使用update與insert。添加with read only 表示該視圖唯讀。

 

15.序列

類似auto_increment，但又不同，auto_increment必須位於索引之上，且每張表唯一，作用於聯合索引之上排序後相同則自增左1欄位，序列， 唯一的數值，可以充當主索引值，共用對象，裝入記憶體可以提高訪問效率，首次預設不存在，需要使用nextval之後才能使用currval，不可以修改序列值，需要刪除後重建，可以修改序列的增量, 最大值, 最小值, 迴圈選項, 或是否裝入記憶體，序列的裂縫可能出現在多個表使用同一個序列，復原（復原對序列無效），系統崩潰。

文法：

create sequence sequence；

       [iincrement by  n]  --每次增長的數值

       [start with n]    --從哪個值開始

       [{maxvalue n | nomaxvalue e}]

       [{minvalue  n | nominvalu e }]

       [{cycle| nocycle}]     --是否需要迴圈

       [{cache  | nocache}];  --是否緩衝登入

 

16.索引

刪除一個表時,所有基於該表的索引會自動被刪除，可以通過指標加速 Oracle 伺服器的查詢速度，減少磁碟 I/O，索引的建立刪除同mysql

 

17.同義字

create  synonym  別名 for 表名;//建立，類似給表起別名

drop synonym  別名；//刪除同義字

 

18.許可權管理

1）建立，修改使用者以及更改密碼

create user 使用者名稱 identified by   密碼;//添加使用者

alter  user 使用者名稱 identified by   新密碼；//修改密碼

grant 許可權    to 使用者名稱;//賦予許可權

一般包括如下許可權

create session（建立會話）

create table（建立表）

create sequence（建立序列）

create view（建立視圖）

create procedure（建立預存程序）

備忘：需要建立表的許可權，除了需要建立表許可權，還需要使用alter user 使用者名稱 quota unlimited   on users

 

2）角色的建立，修改

create role 角色名稱；//建立角色

grant 許可權    to 角色名稱;//賦予許可權

grant 角色 to 使用者名稱...;//賦予角色給使用者  

grant      對象許可權 on 對象 to 使用者/角色；//建立者具有所有許可權，system也有許可權，擁有者給其他人分配許可權，角色使用者為public表示對所有人具有許可權

grant      對象許可權 on 對象 to 使用者/角色with grant option；//使使用者同樣具有分配許可權的權利

 

3）取消許可權

revoke 許可權 on 對象 from  使用者/角色；//with grant option的許可權同樣被收回

revoke 許可權 on 對象 from  使用者/角色cascade constraints;//去除級聯許可權，a賦予b，b賦予c，a使用cascade constraints去除b的許可權，那麼b賦予geic的許可權同樣會清除

 

備忘：許可權分配情況可查詢的表，可以在刪除相應的使用者與角色

role_sys_privs  角色擁有的系統許可權

role_tab_privs 角色擁有的對象許可權

user_role_privs      使用者擁有的角色

user_tab_privs_made   使用者指派的關於表對象許可權

user_tab_privs_recd     使用者擁有的關於表對象許可權

user_col_privs_made    使用者指派的關於列的對象許可權

user_col_privs_recd      使用者擁有的關於列的對象許可權

user_sys_privs 使用者擁有的系統許可權

 

19.set操作符

union/union all //union可以去重，union all不會去重，intersect  //取交集，minus //減，用法都同union

 

20.pl/sql文法

1）一般用法

declare

聲明變數 //a  int（10）或者a  表名.欄位名%type；表名.欄位名%type表示與表名的欄位名的類型一致，表欄位的類型改變，該處也隨之改變

type 變數類 is  record（//類似java中的類與屬性

b 表名.欄位名%type；

）

變數類執行個體 變數類

cursor 遊標名稱 is select..//定義遊標，將select單行的查詢結果放入遊標中，

begin

select  ... into 變數類名執行個體  from ... //select後的欄位名順序與數量需要與變數類名保持一致，後續使用可以使用類名.變數讀取

fetch 遊標名稱 into 變數；//提取遊標值，遊標名%found類似於iterate的hasnext，可以用迴圈提取每次遊標指向的值

close 遊標名//關閉遊標

exception  //沒有書寫異常，那麼這裡不寫，when  異常的類型 then 輸出列印；

end

 

2）函數

Create  or  replace function 函數名（參數）return 傳回值類型

is  聲明變數類似declare；

begin；

return；

exception

end 函數名；

 

3）預存程序//在函數的基礎上去除return

create or replace procedure 預存程序名（參數） 

as  //聲明   

begin // 執行 

exception//預存程序異常 

end;

備忘：參數類型有3中 in 可以是值，out必須是變數，inout 必須是變數 //create   procedure 過程名 （in  形參名稱 資料類型 ，out   形參名稱 資料類型，inout  形參名稱 資料類型），蒸鍋過程是滯後的，只有在內部完成之後，賦值才給傳入給外部的全域變數。

 

4）觸發器

Create or  replace trigger 觸發器..//後面的同mysql

備忘：要使用輸出需要開啟  set  serveroutput on；

 

oracle（2）oracle的基礎入門