ޢ 5澝પ Bcreate table tab_new as select col1,col2… from tab_old definition only ੮)ޏڏ੮ӫޛAcreate table tab_new like tab_old (҅ऀ ੮ޏڏ ߿݇ٝ߄ङ੮ӫ create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) ੮ޏڏӫޢ 4澝પ BACKUP DATABASE pubs TO testBack ѡו ڐ --- ’EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:\mssql7backup\MyNwind_1.dat USE master ङ device݇ރѡו ڏ --- ӫ ѡ sql serverוޢ 3澝પ drop database dbname ڀ݇ރӭஔޢ 2澝પ CREATE DATABASE database-name ڀ݇ރڏӫޢ 1澝પ ү,এѐׂॅધՏ Ӳધઈ(GRANTͫREVOKEͫCOMMITͫROLLBACK)ݏ݇ރ— DCL ݱધઈ(SELECTͫDELETEͫUPDATEͫINSERT)݇ރ— DML Уધઈ(CREATEͫALTERͫDROPͫDECLARE)ؔ݇ރ— DDL SQLӣঝ ЈӧધՏୂӣީ Mssql ધՏͫЉՕљ֨ access И҅ऀ澞 !މ߲ͫ׳তڮselect * from table1 where field1 like ’%value1%’ ---like ङધࡣ܇ ߲ update table1 set field1=value1 where ਸ֠ޏ ӭஔdelete from table1 where ਸ֠ ݖҵinsert into table1(field1,field2) values(value1,value2) select * from table1 where ਸ֠ܫ ଣ әЗএԥङׂߎङ sql ધՏޢ10澝પ ӭஔં֣drop view viewname create view viewname as select statement֣ંڏ ӫ ޢ 9澝પ 澞ڏޏӭஔ୍ீڷݷЉՕݷङͫީږ ࡨফ drop index idxnameږ ӭஔফ create [unique] index idxname on tabname(col….)ږফڏ ӫ ޢ 8澝પ ӭஔП୰Alter table tabname drop primary key(col) ޢ પ ࢍԆП୰Alter table tabname add primary key(col) ޢ 7澝પ 澞چঝֺङ ঝֺЭЉਈݷՊͫ֊▲ਈݷՊङީԆ varchar݇ރЉਈӭஔ澞DB2 ИӧԆЇեر ࡨӧԆե Ԇ▲ЗӧAlter table tabname add column col type ޢ 6澝પ ੮drop table tabnameޏӭஔ SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c ੮ङ۱߄੧澞ݎକ٘ܬ੮ङԙ੧ͫЭԕݎକܬৈߧதәԕͨݎକ٘ͧݎକי٘ A澝left outer join ݎକי҅ऀޢ 12澝પ ਚङ澞▲ީீڷ ࡨ҅ऀટङәЗ߲તৈߧ੧ ੧澞זINTERSECT ALL)ͫЉࢃஔ୍) ޞALL ச INTERSECT ▲҅ऀ ڢ▲Зৈߧ੮澞 ੧৲ࡵࣿӟזࢃஔ۱߄୍ٷTABLE1 չ TABLE2 Иୃ߄ङ੧ ܬ INTERSECT অପଋՑԕ C INTERSECT অ ੧澞זEXCEPT ALL)ͫЉࢃஔ୍) ޞALL ச EXCEPT ▲҅ऀ ڢӟ▲Зৈߧ੮澞 ੧৲ࡵࣿזࢃஔ۱߄୍ٷ۱߄֨ TABLE1 ИѸЉ֨ TABLE2 Иङ੧ܬ EXCEPT অପଋԕ B EXCEPT অ ӑЈͫࡵࣿ੮ङ▲੧Љީߛਘ TABLE1 ؼީߛਘ TABLE2澞ە ੧澞Дय़זԯ UNION ALLͨͫЉࢃஔ୍ͧޞALL ச UNION ▲҅ऀ ڢ৲ࡵࣿӟ▲Зৈߧ੮澞 ੧זࢃԾ੮ИѠѾ୍ٷTABLE1 չ TABLE2ͨ ײ UNION অପଋুՠҿѕДЗৈߧ੮ͧ҆ A UNION অ әЗ߲તટޢ 11澝પ select min(field1) as minvalue from table1ش ߂ select max(field1) as maxvalue from table1ם ߂ select avg(field1) as avgvalue from table1֮ٵ ࡌչselect sum(field1) as sumvalue from table1 select count * as totalcount from table1ރۈ [select * from table1 order by field1,field2 [descپ ;select * from (SELECT a,b,c FROM a) T where t.a > 1 ֨িં֣߲ત(੮դ 1a )ޢ 7澝પ select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c ߲ત(੮դ 1a ੮դ 2b)ݎକיޢ6澝પ where table.title=a.title) b select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table ޞזॾ澝ݕсыչ߂ե֛ކުॐޢ 5澝પ select a,b,c from a where a IN (select d from b ) ۪ৱ: select a,b,c from a where a IN (1,2,3) ߲ત(੮դ 1a ੮դ 2b)ޢ 4澝પ ҆..from b in ’"&Server.MapPath(".")&"\data.mdb" &"’ where.. where ߚў ’ڀ݇ރinsert into b(a, b, c) select d,e,f from b in ‘Ӏѽ Access Օऀ)) (ڬ૨ثऀ্҅݇ރા(ӀѽܯФ੮ङڀ݇ރ૧ޢ 3澝પ insert into b(a, b, c) select d,e,f from b; ࢛੮դa ऩ߶੮դb) (Access Օऀ),݇ރાܯ)ા੮ܯޢ 2澝પ ࡣиselect top 0 * into b from a ࡣ▲select * into b from a where 1<>1 ੮դb) (Access Օऀ)ޏ Ӳৈߣ,࢛੮դaזӲ੮(Ցזޢ 1澝પ ߛर▲пЉ୪ङ sql ધՏ؟ם ҿࠩͫ 澞ڣ੮Иङ۱߄ઓݎДЗକܬ੮ङԙ੧ͫଐԕݎঅ՚କܬЉюԕݎକי Ҷ Cfull outer join ੮ङ۱߄੧澞ݎକܬ੧ͫЭԕݎ੮ङԙକݎକܬԕޘৈߧத(ݎକ)ݎକי Bright outer join: ӟ▲Зৈߧ੮ ੧৲ࡵࣿזࢃஔ۱߄୍ٷ۱߄֨ TableA ИѸЉ֨ TableB չ TableC Иङ੧ܬԕޢ 16澝પ select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) Օљऀйખִ߃੧ࠣ,߃ࢽ୦фսӣߤ,ܷॠऩۨ働դ,ঈঈ.) ঝѷ߽ङऀࡣ)ێङ۱߄Ғڣङઓםङ a ߂ځثИ݇ރ▲ু b қबգङ֨ܫଣޢ 15澝પ select top 10 * form table1 where ਸ֠ ڣӹ 10 ߚઓޢ 14澝પ ࠼پ࠼ order by a.࠼ = a.П୰a,੮դ b where b.П୰ ࠼ desc)پ࠼ from ੮դ order by پ࠼,select top 10 b.* from (select top 20 П୰ ӣڀ݇ރ▲ߚ sql ધՏݝؔޢ 13澝પ ,getdate())>5ޞڐ where datediff(’minute’,f؍३ޙ SQL: select * from ݕӹмӣ୕ݕ؍३ޙޢ 12澝પ a.a=d.d where ..... select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on ֚੮৻߲ொޢ 11澝પ delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) ێҼ৻੮ͫӭஔП੮Иٝ֨Ԁ੮И࡚߄ङҒڙДޢ 10澝પ select * from table1 where a [not] in (‘қ 1’,’қ 2’,’қ 4’,’қ 6’) ࡣސin ङ҅ऀޢ 9澝પ қ 2ރ қ 1 andރ select a,b,c, from table1 where a not between select * from table1 where time between time1 and time2 ܬдଆउқ,not between Љԕܬԕޞਸ֠݇ރbetween ङऀࡣ,between ஒӲ߲તޢ8澝પ Ұद B 2 ऄ A 1 ऄ A 1 type vender pcs ުॐৈߧ group by type then pcs else 0 end),sum(case vender when ’B’ then pcs else 0 end) FROM tablename select type,sum(case vender when ’A’ then pcs else 0 end),sum(case vender when ’C’ ঝѷ select Иङ case澞 ͫܫଣ୍ךҎؘ֪࣫ސ࠼ӧͫcase Օљ ࠼ ͫљ type ӧॐ type澝vender澝pcsޢ 22澝પ select name from syscolumns where id=object_id(’TableName’) ӧӟ੮ୌङ۱߄ङޢ21澝પ select name from sysobjects where type=’U’ ୌ۱߄ङ੮դڀ݇ރӧӟޢ 20澝પ col1,col2,...) Delete from tablename where id not in (select max(id) from tablename group by ڣઓזӭஔ୍ޢ 19澝પ select newid() ڣઓܫசߑଣޢ 18澝પ select top 10 * from tablename order by newid() ݇ރசߑՈӟ 10 ߚޢ 17澝પ select a from tableA ) except (select a from tableB) except (select a from tableC)) ਰङڀ݇ރ࣪▲ଲڲ҂ङ RNumber ঈй 495ͫТײङ▲ֲїु澞ҝڐं THEN Ҽ୰ ੧ۼߛࡹડ ID ङқ澝ࠒ߲ҿީիԙ RNumber澞ࢠૡߚўङઢؼڣ澞ࣀեͫ҂ଲԷ▲ઓͨރۈ ङڣӄઓڀ݇ރҝગ 500 ؼީͧރࣲઆ澞үͫ҂Ոӟ 1 ӱ 500 ਸ֠Фӄङ▲Зசߑޣؠڮ Wend objRec.MoveNext end if ੧ߎ ...ۼ... ୌީ If objRec("ID") = RNumber THEN While Not objRec.EOF RNumber = Int(Rnd*499) +1 Randomize ࣪ڲЈ۱ॐङײॹڏࠄީސङઆӐٯࣀե۸ԭӟߛ澞ؘஎЇ”ރЗசߑ܇“ࡌ ASP дп澞҂ЉਈۣןਈোӟЇவङݼߧͫѸ؉ћՕਈۅࣔރߛપͫசߑ݇ރИङڀ݇ރҥ֨ث ପଋ SQL ધՏؘ࣫ͨͫރࡣͧ҅ऀ Randomize ӡސङڣઓڀ݇ރܫசߑଣ select top 5 * from (select top 15 * from table order by id asc) table_Ӱդ order by id desc ڣђ 10 ӱ 15 ङઓܫଣޢ 24澝પ TRUNCATE TABLE table1 Ӭԗ੮ table1ޢ 23澝પ ۴ߑ C 3 ۴ߑ B 3 Ұद A 2 RNumber2 & " OR ID = " & RNumber3 SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & ڣङઓރԙпڱੂڀ݇ރ ࣀե߲તͫރҥІЗசߑݹ֨գ▲ recordset ӄͫ҂Օљٷڣङઓܫ ОдՈӟәߚசߑଣ ӑд澞ەय़ ЇவДثځ ЈؼՕљऀ SQL▲ي۽Їவङ߶ӕ Random ॐ҆܋澞ڣऀ▲ؔசߑਸ֠ӄङઓৱ ۪ڣߚசߑઓךङ߂ե▲ࢤͫТ҂Օਈѫ▲ࠩՈӟރRandom ӡ ٴ㴹ڶ࣫֨҂ЈؔӐ ރӇળசߑ 澞ޞࣲהѺдםםؼ ߽ڣӱ҂ङઓ܇ؼਈڽڮ澞Recordset ࡚߄ԕխҿѕӄؠͫ֜࠴҂ڣՕܷݱҁ“சߑ”ઓ љЇїुङٗҁࢠ۞ͫ҂ਘثӑԯՕ澞Ց҂ەӊӟ RNumber չ IDͫ҂Ցࠒ߲ԙڷ Љ Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email") set objRec = ObjConn.Execute(SQL) SQL = "SELECT * FROM Customers WHERE ID = " & RNumber RNumber = Int(Rnd*499) + 1 Randomize ॐ Ј۱ײङ recordsetͫڣЗՑԕխથઓ▲ڐЌ۸ٷڣӟӕेङઓ܇֪ڽڮ ऀ SQLͫ҂ؼՕљ ҘЉؼ࠸ؔд;ޞ澞ڣӄؼԕխдۨԞЇЅߚઓڀ݇ރЗ▲֨ٯдͫեৱପڀ݇ ރֺشࠄଐީЗސдпͫѸबࡁО६֡ङѣЏઆӐםरߛރՕؼд澞ࣀ 500 Зޞ ӣОљڔࡣङ҅ऀࠀސதͫથڣ੧եީիଏ֛ઓۼ੧ SQL ધՏ澞߿݇ SQL ધՏۼࡣऀйސ થ ࡣސExecute ޢ Conn.Execute પ SELECT iif(len(field)>2,left(field,2)+’...’,field) FROM tablename;ڀ݇ރ Access news_name,news_id from tablename select case when len(field)>10 then left(field,10)+’...’ else field end asڀ݇ރ SQL ધࡣ অКުॐ ҅ऀ SQL ધՏ ऀ...ї߁ଋङ table1.fd1,table2.fd1 where ... ધࡣ select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on ե߲)וՑ߄ਘٜࡹડ, ࣫֨ઓЈљ,ޢ ԉэТୃ࡚߄,ৠЇ࡚߄ Access ङ SQL પٮ Access,ݎՇऀ٘କڐધࡣ(߂ݎ Access ٘କ mysql select * From ੮դ Order By rand() Limit n Sql server:select top n * from ੮դ order by newid() Access ધࡣSELECT top 10 * From ੮դ ORDER BY Rnd(id) ࡹડଋͫڣߚઓٴ சߑયՈਲ਼ 澞ڣՇઓٷӄީի߄ 9 ߚڀ݇ރ੧ऩङЉީࠒ߲ۼ ࡨ۞љЇїुङ RNumber & "+ 9" SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber & " AND " & SELECT ધՏՑުॐ▲य़Օਈͧୌङ ID ީਘԈࣿۨङ՚ुͨ ߛ؏ۨͫѸީڔސәय़ױ澞▲ݱҁՕљପଋڣङଧઓރڢչଠڣଣӟআ▲ߚઓڔ؆ঈރৱ ङӧ੮ͨͫ҂Օљऀ BETWEEN ۪ݎङ 10 ߚޞЭકީࠩவૺͧڣଣӟ 10 ߚઓ҂ײҝ 2006/10/26 13:46 ҶতםSQL ધՏ 澞ێҒ ͫї੮▲З୪નث 澞Error தՠИङ▲З Errorૂעࣲה߄୪નՇࣿͫзԇޢЉО 0ͫөપރЗ ङ Error தՠߛؘ࣫ͫਲ਼ Error தՠङۨմثݎࣲީիۨԅͫՕପଋକהзԇލࣲङધՏ澞Ӯהԇ չৈߘͫ֨ДЗФङધՏͫؼީҁОзڐ BeginTrans չ CommitTrans ऀй߶ઓзԇङ 澞ہୌӹङࣙהӱזۊٷͫૂעࣲؼהЗޅөͫૂע੧ۼҿИ߄▲ЗધՏ ࣲ۵ۨԅͺਲ਼ה੧եͫзԇۼѽͫՑ߄ҶୂધՏୃۨԅޅݱҁંО▲Зু▲رࣲՕљה зԇ ࣲ澞הࣲৈߧͫԯेઍзԇङהRollbackTrans ऀй֛࢞зԇͺCommitTrans ऀйݕс۱߄ङзԇ ▲Зз࣒ͺڐࡣ澞BeginTrans ऀйސࣲङהݕ҈ङऀйзԇثݎࡣީକސ ІЗ ࡣސ ·BeginTrans澝RollbackTrans澝CommitTrans ੧ݼ澞ۼՕ҅ͫރߎ澞ପଋܶؔથՀކঅઆୋОոјФեङআ▲З ࡣސ Execute رથځО adCMDTextͫ؉ऀйճઝ ADOͫٯङՈқପރ ·Option ՕଣாͫથՀ ੧дݱҁ澞ڣߚઓصךӱથՊИ澞ପଋઘથՊͫؼՕऽଳ SQL ધՏґ ѫਘԈރڣ੧եͫ۱ࣿݼङઓۼ ·RecordAffected ОՕଣாͫ࠴ӟՕݹ▲ЗՊͫSQL ધՏ ધՏ" [, RecordAffected][, Option]ۅ.Execute "SQL ݱҁثݎ କ ऀࡣОޞதङଏ֛澞࠴ڣ࡚߄ઓͫޞધઈۅ੧ SQL ङݱҁۼ 2ͭ 澞ثதڣՊؼї੮дથઓثљեͫґثதૐোܶؔङڣઓرࡣͫސ Иͫପଋ Setثڣҥ֨થઓ߲તৈߧرٷͫثதڣઓڏࡣલऀեͫѫਘԈӫސ Execute .Execute("SQL ߲તધઈ")ثݎՊդ=କث Set த澞ऀࡣОڣӱङઓڱଏ֛߲તرͫޞ੧ SQL ߲તધՏۼ 1ͭ ЈДय़ (update a set a.lastname=(select b.lastname from b where a.id=b.id үશશд!!!! ࠼ީबлҼ৻ङ澞 Иङ ID ङѹ澞ДЗ੮ځҵӱ A ੮И LASTNAME ब݇ރङځ࠼ङब ੮ BИङ LASTNAME܋࣫֨ ङޅ؏ީ݇ރ࠼ङ੮ BИԽߛ ID,LASTNAME ДЗ ङޅ؏ީ݇ރ࠼ङ੮ A ИԽߛ ID,FIRSTNAME ДЗ ੮ B( ID,LASTNAME) ੮ A ( ID ,FIRSTNAME,LASTNAME ) Иڀ݇ރ ORACLE ֨ ͠͠͠͠͠͠͠͠͠͠͠ ۄ ߄Ҽ UPDATEͫ UPDATE ΐΐ291911320 澞ږՃফۅ࠼ًײࣀґऊͫ҆ ёۅѫੴӭஔͺ੮ङৈߣљՃ੮ङ۱߄ً݇ރऀ DELETEͫՑ߄҅ڢԾ੮ङৈߣ澞Љգङީעѫر ࡣӭஔ੮ͫސЗ੮澞Љଋͫਲ਼ऀय़ޅڒИݹڀ݇ރࡣЊ▲З DROP ધՏђސ Օљ҅ऀ Execute 澞ڔથੴӭஔङ੮ଇځڣऀйܶؔքпઓͫڔО▲З੮ଇރcriteria Հ ङ੮ङդ०澞ڣऀйܶؔђҿИӭஔઓރtable Հޢપ ’ધࡣDELETE * FROM table WHERE criteria=’߲તङ ધࡣDELETE [table.*] FROM table WHERE criteria 澞ڣЗઓךЌથՏࢠૡ WHERE ՏИङߚўͫՕљ҅ऀ DELETE ӭஔ ͫڣЗ੮ИӭஔઓךЗӭஔ߲તͫՕђӧ֨ FROM ՏФИङ▲З۪▲ڏDELETE ધՏऀйӫ DELETE ધՏ ऀӱ؉澞ر ੮ͫ۩ћ֨եவङ҆Иڙۑ澞બࣅڣЗࣔؔङմઓ▲ځثEMPLOYEES ङ੮澞ҿИङ▲੧ 澞֣֨ 1 И۩ћোӟд▲ЗդОޢ؉ћ੧▲▲પث֨࠴ڷऀӱङͫ۩ћ߄ٯۯSQL ऀ ރךם SQLИ߄֚य़ׂߎङ DML ݱҁINSERTͫSELECTͫUPDATE չ DELETE澞ंйީ ࣲ澞הݱҁધઈͧDMLͨધՏ੧݇ރ—澞пٗҁंՐ▲ঝ SQL ધՏ݇ރИؘஎङ ڀ݇ރࣲהЉٷࣲͫީ֜О DDL ધՏה੮Иङ੧੧ثЉٷ੮澝ӧչં੧ؔУ澞؉ћײثڀ ݇ރث۱ॐͨ澞DDL ધՏ 1 ֣ײЗ੮ͧ▲ڏФӹͫү҅ऀ CREATE TABLE ધՏߛӫڐ ֨۩ћ ީতପ SQL д澞ڐުࣀ҂ٝ ҂؏ۨп؆ЮեͫڢઈљՃІқମ૾澞ލܫԅਈ—ߛ҉ࠩѐࡁૻݱҁঅ澝ଣڶધՏ—SQL ङ߾ ݱҁ݇ރ҂ݗ֚ߚ߂ׂߎङحږرИͫ۩ћކ澞֨ߎئ ङؗીۯऀڀ݇ރީ ীݗ SQL ݱҁધՏInsertͫSelectͫUpdate չ Delete澞݇ރݗ SQL ֚ߚ߂ׂߎङ