w3resource

MySQL Views

Views

View is a data object which does not contain any data. Contents of the view are the resultant of a base table. They are operated just like base table but they don’t contain any data of their own. The difference between a view and a table is that views are definitions built on top of other tables (or views). If data is changed in the underlying table, the same change is reflected in the view. A view can be built on top of a single or multiple tables.

Version: MySQL 5.6

Contents:

Why views?

  • Views can be effective copies of base tables.
  • Views can have column names and expressions.
  • You can use any clauses in views.
  • Views can be used in INSERT/UPDATE/DELETE.
  • Views can contain expressions in the select list.
  • Views can be views of views.

MySQL Views need Version 5.0 or higher

To get views to work, you'll need to upgrade to MySQL version 5.0 (or higher). You can check your MySQL version in the following way:

mysql>SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.12    |
+-----------+
1 row in set (0.00 sec)

Check the privileges of the current user:

The CREATE VIEW statement requires the CREATE VIEW privilege for the view and some privilege for each column selected by the SELECT statement. The following command shows the user privileges.

mysql> SHOW PRIVILEGES;
+-----------------+----------------------------+-------------------------------------------------------+
| Privilege       | Context                    | Comment                                               |
+-----------------+----------------------------+-------------------------------------------------------+
| Alter           | Tables                     | To alter the table                                    |
| Alter routine   | Functions,Procedures       | To alter or drop stored functions/procedures          |
| Create          | Databases,Tables,Indexes   | To create new databases and tables                    |
| Create routine  | Databases                  | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary| Databases                  | To use CREATE TEMPORARY TABLE                         |
| tables          |                            |                                                       |
| Create view     | Tables                     | To create new views                                   |
| Create user     | Server Admin               | To create new users                                   |
| Delete          | Tables                     | To delete existing rows                               |
| Drop            | Databases,Tables           | To drop databases, tables, and views                  |
| Event           | Server Admin               | To create, alter, drop and execute events             |
| Execute         | Functions,Procedures       | To execute stored routines                            |
| File            | File access on server      | To read and write files on the server                 |
| Grant option    | Databases,Tables,          | To give to other users those privileges you possess   |
|                 | Functions,Procedures       |                                                       |
| Index           | Tables                     | To create or drop indexes                             |
| Insert          | Tables                     | To insert data into tables                            |
| Lock tables     | Databases                  | To use LOCK TABLES (together with SELECT privilege)   |
| Process         | Server Admin               | To view the plain text of currently executing queries |
| Proxy           | Server Admin               | To make proxy user possible                           |
| References      | Databases,Tables           | To have references on tables                          |
| Reload          | Server Admin               | To reload or refresh tables, logs, and privileges     |
| Replication     | Server Admin               | To ask where the slave or master servers are          |
| client          |                            |                                                       |
| Replication     | Server Admin               | To read binary log events from the master             |
| slave           |                            |                                                       |
| Select          | Tables                     | To retrieve rows from table                           |
| Show databases  | Server Admin               | To see all databases with SHOW DATABASES              |
| Show view       | Tables                     | To see views with SHOW CREATE VIEW                    |
| Shutdown        | Server Admin               | To shut down the server                               |
| Super           | Server Admin               | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger         | Tables                     | To use triggers                                       |
| Create          | Server Admin               | To create/alter/drop tablespaces                      |
| tablespace      |                            |                                                       |
| Update          | Tables                     | To update existing rows                               |
| Usage           | Server Admin               | No privileges - allow connecting only                 |
+-------------------------+--------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)

Select a database :

Before creating a view we must select a database. Following command shows the list of databases.

mysql> SHOW DATBASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hr                 |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
7 rows in set (0.06 sec)

Now select the database 'hr' and list the tables:

