2021-10-05

1、MySQL建立数据库、并且进入,创表,插入数据十条

create phone_book;
use phone_book;
create table info(id int(3),name char(8),tel char(11),sex char(2));

2、Java代码操作数据库

public class Third {
	public void MySQL() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection result = DriverManager.getConnection("jdbc:mysql://localhost:3306/phone_book",
				"root","123456");
        System.out.println("---------------查询info通讯录信息-------------------");
        String sql = "select * from info";
		PreparedStatement ques = result.prepareStatement("sql");
		ResultSet returnSet = ques.executeQuery();
		while(returnSet.next()) {
			System.out.print("编号:" + returnSet.getString("id") + " ");
			System.out.print("姓名:" + returnSet.getString("name") + "\t");
			System.out.print("电话:" + returnSet.getString("tel") + " ");
			System.out.println("性别:" + returnSet.getString("sex") + " ");
		}
        
        System.out.println("---------------查询tel为182开头的-------------------");
		String sql1 = "select * from info where tel like ?";
		PreparedStatement ques1 = result.prepareStatement(sql1);
		ques1.setString(1, "182%");
		ResultSet returnSet1 = ques1.executeQuery();
		while(returnSet1.next()) {
			System.out.print("编号:" + returnSet1.getString("id") + " ");
			System.out.print("姓名:" + returnSet1.getString("name") + "\t");
			System.out.print("电话:" + returnSet1.getString("tel") + " ");
			System.out.println("性别:" + returnSet1.getString("sex") + " ");
		}
		
        System.out.println("---------------修改第5条数据,tel改为“12345678901”-------------------");
		String sql2 = "update info set tel = ? where id = ?";
		PreparedStatement ques2 = result.prepareStatement(sql2);
		ques2.setString(1, "12345678901");
		ques2.setInt(2, 5);
        int returnSet2 = ques2.executeUpdate();
		//查询更新后的数据
		String sql3 = "select * from info";
		PreparedStatement ques3 = result.prepareStatement(sql3);
		ResultSet returnSet3 = ques3.executeQuery();
		while(returnSet3.next()) {
			System.out.print("编号:" + returnSet3.getString("id") + " ");
			System.out.print("姓名:" + returnSet3.getString("name") + "\t");
			System.out.print("电话:" + returnSet3.getString("tel") + " ");
			System.out.println("性别:" + returnSet3.getString("sex") + " ");
		}

        System.out.println("---------------新增一条数据-------------------");
		String sql4 = "insert into info values (?,?,?,?)";
		PreparedStatement ques4 = result.prepareStatement(sql4);
		
		ques4.setInt(1, 11);ques4.setString(2, "妲己");
		ques4.setString(3, "13226374698");ques4.setString(4, "女");
		
		//查询更新后的数据
		String sql5 = "select * from info";
		PreparedStatement ques5 = result.prepareStatement(sql3);
		ResultSet returnSet4 = ques5.executeQuery();
		while(returnSet4.next()) {
			System.out.print("编号:" + returnSet4.getString("id") + " ");
			System.out.print("姓名:" + returnSet4.getString("name") + "\t");
			System.out.print("电话:" + returnSet4.getString("tel") + " ");
			System.out.println("性别:" + returnSet4.getString("sex") + " ");
		}

        
		System.out.println("---------------查询所有男性信息-------------------");
		String sql6 = "select * from info where sex = ?";
		PreparedStatement ques6 = result.prepareStatement(sql6);
		ques1.setString(1, "男");
		ResultSet returnSet5 = ques6.executeQuery();
		while(returnSet5.next()) {
			System.out.print("编号:" + returnSet5.getString("id") + " ");
			System.out.print("姓名:" + returnSet5.getString("name") + "\t");
			System.out.print("电话:" + returnSet5.getString("tel") + " ");
			System.out.println("性别:" + returnSet5.getString("sex") + " ");
		}
		
		System.out.println("---------------修改表中第10条数据,姓名改为“大帅哥”-------------------");
		String sql7 = "update info set name = ? where id = ?";
		PreparedStatement ques7 = result.prepareStatement(sql7);
		ques2.setString(1, "大帅哥");
		ques2.setInt(2, 10);
		//查询更新后的数据
		String sql8 = "select * from info";
		PreparedStatement ques8 = result.prepareStatement(sql8);
		ResultSet returnSet8 = ques8.executeQuery();
		while(returnSet8.next()) {
			System.out.print("编号:" + returnSet8.getString("id") + " ");
			System.out.print("姓名:" + returnSet8.getString("name") + "\t");
			System.out.print("电话:" + returnSet8.getString("tel") + " ");
			System.out.println("性别:" + returnSet8.getString("sex") + " ");
		}
		
		System.out.println("---------------删除一条数据-------------------");
		String sql9 = "delete from info where id = ?";
		PreparedStatement ques9 = result.prepareStatement(sql9);
		ques9.setInt(1, 3);
        int returnSet11 = ques9.executeUpdate();
		//查询更新后的数据
		String sql10 = "select * from info";
		PreparedStatement ques10 = result.prepareStatement(sql10);
		ResultSet returnSet10 = ques10.executeQuery();
		while(returnSet10.next()) {
			System.out.print("编号:" + returnSet10.getString("id") + " ");
			System.out.print("姓名:" + returnSet10.getString("name") + "\t");
			System.out.print("电话:" + returnSet10.getString("tel") + " ");
			System.out.println("性别:" + returnSet10.getString("sex") + " ");
		}
		

		result.close();
		returnSet.close();
		ques.close();
	}
	
}
上一篇:十.java版Spring Cloud Spring Boot b2b2c o2o 之ADO 通过 GetString() 加速脚本


下一篇:Java实现腾讯云短信定时推送天气信息(通俗易懂)