SQL经典语句大全.doc

1create database database-name2drop database dbname3sql server- deviceuse masterexec sp_addumpdevice disk, testback, c:mssql7backupmynwind_1.dat- backup database pubs to testback4create table tabname(col1 type1 not null primary key,col2 type2 not null,.) acreate table tab_new like tab_old ()bcreate table tab_new as select col1,col2? from tab_old definition only5drop table tabname6alter table tabname add column col typedb2varchar7 alter table tabname add primary key(col) alter table tabname drop primary key(col)8create unique index idxname on tabname(col?.)drop index idxname9create view viewname as select statementdrop view viewname10sqlselect * from table1 where insert into table1(field1,field2) values(value1,value2)delete from table1 where update table1 set field1=value1 where select * from table1 where field1 like %value1% -like! select * from table1 order by field1,field2 descselect count as totalcount from table1select sum(field1) as sumvalue from table1select avg(field1) as avgvalue from table1select max(field1) as maxvalue from table1select min(field1) as minvalue from table111a union union table1 table2 all union union all table1 table2b except except table1 table2 all except (except all)c intersect intersect table1 table2 all intersect (intersect all)12aleft outer joinsql: select a.a, a.b, a.c, b.c, b.d, b.f from a left out join b on a.a = b.cbright outer join:()cfull outer join1(,a b) (access)select * into b from a where 1<>1select top 0 * into b from a2(,a b) (access)insert into b(a, b, c) select d,e,f from b;3() (access)insert into b(a, b, c) select d,e,f from b in where .from b in &server.mappath(.)&data.mdb & where.4(1a 2b)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)5select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b6(1a 2b)select a.a, a.b, a.c, b.c, b.d, b.f from a left out join b on a.a = b.c7(1a )select * from (select a,b,c from a) t where t.a > 1;8between,between,not betweenselect * from table1 where time between time1 and time2select a,b,c, from table1 where a not between 1 and 29in select * from table1 where a not in (1,2,4,6)10delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )11select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .12sql: select * from where datediff(minute,f,getdate()>513sql select top 10 b.* from (select top 20 , from order by desc) a, b where b. = a. order by a.1410select top 10 * form table1 where 15ba(,.)select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)16 tablea tablebtablec (select a from tablea ) except (select a from tableb) except (select a from tablec)1710select top 10 * from tablename order by newid()18select newid()19delete from tablename where id not in (select max(id) from tablename group by col1,col2,.)20select name from sysobjects where type=u / u21select name from syscolumns where id=object_id(tablename)22typevenderpcstypecaseselect caseselect type,sum(case vender when a then pcs else 0 end),sum(case vender when c then pcs else 0 end),sum(case vender when b then pcs else 0 end) from tablename group by type type vender pcs a 1 a 1 b 2 a 2 b 3 c 323table1truncate table table1241015select top 5 * from (select top 15 * from table order by id asc) table_ order by id desc11=11=2sqlwhere 1=1 where 1=2if strwhere !=beginset strsql = select count(*) as total from + tblname + where + strwhere endelsebeginset strsql = select count(*) as total from + tblname + endset strsql = select count(*) as total from + tblname + where 1=1 + strwhere2-dbcc reindexdbcc indexdefrag-dbcc shrinkdbdbcc shrinkfile3dbcc shrinkdatabase(dbname)4exec sp_change_users_login update_one,newname,oldnamego5restore verifyonly from disk=e:dvbbs.bak6alter database dvbbs set single_usergodbcc checkdb(dvbbs,repair_allow_data_loss) with tablockgoalter database dvbbs set multi_usergo7set nocount ondeclare logicalfilename sysname,maxminutes int,newsize intusetablename - selectlogicalfilename = tablename_log,- maxminutes = 10, - limit on time allowed to wrap log. newsize = 1 - (m) - setup / initializedeclare originalsize intselect originalsize = sizefrom sysfileswhere name = logicalfilenameselect original size of + db_name() + log is +convert(varchar(30),originalsize) + 8k pages or + convert(varchar(30),(originalsize*8/1024) + mbfrom sysfileswhere name = logicalfilenamesql()sql 2009-06-08 12:12mysql transact-sqlfromwheretesttablenicknameemail:select nickname,emailfrom testtablewhere name=() (select_list)()1testtable:select * from testtable2:select nickname,email from testtable3= :select =nickname,=email from testtable4selectalldistinctalldistinctselect5top n percenttop nntop n percentn:select top 2 * from testtable:select top 20 percent * from testtable() fromfromselectfrom256fromusertablecitytablecityidcityid:select username,citytable.cityidfrom usertable,citytablewhere usertable.cityid=citytable.cityidfrom: as :select username,b.cityidfrom usertable a,citytable bwhere a.cityid=b.cityidselect:select a.au_fname+a.au_lnamefrom authors a,titleauthor ta(select title_id,titlefrom titleswhere ytd_sales>10000) as twhere a.au_id=ta.au_idand ta.title_id=t.title_idselectt() wherewhere20 :select * from usertable where age>20 where ()>>=!>!=10 and age:select * from usertable order by age desc,userid asc unionselectunion:select_statementunion all selectstatementunion all selectstatementnselectstatementselect allunion union1 union (2 union 3)sqlmssqlaccesssqlddl(createalterdropdeclare)dml(selectdeleteupdateinsert),1create database database-name2drop database dbname3sql server- deviceuse masterexec sp_addumpdevice disk, testback, c:mssql7backupmynwind_1.dat- backup database pubs to testback4create table tabname(col1 type1 not null primary key,col2 type2 not null,.)acreate table tab_new like tab_old ()bcreate table tab_new as select col1,col2? from tab_old definition only5drop table tabname6alter table tabname add column col typedb2varchar7alter table tabname add primary key(col)alter table tabname drop primary key(col)8create unique index idxname on tabname(col?.)drop index idxname9create view viewname as select statementdrop view viewname10sqlselect * from table1 where insert into table1(field1,fie