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 view can be a simple straight query or it can be a more complex joined query. The technique is the same. For this lesson, we will be using the album database. And I want to start with a query that shows all the tracks for each album with the duration as minutes and seconds. I'm just going to go ahead and copy and paste this out of the exercise files here. And grab it here starting at line 75. And I'll copy that and paste it into SID and press go.
And so you see we have a simple joined query with artist, album, track, track number, minutes and seconds, like that. And, there's the query where we can look at it in the editor. And you can see it's a pretty straight forward joint query, we've covered all of this stuff already. Now if I find this query very, very useful, I can save this as a view. So I can just say CREATE VIEW, call it joinedAlbum. As, and there's my query.
And then I can come down here and I can say SELECT * FROM joinedAlbum like that. Press Go. And there is my exact same result, but this time the query is saved. As a view and so I can just do this again and do this again. And I get exactly the same result without having to have all of that code in there very time. In fact, because this is a view and the view is a select statement and a select statement can be used any way that you use a table.
I can just say SELECT FROM joinedAlbum WHERE artist equals Jimi Hendrix and say Go, and I get just the albums that are by the artist Jimi Hendrix. In fact, I can say SELECT artist, album, track, trackno, and I can do something like this. And by concatenating with separator the minutes and seconds padded to two places with a leading zero if necessary.
I can create the minutes and seconds as a duration, and so that it looks like minutes and seconds in normal time notation. There is a MySQL function for this. I believe it's called seconds to time. But it pads it out farther with hours, minutes and seconds and I don't really want that. I just want minutes and seconds and this is a pretty simple, way to do that and now with my view it makes it relatively easy. And in fact I can even incorporate this into the view if I wanted to.
So again when you're done with a view. You can just say DROP VIEW and the name of the view. And it doesn't delete the underlying tables, it just deletes the view. And so any select statement can be saved as a view. Views are a fairly standard feature across all systems and platforms. MySQL conforms to the SQL standard syntax for creating and dropping views.
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.