--创建school库到指定的目录--

 --创建school库到指定的目录--
-- exec  xp_cmdshell 'mkdir f:\school_data\log'

if exists (select * from master.dbo.sysdatabases where name='school')
           drop database school
           go
--创建数据库--
create database school
on primary
(
name='school_db',
filename ='f:\cmdcomm',
size=50mb,
filegrowth=20%
)
log on
(
name='school_log',
filename='f:\cmdcomm',
size=25mb,
filegrowth=20%
)
go
  ----------------创建数据表开始-----
                --进入数据库--
                use school
                go
                /*创建学生表*/        
CREATE table student(
                     st_id nvarchar(9) not null,
                     st_nm nvarchar(8) not null,
                     st_sex nVarchar(2) null,
                     st_birth datetime null,
                     st_score int null,
                     st_date  datetime null,
                     st_from nchar (20) null,
                     st_dpid nVarChar (2) null,
                     st_mnt  tinyint null
                     )
          /*课程表*/
 Create table couse(
                     cs_id nVarchar(4) not null,
                     cs_nm nVarchar(20) not null,
                     cs_tm int           null,
                     cs_sc int           null
                     )
            /*选课表*/
 Create table slt_couse(
                         cs_id nVarchar(4) not null,
                         st_id nVarchar(9) not null,
                         score int             null,
                         sltdate datetime      null
                         )
                          /*创建院系信息表*/
   Create table dept( 
                         dp_id nVarchar(2)   not null,
                         dp_nm nVarchar(20)  not null,
                         dp_drt nVarchar(8) null,
                         dp_tel nVarchar (12) null
                         )
                        
                                  
        ----创建表约束---------
        /*设置学生ID主键*/
  alter table student
                      add
                         constraint   PK_st_id primary key(st_id)  
                         go
         /*设置同课程表主键*/
  alter table couse
                   add
                       constraint PK_cs_id primary key (cs_id)
                       go
         /*设置选课表的cs_id、st_id外键*/
   alter table slt_couse
                        add
                            constraint FK_cs_id foreign key (cs_id) references couse(cs_id) ON DELETE CASCADE,
                            constraint FK_st_id foreign key (st_id) references student(st_id) ON DELETE CASCADE
                            go

           -------------创建视图-------------
 
                /*学生视图*/
  create view V_student
                     as
                     (select st_id as 学生学号,
                             st_nm as 学生姓名,
                             st_sex as 学生性别,
                             st_birth as 出生日期,
                             st_score as 入学成绩,
                             st_date  as 入学日期,
                             st_from  as 学生来源,
                             st_dpid  as 所在系编号,
                             st_mnt   as 学生职务
                        
                             from student
                             )                
                            go
                  /*课程视图*/
  create view V_couse 
                      as
                      (select
                             cs_id as 课程编号,
                             cs_nm as 课程名称,
                             cs_tm as 课程学时,
                             cs_sc as 课程学分
                          from couse)  
                          go
                     /*选课表视图*/
   create view V_slt_couse
                          as
                          (select
                                 cs_id as 课程编号,
                                 st_id as 学生编号,
                                 score as 课程成绩,
                                 sltdate as 选课日期
                                 from slt_couse )                                                       
                                           go
                      /*院系信息视图*/
   create view V_dept
                     as
                     (select
                              dp_id as 系编号,
                              dp_nm as 院系名称,
                              dp_drt as 院系主任,
                              dp_tel as 联系电话
                         from dept) 
                         go                                     

  • blogger

版权所有:oldboy ---- 《--创建school库到指定的目录--
本文地址:http://www.bokeren.cc/post-130.html
除非注明,文章均为 《悠然生活的平淡博客-博客人linux运维之家》 原创转载请注明本文地址,谢谢。


相关推荐

你肿么看?

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。