MySQL - 新增/刪除列

在Node.js程式中，要向MySQL資料庫的表中新增/刪除列，我們需要使用**mysql2**庫的**query()**函式執行**ALTER**語句，如下所示：

//following is the syntax for add column in existing table.
sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name";
//following is the syntax for delete column in existing table.
sql = "ALTER TABLE table_name DROP COLUMN column_name";
con.query(sql);

在Java程式中，要向MySQL資料庫的表中新增/刪除列，我們需要使用**JDBC**函式**executeUpdate()**執行**ALTER**語句，如下所示：

//following is the syntax for add column in existing table.
String sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name";
//following is the syntax for delete column in existing table.
String sql = "ALTER TABLE table_name DROP COLUMN column_name";  
statement.executeUpdate(sql);

在Python程式中，要向MySQL資料庫的表中新增/刪除列，我們需要使用MySQL **Connector/Python**的**execute()**函式執行**ALTER**語句，如下所示：

//following is the syntax for add column in existing table.
sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name"
//following is the syntax for delete column in existing table.
sql = "ALTER TABLE table_name DROP COLUMN column_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 STUDENTS (ID INT NOT NULL, NAME VARCHAR(40) NOT NULL);"
  con.query(sql);

  //Adding column named "AGE"
  sql = "ALTER TABLE STUDENTS ADD COLUMN AGE INT NOT NULL;"
  con.query(sql);

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

  //Deleting column named "AGE"
  sql = "ALTER TABLE STUDENTS DROP COLUMN AGE;"
  con.query(sql);

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

輸出

生成的輸出如下：

Connected!
--------------------------
[
  {
    Field: 'ID',
    Type: 'int',
    Null: 'NO',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'NAME',
    Type: 'varchar(40)',
    Null: 'NO',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'AGE',
    Type: 'int',
    Null: 'NO',
    Key: '',
    Default: null,
    Extra: ''
  }
]
--------------------------
[
  {
    Field: 'ID',
    Type: 'int',
    Null: 'NO',
    Key: '',
    Default: null,
    Extra: ''
  },
  {
    Field: 'NAME',
    Type: 'varchar(40)',
    Null: 'NO',
    Key: '',
    Default: null,
    Extra: ''
  }
]          

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class AddDelColumn{
   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...!");
         //Adding One Column extra into the tutorials_tbl
         String sql = "ALTER TABLE tutorials_tbl ADD COLUMN tutorial_name VARCHAR(30) NOT NULL AFTER tutorial_id";
         statement.executeUpdate(sql);
         System.out.println("Column added into the tutorials table successfully...!");
         //Deleting One Column from the tutorials_tbl
         String sql1 = "ALTER TABLE tutorials_tbl DROP COLUMN tutorial_name";
         statement.executeUpdate(sql1);
         System.out.println("Column deleted successfully from the tutorials table ...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE tutorials_tbl");
         while (resultSet.next()) {
            System.out.print(resultSet.getNString(1));
            System.out.println();
         }
         connection.close();
      }catch(Exception e) {
         System.out.println(e);
      }
   }
}                               

輸出

獲得的輸出如下所示：

Connected successfully...!
Column added into the tutorials table successfully...!
Column deleted successfully from the tutorials table ...!
tutorial_id
tutorial_title
tutorial_author
submission_date

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
   host='localhost',
   user='root',
   password='password',
   database='tut'
)
table_name = 'tutorials_tbl_cloned'
column_to_add = 'tutorial_price'
column_to_delete = 'tutorial_id'
# Adding a new column
cursorObj = connection.cursor()
add_column_query = f"ALTER TABLE {table_name} ADD COLUMN {column_to_add} INT"
cursorObj.execute(add_column_query)
print(f"Column '{column_to_add}' is added to table '{table_name}' successfully.")
# Deleting a column
delete_column_query = f"ALTER TABLE {table_name} DROP COLUMN {column_to_delete}"
cursorObj.execute(delete_column_query)
print(f"Column '{column_to_delete}' is deleted from table '{table_name}' successfully.")
cursorObj.close()
connection.close()                                            

輸出

以上程式碼的輸出如下：

Column 'tutorial_price' is added to table 'tutorials_tbl_cloned' successfully.
Column 'tutorial_id' is deleted from table 'tutorials_tbl_cloned' successfully.