C#第13章

建表
--建立主键
USE MyQQ
ALTER TABLE QQUser ADD CONSTRAINT PK_QQID PRIMARY KEY (QQID)
ALTER TABLE  BaseInfo  ADD CONSTRAINT PK_QQID1 PRIMARY KEY (QQID)
ALTER TABLE  Relation  ADD CONSTRAINT PK_QQID2 PRIMARY KEY (QQID)
--建立约束
ALTER TABLE QQUser ADD CONSTRAINT CK_PassWord CHECK (LEN(PassWord)>=6)
ALTER TABLE QQUser ADD CONSTRAINT CK_Online CHECK (Online=0 OR Online=1 OR Online=2)
ALTER TABLE  BaseInfo  ADD CONSTRAINT CK_Sex CHECK (Sex=0 OR Sex=1)
ALTER TABLE  BaseInfo  ADD CONSTRAINT CK_Age CHECK (Age>=0 AND Age<=100)
ALTER TABLE Relation  ADD CONSTRAINT CK_RelationStatus CHECK (RelationStatus=0 OR RelationStatus=1)
--建立关系
ALTER TABLE  BaseInfo  ADD CONSTRAINT FK_QQID FOREIGN KEY (QQID) REFERENCES QQUser(QQID)
ALTER TABLE  Relation  ADD CONSTRAINT FK_QQID1 FOREIGN KEY (QQID) REFERENCES QQUser(QQID)
ALTER TABLE  Relation  ADD CONSTRAINT FK_RelationQQID FOREIGN KEY (RelationQQID) REFERENCES QQUser(QQID)

ALTER TABLE  Relation  DROP CONSTRAINT FK_QQID1
ALTER TABLE  Relation  DROP CONSTRAINT PK_QQID2

--添加数据
insert into 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-01-21 16:28:20',1,6

insert into BaseInfo 
select 54789625,'蝴蝶飞飞',1,16,'北京市','北京','亚运村','37547388157668' union
select 88662753,'秋芙蓉',0,20,'河南省','南阳','方城博望','88715783657725' union 
select 8855678,'双眼皮&猪',1,38,'北京市','北京','双榆树东里','65794968876143'  

insert into Relation
select 54789625,88662753,0 union 
select 88662753,8855678,1 union
select 54789625,8855678,0 

insert into Relation
values(88662753,8855678,0)

--查询数据
select a.QQID as 好友QQ号 ,a.NickName as 昵称,a.Age as 年龄 from dbo.BaseInfo as a,dbo.Relation as b
where a.QQID=b.RelationQQID and a.QQID=88662753 and b.RelationStatus=0

select QQID as QQ号, NickName as 昵称, Sex as 性别 , Age as 年龄 , Province as 省份, City as 城市 from dbo.BaseInfo
where Province='北京' and Age between 15 and 45

select Province as 省份,COUNT(*) as 人数 from dbo.BaseInfo as a,dbo.Relation as b
where a.QQID=b.RelationQQID and b.QQID=54789625 and b.RelationStatus=0
group by  Province
order by COUNT(*) desc

select a.QQID as 好友QQ号 ,LastLogTime as 最后一次登录的时间,
NickName as 昵称,Age as 年龄 from dbo.BaseInfo as a, dbo.QQUser as b ,dbo.Relation as c
where a.QQID=b.QQID and a.QQID=c.RelationQQID and c.RelationStatus=0 and  DATEDIFF(DD,LastLogTime,GETDATE())>150 


select a.QQID as QQ号, a.NickName as 昵称,a.Age as 年龄 
from dbo.BaseInfo as a,dbo.Relation as b,dbo.QQUser as c
where   a.QQID=c.QQID and c.QQID=b.QQID
and a.QQID=88662753
and b.RelationStatus=0 
and c.Online=0
--查询好友大于20的qq号

select * from dbo.Relation



 --修改数据
 update  dbo.QQUser set Online=2
  where QQID=8855678
 
 update dbo.BaseInfo set NickName='被淹死的鱼',Address='解放中路6号院106室'
 
  update dbo.Relation set RelationStatus=1
  where QQID=8855678 and RelationQQID=248624066
  
  
  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(*)>3)
  
  UPDATE dbo.Relation SET RelationStatus=1
  WHERE Relation.QQID in(select Relation.QQID from dbo.BaseInfo,dbo.Relation
    where Relation.RelationStatus=0 AND BaseInfo.NickName='被淹死的鱼' 
    and Relation.QQID=286314
  )
 
  --删除数据
 delete from dbo.Relation 
 where QQID IN(SELECT QQID FROM dbo.QQUser  WHERE DATEDIFF(DD,LastLogTime,GETDATE())>1000) 
 OR
 RelationQQID IN(SELECT QQID FROM dbo.QQUser WHERE DATEDIFF(DD,LastLogTime,GETDATE())>1000)
 
 delete FROM dbo.BaseInfo
 WHERE QQID IN(SELECT QQID FROM dbo.QQUser  WHERE DATEDIFF(DD,LastLogTime,GETDATE())>1000) 
 
 delete FROM dbo.QQUser
 WHERE QQID IN(SELECT QQID FROM dbo.QQUser  WHERE DATEDIFF(DD,LastLogTime,GETDATE())>1000) 
 
 


CREATE  table QQUser 
(
QQID bigint not null,
PassWord varchar(20) not null,
LastLogTime datetime not null,
Online int not null,
Level int not null default(0)
)

CREATE TABLE BaseInfo (
QQID bigint not null,
NickName varchar(20) not null,
Sex int  default(1),
Age int default(0),
Province varchar(10) not null,
City varchar(10) not null,
Address varchar(50) default('地址不详'),
Phone char(20) not null
)

CREATE TABLE Relation(
QQID bigint not null,
RelationQQID bigint not null,
RelationStatus int not null
)