Skip navigation

Alright, so lately at work I’ve been up to my eyeballs in SQL.  More specifically MSSQL, but SQL none the less.  So, I wanted to take some time to discuss it and some basic stuff that, even home users, might find useful.  Considering this is a wordpress blog and it uses mysql to store it’s post and configuration data, I’ll use mySQL examples for my code snippets.  Now let’s get started by creating our database.

create database temp;

 

Now let’s create a table for users.

create table temp.test_users
(User_ID0 char(50),
last_name char(50),
first_name char(50),
location char(50));

 

Let’s stop and break this down for a second.  We just created our first database and named it temp.  Now inside of that database we’ve just created a table with 4 columns with a limit of 50 chars per entry.

So what is that exactly? 

Well, a database is just a logical grouping of data.  One easy way to think of a database is to think of a spreadsheet.  We’ll use excel as our example.

The database is your workbook, and a table is a worksheet.

So we’ve got a table now, lets insert some data to play with in there.

insert into temp.test_users
(User_id0,last_name,first_name,location)
VALUES
('dbel','Belcher','Daniel','Birmingham, AL');
insert into temp.test_users
(User_id0,last_name,first_name,location)
VALUES
('drich','Rich','Derrek','Tampa, FL');
insert into temp.test_users
(User_id0,last_name,first_name,location)
VALUES
('ageo','George','Amanda','Birmingham, AL');

 

Lets see how that data looks?

select * from temp.test_users;

 

+----------+-----------+------------+----------------+

| User_ID0 | last_name | first_name | location       |

+----------+-----------+------------+----------------+

| dbel     | belcher   | daniel     | birmingham, al |

| drich    | rich      | derrek     | tampa, fl      |

| ageo     | george    | amanda     | birmingham, al |

+----------+-----------+------------+----------------+

 

Hmm, it would be great if we had a date stamp along with this data for a creation data footer.

alter table temp.test_users
add date0 char(50);

 

Now, how are we going to update these entries?  I mean we could

delete from temp.test_users;

 

And recreate all those previous insertions with a new date column.  But that just isn’t efficient, especially if we had an even larger table than this.  It would be nice if we could look through our selects and just append that data.  But wait, we can…

update temp.test_users
SET date0 = CURDATE()
where date0 is null;

 

Pretty neat huh?  Well, maybe, let’s see how our data looks now.

select * from temp.test_users;

 

+----------+-----------+------------+----------------+------------+

| User_ID0 | last_name | first_name | location       | date0      |

+----------+-----------+------------+----------------+------------+

| dbel     | belcher   | daniel     | birmingham, al | 2011-08-31 |

| drich    | rich      | derrek     | tampa, fl      | 2011-08-31 |

| ageo     | george    | amanda     | birmingham, al | 2011-08-31 |

+----------+-----------+------------+----------------+------------+

Alright, now there goes the basics of building, updating, and viewing a (my)SQL database and table.  Really simple huh?  I’ll try and post some slightly more advanced stuff in the next week.  Like joining tables, and creating subqueries.

I guess until then you can RTFM (read the flippin manual).

2 Comments

  1. Wow something that I understand! I like the basics. I think my problem is that I tend to just dive in and try to figure things out. sometimes it really is better to just walk in through the shallow end.

    Thanks again for the help this week.

    BTW.. could you add a slight padding on the left and right so the text doesn’t bump the edge? It’s driving my OCD crazy. Thanks.

    • Yea, crawl before you can walk. The thing about SQL is that it’s actually really really really simple to use. It’s just not very forgiving on bad syntax. When you understand what everything is you are manipulating it makes life easier to deal with them. So the transition from beginner to expert really isn’t that large of one. Now expert to guru, that’s a different ballgame. However, start small, build some tables, add some content, modify that content, query that content and get comfortable. I’ll come back to this topic again for sure to discuss slightly more advanced query techniques, and explain the syntax a bit.

      Also, will do on the css change. I needed to put a backing on my comments too. Did the form, but not the actual comments 🙁

      What browser are you using btw?


Leave a Reply