1. AI技术聚合首页
  2. IT

数据库课程设计——学生信息管理系统（Sqlserver，C#，Winform）

社会演员多 • • IT • 阅读 47

目录

https://github.com/2736933896/StudentSystem，报告，项目源码，数据库设计，窗体设计源码上传到github，需要的同学自行下载哦

需求分析

1.1设计可视化界面，具有身份验证功能，需要登录时输入账号及密码。

1.2学生用户能够注册自己的账号，添加自己的基本注册信息：学号、密码、姓名、性别，对于学生除了基本注册信息外，还包括学生个人信息、课程信息、学生成绩信息。

1.3学生信息、课程信息、学生成绩都能实现增删改查并且符合符合数据库完整性。

（其他图文内容省略了，需要课程报告的私聊我）

一.登录功能

466afe60f65b4aa2af953127546e3699.png

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.StartPanel;

namespace StudentSY
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 

        private void timer3_Tick(object sender, EventArgs e)
        {

           if(pictureBox2.Location.X<200)
            {
                pictureBox2.Location = new Point(pictureBox2.Location.X + 2, pictureBox2.Location.Y);
            }
           else
            {
                if(comboBox1.Text=="学生")
                {
                    Form4 form4 = new Form4();
                    form4.Show();
                }            
                timer3.Stop();
            }
        }
        private void button3_Click(object sender, EventArgs e)
        {
            if(login())
            {
                timer3.Start();
                textBox3.Visible = false;
                textBox4.Visible = false;
                comboBox1.Visible = false;
                button3.Visible = false;
                button4.Visible = false;
                button5.Visible = false;
                label4.Visible = false;
                label5.Visible = false;
                label6.Visible = false;
            }
        }

        private bool login()
        {
            if(textBox3.Text==null|| textBox4.Text==null)
            {
                MessageBox.Show("登录失败，账号或者密码为空","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning);
                return false;
            }
            else if (comboBox1.Text == "学生")
            {
                string sql = "select * from StudentUser where ID='" + textBox3.Text + "'and PassWord='" + textBox4.Text + "'";
                Dao dao = new Dao();
                IDataReader dr = dao.read(sql);
                if(dr.Read())
                {
                    return true;
                }
                else
                {
                    MessageBox.Show("登录失败！账号或者密码错误，请重试");
                    return false;
                }
            }
            return false;
        }
        private void button4_Click(object sender, EventArgs e)
        {
            textBox3.Text = null;
            textBox4.Text = null;
            comboBox1.Text = null;
        }

        private void button5_Click(object sender, EventArgs e)
        {
            Form11 form11 = new Form11();
            form11.ShowDialog();
        }
        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void label7_Click(object sender, EventArgs e)
        {

        }

        
    }
}

二.注册功能

4925b662081644f093b9884ed9ea886f.png

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace StudentSY
{
    public partial class Form11 : Form
    {
        public Form11()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            if(textBox1.Text==""|| textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "" )
            {
                MessageBox.Show("输入不完整，有空项，请检查！","提示",MessageBoxButtons.OK,MessageBoxIcon.Warning);
            }
            else
            {
                string sql= "insert into StudentUser values('" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "')";
                Dao dao = new Dao();
                int i = dao.Excute(sql);
                if(i>0)
                {
                    MessageBox.Show("添加成功");
                    this.Close();
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            textBox1.Text = null;
            textBox2.Text = null;
            textBox3.Text = null;
            textBox4.Text = null;
        }
    }
}

三.管理员登录后跳转到功能页面：

2a88685f56604ba3a6f319ed4144d002.png

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace StudentSY
{
    public partial class Form4 : Form
    {
        public Form4()
        {
            InitializeComponent();
            toolStripStatusLabel3.Text = DateTime.Now.ToString("G");
            timer1.Start();
        }
        private void timer1_Tick(object sender, EventArgs e)
        {
            toolStripStatusLabel3.Text = DateTime.Now.ToString("G");
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Form2 form2 = new Form2();
            form2.ShowDialog();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Form3 form3 = new Form3();
            form3.ShowDialog();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            Form31 form31 = new Form31();
            form31.ShowDialog();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
    }
}

四.学生信息管理（主界面，删除功能在主界面代码中）

de37171b90cd4204a755824e19852a0c.png

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace StudentSY
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
            toolStripStatusLabel3.Text = DateTime.Now.ToString("G");
            timer1.Start();
            Table();
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            toolStripStatusLabel3.Text = DateTime.Now.ToString("G");
        }

        public  void Table() //读取数据
        {
            string sql = "select * from Student";
            Dao dao = new Dao();
            IDataReader dr = dao.read(sql);
            while(dr.Read())
            {
                string Sno;
                string Sname;
                string Ssex, Sage, Sdept;
                Sno = dr["Sno"].ToString();
                Sname = dr["Sname"].ToString();
                Ssex = dr["Ssex"].ToString();
                Sage = dr["Sage"].ToString();
                Sdept = dr["Sdept"].ToString();
                string[] str = { Sno, Sname, Ssex, Sage, Sdept };
                dataGridView1.Rows.Add(str);
            }
            dr.Close();
        }
        
        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            dataGridView1.Rows[e.RowIndex].DefaultCellStyle.ForeColor = Color.Black;
        }

        private void dataGridView1_RowDefaultCellStyleChanged(object sender, DataGridViewRowEventArgs e)
        {
           ForeColor = Color.Black;
        }

        private void 添加学生ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form21 form21 = new Form21();
            form21.ShowDialog();  //开一个新窗口;
            dataGridView1.Rows.Clear();
            Table();
        }

        private void 修改学生信息ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {


                string[] str = { dataGridView1.SelectedCells[0].Value.ToString(), dataGridView1.SelectedCells[1].Value.ToString(),
                dataGridView1.SelectedCells[2].Value.ToString(),dataGridView1.SelectedCells[3].Value.ToString(),
                dataGridView1.SelectedCells[4].Value.ToString()};
                // MessageBox.Show(str[0]+str[1]+str[2]+str[3]+str[4]);
                Form21 form21 = new Form21(str);
                form21.ShowDialog();
                dataGridView1.Rows.Clear();
                Table();
            }
            catch
            {
                MessageBox.Show("未正确选中行，请重试");
            }
        }

        private void 删除学生信息ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DialogResult r = MessageBox.Show("确定要删除吗？", "提示", MessageBoxButtons.OKCancel);
            if(r==DialogResult.OK)
            {
                try
                {
                    string id, name;
                    id = dataGridView1.SelectedCells[0].Value.ToString(); //选中当前行第一列的值
                    name = dataGridView1.SelectedCells[0].Value.ToString();
                    string sql = "delete from Student where Sno=" + id;
                    Dao dao = new Dao();
                    int i = dao.Excute(sql); //执行SQL语句
                    if(i>0)
                    {
                        dataGridView1.Rows.Clear();
                        Table();
                    }
                }

                catch
                {
                    MessageBox.Show("请正确选择行");
                }
            }
           
        }

        private void toolStripButton4_Click(object sender, EventArgs e)
        {
            dataGridView1.Rows.Clear();
            Table();
        }

        private void 退出ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            添加学生ToolStripMenuItem_Click(sender, e);
        }
        private void toolStripButton3_Click(object sender, EventArgs e)
        {
            删除学生信息ToolStripMenuItem_Click(sender, e);
        }

        private void toolStripButton2_Click(object sender, EventArgs e)
        {
            修改学生信息ToolStripMenuItem_Click(sender, e);

;        }
    }
}

五.学生信息添加和修改（设计在一个页面上，修改需要选中行）

b7fd1cb73991414fa3083b608de6cbfc.png037dfb2ae2734b10bb72643d7d635050.png

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace StudentSY
{
    public partial class Form21 : Form
    {
        string []str = new string[5];
        public Form21()
        {
            InitializeComponent();
            button1.Text = "添加信息"; //更改button的值，用于增加和修改信息
        }

        public Form21(string[] a)
        {
            InitializeComponent();
            button1.Text = "修改信息";
            for(int i =0;i<5;i++)
            {
                str[i] = a[i];
            }
            textBox1.Text = str[0];
            textBox2.Text = str[1];
            textBox3.Text = str[2];
            textBox4.Text = str[3];
            textBox5.Text = str[4];
           // this.Close();
        }
        private void Form21_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (button1.Text == "添加信息")
            {
                if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "" || textBox5.Text == "")
                {
                    MessageBox.Show("输入不完整，不能为空", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                else
                {
                    string sql = "insert into Student Values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "')";

                    MessageBox.Show(sql);
                    Dao dao = new Dao();
                    int i = dao.Excute(sql);
                    if (i > 0)
                    {
                        MessageBox.Show("添加成功");
                        this.Close();
                    }
                
                }
            }
            else if (button1.Text == "修改信息")
            {
                if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "" || textBox4.Text == "" || textBox5.Text == "")
                {
                    MessageBox.Show("修改后有空项,请检查", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                else
                {
                    string sql = "update Student set Sno='"+textBox1.Text+"' ,Sname ='"+textBox2.Text+"',Ssex='"+textBox3.Text+"',Sage='"+textBox4.Text+"',Sdept='"+textBox5.Text+"' where Sno = '" + str[0] +"';";
                    Dao dao = new Dao();
                    int i = dao.Excute(sql);
                    if (i > 0)
                    {
                        MessageBox.Show("修改成功");
                        this.Close();
                    }
                }
            }

        }
        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

六.课程信息管理（删除功能在主界面中）

324d0b150acd4c52876beb47e731df2c.png

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace StudentSY
{
    public partial class Form3 : Form
    {
        public Form3()
        {
            InitializeComponent();
            toolStripStatusLabel3.Text = DateTime.Now.ToString("G");
            timer1.Start();
            Table();
        }

        public void Table()
        {
            string sql = "select * from Course";
            Dao dao = new Dao();
            IDataReader dr = dao.read(sql);
            while (dr.Read())
            {
                string Cno, Cname, Cpno, Credit;
                Cno = dr["Cno"].ToString();
                Cname = dr["Cname"].ToString();
                Cpno = dr["Cpno"].ToString();
                Credit = dr["Credit"].ToString();
                string[] str = { Cno, Cname, Cpno, Credit };
                dataGridView1.Rows.Add(str);
            }
            dr.Close();
        }
        private void timer1_Tick(object sender, EventArgs e)
        {
            toolStripStatusLabel3.Text = DateTime.Now.ToString("G");
        }

        private void 添加课程ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            addcourse addcourse = new addcourse();
            addcourse.ShowDialog();
            dataGridView1.Rows.Clear();
            Table();
        }
        private void 修改课程ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                string[] str = { dataGridView1.SelectedCells[0].Value.ToString(), dataGridView1.SelectedCells[1].Value.ToString(),
                dataGridView1.SelectedCells[2].Value.ToString(),dataGridView1.SelectedCells[3].Value.ToString()};
                addcourse addcourse = new addcourse(str);
                addcourse.ShowDialog();
                dataGridView1.Rows.Clear();
                Table();
            }
            catch
            {
                MessageBox.Show("未正确选择行，请重试");
            }
        }
       

        private void toolStripButton4_Click(object sender, EventArgs e)
        {
            dataGridView1.Rows.Clear();
            Table();
        }

        private void 删除课程ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DialogResult r = MessageBox.Show("确定要删除吗？", "提示", MessageBoxButtons.OKCancel);
            if (r == DialogResult.OK)
            {
                try
                {
                    string id;
                    id = dataGridView1.SelectedCells[0].Value.ToString(); //选中当前行第一列的值
                  //  name = dataGridView1.SelectedCells[0].Value.ToString();
                    string sql = "delete from Course where Cno=" + id;
                    Dao dao = new Dao();
                    int i = dao.Excute(sql); //执行SQL语句
                    if (i > 0)
                    {
                        dataGridView1.Rows.Clear();
                        Table();
                    }
                }

                catch
                {
                    MessageBox.Show("请正确选择行");
                }
            }
        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            添加课程ToolStripMenuItem_Click(sender, e);
        }

        private void toolStripButton2_Click(object sender, EventArgs e)
        {
            修改课程ToolStripMenuItem_Click(sender, e);
        }

        private void toolStripButton3_Click(object sender, EventArgs e)
        {
            删除课程ToolStripMenuItem_Click(sender, e);
        }

        private void 退出ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
        private void menuStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

       
    }
}

 七.课程信息添加和修改

78ee51b3594547e09fa97957b56d6812.pnge881cbf38994419ba22e3335444bda23.png

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;

namespace StudentSY
{
    public partial class addcourse : Form
    {
        string[] str=new string[4];
        public addcourse()
        {
            InitializeComponent();
            button1.Text = "添加信息";

        }

        public addcourse(string[] a)
        {
            InitializeComponent();
            button1.Text = "修改信息";
            for (int i = 0; i < 4; i++)
            {
                str[i] = a[i];
            }
            textBox1.Text = str[0];
            textBox2.Text = str[1];
            textBox3.Text = str[2];
            textBox4.Text = str[3];
         
            // this.Close();
        }

       
        private void button1_Click(object sender, EventArgs e)
        {
                if (button1.Text == "添加信息")
                {
                    if (textBox1.Text == "" || textBox2.Text == ""  || textBox4.Text == "")
                    {
                        MessageBox.Show("除先修课外输入不完整，不能为空", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    }
                    else
                    {
                        string sql = "insert into Course Values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')";

                        MessageBox.Show(sql);
                        Dao dao = new Dao();
                        int i = dao.Excute(sql);
                        if (i > 0)
                        {
                            MessageBox.Show("添加成功");
                            this.Close();
                        }

                    }
                }
                else if (button1.Text == "修改信息")
                {
                    if (textBox1.Text == "" || textBox2.Text == "" ||  textBox4.Text == "" )
                    {
                        MessageBox.Show("除先修课外修改后有空项,请检查", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    }
                    else
                    {
                        string sql = "update Course set Cno='" + textBox1.Text + "' ,Cname ='" + textBox2.Text + "',Cpno='" + textBox3.Text + "',Credit='" + textBox4.Text + "'  where Cno = '" + str[0] + "';";
                        Dao dao = new Dao();
                        int i = dao.Excute(sql);
                        if (i > 0)
                        {
                            MessageBox.Show("修改成功");
                            this.Close();
                        }
                    }
                }
        }
        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }


    }
}

 八.成绩信息管理（删除功能在主界面代码中）

6c4b0abb97bb4f8b996da4377eae9b2d.png

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace StudentSY
{
    public partial class Form31 : Form
    {
        public Form31()
        {
            InitializeComponent();
            Table();
        }

        public void Table()
        {
            string sql = "select * from SC";
            Dao dao = new Dao();
            IDataReader dr = dao.read(sql);
            while (dr.Read())
            {
                string Sno,Cno,Grade;
                Sno = dr["Sno"].ToString();
                Cno = dr["Cno"].ToString();
                Grade = dr["Grade"].ToString();
                string[] str = { Sno,Cno, Grade};
                dataGridView2.Rows.Add(str);
            }
            dr.Close();
        }

        private void 添加成绩ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            addgrade addgrade = new addgrade();
            addgrade.ShowDialog();
            dataGridView2.Rows.Clear();
            Table();
        }

        private void 删除成绩ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DialogResult r = MessageBox.Show("确定要删除吗？", "提示", MessageBoxButtons.OKCancel);
            if (r == DialogResult.OK)
            {
                try
                {
                    string sno,cno;
                    sno = dataGridView2.SelectedCells[0].Value.ToString(); //选中当前行第一列的值
                    cno= dataGridView2.SelectedCells[1].Value.ToString();                                             //  name = dataGridView1.SelectedCells[0].Value.ToString();
                    string sql = "delete from SC where Sno= '"+sno+"' and Cno='"+cno+"'";
                    Dao dao = new Dao();
                    int i = dao.Excute(sql); //执行SQL语句
                    if (i > 0)
                    {
                        dataGridView2.Rows.Clear();
                        Table();
                    }
                }

                catch
                {
                    MessageBox.Show("请正确选择行");
                }
            }
        }

