SQL 指令

如何新增datetime

如何新增 datetime 的值

insert into table (Time) values (CONVERT(datetime,'2018-03-19 17:45:07.563'))

直接新增當前時間

GETDATE()

date 直接轉 datetime

在轉換時，年月日會複製，時間元件設定為 00:00:00.000

DECLARE @date date = '20161221';  
DECLARE @datetime datetime = @date;  

SELECT @datetime AS '@datetime', @date AS '@date';

time 直接轉 datetime

同樣複製時間元件，日期元件設定為'1900-01-01'。 如果時間的小數位數超過三位，則會截斷(無條件捨去)。

DECLARE @time time(4) = '12:10:05.1237';  
DECLARE @datetime datetime = @time;  

SELECT @datetime AS '@datetime', @time AS '@time';

smalldatetime

秒以後的被捨去，只取到分。

將搜尋出來的資料，轉換類型串成方便做成陣列的字串方法

select CAST(欄位 as 類型) + ',' from 表格 where 欄位 = 值 for xml path('')

datetime to date

參考資料:日期和時間資料類型與函數(https://docs.microsoft.com/zh-tw/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-2017)

參考資料:DATENAME (Transact-SQL)(https://docs.microsoft.com/zh-tw/sql/t-sql/functions/datename-transact-sql?view=sql-server-2017)

參考資料:DATEPART (Transact-SQL)(https://docs.microsoft.com/zh-tw/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017)

DATENAME( datepart , date) 回傳字元字串

DATEPART( datepart , date) 回傳整數

datepart 縮寫 year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw, w hour hh minute mi, n second ss, s millisecond ms microsecond mcs nanosecond ns TZoffset tz ISO_WEEK ISOWK, ISOWW

select b.date
from
(
SELECT DATENAME(yyyy,LoginDatetime)+'-'+CAST(DATEPART(mm,LoginDatetime) AS nvarchar(255))+'-'+DATENAME(dd,LoginDatetime) as date
FROM table1
) as b
group by b.date

SQL 的取代方法

str1 完整字串 str2 將被取代的部分 str3 要替換的

Replace (str1, str2, str3)

先搜尋該資料 & 所要改的欄位

先看一次被替換後會變成如何

執行替換動作

select Name
from Comm_CustomPage
where index = 106
//Name = "Brian Wu"

select REPLACE(Name,'Brian','Charles') 
from Comm_CustomPage 
where index = 106
//Name = "Charles Wu"

update Comm_CustomPage
set Name = REPLACE(Name,'Brian','Charles')
where index = 106

由於有大量資料，每次都要select全部資料，會消耗太多時間。

所以可以先設定個大概條件，把需要的資料先撈出來，塞到一張表，之後就去裡面做更細的搜尋，用來節省時間。

  insert into 暫存表(index, Title, Content)
select index, Title, Content from 原表 with (nolock) 
where Title  like '%KeyWord%' or Content like '%KeyWord%'

Isnull

參考資料:(https://docs.microsoft.com/zh-tw/sql/t-sql/functions/isnull-transact-sql)

語法

ISNULL ( check_expression , replacement_value )

如果 check_expression 為 null，則用 replacement_value 取代

check_expression 可以是任何類型 replacement_value 必須隱含能轉換為 check_expression 的類型

查詢資料表資料筆數

參考資料:(https://www.brentozar.com/archive/2014/02/count-number-rows-table-sql-server/)

舊寫法

SELECT COUNT(1) FROM TableName;

帥氣寫法

SELECT TBL.object_id, TBL.name, SUM(PART.rows) AS rows
FROM sys.tables TBL
INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE TBL.name = @TableName
AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name;

資料庫分頁

參考資料:(https://www.dotblogs.com.tw/easonwei/2012/09/24/75008)

0~N

select top 123 * 
 from [user] 
 order by emp_id desc 

M~M+N

select * 
from (  
  select ROW_NUMBER() over (order by emp_id asc) rownum, * 
  from [user] ) as yourselect
where rownum between 123 and 150
order by emp_id desc

建立UNIQUE KEY

參考資料:(https://docs.microsoft.com/zh-tw/sql/relational-databases/tables/create-unique-constraints?view=sql-server-ver15)

USE DB_NAME;
GO
CREATE TABLE TableName
(
    columnName int NOT NULL,
    CONSTRAINT UK_Name UNIQUE(columnName)
);
GO

USE DB_NAME;
GO
ALTER TABLE TableName
ADD CONSTRAINT UK_Name UNIQUE (columnName);
GO

建立FOREIGN KEY

CREATE TABLE TableName
(
    columnName int NOT NULL, Name nvarchar(50),
    CONSTRAINT PK_Name PRIMARY KEY NONCLUSTERED (columnName),
    CONSTRAINT FK_Name FOREIGN KEY (columnName)
    REFERENCES TableName2 (columnName)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
;

USE DB_NAME;
GO
ALTER TABLE TableName 
WITH CHECK ADD CONSTRAINT FK_Name FOREIGN KEY(columnName)
REFERENCES TableName2 (columnName);
GO

sql server 鎖表 select for update

SQL Server提供了強大而完備的鎖機制來幫助實現資料庫系統的併發性和高效能。使用者既能使用SQL Server的預設設定也可以在select 語句中使用“加鎖選項”來實現預期的效果。 本文介紹了SELECT語句中的各項“加鎖選項”以及相應的功能說明。

NOLOCK(不加鎖) 此選項被選中時,SQL Server 在讀取或修改資料時不加任何鎖。 在這種情況下,使用者有可能讀取到未完成事務(Uncommited Transaction)或回滾(Roll Back)中的資料, 即所謂的“髒資料”。

HOLDLOCK(保持鎖) 此選項被選中時,SQL Server 會將此共享鎖保持至整個事務結束,而不會在途中釋放。

UPDLOCK(修改鎖) 此選項被選中時,SQL Server 在讀取資料時使用修改鎖來代替共享鎖,並將此鎖保持至整個事務或命令結束。使用此選項能夠保證多個程序能同時讀取資料但只有該程序能修改資料。

TABLOCK(表鎖) 此選項被選中時,SQL Server 將在整個表上置共享鎖直至該命令結束。 這個選項保證其他程序只能讀取而不能修改資料。

PAGLOCK(頁鎖) 此選項為預設選項, 當被選中時,SQL Server 使用共享頁鎖。

TABLOCKX(排它表鎖) 此選項被選中時,SQL Server 將在整個表上置排它鎖直至該命令或事務結束。這將防止其他程序讀取或修改表中的資料。

每天重啟的序列

參考資料:(https://dba.stackexchange.com/questions/116747/sql-server-2008-sequence-which-restarts-daily)

UPDATE
  dbo.DailySequence
SET
  @ret     = sequence = CASE date WHEN x.Today THEN sequence + 1 ELSE 0 END,
  @nowDate = date     = x.Today
FROM
  (SELECT CAST(GETDATE() AS date)) AS x (Today)
;

重置Table 的id

DBCC CHECKIDENT ('TableName', RESEED, 0)
GO