Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Rename Columns

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Rename Columns

      The ALTER TABLE statement in MySQL can be used to change the structure of a table. For instance, we can add, delete, or rename the columns, etc. using this statement.
      Following are the two options that can be used with the ALTER TABLE statement to rename a column(s) of a table:
      • RENAME COLUMN
      • CHANGE COLUMN
      Note: Renaming a column(s) of table requires ALTER and DROP privilages.

      Using the RENAME COLUMN

      In MySQL, we can change the name of one or multiple columns of a specified table using the ALTER TABLE RENAME COLUMN command.

      Syntax

      Following is the syntax to rename a column in MySQL table 
      ALTER TABLE table_name
      RENAME COLUMN old_column1_name TO new_column1_name,
      RENAME COLUMN old_column2_name TO new_column2_name,
      ...;

      Example

      First of all, let us create a table named CUSTOMERS using the query below 
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR(20) NOT NULL,
         AGE INT NOT NULL
      );
      Here, we are using the DESCRIBE command to display the information about the above created table structure 
      DESCRIBE CUSTOMERS;
      As we can see in the table below, we have three columns present in CUSTOMERS table
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      NO
      
      NULL
      
      NAME
      varchar(20)
      NO
      
      NULL
      
      AGE
      int
      NO
      
      NULL
      
      Now, we are renaming the column named ID to cust_id using following query 
      ALTER TABLE CUSTOMERS
      	RENAME COLUMN ID TO cust_id;

      Output

      Executing the query above will produce the following output 
      Query OK, 0 rows affected (0.02 sec)
      Records: 0  Duplicates: 0  Warnings: 0

      Verification

      Let us retrive the CUSTOMERS table description to verify whether the column ID is renamed to stud_id or not 
      DESCRIBE CUSTOMERS;
      As we observe in the output table, the ID column is renamed to stud_id successfully.
      Field
      Type
      Null
      Key
      Default
      Extra
      cust_id
      int
      NO
      
      NULL
      
      NAME
      varchar(20)
      NO
      
      NULL
      
      AGE
      int
      NO
      
      NULL
      

      Example

      Now, we are renaming the other two columns in CUSTOMERS table named NAME and AGE to cust_name and cust_age
      ALTER TABLE CUSTOMERS 
      	RENAME COLUMN NAME TO cust_name, 
      	RENAME COLUMN AGE TO cust_age;

      Output

      Executing the query above will produce the following output 
      Query OK, 0 rows affected (0.01 sec)
      Records: 0  Duplicates: 0  Warnings: 0

      Verification

      To verify whether column names have been renamed or not, execute the following query 
      DESCRIBE CUSTOMERS;
      As we observe in the output table, the above mentioned columns are successfully renamed.
      Field
      Type
      Null
      Key
      Default
      Extra
      cust_id
      int
      NO
      
      NULL
      
      cust_name
      varchar(20)
      NO
      
      NULL
      
      cust_age
      int
      NO
      
      NULL
      

      Using CHANGE COLUMN

      In MySQL, we can change the name of one or more columns along with their datatypes using the ALTER TABLE ... CHANGE COLUMN command.

      Syntax

      Following is the syntax of the ALTER TABLE ... CHANGE commnad in MySQL 
      ALTER TABLE table_name
      CHANGE COLUMN old_column_name new_column_name Data Type;

      Example

      Consider the previously updated CUSTOMERS table and, let us change the name and the datatype of cust_id column 
      ALTER TABLE CUSTOMERS 
      	CHANGE COLUMN cust_id ID varchar(10);

      Output

      Executing the query above will produce the following output 
      Query OK, 0 rows affected (0.03 sec)
      Records: 0  Duplicates: 0  Warnings: 0

      Verification

      Using the following query, we can verify whether the column cust_id has changed its name and datatype or not 
      DESCRIBE CUSTOMERS;
      The name of the column and datatype has been changed successfully.
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      varchar(10)
      NO
      
      NULL
      
      cust_name
      varchar(20)
      NO
      
      NULL
      
      cust_age
      int
      NO
      
      NULL
      

      Example

      Here, we are changing the names and datatypes of multiple columns (cust_name and cust_age) in the CUSTOMERS table 
      ALTER TABLE CUSTOMERS
      	CHANGE COLUMN cust_name NAME DECIMAL(18,2),
      	CHANGE COLUMN cust_age AGE VARCHAR(20);

      Output

      Executing the query above will produce the following output 
      Query OK, 0 rows affected (0.03 sec)
      Records: 0  Duplicates: 0  Warnings: 0

      Verification

      Let us retrive the CUSTOMERS table description to verify whether the columns name and datatype are changed or not 
      DESCRIBE STUDENTS;
      As we observe in the output table, the names and datatypes of above mentioned columns are successfully changed.
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      varchar(10)
      NO
      
      NULL
      
      NAME
      decimal(18,2)
      NO
      
      NULL
      
      AGE
      varchar(20)
      NO
      
      NULL
      

      Renaming a Column of a Table Using a Client Program

      In addition to rename a column of a table in MySQL Database using MySQL query, we can also perform the ALTER TABLE operation on a table using a client program.

      Syntax

      Following are the syntaxes to rename a column of a MySQL table in various programming languages
      PHPNodeJSJavaPython
      To rename a column of a table in MySQL database through a PHP program, we need to execute ALTER TABLE statement using the mysqli function query() as 
      $sql = "ALTER TABLE table_name
      RENAME COLUMN old_column1_name TO new_column1_name"; 
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      // printf('Connected successfully.');
      
      // rename column;
      $sql = "ALTER TABLE tut_tbl
              RENAME COLUMN tutorial_id TO tutorial_IDs";
      if ($mysqli->query($sql)) {
          printf("Column renamed successfully!.");
      }
      if ($mysqli->errno) {
          printf("Columns could be renamed!.", $mysqli->error);
      }  
      $mysqli->close();

      Output

      The output obtained is as follows 
      Column renamed successfully!.