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

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