天天看點
傳回

oracle資料庫基礎知識彙總—30天（一）

oracle資料庫基礎知識彙總—30天（一）

解鎖scott：

打開SQLPLUS（是oracle的自帶的工具軟體，主要用于執行SQL語句，pl\sql塊），也可以在“運作”裡面輸入“sqlpus或sqlplusw"   輸入：使用者名（如system),密碼

輸入 alter user scott account unlock

PL/SQL developer屬于第三方軟體，主要用于開發，測試，優化oracle pl/sql的存儲過程。這款軟體用的很多，需要單獨安裝。

企業管理器

  1.  oracleDBConsoleorcl(一般不啟動)服務是啟動狀态才可以啟動企業管理器
  2. 在浏覽器中輸入：http：//ip：1158/em

oracle 常用的sql plus指令

  • show user 可以用來顯示目前的使用者名
  • conn 使用者名/密碼 切換使用者，可用于連接配接
  • disc  斷開和oracle資料庫的連接配接，但是不退出sql*plus視窗，exit是斷開與oracle的連接配接，同時退出視窗
  • passw/password 用于修改使用者的密碼。如果給自己修改密碼則不需要使用者名，給别人修改密碼則需要帶使用者名
  • & 互動指令 select * from emp where job=‘&job'；可以自己輸入值代替&進行查詢
  • set linesize 140；設定每行顯示多少個，預設是80個字元；set pagesize  30；設定一而顯示多少條資料。
  • edit 用于編輯腳本。如：edit D:/a.sql ; 
  • spool 把螢幕上顯示的記錄，儲存到檔案中 。首選 spool D:/a.sql建立一個檔案，然後點select * from emp；将查詢資料寫入，最後關閉 spool off；資料就寫入了。

配置設定權限

  • 建立使用者：create user 使用者名 identified by 密碼 。如：create user xm identified by m123 【隻有system/sys有權限建立使用者，普通使用者無法建立】
      • 表空間：表存在的空間，是一個邏輯的概念。　sql> create user demo identify by m123     default tablespace users  temporary tablespace temp quota 3m on users；
      • identified by 表明該使用者demo将用資料庫方式驗證，default tablespace users //使用者的表空間在users上。　temporary tablespace temp //使用者demo的臨時表健在temp空間。 quota 3m on users//表明使用者demo建立的資料對象（表、索引、視圖，pl/SQL塊 ...）最大隻能是3M
      • sql> grant/revoke  [connect|resource|DBA|create session]  to demo 授權或移除授權。create session 是權限，其它的是角色。
      • connect和resource是兩個系統内置的角色，和dba是并列的關系。DBA：擁有所有特權，是系統最高權限，僅僅有DBA才幹夠建立資料庫結構。RESOURCE:擁有Resource權限的使用者僅僅能夠建立實體，不能夠建立資料庫結構。CONNECT：擁有Connect權限的使用者僅僅能夠登入Oracle，不能夠建立實體，不能夠建立資料庫結構。
  • 修改密碼：password 使用者名，具有dba的權限或者擁有alter user的系統權限；alter user 使用者名 identified by 新密碼
  • 删除使用者：drop user 使用者名，當我們删除一個使用者的時候，如果這個使用者自己建立有資料對象時，那麼我們在删除該使用者的時候需要加選項 cascade ，表示把資料對象一起删除。

方案（schema)

了解：當一個使用者建立好後，如果該使用者建立了任意一個資料對象，這時我們的dbms就會建立一個對應的方案與該使用者對應，并且該方案的名字和使用者一緻。

賦權：grant  [select |delete|insert|update|all] on 表名 to 使用者名   with grant option； with grant option 表示得到權限的使用者可以把權限繼續配置設定。如：grant all on scott.emp to stu;如果是管理者權限則帶 with admin option

查詢：select * from   scott.emp 

收回權限：revoke [select |delete|insert|update|all] on 表名 from 使用者名 

oracle使用者管理

profile管理使用者密碼：profile是密碼限制，資源限制的指令集合，當建立資料時，oracle會自動建立名稱為default的profile，當建立使用者沒有指定profile選項，那oracle就會将default配置設定給使用者。

使用profile檔案對密碼進行管理，

建立： create profile 檔案名 limit failed_login_attempts 3  password_lock_time 2   ，

使用：  alter 使用者名 profile 檔案名；解鎖：alter 使用者名  account unlock;

終止密碼：create profile 檔案名 limit password_life_time 10 password_grace_time 2； 10天修改密碼，密碼的寬限是2天

删除：drop profile 檔案名 【cascade】

