DEV Community

Cover image for Useful SQL Commands
Manas Mishra
Manas Mishra

Posted on

Useful SQL Commands

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 use SHOW DATABASES; command. You can notice, that there is a trailing semicolon at the end, which indicates the end of the SQL command.

SHOW DATABASES

CREATE DATABASE [database_name];

If you want to create your own database, then you should use the CREATE DATABASE command, followed by the database name that you want to create.

Image description

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 command USE followed by the database name.

Image description

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 use SHOW TABLES; command.

Image description

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.

Image description

As, you can see in the above table, you should use CREATE TABLE command 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.

Image description

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.

Image description

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.

Image description

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.

Image description

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.

Image description

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.

Image description

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.

Image description

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.

Image description

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.

Image description


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

Top comments (0)