Examples - MySQL

Because views look and act very much like tables do, they allow us to abstract complex queries and present users or application writers with the sets of data they want or need in a manner that appears direct to them while allowing us to preserve a highly normalized database schema. Let’s create and use some views employing MySQL 5.0.1 or newer to illustrate this. For our first few examples, we’ll once again use the world database available from the MySQL AB web site, which we also employed in some of the programming examples in Chapter 7. You can refer back to that chapter if you need to see once again how the tables are defined.

Here’s a fairly basic example in which we create a view based on the City table. This view, which we’ve named big_cities, contains data only for those cities with a population in excess of 7,500,000, as shown here:

Once we’ve created the view, we can select data from it just as we would a table. Since we used SELECT * to create big_cities, this view has the same columns, with the same names and datatypes, as in the original table. We can use any sort of SELECT query to retrieve data from this view that would be legal to use with the equivalent table. For example, we can select just a couple of the columns, and use an ORDER BY clause to sort the result. We show two examples here:

So far, you might say, we’ve not really accomplished anything that we couldn’t have done using a CREATE SELECT statement such as this one:

CREATE TABLE big_cities
  SELECT * FROM City
  WHERE Population > 7500000;

---

NOTE  For many of the illustrations in this section showing SELECT query output, we’re using the MySQL Query Browser, which MySQL AB began developing in early 2004. (The version used for this book was 1.0.7-beta.) This graphical tool provides a GUI interface (comparable in many ways to SQL Server’s Query Analyzer) for testing and viewing the results of queries and stored procedures, as well as for administering tables. This application also provides some nice “extras” such as syntax highlighting and quick access to documentation on MySQL functions. You can download source code and compiled binaries for Linux and win32 platforms from http://dev.mysql.com/downloads/query-browser/index.html .

---

However, as we said earlier, a view isn’t just a static “slice” of a table—it’s a dynamic subset of the table that updates in real time. This can be demonstrated by altering a row in the base table like this:

and then running the second SELECT query on the view, as you can see here:

We also told you earlier that this dynamism isn’t always limited to going in one direction. Let’s prove this right now by updating the view and then seeing what happens when we perform a SELECT query on the base table. First we’ll select some data from City:

Now we’ll update the view using the command line in the MySQL Monitor:

When we run the SELECT again, we can see that the base table has indeed been updated:

Before we continue, let’s pause to answer a question that you’re likely to be asking by now: How do I list the views that I’ve created? If you’re thinking that you’d use a SHOW VIEWS command, that’s a good guess, but the way that this is done in MySQL is through the use of a SHOW TABLES query. Beginning with MySQL 5.0.1, the output of SHOW TABLES contains two columns rather than one as in earlier versions:

Now let’s create a new view based on the City table. The data in this view is intended for direct display to end users (perhaps some young geography students) who don’t really need to see the city ID, so you should probably just omit that from the definition for the new view. The data will also be more user-friendly if it shows the full names of the countries in which the world’s largest cities are located, rather than the three-letter codes from the City table. To accomplish these goals, you’ll need to join the CountryCode column in City on the Code column in the Country table so that you can obtain the corresponding value from the Country.Name column. However, City also has a Name column. To help avoid confusion, you can employ a list of column names following the name of the view in the CREATE VIEW statement. Putting this all together, we might arrive at a view definition that looks something like this:

Let’s see what happens when we try selecting from this view in the MySQL Query Browser:

Notice that because this view is defined in terms of a join, you can’t update it directly. Here’s what happens when you try:

However, you can update either or both of the base tables of the view—City and Country—and any matching rows in big_cities_students will also be updated.

The ALTER VIEW command allows you to change the definition of an existing view, as previously discussed. We use this command here to modify the big_cities_students view in order to include a larger subset of data from the City table:

As you can see from the result of the COUNT() query, we’ve increased the number of cities in this view from 24 to 237.

We could have accomplished the same thing by using

CREATE OR REPLACE VIEW big_cities_students (city, country, inhabitants)
  AS
    SELECT ci.Name, co.Name, ci.Population
    FROM City ci
    JOIN Country co
    ON(co.Code = ci.CountryCode)
    WHERE ci.Population > 1000000
    ORDER BY Name;

Both of these methods are really just shorthand for DROP VIEW big_cities; followed by CREATE VIEW big_cities AS...; .

Now that we’ve explored the mechanics of views in MySQL 5.0.1, we’ll talk about some of the advantages that views offer MySQL database administrators and developers in the following section. We’ll also address some security and related concerns.