C#第⼗⼆章项⽬案例：QQ数据库管理QQ数据库管理
--⽤户表
INSERT dbo.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'
SELECT *from dbo.QQUser
--基本信息表
INSERT dbo.Baselnfo(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'
SELECT *from dbo.Baselnfo
--关系表
INSERT dbo.Relation(QQID,RelationQQID,RelationStatus)
SELECT '54789625','88662753','0'UNION
SELECT '88662753','8855678','1'UNION
SELECT '54789625','8855678','0'
SELECT *from dbo.Relation
--⽤例6：查询数据
--查询当前在线⽤户的信息
SELECT *
FROM dbo.QQUser
WHERE Online=0
--查询北京市，年龄在18~45岁的在线⽤户的信息
SELECT *
业余党校学习心得FROM dbo.Baselnfo
WHERE Province='北京市' AND Age >=18 OR Age <=45
--查询秋芙蓉
SELECT *
FROM dbo.Baselnfo
WHERE NickName='秋芙蓉'
--查询QQ号码为54789625的⽤户的好友中每个省份的总⼈数，并且按总⼈数由⼤到⼩排序
SELECT *
FROM dbo.Relation,dbo.Baselnfo
WHERE (Relation.QQID=54789625 AND Relation.RelationStatus=0 AND Relation.RelationQQID=Baselnfo.QQID)
--查询⾄少有150天未登录QQ账号的⽤户信息
SELECT q.QQID,LastLogTime,Level,NickName,Age
FROM QQUser q,Baselnfo b
WHERE q.QQID=b.QQID and DATEDIFF(dd,LastLogTime,GETDATE())>150
ORDER BY LastLogTime DESC
--查询QQ号码为54789625的好友中等级为10级以上的‘⽉亮’级⽤户信息，
SELECT *
FROM dbo.QQUser
WHERE QQID='54789625' AND Level <=10
--查询QQ号码为54789625的好友中隐⾝的⽤户信息
个体工商注册SELECT *
FROM dbo.QQUser
WHERE QQID='54789625' AND Online=2
--查询好友超过20个的⽤户QQ号码及其好友总数
SELECT QQID ,COUNT(*)
FROM dbo.Baselnfo
GROUP BY QQID
HAVING COUNT(*)>=20
--为了分组查询信誉度，管理员需要查询被当作⿊名单⼈物次数排名前20的⽤户
SELECT TOP 20 QQID
FROM dbo.Relation
WHERE RelationStatus=1
--⽤例7：修改数据
--假设我的QQ和密码为8855678，今天我隐⾝登录
UPDATE dbo.QQUser set Online=2
郴州有什么好玩的地方WHERE QQID=8855678
SELECT*FROM dbo.QQUser
芝麻烧饼--假设我的QQ号码为8855678，修改我的昵称为“被淹死的鱼”，地址为“解放路6号院106室” UPDATE dbo.Baselnfo
SET NickName='被淹死的鱼',Address='解放路6号院106室'
WHERE QQID=8855678
SELECT * FROM dbo.Baselnfo
--假设我的QQ号码为8855678，将我的好友“248624066”拖进⿊名单
UPDATE dbo.Relation
SET RelationQQID=8855678,RelationStatus=1
WHERE QQID=54789625
SELECT * FROM dbo.Relation
--为了提⾼QQ⽤户的聊天积极性，把等级⼩于6级的⽤户等级都提升1个级别
UPDATE dbo.QQUser
SET Level=(Level+1)
WHERE Level<6
SELECT * FROM dbo.QQUser
-
-管理员将超过365天没有登录过的QQ锁定（将等级值设定为-1）
UPDATE dbo.QQUser
SET Level=-1
WHERE DATEDIFF (DD,LastLogTime,GETDATE())>365 AND Online=1
SELECT * FROM dbo.QQUser
--为了奖励⽤户，将好友数量超过20的⽤户等级提升1个级别
UPDATE dbo.QQUser SET Level =(Level+1)
WHERE QQID IN (SELECT QQID FROM dbo.Relation
WHERE RelationStatus=0
GROUP BY QQID
HAVING COUNT (RelationQQID)>20)
-
-把QQ号码为286314的⽤户的好友“嘟嘟鱼”拖进⿊名单中
UPDATE dbo.Relation SET RelationQQID=8855678,RelationStatus=0
网上订票放票时间WHERE QQID=54789625
UPDATE dbo.Relation SET RelationStatus=1
WHERE QQID IN (SELECT QQID FROM dbo.Baselnfo WHERE NickName ='蝴蝶飞飞') --⽤例⼋：删除数据
--⽤例⼋：删除数据
--把QQ号码为54789625的⽤户的⿊名单中的⽤户删除
DELETE dbo.Relation
WHERE QQID =54789625 AND RelationStatus=1
--QQ号码为622009019的⽤户多次在QQ中发布违法信息，造成了很坏的影响，因此管理员决定将其删除
--在关系表中删除⽤户622013019的信息
DELETE FROM dbo.Relation
WHERE QQID =622009019 OR RelationQQID=622013019
--在基本信息表中删除⽤户622013019的信息
DELETE FROM dbo.Baselnfo
WHERE QQID =622013019
--在⽤户表中删除⽤户622013019的信息
DELETE FROM dbo.QQUser
WHERE QQID =622013019
--管理员将超过1000天没有登录过的QQ删除
--查询超过1000天没有登录过的QQID
SELECT QQID
FROM dbo.QQUser
WHERE DATEDIFF (DAY ,LastLogTime,GETDATE ())>=1000
--删除dbo.Relation表中的数据
DELETE FROM dbo.Relation
WHERE QQID IN (SELECT QQID FROM dbo.QQUser WHERE DATEDIFF (DAY ,LastLogTime ,GETDATE ())>=1000) OR RelationQQID IN (SELECT QQID FROM dbo.QQUser WHERE DATEDIFF (DAY ,LastLogTime,GETDATE ())>=1000) --删除dbo.Baselnfo表中的数据
DELETE FROM dbo.Baselnfo
WHERE QQID IN (SELECT QQID FROM dbo.QQUser WHERE DATEDIFF (DAY ,LastLogTime,GETDATE ())>=1000)
--删除dbo.QQUser表中的数据
DELETE FROM dbo.QQUser
WHERE DATEDIFF(DAY,LastLogTime,GETDATE ())>=1000
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论