Oracle基本命令符

標籤：

（1）串連命令：
1》 conn[ect]：用法，conn 使用者名稱/密碼@網路服務名 [as sysdba/sysoper]，當用特權使用者身份串連時，必須帶上as sysdba或者as sysoper
SQL> conn sys/lipeng @orcl as sysdba;
2》 disc[onnect]：該命令用來斷開與當前資料庫的串連
3》passw[ord]：該命令用於修改使用者的密碼，如果要想修改其他使用者的密碼，需要用sys/system登入
4》show user：顯示目前使用者名
5》exit：該命令會斷開與資料庫的串連，同時會退出
（2）檔案操作命令
1》start和@：運行sql指令碼；例如：SQL>@ d:\a.sql或者SQL>START d:\a.sql
2》edit：該命令可以編輯指定的sql指令碼；例如：SQL>edit d:\a.sql
3》spool：該命令可以將sql*plus螢幕上的內容輸出到指定檔案中去。例如：SQL>spool d:\b.sql並輸入SQL>spool off

（3）顯示和設定環境變數：可以用來控制輸出的各種格式，set show如果希望永久地儲存相關的設定，可以去修改glogin.sql指令碼
1》linesize：設定顯示行的寬度，預設是80個字元：
SQL>show linesize
SQL>set linesize 90
2》pagesize：設定每頁顯示的行數目，預設是14，用法和linsize一樣

（4）許可權的授予與回收

希望xxxxx使用者可以去查詢emp表
希望xxxxx使用者可以去查詢scott的emp表：grant select on emp to xxxxxx
希望xxxxx使用者可以去修改scott的emp表：grant update on emp to xxxxxx
希望xxxxx使用者可以去修改/刪除/查詢/添加scott的emp表：grant all on emp to xxxxxx
scott希望收回xxxxxx對emp表的查詢許可權：revoke select on emp from xxxxxxx

//對許可權的維護：希望xxxxx使用者可以去查詢scott的emp表/還希望xxxxx可以把這個許可權繼續給別人（如果是對象許可權，就加入 with grant option）: grant select on emp to xxxx with grant option；（表示xxxxx有把這個許可權繼續給別人的權利）
（如果是系統許可權）grant connect to xxxx with admin option

？？？？如果scott把xxxxx對emp表的查詢許可權回收，那麼xxxxx把這個許可權繼續給yyyy的話，那麼yyyy會怎樣？
答：那麼yyyy的許可權也會被回收了

（5）使用profile系統管理使用者口令：

profile是口令限制，資源限制的命令集合，當建立資料庫時，oracle會自動建立名稱為default的profile；當建立使用者沒有指定profile選項，那oracle就會將default分配給使用者。
1》賬戶鎖定：制定該賬戶（使用者）登入時最多可以輸入密碼的次數，也可以指定使用者鎖定的時間（天）一般用dba的身份去執行該命令，例如：
制定tea這個使用者最多隻能嘗試3次登入，鎖定時間為2天，實現代碼如下：
SQL>create profile lock_account（lock_account代表建立規格的名稱，可以自己任意定義） limit failed_login_attempts 3 password_lock_time 2;（建立profile檔案即建立一種規則）
SQL>alter user tea profile lock_account;（表示將這種規格（lock_account）賦給tea）
2》給賬戶（使用者）解鎖
SQL>alter user tea account unlock;
3》終止口令：為了讓使用者定期修改密碼可以使用終止口令的指令來完成，同樣這個命令也需要dba身份來操作
例如：給使用者tea建立一個profile檔案，要求該使用者每隔10天要修改自己的登入密碼寬限期為2天，實現代碼如下：
SQL>create profile myprofile limit password_life_time 10 password_grace_time_2;
SQL>alter user tea profile myprofile

（6）口令曆史：

如果希望使用者在修改密碼時，不能使用以前使用過的密碼，可使用口令曆史，這樣oracle就會將口令修改的資訊存放到資料字典中，這樣當使用者修改密碼時，oracle就會對新舊密碼進行比較，當發現新舊密碼一樣時，就提示使用者重新輸入密碼。例如：
1）建立profile：
SQL>create profile password_history（password_history檔案名稱，可隨意更改） limit password_life_time 10 password_grace_time 2 password_resuse_time 10
password_reuse_time//指定口令可重用時間即10天后就可以重用
2）刪除profile：當不需要某個profile檔案時，可以刪除該檔案
SQL>drop profile password_history [cascade]

（7）表的修改
1》添加一個欄位：
SQL>alter tables student add(classid number(2));
2》修改欄位的長度：
SQL>alter table student modify (xm varchar2(30));
3》修改欄位的類型/名字（不能有資料）：
SQL>alter table student modify(xm char(30));
4》刪除一個欄位：
SQL>alter table student drop column sal;
5》修改表的名字：
SQL>rename student to stu;
6》刪除表：
SQL>drop table student;

（8）查看
1》表結構：
SQL>desc student;
2》所有列：
SQL>select * from 表名；
3》制定列：
SQL>select ename,sal,job,deptno from emp;
4》取消重複行；
SQL>select distinct deptno,job from emp;

（9）添加資料
1》所有字元段都插入
SQL>insert into student values(‘A001‘,‘張三‘,‘男‘,‘01-5月-01‘,10);
友情提示：oracle中預設的日期格式是‘DD-MON-YY‘ 例如：‘09-6月-99‘
修改日期的預設格式：alter session set nls_date_format=‘yyyy-mm-dd‘;
修改後可以用我們熟悉的格式添加日期類型，即：
SQL>insert into student values(‘A002‘,‘MIKE‘,‘男‘,‘1905-02-30‘,10);
2》插入部分欄位
SQL>insert into student(xh,xm,sex)values(‘A003‘,‘JOHN‘,‘女‘);
3》插入空值
insert into student(xh,xm,sex,birthday) values(‘A004‘,‘MARTIN‘,‘男‘,null);
友情提醒：當需要查詢某個表中的某列上的某個值為空白時，代碼如下：
SQL>select * from student where birthday is null;
4》修改一個欄位
SQL>update student set sex=‘女‘ where xh=‘A001‘;
5》修改多個欄位
SQL>update student set sex=‘男‘,birthday=‘1980-04-01‘ where xh=‘A001‘;
6》修改含有null值的資料
7》刪除資料
SQL>delete from student; 刪除所有記錄，表結構還在，寫日誌，可以恢複的，速度慢
友情提示：要想恢複，必須在刪除所有資料之前儲存一下節點（儲存點），即：savepoint xxx;
刪除之後只需要復原到儲存的節點即可：rollback to xxx;
SQL>drop table student; 刪除表的結構和資料
SQL>delete from student where xh=‘A001‘; 刪除一條記錄
SQL>truncate table student; 刪除表中的所有記錄，表結構還在，不寫日誌，無法找回刪除的記錄，速度快

（10）表的簡單查詢
1》給列取別名：
SQL>select sal*12 "年工資",ename from emp;
2》如何處理null值：使用nvl函數來處理
SQL>select sal*12+nvl(comm,0) "年工資",ename from emp;
其中nvl(comm,0)表示如果comm為空白值那麼就用0來代替，如果comm不為空白值，那麼就用comm來代替
3》如何連接字串
SQL>select ename || ‘is a‘|| job from emp;
4》使用where子句
SQL>select * from emp where sal>2000 and sal<2500;
5》如何使用like操作符
%：表示任意的0到多個字元，例如：SQL>select ename from emp where ename like ‘S%‘;（用來查詢首字元為S的員工姓名）
_：表示任意的單個字元，例如：SQL>select ename from emp where ename like ‘__O%‘;（用來查詢第三個字元為大寫的O的員工姓名）
6》在where條件中使用in：
SQL> select * from emp where empno in(7369,7521,7934); 表示在表中查詢empno是7369,7521,7934的員工資訊
7》使用is null 的操作符：
SQL> select * from emp where mgr is null; 表示沒有上級的員工情況
8》使用邏輯操作運算子號：
SQL> select * from emp where (sal>500 or job=‘MANAGER‘) and ename like ‘J%‘;
表示查詢工資高於500或者職位是MANAGER並且員工手寫字元是J
9》使用order by字句：
SQL> select * from emp order by sal; 表示將按照員工的工資從低到高進行排序
SQL> select * from emp order by sal desc; 表示將按照員工的工資從高到低進行排序
SQL> select * from emp order by deptno ,sal desc; 表示將按照部門從低到高排序並且在部門內部將員工工資按照從高到低排序
10》使用列的別名排序：
SQL>select ename,sal*12 "年薪" from emp order by "年薪";

 

