此系统实现了火车票订票系统的部分功能

create database 陈念
go
use 陈念
go

create table trainticket
        (
           车次 varchar(10) primary key,
           始点站 varchar(8),
           终点站 varchar(8),
           列车类型 varchar(10),
           出发站 varchar(8),
           目的站 varchar(8),
           出发时间  Datetime,
           到达时间  Datetime,
           乘车时间  Datetime
        )
alter table trainticket alter 出发时间 time
alter table trainticket drop column 出发站
alter table trainticket drop column 目的站
create table traveller
       (
          身份证 varchar(20) primary key,
          姓名   varchar(8),
          性别   char(2),
          年龄   int,
          居住址 varchar(20),
          联系电话 varchar(15),
          职业   varchar(10)
       )
create rule age_rule
/*创建一个规则，将其绑定在traveller表上的年龄列上*/
as @年龄 between 0 and 100
sp_bindrule age_rule,'traveller.年龄' 

create table bookticket
       (
          身份证 varchar(20),
          车次 varchar(10),
          乘车日期 datetime,
          票数 int,
          primary key(身份证,车次,乘车日期),
          foreign key(身份证) REFERENCES traveller(身份证),
          foreign key(车次) REFERENCES   trainticket(车次)   
       )
alter table bookticket alter column 乘车日期 DATE
create table user_inf
       (
          用户名 varchar(20) primary key,
          密码 varchar(10),
          foreign key(用户名) references traveller(身份证) 
       )
create procedure zhuceinf
      @sfz varchar(20),
      @xm  varchar(8),
      @xb  char(2),
      @nl  int,
      @jz  varchar(20),
      @dh  varchar(15),
      @zy  varchar(10)
as
      begin
        insert into traveller
        values(@sfz,@xm,@xb,@nl,@jz,@dh,@zy)
      end
go

create procedure userinf
      @yhm varchar(20),
      @mm  varchar(10)
as
      begin
         insert into user_inf
         values(@yhm,@mm)
      end
go 

create procedure selecttrain
      @cc varchar(10) 
as
      begin
         select *
         from trainticket
         where 车次=@cc
      end 
go 

  

create procedure inserttrainticket
      @cc varchar(10),
      @start varchar(8),
      @over varchar(8),
      @trainstyle varchar(10),
      @starttime datetime,
      @arrivetime datetime,
      @taketime datetime
as
      begin
         insert into trainticket
         values(@cc,@start,@over,@trainstyle,@starttime,@arrivetime,@taketime) 
      end 
go

create procedure selectticket
/*此存储过程用于模糊查询,查询所订票的车次*/
      @cc varchar(10)
as
      begin
         select *
         from trainticket
         where 车次 like @cc+'%'
      end 
go

create procedure selectticket1
/*按站点查询车次的存储过程*/
@start varchar(8),
@arrive varchar(8)
as
  begin
    select *
    from trainticket
    where 始点站=@start and 终点站=@arrive
  end
go

create procedure book
/*订票信息的存储过程*/
@sfz varchar(20),
@cc varchar(10),
@ccrq datetime,
@ps int
as
  begin 
    insert into bookticket
    values(@sfz,@cc,@ccrq,@ps)
  end 
go

create procedure updatebook
@sfz1 varchar(20),
@cc1 varchar(10),
@ccrq1 datetime,
@ps1 int,
@sfz2 varchar(20),
@cc2 varchar(10),
@ccrq2 datetime
as
   begin
     update bookticket
     set 身份证=@sfz1,车次=@cc1,乘车日期=@ccrq1,票数=@ps1
     where 身份证=@sfz2 and 车次=@cc2 and 乘车日期=@ccrq2
   end 
go

if exists(select name from sysobjects where name='deluser' and type='tr')
   drop trigger deluser  /*判断deluser触发器是否存在，若存在则删除*/
go
create trigger deluser
/*当注销登录信息时，级联删除乘客信息和订票信息*/
on user_inf
for delete
as
  begin
    print '删除成功！'
    declare @user varchar(20)
    select @user=用户名 from deleted
    delete 
    from bookticket 
    where 身份证=@user
    delete 
    from traveller
    where 身份证=@user
  end
go 

create procedure zhuxiao
/*删除登录信息的存储过程*/
@sfz varchar(20)
as
  begin
    delete
    from user_inf
    where 用户名=@sfz
  end
go
      

create procedure seleuser1
as
  begin
    select traveller.身份证,count(bookticket.身份证)
    from bookticket,traveller
    where traveller.身份证=bookticket.身份证
    group by traveller.身份证
  end
go

create procedure seluser2
as
  begin
    select traveller.身份证,'0'
    from traveller 
    where 身份证 not in(select 身份证 from bookticket)
  end 
go    

create view bb_view(身份证,姓名,车次,乘车日期,票数)
/*创建视图，便于前台生成报表时进行多表操作*/
as
  select traveller.身份证,traveller.姓名,车次,乘车日期,票数
  from traveller,bookticket
  where traveller.身份证=bookticket.身份证 

create procedure breport
/*利用视图查询表里的数据*/
as
  begin
    select *
    from bb_view
  end 
go