Top Most Popular MySQL Commands

This guide highlights essential MySQL commands with examples to help you manage databases more easily.

Create database command

To create a new database in MySQL.

# mysql> create database bitscentric_employee;

Check database command

To check the list of databases in MySQL.

# mysql> show databases;

To change database

# mysql> use bitscentric_employee;

CREATE USER

Use this command to create database users.

# create user employee identified by 'Ek123!@#';

DROP USER

Used to delete a user from the database server permanently. 

# mysql> drop user employee;

SHOW USER

Shows users with access to a specific database server.

# SELECT USER from bitscentric_employee;

DROP DATABASE

Deletes a database and all its data.

# mysql> drop database bitscentric;

Table Commands Overview

This section covers essential commands for creating and managing tables in a database. Here’s a quick rundown of what each command does:

• CREATE TABLE – Create a new table.
• SHOW TABLES – List all tables in the database.
• ALTER TABLE – Modify an existing table.
• DESCRIBE TABLE – View the structure of a table.
• TRUNCATE TABLE – Remove all data from a table without deleting the table itself.
• DROP TABLE – Delete a table completely.
• RENAME TABLE – Change the name of a table.
• INSERT INTO – Add new records to a table.
• UPDATE – Modify existing records.
• DELETE FROM – Remove specific records from a table.
• ADD COLUMN – Add a new column to a table.
• DROP COLUMN – Remove a column from a table.
• SHOW COLUMNS – Display all columns of a table.
• RENAME COLUMN – Change the name of a column.

CREATE TABLE

# mysql> create table emp( ID int, Name varchar(200), Age int, Address varchar(200), State varchar(200), Country varchar(200), RegDate date);

SHOW TABLE

To see all the tables in a database, first, make sure you have selected the database you want to view. Then, you can list all the tables it contains.

# mysql> show tables;

ALTER TABLE

Used to add, modify, or remove columns or constraints.

# mysql> alter table emp add phone int;

DESC Command

Describe the table.

# mysql> desc emp;

DESCRIBE TABLE

Shows the table’s structure, including column names, data types, default values, and keys.

# mysql> DESCRIBE emp;

TRUNCATE TABLE

To removes all data from a table but keeps the table itself.

# mysql> truncate table emp;

DROP TABLE

Used to delete a table including its data permanently.

# mysql> drop table emp;

RENAME TABLE

Used to rename a table.

# mysql> rename table emp to employee;

INSERT INTO

Used to insert new records into a table.

# mysql> insert into employee( ID,Name,Age,Address,State,Country,RegDate )values('01','Aakash','28','Noida','UP','India','2024-06-01');

# mysql> insert into employee
    -> values ('02','Sandeep','21','Delhi','Delhi','India','2024-09-01');

UPDATE TABLE

Used to update data in a table.

# mysql> ALTER TABLE employee ADD COLUMN PIN VARCHAR(10);
# mysql> UPDATE employee SET Name='Aakash', PIN='201301' WHERE ID=1;

Select Command

To show the table inserted data.

# mysql> select * from employee;

DELETE TABLE

Used to delete the existing data items in a table.

# mysql> delete from employee where name='sandeep';

ADD COLUMNS IN A TABLE

Used to add new columns in a table.

# mysql> alter table employee
    -> add column Phone varchar(20) after PIN;

Used to delete columns in a table.

# mysql> ALTER TABLE employee DROP COLUMN Phone;

To add multiple columns

# mysql> ALTER TABLE employee
    -> ADD COLUMN Father_Name VARCHAR(200) AFTER Name,
    -> ADD COLUMN phone INT AFTER RegDate;

SHOW COLUMNS IN A TABLE

Used to display all the columns present in the database.

# mysql> show columns from employee from bitscentric_employee;

RENAME COLUMNS IN A TABLE

Used to rename columns present in a table.

# mysql> alter table employee
    -> rename column ID to Emp_Id;