ORACLE-常用基礎指令總結

博文說明【前言】：

本文将通過個人口吻說明記錄oracle常用基礎指令，在目前時間點【2017年5月16号】下，所掌握的技術水準有限，可能會存在不少知識了解不夠深入或全面，望大家指出問題共同交流，在後續工作及學習中如發現本文内容與實際情況有所偏差，将會完善該博文内容。

關于表空間及使用者建立相關指令，歡迎看我的另一篇博文：“ORACLE-使用者及表空間建立指令總結”

博文連結位址：http://watchmen.blog.51cto.com/6091957/1926409

正文：

Oracle中的增删改查指的是：insert、delete、update、select

Oracle中最精華的部分：對使用者的管理，對權限的控制

一：增【插入資料及添加指令】

1、往表中插入日期格式資料

插入全部字段：

<code>SQL> insert into student values(</code><code>'mark1'</code><code>,to_date(</code><code>'2003-08-21'</code><code>,</code><code>'YYYY-MM-DD'</code><code>));</code>

【oracle預設的日期格式是DD-MON-YY 日-月-年，如果要用預設的方式插入資料化就是這種形式：insert into stu values('2011','zhangsan','男','50','12-11月-1990'); 注意，要寫中文月字】

插入部分字段：

<code>sql>insert into student(xh,xm,sex,birthday) values(</code><code>'a222'</code><code>,</code><code>'john'</code><code>,</code><code>'女'</code><code>,null);</code>

2、修改插入的預設日期格式為年月日

<code>sql>alter session setnls_date_format=</code><code>'yyyy-mm-dd'</code><code>;</code>

3、往表中添加一個字段

<code>SQL> alter table student add classId number(2);</code>

【注意】oracle在增加字段的時候隻會增加到表的最後，不能自定義該字段的位置，不能使用befor或者after，不同于MySQL

二：删【删除資料指令】

1、删除使用者

<code>sql>drop user scott [cascade];</code>

如果要删除的使用者，已經建立了表，那麼就需要在删除時帶上cascade參數

2、删除字段

<code>sql>alter table studentdrop column sal;</code>

3、删除表中的資料【表還在】

<code>sql> </code><code>delete</code> <code>from student;--删除資料，表結構還在，在删除過程中會寫日志，後期還可以進行恢複</code>

<code>sql> truncate table student;</code>

<code>--删除表中的資料，表結構還在，在删除過程中不寫日志，無法找回删除的記錄，是以删除的速度較快</code>

<code>--但後期無法對資料進行恢複</code>

4、删除表【删除表的資料和結構】

<code>sql> drop table student;</code>

三：改【修改資料指令】

1、修改字段的類型或長度【注意：此時該字段不能有資料】

<code>sql>alter table studentmodify xm </code><code>char</code><code>(30);</code>

2、修改字段的名字

<code>sql>alter table student renamecolumn xm to newxm;</code>

3、修改表中某個字段的指

<code>sql> update student setsex=</code><code>'女'</code> <code>wherexh=</code><code>'A0001'</code><code>;</code>

4、修改表中某個字段的值的一部分

<code>sql> update tablename set configvalue=replace(configvalue,</code><code>'10.10.68.172:5003'</code><code>,</code><code>'30.1.32.73:8052'</code><code>)</code>

指令解釋：将10.10.68.172:5003替換成30.1.32.73:8052

5、修改表的名字

<code>sql></code><code>rename</code> <code>student tostu;</code>

6、修改使用者密碼

<code>Sql> alter user “</code><code>system</code><code>” identified by “cxh123456”;</code>

四：查【查詢資料指令】

1、查詢某一個字段的值為空值或不為空值

<code>SQL> select * fromstudent where birthday is null[is not null];</code>

2、查詢結果取消重複記錄

<code>sql> select distinct deptno,job from emp;</code>

3、查詢date日期類型字段

<code>select * from stu whereto_char(LOGINDATE,</code><code>'yyyy-MM-DD hh24:mi:ss'</code><code>)=</code><code>'2016-07-11'</code><code>;</code>

【進行轉義，将date類型轉義成char類型并制定格式，然後進行比對】

4、查詢結果計算平均值并指定輸出位數

select count(distinct cust.regist_no) "每周案件總數",

trunc(count(distinctcust.regist_no)/14,2) "每日平均數"

【總數除以14，指定輸出2位】

from CP_CUST_REGSIT_INFOcust where xxxx…..

查詢3個或3個以上條件【使用邏輯操作符号】

5、查詢結果，并将字段的值指定别名：

SQL> select * from empwhere (sal>500 or job='manager') and ename like 'J%';

select CUSTOMER_CODE "客戶編号",SERVICE_CODE "客服編号",decode(SER_OR_CLI,0,'客服人員',1,'客戶') "消息發送者",CONTENT "聊天内容",to_char(time,'yyyy-mm-dd hh24:mi:ss') "聊天日期"

from CP_WECHAT_NEWS_HIS

whereto_char(time,'yyyy-mm-dd') between '2016-09-26' and '2016-09-29'

