Intro
In this blog I intend to teach you the basics of SQL and what’s the fun part? Unlike other tutorials where they just give you theory, I will show you how you can practice your skills in real time and become better 💪
But you might ask why in the world should I use XAMPP? There is a reason for it too. It kinda helps you learn SQL, if you use it the right way
Let me show you..
Let’s start
For this writeup we will be using XAMPP, which is a awesome software which is open-source(aka totally free 😁). I have used it and I think it is pretty cool and it also gives a great user interface. Install XAMPP from: https://www.apachefriends.org/index.html
It’s pretty easy, but if you face any problems let me know in the comments or write a personal note to me using Medium’s feature. I might an extra installation step for XAMPP here.
Now let’s launch XAMPP
Here we have to select start buttons for MySQL on the interface and then Apache(to get a nice web user interface on our localhost).
Then the admin button will become clickable. Click on it to open the MySQL admin:
Let’s get used to the admin panel:
Click on SQL button to get to the command window.
Let’s begin by creating a database. The command to create a database in MySQL is:
CREATE database <db-name>;
(Note: MySQL is not case -sensitive)
Click on Go button to continue. The database is created. Here the database name is h4ck3r
To mention which database I’m using I’ll be using:
use <db-name>;
Not let’s create a table inside this database (Tables are the main constituent of databases). Relevant command for the purpose:
create table records(
column1 type,
column2 type,
.
.
.
column[n] type
);
Let’s now use one of the features of XAMPP to make it easier for you to learn the basics! 😀
Let’s begin:
Click on the table on the left panel. Remember, we named our table ‘h4ck3r’ so that’s what we’re gonna click here:
Alright!, now let’s select our table: records as we named ours:
Now, select the SQL tab, to where we will type in the commands:
Now, as I said this would be helpful for you to learn SQL, let’s introduce to XAMPP’s features:
As you can see, clicking the options as indicated in the red boxes would autocomplete the commands. Try clicking SELECT * options. The command you can see:
SELECT * FROM `records` WHERE 1;
Let’s break down the command. SELECT * selects everything from the table, FROM ` tablename ` selects which table to print out stuff from. WHERE is used to select any particular condition; we will discuss that a little later, after we INSERT some data into the table and then try to view it again.
Click INSERT from the options below. We see the following command pop on our screen:
INSERT INTO `records`(`roll`, `name`, `Age`) VALUES (‘[value-1]’,’[value-2]’,’[value-3]’);
Quite long? 😨
Nothing to worry, let me explain it to you:INSERT INTO ` records ` inserts stuff into the table ‘records’;(`roll`, `name`, `Age`) indicates the column names under which we intend to insert our data.VALUES (‘[value-1]’,’[value-2]’,’[value-3]’) indicate the values we are gonna insert. Lets change the values to what we want to insert into the table:
INSERT INTO `records`(`roll`, `name`, `Age`) VALUES (‘1’,’h4krG33k’,’20’);
Press on Go
We have successfully inserted data into our table! Let’s add a few more data into the table in order to demonstrate the other SQL commands better:
INSERT INTO `records`(`roll`, `name`, `Age`) VALUES (‘2’,’h4kr’,’13');
INSERT INTO `records`(`roll`, `name`, `Age`) VALUES (‘3’,’naruto’,’16');
INSERT INTO `records`(`roll`, `name`, `Age`) VALUES (‘4’,’pikacku’,’16');
INSERT INTO `records`(`roll`, `name`, `Age`) VALUES (‘5’,’h4kr’,’15');
If you can notice there are two rows with same name ‘h4kr’ and same age. I have added them on purpose. I will use them to demonstrate the SQL commands better. Besides people with same name do exist right? 🤷
Now click on SELECT instead of SELECT *, you might ask where’s the difference. SELECT * selects very much everything in the table whereas SELECT can be used to print out data we specifically want from the table. This will come in handy in case there are tons of entries in a table, and you need some specific ones. Let’s got for it:
SELECT `roll`, `name`, `Age` FROM `records` WHERE 1;
This is the default command which would appear on your screen. SELECT is followed by the column names we want to display. If you don’t want something to be printed out then omit that column name. I will show in the following example. FROM is for selecting which Table we want to display our data from. Now here as promised before, I would explain the role of WHERE here. WHERE is used to place conditions as to which data we want to display.
In the following example I would use the WHERE condition.
SELECT `roll`, `name`, `Age` FROM `records` WHERE `roll`=1;
in this case, the entry in the table with roll section having 1 would be returned.
As you can see the entry with roll 1 is returned.
Oof.. That’s quite a lot right? It’s okay if you don’t get it all at once, neither did I get when I started 😅; partly because the person teaching us, taught everything in theory and never really implemented a way so that we could actually practice it. That’s the very reason I though this way might be actually beneficial to you guys. Let’s keep going 🏎️
Let’s try the same command but this time, let’s use the UNION command. This will come in extremely handy when you will be performing SQL injection attacks.
SELECT `roll`, `name`, `Age` FROM `records` WHERE `name`=‘naruto’ UNION SELECT 1,2,3;
So, basically command appends something to the bottom of the table in out case it’s 1,2,3
We can replace them with columns from other tables too & also display important info like database name, current user, etc which are valuable info to a hacker when performing injection attacks..
To demonstrate I’ve created another table named hacked which contains 3 columns. Let’s try the following command:
SELECT * FROM `records` UNION SELECT * FROM `hacked`;
It’s a good time to see what you have learnt & put it to the test. Try creating a table with 3 columns and try using UNION command to concatenate the two tables. You can also spice it up adding conditions with WHERE command.
Now, what if we have to do the same with a table which got one column less? I created a tabled named hack_table with only 2 columns.
It’s throws an error in case we try to use the previous command (Try it out for yourself and see if the error pops or not 😉).
So we need to improvise. Let’s add a NULL column or a blank entry into the missing column part:
SELECT * FROM`records` UNION SELECT *,NULL FROM `hack_table`;
This command executes without any errors and we can see a NULL column in the third column.
So, let’s try to retrieve info about the current database and current user by using the database(), user() in place of NULL.
SELECT * FROM`records` UNION SELECT *,database() FROM `hack_table`;
As we can see the db name is outputed.
Similarly you can try user(), version(), etc
Google up & you can find a lot of other options..
Let’s now have a quick look at UPDATE & DELETE.
UPDATE allows us to change values of any entry in a table.
I’ll change the the entry h4krG33k (the first entry name) to hacker_cat
the command to do so:
UPDATE `records` SET `name`=’hacker_cat’ WHERE `name`=’h4krG33k’
By default, the other parameters are also present in the command, but since I want to change only the name in the entry I omit the others.
DELETE allows us to delete a row we don’t wish to keep. Let’s delete the first entry. Command I use for this purpose is:
DELETE FROM `records` WHERE `roll`=1;
As you can see the first entry is no where to be seen.
Now, let’s have a look at a few other commands which might come in handy.
desc will allow you to view datatypes of the table entries
desc records;
Now you might say, once a table is created is there no way on earth to add a column? Like I added a million entries to a table and wanna add another column I gotta do it all over again? 😭
Fortunately, no. You don’t have to. We have ALTER for this very purpose.
Let’s try to add another column profession in the records table. Use the following command to do so:
ALTER TABLE records ADD profession varchar(100);
There you go, we have successfully added another column! We can add stuff to these columns too.
We can drop/delete columns with ALTER too. Let’s remove the roll column.
ALTER TABLE records DROP column roll;
As you can see profession is added and roll is removed.
ALTER can be used to modify the datatype of a column.
ALTER TABLE records MODIFY COLUMN age varchar(100);
We have successfully changed the datatype of roll from int to varchar
Since this writeup is already as lengthy as it is, I think it’s a good idea to stop here and maybe continue this tutorial in a part 2.. 🤓
If you have any doubts you can ask here in the comments itself! I would be happy to help 😊
And don’t forget to follow me as I have more cool tutorials coming up 😃
See you later 👋
