2016华南理工计算机考研复试数据库

跟大佬学的：2015华工复试数据库题目详细步骤

以下是2016年的题目：

一、数据库设计

手动打开SQL Server代理（从来记不住，总是等到Sqlserver连不上才打开）

（1）打开数据库后，新建数据库，新建查询，建表代码：

use Student2016;
-- 因为三张表之间的以来关系，建表顺序如下
create table Department
 ( deptNo varchar(2) primary key,
   deptName varchar(30) not null
   )

create table CLASS
 ( classNo varchar(5) primary key,
   specName varchar(30) not null,
   deptNo varchar(2) not null,
   [year] int check([year]>0) not null,
   num int check(num<=30 and num>=0)not null,
   foreign key (deptNo) references Department(deptNo)
   )
   
create table Student
  ( SNo varchar(5) primary key,
    SName varchar(20) not null,
	[Date] date,
	classNo varchar(5),
	foreign key (classNo) references CLASS(classNo)
	)

  因为题目非要给的列名有关键词，所以加上[]才行

（2）插入数据

insert into Department values('1','数学');
insert into Department values('2','计算机');
insert into Department values('3','化学');

insert into CLASS values('101','软件','2',2012,25);
insert into CLASS values('102','微电子','2',2013,28);
insert into CLASS values('111','无机化学','3',2012,20);
insert into CLASS values('112','高分子化学','3',2013,19);
insert into CLASS values('121','统计数学','1',2013,11);

insert into Student values('8101','张三','1996-7-9','101'); 
insert into Student values('8102','钱四','1996-2-6','121');
insert into Student values('8105','李飞','1994-1-21','102');
insert into Student values('8201','张飞','1996-2-25','111');
insert into Student values('8302','周瑜','1996-6-15','112');
insert into Student values('8203','王亮','1997-5-27','111');
insert into Student values('8305','董庆','1996-4-2','102');
insert into Student values('8409','赵龙','1995-6-1','101');

（3）全选，点击  执行完成后，右击建立的数据库->任务->备份，添加题目要求的路径，备份的文件类型是.bak

  完成！不要关掉SQLServer

二、C#管理系统设计

（1）新建项目，填写题目中的路径。一共建了两个窗体，Form1是主窗体，Form2用来在数据维护功能传入学生信息

（2）Form1如下

    右下角有Properties设置

     Form1 设置

            formBorder: fixedSingle

            StartPosition: center

            MaximizeBox = false

            MinimizeBox = false

     四个tabPage的 Text 依次设置为查询，统计1，统计2，数据维护

     listView的设置是

                 View = Details

                FullRowSelect=True    

                MultiSelect = false     

                gridLine =true    

（3）Form2如下

（4）用来连接数据库的类DB.cs

   server='值1'   值1是打开SqlServer时用的用户名

  database='值2'   值2是连接的数据库的名字

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;  //添加
using System.Data.SqlClient; //添加

namespace Anita
{
    class DB
    {
        private SqlConnection sqlConnection;

        public DB()
        {//连接
            sqlConnection = new SqlConnection(@"server=PC201805211617\SQL2012;database=Student2016;Trusted_Connection=SSPI");
            sqlConnection.Open();
        }
        public DataTable getBySql(string sql)
        {//查询
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(new SqlCommand(sql,sqlConnection));
            DataTable dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);
            return dataTable;
        }
        public void setBySql(string sql)
        {//修改
            new SqlCommand(sql,sqlConnection).ExecuteNonQuery();
        }
        public void Dispose()
        {//折构
            sqlConnection.Close();
        }
    }
}

（5）用来在form1和form2之间传递数据的类Intent.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace Anita
{
    class Intent
    {
        public static Dictionary<string, Object> dict = new Dictionary<string, Object>();
    }
}

 

（6）双击控件跳转到编辑页面，为控件添加事件。题目中要求的约束基本都在代码中实现

    Form1代码

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;//这一行自己加上，正则表达式用到


