MOCKSTACKS
EN
Questions And Answers

More Tutorials









MYSQL VIEW

Create a View

Privileges


The CREATE VIEW statement requires the CREATE VIEW privilege for the view, and some privilege for each column selected by the SELECT statement. For columns used elsewhere in the SELECT statement, you must have the SELECT privilege. If the OR REPLACE clause is present, you must also have the DROP privilege for the view. CREATE VIEW might also require the SUPER privilege, depending on the DEFINER value, as described later in this section.

When a view is referenced, privilege checking occurs.

A view belongs to a database. By default, a new view is created in the default database. To create the view explicitly in a given database, use a fully qualified name

For Example:

db_name.view_name

mysql> CREATE VIEW test.v AS SELECT * FROM t;

Note - Within a database, base tables and views share the same namespace, so a base table and a view cannot have the same name.

A VIEW can:

.be created from many kinds of SELECT statements
.refer to base tables or other views
.use joins, UNION, and subqueries
.SELECT need not even refer to any tables


Another Example


The following example defines a view that selects two columns from another table as well as an expression calculated from those columns:

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 3 | 50 | 150 |
+------+-------+-------+

Restrictions


.Before MySQL 5.7.7, the SELECT statement cannot contain a subquery in the FROM clause.
.The SELECT statement cannot refer to system variables or user-defined variables.
.Within a stored program, the SELECT statement 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. After the view has been created, it is possible to drop a table or view that the definition refers to. In this case, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement.
.The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view.
.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).
.A VIEW may or may not optimize as well as the equivalent SELECT. It is unlikely to optimize any better.


Conclusion

In this page (written and validated by ) you learned about MYSQL VIEW . What's Next? If you are interested in completing MYSQL tutorial, your next topic will be learning about: MYSQL Table Creation.



Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.


Share On:


Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.