group byCUSTOMER_CODE,SERVICE_CODE,SER_OR_CLI,CONTENT,time order by CUSTOMER_CODE,time;

五：查詢系統參數指令

1、檢視表空間使用率(M)

<code>set lines 300 pages 1000</code>

<code>col tablespace_name </code><code>for</code> <code>a50</code>

<code> </code><code>select a.tablespace_name,</code>

<code> </code><code>round(a.s,2) </code><code>"CURRENT_TOTAL(MB)"</code><code>,</code>

<code> </code><code>round((a.s - f.s),2) </code><code>"USED(MB)"</code><code>,</code>

<code> </code><code>round(100 - f.s / a.s * 100, 2) </code><code>"USED%"</code><code>,</code>

<code> </code><code>round(f.s / a.s * 100, 2) </code><code>"FREE%"</code><code>,</code>

<code> </code><code>g.autoextensible,</code>

<code> </code><code>round(a.ms,2) </code><code>"MAX_TOTAL(MB)"</code>

<code> </code><code>from (select d.tablespace_name,</code>

<code> </code><code>sum(bytes / 1024 / 1024) s,</code>

<code> </code><code>sum(decode(maxbytes, 0, bytes, maxbytes) / 1024 / 1024) ms</code>

<code> </code><code>from dba_data_files d</code>

<code> </code><code>group by d.tablespace_name) a,</code>

<code> </code><code>(select f.tablespace_name, sum(f.bytes / 1024 / 1024) s</code>

<code> </code><code>from dba_free_space f</code>

<code> </code><code>group by f.tablespace_name) f,</code>

<code> </code><code>(select distinct tablespace_name, autoextensible</code>

<code> </code><code>from DBA_DATA_FILES</code>

<code> </code><code>where autoextensible = </code><code>'YES'</code>

<code> </code><code>union</code>

<code> </code><code>select distinct tablespace_name, autoextensible</code>

<code> </code><code>where autoextensible = </code><code>'NO'</code>

<code> </code><code>and tablespace_name not in</code>

<code> </code><code>(select distinct tablespace_name</code>

<code> </code><code>from DBA_DATA_FILES</code>

<code> </code><code>where autoextensible = </code><code>'YES'</code><code>)) g</code>

<code> </code><code>where a.tablespace_name = f.tablespace_name</code>

<code> </code><code>and g.tablespace_name = f.tablespace_name order by </code><code>"FREE%"</code><code>;</code>

2、檢視表空間的資料檔案對應的實體檔案名稱及路徑

<code>select file_id,</code>

<code>round(bytes / (1024 * 1024), 0)total_space</code>

<code>FROM dba_data_files </code>

<code>ORDER BY tablespace_name;</code>

3、檢視表空間的詳細資訊

<code>select * from dba_data_files;</code>

4、檢視詳細的表空間使用情況【Byte，M等機關詳細顯示】

SELECT a.tablespace_name "表空間名", total "表空間大小", free "表空間剩餘大小", (total - free) "表空間使用大小", total / (1024 * 1024 * 1024) "表空間大小(G)", free / (1024 * 1024 * 1024) "表空間剩餘大小(G)", (total - free) / (1024 * 1024 * 1024) "表空間使用大小(G)", round((total - free) / total, 4) * 100 "使用率 %" FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name;

5、檢視目前資料庫的字元集

<code>select userenv(</code><code>'language'</code><code>) from dual;</code>

<code>select * from nls_database_parameters where parameter=</code><code>'NLS_CHARACTERSET'</code><code>;</code>

6、檢視資料庫版本

<code>select *from v$version;</code>

7、檢視oracle資料庫的service_name

<code>select name from v$database;</code>

8、檢視目前連接配接資料庫的SID (Instance)

<code>select instance_name from v$instance;</code>

9、檢視oracle全局資料庫名

<code>select * from global_name;</code>

<code>輸出類似：ORACLE10.REGRESS.RDBMS.DEV.US.ORACLE.COM 【name.domain】</code>

10、檢視資料庫中有哪些使用者

<code>SQL> selectusername,password from dba_users;</code>

<code>SQL> select * fromall_users;</code>

11、檢視oracle下所有的表空間，包括臨時表空間

<code>SQL> select tablespace_name from dba_tablespaces; --這張表隻存放表空間資訊，不包含使用者資訊什麼的</code>

<code>select * from dba_tablespaces; --可以看到表空間的block_size等資訊</code>

12、檢視資料庫中所有使用者的預設表空間和臨時表空間

<code>selectusername,default_tablespace,temporary_tablespace from dba_users</code>

13、查詢目前登入使用者的預設表空間和臨時表空間

<code>selectusername,default_tablespace,temporary_tablespace from user_users;</code>

<code>select * fromuser_users; 可以檢視目前登入使用者的一些資訊</code>

14、查詢目前登入使用者下的所有表

<code>select * from user_tables;</code>

15、查詢目前登入使用者下表的數量

<code>select count(*) fromuser_tables;</code>

16、查詢RAC叢集的公網資訊，随便一台就能看

<code>select utl_inaddr.get_host_address(host_name),host_name from gv$instance;</code>