（11）表的複雜查詢
1》資料分組----max,min,avg,sum,count函數
SQL> select max(sal),min(sal) from emp; 表示查詢最高工資與最低工資是多少
SQL> select * from emp where sal=(select max(sal) from emp); 表示查詢最高工資的員工的資訊（此處運用到了子查詢）
2》group by和having字句
group by用於對查詢的結果分組統計，having子句用於限制分組顯示結果
溫馨提示：having是將group by分好的組再進行一次篩選
SQL> select avg(sal),max(sal) from emp group by deptno; 表示查詢每個部門的平均工資和最高工資
SQL> select avg(sal),min(sal),deptno,job from emp group by deptno,job;
表示查詢每個部門的每個職位的平均工資與最低工資
SQL> select avg(sal),deptno from emp group by deptno having avg(sal)>2000;
表示將查詢出來的每個部門的平均工資篩選出平均工資高於2000的部門

對資料分組的總結：
1。分組函數只能出現在挑選清單、having、order by子句中；
2.如果在select語句中同時包含有group by、having、order by，那麼它們的順序是group by,having,order by
3.在挑選清單中如果有列、運算式、分組函數，那麼這些列和運算式必須有一個出現在group by子句中，否則會出錯

 

（12）多表查詢
1》多表
SQL> select table1.ename,table1.sal,table2.dname from emp table1,dept table2 where table1.deptno=table2.deptno; 表示查詢員工的姓名、薪水、所在公司名稱（其中姓名、薪水在emp表中，所在公司名稱在dept表中）
2》自串連：是指在同一張表的串連查詢
SQL> select work.ename,boss.ename from emp work,emp boss where work.mgr=boss.empno;
表示員工的名字與其直接上司的名字

（13）子查詢
1》子查詢：子查詢是指嵌入在其他sql語句中的select語句，也叫巢狀查詢
2》單行子查詢：是指只返回一行資料的子查詢語句
SQL> select * from emp where deptno=(select deptno from emp where ename = ‘SMITH‘);
表示查詢出與SMITH在同一個部門的所有員工的資訊
3》多行子查詢：是指返回多行資料的子查詢
SQL> select * from emp where job in (select distinct job from emp where deptno=10);
表示查詢出與部門號為10的職位相同的員工資訊
在多行子查詢中使用all操作符：
SQL> select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);
SQL> select ename,sal,deptno from emp where sal>all (select sal from emp where deptno=30);
表示查詢工資比部門30的所有員工的工資高的員工的姓名、工資和部門號
在多行子查詢中使用any操作符：
SQL> select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30);
SQL> select ename,sal,deptno from emp where sal>any (select sal from emp where deptno=30);
表示查詢工資比部門30的任意一個員工的工資高的員工的姓名、工資和部門號
4》多列子查詢：
單行子查詢是指子查詢只返回單列、單行資料；
多行子查詢是指返回單列多行資料，都是針對單列而言的；
而多列子查詢則是指返回多個列資料的子查詢語句
SQL> select * from emp where (deptno,job)=(select deptno,job from emp where ename = ‘SMITH‘);
表示查詢與SMITH的部門和崗位完全相同的所有員工資訊
5》在from子句中使用子查詢
查詢出高於自己部門平均工資的員工的資訊：
步驟1：查詢出各個部門的平均工資和部門號：
SQL>select deptno,avg(sal) mysal from emp group by deptno;
步驟2：把步驟1的查詢結果看作是一張子表；
步驟3：列出結果：
SQL>select * from emp table1,(select deptno,avg(sal) mysal from emp group by deptno) table2 where table1.deptno=table2.deptno and table1.sal>table2.mysal;
溫馨提示：當在from子句中使用查詢時，該子查詢會被作為一個視圖來對待，因此也叫做內嵌視圖，當在from子句中使用子查詢時必須給子查詢指定別名

 

（14）分頁查詢（oracle分頁查詢一共有三種方式）
1》rownum分頁
第一步：先查詢出一張子表，例如：
SQL>select * from emp;
第二步：在子表中加入行ID：
SQL>select table1.*,rownum rn from (select * from emp) table1;
溫馨提示：rn表示行ID
第三步：截取第六行到第十行的資料
select * from (select table1.*,rownum rown from (select * from emp) table1 where rownum<=10) table2 where rown>5;
溫馨提示：幾個查詢的變化：
1.所有的更改（只顯示部分資訊、排序等等）只需要更改from最裡面的字表即可，例如：
SQL> select * from (select table1.*,rownum rown from (select ename,job,sal from emp) table1 where rownum<=10) table2 where rown>5;
2》rowid分頁
SQL>select * from t_xiaoxi where rowid in (select rid from
(select rownum rn,rid from (select rowid rid,cid from t_xiaoxi order by cid desc)
where rownum<10000) where rn>9980) order by cid desc;

 

（15）用查詢結果建立新表
SQL>create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

 

 

（16）合并查詢
1》union：該操作符用於取得兩個結果集的並集，當使用該操作符時，會自動去掉結果集中重複行，例如：
SQL> select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job=‘MANAGER‘;
2》union all：合并所有並不取消重複行
3》intersect：取得交集
4》minus：取得兩個結果的差集，只會取得存在第一個集合中而不存在第二個集合中的資料

 

（17）用Java操作資料庫
1》如何使用jdbc_odbc橋串連方式
public class Test{
public static void main(String[] arge){
try{
//1.載入驅動
Class.froName("sun.jdbc.odbc.JdbcOdbcDriver");
//2.得到串連
Connection ct=DriverManager.getConnection("jdbc:odbc:testsp//配置資料來源","scott","lipeng");

Statement sm=ct.createsStatement();

//查詢總頁數
int pagecount=0; //分頁數
int rowcount=0; //共有幾條記錄
int pagesize=0; //每頁顯示幾行記錄

ResultSet rs=sm.executeQuery("select * from emp");

while(rs.next()){
System.out.println("使用者名稱："+rs.getString(2)); //2表示ename在表中第二列（預設從1開始的）

}

//關閉各種開啟資源
rs.close();
ct.close();
sm.close();

}catch(Exception e){
e.printStackTrace();
}
}
}

2》使用jdbc串連oracle
public class Test{
public static void main(String[] arge){
try{
//1.載入驅動
Class.froName("oracle.jdbc.driver.OracleDriver");
//2.得到串連
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1（127.0.0.1為要串連的oracle的IP地址）:1521（1521位oracle資料庫的連接埠號碼）:ORACLE（ORACLE為要串連的資料庫的資料庫執行個體）//資料庫的URL","scott","lipeng");

Statement sm=ct.createsStatement();

ResultSet rs=sm.executeQuery("select * from emp");

while(rs.next()){
System.out.println("使用者名稱："+rs.getString(2)); //2表示ename在表中第二列（預設從1開始的）

}

 

//關閉各種開啟資源
rs.close();
ct.close();
sm.close();

}catch(Exception e){
e.printStackTrace();
}
}
}

 

 

（18）在oracle中操作資料

1》使用特定格式插入日期值
使用 to_date函數：
to_date(‘1992/09/01‘,‘yyyy/mm/dd‘)

 

