Introduces views in MySQL, including how to find the view definition. Uses the definition of a stored view as a sample SQL query to walk through
- [Instructor] In this chapter, we're going to look at some of the more advanced aspects of SQL: Functions, Procedures, Variables, and Views. We're going to start with Views, and you can see that we have seven listed here on the left-hand side. Now a word of caution here. What follows is specific to MySQL. All database software should implement a variation on this theme, but they can be really quite different, so this one is specific to MySQL. Now, the word view has a very specific meaning in the world of databases.
A view is a stored query, so when we click on the view, we're shown a fairly simple query, SELECT * FROM 'actor_info', that looks very much as though it could be selecting data from a table. And if we look at the data that's returned, again, it looks very much like a table. We have a series of fields, and we have a series of rows, but in actual fact, this isn't a table, and the data aren't stored in the view. The data are stored in fields that are located in the tables, and all the view stores is a fairly complex query, so the data aren't duplicated.
They don't exist within the view. Now, by the look of it, this view joins data from the actor table and the film table there, and we can see the definition of this view in MySQL by saying SHOW CREATE VIEW, and that means show us the statement that is used to create the view. If we omitted the word SHOW there, we would be creating a view, and we wouldn't have a lot of luck doing that since the view already exists.
So the bit we're interested in is from here, from VIEW all the way down, and because that's almost impossible to see, I've popped it into Atom and made it a bit bigger so that we can take a look at it. This is far and away the most complex query we've looked at so far, but, actually, it uses all the elements that we've learned so far in the rest of the course. Now, the first thing to notice is that every single table in this query has been given an alias. So we've got film, film_category, film_actor, and then down here again we've got film_actor and film_category.
We've also got category and actor, and each one has an alias, and every field that's listed is prefixed by the alias of a table, so there's no room for doubt or ambiguity about which field is coming from where. The way to make sense of this query is to read it in three sections really, and I've separated the code into three sections and indented it to try and help readability. So the first section is kind of vanilla SQL.
It's retrieving three field names into columns, the actor_id from the actor field, first_name and last_name from the actor field, and it's giving each of them an alias. So we have actor_id, first_name, and last_name. And if we look back at the view, we see we have actor_id, first_name, last_name. Now, the final column in the results set is called film_info and that takes us on to the second bit of code, which is a fairly complex statement whose sole purpose is to return data into this film_info alias.
So this entire section of code is all being used to create the fourth column. And then, finally, the third section lists the tables where the data is pulled from, actor, film_actor, and then a group by statement. So to master this query, it seems fairly evident that this middle section is the bit we need to apply ourselves to. So let's have a look at the results again, and see what the output of this query is.
And I'm going to go into Options and say full text so that we can see a little bit more of what film_info is doing just in this first row. We have an actor name, and against that we can see all of the films that they're connected to. Now, these are made up combinations of names, so Anaconda Confessions is meant to be a film. Language Cowboy is meant to be a film. The films are organized into film categories, such as Animation, Children, and Classics, and they are arranged alphabetically and separated by semicolons, if you can see that there.
Within a film category, sometimes there's more than one film, as within Games. We've got two there, and when that happens, they are organized alphabetically and separated by commas. So this is a classic example of the one to many relationship I mentioned earlier. In fact, twice over. For every one actor, there are multiple film categories, and for every film category there are multiple films, and what this view query does is return the whole lot to us in one row.
So this query has completely reorganized our data. It's taken two tables, one with 200 actors and another with 1,000 films, and joined them together in a new way. This would take a long time to do this in Excel if you just downloaded all the films and all the actors and set about matching them up, but if you can master this code, SQL can do it for you relatively quickly. So let's look again at how the query achieves it. First we can see we have two group concat statements, one inside the other.
Here's the external group concat, and inside we have the other group concat. And we've got one group concat for each of the one to many relationships I've mentioned. So the first one is grouping films by film category, and the outer one is grouping film categories together by actor name. And we can see the first one works on the category name followed by colon, and then the second one works on film title separated by comma.
So that fits with what we've seen in the results. Now we can run parts of this query on their own, and that can be really helpful for making sense of what's going on. The middle part is this, and I'm gonna copy that and paste it here. And, once again, I'm going to choose full text so we can see what's going on a bit. And what we get is a list of films in alphabetical order separated by commas.
Now, at the moment, they're all in one cell, and they're not grouped yet. So you can see we have Academy Dinosaur, which is the name of a film, repeated something like 15 times, I would think, before we get on to the next film, so we don't have unique or grouped values here. And, in fact, there's so much text here that despite the fact I've opted for full text under Options, we've been cut off at Alamo, so we're still on the As, and we haven't got the full list of all of the films.
So what the query does is it takes this mass of film names then joins the category name, separates the categories with a semicolon and then groups them by actor name in the end, and this is how we get the re-orientated and merged data that we saw when we clicked on the view. Now, if you'd gone to the trouble of creating this query, you may well want to reward yourself by saving it, and that way you can run it whenever you like just by clicking it.
Views have other benefits as well. You can join the view to tables or to other views, so you can reference it in your queries much as you would reference a table. If you wanted to save this sort of query as a view in MySQL, you would use the command CREATE VIEW.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries