数据库系统设计综合实验(学生管理系统)

实验3 数据库系统设计综合实验 实验目的 通过实验，使学生掌握数据库系统设计和开发的一般方法，能够设计并实现简单的数据库系统。 实验原理 本实验的任务是设计并实现一个数据库系统。数据库设计的一般步骤包括：需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库运行和维护。 (1) 概念结构设计 了解概念结构设计的基本方法，根据需求分析的结果或实验题目给出的要求，能够准确地用实体联系图来描述实体和实体之间的联系。 (2) 逻辑结构设计 理解逻辑结构设计的基本方法，根据实体联系图的设计，转换成合理的关系模式，每个关系模式至少应该满足第三范式的要求。 (3) 物理结构设计 理解物理结构设计的基本方法，选择合理的索引结构和存储结构，优化数据库的存取。 (4) 数据库实施 选择一门熟悉的面向对象程序设计语言，完成应用程序的开发。 使用仪器、材料 Oracle 11g，windows10； 实验步骤 假设有“教师”、“学生”、“课程”三个实体，教师的基本信息包括：工号、姓名、职称、工资，课程的基本信息包括：课程号、课程名、学分数，学生的基本信息包括：学号、姓名、性别、年龄。系统必须满足以下要求： (1) 一门课程只能有一个教师任课，一个教师可以上多门课程； (2) 一个学生可以选修多门课程，一门课程可以由多个学生来选修，记录不同学生选修不同课程的成绩； (3) 设置一个管理员，用于维护（添加、删除和修改等基本任务）学生基本信息、教师基本信息和教师所授课程等工作，此外，管理员添加学生时，为其设置初始密码；当学生选修了某门课程，课程成绩由管理员录入； (4) 学生可以利用学号和密码登录系统，登陆系统后，可以进行选课、修改密码和个人基本信息、查询自己的选课及总学分等操作； (5) 能够统计不同职称的教师的数量、不同职称的教师的平均工资，可以统计每门课程的平均成绩、最高分、最低分，统计每个学生选修课程的总学分； 根据上述描述，解答下列问题： (1) 设计并画出E-R 图，要求标注连通词（即联系类型）； (2) 将E-R 图转化为关系模型，并指出各关系的主码和外码； (3) 在MySql、SQL Server、Oracle 中选择一个数据库管理系统，并完成数据库的逻辑设计； 实验过程原始记录(实验过程、数据、图表、计算等) （1）E-R图如下：   （2）关系模型如下： teacher(tno,tname,tposition,tsalary,pwd),其中，tno为teacher表的主码 course(cno,cname,ccredit,tno),其中，cno为course表的主码，tno为外码 student(sno,sname,ssex,sage,sdept,pwd),其中，sno为student表的主码 sc(sno,con,grade)，其中，(sno,cno)为sc表的主码，sno为外码，参照student(sno),cno为外码，参照course(cno) （3）选择Oracle数据库管理系统，并设计数据库的物理结构； 1.创建用户muzi，密码为muzi create user aaa identified by aaa; grant connect,resource,UNLIMITED TABLESPACE to aaa; grant create view to aaa; 创建表student、course、sc、teacher并向表中插入数据 create table student (    Sno VARCHAR(9) PRIMARY KEY,/*列级完整性约束条件*/    Sname VARCHAR(20) UNIQUE,    Ssex VARCHAR(3),    Sage SMALLINT,    Sdept VARCHAR(20) ); select* from student; insert into student(sname,ssex,sno,sage,sdept)VALUES('一一','男','20211224',18,'CS'); insert into student(sname,ssex,sno,sage,sdept)VALUES('二二','女','20211225',19,'CS'); insert into student(sname,ssex,sno,sage,sdept)VALUES('三三','男','20211226',20,'MA'); insert into student(sname,ssex,sno,sage,sdept)VALUES('四四','女','20211227',19,'IS'); insert into student(sname,ssex,sno,sage,sdept,pwd)VALUES('五五','男','20211228',19,'IS','123456'); create table course (    Cno VARCHAR(4) PRIMARY KEY,    Cname VARCHAR(40),    Cpno VARCHAR(4),    Ccredit SMALLINT,    FOREIGN KEY(Cpno) REFERENCES Course(Cno),    tno varchar(7) not null,    foreign key (tno) references teacher(tno) ); insert into course values('6','数据处理',null,2,'t001'); insert into course values('2','数学',null,2,'t002'); insert into course values('7','PASCAL语言','6',4,'t003'); insert into course values('5','数据结构','7',4,'t002'); insert into course values('1','数据库','5',4,'t003'); insert into course values('3','信息系统','1',4,'t004'); insert into course values('4','操作系统','6',3,'t005'); select* from course order by Cno; create TABLE SC (    Sno VARCHAR(9),    Cno VARCHAR(4),    Grade SMALLINT,    FOREIGN KEY (Sno) REFERENCES Student(Sno),    FOREIGN KEY (Cno) REFERENCES Course(Cno) ); insert into sc values('20211224','1',92); insert into sc values('20211224','2',85); insert into sc values('20211224','3',88); insert into sc values('20211225','2',90); insert into sc values('20211225','3',80); insert into sc values('20211228','7',80); insert into sc values('20211228','2',80); insert into sc values('20211226','2',80); insert into sc values('20211227','2',50); select* from sc; create table teacher (   tno varchar(7) primary key,/*列级完整性约束条件*/   tname varchar(20) not null unique,   tposition varchar(20) not null,   tsalary smallint not null,   pwd varchar(20) not null ); insert into teacher(tno,tname, tposition, tsalary, pwd) values('t001','张三','教授',30000,'123456'); insert into teacher(tno,tname, tposition, tsalary, pwd) values('t002','李四','副教授',35000,'123456'); insert into teacher(tno,tname, tposition, tsalary, pwd) values('t003','熊猫','教授',45000,'123456'); insert into teacher(tno,tname, tposition, tsalary, pwd) values('t004','王小二','副教授',38000,'123456'); insert into teacher(tno,tname, tposition, tsalary, pwd) values('t005','李小小','副教授',42000,'123456'); insert into teacher(tno,tname, tposition, tsalary, pwd) values('t006','李小龙','教授',60000,'123456'); select *from teacher; 3.创建视图studentinfo、courseinfo、teacherinfo1、teacherinfo2并提交 create view courseinfo as select course.cno 课程编号,course.cname 课程名称,teacher.tname 任课老师,course.ccredit 课程学分,countnum 选修人数,avg_grade 平均分,max_grade 最高分,min_grade 最低分 from teacher,course left outer join (select cname, count(*) countnum,Round(avg(grade),1)  avg_grade,max(grade)  max_grade,min(grade) min_grade from sc,course where course.cno=sc.cno group by cname)a1 on (course.cname=a1.cname) where teacher.tno=course.tno; select *from courseinfo; create view studentinfo as select student.sno 学号,student.sname 姓名,(select sum(grade) from sc where sno=student.sno) 总分 ,(select sum(ccredit) from course where cno in (select cno from sc where sno=student.sno)) 总学分 ,(select max(grade)from sc where sc.sno in (student.sno)) 最高分,(select min(grade) from sc where sc.sno in (student.sno)) 最低分 from student; select *from studentinfo; create view teacherinfo1 as select tposition 职位,count(tno) 在任人数,Round(avg(tsalary),1) 平均工资 from teacher group by tposition; create view teacherinfo2 as select tno 教师工号,tname 教师名称,(select count(student.sno) from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and course.tno=teacher.tno) 授课学生总人数 from teacher; select * from teacherinfo1; select * from teacherinfo2; commit; 选择一门熟悉的面向对象程序设计语言，完成系统开发。 部分c#代码 MainForm.cs namespace DbApp {     public partial class MainForm : Form     {         public MainForm()         {             InitializeComponent();         }         private void 查询学生ToolStripMenuItem_Click(object sender, EventArgs e)         {             FormStuQuary frm = new FormStuQuary();             frm.MdiParent = this;//表示是主窗体的一个子窗体             frm.Show();         }         private void 添加学生ToolStripMenuItem_Click(object sender, EventArgs e)         {             FormStuInsert frm = new FormStuInsert();             frm.MdiParent = this;             frm.Show();         }         private void 查询课程ToolStripMenuItem_Click(object sender, EventArgs e)         {             FormCourseQuery frm = new FormCourseQuery();             frm.MdiParent = this;             frm.Show();         }         private void 添加课程ToolStripMenuItem_Click(object sender, EventArgs e)         {             FormCourseInsert frm= new FormCourseInsert();             frm.MdiParent = this;             frm.Show();         }         private void 统计ToolStripMenuItem_Click(object sender, EventArgs e)         {             FormStatistics frm = new FormStatistics();             frm.MdiParent = this;             frm.Show();         }         private void MainForm_Load(object sender, EventArgs e)         {             this.Text = "学生信息管理系统, 当前的登录用户名是: " + FormLogin.loginName;             if (FormLogin.person == "学生")             {                 学生管理ToolStripMenuItem.Visible = false;                 教师管理ToolStripMenuItem.Visible = false;                 添加课程ToolStripMenuItem.Visible = false;                 统计信息ToolStripMenuItem.Visible = false;                 成绩信息ToolStripMenuItem.Visible = false;             }             if (FormLogin.person == "教师")             {                 学生管理ToolStripMenuItem.Visible = false;                 教师管理ToolStripMenuItem.Visible = false;                 添加课程ToolStripMenuItem.Visible = false;             }             if (FormLogin.person == "管理员")             {                 学生管理ToolStripMenuItem.Visible = false;                 个人中心ToolStripMenuItem.Visible = false;             }         }         private void 查询教师ToolStripMenuItem_Click(object sender, EventArgs e)         {             FormTeaQuary frm = new FormTeaQuary();             frm.MdiParent = this;             frm.Show();         }         private void 添加教师ToolStripMenuItem_Click(object sender, EventArgs e)         {             FormTeaInsert frm = new FormTeaInsert();             frm.MdiParent = this;             frm.Show();         }         private void 成绩信息ToolStripMenuItem_Click(object sender, EventArgs e)         {             FormGradeQuery frm = new FormGradeQuery();             frm.MdiParent = this;             frm.Show();         }         private void 个人信息ToolStripMenuItem_Click(object sender, EventArgs e)         {             FormOwn frm = new FormOwn();             frm.MdiParent = this;             frm.Show();         }     } } Program.cs static void Main()         {             Application.EnableVisualStyles();             Application.SetCompatibleTextRenderingDefault(false);             FormLogin frm = new FormLogin();             if (frm.ShowDialog() == DialogResult.OK)                 Application.Run(new MainForm());             else                 Application.Exit();         } FormStuQuery.cs using Oracle.ManagedDataAccess.Client; private void button1_Click(object sender, EventArgs e)         {             string sql = string.Format("select sno,sname,ssex,sage,sdept,pwd from student where sname like '{0}%'"                 , textBox1.Text);             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 OracleDataReader odr = cmd.ExecuteReader();                 if (odr.HasRows)                 {                     BindingSource bs = new BindingSource();                     bs.DataSource = odr;                     dataGridView1.DataSource = bs;                 }                 else dataGridView1.DataSource = null;             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         }         private void button2_Click(object sender, EventArgs e)         {             if (dataGridView1.SelectedRows.Count == 0) return;             if (MessageBox.Show("是否删除数据", "请确认信息", MessageBoxButtons.OKCancel) == DialogResult.Cancel)                 return;             DataGridViewRow row = dataGridView1.SelectedRows[0];             string sno = row.Cells[0].Value.ToString();             string sql = string.Format("delete from student where sno='{0}'", sno);             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 if (1 == cmd.ExecuteNonQuery())                 {                     MessageBox.Show("删除成功");                     dataGridView1.Rows.Remove(row);                 }                 else                     MessageBox.Show("没有找到对应学号的学生");             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         }         private void dataGridView1_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)         {             if (dataGridView1.SelectedRows.Count == 0) return;             FormStuUpdate frm = new FormStuUpdate();             DataGridViewRow row = dataGridView1.SelectedRows[0];             frm.tbSno.Text = row.Cells[0].Value.ToString();             frm.tbSname.Text = row.Cells[1].Value.ToString();             frm.tbSsex.Text = row.Cells[2].Value.ToString();             frm.tbSage.Text = row.Cells[3].Value.ToString();             frm.tbSdept.Text = row.Cells[4].Value.ToString();             frm.tbPwd.Text = row.Cells[5].Value.ToString();             frm.ShowDialog(this);         } FormStuUpdate.cs using Oracle.ManagedDataAccess.Client; private void button1_Click(object sender, EventArgs e)         {             string sql = string.Format("update student set sname='{0}',ssex='{1}',sage={2},sdept='{3}',pwd='{4}' where sno='{5}'",tbSname.Text, tbSsex.Text, tbSage.Text, tbSdept.Text,tbPwd.Text,tbSno.Text);             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 if (1 == cmd.ExecuteNonQuery())                 {                     MessageBox.Show("修改成功");                     this.Close();                 }                 else                     MessageBox.Show("没有找到对应学号的学生");             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         } FormStuInsert.cs using Oracle.ManagedDataAccess.Client; namespace DbApp {     public partial class FormStuInsert : Form     {         private void button1_Click(object sender, EventArgs e)         {             string sql = string.Format("insert into student (sno,sname,ssex,sage,sdept,pwd) values('{0}','{1}','{2}','{3}','{4}','{5}')",                 tbSname.Text, tbSsex.Text, tbSage.Text, tbSdept.Text, tbSno.Text, tbPwd.Text);             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 if (1 == cmd.ExecuteNonQuery())                 {                     MessageBox.Show("插入成功");                     //this.Close();                 }                 else                     MessageBox.Show("没有找到对应学号的学生");             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         }     } } Course.cs using Oracle.ManagedDataAccess.Client; namespace DbApp {     public class Course     {         public string Cno { get; set; }         public string Cname { get; set;}         public string Cpno { get; set; }         public int Ccredit { get; set; }         public string Tno { get; set; }         public static List<Course> SelectCourse(string cname)         {             List<Course> list = new List<Course>();             string sql = "select cno,cname,cpno,ccredit,tno from course where cname like :cname";             OracleParameter[] para = new OracleParameter[]             {                 new OracleParameter(":cname",OracleDbType.Varchar2,40)             };             para[0].Value = cname + "%";             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql,con);                 cmd.Parameters.AddRange(para);                 OracleDataReader odr = cmd.ExecuteReader();                 while (odr.Read())                 {                     Course c = new Course();                     c.Cno = odr.GetString(0);                     c.Cname = odr.GetString(1);                     if (odr.IsDBNull(2)) c.Cpno = null;                     else c.Cpno = odr.GetString(2);                     c.Ccredit = odr.GetInt16(3);                     c.Tno = odr.GetString(4);                     list.Add(c);                 }             }             finally             {                 con.Close();             }             return list;         }         public static int DeleteCourse(string cno)         {             int result = 0;             string sql = "delete from course where cno=:cno";             OracleParameter[] para = new OracleParameter[]             {                 new OracleParameter(":cno",OracleDbType.Varchar2,4)             };             para[0].Value = cno;             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery();             }             finally             {                 con.Close();             }             return result;         }         public static int UpdateCourse(Course c)         {             string sql = "update course set cname=:cname,cpno=:cpno,ccredit=:ccredit,tno=:tno where cno=:cno";             OracleParameter[] para = new OracleParameter[]             {                 new OracleParameter(":cname",OracleDbType.Varchar2,40),                 new OracleParameter(":cpno",OracleDbType.Varchar2,4),                 new OracleParameter(":ccredit",OracleDbType.Int16),                 new OracleParameter(":tno",OracleDbType.Varchar2,7),                 new OracleParameter(":cno",OracleDbType.Varchar2,4)               };             para[0].Value = c.Cname;             if (string.IsNullOrEmpty(c.Cpno))                 para[1].Value = DBNull.Value;             else para[1].Value = c.Cpno;             para[2].Value = c.Ccredit;             para[3].Value = c.Tno;             para[4].Value = c.Cno;             int result = 0;             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery();             }             finally             {                 con.Close();             }             return result;         }         public static int InsertCourse(Course c)         {             string sql = "insert into course (cno,cname,cpno,ccredit,tno) values(:cno,:cname,:cpno,:ccredit,:tno)";             OracleParameter[] para = new OracleParameter[]             {                 new OracleParameter(":cno",OracleDbType.Varchar2,4),                 new OracleParameter(":cname",OracleDbType.Varchar2,40),                 new OracleParameter(":cpno",OracleDbType.Varchar2,4),                 new OracleParameter(":ccredit",OracleDbType.Int16),                 new OracleParameter(":tno",OracleDbType.Varchar2,7)             };             para[0].Value = c.Cno;             para[1].Value = c.Cname;             if (string.IsNullOrEmpty(c.Cpno))                 para[2].Value = DBNull.Value;             else para[2].Value = c.Cpno;             para[3].Value = c.Ccredit;             para[4].Value = c.Tno;             int result = 0;             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 cmd.Parameters.AddRange(para);                 result = cmd.ExecuteNonQuery();             }             finally             {                 con.Close();             }             return result;         }     } } FormCourseQuery.cs private void button1_Click(object sender, EventArgs e)         {             try             {                 this.dataGridView1.DataSource = Course.SelectCourse(this.textBox1.Text);             }             catch(Exception ex)             {                 MessageBox.Show(ex.Message);             }         }         private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)         {             if (e.RowIndex < 0) return;             DataGridViewRow row = dataGridView1.Rows[e.RowIndex];             Course c = (Course)row.DataBoundItem;             try             {                 if (e.ColumnIndex == 5)                 {                     if (MessageBox.Show("是否删除数据", "请确认信息",MessageBoxButtons.OKCancel)=DialogResult.Cancel)                         return;                     if (1 == Course.DeleteCourse(c.Cno))                         MessageBox.Show("删除成功");                     else MessageBox.Show("没有找到对应课程号的课程");                 }                 else if (e.ColumnIndex == 6)                 {                     FormCourseUpdate frm = new FormCourseUpdate();                     frm.tbCno.Text = c.Cno;                     frm.tbCname.Text = c.Cname;                     frm.tbCpno.Text = c.Cpno;                     frm.tbCcredit.Text = c.Ccredit.ToString();                     frm.tbTno.Text = c.Tno;                     frm.ShowDialog(this);                 }             }             catch(Exception ex)             {                 MessageBox.Show(ex.Message);             } } FormCourseUpdate.cs private void button1_Click(object sender, EventArgs e)         {             Course c = new Course();             c.Cno = tbCno.Text;             c.Cname = tbCname.Text;             c.Cpno = tbCpno.Text;             c.Ccredit = Convert.ToInt32(tbCcredit.Text);             c.Tno = tbTno.Text;             try             {                 if (Course.UpdateCourse(c) == 1)                 {                     MessageBox.Show("修改成功");                     this.Close();                 }                 else MessageBox.Show("没有找到对应课程号的课程");             }             catch(Exception ex)             {                 MessageBox.Show(ex.Message);             }         } FormCourseInsert.cs private void button1_Click(object sender, EventArgs e)         {             Course c = new Course();             c.Cno = tbCno.Text;             c.Cname = tbCname.Text;             c.Cpno = tbCpno.Text;             c.Ccredit = Convert.ToInt32(tbCcredit.Text);             c.Tno = tbTno.Text;             try             {                 Course.InsertCourse(c);                 MessageBox.Show("添加成功");             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }         } FormTeaQuery.cs using Oracle.ManagedDataAccess.Client; namespace DbApp {     public partial class FormTeaQuary : Form     {         public FormTeaQuary()         {             InitializeComponent();         }         private void button1_Click(object sender, EventArgs e)         {             string sql = string.Format("select tno,tname,tposition,tsalary,pwd from teacher where tname like '{0}%'"                 , textBox1.Text);             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 OracleDataReader odr = cmd.ExecuteReader();                 if (odr.HasRows)                 {                     BindingSource bs = new BindingSource();                     bs.DataSource = odr;                     dataGridView1.DataSource = bs;                 }                 else dataGridView1.DataSource = null;             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         }         private void button2_Click(object sender, EventArgs e)         {             if (dataGridView1.SelectedRows.Count == 0) return;             if (MessageBox.Show("是否删除数据", "请确认信息", MessageBoxButtons.OKCancel) == DialogResult.Cancel)                 return;             DataGridViewRow row = dataGridView1.SelectedRows[0];             string sno = row.Cells[0].Value.ToString();             string sql = string.Format("delete from teacher where tno='{0}'", sno);             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 if (1 == cmd.ExecuteNonQuery())                 {                     MessageBox.Show("删除成功");                     dataGridView1.Rows.Remove(row);                 }                 else                     MessageBox.Show("没有找到对应工号的教师");             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         }         private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)         {             if (dataGridView1.SelectedRows.Count == 0) return;             FormTeaUpdate frm = new FormTeaUpdate();             DataGridViewRow row = dataGridView1.SelectedRows[0];             frm.tbTno.Text = row.Cells[0].Value.ToString();             frm.tbTname.Text = row.Cells[1].Value.ToString();             frm.tbTpo.Text = row.Cells[2].Value.ToString();             frm.tbTsal.Text = row.Cells[3].Value.ToString();             frm.tbPwd.Text = row.Cells[4].Value.ToString();             frm.ShowDialog(this);         }     } } FormTeaUpdate.cs using Oracle.ManagedDataAccess.Client; namespace DbApp {         private void button1_Click(object sender, EventArgs e)         {             string sql = string.Format("update student set tname='{0}',tposition='{1}',tsalary={2},pwd='{3}' where tno='{4}'",                 tbTname.Text, tbTpo.Text, tbTsal.Text, tbPwd.Text, tbTno.Text);             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 if (1 == cmd.ExecuteNonQuery())                 {                     MessageBox.Show("修改成功");                     this.Close();                 }                 else                     MessageBox.Show("没有找到对应工号的教师");             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         }     } } FormTeaInsert.cs using Oracle.ManagedDataAccess.Client; namespace DbApp {         private void button1_Click(object sender, EventArgs e)         {             string sql = string.Format("insert into teacher (tno,tname,tposition,tsalary,pwd) values('{0}','{1}','{2}','{3}','{4}')",                 tbTno.Text, tbTname.Text, tbTpo.Text, tbTsal.Text, tbPwd.Text);             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 if (1 == cmd.ExecuteNonQuery())                 {                     MessageBox.Show("添加成功");                     //this.Close();                 }                 else                     MessageBox.Show("没有找到对应工号的教师");             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         }     } } FromGradeQuery.cs using Oracle.ManagedDataAccess.Client; namespace DbApp {         private void button1_Click(object sender, EventArgs e)         {             string sql = string.Format("select sno,cno,grade from sc where sno like '{0}%'", textBox1.Text);             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 OracleDataReader odr = cmd.ExecuteReader();                 if (odr.HasRows)                 {                     BindingSource bs = new BindingSource();                     bs.DataSource = odr;                     dataGridView1.DataSource = bs;                 }                 else dataGridView1.DataSource = null;             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         }         private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)         {             if (dataGridView1.SelectedRows.Count == 0) return;             FormGradeUpdate frm = new FormGradeUpdate();             DataGridViewRow row = dataGridView1.SelectedRows[0];             frm.tbSno.Text = row.Cells[0].Value.ToString();             frm.tbCno.Text = row.Cells[1].Value.ToString();             frm.tbGrade.Text = row.Cells[2].Value.ToString();             frm.ShowDialog(this);         } } FormGradeUpdate.cs using Oracle.ManagedDataAccess.Client; namespace DbApp { public partial class FormOwn : Form     {         private void button1_Click(object sender, EventArgs e)         {             string sql = string.Format("update sc set grade={0} where sno='{1}' and cno='{2}'",                 tbGrade.Text,tbSno.Text,tbCno.Text);             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 if (1 == cmd.ExecuteNonQuery())                 {                     MessageBox.Show("修改成功");                     this.Close();                 }                 else                     MessageBox.Show("没有找到对应学号的学生");             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         }     } } FormOwn.cs using Oracle.ManagedDataAccess.Client; namespace DbApp {     public partial class FormOwn : Form     {         private void button1_Click(object sender, EventArgs e)         {             if (FormLogin.person == "学生")             {                 string sql = string.Format("select student.sno,sname,ssex,sage,sdept,pwd,cno,grade from student,sc where student.sno='{0}' and sc.sno='{0}'"                 , FormLogin.loginName);                 OracleConnection con = new OracleConnection(Program.strCon);                 try                 {                     con.Open();                     OracleCommand cmd = new OracleCommand(sql, con);                     OracleDataReader odr = cmd.ExecuteReader();                     if (odr.HasRows)                     {                         BindingSource bs = new BindingSource();                         bs.DataSource = odr;                         dataGridView1.DataSource = bs;                     }                     else dataGridView1.DataSource = null;                 }                 catch (Exception ex)                 {                     MessageBox.Show(ex.Message);                 }                 finally                 {                     con.Close();                 }             }             else if (FormLogin.person == "教师")             {                 string sql = string.Format("select tno,tname, tposition, tsalary, pwd from teacher where teacher.tno='{0}'", FormLogin.loginName);                 OracleConnection con = new OracleConnection(Program.strCon);                 try                 {                     con.Open();                     OracleCommand cmd = new OracleCommand(sql, con);                     OracleDataReader odr = cmd.ExecuteReader();                     if (odr.HasRows)                     {                         BindingSource bs = new BindingSource();                         bs.DataSource = odr;                         dataGridView1.DataSource = bs;                     }                     else dataGridView1.DataSource = null;                 }                 catch (Exception ex)                 {                     MessageBox.Show(ex.Message);                 }                 finally                 {                     con.Close();                 }             }         }         private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)         {             if (dataGridView1.SelectedRows.Count == 0) return;             FormOwnUpdate frm = new FormOwnUpdate();             DataGridViewRow row = dataGridView1.SelectedRows[0];             frm.tbSno.Text = row.Cells[0].Value.ToString();             if (FormLogin.person == "学生")                 frm.tbPwd.Text = row.Cells[5].Value.ToString();             else                 frm.tbPwd.Text = row.Cells[4].Value.ToString();             frm.ShowDialog(this);         }     } } FormOwnUpdate.cs using Oracle.ManagedDataAccess.Client; namespace DbApp {     public partial class FormOwnUpdate : Form     {         private void button1_Click(object sender, EventArgs e)         {             if (FormLogin.person == "学生")             {                 string sql = string.Format("update student set pwd='{0}' where sno='{1}'",                  tbPwd.Text, tbSno.Text);                 OracleConnection con = new OracleConnection(Program.strCon);                 try                 {                     con.Open();                     OracleCommand cmd = new OracleCommand(sql, con);                     if (1 == cmd.ExecuteNonQuery())                     {                         MessageBox.Show("修改成功");                         this.Close();                     }                     else                         MessageBox.Show("没有找到对应学号的学生");                 }                 catch (Exception ex)                 {                     MessageBox.Show(ex.Message);                 }                 finally                 {                     con.Close();                 }             }             else             {                 string sql = string.Format("update teacher set pwd='{0}' where tno='{1}'",tbPwd.Text, tbSno.Text);                 OracleConnection con = new OracleConnection(Program.strCon);                 try                 {                     con.Open();                     OracleCommand cmd = new OracleCommand(sql, con);                     if (1 == cmd.ExecuteNonQuery())                     {                         MessageBox.Show("修改成功");                         this.Close();                     }                     else                         MessageBox.Show("没有找到对应工号的教师");                 }                 catch (Exception ex)                 {                     MessageBox.Show(ex.Message);                 }                 finally                 {                     con.Close();                 }             }         }     } } FormStatistics.cs using Oracle.ManagedDataAccess.Client; namespace DbApp {     public partial class FormStatistics : Form     {         private void button1_Click(object sender, EventArgs e)         {             string sql = "select* from studentinfo";             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 OracleDataReader odr = cmd.ExecuteReader();                 if (odr.HasRows)                 {                     BindingSource bs = new BindingSource();                     bs.DataSource = odr;                     dataGridView1.DataSource = bs;                 }                 else dataGridView1.DataSource = null;             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         }         private void button2_Click(object sender, EventArgs e)         {             string sql = "select* from courseinfo";             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 OracleDataReader odr = cmd.ExecuteReader();                 if (odr.HasRows)                 {                     BindingSource bs = new BindingSource();                     bs.DataSource = odr;                     dataGridView1.DataSource = bs;                 }                 else dataGridView1.DataSource = null;             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         }         private void button3_Click(object sender, EventArgs e)         {             string sql = "select * from teacherinfo2";             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 OracleDataReader odr = cmd.ExecuteReader();                 if (odr.HasRows)                 {                     BindingSource bs = new BindingSource();                     bs.DataSource = odr;                     dataGridView1.DataSource = bs;                 }                 else dataGridView1.DataSource = null;             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         }         private void button4_Click(object sender, EventArgs e)         {             string sql = "select * from teacherinfo1";             OracleConnection con = new OracleConnection(Program.strCon);             try             {                 con.Open();                 OracleCommand cmd = new OracleCommand(sql, con);                 OracleDataReader odr = cmd.ExecuteReader();                 if (odr.HasRows)                 {                     BindingSource bs = new BindingSource();                     bs.DataSource = odr;                     dataGridView1.DataSource = bs;                 }                 else dataGridView1.DataSource = null;             }             catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }             finally             {                 con.Close();             }         }     } } FormLogin.cs using Oracle.ManagedDataAccess.Client; namespace DbApp {     public partial class FormLogin : Form     {         public static string loginName = null;         public FormLogin()         {             InitializeComponent();         }         private void button1_Click(object sender, EventArgs e)         {             string username = tbUsername.Text;             string pwd = tbPwd.Text;             if (radioButton1.Checked)             {                 person = "学生";                 string sql = "select sno,sname from student where sno=:sno and pwd=:pwd";                 OracleParameter[] para = new OracleParameter[]                 {                 new OracleParameter(":sno",OracleDbType.Varchar2,9),                 new OracleParameter(":pwd",OracleDbType.Varchar2,20)                 };                 para[0].Value = username;                 para[1].Value = pwd;                 OracleConnection con = new OracleConnection(Program.strCon);                 try                 {                     con.Open();                     OracleCommand cmd = new OracleCommand(sql, con);                     cmd.Parameters.AddRange(para);                     OracleDataReader odr = cmd.ExecuteReader();                     if (odr.Read())                     {                         loginName = odr.GetString(0);                         this.DialogResult = DialogResult.OK;                     }                     else MessageBox.Show("用户名或者密码错误");                 }                 catch (Exception ex)                 {                     MessageBox.Show(ex.Message);                 }                 finally                 {                     con.Close();                 }             }             else if (radioButton2.Checked)             {                 person = "教师";                 string sql = "select tno,tname from teacher where tno=:tno and pwd=:pwd";                 OracleParameter[] para = new OracleParameter[]                 {                 new OracleParameter(":tno",OracleDbType.Varchar2,9),                 new OracleParameter(":pwd",OracleDbType.Varchar2,20)                 };                 para[0].Value = username;                 para[1].Value = pwd;                 OracleConnection con = new OracleConnection(Program.strCon);                 try                 {                     con.Open();                     OracleCommand cmd = new OracleCommand(sql, con);                     cmd.Parameters.AddRange(para);                     OracleDataReader odr = cmd.ExecuteReader();                     if (odr.Read())                     {                         loginName = odr.GetString(0);                         this.DialogResult = DialogResult.OK;                     }                     else MessageBox.Show("用户名或者密码错误");                 }                 catch (Exception ex)                 {                     MessageBox.Show(ex.Message);                 }                 finally                 {                     con.Close();                 }             }             else if (radioButton3.Checked && username == "1" && pwd == "1")             {                 person = "管理员";                 loginName = "管理员";                 this.DialogResult = DialogResult.OK;             }         }     } } 实验结果及分析 1：首先管理员登录，进行添加学生，添加教师，添加课程等工作。   （1）管理员添加学生     （2）查询学生1 可以修改学生信息；   （3）可以删除学生   （4）查询教师信息   （5）添加教师   （6）修改教师信息：   （7）查询课程   （8）添加课程   （9）修改课程     （10）统计信息查看：     2：学生登录 （1）学生没有教师管理、添加删除课程以及学生管理选项，只能查询   （2）学生可以查询个人信息   （3）学生可以修改密码：   3：教师登录： 教师没有权限进行教师管理 （2）教师可以查看自己的信息，可以查看自己教授的课程。   （3）教师可以修改密码   （4）教师可以修改对应学生的成绩   七、实验报告思考题 实验过程中遇到的问题： 在调用窗体变量时，出现报错，原因是权限不足。要在每一个相对应的.Designer.cs文件中将private改成public。 要获取输入控件的值，要设置参数形式，设置完之后要记得添加，不然就是无效的设置。 每次对Oracle数据进行修改后，要记得提交，否则修改无效。 要在窗体处显示平均值时，报错：指定的转换无效。原因是Oracle中的值为double类型，c#中的值为float类型。在生成平均值的时候，时用Round()函数即可解决问题。 八、实验心得体会 知识点：在本次实验中，有4个表，分别定义了teacher，course，student，sc类，方便查询，插入，删除，修改数据。在统计方面，定义了四个视图，分别为studentinfo，teacherinfo1，teacherinfo2，courseinfo，每一个视图也分别定义了相关的类，为了方便查询和统计。在统计的过程中，不定义类，直接将视图的内容输出的时候，会出现转换的列无效的情况，可能是在第一行的数据中有的统计出来是空值，导致列的属性无法正常输出，在将这些视图全部转换成类之后，然后将这些类的对象导入dataView1中，就可以直接输出统计的信息了，当然在从数据库读出的时候，还是需要先判断一下是否为空值。在主程序运行之前，加入一个登录界面，相同的方法，不是按一下查询键才进行查询，可以在每一个窗口弹出后，在Load事件中添加刷新窗口的功能，使得打开一个新的用户界面时就可以直接看到全部的相应的信息，当然也支持单个的查询。在登录界面中，有三个radios的按钮，一个是学生的，一个是管理员的，另外一个是老师的，学生账号需要选择学生的按钮，再用学生的账号登录，老师的也一样，这样可以有效的防止学生和老师的账号搞混，在登录的时候只需要查询一个表就可以了。由于在数据库中没有把管理员定义成一个表，所以默认管理员只有一个账号，在登录的时候特判。一开始主程序的所有的控件都是屏蔽的，登录成功后，会对应不同的用户开发不同的控件，只有管理员才有最高的权限可以查看所有的信息。在教师信息的统计中界面中，使用了两个dataview1的显示，不知道该如何把两个信息表的内容统计到一起，为了界面美观，我让两个信息表分开进行查询显示。另外还增加了一个个人中心的控件，双击相应信息栏，可以选择个人信息修改，可以查询到自己的个人信息以及修改相应的信息。 心得体会： 对于我而言，这次的综合实验难度极大。一开始我几乎完全不了解如何完成实现应用程序的开发，通过不断的查阅资料，结合实践慢慢摸索，不断修改bug，我了解了概念结构设计的基本方法，理解了逻辑结构的基本方法。对数据库的设计有了初步的认识以及手感。成功的运用了vs可视化编辑软件使用C#完成了一个简单的学生成绩管理系统。并对其功能进行了相应的拓展以及补充，使其看起来更加的实用。（使用c#，熟练掌握运用各个控件，真的非常重要，实现各种功能都要方便很多）