QQ数据库管理

insert dbo.QQUserselect '54789625','add512#&','2013-02-16 17:01:35',2,1 unionselect '88662753','admin0219','2013-02-19 21:08:50',0,5 unionselect '8855678','guest0221','2013-02-21 16:28:20',1,6insert dbo.BaseInfoselect '54789625','蝴蝶飞飞',1,16,'北京市','北京','亚运村','37547388157668' unionselect '88662753','秋芙蓉',0,20,'河南省','南阳','方城博望','88715783657725' unionselect '8855678','双眼皮の潴',1,38,'北京市','北京','双榆树东里','65794968876143' insert dbo.Relationselect '54789625','88662753',0 unionselect '88662753','8855678',1 unionselect '54789625','8855678',0select QQID as QQ账号,NickName as 昵称,Age as 年龄from dbo.BaseInfowhere QQID=88662753select a.QQID  as QQ账号,a.NickName as 昵称,a.Sex as 性别,a.Age as 年龄,a.Province as 省份,a.City as  城市 ,b.Online as 在线状态from dbo.BaseInfo as a,dbo.QQUser as bwhere a.QQID=b.QQID and Online=0select a.QQID  as QQ账号,a.NickName as 昵称,a.Sex as 性别,a.Age as 年龄,a.Province as 省份,a.City as  城市 ,b.Online as 在线状态from dbo.BaseInfo as a,dbo.QQUser as bwhere a.QQID=b.QQID and a.Age>=18 and a.Age<=45 and a.Province='北京市' and b.Online=0select * ,b.Level as 等级, b.Online as 在线状态from dbo.BaseInfo as a,dbo.QQUser as bwhere a.QQID=b.QQID and NickName='秋芙蓉'select COUNT(*) as 总人数,Province as 省份,b.QQID as QQ号码from dbo.BaseInfo as a,dbo.Relation as bwhere(b.QQID=54789625 and b.RelationStatus=0 and b.RelationQQID=a.QQID)group by Province,b.QQIDorder by COUNT(*) descselect a.QQID as QQ号码,a.LastLogTime as 最后一次登录时间,a.Level as 等级,b.NickName as 昵称,b.Age as 年龄from dbo.QQUser as a,dbo.BaseInfo as bwhere DATEDIFF(DD,LastLogTime,GETDATE())>150 order by a.LastLogTime descselect a.QQID as 好友QQ号码,b.NickName as 昵称,b.Age as 年龄,c.Level as 等级from dbo.Relation as a,dbo.BaseInfo as b,dbo.QQUser as cwhere a.RelationQQID=b.QQID and b.QQID=c.QQID and a.QQID='54789625' and c.Level>=10select a.QQID as 好友QQ号码,b.NickName as 昵称,b.Age as 年龄,c.Online as 在线状态 from dbo.Relation as a,dbo.BaseInfo as b,dbo.QQUser as c where a.RelationQQID=b.QQID and b.QQID=c.QQID and a.QQID='54789625' and c.Online=2select COUNT(*) as 好友人数,QQID as 用户QQ号from dbo.Relation group by QQIDhaving COUNT(*)>=2   select top 20 COUNT(*) as 黑名单人物次数,RelationQQID as 黑名单用户from dbo.Relationwhere RelationStatus=1group by RelationQQIDhaving  COUNT(*)>=1--修改数据update dbo.QQUser set Online=2where QQID=8855678update dbo.BaseInfo set NickName='被淹死的鱼',Address='解放中路6号院106室'where QQID=8855678update dbo.Relation set RelationStatus=1where QQID=54789625 and RelationQQID=88662753update dbo.QQUser set Level=Level+1where Level<6update dbo.QQUser set Level=-1where DATEDIFF(DD,LastLogTime,GETDATE())>365update dbo.QQUser set Level=Level+1where QQID in(select QQID from dbo.Relation group by QQID having COUNT(*)>1)