常用经典SQL语句大全完整新版-权威人士总结出的-详解+实例

icescerbo

贡献于2015-08-28

字数:0 关键词: SQL

͹ޢ 5澝પ B͹create table tab_new as select col1,col2… from tab_old definition only ੮)ޏڏ੮ӫޛA͹create 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 ͹֨িં֣߲ત(੮դ 1͹a )ޢ 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 ߲ત(੮դ 1͹a ੮դ 2͹b)ݎକי͹ޢ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) ͹׿߲ત(੮դ 1͹a ੮դ 2͹b)ޢ 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 ધՏ؟ם ҿࠩͫ 澞ڣ੮Иङ۱߄ઓݎДЗକܬ੮ङԙ୆੧ͫଐԕݎঅ՚କܬ͹Љюԕݎକי Ҷ C͹full outer join͹ ੮ङ۱߄੧澞ݎ՘କܬ੧ͫЭԕݎ੮ङԙ୆କݎକܬԕޘ͹ৈߧத(ݎ՘କ)ݎକי ՘ B͹right 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 ֚ߚ߂ׂߎङ

下载文档,方便阅读与编辑

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

需要 8 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档

相关文档