Views and More in MySQL 5.0

MySQL 5.0 supports a range of features that earlier versions of MySQL do not support. This article, the fourth in a series, explains views and other new features. It is excerpted from chapter eight of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress, ISBN: 1590593324).

Taking Derived Tables to the Next Level—Views

Unnamed views became possible in MySQL 4.1 with the inclusion of subselects that allow for the use of derived tables. Beginning with version 5.0.1, MySQL includes support for named views, usually referred to simply as “views.” Before we demonstrate how these are implemented in MySQL, let’s talk first a little bit about exactly what views are and how they will prove useful to MySQL database administrators and developers when it’s possible to deploy MySQL 5.0 in a production setting.

Views are also sometimes known as virtual tables, because they’re defined in terms of (other) tables through the use of queries. (We’ll see exactly how a view is defined in the upcoming “Syntax—Creating, Altering, and Dropping Views” section.) A view can be thought of as a “window” into a table, or perhaps a viewport that shows us a selected portion of a table at any given time, not unlike what’s shown in Figure 8-1.

However, a view is not merely a convenient container for a subset of records from a table. For one thing, a view is a “live” or dynamic snapshot of table data; when the data in the underlying table changes, so does that in the view. For another, we can construct views that aren’t merely subsets of table data; for example, we can perform calculations on the data, or obtain aggregate information for analysis purposes.


Figure 8-1.  A view can be thought of as a “window” into a table.

Finally, in some cases, a view can be active as well as passive—in other words, when the view is updated, so is the data in the table upon which the view is based. It’s possible for this to happen so long as the SELECT statement used in defining the view meets the following conditions:

  1. Data is selected from one and only one table (in other words, you can’t perform an update on a view that was defined using a join).
  2. The statement is not a SELECT DISTINCT query.
  3. The statement does not contain any GROUP BY or HAVING clauses, and does not use any aggregate functions.
  4. No UNION operators are used in the query.

CAUTION 
A bug in MySQL 5.0.1 prevents the use of UNION queries in view definitions. According to bugs.mysql.com , this will be fixed in the next release.

There are some additional restrictions on how views may be used to update data in underlying tables. We’ll discuss some of these shortly, in the “Syntax— Creating, Altering, and Dropping Views” section, which follows the one you’re reading right now.


NOTE  
If the definition of the underlying table changes, the change may not carry over to views derived from that table. For example, if we add a new column to a table that serves as the basis for a view, the new column does not get added to the view. This is true not only of MySQL (at least in version 5.0.1), but also with many other data-bases, including Oracle.

Conversely, if we’re not concerned with using views to update data, then there are few restrictions on how they may be defined. You can use practically any legal SELECT query as the basis for a view, and you’re not confined to selecting data from a single table. Nor do you necessarily have to select data from any tables at all.

It’s also quite possible to select data from one view in defining another view; generally this isn’t a recommended practice, but it can be and is done with databases that support views, the most notable exception to this being PostgreSQL. (MySQL does support nested views, as we’ll see shortly.) The only major prohibition in the ANSI standard is that SELECT statements used to define views may not contain an ORDER BY clause, but, we’ll see, MySQL ignores this restriction. 


CAUTION  Don’t confuse the terms “virtual tables” and “derived tables.” Both are defined using queries (subqueries in the case of derived tables) and both depend upon database tables for their existence. However, a derived table has no name and is transitory; it exists only for the lifetime of a query, and there is no way to recall it once the query has been completed, save by rerunning the query. A virtual table (or view) is a named entity that, once created, persists until it’s dropped, and that can be accessed at any time following its creation.

{mospagebreak title=Syntax–Creating, Altering, and Dropping Views}

As we’ve already mentioned, a view is based on a SELECT query, and this query usually makes up most of the view’s definition. To define a new view, we use the CREATE VIEW statement, whose standard syntax is shown here. While most databases offer extensions to it, nearly all of them that feature views support the basic form shown here. As you can see, it’s relatively straightforward and shouldn’t be too difficult for you to understand just by looking at it:

CREATE [OR REPLACE] VIEW view (columns) AS select-query
(WITH CHECK OPTION);

The optional OR REPLACE clause following the CREATE keyword is used to tell MySQL to re-create the view even if it already exists. Otherwise you’ll get an error if you try to create a view with the same name as an existing one.

In the preceding definition, view is the name of the view, which is covered by the same rules that govern other MySQL identifiers. This may be followed optionally by a list of columns–note that these are simply column names, as the types and sizes of the columns are already defined either in the table from which they’re being retrieved or in the SELECT statement. Next comes the AS keyword, which marks the beginning of the select-query used to obtain the data that will serve as the view’s contents.

The optional WITH CHECK OPTION clause following the query is defined in the SQL standard for use in creating a view that permits updates of its underlying table. (Recall that such a view may refer to only one table, as explained in the previous section.) In MySQL, this option doesn’t do anything; it is simply ignored by the command parser.

In addition to CREATE OR REPLACE VIEW , MySQL allows you to update an existing view with an ALTER VIEW command, although this isn’t part of the SQL standard. (Oracle and SQL Server also support this command.) MySQL’s ALTER VIEW replaces the SELECT query currently used in defining the view with a new query. The syntax is as shown here:

ALTER VIEW view (columns) AS
new-select-query
(WITH CHECK OPTION);

As is the case with CREATE VIEW , the WITH CHECK OPTION clause is ignored in an ALTER VIEW ; it is permitted for compatibility with other databases and the SQL standard but doesn’t actually do anything in MySQL.

In order to retrieve the query that was used to define a view, we use a new variation on the SHOW CREATE command, as shown here:

SHOW CREATE VIEW view;

Dropping a view is also very simple:

DROP VIEW view;

The DROP VIEW statement is supported by all major databases that implement views. view stands for the name of the view to be dropped.

These few statements are all that are required to work with views. We’ll provide some demonstrations showing how to use them in the next section.

{mospagebreak title=Examples}

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.

{mospagebreak title=Benefits}

At least some of the advantages of using views should be apparent. For instance, suppose you have a user who is interested in seeing which product categories have the most products and by which departments these products are sold. Without bogging down the discussion with an additional schema at this point, let’s just suppose that you can create a view fulfilling these requirements as follows:

CREATE VIEW product_view (catid, catname, catcount, deptname) AS
SELECT c1.cid, c2.name, c1.ct, d.name AS category
FROM
   
( SELECT
        
category_id AS cid ,
        department.id AS did,
        COUNT(category_id) AS ct
      FROM products
      GROUP BY category_id 
    ) AS c1
JOIN categories c2
ON c1.cid = c2.id
JOIN departments d
ON d.id = c2.department_id
WHERE c1.ct > 1
GROUP BY d.id;

Once this is done, your user can obtain the current listing by running a simple SELECT query on the view, like so:

SELECT * FROM product_view;

The view acts in all respects like any other table, except that your user does not need to worry about doing a join on three tables in order to obtain the data that he or she actually wants to see. Your user can order or filter the rows of this view:

SELECT * FROM product_view ORDER BY deptname, catname;

Or even use it in a join on another table:

SELECT DISTINCT(p.deptname), CONCAT(m.firstname, ‘ ‘, m.lastname) AS manager FROM product_view p
JOIN departments d
ON p.deptname = d.deptname
JOIN managers m
ON d.manager_id = managers.id
ORDER BY m.lastname ASC;

Of course, this join could very likely be done more efficiently by writing a new query against the original tables involved rather than using the view, and if the data were needed often enough in this form, you might want to define a new view that does so. If you’re in doubt, you can always compare the two methods using the EXPLAIN command to see how much of an efficiency boost you would gain from doing so.

Security and privacy protection are another issue that views can help to address. Let’s look at a scenario involving employee records. Suppose we want to build an address book application for our company intranet allowing employees to obtain name, department, and internal contact information for other employees, but not giving access to confidential data such as salary history or home address and phone. We could define a view such as

CREATE VIEW address_book_view (fname, laname, dept, title, email, ext) AS
SELECT e.firstname, e.lastname, d.name, j.jobtitle, e.email, e.workphone
FROM jobs j
JOIN employees e
ON e.job_id = j.id
JOIN departments d
ON e.department_id = d.id;

The address book application could then query this view as desired:

#  List of mail room employees
SELECT * FROM address_book_view WHERE deptname = ‘Mail Room’;
#  List all receptionists
SELECT * FROM address_book_view WHERE title LIKE ‘%receptionist%’;
#  Get the number of receptionists in each department
SELECT deptname, COUNT(deptname) AS num
  FROM address_book_vie w
  WHERE title LIKE ‘%receptionist%’
  GROUP BY deptname;
# etc., etc. …

These queries can include whatever sorting, filtering, and grouping criteria that are needed.

You don’t have to worry about address book users obtaining confidential information like home telephone numbers, rates of pay, marital status, and so on, because that data is never available to them—or even to the programmers writing the address book application.

On the other hand, for the payroll department, a view such as this one would be more applicable:

CREATE VIEW payroll_view
  (fname, minitial, lname, addr1, addr2, city, state, postcode, title, rate,
    mstatus, numdependents, taxid)
AS
  SELECT e.firstname, e.lastname, e.address1, e.address2, e.city, e.state,
    e.postcode, j.jobtitle, j.rate, e.mstatus, e.numdependents, e.taxid
  FROM employees e
  JOIN jobs j
  ON e.job_id = j.id
  WHERE e.startdate <= NOW()
   
AND e.enddate >= DATE_SUB(NOW(), INTERVAL 30 DAY);

