IT 221- Information Management Introduction to Structured Query Language

LEARNING OBJECTIVES:

At the end of the unit, the student should be able to:

1. Create a database and database tables; and

2. Organize database tables by adding, updating and deleting table columns and rows

LEARNING CONTENT:

MySQL Features
MySQL is a relational database management system (RDBMS) based on the SQL (Structured Query
Language) queries. It is one of the most popular languages for accessing and managing the records
in the table. MySQL is open-source and free software under the GNU license. Oracle Company
supports it.

The following are the most important features of MySQL:

Relational Database Management System (RDBMS)

MySQL is a relational database management system. This database language is based on

the SQL queries to access and manage the records of the table.

Easy to use

MySQL is easy to use. We have to get only the basic knowledge of SQL. We can build and interact
with MySQL by using only a few simple SQL statements.

It is secure

MySQL consists of a solid data security layer that protects sensitive data from intruders. Also,
passwords are encrypted in MySQL.

Client/ Server Architecture

MySQL follows the working of a client/server architecture. There is a database server (MySQL) and
arbitrarily many clients (application programs), which communicate with the server; that is, they can
query data, save changes, etc.

Easy to use

MySQL is easy to use. We have to get only the basic knowledge of SQL. We can build and interact
with MySQL by using only a few simple SQL statements.
[DATE]
It is secure [COMPANY NAME]
[Company address]
IT 221- Information Management Introduction to Structured Query Language

MySQL consists of a solid data security layer that protects sensitive data from intruders. Also,
passwords are encrypted in MySQL.

Client/ Server Architecture

MySQL follows the working of a client/server architecture. There is a database server (MySQL) and
arbitrarily many clients (application programs), which communicate with the server; that is, they can
query data, save changes, etc.

Free to download

MySQL is free to use so that we can download it from MySQL official website without any cost.

It is scalable

MySQL supports multi-threading that makes it easily scalable. It can handle almost any amount of
data, up to as much as 50 million rows or more. The default file size limit is about 4 GB. However, we
can increase this number to a theoretical limit of 8 TB of data.

Speed

MySQL is considered one of the very fast database languages, backed by a large number of the
benchmark test.

High Flexibility

MySQL supports a large number of embedded applications, which makes MySQL very flexible.

Compatible on many operating systems

MySQL is compatible to run on many operating systems, like Novell NetWare, Windows* Linux*, many
varieties of UNIX* (such as Sun* Solaris*, AIX, and DEC* UNIX), OS/2, FreeBSD*, and others. MySQL
also provides a facility that the clients can run on the same computer as the server or on another
computer (communication via a local network or the Internet).

Allows roll-back

MySQL allows transactions to be rolled back, commit, and crash recovery.

Memory efficiency

Its efficiency is high because it has a very low memory leakage problem.

High Performance
IT 221- Information Management Introduction to Structured Query Language

MySQL is faster, more reliable, and cheaper because of its unique storage engine architecture. It
provides very high-performance results in comparison to other databases without losing an essential
functionality of the software. It has fast loading utilities because of the different cache memory.

High Productivity

MySQL uses Triggers, Stored procedures, and views that allow the developer to give higher
productivity.

Platform Independent

It can download, install, and execute on most of the available operating systems.

Partitioning

This feature improves the performance and provides fast management of the large database.

GUI Support

MySQL provides a unified visual database graphical user interface tool named "MySQL Workbench"
to work with database architects, developers, and Database Administrators. MySQL
Workbench provides SQL development, data modeling, data migration, and comprehensive
administration tools for server configuration, user administration, backup, and many more. MySQL
has a fully GUI supports from MySQL Server version 5.6 and higher.

Dual Password Support

MySQL version 8.0 provides support for dual passwords: one is the current password, and another is
a secondary password, which allows us to transition to the new password.

Disadvantages/Drawback of MySQL
Following are the few disadvantages of MySQL:

o MySQL version less than 5.0 doesn't support ROLE, COMMIT, and stored procedure.
o MySQL does not support a very large database size as efficiently.
o MySQL doesn't handle transactions very efficiently, and it is prone to data corruption.
o MySQL is accused that it doesn't have a good developing and debugging tool compared to
paid databases.
o MySQL doesn't support SQL check constraints.
IT 221- Information Management Introduction to Structured Query Language

MySQL Create Database

A database is used to store the collection of records in an organized form. It allows us to hold the
data into tables, rows, columns, and indexes to find the relevant information frequently. We can access
and manage the records through the database very easily.

MySQL implements a database as a directory that stores all files in the form of a table. It allows us to
create a database mainly in two ways:

1. MySQL Command Line Client

2. MySQL Workbench

MySQL Command Line Client

We can create a new database in MySQL by using the CREATE DATABASE statement with the below
syntax:

CREATE DATABASE [IF NOT EXISTS] database_name

[CHARACTER SET charset_name]
[COLLATE collation_name];

Parameter Explanation

The parameter descriptions of the above syntax are as follows:

Parameter Description
database_name It is the name of a new database that should be
unique in the MySQL server instance. The IF NOT
EXIST clause avoids an error when we create a
database that already exists.
charset_name It is optional. It is the name of the character set to
store every character in a string. MySQL database
server supports many character sets. If we do not
provide this in the statement, MySQL takes the
default character set.
collation_name It is optional that compares characters in a
particular character set.

Example

Let us understand how to create a database in MySQL with the help of an example. Open the MySQL
console and write down the password, if we have set during installation. Now we are ready to create
a database. Here, we are going to create a database name "employeedb" using the following
statement:
IT 221- Information Management Introduction to Structured Query Language

mysql> CREATE DATABASE employeesdb;

It will look like the below output:

We can review the newly created database using the below query that returns the database name,
character set, and collation of the database:

mysql> SHOW CREATE DATABASE employeedb;

We can check the created database using the following query:

mysql> SHOW DATABASES;

After executing the above query, we can see all the created databases in the server.
 IT 221- Information Management Introduction to Structured Query Language

Finally, we can use the below command to access the database that enables us to create a table and
other database objects.

1. mysql> USE employeedb;

NOTE: All the database names, table names, and table field names are case sensitive. We must
have to use proper names while giving any SQL command.
MySQL Workbench
It is a visual database designing or GUI tool used to work with database architects, developers, and
Database Administrators. This visual tool supports SQL development, data modeling, data migration,
and comprehensive administration tools for server configuration, user administration, backup, and
many more. It allows us to create new physical data models, E-R diagrams, and SQL development (run
queries, etc.).

To create a new database using this tool, we first need to launch the MySQL Workbench and log in
using the username and password that you want. It will show the following screen:
IT 221- Information Management Introduction to Structured Query Language

Now do the following steps for database creation:

1. Go to the Navigation tab and click on the Schema menu. Here, we can see all the previously created
databases. If we want to create a new database, right-click under the Schema menu and select Create
Schema or click the database icon (red rectangle), as shown in the following screen.

2. The new Schema window screen open. Enter the new database name (for example, employeedb) and use
default character set and collation. Now, click on the Apply button as shown in the screen below:
IT 221- Information Management Introduction to Structured Query Language

3. A new popup window appears. Click on the Apply button.

4. A new popup screen appears. Click on the Finish button to complete the database creation.
IT 221- Information Management Introduction to Structured Query Language

5. After successful database creation, we can see new databases in the Schema menu. If we do not see
this, click on the refresh icon into the Schema menu.

6. We can see more information about the database by selecting the database and click on the 'i' icon.
The information window displays several options, like Table, Triggers, Indexes, Users, and many more.

7. MySQL Workbench does not provide an option to rename the database name, but we can create,
update, and delete the table and data rows from the database.
IT 221- Information Management Introduction to Structured Query Language

MySQL SELECT Database

SELECT Database is used in MySQL to select a particular database to work with. This query is used
when multiple databases are available with MySQL Server.

You can use SQL command USE to select a particular database.

Syntax:

USE database_name;

Example:

Let's take an example to use a database name "customers".

USE customers;

It will look like this:

Note: All the database names, table names and table fields name are case sensitive. You must
have to use proper names while giving any SQL command.

MySQL Show/List Databases

When we work with the MySQL server, it is a common task to show or list the databases, displaying
the table from a particular database, and information of user accounts and their privileges that reside
on the server. In this article, we are going to focus on how to list databases in the MySQL server.
IT 221- Information Management Introduction to Structured Query Language

We can list all the databases available on the MySQL server host using the following command, as
shown below:

mysql> SHOW DATABASES;

Open the MySQL Command Line Client that appeared with a mysql> prompt. Next, log in to the
MySQL database server using the password that you have created during the installation of MySQL.
Now, you are connected to the MySQL server host, where you can execute all the SQL statements.
Finally, run the SHOW Databases command to list/show databases.

We can see the following output that explains it more clearly:

MySQL also allows us another command to list the databases, which is a SHOW SCHEMAS statement.
This command is the synonyms of the SHOW DATABASES and gives the same result. We can
understand it with the following output:

List Databases Using Pattern Matching

Show Databases command in MySQL also provides an option that allows us to filter the returned
database using different pattern matching with LIKE and WHERE clause. The LIKE clause list the
database name that matches the specified pattern. The WHERE clause provides more flexibility to list
the database that matches the given condition in the SQL statement.
 IT 221- Information Management Introduction to Structured Query Language

Syntax

The following are the syntax to use pattern matching with Show Databases command:

1. mysql> SHOW DATABASES LIKE pattern;

2. OR,
3. mysql> SHOW DATABASES WHERE expression;

We can understand it with the example given below where percent (%) sign assumes zero, one, or
multiple characters:

1. mysql> SHOW DATABASES LIKE "%schema";

The above statement will give the following output:

ADVERTISEMENT

ADVERTISEMENT

Sometimes the LIKE clause is not sufficient; then, we can make a more complex search to query the
database information from the schemata table in the information schema. The information schema in
MySQL is an information database so that we can use it to get the output using the SHOW DATABASES
command.

1. mysql> SELECT schema_name FROM information_schema.schemata;

This statement will give the same result as the SHOW DATABASES command:
 IT 221- Information Management Introduction to Structured Query Language

Now, we are going to see how we can use the WHERE clause with the SHOW DATABASES command.
This statement returns the database whose schema name starts with "s":

1. mysql>SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 's%';

It will give the following output:

NOTE: It is to be noted that if the MySQL server started with the "--skip-show-database" option,
we could not use the SHOW DATABASES command unless we have the SHOW DATABASES
privilege.

MySQL DROP Database

We can drop/delete/remove a MySQL database quickly with the MySQL DROP DATABASE command.
It will delete the database along with all the tables, indexes, and constraints permanently. Therefore,
we should have to be very careful while removing the database in MySQL because we will lose all the
data available in the database. If the database is not available in the MySQL server, the DROP
DATABASE statement throws an error.

MySQL allows us to drop/delete/remove a database mainly in two ways:

o MySQL Command Line Client

o MySQL Workbench
IT 221- Information Management Introduction to Structured Query Language

MySQL Command Line Client

We can drop an existing database in MySQL by using the DROP DATABASE statement with the below
syntax:

DROP DATABASE [IF EXISTS] database_name;

In MySQL, we can also use the below syntax for deleting the database. It is because the schema is the
synonym for the database, so we can use them interchangeably.

DROP SCHEMA [IF EXISTS] database_name;

Parameter Explanation

The parameter descriptions of the above syntax are as follows:

Parameter Description
database_name It is the name of an existing database that we
want to delete from the server. It should be
unique in the MySQL server instance.
IF EXISTS It is optional. It is used to prevent from getting an
error while removing a database that does not
exist.

Example

Let us understand how to drop a database in MySQL with the help of an example. Open the MySQL
console and write down the password, if we have set during installation. Now we are ready to delete
a database.

Next, use the SHOW DATABASES statement to see all available database in the server:
 IT 221- Information Management Introduction to Structured Query Language

Suppose we want to remove a database named "mytestdb_copy". Execute the below statement:

1. DROP DATABASE mytestdb_copy;

Now we can verify that either our database is removed or not by executing the following query. It will
look like this:

From the above, we can see that the database "mytestdb_copy" is removed successfully.
IT 221- Information Management Introduction to Structured Query Language

Note: All the database names, table names, and table field names are case sensitive. We must
have to use proper names while giving any SQL command.
DROP Database using MySQL Workbench
To drop a database using this tool, we first need to launch the MySQL Workbench and log in with
the username and password to the MySQL server. It will show the following screen:

Now do the following steps for database deletion:

1. Go to the Navigation tab and click on the Schema menu. Here, we can see all the previously created
databases. If we want to delete a database, right-click the database that you want to remove, for
example, testdb_copy under the Schema menu and select Drop Schema option, as shown in the
following screen.
IT 221- Information Management Introduction to Structured Query Language

When we click the Drop Schema option, MySQL Workbench displays a dialog box to confirm the
deletion process. If we select Review SQL, it will produce the SQL statement that will be executed.
And if we choose Drop Now option, the database will be deleted permanently.

ADVERTISEMENT

If we want the safe deletion of the database, it is required to choose the Review SQL option. Once we
sure, click the Execute button to execute the statement. The below screen explains it more clearly:
IT 221- Information Management Introduction to Structured Query Language

Once we click the execute button, MySQL will return the below message indicating that the database
is dropped successfully. Since the database testdb_copy is an empty database, the number of affected
rows is zero.

If we verify the schemas tab, we will not find the testdb_copy database on the list anymore.

MySQL COPY Database

A database is an application used for storing the organized collection of records that can be accessed
and manage by the user. It holds the data into tables, rows, columns, and indexes to quickly find the
relevant information.

MySQL copy or clone database is a feature that allows us to create a duplicate copy of an existing
database, including the table structure, indexes, constraints, default values, etc. Making a duplicate
copy of an original database into a new database is very useful when accidentally our database is lost
or failure. The most common use of making a duplicate copy of the database is for data backups. It
is also useful when planning the major changes to the structure of the original database.

In MySQL, making the clone of an original database is a three-step process: First, the original
database records are dumped (copied) to a temporary file that holds the SQL commands for
IT 221- Information Management Introduction to Structured Query Language

reinserting the data into the new database. Second, it is required to create a new database. Finally,
the SQL file is processed, and the data will be copied into the new database.

We need to follow these steps to copy a database to another database:

1. First, use the CREATE DATABASE statement to create a new database.

2. Second, store the data to an SQL file. We can give any name to this file, but it must end with
a .sql extension.
3. Third, export all the database objects along with its data to copy using the mysqldump tool
and then import this file into the new database.

For the demonstration, we will copy the testdb database to testdb_copy database using the
following steps:

Open the MySQL console and write down the password, if we have set during installation. Now we
are ready to create a duplicate database of testdb using the command below:

mysql> CREATE DATABASE testdb_copy;

Next, use the SHOW DATABASES statement for verification:

mysql> SHOW DATABASES;

This command will return all available database in the server where we can see the newly created
database in red rectangle box:
IT 221- Information Management Introduction to Structured Query Language

Now, open a DOS or terminal window to access the MySQL server on the command line. For example,
if we have installed the MySQL in the C folder, copy the following folder and paste it in our DOS
command. Then, press the Enter key.

C:\Users\javatpoint> CD C:\Program Files\MySQL\MySQL Server 8.0\bin

In the next step, we need to use the mysqldump tool to copy the database objects and data into the
SQL file. Suppose we want to dump (copy) the database objects and data of the testdb into an SQL
file located at D:\Database_backup folder. To do this, execute the below statement:

mysqldump -u root -p testdb > D:\Database_backup\[Link]

Enter password: **********

The above statement instructs mysqldump tool to log in to the MySQL database server using the
username and password and then exports the database objects and data of the testdb database
to D:\Database_backup\[Link]. It is to note that the operator (>) used for exporting the
database from one location to another.

In the next step, we need to import the D:\Database_backup\[Link] file into testdb_copy database.
To do this, execute the below statement:

mysql -u root -p testdb_copy < D:\Database_backup\[Link]

Enter password: **********

It is to note that the operator (<) used for importing the database from one location to another.

Finally, we can verify whether the above operation is successful or not by using the SHOW
TABLES command in the MySQL command-line tool:

mysql> SHOW TABLES;

IT 221- Information Management Introduction to Structured Query Language

In this output, we can see that all the objects and data from the testdb database to testdb_copy
database have successfully copied.

MySQL CREATE TABLE

A table is used to organize data in the form of rows and columns and used for both storing and
displaying records in the structure format. It is similar to worksheets in the spreadsheet application. A
table creation command requires three things:

o Name of the table

o Names of fields
o Definitions for each field

MySQL allows us to create a table into the database mainly in two ways:

1. MySQL Command Line Client

2. MySQL Workbench

MySQL Command Line Client

MySQL allows us to create a table into the database by using the CREATE TABLE command. Following
is a generic syntax for creating a MySQL table in the database.

CREATE TABLE [IF NOT EXISTS] table_name(

column_definition1,
column_definition2,
........,
table_constraints
);
IT 221- Information Management Introduction to Structured Query Language

Parameter Explanation

The parameter descriptions of the above syntax are as follows:

Parameter Description
database_name It is the name of a new table. It should be unique
in the MySQL database that we have selected.
The IF NOT EXIST clause avoids an error when
we create a table into the selected database that
already exists.
column_definition It specifies the name of the column along with
data types for each column. The columns in table
definition are separated by the comma operator.
The syntax of column definition is as follows:
column_name1 data_type(size) [NULL | NOT
NULL]
table_constraints It specifies the table constraints such as PRIMARY
KEY, UNIQUE KEY, FOREIGN KEY, CHECK, etc.
Example

Let us understand how to create a table into the database with the help of an example. Open the
MySQL console and write down the password, if we have set during installation. Now open the
database in which you want to create a table. Here, we are going to create a table
name "employee_table" in the database "employeedb" using the following statement:

mysql> CREATE TABLE employee_table(

id int NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL,
occupation varchar(35) NOT NULL,
age int NOT NULL,
PRIMARY KEY (id)
);

OTE:
1. Here, NOT NULL is a field attribute, and it is used because we don't want this field to be NULL.
If we try to create a record with a NULL value, then MySQL will raise an error.
2. The field attribute AUTO_INCREMENT specifies MySQL to go ahead and add the next available
number to the id field. PRIMARY KEY is used to define a column's uniqueness. We can use multiple
columns separated by a comma to define a primary key.

Visual representation of creating a MySQL table:

 IT 221- Information Management Introduction to Structured Query Language

We need to use the following command to see the newly created table:

mysql> SHOW TABLES;

It will look like the below output:

ee the table structure:

We can use the following command to see the information or structure of the newly created table:

1. mysql> DESCRIBE employee_table;

It will look like this:

IT 221- Information Management Introduction to Structured Query Language

Create Table Using MySQL Workbench

It is a visual GUI tool used to create databases, tables, indexes, views, and stored procedures quickly
and efficiently. To create a new database using this tool, we first need to launch the MySQL
Workbench and log in using the username and password that you want. It will show the following
screen:

Now do the following steps for table creation:

1. Go to the Navigation tab and click on the Schema menu. Here, we can see all the previously created
databases. Now we are ready to select the database in which a table is created.

2. Select the database, double click on it, and we will get the sub-menu under the database.
These sub-menus are Tables, Views, Functions, and Stored Procedures, as shown in the below screen.
IT 221- Information Management Introduction to Structured Query Language

3. Select Tables sub-menu, right-click on it, and select Create Table option. We can also click on create
a new table icon (shown in red rectangle) to create a table.

4. On the new table screen, we need to fill all the details to create a table. Here, we will enter the table
name (for example, employee_table) and use default collation and engine.

5. Click inside the middle window and fill the column details. Here, the column name contains many
attributes such as Primary Key(PK), Not Null (NN), Unique Index (UI), Binary(B), Unsigned Data
type(UN), Auto Incremental (AI), etc. The following screen explains it more clearly. After filling all the
details, click on the Apply button.
IT 221- Information Management Introduction to Structured Query Language

6. As soon as you click on the Apply button, it will open the SQL statement window. Again, click on
the Apply button to execute the statement and Finish button to save the changes.

7. Now, go to the Schema menu and select the database which contains the newly created table, as
shown in the screen below.
IT 221- Information Management Introduction to Structured Query Language

MySQL ALTER Table

MySQL ALTER statement is used when you want to change the name of your table or any table field.
It is also used to add or delete an existing column in a table.

The ALTER statement is always used with "ADD", "DROP" and "MODIFY" commands according to the
situation.

1) ADD a column in the table

Syntax:

ALTER TABLE table_name

ADD new_column_name column_definition
[ FIRST | AFTER column_name ];

Parameters
table_name: It specifies the name of the table that you want to modify.

new_column_name: It specifies the name of the new column that you want to add to the table.

column_definition: It specifies the data type and definition of the column (NULL or NOT NULL, etc).

FIRST | AFTER column_name: It is optional. It tells MySQL where in the table to create the column. If
this parameter is not specified, the new column will be added to the end of the table.

Example:

In this example, we add a new column "cus_age" in the existing table "cus_tbl".

Use the following query to do this:

 IT 221- Information Management Introduction to Structured Query Language

1. ALTER TABLE cus_tbl

2. ADD cus_age varchar(40) NOT NULL;

Output:

See the recently added column:

SELECT* FROM cus_tbl;

Output:
IT 221- Information Management Introduction to Structured Query Language

2) Add multiple columns in the table

Syntax:

ALTER TABLE table_name

ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
...
;

Example:

In this example, we add two new columns "cus_address", and cus_salary in the existing table "cus_tbl".
cus_address is added after cus_surname column and cus_salary is added after cus_age column.

Use the following query to do this:

ALTER TABLE cus_tbl

ADD cus_address varchar(100) NOT NULL
AFTER cus_surname,
ADD cus_salary int(100) NOT NULL
AFTER cus_age ;

See the recently added columns:

IT 221- Information Management Introduction to Structured Query Language

SELECT* FROM cus_tbl;

3) MODIFY column in the table

The MODIFY command is used to change the column definition of the table.

Syntax:

ALTER TABLE table_name

MODIFY column_name column_definition
[ FIRST | AFTER column_name ];

Example:

In this example, we modify the column cus_surname to be a data type of varchar(50) and force the
column to allow NULL values.

Use the following query to do this:

ALTER TABLE cus_tbl

MODIFY cus_surname varchar(50) NULL;
IT 221- Information Management Introduction to Structured Query Language

See the table structure:

4) DROP column in table

Syntax:

ALTER TABLE table_name

DROP COLUMN column_name;

Let's take an example to drop the column name "cus_address" from the table "cus_tbl".

Use the following query to do this:

IT 221- Information Management Introduction to Structured Query Language

ALTER TABLE cus_tbl

DROP COLUMN cus_address;

Output:

See the table structure:

5) RENAME column in table

Syntax:
IT 221- Information Management Introduction to Structured Query Language

ALTER TABLE table_name

CHANGE COLUMN old_name new_name
column_definition
[ FIRST | AFTER column_name ]

Example:

In this example, we will change the column name "cus_surname" to "cus_title".

Use the following query to do this:

ALTER TABLE cus_tbl

CHANGE COLUMN cus_surname cus_title
varchar(20) NOT NULL;

Output:

6) RENAME table
Syntax:

ALTER TABLE table_name

RENAME TO new_table_name;

Example:

In this example, the table name cus_tbl is renamed as cus_table.

IT 221- Information Management Introduction to Structured Query Language

ALTER TABLE cus_tbl

RENAME TO cus_table;

Output:

See the renamed table:

IT 221- Information Management Introduction to Structured Query Language

MySQL Show/List Tables

The show or list table is very important when we have many databases that contain various tables.
Sometimes the table names are the same in many databases; in that case, this query is very useful.
We can get the number of table information of a database using the following statement:

mysql> SHOW TABLES;

The following steps are necessary to get the list of tables:

Step 1: Open the MySQL Command Line Client that appeared with a mysql> prompt. Next, log in to
the MySQL database server using the password that you have created during the installation of
MySQL. Now, you are connected to the MySQL server, where you can execute all the SQL statements.

Step 2: Next, choose the specific database by using the command below:

mysql> USE database_name;

Step 3: Finally, execute the SHOW TABLES command.

Let us understand it with the example given below. Suppose we have a database name
"mystudentdb" that contains many tables. Then execute the below statement to list the table it
contains:

mysql> USE mystudentdb;

mysql>SHOW TABLES;

The following output explains it more clearly:

IT 221- Information Management Introduction to Structured Query Language

We can also use the FULL modifier with the SHOW TABLES query to get the type of table (Base or
View) that appears in a second output column.

mysql> SHOW FULL TABLES;

This statement will give the following output:

If we want to show or list the table name from different databases or database to which you are not
connected without switching, MySQL allows us to use the FROM or IN clause followed by the database
name. The following statement explains it more clearly:

mysql> SHOW TABLES IN database_name;

The above statement can also be written as:

mysql> SHOW TABLES FROM database_name;

When we execute the below statements, we will get the same result:

mysql> SHOW TABLES FROM mystudentdb;

OR,
mysql> SHOW TABLES IN mystudentdb;

Output:
IT 221- Information Management Introduction to Structured Query Language

Show Tables Using Pattern Matching

Show Tables command in MySQL also provides an option that allows us to filter the returned table
using different pattern matching with LIKE and WHERE clause.

Syntax

The following are the syntax to use pattern matching with show table command:

mysql> SHOW TABLES LIKE pattern;

OR,
mysql> SHOW TABLES WHERE expression;

We can understand it with the example given below where percent (%) sign assumes zero, one, or
multiple characters:

mysql> SHOW TABLES FROM mystudentdb LIKE "stud%";

The above statement will give the following output:

IT 221- Information Management Introduction to Structured Query Language

Let us see another statement that returned the table names starting with "time":

mysql> SHOW TABLES IN mysql LIKE "time%";

The above query will give the following output:

Now, we are going to see how we can use the WHERE clause with the SHOW TABLES command to
list different types of tables (either Base or View type) in the selected database:

mysql> SHOW TABLES FROM sakila WHERE table_type= "VIEW";

This statement gives the below output:

It is noted that if MySQL does not provide the privileges for accessing a Base table or view, then we
cannot get the tables in the result set of the SHOW TABLES command.

Here, we can also see another example of Show Tables statement with the WHERE clause:

mysql> SHOW TABLES In mystudentdb WHERE Tables_in_mystudentdb= "employees";

IT 221- Information Management Introduction to Structured Query Language

It will give the following output:

MySQL Rename Table

Sometimes our table name is non-meaningful, so it is required to rename or change the name of the
table. MySQL provides a useful syntax that can rename one or more tables in the current database.

Syntax
The following are the syntax used to change the name of the table:

mysql> RENAME old_table TO new_table;

Here, we have to make sure that new_table_name must not exist, and old_table_name should be
present in the database. Otherwise, it will throw an error message. It is to ensure that the table is not
locked as well as there are no active transactions before executing this statement.

NOTE: If we use the RENAME TABLE statement, it is required to have ALTER and DROP TABLE
privileges to the existing table. Also, this statement cannot change the name of a temporary table.

We can also use the MySQL RENAME TABLE statement to change more than one table name with a
single statement, as shown below:

RENAME TABLE old_tab1 TO new_tab1,

old_tab2 TO new_tab2, old_tab3 TO new_tab3;

From the MySQL 8.0.13 version, we can change the old table name locked with a LOCK statement and also
uses the WRITE LOCK clause. For example, following are the valid statement:

mysql> LOCK TABLE old_tab_name1 WRITE;

RENAME TABLE old_tab_name1 TO new_tab_name1,
new_tab_name1 TO new_tab_name2;

Following statement are not permitted:

 IT 221- Information Management Introduction to Structured Query Language

mysql> LOCK TABLE old_tab_name1 READ;

RENAME TABLE old_tab_name1 TO new_tab_name1,
new_tab_name1 TO new_tab_name2;

Before MySQL 8.0.13 version, we cannot change the table name that was locked with the LOCK TABLE
statement.

MySQL also use the RENAME TABLE statement for moving a table from one database to other database,
which is show below:

mysql> RENAME TABLE current_db.tablel_name TO other_db.tablel_name;

MySQL RENAME TABLE Example
Let us understand how the RENAME TABLE statement works in MySQL through the various examples.
Suppose we have a table named EMPLOYEE, and due to some reason, there is a need to change it into the
table named CUSTOMER.

Table Name: employee

Next, execute the following syntax to change the table name:

1. mysql> RENAME employee TO customer;

Output

We will see that the table named "employee" will be changed into a new table name "customer":
IT 221- Information Management Introduction to Structured Query Language

In the above output, we can see that if we use the table name employee after executing a RENAME
TABLE statement, it will throw an error message.

How to RENAME Multiple Tables

RENAME TABLE statement in MySQL also allows us to change more than one table name within a
single statement. See the below statement:

Suppose our database "myemployeedb" having the following tables:

 IT 221- Information Management Introduction to Structured Query Language

If we want to change the table name customer into employee and table name shirts into garments,
execute the following statement:

mysql> RENAME TABLE customer TO employee, shirts TO garments;

Output

We can see that the table name customer into employee and table name shirts into garments have
successfully renamed.

Rename table using ALTER statement

The ALTER TABLE statement can also be used to rename the existing table in the current database.
The following are the syntax of the ALTER TABLE statement:

1. ALTER TABLE old_table_name RENAME TO new_table_name;

See the following query that changes the existing table name garments into new table name shirts:

mysql> ALTER TABLE garments RENAME TO shirts:

Output:

Here, we can see that the table name garments renamed into table name shirts.
 IT 221- Information Management Introduction to Structured Query Language

How to RENAME Temporary Table

A temporary table allows us to keep temporary data, which is visible and accessible in the current
session only. So, first, we need to create a temporary table using the following statement:

mysql> CREATE TEMPORARY TABLE Students( name VARCHAR(40) NOT NULL, total_marks DECIMAL(12,2
) NOT NULL DEFAULT 0.00, total_subjects INT UNSIGNED NOT NULL DEFAULT 0);

Next, insert values into this table:

mysql> INSERT INTO Students(name, total_marks, total_subjects) VALUES ('Joseph', 150.75, 2), ('Peter', 180.7
5, 2);

Next, run the show table command to check the temporary table:

1. mysql> SELECT * FROM Students;

Now, run the following command to change the name of the temporary table:

mysql> RENAME TABLE Students TO student_info;

It will throw an error message, as shown below:

Thus, MySQL allows ALTER table statement to rename the temporary table:

mysql> ALTER TABLE Students RENAME TO student_info;

IT 221- Information Management Introduction to Structured Query Language

Output

MySQL TRUNCATE Table

The TRUNCATE statement in MySQL removes the complete data without removing its structure. It is
a part of DDL or data definition language command. Generally, we use this command when we
want to delete an entire data from a table without removing the table structure.

The TRUNCATE command works the same as a DELETE command without using a WHERE clause that
deletes complete rows from a table. However, the TRUNCATE command is more efficient as compared
to the DELETE command because it removes and recreates the table instead of deleting single records
one at a time. Since this command internally drops the table and recreates it, the number of rows
affected by the truncate statement is zero, unlike the delete statement that returns the number of
deleted rows.

