Here is the list common MySQL commands which we need to know in order to perform basic tasks with MySQL Server.
Connecting to MySQL server using Command Line Client
Execute “mysql -u username -p” command from the Command Prompt and enter the password when prompted.
D:\hMailServer\MySQL\MySQL Server 5.5\bin>mysql -u root -p Enter password: ******
List databases on the MySQL server
Use “show databases;” command to display list of all databases on the MySQL server.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
Create a database on MySQL Server
Use “create database db_name;” command to create a new database on MySQL server.
mysql> create database wordpress; Query OK, 1 row affected (0.00 sec)
Delete a database from MySQL Server
Use “drop database db_name;” command to delete a database from MySQL server.
mysql> drop database wordpress; Query OK, 0 rows affected (0.05 sec)
Switch to a database
Use “use db_name;” command to switch
mysql> use wordpress; Database changed
List all tables in the current database
First switch to the database using “use db_name;” command and the issue “show tables;” command
mysql> use wordpress; Database changed mysql> show tables; +---------------------+ | Tables_in_wordpress | +---------------------+ | wp_commentmeta | | wp_comments | | wp_links | | wp_options | +---------------------+ 4 rows in set (0.00 sec)
To see table’s field format
To view the fields and data type of each field with some other information issue “describe table_name;” command.
mysql> describe my_table; +------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+---------+----------------+ | meta_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | comment_id | bigint(20) unsigned | NO | MUL | 0 | | | meta_key | varchar(255) | YES | MUL | NULL | | | meta_value | longtext | YES | | NULL | | +------------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)
Show all data in a table
To display all the data in a table issue “SELECT * FROM table_name;” command.
mysql> SELECT * FROM wp_commentmeta; +---------+------------+-----------------------+------------+ | meta_id | comment_id | meta_key | meta_value | +---------+------------+-----------------------+------------+ | 29 | 29 | _wp_trash_meta_status | 0 | | 30 | 29 | _wp_trash_meta_time | 1322591972 | | 31 | 30 | _wp_trash_meta_status | 0 | | 32 | 30 | _wp_trash_meta_time | 1322592408 | | 33 | 31 | _wp_trash_meta_status | 0 | | 34 | 31 | _wp_trash_meta_time | 1322596386 | | 35 | 36 | _wp_trash_meta_status | 0 | | 36 | 48 | _wp_trash_meta_status | 0 | | 37 | 47 | _wp_trash_meta_status | 0 | | 38 | 46 | _wp_trash_meta_status | 0 | | 39 | 45 | _wp_trash_meta_status | 0 | | 40 | 44 | _wp_trash_meta_status | 0 | | 41 | 43 | _wp_trash_meta_status | 0 | | 42 | 42 | _wp_trash_meta_status | 0 | | 43 | 41 | _wp_trash_meta_status | 0 | | 45 | 39 | _wp_trash_meta_status | 0 | | 46 | 37 | _wp_trash_meta_status | 0 | | 47 | 38 | _wp_trash_meta_status | 0 | | 48 | 56 | _wp_trash_meta_status | 0 | | 49 | 53 | _wp_trash_meta_status | 0 | | 50 | 52 | _wp_trash_meta_status | 0 | | 51 | 50 | _wp_trash_meta_status | 0 | | 52 | 51 | _wp_trash_meta_status | 0 | | 53 | 57 | _wp_trash_meta_status | 0 | | 54 | 58 | _wp_trash_meta_status | 0 | | 55 | 59 | _wp_trash_meta_status | 0 | | 56 | 61 | _wp_trash_meta_status | 0 | | 57 | 61 | _wp_trash_meta_time | 1323632569 | | 58 | 60 | _wp_trash_meta_status | 0 | | 59 | 60 | _wp_trash_meta_time | 1323632569 | | 60 | 62 | _wp_trash_meta_status | 0 | +---------+------------+-----------------------+------------+ 31 rows in set (0.00 sec)
Delete a table
To delete a table from current database issue “drop table table_name;” command.
mysql> use wordpress; Database changed mysql> drop table my_table; Query OK, 0 rows affected (0.03 sec)