2018-D
新建数据库 test0317，目录为考试目录，并在完成建表后备份
1、建表：
use [test0317];
create table [STD_INFO](
[std_id] int not null primary key,
[std_name] varchar(20) not null,
[std_sex] varchar(2) not null check([std_sex]='男' or [std_sex]='女')
);
create table [COURSE_INFO](
[course_id] varchar(10) not null primary key,
[course_name] varchar(20) not null,
)
create table [SCORES](
[std_id] int references [STD_INFO]([std_id]),
[course_id] varchar(10) references [COURSE_INFO]([course_id]),
[score] int not null check([score]>=0 and [score]<=100)
)
insert into [STD_INFO] values(201833,'王二','男');
insert into [STD_INFO] values(201824,'张三','女');
update [STD_INFO] set std_id=201834 where std_name='张三';
insert into [COURSE_INFO] values('001','操作系统');
insert into [COURSE_INFO] values('002','嵌入式系统');
insert into [SCORES] values(201833,'001',70);
insert into [SCORES] values(201834,'001',90);
insert into [SCORES] values(201834,'002',20);
select * from [STD_INFO];
select * from [COURSE_INFO];
select * from [SCORES];
--根据姓名查询成绩
select [STD_INFO].[std_name] from [STD_INFO];
select [COURSE_INFO].[course_name],[SCORES].[score] from [STD_INFO],[COURSE_INFO],[SCORES] where [STD_INFO].[std_name]='张三' and [STD_INFO].[std_id]=[SCORES].[std_id] and [COURSE_INFO].[course_id]=[SCORES].[course_id];
--根据课程名查询成绩
select [COURSE_INFO].[course_name] from [COURSE_INFO];
select [STD_INFO].[std_id],[STD_INFO].[std_name],[SCORES].[score] from [STD_INFO],[COURSE_INFO],[SCORES] where [COURSE_INFO].[course_name]='操作系统' and [COURSE_INFO].[course_id]=[SCORES].[course_id] and [SCORES].[std_id]=[STD_INFO].[std_id];
2、解决方案：
解决方案结构：
Form1.cs[设计]
Form1 属性：
MaximizeBox = False
MinimizeBox = False
Text = 2018机试
事件：
Load = Form1_Load
listView1 属性：
FullRowSelect = True
GridLine = True
View = Details
MultiSelect = False
comboBox1 / comboBox2 属性：
DropDownStyle = DropDownList
button1 / button2 事件：button1_Click / button2_Click
DB.cs:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace test0317
{
internal class DB : IDisposable
{
private SqlConnection sqlConnection;
public DB()
{
sqlConnection = new SqlConnection(@"server=.;database=test0317;Trusted_Connection=SSPI");
sqlConnection.Open();
}
public DataTable GetBySql(string sql)
{
DataTable dt = new DataTable();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(new SqlCommand(sql,sqlConnection));
sqlDataAdapter.Fill(dt);
return dt;
}
public void Dispose()
{
sqlConnection.Close();
}
}
}
Form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace test0317
{
public partial class Form1 : Form
{
DB db;
public Form1()
{
InitializeComponent();
db= new DB();
}
private void Form1_Load(object sender, EventArgs e)
{
DataTable dt = db.GetBySql(@"select [STD_INFO].[std_name] from [STD_INFO];");
for(int i=0; i<dt.Rows.Count; i++)
{
for(int j=0;j<dt.Columns.Count; j++)
{
comboBox1.Items.Add(dt.Rows[i][j].ToString());
}
}
comboBox1.SelectedIndex = 0;
dt = db.GetBySql(@"select [COURSE_INFO].[course_name] from [COURSE_INFO];");
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
comboBox2.Items.Add(dt.Rows[i][j].ToString());
}
}
comboBox2.SelectedIndex = 0;
}
private void button1_Click(object sender, EventArgs e)
{
listView1.Clear();
listView1.Columns.Add("课程名", listView1.Width / 2 - 1, HorizontalAlignment.Left);
listView1.Columns.Add("成绩", listView1.Width / 2 - 1, HorizontalAlignment.Left);
DataTable dt = db.GetBySql(@"select [COURSE_INFO].[course_name],[SCORES].[score] from [STD_INFO],[COURSE_INFO],[SCORES] where [STD_INFO].[std_name]='"+comboBox1.Text+"'and [STD_INFO].[std_id]=[SCORES].[std_id] and [COURSE_INFO].[course_id]=[SCORES].[course_id]");
listView1.BeginUpdate();
for(int i=0; i < dt.Rows.Count; i++)
{
ListViewItem listViewItem = new ListViewItem();
for(int j=0; j < dt.Columns.Count;j++)
{
if (j <= 0)
{
listViewItem.Text = dt.Rows[i][j].ToString();
}
else
{
listViewItem.SubItems.Add(dt.Rows[i][j].ToString());
}
}
listView1.Items.Add(listViewItem);
}
listView1.EndUpdate();
}
private void button2_Click(object sender, EventArgs e)
{
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);
DataTable dt = db.GetBySql(@"select [STD_INFO].[std_id],[STD_INFO].[std_name],[SCORES].[score] from [STD_INFO],[COURSE_INFO],[SCORES] where [COURSE_INFO].[course_name]='" + comboBox2.Text + "' and [COURSE_INFO].[course_id]=[SCORES].[course_id] and [SCORES].[std_id]=[STD_INFO].[std_id];");
listView1.BeginUpdate();
for (int i = 0; i < dt.Rows.Count; i++)
{
ListViewItem listViewItem = new ListViewItem();
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j <= 0)
{
listViewItem.Text = dt.Rows[i][j].ToString();
}
else
{
listViewItem.SubItems.Add(dt.Rows[i][j].ToString());
}
}
listView1.Items.Add(listViewItem);
}
listView1.EndUpdate();
}
}
}
3、结果：
