SQL学习笔记


D«w/home/sd0807/c¿ê QQ292598441/2. SQL note.txt 1 Õ& 7 Õ þÿ„J•w 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ïJi€Q]YÏcÛ!i€QÏbd! 1¿øSQL³Y i+än€!z•.èŠ&(z•]óÒ·®æ“i>JÏáz•ò7áó) SQL³Ybš17™Â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±9BJ§[i]€7(DDLìDCL³YÏáQÍZiÅú@) bšócommit³YÍZi9nú@Åú= øSELECT³Ywd§º§ selectwƒ7áOÏá°ä ¶7áOÏá.uǧ™—ëw\duÿ°iƒuÿ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@ÇÛ|w€w¥Û|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 wbšAI OF ^Çß4•w ... For Update OF salary ... ¨w¥õÔ JORDER BY ]Yi•M Order byw4•MÇ°(ojÚM asciüßùxbI desciJMüùß) ¶4ú.u!ÚMf€•owiñ=µÇ8•Mó 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; (ƒÂßf™•Miþ•price) ORDER BY]Yä5ó!ÇJÏñÛpnd!ÇiûÏòþÂ7Å7î¥9ióí ¶!Ǖ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Ìbš5óÏá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%'n€™a! (“_”°p7á°w´x“%”°p°wÏá°w´) æÁiêñ!9 i[`M„æÁióÒ î´éi JòÆé%!Ç;™­éiɛ¥ îÁixóÒÆ.òÇ;™­é iÅóÁi is nullwÏz(NULL°pŧ™òÆz•0ÏÅ{!) wSELECT prod_name,prod_price FROM Products WHERE prod_price IS NULL; »ù ¶(‹÷0i)w rK¡›¥’uÏáΫ ¶.ubš5óANDOORNOT'>¥{(!Ϋ giMbšr'i()ÒµÇ8·!Å¥'·SúojgiMi5ói()f€Q¿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óÓQŽ8ªÂ 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õM˜zÏ@! NVLw¿øzòi±zòÒ³ñîòbÒ³ñ‘”´.ò'Áõ(¨wwÒ³ iÜ>.cÛ¥{%) wNVL(date, '01-JAN-95') NVL(title,'NO Title Yet') NVL(salary,0) ìµM˜w Select last_name,title,salary*commission_pct/100 COMM From s_emp;--fÍJ!?f˜Ûpfè SÇM˜w Select last_name,title,salary*NVL(commission_pct,0)/100 COMM From s_emp;--±ÍJÏzò!Ò³ñ0 DISTINCTw'tëÅ ±ëÅ!™'tŒxÏᴔ!w i]•!wëÅ! DISTINCTcÛ5óÇ|iÅ5ógiOP° ¼ n€!”w¯..bš5óîÇ|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!z•iä´!Åø 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š;™IƒiojæÏ1Ø‘”Æ‘”bš{ƒi5¬Ø.x‘”Æ‘”ûÅ{I sysdate j>  19.774194 {ÊÏTá,BetweenêñbšA¯. Add_months('11-Jan-94',6) --> 11-Jul-94 lIÏTá Next_day('01-Sep-95','Friday') --> '08-Sep-95' Ã7á”J+ä!'Friday'bó6x˜iüñ”‘z1 Last_day('01-Sep-95') --> '30-Sep-95' 9á!y}7Ø .ò¿øwbš0ó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°ä!n€vô:ìZ7•°ä!vôΫÌvizòÏÌvi9á°ä!n€vô.ÏÛ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¬Ò gzM˜w9>¥ó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·ñÏá‹÷!išu@©á!;™”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 bš1SQLä{ 5ó WHEREìHAVINGiþ™WHEREiA™HAVING ”w¯.w AVGw4Jò (Š&òñNULL!™iûÅó AVG(*)) COUNTwg. (Count(Ç)Ågizòxû COUNT(*)°pN üñ9Ï9µÁNif€1nÚ±Afª1]ƒ1®]›¥ƒä´®] i›=ë]rownum³Y›NÛ| ,-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 ´”|´Åbšw¥|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Åbšroll 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 Ð}bšroll back®z}Åè9µ
还剩6页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 5 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

xlmsky

贡献于2011-10-31

下载需要 5 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf