Introduces the fundamental SQL code—the select query—and explains the significance of capitalization and punctuation
- [Narrator] By default, your username and password are both set to root on phpMyAdmin. So if we click Go to login, we can see what phpMyAdmin looks like. I have a list of databases on the left, and we're going to be using the sakila database in this course. At the top, I have breadcrumb bar, not a very good one yet because it's only got the server equal to localhost. And this tells us that currently we're at the very highest level, the server level.
This breadcrumb bar will become more useful as we start drilling down into databases and tables. The localhost part here, incidentally, means that I'm running a server on my local machine and I'm using MAMP for that. Under the breadcrumb bar, we've got some tabs. So we've got Permissions, Import, Export, Settings. These tabs are context-dependent. So if I were to try and export something using this tab, it would try and export at the server level. Now let's have a look at the sakila database.
You can see that we have functions, procedures, tables, and views. And we're going to start off looking at tables, specifically the actor table. Now the breadcrumb has changed and tells us that we're within the server looking at the sakila database and within that we're looking at the actor table. The Browse tab has turned white to tell us that we are looking at or browsing the data. And the software, in fact, has returned us some data here automatically. When you ask a database to give you some data, you write a piece of code called a query which is the Q in SQL.
To retrieve data from a database, we use the SELECT query. Now when we clicked on the actor table, the software ran a query for us automatically which was SELECT * from `actor`. SELECT queries always take the basic form SELECT field from table name. The asterisk or star means all. So this query means select all the fields from the actor table. And therefore, we can tell from this result set that the actor table has four fields.
Now although we can see all four fields, we're only looking at about 25 rows here if I scroll down. In fact, it says in the yellow bar, "Showing rows naught to 24, 200 total." So the actor table has 200 rows but we're only seeing the first 25. Notice that it says naught to 24 and not one to 25. And that's because databases tend to start counting at zero instead of one.
This is called zero indexing. Let's change the query. Replace star with first_name. Now I've got that field name from the result set down here. And then click Go. The page changes. At the very top, we're given a warning message that says our current selection doesn't contain a unique column. We can ignore that. Once again, we're looking at rows naught to 24 of 200.
So the query has returned us all the rows in the table again, but this time we've only got the one column or field. Now we can click Edit inline again and adjust our query to SELECT first_name, last_name. Now you can see the software is helping me out there by suggesting field names. And I'm going to press Tab to take advantage of that. And now, voila, we have two fields or columns and all rows. We can select or retrieve as many fields as we want to from a table by just listing them and then separating them with commas as we have done here.
Let's take a closer look at the query. Notice that the table name, actor, is enclosed in backticks in MySQL. These aren't the same as apostrophes, by the way. Both the leading and the trailing backtick there slump backwards. For this query in this software, these are optional. So if we take them out and click Go, the query runs just the same as before. Backticks do have their uses though. If the table name had a special character in it such as a percent sign or an at sign, enclosing the table name in this way tells the database to ignore the character's special meaning and treat it as part of the name.
Similarly, if the table name contained a reserved word such as date, backticks tell the software to ignore the special meaning. It's not considered good practice to use special characters in field or table names, by the way, but sometimes you get no choice. And in these cases, you can use backticks on field names or table names in MySQL. In Oracle and PostGreSQL, instead of a backtick, a double quotation mark is used like so.
And in Microsoft, square brackets can be used for the same purpose. Notice also that I've capitalized these keywords, SELECT and FROM. It's just to show the structure of the query more obviously to you. The query would work absolutely fine without that.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries