java作业

1.win+r打开命令行，输入cmd，按住shift+ctrl+enter快捷键以管理员身份打开控制系统

使用net start mysql 启动数据库，输入mysql -uroot -p1125：密码

由于school_java数据库已经创建，我们直接use school_java数据库

创建学生表

往表中插入10条数据

数据库数据插入成功

java代码部分 ：

1.查询表中所有学生信息

}//增加数据结束

2.新增加三条数据

//新增

mysqlDemo.add(11, "张慕辰", "18214343131","男");

mysqlDemo.add1(12, "张沐辰", "13014343132","女");

mysqlDemo.add2(13, "张牧尘", "13914343133","男");

//增加数据

public void add(int Id, String Name,String Tel,String Sex) throws SQLException {

PreparedStatement preparedStatement =

connection.prepareStatement("insert into student values(?,?,?,?);");//问号是符号位

preparedStatement.setInt(1,Id);//数字代表的是第一个问号，id代表的是传入参数

preparedStatement.setString(2,Name);

preparedStatement.setString(3,Tel);

preparedStatement.setString(4,Sex);

preparedStatement.executeUpdate();//修改的方法

}

public void add1(int Id, String Name,String Tel,String Sex) throws SQLException {

PreparedStatement preparedStatement =

connection.prepareStatement("insert into student values(?,?,?,?);");//问号是符号位

preparedStatement.setInt(1,Id);//数字代表的是第一个问号，id代表的是传入参数

preparedStatement.setString(2,Name);

preparedStatement.setString(3,Tel);

preparedStatement.setString(4,Sex);

preparedStatement.executeUpdate();//修改的方法

}

