import java.sql.Connection;
import java.sql.DriverManager;         //数据库设计模式在后面的附加图片
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class test {
  public static void main(String args[]) throws ClassNotFoundException, SQLException{
	  
	  String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/test";
		String user = "root";
		String password = "123456";
		
		Connection conn = null;
		Statement statement = null;
		ResultSet res = null;
		String sql = "select * from students";
		Class.forName(driver);
		conn = DriverManager.getConnection(url, user, password);
		statement = conn.createStatement();
		
		System.out.println("更新前的数据库：");
		res = statement.executeQuery(sql);
		while(res.next()){
			System.out.println(res.getString("sno")+"\t"+res.getString("sname")+"\t"+res.getString("ssex")+"\t"+res.getString("sdept"));
		}
		
		String insertsql2 ="insert into students values (162,'zhgng9','m','computer')";  //更新语句  add 注意更新时主键不能够重复
		statement.executeUpdate(insertsql2);    
		
		System.out.println("更新后的数据库：");
		res = statement.executeQuery(sql);
		while(res.next()){
			System.out.println(res.getString("sno")+"\t"+res.getString("sname")+"\t"+res.getString("ssex")+"\t"+res.getString("sdept"));
		}
		
		
//		MySQL 语法
//		SELECT column_name(s)
//		FROM table_name
//		LIMIT number
		System.out.println("只要数据库中的前3行记录：");
		String sql2 = "select * from students limit 3";      //写法比较好  这是mysql的写法 其他的数据库写法不同
		res = statement.executeQuery(sql2);
		while(res.next()){
			System.out.println(res.getString("sno")+"\t"+res.getString("sname")+"\t"+res.getString("ssex")+"\t"+res.getString("sdept"));
		}
		
		//UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
		System.out.println("修改数据库中的记录：");
		sql2 = "update students set sname = 'guang' where sno=160";      // 更新语句的写法
		statement.executeUpdate(sql2);                               //这个地方是更新语句
		res = statement.executeQuery(sql);                           //这个是查询语句
		while(res.next()){
			System.out.println(res.getString("sno")+"\t"+res.getString("sname")+"\t"+res.getString("ssex")+"\t"+res.getString("sdept"));
		}
		
		//DELETE FROM 表名称 WHERE 列名称 = 值
		System.out.println("删除数据库中的记录：");
		insertsql2 ="delete from students where ssex='n'";  //删除语句
		statement.executeUpdate(insertsql2);    
		res = statement.executeQuery(sql);                           //这个是查询语句
		while(res.next()){
			System.out.println(res.getString("sno")+"\t"+res.getString("sname")+"\t"+res.getString("ssex")+"\t"+res.getString("sdept"));
		}
		
		if(!conn.isClosed()){
			conn.close();
		}
	  
  }
}