学生管理系统 数据库版

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表中所有学生信息