How To Add A Column Order In A Dataset Mysql

Summary: in this tutorial, we volition show you how to add a column to a table using MySQL ADD COLUMN statement.

Introduction to MySQL Add together Cavalcade statement

To add a new column to an existing table, you use the Change Table ADD COLUMN statement every bit follows:

                                          Change                Table                table                ADD                [Column] column_name column_definition [FIRST|AFTER                existing_column];            
                          Code language:              SQL (Structured Query Linguistic communication)              (              sql              )

Allow's examine the argument in more detail.

Kickoff, you specify the table proper noun afterward the ALTER Table clause.
2nd, you lot put the new column and its definition later the Add Cavalcade clause. Note that Column keyword is optional so you can omit it.
Third, MySQL allows you to add together the new column as the get-go cavalcade of the table by specifying the FIRST keyword. It also allows you to add the new cavalcade after an existing column using the AFTER existing_column clause. If you don't explicitly specify the position of the new cavalcade, MySQL will add it as the last cavalcade.

To add ii or more columns to a tabular array at the same time, yous use the following syntax:

                                          ALTER                TABLE                table                ADD                [COLUMN] column_name_1 column_1_definition [Offset|Afterwards                existing_column],                Add                [Cavalcade] column_name_2 column_2_definition [FIRST|After                existing_column], ...;                          
                          Code linguistic communication:              SQL (Structured Query Linguistic communication)              (              sql              )

Let's have a look some examples of adding a new cavalcade to an existing table.

MySQL ADD Cavalcade examples

First, we create a tabular array named vendors for the sit-in purpose using the post-obit statement:

CREATE TABLE IF Not EXISTS vendors ( id INT AUTO_INCREMENT Primary Fundamental, proper noun VARCHAR(255) );

Code language: SQL (Structured Query Language) ( sql )

Second, we add a new column named phone to the vendors table. Because we specify the position of the phone column explicitly later on the proper noun column, MySQL will obey this.

Change TABLE vendors ADD Cavalcade telephone VARCHAR(fifteen) AFTER name;

Code language: SQL (Structured Query Language) ( sql )

Third, we add together a new column named vendor_group to the vendors table. At this time, nosotros don't specify the new column's position and so MySQL adds thevendor_group cavalcade as the last column of the vendors table.

Alter TABLE vendors Add COLUMN vendor_group INT NOT NULL;

Code linguistic communication: SQL (Structured Query Language) ( sql )

Let's insert some rows into the vendors tabular array.

                                          INSERT                INTO                vendors(proper name,telephone,vendor_group)                VALUES('IBM','(408)-298-2987',one);                INSERT                INTO                vendors(proper name,telephone,vendor_group)                VALUES('Microsoft','(408)-298-2988',ane);            
                          Code language:              SQL (Structured Query Language)              (              sql              )

Nosotros can query the data of the vendors table to meet the changes.

                                          SELECT                id,                name, phone,vendor_group                FROM                vendors;                          
                          Code linguistic communication:              SQL (Structured Query Linguistic communication)              (              sql              )

Fourth, add together ii more than columns email and hourly_rate to the vendors table at the same time.

Alter TABLE vendors ADD Column electronic mail VARCHAR(100) NOT Nothing, ADD Column hourly_rate decimal(10,2) NOT NULL;

Code language: SQL (Structured Query Language) ( sql )

Note that both email and hourly_rate columns are assigned toNOT Nothing values However, the vendors table already has data. In such cases, MySQL will use default values for those new columns.

Allow's check the data in the vendors table.

                                          SELECT                id,                proper name, phone, vendor_group, email, hourly_rate                FROM                vendors;            
                          Code linguistic communication:              SQL (Structured Query Language)              (              sql              )

The email column is populated with blank values, non the Zippo values. And the hourly_rate column is populated with 0.00 values.

If you lot accidentally add a column that already exists in the table, MySQL will event an error. For case, if you execute the following statement:

Alter TABLE vendors Add together COLUMN vendor_group INT NOT Zilch;

Code language: SQL (Structured Query Language) ( sql )

MySQL issued an error message:

Error Code: 1060. Duplicate column proper noun 'vendor_group'

Lawmaking language: SQL (Structured Query Language) ( sql )

For the table with a few columns, it is easy to see which columns are already there. Even so, with a big table with hundred of columns, information technology is more difficult.

In some situations, y'all want to check whether a column already exists in a tabular array before adding it. However, there is no statement similar ADD Column IF NOT EXISTS available. Fortunately, you can get this information from the columns table of the information_schema database as the following query:

                                          SELECT                IF(count(*) =                one,                'Exist','Not Exist')                AS                consequence                FROM                information_schema.columns                WHERE                table_schema =                'classicmodels'                AND                table_name =                'vendors'                AND                column_name =                'telephone';            
                          Code linguistic communication:              SQL (Structured Query Language)              (              sql              )

In the WHERE clause, we passed three arguments: table schema or database, tabular array name, and column name. Nosotros used IF function to render whether the column exists or not.

In this tutorial, you have learned how to add one or more than columns to a table using MySQL ADD COLUMN statement.

Was this tutorial helpful?