Intro to SQL Part C

While the last two posts focused heavily on theory and fundamentals of SQL it's time to start on the actual usage. There's a ton of good tutorials out there that skip right to the code, but as a young developer, I wasn't convinced that SQL was a worthwhile tool until I was forced to use it. I wanted to spend a good amount of time discussing the basics before jumping into some examples. If you're looking for more advanced and specific uses, I'd highly recommend checking out w3schools SQL tutorials.

As I introduced in my last post, an SQL table is a collection of unique rows. There are two primary things that you do with the data in a table - read and write. With the proper syntax, a hacker can write to your database, injecting into or deleting your data, so it's important to restrict the front end privileges and validate. That's a subject for a later post, though. Let's look at how you can read a table.

Reading from a table is also known as a 'select' statement, since you're selecting entire rows of information. You can restrict how many rows you select, pulling single rows or entire tables, and you can also restrict what fields you select out of the rows. A select statement has several important clauses: what is being selected, where the data is coming from, what filters are being applies, and how to group or order the result.

example 1: SELECT * FROM blogs
example 2: SELECT body FROM blogs WHERE title='Intro to SQL Part C'
example 3: SELECT title,body,date FROM blogs ORDER BY date DESC LIMIT 1

The first example, with an asterisk, selects everything from the table 'blogs'. This will pull an entire table, which may become a bad idea as the table grows in size. The second example will pull a single field, called 'body' from the blogs table with a specific title. Note that this will pull a single row if there is only one blog with that title. If I have two blogs with the same title (which is very unlikely), then this query will actually pull two rows. The third query is a bit tricky, but it will pull information from the latest blog. It sorts the entire table by date, with the topmost, or most recent, date on top, then plucks out the top one with the limit clause.

Writing to a table involves several different commands. The three most common write queries are 'insert','update', and 'delete', which do exactly what they state. A tricky thing about updates - you can update a row to be null, effectively deleting it. Also, if you're not careful with the where clause on the update or delete, you may end up overwriting or deleting an entire table.

example 1: INSERT INTO blogs VALUES ('Intro to SQL Part C','A pretty basic introduction.','Today')
example 2: INSERT INTO blogs ('title') VALUES ('Intro to SQL Part D')
example 3: UPDATE blogs SET body='' WHERE title='Intro to SQL Part C'
example 4: DELETE FROM blogs WHERE title='Intro to SQL Part C'

Write statements are structured a bit different then reads, defining the table first and the actions second. Let's assume that the blogs table only has three fields: title, body, and date. The first example will insert a brand new row at the bottom of the table with a one sentence body. If the table had more fields, this statement would fail, because an unrestricted insert needs to have a value for all fields. The second example handles this problem by defining both the table and the field that has a new value. The other two fields, body and date, will be null for this row (depending on your table structure). The third example will wipe out body of the post with a specific title, while the fourth one deletes the entire row. If you do not specify a where clause for the last two examples, these statements would wipe the entire database.

There's a lot more you can do with SQL, including joins, math, grouping, etc. However, that's outside the scope of this post. In the next post I'll go over an example class implementation of PHP using SQL connections to read and write to a database. Stay tuned!