关于MySql的一些学习笔记.

MySql的一些使用方式:
    首先需要配置它的语言要使用GBK的否则会出现乱码,然后一下是连接字符串和驱动.

public static Connection getConn() {
		Connection conn = null;
		if (conn == null) {
			try {
				Class.forName("com.mysql.jdbc.Driver");
				conn=DriverManager.getConnection("jdbc:mysql://localhost/drp","root","123");
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		return conn;
	}

关于MySql的一些常用命令
show databases; //查看当前数据库数量已经名称.
use xxx; //改变当前操作的数据库.默认是在系统下
show tables; //查询当前数据库下的所有表
MySql 的一个分页

 pageSize 每页的数量;
 pageCurrtent 当前那一页;
 select * from  table  limit (pageCurrtent-1)*pageSize,pageSize;
 select * from  table  limit 0,3;第一页
 select * from  table  where name like '%b%' limit 0,3;带查询条件的分页
 select * from  table  where name like '%b%' order by name desc limit 0,3;带查询排序分页

mysql导入语句
\. F:\xxx.sql

 MySql 中的 行列转换语句
select r.stu_no,s.stu_name,c.class_name,
 sum(case when  count_no=1 then record else 0 end) 'aaa',
 sum(case when count_no=2 then record else 0 end) 'bbb',
 sum(case when count_no=3 then record else 0 end) 'ccc'
 from t_record r,t_student s,t_class c
 where r.stu_no=s.stu_no
 and c.class_no=s.stu_class_no
 and c.class_no='f0802'
 group by r.stu_no

public class MySqlDB {
	private static Connection con;

	public static Connection getConnection() {
		if (con == null) {
			try {
				Class.forName("com.mysql.jdbc.Driver");
                con=DriverManager.getConnection("jdbc:mysql://localhost/stumanager?user=root&password=123");
                return con;
			} catch (Exception e) {
				// TODO: handle exception
			    e.printStackTrace();
			}
		}
		return con;
	}
	/**
	 * 执行增,删,改操作
	 * */
	public static int executeSql(String preparedSql,String[] param)
	{
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		int num=0;
		try {
			conn=getConnection();
			pstmt=conn.prepareStatement(preparedSql);
			if(param!=null)
			{
				for(int i=0;i<param.length;i++)
				{
					pstmt.setString(i+1,param[i]);
				}
			}
			num=pstmt.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
		}finally
		{
			closeAll(conn, pstmt, rs);
			
		}
		return  num;
		
	}
	/**
	 * 释放资源
	 */
	public static void closeAll(Connection conn, PreparedStatement pstmt, ResultSet rs) {
		
		try {
			if (rs != null) {
				rs.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		} 
		catch (Exception ex) 
		{
			ex.printStackTrace();
		}
	}
}