SQL第十三章QQ

最新推荐文章于 2024-05-12 19:39:59 发布
原创最新推荐文章于 2024-05-12 19:39:59 发布 · 7.1k 阅读
19 ·
本内容遵循CC 4.0 BY-SA版权协议
收录于
SQL
这个博客展示了如何使用SQL进行QQ用户数据管理，包括向QQUser、BaseInfo和Relation表中插入数据，以及各种查询操作，如获取好友信息、在线用户、地理位置筛选等。还涉及更新和删除操作，如修改用户资料、设置黑名单、提升用户等级以及处理长时间未登录的用户。
--向QQUser表插入数据
INSERT INTO QQUser(QQID,PassWord,lastLogTime,Online,Level)
			VALUES(54789625,'add512#&','2013-02-16 17:01:35',2,1)
INSERT INTO QQUser(QQID,PassWord,lastLogTime,Online,Level)
			VALUES(88662753,'admin0219','2013-02-19 21:08:50',0,5)
INSERT INTO QQUser(QQID,PassWord,lastLogTime,Online,Level)
			VALUES(8855678,'guest0221','2013-02-21 16:28:20',1,6)

--向BaseInfo表插入数据
INSERT INTO BaseInfo(QQID,NickName,Sex,Age,Province,City,Address,Phone)
                  VALUES(54789625,'蝴蝶飞飞',1,16,'北京','朝阳区','亚运村','37547388157668')
INSERT INTO BaseInfo(QQID,NickName,Sex,Age,Province,City,Address,Phone)
                  VALUES(88662753,'秋芙蓉',0,20,'河南省','南阳','方城博望','88715783657725')
INSERT INTO BaseInfo(QQID,NickName,Sex,Age,Province,City,Address,Phone)
                  VALUES(8855678,'双眼皮の潴',1,38,'北京','海淀区','双榆树东里','65794968876143')

--向Relation表插入数据
INSERT INTO Relation(QQID,RelationQQID,RelationStatus)VALUES(54789625,88662753,0)
INSERT INTO Relation(QQID,RelationQQID,RelationStatus)VALUES(88662753,8855678,1)
INSERT INTO Relation(QQID,RelationQQID,RelationStatus)VALUES(54789625,8855678,0)




--查询QQ号码为88662753的用户的所有好友信息
SELECT R.RelationQQID AS 好友QQ号,B.NickName AS 昵称,B.Age AS 年龄
FROM Relation AS R,BaseInfo AS B
WHERE R.QQID=88662753 AND R.RelationStatus=0 AND R.RelationQQID=B.QQID

--查询当前在线用户的信息
SELECT Q.QQID AS QQ号,NickName  AS 昵称,Sex AS 性别,Age AS 年龄,Province AS 省份,City AS 城市
FROM QQUser AS Q,BaseInfo AS B
WHERE Q.QQID=B.QQID AND Q.Online=0

--查询北京的、年龄在18至45岁之间的在线用户的信息。
SELECT Q.QQID AS QQ号,NickName  AS 昵称,Sex AS 性别,Age AS 年龄,Province AS 省份,City AS 城市
FROM QQUser AS Q,BaseInfo AS B
WHERE Q.QQID=B.QQID AND Q.Online=0 AND (B.Age BETWEEN 18 AND 45) AND (B.Province LIKE '北京%' OR B.City LIKE '北京%')

--查询昵称为“小笨猪”的用户信息。
SELECT * FROM BaseInfo WHERE NickName='小笨猪'

--查询QQ号码为54789625的用户的好友中每个省份的总人数，并且总人数按由大到小排序。
SELECT B.Province AS 省份,COUNT(*) AS 好友总人数
FROM Relation AS R,BaseInfo AS B
WHERE( R.QQID=54789625 AND R.RelationStatus=0 AND R.RelationQQID=B.QQID)
GROUP BY B.Province ORDER BY COUNT(*) DESC

--查询至少有150天未登录QQ帐号的用户信息。
SELECT QQUser.QQID,LastLogTime,Level,NickName,Age FROM QQUser,BaseInfo 
WHERE QQUser.QQID=BaseInfo.QQID AND DATEDIFF(DAY,LastLogTime,GETDATE())>150 
ORDER BY LastLogTime DESC

--查询QQ号码为54789625的好友中等级为10级以上的“月亮”级用户信息。
SELECT R.RelationQQID AS 好友QQ号,B.NickName AS 昵称,B.Age AS 年龄,Q.Level AS 等级
FROM Relation AS R 
INNER JOIN BaseInfo AS B
ON R.QQID=54789625 AND R.RelationStatus=0 AND R.RelationQQID=B.QQID 
INNER JOIN QQUser AS Q
ON R.RelationQQID=Q.QQID AND Q.Level>=10 

--查询QQ号码为54789625的好友中隐身的用户信息。
SELECT R.RelationQQID AS 好友QQ号,B.NickName AS 昵称,B.Age AS 年龄
FROM Relation AS R 
INNER JOIN BaseInfo AS B
ON R.QQID=54789625 AND R.RelationStatus=0 AND R.RelationQQID=B.QQID 
INNER JOIN QQUser AS Q
ON R.RelationQQID=Q.QQID AND Q.OnLine=2

--查询好友超过20个的用户信息。
SELECT QQID AS QQ号,COUNT(*) AS 好友总数  FROM Relation 
WHERE RelationStatus=0 
GROUP BY QQID 
HAVING COUNT(*)>=20

--为了查看信誉度，管理员需要查询被当做黑名单人物排名前20的最低信誉度用户。
SELECT TOP 20 (RelationQQID )AS QQ号,COUNT(*) AS 被当做黑名单人物总数  FROM Relation 
WHERE RelationStatus=1 
GROUP BY RelationQQID 
ORDER BY COUNT(*) DESC




--今天我隐身登录，假设我的QQ号码为8855678。
UPDATE QQUser SET OnLine=2,LastLogTime=GETDATE() WHERE QQID=8855678

--假设我的QQ号码为8855678，修改我的昵称为“被淹死的鱼”，地址为“解放中路6号院106室”。
UPDATE BaseInfo SET NickName='被淹死的鱼',Address='解放中路6号院106室' WHERE QQID=8855678

--假设我的QQ号码为8855678，将我的好友“248624066”拖进黑名单。
UPDATE Relation SET RelationStatus=1 WHERE QQID=8855678 AND RelationQQID=248624066

--为了提高QQ用户的聊天积极性，把等级小于6级的用户的等级都提升1个级别。
UPDATE QQUser SET Level=level+1 WHERE level<=5 

--管理员将超过365天没有登录过的QQ锁定（即将等级值设定为-1）。
UPDATE QQUser SET Level=-1 WHERE DATEDIFF(DAY,lastLogTime,GETDATE())>=365

--为了奖励用户，将好友数量超过20的用户等级提升1个级别。
UPDATE QQUser SET Level=level+1 WHERE QQID IN
(SELECT QQID  FROM Relation  WHERE RelationStatus=0 
GROUP BY QQID HAVING COUNT(*)>=20)

--把QQ号码为286314的用户的好友“嘟嘟鱼”拖进黑名单中。
UPDATE Relation SET RelationStatus=1
WHERE QQID=286314 
AND RelationQQID IN(SELECT QQID FROM BaseInfo WHERE NickName='嘟嘟鱼')
AND  RelationStatus=0




--把QQ号码为54789625的用户黑名单中的用户删除。
DELETE FROM Relation WHERE QQID=54789625 AND RelationStatus=1


--QQ号码为622013019用户多次在QQ中发布违法信息，造成了很坏的影响，因此管理员决定将其删除。
----在关系表中删除用户622013019的信息
DELETE FROM Relation WHERE QQID=622013019 OR RelationQQID=622013019
-----在基本信息表中删除用户622013019的信息
DELETE  FROM BaseInfo WHERE QQID=622013019
-----在用户表中删除用户622013019的信息
DELETE  FROM QQUser WHERE QQID=622013019



--管理员将超过1000天没有登录过的QQ删除。
----查询超过1000天没有登录过的QQID集
SELECT QQID FROM QQUser WHERE DATEDIFF(DAY,LastLogTime,GETDATE())>=1000
----删除Relation表中的数据
DELETE FROM Relation 
WHERE QQID IN(SELECT QQID FROM QQUser WHERE DATEDIFF(DAY,LastLogTime,GETDATE())>=1000)
 OR RelationQQID IN(SELECT QQID FROM QQUser WHERE DATEDIFF(DAY,LastLogTime,GETDATE())>=1000)
----删除BaseInfo表中的数据
DELECT FROM BaseInfo WHERE QQID IN(SELECT QQID FROM QQUser WHERE DATEDIFF(DAY,LastLogTime,GETDATE())>=1000)
----删除QQUser表中的数据
DELETE FROM QQUser WHERE DATEDIFF(DAY,LastLogTime,GETDATE())>=1000