Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
MySQL is by far the most popular database management system for small- to medium-sized web projects. In this course, Bill Weinman provides clear, concise tutorials that guide you through creating and maintaining a MySQL database of your own. Bill explores the basic syntax, using SQL statements to create, insert, update, and delete data from your tables. He also covers creating a new database from scratch, as well as data types, transactions, subselects, views, and stored routines. Plus, learn about the multi-platform PHP PDO interface that will help you connect your database to web applications.
A subselect is effectively a nested select statement. In SQL, the result of a select statement is effectively a table. It usually just exists in memory but it can always be used, as you would use a table. Because of this, a select statement may be used as a data source for another select statement. This usage is called a subselect or sometimes a subquery. For this lesson we'll be using the World Database. And I'm going to copy and paste some code from the chapter eight exercise file. And I'm just going to grab this much of it, down to this first select statement there.
Copy and paste it into the SID. Go ahead and press go. See, I create a temporary table called T. It just has two columns, A and B. And there are just six character strings. And I've inserted three rows. In column A we have state codes, followed by numbers. And in column B we have country codes, followed by numbers. And I have this select statement using substrings to break apart these pact fields so effectively in column A we have two different pieces of information.
We have the state code. And some sort of a nonsense code there, and in column B we have the country code and the four letter code there. And that's all happening from the select statement with sub string functions. So if I come back out here to the exercise files, I'm going to grab this select statement here and. Paste this in, and you'll see that we're using exactly this same select statement with those substrings, but we're using it as a data source. So, everything from this opening parenthesis to this closed parenthesis is just the results of that select.
And you see I have alias, an AS ss. And so I'm grabbing ss CCode, and you can see that that's this CCode here, which is this substring, it's these four digits here in this fourth column of the result of that select. And so effectively, this here is the Subselect. So, this is the result of the select that we ran before. And you see it has four columns because we're using substring and AS, and substring and AS.
And so those columns can be used just as if they were a table in a database. And so that is one source. Is this select statement, and it's joined with the country table. And it's keyed on this s s country. S s country is this two letter code, right there in that third column. And that's keying on the country table code two, which is the two letter country code, so that we can actually find the name from the country table, right.
And when I press go. We now have name and we have CCode, that's these two columns here from our joint query. So this is very, very handy. You can do some processing in a select statement and use that as a data source for another query. So subselects are a convenient way of making your data available in different forms while keeping your database schema simple and well organized. So, I'm going to go ahead and drop this temporary table to put our database back in its original state for the rest of the course.
There are currently no FAQs about MySQL Essential Training.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.