View is a virtual table based on the result-set of an SQL statement. It will also have rows and columns just like a real table, but few restrictions, which will be discussed later. Views hide the SQL complexity and, more importantly, provide additional security.
Suppose you want to give access only to the emp_no
and salary
columns of the salaries
table, and from_date
is after 2002-01-01
. For this, you can create a view with the SQL that gives the required result.
mysql> CREATE ALGORITHM=UNDEFINED
DEFINER=`root`@`localhost`
SQL SECURITY DEFINER VIEW salary_view
AS
SELECT emp_no, salary FROM salaries WHERE from_date > '2002-01-01';
Now the salary_view
view is created and you can query it just like any other table:
mysql> SELECT emp_no, AVG(salary) as avg FROM salary_view GROUP BY emp_no ORDER BY avg DESC LIMIT 5;
You can see that the view has access to particular rows (that is, from_date > '2002-01-01'
) and not all of the rows. You can use the view to restrict...