大學(xué)課程《Oracle 數(shù)據(jù)庫》操作總結(jié)

\h大學(xué)課程《Oracle目錄登錄操作 2常用工具 2SQLPlus常用命令 2常用數(shù)據(jù)類型 3SQL分類 3數(shù)據(jù)操作（DML） 3查詢語句 3插入語句 6修改記錄 7刪除記錄 7ROLLBACK（回滾） 7左連接與右連接 7標量函數(shù)的使用 9對象管理（DDL） 128.1. 表 128.2. 視圖 138.3. 序列 148.4. 表空間 159. 約束 15用戶管理 16權(quán)限管理 17角色管理 18表空間與段 18表空間 1813.2. 段 19登錄操作Oraesssot的密碼是tiger。scott用戶登錄，則要做以下工作。yr（鎖命令：ALTERUSERscottACCOUNTUNLOCK。sysplusscott/tiger@myorcl。SQLPlus工具登錄命令：系統(tǒng)管理員登錄，DOS中執(zhí)行命令：sqlplususername/password@serviceNameassysdba普通用戶登錄，DOS中執(zhí)行命令：sqlplususername/password@serviceName常用工具SQLPlus系統(tǒng)管理員登錄，DOS中執(zhí)行命令：sqlplususername/password@serviceNameassysdba普通用戶登錄，DOS中執(zhí)行命令：sqlplususername/password@serviceNameSQLPLusW可視化窗口DOS中執(zhí)行命令：sqlpluswiSQLPlus(網(wǎng)頁形式):EnterpriseManager(網(wǎng)頁形式):Manager(網(wǎng)頁形式)時需要網(wǎng)址端口號，如果忘記，可在此文件中查：ORACL_HOME/product/10.2.0/db_1/install/portlist.ini。SQLPlus常用命令SQLPlus中的常用命令：select*fromtab;列出當前用戶下的所有表。showuser;察看當前連接的用戶scott/passwordscott的用戶名/密碼連接數(shù)據(jù)庫;tableName表結(jié)構(gòu)quit|exit;退出disconnect;斷開連接;cls命令@pathpath指定的腳本文件常用數(shù)據(jù)類型:varchar2變長字符串char定長字符串Integer整型number(m,n)數(shù)字型smallint短整型float浮點數(shù)decimal十進制數(shù)字date日期型SQL分類DML(DataManipulationLanguage,數(shù)據(jù)操作語言)用于檢索或修改數(shù)據(jù)DDL(DataDefinitionLanguage，數(shù)據(jù)定義語言)DCL(DataControlLanguage，數(shù)據(jù)控制語言)用于定義數(shù)據(jù)庫用戶的權(quán)限（DML）查詢語句基本數(shù)據(jù)查詢select內(nèi)容from表名；selectdistinctjobfromemp；去除重復(fù)行限定查詢（查詢滿足條件的行）SELECT*|FROMWHERE條件;SELECTempno,ename,commFROMempWHEREcommISNOTNULL;限定查詢 ISNULL和ISNOTNULL的使用SELECTempno,ename,commFROMempWHEREcommISNULL查詢誰沒有獎金限定查詢----AND的使用1500，并且可以領(lǐng)取獎金的雇員EMPWHEREsal>1500ANDcommISNOTNULL;限定查詢----OR的使用1500和可以領(lǐng)取獎金的雇員SELECTempno,ename,sal,commFROMEMPWHEREsal>1500ORcommISNOTNULL;限定查詢 使用NOT對條件整體取反1500并且不能領(lǐng)取資金的雇員SELECTempno,ename,sal,commFROMempWHERENOT(sal>1500ANDcommISNOTNULL);詢----BETWEEN...AND 用15003000的雇員SELECTempno,ename,sal,commFROMempWHEREsalBETWEEN1500AND3000限定查詢----IN的使用7369，7499，7521的雇員的具體信息SELECT*FROMempWHEREempnoIN(7369,7499,7521);限定查詢----NOTIN的使用7369，7499，7521的雇員的具體信息SELECT*FROMempWHEREempnoNOTIN(7369,7499,7521);限定查詢----LIKE的使用M的雇員信息SELECTempno,ename,comm,salFROMempWHEREenameLIKE'_M%';說明：_匹配一個字符，%0個或多個字符限定查詢----LIKE匹配日期1982年入職的所有雇員的信息SELECT*FROMempWHEREhiredateLIKE'%82%'限定查詢----LIKE匹配數(shù)字查詢工資中包含5的雇員信息SELECT*FROMempWHEREsalLIKE'%5%'對結(jié)果排序 ORDERBYORDERBYSQL語句基本格式SELECT列...FROMWHERE條件ORDERBY列1500的信息，按工資排序SELECT*FROMempWHEREsal>1500ORDERBYsal1500員工的信息，按工資降序,按雇傭日期升序排序WHEREsal>1500ORDERBYsalDESC,hiredateASCASC排序，DESCASC左、右外連接查詢員工編號，姓名，所在部門號，部門名稱，將沒有員工的部門也顯示出來empe,deptdWHEREe.deptno(+)=d.deptno;（+）交叉連接（OSJONSELECT*FROMempCROSSJOINdept;自然連接NUALJONUSING子句：直接指定操作關(guān)聯(lián)列SELECT*FROMempJOINdeptUSING(deptno);ON子句：用戶自己編寫連接條件SELECT*FROMempJOINdeptONemp.deptno=dept.deptno;RIGHTJOIN：右外連接SELECTe.empno,e.ename,d.deptno,d.dnameFROMempeRIGHTJOINdeptdONe.deptno=d.deptno;子查詢---IN的使用SMITHJONES在同一部門，同一職位工作的員工SELECT*FROMempWHERE(deptno,job)IN(SELECTdeptno,jobFROMempWHEREenameIN('SMITH','JONES'));子查詢---ANY的使用=ANYIN操作符的效果一致JONES在同一部門，同一職位工作的員工SELECT*FROMempWHERE(deptno,job)=ANY(SELECTdeptno,jopFROMempWHEREenameIN('SMITH','JONES'));>ANY：只要大于子查詢中的任何一個值即可SELECT*FROMempWHEREsal>ANY(SELECTMIN(sal)FROMEMPGROUPBYdeptno);>ALL：比最大的值大SELECT*FROMempWHEREsal>ALL(SELECTMIN(sal)FROMempGROUPBYdeptno);<ANY：只要小于子查詢中的任何一個值即可*FROMempWHEREsal<ANY(SELECTMIN(sal)FROMempGROUPBYdeptno);<ALL：比最小的值小SELECT*FROMempWHEREsal<ALL(SELECTMIN(sal)FROMempGROUPBYdeptno);插入語句into表名values(123…..)插入的數(shù)值的字段類型要與定義的時候一樣values（12，null3………）null來標示into表名(1.23………)values(123……)類型對應(yīng)的語法);MYEMP中增加一條記錄張三','清潔工','7369','14-2-1995',9000,300,40)注：給定的值要和指定的字段數(shù)一致當為表中的所有列都插入數(shù)據(jù)數(shù)，可以采用如下簡單寫法);清潔工','7369','14-2月-1995',9000,300,40)注：要求給定值的數(shù)目和表中列的數(shù)目一致，并且值的順序和表中列的順序一致。MYEMP中，但是該員工沒有獎金也沒有上級領(lǐng)導(dǎo)第一種寫法：VALUES(7899,'張三','清潔工','14-2-1995',9000,40)第二種寫法：(null來指定沒有值的列)',null,'14-2月-1995',9000,null,40)MYEMP中，但日期格式按'1988-09-09'的方式指定?TO_DATE函數(shù)的使用清潔工',TO_DATE('1984-09-09','yyyy-mm-dd'),9000,40);修改記錄update表名set字段=值，字段==值…….where條件;注意：條件必須寫刪除記錄deletefromwhere條件；注意：條件必須寫ROLLBACK（回滾）在sqlplus中如果使用ROLLBACK的話那么將撤銷操作直到上次執(zhí)行commit的時候的狀態(tài)左連接與右連接數(shù)據(jù)表的連接有:1、內(nèi)連接、自然連接:只有兩個表相匹配的行才能在結(jié)果集中出現(xiàn)列出被連接表中的所有列，包括其中的重復(fù)列。的列值。這些運算符包括>、>=、<=、<、!>、!<和<>。(Naturaljoin)是一種特殊的等值連接，它要求兩個關(guān)系中進行重復(fù)的屬性列。等值連接與自然連接的區(qū)別：等值連接中不要求相等屬性值的屬性名相同，而自然連接要求相等屬性值的屬性名必須相同，即兩關(guān)系只有在同名屬性才能進行自然連接。等值連接不將重復(fù)屬性去掉，而自然連接去掉重復(fù)屬性，也可以說，自然連接是去掉重復(fù)列的等值連接。2、外連接:(左邊的表不加限制)右外連接(右邊的表不加限制)(左右兩表都不加限制)3、自連接(連接發(fā)生在一張表內(nèi))SQL標準連接語法：selecttable1.column,table2.columnfromtable1[inner|left|right|full]jointable2ontable1.column1=table2.column2;例如：selecta.*,b.*fromaleftjoinbona.編號=b.編號INNERJOIN表示內(nèi)連接；LEFTJOIN或LEFTOUTERJOIN表示左外連接；RIGHTJOINRIGHTOUTERJOIN表示右外連接；FULLJOIN或FULLOUTERJOIN表示完全外連接；不管匹配ON子句用于指定連接條件。(naturaljoin)關(guān)鍵字自動匹配內(nèi)連接操作。使用“(+)”符號的左右連接：右連接：selecta.studentno,a.studentname,b.classnamefromstudentsa,classesbwherea.classid(+)=b.classid;左連接：selecta.studentno,a.studentname,b.classnamefromstudentsa,classesbwherea.classid=b.classid(+);"(+)"寫在主表的另一側(cè)，主表的所有記錄均會被顯示。"(+)"在左側(cè)是否得到匹配。左連接相反。注意：fromon子句指定連接條件；如果使用（+）where子句指定連接條件。標量函數(shù)的使用轉(zhuǎn)化大小寫selectupper(‘sddfa’)fromdual selectlower(‘sddfa’)fromdual將首字母大寫其余小寫selectinitcap（ename）fromemp；連接字符串：selectcount（deptno）fromdept；selectcontact(‘hello’,’woeld’)fromdual;select‘a(chǎn)bc’||‘jkh’||‘sdf’fromdual;求子串：selectsubstr(字符串，起始位，結(jié)束位)fromdual; 起始位寫0或者1都可以代表第一位selectsubstr(字符串，起始位)fromdual; 從起始位開始往后截取求字符串長度：selectlength（ename）fromemp;字符串替換selectreplace（）fromdual；輸出字符串的后三位select ename，substr（ename，length（ename）-2）fromdual；select enamesubstr（ename，-3）fromdual；四舍五入selectround（132.78454）fromdual；四舍五入取整selectround（132.78345，2）fromdual；四舍五入并且指定保留小數(shù)點的位數(shù)截斷小數(shù)位selecttrunc（546541.151）fromdual 不管四舍五入直接截斷小數(shù)5465541selecttrunc（789.536，2）fromdual 截斷小數(shù)指定小數(shù)的數(shù)位789.53selecttrunc（789.536，-2）fromdual 截斷小數(shù)并且對整數(shù)進行截斷700取余selectmod（10，3）fromdual； 取余數(shù)1運算規(guī)律：日期+數(shù)字=日期日期--數(shù)字=日期日期—=數(shù)字顯示系統(tǒng)時間selectsysdatefromdual；selectto_char(sysdate,'yyyy-mm-dd,hh24:mi:ss')fromdual;顯示某本門員工進入公司星期數(shù)：sctepn，eae，run（ssa-rea）7）romephreetn=10；在指定的日期加上指定的月數(shù)后的日期：selectadd_months(sysdate,2)fromdual;求出給定日期范圍的月數(shù)：selectempnoenamemonth_between(sysdate,hiredate)fromemp;下一個給定的星期是那個日期：selectnext_day(sysdate,’星期一fromdual;求出給定日期所在的月份的最后一天的日期selectlast_day(sysdate)fromdual;轉(zhuǎn)換函數(shù)查詢雇員號，姓名，以及入職的年份emp;說明：yyyy匹配年份，mm匹配月份，dd匹配日設(shè)置日期的顯示格式SELECTempno,ename,TO_CHAR(hiredate,'yyyy-mm-dd')FROMemp;fm去掉前導(dǎo)0empno,ename,TO_CHAR(hiredate,'fmyyyy-mm-dd')FROMemp;設(shè)置數(shù)值的格式SELECTempno,ename,TO_CHAR(sal,'99,999')FROMemp;說明：9代表1位數(shù)字顯示金額SELECTempno,ename,TO_CHAR(sal,'$99,999')FROMemp;說明：$代表美元，L代表本地SELECTTO_NUMBER('123')+TO_NUMBER('123')FROMdual;SELECTTO_DATE('2009-07-31','yyyy-mm-dd')FROMdual;通用函數(shù)查詢每個員工的年收入SELECTempno,ename,(sal+NVL(comm,0))*12incomeFROMemp;commnull時，用0替換常用的列函數(shù)SUM(expression)求和MAX(expression)求最大值MIN(expression)求最小值COUNT(expression)統(tǒng)計記錄數(shù)COUNT(DISTINCTCOLNAME)統(tǒng)計去除重復(fù)行記錄數(shù)SQL語句的執(zhí)行順序FROMWHEREBYHAVINGSELECTORDERBY（DDL）表創(chuàng)建表createtable表名（字段1數(shù)值類型defultxx，字段2數(shù)值類型， ）;createtableCUSTOMER(CUSTOMER_ID int notnull,CUSTOMER_NO CUSTOMER_NAME varchar(200),TYPE INT, --客戶類型SOURCE_TYPE_IDint,--來源INDUSTRY_TYPE_IDint,--行業(yè)LEVEL_TYPE_IDint,--等級REGION_TYPE_IDint,--區(qū)域CREDIT_TYPE_IDint,--信用primarykey(CUSTOMER_ID));defult是默認值可寫可不寫createtable表名2 as表名1 復(fù)制表2到表1中createtable表名as（子查詢）會復(fù)制表結(jié)構(gòu)（數(shù)據(jù)類型）（有時目的只是復(fù)制表結(jié)構(gòu)那么就使用這種select1=2from表名）刪除表droptable表名;注意：delete是刪除表里面的數(shù)據(jù)修改表結(jié)構(gòu)增加一列：altertable表名add（列名稱列數(shù)據(jù)類型defult默認值）注意：defult和默認值可寫可不寫刪除列：altertable表名dropcolumn列名稱注意：不管有沒有數(shù)據(jù)都會刪除修改列的數(shù)據(jù)類型：altertable表名modify（列名稱數(shù)據(jù)類型defult默認值）注意：修改的時候（和原來相同數(shù)據(jù)類型）表名重命名rae表名（舊）o表名（新；截斷表truncate表名；注意：清空表中的所有數(shù)據(jù)立即釋放資源，該操作不可回滾ROWNUM表示行號，是一個偽列，可以在每一張表中出現(xiàn)應(yīng)用：查詢表中記錄的前五行SELECT*FROMEMPWHEREROWNUMBETWEEN<=5;6到10條記錄，使用子查詢SELECT*FROM(SELECTROWNUMnum,empno,ename,salFROMemp)eeWHERErownumBETWEEN6AND10;視圖創(chuàng)建視圖語法：CREATEVIEWAS子查詢EMPNO,ENAME,SAL,DEPTNOCREATEVIEWempv20ASSELECTempno,ename,sal,deptnoFROMemp;注:當創(chuàng)建視圖以后，可以像操作表一樣的操作視圖注：當創(chuàng)建視圖時，視圖的操作會影響到對表的操作，這是不安全的，我們可以采用如下選項來創(chuàng)建視圖?WITHCHECKOPTION不能更新創(chuàng)建視圖的條件?WITHREADONLY創(chuàng)建只讀視圖刪除視圖語法：DROPVIEW視圖名稱刪除視圖empv20;注:當刪除視圖所在的表時，則視圖也不能再被使用序列創(chuàng)建序列語法：CREATESEQUENCE序列名稱[MAXVALUEnum|NOMAXVALUE][MINVALUEnum|NOMINVALUE]10][CACHEnum|NOCACHE];[CYCLE|NOCYCLE]序列的操作nextVal：取得序列的下一個內(nèi)容currVal：取得當前序列的內(nèi)容select 序列名稱.nextvalfromdual.創(chuàng)建表驗證序列的操作nextNUMBER;CURRNUMBER;)nextVal1，而currVal都是取當前值創(chuàng)建序列指定每次增長的增量myseqINCREMENTBY2;1開始。10;創(chuàng)建一個循環(huán)序列1，3，5，7，9。表空間創(chuàng)建表空間CREATETABLESPACEDATAFILE數(shù)據(jù)文件的全路徑SIZE表空間的大小;刪除表空間注：在進行相應(yīng)操作時，應(yīng)該具有相應(yīng)的操作權(quán)限約束constraintCONSTRAINTpersion_pid_pkPRIMARYKEY(pid);非空約束：唯一約束：VARCHAR2(30)UNIQUENOTNULL,在創(chuàng)建表時聲明CONSTRAINTperson_name_ukUNIQUE(pid)在表的結(jié)尾聲明（c）ageNUMBER(3)NOTNULLCHECK(agebetween1and150),（）pidVARCHAR2(18),ageNUMBER(3)NOTNULL,PRIMARYKEY(pid),CONSTRAINTperson_name_ukUNIQUE(name),CONSTRAINTperson_age_ckCHECK(ageBETWEEN1AND150),CONSTRAINTperson_sex_ckCHECK(sexIN('男','女','中')));bidNUMBERPRIMARYKEYNOTNULL,bnameVARCHAR(30),NUMBER(5,2),CONSTRAINTperson_book_pid_fkFOREIGNKEY(pid)REFERENCESperson(pid));增加約束ALTERTABLEADDCONSTRAINT（約束字段）book表KEY(pid)REFERENCESperson(pid)ONDELETECASCADE;約束的命名規(guī)范（建議）UNIQUE：表名稱_字段名稱_ukCHECK：表名稱_字段名稱_ck刪除約束用戶管理創(chuàng)建用戶CREATEUSERIDENTIFIEDBYACCOUNTUNLOCK;為用戶授予連接的權(quán)限GRANTCONNECTTO用戶名;SCOTTemp表的權(quán)限GRANTSELECTONSCOTT.empTO用戶名;刪除用戶DROPUSER用戶名;修改用戶的默認表空間修改用戶的密碼ALTERUSERIDENTIFIEDBY新密碼;鎖定用戶ALTERUSERACCOUNTLOCK;解鎖用戶ALTERUSERACCOUNTUNLOCK;權(quán)限管理級聯(lián)授權(quán)（對象權(quán)限，WITHGRANTOPTION）GRANTTOWITHGRANTOPTION;OPTION;為用戶授權(quán)操作表空間的權(quán)限給用戶授予創(chuàng)建用戶的操作USERTO用戶名;給用戶授予刪除用戶的操作GRANTDROPUSERTO用戶名;級聯(lián)授權(quán)（系統(tǒng)權(quán)限，WITHADMINOPTION）GRANTTOWITHADMINOPTION;GRANTCREATEU