2》使用子查詢插入資料
當使用values子句時,一次只能插入一行資料,當使用子查詢插入資料時,一條insert語句可以插入大量的資料.當處理行遷移或者裝載外部表格的資料到資料庫時,可以使用子查詢來插入資料.例如：
SQL>insert into xxx (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

 

3》使用子查詢更新資料
使用update語句更新資料時,既可以使用運算式或者數值直接修改資料,也可以使用子查詢修改資料。
SQL> update emp set (job,sal)=(select job,sal from emp where ename=‘SMITH‘) where ename=‘SCOTT‘;
希望員工scott的崗位、工資、補助與smith員工一樣

 

（19）sql函數
1》字元函數
lower(char)：將字串轉化為小寫格式
SQL> select lower(job) from emp; 表示將工作轉換成小寫
upper(char)：將字串轉化為大寫的格式
length(char)：返回字串的長度
SQL> select * from emp where length(ename)=5; 表示查詢出名字只有五個字幅的員工資訊
substr(char,m,n)：取字串的子串 //n表示截取n個字元
SQL> select substr(ename,1,3) from emp; 表示截取姓名的前三個字元

 

SQL> select concat(substr(ename,1,1),lower(substr(ename,2,length(ename)))) from emp;
表示以首字母大寫的方式顯示所有員工的姓名
SQL> select concat(lower(substr(ename,1,1)),substr(ename,2,length(ename))) from emp;
表示以首字母小寫方式顯示所有員工的姓名

replace(char1,search_string,replace_string) 在char1字串中用replace_string字串替換
SQL> select replace(ename,‘A‘,‘a‘) from emp;
表示顯示所有員工的姓名,用”a”替換所有"A“
instr(char1,char2,[,n[,m]])取子串在字串的位置

 

2》數學函數
round(n,[m])該函數用於執行四捨五入,如果省掉m，則四捨五入到整數;如果m是正數,則四捨五入到小數點的m位後.如果m是負數,則四捨五入到小數點的m位前
trunc(n,[m])該函數用於截取數字.如果省掉m，就截去小數部分,如果m是正數就截取到小數點的m位後,如果m是負數,則截取到小數點的前m位
mod(m,n)m對n求模（即求餘）
floor(n)返回小於或是等於n的最大整數（即向下取整）
ceil(n) 返回大於或是等於n的最小整數（即向上取整）abs(n) 返回數字n的絕對值select abs(-13) from dual;
acos(n) :返回數位反餘旋值asin(n): 返回數位反正旋值
atan(n): 返回數位反正切cos(n)
exp(n): 返回e的n次冪
log(m,n)返回對數值
power(m,n):返回m的n次冪

特別提示：在做oracle測試時用dual表

 

 

3》日期函數
(1)sysdate: 該函數返回系統時間
SQL> select sysdate from dual;(2)add_months(d,n) 該函數指從d這個時間開始加n個月
SQL> select * from emp where sysdate>add_months(hiredate,8);
尋找已經入職8個月多的員工
SQL> select ename,hiredate from emp where sysdate>add_months(hiredate,120);
顯示滿10年服務年限的員工的姓名和受雇日期.SQL> select trunc(sysdate-hiredate) "入職天數" ,ename from emp;
對於每個員工,顯示其加入公司的天數.(3)last_day(d):返回指定日期所在月份的最後一天SQL> select * from emp where (last_day(hiredate)-hiredate)=2;
找出各月倒數第3天受雇的所有員工.

4》轉換函式
轉換函式用於將資料類型從一種轉為另外一種.在某些情況下，oracle server允許值的資料類型和實際的不一樣，這時oracle server會隱含的轉化資料類型，比如:create table t1(id number);insert into t1 values(’10’) -->這樣oracle會自動的將‘10‘-->10create table t2 (id varchar2(10));insert into t2 values(1); -->這樣oracle 就會自動的將1--->‘1‘;我們要說的是儘管oracle可以進行隱含的資料類型的轉換,但是它並不適應所有的情況，為了提高程式的可靠性,我們應該使用轉換函式進行轉換to_char：
yy: 兩位元字的年份 2004-->04
yyyy: 四位元字的年份 2004年mm :兩位元字的月份 8月-->08dd: 2位元字的天 30號-->30
hh24: 8點－－》20hh12: 8點－－》08
mi、ss -->顯示分鐘\秒

9:顯示數字,並忽略前面0
0:顯示數字,如位元不足,則用0補齊
.：在指定位置顯示小數點
,: 在指定位置顯示逗號
$: 在數字前加美元
L: 在數字前加本地貨幣符號
C: 在數字前加國際貨幣符號
G:在指定位置顯示組分隔字元、
D:在指定位置顯示小數點符號(.)
select ename,to_char(sal,‘L99G999D99‘) from emp ;

SQL> select * from emp where to_char(hiredate,‘yyyy‘)=1980;
顯示1980年入職的所有員工
to_date函數to_date用於將字串轉換成date類型的資料.

 

5》系統函數
sys_context：
1) terminal ：當前會話客戶所對應的終端的標識符2) lanuage: 語言
3) db_name: 當前資料庫名稱
4) nls_date_format:當前會話客戶所對應的日期格式
5) session_user: 當前會話客戶所對應的資料庫使用者名稱
6) current_schema: 當前會話客戶所對應的預設方案名？
7) host: 返回資料庫所在主機的名稱以上7個全部都是sys_context的參數
通過該函數，可以查詢一些重要訊息，比如你怎在使用哪個資料庫?select sys_context(‘userenv‘,‘db_name‘) from dual;
溫馨提示：userenv是不可改變的

 