17、查詢資料庫參數資訊

<code>show PARAMETERS name;</code>

18、查詢資料庫下各使用者的表的數量

<code>select count (*) , owner from dba_tables group by owner order by owner;</code>

19、查詢RAC叢集各執行個體的連接配接數

<code>select inst_id,count(*) from gv$session group by inst_id;</code>

20、查詢資料庫最大連接配接數

<code>select value from v$parameter where name =</code><code>'processes'</code><code>;</code>

21、查詢AIX系統配置資訊

<code>1、prtconf</code>

<code>2、bootinfo -r</code>

<code>3、vmstat 檢視CPU核心數量（虛拟CPU個數）和記憶體</code>

<code>注意：登入AIX系統之後，注意TERM變量的設定，要設定成vt100</code>

六：授權指令

1、系統授權，授權系統權限

<code>grant connect,resource toxiaoming 【一般為建立使用者初始化授權使用】</code>

<code>Resource：可以讓普通使用者在任何一個表空間建表</code>

2、使用者授權，授權資料對象權限

<code>grant select on emp toxiaoming [with grant option];</code>

<code>【隻授權查詢權限給xiaoming使用者,[]内參數為該權限可以被傳遞再次進行權限】</code>

<code>grant all on emp toxiaoming;【授權所有權限給xiaoming使用者】</code>

3、将使用者所有表的查詢權限授權給另一個使用者【不使用dba權限使用者】

<code>登入cklp使用者，将所有表的權限配置設定給query使用者 </code>

<code>select </code><code>'GRANT SELECT ON '</code><code>||table_name||</code><code>' to query;'</code> <code>from user_tables; </code>

<code>将執行結果複制出來，寫成一個sql檔案然後再執行，裡面都是授權語句。</code>

七：權限回收指令

1、使用者授權回收：

<code>revoke select on emp fromxiaoming;</code>

<code>【如果xiaoming使用者把相應的權限做了再次授權給其他使用者，那麼，其他使用者的權限也會被回收，即徹底回收】</code>

八：排序指令

1、查詢結果進行多次排序并且升降序不同

<code>按照部門号升序而雇員的工資降序排列</code>

<code>sql>select * from emp orderby deptno [asc],sal desc; 預設asc</code>

<code>注意：有多個排序同時要求的情況使用,進行隔開注意order by隻有一個後面按順序寫上字段和規則</code>

2、使用列的别名進行排序

<code>select ename,sal*12 </code><code>"年薪"</code> <code>from emp order by</code><code>"年薪"</code><code>asc; 【注意：别名需要使用"】</code>

九：分組指令

在使用groupby的時候前面的select中必須包含它不能使用*代替所有

select 清單項中不存在的列可以出現在group by的清單項中，但在select清單項中出現的列必須全部出現在group by後面(聚合函數除外)。

是以在使用groupby的時候要注意後面接的一定要是前面出現的字段

SQL> select ename,deptno,sal from emp group by deptno,ename,sal;

【注意】在結合使用的時候order by是寫在groupby的後面的

SQL> select ename,deptno,sal from emp group by deptno,ename,sal order by sal desc;

1、對分組顯示的結果進行限制顯示

<code>select deptno,avg(sal),max(sal) from emp group by deptno havingavg(sal)<2000;</code>

10：like操作符

顯示首字元為S的員工姓名和工資

SQL> select ename,sal from emp where ename like 'S%'; 注意要有''單引号

11：設定主鍵

1、在建立表的時候就進行建立

<code>create table categoryinfo(categoryId varchar2(10),categoryName varchar2(30),primarykey(categoryId));</code>

2、建立表之後再進行修改

<code>alter table cott.categoryinfo add constraints pk_category primary key(categoryId);</code>

3、删除主鍵

<code>alter table scott.categoryinfo drop constraint pk_category;</code>

12：設定外鍵

1、在建立表的時候進行外鍵的建立：

<code>create table productinfo(productidvarchar2(10),productname varchar(20),</code>

<code>category varchar2(10),constraint fk_produce foreign key(category) </code>

<code>references categoryinfo(categoryID) on </code><code>delete</code> <code>cascade);</code>

2、建立表之後再進行修改：

<code>alter table productinfo add constraint fk_produce foregin key(category) </code>

<code>references categoryinfo(categoryId) on </code><code>delete</code> <code>cascade);</code>

3、删除外鍵：

<code>alter table productifo drop constraint fk_product;</code>

13：自連接配接

自連接配接是指在同一張表上的連接配接查詢

<code>select worker.ename,boss.ename from emp worker,emp boss </code>

<code>where worker.mgr=boss.empno;</code>

14：其他常用指令

1、解鎖使用者

<code>alter user scott account unlock;</code>

2、用查詢結果來建立新表【常用于對表做備份操作】

<code>create table emp_bak as select * from emp;</code>

結尾：

感謝閱讀，祝有收獲的一天！

本文轉自1清風攬月1 51CTO部落格，原文連結：http://blog.51cto.com/watchmen/1926416

，如需轉載請自行聯系原作者

ORACLE-常用基礎指令總結

繼續閱讀

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List