7 Commands You Should Know in MySQL

4 min readApr 21, 2020

When we plan for the project, the first thing that comes in our mind is the database. Most of the developers use MySQL to store the data because it’s easy to use and open source :). We have many things to learn in MySQL but it depends on our project requirement, So here I’m sharing some common things that help you to improve your knowledge.

Let’s start theses commands:-

User Management: We have a team to make the project but every team member has a different role so we can’t give root user access to everyone. So In MySQL, we can create a user with specific permissions. Here I’m creating a user with read-only permission:

1. create user 'pragyn'@'%' identified by 'Pragyn123!';
2. grant select on *.* to 'pragyn'@'%';
3. flush privileges;

Here point one ‘pragyn’ is the username and ‘%’ is host, which means he can access from outside the server also. if we want to restrict from outside then we’ve to write ‘localhost’. Point two grant is the keyword and select is the access role, we can give select, alter and update, etc. if we give all types of permission then we’ve to write “all”. After on keyword here “*.*” meaning is every database with all tables he can access. Point Three means to restart the permissions, here we don’t need to restart the MySQL.

2. Temporary Variable: Suppose we’ve got the result from the query and we want to show another column with an auto-increment number in the result. So here we have an option to use a temporary variable like this:

Before Query:

After Temp Variable Query:

3. Vertically result: When we want to see many columns in a query then it is a problem to read the data because the default result is showing in a horizontal view.

Default View:

Vertical View:

4. IF Condition: Sometimes we’ve to show the result as per condition I mean in database value to the readable format like we store 0 or 1 value in “active” column but when we give the data to another person that should be like “ACTIVE” or “INACTIVE”. So this case we’ve to write the query like this:

Example: 
select name, (case when active=1 then 'ACTIVE' else 'INACTIVE' end) status from user;

We can write multiple cases with when statements.

5. Use Time Difference: We have multiple types of conditions to get the result so here one condition that comes “give the data of users that registered in last 5 minutes”. For these type of condition we have the function:

TIMESTAMPDIFF(<unit>, <date>, <comparison_date>)

The first parameter is unit we can get the difference from SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. So the query will look like this for Minute difference:

select * from user where TIMESTAMPDIFF(MINUTE, created, now()) <= 5

6. Create table from existing table with records: Yes, we can directly make the table with existing table records. Here is the syntax:

create table <new-table-name> as (select * from <existing-table> where ....)

7. Explain Command: If we want to check the stats behind the execution of the query then we can get these details using explain command. this command gives you the data that helps you to check the performance and how many rows are affected etc.