Oracle基礎（二）---操作命令，oracle基礎---命令

Oracle基礎（二）---操作命令，oracle基礎---命令

接上篇部落格介紹Oracle基本概要，下面將介紹資料庫的操作指令。

Sql*plus常用命令

串連命令

1、 conn[ect]

      用法 conn使用者名稱/密碼@網路服務名[as sysdba/sysoper] 當用特權使用者身份串連時，必須帶上as sysdba或者是as sysoper

檔案操作命令

 1、start 和@

      運行sql指令碼      案例sql >@ d:\a.sql或者sql>start d:\a.sql

2、 edit

         該命令可以編輯制定的sql指令碼  Sql>edit d:\a.sql

3、spool  

      該命令可以將sql*plus螢幕上的內容輸出到指定檔案中去，案例：slq>spool d:\b.sql 並輸入sql>spool off

顯示和設定環境變數

      可以用來控制輸入的各種格式，set show 如果希望永久的儲存相關設定，可以去修改glogin.sql指令碼

1、 linesize 設定顯示行的寬度，預設是80個字元

       Sql>show linesize

       Sql>set linesize 90   

        Pagesize  設定每頁顯示的行數目，預設是14 Set pagesize 8

2、建立使用者

      Create user 一般具有dba的許可權才可以使用

      Sql>create user xiaoming identified bym123   名字以字母開頭

3、 修改密碼  自己密碼 sql >password 使用者名稱  System 可以修改

4、刪除使用者  自己刪自己不可，drop user ；如何刪除的使用者，已經建立了表，那麼需要在刪除時帶一個參數cascade

5、使用者管理

      為新使用者添加登入許可權：串連 sql>conn 使用者名稱

      賦許可權 sql>grant connect to xiaoming

      許可權：系統許可權，使用者對資料庫的相關許可權，登入，修改密碼等； 對象許可權使用者對其他使用者的資料對象訪問的許可權。

       角色：自訂角色；預定義角色；Dba角色

