数据库编程
--创建数据库
create database StudentManageDB
--创建主键约束
alter table Students add constraint pk_StudentId primary key(StudentId)
--创建唯一约束
alter table Students add constraint uq_StudentId unique(StudentIdNo)
--创建检查约束
alter table Students add constraint ck_PhoneNumber check(len(PhoneNumber)=11)
--创建外键约束
alter table Students add constraint fk_ClassId foreign key (ClassId) references StudentClass(ClassId)
--级联删除
alter table StudentClass add constraint fk_ClassId foreign key (ClassId) references Students on delete cascade
--创建视图
create view ww_sttistics
as
select Products.ProductName,SUM(Quantity) as number ,sum(Amount) as total
from Products,Orders,OrderDetails
where Products.ProdcuctID = OrderDetails.ProductID and OrderDetails.OrederID = Orders.OrderID
group by Products.ProductName
插入
Insert into xx(xx,xx,xx) values(xx,xx,xx)
更新
update Students set Gender='男',Age=25 where StudentId=10000
删除数据
delete from Students where StudentId=10000
select*from Students --查询全部行和列
select StudentName,Gender,Birthday,Age,StudentIdNo from Students where Age>=24
select*from ScoreList where SQLServerDB is null--查询空列
模糊查询
select StudentName,StudentAddress from Students
where StudentAddress like'天津%' --天津xxxxxxx
where StudentName like'%小%' --xxxx小xxxxxx
排序后显示
select StudentId,CSharp as C#,DB=SQLServerDB
from ScoreList where CSharp>80
order by CSharp ASC--降序用DESC 相同值想比较其他值可以在后面继续加
求和 计数
select SUM(CSharp) as C#总成绩 from ScoreList
select 总人数=COUNT(*)from Students
内连接
select ScoreList.StudentId,C#成绩=CSharp,StudentName
from ScoreList
inner join Students on Students.StudentId=ScoreList.StudentId
inner join StudentClass on Students.ClassId=StudentClass.ClassId
where CSharp>80
分组查询
select 班级=StudentClass.ClassName,人数=COUNT(*),C#平均分=AVG(CSharp),DB平均分=AVG
(SQLServerDB)
from Students
inner join StudentClass on Students.ClassId=StudentClass.ClassId
inner join ScoreList on Students.StudentId=ScroeList.StudentId
group by StudentClass.ClassName
having AVG(CSharp)>=70 and AVG(SQLServerDB)>=70 --条件不能用where 用having
C#
SQLhelper:
public static readonly string connStr = "Server=.;DataBase=emp;Uid=sa;Pwd=123456";
public static int Update(string sql)
{
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static object GetSingleResult(string sql)
{
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static DataSet GetDataSet(string sql)
{
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static SqlDataReader GetReader(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
if(conn.State==ConnectionState.Open)
{
conn.Close();
}
//将异常信息写入日志
throw new Exception("应用程序和数据库连接出现问题" + ex.Message);
}
//因为别的地方还要读，不能加finally关掉连接
}
XML读写：
public void WriteXml()
{
//Random rd = new Random();
//实例化一个XmlDocument类
XmlDocument xmlDoc = new XmlDocument();
//创建xml文档的声明
XmlDeclaration declaration = xmlDoc.CreateXmlDeclaration("1.0", "UTF-8", "yes");
xmlDoc.AppendChild(declaration);
//一个xml文档必须有一个根元素
//创建根节点students
XmlElement elem = xmlDoc.CreateElement("students");
//把根节点添加到xml文档
xmlDoc.AppendChild(elem);
//读取txt文件中的数据，并把逗号去掉
FileStream fs = new FileStream("D:\\code\\XMLtest\\StudentScore.txt", FileMode.Open);
StreamReader sr = new StreamReader(fs, Encoding.UTF8);
string text = sr.ReadToEnd();
sr.Close();
fs.Close();
string[] newtext = text.Split(new char[3] { '，', ',','\n'});
//将从txt读取到的数据一个个读入xml文件中
int a = 1;
XmlElement elem1 = xmlDoc.CreateElement("student");
foreach (string item in newtext)
{
string newitem=item.Trim();//成绩后面有空格 去掉空格
switch (a)
{
case 1:
elem.AppendChild(elem1);
XmlElement elem1_1 = xmlDoc.CreateElement("学号");
elem1.AppendChild(elem1_1);
elem1_1.InnerText = newitem;
a++;
break;
case 2:
XmlElement elem1_2 = xmlDoc.CreateElement("姓名");
elem1.AppendChild(elem1_2);
elem1_2.InnerText = newitem;
a++;
break;
case 3:
XmlElement elem1_3 = xmlDoc.CreateElement("科目");
elem1.AppendChild(elem1_3);
elem1_3.InnerText = newitem;
a++;
break;
case 4:
XmlElement elem1_4 = xmlDoc.CreateElement("成绩");
elem1.AppendChild(elem1_4);
elem1_4.InnerText = newitem;
a = 1;
break;
}
}
xmlDoc.Save("students.xml");
}
public void ReadXml()
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("students.xml");
//获取根节点
XmlNode node = xmlDoc.SelectSingleNode("students");
XmlNodeList nodeList = node.ChildNodes;
foreach(XmlNode item in nodeList)
{
//强制类型转换
XmlElement xmle = (XmlElement)item;
string stuId = xmle.GetAttribute("StudentId");
string Class = xmle.GetAttribute("Class");
//先输出学号班级信息
Console.WriteLine("学号:" + stuId + " " + "班级:" + Class);
XmlNodeList nodeList1 = item.ChildNodes;
//输出三个成绩信息
foreach (XmlNode item1 in nodeList1)
{
XmlElement xmle1 = (XmlElement)item1;
string subject = xmle1.Name;
string score = xmle1.InnerText;
Console.WriteLine(subject+":"+score);
//读出数据后修改里面的值
if(stuId=="10012")
xmle1.InnerText = "60";
}
}
xmlDoc.Save("students.xml");
Console.ReadLine();
}
排序类
class NameDESC : IComparer<Student>
{
public int Compare(Student x, Student y)//比较函数 返回值为true
//则第一个参数在第二个参数前
{
return y.StudentName.CompareTo(x.StudentName);//若为true，则y的名字更大，会排在x的后面
//即按名字降序排序
}
}
stuList.Sort(new NameDESC());
dgvStudentList.DataSource = stuList;
5162
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
