2021年Oracle實(shí)驗(yàn)總結(jié)

1、oracle實(shí)驗(yàn)總結(jié)oracle實(shí)驗(yàn)總結(jié)近日做了基于oracle的數(shù)據(jù)庫(kù)安全、對(duì)象sql數(shù)據(jù)庫(kù)，xml數(shù)據(jù)庫(kù)的實(shí)驗(yàn)，其中遇到很多問題，先總結(jié)如下。1、安裝oracle的過程中可以選擇建立數(shù)據(jù)庫(kù)，也可以選擇只安裝軟件，后者可在安裝好軟件后，建立數(shù)據(jù)庫(kù)，可參考網(wǎng)上的很多安裝步驟。2、oracle的默認(rèn)用戶有sys和system,在命令行或者sql*plus中登陸時(shí)，寫法如下connectsys/123456assysdba;其中123456是密碼，在創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)候設(shè)置的。普通用戶登錄，connectzhangsan/123456;3、grantselect不支持對(duì)表中選定的幾列授權(quán)，只能將整個(gè)表

2、的select權(quán)限授權(quán)出去。所以實(shí)驗(yàn)中先建立了基于選課信息表的視圖selectiveinfo1，視圖中包含成績(jī)列，學(xué)號(hào)列以及班級(jí)列（不包含其他的列）,再將對(duì)視圖selectiveinfo1的select權(quán)限授予角色teacher。4、創(chuàng)建用戶的時(shí)候，一定要說明該用戶對(duì)表空間的權(quán)限即（quotaunlimitedontablespace或者具體限定大小的句子），否則該用戶即使被授予了建表權(quán)限，也不能建表。創(chuàng)建用戶的時(shí)候必須給用戶授予createsession的權(quán)限，這是最基本的權(quán)限。5、不同的用戶可以創(chuàng)建相同的表格，例如，teacher用戶創(chuàng)建了table1,student用戶也創(chuàng)建了table

3、1,且這兩個(gè)表格式完全相同的，那么其他用戶需要用這兩個(gè)表格時(shí)，可以用teacher.table1和student.table1加以區(qū)分。系統(tǒng)用戶創(chuàng)建的表，和用戶自己創(chuàng)建的表，本用戶引用是可不加前綴。6、oracle有自帶的密碼驗(yàn)證函數(shù)，默認(rèn)情況下是不啟用的，可以用下面的語(yǔ)句啟用，注意必須以connectsys/123456assysdba的身份登陸(其他dba身份的用戶可不可以，本人沒有試過)才能啟用密碼函數(shù)，啟用方式如下7、將日期裝換為字符串可以to_char()函數(shù)，將字符串轉(zhuǎn)換為日期用to_date()函數(shù)。網(wǎng)上有很多這兩個(gè)函數(shù)的資料。8、xml數(shù)據(jù)庫(kù)使用的簡(jiǎn)單舉例（具體路勁寫法好像不對(duì)

4、，只要將改改/為/并把路徑寫全就可以了，路徑的用法其實(shí)是xpath技術(shù)）建立含xmltype數(shù)據(jù)的表createtableabc(idnumber,xmldocsys.xmltype);向表中插入數(shù)據(jù)insertintoabc(id,xmldoc)values(1,sys.xmltype.createxml(abc);查詢表中的數(shù)據(jù)得到id=1的value變臉的值selecti.xmldoc.extract().getstringval()asennames,idfromabci得到a節(jié)點(diǎn)的值selectid,i.xmldoc.extract(/name/a/text().getstringv

5、al()astruenamefromabci得到節(jié)點(diǎn)id屬性的值selecthd.data_t.extract(/root/name/id).getstringval()asnamefromsehr_house_datahd擴(kuò)展閱讀oracle實(shí)訓(xùn)總結(jié)1、conn/assysdba:以sysdba連接數(shù)據(jù)庫(kù)，在這里可以解鎖system、hr(oracle內(nèi)建的用戶名)、scott(oracle內(nèi)建的用戶名)用戶，并修改它們的密碼。2、修改密碼alteruseridentifiedby3、解鎖用戶alteruseraccountunlock4、解鎖hr、scott，并修改system、hr、sc

6、ott密碼alteruserhraccountunlock;alteruserscottaccountunlock;alteruserhridentifiedbyk;alteruserscottidentifiedbyk;alterusersystemidentifiedbyk;5、連接網(wǎng)絡(luò)數(shù)據(jù)庫(kù)在product10.0.2db_1networkadmintnsname.ora文件中添加內(nèi)容test=(description=(address=(protocol=tcp)(host=)(port=1521)(connect_data=(server=dedicated)(service_nam

7、e=orcl)連接數(shù)據(jù)庫(kù)時(shí)，輸入:conn/test6、創(chuàng)建用戶的語(yǔ)句以創(chuàng)建用戶名hr為例connsystem/assysdba;/連接到指定數(shù)據(jù)庫(kù)dropuserhrcascade;/如果hr用戶名已存在的話刪除該用戶名createuserhridentifiedbyhrdefaulttablespaceuserstemporarytablespacetemp;/創(chuàng)建用戶hrhostimphr/hrfile=hr.dmplog=logfromuser=hrtouser=hrignore=y;7、hr環(huán)境搭建connhr/k以用戶名hr、密碼k連接數(shù)據(jù)庫(kù)createtableemp(empno

8、number(6)notnull,enamevarchar2(25),hiredatedate,jobvarchar2(10),salnumber(8,2),commnumber(2,2),deptnonumber(4);insertintoempselectemployee_id,last_name,hire_date,job_id,salary,commission_pct,department_idfromemployees;createtabledept(deptnonumber(4)notnull,dnamevarchar2(30),locnumber(4);insertintode

9、ptselectdepartment_id,department_name,location_idfromdepartments;createtableemp_jobasselectempno,jobfromempwhere1=2;insertintoemp_job(empno,job)selectempno,jobfromempwheredeptno=30;createtablenew_emp(id,name)asselectempno,enamefromemp;createtablesalgrades(grade_levelvarchar2(4),lowest_salarynumber,h

10、ighest_salarynumber);insertintosalgradesvalues(l1,1000,2999);insertintosalgradesvalues(l2,3000,5999);insertintosalgradesvalues(l3,6000,8999);insertintosalgradesvalues(l4,9000,14999);insertintosalgradesvalues(l5,15000,22999);insertintosalgradesvalues(l6,23000,30000);commit;8、sql語(yǔ)句的分類dql:dataquerylang

11、uage數(shù)據(jù)查詢語(yǔ)言:selectdml:datamanipulationlanguage數(shù)據(jù)操作語(yǔ)言insert/update/delete/mergeddl:datedefinitionlanguage數(shù)據(jù)定義語(yǔ)言create/alter/drop/truncatedcl:datecontrollanguage數(shù)據(jù)控制語(yǔ)言grant/revoketcl:transitoncontrollanguage事務(wù)控制語(yǔ)言commit/rollback/savepoint9、null:它不等于零或空格，任意的類型都可以支持nulla)包含null的任何算術(shù)表達(dá)式的結(jié)果都為nullb)包含null的連

12、接表達(dá)式等于與”(空字符串)連接10、列別明的兩種書寫形式a)列名列別名b)列名as列別名11、列別名必須加”的情況a)列別名中包含有空格b)列別名中要求區(qū)分大小寫c)列別名中包含有特殊字符12、使用|連接符代替，實(shí)例selectfirst_name|last_name|sphonenumberis|phone_number“employeephonenumber”fromemployees;其中first_name、last_name、phone_number位列變量，表示輸出空格，中前兩個(gè)表示輸出,sphonenumberis表示輸出sphonenumber，”employeephonen

13、umber”表示輸出列的別名。13、sql*plus/isql*plus命令a)desc:顯示表結(jié)構(gòu)，其中desc是describe的縮寫，可以寫全。b)set系統(tǒng)變量on|off，例如setheadingon|off:控制是否顯示列標(biāo)題。setescapeon|off:控制是否打開轉(zhuǎn)義符，showescape:顯示轉(zhuǎn)義字符setautocommiton|off:空值是否打開自動(dòng)提交，showautocommit:顯示當(dāng)前提交方式14、isql*plus的默認(rèn)顯示格式date和character型左對(duì)齊，numeric型右對(duì)齊、列標(biāo)題居中大寫字符函數(shù)其中字符串可以代表列名或字符表達(dá)式a)low

14、er(字符串)將所有大寫字符轉(zhuǎn)換為小寫字符b)upper(字符串)將所有小寫字符轉(zhuǎn)換為大寫字符c)iniicap(字符串)所有單詞的首字母大寫，其它的小寫d)concat(字符串1,字符串2)將字符串1與字符串2連接e)substr(字符串,m,n)從字符串中第m位開始截取n位，字符串從1開始計(jì)數(shù)f)length(字符串)返回字符串的長(zhǎng)度g)instr(字符串1,字符串2,m,n)返回字符串2在字符串1中，從第m位開始，第n次出現(xiàn)的位置。h)lpad(字符串1,n,字符串2)用字符串2在字符串1的左邊進(jìn)行填充，使字符串1達(dá)到n位i)rpad(字符串1,n,字符串2)用字符串2在字符串1的右邊進(jìn)

15、行填充，使字符串1達(dá)到n位j)trim(leading|trailing|both字符from字符串)將字符串中開頭(leading)和結(jié)尾(trailing)的指定字符（只能是字符）去掉。k)replace(字符串1,字符串2,字符串3)將字符串1中的字符串2用字符串3替換。數(shù)字函數(shù)a)round(數(shù)字,n)將數(shù)字四舍五入到小數(shù)點(diǎn)后n位，n為負(fù)數(shù)表示小數(shù)點(diǎn)前|n|位。b)trunc(數(shù)字,n)將數(shù)字截取到小數(shù)點(diǎn)后n位，其他同上。c)mod(m,n)取m除以n的余數(shù)日期函數(shù)a)sysdate返回當(dāng)前系統(tǒng)日期，不能寫成sysdate()b)months_between(date1,date2)返

16、回date1與date2之間的月數(shù)c)add_months(date,n)在date的基礎(chǔ)上增加n個(gè)月d)next_day(date,d)返回date之后的第一個(gè)指定d，例如next_day(16-8月-11,星期一),返回11年8月16日之后的第一個(gè)星期一是22-8月-11e)last_day(date)返回date所在月的最后一天，例如last_day(16-8月-11)返回31-8月-11f)round(date,fmt)將date按fmt格式四舍五入，fmt可選默認(rèn)為ddg)trunc(date,fmt)將date按fmt格式四舍五入，fmt可選默認(rèn)為dd，時(shí)間提前h)extract(

17、xxfromdate)從date中提取出xx(month、day、year)數(shù)據(jù)類型之間的轉(zhuǎn)換a)to_char(date|number,fmt)將date或number轉(zhuǎn)換為指定格式fmt的字符串，fmt可省略b)to_number(char,fmt)將char轉(zhuǎn)換為指定格式fmt數(shù)字，fmt可省略c)to_date(char,fmt)將char轉(zhuǎn)換為指定格式fmtdate，fmt可省略常用的日期格式a)yyyy四位數(shù)字的年份b)yy兩位數(shù)字的年份c)year年份的英文拼寫d)mm兩位數(shù)字的月份e)month月份的英文拼寫f)dy星期英文的前三位g)day星期英文的全拼h)d星期的第幾天，星

18、期日為第一天i)dd一個(gè)月中的第幾天j)ddd一年中的第幾天k)am或pm上下午l)hh或hh12或hh24表示小時(shí)12小時(shí)制24小時(shí)制m)mi表示分鐘n)ss表示秒數(shù)字格式a)9:一位數(shù)字b)0:一位數(shù)字或前導(dǎo)零c)$:美元符號(hào)d)l:按照地區(qū)顯示貨幣符號(hào)e).:小數(shù)點(diǎn)f),千位分隔符其它函數(shù)a)nvl(表達(dá)式1,表達(dá)式2):如果表達(dá)式1的值不為null，返回表達(dá)式1的值，否則返回表達(dá)式2的值，數(shù)據(jù)格式可以是日期、字符、數(shù)字，但表達(dá)式1和表達(dá)式2的數(shù)據(jù)類型必須一致。b)nvl2(表達(dá)式1,表達(dá)式2,表達(dá)式3):如果表達(dá)式1的值不為空，返回表達(dá)式2的值，否則返回表達(dá)式3的值。表達(dá)式2和表達(dá)式3

19、的數(shù)據(jù)類型必須相同。c)nullif(表達(dá)式1,表達(dá)式2):如果兩個(gè)參數(shù)不相等，返回表達(dá)式1的值；相等，則返回空值。d)coalesce(x1,x2xn):返回第一個(gè)非空的參數(shù)。e)(case表達(dá)式1when條件1then結(jié)果1when條件2then結(jié)果2else結(jié)果nend):如果表達(dá)式為條件1匹配就返回結(jié)果1最后默認(rèn)返回else后的結(jié)果n。f)decode(表達(dá)式，條件1，結(jié)果1，條件2，結(jié)果2缺省值):如果表達(dá)式與條件1匹配則返回結(jié)果1，缺省返回缺省值。表別名如果定義了表別名，那么之后只能使用表別名而不能使用原表名。連接語(yǔ)法selectfromtable1crossjointable2|

20、naturaljointable2|jointable2using(column_name)|jointable2on(tablecolumn_name=tablecolumn_name)|left|right|fullouterjointable2on(tablecolumn_name=tablecolumn_name)a)crossjointable2:生成笛卡爾積b)nutualjointable2:自然連接c)jointable2using(column_name):使用指定列連接，兩列同名d)jointable2on(tablecolumn_name=tablecolumn_name

21、):使用指定列連接，兩列可以不同名e)left|right|fullouterjointable2on(tablecolumn_name=tablecolumn_name):外連接union和unionalla)union和unionall連接的兩個(gè)結(jié)果集所包含的字段要相等。b)union的結(jié)果去掉重復(fù)的記錄，unionall不去掉重復(fù)的記錄。groupby的特點(diǎn)a)在groupby子句的列可以不在select語(yǔ)句中出現(xiàn)，但在select字句中出現(xiàn)的非分組列必須在groupby字句中出現(xiàn)b)groupby后面可以有多列select語(yǔ)句的執(zhí)行過程:順序?yàn)閒rom-where-groupby-ha

22、ving-select-orderbya)from:從from子句中找到需要查詢的表b)where:進(jìn)行非分組函數(shù)的篩選判斷c)groupby:完成分組操作d)having:完成分組函數(shù)的篩選判斷e)select:選擇要顯示的列或表達(dá)式或組函數(shù)f)orderby:對(duì)得到的結(jié)果集進(jìn)行排序組函數(shù)的嵌套最多為兩級(jí)，例如selectmax(count(employee_id)fromemployeesgroupbydepartment_id;子查詢特點(diǎn):a)子查詢只能在括號(hào)中b)子查詢需要寫在運(yùn)算符右邊c)子查詢可以寫在where、having、from中。分類:a)單行子查詢:用=、=、=等連接，子

23、查詢返回結(jié)果只有一條b)多行子查詢:用any、all、in等連接，子查詢的返回結(jié)果只有一條c)多列子查詢:子查詢的返回結(jié)果中含有多列。例如selecte.last_namee.salary,e.job_id,j.avgsalfromemployeese,(selectjob_id,avg(salary)avgsalfromemployeesgroupbyjob_id)jwheree.job_id=j.job_idande.salaryj.avgsal子查詢中的空值問題示例:selecta.last_name,a.salaryfromemployeesawherea.employee_idnotin(selectb.manager_idf