        private void 修改成绩ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                string[] str = { dataGridView2.SelectedCells[0].Value.ToString(), dataGridView2.SelectedCells[1].Value.ToString(),
                dataGridView2.SelectedCells[2].Value.ToString()};
                addgrade addgrade = new addgrade(str);
                addgrade.ShowDialog();
                dataGridView2.Rows.Clear();
                Table();
            }
            catch
            {
                MessageBox.Show("未正确选择行，请重试");
            }
        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            添加成绩ToolStripMenuItem_Click(sender, e);
        }

        private void toolStripButton2_Click(object sender, EventArgs e)
        {
            修改成绩ToolStripMenuItem_Click(sender, e);
        }

        private void toolStripButton3_Click(object sender, EventArgs e)
        {
            删除成绩ToolStripMenuItem_Click(sender, e);
        }

        private void toolStripButton4_Click(object sender, EventArgs e)
        {
            dataGridView2.Rows.Clear();
            Table();
        }

        private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void 退出ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
    }
}

九.成绩信息添加和修改

c479f061df2d42e5bb5a5def04872e9c.png334d7e1424c0463ca7b79b3d6f643fc1.png

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;

namespace StudentSY
{
    public partial class addgrade : Form
    {
        string []str = new string[3];
        public addgrade()
        {
            InitializeComponent();
            button1.Text = "添加信息";
        }

