SQL表之间复制数据、选出随机几条数据、删除重复数据、取得自增长列等操作

 

--表之间数据复制
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)