SQL server数据库项目案例：QQ数据库管理(QQ用户表,基本信息表和关系表,QQ好友和黑名单人物设置)

一、数据库表准备

三个表：（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