第十二章项目案例：QQ数据库管理

创建表结构。并且添加约束。

create table QQUser(
QQID bigint primary key,
PassWord varchar(50) check(len(PassWord)>=6) not null,
LastLogTime datetime not null,
Online int check(Online=‘1’ or Online=‘2’ or Online=‘0’),
Level int default(0)
)
go
create table BaseLnfo(
ID int identity(1,1) primary key,
QQID bigint not null,
NickName varchar(50) not null,
Sex int check(Sex=1 or Sex=0) not null,
Age int check(Age>0 and Age<100) not null,
Province varchar(50) not null,
City varchar(50) not null,
Address varchar(50) not null,
Phone char(30) not null
)
go
create table Relation(
QQID bigint not null,
RelationQQID bigint not null,
RelationStatus int check(RelationStatus=0 or RelationStatus=1) not null
)
go

建立关系。

alter table Relation
add constraint Fk_rel_use foreign key(QQID) references QQUser(QQID);
go
alter table Relation
add constraint Fk_rel_user foreign key(RelationQQID) references QQUser(QQID);
go
alter table Baselnfo
add constraint Fk_bas_use foreign key(QQID) references QQUser(QQID);
go

插入数据。

insert QQUser
(QQID,PassWord,LastLogTime,Online,Level)
select ‘54789625’,‘add512#&’,‘2013-2-16 17:01:35’,‘2’,‘1’ uni