2014年3月23日 星期日

MS SQL指令應用篇

2022/12/06
查詢某個區段的資料,我們可以在條件上使用between,如下。
(欄位 between CONVERT(date,'2022-10-01') and CONVERT(date,'2022-10-31'))

2021/12/03
1. 字串1101203,拆出年、月、日,年轉西元年
Begin
Declare @StrTmp varchar(7);
Set @StrTmp='1101031';

Select (left(@StrTmp,3)+1911) as yy,CONVERT(int,substring(@StrTmp,4,2)) as mm,CONVERT(int,substring(@StrTmp,6,2)) as dd
End
2023/05/17 另一種方法就是將民國直接轉成數字,再加上19110000
CAST(str(CONVERT(int,left('1120101',7))+19110000) AS Date)

2019/07/02
Select Update指令如下
update fs set fs.Name=m.Name
From Table1 as fs left join Table2 as m on fs.ID=m.ID where m.Name is not null

Select Insert指令如下
Insert Table1 (t1,t2)
Select c1,c2 From Table2 where email='TEST@abc.com'

2019/03/28
通常會使用union去將很多張表合併成一張表,而union與union all的差別在於union all會將所有的資料都合併成一張表,所以得到的結果可能同一筆資料會出現兩筆以上的資料;如果使用union重複的資料則會顯示一筆。

2018/04/17
STATISTICS TIME
參考資料:[SQL SERVER] SET STATISTICS 語法執行時間分析與統計訊息
參考資料:SET STATISTICS TIME (Transact-SQL)
可以檢視自己的SQL指令,花了多少時間執行。
範例:
SET STATISTICS TIME ON;
GO

--SQL 語句--
Select  * from table1

GO

SET STATISTICS TIME OFF;
GO

2017/10/24
datalength函數:可以知道資料的長度,例如姓名中文3個字,在資料庫中的資料長度是6。
replicate(' ',4)函數:第一個參數是自己想要插入的字元,第二個參數是插入幾個字元。

2017/05/23
有一種狀況是某個欄位它不是主鍵,但它不能有重複的資料,這個時候可以透過UNIQUE來限制這個欄位的資料必須為唯一,就能避免重複的資料進入資料表時,指令如下:
ALTER TABLE 資料表名稱
ADD CONSTRAINT 自己給一個識別名稱 UNIQUE(欄位)
有一點要注意,欄位不能為NULL

2017/05/22
針對現有的資料表,運用指令方式加入外部索引鍵,以下是1對N的關係,每1張訂單會包含很多筆的訂單明細
ALTER TABLE test_order_detail
ADD
CONSTRAINT FK_test_order_detail_test_order
FOREIGN KEY (Order_no)
REFERENCES test_order(Order_no);
說明:
CONSTRAINT設定外部索引鍵的識別名稱,FK_test_order_detail_test_order是我自己自訂名稱。
FOREIGN KEY設定test_order_detail資料表的Order_no為外部索引鍵。
REFERENCES指定test_order資料表的Order_no為主鍵(主索引鍵)。

2015/05/11
利用CHARINDEX函數(搜尋字元位置),找出符合的資料;假設我們要針對某個資料表(名為table1),某ㄧ個欄位(名為status1,這個欄位會儲存A~D的英文代碼),找出符合A或C的代碼,如下:
Select *,CHARINDEX(status1,'AC') form table1 where CHARINDEX(status1,'AC')>0
說名:如果CHARINDEX函數有找到A或C,此函數就會回傳字元目前的位置。

2014/11/13
區分大小寫-在欄位後加上 Collate Chinese_Taiwan_Stroke_CS_AS(繁中區分大小寫)
select adm.userid,adm.password from s1 as adm
where adm.userid Collate Chinese_Taiwan_Stroke_CS_AS=@userid
and adm.password Collate Chinese_Taiwan_Stroke_CS_AS=@password

2014/10/29
使用CTE暫存資料,並執行刪除重複的資料,此方法適用於MS SQL 2005以上版本
說明:idno為學生學號,values為學生成績;由學生成績有高到低排序,並取最高的成績。
;WITH temp1 AS (
  select idno,values,ROW_NUMBER() OVER(partition by idno order by values desc) as aid from cv
)
DELETE FROM temp1
WHERE aid > 1;

補充:相同成績要相同排名ROW_NUMBER()改成RANK()

2014/09/03
--將年月日轉換為2014/09/03 10:00
select CONVERT(varchar(16),getdate(),120) as dt
--補充:如果要取到秒數,請將varchar(16)改為varchar(19);如果只想取得日期,請將varchar(16)改為varchar(10) 。
--取得日期時間的時間,例如:2014-09-03 08:02:24;執行下列指令會取得時間 08:02:24
select CONVERT(varchar(8),getdate(),108)
--補充:如果要取幾點幾分,請將varchar(8)改為varchar(5)。

2014/02/27
取得日期時間
參考資料:CAST 和 CONVERT (Transact-SQL)
--getdate取得現在時間;
select convert(varchar(10),getdate(),120) as str_date,convert(varchar(16),getdate(),120) as str_datetime

轉換int型態
select (CONVERT(int,'2014')-1911)-1 as s
轉換varchar型態
CONVERT(varchar, 欄位) as s

沒有留言:

張貼留言