三个表：（1）QQUser用户表、（2）BaseInfo基本信息表、（3）Relation关系表

1 QQ密码不得少于6位； 2 在线状态的值必须位0,1,2 0表示在线，1表示离线，2表示隐身 3 用户等级默认值为0 4 性别允许为空值，但如果输入值就必须位0或1 0表示男，1表示女 5 年龄必须在1-100的整数 6 用户关系只能是数字0,1 0表示好友，1表示黑名单人物 三、表间关系

(1)查询QQ号码为的用户的所有好友信息，包括QQ号码，昵称，年龄 SELECT r.RelationQQID as 好友QQ号,b.NickName as 昵称,b.Age as 年龄 from Relation as r inner join BaseInfo as b on b.QQID = r.QQID and r.QQID = 123 where r.RelationStatus = 0

–(2)查询当前在线用户的信息 select b.QQID as QQ号,NickName AS 昵称,Sex as 性别,age as 年龄,Province as 省份,City as 城市 from BaseInfo as b,QQUser as q where q.OnLine = 0 and q.QQID = b.QQID 或者 select q.QQID AS qq号,b.NickName as 昵称,b.Address as 地址 from QQUser as q inner join BaseInfo as b on q.QQID = b.QQID and q.OnLine = 0

–(3)查询武汉市、年龄在-25岁的在线用户的信息。(信息表baseinfo、用户表user) select q.QQID as QQ号,NickName AS 昵称,Sex as 性别,age as 年龄,Province as 省份,City as 城市 from BaseInfo as b,QQUser as q where q.QQID = b.QQID and b.City = ‘武汉’ and b.Age between 18 and 25

–(4)查询昵称为’小张’的用户信息 select * from BaseInfo where NickName = ‘小张’

–(5)查询qq号码为的用户好友中每个省份的总人数，人数按大到小的排序。 select b.Province as 省份,COUNT() as 人数 from BaseInfo as b,Relation as r where b.QQID = 123 and r.RelationQQID = b.QQID group by b.Province ORDER BY COUNT() DESC

–(6)查询至少有10天未登录QQ账号的用户信息，包括QQ号码，最后一次登录时间，登记，昵称和年龄，并按时间的降序排列。 select q.QQID,q.LastLogTime as 上次登录时间,b.NickName as 昵称 ,b.age as 年龄 FROM QQUser AS q,BaseInfo as b where q.QQID = b.QQID and datediff(DD,q.LastLogTime,GETDATE())>= 10 order by q.LastLogTime desc

–(7)查询qq号码为123的好友中等级为1级以上的月老级用户信息。 select r.RelationQQID as 好友QQ号,b.NickName as 昵称,q.Level as 等级 from Relation as r inner join BaseInfo as b on r.QQID = 123 and r.RelationStatus = 0 and r.QQID = b.QQID inner join QQUser as q on r.RelationQQID = q.QQID and q.Level >=1

–(8)查询QQ号码为的好友中隐身的用户信息。 select r.RelationQQID as QQ号,b.NickName as 昵称,b.Age as 年龄 from Relation as r inner join BaseInfo as b on r.QQID = 123 and r.RelationStatus = 0 AND b.QQID = r.RelationQQID inner join QQUser as q on q.QQID = r.RelationQQID and q.OnLine = 2

–(9)查询好友超过个的用户QQ号码及其好友总数 select QQID as QQ号,COUNT() as 好友数量 from Relation where RelationStatus = 0 group by QQID having COUNT() >=1

–(10)查询被当作黑名单人物次数排名前的用户 select top 2 RelationQQID,COUNT() as 黑名单次数 from Relation where RelationStatus = 1 group by RelationQQID order by COUNT() desc

(1)假设我的QQ号码为，今天我隐身登录（在线，离线，隐身） update QQUser set OnLine = 2,LastLogTime = GETDATE() where QQID = 123

–(2)假设我的qq号码为，修改我的昵称为"被淹死的鱼"，地址为"解放中路号院室" update BaseInfo set NickName = ‘被淹死的鱼’,Address = ‘解放中路号院室’

– (3)假设我的QQ号为，将我的好友拖进黑名单（好友，黑名单人物） update Relation set RelationStatus = 1 where QQID = 123 and RelationQQID = 234;

– (4) 为了提高QQ用户聊天的积极性，把等级小于2级的用户等级都提升1个级别。 update QQUser set Level = Level +1 where Level < 2

– (5)管理员将超过天没有登录过的QQ锁定,将等级值设置为-1 update QQUser set Level = -1 where DATEDIFF(DD,LastLogTime,GETDATE())> 30

– (6) 为了奖励用户,将好友数量超过的用户等级提升个级别 update QQUser set Level = Level + 1 where QQID in (select QQID from Relation where RelationStatus = 0 group by QQID having COUNT(*) > 1)

– (7)把QQ号码为234的用户的好友"被淹死的鱼"拖进黑名单 update Relation set RelationStatus = 1 where QQID = 234 and RelationQQID in(select QQID from BaseInfo where NickName = ‘被淹死的鱼’) and RelationStatus = 0

（1）把QQ号为123的用户的黑名单中的用户删除。 delete from Relation where QQID = 123 and RelationStatus = 1;

–(2) qq号码为的用户多次在QQ中发布违法信息，造成了很坏的影响，因此管理员将其删除。 – 删除关系表中的信息 delete from Relation where QQID = 345 or RelationQQID = 345; – 信息表中删除 delete from BaseInfo where QQID = 345 –用户表中删除 delete from QQUser where QQID = 345

– (3)管理员将超过天没有登录过的QQ删除 – 思路先从用户表中查询超过天未登录过的qq号 select QQID from QQUser where DATEDIFF(DD,LastLogTime,GETDATE())>30 –3.1 删除relation表中的数据 delete from Relation where QQID in(select QQID from QQUser where DATEDIFF(DD,LastLogTime,GETDATE())>30 ) OR RelationQQID IN(select QQID from QQUser where DATEDIFF(DD,LastLogTime,GETDATE())>30 ) ----3.2 删除BaseInfo表中的数据 DELETE FROM BaseInfo WHERE QQID IN(SELECT QQID FROM QQUser WHERE DATEDIFF(DAY,LastLogTime,GETDATE())>=30) ----3.3 删除QQUser表中的数据 DELETE FROM QQUser WHERE DATEDIFF(DD,LastLogTime,GETDATE())>=30