一、写SQL语句
1、创建schooljava数据库。
create database scholjava;
2、创建student数据表包含 id、name姓名、tel电话、sex性别字段。
CREATE DATABASE schooljava;
USE schooljava;
CREATE TABLE student (
id INT(11),
name VARCHAR(25),
tel INT(11),
sex VARCHAR(6)
);
DESC student;
3、往student表中加10条数据。
INSERT INTO student VALUES
-> (1,'张三',1234567890,'男'),
-> (2,'李黑',1564481133,'男'),
-> (3,'小明',1215541841,'男'),
-> (4,'老六',1234567890,'男'),
-> (5,'小辉',1395482655,'男'),
-> (6,'小红',1595262000,'女'),
-> (7,'王女',1825523665,'女'),
-> (8,'小兰',1234567890,'女'),
-> (9,'小刘',1234567890,'男'),
-> (10,'小郑',1234567890,'男');
二、写Java代码(要求只用PreparedStatement对象,变化的值都用?代替)
- 查询student表中所有学生信息
- student表中新增三条数据
- 查询student表中电话以182开头的所有学生信息(SQL要用到模糊查询like)
- 修改student表中第5条数据,电话改为“13124235300”
- 查询student表中所有男性的学生信息
- 修改student表中第10条数据,姓名改为“大帅哥”
- 查询student表中电话以139开头的所有学生信息(SQL要用到模糊查询like)
- 删除student表中第3条数据
- 查询student表中所有女性的学生信息
- 查询student表中所有学生信息
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 schooljava { Connection connection; public static void main(String[] args) throws SQLException { schooljava studentsql=new schooljava(); studentsql.getConnection(); System.out.println("查询所有学生数据,结果为:"); studentsql.selectAll(); System.out.println("添加三条学生数据,结果为:"); studentsql.add(11,"嘿嘿","133586246","男"); studentsql.add(12,"黑黑","139565655","男"); studentsql.add(13,"红红","133656859","女"); studentsql.selectAll(); System.out.println("查询中电话以182开头的所有学生信息,结果为:"); studentsql.selectPart182(); System.out.println("修改第5条数据,结果为:"); studentsql.update5(5,"131242353"); studentsql.selectAll(); System.out.println("查询男性所有学生信息,结果为:"); studentsql.selectMan(); System.out.println("修改第10条数据,结果为:"); studentsql.update10(10,"大帅哥"); studentsql.selectAll(); System.out.println("查询中电话以139开头的所有学生信息,结果为:"); studentsql.selectPart139(); System.out.println("删除第3条数据,结果为:"); studentsql.delete(3); studentsql.selectAll(); System.out.println("查询女性所有学生信息,结果为:"); studentsql.selectWoman(); System.out.println("查询所有学生数据,结果为:"); studentsql.selectAll(); studentsql.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 update5(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); //获取statement对象 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(); } //添加课程 public void add(int id,String name,String tel,String sex) throws SQLException { //获取statement对象 PreparedStatement preparedStatement=connection.prepareStatement("insert into student value(?,?,?,?);"); 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","1234"); } //查询所有学生数据 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.getLong("tel")+" "); System.out.println(resultSet.getString("sex")+" "); } //第六步:关闭连接释放资源 resultSet.close(); preparedStatement.close(); } public void close()throws SQLException { connection.close(); } }