oracle資料庫的啟動流程

 linux裡的ssh遠端登入工具，systeminfo可以顯示系統的基本資訊

windows作業系統：

lsnrctl start（啟動監聽）

oradmin -startup -sid orclhsp （啟動執行個體）

unix作業系統/linux系統： 和windows相差不大

oracle登入認證方式

conn ***/***  as sysdba  //能夠連接配接成功，隻要as sysdba 後系統不會去驗證使用者名和密碼就自動登入。

  1. 作業系統認證
    • 如果目前使用者屬于本地作業系統的ora_dba組（對于windows作業系統而言），即可通過作業系統認證。
  2. oracle資料庫驗證（密碼檔案驗證）
    • 對于普通使用者，Oracle預設使用資料庫驗證
    • 對于特權使用者（比如sys使用者），oracle預設使用作業系統認證，如果驗證不通過，再到資料庫驗證（密碼檔案驗證）。通過配置sqlnet.ora檔案，可以修改Oracle登入
      • SQLNET.AUTHENTICATION_SERVICES=(NTS)是基本作業系統驗證
      • SQLNET.AUTHENTICATION_SERVICES=(NONE)是使用資料庫驗證
      • SQLNET.AUTHENTICATION_SERVICES=(NTS,NONE)是兩者共存
  3. 管理者密碼丢失
    • 恢複辦法：把原有密碼檔案删除，生成一個新的密碼檔案
    • 步驟如下：
      • 搜尋名為PWD資料庫執行個體名.ora檔案，如（PWDORACLE.ORA)
      • 删除該檔案，為預防萬一，建議大家備份
      • 生成新的密碼檔案，在dos控制台輸入指令：orapwd file=原來密碼檔案的全路徑\密碼檔案名.ora password=新密碼 entries=10；//entries：允許幾個特權使用者 ，密碼檔案名一定要和原來的密碼檔案名一樣。如:orapwd file=D：\app\admin\product\11.2.0\dbhome_1\database\PRDorcl.ora password=123 entries=10
      • 重新啟動資料庫執行個體

 資料庫基本概念

1.資料類型

 select *,dump(name) from table ; dump檢視字段的詳細資訊（編碼，長度等）

char（size），size不能超過2000，是定長。如果是英文字母，能存放size個字元，如果是中文，占用兩個字元空間。

varchar2（size），varchar2的性能更優，是變長，最長可存放4000個字元，字母占一個位元組，中文占兩個位元組。

nchar（size），是unicode資料類型，定長，最大2000字元。不管是英文還是中文，能存放size個字元。unicode的好處：為了滿足各國不同大小容量的文字。

nvarchar2（size），unicode資料類型，變長，最大4000個字

clob（character large object），字元型大對象，最大8tb

blob（binary large object），二進制資料，可以存放圖檔/聲音8tb

number（p，s），數值類型，變長，p為整數，s為小數位 ；範圍：1<=p<=38,-84<=s<=127,儲存資料泛微-1.0e-130<=number1.0e+126，p為有效位，從左往右數每一個非0的數就是每一個有效位，如number（5，2） 範圍-999.99-999.99；number 什麼都不寫，保持原數；number（6，-2），存123.55——存入100，167.98——存入200；

date，日期型，預設的日期格式是美國格式（“DD-MM-YY"),如果要用習慣的格式的話要用oracle函數，timestamp（n）遊戳類型，當資料更新時，日期自動更新；日期類型必須加單引号。to_date(string,'format');

2.基本文法

 修改表名：rename 舊表名 to 新表名

檢視表結構：desc 表名

修改列名：alter table 表名  modify （列名 列類型）； 當寫where語句為空時 where name is null

删除列名：alter table 表名 drop（列名1，列名2）；

增加列名：alter table 表名 add（列名 列類型）；

删除行後找回：delect * from mytable ；找回資料 先 savepoint a；然後rollback to a ;   truncate table 表名 删除以後無法找回。

查詢：select [distinct] * from mytable；distinct去重複資料。sql語句（如表名、字段名）不區分大小寫，内容是區分大小寫的。

soundex：傳回一個與給定的字元串讀間相同的字元串。select * from table1 where soundex（name）=soundex（‘weather’）；

ABS：傳回指定值的絕對值。select abs（price），abs（-100） from dual;

ACOS：給出反餘弦的值。ATANT：傳回一個數字的反正切值。COS：傳回一個給定資料的餘弦。select cos（-3.1415926） from dual ；———— -1

CEIL：傳回大于或等于給出數字的最小整數。 select ceil（3.14） from dual；FLOOR：對給定的資料取整數；LN：傳回一個資料的對數；

3.基本運算

 nvl（字段名，0）：用于處理資料為null時的問題，如果字段為空時不取0，如果不為空取本身值。

||   連接配接字元串。 select name ||”年齡是“|| age from table1;  // 張三年齡是25

alter user scott account lock；使用者鎖定以後，隻是不能登入，但是表還是可以通路

to_char：字元格式的轉換；//如：where to_char(birthday,'yyyy-mm-dd')>'1995-1-1';  4月出生的：to_char(birthdat,'mm')='4'

like：%表示0到多個字元，_ 表示一個字元；

order by：是對查詢結果進行排序。預設是升序asc。支援别樣查詢。order by後面是可以加表達式的。

資料分組：max、min、avg、sum、count，當傳回一個值時可以放 在一起查詢，當傳回的結果條數不一樣時一起查詢會出錯；avg（SAL)，當SAL為null時不會統計。//統計帶空的時 select sum（SAL)/count（*） from table1; count統計時也會排除null的項。

 SQL語句的執行順序：1、預設情況下是從右向左執行；

group by 和 having：group by 用于對查詢的結果分組統計，group by 字段1，字段2  ；having子句用于過濾分組顯示的結果，即相當于group by的查底結果where 的作用。

自查詢：select * from table1 t1,table1 t2 where t1.name1=t2.grad1（+）;一定要取别名否則會出錯。（+）是外聯的附号，（+）寫在左邊是右外聯，寫在右邊是左外聯。

4.子查詢

單行子查詢：子查詢隻傳回一條記錄。

 多行子查詢：子查詢傳回多條記錄。any指結果中的任一一條資料都滿足條件。

多列子查詢：查詢查詢結果滿足兩列條件  //如查詢與smith的部門和崗位完全相同的所有雇員 ：select * from emp where （detpno，job）=select （depno，job）from emp； 

注：oracle支援數字排序，但是不能數字不能超過列數；子查詢的時候必須指定别名；where上面取個别名，前面的語句将無法識别；desc顯示表結構。

//顯示部門最高工資的人的詳細資訊

oracle資料庫基礎知識彙總—30天（一）

 分頁查詢：

mysql分頁：select * from 表名 where limit  從第幾條取，條數

sql server分頁：select top 10 * from (select rownum，tb1.* from tb1 where rownum>=10 );

oracle分頁：三層過慮 select t2.* from（select t1.* ，rownum rn  from (select * from emp) t1 where rownum<=6) t2  where t2.rn>=4;

oracle資料庫基礎知識彙總—30天（一）

顯示上級的個數（distinct mgr）去掉重複的了再統計；count（列名）統計一列；

oracle資料庫基礎知識彙總—30天（一）

 模拟10W條資料

  1. 建立一張表：create table mytest as select empno,ename,sal,comm,deptno,job from emp;
  2. 自我複制：insert into mytest (empno,ename,sal,comm,deptno,job) select empno,ename,sal,comm,deptno,job from mytest;

 5.合并查詢

union：取得兩個結果集的并集。會自動去掉結果集中的重複行。

union all：和union相似，但是不會去掉重複行，也不會排序。

intersect：取交集

minus：取差集，A1集合-A2集合。cube立方體 select avg（sal），deptno，job from emp group by cube（empno，job）先對empno分組、再組job分組，再對empno和job分組

6.内連接配接和外連接配接

内連接配接：就是利用where子句對兩張表的迪卡爾集進行篩選。 select ... tb1 inner join tb2 where tb1.id=tb2.id；（隻有兩張表兩邊都比對的時候才被選擇）

外連接配接：左外連，右外連，完全外連 。

left join...on（左側的表完全顯示）；另外一種寫法select * from stu ，emam where stu.id=exam.id（+）；

right join...on （右邊的表完全顯示）；另外一種寫法select * from stu ，emam where stu.id（+）=exam.id；

full join...on （左邊的表和右邊的表都顯示）；

 7.小結

  1. 分組函數(avg...）隻能出現在選擇清單、having、order by子句中
  2. 如果在select語句中同時包含group by，having，order by那麼他們順序是group by，having，order by
  3. 在選擇列中如果有列、表達式和分組函數，那麼這些列和表達式必須有一個出現在group by子句中，否則會出錯。

posted on 2019-09-17 15:48  麥田裡的包米 閱讀( ...) 評論( ...) 編輯 收藏

轉載于:https://www.cnblogs.com/guanguan-/p/11451141.html

資料庫 作業系統 運維
上一篇: ORACLE資料庫安全基礎知識
下一篇: Oracle- 資料庫知識回顧

繼續閱讀