sqlserver語(yǔ)句大全

1、Sql server 語(yǔ)句大全創(chuàng)建數(shù)據(jù)庫(kù)創(chuàng)建之前判斷該數(shù)據(jù)庫(kù)是否存在if exists (select * from sysdatabases where name='databaseName') drop database databaseNamegoCreate DATABASE databasename刪除數(shù)據(jù)庫(kù)drop database dbname備份 sql server-創(chuàng)建備份數(shù)據(jù)的deviceUSE masterEXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backu

2、p'-開(kāi)始備份BACKUP DATABASE pubs TO testBack創(chuàng)建新表create table tabname(col1 type1 not null primary key,col2type2 not null,.)根據(jù)已有的表創(chuàng)建新表：A： gouse原數(shù)據(jù)庫(kù)名goselect * into目的數(shù)據(jù)庫(kù)名.dbo.目的表名from 原表名(使用舊表創(chuàng)建新表)B： create table tab_new as select col1,col2 from tab_old definition only創(chuàng)建序列create sequence SIMON_SEQUENCEm

3、invalue 1 -最/4值maxvalue 999 -最大值start with 1 -開(kāi)始值increment by 1 - 每次加幾cache 20;刪除新表drop table tabname增加一個(gè)列Alter table tabname add colname coltype刪除一個(gè)列Alter table tabname drop column colname添加主鍵Alter table tabname add primary key(col)說(shuō)明： 刪除主鍵： Alter table tabname drop primary key(col)創(chuàng)建索引create uniqu

4、e index idxname on tabname(col 。)刪除索弓 I: drop index idxname on tabname注：索引是不可更改的，想更改必須刪除重新建。創(chuàng)建視圖create view viewname as select statement刪除視圖： drop view viewname簡(jiǎn)單基本的sql語(yǔ)句(1)數(shù)據(jù)記錄篩選：sql="select * from 數(shù)據(jù)表 where 字段名=字段值order by 字段名 desc"sql="select * from 數(shù)據(jù)表 where 字段名 like '%字段值 

