QQ management,

QQ management,

# Example 1: query data

#01. query information of all friends whose QQ number is 54789625, including QQ number, nickname, and age

　

#1 select qqid, NickName, Age FROM baseinfo where qqid = 54789625 #2 SELECT 'relation '. relationQQID as qq number, 'baseinfo '. nickName AS NickName, 'baseinfo '. age AS Age FROM BaseInfo, RelationWHERE BaseInfo. QQID = Relation. relationQQID AND Relation. QQID = 54789625 AND RelationStatus = 0

 

 

#02. Query Information of the current online user

#1SELECT *FROM qquser WHERE OnLine!=0#2SELECT NickName,`Province` FROMBaseInfo,QQUserWHERE BaseInfo.QQID=QQUser.QQID AND Online=0

 

#03. Query Information of online users aged between 18 and 45 in Beijing

 

SELECT * FROM baseinfo, qquser WHERE baseinfo. QQID = qquser. qqid and baseinfo. Province = 'beijing' AND baseinfo. Age BETWEEN 18 AND 45 AND qquser. OnLine> 0

 

#04. query user information with the nickname qingcao

SELECT * FROM 'baseinfo' WHERE 'nickname' = 'qingqingqingcao'

 

 

#05. query the total number of friends in each province of a user whose QQ number is 54789625, and the total number is sorted from large to small.

#1SELECT COUNT(Province) FROM baseinfo WHERE QQID IN (SELECT QQID FROM relation WHERE RelationQQIDIN(SELECT RelationQQID FROM relation WHERE QQID ='54789625' ))GROUP BY Province#2SELECT `baseinfo`.`Province`,COUNT(*) FROM `relation`,`baseinfo`WHERE `relation`.`RelationQQID`=`baseinfo`.`QQID`AND `relation`.`RelationStalus`=0AND `relation`.`QQID`=54789625GROUP BY `baseinfo`.`Province`ORDER BY COUNT(*) DESC

 

#06. query the information of users who have not logged on to the QQ account for at least 150 days, including the QQ number, Last Logon Time, grade, nickname, and age, in descending order of time

 

#1SELECT qquser.QQID, LastLogTime,LEVEL,NickName,Age FROM baseinfo,qquser WHERE baseinfo.QQID=qquser.QQID AND baseinfo.QQID IN(SELECT QQID FROM qquser WHERE DATEDIFF(NOW(),LastLogTime)>=150 )GROUP BY LastLogTime#2SELECT QQUser.QQID,QQUser.LastLogTime,QQUser.Level,BaseInfo.NickName,BaseInfo.AgeFROM BaseInfo,QQUserWHERE BaseInfo.QQID=QQUser.QQIDAND DATEDIFF(NOW(),lastLogTime)>=150ORDER BY DATEDIFF(NOW(),lastLogTime) DESC

 

#07. Query "moon" user information of friends whose QQ number is 54789625 and whose grade is 10 or higher.

SELECT *FROM baseinfo WHERE QQID IN(    SELECT QQID FROM qquser WHERE LEVEL>10 AND QQID IN(        SELECT QQID FROM relation WHERE RelationQQID IN (            SELECT RelationQQID FROM relation WHERE QQID ='54789625'        )    )) 

 

#08. -- query the invisible user information of friends whose QQ number is 54789625.

SELECT *FROM baseinfo WHERE QQID IN (    SELECT QQID FROM qquser WHERE OnLine=0 AND QQID IN (        SELECT QQID FROM relation WHERE RelationQQID IN (            SELECT RelationQQID FROM relation WHERE QQID ='54789625'        )    ))

 

#09. -- Query Information of more than 20 friends.

 

SELECT *FROM baseinfo WHERE QQID IN (    SELECT QQID FROM relation WHERE RelationQQID IN(        SELECT RelationQQID FROM relation GROUP BY QQID HAVING COUNT(RelationQQID)>20    ))

 

 

 

#10. To view credibility, the administrator needs to query the top 3 users who have been blacklisted

 

SELECT * FROM  baseinfo WHERE baseinfo.QQID IN(    SELECT qquser.QQID FROM qquser WHERE baseinfo.QQID=qquser.QQID AND qquser.QQID IN(    SELECT relation.QQID FROM relation WHERE relation.RelationStalus=1)ORDER BY qquser.Level DESC)         LIMIT 3

 

# Example 2: Modify data

#01. Suppose my QQ number is 8855678. I logged on to the console today.

 

UPDATE `qquser` SET `online`=0 WHERE `QQID`=8855678

 

 

 

#02. Assume that my QQ number is 8855678, change my nickname to "Drowned fish", and address to "Room 123, Jiefang Zhong Road No"

UPDATE 'baseinfo' SET 'nickname' = 'drowned fish', 'address' = 'room 123, No. 8855678, Jiefang Zhong Road, WHERE 'qqid' =

 

#03. If my QQ number is 54789625, I will drag my friend "qingqingcao" to the blacklist.

UPDATE relation SET RelationStalus =1 WHERE QQID ='54789625'UPDATE relation SET RelationStalus =1 WHERE QQID ='54789625'

 

#04. In order to improve the chat enthusiasm of QQ users, the user levels with a level less than 6 are all upgraded to one level.

 

UPDATE qquser SET LEVEL =LEVEL+1 WHERE LEVEL<6

 

 

 

#05. The administrator has locked the QQ account that has not been logged on for more than 365 days (set the level value to-1 ).

UPDATE qquser SET LEVEL =-1 WHERE DATEDIFF(NOW(),LastLogTime)>=365 

 

#06. In order to reward users, users with more than 20 friends are upgraded to one level.

 

UPDATE qquser SET LEVEL=LEVEL+1  WHERE (SELECT RelationQQID FROM relation GROUP BY QQID HAVING COUNT(RelationQQID)>20)

 

 

 

#07. Drag the "Doodle fish" of a user whose QQ number is 54789625 to the blacklist.

UPDATE  relation SET RelationStalus =1 WHERE QQID ='54789625'

 

# Example 3: Delete data

#1. Delete the users in the user blacklist with QQ number 54789625.

DELETE  FROM relation WHERE QQID='54789625'

 

#2. users with a QQ number of 54789625 repeatedly publish illegal information on QQ, which has a very bad impact. Therefore, the Administrator decided to delete the information.

DELETE FROM baseinfo WHERE QQID ='54789625'

 

#3. The Administrator deletes QQ logs that have not been logged in for more than 1000 days.

DELETE FROM qquser WHERE DATEDIFF(NOW(),LastLogTime)>=1000