Views
Contents |
[edit] Introduction
In addition to the big steps forward in terms of support for stored procedures Version 5 sees support for views in MySQL. Views are supported in most, if not all of the major players in the database market and their introduction into MySQL is a real step forward for support of industry standard database managment.
[edit] So What Are They?
Views are simply a method of storing an SQL statment as a persistent part of the database. Prior to views if you wished to retrieve only a sub set of data from a table or data from two or more tables this needed to be done using an sql statement which once called would need to be rewritten if the same data was needed again. This could of course be written as part of the application layer but at the database level these SQL queries were in effect discarded once used. Also there can be differences in the way data is stored and in the way it is accessed, for example you might have a table which holds details of people, however these people could be divided into seperate groups such as employees, contacts etc. Prior to views to access this data you needed to write specific queries for each sub group, as we will see with views we can create persistent definitions of these sub groups, this has great benefits for the maintainability of your applications.
[edit] Getting Going
Unfortunately the setup and installation of MySQL is a big subject and something we don´t have room to go into detail about here, but there are plenty of web sites which can give you all the information you need.
To program with views in MySQL we need to use version 5.0.1 or above, we recommend you use as high a release as possible, this can be downloaded from the MySQL web site for free. In addition to MySQL we will be using a basic text editor, this is not necessary but will make our life a little easier.
One of the advantages of using views is that they are stored within the database so any commands we use on one operating system will be available on another. The tutorials have been run under SuSe Linux 9.3 but they should run exactly the same on any other operating system you may be using. One final word, if the only copy of MySQL you have is a lower version than 5.0.1 then unfortunately you won´t be able to try the examples yourself, but you can still read and learn all about views from the tutorials.
[edit] Conventions
Finally a word on the conventions we will be using in the tutorials. Any code that you can type in will be displayed in one of two ways
//This is code seen for the first time. //Or code you have already seen but is being reference later
Changes to code previously created during the tutorial will be shown in bold
//This is old code here //This is the line we want you to add
You can download a zipped copy of all the sources used in each chapter using the menu. But after each section of code will be a link to the actual source code file that we used. We suggest you try typing in each section of code yourself as this can often help you understand what is actually going on, but if you can´t get a particular view to work feel free to use our code.
So lets get straight in by having a go at creating a view, you can click on the Creating Views link bellow to go to the relevant page or use the menu at the top of this and every page. At any time you can move on to the next section using the link marked > or back to the previous section using the link marked with <.
[edit] Creating Views
Views as we touched on in the introduction are effectively stored SQL statements. Therefore to use views we will need to create some test tables and data to create views against, run the following script into MySQL to produce the test database we will be using in these first examples.
drop database if exists pers
\g
create database pers
\g
use pers
\g
create table emps(emp_id int NOT NULL,
emp_name varchar(30),
dept_id int,
salary decimal(7,2),
primary key(emp_id))
\g
insert into emps (emp_id,emp_name,dept_id,salary)
values (1,’Roger’,1,2000.00),(2,’John’,2,2500.00),(3,’Alan’,1,2100.00)
\g
select * from emps
\g
create table dept (dept_id int NOT NULL,
description varchar(30),
primary key(dept_id))
\g
insert into dept (dept_id,description)
values (1,’Information Technology’),(2,’Sales’)
\g
select * from dept
\g
Download setup.myp
If you haven’t used source before its a way of writing an SQL script in file and then running it in MySQL. To use source call it in the following way.
source /usr/local/mysql/source/setup.myp
Of course exchange the path for the one you will be using. One word of warning if your using MySQL on windows, you need to use / rather than as MySQL can interpret as a call to a special function such as h which would show the MySQL help.
[edit] Creating Simple Views
Now that we have some test data to work with we can start to create some views against the tables. The syntax to create a view is fairly simple.
CREATE VIEW view_name AS SELECT column_list... FROM table_name;
CREATE VIEW tells MySQL that a view is being created, a name is given to the view, this can be any name which follows the naming conventions for MySQL database objects but it should be noted that as views are dealt with in a similar way to tables view names must not be the same as a table name in the same database. Finally a select statement is specified, this can be almost any valid SQL statement using a single or multiple tables, there are some restrictions we will look at in a later section but for the most part all standard SQL statements will work.
The most simple form of a view is a simply select * from a table.
create view v_emp as select * from emps; Query OK, 0 rows affected (0.00 sec) select * from v_emp; +--------+----------+---------+---------+ | emp_id | emp_name | dept_id | salary | +--------+----------+---------+---------+ | 1 | Roger | 1 | 2000.00 | | 2 | John | 2 | 2500.00 | | 3 | Alan | 1 | 2100.00 | +--------+----------+---------+---------+ 3 rows in set (0.00 sec)
This is effect an exact replica of the SQL used to query the table itself. One very important point to make note of at this early stage is that the view creation makes no copy of the data, it’s simply storing the SQL. We defined the statement with * which as you may be aware equates to all of the columns in the table, but just as with a table itself we can now specify particular columns to be returned from the view like so.
select emp_name from v_emp; +----------+ | emp_name | +----------+ | Roger | | John | | Alan | +----------+ 3 rows in set (0.00 sec)
So any columns we can select from the original table can be selected from the view we created. It’s also possible to use functions with views just as with tables.
select reverse(emp_name) from v_emp; +-------------------+ | reverse(emp_name) | +-------------------+ | regoR | | nhoJ | | nalA | +-------------------+ 3 rows in set (0.02 sec)
We can also use the view in a join with another table.
select v.emp_name, d.description from v_emp v, dept d -> where v.dept_id = d.dept_id; +----------+------------------------+ | emp_name | description | +----------+------------------------+ | Roger | Information Technology | | John | Sales | | Alan | Information Technology | +----------+------------------------+ 3 rows in set (0.01 sec)
As can be seen a view based on select * from a table is in effect the same as using the table itself. Before we move on to more complicated views lets go back a few steps to the point we made about the view being just a stored select statement, lets make an update to the table the view is based on to see what effect this has on the view.
update emps set emp_name = ’Jane’ where emp_name = ’Alan’; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 select * from emps; +--------+----------+---------+---------+ | emp_id | emp_name | dept_id | salary | +--------+----------+---------+---------+ | 1 | Roger | 1 | 2000.00 | | 2 | John | 2 | 2500.00 | | 3 | Jane | 1 | 2100.00 | +--------+----------+---------+---------+ 3 rows in set (0.00 sec) select * from v_emp; +--------+----------+---------+---------+ | emp_id | emp_name | dept_id | salary | +--------+----------+---------+---------+ | 1 | Roger | 1 | 2000.00 | | 2 | John | 2 | 2500.00 | | 3 | Jane | 1 | 2100.00 | +--------+----------+---------+---------+
This demonstrates that the view is just a stored SQL statement and does not store a copy of the data.
We have seen that we can create a view based on the whole table, but its also possible to create a view on a subset of the data contained in a table. Let’s say we have a combined HR and Payroll system and we want a view on the emps table that omits the salary field so that our HR staff don’t see the employees salary column.
create view v_hr_emp as select emp_id, emp_name, dept_id from emps; Query OK, 0 rows affected (0.00 sec) select * from v_hr_emp; +--------+----------+---------+ | emp_id | emp_name | dept_id | +--------+----------+---------+ | 1 | Roger | 1 | | 2 | John | 2 | | 3 | Jane | 1 | +--------+----------+---------+ 3 rows in set (0.00 sec)
The users in the HR department can now be given access to the new view but not the underlying table. This allows them full access to run SQL statements against the view but protect the salary information. Lets say that we then had a number of users that only needed to view the emp_id and emp_name columns and not the dept_id column. We have to options, either create the view based on the original emps table or we can in fact create the new view based on the v_hr_emp view. Now would be a good time to introduce the DROP VIEW syntax as we will be using it in the next example, the syntax is simply.
DROP VIEW [IF EXISTS] view_name, view_name .... [RESTRICT/CASCADE];
Simply use the key words DROP VIEW and the view or views you wish to drop. The keywords RESTRICT and CASCADE will be parsed successfully but are not currently supported so for all intents and purposes can be ignored. You can add IF EXISTS after the DROP VIEW to avoid any error messages being displayed if the view currently does not exist.
To create the new view based on the original table or the current view we do the following.
create view v_emp_only as select emp_id, emp_name from emps; Query OK, 0 rows affected (0.00 sec) select * from v_emp_only; +--------+----------+ | emp_id | emp_name | +--------+----------+ | 1 | Roger | | 2 | John | | 3 | Jane | +--------+----------+ 3 rows in set (0.00 sec) drop view if exists v_emp_only; Query OK, 0 rows affected (0.01 sec) create view v_emp_only as select emp_id, emp_name from v_hr_emp; Query OK, 0 rows affected (0.00 sec) select * from v_emp_only; +--------+----------+ | emp_id | emp_name | +--------+----------+ | 1 | Roger | | 2 | John | | 3 | Jane | +--------+----------+ 3 rows in set (0.00 sec)
[edit] WHERE Clause
The obvious question to ask is why would you base the new view on another view and not the base table. Well it’s possible to include a where clause as part of the view statement, this means that we can restrict the view in one place, in our case v_hr_emp and this is then automatically applied to the new view. As mentioned previously we can use most valid SQL statements so creating a view with a where clause is the same as using one in a select statement. Lets say we want to restrict our view to only show employees in the Information Technology Department.
create view v_hr_emp as select emp_id, emp_name, dept_id from emps where dept_id = 1; Query OK, 0 rows affected (0.02 sec) mysql> select * from v_hr_emp; +--------+----------+---------+ | emp_id | emp_name | dept_id | +--------+----------+---------+ | 1 | Roger | 1 | | 3 | Jane | 1 | +--------+----------+---------+
Running the view shows that the records have now been restricted to just those in the Information Technology department. How does this effect the view we created that was based on v_hr_emp?
select * from v_emp_only; +--------+----------+ | emp_id | emp_name | +--------+----------+ | 1 | Roger | | 3 | Jane | +--------+----------+ 2 rows in set (0.00 sec)
The v_emp_only view being based on the now restricted v_hr_emp now has the same restrictions. It’s worth noting that dropping and recreating v_hr_emp didn’t effect v_emp_only as it’s not compiled code, it’s simply a stored SQL statement.
Views are not restricted to columns in a single table, we can use a join on any number of tables to create our views. It’s a common requirement to store codes and descriptions in a single table to make it easier for producing reports by users who don’t have the knowledge to create complex SQL statements, prior to views the only way to do this in MySQL was to store the data in the same table and maintain this via code in the application, or create a table which was populated with the results during overnight processes. But with views its easy to create these datasets on the fly. Our emp table contains a dept_id which is a reference to a row in the dept table, the dept table in turn stores the actual description of the department. It’s setup this way because we might want to change the name of a department but we don’t want to have to change multiple records in the emp table (this is relatively simple with a SQL but it’s not the most efficient way to store the data). But the HR department want to run reports against the data but don’t have the experience with SQL to join the two tables. We can create a view to show the department name alongside the the emp data.
create view v_emp_detail as select emp_id, emp_name, description, salary from emps e, dept d where e.dept_id = d.dept_id; Query OK, 0 rows affected (0.00 sec) select * from v_emp_detail; +--------+----------+------------------------+---------+ | emp_id | emp_name | description | salary | +--------+----------+------------------------+---------+ | 1 | Roger | Information Technology | 2000.00 | | 2 | John | Sales | 2500.00 | | 3 | Jane | Information Technology | 2100.00 | +--------+----------+------------------------+---------+ 3 rows in set (0.00 sec)
We can now change the name of our departments without having to refresh any tables.
update dept set description = ’Sales and Marketing’ where dept_id = 2; Query OK, 1 row affected (0.26 sec) Rows matched: 1 Changed: 1 Warnings: 0 select * from v_emp_detail; +--------+----------+------------------------+---------+ | emp_id | emp_name | description | salary | +--------+----------+------------------------+---------+ | 1 | Roger | Information Technology | 2000.00 | | 2 | John | Sales and Marketing | 2500.00 | | 3 | Jane | Information Technology | 2100.00 | +--------+----------+------------------------+---------+ 3 rows in set (0.00 sec)
[edit] Niladic Functions
It’s possible to use niladic functions such as CURRENT_USER, CURRENT_TIME within views. At may at first glance seem as if these won’t be of much use but they offer some interesting possibilities. Lets take for example a table with a start date, lets alter the emps table to include one.
alter table emps add column start_date date; Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0
Now imagine if we wanted to expand on our v_emp_details view to show us how long our employees have been employed with the company. We can can do this using the niladic function CURRENT_TIME. First we need to insert some data into our newly created column and then we can create the view using the niladic function.
update emps set start_date = ’1999-01-02’ where emp_id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 drop view v_emp_detail; Query OK, 0 rows affected (0.00 sec) create view v_emp_detail as select emp_id, emp_name, description, salary, (year(current_date)-year(start_date)) as length_of_service from emps e, dept d where e.dept_id = d.dept_id; Query OK, 0 rows affected (0.00 sec) select * from v_emp_detail; +--------+----------+------------------------+---------+-------------------+ | emp_id | emp_name | description | salary | length_of_service | +--------+----------+------------------------+---------+-------------------+ | 1 | Roger | Information Technology | 2000.00 | 6 | | 2 | John | Sales and Marketing | 2500.00 | NULL | | 3 | Jane | Information Technology | 2100.00 | NULL | +--------+----------+------------------------+---------+-------------------+ 3 rows in set (0.00 sec)
The MySQL documentation says that when using niladic functions with views the function is evaluated at query time and not at the time the view was created. This is important as it means the data will change each time the view is selected from. But when testing this it appears that this is not always the case.
A great way to use niladic functions with a view would to separate data in a table by user, I added a column to emps to hold the user id of the user who inserts the data into the table, I then created a trigger which automatically sets the value of the user who inserted the record. A view was then be created which only allows users to view data they have created using the niladic function CURRENT_USER. The table alteration and triggers looked like so.
alter table emps add column user_id varchar(50) // Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 create trigger bi_emps_fer before insert on emps for each row set new.user_id = CURRENT_USER// Query OK, 0 rows affected (0.00 sec)
A record was then inserted into the emps table and the trigger automatically inserted the user_id into the table.
insert into emps (emp_id, emp_name, dept_id, salary) values (4,’Dave’,2,3000.00)// Query OK, 1 row affected (0.00 sec) select * from emps// +--------+----------+---------+---------+----------------+ | emp_id | emp_name | dept_id | salary | user_id | +--------+----------+---------+---------+----------------+ | 1 | Roger | 1 | 2000.00 | NULL | | 2 | John | 2 | 2500.00 | NULL | | 3 | Alan | 1 | 2100.00 | NULL | | 4 | Dave | 2 | 3000.00 | root@localhost | +--------+----------+---------+---------+----------------+ 4 rows in set (0.01 sec)
A view was created using the niladic function CURRENT_USER to filter those records which have been create by our user.
create view v_my_emps as select emp_id, emp_name, dept_id, salary from emps where user_id = CURRENT_USER// Query OK, 0 rows affected (0.00 sec) select * from v_my_emps// +--------+----------+---------+---------+ | emp_id | emp_name | dept_id | salary | +--------+----------+---------+---------+ | 4 | Dave | 2 | 3000.00 | +--------+----------+---------+---------+ 1 row in set (0.26 sec)
But if we then log in as a different user and run the same query we get the same results. I then looked at the view definition using the appropriate information schema (more on this later) and found the following.
+--------------------------------------------------------------------+ | view_definition | +--------------------------------------------------------------------+ | select `pers`.`emps`.`emp_id` AS `emp_id`,`pers`.`emps`.`emp_name` | | AS `emp_name`,`pers`.`emps`.`dept_id` AS `dept_id`, | | `pers`.`emps`.`salary` AS `salary` from `pers`.`emps` | | where (`pers`.`emps`.`user_id` = _latin1’root@localhost’) | +--------------------------------------------------------------------+ 1 row in set (0.23 sec)
This shows that the niladic function CURRENT_USER was evaluated at the time the view was created and converted to the literal value rather than storing the niladic function call in the view. The point of this is that while it can be useful to use niladic functions it’s worth investigating what’s actually stored in the view definition.
While we are talking about what’s actually stored in the view definition it’s worth mentioning views that are based on a select * statement. When a view is created using * the stored view definition uses the column names from that table at that point in time.
create view v_emps as select * from emps; Query OK, 0 rows affected (0.00 sec) select * from v_emps; +--------+----------+---------+---------+ | emp_id | emp_name | dept_id | salary | +--------+----------+---------+---------+ | 1 | Roger | 1 | 2000.00 | +--------+----------+---------+---------+ 1 row in set (0.00 sec) alter table emps add column start_date date; Query OK, 1 row affected (0.25 sec) Records: 1 Duplicates: 0 Warnings: 0 select * from v_emps; +--------+----------+---------+---------+ | emp_id | emp_name | dept_id | salary | +--------+----------+---------+---------+ | 1 | Roger | 1 | 2000.00 | +--------+----------+---------+---------+ 1 row in set (0.00 sec)
You can see that the view v_emps was created using select * but we then added a new column to the underlying table emps. But when we select * from the view this new field isn’t present. The reason for this can be seen in the view definition.
select view_definition from information_schema.views where table_name = ’v_emps’; +--------------------------------------------------------------------------------------------+ | view_definition | +--------------------------------------------------------------------------------------------+ | select `pers`.`emps`.`emp_id` AS `emp_id`,`pers`.`emps`.`emp_name` AS `emp_name`, | | `pers`.`emps`.`dept_id` AS `dept_id`,`pers`.`emps`.`salary` AS `salary` from `pers`.`emps` | +--------------------------------------------------------------------------------------------+ 1 row in set (1.05 sec)
The view definition shows that the view is stored with the column names listed rather than storing the select *.
[edit] PRIVILEGES
We have covered many of the methods for creating views and in the next section we will look how we can use views for inserting and updating data in the underlying tables. But before we move on to that it’s worth mentioning the new view privileges introduced to allow users to create views. To allow users to create views we can use the following syntax
GRANT CREATE VIEW ON database_name.table_name TO user;
The database_name and table_name allows the DBA to restrict creation of views to individual tables, databases or the whole database, so for example we could use on of the following 3 methods.
GRANT CREATE VIEW ON pers.emps TO andrew;
GRANT CREATE VIEW ON pers.* TO andrew;
GRANT CREATE VIEW ON *.* TO andrew;
The first statement allows the user andrew to create views on the emps table in the pers database only, the second statement allows andrew to create views on all tables in the pers database and finally the third statement allows andrew to create views on any tables within the database. In addition to the GRANT CREATE VIEW there is also an associate REVOKE CREATE VIEW which removes the users ability to create views.
The above GRANT and REVOKE statements are new but it is also possible to use existing GRANT privileges to allow users access to use views. As views are treated in the database as tables these privileges are identical to the table privileges. These are in short.
GRANT SELECT ON view_name TO user;
GRANT UPDATE ON view_name TO user;
GRANT DELETE ON view_name TO user;
GRANT INSERT ON view_name TO user;
One important thing to note is that a user only needs access to the view and not the underlying table. So it is sufficient to grant select access on the view and ignore the associated grants on the underlying table, that’s not to say the user gets the privileges on the underlying table just that they are not needed to use the view successfully.
[edit] COLUMN LIST
Up to this point we have been allowing the base table to assign the column names for the view. This may be OK but it is possible to assign new column names to be used with the view. There are two methods to do this, firstly we can simply assign the name as we would in any normal SQL statement using the AS keyword.
create view v_emp as select emp_name AS name from emps; Query OK, 0 rows affected (0.00 sec) select * from v_emp; +-------+ | name | +-------+ | Roger | | John | | Alan | +-------+ 3 rows in set (0.00 sec)
The second method is to define a list of the column names in much the same was as you would with an insert. All that needs to be done is to specify the names in the correct order, making sure there is a name for each column specified.
create view v_emp (name) as select emp_name from emps; Query OK, 0 rows affected (0.00 sec) select * from v_emp; +-------+ | name | +-------+ | Roger | | John | | Alan | +-------+ 3 rows in set (0.00 sec)
[edit] ALGORITHM
So far all of the views statements we have used have been ANSI standard view definitions, MySQL also supports a non-standard clause called ALGORITHM. When processing a view MySQL can use one of two options, a merge algorithm or a temporary table algorithm. If the merge option is used this is in effect converting the view into the equivalent query on the underlying table. Using the temporary table option coverts the view into an appropriate temporary table definition. If the algorithm clause is not specified then MySQL will decide which to use, but in most instances this will be merge because it is faster than the temporary table option and also because updates cannot be made against a temporary table algorithm.
To manually specify which option to use you can use the following syntax.
CREATE ALGORITHM=TEMPTABLE VIEW v_emp AS select * from emps; CREATE ALGORITHM=MERGE VIEW v_emp AS select * from emps; CREATE ALGORITHM=UNDEFINED VIEW v_emp AS select * from emps;
However in almost all cases the algorithm clause can be ignored as MySQL will automatically decide on the best option.
[edit] Updateable Views
In the last section we looked at how to create views so that we could use them to select data from one or more tables. We saw that apart from the name there was very little difference in the way data is retrieved from views and from tables.
The next logical step would be to see if we can use other SQL statements against views, SQL such as UPDATE, INSERT or DELETE. You may remember that we said while views allow us to select data it was not actually stored by the view but by the underlying table the view is based on. This might suggest that inserting, updating and deleting data is not possible, but this is not the case, at least not in all circumstances.
[edit] UPDATES
There are some restrictions on SQL statements against views these become tighter the more complex the view is. We will look at the those restrictions in a moment but lets first look at a simple view we created in the last section and see if it’s possible to update it.
create view v_emp as select * from emps; Query OK, 0 rows affected (0.00 sec) select * from v_emp; +--------+----------+---------+---------+ | emp_id | emp_name | dept_id | salary | +--------+----------+---------+---------+ | 1 | Roger | 1 | 2000.00 | | 2 | John | 2 | 2500.00 | | 3 | Alan | 1 | 2100.00 | +--------+----------+---------+---------+ 3 rows in set (0.00 sec)
This view is a very simple copy of the original table, there shoudn’t anything in the view definition to stop us updating. Let’s try and change Roger’s dept_id from 1 to 2.
update v_emp set dept_id = 2 where emp_id = 1; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 select * from v_emp; +--------+----------+---------+---------+ | emp_id | emp_name | dept_id | salary | +--------+----------+---------+---------+ | 1 | Roger | 2 | 2000.00 | | 2 | John | 2 | 2500.00 | | 3 | Alan | 1 | 2100.00 | +--------+----------+---------+---------+ 3 rows in set (0.00 sec)
We can see that this worked perfectly and as the view definition for v_emp is about as simple as a view can get this isn’t much of a surprise. Let’s move on to a slightly more complex view, one which we specify only a subset of the data in the underlying table.
create view v_hr_emp as select emp_id, emp_name, dept_id from emps; Query OK, 0 rows affected (0.00 sec) select * from v_hr_emp; +--------+----------+---------+ | emp_id | emp_name | dept_id | +--------+----------+---------+ | 1 | Roger | 2 | | 2 | John | 2 | | 3 | Jane | 1 | +--------+----------+---------+ 3 rows in set (0.00 sec)
So will this view treat updates any differently to the simple view? Let’s set Roger’s dep_id back to 1.
update v_hr_emp set dept_id = 1 where emp_id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 select * from v_hr_emp; +--------+----------+---------+ | emp_id | emp_name | dept_id | +--------+----------+---------+ | 1 | Roger | 1 | | 2 | John | 2 | | 3 | Jane | 1 | +--------+----------+---------+ 3 rows in set (0.00 sec)
Again the update worked perfectly, this is because the view definition is working against a single underlying table, this makes it easy for MySQL to work out exactly which rows in the table to update.
Lets make the view more complicated by adding in a second table.
create view v_emp_detail as -> select emp_id, emp_name, description, salary -> from emps e, dept d -> where e.dept_id = d.dept_id; Query OK, 0 rows affected (0.00 sec) select * from v_emp_detail; +--------+----------+------------------------+---------+ | emp_id | emp_name | description | salary | +--------+----------+------------------------+---------+ | 1 | Roger | Information Technology | 2000.00 | | 2 | John | Sales | 2500.00 | | 3 | Alan | Information Technology | 2100.00 | +--------+----------+------------------------+---------+ 3 rows in set (0.06 sec)
This time we will update Roger’s salary and also his department description to ’IT’.
update v_emp_detail set description = ’IT’, -> salary = 3000.00 where emp_id = 1; ERROR 1393 (HY000): Can not modify more than one base table through a join view ’pers.v_emp_detail’
It’s seems that now we have added an additional table to the view that we can’t now update. But if we look at the error message it says we can’t update more than one base table through a join. That would suggest you could do one at a time. Let’s split our update into 2 parts and see what we get.
update v_emp_detail set salary = 3000.00 where emp_id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update v_emp_detail set description = ’IT’ where emp_id = 1; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 select * from v_emp_detail; +--------+----------+-------------+---------+ | emp_id | emp_name | description | salary | +--------+----------+-------------+---------+ | 1 | Roger | IT | 3000.00 | | 2 | John | Sales | 2500.00 | | 3 | Alan | IT | 2100.00 | +--------+----------+-------------+---------+ 3 rows in set (0.06 sec)
It is therefore possible to update views with more than one table in it’s definition, but we can’t update all of the tables at once. This is fine for people who have access to the view definitions but it may be that some users are restricted to particular views and don’t have access to information to tell them which columns in the view belong to which tables. This is not likely to be a problem as users with this level of restriction are unlikely to be doing updates but it is something to bear in mind.
[edit] Update Restrictions
We did mention that there are some known restrictions when using updates via views. These are as follows...
Union All Views that contain a union all cannot be updated. There should’nt really be a reason why this is the case if the view is based on the underlying tables but MySQL uses a temporary table when processing views with a union all clause.
Union As with union all it’s also not possible to update a view based on a union.
DISTINCT or DISTINCTROW If the view definition contains a distinct or distinct row definition then it’s not possible to update the records. This is also true of view definitions which use the HAVING or GROUP BY clauses.
Views Based on a Non-Updateable View If you have based your view on another view that breaks any of the other rules for updateable views then the new view will also be non-updateable.
Views Based on a Select Query using the same table as the Base table. It’s not possible to update a view that has been based on a select statement that takes advantage of a sub query which refers to the same tables as the base table for the view.
ALGORITHM=TEMPTABLE Views based on temporary tables (such as those using a UNION ALL) are not updateable because they do not refer directly to the underlying table. Therefore is you have explicitly told MySQL to based the table on a temporary table using the ALGORITHM=TEMPTABLE clause then this will not be updateable.
Subqeury in the Select List If the view uses a subquery in the select list it will not be updateable. This restriction does not effect views that contain a subquery in the FROM clause or the WHERE clause only if the subquery is in the select list.
[edit] DELETABLE VIEWS
Now that we have established that updating views is possible, we can turn our attention to deleting records via a view. In reality deleting from views is essentially the same as updating, it has the similar features and also the same restrictions. Lets try to delete a record from a simple view.
create view v_emp as select * from emps; Query OK, 0 rows affected (0.00 sec) select * from v_emp; +--------+----------+---------+-----------+---------+ | emp_id | emp_name | dept_id | dept_name | salary | +--------+----------+---------+-----------+---------+ | 1 | Roger | 1 | NULL | 2000.00 | | 2 | John | 2 | NULL | 2500.00 | | 3 | Alan | 1 | NULL | 2100.00 | +--------+----------+---------+-----------+---------+ 3 rows in set (0.00 sec) delete from v_emp where emp_id = 3; Query OK, 1 row affected (0.30 sec) select * from v_emp; +--------+----------+---------+-----------+---------+ | emp_id | emp_name | dept_id | dept_name | salary | +--------+----------+---------+-----------+---------+ | 1 | Roger | 1 | NULL | 2000.00 | | 2 | John | 2 | NULL | 2500.00 | +--------+----------+---------+-----------+---------+ 2 rows in set (0.00 sec)
So deleting from a single table is no problem. What about a view based on two tables.
create view v_emp_detail as
-> select emp_id, emp_name, description, salary
-> from emps e, dept d
-> where e.dept_id = d.dept_id;
Query OK, 0 rows affected (0.00 sec)
delete from v_emp_detail where emp_id = 2;
ERROR 1395 (HY000): Can not delete from join view ’pers.v_emp_detail’
We are not allowed to delete records form a more complicated view as it contains a join. This is because MySQL cannot be sure which tables your referring to in the delete, do you want to delete just from emps or from the dept table also.
One thing to mention about deleting via views is that it’s possible to delete from a single table view even if it doesn’t contain all the columns from the table it is based upon. So the deletes are allowed on the following view.
create view v_hr_emps as select emp_id, emp_name, dept_id from emps; Query OK, 0 rows affected (0.01 sec) select * from v_hr_emps; +--------+----------+---------+ | emp_id | emp_name | dept_id | +--------+----------+---------+ | 1 | Roger | 1 | | 2 | John | 2 | | 3 | Alan | 1 | +--------+----------+---------+ 3 rows in set (0.00 sec) delete from v_hr_emps where emp_id = 3; Query OK, 1 row affected (0.00 sec) select * from v_hr_emps; +--------+----------+---------+ | emp_id | emp_name | dept_id | +--------+----------+---------+ | 1 | Roger | 1 | | 2 | John | 2 | +--------+----------+---------+ 2 rows in set (0.00 sec)
This behavior may seem a little bit odd but it seems to be standard across other databases. It should be noted however that a user can’t delete a record unless they have the required permissions on the underlying table that the view is based on.
[edit] View updatability
MySQL uses a flag called the view updatability flag when a view is created. This flag is used to signal that the view is or is not updatable. This is done at the time of view creation to improve performance to avoid having to workout each time an update or delete is used against a view. If the flag is set to TRUE then the view is updatables (and deleteable) if it is FALSE then it’s not. MySQL does however allow you to grant update and delete on a view whether the flag is set to TRUE or FALSE but this has no effect on the update and delete operations.
[edit] Insert
Views that are updatable are not by definition insertable. In addition to the restrictions on updates there are another 3 which apply to inserts.
All of the columns in the view have to be simple column types, they can’t be function results or expressions. All of the columns must be distinct, you can’t reference the same column more than once.
Let’s test these out on a view.
create view v_emp as select * from emps; Query OK, 0 rows affected (0.00 sec) insert into v_emp (emp_id,emp_name) values (4,’Larry’); Query OK, 1 row affected (0.00 sec) create view v_emps2 as select emp_id, reverse(emp_name) emp_name from emps; Query OK, 0 rows affected (0.08 sec) insert into v_emps2 (emp_id, emp_name) values (7,’Terry’); ERROR 1288 (HY000): The target table v_emps2 of the INSERT is not updatable create view v_emps3 as select emp_id emp_id1, emp_id emp_id2 from emps; Query OK, 0 rows affected (0.00 sec) insert into v_emps3 (emp_id1) values (9); ERROR 1288 (HY000): The target table v_emps3 of the INSERT is not updatable
The first insert works fine, but the second fails because the view is using a function in it’s select list. The third also fails as the view contains the same column twice. You may have noticed that the error message is a little misleading in that it is telling us the target table is not updatable. This isn’t the case as we can update these tables as shown here.
update v_emps3 set emp_id1 = 9 where emp_id1 = 1; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0
So it’s a little misleading but nothing to get to worried about.
In the MySQL documentation they mention a third restriction as follows.
The view must contain every column in the underlying base table which does not have a default value.*
However when trying to test this I couldn’t get it to go wrong. Take for example the following examples. * I have directly quoted MySQL on this so that there is no change for me to have misquoted or misunderstood the documentation.
create view v_hr_emp as select emp_id, emp_name from emps; Query OK, 0 rows affected (0.03 sec) insert into v_hr_emp (emp_id, emp_name) values (5,’Paul’); Query OK, 1 row affected (0.00 sec) insert into v_hr_emp (emp_id) values (6); Query OK, 1 row affected (0.00 sec)
According to the MySQL documentation these should both fail, but as you can see both work correctly.
We saw that it was possible to update a view with a join but that it was not possible to delete from the same view. It is possible to insert against a view that contains a join but we must use a similar method to the one we used when updating. This involves only using columns in a single table when inserting.
create view v_emp_detail as -> select emp_id, emp_name, description, salary -> from emps e, dept d -> where e.dept_id = d.dept_id; Query OK, 0 rows affected (0.01 sec) insert into v_emp_detail (emp_id, emp_name) values (11,’James’); Query OK, 1 row affected (0.00 sec) insert into v_emp_detail (description) values (’Marketing’); Query OK, 1 row affected (0.03 sec) insert into v_emp_detail (emp_id, description) values (20,’Packing’); ERROR 1393 (HY000): Can not modify more than one base table through a join view ’pers.v_emp_detail’
Therefore you must be careful when inserting into a view which contains joined tables that you only use columns from a single table in an insert statement.
[edit] Check and Alter
[edit] With Check Option
The with check option clause was added to views in release 5.0.3. With Check Option is a constraint that can be used with views when inserting and updating records.
If we look at a simple view which is a duplicate of the emps table.
create view v_emp as select * from emps; Query OK, 0 rows affected (0.00 sec) select * from v_emp; +--------+----------+---------+-----------+---------+ | emp_id | emp_name | dept_id | dept_name | salary | +--------+----------+---------+-----------+---------+ | 1 | Roger | 1 | NULL | 2000.00 | | 2 | John | 2 | NULL | 2500.00 | | 3 | Alan | 1 | NULL | 2100.00 | +--------+----------+---------+-----------+---------+ 3 rows in set (0.01 sec)
In this example we can insert and update any of the records in the table via the view. But what if we wanted to created a view which had restrictions via a where clause.
create view v_it_emp as select * from emps where dept_id = 1; Query OK, 0 rows affected (0.05 sec) select * from v_it_emp; +--------+----------+---------+-----------+---------+ | emp_id | emp_name | dept_id | dept_name | salary | +--------+----------+---------+-----------+---------+ | 1 | Roger | 1 | NULL | 2000.00 | | 3 | Alan | 1 | NULL | 2100.00 | +--------+----------+---------+-----------+---------+ 2 rows in set (0.02 sec)
We now have a view with a where clause which only shows those employees in the IT department. What happens is we try to insert into the table using a value which are filtered out by the where clause, in our case using a dept_id other than 1.
insert into v_it_emp values(4,’Paul’,2,NULL,3000.00); Query OK, 1 row affected (0.03 sec) select * from v_it_emp; +--------+----------+---------+-----------+---------+ | emp_id | emp_name | dept_id | dept_name | salary | +--------+----------+---------+-----------+---------+ | 1 | Roger | 1 | NULL | 2000.00 | | 3 | Alan | 1 | NULL | 2100.00 | +--------+----------+---------+-----------+---------+ 2 rows in set (0.00 sec)
The insert worked correctly but when we select against that view the record isn’t displayed. If we check against the base table we can see if the record was inserted or not.
select * from emps; +--------+----------+---------+-----------+---------+ | emp_id | emp_name | dept_id | dept_name | salary | +--------+----------+---------+-----------+---------+ | 1 | Roger | 1 | NULL | 2000.00 | | 2 | John | 2 | NULL | 2500.00 | | 3 | Alan | 1 | NULL | 2100.00 | | 4 | Paul | 2 | NULL | 3000.00 | +--------+----------+---------+-----------+---------+ 4 rows in set (0.12 sec)
So the record was inserted into the base table but the where clause on the view stops it being returned when selecting against the view. It may be that this behavior is acceptable but it’s easy to see that this wouldn’t always be wanted. This is exactly the situation we would use the WITH CHECK OPTION clause with the view. Let’s drop the view and recreate it using the WITH CHECK OPTION clause.
drop view v_it_emp;
Query OK, 0 rows affected (0.00 sec)
create view v_it_emp as select * from emps where dept_id = 1
-> with check option;
Query OK, 0 rows affected (0.00 sec)
OK now we can run the insert again and see what happens.
delete from emps where emp_id = 4; Query OK, 1 row affected (0.00 sec) insert into v_it_emp values(4,’Paul’,2,NULL,3000.00); ERROR 1369 (HY000): CHECK OPTION failed ’pers.v_it_emp’
This time the insert failed because we have added the WITH CHECK OPTION clause. The error message doesn’t give us exact details of what is exactly wrong with the insert statement but it does allow us to stop inserts via the view which violate the where clause.
[edit] Cascade and Local
When using the WITH CHECK OPTION clause there is are 2 additional keywords which can be used, CASCADED and LOCAL. These are used when the view that is being updated is based on another view. Using CASCADED will evaluate the where clause against both views, but using LOCAL will only evaluate the where clause of the view you are using in the update statement.
Let’s create 2 views based on the emps table both with where clauses.
create view v_emp1 as select * from emps where salary < 3100.00
-> with local check option;
Query OK, 0 rows affected (0.00 sec)
create view v_emp2 as select * from v_emp1 where dept_id = 1
-> with cascaded check option;
Query OK, 0 rows affected (0.00 sec)
As we have used cascaded in the v_emp2 view it will check both it’s own constraint and also that of the view it is based on, in this case v_emp2;
insert into v_emp2 values(4,’Paul’,1,NULL,4000.00); ERROR 1369 (HY000): CHECK OPTION failed ’pers.v_emp2’ insert into v_emp2 values(4,’Paul’,1,NULL,3000.00); Query OK, 1 row affected (0.00 sec)
The first insert fails because it violates the constraint on the underlying view v_emp1. Notice that the error message refers to v_emp2 and not v_emp1 where the constraint that failed is, this isn’t particularly important but it’s worth remembering if you need to debug problems in the future.
So what happens if we change the WITH CHECK OPTION in v_emp3 from CASCADES to LOCAL.
drop view v_emp2; Query OK, 0 rows affected (0.00 sec) create view v_emp2 as select * from v_emp1 where dept_id = 1 with local check option; Query OK, 0 rows affected (0.00 sec) insert into v_emp2 values(5,’Mike’,1,NULL,5000.00); Query OK, 1 row affected (0.00 sec)
This time the constraint on v_emp1 is not evaluated and the insert is allowed to take place. The default value is CASCADED so if you don’t specify it explicitly the view will validate based on all of the views it’s based on.
One final thing to mention when using CASCADED or LOCAL is that it has no effect on table constraints. The WITH CHECK OPTION is only relevant to view where clauses and using LOCAL has no effect on table based check constraints.
insert into v_emp2 values(NULL,’Mike’,1,NULL,5000.00); ERROR 1048 (23000): Column ’emp_id’ cannot be null
[edit] ALTER TABLE
As views are based on tables it’s worth knowing what effect ALTER TABLE has on them. Let’s use a simple select * view to see what effect ALTER TABLE has.
create view v_emp as select * from emps; Query OK, 0 rows affected (0.03 sec)
As we have used select * you might expect that adding a column to the underlying table should then appear in the view. But this is not the case.
alter table emps add age int; Query OK, 5 rows affected (0.07 sec) Records: 5 Duplicates: 0 Warnings: 0 select * from v_emp; +--------+----------+---------+-----------+---------+ | emp_id | emp_name | dept_id | dept_name | salary | +--------+----------+---------+-----------+---------+ | 1 | Roger | 1 | NULL | 2000.00 | | 2 | John | 2 | NULL | 2500.00 | | 3 | Alan | 1 | NULL | 2100.00 | | 4 | Paul | 1 | NULL | 3000.00 | | 5 | Mike | 1 | NULL | 5000.00 | +--------+----------+---------+-----------+---------+ 5 rows in set (0.00 sec)
So while select * creates the view based on the whole table this doesn’t mean that future changes to the table will be replicated in the view. This may mean that you need to rebuild any select * views that are associated with any tables you alter.
So what happens when we drop a column from the table which is present in the view.
alter table emps drop dept_name; Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0 select * from v_emp; ERROR 1356 (HY000): View ’pers.v_emp’ references invalid table(s) or column(s) or function(s)
This time the view fails with an error message (more on that in a moment). So while the view maintains it’s original state with regards to adds there is no attempt by MySQL to remove columns from a view’s select list when columns are dropped.
This is also true if you use MODIFY or CHANGE against any columns which are defined in the view.
alter table emps change salary sal varchar(10); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 select * from v_emp; ERROR 1356 (HY000): View ’pers.v_emp’ references invalid table(s) or column(s) or function(s)
[edit] CHECK TABLE
As we have just seen removing or changing columns in the base table a view is based upon causes the view to become invalid. This is also true if the underlying table is dropped. MySQL offers the ability to check if a view has become invalid. This is done using the following syntax
CHECK TABLE view_name'
So if we wanted to check the status of our view v_emp we do the following.
CHECK TABLE v_emp; +------------+-------+----------+-------------------------------+ | Table | Op | Msg_type | Msg_text | +------------+-------+----------+-------------------------------+ | pers.v_emp | check | error | View ’pers.v_emp’ references | | | | | invalid table(s) or column(s) | | | | | or function(s) | +------------+-------+----------+-------------------------------+ 1 row in set (0.00 sec)
[edit] View Limitations
It may seem odd to mention the limitations of views but some people may be coming from other database systems and have expectations about what views can and can’t do. To be fair it might be better to look at this section as an overview of illegal view definitions rather than limitations.
[edit] Must be based on an existing table
It may sound obvious but when creating a view the base table for the view must exist. The reason this is important to point out is because in Oracle it’s possible to use the FORCE keyword to create a view on a table which currently doesn’t exist. This is done in Oracle so that scripts can be written in such a way that views are created if the are defined before the table they are based on. MySQL has decided not to implement this functionality as it is not part of the SQL standards.
create view v_salary as select * from salary; ERROR 1146 (42S02): Table ’pers.salary’ doesn’t exist
[edit] Parameters and Variables are not allowed
Some databases allow views to be created with a variable as one of the columns or one of the where clause elements. MySQL does not allow this so it’s not possible to create ’dynamic’ views that change based on the contents of a variable.
create view v_emp as select * from emps where dept_id = @id; ERROR 1351 (HY000): View’s SELECT contains a variable or parameter
[edit] Unique names for views and columns
Because views are treated in the same way as tables in MySQL you can’t name a view the same as and existing table. As with tables this is only true for objects in the same namespace (database).
create view emps as select * from emps; ERROR 1066 (42000): Not unique table/alias: ’emps’
In addition to making the view name unique you must also be careful to make sure that column names are unique also. In a normal select statement it’s possible to name any number of columns the same but for SQL used to create views they must be unique. This is logical if you consider that you can’t have two columns with the same name in a create table statement.
create view v_emp as select emp_id, emp_id from emps; ERROR 1060 (42S21): Duplicate column name ’emp_id’
[edit] Views based on Temporary Tables cannot be used
MySQL allows the use of temporary tables to store data for the duration of a connection. It’s simply not possible to create a view against a temporary table. It’s also not possible to create a temporary view which are available in Microsoft SQL Server.
create temporary table temp_emps (emp_id int, emp_name varchar(30)); Query OK, 0 rows affected (0.00 sec) create view v_temp_emps as select * from temp_emps; ERROR 1352 (HY000): View’s SELECT contains a temporary table ’temp_emps’
[edit] =No subqueries in the from clause
MySQL have recognised that this particular problem is a design flaw. The select statement cannot contain a subquery in the from clause, it is possible however to use subqueries in other parts of the view definition.
create view v_emp as select * from (select emp_id from emps) as emps; ERROR 1349 (HY000): View’s SELECT contains a subquery in the FROM clause create view v_emps as select * from emps where dept_id in (select dept_id from dept); Query OK, 0 rows affected (0.03 sec)
[edit] Explain, Order By and Switches
[edit] EXPLAIN
Explain can be used to show information on how a select statement is working. As views are in effect treated as tables by MySQL there was some thought on how EXPLAIN should be handled. The problem comes because EXPLAIN will return information relating to the underlying table rather than using the view name in the EXPLAIN plan. There is the chance that this could confuse users however security has been implemented so that users must be granted the show view privilege to be able to use EXPLAIN against a view. It’s also likely that if you have limited data access to tables via views that you wouldn’t be giving users this privilege so the fact it refers to the base table and not the view is somewhat of a mute point.
create view v_emp as select * from emps; Query OK, 0 rows affected (0.00 sec) explain select * from v_emp; +----+-------------+-------+------+-... | id | select_type | table | type | ... +----+-------------+-------+------+-... | 1 | PRIMARY | emps | ALL | ... +----+-------------+-------+------+-... 1 row in set (0.00 sec)
We can see above that using explain against the view refers to underlying base table rather than the view specifically.
[edit] Order by
MySQL supports order by clauses in view definitions, this is outside of the SQL standards but is supported by some other database management systems. The order by clause is used in exactly the same way as the standard order by.
create view v_emp as
-> select * from emps order by emp_name;
Query OK, 0 rows affected (0.00 sec)
select * from v_emp;
+--------+----------+---------+-----------+---------+
| emp_id | emp_name | dept_id | dept_name | salary |
+--------+----------+---------+-----------+---------+
| 3 | Alan | 1 | NULL | 2100.00 |
| 2 | John | 2 | NULL | 2500.00 |
| 1 | Roger | 1 | NULL | 2000.00 |
+--------+----------+---------+-----------+---------+
3 rows in set (0.04 sec)
The obvious question is what effect does using the order by have when used in a select against the view. In this instance any order by specified in the SQL statement will override the one used in the view definition.
select * from v_emp order by emp_id; +--------+----------+---------+-----------+---------+ | emp_id | emp_name | dept_id | dept_name | salary | +--------+----------+---------+-----------+---------+ | 1 | Roger | 1 | NULL | 2000.00 | | 2 | John | 2 | NULL | 2500.00 | | 3 | Alan | 1 | NULL | 2100.00 | +--------+----------+---------+-----------+---------+ 3 rows in set (0.00 sec)
It may be best however to avoid using order by with view statements. I’ve often worked on system where people have assumed records would be returned in the order data was entered, this is not always the case, adding order by clauses to views could mean people come to rely on that order, if the view is recreated without the order by applications may produce odd results.
[edit] SWITCHES
Switches are additional SQL syntax which gives information to the optimizer or changes the way MySQL deals with situations such as cache and locks. One commonly used switch which you may have used is LIMIT, which limits the number of rows returned by a select statement, you may have also used DISTINCT to group rows together. These are both examples of switches the you might use in a view definition. Lets say we wanted to create a view which only returned the first record from the emps table, we could use this in conjunction with the order by clause we have just looked at to return the person with the highest salary.
create view v_emp as select * from emps order by salary desc limit 1; Query OK, 0 rows affected (0.01 sec) select * from v_emp; +--------+----------+---------+-----------+---------+ | emp_id | emp_name | dept_id | dept_name | salary | +--------+----------+---------+-----------+---------+ | 2 | John | 2 | NULL | 2500.00 | +--------+----------+---------+-----------+---------+ 1 row in set (0.00 sec)
So we can see from this that using switches is useful but they can be a little complex because some switches take precedent over others. For example SQL_CACHE takes precedence over SQL_NO_CACHE so no matter where it’s used the switch used will be SQL_NO_CACHE. At other times the switch lower down the levels will take precedence, for example if we wished to selected more than one row from the view we just created using a different LIMIT amount will not override the LIMIT 1 used in the view definition.
select * from v_emp limit 3; +--------+----------+---------+-----------+---------+ | emp_id | emp_name | dept_id | dept_name | salary | +--------+----------+---------+-----------+---------+ | 2 | John | 2 | NULL | 2500.00 | +--------+----------+---------+-----------+---------+ 1 row in set (0.00 sec)
In addition to switches in the SQL statement it’s also possible to set environmental switches. These change the way SQL is interpreted and can therefore have an effect on the way a view operates. However this is also true of select statements against tables so it’s worth thinking about the consequences of using environment switches within MySQL.
[edit] View Metadata
As with tables there are a number of different ways to view metadata on the views in your database. While there is specific metadata for views most of the methods used to show standard table meta data can be used.
[edit] DESCRIBE and SHOW COLUMNS
One of the most useful and common types of metadata to view is the table definition and column details. This is useful for showing what columns the table contains, what data type they are and a few other useful details. You can use either describe like so
describe v_emp; +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | emp_id | int(11) | NO | | 0 | | | emp_name | varchar(30) | YES | | NULL | | | dept_id | int(11) | YES | | NULL | | | dept_name | varchar(30) | YES | | NULL | | | salary | decimal(7,2) | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
or use show columns in it’s short form
show columns from v_emp; +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | emp_id | int(11) | NO | | 0 | | | emp_name | varchar(30) | YES | | NULL | | | dept_id | int(11) | YES | | NULL | | | dept_name | varchar(30) | YES | | NULL | | | salary | decimal(7,2) | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)
or in it’s longer form which gives some additional information.
show full columns from v_emp; +-----------+--------------+-----------+------+-----+---------+-------+------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-----------+--------------+-----------+------+-----+---------+-------+------------+---------+ | emp_id | int(11) | NULL | NO | | 0 | | select | | | emp_name | varchar(30) | NULL | YES | | NULL | | select | | | dept_id | int(11) | NULL | YES | | NULL | | select | | | dept_name | varchar(30) | NULL | YES | | NULL | | select | | | salary | decimal(7,2) | NULL | YES | | NULL | | select | | +-----------+--------------+-----------+------+-----+---------+-------+------------+---------+ 5 rows in set (0.23 sec)
[edit] SHOW TABLES and show table status
Because views are treated by MySQL almost identically to tables we can use the SHOW TABLES command to give us information on which tables are our database.
show tables; +----------------+ | Tables_in_pers | +----------------+ | dept | | emps | | v_emp | +----------------+ 3 rows in set (0.00 sec)
If we use the keyword FULL with show tables we can then see which of the ’tables’ are in fact views.
show full tables; +----------------+------------+ | Tables_in_pers | Table_type | +----------------+------------+ | dept | BASE TABLE | | emps | BASE TABLE | | v_emp | VIEW | +----------------+------------+ 3 rows in set (0.00 sec)
We can also view metadata on the status of views via the SHOW TABLE STATUS command.
show table status; +----------+--------+---------+...+---------+ | Name | Engine | Version |...+ Comment | +----------+--------+---------+...+---------+ + emps + MyISAM + 10 +...+ NULL | + dept + MyISAM + 10 +...+ NULL | + v_emp + NULL + NULL +...+ view | +----------+--------+---------+...+---------+ 3 rows in set (0.04 sec)
[edit] SHOW CREATE
With show create we have two options, we can either use the standard SHOW CREATE TABLE or the new SHOW CREATE VIEW
show create table v_emp; +-------+-------------------------------------------------------------------+ | View | Create View | +-------+-------------------------------------------------------------------+ | v_emp | CREATE ALGORITHM=UNDEFINED VIEW `pers`.`v_emp` | | | AS select `pers`.`emps`.`emp_id` AS `emp_id`, | | | `pers`.`emps`.`emp_name` AS `emp_name`,`pers`.`emps`.`dept_id` | | | AS `dept_id`,`pers`.`emps`.`dept_name` AS `dept_name`, | | |`pers`.`emps`.`salary` AS `salary` from `pers`.`emps` | | | order by `pers`.`emps`.`salary` desc limit 1 | +-------+-------------------------------------------------------------------+ 1 row in set (0.00 sec) show create view v_emp; +-------+------------------------------------------------------------------+ | View | Create View | +-------+------------------------------------------------------------------+ | v_emp | CREATE ALGORITHM=UNDEFINED VIEW `pers`.`v_emp` | | | AS select `pers`.`emps`.`emp_id` AS `emp_id`, | | | `pers`.`emps`.`emp_name` AS `emp_name`,`pers`.`emps`.`dept_id` | | | AS `dept_id`,`pers`.`emps`.`dept_name` AS `dept_name`, | | | `pers`.`emps`.`salary` AS `salary` from `pers`.`emps` | | | order by `pers`.`emps`.`salary` desc limit 1 | +-------+------------------------------------------------------------------+ 1 row in set (0.00 sec)
[edit] Information Schema
So far the information on the view meta data has been short. In version 5 and up of MySQL there is now support for the information schema, this gives a much better way of getting metadata about views because it uses a database style approach which means we can use select statements to present the view in a format which suits our needs rather than having to make do with the standard show column lists.
The information schema contains a number of tables but the one we are interested in is called VIEWS. You can query the information schema either by using USE INFORMATION_SCHEMA to connect to the information_schema database or you can simply qualify each call to a table in the database by adding the database name to the table name. To see what’s available for selection in the VIEWS table we can use DESCRIBE.
describe views; +-----------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(1024) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | VIEW_DEFINITION | longtext | NO | | | | | CHECK_OPTION | varchar(8) | NO | | | | | IS_UPDATABLE | varchar(3) | NO | | | | +-----------------+---------------+------+-----+---------+-------+ 6 rows in set (0.01 sec)
Using this information we can then construct SQL statements to view the view metadata. So to simply list all of the view names we can issue the following select statement.
select table_name from information_schema.views; +------------+ | table_name | +------------+ | v_emp | | v_emp1 | | v_emp2 | | v_it_emp | +------------+ 4 rows in set (0.35 sec)
We can also add a where clause or order by to filter and sort the data. For example we might want to view the view definition for our v_emp view.
select view_definition from information_schema.views
-> where table_name = ’v_emp’;
+--------------------------------------------+
| view_definition |
+--------------------------------------------+
| select `pers`.`emps`.`emp_id` AS `emp_id`, |
| `pers`.`emps`.`emp_name` AS `emp_name`, |
| `pers`.`emps`.`dept_id` AS `dept_id`, |
| `pers`.`emps`.`dept_name` AS `dept_name`, |
| `pers`.`emps`.`salary` AS `salary` |
| from `pers`.`emps` |
| order by `pers`.`emps`.`salary` |
| desc limit 1 |
+--------------------------------------------+
1 row in set (0.04 sec)
In addition to the VIEWS table we can also select information from the COLUMNS information schema table. The COLUMNS as the name suggests contains details of the columns contained within the view. Again we can use the DESCRIBE command to view which columns are available for selection.
desc information_schema.columns; +--------------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+---------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(1024) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | COLUMN_NAME | varchar(64) | NO | | | | | ORDINAL_POSITION | bigint(21) | NO | | 0 | | | COLUMN_DEFAULT | varchar(64) | YES | | NULL | | | IS_NULLABLE | varchar(3) | NO | | | | | DATA_TYPE | varchar(64) | NO | | | | | CHARACTER_MAXIMUM_LENGTH | bigint(21) | YES | | NULL | | | CHARACTER_OCTET_LENGTH | bigint(21) | YES | | NULL | | | NUMERIC_PRECISION | bigint(21) | YES | | NULL | | | NUMERIC_SCALE | bigint(21) | YES | | NULL | | | CHARACTER_SET_NAME | varchar(64) | YES | | NULL | | | COLLATION_NAME | varchar(64) | YES | | NULL | | | COLUMN_TYPE | longtext | NO | | | | | COLUMN_KEY | varchar(3) | NO | | | | | EXTRA | varchar(20) | NO | | | | | PRIVILEGES | varchar(80) | NO | | | | | COLUMN_COMMENT | varchar(255) | NO | | | | +--------------------------+---------------+------+-----+---------+-------+ 19 rows in set (0.00 sec)
These can then be used to create select statements to view metadata on our views. The columns table doesn’t explicitly mention views so all we need to do is use the view name against the TABLE_NAME column. So if we wanted to see the the columns contained in our v_emp view we can issue the following select.
select column_name from information_schema.columns
-> where table_name = ’v_emp’;
+-------------+
| column_name |
+-------------+
| emp_id |
| emp_name |
| dept_id |
| dept_name |
| salary |
+-------------+
5 rows in set (0.01 sec)