dbsql.java

培训时做的学生管理系统.基于J2SE平台开发

package cn.com.util;

public interface DBSql {
/**
 * 教师基本信息sql语句
 * 
 */
	
//	 查询所有老师信息
	public static final String SELECT_TEACHER_ALL="select * from teacherinfotable t order by t.t_id ";
	
//	 全部查询信息 行数
	public static final String SELECT_TEACHER_ALL_COUNT="select count(*) from "
		+ "teacherinfotable t";
	
	// 根据学号查询
	public static final String SELECT_TEACHER_BY_T_ID ="select * from " +
	" teacherinfotable  t where  t.t_id = ? order by t.t_id";
	
//	 根据教师编号查询获得行数
	public static final String SELECT_TEACHER_BY_T_ID_COUNT ="select count(*) from "
		+ " teacherinfotable  t  where  t.t_id = ? order by t.t_id";
	
	
	// 根据教师职务查询
	public static final String SELECT_TEACHER_BY_DUTY ="select * from " +
	" teacherinfotable  t where t.t_duty like ? or "
			+ " t.t_duty like ? or t.t_duty like ? or t.t_duty like ?  order by t.t_id";
	
//	 根据教师职务查询获得行数
	public static final String SELECT_TEACHER_BY_DUTY_COUNT ="select count(*) from "
		+ " teacherinfotable  t  where  t.t_duty like ? or "
			+ " t.t_duty like ? or t.t_duty like ? or t.t_duty like ?  order by t.t_id";
	
	// 根据姓名查询
	public static final String SELECT_TEACHER_BY_T_NAME = "select "
			+ " * from teacherinfotable t "
			+ "where   t.t_name like ? or "
			+ " t.t_name like ? or t.t_name like ? or t.t_name like ? order by t.t_id ";

	// 根据姓名查询获得行数
	public static final String SELECT_TEACHER_BY_T_NAME_COUNT = "select count(*) from " +
			" teacherinfotable t where   t.t_name like ? or "
			+ " t.t_name like ? or t.t_name like ? or t.t_name like ? order by t.t_id ";
	
	//删除所选教师信息
	public static final String DELETE_TEACHER_BY_T_ID ="delete from " +
	" teacherinfotable  t where  t.t_id = ? ";
	
	//增加教师信息
	public static final String INSERT_TEACHER = "insert into teacherinfotable(t_id,t_name,t_sex,t_age,t_duty,t_qq,t_email,t_tel) "
		+ "values(?,?,?,?,?,?,?,?)";
	
	//修改教师信息
	public static final String UPDATE_TEACHER = "update teacherinfotable set " +
			" t_name = ?,t_sex=?,t_age=?,t_duty=?,t_qq=?,t_email=?,t_tel=?" +
			" where t_id=? ";
	
	
	
	
	
	
	
	
	
	
	/**
	 * 老师基本信息 高级查询 sql语句
	 * 
	 */
	
	
	public static final String SELECT_TEACHER_011 = "select * from " +
	" teacherinfotable   where t_id = ? and ( t_duty like ? or "
		+ " t_duty like ? or t_duty like ?  or t_duty like ? ) order by t_id ";
	
		public static final String SELECT_TEACHER_101 = "select * from " +
		" teacherinfotable  "
		+ " where ( t_name like ? or "
		+ " t_name like ? or t_name like ? " +
				" or t_name like ? ) and t_id = ? ";
	
		public static final String SELECT_TEACHER_110 = "select * from " +
	" teacherinfotable  where "
		+ " ( t_name like ? or "
		+ " t_name like ? or t_name like ? " +
				" or t_name like ? ) and ( t_duty like ? or "
		+ " t_duty like ? or t_duty like ?  or t_duty like ? ) ";
	
	
		public static final String SELECT_TEACHER_111 = "select * from " +
	" teacherinfotable where ( t_name like ? or "
		+ " t_name like ? or t_name like ? " +
				" or t_name like ? ) and ( t_duty like ? or "
		+ " t_duty like ? or t_duty like ?  or t_duty like ? )  and t_id = ? ";
	
	
	
	
	
	
	
	
	
	
	
	
	/**
	 * 学生基本信息sql语句
	 * 
	 */
//	 查询所有学生信息
	public static final String SELECT_STU_ALL="select * from " +
			" studentinfotable s order by s.s_id ";
	
//	 全部查询信息 行数
	public static final String SELECT_STU_ALL_COUNT="select count(*) from "
		+ "studentinfotable s order by s.s_id";
	
