mysql 存qq数据_MySQL---QQ数据库

insert into BaseInfo

select 54789625,'蝴蝶飞飞',1,16,'北京市','北京','亚运村','37543881566'union

select 88662753,'秋芙蓉',0,20,'河南省','南阳','方城博望','88715783657'union

select 8855678,'双眼',1,38,'北京市','北京','双榆树东村','65794968876'

insert into QQUser

values(54789625,'123456','2013-02-16 17:01:35',2,1),

(88662753,'130219','2013-02-19 21:08:50',0,5),

(8855678,'130221','2013-02-21 16:28:20',1,6)

--查询QQ号码为1966679466的用户的所有好友信息

SELECT QQID,NickName,Age

FROM BaseInfo

WHERE QQID=1966679466

--查询QQ9966903的用户的好友中每个省份的总人数,并且总人数按由大到小排序

SELECT Province,Relation.QQID

FROM Relation,BaseInfo

WHERE (Relation.QQID=9966903 AND Relation.RelationStatus=0 AND Relation.RelationQQID=BaseInfo.QQID)

GROUP BY Province,Relation.QQID

ORDER BY COUNT(Relation.QQID)

--查询QQ号码为54789625的好友中等级为10级以上的“月亮”级用户信息

SELECT *

FROM Relation

INNER JOIN BaseInfo ON BaseInfo.QQID=54789625

INNER JOIN QQUser ON QQUser.Level>=10

--查询被当做黑名单人物次数排名前20的用户

SELECT top 20 QQID

FROM Relation

WHERE RelationStatus=1

--修改用户的个人信息

update dbo.BaseInfo

set NickName='青青',Address='峪里村'

WHERE QQID=8855678

--修改限定条件关系用户的信息

update Relation

set RelationQQID=6609860,RelationStatus=1

WHERE QQID=4736834

--修改管理方面的信息

update QQUser

set Level=(Level+1)

WHERE Level<6

--删除黑名单中的用户

DELETE Relation

WHERE QQID=88088108 AND RelationStatus=1

--删除超过1000天没有登录过的QQ

update QQUser

set Level=-1

WHERE DATEDIFF(dd,LastLogTime,GETDATE())>1000 and Online=1