13章 项目

--插入数据----------------------------------------------------------------------------
INSERT QQUser(QQID,PassWord,LastLogTime,Online,Level)
SELECT 54789625,'add512#&','2013-02-16 17:01:35',2,1 UNION
SELECT 88662753,'admin0219','2013-02-19 21:08:50',0,5 UNION
SELECT 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' UNION
SELECT 88662753,'秋芙蓉',0,20,'河南省','南阳','方城博望','88715783657725' UNION
SELECT 8855678,'双眼皮の潴',1,38,'北京市','北京','双榆树东里','65794968876143'
--------------------------------------------------------------------------------------
INSERT Relation(QQID,RelationQQID,RelationStatus)
SELECT 54789625,88662753,0 UNION
SELECT 88662753,8855678,1 UNION
SELECT 54789625,8855678,0



--查询数据----------------------------------------------------------------------------------------------------------------
SELECT R.RelationQQID AS 好友QQ号码,B.NickName AS 昵称,B.Age AS 年龄
FROM Relation AS R
INNER JOIN BaseInfo AS B ON (R.RelationQQID=B.QQID)
WHERE R.RelationStatus=0 AND R.QQID = 54789625
----------------------------------------------------------------------------------------------------------------
SELECT B.*
FROM QQUser AS U
INNER JOIN BaseInfo AS B ON (U.QQID=B.QQID)
WHERE U.Online = 0
----------------------------------------------------------------------------------------------------------------
SELECT B.*
FROM QQUser AS U
INNER JOIN BaseInfo AS B ON (U.QQID=B.QQID)
WHERE U.Online = 0 AND B.Province = '北京市' AND Age >=18 AND Age <=45
----------------------------------------------------------------------------------------------------------------
SELECT *
FROM BaseInfo
WHERE NickName='小笨猪'
----------------------------------------------------------------------------------------------------------------
SELECT B.Province AS 省份,COUNT (*) AS 人数
FROM Relation AS R
INNER JOIN BaseInfo AS B ON (R.RelationQQID=B.QQID)
WHERE R.QQID = 54789625 AND R.RelationStatus=0
GROUP BY B.Province
ORDER BY COUNT(*) DESC
----------------------------------------------------------------------------------------------------------------
SELECT U.QQID,U.LastLogTime,U.Level,B.NickName,B.Age
FROM QQUser AS U
INNER JOIN BaseInfo AS B ON (U.QQID=B.QQID)
WHERE DATEDIFF(dd,U.LastLogTime,GETDATE())>150
ORDER BY DATEDIFF(dd,U.LastLogTime,GETDATE()) DESC
----------------------------------------------------------------------------------------------------------------
SELECT R.RelationQQID,B.NickName,B.Age,U.Level
FROM QQUser AS U
INNER 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.Age
FROM QQUser AS U
INNER 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 Relation
GROUP BY QQID
HAVING COUNT(*)>1
----------------------------------------------------------------------------------------------------------------
SELECT TOP 20 RelationQQID,COUNT(*) AS 被拉黑次数
FROM Relation
WHERE RelationStatus = 1 
GROUP BY RelationQQID
ORDER BY COUNT(*)




--修改数据----------------------------------------------------------------------------------------------------------------
UPDATE QQUser 
SET Online = 2
WHERE QQID = 8855678
----------------------------------------------------------------------------------------------------------------
UPDATE BaseInfo
SET NickName = '被淹死的鱼',Address = '解放中路6号院106室'
WHERE QQID = 8855678
----------------------------------------------------------------------------------------------------------------
UPDATE Relation
SET RelationStatus = 1
WHERE QQID = 8855678 AND RelationQQID = 248624066
----------------------------------------------------------------------------------------------------------------
UPDATE QQUser
SET Level=Level+1
----------------------------------------------------------------------------------------------------------------
UPDATE QQUser
SET Level=-1
WHERE DATEDIFF(dd,LastLogTime,GETDATE())>365
----------------------------------------------------------------------------------------------------------------
UPDATE QQUser
SET Level=Level+1
WHERE QQID IN 
(SELECT QQID 
FROM Relation
GROUP BY QQID
HAVING COUNT(*)>20)
----------------------------------------------------------------------------------------------------------------
UPDATE Relation
SET RelationStatus = 1
WHERE RelationQQID IN
(SELECT QQID
FROM BaseInfo
WHERE NickName = '嘟嘟鱼') AND QQID = 286314 AND RelationStatus = 0




--删除数据----------------------------------------------------------------------------------------------------------------
DELETE FROM Relation
WHERE QQID = 54789625 AND RelationStatus = 1
----------------------------------------------------------------------------------------------------------------