        public addgrade(string[] a)
        {
            InitializeComponent();
            button1.Text = "修改信息";
            for (int i = 0; i < 3; i++)
            {
                str[i] = a[i];
            }
            textBox1.Text = str[0];
            textBox2.Text = str[1];
            textBox3.Text = str[2];
        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (button1.Text == "添加信息")
            {
                if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "")
                {
                    MessageBox.Show("输入不完整，不能为空", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                else
                {
                    string sql = "insert into SC Values('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "')";

                    MessageBox.Show(sql);
                    Dao dao = new Dao();
                    int i = dao.Excute(sql);
                    if (i > 0)
                    {
                        MessageBox.Show("添加成功");
                        this.Close();
                    }

                }
            }
            else if (button1.Text == "修改信息")
            {
                if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text == "")
                {
                    MessageBox.Show("修改后有空项,请检查", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                else
                {
                    string sql = "update SC set Grade='" + textBox3.Text + "'  where Sno = '" + str[0] + "' and Cno ='" + str[1] +"';";
                    Dao dao = new Dao();
                    int i = dao.Excute(sql);
                    if (i > 0)
                    {
                        MessageBox.Show("修改成功");
                        this.Close();
                    }
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

十.数据库设计

USE MySchool;
DROP TABLE IF EXISTS SC       /*成绩*/
DROP TABLE IF EXISTS Student  /*学生信息*/
DROP TABLE IF EXISTS Course   /*课程*/
DROP TABLE IF EXISTS StudentUser  /*学生用户信息*/
DROP TABLE IF EXISTS Administrator  /*管理员用户信息*/
DROP TABLE IF EXISTS SysLog   /*注册日志*/
DROP TABLE IF EXISTS SysLog1   /*登陆日志*/
DROP TABLE IF EXISTS AVG1   /*平均成绩*/

Create table StudentUser  --学生注册信息表格
(ID nchar(20) primary key,
 PassWord nchar(32),
 Name nchar(20),
 Sex nchar(2),
 );

 Create table Administrator  --管理员注册信息表格
 (
 ID char(20) primary key,
 PassWord nchar(32),
 Name nchar(20),
 Sex char(2),
-- Birthday datetime ,
 --UserMobile nchar(11),
 );

Create table Student      --学生信息表格
(
Sno char(9) primary key,  --列级完整性约束条件,Sno是主码
Sname char(20) Unique,    --名字唯一
Ssex char(2),
Sage int,
Sdept char(20),
);

Create table Course
(
Cno char(9) primary key,  --列级完整性约束条件,Cno是主码
Cname char(40),
cpno char(4),
Credit int,
);

 
create table  SC
 (
 Sno char(9), 
 Cno char(9),  
 Grade int,
 primary key (Sno,Cno),                     --主码由两个属性构成，必须作为表级完整性进行定义
 foreign key (Sno) references Student(Sno),  --表级完整性约束条件，Sno是外码，被参照表是Student 
 foreign key (Cno) references Course(Cno)     --表级完整性约束条件， Cno是外码，被参照表是Course
 ); 
INSERT  INTO  StudentUser VALUES ('2023123','123456','张三','男');
INSERT  INTO  Administrator VALUES ('2023124','123456','张三','男');
INSERT  INTO  Administrator VALUES ('2023125','123456','张三','男');
 
 
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215121','李勇','男','CS',20);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215122','刘晨','女','CS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215123','王敏','女','MA',18);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215125','张立','男','IS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215128','陈冬','男','IS',20);
 
SELECT * FROM Student
 
INSERT  INTO Course(Cno,Cname,Cpno,Credit)	VALUES ('1','数据库',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Credit)	VALUES ('2','数学',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Credit)	VALUES ('3','信息系统',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Credit)	VALUES ('4','操作系统',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Credit)	VALUES ('5','数据结构',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Credit)	VALUES ('6','数据处理',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Credit)	VALUES ('7','Pascal语言',NULL,4);
 
UPDATE Course SET Cpno = '5' WHERE Cno = '1' 
UPDATE Course SET Cpno = '1' WHERE Cno = '3' 
UPDATE Course SET Cpno = '6' WHERE Cno = '4' 
UPDATE Course SET Cpno = '7' WHERE Cno = '5' 
UPDATE Course SET Cpno = '6' WHERE Cno = '7' 
 
SELECT * FROM Course
 
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','1',92);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','2',85);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','3',88);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','2',90);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','3',80);
 
SELECT * FROM SC

CREATE TABLE AVG1
	(
		Cname CHAR(10),   /* 科目*/	
		avg1 INT
	);
INSERT  INTO AVG1(Cname,avg1)	VALUES ('数据库',NULL);
INSERT  INTO AVG1(Cname,avg1)	VALUES ('数学',NULL);
INSERT  INTO AVG1(Cname,avg1)	VALUES ('信息系统',NULL);
INSERT  INTO AVG1(Cname,avg1)	VALUES ('操作系统',NULL);
INSERT  INTO AVG1(Cname,avg1)	VALUES ('数据结构',NULL);
INSERT  INTO AVG1(Cname,avg1)	VALUES ('数据处理',NULL);
INSERT  INTO AVG1(Cname,avg1)	VALUES ('Pascal语言',NULL);

学生信息表（Student）:

课程表（Course）：

成绩表（SC）：

学生用户表（StudentUser）：

 有需要完整代码和实验报告的同学私聊我，有什么问题评论

版权声明：本文为博主作者：好好活着呀原创文章，版权归属原作者，如果侵权，请联系我们删除！

原文链接：https://blog.csdn.net/qq_64011418/article/details/130910414

共计人评分，平均分

到目前为止还没有投票！成为第一位评论此文章。

赞 (0)
社会演员多的头像社会演员多普通用户
0
上一篇 2024年1月16日
下一篇 2024年1月16日

相关推荐