MySQL Most Useful Commands
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_INCREMENTalter table items modify item_id int(11) auto_increment;
These are the few most useful MySQL commands to manage your MySQL database. Send your queries on our email id.