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.
In this lesson, I'll show you a practical example of using a sub select to provide a list of rows to search for in a table. We're going to be using the album database for this lesson. I'm going to start with a simple query. It'll give us a list of albums that have tracks with a duration of 90 seconds or less. And so for this, I'm going to select from the track table, and I'm going to select distinct album IDs where the duration of a track is less than or equal to 90 seconds.
So it looks like this, SELECT DISTINCT album_id FROM track WHERE duration is less than or equal to 90 seconds. And when I say Go, I get a list of two album_ids. And these are the albums that have tracks with durations of less than or equal to 90 seconds. Now, I can get all of the details on the albums by using this query. As just the list of album IDs for a where clause in a query on the album table.
So, that looks like this. So, now I'm selecting from the album table, and I'm using the result of this other query to provide the IDs for the rows that I want to see. From the album table. So I press Go. And now I have rows from the album table with these IDs. So that's very useful. In fact, I can get a little bit more complicated if I want to. I can create a join query to get the full details of the album and tracks for the entire albums for these two IDs.
So that would look something like this. So A will represent the album table, and T will represent the track table. So there I'm getting title as album, artist from the album table, track number from the track table as sequence, title from the track table, and duration from the track table as seconds. And so I'll have FROM album AS a, and JOIN track AS t. And I'm joining on, t.album_id equals a.id.
So that's all very straightforward. I'm using this same where clause and I'm just going to insert an ORDER BY and when I press Go. Looks like I have a little typo here. This needs to be a.id, and I press Go and I have these two albums and all of their tracks and all of their data from both of these tables. Now, if I want to just get the tracks that I'm looking at, I can actually simplify this query quite a bit.
So, instead of joining the track table, I can simply join this query like this. As t, and I need to add these other columns here. Track number, duration, and title. Because this query is coming from the track table anyway, right? And I still need an on clause for my join, like that. And when I press Go, now I just have these two tracks.
And I'm using this sub select, as the joined table. So, a sub select, as you can see, can be used anywhere you would use a table, because the result of a select is effectively another table itself. In the next lesson I'll show you how you can save your select query as a view. And that view can then be used over and over again. In place of a table in the same way you would use a sub select.
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.