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
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 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;
資料庫分頁
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
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
NOLOCK(不加鎖)
此選項被選中時,SQL Server 在讀取或修改資料時不加任何鎖。 在這種情況下,使用者有可能讀取到未完成事務(Uncommited Transaction)或回滾(Roll Back)中的資料, 即所謂的“髒資料”。
HOLDLOCK(保持鎖)
此選項被選中時,SQL Server 會將此共享鎖保持至整個事務結束,而不會在途中釋放。
UPDLOCK(修改鎖)
此選項被選中時,SQL Server 在讀取資料時使用修改鎖來代替共享鎖,並將此鎖保持至整個事務或命令結束。使用此選項能夠保證多個程序能同時讀取資料但只有該程序能修改資料。
TABLOCK(表鎖)
此選項被選中時,SQL Server 將在整個表上置共享鎖直至該命令結束。 這個選項保證其他程序只能讀取而不能修改資料。
PAGLOCK(頁鎖)
此選項為預設選項, 當被選中時,SQL Server 使用共享頁鎖。
TABLOCKX(排它表鎖)
此選項被選中時,SQL Server 將在整個表上置排它鎖直至該命令或事務結束。這將防止其他程序讀取或修改表中的資料。
每天重啟的序列
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)
;