5、9; order by 字段名desc"sql="select top 10 * from 數(shù)據(jù)表 where 字段名=字段值 order by 字段名desc"sql="select top 10 * from 數(shù)據(jù)表 order by 字段名desc"sql="select * from 數(shù)據(jù)表 where 字段名 in ('值 1','值 2','值 3'）"sql="select * from 數(shù)據(jù)表 where 字段名 between 值 1 and 值 2

6、"（2）更新數(shù)據(jù)記錄：sql="update 數(shù)據(jù)表set 字段名二字段值 where 條件表達(dá)式"sql="update 數(shù)據(jù)表set 字段1=值1,字段2=值2 字段n=W nwhere條件表達(dá)式"（3）刪除數(shù)據(jù)記錄：sql="delete from 數(shù)據(jù)表where 條件表達(dá)式"sql="delete from 數(shù)據(jù)表"（將數(shù)據(jù)表所有記錄刪除）（4）添加數(shù)據(jù)記錄：值2,值3)" sql="insert intosql="insert into 數(shù)據(jù)表（字段1,字段2,字

7、段3）values （ 值1,目標(biāo)數(shù)據(jù)表select * from源數(shù)據(jù)表"（把源數(shù)據(jù) 表的記錄添加到目標(biāo)數(shù)據(jù)表 ）（5）數(shù)據(jù)記錄統(tǒng)計(jì)函數(shù)：AVG序段名）得出一個(gè)表格欄平均值COUNT（*;字段名）對(duì)數(shù)據(jù)行數(shù)的統(tǒng)計(jì)或?qū)δ骋粰谟兄档臄?shù)據(jù)行數(shù)統(tǒng)計(jì)MAX仔段名）取得一個(gè)表格欄最大的值MIN（字段名）取得一個(gè)表格欄最小的值SUM仔段名）把數(shù)據(jù)欄的值相加引用以上函數(shù)的方法：sql="select sum（ 字段名）as 別名from 數(shù)據(jù)表 where 條件表達(dá)式 set rs=（sql）用rs（"別名”）獲取統(tǒng)計(jì)的值，其它函數(shù)運(yùn)用同上。查詢?nèi)コ貜?fù)值：select dis

8、tinct * from table1（5）數(shù)據(jù)表的建立和刪除：CREATE TABLE數(shù)據(jù)表名稱（字段1類型1（長(zhǎng)度），字段2類型2（長(zhǎng)）（6）單列求和：SELECT SUM存段名）FROM數(shù)據(jù)表幾個(gè)高級(jí)查詢運(yùn)算詞A: UNION運(yùn)算符UNION運(yùn)算符通過(guò)組合其他兩個(gè)結(jié)果表(例如 TABLE1和TABLE2并 消去表中任何重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng)ALL隨UNION一起使用時(shí)(即UNIONALL),不消除重復(fù)行。兩種情況下，派生表的每一行不是來(lái)自TABLE1就是來(lái)自TABLE2B: EXCEPT運(yùn)算符EXCEPT運(yùn)算符通過(guò)包括所有在TABLE1中但不在TABLE2中的行并消除所有重復(fù)行而派

9、生出一個(gè)結(jié)果表。當(dāng) ALL隨EXCEPT一起使用時(shí)(EXCEPT ALL),不消除重復(fù)行。C: INTERSECT運(yùn)算符INTERSECT運(yùn)算符通過(guò)只包括 TABLE1和TABLE2中都有的行并消除所 有重復(fù)行而派生出一個(gè)結(jié)果表。當(dāng) ALL隨INTERSECT一起使用時(shí) (INTERSECT ALL),不消除重復(fù)行。注：使用運(yùn)算詞的幾個(gè)查詢結(jié)果行必須是一致的。使用外連接A、left outer join左外連接(左連接)：結(jié)果集既包括連接表的匹配行，也包括左連接表的所有行。SQL: select , , , , , from a LEFT OUT JOIN b ON =B: right out

10、er join:右外連接(右連接)：結(jié)果集既包括連接表的匹配連接行，也包括右連 接表的所有行。C: full outer join：全外連接：不僅包括符號(hào)連接表的匹配行，還包括兩個(gè)連接表中的所 有記錄。編輯本段判斷對(duì)象是否存在判斷數(shù)據(jù)庫(kù)是否存在if exists ( select * from sysdatabases where name =' 數(shù)據(jù)庫(kù)名 ') dropdatabase 數(shù)據(jù)庫(kù)名判斷表是否存在if not exists (select * from sysobjects where name='表名'and xtype='U')

11、begin-這里創(chuàng)建表end判斷存儲(chǔ)過(guò)程是否存在if exists ( select * from sysobjects whereid = object_id(N' 存 儲(chǔ)過(guò)程名')and OBJECTPROPERTY(id, N'IsProcedure') = 1)dropprocedure 存儲(chǔ)過(guò)程名判斷臨時(shí)表是否存在if object_id('tempdb.# 臨時(shí)表名')is not nulldroptable #臨時(shí)表名判斷視圖是否存在-SQL Server 2000IF EXISTS ( SELECT FROMsysviews WH

12、EREject_id = 'dbo. 視 圖名-SQL Server 2005IF EXISTS ( SELECT FROM WHERSbjectJd = 'dbo. 視圖名' 判斷函數(shù)是否存在if exists (select * from where id = object_id(N'dbo. 函數(shù)名') and xtype in (N'FN', N'IF', N'TF')dropfunction dbo.函數(shù)名獲取創(chuàng)建信息SELECT nam,id,crdateFROMsysobjects where

13、 xtype='U'/*xtype的表示參數(shù)類型，通常包括如下這些C = CHECK勺束D =默認(rèn)值或DEFAULT勺束F = FOREIGNKEY勺束L =日志FN =標(biāo)量函數(shù)IF =內(nèi) 嵌表函數(shù)P = 存儲(chǔ)過(guò)程 PK = PRIMARYKEY勺束(類型是 K) RF =復(fù)制篩 選存儲(chǔ)過(guò)程 S =系統(tǒng)表TF =表函數(shù)TR =觸發(fā)器U =用戶表UQ = UNIQUE 約束(類型是K) V =視圖X =擴(kuò)展存儲(chǔ)過(guò)程*/判斷列是否存在if exists（ select * from syscolumns where id=object_id（' 表名'）and na

14、me='列名'）altertable 表名 dropcolumn 歹！J名判斷列是否自增列if columnproperty（objectJd（'table'）,'col','IsIdentity'）=1print ' 自增列elseprint ' 不是自增列SELECT FROM WHEREjectJd=OBJECT_ID（'表名'）AND is_identity=1判斷表中是否存在索引if exists（ select * from sysindexes where id=object_id（&

15、#39; 表名'）and name='索引名'）print '存在'elseprint ' 不存在查看數(shù)據(jù)庫(kù)中對(duì)象SELECT FROMsysobjects WHEREname對(duì)象名'select * from table（所要查詢的表名）where coloum（ 條件）編輯本段提升復(fù)制表（只復(fù)制結(jié)構(gòu)，源表名：a新表名：b） （Access 可用）法一 ： select * into b from a where 1<>1法二：select top 0 * into b from a拷貝表（拷貝數(shù)據(jù)，源表名：a目標(biāo)表名：b）

16、 （Access 可用）insert into b（x, y, z） select d,e,f from a;跨數(shù)據(jù)庫(kù)之間表的拷貝(具體數(shù)據(jù)使用絕對(duì)路徑)(Access 可用)insert into b(x, y, z) select d,e,f from a in '具體數(shù)據(jù)庫(kù)' where 條件例子：。.from b in ”'&("."&"" &"' where.子查詢(表名1: a表名2： b)select a,b,c from a where a IN (select d fro

17、m b或者： selecta,b,c from a where a IN (1,2,3)顯示文章最后時(shí)間select , from table a,(select max(adddate) adddate from table where = b外連接查詢(表名1: a表名2： b)select , , , , , from a LEFT OUT JOIN b ON =在線視圖查詢(表名1： aselect * from (Select a,b,c FROM a) T where > 1;between 的用法between為查詢某字段的指定范圍，限制查詢數(shù)據(jù)范圍時(shí)包括了邊界值，not

18、between 不包括邊界值select * from table1 where time between time1 and time2select a,b,c, from table1 where a not between數(shù)值 1 and 數(shù)值2in的使用方法select * from table1 where a not in ('值 1','值 2','值4','值 6')刪除主表沒(méi)有的信息兩張關(guān)聯(lián)表 delete from tablel where not exists ( select * fromtable2 wh

19、ere =四表聯(lián)查問(wèn)題select * from a left inner join b on = right inner join c on = inner join d on = where .日程安排提前五分鐘SQL: select * from 日程安排 where datediff('minute',f 開(kāi)始時(shí)間， getdate()>5一條sql搞定數(shù)據(jù)庫(kù)頁(yè)select top 10 b.* from (select top 20 主鍵字段，排序字段 from 表 名order by排序字段 desc) a, 表名b where b.主鍵字段=a.主鍵字段or

20、der by a.排序字段前10條記錄select top 10 * from tablel where范圍選擇排名選擇在每一組b值相同的數(shù)據(jù)中對(duì)應(yīng)的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜，每月熱銷產(chǎn)品分析，按科目成績(jī)排名，等等。)select a,b,c from tablename ta where a=(select max(a) from tablename tb where =派生結(jié)果表包括所有在TableA中但不在TableB和TableC中的行并消除所有重 復(fù)行而派生出一個(gè)結(jié)果表(select a from tableA except (select a f

21、rom tableB) except (select a from tableC)隨機(jī)取出10條數(shù)據(jù)select top 10 * from tablename order by newid()隨機(jī)選擇記錄select newid()刪除重復(fù)記錄Delete from tablename where id not in (select max(id) fromtablename group by col1,col2，)列出數(shù)據(jù)庫(kù)里的表名select name from sysobjects where type='U'列出表里的所有的select name from sysco

22、lumns where id=object_id('TableName')列示排列列示type、vender > pcs字段，以type 字段排列，case可以方便地實(shí) 現(xiàn)多重選擇，類似 select 中的case。select type,sum(case vender when 'A' then pcs else 0end),sum(case vender when 'C' then pcs else 0 end),sum(case venderwhen 'B' then pcs else 0 end) FROM tabl

23、ename group by type顯示結(jié)果：type vender pcs電腦A 1電腦A 1光盤(pán)B 2光盤(pán)A 2手機(jī)B 3手機(jī)C 3初始化表tableiTRUNCATE TABLE tablei選擇從10到15的記錄select top 5 * from (select top10 * from (select top 15 * fromtable order by id asc) table_ 別名 order by id desc) table_2 order by id數(shù)據(jù)類型轉(zhuǎn)換declare numid intdeclare id varchar(50)set numid=20

24、05set id=convert(varchar,numid)通過(guò)上述語(yǔ)句完成數(shù)據(jù)類型Int轉(zhuǎn)換成varchar ,其他轉(zhuǎn)換類似，可參看convert 函數(shù)編輯本段技巧1=1, 1=2的使用在SQL語(yǔ)句組合時(shí)用的較多“where 1=1”是表示選擇全部“where 1=2”全部不選，如：if strWhere !='beginset strSQL = 'select count(*) as Total from ' + tblName + 'where ' + strWhereendelsebeginset strSQL = 'select co

25、unt(*) as Total from ' + tblName + '' end我們可以直接寫(xiě)成set strSQL = 'select count(*) as Total from ' + tblName + ' where 1=1 and '+ strWhere收縮數(shù)據(jù)庫(kù)- -重建索引DBCC REINDEXDBCC INDEXDEFRAG- -收縮數(shù)據(jù)和日志DBCC SHRINKDBDBCC SHRINKFILE壓縮數(shù)據(jù)庫(kù)dbcc shrinkdatabase(dbname)轉(zhuǎn)移數(shù)據(jù)庫(kù)給新用戶以已存在用戶權(quán)限exec sp_cha

26、nge_users_login 'update_one','newname','oldname'go檢查備份集RESTORE VERIFYONLY from disk='E:'修復(fù)數(shù)據(jù)庫(kù)Alter DATABASE dvbbs SET SINGLE_USERGODBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCKGOAlter DATABASE dvbbs SET MULTIUSERGO日志清除SET NOCOUNT ONDECLARE LogicalF

27、ileName sysname,MaxMinutes INT,NewSize INTUSE tablename -要操作的數(shù)據(jù)庫(kù)名Select LogicalFileName = 'tablename_log', -B_志文件名MaxMinutes = 10, - Limit on time allowed to wrap log.NewSize = 1 -你想設(shè)定的日志文件的大小(M)- -Setup / initializeDECLARE OriginalSize intSelect OriginalSize = sizeFROM sysfilesWhere name =

28、LogicalFileNameSelect 'Original Size of ' + db_name() + ' LOG is ' +CONVERT(VARCHAR(30),OriginalSize) + ' 8K pages or ' +CONVERT(VARCHAR(30),(OriginalSize*8/1024) + 'MB'FROM sysfilesWhere name = LogicalFileNameCreate TABLE DummyTrans(DummyColumn char (8000) not null)D

29、ECLARE Counter INT, StartTime DATETIME, TruncLog VARCHAR(255) Select StartTime = GETDATE(), TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' DBCC SHRINKFILE (LogicalFileName, NewSize) EXEC (TruncLog)-Wrap the log if necessary.WHILEMaxMinutes > DATEDIFF(mi, StartTime, G

30、ETDATE()- time has not expiredAND OriginalSize = (Select size FROM sysfiles Where name = LogicalFileName)AND (OriginalSize * 8 /1024) > NewSizeBEGIN - Outer loop.Select Counter = 0WHILE (Counter < OriginalSize / 16) AND (Counter < 50000)BEGIN - updateInsert DummyTrans VALUES ('Fill Log')Delete DummyTransSelect Counter = Counter + 1ENDEXEC (TruncLog)ENDSelect 'Final Size of '