##用例1：查詢數據

#01.查詢QQ號碼為54789625的所有好友信息，包括QQ號碼，昵稱，年齡

#1
SELECT QQID,NickName,Age FROM baseinfo WHERE QQID =54789625
#2
SELECT `relation`.RelationQQID AS QQ號碼,`baseinfo`.NickName AS 昵稱,`baseinfo`.Age AS 年齡
 FROM BaseInfo,Relation
WHERE BaseInfo.QQID=Relation.RelationQQID
 AND Relation.QQID=54789625
 AND RelationStatus=0

#02.查詢當前線上用戶的信息

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

#03.查詢北京的、年齡在18至45歲之間的線上用戶的信息

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

#04.查詢昵稱為青青草的用戶信息

SELECT * FROM `baseinfo` WHERE `NickName`='青青草'

#05.查詢QQ號碼為54789625的用戶的好友中每個省份的總人數，並且總人數按由大到小排序。

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

#06.查詢至少有150天未登錄QQ賬號的用戶信息，包括QQ號碼，最後一次登錄時間、等級、昵稱、年齡，並按時間的降序排列

#1
SELECT 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

#2
SELECT QQUser.QQID,QQUser.LastLogTime,QQUser.Level,BaseInfo.NickName,BaseInfo.Age
FROM BaseInfo,QQUser
WHERE BaseInfo.QQID=QQUser.QQID
AND DATEDIFF(NOW(),lastLogTime)>=150
ORDER BY DATEDIFF(NOW(),lastLogTime) DESC

#07.查詢QQ號碼為54789625的好友中等級為10級以上的“月亮”級用戶信息。

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.--查詢QQ號碼為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.--查詢好友超過20個的用戶信息。

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.為了查看信譽度，管理員需要查詢被當做黑名單人物次數排名前3的用戶

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

##用例2：修改數據

#01.假設我的QQ號碼為8855678，今天我隱身登錄

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

#02.假設我的QQ號碼為8855678，修改我的昵稱為“被淹死的魚”，地址為“解放中路號院123室”

UPDATE `baseinfo` SET `NickName`='被淹死的魚',`Address`='解放中路號院123室' WHERE `QQID`=8855678

#03.假設我的QQ號碼為54789625，將我的好友“青青草”拖進黑名單。

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

#04.為了提高QQ用戶的聊天積極性，把等級小於6級的用戶的等級都提升1個級別。

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

#05.管理員將超過365天沒有登錄過的QQ鎖定（即將等級值設定為-1）。

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

#06.為了獎勵用戶，將好友數量超過20的用戶等級提升1個級別。

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

#07.把QQ號碼為54789625的用戶的好友“嘟嘟魚”拖進黑名單中。

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

##用例3：刪除數據

#1.把QQ號碼為54789625的用戶黑名單中的用戶刪除。

DELETE  FROM relation WHERE QQID='54789625'

#2.QQ號碼為54789625的用戶多次在QQ中發佈違法信息，造成了很壞的影響，因此管理員決定將其刪除。

DELETE FROM baseinfo WHERE QQID ='54789625'

#3.管理員將超過1000天沒有登錄過的QQ刪除。

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