/*
a) 創(chuàng)建數(shù)據(jù)庫
使用T-SQL創(chuàng)建數(shù)據(jù)庫feedback，要求：①一個(gè)主要文件（存放在第一個(gè)硬盤分區(qū)C:\project文件夾下），初始大小為10M,最大為200M,文件自動(dòng)增長率為15%
②一個(gè)次要數(shù)據(jù)文件(分別存放在第二個(gè)硬盤分區(qū)D上)
③一個(gè)日志文件(存放在第三個(gè)硬盤分區(qū)E：上) ④檢查數(shù)據(jù)庫是否已存在，如果存在則先刪除
*/
use master
if exists(select * from sysdatabases where name = 'feedback')
drop database feedback
go
create database feedback
on primary
(
name = 'feedback_data', --文件名稱
filename = 'C:\project\feedback_data.mdf', --文件存儲(chǔ)位置
size = 10mb, --初始大小
maxsize = 200mb, --最大文件大小
filegrowth = 15% --可以通過 百分比 % 指定自動(dòng)增長率 或 通過 數(shù)字+ mb指定
),
(
name = 'feedback_data1', --文件名稱
filename = 'C:\project\feedback_data.ndf' --文件存儲(chǔ)位置
)
log on
(
name = 'feedback_log', --文件名稱
filename = 'C:\project\feedback_data.ldf' --文件存儲(chǔ)位置
)
go
-------------------------------------------建表-----------------------------------------------------
USE feedback
GO
/*新建 usertype 表*/
if exists(select * from sysobjects where name = 'usertype')
drop table usertype
GO
create table usertype
(
usertypeid INT IDENTITY (1, 1) NOT NULL ,
utypename VARCHAR (20) NOT NULL
)
GO
/*新建 methodtype 表*/
if exists(select * from sysobjects where name = 'methodtype')
drop table methodtype
GO
create table methodtype
(
methodtypeid INT IDENTITY (1, 1) NOT NULL ,
typename VARCHAR (20) NOT NULL,
description VARCHAR (100) NULL
)
GO
/*新建 item 表*/
if exists(select * from sysobjects where name = 'item')
drop table item
GO
create table item
(
itemid INT IDENTITY (1, 1) NOT NULL ,
itemname VARCHAR (60) NOT NULL,
methodtypeid INT NOT NULL,
usertypeid INT NOT NULL
)
GO
/*新建 template 表*/
if exists(select * from sysobjects where name = 'template')
drop table template
GO
create table template
(
templateid INT IDENTITY (1, 1) NOT NULL ,
templatename VARCHAR (30) NOT NULL,
status INT NOT NULL,
usertypeid INT NOT NULL,
usecount INT
)
GO
/*新建 templateanditem 表*/
if exists(select * from sysobjects where name = 'templateanditem')
drop table templateanditem
GO
create table templateanditem
(
id INT IDENTITY (1, 1) NOT NULL,
templateid INT NOT NULL,
itemid INT NOT NULL
)
GO
/*新建 classtype 表*/
if exists(select * from sysobjects where name = 'classtype')
drop table classtype
GO
create table classtype
(
ctypeid INT IDENTITY (1, 1) NOT NULL,
ctypename VARCHAR (20) NOT NULL
)
GO
/*新建 classinfo 表*/
if exists(select * from sysobjects where name = 'classinfo')
drop table classinfo
GO
create table classinfo
(
classid INT IDENTITY (1, 1) NOT NULL,
classname VARCHAR (30) NOT NULL,
startdate DATETIME NOT NULL,
status INT NOT NULL,
ctypeid INT NOT NULL
)
GO
/*新建 userinfo 表*/
if exists(select * from sysobjects where name = 'userinfo')
drop table userinfo
GO
create table userinfo
(
userid INT IDENTITY (1, 1) NOT NULL,
username VARCHAR (20) NOT NULL,
usertypeid INT NOT NULL
)
GO
/*新建 courseinfo 表*/
if exists(select * from sysobjects where name = 'courseinfo')
drop table courseinfo
GO
create table courseinfo
(
courseid INT IDENTITY (1, 1) NOT NULL,
coursename VARCHAR (30) NOT NULL
)
GO
/*新建 activeinfo 表*/
if exists(select * from sysobjects where name = 'activeinfo')
drop table activeinfo
GO
create table activeinfo
(
activeid INT IDENTITY (1, 1) NOT NULL,
activename VARCHAR (50) NOT NULL,
activedate DATETIME NOT NULL,
usertypeid INT NOT NULL,
userid INT NOT NULL,
courseid INT NULL,
templateid INT NOT NULL,
status INT NOT NULL
)
GO
/*新建 activeandclass 表*/
if exists(select * from sysobjects where name = 'activeandclass')
drop table activeandclass
GO
create table activeandclass
(
acid INT IDENTITY (1, 1) NOT NULL,
activeid INT NOT NULL,
classid INT NOT NULL,
useramount INT NOT NULL,
status INT NOT NULL,
total INT NOT NULL,
avg decimal(18, 2) NOT NULL
)
GO
/*新建 result 表*/
if exists(select * from sysobjects where name = 'result')
drop table result
GO
create table result
(
resultid INT IDENTITY (1, 1) NOT NULL,
ip VARCHAR (15) NOT NULL,
acid INT NOT NULL,
itemid INT NOT NULL,
userresult INT NOT NULL,
answer VARCHAR (500) NULL
)
GO
/*新建 score 表*/
if exists(select * from sysobjects where name = 'score')
drop table score
GO
create table score
(
scoreid INT IDENTITY (1, 1) NOT NULL,
acid INT NOT NULL,
itemid INT NOT NULL,
total INT NOT NULL,
avg decimal(18, 2) NOT NULL,
numexcellent INT NOT NULL,
numpoorest INT NOT NULL
)
GO
----------------------------------------------------------------------------------------------------
/*usertype表約束*/
alter table usertype add constraint PK_usertypeid primary key (usertypeid) --主鍵約束
alter table usertype add constraint UQ_utypename unique (utypename) --唯一約束
/* methodtype 表約束*/
alter table methodtype add constraint PK_methodtypeid primary key (methodtypeid) --主鍵約束
alter table methodtype add constraint UQ_typename unique (typename) --唯一約束
/* item 表約束*/
alter table item add constraint PK_itemid primary key (itemid) --主鍵約束
alter table item add constraint UQ_itemname unique (itemname) --唯一約束
alter table item add constraint FK_item_methodtypeid foreign key (methodtypeid) references methodtype(methodtypeid) --外鍵約束
alter table item add constraint FK_item_usertypeid foreign key (usertypeid) references usertype(usertypeid) --外鍵約束
/* template 表約束*/
alter table template add constraint PK_templateid primary key (templateid) --主鍵約束
alter table template add constraint UQ_templatename unique (templatename) --唯一約束
alter table template add constraint FK_template_usertypeid foreign key (usertypeid) references usertype(usertypeid) --外鍵約束
alter table template add constraint DF_status default(0) for status --默認(rèn)
alter table template add constraint DF_usecount default(0) for usecount --默認(rèn)
/* templateanditem 表約束*/
alter table templateanditem add constraint PK_id primary key (id) --主鍵約束
alter table templateanditem add constraint FK_templateanditem_itemid foreign key (itemid) references item(itemid) --外鍵約束
alter table templateanditem add constraint FK_templateanditem_templateid foreign key (templateid) references template(templateid) --外鍵約束
alter table templateanditem add constraint UQ_itemid_templateid unique (itemid, templateid) --唯一約束
/* classtype 表約束*/
alter table classtype add constraint PK_ctypeid primary key (ctypeid) --主鍵約束
alter table classtype add constraint UQ_ctypename unique (ctypename) --唯一約束
/* classinfo 表約束*/
alter table classinfo add constraint PK_classid primary key (classid) --主鍵約束
alter table classinfo add constraint UQ_classname unique (classname) --唯一約束
alter table classinfo add constraint DF_startdate default(getDate()) for startdate --默認(rèn)
alter table classinfo add constraint DF_classinfo_status default(0) for status --默認(rèn)
alter table classinfo add constraint FK_classinfo_ctypeid foreign key (ctypeid) references classtype(ctypeid) --外鍵約束
/* userinfo 表約束*/
alter table userinfo add constraint PK_userid primary key (userid) --主鍵約束
alter table userinfo add constraint UQ_username unique (username) --唯一約束
alter table userinfo add constraint FK_userinfo_usertypeid foreign key (usertypeid) references usertype(usertypeid) --外鍵約束
/* courseinfo 表約束*/
alter table courseinfo add constraint PK_courseid primary key (courseid) --主鍵約束
alter table courseinfo add constraint UQ_coursename unique (coursename) --唯一約束
/* activeinfo 表約束*/
alter table activeinfo add constraint PK_activeid primary key (activeid) --主鍵約束
alter table activeinfo add constraint UQ_activename unique (activename) --唯一約束
alter table activeinfo add constraint DF_activedate default(getDate()) for activedate --默認(rèn)
alter table activeinfo add constraint FK_activeinfo_userid foreign key (userid) references userinfo(userid) --外鍵約束
alter table activeinfo add constraint FK_activeinfo_courseid foreign key (courseid) references courseinfo(courseid) --外鍵約束
alter table activeinfo add constraint FK_activeinfo_templateid foreign key (templateid) references template(templateid) --外鍵約束
alter table activeinfo add constraint DF_activeinfo_status default(0) for status --默認(rèn)
/* activeandclass 表約束*/
alter table activeandclass add constraint PK_acid primary key (acid) --主鍵約束
alter table activeandclass add constraint FK_activeandclass_activeid foreign key (activeid) references activeinfo(activeid) --外鍵約束
alter table activeandclass add constraint FK_activeandclass_classid foreign key (classid) references classinfo(classid) --外鍵約束
alter table activeandclass add constraint UQ_activeid_classid unique (activeid, classid) --唯一約束
alter table activeandclass add constraint DF_activeandclass_useramount default(0) for useramount --默認(rèn)
alter table activeandclass add constraint DF_activeandclass_status default(0) for status --默認(rèn)
alter table activeandclass add constraint DF_activeandclass_total default(0) for total --默認(rèn)
alter table activeandclass add constraint DF_activeandclass_avg default(0.00) for avg --默認(rèn)
/* result 表約束*/
alter table result add constraint PK_resultid primary key (resultid) --主鍵約束
alter table result add constraint FK_result_acid foreign key (acid) references activeandclass(acid) --外鍵約束
alter table result add constraint FK_result_itemid foreign key (itemid) references item(itemid) --外鍵約束
alter table result add constraint UQ_result_ip_acid_itemid unique (ip, acid, itemid) --唯一約束
alter table result add constraint DF_userresult default(0) for userresult --默認(rèn)
/* score 表約束*/
alter table score add constraint PK_scoreid primary key (scoreid) --主鍵約束
alter table score add constraint FK_score_acid foreign key (acid) references activeandclass(acid) --外鍵約束
alter table score add constraint FK_score_itemid foreign key (itemid) references item(itemid) --外鍵約束
alter table score add constraint UQ_score_acid_itemid unique (acid, itemid) --唯一約束
alter table score add constraint DF_total default(0) for total --默認(rèn)
alter table score add constraint DF_avg default(0.00) for avg --默認(rèn)
alter table score add constraint DF_numexcellent default(0) for numexcellent --默認(rèn)
alter table score add constraint DF_numpoorest default(0) for numpoorest --默認(rèn)
/*
1添加反饋活動(dòng)
a) 使用存儲(chǔ)過程實(shí)現(xiàn)如下功能，根據(jù)實(shí)際傳遞的數(shù)據(jù)增加一項(xiàng)反饋活動(dòng)，同時(shí)往activeandclass表中添加多條數(shù)據(jù)。
要求：注意事務(wù)的處理；檢查存儲(chǔ)過程是否已存在，如果存在則先刪除
b) 測(cè)試存儲(chǔ)過程，添加如下數(shù)據(jù)（其中參與班級(jí)應(yīng)根據(jù)表中實(shí)際數(shù)據(jù)需添加對(duì)應(yīng)的班級(jí)ID）：
09級(jí)實(shí)訓(xùn)班講師反饋第一次 參與班級(jí)0901班/0902班/0903班/0904班
09級(jí)實(shí)訓(xùn)班講師反饋第2次 參與班級(jí)0901班/0902班/01實(shí)訓(xùn)班/02實(shí)訓(xùn)班
*/
/*
select * from activeinfo
select * from classinfo
select * from activeandclass
*/
--a) 使用存儲(chǔ)過程實(shí)現(xiàn)如下功能，根據(jù)實(shí)際傳遞的數(shù)據(jù)增加一項(xiàng)反饋活動(dòng)，同時(shí)往activeandclass表中添加多條數(shù)據(jù)。
--要求：注意事務(wù)的處理；檢查存儲(chǔ)過程是否已存在，如果存在則先刪除
-- 創(chuàng)建截取字符串存儲(chǔ)過程
use feedback
if exists(select name from sysobjects where name = 'proc_splitStr')
drop procedure proc_splitStr
go
create procedure proc_splitStr
-- 存儲(chǔ)過程參數(shù)，不寫output默認(rèn)為輸入
@bigStr varchar(100) output,
@headStr varchar(20) output
as
-- 聲明變量
declare @position int
-- 給變量賦值
set @position = charindex('/', @bigStr)
if @position = 0 -- 判斷傳入的字符串是否含有'/'
begin
set @headStr = @bigStr
set @bigStr = null
end
else
begin
-- substring（字符串， 字符串中的起點(diǎn)， 字符數(shù)）
-- ( @headStr 參與班級(jí)0901班)/0902班/0903班/0904班
set @headStr = substring(@bigStr, 0, @position)
-- @bigStr 0902班/0903班/0904班
set @bigStr = substring(@bigStr, @position + 1, len(@bigStr)-@position)
end
go
/*
-- 測(cè)試
declare @bigStr varchar(100)
declare @headStr varchar(20)
set @bigStr = '0901班/0902班/0903班/0904班'
set @headStr = null
exec proc_splitStr @bigStr output, @headStr output
print @bigStr
print @headStr
go
*/
-- 創(chuàng)建插入活動(dòng)數(shù)據(jù)的存儲(chǔ)過程
use feedback
if exists(select * from sysobjects where name ='proc_addactive')
drop proc proc_addactive
go
create proc proc_addactive
@activename varchar(100),
@usertypeid int,
@userid int,
@courseid int,
@templateid int,
@classname varchar(100),
@amount int
as
--聲明變量
declare @identityNum int
declare @classunit varchar(50)
declare @classid int
declare @sumerror int
set @sumerror=0
--開啟事務(wù)
begin transaction
insert into activeinfo(activename,usertypeid,userid,courseid,templateid)
values(@activename,@usertypeid,@userid,@courseid,@templateid)
set @sumerror = @sumerror + @@error
set @identityNum=@@identity
--截取班級(jí)的字符串
while @classname is not null
begin
exec proc_splitStr @classname output,@classunit output
select @classid=classid from classinfo where classname=@classunit
--添加班級(jí)與活動(dòng)的對(duì)象關(guān)系
insert into activeandclass(activeid,classid,useramount) values(@identityNum,@classid,@amount)
set @sumerror = @sumerror + @@error
end
--判斷語句執(zhí)行的狀態(tài)
if @sumerror=0
begin
--沒錯(cuò)誤
commit transaction
print '儲(chǔ)存操作成功'
end
else
begin
--中間存在問題
rollback transaction
print '儲(chǔ)存操作失敗'
end
go
--b) 測(cè)試存儲(chǔ)過程，添加如下數(shù)據(jù)（其中參與班級(jí)應(yīng)根據(jù)表中實(shí)際數(shù)據(jù)需添加對(duì)應(yīng)的班級(jí)ID）：
--09級(jí)實(shí)訓(xùn)班講師反饋第一次 參與班級(jí)0901班/0902班/0903班/0904班
--09級(jí)實(shí)訓(xùn)班講師反饋第2次 參與班級(jí)0901班/0902班/01實(shí)訓(xùn)班/02實(shí)訓(xùn)班
-- 添加班級(jí)
insert into classtype values('普通班')
insert into classtype values('沖刺班')
insert into classtype values('實(shí)訓(xùn)班')
-- 添加班級(jí)信息
insert into classinfo (classname,ctypeid) values ('0901班',1)
insert into classinfo (classname,ctypeid) values ('0902班',1)
insert into classinfo (classname,ctypeid) values ('0903班',1)
insert into classinfo (classname,ctypeid) values ('0904班',1)
insert into classinfo (classname,ctypeid) values ('01實(shí)訓(xùn)班',3)
insert into classinfo (classname,ctypeid) values ('02實(shí)訓(xùn)班',3)
-- 用戶類型
INSERT INTO usertype (utypename) VALUES ('教員')
INSERT INTO usertype (utypename) VALUES ('班主任')
INSERT INTO usertype (utypename) VALUES ('機(jī)房維護(hù)員')
INSERT INTO usertype (utypename) VALUES ('教務(wù)人員')
-- 用戶
insert into userinfo (username, usertypeid) values ('教員1', 1)
insert into userinfo (username, usertypeid) values ('教員2', 1)
insert into userinfo (username, usertypeid) values ('教員3', 1)
-- 課程
insert into courseinfo values ('語文')
insert into courseinfo values ('數(shù)學(xué)')
insert into courseinfo values ('英語')
-- 模板
insert into template (templatename, usertypeid, usecount) values ('理論課評(píng)定', 1, 20)
insert into template (templatename, usertypeid, usecount) values ('課外活動(dòng)評(píng)定', 1, 20)
select * from classtype
select * from classinfo
select * from usertype
select * from userinfo
select * from courseinfo
select * from template
-- 測(cè)試存儲(chǔ)過程
--09級(jí)實(shí)訓(xùn)班講師反饋第一次 參與班級(jí)0901班/0902班/0903班/0904班
--09級(jí)實(shí)訓(xùn)班講師反饋第2次 參與班級(jí)0901班/0902班/01實(shí)訓(xùn)班/02實(shí)訓(xùn)班
exec proc_addactive '09級(jí)實(shí)訓(xùn)班講師反饋第一次',1,1,1,1,'0901班/0902班/0903班',50
exec proc_addactive '09級(jí)實(shí)訓(xùn)班講師反饋第2次',1,1,1,1,'0901班/0902班/01實(shí)訓(xùn)班/02實(shí)訓(xùn)班',50
select * from activeinfo
select * from activeandclass
select * from classinfo
--------------------------
/*
2批量發(fā)布反饋活動(dòng)
a) 使用存儲(chǔ)過程實(shí)現(xiàn)如下功能，批量發(fā)布實(shí)際選中的多個(gè)反饋活動(dòng)。
要求：注意事務(wù)的處理；檢查存儲(chǔ)過程是否已存在，如果存在則先刪除
*/
-- 定義批量發(fā)布反饋活動(dòng)存儲(chǔ)過程
use feedback
if exists(select * from sysobjects where name ='proc_deployactive')
drop proc proc_deployactive
go
create proc proc_deployactive
@activeids varchar(100) -- 反饋活動(dòng)id（id1/id2/id3）
as
--聲明用到的變量
declare @activeid varchar(10) -- 待發(fā)布反饋活動(dòng)的id
declare @sumerror int -- 錯(cuò)誤號(hào)
set @sumerror=0
--開啟事務(wù)
begin transaction
--截取班級(jí)的字符串
while @activeids is not null
begin
exec proc_splitStr @activeids output,@activeid output
--添加班級(jí)與活動(dòng)的對(duì)象關(guān)系
update activeinfo set status = 2 where activeid = @activeid
set @sumerror = @sumerror + @@error
end
--判斷語句執(zhí)行的狀態(tài)
if @sumerror=0
begin
--沒錯(cuò)誤
commit transaction
print '發(fā)布操作成功'
end
else
begin
--中間存在問題
rollback transaction
print '發(fā)布操作失敗'
end
go
-- 測(cè)試
select * from activeinfo
exec proc_deployactive '1/2'
update activeinfo set status = 0 where activeid = 1
update activeinfo set status = 0 where activeid = 2
/*
3刪除反饋活動(dòng)結(jié)果
a) 使用觸發(fā)器模擬簡(jiǎn)易的刪除反饋活動(dòng)結(jié)果的功能，要求刪除反饋活動(dòng)結(jié)果的同時(shí)修改activeandclass表中“參與人數(shù)”字段useramount -1
b) 測(cè)試觸發(fā)器
*/
-- 添加數(shù)據(jù)
INSERT INTO methodtype (typename,description) VALUES ('answer','按回答評(píng)定')
INSERT INTO methodtype (typename,description) VALUES ('sorce','按分?jǐn)?shù)評(píng)定/評(píng)價(jià)標(biāo)準(zhǔn)：5分[優(yōu)秀] 4分[良好] 3分[一般] 2分[差] 1分[很差]')
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('對(duì)該教員有什么建議？',1,1)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('該教員哪方面對(duì)你有幫助？',1,1)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('講課是否活躍？',2,1)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('內(nèi)容是否詳細(xì)？',2,1)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('是否熱心幫助同學(xué)？',2,1)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('感覺班主任哪些方面需要改進(jìn)',1,2)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('是否經(jīng)常開班會(huì)？',2,2)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('是否關(guān)心班級(jí)的相關(guān)事情？',2,2)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('對(duì)該管理的服務(wù)態(tài)度有什么建議？',1,3)
INSERT INTO item (itemname,methodtypeid,usertypeid) VALUES ('對(duì)該管理員的態(tài)度打多少分？',2,3)
select * from result
select * from activeandclass
insert into result (ip, acid, itemid, userresult, answer) values ('127.0.0.1', 1, 1, 1, '很好啊')
insert into result (ip, acid, itemid, userresult, answer) values ('127.0.0.1', 1, 2, 90, '不錯(cuò)')
insert into result (ip, acid, itemid, userresult, answer) values ('127.0.0.1', 1, 3, 85, '不錯(cuò)的事情')
--a) 使用觸發(fā)器模擬簡(jiǎn)易的刪除反饋活動(dòng)結(jié)果的功能，要求刪除反饋活動(dòng)結(jié)果的同時(shí)
--修改activeandclass表中“參與人數(shù)”字段useramount -1
if exists(select * from sysobjects where name ='tgr_result_delete')
drop trigger tgr_result_delete
go
create trigger tgr_result_delete
on result
for delete --刪除觸發(fā)
as
-- 定義變量
declare @acid int
declare @sumerror int
set @sumerror = 0
select @acid=acid from Deleted
update activeandclass set useramount = useramount - 1 where acid = @acid
set @sumerror = @sumerror + @@error
if @sumerror = 0
begin
print 'activeandclass表中“參與人數(shù)”字段useramount -1'
end
else
begin
print 'activeandclass表更改失敗'
end
go
--b) 測(cè)試觸發(fā)器
select * from activeandclass
insert into result (ip, acid, itemid, userresult, answer) values ('127.0.0.1', 1, 1, 1, '很好啊')
delete result where answer = '很好啊'
/*
4查看所有反饋活動(dòng)
a) 使用視圖和函數(shù)實(shí)現(xiàn)，查看所有反饋活動(dòng)的功能。如下圖所示：
*/
select * from activeinfo
--select * from usertype
--select * from userinfo
--select * from courseinfo
select * from activeandclass
select * from classinfo
-- 創(chuàng)建根據(jù) activeid 獲取所有班級(jí)字符串的函數(shù)
if exists(select * from sysobjects where name = 'fun_getcnames')
drop function fun_getcnames
go
create function fun_getcnames(@activeid int)
returns varchar (100)
as
begin
-- 變量聲明
declare @result_classinfo varchar (200)
declare @classnames varchar (200)
set @classnames = ''
-- 聲明一個(gè)游標(biāo)
declare cur_classinfo cursor for
-- 查詢語句
select classname from activeandclass,classinfo
where activeandclass.classid = classinfo.classid and activeandclass.activeid = @activeid
-- 打開游標(biāo)
Open cur_classinfo
-- 循環(huán)并提取記錄
Fetch Next From cur_classinfo Into @result_classinfo-- 取第一條記錄存入@result中
While ( @@Fetch_Status = 0 )
begin
set @classnames = @classnames + ' ' +@result_classinfo -- 處理結(jié)果
Fetch Next From cur_classinfo into @result_classinfo -- 下一條
end
-- 關(guān)閉游標(biāo)
close cur_classinfo
-- 釋放游標(biāo)
deallocate cur_classinfo
return @classnames
end
go
-- 測(cè)試函數(shù)
select activeid from activeinfo
select dbo.fun_getcnames(1) as 'result'
select * from activeinfo
------------------- 創(chuàng)建視圖 ---------------------------
if exists(select * from sysobjects where name = 'view_activeinfos')
drop view view_activeinfos
go
create view view_activeinfos
as
select activename as '活動(dòng)名稱', activedate as '活動(dòng)時(shí)間', utypename as '被評(píng)價(jià)人類型',
username as '被評(píng)價(jià)人姓名', coursename as '技能課程', dbo.fun_getcnames(activeid) as '參與班級(jí)'
from activeinfo
left join usertype
on activeinfo.usertypeid = usertype.usertypeid
left join userinfo
on userinfo.userid = activeinfo.userid
left join courseinfo
on courseinfo.courseid = activeinfo.courseid
go
-- 測(cè)試結(jié)果
select * from view_activeinfos
/*
5根據(jù)條件查詢反饋活動(dòng)
a) 查詢某位教員（如，劉小林），在某一時(shí)間段內(nèi)（如，2009年度）被評(píng)價(jià)的反饋活動(dòng)。如下圖所示：
提示：使用索引
*/
-------------------------------------------------------------------------------
/*
select username as '被評(píng)價(jià)人姓名' ,
activename as '活動(dòng)名稱',
activedate as '活動(dòng)時(shí)間',
dbo.fun_getcnames(activeinfo.activeid) as '參與班級(jí)',
--SUM() as '參與人數(shù)',
--AVG() as '總平均分'
userresult as '得分',
useramount as '本班人數(shù)'--,
--classname as '班級(jí)名稱'
from result
left join activeandclass
on result.acid = activeandclass.acid
left join classinfo
on classinfo.classid = activeandclass.classid
left join activeinfo
on activeinfo.activeid = activeandclass.activeid
left join usertype
on activeinfo.usertypeid = usertype.usertypeid
left join userinfo
on userinfo.userid = activeinfo.userid
left join courseinfo
on courseinfo.courseid = activeinfo.courseid
--left join classinfo
-- on classinfo.classid = activeandclass.classid
where username = '教員1'
-- 添加時(shí)間限制
and convert(date, activedate) > convert(date, '2013')
and convert(date, activedate) < convert(date, '2014')
*/
-- 定義獲取班級(jí)人數(shù)函數(shù)人數(shù)
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fun_getclassmount')
DROP function fun_getclassmount
go
create function fun_getclassmount(@activeid int)
returns int
as
begin
-- 定義游標(biāo)
declare class_usermount cursor read_only for
select activeandclass.useramount
from activeandclass,classinfo
where activeandclass.activeid=@activeid and activeandclass.classid=classinfo.classid
--聲明變量接收游標(biāo)數(shù)據(jù)
declare @useramount int,@mount int
set @mount = 0
--打開游標(biāo)
open class_usermount
--獲取游標(biāo)數(shù)據(jù)
fetch next from class_usermount into @useramount
--迭代遍歷
while @@fetch_status = 0
begin
set @mount = @mount + @useramount
--獲取游標(biāo)數(shù)據(jù)
fetch next from class_usermount into @useramount
end
--關(guān)閉游標(biāo)
close class_usermount
--釋放游標(biāo)
deallocate class_usermount
return @mount
end
GO
-- 定義獲取平均分函數(shù)
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fun_getrseultavg')
DROP function fun_getrseultavg
go
create function fun_getrseultavg(@activeid int)
returns float
as
begin
--定義游標(biāo)
declare class_usermount cursor read_only for
select activeandclass.total,activeandclass.useramount
from activeandclass,classinfo
where activeandclass.activeid=@activeid and activeandclass.classid=classinfo.classid
declare @useramount int,@mount int,@number int,@number_all int
set @mount=0
set @number_all=0
-- 打開游標(biāo)
open class_usermount
-- 獲取游標(biāo)數(shù)據(jù)
fetch next from class_usermount into @useramount,@number
-- 迭代遍歷
while @@fetch_status = 0
begin
--連接字符串
set @mount = @mount + @useramount
set @number_all = @number_all + @number
--獲取游標(biāo)數(shù)據(jù)
fetch next from class_usermount into @useramount,@number
end
--關(guān)閉游標(biāo)
close class_usermount
--釋放游標(biāo)
deallocate class_usermount
declare @avg float
set @avg = @mount / @number_all
return @avg
end
GO
-----------------------------------------------------------------------
-- 測(cè)試函數(shù)
use feedback
select dbo.fun_getclassmount(1)
select * from activeinfo
select * from activeandclass
select * from classinfo
select username as '被評(píng)價(jià)人姓名',activename as '活動(dòng)名稱',activedate as '活動(dòng)日期',
dbo.fun_getclassmount(activeinfo.activeid) as '參與班級(jí)',
dbo.fun_getclassmount(activeinfo.activeid) as '參與人數(shù)',
dbo.fun_getrseultavg(activeinfo.activeid) as '總平均分'
from (activeinfo left join userinfo on activeinfo.userid=userinfo.userid)
--left join activeandclass on activeinfo.activeid=activeandclass.activeid
?
教學(xué)反饋系統(tǒng)-階段項(xiàng)目2
第一部分案例描述
案例目的
???????? 學(xué)習(xí)并鞏固 SQL Server 數(shù)據(jù)庫編程技術(shù)，包括存儲(chǔ)過程、觸發(fā)器、索引、視圖、事務(wù)、游標(biāo)、函數(shù)等，提高學(xué)生數(shù)據(jù)庫設(shè)計(jì)和數(shù)據(jù)庫編程的能力。
案例難度
???????? ★★★★
案例覆蓋技能點(diǎn)
1、 ?? 存儲(chǔ)過程
2、 ?? 觸發(fā)器
3、 ?? 索引
4、 ?? 視圖
5、 ?? 事務(wù)
6、 ?? 游標(biāo)
7、 ?? 函數(shù)
推薦案例完成時(shí)間
???????? ?0.5 天
適用課程和對(duì)象
???????? SQL SERVER 數(shù)據(jù)庫設(shè)計(jì)
第二部分需求和開發(fā)環(huán)境
使用技術(shù)和開發(fā)環(huán)境
???????? SQL Server 2005
項(xiàng)目背景
中國經(jīng)濟(jì)數(shù)年來持續(xù)高增長帶來了專業(yè)性職業(yè)人才的需求激增，職業(yè)教育作用日益顯現(xiàn)，優(yōu)秀企業(yè)也孕育而生。他們的作用不僅僅為社會(huì)培養(yǎng)了專業(yè)人才，在產(chǎn)業(yè)經(jīng)營領(lǐng)域，他們也扮演了重要的角色。改革開放以來，隨著中國經(jīng)濟(jì)社會(huì)的發(fā)展，職業(yè)教育越來越受到國家的高度重視和社會(huì)的廣泛關(guān)注。隨著經(jīng)濟(jì)社會(huì)的發(fā)展，中國的職業(yè)教育取得了長足的發(fā)展，在職業(yè)教育理念的實(shí)踐群體中，若想更好地成為佼佼者，無疑是在 在規(guī)模化發(fā)展中保障 教學(xué)質(zhì)量是其中一個(gè)比較重要的方面。
案例需求
教學(xué)質(zhì)量是學(xué)校生存與發(fā)展的生命線，不斷提高課堂教學(xué)水平是學(xué)校和每一位教師的共同心愿。及時(shí)了解課堂教學(xué)的主體—學(xué)生對(duì)教學(xué)情況的評(píng)價(jià)及建議，有利于教師發(fā)現(xiàn)自己教學(xué)中的優(yōu)點(diǎn)以及不足，從而進(jìn)一步改進(jìn)教學(xué)方法，提高教學(xué)水平。為了更好的提高教學(xué)水平，建立學(xué)校與學(xué)員的更好勾通，院領(lǐng)導(dǎo)研究決定研發(fā)本系統(tǒng)，并提供考核內(nèi)容管理、反饋項(xiàng)目管理、反饋表管理、數(shù)據(jù)統(tǒng)計(jì)分析等主要功能，本階段案例主要以反饋活動(dòng)管理為主要分析目標(biāo)， 詳細(xì)功能描述如下：
1、? 反饋活動(dòng)管理
對(duì)學(xué)院內(nèi)部反饋活動(dòng)進(jìn)行管理和維護(hù)，包括對(duì)反饋活動(dòng)的添加、修改、刪除、查看、批量刪除、發(fā)布、批量發(fā)布和關(guān)閉某個(gè)班的反饋活動(dòng)等。反饋 活動(dòng) 的詳細(xì)信息包括：反饋活動(dòng)編號(hào)、反饋活動(dòng)名稱、活動(dòng)日期、被評(píng)價(jià)人類型、被評(píng)價(jià)人姓名、技能課程、本次反饋采用模板、參與班級(jí)。
反饋活動(dòng)列表
?
添加反饋活動(dòng)
?
???????? 查看反饋活動(dòng)
???????? 系統(tǒng)基本模塊包括：
|
功能點(diǎn) |
難度 |
? |
|
添加反饋活動(dòng) |
★★★★ |
? |
|
批量發(fā)布反饋活動(dòng) |
★★★ |
? |
|
刪除反饋活動(dòng)結(jié)果 |
★★★★ |
? |
|
查看所有反饋活動(dòng) |
★★★★ |
? |
|
根據(jù)條件查詢反饋活動(dòng) |
★★ |
? |
?
功能點(diǎn)介紹
?
數(shù)據(jù)庫表結(jié)構(gòu)關(guān)系圖
?
?
表 1 人員類型表
|
表名 |
usertype (人員類型表) |
|||
|
列名 |
描述 |
數(shù)據(jù)類型 |
空/非空 |
約束條件 |
|
usertypeid |
類型編號(hào) |
int |
非空 |
主鍵，標(biāo)識(shí)列 |
|
utypename |
類型名稱 |
Varchar(20) |
非空 |
唯一 |
?
?
表 2 考核方式類型表
|
表名 |
methodtype (考核方式表) |
|||
|
列名 |
描述 |
數(shù)據(jù)類型 |
空/非空 |
約束條件 |
|
methodtypeid |
考核方式編號(hào) |
int |
非空 |
主鍵，標(biāo)識(shí)列 |
|
typename |
考核方式名稱 |
Varchar(20) |
非空 |
唯一 |
|
description |
描述 |
Varchar(100) |
? |
? |
?
表 3 考核項(xiàng)表
|
表名 |
item (考核項(xiàng)表) |
|||
|
列名 |
描述 |
數(shù)據(jù)類型 |
空/非空 |
約束條件 |
|
itemid |
考核項(xiàng) 編號(hào) |
int |
非空 |
主鍵，標(biāo)識(shí)列 |
|
itemname |
考核項(xiàng) 名稱 |
Varchar(60) |
非空 |
唯一 |
|
methodtypeid |
考核方式編號(hào) |
int |
非空 |
外鍵 |
|
usertypeid |
適用人員類型編號(hào) |
int |
非空 |
外鍵 |
?
表 4 反饋模板表
|
表名 |
template (人員類型表) |
|||
|
列名 |
描述 |
數(shù)據(jù)類型 |
空/非空 |
約束條件 |
|
templateid |
模板 編號(hào) |
int |
非空 |
主鍵，標(biāo)識(shí)列 |
|
templatename |
模板 名稱 |
Varchar(30) |
非空 |
唯一 |
|
status |
狀態(tài) |
int |
非空 |
0-正常（默認(rèn)值） 1-刪除 |
|
usertypeid |
適用人員類型編號(hào) |
int |
非空 |
外鍵 |
|
usecount |
使用次數(shù) |
int |
非空 |
外鍵 |
?
表 5 反饋模板與考核項(xiàng)關(guān)聯(lián)表
|
表名 |
templateanditem (人員類型表) |
|||
|
列名 |
描述 |
數(shù)據(jù)類型 |
空/非空 |
約束條件 |
|
id |
編號(hào) |
int |
非空 |
主鍵，標(biāo)識(shí)列 |
|
templateid |
模板編號(hào) |
int |
非空 |
外鍵 與 考核項(xiàng) 編號(hào)一起，唯一 |
|
itemid |
考核項(xiàng) 編號(hào) |
int |
非空 |
外鍵 與模板編號(hào)一起，唯一 |
?
表 6 班級(jí)類型表
|
表名 |
classtype (班級(jí)類型表) |
|||
|
列名 |
描述 |
數(shù)據(jù)類型 |
空/非空 |
約束條件 |
|
ctypeid |
編號(hào) |
int |
非空 |
主鍵，標(biāo)識(shí)列 |
|
ctypename |
類型名稱 |
Varchar(20) |
非空 |
唯一 |
?
表 7 班級(jí)信息表
|
表名 |
classinfo (班級(jí)信息表) |
|||
|
列名 |
描述 |
數(shù)據(jù)類型 |
空/非空 |
約束條件 |
|
classid |
編號(hào) |
int |
非空 |
主鍵，標(biāo)識(shí)列 |
|
classname |
班級(jí)名稱 |
Varchar(30) |
非空 |
唯一 |
|
startdate |
開班時(shí)間 |
datetime |
非空 |
默認(rèn)系統(tǒng)日期 |
|
status |
狀態(tài) |
int |
非空 |
0-正常(默認(rèn)) 1-刪除 2-結(jié)業(yè) |
|
ctypeid |
班級(jí)類型編號(hào) |
int |
非空 |
外鍵 |
?
表 8 用戶信息表
|
表名 |
userinfo (用戶信息表) |
|||
|
列名 |
描述 |
數(shù)據(jù)類型 |
空/非空 |
約束條件 |
|
userid |
編號(hào) |
int |
非空 |
主鍵，標(biāo)識(shí)列 |
|
username |
用戶名稱 |
Varchar(20) |
非空 |
唯一 |
|
usertypeid |
用戶 類型編號(hào) |
int |
非空 |
外鍵 |
?
表 9 課程信息表
|
表名 |
courseinfo (課程信息表) |
|||
|
列名 |
描述 |
數(shù)據(jù)類型 |
空/非空 |
約束條件 |
|
courseid |
編號(hào) |
int |
非空 |
主鍵，標(biāo)識(shí)列 |
|
coursename |
課程名稱 |
Varchar(30) |
非空 |
唯一 |
?
表 10 反饋活動(dòng)信息表
|
表名 |
activeinfo (反饋活動(dòng)信息表) |
|||
|
列名 |
描述 |
數(shù)據(jù)類型 |
空/非空 |
約束條件 |
|
activeid |
編號(hào) |
int |
非空 |
主鍵，標(biāo)識(shí)列 |
|
activename |
活動(dòng)名稱 |
Varchar(50) |
非空 |
唯一 |
|
activedate |
活動(dòng)時(shí)間 |
datetime |
非空 |
默認(rèn)系統(tǒng)日期 |
|
usertypeid |
被評(píng)價(jià)人 類型編號(hào) |
int |
非空 |
? |
|
userid |
被評(píng)價(jià)人 編號(hào) |
int |
非空 |
外鍵 |
|
courseid |
技能課程 編號(hào) |
int |
? |
外鍵 |
|
templateid |
反饋模板 編號(hào) |
int |
非空 |
外鍵 |
|
status |
狀態(tài) |
int |
非空 |
0-正常（未發(fā)布，默認(rèn)）；1-刪除；2-已發(fā)布 |
?
表 11 反饋活動(dòng)與班級(jí)關(guān)聯(lián)表
|
表名 |
activeandclass (反饋活動(dòng)與班級(jí)關(guān)聯(lián)表) |
|||
|
列名 |
描述 |
數(shù)據(jù)類型 |
空/非空 |
約束條件 |
|
acid |
編號(hào) |
int |
非空 |
主鍵，標(biāo)識(shí)列 |
|
activeid |
活動(dòng) 編號(hào) |
int |
非空 |
外鍵 與 班級(jí) 編號(hào)一起，唯一 |
|
classid |
班級(jí) 編號(hào) |
int |
非空 |
外鍵 與 活動(dòng) 編號(hào)一起，唯一 |
|
useramount |
參與人數(shù) |
int |
非空 |
默認(rèn)0 |
|
status |
狀態(tài) |
int |
非空 |
0-正常（活動(dòng)未開始，默認(rèn)）；1-刪除；2-進(jìn)行中；3-關(guān)閉 |
|
total |
總成績(jī) |
int |
非空 |
默認(rèn)0 |
|
avg |
平均成績(jī) |
decimal(18, 2) |
非空 |
默認(rèn)0.00 |
?
表 12 反饋結(jié)果表
|
表名 |
result (反饋結(jié)果表) |
|||
|
列名 |
描述 |
數(shù)據(jù)類型 |
空/非空 |
約束條件 |
|
resultid |
編號(hào) |
int |
非空 |
主鍵，標(biāo)識(shí)列 |
|
ip |
IP地址 |
Varchar(15) |
非空 |
? |
|
acid |
反饋活動(dòng)與班級(jí)關(guān)聯(lián)ID 號(hào) |
int |
非空 |
外鍵 |
|
itemid |
考核項(xiàng) 編號(hào) |
int |
非空 |
外鍵 |
|
userresult |
提交的成績(jī) |
int |
非空 |
默認(rèn)0 |
|
answer |
按回答評(píng)定時(shí)的用戶答案 |
Varchar(500) |
? |
默認(rèn)null |
?
表 13 成績(jī)表
|
表名 |
score (成績(jī)表) |
|||
|
列名 |
描述 |
數(shù)據(jù)類型 |
空/非空 |
約束條件 |
|
scoreid |
編號(hào) |
int |
非空 |
主鍵，標(biāo)識(shí)列 |
|
acid |
反饋活動(dòng)與班級(jí)關(guān)聯(lián)ID 號(hào) |
int |
非空 |
外鍵 與 考核項(xiàng) 編號(hào)一起，唯一 |
|
itemid |
考核項(xiàng) 編號(hào) |
int |
非空 |
外鍵 與 反饋活動(dòng)與班級(jí)關(guān)聯(lián)ID號(hào) 一起，唯一 |
|
total |
總成績(jī) |
int |
非空 |
默認(rèn)0 |
|
avg |
平均成績(jī) |
decimal(18, 2) |
非空 |
默認(rèn)0.00 |
|
numexcellent |
選”優(yōu)”的人數(shù) |
int |
非空 |
默認(rèn)0 |
|
numpoorest |
選”很差”的人數(shù) |
int |
非空 |
默認(rèn)0 |
?
1 添加反饋活動(dòng)
a) 使用存儲(chǔ)過程實(shí)現(xiàn)如下功能，根據(jù)實(shí)際傳遞的數(shù)據(jù)增加一項(xiàng)反饋活動(dòng)，同時(shí)往 activeandclass 表中添加多條數(shù)據(jù)。
要求：注意事務(wù)的處理；檢查存儲(chǔ)過程是否已存在，如果存在則先刪除
b) 測(cè)試存儲(chǔ)過程，添加如下數(shù)據(jù)（其中參與班級(jí)應(yīng)根據(jù)表中實(shí)際數(shù)據(jù)需添加對(duì)應(yīng)的班級(jí) ID ）：
09 級(jí)實(shí)訓(xùn)班講師反饋第一次 ? 參與班級(jí) 0901 班 /0902 班 /0903 班 /0904 班
09 級(jí)實(shí)訓(xùn)班講師反饋第 2 次 ? 參與班級(jí) 0901 班 /0902 班 /01 實(shí)訓(xùn)班 /02 實(shí)訓(xùn)班
2 批量發(fā)布反饋活動(dòng)
a) 使用存儲(chǔ)過程實(shí)現(xiàn)如下功能，批量發(fā)布實(shí)際選中的多個(gè)反饋活動(dòng)。
要求：注意事務(wù)的處理；檢查存儲(chǔ)過程是否已存在，如果存在則先刪除
3 刪除反饋活動(dòng)結(jié)果
a) 使用觸發(fā)器模擬簡(jiǎn)易的刪除反饋活動(dòng)結(jié)果的功能，要求刪除反饋活動(dòng)結(jié)果的同時(shí)修改 activeandclass 表中“參與人數(shù)”字段 useramount -1
b) 測(cè)試觸發(fā)器
4 查看所有反饋活動(dòng)
a) 使用視圖和函數(shù)實(shí)現(xiàn)，查看所有反饋活動(dòng)的功能。如下圖所示：
?
5 根據(jù)條件查詢反饋活動(dòng)
a) 查詢某位教員（如，劉小林），在某一時(shí)間段內(nèi)（如， 2009 年度）被評(píng)價(jià)的反饋活動(dòng)。如下圖所示：
?
提示：使用索引
第三部分考核評(píng)價(jià)點(diǎn)
|
序號(hào) |
功能列表 |
功能描述 |
分?jǐn)?shù) |
說明 |
|
1 |
批量刪除反饋活動(dòng) |
? |
? |
? |
|
2 |
? |
? |
? |
? |
|
3 |
刪除反饋活動(dòng)結(jié)果 |
? |
? |
? |
|
4 |
查看所有反饋活動(dòng) |
? |
? |
? |
|
5 |
根據(jù)條件查詢反饋活動(dòng) |
? |
? |
? |
|
6 |
數(shù)據(jù)庫命名規(guī)范 |
? |
? |
? |
?
?
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
