SQL server范围查询 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;
}