Most Useful MySQL Commands

Show Databases

This command is used to display all the available databases in your MySQL server. The command can be used from the MySQL prompt.
Example with output

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| binarynote         |
| davschool          |
| moodle             |
| mysql              |
| performance_schema |
| report_card        |
| school             |
| skin               |
| softmany           |
| sys                |
| united             |
| wallpaper          |
+--------------------+
13 rows in set (0.03 sec)

Show Tables command

Show table command is used to display the names of all the available Tables in the current database. That is set using use database command.
Example

mysql> use binarynote;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_binarynote |
+----------------------+
| grade                |
| gradesubject         |
| gst                  |
| question             |
| subject              |
| topic                |
| user                 |
+----------------------+
7 rows in set (0.00 sec)

Use Database

Use command is used to load the database into its memory to make it current database.
Example

mysql> use school
Database changed

Select Database()

Select database command shows the currently active database in its memory
Example

mysql> select database();
+------------+
| database() |
+------------+
| school     |
+------------+
1 row in set (0.00 sec)

Describe /Desc Command

Describe/Desc command is used to display the structure of any exisiting table on the screen. The general syntax is as follows

DESCRIBE TableName

Example command on MySQL prompt

mysql>desc student;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| admno  | char(10)   | NO   | PRI | NULL    |       |
| name   | char(30)   | NO   |     | NULL    |       |
| stream | char(20)   | YES  |     | NULL    |       |
| marks  | float(5,2) | YES  |     | NULL    |       |
| grade  | char(2)    | YES  |     | NULL    |       |
| fees   | float(7,2) | YES  |     | NULL    |       |
| gender | char(1)    | YES  |     | NULL    |       |
| gameID | int(4)     | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

Change Column name in MySQL

Syntax to change a column name of any table using MySQL Alter Table command.

ALTER TABLE table_name CHANGE old_column_name new_column_name  datatype(size) constraint

Example

alter table Items Change id  item_id int(11)

Change Table name in MySQL

Syntax to Change the Table name of any existing Table

RENAME TABLE old_table_name New_table_name

Example

RENAME TABLE item items

Add auto increment to column

Primary Key contains unique values and thus this feature is required in almost all the tables to store unique values.
Syntax

# use this command with exisiting column name
ALTER TABLE table_name MODIFY column_name datatype(size) AUTO_INCREMENT

These are the few most useful MySQL commands to manage your MySQL database. Send your queries on our email id.