Important MySQL commands Every Developer Should Know

If you are a developer, then should know the basic commands of MySQL. Because MySQL is the most popular database that has been used by organizations for many applications. Knowing the basic commands will make you more productive when working with databases and tables. So here you can see a list of MySQL commands which may help you in some situations.

Important MySQL commands

We have segregated the commands into different sections. Each section has the explanation about the commands and its uses.

Connecting To MySQL Hosts

The first thing you would do is connect to the MySQL server. It could be local or a remote host.

Connecting to Local Database

You can access the MySQL database by running this command.

mysql -u root -p

Connecting to Remote Host

mysql -u <useraname> -p -h <host IP or DNS name>

mysql -u michael -p -h 52.34.56.7

Database Management

It is nothing but managing the database. Managing means if any user wants any data from the database, then it can retrieve the data in a most efficient way. The database management system will help to manipulate the query given to it. The data in the database have different operations like creating a database, deleting the database and listing databases.

1. Creating Database

You can create a database by typing the command.

CREATE DATABASE stock;

2. Listing Databases

You can see the databases by typing this command:

show databases;

3. Deleting Database

If you want to delete a database use the command as :

DROP DATABASE stock;

Table Management

The Table consists of mainly two parts. one is the table and the second one is the database.

1. Creating Tables

According to your data, you have to create the table by using the below commands. you have to decide how many rows and columns are needed for your data storing purpose. Before that, you have to select the database.

USE stock

For example, here I am including three rows.

CREATE TABLE shop (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,employees VARCHAR(20),
items VARCHAR(30));

2. Listing Tables

After creating the table, you have to see that table, whether it is created correctly or not. For that, you can call as listing tables. Use the below command in order to see your table.

SHOW TABLES;

Then you can see your table below

+------------------+
| Tables_in_stock |
+------------------+
| shop |
+------------------+
1 row in set (0.01 sec)

Then you can see the details of your table by running the command below:

show columns in shop;   

Then you can see the table which is created by you.

+-----------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| employees | varchar(20) | YES | | NULL | |

| items | varchar(30) | YES | | NULL | |

+-----------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

3. Inserting Content into the Table

You can insert ‘N’ no:of content into the table. For example, you can see three inserted content with the below commands:

INSERT INTO `shop` (`id`,`employees`,`items`) VALUES (NULL, "deepak", "bread");

INSERT INTO `shop` (`id`,`employees`,`items`) VALUES (NULL, "monica", "butter");

INSERT INTO `shop` (`id`,`employees`,`items`) VALUES (NULL, "bibin", "jam");

Now you can again see your table by typing :

SELECT * FROM shop;

So you can see the inserted contents in your table.

+----+-----------+--------+

| id | employees | items |

+----+-----------+--------+

| 1 | deepak | bread |

| 2 | monica | butter |

| 3 | bibin | jam |

+----+-----------+--------+

3 rows in set (0.00 sec)

3. Updating the Content into the Table

You can update the contents in the table whenever needed. Because in some situations, you have to change the content from the table. Then you have to do as below:

UPDATE `shop` SET `items` = 'sugar' WHERE `shop`.`employees` ='monica';

4. Deleting a row

You can delete a row using the below command:

DELETE from shop where items='jam';

Then you can check whether the row is deleted or not :

SELECT * FROM shop;

+----+-----------+-------+

| id | employees | items |

+----+-----------+-------+

| 1 | deepak | bread |

| 2 | monica | sugar |

+----+-----------+-------+

2 rows in set (0.00 sec)

5. Deleting a Column

You can delete a column using the below command:

ALTER TABLE shop DROP items;

Then you can check whether the column is deleted or not :

SELECT * FROM shop;
+----+-----------+

| id | employees |

+----+-----------+

| 1 | deepak |

| 2 | monica |

+----+-----------+

2 rows in set (0.00 sec)

User Management

The most important thing while creating and deleting database is you have to manage every user properly. That means you have to give the privileges according to each user privileges needed for accessing the particular database.

1. Creating User Accounts

You can create the user using the below commands.

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

2. Granting access for users on databases

Grant all the privileges to the new user.

GRANT ALL PRIVILEGES ON * . * TO 'username'@'localhost';

3. Granting remote access for users

Please refer this article for learning database remote access.

4. Listing users

For listing or showing all the users in MySQL.

select user,host from mysql.user;

so you can see the users that you created in your database.

+------------------+-----------+

| user             | host      |

+------------------+-----------+

| developer        | %         |

| root             | 127.0.0.1 |

| root             | ::1       |

| root             | database  |

| Joy              | localhost |

| debian-sys-maint | localhost |

| joy              | localhost |

| newuser          | localhost |

| root             | localhost |

| username         | localhost |

| wordpress        | localhost |

| wordpressuser    | localhost |

+------------------+-----------+

Backup and Recovery

This is the necessary command that every developer should know. Because the developers are creating and using n number of databases daily for their use. Sometimes some are more important and it should be secured safely. For that purpose, you can use the backup. And the other thing is you have to utilize the backup data later for another use. At that, you have to recover that particular database for the future use.

1. Backup a database

So for backing up the database you have to use the “mysqldump” utility. This utility will create a backup to a .sql file. Use the command as shown below from the shell (Linux shell and not the MySQL shell).

Note: change the username, password and database name accordingly.

mysqldump -u username -ppassword database-name > dumpfile.sql

Multiple Database Backup

If you want to take backup of multiple databases, use the following format.

mysqldump -u root -ppassword --databases database1 database2  > dbbackup.sql

Complete Databases Backup

If you want to take backup of all the databases on the server, use the following format.

mysqldump -u root -ppassword --all-databases > dbfullbackup.sql

2. Recovering a database

At times, you might want to recover the whole database. In this case, you can create a database under the same name and import all the previous data using the backup file.

To recover a database, use the following format.

mysql -u root -ppassword stock < stock.sql

Conclusion

In this guide, we have covered the most important commands you should remember as a developer to when you are working with MySQL databases. We hope you liked this article. Like and share it with your friends.