Beginner SQL Tutorial

Ghost

Seasoned Veteran
Blogger
Jun 25, 2009
3,202
187
213
Earth
wubur.com
FP$
2,497
SQL, or structured query language, should interest you if you plan on ever maintaining a large community or website. While free web software such as MyBB or paid software like XenForo won't make you go into the database, there may be a time when it's not a choice. Large forums often have to restructure the web software or database to make sure its optimized. For example, the largest MyBB site (HackForums) has had trouble because their traffic was overloading their servers. Every single 'search' for information (forum sections, topics, replies, member info, etc) uses a bit of resources on the server, and excessive requests can make a database run slowly. So, even if you do not want to set up databases for fun or for a living, I highly recommend at least having a basic knowledge of SQL.

In this tutorial we will only be focusing on the actual SQL commands. We will not be going over how to actually execute the SQL (with PHP for example) or display the database results on a page for a user. Perhaps I will cover that more in the future, but you can always watch my PHP & SQL tutorial on YouTube! That video will teach you how to use 'prepared statements' in PHP to INSERT, DELETE, UPDATE, and SELECT data in a database. We'll go over those concepts here, but only from the database perspective!

First let's go over what our database will actually hold.
For this tutorial, I have am using a basic spreadsheet to show you the information as the format will be the same across various database engines and UI management tools.



As you can see, we have a table titled 'content', with three unique columns (id, title, author).
In SQL, tables should have a column designated as being a "unique identifier". This column is our 'id' column. The uniquely identified column acts as a way for all of the data to be accessed efficiently, with a label / id that can be referenced elsewhere. For example, content ID 2 is 'Oranges' by 'Ghost'. I could have a separate table called "Featured" which stores the content IDs of all the posts I want shown on the frontpage. In this table I could simply have a column labeled "content_id" and put "2" there. That would make it easy to pull those featured posts when someone goes on the homepage. If you do want more information on this specific type of database design, you can always watch my video, How to Create & Set Up Relational Databases. I will however discuss relational tables below when we make our author column use an author id, and set up an author table to store the user information.

So let's start with our SQL SELECT statements!
A SELECT statement allows us to retrieve data from our table and it looks like this:
SELECT columns FROM tablename
That would not work if we just copied & pasted that because 'columns' is not a real column name in our table, and our table name is 'content' - not 'tablename'. So let's reword it!
SELECT title FROM content
If we did this, we would receive the following result:


We can see the "title" column, but nothing else because that is all we selected. Additionally, because we did not select our 'id' column (our unique identifier), our database manager will not allow us to delete or edit any of the content because it requires a unique identifier to properly target the data. This is not always vital for certain operations, but every table should have a unique id attached to each row even if you are not always selecting the id column itself... However, 99.99% of the time you will want to select the ID because of what you are doing with the data.

SELECT id, title, author FROM content
This SELECT query will fetch all of our columns!

I have reformatted our column names to make it more obvious that the columns have different settings. Our 'id' column is our unique identifier, so it's set to be a "PRIMARY KEY" column, which is unique. Additionally, I have set this column to "auto increment" so that it automatically goes +1 for each new row in the table. This is nice because it means I do not have to worry about inserting a unique ID whenever I insert a new row, which we'll get to soon.

In the above result, we selected the three columns, and saw all of them. There's another way we can retrieve the same results...

The * asterisk symbol allows us to basically say SELECT *ALL*
SELECT * FROM content
/ SELECT everything FROM the table content
/ SELECT id, title, author FROM content

But what if we want to only know the content created by Ghost?
Well, that's easy. We just use a WHERE statement.

SELECT * FROM content WHERE author = "Ghost"

As you can see, it only returned the content made by Ghost.
We could also do the following to find all content NOT made by Ghost:
SELECT * FROM content WHERE author != "Ghost"


As you can see, it was quite simple. We got Sally's content.

Well, now is the right time to mention that storing our author name in our content table is an awful idea. What if they change their username? If I don't want to be labeled as 'Ghost' then the database would have to find ALL of the places where it says 'Ghost' and update it... which is very inefficient. Instead we can have a table to store my username, and reference that table by an author_id - aka we'll have a relational database.



In those examples we do a few things... For one, the author column is now "author_id". We also have an official authors table to store the username/gender information. We then go ahead and pull some content by author ID instead of by name. We also requested a list of all authors who are female. And in the final example, I made a more complicated query that pulls the username information for every piece of content & shows it alongside the title, even though the username is now stored in a separate table. We won't go over JOIN queries in this tutorial, but I wanted to give you guys an example of how even though data is in a different table, it can still be used for content in another table.

Okay, so let's move on to the INSERT statement. INSERTS are easy and are formatted like this...
INSERT INTO tablename (column1, column2, column3) VALUES (value1, value2, value)

For example, to create a new author we could do...
INSERT INTO authors (username, gender) VALUES ("Phil", "male")

Or for our content, we could do this:
INSERT INTO content (title, author_id) VALUES ("Carrot", 2)
That would add a new piece of content called 'Carrot' by Sally (author_id 2)



Good, we created some new content and an author.
INSERTS are easy, let's try an UPDATE!

UPDATE queries allow us to modify existing information, and look like this...
UPDATE tablename SET column = "value"
That would update every single row's column to equal that value. 99.99% of the time you will be using a WHERE statement for an UPDATE query.
UPDATE authors SET username = "William" WHERE id = 1
That would be a query to change author id 1's username from Ghost to William.

First, I want to replace the words "male" and "female" with integers. I am going to use 0 for male, and 1 for female. This is because I do not want to repeat the words "male" or "female" dozens of times. It takes up extra space and can be more easily searched for with small single digits. I could even add a new digit like "3" to equal something like "Not Disclosed".

I also want to show you an UPDATE query that could be used if a piece of content (identified by ID) was edited by the author at a later date. Let's pretend that Sally is updating her Carrot article...



As you can see, we ran two different update queries to update our gender column, and one update query for the content title! The results changed, and we can go on with our day.

Just to remind you, if you do not use a WHERE statement in your update query, you will update EVERYTHING in that table! That can be very bad if not intended!

Now let's delete some data!
Deleting is simple too...
DELETE FROM tablename WHERE column = "value"

Remember to use WHERE because you could delete EVERYTHING in the table just by doing...
DELETE FROM tablename

For this, we'll pretend that an author has been banned and we want to remove all of their content.
You can read the notes in the image to see some examples of our process in the database.

As you can see, we were able to successfully mark Laura as banned, delete her content, and then delete her profile when we changed our mind later! Very simple.

That's all there is to it with beginner SQL.
You have your INSERT...
INSERT INTO tablename (columns, go, here) VALUES ("values", "go", "here")

Your SELECT...
SELECT column, columnTwo FROM tablename WHERE column = "value"
SELECT * FROM tablename

You can UPDATE...
UPDATE tablename SET column = "valueYouWant" WHERE columnTwo = "valueYOuLOOKfor"

and you can DELETE!
DELETE FROM tablename WHERE column = "val"

You are now on your way to being able to create or manage your own databases. If you can never imagine yourself doing this, you might be surprised. You may have to alter the way your database works, like the large MyBB forum HackForums has to do. You might need to run some queries on your data to discover cool trends or market data. There's so many good reasons to know SQL (structured query language), especially with data becoming more and more valuable over time. Even if you don't create scripts with PHP to insert/select/update/delete data, it's an impressive skill that can help you in almost any job that uses a software to manage their customer / employee / company data. You never know, maybe you will be happy you know it!

Thank you for reading my beginner SQL tutorial.
I hope it was informative and helpful :)
 

Ghost

Seasoned Veteran
Blogger
Jun 25, 2009
3,202
187
213
Earth
wubur.com
FP$
2,497
why not post it as an blog or open your own blog up with that!!
Hey Empire :)
That's a good point. I will be contributing on our new blog at Wubur (our company) as well as probably using my domain Nerdi.org to post some tutorials, resources and open source scripts that I create. I just wanted to create some tutorials here at FP to hopefully teach some members some interesting things & maybe even bring in some traffic from GOogle :)
 

Malcolmjr96

Forum Technician
Forum Technician
Apr 17, 2016
1,366
480
143
23
Canada
FP$
1,767
why not post it as an blog or open your own blog up with that!!
Hey Empire :)
That's a good point. I will be contributing on our new blog at Wubur (our company) as well as probably using my domain Nerdi.org to post some tutorials, resources and open source scripts that I create. I just wanted to create some tutorials here at FP to hopefully teach some members some interesting things & maybe even bring in some traffic from GOogle :)
That's awesome! Your tutorials are amazing! If you're interested in posting more tutorials please PM me
 
D

Deleted member 44219

Guest
Hey, @Ghost. Do you plan on doing a Tutorial for People who want to learn HTML. If not, I can do it for you.
 

Ghost

Seasoned Veteran
Blogger
Jun 25, 2009
3,202
187
213
Earth
wubur.com
FP$
2,497
Hey, @Ghost. Do you plan on doing a Tutorial for People who want to learn HTML. If not, I can do it for you.
I have thought about it, yes. I think I would rather start off by doing a Beginner CSS Tutorial to talk about some simple designs, like color, background, padding, margins, etc.

I will most likely create a Basic HTML/CSS tutorial soon. It'll be pretty simple and will focus on things like creating buttons, little alert boxes, and a few other elements...

That's awesome! Your tutorials are amazing! If you're interested in posting more tutorials please PM me
Hey Malcolm, yes I will probably share a few more tutorials :)