建表
--建立主键 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 )
C#第13章
最新推荐文章于 2020-07-08 16:53:05 发布