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.
MySQL supports views, so you can save a query as a view, and use that view like you would a subselect. This is really very simple. For this example, we'll use the database. And here's a useful little query that gets the as minutes and seconds I can say SELECT ID, album_id, title, track_number. And duration DIV 60 AS minutes.
And duration MOD 60 as seconds FROM track. And when I run this, you'll see we have the entire track table. And instead of the duration as a number of seconds we've got it in minutes and seconds. And that's actually pretty useful. Now if I know I'm going to use this query a lot, I can save it as a view. I can simply do this. I can say CREATE VIEW. And name it trackView.
And then here's just an overloading of the as keyword. As. And this is the view itself. I like to put the semicolon on a separate line when I indent like that. It's just me, it's not necessary. And now I can say select asterisk FROM TrackView, as if it were a table, or a sub select. I press Go. I've got exactly the same result, but now I have got it as a trackView. I don't need to have all of this anymore, I can just say select asterisk from trackView and I get that result.
Now because this select is effectively a table, this trackView used as a SELECT. And it can be used exactly as you would use a table. I can use this as I would use a subselect. Anywhere otherwise, I would use a table. For example, I can do this. So A would be the album table. And so we got a.artist and t.track_number. And T will be our trackView. And title from the trackView.
And minutes and seconds from the trackView from album is A. And join. And we'll grab our track view here, as t on t.album ID equals a dot ID. And we could ORDER BY. And I'll put my semicolon down on another line like that. And when I press Go now I have this lovely joined table again.
I've got my album and my sequence in the album. I've got the minutes and seconds because instead of the track table I'm using this trackView that has that minutes and seconds split out like that. Very useful. When you're done with the View, you can drop it just like you would a table. You can say DROP VIEW trackView. This does not affect the underlying tables. It only deletes the View. So a View is really just a saved query. Because the result of a select is effectively a table, you can use a view anywhere you would use a table or a sub select.
I'll show you more complex example in the next lesson.
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.