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).