Oracle數(shù)據(jù)庫(kù)操作基本語(yǔ)法

1、數(shù)據(jù)庫(kù)執(zhí)行是從左到右執(zhí)行的創(chuàng)建表SQL>createtableclasses(classIdnumber(2),cnamevarchar2(40),birthdaydate)；添加一個(gè)字段SQL>altertablestudentadd(classIdnumber(2);修改字段長(zhǎng)度SQL>altertablestudentmodify(xmvarchar2(30);修改字段的類(lèi)型/或是名字(不能有數(shù)據(jù))SQL>altertablestudentmodify(xmchar(30);刪除一個(gè)字段SQL>altertablestudentdropcolumnsal修改

2、表的名字SQL>renamestudenttostu;刪除表SQL>droptablestudentJSQL>insertintostudentvalues(001'',salina','女'-5,'月0-105'修改日期輸入格式SQL>altersessionsetnls_date_format=yyyy-mm-dd'/臨時(shí)生效，起錯(cuò)用SQL>insertintostudentvalues(,010salina'女,',to_-05','yyyy-mm-dd'

3、),10)；SQL>insertintostudentvalues('001salina'女,',to_-05','yyyy/mm/dd'),10)；插入所有字段數(shù)據(jù)插入部分字段SQL>insertintostudent(xh,xm,sex)values('001lison','）；女插入空值重啟后不10)；01-501/5SQL>insertintostudent(xh,xm,sex,birthday)values('021男',null);BLYK一條插入語(yǔ)句可以插入多行數(shù)據(jù)SQL&g

4、t;insertintokkk(Myid,myname,mydept)selectempno,ename,deptnofromempwheredeptno=10;查詢空值/(非空)的數(shù)據(jù)SQL>select*fromstudentwherebrithdayisnull(/notnull)修改(更新)數(shù)據(jù)SQL>updatestudentsetsal=sql/2wheresex='男'；更改多項(xiàng)數(shù)據(jù)SQL>updateempset(job,sal,comm)=(selectjob,sal,commfromempwhereename='SMITH'

5、)whereename='SCOTT'刪除數(shù)據(jù)1.保存還原點(diǎn)SQL>savepointaa;2.刪除數(shù)據(jù)SQL>deletefromstudent;/刪除表的數(shù)據(jù)2】SQL>droptablestudent;/刪除表的結(jié)構(gòu)和數(shù)據(jù)3SQL>deletefromstudentwherexh='001'/刪除一條記錄4】SQL>truncatetablestudent;/刪除表中的所有記錄，表結(jié)構(gòu)還在日志，無(wú)法扎找回的記錄，速度快查看表結(jié)構(gòu)SQL>descstudent;查詢指定列SQL>selectsex,xh,xmfrom

6、student如何取消重復(fù)SQL>selectdistinctdeptno,jobfromstudent;打開(kāi)顯示操作時(shí)間的開(kāi)關(guān)SQL>settimingon;為表格添加大的數(shù)據(jù)行(用于測(cè)試反應(yīng)時(shí)間)SQL>insertintousers(userid,username,userpss)select*fromusers;統(tǒng)計(jì)表內(nèi)有多少條記錄SQL>selectcount(*)fromusers;屏蔽列內(nèi)相同數(shù)據(jù)SQL>selectdistinctdeptno,jobfromemp;查詢指定列的某個(gè)數(shù)據(jù)相關(guān)的數(shù)據(jù)SQL>selectdeptno,job,sal

7、fromempwhereename='smith使用算數(shù)表達(dá)式SQL>selectsal*12fromemp;使用類(lèi)的別名SQL>selectename”姓名”，sal*12as”年收入”emp;處理null(空)值SQL>selectsal*13+nvl(comm,0)*13連接字符串(|)SQL>selectename|isa'|jolmemp年工資”e,name,commfromemp【1SQL>selectename,salfromempwheresal>3000;/number的范圍確【2SQL>selectename,hir

8、edatefromempwherehiredate>'-11月-1982'/日期格式的范圍確定【3SQL>selectename,salfromempwheresal>=2000andsal<=2500;/組合條件Like操作符：%'、'SQL>selectename,salfromempwhereenamelikeS'%'/第一個(gè)字符【名字第一個(gè)字符為S的員工的信息（工資）SQL>selectename,salfromempwhereenamelike'_O%'/其它字符【名字第三個(gè)字符為O的

9、員工的信息（工資）批量查詢SQL>select*fromempwherein(123,456,789;/查詢一個(gè)條件的多個(gè)情況的批量處理查詢某個(gè)數(shù)據(jù)行的某列為空的數(shù)據(jù)的相關(guān)數(shù)據(jù)SQL>select*fromempwheremgrisnull;Where子句的使用條件組合查詢（與、或）SQL>select*fromempwhere(sal>500orjob='MANAGER'a)ndenamelike'J%'Orderby排序SQL>select*fromemorderbysal(asc);/從低到高默認(rèn)SQL>select*f

10、romemorderbysaldesc;/從高到低SQL>select*fromemporderbydeptno(asc),saldesc;/組合排序SQL>selectename,sal*12"年薪"fromemporderby"年薪"(asc);SQL>selectename,(sal+nvl(comm,0)*12as"年薪"fromemporderby"年薪SQL>selectmax(sal),min(sal)fromemp;SQL>selectename,salfromempwheres

11、al=(selectmax(sal)fromemp);/資料分組（max、min、avg、sum、count）子查詢，組合查詢子查詢，組合查詢SQL>select*fromempwheresal>(selectavg(sal)fromemp);/月-1982'/將工資SQL>updateempsetsal=sal*1.1wheresal<(selectavg(sal)fromemp)andhiredate<'1-1小于平均工資并且入職年限早于1982-1-1的人工資增加10%Groupby和having子句/groupby用于對(duì)查詢出的數(shù)據(jù)進(jìn)行分組

12、統(tǒng)計(jì)/having用于限制分組顯示結(jié)果fromempgroupbydeptno;/SQL>selectavg(sal),max(sal),deptnofromempgroupbydeptno;/SQL>selectavg(sal),max(sal),deptnofromempgroupbydeptnoavg(sal)>2000;SQL>selectavg(sal),max(sal),deptnofromempgroupbydeptno顯示每個(gè)部門(mén)的平均工資和最低工資avg(sal)>2000orderbyavg(sal);havinghaving多表查詢SQL&g

13、t;selecta1.ename,a1.sal,a2.dname笛卡爾集：規(guī)定多表查詢的條件是至少不能少于：表的個(gè)數(shù)fromempa1,depta2where-1SQL>selecta1.dname,a2.ename,a2.sala1.deptno=a2.deptnoa1.deptno=a2.deptnoanda1.deptno=10fromdepta1,empa2where/顯示部門(mén)編號(hào)為10的部門(mén)名、員工SQL>selectavg(sal),max(sal),deptnoSQL>selecta1.ename,a1.sal,a2.grade名和工資fromempa1,sal

14、gradea2whereSQL>selecta1.ename,a1.sal,a2.dnamea1.salbetweena2.losalanda2.hisalfromempa1,depta2where顯示每個(gè)部門(mén)的平均工資和最低工資a1.deptno=a2.deptnoorderbyal.deptno/多表排序SQL>selectworker.ename,boss.enamefromempworker,empbosswhereSQL>selectworker.ename,boss.enamefromempworker,empbosswhereworker.mgr=boss.em

15、pno;/白連接（多表查詢的特殊情況）worker.mgr=boss.empnoandworker.ename='FORD'子查詢SQL>select*fromempwheredeptno=(selectdeptnofromempwhereename='SMITH');SQL>selectdistinctjobfromempwheredeptno=10;SQL>select*fromempwherejobin(selectdistinctjobfromempwheredeptno=10);/如何查詢和部門(mén)10的工作相同的雇員的名字、崗位、工資、

16、部門(mén)號(hào)。SQL>selectename,sal,deptnofromempwheresal>all(selectsalfromempwheredeptno=30);/如何查詢工資比部門(mén)30的所有員工的工資高的員工的姓名、工資和部門(mén)號(hào)SQL>selectename,sal,deptnofromempwheresal>(selectmax(sal)fromempwheredeptno=30);SQL>select*fromempwhere(deptno,job)=(selectdeptno,jobfromempwhereename='SMITH');內(nèi)

17、嵌視圖/當(dāng)在from子句中使用子查詢的時(shí)候，必須給子查詢指定別名SQL>selecta2.ename,a2.sal,a2.deptno,a1.mysalfromempa2,(selectdeptno,avg(sal)(as)mysalfromempgroupbydeptno)alwherea2.deptno=a1.deptnoanda2.sal>a1.mysal;分頁(yè)為表分配SQL>selecta1.*,rownumrnfrom（select*fromemp）a1;/orcle的行號(hào)emp）a1SQL>select*from（selecta1.*,rownumrnfro

18、m（select*fromwhererownum<=10）wherern>=6;查詢內(nèi)容的變化1. 所有的改動(dòng)（指定查詢列）只需更改最里面的子查詢（排序）只需更改最里面的子查詢子查詢（用查詢結(jié)果創(chuàng)建新表）selectSQL>createtablemytable（id,name,sal,job,deptno）asempno,ename,sal,job,deptnofromemp;合并查詢union（求并集）,unionall,intersect（取交集），minus（差集）unionselectSQL>selectename,sal,jobfromempwheresal>2500;SQL>selectename,sal,jobfromempwheresal>2500SQL>selectename,sal,jobfromempwherejob='MANAGER'/union（求并集）ename,sal,jobfromempwherejob='MANAGER'Java連接數(shù)據(jù)庫(kù)事務(wù)SQL>commit;/事務(wù)(第一次創(chuàng)建,第二次提交)當(dāng)退出數(shù)據(jù)庫(kù)時(shí),/創(chuàng)建保存點(diǎn)(aa;/使用保存點(diǎn)回滾到aa保存點(diǎn)的個(gè)數(shù)沒(méi)有限制)統(tǒng)白動(dòng)提交事務(wù)SQL>savepointal；SQL