MySQL - 描述表

要透過 Node.js 程式描述 MySQL 資料庫中的表，我們需要使用 **mysql2** 庫的 **query()** 函式執行 **Describe Table** 語句，如下所示：

sql="Describe Table_name";
con.query(sql);

要透過 Java 程式描述 MySQL 資料庫中的表，我們需要使用 **JDBC** 函式 **executeUpdate()** 執行 **Describe Table** 語句，如下所示：

String sql="Describe Table_name";
statement.executeUpdate(sql);

要透過 Python 程式描述 MySQL 資料庫中的表，我們需要使用 MySQL **Connector/Python** 的 **execute()** 函式執行 **Describe Table** 語句，如下所示：

sql="Describe Table_name";
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("--------------------------");

  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "CREATE TABLE SALES(ID INT, ProductName VARCHAR(255), CustomerName VARCHAR(255), DispatchDate date, DeliveryTime time, Price INT, Location VARCHAR(255));"
  con.query(sql);

  sql = "DESCRIBE SALES;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

輸出

產生的輸出如下所示：

Connected!
--------------------------
[
  {
    Field: 'ID',
    Type: 'int',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'ProductName',
    Type: 'varchar(255)',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'CustomerName',
    Type: 'varchar(255)',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'DispatchDate',
    Type: 'date',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'DeliveryTime',
    Type: 'time',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'Price',
    Type: 'int',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'Location',
    Type: 'varchar(255)',
    Null: 'YES',
    Key: '',
    Default: null,
    Extra: ''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DescribeTable {
    public static void main(String[] args){
        String url = "jdbc:mysql://:3306/TUTORIALS";
        String username = "root";
        String password = "password";
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            System.out.println("Connected successfully...!");

            //Describe table details...!
            ResultSet resultSet = statement.executeQuery("DESCRIBE customers");
            System.out.println("Following is the table description");
            while(resultSet.next()) {
                System.out.print(resultSet.getNString(1));
                System.out.println();
            }
            connection.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
    }
}

輸出

獲得的輸出如下所示：

Connected successfully...!
Following is the table description
ID
NAME
AGE
ADDRESS
SALARY

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'tutorials_tbl'
#Creating a cursor object 
cursorObj = connection.cursor()
describe_table_query = f"DESCRIBE {table_name}"
cursorObj.execute(describe_table_query)
columns_info = cursorObj.fetchall()
print(f"Description of table '{table_name}':")
for column in columns_info:
    print(f"Column Name: {column[0]}, Type: {column[1]}, Null: {column[2]}, Key: {column[3]}, Default: {column[4]}, Extra: {column[5]}")
cursorObj.close()
connection.close()

輸出

以下是上述程式碼的輸出：

Description of table 'tutorials_tbl':
Column Name: tutorial_id, Type: b'int', Null: NO, Key: PRI, Default: None, Extra: auto_increment
Column Name: tutorial_title, Type: b'varchar(100)', Null: NO, Key: , Default: None, Extra: 
Column Name: tutorial_author, Type: b'varchar(40)', Null: NO, Key: , Default: None, Extra: 
Column Name: submission_date, Type: b'date', Null: YES, Key: , Default: None, Extra: 
Column Name: tutorial_name, Type: b'varchar(20)', Null: YES, Key: , Default: None, Extra: