|A typical table|
The most common statement you'll use is the SELECT statement, which (surprise, surprise) selects data from a table or tables. The basic format of the select statement is
[columns]is a comma separated list of which columns you want to pull. For the sake of example, I'm going to be using a hypothetical set of tables that Blizzard might user for their games, since that will make the things we're talking about as common as possible to the typical audience of this blog. The first table we'll talk about will be
logins, which contains data about which users logged into which Blizzard games at what time. So it might have 3 columns
|| ||| |
|1/1/2013||| 1||| 1234567|
|1/5/2013||| 3||| 3456789|
|1/7/2013||| 2||| 7636857|
This table has three columns, recording who (
user_id), logged into which game (
game_id) on which date (
date). To select all the data in this table, you would code
But you'll probably never need to select all the data in a table. You might only be concerned with all users who logged into WoW (let's call that
game_id=1) on 1/1/2013. To do this, you would code
game_id=1 AND date='1/1/2013';
I omitted the
game_idcolumns since we already know what they are based on how we selected the data. They could be left in (and in some cases should).
I lied about how I would set up the
loginstable. I would instead set up the columns as such:
logins- tracks players logging into games
login_time- not just the date but also the time
game_id- id for the game
user_id- id for the user
acct_id- id for the acct they log into
login_timehave the date and the time, we can better track when people log in and how many times a day they log in. By adding
acct_idyou can keep track of which account for that game they logged in to. That query would become
game_id=1 AND login_time::date='1/1/2013';
Question: How would you select when and what games user 2435649 logged into during November?
If you have any questions, feel free to leave them in the comments or to email me. My email can be found on my About page.
login_time::datetakes the timestamp from the
login_timefield and removes the time portion, giving you just the date so that the database can accurately tell if it's equal to '1/1/2013'. The way this is done will vary from database to database, but a similar solution should exist for all. Not all databases will necessarily read '1/1/2013' and accept it as a date and may have you do it differently, but for the sake of this, and all further examples, I'll keep doing things this way.
Note: If you're going to select all of the columns in a table you can use
*instead of typing all of the column's names. To select all logins for a particular user (1234567) in December you would code
AND login_time::date BETWEEN '12/1/2012' AND '12/31/2012';
Note: The way I've done the spacing and decided where new lines go isn't mandatory and I just do it for easy reading.