mysql> USE hr;
Database changed
mysql> SHOW TABLES;
+--------------+
| Tables_in_hr |
+--------------+
| alluser      |
| departments  |
| emp_details  |
| job_history  |
| jobs         |
| locations    |
| regions      |
| user         |
| user_details |
+--------------+
9 rows in set (0.00 sec)

Create View

Following statements create a view. By default, a view is associated with the default database (currently used the database). To associate the view with a given database, specify the name as database_name. view_name when you create it. Here is the complete syntax :

Syntax:

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

Explanation:

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

The CREATE VIEW statement creates a new view.

view_name: view_name is the name of the view. A view always belongs to a database. By default, a new view is created in the currently used database. The view name may also be used with the database name, as database_name.view_name, but it is unnecessary if database_name is the default database.

select_statement: The select_statement is a SELECT statement and provides the definition of the view. select_statement can select data from base tables or other views.

Example:

mysql> USE hr;
Database changed
mysql> CREATE VIEW my_v1 AS SELECT * FROM user_details;
Query OK, 0 rows affected (0.13 sec)

column_list: The column_list part is optional. It provides a list of names for the view's columns right after the view name where the names must be unique. The number of names in column_list must be the same as the number of columns retrieved by the SELECT statement. If you want to give your view columns a different name, you can do so by adding an [AS name] clause in the select list.

Example: View without column_list

mysql> SELECT * FROM user;
+----------+-----------+--------+
| userid   | password  | name   |
+----------+-----------+--------+
| scott123 | 123@sco   | Scott  |
| ferp6734 | dloeiu@&3 | Palash |
| diana094 | ku$j@23   | Diana  |
+----------+-----------+--------+
3 rows in set (0.04 sec)
mysql> CREATE VIEW my_v2 AS SELECT * FROM user;
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT * FROM my_v2;
+----------+-----------+--------+
| userid   | password  | name   |
+----------+-----------+--------+
| scott123 | 123@sco   | Scott  |
| ferp6734 | dloeiu@&3 | Palash |
| diana094 | ku$j@23   | Diana  |
+----------+-----------+--------+
3 rows in set (0.05 sec)

Now specify the columns name in the above view:

mysql> CREATE VIEW my_v3 AS SELECT userid AS User_ID, password AS Password, name AS Name  FROM user;
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT * FROM my_v3;
+----------+-----------+--------+
| User_ID  | Password  | Name   |
+----------+-----------+--------+
| scott123 | 123@sco   | Scott  |
| ferp6734 | dloeiu@&3 | Palash |
| diana094 | ku$j@23   | Diana  |
+----------+-----------+--------+
3 rows in set (0.04 sec)
CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

OR REPLACE: If the optional OR REPLACE clause is added with CREATE VIEW statement, the CREATE VIEW statement replaces an existing view and create a new one. If the view does not exist, CREATE VIEW is the same as CREATE OR REPLACE VIEW.

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

- ALGORITHM : The ALGORITHM clause is optional, it affects how MySQL processes the view. ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED. The default algorithm is UNDEFINED.

[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }] :
The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at view invocation time.

If you specify the DEFINER clause, the following rules determine the legal DEFINER user values :

  • If you do not have the SUPER privilege, the only legal user value is your own account and you cannot set the definer to some other account.
  • If you have the SUPER privilege, you can specify any syntactically legal account name.

Within a stored routine that is defined with the SQL SECURITY DEFINER characteristic, CURRENT_USER returns the routine's DEFINER value. This also affects a view defined within such a routine, if the view definition contains a DEFINER value of CURRENT_USER.

[WITH [CASCADED | LOCAL] CHECK OPTION] : The WITH CHECK OPTION clause can be given for an updatable view to preventing inserts or updates to rows except those for which the WHERE clause in the select_statement is true. In a WITH CHECK OPTION clause for an updatable view, the LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. The LOCAL keyword restricts the CHECK OPTION only to the view being defined. CASCADED causes the checks for underlying views to be evaluated as well. When neither keyword is given, the default is CASCADED.

Restrictions on View definition

  • The SELECT statement cannot contain a subquery in the FROM clause.
  • The SELECT statement cannot refer to system or user variables.
  • Within a stored program, the definition cannot refer to program parameters or local variables.
  • The SELECT statement cannot refer to prepared statement parameters.
  • Any table or view referred to in the definition must exist.
  • The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view.
  • Any tables named in the view definition must exist at definition time.
  • You cannot associate a trigger with a view.
  • Aliases for column names in the SELECT statement are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).

Tools to create MySQL Views

You can write a procedure in MySQL command line tool or you can use MySQL workbench which is an excellent front-end tool (here we have used version 5.3 CE).

MySQL command line tool:

Select MySQL command Client from Start menu:

MySQL command prompt

Selecting MySQL command prompt following screen will come:

mysql5.6 command prompt password

After a successful login you can access the MySQL command prompt:

mysql5.6 command line client

Now you can create and run your own view, see the following example:

mysql write and execute view in command line

MySQL workbench (5.3 CE):

Select MySQL workbench from Start menu:

mysql workbench start

After selecting MySQL workbench following login screen will come:

mysql workbench 5.2

Now input the login details:

mysql 5.6 workbench login

After successful login, a new screen will come and from the object browser panel selects a database:

mysql 5.6 workbench select database

After selecting the database right click on Views, a new popup will come:

mysql 5.6 workbench selece views

After selecting "Create View " following screen will come where you can write your own view.

mysql 5.6 workbench create views

After writing the view click on Apply button and the following screen will come:

mysql 5.6 workbench save view

Next screen will be to review the script and apply on the database.

mysql 5.6 workbench apply save view

Now click on Finish button and run the view:

mysql 5.6 workbench run view

Alter a view

ALTER VIEW statement changes the definition of an existing view. The syntax of the statement is similar to CREATE VIEW.

Syntax:

    ALTER 
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

This statement requires the CREATE VIEW and DROP privileges for the view, and some privilege for each column referred to in the SELECT statement.

Drop a view

DROP VIEW statement is used to remove one or more views. To drop a view, you must have DROP privilege for each view. Here is the syntax :

Syntax:

DROP VIEW [IF EXISTS]      
     view_name [, view_name] ...      
	 [RESTRICT | CASCADE]

The IF EXISTS clause prevents an error from occurring for views that don't exist.

MySQL CREATE VIEW with WHERE

CREATE VIEW command can be used with WHERE clause.

Example:

Sample table: author


CREATE VIEW view_author 
AS SELECT * 
FROM author 
WHERE country='USA'

The above MySQL statement will create a view 'view_author' taking records (for all columns) of author table if those records contain the value 'USA' for country column.

MySQL CREATE VIEW with AND and OR

CREATE VIEW command can be used with AND and OR operators.

Example:

Sample table: publisher


CREATE VIEW view_publisher 
AS SELECT pub_name,pub_city,country
FROM publisher	
WHERE (country='USA' AND pub_city='New York')
OR 	(country='India' AND pub_city='Mumbai');

The above MySQL statement will create a view 'view_publisher' taking records for pub_name, pub_city and country columns of publisher table, if (A)(i)value of the country column is the USA, and (ii)value of the pub_city is New York; or (B)(i)value of the country column is INDIA, and (ii)value of the pub_city is Mumbai.

MySQL CREATE VIEW with GROUP BY

CREATE VIEW command can be used with GROUP BY clause.

Example:

Sample table: book_mast


CREATE VIEW view_bookmast
AS SELECT pub_lang, count(*)
FROM book_mast 
GROUP BY pub_lang

The above statement will create a view 'view_bookmast' taking all records grouped w.r.t. pub_lang, from pub_lang and number of books for each language (pub_lang).

MySQL CREATE VIEW with ORDER BY

CREATE VIEW command can be used with ORDER BY clause.

Example:

Sample table: book_mast


CREATE VIEW view_bookmast
AS SELECT pub_lang,count(*) 
FROM book_mast 
GROUP BY pub_lang 	ORDER BY pub_lang;

The above MySQL statement will create a view 'view_bookmast' taking all the records grouped w.r.t. pub_lang and sorted against pub_lang, from pub_lang and number of books for each language (pub_lang) of book_mast table.

MySQL CREATE VIEW with BETWEEN and IN

CREATE VIEW command can be used with BETWEEN and IN operator.

Example:

Sample table: book_mast


CREATE VIEW view_bookmast
AS SELECT *
FROM book_mast
WHERE book_name BETWEEN 'A' AND 'G' 
AND no_page IN(165,250,350,400,510);

The above statement will create a view 'view_bookmast' taking all the records of book_mast table, if (A)name of the book (book_name) starts with any of the characters from 'A' through 'G' and (B) number of pages (no_page) are any of the following 165, 250, 350, 400, 510.

MySQL CREATE VIEW with LIKE

CREATE VIEW command can be used with LIKE operator.

Example:

Sample table: author


CREATE VIEW view_author 
AS SELECT *
FROM author
WHERE aut_name  
NOT LIKE 'T%' AND aut_name NOT LIKE 'W%';

The above MySQL statement will create a view 'view_author' taking all the records of author table if (A)name of the author (aut_name) does not start with 'T' and (B) name of the author (aut_name) does not start with 'W'.

MySQL CREATE VIEW using subqueries

CREATE VIEW command can be used with subqueries.

Example:

Sample table: purchase


Sample table: book_mast

CREATE VIEW view_purchase 
AS SELECT invoice_no,book_name,cate_id 
FROM purchase	
WHERE cate_id= 	(SELECT cate_id FROM book_mast WHERE no_page=201);

The above MySQL statement will create a view 'view_purchase' taking all the records of invoice_no, book_name and cate_id columns of purchase table, if category id (cate_id) satisfies the condition defined within a subquery (followed by cate_id=).

The subquery retrieves only cate_ids from book_mast table, which contain books with 201 pages.

MySQL CREATE VIEW with JOIN

CREATE VIEW command can be used along with a JOIN statement.

Example:

Sample table: category


Sample table: purchase


CREATE VIEW view_purchase 	
AS SELECT a.cate_id,a.cate_descrip, b.invoice_no,
b.invoice_dt,b.book_name        
FROM category a,purchase b 
WHERE a.cate_id=b.cate_id;

The above MySQL statement will create a view 'view_purchase' along with a JOIN statement.

The JOIN statement here retrieves cate_id, cate_descrip from category table and invoice_no, invoice_dt, and book_name from purchase table if cate_id of category table and that of purchase are same.

MySQL CREATE VIEW with UNION

CREATE VIEW command can be used with UNION.

Example:

Sample table: book_mast


CREATE VIEW view_bookmast AS
SELECT * 
FROM book_mast
WHERE pub_id='P001' UNION
SELECT * 
FROM book_mast	
WHERE book_name BETWEEN 'A' AND 'G' UNION
SELECT *
FROM book_mast 
WHERE no_page IN(165,250,350,400,510);

The above MySQL statement will create a view 'view_bookmast' contains columns as in the 'book_mast'.

The records will be inserted with the union of three subqueries.

The first query inserts those rows into the 'view_bookmast' view from the 'book_mast' table whose 'pub_id' is 'P001'.

The second query inserts those rows into the 'view_bookmast' view from the 'book_mast' table whose rows have the 'book_name' column beginning with any letter between 'A' to 'G'.

The third query inserts those rows into the 'view_bookmast' view from the 'book_mast' table whose rows have any of the following values 165,250,350,400,510 in 'no_page'.

Online Practice Editor:

Previous: MySQL Transaction
Next: MySQL Security



Follow us on Facebook and Twitter for latest update.