Well first and most importantly you need to know what exactly SQL is and what how is it useful. SQL is sort of programming language designed to manage data that is in RDBMS
RDBMS relational database management system is a system designed to retrieve, extract and insert information into database. While doing so it's interacting with database. mySQL is one of the examples.
Database is a collection of organized information, for example, name, last_name, email etc.. You're accessing it using relational database management system(RDBMS) Here is a list most popular DB engines.
Our data is stored in tables, this is how it would look like. Table is just a set of columns and rows.
Column is often referred as field, in picture above there is 4 fields, titel, interpret, jahr and id. And row is also called record.
You may ask, alright now how can this be useful to me? Well it can be pretty damn useful. Lets say you have started working on a site index.php is a page for regular users/guest and premium.php is for VIP members only.. how would you distinguish VIP members from regular ones? Well you can do so by asking them to signup, you would store their username and password in the database.. you could write a registration system, which would use an extension(sqli, pdo etc) to connect to database and then use SQL queries to perform tasks.. you could insert their info, retrieve info to check if user of that name already exists etc.. check if user is VIP when user is logging in.
Let's say we are writing a login system we need to check if information user send us using POST(more about this here) is correct or not. This is what we do.
SELECT username, password FROM table_name WHERE username = $username and password = $password
We are essentially saying SELECT the username and password from the table table_name WHERE username is equal to $username variable AND password is equal to $password variable's value. $password and $username contains the value we received from user.
Lets talk a little more about table because this important.. usually you'd want to create different tables for different kind of information, you don't want to fill a single table with every single piece of info you have. Suppose you are writing a registration system, you would store, username, password, and email in one table and recovery questions in another.. I know this is getting confusing already, here's an example.
Table users:
username|password|email
jon 123456 jon@gmail.com
brown 3643554 brown@gmail.com
Table recovery:
Hm... we need a way to relate recovery questions to each user don't we? if we didn't do it how would we know which question belongs to user.. you could say why not just write username in front of the question.. well we could very well do it.. but then we aren't separating the information or if we're doing it in different table we're repeating information which we don't want. So solve this problem we just add an id field to our users table, whenever we insert anything using INSERT keyword INTO users table we also give it an id which increments automatically.. So changing a bit this is how our users table would look like this time.
Table users:
id|username |password|email
1 jon 123456 jon@gmail.com
2 brown 3643554 brown@gmail.com
Now when we're inserting data in users table we also insert data into recovery table.
Table users:
id | recovery_question | recovery_answer
1 Where were you born? usa
2 Your favorite food? rice chicken
We now know that id 1 refers to jon and 2 to brown. We're creating relations here.
You also need to know that in SQL database there's certain data types. If you're storing a name you'd need to give it data type text, or varchar.. for number you'd need to give it integer. The list can be found here.
To finish this off, here's a few examples that will give you a better understand
No comments:
Post a Comment