namespace Anita
{
    public partial class Form1 : Form
    {
        DB db;
        public Form1()
        {
            InitializeComponent();
            db = new DB();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //菜单2-统计1 每个系的学生人数
              //生成表头
            listView2.Columns.Add("系号", listView2.Width / 3 - 1, HorizontalAlignment.Left);
            listView2.Columns.Add("系名", listView2.Width / 3 - 1, HorizontalAlignment.Left);
            listView2.Columns.Add("人数", listView2.Width / 3 - 1, HorizontalAlignment.Left);
            DataTable dataTable = db.getBySql(@"select Department.deptNo,Department.deptName,sum(CLASS.num)as '总人数' " +
                "from Department,CLASS " +
                "where Department.deptNo = CLASS.deptNo " +
                "group by Department.deptNo,Department.deptName;");
            listView2.BeginUpdate(); //开始加载数据
            //表的内容
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                ListViewItem listViewItem = new ListViewItem();
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    if (j <= 0)
                    {
                        listViewItem.Text = dataTable.Rows[i][j] + "";
                    }
                    else
                    {
                        listViewItem.SubItems.Add(dataTable.Rows[i][j] + "");
                    }
                }
                listView2.Items.Add(listViewItem);
            }
            listView2.EndUpdate();//加载完成

            //菜单3-统计2 按入学年份统计人数
               //表头
            listView3.Columns.Add("入学年份", listView3.Width / 2 - 1, HorizontalAlignment.Left);
            listView3.Columns.Add("总人数", listView3.Width / 2 - 1, HorizontalAlignment.Left);
            dataTable = db.getBySql(@"select CLASS.[year],sum(CLASS.num) as '总人数' " +
                "from CLASS group by CLASS.[year];");
            listView3.BeginUpdate(); //开始加载
                //表的内容
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                ListViewItem listViewItem = new ListViewItem();
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    if (j <= 0)
                    {
                        listViewItem.Text = dataTable.Rows[i][j] + "";
                    }
                    else
                    {
                        listViewItem.SubItems.Add(dataTable.Rows[i][j] + "");
                    }
                }
                listView3.Items.Add(listViewItem);
            }
            listView3.EndUpdate(); //加载完成

            //菜单4-数据维护 
              //数据加载
                // 生成表头
            listView4.Columns.Add("学号", listView4.Width / 4 - 1, HorizontalAlignment.Left);
            listView4.Columns.Add("姓名", listView4.Width / 4 - 1, HorizontalAlignment.Left);
            listView4.Columns.Add("出生年月", listView4.Width / 4 - 1, HorizontalAlignment.Left);
            listView4.Columns.Add("班号", listView4.Width / 4 - 1, HorizontalAlignment.Left);
            dataTable = db.getBySql(@"select * from Student;");
            listView4.BeginUpdate();//开始加载信息
                // 表的内容
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                ListViewItem listViewItem = new ListViewItem();
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    if (j <= 0)
                    {
                        listViewItem.Text = dataTable.Rows[i][j] + "";
                    }
                    else
                    {
                        listViewItem.SubItems.Add(dataTable.Rows[i][j] + "");
                    }
                }
                listView4.Items.Add(listViewItem);
            }
            listView4.EndUpdate(); //加载完毕

        }//Form1

        private void button1_Click(object sender, EventArgs e)
        {//button1查询
            //若系的输入不为空，检查是不是存在这个系
            bool dept = false; //不存在这个系
            if(textBox1.Text!="")
            {//系有获取内容
                DataTable table = db.getBySql(@"select deptName from Department");
                for(int i=0;i<table.Rows.Count;i++)
                {
                   if(table.Rows[i][0]+"" == textBox1.Text)
                   {
                       dept=true;
                       break;
                   }
                }
                if(!dept)
                {
                    MessageBox.Show("系名不正确或不存在该系",this.Text);
                }
            }
            //若班级输入不为空，检查是不是存在这个班级
            bool clas = false;//不存在该班级
            if(textBox2.Text != "")
            {
                DataTable table = db.getBySql(@"select specName from CLASS");
                for(int i=0;i<table.Rows.Count;i++)
                {
                    if(table.Rows[i][0]+"" == textBox2.Text)
                    {
                        clas = true;
                        break;
                    }
                }
                if(!clas)
                {
                    MessageBox.Show("专业名称不正确或不存在该专业",this.Text);
                }
            }
            //注意名字模糊查询
            //我不会写，所以匹配写的是 '%飞%' 样子,可匹配 某飞某、某飞，飞某 
            DataTable dataTable = new DataTable();
            if(!dept && !clas)  //只有姓名
            {
                 dataTable = db.getBySql(@"select Department.deptName,CLASS.specName,Student.SName "+
                    "from CLASS,Department,Student "+
                    "where Student.classNo = CLASS.classNo and "+
                    "CLASS.deptNo = Department.deptNo and "+
                    "SName like '%"+textBox3.Text+"%' "+
                    "order by Department.deptNo desc, CLASS.classNo asc, Student.SNo desc; ");
            }
            else if(!dept && clas)//班，姓名
            {
                 dataTable = db.getBySql(@"select Department.deptName,CLASS.specName,Student.SName "+
                    "from CLASS,Department,Student "+
                    "where Student.classNo = CLASS.classNo and "+
                    "CLASS.deptNo = Department.deptNo and "+
                    "CLASS.specName = '"+textBox2.Text+"' and "+
                    "SName like '%"+textBox3.Text+"%' "+
                    "order by Department.deptNo desc, CLASS.classNo asc, Student.SNo desc; ");
            }
            else if(dept && !clas) //系，姓名
            {
                 dataTable = db.getBySql(@"select Department.deptName,CLASS.specName,Student.SName "+
                    "from CLASS,Department,Student "+
                    "where Student.classNo = CLASS.classNo and "+
                    "CLASS.deptNo = Department.deptNo and "+
                    "Department.deptName = '"+textBox1.Text+"' and "+
                    "SName like '%"+textBox3.Text+"%' "+
                    "order by Department.deptNo desc, CLASS.classNo asc, Student.SNo desc; ");
            }
            else if(dept && clas) //系，班，姓名
            {
                 dataTable = db.getBySql(@"select Department.deptName,CLASS.specName,Student.SName "+
                    "from CLASS,Department,Student "+
                    "where Student.classNo = CLASS.classNo and "+
                    "CLASS.deptNo = Department.deptNo and "+
                    "Department.deptName = '"+textBox1.Text+"' and "+
                    "CLASS.specName = '"+textBox2.Text+"' and "+
                    "SName like '%"+textBox3.Text+"%' "+
                    "order by Department.deptNo desc, CLASS.classNo asc, Student.SNo desc; ");
            }
            //生成表头
            listView1.Clear(); //清空当前内容
            listView1.Columns.Add("系名称",listView1.Width/3-1,HorizontalAlignment.Left);
            listView1.Columns.Add("专业名称",listView1.Width/3-1,HorizontalAlignment.Left);
            listView1.Columns.Add("学生名字",listView1.Width/3-1,HorizontalAlignment.Left);
            listView1.BeginUpdate();  //UI挂起，加载查询内容
            //表的内容
            for(int i=0;i<dataTable.Rows.Count;i++)
            {
                ListViewItem listViewItem = new ListViewItem();
                for(int j=0;j<dataTable.Columns.Count;j++)
                {
                    if(j<=0)
                    {
                        listViewItem.Text = dataTable.Rows[i][j]+"";
                    }
                    else
                    {
                        listViewItem.SubItems.Add(dataTable.Rows[i][j]+"");
                    }
                }
                listView1.Items.Add(listViewItem);
            }
            listView1.EndUpdate();  //加载查询完成
        }//button1查询

        private void button2_Click(object sender, EventArgs e)
        {//添加
            Form2 form2 = new Form2(); //使用Form2
            //将form1当前内容压入Intent
            Intent.dict["form1_text"] = this.Text;
            Intent.dict["form1_flag"] = 0;  // flag=0表示添加
            if(form2.ShowDialog() == DialogResult.OK)
            {
                bool canAdd = true;
                //检查学号是否重复
                foreach(ListViewItem item in this.listView4.Items)
                {
                    if(Intent.dict["form2_textbox1_text"]+"" == item.SubItems[0].Text)
                    {
                        canAdd = false;
                        MessageBox.Show("已存在该学号",this.Text);
                        break;
                    }
                }
                //检查生日
                Regex regex = new Regex(@"^[0-9]{4}[\-\/]((1[0-2])|(0?[1-9]))[\/\-]((0?[1-9])|([12][0-9])|(3[0-1]))$");//粗略的检查，至于2月多少天之类的不管
                if(!(regex.IsMatch(Intent.dict["form2_textbox3_text"]+"")))
                {
                    canAdd = false;
                    MessageBox.Show("请输入正确的日期格式",this.Text);
                }
                //检查班级号
                DataTable dataTable = new DataTable();
                dataTable = db.getBySql(@"select classNo from CLASS");
                bool isClass = false; //是否存在该班级，默认值不存在
                for(int i=0;i<dataTable.Rows.Count;i++)
                {
                    if((Intent.dict["form2_textbox4_text"]+"") == (dataTable.Rows[i][0]+""))
                    {
                        isClass = true;           
                        break;
                    }
                }
                if(!isClass){ //如果不存在该班级
                    canAdd = false;
                    MessageBox.Show("不存在该班级", this.Text);
                }
                //输入信息全部正确
                if(canAdd)
                {
                    ListViewItem listViewItem = new ListViewItem();
                    listViewItem.Text = Intent.dict["form2_textbox1_text"]+""; //学号
                    listViewItem.SubItems.Add(Intent.dict["form2_textbox2_text"]+"");//姓名
                    listViewItem.SubItems.Add(Intent.dict["form2_textbox3_text"]+"");//生日
                    listViewItem.SubItems.Add(Intent.dict["form2_textbox4_text"]+"");//班号
                    listView4.Items.Add(listViewItem);
                    db.setBySql("insert into Student values('"+Intent.dict["form2_textbox1_text"]+"','"+Intent.dict["form2_textbox2_text"]+"','"+Intent.dict["form2_textbox3_text"]+"','"+Intent.dict["form2_textbox4_text"]+"')");
                }
            }

        }//添加

        private void button3_Click(object sender, EventArgs e)
        {//修改
            Form2 form2 = new Form2();
            //将form1压入Intent
            Intent.dict["form1_text"] = this.Text;
            Intent.dict["form1_flag"] = 1; //flag=1表示修改
            if (listView4.SelectedItems.Count == 0)
            { //没有选中
                MessageBox.Show("请先选择要修改的学生", this.Text);
            }
            else
            { //选中
                //向form2传递数据
                Intent.dict["form1_textbox1_text"] = listView4.SelectedItems[0].SubItems[0].Text;
                Intent.dict["form1_textbox2_text"] = listView4.SelectedItems[0].SubItems[1].Text;
                Intent.dict["form1_textbox3_text"] = listView4.SelectedItems[0].SubItems[2].Text;
                Intent.dict["form1_textbox4_text"] = listView4.SelectedItems[0].SubItems[3].Text;
                if (form2.ShowDialog() == DialogResult.OK)
                {
                    bool canUpdate = true;
                    //检查学号是否重复
                    foreach (ListViewItem item in this.listView4.Items)
                    {
                        if (Intent.dict["form2_textbox1_text"] + "" == item.SubItems[0].Text)
                        {
                            canUpdate = false;
                            MessageBox.Show("已存在该学号", this.Text);
                            break;
                        }
                    }
                    //检查生日
                    Regex regex = new Regex(@"^[0-9]{4}[\-\/]((1[0-2])|(0?[1-9]))[\/\-]((0?[1-9])|([12][0-9])|(3[0-1]))$");//瞎几把写
                    if (!(regex.IsMatch(Intent.dict["form2_textbox3_text"] + "")))
                    {
                        canUpdate = false;
                        MessageBox.Show("请输入正确的日期格式", this.Text);
                    }
                    //检查班级号
                    DataTable dataTable = new DataTable();
                    dataTable = db.getBySql(@"select classNo from CLASS");
                    bool isClass = false; //是否存在该班级，默认值不存在
                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        if (Intent.dict["form2_textbox4_text"] + "" == dataTable.Rows[i][0] + "")
                        {
                            isClass = true;
                            break;
                        }
                    }
                    if (!isClass)
                    { //如果不存在该班级
                        canUpdate = false;
                        MessageBox.Show("不存在该班级", this.Text);
                    }
                    //信息无误
                    if (canUpdate)
                    {
                        listView4.SelectedItems[0].SubItems[0].Text = Intent.dict["form2_textbox1_text"] + ""; //学号
                        listView4.SelectedItems[0].SubItems[1].Text = Intent.dict["form2_textbox2_text"] + "";//姓名
                        listView4.SelectedItems[0].SubItems[2].Text = Intent.dict["form2_textbox3_text"] + "";//生日
                        listView4.SelectedItems[0].SubItems[3].Text = Intent.dict["form2_textbox4_text"] + "";//班号
                        //借住要修改的人的学号
                        //如果是借住名字之类的，有两个叫张飞的就都改了
                        //先修改这个人的其他信息，最后修改学号，否则找不到这个人了
                        db.setBySql("update Student set SName='" + Intent.dict["form2_textbox2_text"] + "' where SNo='" + Intent.dict["form1_textbox1_text"] + "';");
                        db.setBySql("update Student set [Date]='" + Intent.dict["form2_textbox3_text"] + "' where SNo='" + Intent.dict["form1_textbox1_text"] + "';");
                        db.setBySql("update Student set classNo='" + Intent.dict["form2_textbox4_text"] + "' where SNo='" + Intent.dict["form1_textbox1_text"] + "';");
                        db.setBySql("update Student set SNo='" + Intent.dict["form2_textbox1_text"] + "' where SNo='" + Intent.dict["form1_textbox1_text"] + "';");
                    }
                }
            }
        }//修改

        private void button4_Click(object sender, EventArgs e)
        {//删除
            db.setBySql("delete from Student where SNo='"+listView4.SelectedItems[0].SubItems[0].Text+"';");
            listView4.SelectedItems[0].Remove();

        }//删除
    }
}

（7）Form2代码

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

namespace Anita
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            if ((int)Intent.dict["form1_flag"] == 0)
            {//flag=0是添加
                this.Text = Intent.dict["form1_text"]+"";
                textBox1.Focus();
            }
            else
            {//flag=1是修改
                textBox1.Text = Intent.dict["form1_textbox1_text"] + "";
                textBox2.Text = Intent.dict["form1_textbox2_text"] + "";
                textBox3.Text = Intent.dict["form1_textbox3_text"] + "";
                textBox4.Text = Intent.dict["form1_textbox4_text"] + "";
                this.Text = Intent.dict["form1_text"] + "";
                textBox1.Focus();
                //textBox1.SelectAll();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {//取消
            this.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {//确认
            if (textBox1.Text == "" || textBox2.Text == "" || textBox3.Text=="" || textBox3.Text == "")
            {
                MessageBox.Show("信息填写不完整", this.Text);
            }
            else
            {
                Intent.dict["form2_textbox1_text"] = textBox1.Text;
                Intent.dict["form2_textbox2_text"] = textBox2.Text;
                Intent.dict["form2_textbox3_text"] = textBox3.Text;
                Intent.dict["form2_textbox4_text"] = textBox4.Text;
                this.DialogResult = DialogResult.OK;  //返回确认值
                this.Close();
            }
        }
    }
}

（8）结果

      a） from1代码中对查询写的很麻烦，也不晓对不对，反正最后的exe能用就是了

    b) sqlServer中的出生年月是date数据类型，到c#中只有DateTime类型，不知道怎么换，无伤大雅，，反正考试都不一定能做到这儿（肯定做不到这）