第十三章上机练习

INSERT QQuser--查询数据select 54789625,'add512#&','2013-02-16 17:01:35',2,1 unionselect 88662753,'admin0219','2013-02-19 21:08:50',0,5 unionselect 8855678,'guest0221','2013-02-21 16:28:20',1,6INSERT BaseInfoselect 54789625,'蝴蝶飞飞',1,16,'北京市','北京','亚运村','37547388157668' unionselect 88662753,'秋芙蓉',0,20,'河南省','南阳','方城博望','88715783657725' unionselect 8855678,'双眼皮の潴',1,38,'北京市','北京','双榆村东里','65794968876143'INSERT Relationselect 54789625,88662753,0 unionselect 88662753,8855678,1 unionselect 54789625,8855678,0select BaseInfo.QQID as 好友QQ号,NickName as 昵称,Age as 年龄 from BaseInfo,Relationwhere BaseInfo.QQID=Relation.QQID and RelationStatus=0select * from QQuser where  Online=0select * from BaseInfo,QQuserwhere BaseInfo.QQID =QQuser.QQID and Online=0 and City ='北京市' and Age>=18 and Age<=45select * from BaseInfowhere NickName='秋芙蓉'select BaseInfo.Province as 省份,COUNT (*)as 人数 from BaseInfo,Relationwhere(Relation.QQID=54789625 and Relation.RelationStatus=0 and Relation.RelationQQID=BaseInfo.QQID)group by Provinceorder by COUNT (*)descselect a.QQID as QQ号码,a.LastLogTime as 最后一次登录时间,a.Level as 等级,b.NickName as 昵称,b.Age as 年龄from dbo.QQUser as a,dbo.BaseInfo as bwhere DATEDIFF(DD,LastLogTime,GETDATE())>150 order by a.LastLogTime descselect 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 cwhere a.RelationQQID=b.QQID and b.QQID=c.QQID and a.QQID='54789625' and c.Level>=10 and RelationStatus=1select 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=1select COUNT(*) as 好友人数,QQID as 用户QQ号from dbo.Relation group by QQIDhaving COUNT(*)>=2   select top 20 COUNT(*) as 黑名单人物次数,RelationQQID as 黑名单用户from dbo.Relationwhere RelationStatus=1group by RelationQQID;having  COUNT(*)>=1--修改数据update dbo.QQUser set Online=2where QQID=8855678update dbo.BaseInfo set NickName='被淹死的鱼',Address='解放中路6号院106室'where QQID=8855678update dbo.Relation set RelationStatus=1where QQID=54789625 and RelationQQID=88662753update dbo.QQUser set Level=Level+1where Level<6update dbo.QQUser set Level=-1where DATEDIFF(DD,LastLogTime,GETDATE())>365update dbo.QQUser set Level=Level+1where 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=1delete from QQuserwhere QQID=622009019