（20）資料字典（就是sys使用者的靜態表和動態視圖）
1）user_tables：用於顯示目前使用者所擁有的所有表，它只返回使用者所對應方案的所有表
SQL> select table_name from user_tables;
2）all_tables：用於顯示目前使用者可以訪問的所有表，他不僅會返回目前使用者反感的所有表，還會返回目前使用者可以訪問的其它方案的表
SQL> select table_name from all_tables;
3）dba_tables：它會顯示所有方案擁有的資料庫表，但是查詢這種資料庫字典視圖，要求使用者必須是dba角色或者是有select any table系統許可權，例如：當用system使用者查詢資料字典視圖dba_tables時，會返回system,sys,scott……方案所對應的資料庫表
SQL> select table_name from dba_tables;

4）使用者名稱、許可權、角色
在建立使用者時，oracle會把使用者的資訊存放到資料字典中，當給使用者授予許可權或者是角色時，oracle會將許可權和角色的資訊存放到資料字典。
通過查詢dba_users可以顯示所有資料庫使用者的詳細資料；
通過查詢資料字典視圖dba_sys_privs，可以顯示使用者所具有的系統許可權；
通過查詢資料字典視圖dba_tab_privs可以顯示使用者具有的對象許可權；
通過查詢資料字典dba_col_privs可以顯示使用者具有的列許可權；
通過查詢資料庫字典視圖dba_role_privs可以顯示使用者所具有的角色
oracle究竟有多少種角色？
SQL>select * from dba_roles;
查詢oracle中所有的系統許可權，一般是dba
SQL>select * from system_privilege_map order by name;
查詢oracle中所有對象許可權，一般是dba
SQL>select distinct privilege from dba_tab_privs;
查詢資料庫的資料表空間
SQL>select tablespace_name from dba_tablespaces;
查詢使用者具有怎樣的角色
SQL>select * from dba_role_privs where grantee=‘使用者名稱‘
查看某個角色包括哪些系統許可權
SQL>select * from dba_sys_privs where grantee=‘DBA‘（DBA表示一個角色）
或者是
SQL>select * from role_sys_privs where role=‘DBA‘;
查看某個角色包括的對象許可權：
SQL>select * from dba_tab_privs where grantee=‘角色名稱‘
顯示目前使用者可以訪問的所有資料字典視圖：
SQL>select * from dict where comments like ‘%grant%‘;
顯示當前資料庫的全稱：
SQL>select * from global_name;

 

（21）建表
1》主鍵：create talbe customer(customerId char(8) primary key,--主鍵
2》外鍵：create table purchase(customerId char(8) references customer(customerId),--外鍵
3》其他約束條件直接寫在定義變數的後面
4》設定預設選項和幾選一選項：sex char(2) default ‘男‘ check (sex in (‘男‘,‘女‘))

Oracle基本命令符