Understanding the basic concepts of CRUD in SQL
7 min read
Hi, welcome back. In this post, we are going to understand the basic concepts of SQL query and why it is very crucial for developers. Every developer at some point of time in his developer journey might have come across SQL. But before getting started, I'd like to explain what is SQL.
What is SQL?
SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a Relational Database.
It is the standard language for Relational Database System.
CRUD stands for
Let's start with the
CREATE statement in SQL.
One more thing. If you want to try these queries, you can try them online and you don't have to configure the database locally. Here is the link to an online database.
SQL Online IDE - for Data Science
The create statement can be used for both Database and Table creation. Let's see the database creation.
We'll start with the query for database creation.
CREATE DATABASE ecommerce;
This query will help you in creating a database named ecommerce.
Now, let's try to create a table with the
If you don't know what a table is, read more about it here. Table (database) - Wikipedia)
For creating a table in the database, we need to pass column names with their data types in the query.
Here I'll be creating a
CREATE TABLE users ( id INT NOT NULL, name STRING, PRIMARY KEY (id) );
The above query will create a table named
users and, it will have columns named
To identify every row uniquely, we have
idand, we increment them automatically and we'll also set them as
PRIMARY KEYand, it should be
Now we are done with the creating database and table we can now try to store some data in the users table.
To insert data in the table, we have the
INSERT INTO users VALUES (1, "Pratik Sah");
It will add a new row in the users table and the values will be stored in the respective columns or fields.
Try inserting some rows on your own.
We already have stored some useful data in the table, time to retrieve them.
For reading, we have the
Suppose we want to access
name column from the table, we'll write:
SELECT name FROM users;
If we want to select everything from the table, we use
* for that.
SELECT * FROM users;
Suppose we want to read only those data which satisfies a particular condition, then we'll use
WHERE in our query.
SELECT * FROM users WHERE name = "Pratik";
Here, we'll get those results only which have
name equals to
Pratik in the table
We can also use id for querying a particular row from the table. For example,
SELECT name FROM users WHERE id=1 and it will return us the name of the row where the id will be 1.
We now have our data stored in our table. We may want to change the name of the user or maybe the user wants to change the email. In that case, we can use the update statement.
UPDATE users SET name='Pratik' WHERE id=1;
The above statement will help us to update the name of the user with id equals to 1. We can use this statement for making any changes stored in the table.
Sometimes what happens is, suppose a user wants to delete his account from our portal. We may want to delete his record from our users table and to that, we use the
The delete statement helps us in deleting the data from our table.
DELETE FROM users WHERE id=1;
Now, this will delete the entire row from the users table where id will be 1.
NOTE: Don't forget to use the
WHERE or else it will delete the entire row from the table.
Sometimes we want to add a completely new column in our table. Now, in that case, we have to
ALTER our table.
ALTER is used to add, delete or modify columns in an existing table.
Suppose we want to add a new field in our users table where we want to store the age of user. We'll add a new column using the
ALTER TABLE users ADD age INT;
Now, this command will add a new column named age with the data type
INT in our
Well, this was all for today's post and if you found it helpful, please leave a like, share this post with your friends & comment below if you have any doubts.
Thanks for your time and I'll meet you in my next post. Take care and happy coding🙂!
Did you find this article valuable?
Support Pratik Sah by becoming a sponsor. Any amount is appreciated!