/********************************************
DROP TABLE tblMaster
DROP TABLE tblCopy
SELECT * FROM tblMaster
SELECT * FROM tblCopy
DELETE FROM tblMaster
DELETE FROM tblCopy
********************************************/
--Create Table
--創(chuàng)建表
CREATE TABLE tblMaster(
id int identity(1,1) not null ,
details varchar(8000) null
)
--Insert Initial Data
--插入初始數(shù)據(jù)
INSERT INTO tblMaster(details)
SELECT N'A'
UNION ALL
SELECT N'B'
UNION ALL
SELECT N'C'
UNION ALL
SELECT N'測(cè)'
UNION ALL
SELECT N'試'
UNION ALL
SELECT N'數(shù)'
UNION ALL
SELECT N'據(jù)'
UNION ALL
SELECT N'A'
UNION ALL
SELECT N'B'
UNION ALL
SELECT N'測(cè)'
UNION ALL
SELECT N'試'
UNION ALL
SELECT N'數(shù)'
UNION ALL
SELECT N'據(jù)'
--Copy Table,Exclude Data
--復(fù)制表，不拷貝數(shù)據(jù)
SELECT
TOP 0
*
INTO
tblCopy
FROM
tblMaster
WHERE
1<>1
--Copy data
--拷貝數(shù)據(jù)
INSERT INTO
tblCopy(details)
SELECT
details
FROM
tblMaster
--Caculate Distance Between Two Days
--計(jì)算兩天之間的時(shí)間間隔
SELECT datediff(day,'2006-12-12','2007-12-12')
--Search From 2th Record and 6th Record
--查詢從第二條記錄到第六條記錄
SELECT
*
FROM
(
SELECT
top 5 *
FROM (
SELECT
top 6 id,details
FROM tblMaster
ORDER BY
id asc
) a
ORDER BY
id desc
) T
ORDER BY
id asc
--Choose some Records Random
--隨機(jī)選取幾條數(shù)據(jù)
SELECT
top 3 *
FROM
tblMaster
ORDER BY
newID()
--Delete Duplicated Data
--刪除重復(fù)數(shù)據(jù)
DELETE FROM
tblCopy
Where
id not in(
SELECT
min(id)
FROM
tblCopy
Group by
details
)
--if the value of tblMaster.details == null then the following clauses have different values
--如果details列有null值,那么如下兩條語句得到的返回值不等
SELECT
count(*)
FROM
tblMaster
SELECT
count(details)
FROM
tblMaster
--Password Encrypt return 1:equel;return 0:not equel
--密碼加密 返回1:相等;返回2:不相等
SELECT pwdcompare('123',pwdencrypt('123'),0)