筆記（二） C#sql語句

標籤：access   data   login   bbs   sys   產品   檔案   identity   ddd   

SQL語句大全刪除資料庫drop database databasenameSQL語句大全備份--- 建立備份資料的 deviceUSE masterEXEC sp_addumpdevice ‘disk‘, ‘testBack‘, ‘c:\mssql7backup\MyNwind_1.dat‘--- 開始備份BACKUP DATABASE pubs TO testBackSQL語句大全建立新表create table tabname(col1 type1 [not null] [primary key] identity(起始值,遞增量),col2 type2 [not null],..)--primary key為主鍵 identity表示遞增數量根據已有的表建立新表：A：gouse 原資料庫名goselect * into 目的資料庫名.dbo.目的表名 from 原表名(使用舊錶建立新表)B：create table tab_new as select col1,col2… from tab_old definition onlySQL語句大全建立序列create sequence SIMON_SEQUENCEminvalue 1 -- 最小值maxvalue 999999999999999999999999999 -- 最大值start with 1 -- 開始值increment by 1 -- 每次加幾cache 20;SQL語句大全刪除表drop table tabname--這是將表連同表中資訊一起刪除但是記錄檔中會有記錄SQL語句大全刪除資訊delete from table_name-這是將表中資訊刪除但是會保留這個表SQL語句大全增加列Alter table table_name add column_name column_type [default 預設值]--在表中增加一列，[]內的內容為可選項SQL語句大全刪除列Alter table table_name drop column column_name--從表中刪除一列SQL語句大全添加主鍵Alter table tabname add primary key(col)說明：刪除主鍵：Alter table tabname drop primary key(col)SQL語句大全建立索引create [unique] index idxname on tabname(col…。)刪除索引：drop index idxname on tabname註：索引是不可更改的，想更改必須刪除重建立。SQL語句大全建立視圖create view viewname as select statement刪除視圖：drop view viewnameSQL語句大全基本語句(1) 資料記錄篩選：sql="select * from 資料表 where欄位名=欄位值 order by欄位名[desc]"（按某個欄位值降序排列。預設升序ASC）sql="select * from 資料表 where欄位名like ‘%欄位值%‘ order by 欄位名 [desc]"sql="select top 10 * from 資料表 where欄位名=欄位值 order by 欄位名 [desc]"sql="select top 10 * from 資料表 order by 欄位名 [desc]"sql="select * from 資料表 where欄位名in (‘值1‘,‘值2‘,‘值3‘)"sql="select * from 資料表 where欄位名between 值1 and 值2"(2) 更新資料記錄：sql="update 資料表 set欄位名=欄位值 where 條件運算式"sql="update 資料表 set 欄位1=值1,欄位2=值2 …… 欄位n=值n where 條件運算式"(3) 刪除資料記錄：sql="delete from 資料表 where 條件運算式"sql="delete from 資料表" (將資料表所有記錄刪除)(4) 添加資料記錄：sql="insert into 資料表 (欄位1,欄位2,欄位3 …) values (值1,值2,值3 …)"sql="insert into 目標資料表 select * from 來源資料表" (把來源資料表的記錄添加到目標資料表)(5) 資料記錄統計函數：AVG(欄位名) 得出一個表格欄平均值COUNT(*;欄位名) 對資料行數的統計或對某一欄有值的資料行數統計MAX(欄位名) 取得一個表格欄最大的值MIN(欄位名) 取得一個表格欄最小的值SUM(欄位名) 把資料欄的值相加引用以上函數的方法：sql="select sum(欄位名) as 別名 from 資料表 where 條件運算式"set rs=conn.excute(sql)用 rs("別名") 擷取統計的值，其它函數運用同上。查詢去除重複值：select distinct * from table1(6) 資料表的建立和刪除：CREATE TABLE 資料表名稱(欄位1 類型1(長度),欄位2 類型2(長度) …… )(7) 單列求和:SELECT SUM(欄位名) FROM 資料表  最新查詢 查詢資料庫中含有同一這欄位的表：select name from sysobjects where xtype = ‘u‘ and id in(select id from syscolumns where name = ‘s3‘)根據出生日期可以算出年齡：select datediff(year,scrq,‘2013‘) as 年齡 from page_shsjgrgl根據當前年份自動算出年齡select datediff(year,csny,cast(YEAR(GETDATE()) as char))年select year(djsj) from page_shsjgrgl月select month(djsj) from page_shsjgrgl日select day(djsj) from page_shsjgrgl在同一資料庫中複製表結構：select * into a from b where 1<>1當 IDENTITY_INSERT 設定為 OFF 時，不能為表 ‘aa‘ 中的識別欄位插入顯式值。set identity_insert aa ON----設定開啟，批量插入：insert into aa(Customer_ID, ID_Type, ID_Number) select Customer_ID, ID_Type, ID_Number from TCustomer;set identity_insert aa OFF－－－關閉不同資料庫之間的複製：複製結構：select * into test.dbo.b from GCRT.dbo.page_shsjgrgl where 1<>1複製內容：insert into test.dbo.b(xm,ssdq) select xm,ssdq from GCRT.dbo.page_shsjgrgl查看資料庫中所有的資料表表名：select name from SysObjects where type=‘u‘查看資料庫中所有表含有同一欄位的表：select name from sysobjects where xtype = ‘u‘ and id in(select id from syscolumns where name = ‘同一欄位‘)查看資料表中的所有欄位：select name from Syscolumns where id=object_id(‘表名‘)查詢資料庫時前10條記錄：select top 10 * from td_areacode order by newid()修改欄位類型：ALTER TABLE 表名 ALTER COLUMN 欄位名 varchar(30) NOT NULLuse ZHJIANGJGYLdeclare @temp nvarchar(30)set @temp = ‘ZWI4‘select hllx from page_yljg_zyry where hllx not in(selectcase @temp when ‘‘then ‘‘else b1 endfrom (select * from TD_Code where page_en=‘page_yljg_zyry‘ and B2=‘ZWI‘) s where s.b1 !=case @temp when ‘‘ then ‘‘ else @temp end)更改資料庫表欄位類型：alter table page_shsjgrgl alter column s1 intSQL語句大全進階查詢A：UNION運算子UNION 運算子通過組合其他兩個結果表（例如TABLE1 和TABLE2）並消去表中任何重複行而派生出一個結果表。當 ALL 隨UNION 一起使用時（即UNION ALL），不消除重複行。兩種情況下，派生表的每一行不是來自TABLE1 就是來自TABLE2。B： EXCEPT運算子EXCEPT 運算子通過包括所有在TABLE1 中但不在TABLE2 中的行並消除所有重複行而派生出一個結果表。當ALL 隨EXCEPT 一起使用時(EXCEPT ALL)，不消除重複行。C：INTERSECT運算子INTERSECT 運算子通過只包括TABLE1 和TABLE2 中都有的行並消除所有重複行而派生出一個結果表。當ALL 隨INTERSECT 一起使用時(INTERSECT ALL)，不消除重複行。註：使用運算詞的幾個查詢結果行必須是一致的。SQL語句大全外串連A、left outer join：左外串連（左串連）：結果集既包括串連表的匹配行，也包括左串連表的所有行。SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.cB：right outer join:右外串連(右串連)：結果集既包括串連表的匹配串連行，也包括右串連表的所有行。C：full outer join：全外串連：不僅包括符號串連表的匹配行，還包括兩個串連表中的所有記錄。  判斷對象 判斷資料庫是否存在if exists ( select* fromsysdatabases where name= ‘資料庫名‘) drop database[資料庫名] 判斷表是否存在if not exists (select * from sysobjects where [name] = ‘表名‘ and xtype=‘U‘)begin--這裡建立表end 判斷預存程序是否存在if exists ( select* fromsysobjects whereid = object_id(N‘[預存程序名]‘) and OBJECTPROPERTY(id, N‘IsProcedure‘) = 1) drop procedure[預存程序名] 判斷暫存資料表是否存在if object_id(‘tempdb..#暫存資料表名‘) isnot null drop table#暫存資料表名 判斷視圖是否存在--SQL Server 2000IF EXISTS ( SELECT* FROMsysviews WHEREobject_id = ‘[dbo].[視圖名]‘--SQL Server 2005IF EXISTS ( SELECT* FROMsys.views WHEREobject_id = ‘[dbo].[視圖名]‘ 判斷函數是否存在if exists ( select* fromdbo.sysobjects whereid = object_id(N‘[dbo].[函數名]‘) and xtype in (N‘FN‘, N‘IF‘, N‘TF‘)) drop function[dbo].[函數名] 擷取建立資訊 SELECT[ name],[id],crdate FROMsysobjects wherextype=‘U‘/*xtype 的表示參數類型，通常包括如下這些 C = CHECK約束 D = 預設值或 DEFAULT約束 F = FOREIGN KEY約束 L =日誌FN =純量涵式IF = 內嵌表函數 P =預存程序PK = PRIMARY KEY約束（類型是K） RF = 複製篩選預存程序 S = 系統資料表 TF = 表函數 TR =觸發器U = 使用者表 UQ = UNIQUE約束（類型是K） V = 視圖 X = 擴充預存程序 */ 判斷列是否存在if exists( select* fromsyscolumns whereid=object_id(‘表名‘) and name=‘列名‘) alter table表名 drop column列名 判斷列是否自增列if columnproperty(object_id(‘table‘),‘col‘,‘IsIdentity‘)=1print ‘自增列‘ elseprint ‘不是自增列‘ SELECT* FROMsys.columns WHEREobject_id=OBJECT_ID(‘表名‘)AND is_identity=1 判斷表中是否存在索引if exists( select* fromsysindexes whereid=object_id(‘表名‘) and name=‘索引名‘)print ‘存在‘ elseprint ‘不存在 查看資料庫中對象 SELECT* FROMsysobjects WHERE name=‘對象名‘select * from table（所要查詢的表名） where coloum(條件)  提升 複製表(只複製結構，源表名：a 新表名：b) (Access可用)法一：select * into b from a where 1<>1法二：select top 0 * into b from a 拷貝表(拷貝資料,源表名：a 目標表名：b) (Access可用)insert into b(x, y, z) select d,e,f from a; 跨資料庫之間表的拷貝(具體資料使用絕對路徑) (Access可用)insert into b(x, y, z) select d,e,f from a in ‘具體資料庫’ where 條件例子：。.from b in ‘"&Server.MapPath("."&"\data.mdb" &"‘ where.. 子查詢(表名1：a 表名2：b)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) 顯示文章最後時間select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 外串連查詢(表名1：a 表名2：b)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：aselect * from (Select a,b,c FROM a) T where t.a > 1; between的用法between為查詢某欄位的指定範圍，限制查詢資料範圍時包括了邊界值，not between不包括邊界值select * from table1 where time between time1 and time2select a,b,c, from table1 where a not between 數值1 and 數值2 in 的使用方法select * from table1 where a [not] in (‘值1’，’值2’,’值4’，’值6’) 刪除主表沒有的資訊兩張關聯表delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 四表聯查問題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 a.a=d.d where ..... 排程提前五分鐘SQL: select * from 排程 where datediff(‘minute‘,f開始時間，getdate())>5 一條sql 搞定資料庫頁select top 10 b.* from (select top 20 主鍵欄位,排序欄位 from 表名 order by 排序欄位 desc) a,表名 b where b.主鍵欄位= a.主鍵欄位 order by a.排序欄位 前10條記錄select top 10 * from table1 where 範圍 選擇排名選擇在每一組b值相同的資料中對應的a最大的記錄的所有資訊(類似這樣的用法可以用於論壇每月熱門排行榜，每月熱銷產品分析,按科目成績排名，等等。)select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 派生結果表包括所有在TableA 中但不在TableB和TableC 中的行並消除所有重複行而派生出一個結果表(select a from tableA except (select a from tableB) except (select a from tableC) 隨機取出10條資料select top 10 * from tablename order by newid() 隨機播放記錄select newid() 重複資料刪除記錄Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) 列出資料庫裡的表名select name from sysobjects where type=‘U‘ 列出表裡的所有的select name from syscolumns where id=object_id(‘TableName‘) 列示排列列示type、vender、pcs欄位，以type欄位排列，case可以方便地實現多重選取，類似select 中的case。select 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 3 初始化表table1TRUNCATE TABLE table1 選擇從10到15的記錄select top 5 * from (select top10 * from (select top 15 * from table order by id asc) table_別名 order by id desc) table_2 order by id 資料類型轉換declare @numid intdeclare @id varchar(50)set @numid=2005set @id=convert(varchar,@numid)通過上述陳述式完成資料類型Int轉換成varchar，其他轉換類似，可參看convert函數  技巧 1=1，1=2的使用在SQL語句組合時用的較多“where 1=1”是表示選擇全部 “where 1=2”全部不選，如：if @strWhere !=‘beginset @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘] where ‘ + @strWhereendelsebeginset @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘]‘end我們可以直接寫成set @strSQL=‘select count(*) as Total from tablename where 1+1‘if(@strWhere!=‘‘){set @[email protected]+’and’+strWhere} 收縮資料庫--重建索引DBCC REINDEXDBCC INDEXDEFRAG--收縮資料和日誌DBCC SHRINKDBDBCC SHRINKFILE 壓縮資料庫dbcc shrinkdatabase(dbname)轉移資料庫給新使用者以已存在使用者權限exec sp_change_users_login ‘update_one‘,‘newname‘,‘oldname‘go 檢查備份組RESTORE VERIFYONLY from disk=‘E:\dvbbs.bak‘ 修複資料庫Alter DATABASE [dvbbs] SET SINGLE_USERGODBCC CHECKDB(‘dvbbs‘,repair_allow_data_loss) WITH TABLOCKGOAlter DATABASE [dvbbs] SET MULTI_USERGO 日誌清除SET NOCOUNT ONDECLARE @LogicalFileName sysname,@MaxMinutes INT,@NewSize INTUSE tablename -- 要操作的資料庫名Select @LogicalFileName = ‘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)) + ‘MB‘FROM sysfilesWhere name = @LogicalFileNameCreate TABLE DummyTrans(DummyColumn char (8000) not null)DECLARE @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.WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- 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 ‘ + db_name() + ‘ LOG is ‘ +CONVERT(VARCHAR(30),size) + ‘ 8K pages or ‘ +CONVERT(VARCHAR(30),(size*8/1024)) + ‘MB‘FROM sysfilesWhere name = @LogicalFileNameDrop TABLE DummyTransSET NOCOUNT OFF 更改某個表exec sp_changeobjectowner ‘tablename‘,‘dbo‘ 儲存更改全部表Create PROCEDURE dbo.User_ChangeObjectOwnerBatch@OldOwner as NVARCHAR(128),@NewOwner as NVARCHAR(128)ASDECLARE @Name as NVARCHAR(128)DECLARE @Owner as NVARCHAR(128)DECLARE @OwnerName as NVARCHAR(128)DECLARE curObject CURSOR FORselect ‘Name‘ = name,‘Owner‘ = user_name(uid)from sysobjectswhere user_name(uid)[email protected]order by nameOPEN curObjectFETCH NEXT FROM curObject INTO @Name, @OwnerWHILE(@@FETCH_STATUS=0)BEGINif @[email protected]beginset @OwnerName = @OldOwner + ‘.‘ + rtrim(@Name)exec sp_changeobjectowner @OwnerName, @NewOwnerend-- select @name,@NewOwner,@OldOwnerFETCH NEXT FROM curObject INTO @Name, @OwnerENDclose curObjectdeallocate curObjectGO 迴圈寫入資料declare @i intset @i=1while @i<30begininsert into test (userid) values(@i)set @[email protected]+1end

筆記（二） C#sql語句