实验五 数据库编程
一、实验目的
熟悉并掌握嵌入式 SQL 编程、使用数据库访问接口技术实现对数据库的访问。
二、实验要求
熟悉使用嵌入式SQL编程访问数据库，熟悉VB中开发数据库应用程序的过程。
三、实验步骤
设计一个小型的数据库应用程序
1. 基本表建立
（1）教师表建立 Xum_Teachers18
create table Xum_Teachers18 (
xm_Tno18 char(10) primary key,
xm_Tname18 char(10),
xm_Tsex18 char(5),
xm_Tage18 int,
xm_Tpos18 char(10),
xm_Tpho18 char(20)
)
（2）专业表建立Xum_Majors18
create table Xum_Majors18(
xm_Mno18 char(10) primary key,
xm_Mname18 char(10)
)
（3）班级表建立Xum_Class18
create table Xum_Class18(
xm_Clno18 char(10) primary key,
xm_Clname char(10),
xm_Mno18 char(10)
constraint Major_Class foreign key(xm_Mno18) references Xum_Majors18
)
（4）学生表Xum_Students18
create table Xum_Students18(
xm_Sno18 char(12) primary key,
xm_Sname18 char(10),
xm_Ssex18 char(5),
xm_Sage18 int,
xm_Sorig18 char(20),
xm_Ssum18 float,
xm_Clno18 char(10)
constraint Class_Student foreign key(xm_Clno18) references Xum_Class18
)
（5）课程表 Xum_Courses18
create table Xum_Courses18(
xm_Cno18 char(10) primary key,
xm_Cname18 char(10),
xm_Tname18 char(10),
xm_Cdate18 char(20),
xm_Cway18 char(5),
xm_Ccredit18 float check(xm_Ccredit18>0),
xm_Chour18 int check(xm_Chour18>0)
)
（6）授课表 Xum_Tea_Cour18
create table Xum_Tea_Cour18(
xm_Tno18 char(10),
xm_Cno18 char(10),
primary key(xm_Tno18,xm_Cno18),
constraint teacher_course foreign key(xm_Tno18) references Xum_Teachers18,
constraint tea_cour foreign key(xm_Cno18) references Xum_Courses18
)
（7）班级上课表Xum_Class_Cour18
create table Xum_Class_Cour18(
xm_Clno18 char(10),
xm_Cno18 char(10),
primary key(xm_Clno18,xm_Cno18),
constraint class_course foreign key(xm_Clno18) references Xum_Class18,
constraint clas_cour foreign key(xm_Cno18) references Xum_Courses18
)
（8）教师任课表Xum_Teac_Class18
create table Xum_Teac_Class18(
xm_Tno18 char(10),
xm_Clno18 char(10),
primary key(xm_Tno18,xm_Clno18),
constraint tea_class foreign key(xm_Tno18) references Xum_Teachers18,
constraint teach_class foreign key(xm_Clno18) references Xum_Class18
)
（9）成绩表Xum_Reports18
create table Xum_Reports18(
xm_Cno18 char(10),
xm_Sno18 char(12),
xm_Score18 float,
xm_date char(10),
xm_Tname18 char(10),
primary key(xm_Cno18,xm_Sno18),
constraint cour foreign key(xm_Cno18) references Xum_Courses18,
constraint stude foreign key(xm_Sno18) references Xum_Students18
)
（10）管理员账号密码表
create table Students_account18(
xm_Sno18 char(12) primary key,
xm_Skey18 char(10)
)
（11）学生账号密码表
create table Teachers_account18(
xm_Tno18 char(12) primary key,
xm_Tkey18 char(10)
)
（12）教师账号密码表
create table Admin_account18(
xm_Ano18 char(12) primary key,
xm_Akey18 char(10)
)
2.视图的建立
（1）学生成绩
create view 学生成绩统计
as
(select Xum_Students18.xm_Sno18,Xum_Students18.xm_Sname18,
Xum_Courses18.xm_Cname18,Xum_Class18.xm_Clname,
Xum_Reports18.xm_Tname18,Xum_Courses18.xm_Ccredit18,
Xum_Courses18.xm_Cdate18,Xum_Reports18.xm_Score18
from Xum_Reports18,Xum_Students18,Xum_Courses18,Xum_Class18
where Xum_Students18.xm_Sno18=Xum_Reports18.xm_Sno18
and Xum_Reports18.xm_Cno18=Xum_Courses18.xm_Cno18
and Xum_Class18.xm_Clno18=Xum_Students18.xm_Clno18
)
（2）课程平均成绩
create view 每门平均成绩
as(
select xm_Cno18,avg(xm_Score18)平均分
from Xum_Reports18
group by xm_Cno18
)
（3）课程和学分统计
create view 课程和学分统计
as
(
select xm_Sno18,xm_Cname18,xm_Ccredit18
from 学生成绩统计
)
（4）教师课表
create view 教师课表
as(
select xm_Tno18,Xum_Teachers18.xm_Tname18,xm_Cno18,xm_Cname18,
xm_Ccredit18,xm_Chour18
from Xum_Teachers18,Xum_Courses18
where Xum_Teachers18.xm_Tname18=Xum_Courses18.xm_Tname18
)
（5）班级课表
create view 班级课表
as
(
select Xum_Class18.xm_Clno18,xm_Clname,Xum_Courses18.xm_Cno18,
xm_Cname18,xm_Cdate18,xm_Ccredit18,xm_Chour18
from Xum_Class18,Xum_Courses18,Xum_Class_Cour18
where Xum_Class18.xm_Clno18=Xum_Class_Cour18.xm_Clno18
and Xum_Class_Cour18.xm_Cno18=Xum_Courses18.xm_Cno18
)
（6）生源地人员统计
create view 生源地人员统计
as
(select xm_Sorig18,count(xm_Sno18)数量
from Xum_Students18
group by xm_Sorig18
)
（7）教师课程成绩
create view 教师课程成绩
as
(
select xm_Tno18,Xum_Teachers18.xm_Tname18,xm_Cno18,xm_Sno18,xm_Score18
from Xum_Teachers18,Xum_Reports18
where Xum_Teachers18.xm_Tname18=Xum_Reports18.xm_Tname18
)
3.索引建立
create unique index Tea_Tno on Xum_Teachers18(xm_Tno18)
create unique index Cour_Cno on Xum_Courses18(xm_Cno18)
create unique index Mno_CLno on Xum_Class18(xm_CLno18,xm_Mno18)
create unique index Majo_Mno on Xum_Majors18(xm_Mno18)
create unique index CLno_Sno on Xum_Students18(xm_Sno18,xm_CLno18)
create unique index Cno_Sno on Xum_Reports18(xm_Sno18,xm_Cno18)
4.触发器建立
（1）教师删除（删除一个教师时，与教师相关的记录全都删除）
create trigger 教师删除18
on Xum_Teachers18
for delete
as
delete xm_Tno18
where Xum_Tea_Cour18.xm_Tno18=(select xm_Tno18 from deleted)
and Xum_Teac_Class18.xm_Tno18=(select xm_Tno18 from deleted)
（2）学生删除
create trigger 学生删除18
on Xum_Students18
for delete
as
begin
delete from Xum_Reports18
where Xum_Reports18.xm_Sno18 in (select xm_Sno18 from deleted)
delete from Students_account18
where Students_account18.xm_Sno18 in (select xm_Sno18 from deleted)
End
（3）学生统计（提交成绩时，将>=60的成绩记为合格，同时加上该课程学分）
create trigger 学分统计18 on Xum_Reports18
for insert
as
update Xum_Students18
set xm_Ssum18=(
select sum(xm_Ccredit18)
from Xum_Courses18
where xm_Cno18 in
(
select xm_Cno18
from Xum_Reports18
where xm_Score18>=60
and Xum_Reports18.xm_Sno18 in
(select xm_Sno18
from inserted)
)
)
where Xum_Students18.xm_Sno18 in
(
select xm_Sno18
from inserted
)
（4）教师更新
create trigger 教师更新
on Xum_Teachers18
for update
as
if update(xm_Tno18)
begin
update Xum_Tea_Cour18
set xm_Tno18=i.xm_Tno18
from deleted d,inserted i ,Xum_Tea_Cour18 t
where t.xm_Tno18=d.xm_Tno18
end
begin
update Xum_Teac_Class18
set xm_Tno18=i.xm_Tno18
from deleted d,inserted i,Xum_Teac_Class18 c
where c.xm_Tno18=d.xm_Tno18
End
（5）学生添加 自动注册新用户 默认密码12345
create trigger 插入创建学生新用户18
on Xum_Students18
for insert
as
declare @sno char(10)
begin
select @sno=xm_Sno18 from inserted
insert into Students_account18
values(@sno,'12345')
End
（6）教师添加 自动注册新用户 默认密码12345
create trigger 插入创建教师新用户18
on Xum_Teachers18
for insert
as
declare @tno char(10)
begin
select @tno=xm_Tno18 from inserted
insert into Teachers_account18
values(@tno,'12345')
End
- 建立存储过程
（1）插入新学生
create procedure pro_插入新学生
@sno char(12),
@sname char(10),
@ssex char(5),
@sage int,
@sorig char(20),
@ssum float,
@clno char(10)
as
insert Xum_Students18(xm_Sno18,xm_Sname18,xm_Ssex18,xm_Sage18
,xm_Sorig18,xm_Ssum18,xm_Clno18)
values(@sno,@sname,@ssex,@sage,@sorig,@ssum,@clno)
select *
from Xum_Students18
Go
（2）自动算学分
create procedure pro_输入成绩自动生成学分
@sno char(12),
@cno char(10),
@cname char(10),
@cdate char(10),
@score int,
@tname char(10),
@credit float
as
begin
insert into pro_选修
values(@sno,@cno,@score)
update Xum_Students18
set xm_Ssum18=xm_Ssum18+@credit
where Xum_Students18.xm_Sno18=@sno
End
6. 前台界面与后台数据库连接说明，代码实现。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
class sqlConnect
{
public SqlConnection conn = null;
public sqlConnect()
{
if (conn == null)
{
string connectString = "Data Source =LAPTOP-FPCD1SM8\\SQL2014; database =XumMIS18; " +
"Integrated Security = True";
conn = new SqlConnection(connectString);
if (conn.State == ConnectionState.Closed) conn.Open();
}
}
public void closeConnect()
{
if (conn.State == ConnectionState.Closed) conn.Close();
}
public DataSet Getds(string sql)
{
if (conn.State == ConnectionState.Closed) conn.Open();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.Fill(ds);
conn.Close();
return ds;
}
public int OperateData(string sql)
{
if (conn.State == ConnectionState.Closed) conn.Open();
SqlCommand sqlcom = new SqlCommand();
sqlcom.CommandText = sql;
sqlcom.CommandType = CommandType.Text;
sqlcom.Connection = conn;
int x = sqlcom.ExecuteNonQuery();
conn.Close();
return x;
}
public DataSet BindDataGridView(DataGridView dgv, string sql)
{
if (conn.State == ConnectionState.Closed) conn.Open();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dgv.DataSource = ds.Tables[0];
return ds;
}
}
}
四、实验报告注意事项及要求
通过这次课程设计发现这其中需要的很多知识我们没有接触过，还有很多需要我们掌握的东西我们不明白。同时也发现有很多已经学过的东西我们没有理解到位，不能灵活运用于实际，不能很好的用来解决问题，这就需要我们不断的大量的实践，通过不断的自学，不断地发现问题，思考问题，进而解决问题。
标签：10,xm,数据库,编程,char,Tno18,6.3,Xum,create From： https://www.cnblogs.com/yindantong/p/18258726