-- 資料庫SQL總結中...........
--SQL分類：
(CREATE，ALTER，DROP，DECLARE) ---DDL—資料定義語言
(SELECT，DELETE，UPDATE，INSERT) ---DML—資料操縱語言
(GRANT，REVOKE，COMMIT，ROLLBACK) --DCL—資料控制語言
--設定記憶體選項
--設定 min server memory 配置項
EXEC sp_configure N'min server memory (MB)', 0
--設定 max server memory 配置項
EXEC sp_configure N'max server memory (MB)', 256
--使更新生效
RECONFIGURE WITH OVERRIDE
------設定記憶體選項
--建立資料庫，指定日誌檔案最大大小
CREATE DATABASE karaok
ON (NAME = N'karaok', FILENAME = N'C:/Program Files/Microsoft SQL Server/MSSQL/data/karaok.mdf' , SIZE = 200, FILEGROWTH = 10%)
LOG ON (NAME = N'karaok_log', FILENAME = N'C:/Program Files/Microsoft SQL Server/MSSQL/data/karaok_log.LDF', SIZE = 200, FILEGROWTH = 10%, MAXSIZE = 2000)
Go
--備份資料庫
USE master
exec sp_addumpdevice 'disk', 'QuickChainTY20091210', 'D:/Program Files/Microsoft SQL Server/BACK/QuickChainTY20091210.BAK'
BACKUP DATABASE QuickChainTY TO QuickChainTY20091210
--備份資料庫日誌
exec sp_addumpdevice 'disk', 'QuickChainTYLog20091210', 'D:/Program Files/Microsoft SQL Server/BACK/QuickChainLogTY20091210.BAK'
BACKUP LOG QuickChainTY TO QuickChainTYLog20091210
--備份
declare @sql varchar(8000)
set @sql='backup database mis to disk=''d:/databack/mis/mis'
+rtrim(convert(varchar,getdate(),112))+'.bak'''
exec(@sql)
--刪除15天前備份檔案
set @sql='del d:/databack/mis/mis'
+rtrim(convert(varchar,getdate()-15,112))+'.bak'''
exec master..xp_cmdshell @sql
GO
--建立表、試圖、儲存過程、函式
create table newtable (columnid int primary key ,columnname char(20) not null )
create table newtable (columnid int identity(1,1))
select * into newtable from oldtable
create view viewname
create procedure pro_name
(@a varchar,
@b int =1)
create FUNCTION f_GetCustomerName(@CustomerCode varchar(32))
RETURNS varchar(128)
AS
BEGIN
declare @Name varchar(128)
select @Name=Name from ComCustomer where CustomerCode=@CustomerCode
set @Name=IsNull(@Name,@CustomerCode)
RETURN @Name
END
--刪除表、試圖、儲存過程、函式
drop table tablename
drop view viewname
drop proc procedurename
drop function functionname
---修改表
alter table tablename add columnnew varchar(100)default ('')--新增列
alter table tablename add createdate datetime null default getdate() with values
alter table tablename drop columnold --刪除列
alter table tablenaem add column_a varchar(200) null constraint yueshu unique--新增約束
---填充資料或建立表
insert into a (aa,bb,cc)values (1,2,3)
insert a select 1,2,3
---要求目標表newtable不存在，因為在插入時會自動建立表newtable，並將oldtable中指定欄位資料複製到newtable中。
select * into newtable from oldtable
select top 0 * into newtable from oldtable
--要求目標表Table2必須存在，由於目標表Table2已經存在，所以我們除了插入源表Table1的欄位外，還可以插入常量
Insert into Table2(field1,field2) select value1,value2 from Table1
--刪除資料
delete from tablename --記錄被刪除的每行資料操作日誌
truncate table tablename--釋放整頁不記錄操作日誌(初始化表tablename )
--修改表中的資料
update tablename set id=232 where id=1
update tablename set quantity=(select quantity from sectable a where a.id=tablename.id )
---將aaa表中的資料更新到psy_t_markinfo表中
update psy_t_markinfo set UserDef1=(select UserDef1 from aaa where aaa.MarkID=psy_t_markinfo.MarkID)
--收縮資料庫表
dump transaction tablename with no_log
truncate,delete,drop--的異同點
/*
truncate,delete,drop的異同點
注意:這裡說的delete是指不帶where子句的delete語句
相同點:truncate和不帶where子句的delete, 以及drop都會刪除表內的資料
不同點:
1. truncate和 delete只刪除資料不刪除表的結構(定義)
drop語句將刪除表的結構被依賴的約束(constrain),觸發器(trigger),索引(index); 依賴於該表的儲存過程/函式將保留,但是變為invalid狀態.
2.delete語句是dml,這個操作會放到rollback segement中,事務提交之後才生效;如果有相應的trigger,執行的時候將被觸發.
truncate,drop是ddl, 操作立即生效,原資料不放到rollback segment中,不能回滾. 操作不觸發trigger.
3.delete語句不影響表所佔用的extent, 高水線(high watermark)保持原位置不動
顯然drop語句將表所佔用的空間全部釋放
truncate 語句預設情況下將空間釋放到 minextents個 extent,除非使用reuse storage; truncate會將高水線復位(回到最開始).
4.速度,一般來說: drop> truncate > delete
5.安全性:小心使用drop 和truncate,尤其沒有備份的時候.否則哭都來不及
使用上,想刪除部分資料行用delete,注意帶上where子句. 回滾段要足夠大.
想刪除表,當然用drop
想保留表而將所有資料刪除. 如果和事務無關,用truncate即可. 如果和事務有關,或者想觸發trigger,還是用delete.
如果是整理表內部的碎片,可以用truncate跟上reuse stroage,再重新匯入/插入資料
*/
---
--返回當前的使用者。
select CURRENT_USER
select USER_NAME()
---定義一個遊標及while的使用
DECLARE abc CURSOR FOR
SELECT * FROM Shippers
OPEN abc
FETCH NEXT FROM abc
WHILE (@@FETCH_STATUS = 0)
FETCH NEXT FROM abc
CLOSE abc
DEALLOCATE abc
---while
while (@count<100)
begin
print @count;
continue;
end
else
break;
----快速檢視錶結構和試圖、儲存過程索引等資訊
sp_help tablename
sp_helptext viewname or procedurename
sp_helpindex tablename or viewname
---建立表和試圖的索引
create index indexname on tablename (columnname)
create (unique) index indexname on viewname (columnname)
drop index indexname
/*幾個高階查詢運算詞
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)，不消除重複行。
注：使用運算詞的幾個查詢結果行必須是一致的。
*/
--between的用法,between限制查詢資料範圍時包括了邊界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 數值1 and 數值2
--兩張關聯表，刪除主表中已經在副表中沒有的資訊
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
--日程安排提前五分鐘提醒
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.排序欄位
--選擇在每一組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()
--列出資料庫裡所有的表名
select name from sysobjects where type='U'
--列出表裡的所有的欄位
select name from syscolumns where id=object_id('TableName')
---重複行中只顯示第一行的某個欄位資料，其他行為空或0
SELECT
CASE WHEN (ROW_NUMBER() OVER (PARTITION BY JobNo ORDER BY tJobNo)) = 1 THEN InvoiceAmount ELSE 0 END AS InvoiceAmount
FROM TrkJob
-----------------------------------排序函式
---自動生成序號
select row_Number() over(order by ProjectNo desc )as RowNum from TrkJob
select row_number() over(partition by (OperateDate) order by projectno asc) from trkload
---返回結果集分割槽中行的排名，在排名中沒有任何間斷。行的排名等於所討論行之前的所有排名數加一
select DENSE_RANK () over(order by loadno desc)as 'dense_rank' from trkload
select DENSE_RANK() over(partition by (OperateDate) order by projectno asc) from trkload
----返回結果集的分割槽內每行的排名。行的排名是相關行之前的排名數加一。
select RANK () over(order by loadno desc )as 'ranks' from trkload
select RANK() over(partition by (OperateDate) order by projectno asc) from trkload
--某列值相等，其他列值不等，只在第一行顯示一個相等的值，其他行為空
DECLARE @TAB TABLE ([col1] varchar(1),[col2] int,[col3] int)
insert @TAB
select 'a',1,2 union all
select 'a',2,3 union all
select 'a',3,4 union all
select 'b',4,5 union all
select 'b',5,6 union all
select 'b',6,7
--------------開始查詢--------------------------
select
case px when 1 then col1 else '' end as col1,
col2,col3
from
(select px=row_number()over(partition by col1 order by getdate()),* from @TAB)t
---顯示10%的資料
select * from
(select ntile(10) over(order by loadno desc)as ass,loadno,projectno,jobno,forwardercode,operatedate from trkload
)qq where ass=1
-----------------------------------排序函式
--日期函式
set datefirst 1
select @@datefirst--美國預設第一天為星期日 7
select datepart(year,getdate()),datepart(month,getdate()),datepart(day,getdate()),datepart(week,getdate()),
datepart(quarter,getdate()),datepart(dayofyear,getdate()), datepart(weekday,getdate()),
datepart(hour,getdate()),datepart(minute,getdate()),datepart(second,getdate()),datepart(millisecond,getdate())
---查詢所有資料庫名
select * from master.dbo.sysdatabases
---查詢資料庫表中的所有列（欄位）
select * from syscolumns
name id colid
//欄位名稱 //表ID號. 欄位ID號.
----sysobjects有用的欄位名稱和相關說明
select * from sysobjects
name id xtype uid
物件名. 物件ID 物件型別 所有者物件的使用者ID。
物件型別(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 = 擴充套件儲存過程
聚合函式:
--函式名稱 函式說明 函式返回值
Avg() --返回組中各值的平均值 int,double,money,float
Binary_checksum() --返回按照表的某一行或某一組表示式計算出來的二進位制表示式 二進位制數字
Checksum() -- 返回按照表的某一行或某一組表示式計算出來的效驗和的值 int
Checksum_agg() --返回組中各值的效驗和 int
Count() --返回組中的項數 int
Count_big() --返回組中的項數 int
Grouping() --聚合函式:當行由CUBE或ROLLUP運算子新增時,該函式將導致列的輸出值為1,當行不由CUBE或ROLLUP運算子新增時,該函式將導致列的輸出值為0 int
Max() --返回表示式的最大值
Min() --返回表示式的最小值
Stdev() --返回給定表示式中所有值的標準偏差
Stdevp() --返回給定表示式中所有值的總體標準偏差
Sum() ---返回表示式中所有值和或僅非重複值的和
Var() --返回表示式中所有值的方差
Varp() --返回表示式中所有值的總體方差
配置函式:
@@Datefirst --返回SET DATAFIRST引數的當前值,該值表示為每週指定的第一天
@@Dbts --返回一個時間戳
@@Langid --返回本地語言的標識
@@Language --返回本地語言的名稱
@@Lock_Timeout --返回當前會話的當前鎖定超時設定(毫秒)
@@Max_Connections --返回同時連線的最大使用者數
@@Max_Precision --返回浮點型別的精度級別
@@Nestlevel --返回當前儲存過程的巢狀級別
@@Options --返回當前SET選項資訊
@@Remserver --返回在遠端登陸記錄中顯示的名稱
@@Servername --返回本地SQL的名稱
@@Servicename --返回SQL登錄檔項名稱
@@Spid --返回當前使用者程式PID
@@Textsize --返回SET選項的TEXTSIZE選項的當前值
@@Version --返回SQL的安裝日期,版本,CPU型別
遊標函式:
@@Cursor_Rows --返回連線上開啟的上一個遊標中的當前限定行的數目
Cursor_Status() -- 一個標量函式,允許儲存過程呼叫方確定該儲存過程是否為給定的引數返回結果集和遊標
@@Fetch_Status --返回針對連線當前開啟的任何遊標發出的上一條遊標FETCH語句的狀態
日期和時間函式
Dateadd() --根據日期增量,返回新的日期
Datediff() --返回跨兩個指定日期的日期和時間邊界數
Day() --返回日期中的日
Getdate() --返回日期時間
Getutcdate() --返回UCT的日期
Month() --返回日期中的月
Year() -- 返回日期中的年
數學函式
Abs() --返回絕對值
Acos() --返回以弧度表示的角,其餘弦為給定浮點表示式
Asin() --返回以弧度表示的角,其正弦為給定浮點表示式
Atan() --返回以弧度表示的角,其正切為給定浮點表示式
Atn2() --返回以弧度表示的角,其正切為2給定浮點表示式的商
Ceiling() --返回大於等於給定數值表示式的最小整數
Cos() --返回給定角的三角餘怰
Cot() --返回給定角的三角餘切
Degrees() --如果角以弧度表示,返回以度數表示的角
Exp() --返回給定浮點表示式的指數值
Floor() --返回小於等於給定數值表示式的最大整數
Log() -- 給定浮點表示式的自然對數
Log10() -- 給定浮點表示式的常用對數
Pi() --返回圓周率
Power() --給定表示式的指定冪的值
Radians() --返回指定數值表示式的弧度
Rand() -- 隨機浮點數
Round() --舍入到指定精度
Sign() --返回指定表示式的符號
Sin() --三角正怰
Sqrt() --開方
Square() --給引數平方
Tan() --給引數正切
後設資料函式
Col_Length() --返回指定表中列的長度(位元組)
Col_Name() --返回資料庫表ID, 列ID的列名稱
Columnproperty() --返回列引數,過程引數的屬性值
Databaseproperty() --返回資料庫和屬性名稱的屬性值
Databasepropertyex() --返回資料庫和屬性名稱的屬性值
Db_Id() --返回資料庫名稱的標識號
Db_Name() --返回資料庫ID的名稱
File_id() --返回資料庫中給定邏輯檔名的檔案標識號
File_Name() --返回資料庫中給定檔案標識號的邏輯檔名
Filegroup_Name() --返回資料庫中給定邏輯檔案組的檔案標識號
Filegroupproperty() --返回資料庫中給定邏輯檔案組和屬性名稱的指定屬性值
Fileproperty() -- 返回檔名,屬性名稱的指定屬性值
::fn_Listextendedproperty() --返回資料庫物件的擴充套件屬性值
Isdate() --確定輸入表示式是否為有效日期.要驗證其是否為日期的表示式。expression 是 text、ntext 表示式和 image 表示式以外的任意表示式，可以隱式轉換為 nvarchar
Isnull() --如果 check_expression 不為 NULL，則返回它的值；否則，在將 replacement_value 隱式轉換為 check_expression 的型別（如果這兩個型別不同）後，則返回前者。
Isnumeric() --當輸入表示式的計算結果為有效的 numeric 資料型別時，ISNUMERIC 返回 1；否則返回 0
Newid()
Nullif() --如果兩個指定的表示式相等，則返回空值。如果兩個表示式不相等，則 NULLIF 返回第一個 expression 的值。如果表示式相等，則 NULLIF 返回第一個 expression 型別的空值。
Parsename() --返回物件名稱的指定部分。可以檢索的物件部分有物件名、所有者名稱、資料庫名稱和伺服器名稱.
-----常用字串函式
PatIndex--函式支援使用萬用字元來進行搜尋，然而CHARINDEX不支援通佩符,函式返回字元或者字串在另一個字串或者表示式中的起始位置，PATINDEX函式支援搜尋字串中使用萬用字元，這使PATINDEX函式對於變化的搜尋字串很有價值。
CharIndex--函式返回字元或者字串在另一個字串中的起始位置。
1、)CharIndex
CharIndex (分隔符，被查詢欄位[,開始位置]
2、)PatIndex ( ’%pattern%’ , expression )
--Pattern是你要搜尋的字串，expression是被搜尋的字串。一般情況下expression是一個表中的一個欄位，pattern的前後需要用“%”標記，除非你搜尋的字串在被收縮的字串的最前面或者最後面。
--這裡的%標記告訴PATINDEX函式去找字串“pattern”，不管被搜尋的字串中在“pattern”的前後有多少字元！
--假如你想知道被搜尋字串是否由特定的字串開始，你可以省去前面的%標記。PATINDED函式就要這樣寫：
PatIndex(’pattern%’,expression)
3、)Substring(被擷取的字串，起始位置，長度)
4、)Stuff(原字串[常量|變數|字元列|二進位制資料列],指定刪除和插入的開始位置,指定要刪除的字元數,將要插入的字元或串)---函式將字串插入另一字串。它在第一個字串中從開始位置刪除指定長度的字元；然後將第二個字串插入第一個字串的開始位置。
--例如：
-----從字串中取數字
IF OBJECT_ID('tb_test') IS NOT NULL DROP TABLE tb_test
GO
CREATE TABLE tb_test(columnfile VARCHAR(50))
---填充測試資料
INSERT tb_test
SELECT 'ADSF-123A1' UNION ALL
SELECT 'ADDFSF - 36B133' UNION ALL
SELECT 'AD- 555FDF3.5'
----查詢
SELECT
CASE WHEN PATINDEX('%[^0-9]%',columnfile_b)=0
THEN columnfile_b
ELSE LEFT(columnfile_b,PATINDEX('%[^0-9]%',columnfile_b)-1)
END
FROM (
SELECT
CASE WHEN PATINDEX('%[0-9]%',columnfile)=0
THEN NULL
ELSE STUFF(columnfile,1,PATINDEX('%[0-9]%',columnfile)-1,'')
END AS columnfile_b
FROM tb_test
) AA
------從字串中取數字
5、)Reverse(字串)--將字串反轉
6、)Replicate(expression,n)--將指定的物件重複N次
----常用日期函式
7、)DateDiff 函式
DateDiff (dd|yyyy|mm,當前被比較的日期欄位,比較的日期)
8、)DateAdd(day|month|year,16|3|3,getdate()) 得到在指定的日期上增加指定的日期（天，月，年）的日期
9、)DateDiff(day|month|year, getdate(),getdate()-10) 獲取兩個指定日期之間的日期間隔
10、)DateName(day|month|year,getdate()) 返回表示指定日期的指定日期部分的字串。
11、)DatePart(day|month|year,getdate())返回表示指定日期的指定日期部分的整數。
12、)CAST 和 CONVERT 將某種資料型別的表示式顯式轉換為另一種資料型別。CAST 和 CONVERT 提供相似的功能。
CAST ( expression AS data_type )
CONVERT (data_type[(length)], expression [, style])
/*引數
data_type 表示式將轉換成的資料型別。
expression 要轉換的表示式。
style 轉換的格式（120 代表日期的格式“yyyy-MM-dd”）
*/
-----轉載聯機幫助
-PARSENAME ( 'object_name' , object_piece ) ---返回物件名稱的指定部分。可以檢索的物件部分有物件名、所有者名稱、資料庫名稱和伺服器名稱。
/*' object_name '
要檢索其指定部分的物件的名稱。object_name 的資料型別為 sysname。此引數是可選的限定物件名稱。如果物件名稱的所有部分都是限定的，則此名稱可包含四部分：伺服器名稱、資料庫名稱、所有者名稱以及物件名稱。
object_piece
要返回的物件部分。object_piece 的資料型別為 int 值，可以為下列值。
1 = 物件名稱
2 = 架構名稱
3 = 資料庫名稱
4 = 伺服器名稱
返回型別為nchar
*/
SELECT ParseName('AdventureWorks..Contact', 1) AS 'Object Name';
SELECT PARSENAME('AdventureWorks..Contact', 2) AS 'Schema Name';
SELECT PARSENAME('AdventureWorks..Contact', 3) AS 'Database Name;'
SELECT PARSENAME('AdventureWorks..Contact', 4) AS 'Server Name';
--轉載聯機幫助
----巢狀遊標案例
declare TaoCan_LuoDan_Cursor cursor for --定義一個遊標，用來依次從自動匹配好為套餐中取酒菜名和數量
select TaoCan_Quantity,BusDetaOld_RecipeName from #TaoCan_PiPeiDetail
open TaoCan_LuoDan_Cursor
fetch next from TaoCan_LuoDan_Cursor
into @taocannum,@taocan_recipename
while @@fetch_status=0
begin--根據酒菜名相同依次迴圈更新從零點酒菜中匹配出去的酒菜數量後還剩下的數量（相當與實際零點就酒水數量）
if exists (select * from #TaoCanLuoDan where Recipe_Name=@taocan_recipename)
Begin
declare PayType_Cursor cursor for
select Recipe_Name,Quantity,BusinessDetail_ID from #TaoCanLuoDan whereRecipe_Name=@taocan_recipename
open PayType_Cursor
fetch next from PayType_Cursor into @PayType_Name ,@PayType_Num,@BusinessDetail_ID
while @@fetch_status=0
begin
if @PayType_Num< @taocannum
begin
set @taocannum=@taocannum-@PayType_Num
delete from #TaoCanLuoDan where BusinessDetail_ID=@BusinessDetail_ID andRecipe_Name=@taocan_recipename
end
else if @PayType_Num>@taocannum
begin
update #TaoCanLuoDan set Quantity=@PayType_Num-@taocannum,
SingleRecipeTotal=(@PayType_Num-@taocannum)*UnitPrice*DisRatio*0.01,
SingleRecipeTotal_last=(@PayType_Num-@taocannum)*UnitPrice*DisRatio*0.01,
SingleRecipeSum=(@PayType_Num-@taocannum)*UnitPrice*DisRatio*0.01
where BusinessDetail_ID=@BusinessDetail_ID andRecipe_Name=@taocan_recipename
SET @taocannum=0
end
else if @PayType_Num=@taocannum
begin
set @taocannum=@taocannum-@PayType_Num
delete from #TaoCanLuoDan where BusinessDetail_ID=@BusinessDetail_ID andRecipe_Name=@taocan_recipename
end
fetch next from PayType_Cursor into @PayType_Name ,@PayType_Num,@BusinessDetail_ID
end
close PayType_Cursor
deallocate PayType_Cursor
end
fetch next from TaoCan_LuoDan_Cursor
into @taocannum,@taocan_recipename
end
close TaoCan_LuoDan_Cursor
deallocate TaoCan_LuoDan_Cursor
-----遊標
---表值函式
Create Function f_GetAllSubNodeForNode(@DepartmentID int) returns @t_level table(DepartmentID int , level int)
as
begin
declare @level int
set @level = 1
insert into @t_level select @DepartmentID , @level
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.DepartmentID , @level
from PSY_U_Department as a , @t_Level b
where a.HigherDepartmentID = b.DepartmentID and b.level = @level - 1
end
return
end
---表值函式
Create Function f_GetUnionStr(@file varchar(32))
returns varchar(1000)
----按某欄位合併字串
----@file是條件（可以是單條件也可以是複合條件）,最後將table1 改為具體的表或檢視就可以了
as
begin
declare @returnstr varchar(1000)
select @returnstr=IsNull(@returnstr+',','')+convert(varchar(128),value) from table1 wherefiles=@file
return @returnstr
end
--顯示高階配置選項
exec sp_configure 'show advanced options',1
reconfigure
go
exec sp_configure ---顯示所有高階配置選項
SELECT * FROM sys.configurations
go
--啟用 xp_cmdshell
exec sp_configure 'xp_cmdshell',1
reconfigure
--禁用 xp_cmdshell
exec sp_configure 'xp_cmdshell',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
go
---拷貝（移動）檔案
exec xp_cmdshell 'copy D:\g_goods_0723.bak
\\zhaowenzhong\test\g_goods_0723.bak, no_output';
go
exec xp_readerrorlog ----讀取日誌
--返回可執行檔案列表
EXEC master..xp_cmdshell 'dir *.exe'
--刪除檔案
exec master..xp_cmdshell 'del F:\test_cmd_shell\復件to_all.xls '
/* SQL2000版本支援，但是從2005以後不支援刪除擴充套件儲存過程（所有，不僅僅是xp_cmdshell),可以將其禁用掉。
--去掉擴充套件儲存過程xp_cmdshell
use master
EXEC sp_dropextendedproc 'xp_cmdshell '
--恢復擴充套件儲存過程xp_cmdshell
EXEC sp_addextendedproc 'xp_cmdshell ', 'xpsql70.dll '
*/
/*名稱相同，資料不同，只顯示一個名稱。（即隱藏或不顯示重複名稱）*/
create table tb(name varchar(120), qty int)
insert into tb values('A', 100)
insert into tb values('A', 200)
insert into tb values('C', 200)
insert into tb values('d', 100)
insert into tb values('A', 500)
insert into tb values('C', 200)
go
select (case when qty = (select top 1 qty from tb where name = t.name) then name else '' end) name, qty
from (select top 1000 percent * from tb order by name , qty) t
--或者
select name=(case when rownumber=1 then name else ''end ),qty
from (select *,rownumber=ROW_NUMBER()over( partition by name order by getdate()) from tb) t
--1個簡單的統計每天系統訪問次數及明細
create table [user](username varchar(10),taskid int)
insert into [user]
select '張三',1 union
select '王二',2 union
select '張三',3 union
select '李四',4
create table task(taskid int,loginTime datetime)
insert into task
select 1,'2012-12-23 11:30:00' union
select 2,'2012-12-23 18:30:00' union
select 3,'2012-12-25 11:30:00' union
select 4,'2012-12-23 11:00:00'
declare @bdate datetime=(select min(loginTime) from task),
@edate datetime=(select MAX(loginTime) from task)
;with cte1 as
(
select 日期=DATEADD(day,number,@bdate) from master.dbo.spt_values
where type='p' and number<=DATEDIFF(DAY,@bdate,@edate)
)
,cte2 as
(
select a.username,b.loginTime from [user] a,task b where a.taskid=b.taskid
)
select 日期=convert(varchar(10),日期,120),
人數=(select COUNT(distinct username) from cte2 where DATEDIFF(DAY,a.日期,loginTime)=0),
登入人=isnull(stuff((select distinct ','+username from cte2
where DATEDIFF(DAY,a.日期,loginTime)=0 for xml path('')),1,1,''),'無')
from cte1 a
--消除行中 重複資料
create table t1(col1 varchar(10),col2 varchar(8000))
insert t1
select 'A' , '美國,法國,英國,美國,日本,韓國,日本' union all
select 'B' , '朝鮮,緬甸,緬甸'
go
;with cte as
(
select col1,
col2=SUBSTRING(case when right(col2,1)=',' then col2 else col2+',' end,1,charindex(',',case when right(col2,1)=',' then col2 else col2+',' end)-1),
col3=stuff(case when right(col2,1)=',' then col2 else col2+',' end,1,charindex(',',case when right(col2,1)=',' then col2 else col2+',' end),'')
from t1
union all
select col1,col2=SUBSTRING(col3,1,charindex(',',col3)-1),col3=stuff(col3,1,charindex(',',col3),'') from cte
where CHARINDEX(',',col3)>0
)
select distinct col1,col2 from cte
order by col1