數(shù)據(jù)庫-編程day02oracle sql database第二天

學(xué)習(xí)

oracle的體系結(jié)構(gòu):實(shí)例+數(shù)據(jù)文件實(shí)例--用戶(方案OracleServiceORCLc:\app\administrator\product\11.2.0\dbhome_1\bin\ORACLE.EXEoracle用戶：sqlplusas :lsnrctl上圖為配置了tns配置文件的登錄方式,orcl00上圖為比較直接登陸網(wǎng)絡(luò)服務(wù)器的方式@后指定了對應(yīng)的主機(jī)192.168.137.100查 查詢可以全部列,部分列,表達(dá)式distinct的作用:去除重Where條件過濾:比較運(yùn)算符(!),邏輯運(yùn)算符(andor),在集合（notin集合中不能有null）,模糊查詢（like_ Orderby排序可以按列名,別名,表達(dá)式,序號Having的作用:和group一起使用,對分組的數(shù)據(jù)進(jìn)行過濾where后不不能用或者notin的集合中不能有)如果求10號部門的最低薪水和最selectmin(sal),max(sal)fromempwhere求入職的員工信求當(dāng)前日期所在的月份的最后一天和第一天,顯示格查詢名字中有字母A,并且是MANAGER的員select*fromempwhereenamelike'%A%'and將員工信息按照部門倒序,薪水升序排列select*fromemporderbydeptnodesc,sal求薪資在1200以上selectcount(*)fromempwheresal>多表9:27集集行數(shù)=表1的行數(shù)*表2的行數(shù)12--查詢員工信息：員工號月薪（emp）和部門名稱selectempno,ename,sal,fromempe,deptwheree.deptno=selecte.empno,ename,sal,dname,e.deptnofromempe,deptdwheree.deptno=--查詢員工信息：員工號月薪和薪水級別(salgrade表)selecte.empno,e.ename,e.sal,s.gradefromempe,salgradeswheree.sal>=s.losalande.sal<=s.hisal;fromempe,salgradewheree.salbetweens.losalandfromempe,deptdwheree.deptno=d.deptno/-fromempe,deptgroupbyd.deptno,d.dnameorderby1fromempe,deptgroupbyd.deptno,d.dnameorderby1--查詢員工信息：xxx的是yyyselecte.ename||'''sbossis'||b.enamefromempe,empbwheree.mgr=selecte.ename||'''sbossisfromempe,empwheree.mgr=

得到scott的工資selectsalfromempwhereename查詢大于這個(gè)工資的員工select*fromempwheresal>select*fromempwheresal>(selectsalfromempwhereename合理的書寫風(fēng)格小括號分步做：1.先得到部門2.通過部門得到員工信selectdeptnofromdeptwherednameselect*fromempwhereselect*fromempwheredeptno=(selectdeptnofromdeptwherednameselect where…groupby…errhaving…orderby…err--select后查詢10號部門員工號，員工，部門，部門名selectfromempe,deptdande.deptno=10selecte.empno,e.ename,e.deptno,(selectdnamefromdeptwheredeptno=10)fromempwhere題：select*select*from(selectename,sal,sal*16fromemp);--whereward相同job并且薪水比他高的員工信息selectjob,salfromempwhereselect*fromempwheresal>1250andjobselect*fromempwheresal>(selectsalfromempwhereename='WARD')andjob=(selectjobfromwhere--having30號部門最低薪水的部門及其最低薪水selectmin(sal)fromempwhereselectdeptno,min(sal)fromempgroupbydeptnohavingmin(sal)>selectdeptno,min(sal)fromempgroupbydeptnohavingmin(sal)>(selectmin(sal)fromemp不可以在主查詢的groupby后面放置子查詢(SQL語句的語范 --多行操作符有INANY(任意一個(gè))ALL（所有select*fromdeptwheredname='SALES'orselect*fromempwheredeptnoinselect*fromempwheredeptnoin(selectdeptnofromdeptwheredname='SALES'--查詢薪水比30號部門任意一個(gè)(某一個(gè)ANY員工高的員工信息selectmin(sal)fromempwhereselect*fromempwheresal>select*fromempwheresal>(selectmin(sal)fromempwhereselect*fromempwheresal>ANY(selectsalfromempwhereselect*fromempwheresal>ALL(selectsalfromempwheredeptno=30);select*fromempwheresal>(selectmax(sal)fromempwheredeptno=30);select*fromempwhereempnoin(selectdistinctmgrfromemp)select*fromempwhereempnonotin(selectdistinctmgrfromemp)select*fromempwhereempnonotin(selectdistinctmgrfromempwheremgrisnotnull)一般不在子查詢中使用orderby,但在Top-N分析問題中，必須使用order瑞典公主汀

○○

unionselect*fromempwheredeptno=10unionselect*fromempwheredeptno=20;select*fromempwheredeptno=10unionselect*fromempwheredeptnoin(20,10);select*fromempwheredeptno=10unionallselect*fromempwheredeptnoin(20,10);select*fromempwheredeptno=10intersectselect*fromempwheredeptnoin(20,10);select*fromempwheredeptnoin(10,30)minusselect*fromempwheredeptnoin(20,10);selectdeptno,job,sum(sal)fromempgroupbydeptno,jobselectdeptno,sum(sal)fromempgroupbydeptnoselectsum(sal)from參與運(yùn)算的各個(gè)集合必須列數(shù)相同，且類型一致 selectdeptno,job,sum(sal)fromempgroupbydeptno,jobselectdeptno，to_char(null),sum(sal)fromempgroupbydeptnounionbreakondeptnoskipbreakon

sql語言的類DMLdatamanipulationlanguage數(shù)據(jù)操作語言，對應(yīng)增刪改DDLdatadefinitionlanguage數(shù)據(jù)定義語言create|truncateDCL ontrollanguage數(shù)據(jù)控制語言新增數(shù)據(jù)語法：insertintotablename[col1,…]insertintodept--隱式insertintodept(deptno,dname)--顯示insertintodept(deptno,dname,loc)&SQL>insertintodept(deptno,dname)dpt的值:原 1:insertintodept(deptno,dname)新 1:insertintodept(deptno,dname)創(chuàng)建一個(gè)與emp相同結(jié)構(gòu)的表，不拷貝createtableemp10asselect*fromempwhere話按天建表t_uage_events_yyyymmdd--createtablet_usage_events_20170701asselect*t_uage_events_yyyymmddwhere批insertintoemp10select*fromempwhere修改數(shù)據(jù)：update--注意安語法：updatetablenamesetcol1=val1，col2=val2where舉updatedeptsetloc=nullwheredeptno=52;--設(shè)置的時(shí)候，null刪除數(shù)據(jù) --注意安全，做好備語法：deletefromtablenamewhere舉deletefromdeptwheredeptnodeletefromdeptwheredeptnodeletefromdeptwheredeptno刪除droptabletestdelete使用truncate測試結(jié)論：當(dāng)前數(shù)量級delete更快，當(dāng)數(shù)量級特別大的時(shí)候，truncate更truncate先摧毀表，再重建delete逐條刪除表“內(nèi)容”，truncatedelete是DML語句，truncate是DDL（閃回：做錯了一個(gè)操作并且commit了，對應(yīng)的撤銷行為。了解兩個(gè)數(shù)據(jù)之間的數(shù)據(jù)被刪除，刪除的數(shù)據(jù)——碎片，整理碎片，數(shù)據(jù)連續(xù)，行移 【圖示delete不會釋放空間，truncate用delete刪除一張10M的表，空間不會釋放。而truncate以當(dāng)確定表不再使用，應(yīng) 事A---> 1000A賬戶-1000 insertintotabname(col1,..) 提交結(jié)束顯示提交：SQL99定義4中級別： 讀未提交數(shù)據(jù)默認(rèn)默認(rèn)Read （ （

RepeatableRead createtabletestsp(tidnumber,tnameinsertintotestspvalues(1'Tom')；--事務(wù)開始insertintotestspvalues(1,'Tom')；savepointinsertintotestspvalues(3,updatetestspsettname='tomson'wheretid=1;deletefromtestspwheretid=1;rollbacktosavepointbbb;SQL>select*fromTID113rollbacktosavepointaaa;--此時(shí)不能再回退到select*from練習(xí)

rownum取工資5-8（oracle分頁問題SQL>selectto_char(hiredate,'yyyy'),count(*)fromempgroupby 211 SQL>selectto_char(hiredate,'yyyy')from10sum（）求和練習(xí)

SQL>select*from(selectrownumr,empno,ename,salfrome.*fromempeorderbysaldesc)whererownum<=8)wherer取前8selectdeptno,avg(sal)selectdeptno,avg(sal)fromempgroupby 30 10selectfrom(selectdeptno,avg(sal)avgsalfromempgroupbydeptno)a,empewherea.deptno=e.deptnoande.sal>a.avgsalselecte.empno,e.ename,e.sal,(selectavg(sal)fromempdeptno=10)avgsalfromempewhereande.sal>(selectavg(sal)fromempwhereselecte.empno,e.ename,e.sal,(selectavg(sal)fromempwheredeptno=e.deptno)avgsalfromempwheree.sal>(selectavg(sal)fromempwhere多表查詢相當(dāng)于中的空間換時(shí)間selectcount(*)fromempc1.cc2.cc3.cc4.cfromemp(selectcount(*)cfromempwhereto_char(hiredate,'yyyy')='1980')c1,(selectcount(*)cfromempwhereto_