	// 根据学号查询
	public static final String SELECT_STU_BY_S_ID ="select * from " +
	" studentinfotable  s where  s.s_id = ? order by s.s_id";
	
//	 根据学号查询获得行数
	public static final String SELECT_STU_BY_S_ID_COUNT ="select count(*) from "
		+ " studentinfotable  s  where  s.s_id = ? order by s.s_id";
	
	
	// 根据组号查询
	public static final String SELECT_STU_BY_G_ID ="select * from " +
	" studentinfotable  s where  s.g_id = ? order by s.s_id";
	
//	 根据组号查询获得行数
	public static final String SELECT_STU_BY_G_ID_COUNT ="select count(*) from "
		+ " studentinfotable  s  where  s.g_id = ? order by s.s_id";
	
	// 根据姓名查询
	public static final String SELECT_STU_BY_S_NAME = "select "
			+ " * from studentinfotable s "
			+ "where   s.s_name like ? or "
			+ " s.s_name like ? or s.s_name like ? or s.s_name like ? order by s.s_id ";

	// 根据姓名查询获得行数
	public static final String SELECT_STU_BY_S_NAME_COUNT = "select count(*) from " +
			" studentinfotable s where   s.s_name like ? or "
			+ " s.s_name like ? or s.s_name like ? or s.s_name like ? order by s.s_id ";
	
	//删除所选学生信息
	public static final String DELETE_STU_BY_S_ID ="delete from " +
	" studentinfotable  s where  s.s_id = ? ";
	
	//增加学生信息
	public static final String INSERT_STU = "insert into studentinfotable(s_id,g_id,s_name,s_sex,s_grade,s_school,s_professional,s_tel,s_qq,s_email) "
		+ "values(?,?,?,?,?,?,?,?,?,?)";
	
	//增加学生后增加考试课程信息
	public static final String INSERT_STU_EXAM = "insert into studentexamchivement(s_id,g_id,classnum) "
		+ "values(?,?,?)";
	
	
	//增加学生后增加考试课程信息
	public static final String INSERT_STU_OR = "insert into studentordinarychivement(s_id,g_id) "
		+ "values(?,?)";
	
	
	//修改学生信息
	public static final String UPDATE_STU = "update studentinfotable set " +
			" g_id = ?,s_name=?,s_sex=?,s_grade=?,s_school=?,s_professional=?,s_tel=?,s_qq=?,s_email=?" +
			" where s_id=? ";
	
	
	
	/**
	 * 学生基本信息 高级查询 sql语句
	 * 
	 */
	
	
	public static final String SELECT_STU_011 = "select * from " +
	" studentinfotable  s where s.g_id = ? and s.s_id=? order by s.s_id ";
	
		public static final String SELECT_STU_101 = "select * from " +
		" studentinfotable  s "
		+ " where ( s.s_name like ? or "
		+ " s.s_name like ? or s.s_name like ? " +
				" or s.s_name like ? ) and s.s_id = ? ";
	
		public static final String SELECT_STU_110 = "select * from " +
	" studentinfotable  s where "
		+ " ( s.s_name like ? or "
		+ " s.s_name like ? or s.s_name like ? " +
				" or s.s_name like ? ) and s.g_id = ? ";
	
	
		public static final String SELECT_STU_111 = "select * from " +
	" studentinfotable  s where ( s.s_name like ? or "
		+ " s.s_name like ? or s.s_name like ? " +
				" or s.s_name like ? ) and s.g_id = ? and s.s_id = ? ";
	
	
	
	
	
	
	
	
	
	
	
/**
 * 学生平时成绩管理sql语句
 * 
 */
	
//	 查询所有学生成绩
	public static final String SELECT_OR_ALL="select s.s_id,s.g_id,s.s_name,t.ordinary"
			+ " from studentordinarychivement t,studentinfotable s "
			+ " where  t.s_id=s.s_id order by t.s_id";
	
//	 全部查询 行数
	public static final String SELECT_OR_ALL_COUNT="select count(*) "
		+ " from studentordinarychivement t,studentinfotable s "
		+ " where  t.s_id=s.s_id ";
	
	
	// 根据学号查询
	public static final String SELECT_OR_BY_S_ID ="select s.s_id,s.g_id,s.s_name,t.ordinary"
		+ " from studentordinarychivement t,studentinfotable s "
		+ " where  t.s_id=s.s_id and s.s_id = ? order by t.s_id";
	
//	 根据学号查询获得行数
	public static final String SELECT_OR_BY_S_ID_COUNT ="select count(*) "
		+ " from studentordinarychivement t,studentinfotable s "
		+ " where  t.s_id=s.s_id and s.s_id = ? order by t.s_id";
	
	
	// 根据组号查询
	public static final String SELECT_OR_BY_G_ID ="select s.s_id,s.g_id,s.s_name,t.ordinary"
		+ " from studentordinarychivement t,studentinfotable s "
		+ " where  t.s_id=s.s_id and s.g_id = ? order by t.s_id";
	
//	 根据组号查询获得行数
	public static final String SELECT_OR_BY_G_ID_COUNT ="select count(*) "
		+ " from studentordinarychivement t,studentinfotable s "
		+ " where  t.s_id=s.s_id and s.g_id = ? order by t.s_id";
	
	// 根据姓名查询
	public static final String SELECT_OR_BY_S_NAME = "select "
			+ " s.s_id,s.g_id,s.s_name,t.ordinary from "
			+ " studentordinarychivement t,studentinfotable s "
			+ "where t.s_id=s.s_id and ( s.s_name like ? or "
			+ " s.s_name like ? or s.s_name like ? or s.s_name like ? )";

	// 根据姓名查询获得行数
	public static final String SELECT_OR_BY_S_NAME_COUNT = "select count(s.s_id)  from "
		+ " studentordinarychivement t,studentinfotable s "
		+ "where t.s_id=s.s_id and ( s.s_name like ? or "
		+ " s.s_name like ? or s.s_name like ? or s.s_name like ? )";
	
	// 修改选中学生所选的课程
	public static final String UPDATE_OR_BY_STUID = " update studentordinarychivement set " +
			" ordinary = ? where s_id=? ";
	
	// 获得平时成绩学生学号姓名分数
	public static final String SELECT_OR_STU_SNO_SNAME_EXAM = "select s.s_id,s.s_name,t.ordinary"
		+ " from studentordinarychivement t,studentinfotable s "
		+ " where  t.s_id=s.s_id order by t.s_id";

	// 获得平时成绩学生学号姓名分数行数
	public static final String SELECT_OR_STU_SNO_SNAME_EXAM_COUNT = "select "
			+ " count(*) from studentordinarychivement t,studentinfotable s "
			+ " where  t.s_id=s.s_id ";
	
	
	// 修改学生平时成绩
	public static final String UPDATE_OR_BY_CLASS = "UPDATE "
			+ "studentordinarychivement set   ordinary= ? where s_id = ? ";
	
	
	
	
	
	
	
	/**
	 * 学生平时成绩管理 高级查询语句 
	 * 
	 */
	
	
	
	
	
	public static final String SELECT_OR_011 = "select "
		+ " s.s_id,s.g_id,s.s_name,t.exam from "
		+ " studentordinarychivement t,studentinfotable s "
		+ "where  t.s_id=s.s_id and " +
				" s.g_id = ? and s.s_id=?";
	
		public static final String SELECT_OR_101 = "select "
		+ " s.s_id,s.g_id,s.s_name,t.exam from "
		+ " studentordinarychivement t,studentinfotable s "
		+ "where  t.s_id=s.s_id and ( s.s_name like ? or "
		+ " s.s_name like ? or s.s_name like ? " +
				" or s.s_name like ? ) and s.s_id = ? ";
	
		public static final String SELECT_OR_110 = "select "
			+ " s.s_id,s.g_id,s.s_name,t.exam from "
			+ " studentordinarychivement t,studentinfotable s where "
		+ "t.s_id=s.s_id and  ( s.s_name like ? or "
		+ " s.s_name like ? or s.s_name like ? " +
				" or s.s_name like ? ) and s.g_id = ? ";
	
	
		public static final String SELECT_OR_111 = "select "
			+ " s.s_id,s.g_id,s.s_name,t.exam from "
			+ " studentordinarychivement t,studentinfotable s "
		+ "where t.s_id=s.s_id and  ( s.s_name like ? or "
		+ " s.s_name like ? or s.s_name like ? " +
				" or s.s_name like ? ) and s.g_id = ? and s.s_id = ? ";
	
	
	
	
	
	
	
/**
 * 学生考试成绩管理sql语句
 * 
 */
	// 查询所有学生成绩
	public static final String SELECT_ALL = " select s.s_id,s.g_id,s.s_name,c.classnum,c.classname,t.exam "
			+ " from studentexamchivement t,classtable c,studentinfotable s "
			+ " where t.classnum=c.classnum and t.s_id=s.s_id order by t.s_id";

	// 全部查询 行数
	public static final String SELECT_ALL_COUNT = "select count(*) from "