6.3实验五 数据库编程

实验五 数据库编程

一、实验目的

熟悉并掌握嵌入式 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. 建立存储过程

（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;

 

 

        }

    }

}

 

四、实验报告注意事项及要求

通过这次课程设计发现这其中需要的很多知识我们没有接触过，还有很多需要我们掌握的东西我们不明白。同时也发现有很多已经学过的东西我们没有理解到位，不能灵活运用于实际，不能很好的用来解决问题，这就需要我们不断的大量的实践，通过不断的自学，不断地发现问题，思考问题，进而解决问题。