I think the answer to my question is so simple that there's not even an answer to my question lol:
How does the concept of User Authentication/User Accounts work? How does a certain webpage, for example, know to pull up your information and not someone else's when one logs in? Is it really just a bunch of select statetments with a where clause on the userid to pull back info?
It depends on the underlying technology used to create the website. Usually there's a cookie stored in your browser once you log in that uniquely identifies you. Then when you load a page, the site checks the value of the cookie to see how you are and loads information appropriate to you from a database.
As an example, when you log in to Facebook it creates a cookie on your computer. Then when you go to your homepage it knows who you are based on that cookie and uses it to load your profile picture, your friends, your apps, etc.
No switch statements, though. :O
When you connect to a website, a session cookie is placed in your browser. This uniquely identifies you so that the website knows from request to request, page to page, that you are the same person. Somewhere on the server, the ID in this session cookie is stored. The server knows you are there. The server knows when you click on a link that you're the same person who generated the page on which the link was present.
When you log in, the programmer authenticates your username and password against the database (or whatever he uses for user authentication), and then stores some sort of User ID on the server, attached to your session ID from your cookie. Now, whenever you request a page, the programmer checks to see if there's a User ID associated with your session ID on the server, and then knows that you're already logged in. It's common at this point, the first thing when you log in, for there to be a bunch of select statements to load your user inforamtion, any new messages, etc. This way, it can display at the top of the page.
For example, on StackOverflow, this would be your name, reputation, amount of badges, and if you have a new message.
The website never gets confused, because the cookies are never duplicated. Whenever someone comes to the website without a cookie, a new value is generated and sent to the user in the response. Then, every request after that, the browser sends the cookie value back with it. There's no way to possibly know (and it would be nearly impossible to guess) any other user's cookie ID, assuming the server wasn't also using IP address to validate session cookies. Regardless, for the programmers, this all takes place "behind the scenes". Programmers just typically access some sort of session data repository where they can store and retrieve information that is valid across page loads. As long as the user doesn't clear his cache or restart his browser, the session data will be available and unique to that user.
When ever we log in to our accounts, a session or cookie is created by the server. This session or cookie contains all the relevant information that the server needs to identify the user. Once server access this info, it knows which user it is dealing with and hence retrieves the users details only.