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?
- How to Create MySQL View?
- Restrictions on View definition
- Tools to create MySQL Views
- Alter a MySQL view
- DROP a MySQL view
- MySQL CREATE VIEW with WHERE
- MySQL CREATE VIEW with AND and OR
- MySQL CREATE VIEW with GROUP BY
- MySQL CREATE VIEW with ORDER BY
- MySQL CREATE VIEW with BETWEEN and IN
- MySQL CREATE VIEW with LIKE
- MySQL CREATE VIEW using subqueries
- MySQL CREATE VIEW with JOIN
- MySQL CREATE VIEW with UNION
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:
Selecting MySQL command prompt following screen will come:
After a successful login you can access the MySQL command prompt:
Now you can create and run your own view, see the following example:
MySQL workbench (5.3 CE):
Select MySQL workbench from Start menu:
After selecting MySQL workbench following login screen will come:
Now input the login details:
After successful login, a new screen will come and from the object browser panel selects a database:
After selecting the database right click on Views, a new popup will come:
After selecting "Create View " following screen will come where you can write your own view.
After writing the view click on Apply button and the following screen will come:
Next screen will be to review the script and apply on the database.
Now click on Finish button and run the 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
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/mysql/mysql-views.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics