How to manage columns in MySQL?

In this article we will first know what columns, rows and tables are in MySQL and then we will know how to manage columns in MySQL i.e. add columns, modify columns, remove columns or show columns.

What are columns, rows, and table in MySQL?

Columns in a table are the fields that store values for a row. The column expects value of a particular type like text, numbers, images, etc. In a MySQL table, columns are displayed vertically. While creating the table we add columns using CREATE TABLE SQL syntax. We can add, modify, and remove any column using the ALTER TABLE SQL syntax in an already created table.

Rows in a table are the set of data items. Each items of this data set are stored in different columns. In a MySQL table, rows are displayed horizontally. We add new row to a table using INSERT SQL syntax.

Table is a structured format to store and display records in the form of rows and columns. The CREATE TABLE SQL syntax is used to create a new table.

How rows and columns are displayed in the table we can see from the following example of a table.

TABLE 1

	Column 1	Column 2	Column 3
Row 1	Row 1, Column 1	Row 1, Column 2	Row 1, Column 3
Row 2	Row 2, Column 1	Row 2, Column 2	Row 2, Column 3
Row 3	Row 3, Column 1	Row 3, Column 2	Row 3, Column 3

According to MYSQL documentation MySQL has hard limit of 4096 columns per table. You can learn more about column count and row size at: https://dev.mysql.com/doc/mysql-reslimits-excerpt/8.0/en/column-count-limit.html

Manage columns in MySQL

Here first we will know how to add column in MySQL.

Add columns in MySQL

Columns are added when creating a table using the create table syntax. We will now create the users table which will contain the fields user_id, email, first_name, last_name, description, and dob. The SQL syntax is as follows:

CREATE TABLE users (
    user_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    description TEXT,
    dob DATE NOT NULL, 
    PRIMARY KEY (user_id)
);

This will create users table in a selected database.

We can add, modify, and remove any column using the ALTER TABLE SQL syntax in an already created table.

Now we will add display_name column after email column using ALTER TABLE syntax in above users table. The SQL syntax is as follows:

ALTER TABLE users ADD display_name varchar(255) NOT NULL AFTER email;

View columns in MySQL

DESCRIBE or SHOW COLUMNS SQL syntax shows information about the columns in a given table.

If we use either of the following two SQL syntaxes:

DESCRIBE users;

SHOW COLUMNS FROM users;

We get the information about the columns:

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| user_id      | int          | NO   | PRI | NULL    |       |
| email        | varchar(255) | NO   |     | NULL    |       |
| display_name | varchar(255) | NO   |     | NULL    |       |
| first_name   | varchar(255) | NO   |     | NULL    |       |
| last_name    | varchar(255) | NO   |     | NULL    |       |
| description  | text         | YES  |     | NULL    |       |
| dob          | date         | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

Modify columns in MySQL

We can modify any column using the ALTER TABLE SQL syntax in a given table.

We will now change the data type of the dob column from type date to type year using ALTER TABLE syntax. The SQL syntax as follows:

ALTER TABLE users MODIFY COLUMN dob year NOT NULL;

The output of the following DESCRIBE <TABLE_NAME> or SHOW COLUMNS FROM <TABLE_NAME> SQL syntax shows that the data type of the column name has changed from date to year.

SHOW COLUMNS FROM users;

we get the following information about the columns:

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| user_id      | int          | NO   | PRI | NULL    |       |
| email        | varchar(255) | NO   |     | NULL    |       |
| display_name | varchar(255) | NO   |     | NULL    |       |
| first_name   | varchar(255) | NO   |     | NULL    |       |
| last_name    | varchar(255) | NO   |     | NULL    |       |
| description  | text         | YES  |     | NULL    |       |
| dob          | year         | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

We can also change the name of the dob column of the users table using the following SQL syntax:

ALTER TABLE users RENAME COLUMN dob TO doy;

Here we have modified the column name from dob to doy. With ALTER TABLE, RENAME is used to change the name of the column. We can see from the output of the following DESCRIBE <TABLE_NAME> or SHOW COLUMNS FROM <TABLE_NAME> SQL syntax that the column name has changed.

SHOW COLUMNS FROM users;

Now, we get the following information about the columns:

+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| user_id      | int          | NO   | PRI | NULL    |       |
| email        | varchar(255) | NO   |     | NULL    |       |
| display_name | varchar(255) | NO   |     | NULL    |       |
| first_name   | varchar(255) | NO   |     | NULL    |       |
| last_name    | varchar(255) | NO   |     | NULL    |       |
| description  | text         | YES  |     | NULL    |       |
| doy          | year         | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+

Remove columns in MySQL

Now If we want to remove or delete a column from a table we use ALTER TABLE <TABLE_NAME> DROP COLUMN <COLUMN_NAME> syntax.

Here if we want to remove the display_name column from the users table then we will use the following SQL syntax.

ALTER TABLE users DROP COLUMN display_name;

This will remove the display_name column from the users table. We can see from the output of the following DESCRIBE <TABLE_NAME> or SHOW COLUMNS FROM <TABLE_NAME> SQL syntax.

SHOW COLUMNS FROM users;

We get the following information about the columns:

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| user_id     | int          | NO   | PRI | NULL    |       |
| email       | varchar(255) | NO   |     | NULL    |       |
| first_name  | varchar(255) | NO   |     | NULL    |       |
| last_name   | varchar(255) | YES  |     | NULL    |       |
| description | text         | YES  |     | NULL    |       |
| doy         | year         | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

Apart from column add, modify, delete, we can add the index of a column while creating the table using CREATE table or while modifying the table using ALTER TABLE syntax. I have discussed indexing in MySQL in detail at: https://webtechbased.com/what-is-indexing-in-mysql/.

Fetching records from tables using column name

Now we will learn how to use column names while fetching data from a table. So at the beginning we will add data to the users table. We will add 2 records here for testing. For this we will add the following SQL syntax.

INSERT INTO `users` (`user_id`, `email`, `first_name`, `last_name`, `doy`) VALUES 
('1', 'dev1@dev.com', 'john', 'clark', '2000'),
('2', 'dev2@dev.com', 'jack', 'clark', '2001');

Fetching all rows

Now if we want to see all the records we will use the following syntax.

SELECT * FROM users;

The output of this query is as follows:

+---------+--------------+------------+-----------+-------------+------+
| user_id | email        | first_name | last_name | description | doy  |
+---------+--------------+------------+-----------+-------------+------+
|       1 | dev1@dev.com | john       | clark     | NULL        | 2000 |
|       2 | dev2@dev.com | jack       | clark     | NULL        | 2001 |
+---------+--------------+------------+-----------+-------------+------+

Now if we want to fetch data of any column instead of all columns then we can use column name instead of * in SELECT SQL query. To fetch data of multiple columns we use comma separated column names in SELECT SQL query.

If we first want to fetch only email data from the users table, the syntax is as follows:

SELECT email FROM users;

The output of the above SQL query is:

+--------------+
| email        |
+--------------+
| dev1@dev.com |
| dev2@dev.com |
+--------------+

If we want to fetch email, first_name and last_name from the users table then the SQL query is as follows:

SELECT email, first_name, last_name FROM users;

The output of the above SQL query is:

+--------------+------------+-----------+
| email        | first_name | last_name |
+--------------+------------+-----------+
| dev1@dev.com | john       | clark     |
| dev2@dev.com | jack       | clark     |
+--------------+------------+-----------+

Now if we want to fetch records of multiple columns together instead of fetching them separately then we can use CONCAT in SELECT SQL query. For example, if we want to fetch first_name and last_name together as name instead of first_name and last_name separately, the SQL query would be as follows:

SELECT CONCAT(first_name, ' ', last_name) AS name FROM users;

The output of the above SQL query is:

+------------+
| name       |
+------------+
| john clark |
| jack clark |
+------------+

Fetching specific rows

In the above examples we are fetching the data of all the rows of the table but to fetch the data of a specific row we apply the filter on the value of a column.
That is, if we want to fetch the data of all the columns of the row in which the value of the user_id column is 1, then we will add WHERE user_ID = 1 to SELECT * FROM <TABLE_NAME> SQL queries. In this case the SQL query will be as follows:

SELECT * FROM users WHERE user_id =1;

Here, the output is:

+---------+--------------+------------+-----------+-------------+------+
| user_id | email        | first_name | last_name | description | doy  |
+---------+--------------+------------+-----------+-------------+------+
|       1 | dev1@dev.com | john       | clark     | NULL        | 2000 |
+---------+--------------+------------+-----------+-------------+------+

From the output we can see that here only the data of user_id =1 is fetched.

The above SQL query fetches all columns for user_id = 1. But if we want to fetch the data of email, first_name and last_name column for user_id = 1 then the SQL query will be as follows:

SELECT email, first_name, last_name FROM users WHERE user_id = 1;

The output of the above query is:

+--------------+------------+-----------+
| email        | first_name | last_name |
+--------------+------------+-----------+
| dev1@dev.com | john       | clark     |
+--------------+------------+-----------+

Table of Contents

What are columns, rows, and table in MySQL?

Manage columns in MySQL

Add columns in MySQL

View columns in MySQL

Modify columns in MySQL

Remove columns in MySQL

Fetching records from tables using column name

Fetching all rows

Fetching specific rows