{"id":555,"date":"2009-12-27T13:42:04","date_gmt":"2009-12-27T13:42:04","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=555"},"modified":"2024-01-04T20:09:32","modified_gmt":"2024-01-05T03:09:32","slug":"mysql-views","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-views\/","title":{"rendered":"MySQL Views"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL views and how to manipulate views effectively.<\/p>\n\n\n\n

Introduction to MySQL Views<\/h2>\n\n\n\n

Let’s see the following tables customers<\/code> and payments<\/code> from the sample database<\/a>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

This query<\/a> returns data from both tables customers<\/code> and payments<\/code> using the inner join<\/a>:<\/p>\n\n\n

SELECT<\/span> \n    customerName, \n    checkNumber, \n    paymentDate, \n    amount\nFROM<\/span>\n    customers\nINNER<\/span> JOIN<\/span>\n    payments USING<\/span> (customerNumber);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Next time, if you want to get the same information including customer name, check number, payment date, and amount, you need to issue the same query again.<\/p>\n\n\n\n
One way to do this is to save the query in a file, either .txt or .sql file so that later you can open and execute it from MySQL Workbench or any other MySQL client tools.<\/p>\n\n\n\n
A better way to do this is to save the query in the database server and assign a name to it. This named query is called a database view,<\/strong> or simply, view<\/strong>.<\/p>\n\n\n\n
By definition, a view is a named query stored in the database catalog.<\/p>\n\n\n\n
To create a new view you use the CREATE VIEW<\/a><\/code> statement. This statement creates a view customerPayments<\/code> based on the above query above:<\/p>\n\n\n
CREATE<\/span> VIEW<\/span> customerPayments\nAS<\/span> \nSELECT<\/span> \n    customerName, \n    checkNumber, \n    paymentDate, \n    amount\nFROM<\/span>\n    customers\nINNER<\/span> JOIN<\/span>\n    payments USING<\/span> (customerNumber);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
After you execute the CREATE VIEW<\/code> statement, MySQL creates the view and stores it in the database.<\/p>\n\n\n\n
Now, you can reference the view as a table in SQL statements. For example, you can query data from the customerPayments<\/code> view using the SELECT<\/code> statement:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> customerPayments;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
As you can see, the syntax is much simpler.<\/p>\n\n\n\n
MySQL allows you to create a view based on a SELECT<\/code> statement that retrieves data from one or more tables. This picture illustrates a view based on columns of multiple tables:<\/p>\n\n\n
\n
\"MySQL<\/figure>\n<\/div>\n\n\n
In addition, MySQL even allows you to create a view that does not refer to any table. But you will rarely find this kind of view in practice.<\/p>\n\n\n\n
For example, you can create a view called daysofweek<\/code> that return 7 days a week by executing the following query:<\/p>\n\n\n
CREATE<\/span> VIEW<\/span> daysofweek (day<\/span>) AS<\/span>\n    SELECT<\/span> 'Mon'<\/span> \n    UNION<\/span> \n    SELECT<\/span> 'Tue'<\/span>\n    UNION<\/span> \n    SELECT<\/span> 'Web'<\/span>\n    UNION<\/span> \n    SELECT<\/span> 'Thu'<\/span>\n    UNION<\/span> \n    SELECT<\/span> 'Fri'<\/span>\n    UNION<\/span> \n    SELECT<\/span> 'Sat'<\/span>\n    UNION<\/span> \n    SELECT<\/span> 'Sun'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
You can query data from the daysofweek<\/code> view as follows:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> daysofweek;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This picture shows the output:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Advantages of MySQL Views<\/h2>\n\n\n\n
MySQL views bring the following advantages.<\/p>\n\n\n\n
1) Simplify complex query<\/h3>\n\n\n\n
Views help simplify complex queries. If you have any frequently used complex query, you can create a view based on it so that you can reference the view by using a simple SELECT<\/code> statement instead of typing the query all over again.<\/p>\n\n\n\n
2) Make the business logic consistent<\/h3>\n\n\n\n
Suppose you have to repeatedly write the same formula in every query.  Or you have a query that has complex business logic. To make this logic consistent across queries, you can use a view to store the calculation and hide the complexity.<\/p>\n\n\n\n
3) Add extra security layers<\/h3>\n\n\n\n
A table may expose a lot of data including sensitive data such as personal and banking information.<\/p>\n\n\n\n
By using views and privileges, you can limit which data users can access by exposing only the necessary data to them.<\/p>\n\n\n\n
For example, the table employees<\/code> may contain SSN and address information, which should be accessible by the HR department only.<\/p>\n\n\n\n
To expose general information such as first name, last name, and gender to the General Administration (GA) department, you can create a view based on these columns and grant the users of the GA department the view, not the entire table employees<\/code> .<\/p>\n\n\n\n
4) Enable backward compatibility<\/h3>\n\n\n\n
In legacy systems, views can enable backward compatibility.<\/p>\n\n\n\n
Suppose, you want to normalize a big table into many smaller ones. And you don’t want to impact the current applications that reference the table.<\/p>\n\n\n\n
In this case, you can create a view whose name is the same as the table based on the new tables so that all applications can reference the view as if it were a table.<\/p>\n\n\n\n
Note that a view and table cannot have the same name so you need to drop the table<\/a> first before creating a view whose name is the same as the deleted table.<\/p>\n\n\n\n
Managing views in MySQL<\/h2>\n\n\n\n
Note that a view does not physically store the data. When you issue the SELECT<\/a><\/code> statement against the view, MySQL executes the underlying query specified in the view’s definition and returns the result set. For this reason, sometimes, a view is referred to as a virtual table.<\/p>\n\n\n\n