MS-SQL数据库笔记

1. 树(sql2005以上版本)

--通过子节点查询父节点
WITH 
TREE AS( 
    SELECT * FROM tstructure 
    WHERE id = 6  -- 要查询的子 id 
    UNION ALL 
    SELECT tstructure.* FROM tstructure, TREE 
    WHERE TREE.parent = tstructure.id
) 
SELECT * FROM TREE

--通过父节点查询子节点
WITH TREE AS 
(   
    SELECT t.*, 0 level FROM t_sys_menu t WHERE t.menu_id = 1  -- 要查询的父 id
    UNION ALL   
    SELECT m.*, Tree.level + 1 FROM t_sys_menu m, TREE WHERE m.up_menu_id = TREE.menu_id  
)   
SELECT * FROM TREE  

 

2. 从结果集更新表 

update t_health_gps 
set latitude = b.lat, longitude = b.lng 
from t_health_gps a , 
(select x.aged_id, 
y.community, y.detailed, 
z.area_name,z.position, 
u.gps_id, u.latitude,u.longitude,
SUBSTRING(z.position, 0, CHARINDEX(',', z.position)) as lng,
SUBSTRING(z.position, CHARINDEX(',', z.position) + 1, LEN(z.position)) as lat
from t_aged x, t_aged_address y, t_area_dict z, t_health_gps u
where x.aged_id = y.aged_id  and y.community = z.area_id and u.aged_id = x.aged_id 
) as b
where a.gps_id = b.gps_id

 

3. 注释

EXECUTE sp_addextendedproperty N'MS_Description', '服务商信息表',
	 N'user', N'dbo', N'table', N't_sp_company', NULL, NULL;
EXECUTE sp_addextendedproperty N'MS_Description', '服务商ID',
	 N'user', N'dbo', N'table', N't_sp_company', N'COLUMN', N'sp_id';

 

4. 修改字段

alter table t_member alter column face_url varchar(500);

 

5. 分页查询

-- sqlserver 2000不支持ROW_NUMBER()
SELECT * FROM t_table as t1 , 
( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY t1.id DESC) as n,  t1.id FROM t_table as t1 ) t2 
WHERE t1.id = t2.id and t2.n > 10 ;
-- 通用
SELECT * FROM t_table as t1
WHERE t1.id in
(
	SELECT TOP 10 t1.id FROM 
	(
		SELECT TOP 20 t1.id FROM t_table as t1 ORDER BY t1.id DESC
	) as t1 ORDER BY t1.id ASC
)
ORDER BY t1.id DESC;

 

6. 强制清空数据库所有表的数据

-- 强制清空数据库所有表的数据
CREATE PROCEDURE sp_DeleteAllData
AS
	-- sp_MSForEachTable !!注意:微软官方文档对此存储过程没有详细描述
	
	-- 禁用约束
	EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
	-- 禁用触发器
	EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
	-- 删除所有表的数据
	EXEC sp_MSForEachTable 'DELETE FROM ?'
	-- 启用约束
	EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
	-- 启用触发器
	EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
	-- 执行检查, 可省略
	EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'
GO


-- 执行存储过程
EXEC sp_DeleteAllData;

 

7. 删除所有表

--/第1步**********删除所有表的外键约束*************************/

DECLARE c1 cursor for
select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
from sysobjects
where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
begin
exec(@c1)
fetch next from c1 into @c1
end
close c1
deallocate c1

--/第2步**********删除所有表*************************/

 
GO
declare @sql varchar(8000)
while (select count(*) from sysobjects where type='U')>0
begin
SELECT @sql='drop table ' + name
FROM sysobjects
WHERE (type = 'U')
ORDER BY 'drop table ' + name
exec(@sql) 
end

 

 8. 表|列|索引-重命名

-- 基本语法:
-- sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'  [ , [ @objtype = ] 'object_type' ]

--eg:
-- 1. table
EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr'; 

-- 2. column
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN'; 

-- 3. index
GO EXEC sp_rename N'Purchasing.ProductVendor.IX_ProductVendor_VendorID', N'IX_VendorID', N'INDEX';

 9. 查找外键信息

-- 外键约束名称, 子表, 子表列, 父表
select fk.name fkname , ftable.name ftablename, cn.name fkcol, rtable.name ftablename from sysforeignkeys 
  join sysobjects fk 
    on sysforeignkeys.constid = fk.id 
  join sysobjects ftable 
    on sysforeignkeys.fkeyid = ftable.id 
  join sysobjects rtable 
    on sysforeignkeys.rkeyid = rtable.id 
  join syscolumns cn 
    on sysforeignkeys.fkeyid = cn.id and sysforeignkeys.fkey = cn.colid