/********************************************
DROP TABLE tblMaster
DROP TABLE tblCopy
SELECT * FROM tblMaster
SELECT * FROM tblCopy
DELETE FROM tblMaster
DELETE FROM tblCopy
********************************************/
--Create Table
--创建表
CREATE TABLE tblMaster(
id int identity(1,1) not null ,
details varchar(8000) null
)
--Insert Initial Data
--插入初始数据
INSERT INTO tblMaster(details)
SELECT N'A'
UNION ALL
SELECT N'B'
UNION ALL
SELECT N'C'
UNION ALL
SELECT N'测'
UNION ALL
SELECT N'试'
UNION ALL
SELECT N'数'
UNION ALL
SELECT N'据'
UNION ALL
SELECT N'A'
UNION ALL
SELECT N'B'
UNION ALL
SELECT N'测'
UNION ALL
SELECT N'试'
UNION ALL
SELECT N'数'
UNION ALL
SELECT N'据'
--Copy Table,Exclude Data
--复制表，不拷贝数据
SELECT
TOP 0
*
INTO
tblCopy
FROM
tblMaster
WHERE
1<>1
--Copy data
--拷贝数据
INSERT INTO
tblCopy(details)
SELECT
details
FROM
tblMaster
--Caculate Distance Between Two Days
--计算两天之间的时间间隔
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
--随机选取几条数据
SELECT
top 3 *
FROM
tblMaster
ORDER BY
newID()
--Delete Duplicated Data
--删除重复数据
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)