select cast(c1 as integer) from t1 //將欄位值取出並轉換為數值型態
select cast(substr (c1,1,1) as integer) from t1
select substr (c1,1,2) from t1 //DB2取出欄位值並擷取其長度[欄位名稱,開始(從1起始),長度],MS SQL使用substring
------------------------------------------------------------------------------------------------------
create table t1(c1 varchar(8) not null,c2 varchar(5), c3 int(3)) //建立table
drop table t1 //刪除table
alert table t1 add c1 varchar(5) default 0 not null //新增table欄位
alter table t1 drop column c1 //刪除table欄位
alter table t1 modify c1 varchar(8) //修改table欄位
alter table t1 rename column c1 to c4 //更改欄位名稱
-----------------------------------------------------------------------
insert into t1(c1,c2,c3) values('str1','str2',int1) //新增
-----------------------------------------------------------------------
delete from t1 where c1='str1' //刪除
-----------------------------------------------------------------------
update t1 set c1='str1',c2='str2',c3=int1 where c1='str3' //修改
-----------------------------------------------------------------------
int i=stmt.executeUpdate(); //取得更新資料的筆數(判斷對資料庫動作是否執行成功)
i == 0 ? false : true
------------------------------------------------------------------------------
取得ResultSet傳回資料筆數
int count=0;
while(rs.next()){
rs.getString("c1");
count++;
}
------------------------------------------------------------------------------
// ResultMetaData
ResultSet rs=stmt.executeQuery(sql);
ResultMetaData rmd=rs.getMetaData();
int a=rs.findColumn("欄位名稱"); //取得欄位編號
int i=rmd.getColumnCount(); //取得table內欄位數目
String j=rmd.getColumnName(欄位編號1~); //取得欄位名稱
String j=rmd.getColumnLabel(欄位編號1~); //取得欄位名稱
String k=rmd.getColumnType(欄位編號); //取得欄位資料型態
int l=rmd.getColumnDisplaySize(欄位編號); //取得欄位資料長度
------------------------------------------------------------------------------
//and or > <
where id=5 and year>30 or year<20 order by id desc
--------------------------------------------------------------------------------------------------------
//between and
select * from t1 where c1='12345' and c2='1' and c3 between '930101' and '931231' order by c1
------------------------------------------------------------------------------
//distinct 去除重複值
select distinct c1 from t1 where c2 between '2004/04/01' and '2004/05/01'
--------------------------------------------------------------------------------------------------
//order by c1 desc 由大至小排序
--------------------------------------------------------------------------------------------------
// 取得欄位資料的筆數
select count(c1) 總計筆數 from t1 where c2='A'
int i=rs.getInt("總計筆數"); //用別名取出欄位資料的筆數
或int i=rs.getInt(1); // select出來的第一個欄位的值,編號從一開始
-------------------------------------------------------------------------------------------------
// 取得欄位內資料的加總值(欄位型態需為int) sum()一定要做group by敘述
select sum(c1) 合計金額 from t1 where c1='A' group by c1
int i=rs.getInt("合計金額"); //用別名取出欄位內資料的加總值
或int i=rs.getInt(1);
-------------------------------------------------------------------------------------------------
// COUNT(),SUM()
select c1 , count(c1) from t1 group by c1 order by c1
select c1 , sum(c1) from t1 group by c1 order by c1
-------------------------------------------------------------------------------------------------
//MAX() MIN() AVG() MAX() MIN()對日期也有效 如求得最新日期
select MAX(c1),MIN(c1),AVG(c1) from t1
-------------------------------------------------------------------------------------------------
// IN , NOT IN
select c1 from t1 where c2 IN (1,2,3)
可改寫為 select c1 from t1 where c2=1 OR c2=2 OR c2=3
select c1 from t1 where c2 NOT IN (1,2,3)
-------------------------------------------------------------------------------------------------
//HAVING 須先群組化group by之後,才可使用having敘述
--查詢資料表中重複的電子郵件名單
SELECT R_EMAIL , COUNT(R_EMAIL) AS CACL FROM Res_Main_Tot GROUP BY R_EMAIL HAVING COUNT(R_EMAIL) > 1
-------------------------------------------------------------------------------------------------------------
//GROUP BY
只有出現在Group By子句中的屬性，才可出現在Select子句中
SELECT x, sum(y) FROM table1 GROUP BY x;
-------------------------------------------------------------------------------------------------
//IS NULL
SELECT TOP 100 * , ISNULL(SUM(A.Price),0) FROM A //若取出的值為NULL 則給予0
-------------------------------------------------------------------------------------------------
//比較運算式
WHERE C1 = 'A'
WHERE C1 != 'A'
WHERE C1 <> 'A'
WHERE C1 IS NULL
WHERE C1 IS NOT NULL
WHERE C1 LIKE '%STR%'
WHERE C1 NOT LIKE '%STR%'
所有運算式列表:
確定範圍：Between A and B、Not Between A and B
確定集合：IN、NOT IN
字符匹配：LIKE，NOT LIKE
空值：IS NULL、IS NOT NULL
多重條件：AND、OR、NOT
所有運算元列表:
運算符：>、>=、=、<=、<、 <>
-------------------------------------------------------------------------------------------------
//聚合函數
平均值：Avg（ALL|DISTINCT<欄位名稱>） 欄位名稱須為數值型態
總和： Sum（ALL|DISTINCT<欄位名稱>） 欄位名稱須為數值型態
最小值：Min（ALL|DISTINCT<欄位名稱>）
最大值：Max（ALL|DISTINCT<欄位名稱>）
計數： Count（ALL|DISTINCT<欄位名稱>）
-------------------------------------------------------------------------------------------------
//LIKE
限制以Publishing結尾，使用LIKE '%Publishing'
限制以A開頭：LIKE '[A]%'
限制以A開頭外：LIKE '[^A]%'
[]——指定範圍 ([A-F]) 或集合 ([ABCDEF]) 中的任何單個字符。
[^]——不屬於指定範圍 ([^A-F]) 或集合 ([^ABCDEF]) 的任何單個字符
-------------------------------------------------------------------------------------------------
//AS別名c3
SELECT SUM(CAST(c1 AS INTEGER)) + SUM(CAST(c2 AS INTEGER)) AS c3 FROM T1 WHERE c1 != '' GROUP BY C1 ORDER BY c1
SELECT SUM(CAST(c1 AS INTEGER)) c3 FROM T1 WHERE c1 != '' GROUP BY c1 ORDER BY c1
------------------------------------------------------------------------------------------------------------------------
//取得欄位內容的加總值
SELECT SUM(CAST(c1 AS INTEGER)) + SUM(CAST(c2 AS INTEGER)) AS C3 , SUM(CAST(C4 AS INTEGER)) FROM T1 WHERE C1 != '' AND SUBSTR(C2,1,4) IN ( '0', '1', '2', '3') AND CAST( SUBSTR (C3,1,4) AS INTEGER ) = " + strDATE + " GROUP BY C2 , C3 ORDER BY C1
---------------------------------------------------------------------------------------------------------------
// 外部結合 OUTER JOIN 將複數個TABLE結合為一個TABLE 並取得指定欄位的值
SELECT t1.* , t2.c2 ,t3.c3, FROM t2
RIGHT OUTER JOIN t1 ON t2.c5 = t1.c5
LEFT OUTER JOIN t3 ON t1.c1 = t3.c6
where t1.c7 like '9004100%' AND t1.c8 IN ('A','B') AND t1.c8 < '00930231' AND t2.c2 <> '99999999' OR t2.c2 IS NULL
order by t1.c7
------------------------------------------------------------------------------------------------------------------
//集合運算子 UNION 同時取得兩個TABLE中的指定欄位值,不包含兩TABLE中欄位的重複值(重複值只會列出一筆)
兩列select語法的欄位資料型態和欄位數目必須相同
若要以order by 排序union得結果時,order by一定要寫在最後的select的語法後面
當各select語法中的欄位名稱不同時,就要在order by語法中指定欄位編號 如order by 1
SELECT C1, C2, SUM(C3) FROM T1 WHERE C1 != '' GROUP BY C1
UNION (
SELECT C1 ,C2, SUM(C3) FROM T2 WHERE C2 != '' GROUP BY C1 ORDER BY C1)
-------------------------------------------------------------------------------------------------
//集合運算子 UNION ALL 同時取得兩個TABLE中的指定欄位值,包含兩TABLE中欄位的重複值(重複值會全部列出)
SELECT C1, C2, SUM(C3) FROM T1 WHERE C1 != '' GROUP BY C1
UNION ALL(
SELECT C1 ,C2, SUM(C3) FROM T2 WHERE C2 != '' GROUP BY C1 ORDER BY C1)
-------------------------------------------------------------------------------------------------
//UNION時 若兩TABLE欄位數目不同(其對應的欄位資料型態必須相同)
select a, b, '' as c, d ,0 as e from A
union
select a, b, c, '' as d, e from B
-------------------------------------------------------------------------------------------------
//權限
GRANT ALL ON t1 TO PUBLIC
REVOKE ALL ON t1 FROM PUBLIC
-------------------------------------------------------------------------------------------------
//取得一個月內的資料
select * from table where date >= dateadd(m,-1,getdate())
-------------------------------------------------------------------------------------------------
//SQL指令取得2005年2月份所有資料
Select * From table1 Where Year(時間欄位) = 2005 And Month(時間欄位) = 2
-------------------------------------------------------------------------------------------------
//MS SQL
SELECT TABLE1.A, TABLE2.D FROM TABLE1, TABLE2 WHERE TABLE1.A = TABLE2.D
SELECT TABLE1.A, TABLE2.D FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.A = TABLE2.D
SELECT A FROM TABLE1 GROUP BY A HAVING A = '123'
SELECT SUM(CAST(A AS INTEGER)) AS COUNT1 FROM TABLE1
SELECT A FROM TABLE1 WHERE A LIKE '%12%'
SELECT A, SUM(B) AS Expr1 FROM TABLE1 GROUP BY A
SELECT A, SUM(B) AS TOTAL FROM TABLE1 GROUP BY A HAVING SUM(B) > 50 ORDER BY A DESC
-------------------------------------------------------------------------------------------------
//從日期型態轉換為指定格式的字串型態
SELECT CONVERT(CHAR, date_TIME, 101) AS EXECUTE_TIME FROM TABLE1 07/04/2005
SELECT CONVERT(CHAR, date_TIME, 102) AS EXECUTE_TIME FROM TABLE1 2005.07.04
SELECT CONVERT(CHAR, date_TIME, 103) AS EXECUTE_TIME FROM TABLE1 04/07/2005
SELECT CONVERT(CHAR, date_TIME, 104) AS EXECUTE_TIME FROM TABLE1 04.07.2005
SELECT CONVERT(CHAR, date_TIME, 105) AS EXECUTE_TIME FROM TABLE1 04-07-2005
SELECT CONVERT(CHAR, date_TIME, 106) AS EXECUTE_TIME FROM TABLE1 04 07 2005
SELECT CONVERT(CHAR, date_TIME, 110) AS EXECUTE_TIME FROM TABLE1 07-04-2005
SELECT CONVERT(CHAR, date_TIME, 111) AS EXECUTE_TIME FROM TABLE1 2005/07/04
//從字串形態轉換為日期型態
CONVERT(DATETIME, '2005/07/04')
CAST('2005/07/04' AS DATETIME)
-------------------------------------------------------------------------------------------------
//日期型態欄位 將目前時間加上 100 毫秒存入
DateAdd(ms,100,getdate());
-------------------------------------------------------------------------------------------------
//日期函數
SELECT GETDATE() AS Expr1 FROM Groups //2006/2/9 下午 02:30:57
SELECT DATEDIFF(dd, UpdateTime, GETDATE()) AS Expr1, UpdateTime FROM Groups //20 (GETDATE()-UpdateTime)
SELECT DATEADD(dd, 3, UpdateTime) AS Expr1 FROM Groups //2006/1/23 下午 05:45:18
SELECT CONVERT(VarChar, UpdateTime, 111) AS Expr1 FROM Groups //2006/01/20
SELECT DATENAME(mm, UpdateTime) AS Expr1, UpdateTime FROM Groups //一月
SELECT DATEPART(yy, UpdateTime) AS Expr1 FROM Groups //2006
yy 1753-9999 年份
qq 1-4 季
mm 1-12 月
dy 1-366 日
dd 1-31 日
wk 1-54 週
dw 1-7 週幾 dw-1= 0星期日 1星期一....6星期六
hh 0-23 小時
mi 0-59 分鐘
ss 0-59 秒
ms 0-999 毫秒
-------------------------------------------------------------------------------------------------
//CASE
SELECT ID,
CASE WHEN NAME = 'MARK' THEN 'BOY'
WHEN NAME = 'JENNY' THEN 'GIRL'
ELSE 'OTHER'
END AS NAME1
FROM Table1
Select case when sum(f4)=0 then 0 else (Sum(f1)+Sum(f2)+Sum(f3))/sum(f4) end From YouTable
---------------------------------------------------------------------------------------------------
//讀出資料庫中所有的Table Name
select name from sysobjects where (xtype in ('u','v')) and (status>=0) order by name
---------------------------------------------------------------------------------------------------
SELECT SUM(AMOUNT) AS TOT_AMT, NAME
FROM TABLE2
WHERE (EX_DATE = CONVERT(CHAR, GETDATE(), 111))
GROUP BY NAME
---------------------------------------------------------------------------------------------------
//Excel Sheet to SqlServer Table
SELECT * INTO newTable FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\test\test.xls;Extended Properties=Excel 8.0')...[Sheet1$]
newTable: 導入SqlServer 後要建立的表格名稱
Data Source: Excel 檔案位置
[Sheet1$] : Excel工作表名稱
---------------------------------------------------------------------------------------------------
//SqlServer Table to Excel Sheet
EXEC master..xp_cmdshell 'bcp TABLE1 out c:\test\test.xls -c -q -S"localhost" -U"sdm" -P"sdm"'
TABLE1: 導出的表格
c:\test\test.xls: 導至的Excel位置
-S"localhost" 資料庫位置
-U"sdm" 資料庫帳號
-P"sdm" 資料庫密碼
---------------------------------------------------------------------------------------------------
//從csv檔案 將資料導入指定的table
EXEC master..xp_cmdshell 'bcp "TEST..ImportCsv" IN c:\test\test1.csv -c -t , -S"localhost" -U"sdm" -P"sdm"'
資料庫名稱: TEST
導入的資料表名稱: ImportCsv
導入檔案: c:\test\test1.csv
server name: localhost
帳號: sdm
密碼: sdm
若出現帳號密碼無權限時,須將該帳號 加入 對 master 資料庫的存取權限
---------------------------------------------------------------------------------------------------
//從table 將資料導入指定的 csv檔案
EXEC master..xp_cmdshell 'bcp "TEST..ImportCsv" OUT c:\test\test1.csv -c -t , -S"localhost" -U"sdm" -P"sdm"'
---------------------------------------------------------------------------------------------------
//Access匯入至SQL
insert into Table(CustomerID, CompanyName)
SELECT CustomerID, CompanyName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb'; 'admin';'',Customers)
---------------------------------------------------------------------------------------------------
//Join 多個TABLE
SELECT DISTINCT A.NAME, B.ImgSize, C.Length
FROM TABLE1 A LEFT OUTER JOIN TABLE2 B ON A.Id = B.Id
RIGHT OUTER JOIN TABLE3 C ON A.Id = B.Id
---------------------------------------------------------------------------------------------------
//取消顯示系統資料庫及系統資訊
編輯SQL Server註冊屬性 / 將顯示系統資料庫及系統資訊取消
---------------------------------------------------------------------------------------------------
//取出Instance Name
sp_helpserver 取出Name屬性
若一台電腦上同時安裝了sql2000 sql2005則存取另一個資料庫時 Data Source = Servere Name\Instance Name
---------------------------------------------------------------------------------------------------
//sp_helpdb
查看所有資料庫的名稱 大小 擁有者 建立時間
---------------------------------------------------------------------------------------------------
//sp_changedbowner 'sa'
更改資料庫的擁有者
---------------------------------------------------------------------------------------------------
//SQL 2000 與 SQL 2005 並存
安裝時不需特別設定 但 instance 名稱不能相同
SQL Server 2000: <Servere Name>
SQL Server 2005: <Servere Name>\sql2005
---------------------------------------------------------------------------------------------------
//SQL Server 變更Server Name <只可修改Server Name，但是無法修改執行個體名稱>
若是電腦中只有一個Sql Server執行個體:
1. 先修改電腦名稱, 再將 SQL Server instance name做更改
2. select @@servername
3. sp_dropserver 'old_name'
GO
sp_addserver 'new_name', 'local'
GO
4. --重啟服務......
select @@servername
若是電腦中含有一個以上的Sql Server執行個體:
步驟3改為:
sp_dropserver 'old_servername\instancename'
GO
sp_addserver 'new_servername\instancename', 'local'
GO
---------------------------------------------------------------------------------------------------
//更改資料庫名稱
ALTER DATABASE oldDbName MODIFY NAME = newDbName
GO
若出現:"資料庫無法獨佔鎖定 來進行這個動作", 此時須將資料庫停止(非暫停),然後再將資料庫重新啟動
---------------------------------------------------------------------------------------------------
//移除SQL2005 Instance
1.開啟控制台中的「新增或移除程式」，選取「Microsoft SQL Sever 2005」，按下「變更」按鈕，在「Microsoft SQL Server 2005 維護」對話方塊中，選取列示於「SQL Server 2005 執行個體」中，您欲移除的執行個體名稱前面的 ○ 選取按鈕，使其變成 ⊙ ，接著按下「下一步」按鈕，然後依照畫面指示進行操作。
2.cmd regedit :HKEY_LOCAL_MACHINE SOFTWARE Microsoft MicrosoftSqlServer 刪除整個資料夾
3.使用Windows Installer Clean Up工具 移除
---------------------------------------------------------------------------------------------------
//資料庫定序
目前的資料庫定序:
SELECT DATABASEPROPERTYEX('Northwind', 'Collation')
Chinese_Taiwan_Stroke_CI_AS
目前這個資料庫的執行個體所使用的定序:
SELECT CONVERT(varchar, ServerProperty('collation'))
Chinese_Taiwan_Stroke_CI_AS
---------------------------------------------------------------------------------------------------
//刪除資料庫log檔
1.卸除資料庫
2.刪除檔案 C:\Program Files\Microsoft SQL Server\MSSQL\Data\資料庫名稱.ldf
3.重新附加資料庫即可
4.最後再去限制交易記錄檔的成長空間
---------------------------------------------------------------------------------------------------
//更改物件(TABLE VIEW SP...)擁有者
1. exec sp_changeobjectowner 'tablename','dbo'
2. exec sp_changeobjectowner "dws.tablename",'dbo'
3. 設計資料表/右鍵/屬性/資料表/擁有人
---------------------------------------------------------------------------------------------------
//更改資料庫擁有者
sp_changedbowner dbo
---------------------------------------------------------------------------------------------------
//隨機選取資料
SELECT TOP 6 * FROM dbo.AD ORDER BY NEWID()
---------------------------------------------------------------------------------------------------
//存取資料時的亂碼問題
insert into tablename(col1,col2) values(N'value1',N'value2');
---------------------------------------------------------------------------------------------------
//以sql語法 取得系統日期
SELECT DATENAME([year], GETDATE()) + '/' + RTRIM(CONVERT(char, DATEPART([month], GETDATE()))) + '/' + DATENAME([Day], GETDATE()) AS nowdate
執行結果:2006/5/29
SELECT CONVERT(VarChar, GETDATE(), 111) AS newdate
執行結果:2006/05/29
SELECT CONVERT(datetime, GETDATE(), 111) AS newdate
執行結果:2006/5/29 下午 07:49:33
---------------------------------------------------------------------------------------------------
//建立流水號 20060727235912233(年+月+日+時+分+秒+毫秒)
SELECT CAST(YEAR(GETDATE()) AS Varchar(4)) + REPLICATE('0',
2 - LEN(MONTH(GETDATE()))) + CAST(MONTH(GETDATE()) AS Varchar(2))
+ REPLICATE('0', 2 - LEN(DAY(GETDATE()))) + CAST(DAY(GETDATE())
AS Varchar(2)) + REPLICATE('0', 2 - LEN(DATEPART([Hour], GETDATE())))
+ CAST(DATEPART([Hour], GETDATE()) AS Varchar(2)) + REPLICATE('0',
2 - LEN(DATEPART([Minute], GETDATE()))) + CAST(DATEPART([Minute],
GETDATE()) AS Varchar(2)) + REPLICATE('0', 2 - LEN(DATEPART([Second],
GETDATE()))) + CAST(DATEPART([Second], GETDATE()) AS Varchar(2))
+ REPLICATE('0', 3 - LEN(DATEPART(MilliSecond, GETDATE())))
+ CAST(DATEPART(MilliSecond, GETDATE()) AS Varchar(3)) AS SerialNumber
---------------------------------------------------------------------------------------------------
//取得資料為第幾筆資料
SELECT NAME, IDENTITY(INT, 1, 1) AS seq INTO #tmp FROM Table1 ;
SELECT * FROM #tmp;
--------SQL 2005----------
SELECT NAME, ROW_Number OVER (ORDER BY NAME) AS seq FROM Table1;
---------------------------------------------------------------------------------------------------
//取得INSERT的 IDENTITY
insert into Table1 (NAME,ADDR,SEX) values('MARK','Taipei','F');SELECT SCOPE_IDENTITY() AS SEQ
---------------------------------------------------------------------------------------------------
//星期五
SELECT CASE
when DATEPART(dw, getdate()) = 1 THEN '星期日'
when DATEPART(dw, getdate()) = 2 THEN '星期一'
when DATEPART(dw, getdate()) = 3 THEN '星期二'
when DATEPART(dw, getdate()) = 4 THEN '星期三'
when DATEPART(dw, getdate()) = 5 THEN '星期四'
when DATEPART(dw, getdate()) = 6 THEN '星期五'
when DATEPART(dw, getdate()) = 7 THEN '星期六'
END AS WEEK
---------------------------------------------------------------------------------------------------
//星期五
SELECT '星期' + SUBSTRING('日一二三四五六', DATEPART(dw, GETDATE()), 1) AS WEEK
---------------------------------------------------------------------------------------------------
//取得星期幾
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS Expr1, DATENAME(dw, GETDATE()) AS Expr2 //2007-04-09 星期一
---------------------------------------------------------------------------------------------------
//今天開始後的七天(不包含六日)
select dateadd(day,case when datepart(weekday,getdate()) < 5 then 9 else 11 end,getdate())
--6-1(星期五)
select datepart(weekday,getdate())
---------------------------------------------------------------------------------------------------
//讓自動遞增的欄位重新計算
DBCC CHECKIDENT (@tableName, RESEED, 1)
---------------------------------------------------------------------------------------------------
//SQL 2000 取出 11~20筆資料
SELECT TOP 10 * FROM Table1 WHERE (ID NOT IN (SELECT TOP 10 ID FROM Table1))
---------------------------------------------------------------------------------------------------
//SQL 2005 取出 11~20筆資料
select * from (select *, row_number() over(order by DatabaseLogID) cn from DatabaseLog) temp where cn between 11 and 20
---------------------------------------------------------------------------------------------------
//取出不重複的所有資料
SELECT * FROM Forum_Detail
WHERE (id IN (SELECT MAX(id) FROM Forum_Detail GROUP BY auther))
---------------------------------------------------------------------------------------------------
//產生 <正項加總 負項加總 正項加總+負項加總> 欄位
TABLE: SALES
COLUMN: AMOUNT (int)
以 欄位AMOUNT 產生 三個新增欄位 (PLUS:正項加總 MINUES:負項加總 TOTAL: PLUS + MINUES)
SELECT SUM(CASE WHEN AMOUNT > 0 THEN AMOUNT ELSE 0 END) AS PLUS,
SUM(CASE WHEN AMOUNT < 0 THEN AMOUNT ELSE 0 END) AS MINUES,
SUM(AMOUNT) AS TOTAL
FROM SALES
----------------------
PLUS MINUES TOTAL
27 -16 11
---------------------------------------------------------------------------------------------------
//查詢資料庫所有Table的Primary Key與Foreign Key
select * From INFORMATION_SCHEMA.KEY_COLUMN_USAGE
---------------------------------------------------------------------------------------------------
//清除表格中的所有資料
TRUNCATE TABLE 'Table1'
---------------------------------------------------------------------------------------------------
//清空資料庫所有資料表內的資料.
USE '資料庫'
GO
EXEC sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"
---------------------------------------------------------------------------------------------------
//判斷資料表是否存在
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_NAME = 'Groups') //Groups
---------------------------------------------------------------------------------------------------
//統計欄位中相同欄位值的筆數
SELECT MEMO, COUNT(MEMO) AS Total_Amount FROM Table1 GROUP BY MEMO
---------------------------------------------------------------------------------------------------
//子查詢
SELECT * FROM (SELECT * FROM Table1 WHERE Bit_Type = 0) WHERE ID >= 11
---------------------------------------------------------------------------------------------------
//LinkServer
sp_addlinkedserver @server = N'LinkServer',
@srvproduct = N' ',
@provider = N'SQLOLEDB',
@datasrc = N'ServerNetName',
@catalog = N'Northwind'
GO
SELECT *
FROM LinkServer.Northwind.dbo.Shippers //server_name.db_name.dbo.table_name
GO
---------------------------------------------------------------------------------------------------
//SELECT... INTO... <Table3必須不存在,sql執行完後 會自動建立一個新的Table(Table3)>
SELECT ID, NAME, ADDR
INTO Table3
FROM Table1 T1
---------------------------------------------------------------------------------------------------
//INSERT INTO... SELECT... <Table2必須為已存在的資料表>
INSERT INTO Table2 (NAME2, ADDR2)
SELECT NAME, ADDR
FROM Table1
---------------------------------------------------------------------------------------------------
//內建數學函式
SELECT ROUND(100.1264, 2) AS Expr1 100.13
SELECT CEILING(123.45) AS Expr1 124
SELECT CEILING(- 123.65) AS Expr1 -123
SELECT FLOOR(123.45) AS Expr1 123
SELECT FLOOR(- 123.45) AS Expr1 -124
SELECT ABS(- 1.0) AS Expr1 1
SELECT POWER(2, 3) AS Expr1 8
SELECT SQUARE(9) AS Expr1 81
SELECT SQRT(9) AS Expr1 3
---------------------------------------------------------------------------------------------------
//取出字串最左邊或最右邊的指定常度子字串
SELECT RIGHT(forumId, 4) AS Result FROM Forum_Detail
SELECT LEFT(forumId, 4) AS Result FROM Forum_Detail
---------------------------------------------------------------------------------------------------
//CHARINDEX LEN
SELECT CHARINDEX('k', NAME) AS Expr1 FROM Table1 //JeckChen 4
SELECT LEN(NAME) AS Expr1 FROM Table1 //JeckChen 8
---------------------------------------------------------------------------------------------------
//預設值
datetime (getdate())
uniqueidentifier (newid())
---------------------------------------------------------------------------------------------------
//REVERSE
SELECT REVERSE(GroupName) AS Expr1 FROM Groups //組群員人務業
---------------------------------------------------------------------------------------------------
//replace
update tableName set columnName = replace(columnName , "oldString", "newString")
---------------------------------------------------------------------------------------------------
//隨機取出前五筆資料
select top 5 * from tableName order by newId()
---------------------------------------------------------------------------------------------------
//匯入圖檔的SQL<僅限SQL2005>
INSERT INTO Table1 SELECT * FROM OPENROWSET( BULK 'D:\d2.jpg', SINGLE_BLOB) AS x
----------------------------
SQL Server 2005
--建立資料表
CREATE TABLE dbo.練習資料表(
編號 int IDENTITY(1,1) NOT NULL,
姓名 nvarchar(10) NULL,
性別 nvarchar(2) NULL,
自傳 nvarchar(max) NULL,
玉照 varbinary(max) NULL,
);
GO
--新增第一筆資料(包括 NCLOB 與 BLOB 在內)
INSERT INTO dbo.練習資料表
(
姓名,
性別,
自傳,
玉照
)
SELECT '章立民' AS 姓名, '男' AS 性別,
* FROM
OPENROWSET
(BULK 'C:\Temp\Employee-A156401174.txt', SINGLE_NCLOB) AS 自傳,
OPENROWSET
(BULK 'C:\Temp\Employee-A156401174.jpg', SINGLE_BLOB) AS 玉照;
GO
---------------------------------------------------------------------------------------------------
//查詢SqlServer 錯誤代碼
select * from master.dbo.sysmessages where error = 20055
---------------------------------------------------------------------------------------------------
//取得指定Table中所有欄位的相關資訊
SELECT * FROM Information_Schema.COLUMNS where TABLE_NAME='ACCOUNT'
---------------------------------------------------------------------------------------------------
//取得識別值
IDENT_CURRENT 會傳回在任何工作階段和任何範圍中，產生給特定資料表的最後一個識別值。
@@IDENTITY 會傳回所有範圍的目前工作階段中，任何資料表所產生的最後一個識別值。
SCOPE_IDENTITY 會傳回在目前工作階段以及目前範圍中，任何資料表產生的最後一個識別值。
-------------------------------------------------------
SELECT IDENT_CURRENT('Table1') AS MaxIdentity //39
SELECT @@IDENTITY //null
SELECT SCOPE_IDENTITY() //null
----------------after insert-----------------------------
insert into Table1(name) values('test1')
SELECT IDENT_CURRENT('Table1') AS MaxIdentity //40
SELECT @@IDENTITY //40
SELECT SCOPE_IDENTITY() //40
---------------------------------------------------------------------------------------------------
//TimeStamp欄位型態
資料庫中的值新增或修改時 會自動更新,主要可使用在 防止同一資料同一時間被重複修改
可於取出資料欲修改時Select * , (TS + 0) AS TSTAMP from Table4 將資料和TimeStamp欄位的值一並取出
於寫回資料庫時 比對TimeStamp欄位是否相同 若不同表示已被其他使用者更新過
---------------------------------------------------------------------------------------------------
//計算兩個日期區間相差的月數及日數
SELECT STARTDATE , ENDDATE ,
CASE WHEN DAY(ENDDATE) >= DAY(STARTDATE) THEN DATEDIFF(MONTH, STARTDATE, ENDDATE)
ELSE DATEDIFF(MONTH, STARTDATE, ENDDATE) -1
END as 月數
,
CASE
WHEN DAY(ENDDATE) >= DAY(STARTDATE) THEN DAY(ENDDATE) - DAY(STARTDATE)
ELSE DAY(DATEADD(MONTH, DATEDIFF(MONTH, 0, ENDDATE), -1)) - DAY(STARTDATE) + DAY(ENDDATE)
END as 日數
FROM TABLE1
---------------------------------------------------------------------------------------------------
//取得一個月內的資料
SELECT STARTDATE , ENDDATE FROM TABLE1 WHERE DATEDIFF(MM,ENDDATE,GETDATE()) <= 1
---------------------------------------------------------------------------------------------------
//計算兩個日期區間相差的月數及日數
SELECT *,
CASE WHEN DAY(ENDTIME) >= DAY(STARTTIME)
THEN DATEDIFF(MONTH, STARTTIME, ENDTIME)
ELSE DATEDIFF(MONTH, STARTTIME, ENDTIME) - 1
END AS 月數,
CASE WHEN DAY(ENDTIME) >= DAY(STARTTIME)
THEN DAY(ENDTIME) - DAY(STARTTIME)
ELSE DAY(DATEADD(MONTH, DATEDIFF(MONTH, 0, ENDTIME), - 1)) - DAY(STARTTIME) + DAY(ENDTIME)
END AS 日數
FROM Table1
---------------------------------------------------------------------------------------------------
//抓出同一TABLE中, SEX欄位中 重複值的前兩筆資料
SELECT * FROM Table1 A
WHERE (ID IN
(SELECT TOP 2 ID FROM Table1 B WHERE A.SEX = B.SEX ORDER BY ID ASC)
)
ORDER BY SEX
---------------------------------------------------------------------------------------------------
//cmd斷掉所有connection
SP_Who可以看到目前的連線，50以下為系統所用
exec sp_who
kill 56
---------------------------------------------------------------------------------------------------
//搜尋欄位中特定字元的位置(若無符合字元 則傳回0;可用以代替like)
select * from Table1 where charindex('xxx',Column1) > 0
SELECT * FROM ACCOUNT WHERE RTRIM( LTRIM(A_USERNAME)) LIKE '陳%'
SELECT * FROM ACCOUNT WHERE CHARINDEX( '陳' , RTRIM( LTRIM(A_USERNAME)) ) = 2
---------------------------------------------------------------------------------------------------
//檢查有多少人連至資料庫的語法
SELECT cntr_value AS User_Connections FROM master..sysperfinfo as p
WHERE p.object_name = 'SQLServer:General Statistics' And p.counter_name = 'User Connections'
---------------------------------------------------------------------------------------------------
//計算TEXT欄位型態內容長度 (中Aa1皆為一個字元長度)
SELECT LEN(CONVERT(VARCHAR(8000), USERNAME)) AS RESULT FROM Table2
SELECT LEN(CAST(USERNAME AS VARCHAR(8000))) AS RESULT FROM Table2
SELECT DATALENGTH(USERNAME) AS RRSULT FROM Table2
---------------------------------------------------------------------------------------------------
//DB LOCK
--將大型的複雜查詢分解成幾個比較簡單的查詢
--一個用戶端應用程式執行緒開啟了兩個連線
--避免使用 SELECT *
SELECT COUNT(*) FROM RES_MAIN_TOT WITH(NOLOCK) WHERE R_NO > 1000 --被鎖定(INSERT UPDATE)的紀錄一併顯示
SELECT COUNT(*) FROM RES_MAIN_TOT WITH(READPAST) --被鎖定的紀錄不顯示
---------------------------------------------------------------------------------------------------
//新增資料時 指定識別欄位的值
SET IDENTITY_INSERT Table1 ON
INSERT INTO Table1(ID, NAME, COUNTRY)
VALUES (23, 'WhenChang', 'America')
---------------------------------------------------------------------------------------------------
--資料庫或資料表的大小
EXEC SP_SPACEUSED
--database_name :: 資料庫名稱
--database_size ::資料庫的大小
--unallocated space ::資料庫的未配置空間
--reserved ::保留的總空間
--Data ::資料使用的總空間
--index_size ::索引使用的空間
--Unused ::未使用的總空間
EXEC SP_SPACEUSED 'JOB_MAIN_TOT'
--Name :: 資料表的名稱
--Rows ::資料列數
--reserved ::保留的總空間
--Data ::資料使用的總空間
--index_size ::索引使用的空間
--Unused ::未使用的總空間
---------------------------------------------------------------------------------------------------
SP_HELPSERVER --INSTANCE NAME
SP_HELPDB --所有資料庫的大小 相容性
SP_HELPUSER --單一資料庫的所有使用者相關資訊
SP_HELP 'Table_Name' --單一資料庫所有Table的欄位型態 大小
SP_SPACEUSED --單一資料庫資料量大小
SP_SPACEUSED 'Table_Name' --單一資料表的資料筆數 資料量大小
---------------------------------------------------------------------------------------------------
-- 取出資料時 自行增加自動編號欄位
SELECT S_NO = IDENTITY(INT, 1, 1), *
INTO #TempTable
FROM ( SELECT TOP 20 * FROM MYBOOK) A
SELECT * FROM #TempTable
DROP TABLE #TempTable
---------------------------------------------------------------------------------------------------
//TABLE1(NAME,CODE) ; TABLE2(NAME2,CODE2) 將 SOURE2.CODE2 的資料設定給 SOURCE.NAME
UPDATE SOURCE SET CODE = (SELECT CODE2 FROM SOURCE2 WHERE SOURCE.NAME = SOURCE2.NAME2)
---------------------------------------------------------------------------------------------------
//跨SQL server新增資料
利用 sp_addlinkedserver 語法或
SQL Server Enterprise Manager --> ServerName --> 安全性 --> 連結伺服器 去設定遠端伺服器
之後在本端下 Select * from [ServerName/IP].[DBNAME].[Onwer].[TableName]
---------------------------------------------------------------------------------------------------
//判斷欄位字串是否為日期格式 (CDATE為VARCHAR)
SELECT CASE WHEN ISDATE(CDATE) <> 0 THEN CDATE ELSE 'NODATA' END AS 開始日期 FROM Table1
---------------------------------------------------------------------------------------------------
// 隨機取出十筆資料
SELECT TOP 10 * FROM ACCOUNT ORDER BY NEWID()
---------------------------------------------------------------------------------------------------
//PRINT & RAISERROR
IF EXISTS(
SELECT * FROM ACCOUNT WHERE A_NO = '262823'
)
PRINT 'DATA ALREADY EXISTS'
ELSE
RAISERROR('SQL STATEMENT WAS ERROR',16,1) --伺服器: 訊息 50000，層級 16，狀態 1，行 6
---------------------------------------------------------------------------------------------------
//查詢欄位中的特殊符號
想要查詢出地址資料中包含底線符號 _
SELECT NAME,ADDR FROM TABLE1 WHERE ADDR LIKE '%\_%' ESCAPE '\'
想要查詢出地址資料中包含百分符號 %
SELECT NAME,ADDR FROM TABLE1 WHERE ADDR LIKE '%\%%' ESCAPE '\'
---------------------------------------------------------------------------------------------------
//選出每個縣市中人數較多的那筆資料
人數 政黨 縣市
4 中國國民黨 台中縣
1 無黨團結聯盟 台中縣
8 中國國民黨 高雄縣
10 中國國民黨 高雄縣
SELECT MAX(人數), (SELECT TOP 1 政黨 FROM 表格 B WHERE B.縣市=A.縣市 ORDER BY 人數 ) AS 政黨, 縣市 FROM 表格 A GROUP BY 縣市
---------------------------------------------------------------------------------------------------
//將字串中的某一區段字串取代為其他字串
SELECT STUFF(A_USERID,2,3,'XXX') FROM ACCOUNT WHERE A_USERID = 'D122136512' -- D122136512 DXXX136512
---------------------------------------------------------------------------------------------------
//計算字串在字串中出現的次數
test在testatesta(content) 中出現的次數
select content,(len(content)-len(replace(content,'test','')))/4 as num from table order by num desc
或
select content,(len(content)-len(replace(content,'test','')))/len('test') as num from table order by num desc
---------------------------------------------------------------------------------------------------
//分群取最小值
SELECT MIN(DATET) , ID FROM Table3 GROUP BY ID
select cast(substr (c1,1,1) as integer) from t1
select substr (c1,1,2) from t1 //DB2取出欄位值並擷取其長度[欄位名稱,開始(從1起始),長度],MS SQL使用substring
------------------------------------------------------------------------------------------------------
create table t1(c1 varchar(8) not null,c2 varchar(5), c3 int(3)) //建立table
drop table t1 //刪除table
alert table t1 add c1 varchar(5) default 0 not null //新增table欄位
alter table t1 drop column c1 //刪除table欄位
alter table t1 modify c1 varchar(8) //修改table欄位
alter table t1 rename column c1 to c4 //更改欄位名稱
-----------------------------------------------------------------------
insert into t1(c1,c2,c3) values('str1','str2',int1) //新增
-----------------------------------------------------------------------
delete from t1 where c1='str1' //刪除
-----------------------------------------------------------------------
update t1 set c1='str1',c2='str2',c3=int1 where c1='str3' //修改
-----------------------------------------------------------------------
int i=stmt.executeUpdate(); //取得更新資料的筆數(判斷對資料庫動作是否執行成功)
i == 0 ? false : true
------------------------------------------------------------------------------
取得ResultSet傳回資料筆數
int count=0;
while(rs.next()){
rs.getString("c1");
count++;
}
------------------------------------------------------------------------------
// ResultMetaData
ResultSet rs=stmt.executeQuery(sql);
ResultMetaData rmd=rs.getMetaData();
int a=rs.findColumn("欄位名稱"); //取得欄位編號
int i=rmd.getColumnCount(); //取得table內欄位數目
String j=rmd.getColumnName(欄位編號1~); //取得欄位名稱
String j=rmd.getColumnLabel(欄位編號1~); //取得欄位名稱
String k=rmd.getColumnType(欄位編號); //取得欄位資料型態
int l=rmd.getColumnDisplaySize(欄位編號); //取得欄位資料長度
------------------------------------------------------------------------------
//and or > <
where id=5 and year>30 or year<20 order by id desc
--------------------------------------------------------------------------------------------------------
//between and
select * from t1 where c1='12345' and c2='1' and c3 between '930101' and '931231' order by c1
------------------------------------------------------------------------------
//distinct 去除重複值
select distinct c1 from t1 where c2 between '2004/04/01' and '2004/05/01'
--------------------------------------------------------------------------------------------------
//order by c1 desc 由大至小排序
--------------------------------------------------------------------------------------------------
// 取得欄位資料的筆數
select count(c1) 總計筆數 from t1 where c2='A'
int i=rs.getInt("總計筆數"); //用別名取出欄位資料的筆數
或int i=rs.getInt(1); // select出來的第一個欄位的值,編號從一開始
-------------------------------------------------------------------------------------------------
// 取得欄位內資料的加總值(欄位型態需為int) sum()一定要做group by敘述
select sum(c1) 合計金額 from t1 where c1='A' group by c1
int i=rs.getInt("合計金額"); //用別名取出欄位內資料的加總值
或int i=rs.getInt(1);
-------------------------------------------------------------------------------------------------
// COUNT(),SUM()
select c1 , count(c1) from t1 group by c1 order by c1
select c1 , sum(c1) from t1 group by c1 order by c1
-------------------------------------------------------------------------------------------------
//MAX() MIN() AVG() MAX() MIN()對日期也有效 如求得最新日期
select MAX(c1),MIN(c1),AVG(c1) from t1
-------------------------------------------------------------------------------------------------
// IN , NOT IN
select c1 from t1 where c2 IN (1,2,3)
可改寫為 select c1 from t1 where c2=1 OR c2=2 OR c2=3
select c1 from t1 where c2 NOT IN (1,2,3)
-------------------------------------------------------------------------------------------------
//HAVING 須先群組化group by之後,才可使用having敘述
--查詢資料表中重複的電子郵件名單
SELECT R_EMAIL , COUNT(R_EMAIL) AS CACL FROM Res_Main_Tot GROUP BY R_EMAIL HAVING COUNT(R_EMAIL) > 1
-------------------------------------------------------------------------------------------------------------
//GROUP BY
只有出現在Group By子句中的屬性，才可出現在Select子句中
SELECT x, sum(y) FROM table1 GROUP BY x;
-------------------------------------------------------------------------------------------------
//IS NULL
SELECT TOP 100 * , ISNULL(SUM(A.Price),0) FROM A //若取出的值為NULL 則給予0
-------------------------------------------------------------------------------------------------
//比較運算式
WHERE C1 = 'A'
WHERE C1 != 'A'
WHERE C1 <> 'A'
WHERE C1 IS NULL
WHERE C1 IS NOT NULL
WHERE C1 LIKE '%STR%'
WHERE C1 NOT LIKE '%STR%'
所有運算式列表:
確定範圍：Between A and B、Not Between A and B
確定集合：IN、NOT IN
字符匹配：LIKE，NOT LIKE
空值：IS NULL、IS NOT NULL
多重條件：AND、OR、NOT
所有運算元列表:
運算符：>、>=、=、<=、<、 <>
-------------------------------------------------------------------------------------------------
//聚合函數
平均值：Avg（ALL|DISTINCT<欄位名稱>） 欄位名稱須為數值型態
總和： Sum（ALL|DISTINCT<欄位名稱>） 欄位名稱須為數值型態
最小值：Min（ALL|DISTINCT<欄位名稱>）
最大值：Max（ALL|DISTINCT<欄位名稱>）
計數： Count（ALL|DISTINCT<欄位名稱>）
-------------------------------------------------------------------------------------------------
//LIKE
限制以Publishing結尾，使用LIKE '%Publishing'
限制以A開頭：LIKE '[A]%'
限制以A開頭外：LIKE '[^A]%'
[]——指定範圍 ([A-F]) 或集合 ([ABCDEF]) 中的任何單個字符。
[^]——不屬於指定範圍 ([^A-F]) 或集合 ([^ABCDEF]) 的任何單個字符
-------------------------------------------------------------------------------------------------
//AS別名c3
SELECT SUM(CAST(c1 AS INTEGER)) + SUM(CAST(c2 AS INTEGER)) AS c3 FROM T1 WHERE c1 != '' GROUP BY C1 ORDER BY c1
SELECT SUM(CAST(c1 AS INTEGER)) c3 FROM T1 WHERE c1 != '' GROUP BY c1 ORDER BY c1
------------------------------------------------------------------------------------------------------------------------
//取得欄位內容的加總值
SELECT SUM(CAST(c1 AS INTEGER)) + SUM(CAST(c2 AS INTEGER)) AS C3 , SUM(CAST(C4 AS INTEGER)) FROM T1 WHERE C1 != '' AND SUBSTR(C2,1,4) IN ( '0', '1', '2', '3') AND CAST( SUBSTR (C3,1,4) AS INTEGER ) = " + strDATE + " GROUP BY C2 , C3 ORDER BY C1
---------------------------------------------------------------------------------------------------------------
// 外部結合 OUTER JOIN 將複數個TABLE結合為一個TABLE 並取得指定欄位的值
SELECT t1.* , t2.c2 ,t3.c3, FROM t2
RIGHT OUTER JOIN t1 ON t2.c5 = t1.c5
LEFT OUTER JOIN t3 ON t1.c1 = t3.c6
where t1.c7 like '9004100%' AND t1.c8 IN ('A','B') AND t1.c8 < '00930231' AND t2.c2 <> '99999999' OR t2.c2 IS NULL
order by t1.c7
------------------------------------------------------------------------------------------------------------------
//集合運算子 UNION 同時取得兩個TABLE中的指定欄位值,不包含兩TABLE中欄位的重複值(重複值只會列出一筆)
兩列select語法的欄位資料型態和欄位數目必須相同
若要以order by 排序union得結果時,order by一定要寫在最後的select的語法後面
當各select語法中的欄位名稱不同時,就要在order by語法中指定欄位編號 如order by 1
SELECT C1, C2, SUM(C3) FROM T1 WHERE C1 != '' GROUP BY C1
UNION (
SELECT C1 ,C2, SUM(C3) FROM T2 WHERE C2 != '' GROUP BY C1 ORDER BY C1)
-------------------------------------------------------------------------------------------------
//集合運算子 UNION ALL 同時取得兩個TABLE中的指定欄位值,包含兩TABLE中欄位的重複值(重複值會全部列出)
SELECT C1, C2, SUM(C3) FROM T1 WHERE C1 != '' GROUP BY C1
UNION ALL(
SELECT C1 ,C2, SUM(C3) FROM T2 WHERE C2 != '' GROUP BY C1 ORDER BY C1)
-------------------------------------------------------------------------------------------------
//UNION時 若兩TABLE欄位數目不同(其對應的欄位資料型態必須相同)
select a, b, '' as c, d ,0 as e from A
union
select a, b, c, '' as d, e from B
-------------------------------------------------------------------------------------------------
//權限
GRANT ALL ON t1 TO PUBLIC
REVOKE ALL ON t1 FROM PUBLIC
-------------------------------------------------------------------------------------------------
//取得一個月內的資料
select * from table where date >= dateadd(m,-1,getdate())
-------------------------------------------------------------------------------------------------
//SQL指令取得2005年2月份所有資料
Select * From table1 Where Year(時間欄位) = 2005 And Month(時間欄位) = 2
-------------------------------------------------------------------------------------------------
//MS SQL
SELECT TABLE1.A, TABLE2.D FROM TABLE1, TABLE2 WHERE TABLE1.A = TABLE2.D
SELECT TABLE1.A, TABLE2.D FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.A = TABLE2.D
SELECT A FROM TABLE1 GROUP BY A HAVING A = '123'
SELECT SUM(CAST(A AS INTEGER)) AS COUNT1 FROM TABLE1
SELECT A FROM TABLE1 WHERE A LIKE '%12%'
SELECT A, SUM(B) AS Expr1 FROM TABLE1 GROUP BY A
SELECT A, SUM(B) AS TOTAL FROM TABLE1 GROUP BY A HAVING SUM(B) > 50 ORDER BY A DESC
-------------------------------------------------------------------------------------------------
//從日期型態轉換為指定格式的字串型態
SELECT CONVERT(CHAR, date_TIME, 101) AS EXECUTE_TIME FROM TABLE1 07/04/2005
SELECT CONVERT(CHAR, date_TIME, 102) AS EXECUTE_TIME FROM TABLE1 2005.07.04
SELECT CONVERT(CHAR, date_TIME, 103) AS EXECUTE_TIME FROM TABLE1 04/07/2005
SELECT CONVERT(CHAR, date_TIME, 104) AS EXECUTE_TIME FROM TABLE1 04.07.2005
SELECT CONVERT(CHAR, date_TIME, 105) AS EXECUTE_TIME FROM TABLE1 04-07-2005
SELECT CONVERT(CHAR, date_TIME, 106) AS EXECUTE_TIME FROM TABLE1 04 07 2005
SELECT CONVERT(CHAR, date_TIME, 110) AS EXECUTE_TIME FROM TABLE1 07-04-2005
SELECT CONVERT(CHAR, date_TIME, 111) AS EXECUTE_TIME FROM TABLE1 2005/07/04
//從字串形態轉換為日期型態
CONVERT(DATETIME, '2005/07/04')
CAST('2005/07/04' AS DATETIME)
-------------------------------------------------------------------------------------------------
//日期型態欄位 將目前時間加上 100 毫秒存入
DateAdd(ms,100,getdate());
-------------------------------------------------------------------------------------------------
//日期函數
SELECT GETDATE() AS Expr1 FROM Groups //2006/2/9 下午 02:30:57
SELECT DATEDIFF(dd, UpdateTime, GETDATE()) AS Expr1, UpdateTime FROM Groups //20 (GETDATE()-UpdateTime)
SELECT DATEADD(dd, 3, UpdateTime) AS Expr1 FROM Groups //2006/1/23 下午 05:45:18
SELECT CONVERT(VarChar, UpdateTime, 111) AS Expr1 FROM Groups //2006/01/20
SELECT DATENAME(mm, UpdateTime) AS Expr1, UpdateTime FROM Groups //一月
SELECT DATEPART(yy, UpdateTime) AS Expr1 FROM Groups //2006
yy 1753-9999 年份
qq 1-4 季
mm 1-12 月
dy 1-366 日
dd 1-31 日
wk 1-54 週
dw 1-7 週幾 dw-1= 0星期日 1星期一....6星期六
hh 0-23 小時
mi 0-59 分鐘
ss 0-59 秒
ms 0-999 毫秒
-------------------------------------------------------------------------------------------------
//CASE
SELECT ID,
CASE WHEN NAME = 'MARK' THEN 'BOY'
WHEN NAME = 'JENNY' THEN 'GIRL'
ELSE 'OTHER'
END AS NAME1
FROM Table1
Select case when sum(f4)=0 then 0 else (Sum(f1)+Sum(f2)+Sum(f3))/sum(f4) end From YouTable
---------------------------------------------------------------------------------------------------
//讀出資料庫中所有的Table Name
select name from sysobjects where (xtype in ('u','v')) and (status>=0) order by name
---------------------------------------------------------------------------------------------------
SELECT SUM(AMOUNT) AS TOT_AMT, NAME
FROM TABLE2
WHERE (EX_DATE = CONVERT(CHAR, GETDATE(), 111))
GROUP BY NAME
---------------------------------------------------------------------------------------------------
//Excel Sheet to SqlServer Table
SELECT * INTO newTable FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\test\test.xls;Extended Properties=Excel 8.0')...[Sheet1$]
newTable: 導入SqlServer 後要建立的表格名稱
Data Source: Excel 檔案位置
[Sheet1$] : Excel工作表名稱
---------------------------------------------------------------------------------------------------
//SqlServer Table to Excel Sheet
EXEC master..xp_cmdshell 'bcp TABLE1 out c:\test\test.xls -c -q -S"localhost" -U"sdm" -P"sdm"'
TABLE1: 導出的表格
c:\test\test.xls: 導至的Excel位置
-S"localhost" 資料庫位置
-U"sdm" 資料庫帳號
-P"sdm" 資料庫密碼
---------------------------------------------------------------------------------------------------
//從csv檔案 將資料導入指定的table
EXEC master..xp_cmdshell 'bcp "TEST..ImportCsv" IN c:\test\test1.csv -c -t , -S"localhost" -U"sdm" -P"sdm"'
資料庫名稱: TEST
導入的資料表名稱: ImportCsv
導入檔案: c:\test\test1.csv
server name: localhost
帳號: sdm
密碼: sdm
若出現帳號密碼無權限時,須將該帳號 加入 對 master 資料庫的存取權限
---------------------------------------------------------------------------------------------------
//從table 將資料導入指定的 csv檔案
EXEC master..xp_cmdshell 'bcp "TEST..ImportCsv" OUT c:\test\test1.csv -c -t , -S"localhost" -U"sdm" -P"sdm"'
---------------------------------------------------------------------------------------------------
//Access匯入至SQL
insert into Table(CustomerID, CompanyName)
SELECT CustomerID, CompanyName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb'; 'admin';'',Customers)
---------------------------------------------------------------------------------------------------
//Join 多個TABLE
SELECT DISTINCT A.NAME, B.ImgSize, C.Length
FROM TABLE1 A LEFT OUTER JOIN TABLE2 B ON A.Id = B.Id
RIGHT OUTER JOIN TABLE3 C ON A.Id = B.Id
---------------------------------------------------------------------------------------------------
//取消顯示系統資料庫及系統資訊
編輯SQL Server註冊屬性 / 將顯示系統資料庫及系統資訊取消
---------------------------------------------------------------------------------------------------
//取出Instance Name
sp_helpserver 取出Name屬性
若一台電腦上同時安裝了sql2000 sql2005則存取另一個資料庫時 Data Source = Servere Name\Instance Name
---------------------------------------------------------------------------------------------------
//sp_helpdb
查看所有資料庫的名稱 大小 擁有者 建立時間
---------------------------------------------------------------------------------------------------
//sp_changedbowner 'sa'
更改資料庫的擁有者
---------------------------------------------------------------------------------------------------
//SQL 2000 與 SQL 2005 並存
安裝時不需特別設定 但 instance 名稱不能相同
SQL Server 2000: <Servere Name>
SQL Server 2005: <Servere Name>\sql2005
---------------------------------------------------------------------------------------------------
//SQL Server 變更Server Name <只可修改Server Name，但是無法修改執行個體名稱>
若是電腦中只有一個Sql Server執行個體:
1. 先修改電腦名稱, 再將 SQL Server instance name做更改
2. select @@servername
3. sp_dropserver 'old_name'
GO
sp_addserver 'new_name', 'local'
GO
4. --重啟服務......
select @@servername
若是電腦中含有一個以上的Sql Server執行個體:
步驟3改為:
sp_dropserver 'old_servername\instancename'
GO
sp_addserver 'new_servername\instancename', 'local'
GO
---------------------------------------------------------------------------------------------------
//更改資料庫名稱
ALTER DATABASE oldDbName MODIFY NAME = newDbName
GO
若出現:"資料庫無法獨佔鎖定 來進行這個動作", 此時須將資料庫停止(非暫停),然後再將資料庫重新啟動
---------------------------------------------------------------------------------------------------
//移除SQL2005 Instance
1.開啟控制台中的「新增或移除程式」，選取「Microsoft SQL Sever 2005」，按下「變更」按鈕，在「Microsoft SQL Server 2005 維護」對話方塊中，選取列示於「SQL Server 2005 執行個體」中，您欲移除的執行個體名稱前面的 ○ 選取按鈕，使其變成 ⊙ ，接著按下「下一步」按鈕，然後依照畫面指示進行操作。
2.cmd regedit :HKEY_LOCAL_MACHINE SOFTWARE Microsoft MicrosoftSqlServer 刪除整個資料夾
3.使用Windows Installer Clean Up工具 移除
---------------------------------------------------------------------------------------------------
//資料庫定序
目前的資料庫定序:
SELECT DATABASEPROPERTYEX('Northwind', 'Collation')
Chinese_Taiwan_Stroke_CI_AS
目前這個資料庫的執行個體所使用的定序:
SELECT CONVERT(varchar, ServerProperty('collation'))
Chinese_Taiwan_Stroke_CI_AS
---------------------------------------------------------------------------------------------------
//刪除資料庫log檔
1.卸除資料庫
2.刪除檔案 C:\Program Files\Microsoft SQL Server\MSSQL\Data\資料庫名稱.ldf
3.重新附加資料庫即可
4.最後再去限制交易記錄檔的成長空間
---------------------------------------------------------------------------------------------------
//更改物件(TABLE VIEW SP...)擁有者
1. exec sp_changeobjectowner 'tablename','dbo'
2. exec sp_changeobjectowner "dws.tablename",'dbo'
3. 設計資料表/右鍵/屬性/資料表/擁有人
---------------------------------------------------------------------------------------------------
//更改資料庫擁有者
sp_changedbowner dbo
---------------------------------------------------------------------------------------------------
//隨機選取資料
SELECT TOP 6 * FROM dbo.AD ORDER BY NEWID()
---------------------------------------------------------------------------------------------------
//存取資料時的亂碼問題
insert into tablename(col1,col2) values(N'value1',N'value2');
---------------------------------------------------------------------------------------------------
//以sql語法 取得系統日期
SELECT DATENAME([year], GETDATE()) + '/' + RTRIM(CONVERT(char, DATEPART([month], GETDATE()))) + '/' + DATENAME([Day], GETDATE()) AS nowdate
執行結果:2006/5/29
SELECT CONVERT(VarChar, GETDATE(), 111) AS newdate
執行結果:2006/05/29
SELECT CONVERT(datetime, GETDATE(), 111) AS newdate
執行結果:2006/5/29 下午 07:49:33
---------------------------------------------------------------------------------------------------
//建立流水號 20060727235912233(年+月+日+時+分+秒+毫秒)
SELECT CAST(YEAR(GETDATE()) AS Varchar(4)) + REPLICATE('0',
2 - LEN(MONTH(GETDATE()))) + CAST(MONTH(GETDATE()) AS Varchar(2))
+ REPLICATE('0', 2 - LEN(DAY(GETDATE()))) + CAST(DAY(GETDATE())
AS Varchar(2)) + REPLICATE('0', 2 - LEN(DATEPART([Hour], GETDATE())))
+ CAST(DATEPART([Hour], GETDATE()) AS Varchar(2)) + REPLICATE('0',
2 - LEN(DATEPART([Minute], GETDATE()))) + CAST(DATEPART([Minute],
GETDATE()) AS Varchar(2)) + REPLICATE('0', 2 - LEN(DATEPART([Second],
GETDATE()))) + CAST(DATEPART([Second], GETDATE()) AS Varchar(2))
+ REPLICATE('0', 3 - LEN(DATEPART(MilliSecond, GETDATE())))
+ CAST(DATEPART(MilliSecond, GETDATE()) AS Varchar(3)) AS SerialNumber
---------------------------------------------------------------------------------------------------
//取得資料為第幾筆資料
SELECT NAME, IDENTITY(INT, 1, 1) AS seq INTO #tmp FROM Table1 ;
SELECT * FROM #tmp;
--------SQL 2005----------
SELECT NAME, ROW_Number OVER (ORDER BY NAME) AS seq FROM Table1;
---------------------------------------------------------------------------------------------------
//取得INSERT的 IDENTITY
insert into Table1 (NAME,ADDR,SEX) values('MARK','Taipei','F');SELECT SCOPE_IDENTITY() AS SEQ
---------------------------------------------------------------------------------------------------
//星期五
SELECT CASE
when DATEPART(dw, getdate()) = 1 THEN '星期日'
when DATEPART(dw, getdate()) = 2 THEN '星期一'
when DATEPART(dw, getdate()) = 3 THEN '星期二'
when DATEPART(dw, getdate()) = 4 THEN '星期三'
when DATEPART(dw, getdate()) = 5 THEN '星期四'
when DATEPART(dw, getdate()) = 6 THEN '星期五'
when DATEPART(dw, getdate()) = 7 THEN '星期六'
END AS WEEK
---------------------------------------------------------------------------------------------------
//星期五
SELECT '星期' + SUBSTRING('日一二三四五六', DATEPART(dw, GETDATE()), 1) AS WEEK
---------------------------------------------------------------------------------------------------
//取得星期幾
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS Expr1, DATENAME(dw, GETDATE()) AS Expr2 //2007-04-09 星期一
---------------------------------------------------------------------------------------------------
//今天開始後的七天(不包含六日)
select dateadd(day,case when datepart(weekday,getdate()) < 5 then 9 else 11 end,getdate())
--6-1(星期五)
select datepart(weekday,getdate())
---------------------------------------------------------------------------------------------------
//讓自動遞增的欄位重新計算
DBCC CHECKIDENT (@tableName, RESEED, 1)
---------------------------------------------------------------------------------------------------
//SQL 2000 取出 11~20筆資料
SELECT TOP 10 * FROM Table1 WHERE (ID NOT IN (SELECT TOP 10 ID FROM Table1))
---------------------------------------------------------------------------------------------------
//SQL 2005 取出 11~20筆資料
select * from (select *, row_number() over(order by DatabaseLogID) cn from DatabaseLog) temp where cn between 11 and 20
---------------------------------------------------------------------------------------------------
//取出不重複的所有資料
SELECT * FROM Forum_Detail
WHERE (id IN (SELECT MAX(id) FROM Forum_Detail GROUP BY auther))
---------------------------------------------------------------------------------------------------
//產生 <正項加總 負項加總 正項加總+負項加總> 欄位
TABLE: SALES
COLUMN: AMOUNT (int)
以 欄位AMOUNT 產生 三個新增欄位 (PLUS:正項加總 MINUES:負項加總 TOTAL: PLUS + MINUES)
SELECT SUM(CASE WHEN AMOUNT > 0 THEN AMOUNT ELSE 0 END) AS PLUS,
SUM(CASE WHEN AMOUNT < 0 THEN AMOUNT ELSE 0 END) AS MINUES,
SUM(AMOUNT) AS TOTAL
FROM SALES
----------------------
PLUS MINUES TOTAL
27 -16 11
---------------------------------------------------------------------------------------------------
//查詢資料庫所有Table的Primary Key與Foreign Key
select * From INFORMATION_SCHEMA.KEY_COLUMN_USAGE
---------------------------------------------------------------------------------------------------
//清除表格中的所有資料
TRUNCATE TABLE 'Table1'
---------------------------------------------------------------------------------------------------
//清空資料庫所有資料表內的資料.
USE '資料庫'
GO
EXEC sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"
---------------------------------------------------------------------------------------------------
//判斷資料表是否存在
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_NAME = 'Groups') //Groups
---------------------------------------------------------------------------------------------------
//統計欄位中相同欄位值的筆數
SELECT MEMO, COUNT(MEMO) AS Total_Amount FROM Table1 GROUP BY MEMO
---------------------------------------------------------------------------------------------------
//子查詢
SELECT * FROM (SELECT * FROM Table1 WHERE Bit_Type = 0) WHERE ID >= 11
---------------------------------------------------------------------------------------------------
//LinkServer
sp_addlinkedserver @server = N'LinkServer',
@srvproduct = N' ',
@provider = N'SQLOLEDB',
@datasrc = N'ServerNetName',
@catalog = N'Northwind'
GO
SELECT *
FROM LinkServer.Northwind.dbo.Shippers //server_name.db_name.dbo.table_name
GO
---------------------------------------------------------------------------------------------------
//SELECT... INTO... <Table3必須不存在,sql執行完後 會自動建立一個新的Table(Table3)>
SELECT ID, NAME, ADDR
INTO Table3
FROM Table1 T1
---------------------------------------------------------------------------------------------------
//INSERT INTO... SELECT... <Table2必須為已存在的資料表>
INSERT INTO Table2 (NAME2, ADDR2)
SELECT NAME, ADDR
FROM Table1
---------------------------------------------------------------------------------------------------
//內建數學函式
SELECT ROUND(100.1264, 2) AS Expr1 100.13
SELECT CEILING(123.45) AS Expr1 124
SELECT CEILING(- 123.65) AS Expr1 -123
SELECT FLOOR(123.45) AS Expr1 123
SELECT FLOOR(- 123.45) AS Expr1 -124
SELECT ABS(- 1.0) AS Expr1 1
SELECT POWER(2, 3) AS Expr1 8
SELECT SQUARE(9) AS Expr1 81
SELECT SQRT(9) AS Expr1 3
---------------------------------------------------------------------------------------------------
//取出字串最左邊或最右邊的指定常度子字串
SELECT RIGHT(forumId, 4) AS Result FROM Forum_Detail
SELECT LEFT(forumId, 4) AS Result FROM Forum_Detail
---------------------------------------------------------------------------------------------------
//CHARINDEX LEN
SELECT CHARINDEX('k', NAME) AS Expr1 FROM Table1 //JeckChen 4
SELECT LEN(NAME) AS Expr1 FROM Table1 //JeckChen 8
---------------------------------------------------------------------------------------------------
//預設值
datetime (getdate())
uniqueidentifier (newid())
---------------------------------------------------------------------------------------------------
//REVERSE
SELECT REVERSE(GroupName) AS Expr1 FROM Groups //組群員人務業
---------------------------------------------------------------------------------------------------
//replace
update tableName set columnName = replace(columnName , "oldString", "newString")
---------------------------------------------------------------------------------------------------
//隨機取出前五筆資料
select top 5 * from tableName order by newId()
---------------------------------------------------------------------------------------------------
//匯入圖檔的SQL<僅限SQL2005>
INSERT INTO Table1 SELECT * FROM OPENROWSET( BULK 'D:\d2.jpg', SINGLE_BLOB) AS x
----------------------------
SQL Server 2005
--建立資料表
CREATE TABLE dbo.練習資料表(
編號 int IDENTITY(1,1) NOT NULL,
姓名 nvarchar(10) NULL,
性別 nvarchar(2) NULL,
自傳 nvarchar(max) NULL,
玉照 varbinary(max) NULL,
);
GO
--新增第一筆資料(包括 NCLOB 與 BLOB 在內)
INSERT INTO dbo.練習資料表
(
姓名,
性別,
自傳,
玉照
)
SELECT '章立民' AS 姓名, '男' AS 性別,
* FROM
OPENROWSET
(BULK 'C:\Temp\Employee-A156401174.txt', SINGLE_NCLOB) AS 自傳,
OPENROWSET
(BULK 'C:\Temp\Employee-A156401174.jpg', SINGLE_BLOB) AS 玉照;
GO
---------------------------------------------------------------------------------------------------
//查詢SqlServer 錯誤代碼
select * from master.dbo.sysmessages where error = 20055
---------------------------------------------------------------------------------------------------
//取得指定Table中所有欄位的相關資訊
SELECT * FROM Information_Schema.COLUMNS where TABLE_NAME='ACCOUNT'
---------------------------------------------------------------------------------------------------
//取得識別值
IDENT_CURRENT 會傳回在任何工作階段和任何範圍中，產生給特定資料表的最後一個識別值。
@@IDENTITY 會傳回所有範圍的目前工作階段中，任何資料表所產生的最後一個識別值。
SCOPE_IDENTITY 會傳回在目前工作階段以及目前範圍中，任何資料表產生的最後一個識別值。
-------------------------------------------------------
SELECT IDENT_CURRENT('Table1') AS MaxIdentity //39
SELECT @@IDENTITY //null
SELECT SCOPE_IDENTITY() //null
----------------after insert-----------------------------
insert into Table1(name) values('test1')
SELECT IDENT_CURRENT('Table1') AS MaxIdentity //40
SELECT @@IDENTITY //40
SELECT SCOPE_IDENTITY() //40
---------------------------------------------------------------------------------------------------
//TimeStamp欄位型態
資料庫中的值新增或修改時 會自動更新,主要可使用在 防止同一資料同一時間被重複修改
可於取出資料欲修改時Select * , (TS + 0) AS TSTAMP from Table4 將資料和TimeStamp欄位的值一並取出
於寫回資料庫時 比對TimeStamp欄位是否相同 若不同表示已被其他使用者更新過
---------------------------------------------------------------------------------------------------
//計算兩個日期區間相差的月數及日數
SELECT STARTDATE , ENDDATE ,
CASE WHEN DAY(ENDDATE) >= DAY(STARTDATE) THEN DATEDIFF(MONTH, STARTDATE, ENDDATE)
ELSE DATEDIFF(MONTH, STARTDATE, ENDDATE) -1
END as 月數
,
CASE
WHEN DAY(ENDDATE) >= DAY(STARTDATE) THEN DAY(ENDDATE) - DAY(STARTDATE)
ELSE DAY(DATEADD(MONTH, DATEDIFF(MONTH, 0, ENDDATE), -1)) - DAY(STARTDATE) + DAY(ENDDATE)
END as 日數
FROM TABLE1
---------------------------------------------------------------------------------------------------
//取得一個月內的資料
SELECT STARTDATE , ENDDATE FROM TABLE1 WHERE DATEDIFF(MM,ENDDATE,GETDATE()) <= 1
---------------------------------------------------------------------------------------------------
//計算兩個日期區間相差的月數及日數
SELECT *,
CASE WHEN DAY(ENDTIME) >= DAY(STARTTIME)
THEN DATEDIFF(MONTH, STARTTIME, ENDTIME)
ELSE DATEDIFF(MONTH, STARTTIME, ENDTIME) - 1
END AS 月數,
CASE WHEN DAY(ENDTIME) >= DAY(STARTTIME)
THEN DAY(ENDTIME) - DAY(STARTTIME)
ELSE DAY(DATEADD(MONTH, DATEDIFF(MONTH, 0, ENDTIME), - 1)) - DAY(STARTTIME) + DAY(ENDTIME)
END AS 日數
FROM Table1
---------------------------------------------------------------------------------------------------
//抓出同一TABLE中, SEX欄位中 重複值的前兩筆資料
SELECT * FROM Table1 A
WHERE (ID IN
(SELECT TOP 2 ID FROM Table1 B WHERE A.SEX = B.SEX ORDER BY ID ASC)
)
ORDER BY SEX
---------------------------------------------------------------------------------------------------
//cmd斷掉所有connection
SP_Who可以看到目前的連線，50以下為系統所用
exec sp_who
kill 56
---------------------------------------------------------------------------------------------------
//搜尋欄位中特定字元的位置(若無符合字元 則傳回0;可用以代替like)
select * from Table1 where charindex('xxx',Column1) > 0
SELECT * FROM ACCOUNT WHERE RTRIM( LTRIM(A_USERNAME)) LIKE '陳%'
SELECT * FROM ACCOUNT WHERE CHARINDEX( '陳' , RTRIM( LTRIM(A_USERNAME)) ) = 2
---------------------------------------------------------------------------------------------------
//檢查有多少人連至資料庫的語法
SELECT cntr_value AS User_Connections FROM master..sysperfinfo as p
WHERE p.object_name = 'SQLServer:General Statistics' And p.counter_name = 'User Connections'
---------------------------------------------------------------------------------------------------
//計算TEXT欄位型態內容長度 (中Aa1皆為一個字元長度)
SELECT LEN(CONVERT(VARCHAR(8000), USERNAME)) AS RESULT FROM Table2
SELECT LEN(CAST(USERNAME AS VARCHAR(8000))) AS RESULT FROM Table2
SELECT DATALENGTH(USERNAME) AS RRSULT FROM Table2
---------------------------------------------------------------------------------------------------
//DB LOCK
--將大型的複雜查詢分解成幾個比較簡單的查詢
--一個用戶端應用程式執行緒開啟了兩個連線
--避免使用 SELECT *
SELECT COUNT(*) FROM RES_MAIN_TOT WITH(NOLOCK) WHERE R_NO > 1000 --被鎖定(INSERT UPDATE)的紀錄一併顯示
SELECT COUNT(*) FROM RES_MAIN_TOT WITH(READPAST) --被鎖定的紀錄不顯示
---------------------------------------------------------------------------------------------------
//新增資料時 指定識別欄位的值
SET IDENTITY_INSERT Table1 ON
INSERT INTO Table1(ID, NAME, COUNTRY)
VALUES (23, 'WhenChang', 'America')
---------------------------------------------------------------------------------------------------
--資料庫或資料表的大小
EXEC SP_SPACEUSED
--database_name :: 資料庫名稱
--database_size ::資料庫的大小
--unallocated space ::資料庫的未配置空間
--reserved ::保留的總空間
--Data ::資料使用的總空間
--index_size ::索引使用的空間
--Unused ::未使用的總空間
EXEC SP_SPACEUSED 'JOB_MAIN_TOT'
--Name :: 資料表的名稱
--Rows ::資料列數
--reserved ::保留的總空間
--Data ::資料使用的總空間
--index_size ::索引使用的空間
--Unused ::未使用的總空間
---------------------------------------------------------------------------------------------------
SP_HELPSERVER --INSTANCE NAME
SP_HELPDB --所有資料庫的大小 相容性
SP_HELPUSER --單一資料庫的所有使用者相關資訊
SP_HELP 'Table_Name' --單一資料庫所有Table的欄位型態 大小
SP_SPACEUSED --單一資料庫資料量大小
SP_SPACEUSED 'Table_Name' --單一資料表的資料筆數 資料量大小
---------------------------------------------------------------------------------------------------
-- 取出資料時 自行增加自動編號欄位
SELECT S_NO = IDENTITY(INT, 1, 1), *
INTO #TempTable
FROM ( SELECT TOP 20 * FROM MYBOOK) A
SELECT * FROM #TempTable
DROP TABLE #TempTable
---------------------------------------------------------------------------------------------------
//TABLE1(NAME,CODE) ; TABLE2(NAME2,CODE2) 將 SOURE2.CODE2 的資料設定給 SOURCE.NAME
UPDATE SOURCE SET CODE = (SELECT CODE2 FROM SOURCE2 WHERE SOURCE.NAME = SOURCE2.NAME2)
---------------------------------------------------------------------------------------------------
//跨SQL server新增資料
利用 sp_addlinkedserver 語法或
SQL Server Enterprise Manager --> ServerName --> 安全性 --> 連結伺服器 去設定遠端伺服器
之後在本端下 Select * from [ServerName/IP].[DBNAME].[Onwer].[TableName]
---------------------------------------------------------------------------------------------------
//判斷欄位字串是否為日期格式 (CDATE為VARCHAR)
SELECT CASE WHEN ISDATE(CDATE) <> 0 THEN CDATE ELSE 'NODATA' END AS 開始日期 FROM Table1
---------------------------------------------------------------------------------------------------
// 隨機取出十筆資料
SELECT TOP 10 * FROM ACCOUNT ORDER BY NEWID()
---------------------------------------------------------------------------------------------------
//PRINT & RAISERROR
IF EXISTS(
SELECT * FROM ACCOUNT WHERE A_NO = '262823'
)
PRINT 'DATA ALREADY EXISTS'
ELSE
RAISERROR('SQL STATEMENT WAS ERROR',16,1) --伺服器: 訊息 50000，層級 16，狀態 1，行 6
---------------------------------------------------------------------------------------------------
//查詢欄位中的特殊符號
想要查詢出地址資料中包含底線符號 _
SELECT NAME,ADDR FROM TABLE1 WHERE ADDR LIKE '%\_%' ESCAPE '\'
想要查詢出地址資料中包含百分符號 %
SELECT NAME,ADDR FROM TABLE1 WHERE ADDR LIKE '%\%%' ESCAPE '\'
---------------------------------------------------------------------------------------------------
//選出每個縣市中人數較多的那筆資料
人數 政黨 縣市
4 中國國民黨 台中縣
1 無黨團結聯盟 台中縣
8 中國國民黨 高雄縣
10 中國國民黨 高雄縣
SELECT MAX(人數), (SELECT TOP 1 政黨 FROM 表格 B WHERE B.縣市=A.縣市 ORDER BY 人數 ) AS 政黨, 縣市 FROM 表格 A GROUP BY 縣市
---------------------------------------------------------------------------------------------------
//將字串中的某一區段字串取代為其他字串
SELECT STUFF(A_USERID,2,3,'XXX') FROM ACCOUNT WHERE A_USERID = 'D122136512' -- D122136512 DXXX136512
---------------------------------------------------------------------------------------------------
//計算字串在字串中出現的次數
test在testatesta(content) 中出現的次數
select content,(len(content)-len(replace(content,'test','')))/4 as num from table order by num desc
或
select content,(len(content)-len(replace(content,'test','')))/len('test') as num from table order by num desc
---------------------------------------------------------------------------------------------------
//分群取最小值
SELECT MIN(DATET) , ID FROM Table3 GROUP BY ID