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();
}
}