十三章QQ项目查询删除插入

最新推荐文章于 2021-12-24 19:25:24 发布

qq_36074096

最新推荐文章于 2021-12-24 19:25:24 发布

阅读量824

点赞数 1

CC 4.0 BY-SA版权

分类专栏：初始JAVA

本文链接：https://blog.youkuaiyun.com/qq_36074096/article/details/53364659

初始JAVA 专栏收录该内容

6 篇文章

订阅专栏

这篇博客展示了多个SQL查询和更新操作，包括根据条件查询QQ用户信息，如年龄、在线状态、省份分布；更新用户的在线状态、昵称和地址；管理好友关系和黑名单；以及批量更新在线状态。此外，还包含了删除指定用户及其关联记录的操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--  六   查询数据

SELECT QQID,NickName,Age FROM BaseInfo WHERE QQID='88662753'

SELECT * FROM QQUser WHERE Online=0

SELECT * FROM BaseInfo a,QQUser b WHERE(a. Age >=18 AND Age <=45 ) AND a.Province='北京市' and b.Online=0

SELECT * FROM BaseInfo WHERE NickName='秋芙蓉'

SELECT Province,COUNT(Province) FROM Relation b,Baseinfo c WHERE (b.QQID='54789625' and b.RelationStatus=0 and b.RelationQQID= c.QQID)
Group BY Province ORDER BY COUNT(Province) DESC 

SELECT  QQID,LastLogTime,Online,[Level] FROM QQUser 
 WHERE DATEDIFF(DD,LastLogTime,GETDATE())>150 ORDER BY  DATEDIFF(DD,LastLogTime,GETDATE()) DESC 


--等级大于2级的 
 SELECT RelationQQID,NickName,Age,[Level] FROM Relation C 
INNER JOIN BaseInfo as b ON C.QQID='54789625' and C.RelationStatus=0 and c.RelationQQID=b.QQID
INNER JOIN QQUser as E ON C.RelationQQID=E.QQID AND E.Level>=2


--隐身的
SELECT  RelationQQID,NickName,Age FROM Relation C 
INNER JOIN BaseInfo AS B ON C.QQID='54789625' AND C.RelationStatus=B.QQID
 INNER JOIN QQUser AS Q ON C.RelationQQID=Q.QQID AND Q.Online=1

--好友数大于2的
SELECT QQID AS QQ,COUNT(*) AS 好友总数 from Relation
where RelationStatus=0 group by QQID having COUNT(*)>2
--黑名单啊  
SELECT TOP 20 (RelationQQID)as qq,COUNT(*) AS 被当黑名单的人物数
FROM Relation
WHERE RelationStatus=1
GROUP BY RelationQQID ORDER BY COUNT(*) DESC

--用例7
--NO.1
UPDATE QQUser SET Online=2
WHERE QQID='8855678'
--NO.2
UPDATE BaseInfo SET NickName='被淹死的鱼', Address='解放中路6号院106室'
WHERE QQID='8855678'
--NO.3
UPDATE Relation SET Relationstatus=1
WHERE QQID='8855678' and RelationQQID='248624066'
--NO.4
UPDATE QQUser SET Online+=1
WHERE Online<6
--NO.5
UPDATE QQUser SET Online-=1
WHERE DATEDIFF(DD,LastLogTime,GETDATE())>365
--NO.6
UPDATE QQUser SET Online+=1
WHERE QQID IN (SELECT QQID FROM Relation WHERE RelationStatus=0 GROUP BY QQID HAVING COUNT(*)>=2)

--  嘟嘟鱼啊    嘟嘟鱼   

--用例8   删除
--NO.1
DELETE FROM Relation WHERE QQID='54789625' AND Relationstatus=1


--NO.2
DELETE FROM QQUser WHERE QQID='622009019'
DELETE FROM Relation WHERE QQID='622009019'
DELETE FROM BaseInfo WHERE QQID='622009019'