学生管理系统 数据库版结果 查询student表中所有学生信息

1.创建school_java数据库

CREATE DATABASE schooljava;
USE schooljava;
CREATE TABLE student (
id INT(11),
name VARCHAR(25),
tel INT(11),
sex VARCHAR(6)
);
DESC student;

java代码

package Mysql;
?
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 schooljava=new schooljava();
?? ??? ?schooljava.getConnection();
?? ??? ?schooljava.selectAll();
?? ??? ?schooljava.add(11,"一号",1824567890,"男");
?? ??? ?schooljava.add(12,"二号",1394567890,"女");
?? ??? ?schooljava.add(13,"三号",1893154635,"男");
?? ??? ?schooljava.select182();
?? ??? ?schooljava.xiugai();
?? ??? ?schooljava.chanan();
?? ??? ?schooljava.dsg();
?? ??? ?schooljava.select139();
?? ??? ?schooljava.sc();
?? ??? ?schooljava.chanv();
?? ??? ?schooljava.selectAll();
?? ?}
?? ?public void add(int id, String name,int tel,String sex) throws SQLException {
?? ??? ?PreparedStatement preparedStatement=connection.prepareStatement("insert into student values(?,?,?,?);");
?? ??? ?preparedStatement.setInt(1, id);
?? ??? ?preparedStatement.setString(2, name);
?? ??? ?preparedStatement.setInt(3,tel);
?? ??? ?preparedStatement.setString(4,sex);
?? ??? ?preparedStatement.executeUpdate();
?? ?}
?? ?public void getConnection()throws SQLException {
?? ??? ?DriverManager.registerDriver(new Driver());//第一步：注册驱动
?? ??? ?connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/school","root","1234");//第二步：获取连接
}
?
?? ?public void selectAll() throws SQLException {?? ??? ?//查询课程表所有数据
?? ??? ?PreparedStatement preparedStatement=connection.prepareStatement("select*from ?student");//第三步：获取statement对象
?? ??? ?ResultSet resultSet=preparedStatement.executeQuery();//第四步：执行SQL语句返回结果集
?? ??? ?while(resultSet.next()){
?? ??? ??? ?System.out.print(resultSet.getInt("id"));
?? ??? ??? ?System.out.print(resultSet.getString("name"));
?? ??? ??? ?System.out.print(resultSet.getInt("tel"));
?? ??? ??? ?System.out.println(resultSet.getString("sex"));//第五步：遍历结果集
?? ??? ??? ?
?? ??? ?}resultSet.close();
?? ??? ?preparedStatement.close();}
?? ?public void select182() 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.getInt("tel"));
?? ??? ??? ?System.out.println(resultSet.getString("sex"));}
?? ??? ?
}
?? ?
?? ?public void xiugai() throws SQLException {
?? ??? ?PreparedStatement preparedStatement=connection.prepareStatement("update student set tel=1312423530 where id=5");
?? ??? ?preparedStatement.executeUpdate( );
?? ??? ?
?? ?}
?? ?public void chanan() 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.getInt("tel"));
?? ??? ??? ?System.out.println(resultSet.getString("sex"));}
?? ?}
?? ?public void dsg() throws SQLException {
?? ??? ?PreparedStatement preparedStatement=connection.prepareStatement("update student set name='大帅哥' where id=10");
?? ??? ?preparedStatement.executeUpdate( );
?? ??? ?
?? ?}
?? ?public void select139() 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.getInt("tel"));
?? ??? ??? ?System.out.println(resultSet.getString("sex"));}
?? ??? ?}
?? ?public void sc() throws SQLException {
?? ??? ?PreparedStatement preparedStatement=connection.prepareStatement("delete from student where id=3");
?? ??? ?preparedStatement.executeUpdate( );
?? ??? ?
?? ?}
?? ?public void chanv() 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.getInt("tel"));
?? ??? ??? ?System.out.println(resultSet.getString("sex"));}}
?? ?
}

结果?

查询student表中所有学生信息

student表中新增三条数据

3.查询student表中电话以182开头的所有学生信息（SQL要用到模糊查询like）

4.修改student表中第5条数据，电话改为“13124235300”?

5.查询student表中所有男性的学生信息?

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

7.查询student表中电话以139开头的所有学生信息（SQL要用到模糊查询like）

8.删除student表中第3条数据

9.查询student表中所有女性的学生信息

10.查询student表中所有学生信息