1 類型轉換 Cast(10.947 AS INT) 或Convert(INT,10.947)
2 經常用到的函數
newid() 用於創建GUID值，round(price,2) 四捨五入
isnull(summary,0) 如果summary為空，則返回0
substring(‘abcdefg’,2,3) 取子字符串
replace(‘abcdefg’,’abc’,’123’) 替換字符串
rand() 求0-1之間的隨機數
dateAdd(day, 21, getdate()) 在向指定日期加上一段時間的基礎上，返回新的 datetime 值
datePart(month, GETDATE()) 返回代表指定日期的指定日期部分的整數
3 動態構建SQL
exec (‘ SELECT [job_id], [job_desc] FROM [pubs].[dbo].[jobs] ‘)
或 exec sp_executesql N’SELECT [job_id], [job_desc] FROM [pubs].[dbo].[jobs]’
字段名，表名，數據庫名作變量時，必須用動態SQL
declare @fname varchar(20)
set @fname='job_id'
exec (' SELECT '+ @fname+ ' FROM [pubs].[dbo].[jobs] ')
4 在數據導入導出功能時，如果是SQL Server 之間相互拷貝數據，並且表的字段類型含有identity值，則應該使用下面的腳本關閉自增，導入導出完畢後再打開
set identity_insert 表名 on --關閉表的identity屬性作用
set identity_insert 表名 off --打開表的identity屬性作用
5 CASE語句的寫法
SELECT Price=CASE WHEN Price IS NULL THEN ‘not yeat’
WHEN Price<10 THEN ‘Cheap’
WHEN Price>10 THEN ‘Expensive’
END
或是把需要比較的列值放到when的後面。
SELECT Gender=
CASE 1 THEN ‘男’
CASE 0 THEN ‘女’
ELSE ‘not yet’
END
CASE 語句常用於行列轉換
SELECT 部門,
[材料1]= SUM(CASE 材料 WHEN ‘材料1’ THEN 數量 ELSE 0 END) ，
[材料2]= SUM(CASE 材料 WHEN ‘材料2’ THEN 數量 ELSE 0 END)
FROM 部門耗材
GROUP BY 部門
6 分頁 查詢第X頁,每頁Y條記錄
如果表中有主鍵
select top y * from 表 where 主鍵 not in(select top (x-1)*y 主鍵 from 表)
如果表中無主鍵,可以用臨時表,加標識字段解決
select id=identity(int,1,1),* into #tb from 表
select * from #tb where id between (x-1)*y and x*y-1
7 EXISTS的用法
SELECT DISTINCT pub_name FROM publishers
WHERE EXISTS
(SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type = 'business')
8 流水號生成
生成長度為8的編號，編號以BH開頭，其余6位為流水號
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO
--在表中應用函數
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)
9
生成的編號長度為12，前6位為日期信息，格式為YYMMDD，後6位為流水號
--創建得到當前日期的視圖
CREATE VIEW v_GetDate AS
SELECT dt=CONVERT(CHAR(6),GETDATE(),12) GO
--得到新編號的函數
CREATE FUNCTION f_NextBH()
RETURNS char(12)
AS
BEGIN
DECLARE @dt CHAR(6)
SELECT @dt=dt FROM v_GetDate
RETURN(
SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6)
FROM tb WITH(XLOCK,PAGLOCK)
WHERE BH like @dt+'%')
END
GO
--在表中應用函數
CREATE TABLE tb(
BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)
9 選擇不重復的字符串，表的數據如下，假設列名為ID,表名為tbl
aaa
bbb
ccc
aaa
1) DISTINCT SELECT DISTINCT ID FROM tbl
2) 分組 SELECT ID FROM tbl GROUP BY ID Having COUNT(*)>1
10 字符串用在where語句中判斷是否相等的時候，提示cannot resolve collection conflict for equal to operation,
應該加上database default
11 SQL Server 字符串類型是大小寫不敏感的，aa,AA是同樣的含義。
有時候確需要實現大小寫字符敏感，比如密碼。aa,AA表示不同的密碼。
為實現字符串大小寫敏感，可以把字符串轉話為二進制後再作比較。
CAST(Password AS varbinary)
12 SQL 錯誤處理
檢測@@error變量的值，發生錯誤時，該值不為0
if @@error<>0
print '發生錯誤1'
不是嚴重的錯誤,所以SQL會執行下去;
屬於嚴重的錯誤,所以SQL沒有執行下去;
被調用的存儲過程發生嚴重錯誤時,調用它的存儲過程可以捕獲錯誤,並可以繼續執行下去;
SQL Server 2005的用法
BEGIN TRY
DELETE FROM IPR WHERE TransationID= 1003
END TRY
BEGIN CATCH
END CATCH
13 子查詢
求工資最高的員工的姓名
SELECT name FROM Employee
WHERE wage=(SELECT MAX(wage) FROM Employee)
求評論最多的文章
SELECT Title FROM Post
WHERE Reply=(SELECT MAX(Reply) FROM Post)
