In this guide, we're going to get into querying when it comes to databases. So we're going to start off with the world's most basic query which is the SELECT * query. So when you say

SELECT * 
FROM Users

Now if I run this code right here this is going to bring up all of the users in our database.

If you do not have these users shown and you ran the script that is provided in the show notes. If this is empty then that means that there may have been an issue when running the script but that should have also reflected in the action output.

Right here you can see that we have three users Christine, Tiffany, and Jordan. You’ll notice that they are with their email addresses and the user's_id. Notice how the user ID was automatically set to 1, 2, and 3. This is because of the primary key and auto-incrementing. That's a rule we set when we created the table. Now notice the statement and it may seem like it's perfectly fine to do this.

However, this is considered a very bad practice so you wouldn't really want to run this code specifically like this in your application. It's not a problem when you only have three users and a limited number of columns.

By doing select star what this is doing is saying “I want you to grab me literally everything from the users table”. That could lead to some disastrous things if you are working with large data sets. That means you're bringing in millions of records and you could crash the entire system. Another way that we could do this is, we can say that I want to bring in the users_name.

Now if I run this code you can see that it brings in Christine, Tiffany, and Jordan just like that.

We don't have to do anything else it just brings in the name. If I did the same thing with users_email it brings only the email addresses. Now you can also do multiple so I can say users_name and users_email.

When I run this it's going to run the user's_name and the users_emails. This is a way of making your queries a little bit more intelligent. In addition to performance reasons why we do this. It also is smart to do this because it makes it more clear on exactly what is being returned.

It makes it more clear to other developers that may be reading it or even yourself. You might have to come back and read it a few months later on and find out which values you're actually going to have access to. If you just put the * in there then you're going to have to go check out the schema to find the exact table name or the column names. That can be kind of a clunky way of finding the data. So it's a basic star query and we could do the same thing on all of the tables.

So I'm going to change this back to * and I'm not doing this because I'm recommending to do it this way. I'm doing it just so you can see all of the data. I'm going to say select all from users, addresses, and then select all from guides.

Now another cool thing about mySQL workbench is if you select the item and you highlight. Then come up and come to the lightning bolt and it will say “execute the selected portion of the script or everything if there is no selection”.

That means that if I'm not selecting anything at all it's going to run all of these queries. If I want to only run a selected query then I can just highlight it all. Now if I run this you can see it brings up all of the guides.

I now have all of my guides. Another reason why you want to be careful when you use * is notice how it brought back the guides_id, guides_revenue, and all of that. Notice how we have the guides_id. and the users_id look similar? They're not actually the same thing because guides_users_id is our foreign key.

This could make for some confusing output when you're running this so just make sure that you're very careful whenever using the *. That got us all of our guides and If I do the same thing for addresses. This brings all of our addresses right here you can see each one of them from is all the way to our foreign key.

Then if I just ran the whole thing without anything being highlighted. Notice how it simply runs and returns the last statement.

So that's why you want to be very cognizant of running these types of queries. Come down to the tabs and see what gets returned on each one of these. I usually like to highlight the items that way I actually want to use just because that way I know exactly what values are coming in. So if I close out of the each one of the tabs.

let's say I want to see the users just to see that I'll skip addresses and guides. Now you can see only one tab and it brings up only the users. So that is how you can query a database for all of its records.

Lesson Code

USE devcamp_sql_course_schema;

SELECT users_name, users_email
FROM users;

SELECT *
FROM addresses;

SELECT *
FROM guides;