SELECT 列名1, 列名2, ... FROM 表名 ORDER BY 排序依据的列;
-- 使用LENGTH函数按字符串长度排序 SELECT product_name FROM products ORDER BY LENGTH(product_name) DESC;
-- 使用DATE函数按日期部分排序 SELECT order_id, order_date FROM orders ORDER BY DATE(order_date);
-- 使用UPPER函数实现不区分大小写的排序 SELECT username FROM users ORDER BY UPPER(username);
// 加载JDBC驱动 Class.forName("com.mysql.cj.jdbc.Driver");
// 建立数据库连接 String url = "jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8"; String username = "your_username"; String password = "your_password";
Connection connection = DriverManager.getConnection(url, username, password);
// 创建排序查询语句 String sql = "SELECT id, name, salary FROM employees ORDER BY salary DESC, name ASC"; PreparedStatement statement = connection.prepareStatement(sql);
// 执行查询并获取结果 ResultSet resultSet = statement.executeQuery();
-- 没有索引的排序需要全表扫描和临时文件排序 SELECT * FROM employees ORDER BY hire_date DESC;
-- 为hire_date创建索引后，排序直接使用索引的有序性 CREATE INDEX idx_hire_date ON employees(hire_date);
-- 数字存储为字符串时的经典问题 SELECT * FROM products ORDER BY product_code; -- 期望：1, 2, 3, 10, 11 -- 实际：1, 10, 11, 2, 3
-- 解决方案：确保排序字段使用正确的数据类型 SELECT * FROM products ORDER BY CAST(product_code AS UNSIGNED);