Resourse 任意在資料表空間建表

       建表 sql>create table test(userID varchar2(30)

       對象許可權分類：Select,insert,update ,delete .alll ,createindex

       授權：Grant select on emp to xiaoming

         Sql>conn xiaoming /m1234

        Sql>select * from scott.emp;

       希望使用者具有所有許可權：Grant all on emp to xiaoming

        收回許可權：Revoke select on emp from xiaoming

        對許可權的維護，許可權的傳遞

        如果是對象許可權，加入with grand option例如：    Grant select on emp to xiaoming with grantoption

       建立使用者

      Sql>create user xiaohong identified bym123

      Sql>grant connect on xiaohong

      Sql>conn xiaoming /m123

      Sql>grant select on scott.emp toxiaohong

       如果是系統許可權

       Grant connet to xiaoming with admin option

       xiaoming 可以將許可權下分到另一個人 

使用 profile系統管理使用者口令（是口令限制，資源限制的命令集合）

1、 賬戶鎖定

      登入次數的限定

      Sql> create profile lock_account()規則名稱 limistfailed_login_attempts 3 password_lock_time 2（天）;

      Sql>alter user teaprofile lock_account;

2、 給使用者解鎖

       Sql>alter user tea account unlock;

3、終止口令

      Dba身份   Sql>create profile myprofile limitpassword_life_time10 password_grace_time2;

      每10天修改密碼。2天的延遲

      Sql>alter user tea profile myprofile.

      口令曆史：不能使用以前的密碼：

      建立profile：Sql>createprofile password_historylimitpassword_life_time 10password_grace _time2password_reuse_time 10    注釋：Password_reuse_time 10天后可以重複

4、刪除profile

      Sql>drop profilepassword_history[cascade]（級聯）

Oracle表的管理

1、資料類型

     字元型： Char  定長 最大2000字元

                      Varchar2(20) 變長，最大4000字元

                     Clob(character large object)字元型大對象  最大4G

     數字類型

     Number 範圍-0的-38次方，到10的38次方

     Number(5,2)表示一個小數位有5位有效數，2位小數 範圍-999.99-999.99

     Number（5）表示一個五位整數   -99999-99999

     日期類型

     Date 包含年月日和時分秒   Timestamp 擴充

     圖片：Blob 位元據，可以存放圖片、聲音 4g

2、建表：

      Sql> create table users (usernamechar(200),

      Sql>create table classes(classId

      Sql> dec

3、添加欄位

     Sql>alter table student add(classidnumber(2));

4、修改

      Sql>alter table student modify(xmvarchar(30));

5、刪除

       Sql>alter table student drop column sal;

6、修改變名稱

      Sql>rename student to stu;

7、刪除表

     Sql>drop table student ;

8、添加資料

      Insert into student values(‘A001’,’張三‘)；預設時間格式 dd-mon-yy 09-6月-99  

      修改日期的預設格式 alter session setnls_date_fomart=’yyyy-mm-dd’

      修改後添加資料

      Insert into student values(‘a002’,’mike’,’男’，‘1905-05-06‘)；

9、插入部分欄位

       Insert into student(xh,xm,sex)values(‘A003’,’john’,’女”)；

10、插入空值

       Insert into student (xh,xm，sex,birthday)values(‘a004’,’mat’,’男’，null)

11、改一個欄位

        Update student set sex=’女“

12、儲存點：Sql> savapoint aa;Sql>rollback to aa;復原

13、刪除資料

       Delete from student

       刪除所有記錄，表結構還在，寫日誌，可以恢複，速度慢

        Drop table student 刪除表的結構和資料

        Delete from student where xh=‘a001’; 刪除一條資料

       Truncate table student;

       刪除表中的所有記錄，表結構還在，不謝日誌，無法找回，速度快

14、查看錶結構：Sql>desc dept

Oracle查詢

1、如何取消重複行

      Select distinct deptno, job from emp;

2、複製

       Sql>nstert into users(userid,username,userpss) select *　from users

3、使用算數運算式

4、使用列的別名：Select ename “姓名” ,en fromemp;別名

       Sql> select name from emp

5、如何處理null

     使用nvl（comm,0）如果comm是空值則用0來表示

6、如何連接字串（||）

      Select ename || ‘is a’|| job form emp;

7、使用where、

     Sql> select ename,sal from emp wheresal>3000; sal>=2000 and sal|<=2500

8、使用 like

      Select ename,sal from emp where emp whereename like ’_ _o%’

9、在where條件中使用in

      Select *from emp  where empno in(11,234,456);

10、使用is null 的操作符

        Select * from emp where mgr is null;

11、使用邏輯操作符：使用order by，使用列的別名排序

12、分頁查詢

         Group by 用於對查詢結果分組統計

         Having 子句用於限制分組顯示結果

13、多表查詢

        按部門排序

        Sql>select？，？，？，from empa1,dept a2 where a1.deptno= a2.deptno order by a.deptno;

         如果是group by 欄位必須包含在查詢的欄位裡面

14、自串連：是指在同一張表的連結查詢

        Select worker.name.boss.name  from emp worker, emp boss where  worker.mgr = boss.empno workere.name =‘frode’;

15、單行子查詢

        Select *from emp where deptno =(select…);     資料在執行sql是從左至右執行

 16、多行子查詢

        Select ename,sal,dept from emp wheresal>all(select sal form emp where deptno =30);

        任一：Select ename,sal,dept from emp wheresal>any(select sal form emp where deptno =30);

17、多列子查詢

       Select * from emp where (deptno,job)=(selectdeptno,job from emp where name=’’);

18、分頁查詢

        右子查詢過來

        Select a1.* ，rownum rn from(select * from emp) al;

       一共有三種方式

       1.      rownum 分頁

        select * from emp

         select * from (Select a1.* ，rownum rn from (select * from emp) alwhere rownum<=10) where rn>6;

       2.      顯示rownum

       Select  al.* ,rownum  rn form emp

19、建表並將其他表資料匯入到新表

        Create table mytable (id,name ,sal,job,deptno) as select empno,ename,sal ,job ,deptbo from emp;

20、合并查詢

       1 union 取得兩個結果集的並集，自動去掉重複行。

        Select ename,sal ,job from emp wheresal>2500 union select ename ,sal,job from emp where job =’manager’;

       2 union all   同Union ，但不取消重複行，而且不會排序

        Select ename,sal ,job from emp wheresal>2500 union all select ename ,sal,job from emp where job =’manager’;

        3 minus合并查詢

       獲得兩個結果集的差集，只顯示存在第一個集合中，而不存在第二集合中的資料

       Select ename,sal ,job from emp wheresal>2500 minus select ename ,sal,job from emp where job =’manager’;

以上是基本的操作命令，一開始覺得挺多的，但是後來覺著這些東西跟SQL Server的SQL語句一樣。所以學習的東西就少了很多。不過這些東西挺多的，所以就懶下別記了，什麼時候用什麼再回來看看，孰能生巧，這些命令特別是資料庫的查詢，如何寫，將影響到資料的查詢效能。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。