This command does not maintain the transaction log during the execution. It deallocates the
data pages instead of rows and makes an entry for the deallocating pages instead of rows in
transaction logs. This command also locks the pages instead of rows; thus, it requires fewer locks and
resources.

The following points must be considered while using the TRUNCATE command:

o We cannot use the WHERE clause with this command so that filtering of records is not possible.
o We cannot rollback the deleted data after executing this command because the log is not maintained
while performing this operation.
o We cannot use the truncate statement when a table is referenced by a foreign key or participates in
an indexed view.
o The TRUNCATE command doesn't fire DELETE triggers associated with the table that is being truncated
because it does not operate on individual rows.
IT 221- Information Management Introduction to Structured Query Language

Syntax
The following syntax explains the TRUNCATE command to remove data from the table:

TRUNCATE [TABLE] table_name;

In this syntax, first, we will specify the table name which data we are going to remove. The TABLE
keyword in the syntax is not mandatory. But it's a good practice to use it to distinguish between
the TRUNCATE() function and the TRUNCATE TABLE statement.

MySQL Truncate Table Example

Let us demonstrate how we can truncate the table with the help of an example. First, we are going to
create a table named "customer" using the below statement:

CREATE TABLE customer (

Id int PRIMARY KEY NOT NULL,
Name varchar(45) NOT NULL,
Product varchar(45) DEFAULT NULL,
Country varchar(25) DEFAULT NULL,
Year int NOT NULL
);

Next, we will add values to this table using the below statement:

INSERT INTO customer ( Id, Name, Product, Country, Year)

VALUES (1, 'Stephen', 'Computer', 'USA', 2015),
(2, 'Joseph', 'Laptop', 'India', 2016),
(3, 'John', 'TV', 'USA', 2016),
(4, 'Donald', 'Laptop', 'England', 2015),
(5, 'Joseph', 'Mobile', 'India', 2015),
(6, 'Peter', 'Mouse', 'England', 2016);

Now, verify the table by executing the SELECT statement whether the records inserted or not:

mysql> SELECT * FROM customer;

We will get the output, as shown below:

 IT 221- Information Management Introduction to Structured Query Language

Now, execute the following statement that truncates the table customer using the TRUNCATE
syntax discussed above:

1. mysql> TRUNCATE TABLE customer;

After the successful execution, we will get the following output:

As we can see, this query returns 0 rows are affected even if all the table records are deleted. We can
verify the deletion of the data by executing the SELECT statement again. This command gives the
following output that shows none of the records present in the table:

How to Truncate Table with Foreign key?

If we perform the TRUNCATE operation for the table that uses a foreign key constraint, we will get the
following error:

1. ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

In that case, we need to log into the MySQL server and disable foreign key checks before executing
the TRUNCATE statement as below:

SET FOREIGN_KEY_CHECKS=0;
IT 221- Information Management Introduction to Structured Query Language

Now, we are able to truncate tables. After execution, re-enable foreign key checks as given below:

SET FOREIGN_KEY_CHECKS=1;
How to truncate all tables in MySQL?
The TRUNCATE statement in MySQL will delete only one table at a time. If we want to delete more
than one table, we need to execute the separate TRUNCATE statement. The below example shows
how to truncate multiple tables in MySQL:

TRUNCATE TABLE table_name1;

TRUNCATE TABLE table_name2;
TRUNCATE TABLE table_name3;

We can also use the below SQL query that generates several TRUNCATE TABLE commands at once
using the table names in our database:

SELECT Concat('TRUNCATE TABLE ', TABLE_NAME)

FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'database_name';

MySQL DESCRIBE TABLE

DESCRIBE means to show the information in detail. Since we have tables in MySQL, so we will use
the DESCRIBE command to show the structure of our table, such as column names, constraints on
column names, etc. The DESC command is a short form of the DESCRIBE command. Both DESCRIBE
and DESC command are equivalent and case sensitive.

Syntax
The following are the syntax to display the table structure:

{DESCRIBE | DESC} table_name;

We can use the following steps to show all columns of the table:

Step 1: Login into the MySQL database server.

Step 2: Switch to a specific database.

Step 3: Execute the DESCRIBE statement.

Let us understand it with the help of an example that explains how to show columns of the table in
the selected database.
IT 221- Information Management Introduction to Structured Query Language

Login to the MySQL Database

The first step is to login to the database server using the username and password. We should see
the output as below image:

>mysql -u root -p
Enter password: **********
mysql>

Switch to a Specific Database

The next step is to open a particular database from which you want to display the table information
using the following query. After the execution of a query, we should see the below output:

mysql> USE mysqltestdb;

Execute DESCRIBE Statement

It is the last step to display the table information. Before executing the DESCRIBE statement, we can
optionally display all the tables stored in our selected database with the SHOW TABLES statement:

mysql> SHOW TABLES;

IT 221- Information Management Introduction to Structured Query Language

For example, if we want to show a customer table's structure, execute the below statement. After
successful execution, it will give the output as below image:

mysql> DESCRIBE customer;

We can also use the DESC statement for practice, which is a shorthand of the DESCRIBE command.
See the below output:
IT 221- Information Management Introduction to Structured Query Language

How to display table information in MySQL Workbench?

To display the column information of the table in MySQL Workbench, we first need to launch the Workbench
tool and login with the username and password to the MySQL database server. We will get the following
screen:

Now do the following steps to show the table information:

1. Go to the Navigation tab and click on the Schema menu. Here, we can see all the previously created
databases. Select any database under the Schema menu, for example, mysqltestdb. It will pop up the
multiple options that can be shown in the following image.
IT 221- Information Management Introduction to Structured Query Language

2. Next, click on the "Tables" that shows all tables stored in the mysqltestdb database. Select a table whose
column information you want to display. Then, mouse hour on that table, it will show three icons. See the
below image:

Now, click the icon (i) shown in the red rectangular box. It will display the following image:

Finally, click on the "Columns" menu to display the table structure.

IT 221- Information Management Introduction to Structured Query Language

MySQL SHOW COLUMNS Command

MySQL also allows the SHOW COLUMNS command to display table structure. It is a more flexible way
to get columns information of a table.

Syntax:

The following are the syntax of the SHOW COLUMNS command:

mysql> SHOW COLUMNS FROM table_name;

For example, if we execute the below query, we will get all columns information of a table in a
particular database:

mysql> SHOW COLUMNS FROM customer;

If we want to show the columns information of a table from another database or not available in
the current database, we can use the following query:

mysql> SHOW COLUMNS FROM database_name.table_name;

OR

mysql> SHOW COLUMNS FROM table_name IN database_name;

In the below image, we can see that we had used the mysqltestdb database. But we had displayed the
column's information of a table from another database without switching to the current database.
IT 221- Information Management Introduction to Structured Query Language

If we want to display the more column information, we need to add FULL keyword with the SHOW
TABLES statement as follows:

mysql> SHOW FULL COLUMNS FROM table_name;

For example, the below SQL query lists all columns of the student_info table in the mystudentdb
database:

mysql> SHOW FULL COLUMNS FROM student_info;

After execution, we can see that this command adds the collation, privileges, default,
and comment columns to the result set.
IT 221- Information Management Introduction to Structured Query Language

MySQL EXPLAIN
The EXPLAIN keyword is synonyms to the DESCRIBE statement, which is used to obtain information
about how MySQL executes the queries. It can work with INSERT, SELECT, DELETE, UPDATE,
and REPLACE queries. From MySQL 8.0.19 and later versions, it can also work with TABLE statements.
When we use this keyword in queries, it will process the statement and provide the information about
how tables are joined, the order of the table, estimated partitions and rows.

Example

If we want to show the execution plan of a SELECT statement, we can use the query as below:

mysql> EXPLAIN SELECT * FROM customer;

