13章 项目

--插入数据----------------------------------------------------------------------------INSERT QQUser(QQID,PassWord,LastLogTime,Online,Level)SELECT 54789625,'add512#&','2013-02-16 17:01:35',2,1 UNIONSELECT 88662753,'admin0219','2013-02-19 21:08:50',0,5 UNIONSELECT 8855678,'guest0221','2013-02-21 16:28:20',1,6--------------------------------------------------------------------------------------INSERT BaseInfo(QQID,NickName,Sex,Age,Province,City,Address,Phone)SELECT 54789625,'蝴蝶飞飞',1,16,'北京市','北京','亚运村','37547388157668' UNIONSELECT 88662753,'秋芙蓉',0,20,'河南省','南阳','方城博望','88715783657725' UNIONSELECT 8855678,'双眼皮の潴',1,38,'北京市','北京','双榆树东里','65794968876143'--------------------------------------------------------------------------------------INSERT Relation(QQID,RelationQQID,RelationStatus)SELECT 54789625,88662753,0 UNIONSELECT 88662753,8855678,1 UNIONSELECT 54789625,8855678,0--查询数据----------------------------------------------------------------------------------------------------------------SELECT R.RelationQQID AS 好友QQ号码,B.NickName AS 昵称,B.Age AS 年龄FROM Relation AS RINNER JOIN BaseInfo AS B ON (R.RelationQQID=B.QQID)WHERE R.RelationStatus=0 AND R.QQID = 54789625----------------------------------------------------------------------------------------------------------------SELECT B.*FROM QQUser AS UINNER JOIN BaseInfo AS B ON (U.QQID=B.QQID)WHERE U.Online = 0----------------------------------------------------------------------------------------------------------------SELECT B.*FROM QQUser AS UINNER JOIN BaseInfo AS B ON (U.QQID=B.QQID)WHERE U.Online = 0 AND B.Province = '北京市' AND Age >=18 AND Age <=45----------------------------------------------------------------------------------------------------------------SELECT *FROM BaseInfoWHERE NickName='小笨猪'----------------------------------------------------------------------------------------------------------------SELECT B.Province AS 省份,COUNT (*) AS 人数FROM Relation AS RINNER JOIN BaseInfo AS B ON (R.RelationQQID=B.QQID)WHERE R.QQID = 54789625 AND R.RelationStatus=0GROUP BY B.ProvinceORDER BY COUNT(*) DESC----------------------------------------------------------------------------------------------------------------SELECT U.QQID,U.LastLogTime,U.Level,B.NickName,B.AgeFROM QQUser AS UINNER JOIN BaseInfo AS B ON (U.QQID=B.QQID)WHERE DATEDIFF(dd,U.LastLogTime,GETDATE())>150ORDER BY DATEDIFF(dd,U.LastLogTime,GETDATE()) DESC----------------------------------------------------------------------------------------------------------------SELECT R.RelationQQID,B.NickName,B.Age,U.LevelFROM QQUser AS UINNER JOIN Relation AS R ON (R.RelationQQID=U.QQID)INNER JOIN BaseInfo AS B ON (R.RelationQQID=B.QQID)WHERE R.RelationStatus=0 AND R.QQID = 54789625 AND U.Level>1----------------------------------------------------------------------------------------------------------------SELECT R.RelationQQID,B.NickName,B.AgeFROM QQUser AS UINNER JOIN Relation AS R ON (R.RelationQQID=U.QQID)INNER JOIN BaseInfo AS B ON (R.RelationQQID=B.QQID)WHERE R.RelationStatus=0 AND R.QQID = 54789625 AND U.Online=1----------------------------------------------------------------------------------------------------------------SELECT QQID AS QQ号码,COUNT(*) AS 好友总数FROM RelationGROUP BY QQIDHAVING COUNT(*)>1----------------------------------------------------------------------------------------------------------------SELECT TOP 20 RelationQQID,COUNT(*) AS 被拉黑次数FROM RelationWHERE RelationStatus = 1 GROUP BY RelationQQIDORDER BY COUNT(*)--修改数据----------------------------------------------------------------------------------------------------------------UPDATE QQUser SET Online = 2WHERE QQID = 8855678----------------------------------------------------------------------------------------------------------------UPDATE BaseInfoSET NickName = '被淹死的鱼',Address = '解放中路6号院106室'WHERE QQID = 8855678----------------------------------------------------------------------------------------------------------------UPDATE RelationSET RelationStatus = 1WHERE QQID = 8855678 AND RelationQQID = 248624066----------------------------------------------------------------------------------------------------------------UPDATE QQUserSET Level=Level+1----------------------------------------------------------------------------------------------------------------UPDATE QQUserSET Level=-1WHERE DATEDIFF(dd,LastLogTime,GETDATE())>365----------------------------------------------------------------------------------------------------------------UPDATE QQUserSET Level=Level+1WHERE QQID IN (SELECT QQID FROM RelationGROUP BY QQIDHAVING COUNT(*)>20)----------------------------------------------------------------------------------------------------------------UPDATE RelationSET RelationStatus = 1WHERE RelationQQID IN(SELECT QQIDFROM BaseInfoWHERE NickName = '嘟嘟鱼') AND QQID = 286314 AND RelationStatus = 0--删除数据----------------------------------------------------------------------------------------------------------------DELETE FROM RelationWHERE QQID = 54789625 AND RelationStatus = 1----------------------------------------------------------------------------------------------------------------