13章 练习

--查询数据   --1   select RelationQQID as 好友QQ,NickName as 昵称,Age as 年龄   from [QQ].[dbo].[Relation] as a,[QQ].[dbo].[BaseInfo] as b   where a.QQID=88662753 and RelationStatus=1     --2 查询当前在线用户信息 select b. QQID, NickName, Sex, Age, Province, City, Address, Phone from [QQ].[dbo].[QQUser] a,[QQ].[dbo].[BaseInfo] b where Online=0 and a.QQID=b.QQID    --3 查询北京市,年龄在18-45岁间的在线用户信息   ------select b.QQID, NickName, Sex, Age, Province, City, Address, Phone   ------from [QQ].[dbo].[BaseInfo] b,[QQ].[dbo].[QQUser] a   ------where Province='北京市' and Age>18 or Age<45   and Online=0  AND B.QQID=A.QQID      --4 查询昵称为'飞快的火车 '的用户信息    select*    from [QQ].[dbo].[BaseInfo]    where NickName='飞快的火车 '           --5 查询QQ号码为54789625的用户的好友中每个省份的总人数,且按总人数由大到小排序   SELECT Province,COUNT(*)AS 好友人数   FROM [QQ].[dbo].[BaseInfo] B,[QQ].[dbo].[Relation] A   WHERE A.QQID=54789625 AND RelationStatus=0 and a.RelationQQID=b.QQID   GROUP BY Province   ORDER BY COUNT(*) DESC      --6 查询至少150天未登陆QQ账号的用户信息,包括QQ号码,最后一次登陆时间,等级,昵称,年龄,并按时间的降序排列   SELECT B.QQID,Q.LastLogTime,Q.Level,B.NickName,B.Age   FROM [QQ].[dbo].[BaseInfo] B,[QQ].[dbo].[QQUser] Q   WHERE B.QQID=Q.QQID AND DATEDIFF(DD,Q.LastLogTime, GETDATE())>150   ORDER BY   Q.LastLogTime  DESC   --7查询QQ54789625中的好友等级为10级以上的用户信息   SELECT R.QQID,B.NickName,B.Age,a.Level   FROM [QQ].[dbo].[QQUser] a,[QQ].[dbo].[BaseInfo] B,[QQ].[dbo].[Relation] R   WHERE Level>10 and R.QQID=54789625 and RelationStatus=0 and a.QQID=B.QQID     --8 查询QQ54789625中所有隐身好友的信息      ----SELECT R.RelationQQID ,B.NickName, B.Age   ----FROM [QQ].[dbo].[Relation] R,[QQ].[dbo].[BaseInfo] B ,[QQ].[dbo].[QQUser] Q   ----WHERE  Q.QQID=54789625 AND Online=2 and RelationStatus=0    --9 查询好友超过20个用户QQ号码及其好友总数   SELECT R.QQID,COUNT(*) AS 人数   FROM [QQ].[dbo].[Relation] R,[QQ].[dbo].[BaseInfo] B   WHERE R.QQID=B.QQID    GROUP BY R.QQID   HAVING COUNT(*)>1   --10   select top 20 r.RelationQQID,COUNT(*)AS 被拉黑人数次数   from [QQ].[dbo].[Relation] r, [QQ].[dbo].[BaseInfo] b   where r.RelationQQID=b.QQID  and r.RelationStatus=1   group by r.RelationQQID   order by COUNT(*)   --修改数据   --1   update [QQ].[dbo].[QQUser] set Online=2   where QQID=8855678   --2   UPDATE [QQ].[dbo].[BaseInfo]    SET NickName='被淹死的鱼'        ,Address='解放中路6号院106室'      where QQID=95001200   --3   update[QQ].[dbo].[Relation] set RelationStatus=1   where QQID=8855678   --4    update [QQ].[dbo].[QQUser] set Level=Level+1    where Level<6    --5    update [QQ].[dbo].[QQUser] set Level=-1     where  DATEDIFF(dd,LastLogTime,GETDATE())>365    --6    update [QQ].[dbo].[QQUser] set Level=Level+1     where QQID in (select QQID from [QQ].[dbo].[Relation] group by QQID     having COUNT(*)>20)    --7    update [QQ].[dbo].[Relation] set RelationStatus=1     where RelationQQID in(select QQID from [QQ].[dbo].[BaseInfo]where NickName='嘟嘟鱼' )     AND QQID=286314 AND RelationStatus=0