MySQL - GROUP BY 子句

要通过 JavaScript 程序在 MySQL 表中使用 GROUP BY 子句，我们需要使用 mysql2 连接器提供的名为 query() 的函数执行 SQL 语句 -

sql= " SELECT column_name(s) FROM table_name
GROUP BY [condition | column_name(s)];"
Con.query(sql);

要通过 Java 程序在 MySQL 表中使用 GROUP BY 子句，我们需要使用 JDBC type 4 驱动程序提供的名为 executeQuery() 的函数执行 SQL 语句 -

String sql = "SELECT column_name(s) FROM table_name
GROUP BY [condition | column_name(s)]";
statement.executeQuery(sql);

要通过 Java 程序在 MySQL 表中使用 GROUP BY 子句，我们需要使用 MySQL Connector/Python 提供的名为 execute() 的函数执行 SQL 语句 -

group_by_clause_query = "SELECT column_name(s) aggregate_function(column)
FROM table_name GROUP BY column_name(s)"
cursorObj.execute(group_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 IF NOT EXISTS employee_tbl(id INT NOT NULL, name VARCHAR(100) NOT NULL, work_date DATE, daily_typing_pages INT);"
  con.query(sql);
 //插入记录
  sql = "INSERT INTO employee_tbl(id, name, work_date, daily_typing_pages) VALUES(1, 'John', '2007-01-24', 250), (2, 'Ram', '2007-05-27', 220), (3, 'Jack', '2007-05-06', 170), (3, 'Jack', '2007-04-06', 100), (4, 'Jill', '2007-04-06', 220),(5, 'Zara', '2007-06-06', 300),(5, 'Zara', '2007-02-06', 350);"
  con.query(sql);
 //使用 GROUP BY 子句
  sql = "SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

输出

生成的输出如下 -

Connected!
--------------------------
[
  { name: 'John', 'COUNT(*)': 1 },
  { name: 'Ram', 'COUNT(*)': 1 },
  { name: 'Jack', 'COUNT(*)': 2 },
  { name: 'Jill', 'COUNT(*)': 1 },
  { name: 'Zara', 'COUNT(*)': 2 }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class GroupByClause {
	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 AGE, AVG(SALARY) as avg_salary FROM CUSTOMERS GROUP BY age";
            rs = st.executeQuery(sql);
            System.out.println("Table records(gruop by age): ");
            while(rs.next()){
            	String age = rs.getString("Age");
            	String avg_salary = rs.getString("avg_salary");
            	System.out.println("Age: " + age + ", Salary: " + avg_salary);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

输出

获得的输出如下所示 -

Table records(gruop by age): 
Age: 32, Salary: 2000.000000
Age: 25, Salary: 4000.000000
Age: 23, Salary: 2000.000000
Age: 27, Salary: 8500.000000
Age: 22, Salary: 4500.000000
Age: 24, Salary: 10000.000000

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象 
cursorObj = connection.cursor()
group_by_clause_query = """
SELECT ADDRESS, AVG(SALARY) AS average_salary
FROM customers
GROUP BY ADDRESS
"""
cursorObj.execute(group_by_clause_query)
# 获取所有分组的行
grouped_rows = cursorObj.fetchall()
for row in grouped_rows:
    address, average_salary = row
    print(f"Address: {address}, Average Salary: {average_salary}")
cursorObj.close()
connection.close()

输出

以下是上述代码的输出 -

Address: Hyderabad, Average Salary: 7500.000000
Address: Kerala, Average Salary: 8000.000000
Address: Mumbai, Average Salary: 1200.000000
Address: Vishakapatnam, Average Salary: 8500.000000
Address: Delhi, Average Salary: 10000.000000