ACCP S1 C#SQL第十三章项目

用例5

insert QQUser values(54789625,'add512#&','2013-02-16 17:01:35',2,1)
insert QQUser values(88662753,'admin0219','2013-02-19 21:08:50',0,5)
insert QQUser values(8855678,'guest0221','2013-02-21 16:28:20',1,6)
insert BaseInfo values(54789625,'蝴蝶飞飞',1,16,'北京市','北京','亚运村','37547388157668')
insert BaseInfo values(88662753,'秋芙蓉',0,20,'河南省','南阳','方城博望','88715783657725')
insert BaseInfo values(8855678,'双眼皮の潴',1,38,'北京市','北京','双榆树东里','65794968876143')
insert Relation values(54789625,88662753,0)
insert Relation values(88662753,8855678,1)
insert Relation values(54789625,8855678,0)

用例6

select r.RelationQQID as '好友QQ号',Nickname as '昵称', Age as '年龄' from BaseInfo b inner join Relation r on r.RelationQQID=b.QQID where r.QQID=88662753

select BaseInfo.QQID as 'QQ号', Nickname as '昵称', Sex as '性别', Age as '年龄', Province as '省份', City as '城市'  , Address as '地址', Phone as '电话' from QQUser,BaseInfo where Online=0

select BaseInfo.QQID as 'QQ号', Nickname as '昵称', Sex as '性别', Age as '年龄', Province as '省份', City as '城市', Address as '地址', Phone as '电话' from BaseInfo,QQUser where Online=0 and Province='北京市' and Age>=18 and Age<=45

select BaseInfo.QQID as 'QQ号', Nickname as '昵称', Sex as '性别', Age as '年龄', Province as '省份', City as '城市', Address as '地址', Phone as '电话' from BaseInfo where Nickname='双眼皮の潴'

select Province as '省份',COUNT(Province) as '好友总人数' from BaseInfo b,Relation r where(r.QQID=54789625 and r.RelationStatus=0 and r.RelationQQID=b.QQID) group by Province order by COUNT(Province) desc

select QQUser.QQID as 'QQ号',Lastlogtime as '最后一次登录时间', level as '等级',Nickname as '昵称',Age as '年龄' from QQUser,BaseInfo where DATEDIFF(DD,Lastlogtime,GETDATE())>150 order by DATEDIFF(DD,Lastlogtime,GETDATE()) desc

select RelationQQID as '好友QQ号',Nickname as '昵称',Age as '年龄',level as '等级' from QQUser q inner join BaseInfo b on b.QQID=q.QQID inner join Relation r on r.RelationQQID=q.QQID where level>3

select r.RelationQQID as '好友QQ号',Nickname as '昵称', Age as '年龄' from BaseInfo b inner join Relation r on r.RelationQQID=b.QQID inner join QQUser q on b.QQID=q.QQID where r.QQID=54789625 and Online=2

select QQID as 'QQ号码',COUNT(RelationQQID) as '好友总数' from Relation  group by QQID Having COUNT(RelationQQID)>1

select top 20 RelationQQID as 'QQ号',COUNT(RelationStatus) as '黑名单次数' from Relation group by RelationQQID Having COUNT(RelationStatus)=1

用例7

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+=1 where level<6

update QQUser set level=-1 where DATEDIFF(DD,Lastlogtime,GETDATE())>365

update QQUser set level+=1 where QQID in (SELECT QQID from Relation where RelationStatus=0 group by QQID having COUNT(*)>=2)
 
update Relation set RelationStatus=1 where QQID=286314 and RelationQQID in (SELECT QQID from BaseInfo where Nickname='嘟嘟鱼')and RelationStatus=0

用例8

delete from Relation where RelationQQID=54789625 and RelationStatus=1

delete from Relation where QQID=622009019
delete from BaseInfo where QQID=622009019
delete from QQUser where QQID=622009019