package com.jdbc;

import java.sql.*;
import java.util.Date;

public class JdbcShiYong {
    public static void Zeng() throws ClassNotFoundException, SQLException {
        //连接数据库
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?useSSL=true","root","root");
        //创建SQL语句
        String sql="insert into student values(default,‘小白‘,‘3‘,‘男‘,‘幼儿园三班‘,‘语文‘,‘武安‘,‘2018-09-01‘)";
        //创建预处理对象
        PreparedStatement ps=connection.prepareStatement(sql);
        //发送并执行SQL语句(执行增删改语句),返回受影响行数
        int num= ps.executeUpdate();
        if(num>0){
            System.out.println("数据添加成功!");
        }else {
            System.out.println("数据添加失败!");
        }
        ps.close();
        connection.close();
    }
    public static void ZengA() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?useSSL=true","root","root");
        String sql="insert into student values(default,‘小黑‘,‘3‘,‘男‘,‘幼儿园三班‘,‘语文‘,‘武安‘,‘2018-09-01‘)";
        PreparedStatement ps=connection.prepareStatement(sql);
        int num= ps.executeUpdate();
        if(num>0){
            System.out.println("数据添加成功!");
        }else {
            System.out.println("数据添加失败!");
        }
        ps.close();
        connection.close();
    }
    public static void Shan() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?useSSL=true","root","root");
        String sql="delete from student where student_id=14";
        PreparedStatement ps=connection.prepareStatement(sql);
        int num= ps.executeUpdate();
        if(num>0){
            System.out.println("数据删减成功!");
        }else {
            System.out.println("数据删减失败!");
        }
        ps.close();
        connection.close();
    }
    public static void Gai() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?useSSL=true","root","root");
        String sql="update student set student_name=‘大白‘ where student_id=13";
        PreparedStatement ps=connection.prepareStatement(sql);
        int num= ps.executeUpdate();
        if(num>0){
            System.out.println("数据修改成功!");
        }else {
            System.out.println("数据修改失败!");
        }
        ps.close();
        connection.close();
    }
    public static void ChaA() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
       Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?useSSL=true","root","root");
       String sql="select * from student";
       PreparedStatement ps=connection.prepareStatement(sql);
        //执行查询语句,返回一个结果集对象(ResultSet)
        ResultSet rs=ps.executeQuery();
        //使用while循环遍历
        //rs.next()该方法让结果指针下移一行并检测当前行是否存在数据，如果存在返回true，否则返回false
        while (rs.next()){
            //根据列名或列编号获取列数据,根据列的类型使用Java中相对应的类型获取
            int student_id=rs.getInt("student_id");
            String student_name=rs.getString("student_name");
            int student_age=rs.getInt("student_age");
            String studengt_sex= rs.getString("student_sex");
            String student_class=rs.getString(5);//数字表述取对应列的数据
            System.out.println("student_id:"+student_id+"student_name:"+student_name+"student_age:"+student_age+"student_sex:"+studengt_sex+"student_class:"+student_class);
        }
        //释放资源,先释放rs，在释放ps，最后释放connection
        rs.close();
        ps.close();
        connection.close();
    }
    public static void ChaB() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?useSSL=true","root","root");
        String sql="select student_id,student_name from student";
        PreparedStatement ps=connection.prepareStatement(sql);
        ResultSet rs=ps.executeQuery();
        while (rs.next()){
            int student_id=rs.getInt("student_id");
            String student_name=rs.getString("student_name");
            System.out.println("student_id:"+student_id+"student_name:"+student_name);
        }
        rs.close();
        ps.close();
        connection.close();
    }
    public static void ChaC() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?useSSL=true","root","root");
        String sql="select count(student_id),sum(student_age) from student";
        PreparedStatement ps=connection.prepareStatement(sql);
        ResultSet rs=ps.executeQuery();
        //当只有一行时用if
        if (rs.next()){
            int count= rs.getInt(1);
            double age= rs.getDouble(2);
            System.out.println("学生总数:"+count+"学生所有年龄总数:"+age);
        }
        rs.close();
        ps.close();
        connection.close();
    }
    public static void CharD() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
     Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?useSSL=true","root","root");
    String sql="select student_id,student_name from student where student_birthday is null";
    PreparedStatement ps=connection.prepareStatement(sql);
    ResultSet rs= ps.executeQuery();
    while (rs.next()){
        int student_id=rs.getInt("student_id");
        String student_name=rs.getString("student_name");
        System.out.println("student_id:"+student_id+"student_name:"+student_name);
    }
    rs.close();
    ps.close();
    connection.close();
    }
    public static  void CharE() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?useSSL=true","root","root");
        String sql="select * from students where students_id in(select students_id from students group by students_id having students_score between 70 and 90)";
    PreparedStatement ps=connection.prepareStatement(sql);
    ResultSet rs= ps.executeQuery();
    while (rs.next()){
        int students_id=rs.getInt("students_id");
        String students_name=rs.getString("students_name");
        double students_score= rs.getDouble("students_score");
        System.out.println("students_id:"+students_id+"students_name:"+students_name+"students_score:"+students_score);
    }
        rs.close();
        ps.close();
        connection.close();
    }

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        CharE();
    }
}

JDBC的基础使用