用sql查找学号第4位是“2”的学生信息

要从学生信息表中查询学号第四位是"2"的学生信息，你可以使用SQL的LIKE操作符来匹配特定模式。但是，由于MySQL不直接支持正则表达式来查找子串，我们可以先将学号转换为字符串并使用SUBSTRING_INDEX函数来提取第四位。假设你的表名为Sinfo，列名分别为student_id（学号）和其他相关信息。

在Python中，结合`pymysql`库，可以这样实现查询：

# 假设你已经有了数据库连接
cursor.execute(f"""
SELECT * FROM Sinfo 
WHERE SUBSTRING_INDEX(student_id, '_', -1) LIKE '%_{'2'}%'
""")  # 使用'_'作为分隔符，'-1'表示取最后一个部分

# 获取查询结果
results = cursor.fetchall()

# 打印查询结果
for row in results:
    print(row)

这里的SQL查询使用了`SUBSTRING_INDEX`函数来取得学号的倒数第二个字符（即第四位），然后通过LIKE语句查找以 "_" 加上 "2" 结尾的学号。

相关问题

用MySQL查找学号第4位是“2”的学生信息

要从MySQL数据库中查找学号第四位是"2"的学生信息，您需要构造一个SQL查询语句。由于Python通常用于与数据库交互，这里假设我们已经有了一个连接到MySQL的库（如`pymysql`或`mysql-connector-python`）。

Python中使用`pandas`库操作SQL查询结果时，可以这样做[^1]：

import pymysql
import pandas as pd

# 建立数据库连接
conn = pymysql.connect(host='your_host', user='your_username', password='your_password', db='your_database')

try:
    # 创建游标对象
    cursor = conn.cursor()

    # 构造SQL查询（这里假设student_num字段是数字类型）
    sql = "SELECT * FROM cloud_one WHERE SUBSTRING(student_num, -1) = '2'"  # 使用SUBSTRING函数获取最后一位
    cursor.execute(sql)

    # 获取查询结果并转换为pandas DataFrame
    result = pd.read_sql_query(sql, conn)

    # 打印查询结果
    print(result)
finally:
    # 关闭连接
    conn.close()

这段代码执行了一个SQL查询，选取`cloud_one`表中第四位是"2"的学生记录。如果`student_num`不是数字类型，可能需要其他方式来解析它。

通过Java控制台开发一个学生成绩管理系统，使用MySQL创建学生成绩管理系统数据库，实现管理员端的功能管理员功能1系统登录验证功能2添加指定个数学生信息3查找学生信息，可以选择按姓名查找或者按学号查找4删除指定学号的学生信息5对学生成绩进行排序，可按学号、按姓名、按科目、按平均分进行排序6插入学生信息7查看所有学生信息；退出系统功能

好的，我可以为您提供一些思路和代码示例。

首先，您需要在MySQL中创建一个名为“student”的数据库，并创建一个名为“score”的表，其中包含以下字段：id（主键，自动递增）、name（学生姓名）、number（学生学号）、subject（科目）、score（成绩）。

然后，您需要编写Java代码连接到MySQL数据库，具体示例代码如下：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String URL = "jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=UTC";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";

    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}

接着，您可以编写管理员端的代码实现各项功能。以下是示例代码：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Scanner;

public class Admin {
    private static Scanner scanner = new Scanner(System.in);

    public static void main(String[] args) {
        // 登录验证
        System.out.print("请输入管理员用户名：");
        String username = scanner.nextLine();
        System.out.print("请输入管理员密码：");
        String password = scanner.nextLine();
        if (!"admin".equals(username) || !"123456".equals(password)) {
            System.out.println("用户名或密码错误，登录失败！");
            return;
        }
        System.out.println("登录成功！");

        while (true) {
            System.out.println("请选择操作：");
            System.out.println("1. 添加学生信息");
            System.out.println("2. 查找学生信息");
            System.out.println("3. 删除学生信息");
            System.out.println("4. 排序学生信息");
            System.out.println("5. 插入学生信息");
            System.out.println("6. 查看所有学生信息");
            System.out.println("7. 退出系统");
            System.out.print("请选择操作（输入数字1-7）：");
            int choice = scanner.nextInt();
            scanner.nextLine(); // 读取多余的回车符

            switch (choice) {
                case 1:
                    addStudent();
                    break;
                case 2:
                    searchStudent();
                    break;
                case 3:
                    deleteStudent();
                    break;
                case 4:
                    sortStudent();
                    break;
                case 5:
                    insertStudent();
                    break;
                case 6:
                    showAllStudents();
                    break;
                case 7:
                    System.out.println("感谢使用学生成绩管理系统！");
                    return;
                default:
                    System.out.println("输入有误，请重新选择！");
                    break;
            }
        }
    }

