QQ数据库管理

insert dbo.QQUser
select '54789625','add512#&','2013-02-16 17:01:35',2,1 union
select '88662753','admin0219','2013-02-19 21:08:50',0,5 union
select '8855678','guest0221','2013-02-21 16:28:20',1,6
insert dbo.BaseInfo
select '54789625','蝴蝶飞飞',1,16,'北京市','北京','亚运村','37547388157668' union
select '88662753','秋芙蓉',0,20,'河南省','南阳','方城博望','88715783657725' union
select '8855678','双眼皮の潴',1,38,'北京市','北京','双榆树东里','65794968876143' 
insert dbo.Relation
select '54789625','88662753',0 union
select '88662753','8855678',1 union
select '54789625','8855678',0
select QQID as QQ账号,NickName as 昵称,Age as 年龄
from dbo.BaseInfo
where QQID=88662753
select 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 b
where a.QQID=b.QQID and Online=0
select 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 b
where a.QQID=b.QQID and a.Age>=18 and a.Age<=45 and a.Province='北京市' and b.Online=0
select * ,b.Level as 等级, b.Online as 在线状态
from dbo.BaseInfo as a,dbo.QQUser as b
where a.QQID=b.QQID and NickName='秋芙蓉'
select COUNT(*) as 总人数,Province as 省份,b.QQID as QQ号码
from dbo.BaseInfo as a,dbo.Relation as b
where(b.QQID=54789625 and b.RelationStatus=0 and b.RelationQQID=a.QQID)
group by Province,b.QQID
order by COUNT(*) desc
select a.QQID as QQ号码,a.LastLogTime as 最后一次登录时间,a.Level as 等级,b.NickName as 昵称,b.Age as 年龄
from dbo.QQUser as a,dbo.BaseInfo as b
where DATEDIFF(DD,LastLogTime,GETDATE())>150 
order by a.LastLogTime desc
select 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 c
where a.RelationQQID=b.QQID and b.QQID=c.QQID and a.QQID='54789625' and c.Level>=10
select 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=2
select COUNT(*) as 好友人数,QQID as 用户QQ号
from dbo.Relation 
group by QQID
having COUNT(*)>=2   
select top 20 COUNT(*) as 黑名单人物次数,RelationQQID as 黑名单用户
from dbo.Relation
where RelationStatus=1
group by RelationQQID
having  COUNT(*)>=1


--修改数据
update dbo.QQUser set Online=2
where QQID=8855678


update dbo.BaseInfo set NickName='被淹死的鱼',Address='解放中路6号院106室'
where QQID=8855678


update dbo.Relation set RelationStatus=1
where QQID=54789625 and RelationQQID=88662753


update dbo.QQUser set Level=Level+1
where Level<6


update dbo.QQUser set Level=-1
where DATEDIFF(DD,LastLogTime,GETDATE())>365


update dbo.QQUser set Level=Level+1
where QQID in(select QQID from dbo.Relation group by QQID having COUNT(*)>1)