public void add2(int Id, String Name,String Tel,String Sex) throws SQLException {

PreparedStatement preparedStatement =

connection.prepareStatement("insert into student values(?,?,?,?);");//问号是符号位

preparedStatement.setInt(1,Id);//数字代表的是第一个问号，id代表的是传入参数

preparedStatement.setString(2,Name);

preparedStatement.setString(3,Tel);

preparedStatement.setString(4,Sex);

preparedStatement.executeUpdate();//修改的方法

//查询数据

 

 

//查询课程表所有数据

public void selectAll() throws SQLException {

PreparedStatement preparedStatement = connection.prepareStatement("select*from student");//准备查询语句

ResultSet resultSet =preparedStatement.executeQuery();

while(resultSet.next() ) {

System.out.print(resultSet.getInt("id")+" ");

System.out.print(resultSet.getString("name"));

System.out.print(resultSet.getString("Tel"));

System.out.println(resultSet.getString("Sex"));//打印输出SQL语句的字段，返回接结果集

}

resultSet.close(); //关闭连接

preparedStatement .close();

}

//查询学生表中以182开头的所有学生信息

public void selectAll1() throws SQLException {

PreparedStatement preparedStatement = connection.prepareStatement("select*from student where Tel like'%182%'");//准备查询语句

ResultSet resultSet =preparedStatement.executeQuery();

while(resultSet.next()) {

System.out.print(resultSet.getInt("id")+" ");

System.out.print(resultSet.getString("name"));

System.out.print(resultSet.getString("Tel"));

System.out.println(resultSet.getString("Sex"));//打印输出SQL语句的字段

}

resultSet.close();

preparedStatement .close();

}

//查询学生表中以139开头的所有学生信息

public void selectAll2() throws SQLException {

PreparedStatement preparedStatement = connection.prepareStatement("select*from student where Tel like'%139%'");//准备查询语句

ResultSet resultSet =preparedStatement.executeQuery();

while(resultSet.next()) {

System.out.print(resultSet.getInt("id")+" ");

System.out.print(resultSet.getString("name"));

System.out.print(resultSet.getString("Tel"));

System.out.println(resultSet.getString("Sex"));//打印输出SQL语句的字段

}

resultSet.close();

preparedStatement .close();

}

//查询男性所有信息

public void selectAll3() throws SQLException {

PreparedStatement preparedStatement = connection.prepareStatement("select*from student where Sex ='男'");//准备查询语句

ResultSet resultSet =preparedStatement.executeQuery();

while(resultSet.next()) {

System.out.print(resultSet.getInt("id")+" ");

System.out.print(resultSet.getString("name"));

System.out.print(resultSet.getString("Tel"));

System.out.println(resultSet.getString("Sex"));//打印输出SQL语句的字段

}

resultSet.close();

preparedStatement .close();

}

//查询女性所有信息

public void selectAll4() throws SQLException {

PreparedStatement preparedStatement = connection.prepareStatement("select*from student where Sex ='女'");//准备查询语句

ResultSet resultSet =preparedStatement.executeQuery();

while(resultSet.next()) {

System.out.print(resultSet.getInt("id")+" ");

System.out.print(resultSet.getString("name"));

System.out.print(resultSet.getString("Tel"));

System.out.println(resultSet.getString("Sex"));//打印输出SQL语句的字段

}

resultSet.close();

preparedStatement .close();

}//查询结束

//修改数据

修改student表中第10条数据，姓名改为“大帅哥”

public void update10(int id, String name) throws SQLException {

//获取statement对象

PreparedStatement preparedStatement=

connection.prepareStatement("update student set name= ? where id= ?;");

preparedStatement.setString(1, name);

preparedStatement.setInt(2, id);

//获取statement对象

preparedStatement.executeUpdate();

}

//删除数据

public void delete(int id) throws SQLException {

//获取statement对象

PreparedStatement preparedStatement=

connection.prepareStatement("delete from student where id= ?;");

preparedStatement.setInt(1,id);

//执行SQL语句返回结果集

preparedStatement.executeUpdate();

}

//最后再次查询数据

//完整代码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.cj.jdbc.Driver;

public class MysqlDemo {

	Connection connection;
	
	public static void main(String[] args) throws SQLException {
		MysqlDemo mysqlDemo = new MysqlDemo();
		mysqlDemo.getConnection();
		System.out.println("查询学生表中数据，结果为：");
		mysqlDemo.selectAll();
		System.out.println("新增三条数据，数据为");
		mysqlDemo.add(11, "张慕辰", "18214343131","男");
		mysqlDemo.add(12, "张沐辰", "13014343132","女");
		mysqlDemo.add(13, "张牧尘", "13914343133","男");
		mysqlDemo.selectAll();
		System.out.println("查询中电话以182开头的所有学生信息,结果为：");
		mysqlDemo.selectPart182();
		System.out.println("修改数据5，数据为：13124235300");
		mysqlDemo.update(5,"13124235300");
		mysqlDemo.selectAll();
		System.out.println("查询男性所有学生信息,结果为：");
		mysqlDemo.selectMan();
		System.out.println("修改第10条数据,结果为：");
		mysqlDemo.update10(10,"大帅哥");
		mysqlDemo.selectAll();
		System.out.println("查询中电话以139开头的所有学生信息,结果为：");
		mysqlDemo.selectPart139();
		System.out.println("删除数据3");
		mysqlDemo.delete(3);
		mysqlDemo.selectAll();
		System.out.println("查询女性所有学生信息,结果为：");
		mysqlDemo.selectWoman();
		System.out.println("查询学生表中数据，结果为：");
		mysqlDemo.selectAll();
		mysqlDemo.close();
		}
	//查询女性所有学生信息
		public void selectWoman() throws SQLException {
			//获取statement对象
			PreparedStatement preparedStatement=
			connection.prepareStatement("select*from student where sex='女';");
					//执行SQL语句返回结果集
			ResultSet resultSet=preparedStatement.executeQuery();
						//遍历结果集
			while(resultSet.next()) {
				System.out.print(resultSet.getInt("id")+" ");
				System.out.print(resultSet.getString("name")+" ");
				System.out.print(resultSet.getString("tel")+" ");
				System.out.println(resultSet.getString("sex")+" ");
							}
						//关闭连接释放资源
						resultSet.close();
						preparedStatement.close();
		}
		
	 
		//删除student表中第3条数据
		public void delete(int id) throws SQLException {
			//获取statement对象
			PreparedStatement preparedStatement=
			connection.prepareStatement("delete from student where id= ?;");
			preparedStatement.setInt(1,id);
						//执行SQL语句返回结果集
	    	preparedStatement.executeUpdate();							
		}
		
		//查询中电话以139开头的所有学生信息
		public void selectPart139() throws SQLException {
			//获取statement对象
					PreparedStatement preparedStatement=
					connection.prepareStatement("select*from student where tel like '139%';");
					//执行SQL语句返回结果集
					ResultSet resultSet=preparedStatement.executeQuery();
					//遍历结果集
					while(resultSet.next()) {
						System.out.print(resultSet.getInt("id")+" ");
						System.out.print(resultSet.getString("name")+" ");
						System.out.print(resultSet.getString("tel")+" ");
						System.out.println(resultSet.getString("sex")+" ");
						}
					//关闭连接释放资源
					resultSet.close();
					preparedStatement.close();							
					}
	 
		
		//修改student表中第10条数据，姓名改为“大帅哥”
		public void update10(int id, String name) throws SQLException {
			//获取statement对象
					PreparedStatement preparedStatement=
			connection.prepareStatement("update student set name= ? where id= ?;");
					preparedStatement.setString(1, name);
					preparedStatement.setInt(2, id);
					//获取statement对象
					preparedStatement.executeUpdate();
				}	
	 
		//查询所有男性的学生信息
		public void selectMan() throws SQLException {
			//获取statement对象
		PreparedStatement preparedStatement=
		connection.prepareStatement("select*from student where sex='男';");
				//执行SQL语句返回结果集
		ResultSet resultSet=preparedStatement.executeQuery();
					//遍历结果集
		while(resultSet.next()) {
			System.out.print(resultSet.getInt("id")+" ");
			System.out.print(resultSet.getString("name")+" ");
			System.out.print(resultSet.getString("tel")+" ");
			System.out.println(resultSet.getString("sex")+" ");
						}
					//关闭连接释放资源
					resultSet.close();
					preparedStatement.close();					
					}
		
	 
	//修改第5条数据
	public void update(int Id, String Tel) throws SQLException {
		//第三步：获取statement对象
		PreparedStatement preparedStatement = 
			connection.prepareStatement
			("update student set Tel = ? where Id = ?");
		preparedStatement.setString(1, Tel);
		preparedStatement.setInt(2, Id);
		//第四步：执行SQL语句
		preparedStatement.executeUpdate();
	}
	//查询电话以182开头的所有学生信息
	public void selectPart182() throws SQLException {
		//获取statement对象
			PreparedStatement preparedStatement=
			connection.prepareStatement("select*from student where tel like '182%';");
			//执行SQL语句返回结果集
			ResultSet resultSet=preparedStatement.executeQuery();
			//遍历结果集
			while(resultSet.next()) {
				System.out.print(resultSet.getInt("id")+" ");
				System.out.print(resultSet.getString("name")+" ");
				System.out.print(resultSet.getString("tel")+" ");
				System.out.println(resultSet.getString("sex")+" ");
				}
			//关闭连接释放资源
			resultSet.close();
			preparedStatement.close();		
			}
	
//新增3条数据
		public void add(int id, String name,String Tel,String Sex)throws SQLException {
			//第三步：获取statement对象
			PreparedStatement preparedStatement = 
				connection.prepareStatement
				("insert into student values(?,?,?,?);");
			preparedStatement.setInt(1, id);
			preparedStatement.setString(2, name);
			preparedStatement.setString(3, Tel);
			preparedStatement.setString(4, Sex);
			//第四步：执行SQL语句
			preparedStatement.executeUpdate();
	}
	
	
		//连接数据库步骤
		//注册驱动，获取连接
		public void getConnection()throws SQLException {
			//第一步：注册驱动
			DriverManager.registerDriver(new Driver());//注册司机
			//第二步：获取连接
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/school_java","root","1125");
			//什么技术，什么数据库，什么主机地址localhost，什么端口号3306，什么数据库名字，什么名字，什么密码
		}
		//查询学生表所有数据
		public void selectAll()throws SQLException{
			//第三步：获取statement对象
			PreparedStatement preparedStatement = connection.prepareStatement("select * from student;");//准备好执行对象
			//第四步：执行SQL语句返回结果集
			ResultSet resultSet = preparedStatement.executeQuery();//执行代码
			//第五步：遍历结果集
			while(resultSet.next()) {//遍历结果集
				System.out.print(resultSet.getInt("Id")+("   "));
				System.out.print(resultSet.getString("Name")+("   "));
				System.out.print(resultSet.getString("Tel")+("   "));
				System.out.println(resultSet.getString("Sex")+("   "));
				
			}
			//第六步：关闭连接释放资源
			resultSet.close();
			preparedStatement.close();
		}
		//关闭连接
		public void close()throws SQLException{
			connection.close();
		};
}