若有足够权限,本安装文件可直接执行,执行成功后将会得到完整的DriversSchool数据库。其中包括数据表,测试数据,以及常用的存储过程封装。

--建库
create database DriversSchool
go
use DriversSchool
--建表(约束)
--介绍信息表
create table IntroduceInfo
(
    IID int identity(1,1) primary key,  --编号
    Title varchar(200) not null,  --标题
    Content text  --内容
)
--信息媒介表(图片表)
create table IntroducePicInfo  --说实话这个名字很傻,但文档就是这样,他给钱了我又能说什么呢?
(
    PID int identity(1,1) primary key, --编号
    IntroduceID int references IntroduceInfo(IID) not null,  --信息表外键编号
    [PPath] varchar(200) not null,  --图片路径
    Pcontent varchar(200)  --内容
)
--车型信息表
create table CarTypeInfo
(
    CID int identity(1,1) primary key,  --编号
    [CName] varchar(100) not null,  --名称
    Price money not null  --价格
)
--报名申请表
create table ApplicationFormInfo --??? What?
(
    [UID] int identity(1,1) primary key,
    [UName] nvarchar(10) check(len([UName]) > 1),  --姓名
    UGender nchar(1) check(UGender in('男','女')) not null,  --性别
    UAge int not null,  --年龄
    --文档没说要检查有效性,甲方最大,嘻嘻
    CardID char(18) check(len(CardID) in(15,18)) not null,  --身份证号
    --验证身份证有效性是个相对复杂的过程,不应该放到数据库来做。
    TelePhone char(11) check(len(TelePhone) = 11) not null,  --电话号码
    [Address] nvarchar(200) not null,  --住址
    StudyCarType int references CarTypeInfo(CID) not null,  --学习车型
    Email varchar(200) check(Email like '%@%.%') not null,  --电子邮箱
    WorkUnit varchar(200) not null,  --工作单位
    Question text  --咨询问题
)

create table Admins  --后台管理员表,额外追加,肯定会用到
(
    AID int identity(1,1) primary key,
    [key] text,
    [Power] int
)
go
--测试值(编的我头皮发麻呀)
insert into IntroduceInfo(Title,Content)
select '简介','测试简介1' union
select '价格','小型车:¥100<br />中型车:¥300<br />大型车¥600'

insert into IntroducePicInfo(IntroduceID,PPath,Pcontent)
select 1,'https://images.com/2IKO75X','这是图床上的图片外链' union
select 2,'http://driversSchool.cn/imges/118.png','这是本地图片'

insert into CarTypeInfo(CName,Price)
select '小型车',100 union
select '中型车',300 union
select '大型车',600

insert into ApplicationFormInfo(UName,UGender,UAge,CardID,TelePhone,[Address],StudyCarType,Email,WorkUnit,Question)
select '张伟','男',24,'XXXXXXXXXXXXXXXXXX','XXXXXXXXXXX','XDALJFOEJLAJNDL',2,'EX@k1.COM','XXXXX',null union
select
'李丽','女',21,'XXXXXXXXXXXXXXXXXX','XXXXXXXXXXX','XDALJFOEJLAJNDL',1,'vVv@xuw.COM','XXXXX',null
go
--业务函数
---增 部分
create proc AddIntroduce
(
    @title varchar(200),
    @Content text
)    
    as
    insert into IntroduceInfo(Title,Content) values(@title,@Content)
go
create proc AddPic
(
    @IntroduceID int,
    @Path varchar(200),
    @content varchar(200) = null
)
    as
    insert into IntroducePicInfo(IntroduceID,PPath,Pcontent)
    values(@IntroduceID,@Path,@content)
go
create proc AddCarType
(
    @name varchar(100),
    @price money
)
    as
    insert into CarTypeInfo(CName,Price) values(@name,@price)
go
create proc AddUser
(
    @name nvarchar(10),
    @gender nchar(1),
    @age int,
    @CardID varchar(18),
    @Phone char(11),
    @Address varchar(200),
    @CarType int = 1,
    @Email varchar(200),
    @work varchar(200),
    @Question text = null
)
    as
    insert into ApplicationFormInfo(
    UName,
    UGender,
    UAge,
    CardID,
    TelePhone,
    [Address],
    StudyCarType,
    Email,
    WorkUnit,
    Question
    )
    values(
    @name,
    @gender,
    @age,
    @CardID,
    @Phone,
    @Address,
    @CarType,
    @Email,
    @work,
    @Question
    )
go
---删 部分
create proc DelIntroduce
(
    @ID int
)
    as
    delete from IntroduceInfo where IID = @ID
go
create proc DelPic
(
    @ID int
)
    as
    delete from IntroducePicInfo where PID = @ID
go
create proc DelCarType
(
    @ID int
)
    as
    delete from CarTypeInfo where CID = @ID
go
create proc DelUser
(
    @ID int
)
    as
    delete from ApplicationFormInfo where [UID] = @ID
go
---改 部分
create proc UpIntroduce
(
    @ID int,
    @title varchar(200),
    @Content text
)    
    as
    update IntroduceInfo set Title = @title,Content = @Content where IID = @ID
go
create proc UpPic
(
    @ID int,
    @IntroduceID int,
    @Path varchar(200),
    @content varchar(200) = null
)
    as
    update IntroducePicInfo set
    IntroduceID = @IntroduceID,
    PPath = @Path,
    Pcontent = @content
    where
    PID = @ID
go
create proc UpCarType
(
    @ID int,
    @name varchar(100),
    @price money
)
    as
    update CarTypeInfo set
    CName = @name,
    Price = @price
    where
    CID = @ID
go
create proc UpUser
(
    @ID int,
    @name nvarchar(10),
    @gender nchar(1),
    @age int,
    @CardID varchar(18),
    @Phone char(11),
    @Address varchar(200),
    @CarType int = 1,
    @Email varchar(200),
    @work varchar(200),
    @Question text = null
)
    as
    update ApplicationFormInfo set
    UName = @name,
    UGender = @gender,
    UAge = @age,
    CardID = @CardID,
    TelePhone = @Phone,
    [Address] = @Address,
    StudyCarType = @CarType,
    Email = @Email,
    WorkUnit = @work,
    Question = @Question
    where
    [UID] = @ID
go

---查 部分
create proc getIntroduce
(
    @ID int
)
    as
    select IID,Title,Content from IntroduceInfo where IID = @ID
go
create proc getPic
(
    @ID int
)
    as
    select PID,IntroduceID,PPath,Pcontent from IntroducePicInfo where PID = @ID
go
create proc getCarType
(
    @ID int
)
    as
    select CID,CName,Price from CarTypeInfo where CID = @ID
go
create proc getUser
(
    @ID int
)
    as
    select 
    [UID]
    UName,
    UGender,
    UAge,
    CardID,
    TelePhone,
    [Address],
    StudyCarType,
    Email,
    WorkUnit,
    Question
    from ApplicationFormInfo where [UID] = @ID


若出现安装失败的情况,请依次检查:
1.确认当前登录账号拥有足够权限
2.确认环境中没有同名数据库
3.若无法解决,请联系本项目的技术实施人员。

笔记:

SQL中字段名往往会以表的缩写作为前缀(例如User Info表的主键叫UID),是为了能在联接查询中不因为名字重复而做的区分
一个表代表一堆对象(类),这个表中的一行数据代表一个对象


后续会完成ASP版的DriversSchool项目。(尽量)完全对标行业标准

Last modification:April 27th, 2020 at 12:28 pm
如果觉得我的文章对你有用,请随意赞赏