By now, you’re probably getting the idea, but there’s more to keeping data secure than this. Not only do we sometimes need to prevent unauthorized par ties from reading certain types of information, but we must also be able to protect data from being overwritten when it shouldn’t be. If you look back for a moment at the views that we created in the previous section, you’ll recall that it’s sometimes possible to update a view and thus update the data in the base table from which it derives. However, there are often times when we don’t want this to happen. Some databases, like Oracle, allow you to deal with this type of situation by declaring a view to be read-only. MySQL doesn’t afford this option.

So how do we accomplish this task? The answer lies in leveraging the MySQL privileges system, which applies to views in much the same way as it does to tables. For instance, we can use table-level permissions in such a way that users may access views but not their base tables. Still referring to the examples in the previous section, suppose we want to create a user named student who can access either of the big_cities or big_cities_students views, but not any of the base tables in the world database. You can create such a user using standard GRANT commands, as shown here:

This user then logs in using the MySQL Monitor, and runs a SELECT query on big_cities. However, when the user tries to run an UPDATE on this table, he or she gets an access denied error from MySQL:

When this user attempts to list all the tables and views in the world database, the user sees only those views for which he or she has been granted privileges on, just as with regular tables:

Then there is the question of users accessing information about how views are defined. First of all, you can rest assured that users can’t find out what’s in base tables upon which they have no privileges by running a SHOW CREATE VIEW , as you can see here:

Users also cannot create views without restriction even on views (or tables) on which they have SELECT privileges, as shown here:

Beginning with MySQL 5.0.1, two new privilege types are introduced: the CREATE VIEW and SHOW CREATE VIEW privileges. Users must have the first of these privileges in order to create views and the second in order to see how existing views were created. In addition, users must have the proper privileges on the tables or views that they wish to use as the basis for new views.

Columns corresponding to these two privileges, named Create_view_priv and Show_view_priv, are added to the db, host, and user tables of the mysql database to accommodate these. Here’s the output of a SHOW CREATE TABLE statement showing how these columns are defined in the updated user table:

These privileges can be granted and revoked by setting the proper column values in the user, host, or db tables, or (preferably) through the use of one or more of the following:

  1. GRANT CREATE VIEW
  2. GRANT SHOW CREATE VIEW
  3. REVOKE CREATE VIEW
  4. REVOKE SHOW CREATE VIEW

A full discussion of these is beyond the scope of this chapter; however, if you’re familiar at all with the MySQL privilege system, you should not have a great deal of trouble figuring out how these commands ought to be used.

{mospagebreak title=Other Improvements in MySQL 5.0} 

There are some additional new features in MySQL 5.0 that you may find useful. These are detailed in the next few subsections.

New SELECT INTO Syntax

We’ve already seen how this can be used with cursors in a stored procedure. You can also use it to assign values to user variables in a regular SELECT query, e.g., SELECT name INTO @prodname FROM products WHERE prodid = 327; . Note that the SELECT must return a single row for this to work. We show an example here:

New Timestamp Functions

The TIMESTAMPADD() and TIMESTAMPDIFF() functions are introduced in MySQL 5.0.0. Here are the definitions for each:

TIMESTAMPADD(interval, expr, datetime) TIMESTAMPDIFF(interval, datetime1, datetime2)

TIMESTAMPADD() adds expr interval intervals to datetime and returns the result, where expr is an integer value. TIMESTAMPDIFF() returns the integer difference between datetime1 and datetime2 in terms of the unit supplied as interval .

In both cases, the interval argument may be any one of the values FRAC_SECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , or YEAR , and any of these may optionally be prefixed with SQL_TSI_ . The datetime expressions may be any valid DATE or DATETIME values.

A few examples, as shown here, may help to make clear how these functions are used:

Notice that negative values are possible both for the value of expr input to the TIMESTAMPADD() function as well as for the result returned by TIMESTAMPDIFF() .

{mospagebreak title=Change in User Variable Behavior}

Beginning in MySQL 5.0.0, user variable names are case insensitive. This means that @myvar , @MyVar , and @MYVAR will all be regarded as the same variable, as shown here (using MySQL 5.0.1-alpha):

If you’ve been relying on case sensitivity in user variables for any of your MySQL-related work, we recommend highly that you abandon this practice immediately.

New Values for DATE_ADD() and DATE_SUB() Functions

Two new values, WEEK and QUARTER , have been added for use with the DATE_ADD() and DATE_SUB() functions in MySQL 5.0. You can see how these are used and what sorts of results you can expect from them here:

Change in the VARCHAR Type

The VARCHAR type is expected to be altered so that it will be possible to store values with more than 255 characters. That hasn’t happened yet as of MySQL 5.0.1—if you attempt to define a VARCHAR column with a size of greater than 255 characters, MySQL will simply convert the column to the TEXT datatype. (See Chapter 2 of this book for a discussion of how MySQL “silently” changes column types.) However, we look for a change to take place by the time that MySQL 5.0 appears in a production release.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye