--表之间数据复制
SELECT* INTO yozhu FROM yo --复制一份表
SELECT* INTO yozhu1 FROM yo where 1<>1 --只复制表结构，无数据
SELECT TOP 0 * into yozhu2 FROM yo --只复制表结构，无数据
insert into yo(yoName) select yoName from yo--把本表复制一遍
insert into pubs.dbo.yo select yoName from dbo.yo --数据库之间的表复制
SELECT * FROM pubs.dbo.yo
SELECT * FROM yo
--嵌套查询; 括号里面可以是其他任意子查询
SELECT * FROM yo WHERE yoID IN (SELECT yoID FROM yo WHERE yoID in(1,2,3))
--时间差值(查询工作了50年的)
--年 yy, yyyy
--季度 qq, q
--月 mm, m
--年中的日 dy, y
--日 dd, d
--周 wk, ww
--星期 dw, w
--小时 hh
--分钟 mi, n
--秒 ss, s
--毫秒 ms
--微妙 mcs
--纳秒 ns
SELECT * FROM dbo.Employees WHERE DATEDIFF(yy,dbo.Employees.BirthDate,dbo.Employees.HireDate)>50
--此代码未实现功能；sp_executesql值得研究;代码写成这样是因为TOP后面不能接变量
DECLARE @startPage INT,@endPage INT,@sql VARCHAR(300)
SET @endPage=2
SET @startPage=1
SET @sql=' select top '+STR(@endPage-@startPage+1)+'from dbo.Employees where Rid not in (select top'
+STR(@startPage-1)+'Rid from dbo.Employees where Rid>1)'
EXEC sp_executesql @sql
--范围查询
SELECT * FROM dbo.Employees WHERE BirthDate BETWEEN '1963-01-01' AND GETDATE()
SELECT * FROM dbo.Employees WHERE BirthDate NOT BETWEEN '1963-01-01' AND GETDATE()
--删除主表中附表没有的信息;注意：这个时候外层表不能使用别名
DELETE FROM dbo.Employees WHERE NOT EXISTS
(SELECT * FROM dbo.EmployeeTerritories b WHERE dbo.Employees.EmployeeID=b.EmployeeID)
--随机选出5条数据
SELECT TOP 5 * FROM dbo.Employees ORDER BY NEWID()
--由此看出随机性还是很科学的
select newid()
--删除重复数据;这是yo表本身是自增主键的情况下
DELETE FROM yo WHERE yoID NOT IN
(SELECT MAX(yoID) FROM yo GROUP BY yoName)
--这种方式对大数据不适用
SELECT DISTINCT * INTO tmp FROM dbo.yo
DELETE FROM dbo.yo
INSERT INTO yozhu1 SELECT * FROM tmp
--在非自增列下删除重复数据;添加一列，初始值为1，步长值为1
ALTER TABLE dbo.yozhu1 ADD shao INT IDENTITY(1,1)
DELETE FROM dbo.yozhu1 WHERE dbo.yozhu1.yoID NOT IN
(SELECT MAX(yoID) FROM dbo.yozhu1 GROUP BY yoName)
ALTER TABLE dbo.yozhu1 DROP COLUMN shao
SELECT * FROM yozhu1
--找出本数据库下用户建的表
SELECT NAME FROM sys.objects WHERE type='U'
SELECT NAME FROM sys.objects WHERE type='s' --找系统表
--找出该表下有些什么列
SELECT NAME FROM syscolumns WHERE id=OBJECT_ID('yo')
--case就和C/C++/C#这些语言中的swith case道理是一样的
--首先给yoID分组，然后组内分组
SELECT yoID,(CASE yoName
WHEN 'a' THEN 'shao'
WHEN 'b' THEN 'yozhu'
ELSE 'li' END)
FROM dbo.yozhu1 GROUP BY yoID,yoName
--case还能用在group by语句后
SELECT yoName,(CASE WHEN yoID<72 AND yoID>65 THEN N'合格' ELSE N'不合格' END)
FROM yozhu1 GROUP BY CASE WHEN yoID<72 AND yoID>65 THEN N'合格' ELSE N'不合格' END,yoName
--删除表数据，效果和delete一样
/*TRUNCATE删除数据性能更好;不能使用带where的字句;不能进行事务回滚*/
TRUNCATE TABLE dbo.yozhu1
--找出表的自增长列
SELECT Name FROM syscolumns WHERE id=OBJECT_ID('dbo.table_A')
AND (autoval IS NOT NULL OR status=128)
