SQL第十三章

 INSERT INTO [QQ数据库].[dbo].[Baselnfo](QQID,NickName,Sex,Age,Province,City,Address,Phone) 
  VALUES(54789625,'蝴蝶飞飞',1,16,'北京市','北京','亚运村','37547388157668')
  INSERT INTO [QQ数据库].[dbo].[Baselnfo](QQID,NickName,Sex,Age,Province,City,Address,Phone) 
  VALUES(88662753,'秋芙蓉',0,20,'河南省','南阳','方城博望','88715783657725')
  INSERT INTO [QQ数据库].[dbo].[Baselnfo](QQID,NickName,Sex,Age,Province,City,Address,Phone) 
  VALUES(8855678,'双眼皮$猪',1,38,'北京市','北京','双榆树东里','65794968876143')
  --查询数据
  --查询QQ号码为88662753用户的所有好友信息
  SELECT R.RelationQQID AS 好友QQ号,B.NickName AS 昵称,B.Age AS 年龄
  FROM Relation AS R,[QQ数据库].[dbo].[Baselnfo] AS B
  WHERE R.QQID=88662753 AND R.RelationStatus=0 AND R.RelationQQID=B.QQID
  --查询当前在线用户信息
  SELECT Q.QQID AS QQ号,NickName  AS 昵称,Sex AS 性别,Age AS 年龄,Province AS 省份,City AS 城市
  FROM QQUser AS Q,[QQ数据库].[dbo].[Baselnfo] AS B
  WHERE Q.QQID=B.QQID AND Q.Online=0
  --查询北京市 年龄在18~45岁之间的在线用户信息
  SELECT Q.QQID AS QQ号,NickName  AS 昵称,Sex AS 性别,Age AS 年龄,Province AS 省份,City AS 城市
  FROM QQUser AS Q,[QQ数据库].[dbo].[Baselnfo] AS B
  WHERE Q.QQID=B.QQID AND Q.Online=0 AND (B.Age BETWEEN 18 AND 45) AND (B.Province LIKE '北京%' OR B.City LIKE '北京%')
  --查询昵称为'小笨猪'的用户信息
  SELECT * FROM [QQ数据库].[dbo].[Baselnfo] WHERE NickName='秋芙蓉'
  --查询QQ号码为54789625的用户好友中每个省份的总人数 并由大到小排序
  SELECT B.Province AS 省份,COUNT(*) AS 好友总人数
  FROM Relation AS R,[QQ数据库].[dbo].[Baselnfo] AS B
  WHERE( R.QQID=54789625 AND R.RelationStatus=0 AND R.RelationQQID=B.QQID)
  GROUP BY B.Province ORDER BY COUNT(*) DESC
  --查询至少150天未登陆QQ账号的用户信息 QQ号码 最后一次登录时间 等级 昵称 年龄 并时间降序
  SELECT u.QQID,u.LastLogTime,u.Level,b.NickName,b.Age FROM QQUser as u,[QQ数据库].[dbo].[Baselnfo] as  b 
  WHERE b.QQID=u.QQID AND DATEDIFF(DAY,LastLogTime,GETDATE())>150 
  ORDER BY LastLogTime DESC
  --查询QQ号码为54789625的好友中等级为10级以上的"月亮"用户
  SELECT R.RelationQQID AS 好友QQ号,B.NickName AS 昵称,B.Age AS 年龄,Q.Level AS 等级
  FROM Relation AS R INNER JOIN [QQ数据库].[dbo].[Baselnfo] AS B ON R.QQID=54789625 
  AND R.RelationStatus=0 AND R.RelationQQID=B.QQID INNER JOIN QQUser AS Q ON R.RelationQQID=Q.QQID AND Q.Level>=10 
  --查询QQ号码为54789625的好友中隐身的用户信息
  SELECT R.RelationQQID AS 好友QQ号,B.NickName AS 昵称,B.Age AS 年龄
  FROM Relation AS R INNER JOIN [QQ数据库].[dbo].[Baselnfo] AS B ON R.QQID=54789625 
  AND R.RelationStatus=0 AND R.RelationQQID=B.QQID INNER JOIN QQUser AS Q ON R.RelationQQID=Q.QQID AND Q.OnLine=2
  --查询好友超过20个的用户QQ号码及好友总数
  SELECT QQID AS QQ号,COUNT(*) AS 好友总数  FROM [QQ数据库].[dbo].[Relation] 
  WHERE RelationStatus=0 
  GROUP BY QQID 
  HAVING COUNT(*)>=20 
  --查看信誉度 管理员查询被当做黑名单人物排前20的用户
  SELECT TOP 20 (RelationQQID )AS QQ号,COUNT(*) AS 黑名单  FROM [QQ数据库].[dbo].[Relation] 
  WHERE RelationStatus=1 
  GROUP BY RelationQQID 
  ORDER BY COUNT(*) DESC
  --修改数据
  --假设我的QQ号码为8855678,今天我隐身
   UPDATE QQUser SET Online=2 WHERE QQID=8855678
  --假设我的QQ号码为8855678,修改昵称为"被淹死的鱼",地址"解放中路6号院106室"
  UPDATE [QQ数据库].[dbo].[Baselnfo] SET NickName='被淹死的鱼',Address='解放中路6号院106室' WHERE QQID=8855678
  --假设我的QQ号码为8855678,将我的好友248624066拖进黑名单
  UPDATE [QQ数据库].[dbo].[Relation] SET RelationStatus=1 WHERE QQID=8855678 AND RelationQQID=248624066
  --提高QQ用户的聊天积极性 等级小于6的用户提高1级
  UPDATE QQUser SET Level=level+1 WHERE level<=5 
  --管理员将超过365天没有登陆过QQ锁定
  UPDATE QQUser SET Level=-1 WHERE DATEDIFF(DAY,lastLogTime,GETDATE())>=365
  --奖励用户 好友数量超过20的用户等级提升1级
  UPDATE QQUser SET Level=Level+1 WHERE QQID IN(SELECT QQID FROM Relation WHERE RelationStatus=0 GROUP BY QQID HAVING COUNT(*)>=20)
  --QQ号码为286314的用户好友'嘟嘟鱼'拖进黑名单
  UPDATE Relation SET RelationStatus=1
  WHERE QQID=286314 
  AND RelationQQID IN(SELECT QQID FROM [QQ数据库].[dbo].[Baselnfo] WHERE NickName='嘟嘟鱼')
  AND  RelationStatus=0
  
  --删除数据
  --把QQ号码为54789625的用户黑名单中的用户信息
  DELETE FROM [QQ数据库].[dbo].[Relation] WHERE QQID=54789625 And RelationStatus=1
  --删除发布违法信息
  DELETE FROM [QQ数据库].[dbo].[Relation] WHERE QQID=622009019
  DELETE FROM [QQ数据库].[dbo].[QQuser] WHERE QQID=622009019
  DELETE FROM [QQ数据库].[dbo].[Baselnfo] WHERE QQID=622009019
  --1000天没有登录过的QQ删除
  SELECT QQID FROM QQUser WHERE DATEDIFF(DAY,LastLogTime,GETDATE())>=1000
  DELETE FROM Relation 
WHERE QQID IN(SELECT QQID FROM QQUser WHERE DATEDIFF(DAY,LastLogTime,GETDATE())>=1000)
 OR RelationQQID IN(SELECT QQID FROM QQUser WHERE DATEDIFF(DAY,LastLogTime,GETDATE())>=1000)
 SELECT * FROM [QQ数据库].[dbo].[Baselnfo] WHERE QQID IN(SELECT QQID FROM QQUser WHERE DATEDIFF(DAY,LastLogTime,GETDATE())>=1000)
  DELETE FROM QQUser WHERE DATEDIFF(DAY,LastLogTime,GETDATE())>=1000