Database manipulation - Basic SQL

[zkiller]

well, what can i say, i am bored as usuall, so i figured instead sitting in front of my PS2 playing gran turismo 3 i would try and make some good use of my time.

So, sooner or later in your journey through web development you are going to want to learn how to make use of databases. Be it to store information or to get content from, so will need to learn at least the basics of SQL.

What is SQL you ask?

SQL = Structured Query Language

SQL is the language of the databases.

Ok, there are four basic types of SQL querys. Select, Insert, Update and Delete. We will cover the Select query first, since this is the one you will need the most.

Let's say you have the following table called "example" in your database...

id | cell1 | cell2 | cell3 | cell4
1 | joe | Dow | male | web dev

so you decide you want or need to pull the content of cell1 and cell2 from the database to be displayed on your web page. The SQL for this would look as follows...

"SELECT cell1, cell2 FROM example"

pretty simple isn't it. now, let's imagine you had more than one record in your database you wanted to order the data that is being pulled from the database by the last name ascending, which in this case is in cell2. you would go about this as follows...

"SELECT cell1, cell2 FROM example ORDER BY cell2 ASC"

descending is very simular...

"SELECT cell1, cell2 FROM example ORDER BY cell2 DESC"

now, what if the user messed up when he signed up at your site. he is actually a she for instance. well, you would need to run an update query to correct the false information. this would look as follows...

"UPDATE exemple (cell1, cell3) VALUES ('varible1', 'variable2') WHERE cell1 =" & idvariable

in the first set of bracket you put the cell names you want to update. in the second set you need to put the names of the variables containing the updated user information. be carefull to put the variables in the right order, or you might end up having the wrong information in the wrong cells. also, if the variable contains text, you will need to put " ' " around it, if it contains an integer, these can be left out however. last, but not least, you will need to tell the database which record it is that is being updated, since you don't want it to update every record in the database to be identicle.

both insert and delete querys are identicle to update querys. just that the word update is exchanged with the either "insert into" (to create a new record) or "delete" (to delete a record).

that's pretty much all you need to know SQL wise for the most commen uses. i hope the bit about the update query wasn't to confusing.

if you have any questions, feel free to ask them here. good luck! :thup: