classsystemdbsql.java

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

package cn.com.util.calsssystemcommon;

public interface ClassSystemDBsql {
	/**
	 * 查询所有的课程信息和执教老师
	 */

	public static final String ALLCLASS_INFO = "select classnum,classname,totaltime,classtime,credithour,classestate,"
			+ "t.t_id,t_name,t_duty,classremake from classtable c,teacherinfotable t where c.t_id = t.t_id ";

	/**
	 * 获得课程总数目
	 */
	public static final String CLASS_DATA = "select count(*) from classtable";

	/**
	 * 添加课程
	 */
	public static final String CLASS_APPEND = "insert into classtable values (?,?,?,?,?,?,?,?)";

	// 查询特定编号的课程信息
	public static final String CLASS_SELECT = "select * from classtable where classnum = ?";

	// 根据老师编号查询老师姓名
	public static final String T_NAME_FIND = "select t_name from teacherinfotable where t_id = ?";

	/**
	 * "课程编号", "课程名称", "课时", "课程时段", "学分", "课程状态", "执教老师编号", "执教老师姓名",
	 * "职务","课程备注"查找
	 */
	public static final String C_T_FIND = "select classnum,classname,totaltime,classtime,credithour,classestate,"
			+ "t.t_id,t_name,t_duty,classremake from classtable c,teacherinfotable t where c.t_id = t.t_id and classnum = ?";

	/**
	 * 修改课程信息
	 */
	public static final String C_T_UPDATE = "update classtable set classname = ?,credithour = ?,"
			+ "totaltime = ? ,classestate = ?,t_id = ?,classtime = ?,classremake = ? where classnum = ?";

	/**
	 * 删除所选择的课程信息
	 */
	public static final String CLASS_DELETE = "delete from classtable where classnum = ?";

	/**
	 * 按学号查询课程信息
	 */
	public static final String STU_FIND = "select * from studentinfotable where s_id = ?";

	/**
	 * 根据学号查找其选的所有课程
	 */
	public static final String FINDCLASS_BYNO = "select c.classnum,classname,totaltime,classtime,credithour,classestate,"
			+ "t.t_id,t_name,t_duty,classremake from studentinfotable s, classtable c,studentexamchivement e,teacherinfotable t"
			+ "where s.s_id = e.s_id and e.classnum = c.classnum and c.t_id = t.t_id and s.s_id = ?";

	/**
	 * 根据学号获得其选的所有课程数
	 */
	public static final String FINDCLASS_BYCNO = "select count(*) from studentinfotable s, classtable c,studentexamchivement e,teacherinfotable t"
			+ "where s.s_id = e.s_id and e.classnum = c.classnum and c.t_id = t.t_id and s.s_id = ? order by s.s_id";

	/**
	 * 根据老师编号查询其所教的课程
	 */
	public static final String FINDCLASS_BYTNO = "select classnum,classname,credithour,totaltime,classestate,classtime,"
			+ "classremake from classtable where t_id = ?";

	public static final String T_COUNT = "select count(*) from classtable where t_id = ?";

	/**
	 * 查询所有的课程
	 */
	public static final String ALLCLASS_FIND = "select classnum,classname,credithour,totaltime,classestate,classtime,"
			+ "classremake from classtable ";

	/**
	 * 学生选课
	 */
	public static final String CLASS_CHOOSE = "insert into studentexamchivement(s_id,classnum) values (?,?) ";
	/**
	 * 学生删除所选课
	 */
	public static final String CLASSDELETE_CHOOSE = "delete from studentexamchivement  where s_id = ? and classnum = ? ";

	/**
	 * 插入登记时间
	 */
	public static final String CLASS_BOOK = "insert into classbook values (?,?,?,?)";

	/**
	 * 记录登记次数
	 */
	public static final String CLASS_BOOKTIME = "update classbook set booktime = booktime + 1 where s_id = ? and classnum = ?";

	/**
	 * 查询学生所有课程的出勤情况
	 */
	public static final String BOOKTIME_FIND = "select classnum,classname,booktime from classbook where s_id = ? and classnum = ?";

	/**
	 * 查看有登记记录的课程总数
	 */
	public static final String CLASSBOOK_COUNT = "select count(*) from classbook where s_id = ? and classnum = ?";

	public static final String CLASSINFO_PART = "select classnum,classname,classtime,credithour from classtable where classnum = ?";
	
	/**
	 * 检查是否存在该学生
	 */
	public static final String STU_CHECK = "select * from studentinfotable where s_id = ?";
	/**
	 * 检查是否存在该老师
	 */
	public static final String TEA_CHECK = "select * from teacherinfotable where t_id = ?";
	
	/**
	 *查询所有老师的编号
	 * 
	 */
	public static final String TEA_N_N_FIND = "select t_id,t_name from teacherinfotable";
	/**
	 * 出勤查询
	 */
	public static final String BRETRAY_FIND = "select classnum,classname,booktime from classbook where s_id = ?";
	/**
	 * 得到所选的课程数
	 */
	public static final String CLASSCOUNT = "select count(*) from classbook where s_id = ?";
}