    // 添加学生信息
    private static void addStudent() {
        System.out.print("请输入要添加的学生数量：");
        int count = scanner.nextInt();
        scanner.nextLine(); // 读取多余的回车符

        List<Student> students = new ArrayList<>();
        for (int i = 1; i <= count; i++) {
            System.out.println("请输入第" + i + "个学生的信息：");
            System.out.print("姓名：");
            String name = scanner.nextLine();
            System.out.print("学号：");
            String number = scanner.nextLine();
            System.out.print("科目：");
            String subject = scanner.nextLine();
            System.out.print("成绩：");
            double score = scanner.nextDouble();
            scanner.nextLine(); // 读取多余的回车符

            Student student = new Student(name, number, subject, score);
            students.add(student);
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO score (name, number, subject, score) VALUES (?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            for (Student student : students) {
                pstmt.setString(1, student.getName());
                pstmt.setString(2, student.getNumber());
                pstmt.setString(3, student.getSubject());
                pstmt.setDouble(4, student.getScore());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            System.out.println("添加成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(pstmt, conn);
        }
    }

    // 查找学生信息
    private static void searchStudent() {
        System.out.print("请输入要查找的学生信息（姓名或学号）：");
        String keyword = scanner.nextLine();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM score WHERE name LIKE ? OR number LIKE ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            pstmt.setString(2, "%" + keyword + "%");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String number = rs.getString("number");
                String subject = rs.getString("subject");
                double score = rs.getDouble("score");
                System.out.println("id：" + id + "，姓名：" + name + "，学号：" + number + "，科目：" + subject + "，成绩：" + score);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, pstmt, conn);
        }
    }

    // 删除学生信息
    private static void deleteStudent() {
        System.out.print("请输入要删除的学生学号：");
        String number = scanner.nextLine();

        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM score WHERE number = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, number);
            int count = pstmt.executeUpdate();
            if (count > 0) {
                System.out.println("删除成功！");
            } else {
                System.out.println("学号为" + number + "的学生不存在！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(pstmt, conn);
        }
    }

    // 排序学生信息
    private static void sortStudent() {
        System.out.println("请选择排序方式：");
        System.out.println("1. 按学号排序");
        System.out.println("2. 按姓名排序");
        System.out.println("3. 按科目排序");
        System.out.println("4. 按平均分排序");
        System.out.print("请选择排序方式（输入数字1-4）：");
        int choice = scanner.nextInt();
        scanner.nextLine(); // 读取多余的回车符

        List<Student> students = getAllStudents();

        switch (choice) {
            case 1:
                Collections.sort(students, new Comparator<Student>() {
                    @Override
                    public int compare(Student o1, Student o2) {
                        return o1.getNumber().compareTo(o2.getNumber());
                    }
                });
                break;
            case 2:
                Collections.sort(students, new Comparator<Student>() {
                    @Override
                    public int compare(Student o1, Student o2) {
                        return o1.getName().compareTo(o2.getName());
                    }
                });
                break;
            case 3:
                Collections.sort(students, new Comparator<Student>() {
                    @Override
                    public int compare(Student o1, Student o2) {
                        return o1.getSubject().compareTo(o2.getSubject());
                    }
                });
                break;
            case 4:
                Collections.sort(students, new Comparator<Student>() {
                    @Override
                    public int compare(Student o1, Student o2) {
                        double avg1 = (o1.getScore() + o2.getScore()) / 2.0;
                        double avg2 = (o2.getScore() + o2.getScore()) / 2.0;
                        return Double.compare(avg1, avg2);
                    }
                });
                break;
            default:
                System.out.println("输入有误，请重新选择！");
                return;
        }

        System.out.println("排序结果如下：");
        for (Student student : students) {
            System.out.println("姓名：" + student.getName() + "，学号：" + student.getNumber() + "，科目：" + student.getSubject() + "，成绩：" + student.getScore());
        }
    }

    // 插入学生信息
    private static void insertStudent() {
        System.out.print("请输入要插入的学生位置（从1开始）：");
        int index = scanner.nextInt();
        scanner.nextLine(); // 读取多余的回车符

        System.out.println("请输入要插入的学生信息：");
        System.out.print("姓名：");
        String name = scanner.nextLine();
        System.out.print("学号：");
        String number = scanner.nextLine();
        System.out.print("科目：");
        String subject = scanner.nextLine();
        System.out.print("成绩：");
        double score = scanner.nextDouble();
        scanner.nextLine(); // 读取多余的回车符

        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT COUNT(*) FROM score";
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            rs.next();
            int count = rs.getInt(1);
            if (index < 1 || index > count + 1) {
                System.out.println("位置输入有误，请重新选择！");
                return;
            }

            sql = "INSERT INTO score (name, number, subject, score) VALUES (?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.setString(2, number);
            pstmt.setString(3, subject);
            pstmt.setDouble(4, score);
            pstmt.executeUpdate();

            System.out.println("插入成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(pstmt, conn);
        }
    }

    // 查看所有学生信息
    private static void showAllStudents() {
        List<Student> students = getAllStudents();
        for (Student student : students) {
            System.out.println("姓名：" + student.getName() + "，学号：" + student.getNumber() + "，科目：" + student.getSubject() + "，成绩：" + student.getScore());
        }
    }

    // 获取所有学生信息
    private static List<Student> getAllStudents() {
        List<Student> students = new ArrayList<>();

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM score";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String number = rs.getString("number");
                String subject = rs.getString("subject");
                double score = rs.getDouble("score");
                Student student = new Student(name, number, subject, score);
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, pstmt, conn);
        }

        return students;
    }
}

以上就是一个简单的学生成绩管理系统的实现，希望对您有帮助！