第一部分 建表
- 打开SSMS(sql server management studio),建数据库和建表
- 建表时,关于数据类型:
nvarchar(n):字符串,最大长度n
timestamp:存储时间,默认设置为getdate()
- 读入数据:考察csharp读取和写入xml文件
- Sql常用语句
(1)查询语句:
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value
(2)连接两个表
Select * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号
(3)连接三个表
Select * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号
(4)插入内容
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
INSERT INTO table_name
VALUES (value1,value2,value3,...);
(5)排序
SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC; (默认ASC升序,DESC降序)
(6)统计某一个类别的数量
SELECT COUNT(column_name) as newname FROM table_name
group by column_name1, column_name2
(7)模糊查询:查询中间含有123的ID
select * from hs_user where ID like '%123%'
(8)having
HAVING aggregate_function(column_name) operator value;
(在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用)
- c#执行sql语句方法:
(1)sql执行方法一:
SqlCommand command = new SqlCommand(strCmd, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while(reader.Read())
{
MessageBox.Show(reader["学号"].ToString());
}
connection.Close();
(2)sql执行方法二:
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(strCmd, connection);
DataTable dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
dGVScores.DataSource = dataTable;
(3)sql插入指令执行:
connection.Open();
using(SqlCommand command = new SqlCommand(strCmd, connection))
{
command.ExecuteNonQuery();
}
connection.Close();
6、windows form 控件:
一、DataGridView控件:显示网络表格
(1)在DataGridView中添加单行数据
int index = this.dGVScores.Rows.Add();
this.dGVScores.Rows[index].Cells[0].Value = "1";
this.dGVScores.Rows[index].Cells[1].Value = "2";
(2)显示所有数据:
dGVScores.DataSource = dataTable;
二、comboBox控件:显示下拉菜单
(1)添加列表:
comboBox1.Items.Add(dr["EmpNo"]);
- xml文件处理
(1)读取所有xml文件中的数据
XmlDocument doc = new XmlDocument();
XmlReaderSettings settings = new XmlReaderSettings();
settings.IgnoreComments = true; //忽略文档里面的注释
XmlReader reader = XmlReader.Create(@"C:\Users\76982\Desktop\sample.xml", settings);
doc.Load(reader);
得到根节点students:
XmlNode xn = doc.SelectSingleNode("students");
得到节点下的所有子节点
XmlNodeList xmlStudents = xn.ChildNodes;
//处理每个student
foreach (XmlNode xnl in xmlStudents)
{
Students student = new Students();
XmlNodeList xnls = xnl.ChildNodes;
//获取子节点的内容
student.studentID = xnls.Item(0).InnerText;
student.studentName = xnls.Item(1).InnerText;
//获取子节点的属性值
student.projectName_1 = xnls.Item(2).Attributes.GetNamedItem("课程名").InnerText;
student.projectID_1 = xnls.Item(2).Attributes.GetNamedItem("课程号").InnerText;
student.projectScore_1 = xnls.Item(2).InnerText;
student.projectName_2 = xnls.Item(3).Attributes.GetNamedItem("课程名").InnerText;
student.projectID_2 = xnls.Item(3).Attributes.GetNamedItem("课程号").InnerText;
student.projectScore_2 = xnls.Item(3).InnerText;
/*
MessageBox.Show(student.studentID + student.studentName +
student.projectName_1 + student.projectScore_1 + student.projectID_1 +
student.projectName_2 + student.projectScore_2 + student.projectID_2);
*/
students.Add(student);
}
(2)写入数据到xml文件
//xml文件设置
XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;
string xmlPath = @"C:\软件工程考研复试机试——李钊华\sample2016.xml";
XmlWriter writer = XmlWriter.Create(xmlPath, settings);
//写入文档声明
writer.WriteStartDocument();
//写入新元素
writer.WriteStartElement("students");
//写入分支元素
writer.WriteStartElement("student");
//写入分支元素
writer.WriteStartElement("学号");
writer.WriteString("1");//写入元素内容
writer.WriteEndElement();
writer.WriteStartElement("姓名");
writer.WriteString("李钊华");
writer.WriteEndElement();
writer.WriteStartElement("课程");
writer.WriteAttributeString("课程号", "1");//写入元素属性
writer.WriteAttributeString("课程名", "开心");
writer.WriteString("90");
writer.WriteEndElement();
writer.WriteEndElement();
writer.WriteEndElement();
writer.WriteEndDocument();
writer.Flush();//刷新
writer.Close();
结果:
<?xml version="1.0" encoding="utf-8"?>
<students>
<student>
<学号>1</学号>
<姓名>李钊华</姓名>
<课程 课程号="1" 课程名="开心">90</课程>
</student>
</students>
常见问题汇总:
问题一:sqlserver 2008 保存好的数据表不能更改数据类型问题解决
如图操作即可
ssms提醒已创建好的表是无效表的话记得刷新