创建SQLQuery医院挂号数据库、数据表

USE mastergoif exists(select * from sysdatabases where name='hospital')drop database hospitalGOcreate database hospitalon primary(name='hospital_data',filename='G:\project\hospital_data.mdf', size=10mb,maxsize=100mb,filegrowth=10%)log on(name='hospital_log',filename='G:\project\hospital_log.ldf',size=5mb,filegrowth=1MB) Gouse hospitalgoif exists(select * from sysobjects where name='doctor')drop table doctorgo--医师表create table doctor (doctorid int not null,              --医师IDname varchar(32) not null,          --医师姓名[description] varchar(32) not null, --医师简介zhicheng varchar(32) not null,      --医师职称memo varchar(200) null              --备注)goUSE hospitalgoif exists(select * from sysobjects where name='user')drop table [user]go--用户表create table [user](userid int not null,                   --用户IDname varchar(32) not null,             --用户姓名loginName  varchar(16) not null,       --登录名称loginpwd varchar(32) not null,         --登录密码department varchar(32) not null,       --所在院系schoolNumber varchar(32) not null,     --学号Sex char(2) not null,                  --性别[address] varchar(32) null,            --所在公寓phone varchar(32) null,                --联系电话email varchar(32) null,                --电子邮箱doctorid int not null,                 --主治医师time smalldatetime not null,           --预约时间memo varchar(200) null                 --备注)goif exists(select * from sysobjects where name='administrator')drop table administratorgo--管理员表create table administrator(administratorid int not null,        --管理员IDloginName varchar(16) not null,      --登录账号loginpwd varchar(32) not null        --登录密码)goif exists(select * from sysobjects where name='[time]')drop table [time]go --时刻表create table [time](timeid int not null,           --时刻IDtime smalldatetime not null    --预约时间)goif exists(select * from sysobjects where name='department')drop table departmentgo--科室表create table department (departmentid int not null,            --科室IDname varchar(32) not null,            -- 科室名称[description] varchar(32) not null,   --科室简介doctorid int not null,                --医师IDmemo varchar(200) null                --备注)go/*********************************************************//***为医师表创建约束***/--创建主键约束use hospitalgoalter table doctoradd constraint pk_doctor primary key(doctorid)go/*********************************************************//***为用户表创建约束***/--创建主键约束use hospitalgoalter table [user]add constraint pk_user primary key(userid)go--创建唯一约束alter table [user]add constraint UQ_id unique (userid)goalter table [user]add constraint UQ_loginName1 unique (loginName)go--创建用户表字段Sex 设置check约束(检查约束)alter table [user]add constraint ck_usercheck(Sex between '男' and '女') go--创建用户表外键约束alter table [user] with check add constraint fk_doctorl foreign key(doctorid)references doctor (doctorid)go/*********************************************************//***为时刻表创建约束***/--创建主键约束use hospitalgoalter table  [time]add constraint pk_time primary key(time)go--创建时刻表外键约束alter table [user] with check add constraint fk_time foreign key(time)references time (time)go/*********************************************************//***为管理员表创建约束***/--创建主键约束use hospitalgoalter table administratoradd constraint pk_administrator primary key(administratorid)go--创建唯一约束alter table administratoradd constraint UQ_loginName unique (loginName)go/*********************************************************//***为科室表创建约束***/--创建主键约束use hospitalgoalter table departmentadd constraint pk_department primary key(departmentid)go--创建科室表外键约束alter table department with check add constraint fk_doctor foreign key(doctorid)references doctor (doctorid)go