SCUT-2016机试题目数据库
往年代码没了，只能自己探索了，估计实现的功能没有那么完整。
题目
个人答案
建立数据库:
create table student(
SNo int primary key,
SName varchar(20) not null,
Date date,
classNo int
);
create table class(
classNo int primary key,
specName varchar(20) unique,
deptNo int,
year varchar(20),
num int check(num<31)
);
create table department(
deptNo int primary key,
deptName varchar(20)
);
alter table student add foreign key (classNo) references class(classNo) on delete cascade;
alter table class add foreign key (deptNo) references department(deptNo);
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,'王亮','1996-5-27',111);
insert into student values (8305,'董庆','1996-4-2',102);
insert into student values (8409,'赵龙','1996-6-1',101);
实现
WinForm窗体:
代码：
DBUtils.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
namespace Demo2016
{
class DBUtils
{
private DBUtils() { }
static SqlConnection con;
static String strCon="";
static DBUtils(){
strCon = @"Data Source=dc2017-PC\sqlexpress;Initial Catalog=Students;Integrated Security=True";
SqlConnection con=new SqlConnection(strCon);
}
public static SqlConnection getCon()
{
if(con==null)
con=new SqlConnection(strCon);
con.Close();
return con;
}
}
}
Query.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
namespace Demo2016
{
class Query
{
internal static DataSet queryDate(string sql)
{
SqlConnection con = DBUtils.getCon();
SqlCommand com = new SqlCommand(sql, con);
SqlDataAdapter sda = new SqlDataAdapter(com);
DataSet ds = new DataSet();
sda.Fill(ds, "Students");
return ds;
}
}
}
主窗体代码：
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 System.Data.SqlClient;
namespace Demo2016
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
String sql = "";
private void btn1_Click(object sender, EventArgs e)
{
String deptName = tb1.Text.Trim();
String specName = tb2.Text.Trim();
String SName = tb3.Text.Trim();
//此处简单做了一下防止sql注入，使用参数赋值方法
SqlParameter deptn=null;
SqlParameter specn=null;
SqlParameter sn=null;
sql = "select deptName as 系名,specName as 班级名,Sname as 学生名 from student,class,department where student.classNo =class.classNo and class.deptNo=department.deptNo ";
if (deptName != "")
{
sql += "and [email protected] ";
deptn = new SqlParameter("@deptName", deptName);
}
if (specName != "")
{
sql += "and [email protected] ";
specn = new SqlParameter("@specName", specName);
}
if (SName != "")
{
//注：在写@SName这些参数前后都要有空格！！！
sql += "and SName like @SName ";
sn = new SqlParameter("@SName","%"+SName+"%");
}
sql += "order by deptName desc,specName,Sname desc";
SqlConnection con = DBUtils.getCon();
SqlCommand com = new SqlCommand(sql, con);
SqlDataAdapter sda = new SqlDataAdapter(com);
if(deptn!=null)
sda.SelectCommand.Parameters.Add(deptn);
if(specn!=null)
sda.SelectCommand.Parameters.Add(specn);
if (sn != null)
sda.SelectCommand.Parameters.Add(sn);
DataSet ds = new DataSet();
sda.Fill(ds, "Students");
dataGridView1.DataSource = ds.Tables["Students"];
dataGridView1.ReadOnly = true;
dataGridView1.MultiSelect = false;
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
//主菜单控制
private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
{
String name=this.tabControl1.SelectedTab.Name;
if ("tabPage1".Equals(name))
{
}
else if ("tabPage2".Equals(name))
{
sql = "select deptName as 系名,sum(num) as 人数 from class inner join department on class.deptNo=department.deptNo group by class.deptNo,deptName;";
DataSet ds = Query.queryDate(sql);
dataGridView2.DataSource = ds.Tables["Students"];
dataGridView2.ReadOnly = true;
dataGridView2.MultiSelect = false;
dataGridView2.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
else if (this.tabControl1.SelectedTab==tabPage3)
{
sql = "select year as 入学年份,COUNT(year) as 人数 from class inner join department on class.deptNo=department.deptNo group by year";
DataSet ds = Query.queryDate(sql);
dataGridView3.DataSource = ds.Tables["Students"];
dataGridView3.ReadOnly = true;
dataGridView3.MultiSelect = false;
dataGridView3.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
}
}
}
运行结果
第一题：
第二题(1)：
(2)