Useful SQL Commands

As a backend developer, I started with MongoDB as a database, but later working with clients

As a backend developer, I started with MongoDB as a database, but later working with clients, I always have to work with SQL, postgreSQL and MariaDB. And, because of these industry demands, I had to learn the SQL commands, that I am sharing some of them and most useful commands.

SHOW DATABASES;

If you are running the sql in the Command Line Interface (CLI), then its necessary to check that what are the databases that you have, and to list down all the databases, you have to useSHOW DATABASES;command. You can notice, that there is a trailing semicolon at the end, which indicates the end of the SQL command.

CREATE DATABASE [database_name];

If you want to create your own database, then you should use theCREATE DATABASEcommand, followed by the database name that you want to create.

USE [database_name];

After listing down all the databases, you have to choose a database to work with. So, you can move further with creating tables, and perform actions with the data in that table. And, for that, you have to use the commandUSEfollowed by the database name.

SHOW TABLES;

After creating the database and selecting it, to perform your actions, you have to check that is there any table inside it, and for that we useSHOW TABLES;command.

As, we just created the Database, we dont have any table, inside it.

CREATE TABLE ([entities]);

With this command, you are able to create table inside the selected db, and able to define the structure of it too.

As, you can see in the above table, you should useCREATE TABLEcommand followed by the structure of the table.

In that structure, we are adding id, name, age in the table, and their datatype has been defined after giving an space (int, varchar), which is followed by the length of it as 10 and 200.

Please note, that every datatype, has its fixed length.

INSERT INTO [table_name] VALUES [table_content];

After creating the table, we should be adding data inside it, and for that we use the above command. Under table name alias, we have to give the name of the table, in which we want to insert the data and under table_content, we have to add the data, that we want to insert.

SELECT * FROM [table_name];

After creating the table and adding the data into the table, you want to check all the data that you added inside the table. then you have to use the above command.

In the SQL command,*is being used as all. so, SELECT * means that select all, in the above command.

SELECT * FROM [table_name] WHERE [alias];

So, you adeed multiple users in the users table, and now you want to check the data of a specific id or of a specifc age, then you should use WHERE command to filter out to the data.

As you can see in the above image, we are filtering out the users table data for age=10.

AND

Consider the situation where you want to filter out the data for age=10 and id=2, then how are you going to combine both the filter in the same command to get the specific data.

To combine the command or alias, we use AND command.

As, you can see in the above image, we are searchig for the data with age=10 and id=1.

OR

If you want to filter out the data in a way, that either a data has age=10 or id=1, then in this case, we use OR.

We are getting those data from the users table, which either has age=10 or id=2, If any of this case satisies then the data will be shown.

UPDATE

We already saw, the view and create command. And, to complete the CRUD operations, we are moving towards UPDATE command. This command, is being used to UPDATE the data in the created table.

As, you can see, that we are updating the name in the users table, for a column whose id=1.

ORDER BY

If you want to sort the data in the table, then you should use ORDER BY command followed by the data according to which you want to sort the table's data.

As you can see, that we are sorting the data according to name.

Please note, that ORDER BY, is using ascending order to sort by default. But, if you want to sort in descending order, then you have to use DESC command after the ORDER BY command.

DELETE

If you want to delete a data from the users table, then you should use above command, followed by table name and where condition.

I hope you like the article and learn from it. It will be good to give me a feedback.