Views

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.

 

How to do it...

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...