Output:

This query produces the following information:

MySQL DROP Table

MYSQL uses a Drop Table statement to delete the existing table. This statement removes the complete
data of a table along with the whole structure or definition permanently from the database. So, you
must be very careful while removing the table because we cannot recover the lost data after deleting
it.

Syntax
The following are the syntax to remove the table in MySQL:

mysql> DROP TABLE table_name;

OR,
mysql> DROP TABLE schema_name.table_name;

The full syntax of DROP TABLE statement in MySQL is:

DROP [ TEMPORARY ] TABLE [ IF EXISTS ] table_name [ RESTRICT | CASCADE ];

The above syntax used many parameters or arguments. Let us discuss each in detail:
IT 221- Information Management Introduction to Structured Query Language

Parameter Name Description

TEMPORARY It is an optional parameter that specifies to delete
the temporary tables only.
table_name It specifies the name of the table which we are
going to remove from the database.
IF EXISTS It is optional, which is used with the DROP TABLE
statement to remove the tables only if it exists in
the database.
RESTRICT and CASCADE Both are optional parameters that do not have
any impact or effect on this statement. They are
included in the syntax for future versions of
MySQL.
NOTE: It is to be noted that you must have a DROP privileges to execute the DROP TABLE
statement in the MySQL.
Example
This example specifies how we can drop an existing table from the database. Suppose our database
contains a table "orders" as shown in the image below:

To delete the above table, we need to run the following statement:

mysql> DROP TABLE orders;

It will remove the table permanently. We can also check the table is present or not as shown in the
below output:

If we try to delete a table that does not exist in the database, we will get an error message as given
below:
IT 221- Information Management Introduction to Structured Query Language

If we use the IF EXISTS clause with the DROP TABLE statement, MySQL gives the warning message
which can be shown in the below output:

How to DROP table in Workbench

1. To delete a table, you need to choose the table, right-click on it, and select the Drop Table option.
The following screen appears:

2. Select Drop Now option in the popup window to delete the table from the database instantly.

MySQL DROP Multiple Table

Sometimes we want to delete more than one table from the database. In that case, we have to use
the table names and separate them by using the comma operator. The following statement can be
used to remove multiple tables:

DROP TABLE IF EXISTS table_name1, table_name2, table, ......., table_nameN;

MySQL TRUNCATE Table vs. DROP Table
You can also use the DROP TABLE command to delete the complete table, but it will remove complete
table data and structure both. You need to re-create the table again if you have to store some data.
But in the case of TRUNCATE TABLE, it removes only table data, not structure. You don't need to re-
create the table again because the table structure already exists.
IT 221- Information Management Introduction to Structured Query Language

MySQL Temporary Table

MySQL has a feature to create a special table called a Temporary Table that allows us to keep
temporary data. We can reuse this table several times in a particular session. It is available in MySQL
for the user from version 3.23, and above so if we use an older version, this table cannot be used.
This table is visible and accessible only for the current session. MySQL deletes this table automatically
as long as the current session is closed or the user terminates the connection. We can also use
the DROP TABLE command for removing this table explicitly when the user is not going to use it.

If we use a PHP script to run the code, this table removes automatically as long as the script has
finished its execution. If the user is connected with the server through the MySQL client, then this
table will exist until the user closes the MySQL client program or terminates the connection or
removed the table manually.

A temporary table provides a very useful and flexible feature that allows us to achieve complex tasks
quickly, such as when we query data that requires a single SELECT statement with JOIN clauses. Here,
the user can use this table to keep the output and performs another query to process it.

A temporary table in MySQL has many features, which are given below:

o MySQL uses the CREATE TEMPORARY TABLE statement to create a temporary table.
o This statement can only be used when the MySQL server has the CREATE TEMPORARY TABLES privilege.
o It can be visible and accessible to the client who creates it, which means two different clients can use
the temporary tables with the same name without conflicting with each other. It is because this table
can only be seen by that client who creates it. Thus, the user cannot create two temporary tables with
the same name in the same session.
o A temporary table in MySQL will be dropped automatically when the user closes the session or
terminates the connection manually.
o A temporary table can be created by the user with the same name as a normal table in a database. For
example, if the user creates a temporary table with the name student, then the existing student table
cannot be accessible. So, the user performs any query against the student table, is now going to refer
to the temporary student table. When the user removes a temporary table, the permanent student table
becomes accessible again.

Syntax of Creating Temporary Table

In MySQL, the syntax of creating a temporary table is the same as the syntax of creating a normal
table statement except the TEMPORARY keyword. Let us see the following statement which creates
the temporary table:

mysql> CREATE TEMPORARY TABLE table_name (

IT 221- Information Management Introduction to Structured Query Language

column_1, column_2, ..., table_constraints

);

If the user wants to create a temporary table whose structure is the same as an existing table in the
database, then the above statement cannot be used. Instead, we use the syntax as given below:

Mysql> CREATE TEMPORARY TABLE temporary_table_name SELECT * FROM original_table_

name LIMIT 0;
MySQL Temporary Table Example
Let us understand how we can create a temporary table in MySQL. Execute the following statement
that creates a temporary table in the selected database:

mysql> CREATE TEMPORARY TABLE Students( student_name VARCHAR(40) NOT NULL, tota
l_marks DECIMAL(12,2) NOT NULL DEFAULT 0.00, total_subjects INT UNSIGNED NOT NULL D
EFAULT 0);

We can see the below image:

Next, we need to insert values in the temporary table:

mysql>INSERT INTO Students(student_name, total_marks, total_subjects) VALUES ('Joseph', 150.75, 2

), ('Peter', 180.75, 2);

After executing the above statement, it will give the below output:
IT 221- Information Management Introduction to Structured Query Language

Now, run the following query to get the result:

mysql> SELECT * FROM Students;

After the successful execution of the above statement, we will get the output as below:

Now, run the below command to see the temporary table:

mysql> SELECT * FROM temp_customers;

We can also perform queries from the above temporary table "temp_customers" similar to the
querying data from a permanent table. The following query explains it more clearly:

Mysql> SELECT cust_name, prod_name, price FROM temp_customers;

After executing the above statement, it will give the output as below:
IT 221- Information Management Introduction to Structured Query Language

It is to be noted that when we run a SHOW TABLES command, then our temporary table will not be
shown on the list. Also, if we close the current session and then will execute the SELECT statement, we
will get a message saying that no data available in the database, and even the temporary table will
not exist.

A Temporary Table whose structure is based on a normal table

In this example, we are going to create a temporary table whose structure is based on the already
available tables in the database.

Suppose our database has the following table as permanent:

Here, the structure of a temporary table is created by using the SELECT statement and merge two
tables using the INNER JOIN clause and sorts them based on the price. Write the following statement
in the MySQL prompt:

CREATE TEMPORARY TABLE temp_customers

SELECT c.cust_name, [Link], o.prod_name, [Link]
FROM orders o
IT 221- Information Management Introduction to Structured Query Language

INNER JOIN customer c ON c.cust_id = o.order_id

ORDER BY [Link] DESC;

When we execute the above statement, we will get the following message:

Now, run the below command to see the temporary table:

mysql> SELECT * FROM temp_customers;

We can also perform queries from the above temporary table "temp_customers" similar to the
querying data from a permanent table. The following query explains it more clearly:

Mysql> SELECT cust_name, prod_name, price FROM temp_customers;

After executing the above statement, it will give the output as below:

NOTE: It is noted that we can use IF NOT EXISTS keyword to avoid the "table already exists" error.
How to Drop Temporary Table in MySQL
MySQL allows us to remove the temporary table using the DROP TABLE statement. But, it"s a good
practice to use the TEMPORARY keyword with the DROP TABLE statement. This keyword helps us to
avoid the mistake of deleting a permanent table when the temporary table and permanent table have
the same name in the current session. So, it is recommended to use the following query for removing
the temporary table:

mysql> DROP TEMPORARY TABLE table_name;

IT 221- Information Management Introduction to Structured Query Language

This query will not remove a permanent table of the database that means it only deletes a temporary
table. If we try to delete a permanent table with this statement, it will throw an error message saying
that you are deleting a table is unknown. For example, if we want to remove the above temporary
table "temp_customers", we need to use the following statement:

mysql> DROP TEMPORARY TABLE top_customers;

MySQL Add/Delete Column

A column is a series of cells in a table that may contain text, numbers, and images. Every column stores
one value for each row in a table. In this section, we are going to discuss how to add or delete columns
in an existing table.

How can we add a column in MySQL table?

MySQL allows the ALTER TABLE ADD COLUMN command to add a new column to an existing table.
The following are the syntax to do this:

ALTER TABLE table_name

ADD COLUMN column_name column_definition [FIRST|AFTER existing_column];

In the above,

o First, we need to specify the table name.

o Next, after the ADD COLUMN clause, we have to specify the name of a new column along with
its definition.
o Finally, we need to specify the FIRST or AFTER keyword. The FIRST Keyword is used to add the
column as the first column of the table. The AFTER keyword is used to add a new column after
the existing column. If we have not provided these keywords, MySQL adds the new column as
the last column in the table by default.

Sometimes it is required to add multiple columns into the existing table. Then, we can use the syntax
as follows:

ALTER TABLE table_name

ADD COLUMN column_name1 column_definition [FIRST|AFTER existing_column],
ADD COLUMN column_name2 column_definition [FIRST|AFTER existing_column];
MySQL ADD COLUMN Example
Let us understand it with the help of various examples. Here, we will create a table named "Test" using
the following statements:
IT 221- Information Management Introduction to Structured Query Language

CREATE TABLE Test (

Stude_id int AUTO_INCREMENT PRIMARY KEY,
Name varchar(55) NOT NULL
);

The table structure looks like the below image:

After creating a table, we want to add a new column named City to the Test table. Since we have not
specified the new column position explicitly after the column name, MySQL will add it as the last
column.

ALTER TABLE Test

ADD COLUMN City VARCHAR(30) NOT NULL;

Next, we want to add a new column named Phone_number to the Test table. This time, we will
explicitly specify the new column position so that MySQL adds the column to the specified place.

ALTER TABLE Test

ADD COLUMN Phone_number VARCHAR(20) NOT NULL AFTER Name;

In the below output, we can see that the two columns are added successfully at the specified position.
IT 221- Information Management Introduction to Structured Query Language

Let us add some data into the Test table using the INSERT statement as follows:

INSERT INTO Test( Name, Phone_number, City)

VALUES ('Peter', '34556745362', 'California'),
('Mike', '983635674562', 'Texas');

It will look like this.

Suppose we want to add more than one column ,(Branch, Email) in the Test table. In that case,
execute the statement as follows:

ALTER TABLE Test

ADD COLUMN Branch VARCHAR(30) DEFAULT NULL After Name,
ADD COLUMN Email VARCHAR(20) DEFAULT NULL AFTER Phone_number;
IT 221- Information Management Introduction to Structured Query Language

It is to note that columns Branch and Email are assigned to default value NULL. However, the Test
table already has data so that MySQL will use null values for those new columns.

We can verify the record in the Test table as below:

If we accidentally add a new column with the existing column name, MySQL will throw an error. For
example, execute the below statement that issues an error:

ALTER TABLE Test

ADD COLUMN City VARCHAR(30) NOT NULL;

We will get the following error message.

How can we rename a column in MySQL table?

MySQL allows the ALTER TABLE CHANGE COLUMN statement to change the old column with a new name.
The following are the syntax to do this:

ALTER TABLE table_name

CHANGE COLUMN old_column_name new_column_name column_definition [FIRST|AFTER
existing_column];

In the above,

o First, we need to specify the table name.

IT 221- Information Management Introduction to Structured Query Language

o Next, after the CHANGE COLUMN clause, we have to specify the old column name and new
column name along with its definition. We must have to specify the column definition even it
will not change.
o Finally, we need to specify the FIRST or AFTER keyword. It is optional that specified when we
need to change the column name at the specific position.

MySQL RENAME COLUMN Example

This example shows how we can change the column name in the MySQL table:

ALTER TABLE Test

CHANGE COLUMN Phone_number Mobile_number
varchar(20) NOT NULL;

This statement will change the column name Phone_number with the new name Mobile_number in
the Test table. The below output explains it more clearly.

How can we drop a column from MySQL table?

Sometimes, we want to remove single or multiple columns from the table. MySQL allows the ALTER
TABLE DROP COLUMN statement to delete the column from the table. The following are the syntax
to do this:

ALTER TABLE table_name DROP COLUMN column_name;

In the above,

o First, we need to specify the table name from which we want to remove the column.
o Next, after the DROP COLUMN clause, we have to specify the column name that we want to delete
from the table. It is to note that the COLUMN keyword is optional in the DROP COLUMN clause.
IT 221- Information Management Introduction to Structured Query Language

If we want to remove multiple columns from the table, execute the following statements:

ALTER TABLE table_name

DROP COLUMN column_1,
DROP COLUMN column_2,
......;

MySQL DROP COLUMN Example

This example explains how we can delete a column from the MySQL table. Here, we will take a
table "Test" that we have created earlier and look like the below image:

Suppose we want to delete a column name "Branch" from the Test table. To do this, execute the
below statement:

ALTER TABLE Test DROP COLUMN Branch;

After successful execution, we can verify the result below where a column Branch is deleted from the
table:

In some cases, it is required to remove multiple columns from the table. To do this, we need to execute
the below statement:

ALTER TABLE Test

IT 221- Information Management Introduction to Structured Query Language

DROP COLUMN Mobile_number,

DROP COLUMN Email;

The command will delete both columns. We can verify it using the queries given in the below image.

Remember the following key points before deleting a column from the table:

MySQL works with relational databases where the schema of one table can depend on the columns
of another table. So when we remove a column from one table, it will effects all dependent tables also.
Consider the below points while removing column:

o When we remove columns from a table, it will affect all associated objects such as triggers,
stored procedures, and views. Suppose we delete a column that is referencing in the trigger.
After removing the column, the trigger becomes invalid.
o The dropped column depends on other applications code, must also be changed, which takes
time and effort.
o When we remove a column from the large table, it will affect the database's performance during
removal time.

MySQL Show Columns

Columns in the table are a series of cells that can stores text, numbers, and images. Every column
stores one value for each row in a table. When we work with the MySQL server, it is common to display
the column information from a particular table. In this section, we are going to discuss how to display
or list columns in an existing table.

MySQL provides two ways for displaying the column information:

1. MySQL SHOW COLUMNS Statement

IT 221- Information Management Introduction to Structured Query Language

2. MySQL DESCRIBE Statement

Let us discuss both in detail.

MySQL SHOW COLUMNS Statement

SHOW COLUMNS statement in MySQL is a more flexible way to display the column information in a
given table. It can also support views. Using this statement, we will get only that column information
for which we have some privilege.

Syntax
The following is a syntax to display the column information in a specified table:

SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}

{FROM | IN} table_name
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]

Let's discuss the syntax parameters in detail.

The EXTENDED is an optional keyword to display the information, including hidden columns. MySQL
uses hidden columns internally that are not accessible by users.

The FULL is also an optional keyword to display the column information, including collation,
comments, and the privileges we have for each column.

The table_name is the name of a table from which we are going to show column information.

The db_name is the name of a database containing a table from which we will show column
information.

The LIKE or WHERE clause is used to display only the matched column information in a given table.

We can also use the alternative of table_name FROM db_name syntax as db_name.tbl_name.
Therefore, the below statements are equivalent:

SHOW COLUMNS FROM mytable_name FROM mydb_name;

OR,
SHOW COLUMNS FROM mydb_name.mytable_name;
SHOW COLUMNS Statement Example
Let us understand how this statement works in MySQL through various examples.
IT 221- Information Management Introduction to Structured Query Language

Suppose we have a table named student_info in a sample database that contains the data as follows:

Next, if we want to get the columns information of this table, we can use the statement as follows:

mysql> SHOW COLUMNS FROM student_info;

We will see the below output:

If we want to filter the columns of a table, we need to use the LIKE or WHERE clause in the statement. See the
below query:

mysql> SHOW COLUMNS FROM student_info LIKE 's%';

This query shows the column information that starts with the letter S only. See the below output:
IT 221- Information Management Introduction to Structured Query Language

If we want to display hidden column information, we need to add the FULL keyword to the SHOW
COLUMNS statement as follows:

mysql> SHOW FULL COLUMNS FROM student_info;

It returns the below output that displays all columns information of the student_info table in the
sample database.

The SHOW COLUMNS statement provides the below information for each column in a given
table:

Field: It indicates the name of the column in a given table.

Type: It indicates the data type of each column.

Collation: It is used to sequence the order of a specific character set. Here it indicates the string
column for non-binary values and NULL for other columns. We will see this column only when we use
the FULL keyword.

Null: It indicates the nullability of a column. If a column can store NULL values, it returns YES. And if
a column cannot store NULL value, it contains NO value.

Key: It indicates the indexing of the columns as PRI, UNI, and MUL. Let us understand this field in
detail.
IT 221- Information Management Introduction to Structured Query Language

o If we have not specified any key, it means the column is not indexed. Otherwise, index as a
secondary column in a multiple-column.
o If the column is specified as a PRI, it means the column is a PRIMARY KEY or one of the fields
in a multiple-column PRIMARY KEY.
o If the column is specified as a UNI, it means the column contains a UNIQUE index.
o If the column is specified as a MUL, it means the column is the first column of a non-unique
index where we can use a given value multiple times.
o If the column is specified by more than one key-value, this field displays the key which has the
highest priority (the key priority is in the order of PRI, UNI, and MUL).

Default: It indicates the default value to the column. If the column includes no DEFAULT clause or has
an explicit NULL default, it contains a NULL value.

Extra: It indicates the additional information related to a given column. This field is non-empty in the
following cases:

o If the column is specified with the AUTO_INCREMENT attribute, its value is filled with
auto_increment.
o If the column is specified with TIMESTAMP or DATETIME that have the ON UPDATE
CURRENT_TIMESTAMP attribute, its value is filled with on update CURRENT_TIMESTAMP.
o For the generated columns, its value filled with VIRTUAL GENERATED or VIRTUAL STORED.
o If the column contains an expression default value, its value is filled with DEFAULT_GENERATED.

Privileges: It indicates the privileges that we have for the column. We will see this column only when
we use the FULL keyword.

Comment: It indicates the comment that we have included in the column definition. We will see this
column only when we use the FULL keyword.

MySQL DESCRIBE Statement

DESCRIBE statement in MySQL is also provides information similar to the SHOW COLUMNS command.

Syntax
The following is the syntax to display the column information in a given table:

{DESCRIBE | DESC} table_name;

In this syntax, the DESCRIBE and DESC clause return the same result.
IT 221- Information Management Introduction to Structured Query Language

Example
If we want to show column information of students_info table, we can execute the below statement.

mysql> DESCRIBE students_info;

After successful execution, it will give the output as below image:

How to display column information in MySQL Workbench?

We first launch the tool and log in with the username and password to display the given table's column
information in MySQL Workbench. Now, we need to do the following steps to show the column
information:

1. Go to the Navigation tab and click on the Schema menu where all the previously created
databases available. Select your desired database (for example, mstudentdb). It will pop up the
following options.
IT 221- Information Management Introduction to Structured Query Language

2. Click on the Tables that show all tables stored in the mysqltestdb database. Select a table whose
column information you want to display. Then, mouse hour on that table, it will show three icons. See
the below image:

3. Click the icon (i) shown in the red rectangular box. We should get the screen as follows:

4. Finally, click on the "Columns" menu. We can see the column information as like below output.
IT 221- Information Management Introduction to Structured Query Language

MySQL Rename Column

Sometimes our column name is non-meaningful, so it is required to rename or change the column's
name. MySQL provides a useful syntax that can rename one or more columns in the table. Few
privileges are essential before renaming the column, such as ALTER and DROP statement privileges.

MySQL can rename the column name in two ways:

1. Using the CHANGE statement

2. Using the RENAME statement

Using the CHANGE Statement:

The following are the syntax that illustrates the column rename using the CHANGE statement:

ALTER TABLE table_name

CHANGE COLUMN old_column_name new_column_name Data Type;

In this syntax, we can see that we may require re-specification of all the column attributes. This syntax
can also allow us to change the column's data types. But, sometimes the CHANGE statement might
have the following disadvantages:

o ll information of column attributes might not be available to the application for renaming.
o There is a risk of accidental data type change that might result in the application's data loss.

Example
Let us understand how the CHANGE statement works in MySQL to rename a column through the
various examples. Suppose we have a table named balance that contains the data as follows:
IT 221- Information Management Introduction to Structured Query Language

Due to some reason, we need to change the column name account_num along with its data type.
In that case, we first check the structure of the table using the DESCRIBE statement as follows:

In this image, we can see that the data type of column name account_num is int. And we want to
change this column name as account_no and its data type as int to varchar. Thus, we can execute
the below statement to do this:

mysql> ALTER TABLE balance

CHANGE COLUMN account_num account_no VARCHAR(25);

After executing the above command, we can verify it by using the DESCRIBE statement again. In the
below image, the column name account_num and its data type have changed successfully.
IT 221- Information Management Introduction to Structured Query Language

Using the RENAME Statement:

To remove the drawback of a CHANGE statement, MySQL proposed the following syntax that
illustrates the changing of the column name using a RENAME statement:

ALTER TABLE table_name

RENAME COLUMN old_column_name TO new_column_name;
Example
Let us understand how the RENAME statement works in MySQL to change the column name through
the various examples. Suppose we have a table named customer that contains the following data:

Suppose we want to change the column name account with account_no without changing its data
types. We can do this by executing the below statement:

mysql> ALTER TABLE customer RENAME COLUMN account to account_no;

After executing the above command, we can verify it by using the SELECT statement again. In the
below image, the column name account has changed successfully.
 IT 221- Information Management Introduction to Structured Query Language

Renaming Multiple Columns

MySQL also allows us to change the multiple column names within a single statement. If we want to
rename multiple column names, we might use the below syntax:

ALTER TABLE table_name

CHANGE old_column_name1 new_column_name1 Data Type,
CHANGE old_column_name2 new_column_name2 Data Type,
...
...
CHANGE old_column_nameN new_column_nameN Data Type;

OR

ALTER TABLE table_name

RENAME COLUMN old_column_name1 TO new_column_name1,
RENAME COLUMN old_column_name2 TO new_column_name2,
...
...
RENAME COLUMN old_column_nameN TO new_column_nameN;
Example
Suppose we want to change column names id and customer_name from the customer table. To
change multiple column names within a single statement, we can use the statement as follows:

1. mysql> ALTER TABLE customer

2. CHANGE id cust_id int,
3. CHANGE customer_name cust_name varchar(45);
IT 221- Information Management Introduction to Structured Query Language

After executing the above command, we can verify it by using the SELECT statement again. In the
below image, the column name id and customer_name have changed successfully:

Let us again change the currently modifying column name through the RENAME COLUMN statement
as follows:

mysql> ALTER TABLE customer

RENAME COLUMN cust_id TO id,
RENAME COLUMN cust_name TO customer_name;

After executing the above command, we can verify it by using the DESCRIBE statement again. In the
below image, the column name cust_id and cust_name have changed successfully:

In this article, we have learned an introduction of the MySQL RENAME column and how to change the
column name in a specified table, along with a query example for better understanding.
IT 221- Information Management Introduction to Structured Query Language