SQL语句查询

查询语句

string strCom = "select * from 管理员 where 用户 = '" + users + "'";

string strCom = "select * from 学生 where ID = '" + num.ToString() + "'";

string strCom = "select * from 学生 where ID = '" + num + "'";

string strCom = "select * from 学生 ";

string strCom = "select * from 出入记录 where 学号 ='" + xuehao + "'";

string strCom = "select * from 学生 where 学号 = '" + xuehao + "'";

string strCom = "select * from 出入记录 ";

string strCom = "select * from 出入记录 where 学号 = '" + xuehao + "'";

string strCom = "delete  FROM  出入记录 where 学号= '" + num + "'";

string strCom = "select ID from 学生 where 学号='" + xuehao + "'";

查询之后按ID字段排序

 string strCom = "select ID from 学生 order by ID";  //查找之后按ID字段排序

查询某时间段内的记录

string strCom = "select * from 学生 where 入学年份>='" + start + "-1-1' and 入学年份<=' " + stop + "-12-30'";

string strCom = "SELECT 学号 FROM  学生 where 入学年份 >= '" + start + "-1-1'and  入学年份<= '" + stop + "-12-30'";

string strCom = "select ID from 学生 where 入学年份>='" + start + "-1-1' and 入学年份<=' " + stop + "-12-30'";

string strCom = "select * from 出入记录 where 进入日期>='" + start + "' and 进入日期<=' " + stop + "'";

查询当天的记录

string strCom = "select * from 出入记录 where DATEDIFF(DAY,进入日期,GETDATE()) = 0";

两表查询

string strCom = "select * from 出入记录 where 学号 = (select 学号 from 学生 where 教室  = '" + jiaoshi + "')";

 string strCom = "select * from 出入记录 where ID =(SELECT MAX(ID)from 出入记录 where 学号 = '" + num + "')";

联合查询

string strCom = "SELECT 学生.学号, 学生.姓名,学生.教室,学生.学院, 学生.班级,出入记录.进入日期,出入记录.进入时间,出入记录.离开日期,出入记录.离开时间,出入记录.计时 FROM  学生 INNER JOIN 出入记录 ON 学生.学号 = 出入记录.学号 and 进入日期 = '" + date + "' order by 进入时间";

根据查询内容返回某一个字段

public string searchInTable_管理员_根据用户_返回密码(string users){     SqlConnection myConnection = new SqlConnection(Gloable.strConnection);       string strCom = "select * from 管理员 where 用户 = '" + users + "'";     DataSet myDataSet = new DataSet();     myConnection.Open();     SqlDataAdapter myCommand = new SqlDataAdapter(strCom, myConnection);     myCommand.Fill(myDataSet, "管理员");     myConnection.Close();     if (myDataSet.Tables["管理员"].Rows.Count == 0)         return "";     string mima = myDataSet.Tables["管理员"].Rows[0]["密码"].ToString();     return mima;}

根据查找内容，返回所有符合的记录

 public DataSet searchInTable_学生() {     SqlConnection myConnection = new SqlConnection(Gloable.strConnection);       string strCom = "select * from 学生 ";     DataSet myDataSet = new DataSet();     myConnection.Open();     SqlDataAdapter myCommand = new SqlDataAdapter(strCom, myConnection);     myCommand.Fill(myDataSet, "学生");     myConnection.Close();     //返回所有符合的记录     return myDataSet;}

添加语句

string ss = "insert into 管理员 (用户,姓名,密码,注册日期) values('" + users + "','" + name + "','" + mima + "','" + DateTime.Now.ToString("g") + "')";

string ss = "insert into 学生 (ID,入学年份,学号,姓名,学院,班级,教室,指纹) values('" + number.ToString() + "','" + ruxuenianfen + "','" + xuehao + "','" + name + "','" + xueyuan + "','" + banji + "','" + jiaoshi + "','" + sendStr + "')";

string ss = "insert into 出入记录 (学号,姓名,进入日期,进入时间,进入) values('" + xuehao + "','" + name + "','" + System.DateTime.Now.ToString("d")+ "','" + System.DateTime.Now.ToString("T")+ "','"+ System.DateTime.Now.ToString("G")+"')";

public void addToTable_管理员(string users, string name, string mima){    SqlConnection con = new SqlConnection(Gloable.strConnection);    string ss = "insert into 管理员 (用户,姓名,密码,注册日期) values('" + users + "','" + name + "','" + mima + "','" + DateTime.Now.ToString("g") + "')";    SqlCommand com = new SqlCommand(ss, con);     con.Open();    com.ExecuteNonQuery();    con.Close();    }

删除语句

string strCom = "delete from 管理员 where 用户 = '" + users + "'";

string strCom = "delete from 学生 where 学号 = '" + xuehao + "'";

string strCom = "delete from 学生 where 入学年份>='" + start + "-1-1' and 入学年份<=' " + stop + "-12-30'";

public void deleateInTable_出入记录_根据学号(string num){      SqlConnection myConnection = new SqlConnection(Gloable.strConnection);      string strCom = "delete  FROM  出入记录 where 学号= '" + num + "'";      DataSet myDataSet = new DataSet();      myConnection.Open();      SqlDataAdapter myCommand = new SqlDataAdapter(strCom, myConnection);      myCommand.Fill(myDataSet, "出入记录");      myConnection.Close();}

更新记录

string ss = "update  出入记录 set 离开日期= '" + System.DateTime.Now.ToString("d") + "', 离开时间 = '" + System.DateTime.Now.ToString("T") + "', 离开 = '" + System.DateTime.Now.ToString("G") + "' where ID =(SELECT MAX(ID) FROM 出入记录 WHERE 学号 ='" + xuehao + "')";

string strCom = "update  出入记录 set 计时= '" + time + "'where ID =(SELECT MAX(ID) FROM 出入记录 WHERE 学号 ='" + num + "')";

string strCom = "update  出入记录 set 累计= '" + time + "'where ID =(SELECT MAX(ID) FROM 出入记录 WHERE 学号 ='" + num + "')";

public DataSet addToTable_出入记录_根据学号_添加累计(string num, TimeSpan time){     SqlConnection myConnection = new SqlConnection(Gloable.strConnection);     string strCom = "update  出入记录 set 累计= '" + time + "'where ID =(SELECT MAX(ID) FROM 出入记录 WHERE 学号 ='" + num + "')";     DataSet myDataSet = new DataSet();     myConnection.Open();     SqlDataAdapter myCommand = new SqlDataAdapter(strCom, myConnection);     myCommand.Fill(myDataSet, "出入记录");     myConnection.Close();     //返回所有符合的记录     return myDataSet;}