项目案例：QQ数据库管理

项目案例：QQ数据库管理

一、数据库表准备

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

二、各表的约束条件

1  QQ密码不得少于6位；  
  Len(password) >= 6
2 在线状态的值必须位0,1,2    0表示在线，1表示离线，2表示隐身
  Online in(0,1,2)
3 用户等级默认值为0
  ![在这里插入图片描述](https://img-blog.csdnimg.cn/20210302144415296.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW9jaGFvXzAxMDc=,size_16,color_FFFFFF,t_70)

4 性别允许为空值，但如果输入值就必须位0或1    0表示男，1表示女
  Sex is null or sex in(0,1)
5 年龄必须在1-100的整数
  Age between 1 and 100
6 用户关系只能是数字0,1     0表示好友，1表示黑名单人物
  RelationStatus in(0,1)

三、表间关系和添加测试数据

添加测试数据

USE QQBase
SELECT * FROM QQUser
--在线状态的值必须位,1,2    0表示在线，1表示离线，2表示隐身
--添加测试数据到QQUser表
insert into QQUser values(234,'222222','2021-01-21',1,3),(345,'333333','2021-03-01',2,5);
--添加测试数据到BaseInfo表
INSERT INTO BaseInfo values(123,'小张',0,18,'湖北','武汉','汉正街','131123'),
(234,'小李',0,20,'湖南','长沙','香港路','132234'),
(345,'小王',1,18,'湖北','武汉','江汉路','133345')
select * from BaseInfo
--添加测试数据到Relation表
insert into Relation values(123,345,1),(345,234,0);
--用户关系只能是数字0,1     0表示好友，1表示黑名单人物
select * from Relation

四、查询数据

--(1)查询QQ号码为123的用户的所有好友信息，包括好友QQ号码，昵称，年龄
(Relation关系表，BaseInfo表)
--(Relation关系表，BaseInfo表)  RelationStatus值0表示好友，表示黑名单人物
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)查询当前在线用户的信息 （QQUser表,BaseInfo表）
-- （QQUser表,BaseInfo表）Online的值0表示在线，表示离线，表示隐身
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 q.OnLine = 0 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号码为123，今天我隐身登录
（Online的值0表示在线，1表示离线，2表示隐身）
update QQUser set OnLine = 2,LastLogTime = GETDATE() where QQID = 123

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

-- (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的用户的黑名单中的用户删除。（0好友，1黑名单）
delete from Relation where QQID = 123 and RelationStatus = 1;

--(2) qq号码为345的用户多次在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