黑马程序员-创建sql数据库并初始化

 

                                                            ---------- android培训  java培训   期待与您交流！------------

use master

go
      --如果myschool这个数据库存在就删除它
if exists(select * from sysdatabases where name = 'myschool')
drop database myschool
go
  --  创建数据库 里面包含了必备的信息
  create database myschool
on
(   --数据库名称
    name = 'myschool',                

    --文件名称
    filename = 'e:\myschool.mdf',

    --初始化大小
    size = 3mb,

    --不能超过10兆
    maxsize = 10mb,

    --空间不足时以3%的量增长
    filegrowth = 3%
)

  --创建日志文件，用来记录数据库操作，便于因不小心操作至数据损坏后还原
log on
(
     --日志名称，
    name = 'myschool_log',

    --日志在硬盘上的文件名
    filename = 'e:\myschool_log.ldf',

    --初始化大小
    size = 1mb,

    --最大不能超过3兆
    maxsize = 3mb,

    --以百分之三的量增长
    filegrowth = 3%
)
go

--进入myschool数据库
use myschool
go
--如果数据库包函了grade表，那么就删除它
if exists(select * from sysobjects where name = 'grade')
drop table grade
go

--创建grade表
create table grade
(
    gradeid int identity(1,1) not null primary key,
    gradename nvarchar(10) not null ,
)
go
--如果数据库中包含student表，那么就删除
if exists(select * from sysobjects where name = 'student')
drop table student
go

--创建student表
create table student
(
    id int identity(1,1) not null primary key,
    name  nvarchar(50) not null,
    pwd nvarchar(50) null default('123456'),
    sex char(2) not null,
    grade int not null foreign key REFERENCES grade(gradeid),
    phone nvarchar(50) null,
    address nvarchar(50)  null,
    email nvarchar(50) null,
    rqtime datetime null
)
go
if exists(select * from sysobjects where name ='subject')
drop table subject
go
create table subject
(
    sbid int identity(1,1) not null primary key,
    sbname nvarchar(50) not null,
    gradeid int not null foreign key REFERENCES grade(gradeid)
)
go
if exists(select *from sysobjects where name = 'result')
drop table result 
go
create table result
(
    resultid int identity(1,1) not null primary key,
    id int not null foreign key REFERENCES student(id),
    sbid int not null foreign key REFERENCES subject(sbid),
    studentresult int not null,
    time datetime not null
)
go 
if exists(select * from sysobjects where name = 'admin')
drop table admin
go
create table admin
(
    loginname nvarchar(10) not null,
    loginpwd nvarchar(10) not null
)
go

--给admin表赋值
insert into admin values('admin','00000')



--给insert表赋值
insert into grade 
select 's1' union
select 's2' union
select 's3' union
select 'd1' union
select 'd2' 

--给student表赋值
insert into student(name,sex,grade,phone,address,email,rqtime)
select '范德萨','男','1','124335','西安','463544354@','1987/9/8' union
select '更好的','女','3','54354','北京','4665644354@','1988/4/2' union
select '吐谷浑','男','5','675656','青岛','4635443544@','1985/10/9' union
select '而我分','男','3','33553','兰州','4657684354@','1983/5/7' union
select '恒久远','女','2','12787','成都','463547674@','1990/12/6' union
select '士大夫','男','4','535322','昆明','64353354@','1986/2/19' union
select '感叹号','男','2','165463','广州','463545454@','1986/7/15' union
select '镜花缘','女','3','654533','长沙','463543354@','1985/3/2' union
select '四分卫','男','1','165767','乌鲁木齐','4635464454@','1985/3/10' union
select '红樱桃','女','2','656334','沈阳','46356446354@','1989/2/5' 

go

--给 subject 表赋值
insert into subject (sbname,gradeid)
select 'java','1' union
select 'c#','3' union
select 'Sql server','2' union
select 'html','4' union
select 'vb','2' union
select 'accp','5' 

go

--给result表赋值
insert into result(id,sbid,studentresult,time)
select '2','3','67' ,'2012/5/6' union
select '5','2','98' ,'2012/5/7' union
select '2','3','81' ,'2012/5/6' union
select '5','2','85' ,'2012/5/6' union
select '8','3','78' ,'2012/5/7' union
select '8','3','91' ,'2012/5/6' 

                                                       ---------- android培训  java培训    期待与您交流！------------
                                                                              详情请点击：http://edu.csdn.net/