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