Retrieve data with SELECT - SQL Tutorial

- [Instructor] When you ask a database to give you some data, you write a piece of code called a query and that's the queue in SQL. To retrieve data from a database, we use what's called the SELECT query. So if I click on the actor table there, you can see that the database has run its own query for us. SELECT * FROM actor. SELECT queries always take the basic form, SELECT field name FROM table name. And the asterisk there just means all. So this means SELECT ALL FIELDS FROM actor. And we know from what we've seen previously that the four fields we can see here horizontally are indeed all of the fields available in the actor table. So our query has worked. Although, we can see all four fields, we can only see 25 rows. You see it says 25, KEVIN BLOOM. And if we go up in the yellow bar with a little tick, it says showing rows 0 to 24 of 200 total. So there's 200 rows in this table, but the database is only showing us the first 25. Notice that it says here 0 to 24, and not 1 to 25. This is called zero indexing. And it means that the database starts counting at 0 instead of 1. So even though the data in the table, the actor ID data says 1, 2, 3, and so forth, up to 25, the database itself counts these rows in a way that isn't shown here on the screen, from 0. Let's change this query. So click Edit in line, if you haven't already, and replace the * with first name. So you have SELECT first_name FROM actor. And I'm going to just pop the FROM section of this query onto another line. You can keep it all on the same line, if you like. I just think it's easier to see the query, the parts of the query that way. So our screen changes when we click Go. Because instead of seeing all four rows now, all four fields, sorry, we can only see one, which is the first name column. Now, we can click Edit in line again and say SELECT first_name, last_name. And can you see it's offering us field names that we might want to use, and I've just pressed Tab to take advantage of that. So we've now got SELECT first_name, last_name FROM actor. Then we click Go. And unsurprisingly, perhaps, we now have two fields showing. And you can see how the fake data works here, by the way, because we have PENELOPE GUINESS here, which, I think, the real actor would probably be Alec Guinness. So they've scrambled the first and last names of different actors in this database. We can select or retrieve as many fields as we want to, by the way, just by listing all the fields with commas in between them, as we've done here. Now notice that the actor table is enclosed in back ticks. This is an oddity of MySQL. Back ticks aren't the same as single quotation marks or apostrophes, by the way. On a Mac, you find the back tick, bottom left, just below the tilde sign of your keyboard. They are optional here. And I want to try and quote the queries in this course without back ticks in them, because that way, the queries will work across Microsoft Oracle, MySQL, and PostgreSQL. Back ticks should be used in MySQL, where the table name has a special character or a space or a carriage return, or uses a reserved word such as date in the table name, because those things would confuse the database. So if we had say an @ sign there, and the database maybe would get confused. In MySQL, you would use back ticks to quote your system identifier, that's the proper name for it, your table name. And that tells that MySQL database that anything inside the back ticks is the table name. And we don't want to do anything special with our query. It's just a table name that we're using. If you're using Microsoft, you quote your system identifies using square brackets. And if you're using PostgreSQL, you quote them using double quotation marks. This is compliant with the standard, actually, the way that Postgre does it. Now, from what I've read, Oracle also uses double quotation marks. But when I have tried to make that work in my own testing, it hasn't worked properly. So in this course, we're going to try avoiding using any system identifiers. And we're just going to use the table names like that. Where possible, if you are creating a database, don't use any special characters, whitespace symbols, or reserve names when naming your tables because it makes the whole thing really complicated. But if you didn't create the database that you are using, you'll have no choice. And then it's good to know how to quote system identifies for your version of SQL. Notice also that I've capitalized the keywords here, SELECT and FROM, and it's just to show the structure of the query more clearly. They can all be lowercase, and it works perfectly well. So coming up, we're going to look at how to filter our new query.