MySQL - 添加/删除列

要通过 Node.js 程序在 MySQL 数据库中添加/删除表的列，我们需要使用 mysql2 库的 query() 函数执行 ALTER 语句，如下所示 -

//以下是在现有表中添加列的语法。
sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name";
//以下是在现有表中删除列的语法。
sql = "ALTER TABLE table_name DROP COLUMN column_name";
con.query(sql);

要通过 Java 程序在 MySQL 数据库中添加/删除表的列，我们需要使用 JDBC 函数 executeUpdate() 执行 ALTER 语句，如下所示 -

//以下是在现有表中添加列的语法。
String sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name";
//以下是在现有表中删除列的语法。
String sql = "ALTER TABLE table_name DROP COLUMN column_name";
statement.executeUpdate(sql);

要通过 Python 程序在 MySQL 数据库中添加/删除表的列，我们需要使用 MySQL Connector/Python 的 execute() 函数执行 ALTER 语句，如下所示 -

//以下是在现有表中添加列的语法。
sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name"
//以下是删除现有表中列的语法。
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"
});
 //连接到 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://localhost: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...!");
         //在 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...!");
         //从 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
#建立连接
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'
# 添加新列
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.")
# 删除一列
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.