Our Blog

Share

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)
Share

Gopal Thorve

0 comments
Read more:
POP3 Client-Server Communication

POP3 Client-Server Communication Let us see how POP3 Client and Server communicates with each other step by step. Here, I...

Close