D«w/home/sd0807/c¿ê QQ292598441/2. SQL note.txt 1 Õ& 7 Õ þÿJw telnet 192.168.0.23 !ãiw openlab-open123 tarena-tarena A;ASQLwsqlplus sd0807/sd0807 ãiwsd0807jg{ !ãiwopenlab-open123 7¨wBÙw [`MÅiþÅÍ·[`MÍ×(´[MiuDÅÁìɼ “^”1SQL¦WÃUnixà SQL³YÏüWæ!˳æïJx˳æñ(´/ ´iÅÛóSQLüÏá(´ïJ SQL³Yü]YïJiQ]YÏcÛ!iQÏbd! 1¿øSQL³Y i+än!z.è&(z]óÒ·®æi>JÏázò7áó) SQL³Yb17ÂM¬iªtÏM¬iuDÅÁìÉ ÏÎSQL³YcÛ·i·DÏ.DBMSÅ¥1æÎSQL³Y}I·iiûî!DBMSbcÛ1æÎSQL³Y}I·i SQL³Y!y}7Y¥ “x”i0Ì =M]YÚM Select column,group_function From table [Where condition] [Group by group_by_expression] [Having group_condition] …… [Order by column]; --y} ÁóWæ³Yw clear screenw® editws÷Êz!7Y desc/describew(ǬnÇ|) ów DESCRIBE [schema.]object[@db_link] dualwP°ië ó wdesc dualxlfrom dualx rollbackwú@iú@ß §û!Ìi±9 BJ§[i]7 (DDLìDCL³YÏáQÍZiÅú@) bócommit³YÍZi9nú@Åú= øSELECT³Ywd§º§ selectw7áOÏá°ä ¶7áOÏá.uǧëw\duÿ°iuÿ8mdcÛ¥From]Y(yó) òÏÉ°êG¨! ÏiÏ]îýîWxbóΫ'tÛ òÜá°{{´ cÛIû]iÇ|ûI°|ì“.”i “s_emp.id” 1ówSELECT columns,prod2,prod3<Ç> FROM Table1,table2<°|> ·i0Ì w select id from s_emp; select last_name,name from s_emp,s_dept where s_emp.dept_id=s_dept.id;--Ç°©?n1 ¨ SELECT * FROM Products; -- ¶nÇ .uÙÏiyÛ5óÂYiÏ5DBMS ¶ìRó#M!°(*rv) 2@.u%!Çb;0i( I) 3@ÇÛ|ww¥Û|iIASÛ|ióGÁiÛ|'Áõm (æÁiiÁ´éxGÁiiÁÛ|Û|iiOPÍ·[`M icÛóGÁi) Ï°G¨ ib6°Û|(6ÇÛ|iÇÇÛ|x6°Û|i°°Û|x) wSelect first_name EMPLOYEES, 12*(salary+100) AS MONEY, manager_id "ID1" From s_emp E; 4´!¥,bóGÆ (GÆ ]óDselect³Yê)Å{!DBMSb5óÅ{!§;¥!´{bÛ | wSelect first_name ||' '|| last_name || ', '|| title "Employees" From s_emp; ÔwSelect id,salary From s_emp where id=1 For Update; bõR?7K!¤iwßõÔ ÍÔÉáQ\¬wSelect id,salary From s_emp where id=1 For Update NoWait; FOR UPDATE wbAI OF ^Çß4 w ... For Update OF salary ... ¨w¥õÔ JORDER BY ]YiM Order byw 4MÇ°(ojÚM asciüßùxbI desciJMüùß) ¶4ú.u!ÚMfowiñ=µÇ8Mó SELECT ³Y ¶¬!.uib5ó ORDER BY ]Y ORDER BY ]YN7áOÏáÇ!|´ @zòi w[¿ø(ÚMäizòy}xMäyû) 1ówSelect prod_id,prod_price,prod_name From Products Order By prod_price,prod_name; (ÂßfMiþprice) ORDER BY]Yä5ó!ÇJÏñÛpnd!ÇiûÏòþÂ7Å7î¥9ióí ¶!ÇM.uÏâ w! ñ= ÏáÇMiÇ|´ópi·® 2û {@ÇÇ;M ùA SELECT prod_id,prod_price,prod_name D«w/home/sd0807/c¿ê QQ292598441/2. SQL note.txt 2 Õ& 7 Õ FROM Products ORDER BY 2i3 --(2pricei3name) 3ÚMMojÏÚM(asci`ß[M)i\M ódesc wSELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC; ¨wwDESC (´]Róßw¥D+ûñ!Ç| \1ÏáÇÂ;MicÛ@©áÇîDESC(´ ÚMÏoj!ibÅMiûMcÛM "WHERE]Yid't +}]o÷³Yi4úò]tureOfalse w select last_name,salary from s_emp where salaryz1000;--¥¬fè1000!? WHERE]Y§w 1÷é0i z 'D ^= Å'Di8(<> ^= 9Üá{°pÅ'D) > [D >= [D'D < `D <= `D'D 2SQL é0i between…and… w1ÜP´(BETWEEN `ò AND [ò) wselect last_name,salary from s_emp where salary between 1000 and 1500x --fè1000ß1500!?i§1000ì1500 in(Ç°)w1Ç°êñ! wselect last_name,dept_id from s_emp where dept_id in(41,42); 4142 ¨!? like w §49 !GhG¨ bÙórv˪éî.u!¶G¼irv]óDD iíD .u%Å5órv rv1¶G¼ä°wÇ5ói7Ìb5óÏárv rv%°p° ´¬§°w .x8°¶G¼ä6îÇ!0áOÏá´Ã Ìvæá°w´ wselect table_name from user_tables where table_name like 'S\_%' escape'\'; ' ¥¬“S_“®á!,üDà°w´!i ZÌîÒ ûXó“\”iñmu+#MÁÅÁì i7Åó+! like 'M%'wM®á! like '_a%'w =á´Ïa! like '%a%'na! (“_”°p7á°w´x“%”°p°wÏá°w´) æÁiêñ!9 i[`MæÁióÒ î´éi JòÆé%!Ç;éiÉ¥ îÁixóÒÆ.òÇ;é iÅóÁi is nullwÏz(NULL°pŧòÆz0ÏÅ{!) wSELECT prod_name,prod_price FROM Products WHERE prod_price IS NULL; »ù ¶(÷0i)w rK¡¥uÏáΫ ¶.ub5óANDOORNOT'>¥{(!Ϋ gi Mbr'i()ÒµÇ8·!Å¥'·Súojgi Mi5ói()fQ¿iÛ==° andwÎ«Æ SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price<4 AND vend_id=‘DELL’ orwΫO (¨w and !Íþ or nùiPÍþbói) SELECT prod_id,prod_price,prod_name FROM Products WHERE prod_price<4 OR vend_id=‘DELL’ notwΫíîÛ}no!° Ϋ î!SQL é0iw NOT BETWEENx NOT INx NOT LIKEx IS NOT NULLw (¨wi ˳Xó is notiUÅÏ not is) NOT Æ IN 175ó iNOT Ï¥¬ÆΫǰÅÌv! IN Ç°ê NULL Å¿øiÅ,0xó NOT IN i NULL ɬìicÛzòA0i in wdÇ°!Ϋ 5óIN§!Íw 1Q!dÙ®æ ,³wÖx gi! M ºqøx OR §®æn~xy[ÍÏb§+ SELECT ³Yi5óÓQ8ªÂ WHERE ]Y SELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id IN(‘DELL’,’RBER’,’TTSR’); Bæ¯.w ¯.71.uÂiÛ6.u!Òkì¿øÍQ=muÅ{!DBMSÍQ!¯.Å{ ¯.bÏ÷ÒÆ!0i"ñz zòÆzò7Å{'iüñzòÅw¥0i wprod_price="" 9õMÏì!(Å¥Oßcorejava!,) prod_price=NULL 9õMÏì!(Å¥Oßcorejava!,) prod_price IS NULL 9õMzÏ@! NVLw¿øzòi±zòÒ³ñîòbÒ³ñ´.ò'Áõ(¨wwÒ³ iÜ>.cÛ¥{%) wNVL(date, '01-JAN-95') NVL(title,'NO Title Yet') NVL(salary,0) ìµMw Select last_name,title,salary*commission_pct/100 COMM From s_emp;--fÍJ!?fÛpfè SÇMw Select last_name,title,salary*NVL(commission_pct,0)/100 COMM From s_emp;--±ÍJÏzò!Ò³ñ0 DISTINCTw'tëÅ ±ëÅ!'txÏá´!w i]!wëÅ! DISTINCTcÛ5óÇ|iÅ5ógiOP° ¼ n!w¯..b5ó îÇ|iÉDISTINCT]óDCOUNT(Ç|)iDISTINCTÅóDCOUNT(*) wSelect Distinct name From s_dept; Select Distinct dept_id,title From s_emp; D ¿øw TRIM()/LTRIM()/RTIRM()w:z]:áìx!ziä´!Åø trim(' heo Are fdou ') --> heo Are fdou ùAwselect trim(' heo Are fdou ') from dual; -->wheo Are fdou LOWERwÒ`M lower('SQL Course') --> sql course UPPERwÒ[M upper(' SQL Course') --j>SQL COURSE INITCAPwÜ´¨Ò[Mi+Ò`M initcap(SQL Course') '--> Sql Course CONCATwwJGÆ ]1select³Yêñói9ábóD° ³Y Concat('Good','String') --> GoodString SUBSTRw^N Substr('String', 1 ,3) --> Str 7á.´“1”i°p Fá®]^NxÀ¥È. Fá®]ióÃ.i “j2”°pÈ. 2á ¼ä 2á.´“3”°p^NÏTá LENGTHw 6 NVLwÒkzò ì ´¿øw Oracle¼wDD-MMM-YYYY (D°dateiM°monthiY°5year) wSELECT prod_name (DAY°pâ1!ÂFiDYÛpÂ!ûÁá´¨) FROM Products WHERE prod_time BETWEEN to_date(’01-JAN-2008’) AND to_date(’31-DEC-2008’); b;IiojæÏ1ØÆb{i5¬Ø.xÆûÅ{I sysdate j> 19.774194 {ÊÏTá,BetweenêñbA¯. Add_months('11-Jan-94',6) --> 11-Jul-94 lIÏTá Next_day('01-Sep-95','Friday') --> '08-Sep-95' Ã7áÂJ+ä!'Friday'bó6xiüñÂz1 Last_day('01-Sep-95') --> '30-Sep-95' 9á!y}7Ø .ò¿øwb0óD.iÁæiF ROUNDwøþJA Round(45.925,2) j> 45.93 Round(45.925,0) j> 46 Round(45.925,-1) j> 50 piû7á.Ï¥¿ø!.u'i}7á>.°p ÏT`. }7>.ÏÃ. i°pþ:`.û!FiA3Ïþ:áE+}!ÅM}7>. iojÅ `. TRUNCwþ:w¥þ:iÅÏ; Trung(45.925,2) j> 45.92 Trung(45.925,2) j> 45.92 Trung(45.925,2) j> 45.92 !þNw ó!.ò¿ø¯.w D«w/home/sd0807/c¿ê QQ292598441/2. SQL note.txt 4 Õ& 7 Õ ABS() :@ò ABS(-5741.5854) --> 5741.5854 PI() Í ¨wworacleäÅû PI()¯.xMYSql ûPI()¯. SIN() S<ò Oracle8ûCOS()ASIN()ACOS()¯. SQRT() 4m Ò³w TO_CHAR(number,'fmt')w±.òÒkJ´é Ûp.´!à 9wSÛp.´x 0wÛp§0!.ò¼izÞå®0x $wãùiÛpËÖx Lw òû¦WÛp{(!ËÖix . ìiw1îǬ§“.” ì“i”pixÅÓ iøþJA AøwSQL> select 'Order'||To_char(id)|| 2 'was filled for a total of' 3 ||To_char(total,'fm$9,999,999') 4 from s_ord 5 where ship_date ='21-SEP-92'; TO_NUMBER(char)w±´ÒkJ.´ Ñ¥ 9Ñ¥wÝÌvÜ°!vô ÌÑ¥·ÂÑ¥ìfÑ¥: Ï5ó7m°ä!nvô:ìZ7°ä!vô ΫÌvizòÏÌvi9á°ä!nvô.ÏÛpi .uQÏGù¬vô:ìãQÅÌv!vôÌv ÂÑ¥ Ii1fñ w TABLE1 TABLE2 1!7Îvô12êñfÌvÂiãÿ1êñ!vô 2!7Îvô11êñfÌv iãÿ2Ú± fÑ¥S{H --AøwIQ?ÏæC select distinct b.id,b.last_name manager from s_emp a,s_emp b where a.manager_id=b.id(+); ÂfÚMÍÛi9ÏZÌfª7á°i¥Ûp!Ï =á°!9 +1fÌv!°7]i°â1Ûp¬Ò gzMw9>¥óINNERiÂ>¥óLEFTif>¥óRIGHT select distinct b.id,b.last_name manager from s_emp a LEFT join s_emp b ON a.manager_id=b.id; Ô!¯.w ·!÷~J.u·ñÏá÷!iu@©á!;jgi Groupw·! Group byw·!(oj ÚM@n·!!Mx\¥M¥ó order by)b§°w.q!Ç UA=·!i.uJ1y}Øî!·!Â;Á GROUP BY ]YäǬ!©áÇ.cÛÏ ¶ÇO!° ¼iûÅÏj¯. * 1SELECT ä5ó° ¼iÉcÛ1GROUP BY]Yäî{{!° ¼iÅ5óÛ| wgi³YÌiSELECT³Yä!©áÇ.cÛ1GROUP BY]Yä6¬ ·!Çä,NULLòiÉNULLJñ7á·!4ú ÇäÏNULLiÛ¡J·ñ7! Havingw't·!}iÅAówherei¥óhaving d'tHavingÅæ÷µ1icÛo1group by}ñ WHERE1.u·!û;'tiHAVING1.u·!}'t b1SQLä{ 5ó WHEREìHAVINGiþWHEREiAHAVING w¯.w AVGw4Jò (&òñNULL!iûÅó AVG(*)) COUNTwg. (Count(Ç)Ågizòxû COUNT(*)°pN üñ9Ï9µÁNif1nÚ±Afª1]1®]¥ä´®] i=ë]rownum³YNÛ| ,-Rów Top-n Analysis (ûN|Oy}N|) Select [G¨Ç°], Rownum From (Select [G¨Ç°(¥@R)] From ° Order by Top-N_´) Where Rownum <= N ·ÕÛpw --Nfè 5»10|!Áf(=ë]rownum³YiNÛ|) select rn,id,last_name,salary From ( select id,last_name,salary,Rownum rn From (Select id,last_name,salary from s_emp order by salary desc) where rownum <= 10) D«w/home/sd0807/c¿ê QQ292598441/2. SQL note.txt 7 Õ& 7 Õ where rn between 5 and 10; Unionww7° Select … Union Select… ±ÜáSelect³Y!°w7 ¥Ü°!´.qì% ÚM@Rw7}!°iáQ'tëÅ! IntersectwZ {ÂAi±ÜáSelect°{Z Minusw ±{Z!9 : not exists 0i ¦I´(Ç)w Alter Table °| Add (column dataype [Default expr][Not Null] [,column datatype]…); ¦Iíz å!´ i¥IDefault³Y ´|´Åbw¥|i¥¦If´iAÅå´}д ¦IÌw Alter Table °| Add [CONSTRAINT constraint] type (column); ¦IízÌ i¥óModify³Y GÌ| ib=HÌA¬ìÍpxOPGÌ´-desc user_constraints T´w Alter Table °| Drop (column [,column]…); ÐÌw Alter Table °| Drop CONSTRAINT column; Ow Alter Table °| Drop Primary Key Cascade; (Ìi7íÐw Alter Table °| Disable CONSTRAINT column Cascade; ®Êz(!Ìw Alter Table °| Enable CONSTRAINTcolumn; ¤´w Alter Table °| Modify (column dataype [Default expr][Not Null] [,column datatype]…); ¤´!%[`Ìíz åzòÒk аw Ïа!n.uin¶ÁÏÐiÌΫÐiÅbroll backÞÅ Drop Table °| [Cascade Constraints]; I [Cascade Constraints] ±]°!ÌΫÐxû]I [Cascade]ϱ]°Ð °|w Rename %°| To f°|; ®z°w TRUNCATE TABLE °|; {Delete,Truncate Table®z&~iûÅbÞÅ®z}è9µ Delete Ð}broll back®z}Åè9µ