MySQL - 顯示使用者

要顯示使用者資訊，我們需要使用 JavaScript (NodeJS) 程式中的 mysql2 庫的 query() 函式執行 **SELECT * FROM 語句**，如下所示：

sql= "SELECT * FROM mysql.user";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

類似地，在 Java 中，我們可以使用 JDBC 的 **executeQuery()** 函式執行顯示使用者資訊的 SQL 查詢，如下所示：

String sql = "SELECT USER FROM MYSQL.USER";
statement.executeQuery(sql);

**MySQL Connector/Python** 提供了一個名為 **execute()** 的函式，用於在 MySQL 資料庫中執行 SQL 查詢。要在 MySQL 資料庫中顯示使用者資訊，我們需要使用此函式執行 **SELECT USER** 語句，如下所示：

sql = "SELECT user, host FROM mysql.user";
cursorObj.execute(sql);

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating Users
  sql = "CREATE USER TestUser1@localhost IDENTIFIED BY 'password1';"
  con.query(sql);
  sql = "CREATE USER TestUser2@localhost IDENTIFIED BY 'password2';"
  con.query(sql);
  sql = "CREATE USER TestUser3@localhost IDENTIFIED BY 'password3';"
  con.query(sql);
  sql = "CREATE USER TestUser4@localhost IDENTIFIED BY 'password4';"
  con.query(sql);

  //Listing the users
  sql = "SELECT USER FROM mysql.user;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**List of Users:**")
    console.log(result)
  });
});

輸出

生成的輸出如下：

Connected!
--------------------------
**List of Users:**
[
  { USER: 'TestUser1' },
  { USER: 'TestUser2' },
  { USER: 'TestUser3' },
  { USER: 'TestUser4' },
  { USER: 'mysql.infoschema' },
  { USER: 'mysql.session' },
  { USER: 'mysql.sys' },
  { USER: 'root' },
  { USER: 'sample' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ShowUsers {
	public static void main(String[] args) {
		String url = "jdbc:mysql://: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 = "CREATE USER 'Vivek'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'";
            st.execute(sql);
            System.out.println("User 'Vivek' created successfully...!");
            String sql1 = "SELECT USER FROM MYSQL.user";
            rs = st.executeQuery(sql1);
            System.out.println("Users: ");
            while(rs.next()) {
            	String users = rs.getNString(1);
            	System.out.println(users);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

輸出

獲得的輸出如下所示：

User 'Vivek' created successfully...!
Users: 
Sarika
Vivek Verma
Revathi
Sarika
Vivek
mysql.infoschema
mysql.session
mysql.sys
root

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
)
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute("SELECT user, host FROM mysql.user")
users = cursorObj.fetchall()
print("Existing users are:")
for user, host in users:
    print(f"User: {user}, Host: {host}")
cursorObj.close()
connection.close()

輸出

以上程式碼的輸出如下：

Existing users are:
User: UserNew, Host: localhost
User: mysql.infoschema, Host: localhost
User: mysql.session, Host: localhost
User: mysql.sys, Host: localhost
User: newUser, Host: localhost
User: root, Host: localhost