sql 备忘

--1.查询数据库的所有表的所有字段
select name from syscolumns where id in (select id from sysobjects where xtype='u' 
and name='StoryParts')and name like'%Story%' 

--2.根据一个表的字段修改另一个表的字段
 update Customer set ProjectCount=(select COUNT(ID) from [Project] where 
 Customer.ID=Project.CustomerID)   

--3.修改列名
  exec sp_rename 'StoryParts.AudioClassIDsl','AudioClassIDs'  

--4.根据商品价格修改购物车商品价格
update Yim_OrderProduct  set [ProductPrice]= [Yim_GoodsSku].Price from 
[Yim_GoodsSku] where [Uid]=1 and [Oid]=0 and [BuyNow]=0 
and [Yim_OrderProduct].SkuID=[Yim_GoodsSku].SkuID 

--5.修改60天未互动的客户为沉默客户
update [Customer] set [Activation]=2 where exists(select CustomerID from Interaction 
 group by CustomerID  having MAX(addTime) <'sfdsf')


--6.查询数据所有表的数据行数
SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY a.name,b.rows DESC    

  /*7.查询所有的表名(用户自建的表,含聚集索引(主键)的表)及空间占用量\行数 【indid:索引 ID： 
1 = 聚集索引
>1 = 非聚集
255 = 具有 text 或 image 数据的表条目]】*/
select
object_name(id) tablename,
8*reserved/1024 reserved,
rtrim(8*dpages)+'kb' used,
8*(reserved-dpages)/1024 unused,
8*dpages/1024-rows/1024*minlen/1024 free,
rows
--,*
from sysindexes
where indid=1 and status=2066
order by tablename,reserved desc    


 --8.查询所有的表
select id,name from sysobjects where type='U'
select object_name(id) tablename,* from sysindexes where  indid=1 and  id >20000 --indid>1   

--9.查出未设置主键的表，不太准确，但是很接近
select obj.id,obj.name from sysobjects obj left join sysindexes id on id.id=obj.id where
 obj.type='U' and id.id >2000 and id.indid =0   


--10.修改活动里面的商品数量
update [SalePromotion] set ProductCount=(select COUNT(id) from PromotionProduct where
 ThePromotionID=SalePromotion.ID and Exists(select ID from Product where
 ID=PromotionProduct.ProductID and [State]=0 ))

--11.删除已经已参加活动然后下架的商品
delete [PromotionProduct] where  not Exists(select ID from Product where
 ID=PromotionProduct.ProductID and [State]=0 )

--12.查询数据库里面的所有自增值
SELECT
     b.name TableName
    ,a.name ColumnName
    ,a.seed_value
    ,a.increment_value
    ,a.last_value
FROM  sys.identity_columns a
INNER JOIN sys.tables b ON a.object_id = b.object_id

13.mysql 修改正反值(如把1改成0，0改成1)，场景:购物车记录的选中和取消
 update {tableName} set `isSelected`=(case  when isSelected = 0 then 1 when isSelected = 1 then 0 end) where id={id}