MySQL - ORDER BY 子句

要通过 Node.js 程序对 MySQL 表的一个或多个列进行排序，我们需要使用 mysql2 库中的 query() 函数执行带有 ORDER BY 子句的 SELECT 语句，如下所示 -

sql= SELECT column-list FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]
con.query(sql);

要通过 Java 程序对 MySQL 表中的一个或多个列进行排序，我们需要使用 JDBC 函数 executeUpdate() 执行带有 ORDER BY 子句的 SELECT 语句，如下所示 -

String sql = "SELECT column-list FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]";
statement.executeQuery(sql);

要通过 Python 程序对 MySQL 表的一个或多个列进行排序，我们需要使用 MySQL Connector/Python 的 execute() 函数执行带有 ORDER BY 子句的 SELECT 语句，如下所示 -

order_by_clause_query = SELECT column-list FROM table_name
[ORDER BY column1, column2, ..., columnN] [ASC|DESC]
cursorObj.execute(order_by_clause_query);

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});
 //连接到 MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");
 //创建数据库
  sql = "create database TUTORIALS"
  con.query(sql);
 //选择数据库
  sql = "USE TUTORIALS"
  con.query(sql);
 //创建表
  sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID));"
  con.query(sql);
 //插入记录
  sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,'Ramesh', 32, 'Hyderabad', 2000.00),(2,'Khilan', 25, 'Delhi', 1500.00),(3,'kaushik', 23, 'Hyderabad', 2000.00),(4,'Chaital', 25, 'Mumbai', 6500.00),(5,'Hardik', 27, 'Vishakapatnam', 8500.00),(6, 'Komal',22, 'Vishakapatnam', 4500.00),(7, 'Muffy',24, 'Indore', 10000.00);"
  con.query(sql);
 //使用 ORDER BY 子句
  sql = "SELECT * FROM CUSTOMERS ORDER BY NAME;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});               

输出

生成的输出如下 -

Connected!
--------------------------
[
  {
    ID: 4,
    NAME: 'Chaital',
    AGE: 25,
    ADDRESS: 'Mumbai',
    SALARY: '6500.00'
  },
  {
    ID: 5,
    NAME: 'Hardik',
    AGE: 27,
    ADDRESS: 'Vishakapatnam',
    SALARY: '8500.00'
  },
  {
    ID: 3,
    NAME: 'kaushik',
    AGE: 23,
    ADDRESS: 'Hyderabad',
    SALARY: '2000.00'
  },
  {
    ID: 2,
    NAME: 'Khilan',
    AGE: 25,
    ADDRESS: 'Delhi',
    SALARY: '1500.00'
  },
  {
    ID: 6,
    NAME: 'Komal',
    AGE: 22,
    ADDRESS: 'Vishakapatnam',
    SALARY: '4500.00'
  },
  {
    ID: 7,
    NAME: 'Muffy',
    AGE: 24,
    ADDRESS: 'Indore',
    SALARY: '10000.00'
  },
  {
    ID: 1,
    NAME: 'Ramesh',
    AGE: 32,
    ADDRESS: 'Hyderabad',
    SALARY: '2000.00'
  }
]         

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class OrderByClause {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM CUSTOMERS ORDER BY NAME DESC";
            rs = st.executeQuery(sql);
            System.out.println("Table records(in DESC order): ");
            while(rs.next()){
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String age = rs.getString("Age");
              String address = rs.getString("Address");
              String salary = rs.getString("Salary");
              System.out.println("Id: " + id +", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}                             

输出

获得的输出如下所示 -

Table records(in DESC order): 
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00
Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00         

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象 
cursorObj = connection.cursor()
order_by_clause_query = """
SELECT *
FROM customers
ORDER BY AGE ASC
"""
cursorObj.execute(order_by_clause_query)
# 获取所有有序行
ordered_rows = cursorObj.fetchall()
# 打印有序行
for row in ordered_rows:
    print(row)
cursorObj.close()
connection.close()                                         

输出

以下是上述代码的输出 -

(3, 'kaushik', 23, 'Hyderabad', Decimal('11000.00'))
(7, 'Muffy', 24, 'Delhi', Decimal('10000.00'))
(2, 'Khilan', 25, 'Kerala', Decimal('8000.00'))
(4, 'Chaital', 25, 'Mumbai', Decimal('1200.00'))
(5, 'Hardik', 27, 'Vishakapatnam', Decimal('10000.00'))
(6, 'Komal', 29, 'Vishakapatnam', Decimal('7000.00'))
(1, 'Ramesh', 32, 'Hyderabad', Decimal('4000.00'))