第三本 十三章上机

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [QQID]
      ,[RelationQQID]
      ,[RelationStatus]
  FROM [qq].[dbo].[Resation]
  
  INSERT INTO dbo.Resation(QQID,RelationQQID,RelationStatus)
  values(54789625,88662756,0)
  
   INSERT INTO dbo.Resation(QQID,RelationQQID,RelationStatus)
  values(88662756,8855678,1)
  
   INSERT INTO dbo.Resation(QQID,RelationQQID,RelationStatus)
  values(54789625,8855678,0)
  --好友
 SELECT a.RelationQQID,b.NickName,b.Age
 FROM dbo.Baselofo as b, dbo.Resation as a
 WHERE a.QQID=88662756 AND a.QQID=b.QQID
 --在线信息  北京市 18-45 
 SELECT A.QQID,A.NickName,A.Sex,A.Age,A.Phone,A.Proince,A.Address,A.City
 FROM dbo.Baselofo AS A,dbo.QQUser AS B
 WHERE (A.Age>=18 OR A.Age<=45) AND  B.Online=0 AND  A.Proince='北京市' 
 --查名字
 SELECT a.QQID,a.Online,a.LastLogTime,a.Password,a.Level,b.NickName,b.Age,b.Proince,b.Address,b.Phone
 FROM dbo.QQUser as a,dbo.Baselofo as b
 where b.NickName='秋芙蓉' AND a.QQID=b.QQID
 
 --省份人数 并排序
 SELECT COUNT(b.Proince),b.Proince
 FROM dbo.Baselofo as b
 GROUP BY b.Proince
 ORDER BY COUNT(b.Proince) desc
 
 --150未登录的账号信息
 SELECT U.QQID,U.LastLogTime,U.Level,B.Age,B.NickName
 FROM dbo.QQUser AS U,dbo.Baselofo AS B
WHERE DATEDIFF(DD,U.LastLogTime,GETDATE())>150 and B.QQID=U.QQID

--查询等级
select b.QQID,b.NickName,b.Age,U.Level
from dbo.Baselofo AS b,dbo.QQUser AS U,dbo.Resation as r
where r.QQID=54789625 AND U.QQID=R.RelationQQID and U.Level>5 and b.QQID=r.QQID
--隐身的用户
select b.QQID,b.NickName,b.Age
from dbo.Baselofo as b,dbo.QQUser as u
where u.Online=2 AND u.QQID=b.QQID
--好友人数
SELECT COUNT(b.RelationQQID),b.QQID
from dbo.Resation as b
where b.QQID=54789625  
GROUP BY  b.QQID
HAVING COUNT(b.RelationQQID)>=2
-- 信誉度
select b.QQID,b.NickName,count(b.QQID)
from dbo.Baselofo as b,dbo.Resation as r
where r.QQID=b.QQID AND RelationStatus=0
GROUP BY b.QQID,b.NickName
--修改 885678隐身登陆
 UPDATE dbo.QQUser
 set dbo.QQUser.Online=2
 WHERE dbo.QQUser.QQID=8855678
 --修改昵称 地址
  UPDATE b
  set b.NickName='被淹死的鱼',b.Address='解放路'
  from dbo.Baselofo as b, dbo.QQUser as u
  where u.QQID=8855678
  -- 拉黑名单 
  update r
  set r.RelationStatus=0
  from dbo.Resation as r
  where r.QQID=88662756  And r.RelationQQID= 8855678
  --升1级
  update dbo.QQUser
  set dbo.QQUser.Level=dbo.QQUser.Level+1
  where dbo.QQUser.Level<6
  --负1级
  update dbo.QQUser
  set dbo.QQUser.Level=-1
  where DATEDIFF(DD,dbo.QQUser.LastLogTime,GETDATE())>365
  --好友多的 升一级
  update dbo.QQUser
  set Level=Level+1
  where QQID IN(
  
  select QQID 
  from dbo.Resation
  group by QQID 
  having COUNT(*)>3
  )
  
 --拉黑名单
 update dbo.Resation
 set dbo.Resation.RelationStatus=1 
 where QQID
 IN ( 
 select  dbo.Resation.QQID
 from dbo.Baselofo,dbo.Resation
 where  dbo.Baselofo.NickName='秋芙蓉' and
 dbo.Resation.RelationStatus=0 and dbo.Resation.QQID=54789625
)