第十三章上机练习

INSERT QQuser
--查询数据
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
INSERT BaseInfo
select 54789625,'蝴蝶飞飞',1,16,'北京市','北京','亚运村','37547388157668' union
select 88662753,'秋芙蓉',0,20,'河南省','南阳','方城博望','88715783657725' union
select 8855678,'双眼皮の潴',1,38,'北京市','北京','双榆村东里','65794968876143'
INSERT Relation
select 54789625,88662753,0 union
select 88662753,8855678,1 union
select 54789625,8855678,0
select BaseInfo.QQID as 好友QQ号,NickName as 昵称,Age as 年龄 from BaseInfo,Relation
where BaseInfo.QQID=Relation.QQID and RelationStatus=0
select * from QQuser where  Online=0
select * from BaseInfo,QQuser
where BaseInfo.QQID =QQuser.QQID and Online=0 and City ='北京市' and Age>=18 and Age<=45
select * from BaseInfo
where NickName='秋芙蓉'
select BaseInfo.Province as 省份,COUNT (*)as 人数 from BaseInfo,Relation
where(Relation.QQID=54789625 and Relation.RelationStatus=0 and Relation.RelationQQID=BaseInfo.QQID)
group by Province
order by COUNT (*)desc
select a.QQID as QQ号码,a.LastLogTime as 最后一次登录时间,a.Level as 等级,b.NickName as 昵称,b.Age as 年龄
from dbo.QQUser as a,dbo.BaseInfo as b
where DATEDIFF(DD,LastLogTime,GETDATE())>150 
order by a.LastLogTime desc
select a.QQID as 好友QQ号码,b.NickName as 昵称,b.Age as 年龄,c.Level as 等级
from dbo.Relation as a,dbo.BaseInfo as b,dbo.QQUser as c
where a.RelationQQID=b.QQID and b.QQID=c.QQID and a.QQID='54789625' and c.Level>=10 and RelationStatus=1
select a.QQID as 好友QQ号码,b.NickName as 昵称,b.Age as 年龄,c.Online as 在线状态
 from dbo.Relation as a,dbo.BaseInfo as b,dbo.QQUser as c
 where a.RelationQQID=b.QQID and b.QQID=c.QQID and a.QQID='54789625' and c.Online=2 and RelationStatus=1
select COUNT(*) as 好友人数,QQID as 用户QQ号
from dbo.Relation 
group by QQID
having COUNT(*)>=2   
select top 20 COUNT(*) as 黑名单人物次数,RelationQQID as 黑名单用户
from dbo.Relation
where RelationStatus=1
group by RelationQQID;
having  COUNT(*)>=1
--修改数据
update dbo.QQUser set Online=2
where QQID=8855678
update dbo.BaseInfo set NickName='被淹死的鱼',Address='解放中路6号院106室'
where QQID=8855678
update dbo.Relation set RelationStatus=1
where QQID=54789625 and RelationQQID=88662753
update dbo.QQUser set Level=Level+1
where Level<6
update dbo.QQUser set Level=-1
where DATEDIFF(DD,LastLogTime,GETDATE())>365
update dbo.QQUser set Level=Level+1
where QQID in(select QQID from dbo.Relation group by QQID having COUNT(*)>1)
--修改数据的最后一个
----update dbo.Relation set RelationStatus=1
----where Relation.QQID in(select Relation.QQID from dbo.BaseInfo,dbo.Relation 
----where Relation.QQID=54789625 and RelationStatus=0 and NickName='嘟嘟鱼' 
----and dbo.BaseInfo.QQID=dbo.Relation.QQID)
--删除数据
delete from Relation 
where QQID=54789625 and RelationStatus=1
delete from QQuser
where QQID=622009019