MySQL小技巧

用于记录MySQL各种实用的查询

快速找到每个xx中xx最高的

例：快速找到每门课程的成绩最高的学生

​
所有成绩：

思路一：按课程分组后排序取第一位

可以发现能返回每门成绩最高的一个，但是如果有多个第一名的话，则只能返回一个

思路二：若没有比他成绩高的则输出

原理：

1. 将分数表自连接，并查询，s1为原表，s2为比较用
2. 将左边表(s1)的第一列和右边表(s2)比较，若小于即可计数
3. 若计数小于1即为最高成绩，那就输出

---

获得指定范围的随机数

语法：ROUND(RAND()*范围)返回一个0-范围的随机数

//获得一个0-9的随机小数，在通过round来四舍五入即可获得0-10的随机数
SELECT ROUND(RAND()*10)

---

判断一段文字中是否含有中文

//输出所有名字是中文的学生
SELECT * FROM student WHERE LENGTH(sname)!=CHAR_LENGTH(sname);

原理：使用CHAR_LENGTH时，不管是汉字还是英文都是算一个，而LENGTH中汉字根据字符编码长度不同，一般是2以上，若两者不相等，即有汉字

简单的SQL注入

下面是一段简单的登录验证，验证语句使用的代码拼接，但是这样写能够被SQL注入

Scanner sc = new Scanner(System.in);
System.out.println("请输入登录的用户名：");
String name = sc.nextLine();
System.out.println("请输入登录的密码：");
String pwd = sc.nextLine();

try {
    System.out.println(pwd);
    Connection connection = DriverManager.getConnection("jdbc:mysql:///jdbc?serverTimezone=UTC", "root", "root");
    String sql = "select * from t_user where name = '"+name+"' and pwd = '"+pwd+"'";
    System.out.println(sql);
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    ResultSet resultSet = preparedStatement.executeQuery();
    if(resultSet.next()){
        System.out.println("登录成功");
    }else {
        System.out.println("登录失败");
    }
} catch (SQLException e) {
    e.printStackTrace();
}

即我不需要知道账号密码的情况下依旧可以登录
案例：

上面输的密码就等同于下面这个查询，绕过登录验证，但是如果使用PreparedStatement的占位符即可防止这种情况

改进后的代码:

 Scanner sc = new Scanner(System.in);
        System.out.println("请输入登录的用户名：");
        String name = sc.nextLine();
        System.out.println("请输入登录的密码：");
        String pwd = sc.nextLine();

        try {
            System.out.println(pwd);
            Connection connection = DriverManager.getConnection("jdbc:mysql:///jdbc?serverTimezone=UTC", "root", "root");
            String sql = "select * from t_user where name = ? and pwd = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, pwd);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                System.out.println("登录成功");
            }else {
                System.out.println("登录失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

测试：

可以看到同样的密码却会登陆失败

上面的代码等同于

区别：
代码拼接：String sql = "select * from t_user where name = '"+name+"' and pwd = '"+pwd+"'";
占位符： String sql = "select * from t_user where name = ? and pwd = ?";
使用占位符后，会将所有的单引号去掉并在头尾加上单引号