×
☰ See All Chapters

MySQL Views

A view is a logical representation of the data in the table. You can consider view as a virtual table. Views don’t require physical storage. A view is actually a composition of a table in the form of a predefined query, which is stored in the database. Using view you can create a representation of a table that contains selected columns instead of all columns. A view can contain all rows of a table or select rows from a table.  You can create a view from another view.

If a table that created a view is dropped, the view becomes inaccessible. You receive an error when trying to query against the view.

mysql-views-0
 

Advantages of views

  1. It helps in providing security i.e. it helps to hide columns having sensitive info in the underlying table. Views can be utilized as a form of security in the database. Let’s say you have a table called EMPLOYEE_TBL. EMPLOYEE_TBL includes employee names, addresses, phone numbers, emergency contacts, department, position, and salary or hourly pay. You have some temporary help come in to write a report of employees’ names, addresses, and phone numbers. If you give access to EMPLOYEE_TBL to the temporary help, they can see how much each of your employees receives in compensation—you do not want this to happen. To prevent that, you have created a view containing only the required information: employee name, address, and phone numbers. You can then give the temporary help access to the view to write the report without giving them access to the compensation columns in the table. 

  2. Reduces writing complicated join queries. 

  3. Reduces data redundancy. 

Creating Views

Views are created using the CREATE VIEW statement. You can create views from a single table, multiple tables, or another view. To create a view, a user must have the appropriate system privilege according to the specific implementation. If parent tables or views are dropped then child views are invalid. You cannot use the ORDER BY clause in the CREATE VIEW statement; however, the GROUP BY clause has the same effect as an ORDER BY clause when it’s used in the CREATE VIEW statement.

Syntax

CREATE VIEW <VEIW_NAME>

AS

{SELECT_QUERY}

Actual syntax is:

CREATE [RECURSIVE]VIEW VIEW_NAME

[COLUMN NAME [,COLUMN NAME]]

[OF UDT NAME [UNDER TABLE NAME]

[REF IS COLUMN NAME SYSTEM GENERATED |USER GENERATED | DERIVED]

[COLUMN NAME WITH OPTIONS SCOPE TABLE NAME]]

 AS

{SELECT STATEMENT}

[WITH [CASCADED | LOCAL] CHECK OPTION]

mysql-views-1

mysql-views-2

Views Examples

Creating table for demonstrating views

CREATE TABLE DEPT

       (DEPTNO NUMERIC(2) ,

        DNAME VARCHAR(14) ,

        LOC VARCHAR(13),

CONSTRAINT PK_DEPT PRIMARY KEY(DEPTNO)

 ) ;

 

CREATE TABLE EMP

       (EMPNO NUMERIC(4) ,

        ENAME VARCHAR(10),

        JOB VARCHAR(9),

        MGR NUMERIC(4),

        HIREDATE DATE,

        SAL NUMERIC(7,2),

        COMM NUMERIC(7,2),

        DEPTNO NUMERIC(2),

 CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO),

CONSTRAINT PK_EMP PRIMARY KEY(EMPNO)

 

);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');

INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');

INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');

INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

 

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);

INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);

INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);

INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);

INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);

INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);

INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);

INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-4-19',3000,NULL,20);

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);

INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);

INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-5-23',1100,NULL,20);

INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);

INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);

INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);

COMMIT;

Example 1:

CREATE VIEW EMP_VEIW1

AS

SELECT ENAME, JOB, SAL FROM EMP WHERE SAL > ANY(SELECT SAL FROM EMP

WHERE DEPTNO = 20) AND DEPTNO <> 20;

 

select * from EMP_VEIW1;

mysql-views-3

Example 2:

CREATE VIEW EMP_VEIW2

AS

SELECT E.ENAME , D.DNAME, E.JOB

FROM DEPT D LEFT OUTER JOIN EMP E

ON D.DEPTNO = E.DEPTNO

UNION

SELECT E.ENAME , D.DNAME, E.JOB

FROM DEPT D RIGHT OUTER JOIN EMP E

ON D.DEPTNO = E.DEPTNO;

 

SELECT * FROM EMP_VEIW2;

mysql-views-4
 

SELECT ENAME, JOB

FROM EMP_VEIW2

WHERE JOB='SALESMAN'

mysql-views-5
 

Example to create from other view

CREATE VIEW EMP_VEIW3

AS

SELECT ENAME, JOB

FROM EMP_VEIW2

WHERE JOB='SALESMAN';

 

SELECT * FROM EMP_VEIW3;

mysql-views-6

Updating Data through a View

To update through the view columns must be simple column references and not derived columns. A derived column is one that is not a simple column reference but is derived from an expression.

To be more specific, a view is not updatable if it contains any of the following:

  • Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth) 

  • DISTINCT 

  • GROUP BY 

  • HAVING 

  • UNION or UNION ALL 

  • Subquery in the select list 

  • Certain joins (see additional join discussion later in this section) 

  • Nonupdatable view in the FROM clause 

  • A subquery in the WHERE clause that refers to a table in the FROM clause 

  • Refers only to literal values (in this case, there is no underlying table to update) 

  • Uses ALGORITHM = TEMPTABLE (use of a temporary table always makes a view nonupdatable)  

  • Multiple references to any column of a base table. 

With respect to insert ability (being updatable with INSERT statements), an updatable view is insertable if it also satisfies these additional requirements for the view columns:

  • There must be no duplicate view column names. 

  • The view must contain all columns in the base table that do not have a default value. 

  • A view that has a mix of simple column references and derived columns is not insertable, but it can be updatable if you update only those columns that are not derived.  

CREATE VIEW DEPT_VEIW

AS

SELECT *

FROM DEPT

 

 

mysql-views-7
 

INSERT INTO DEPT_VEIW  (DEPTNO, DNAME,LOC) VALUES(50,'EXECUTION','INDIA');

 

mysql-views-8
 

WITH CHECK OPTION

WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of WITH CHECK OPTION is to ensure that all UPDATE and INSERT commands satisfy the condition(s) in the view definition. If they do not satisfy the condition(s), the UPDATE or INSERT returns an error. WITH CHECK OPTION actually enforces referential integrity by checking the view’s definition to see that it is not violated.

CREATE VIEW DEPT_VEIW

AS

SELECT *

FROM DEPT

WHERE DNAME IS NOT NULL

WITH CHECK OPTION;

INSERT INTO DEPT_VEIW  VALUES(50,NULL,'INDIA');

 

 

WITH CHECK OPTION in this case should deny the entry of any NULL values in the view’s DNAME column because the view is defined by data that does not have a NULL value in the DNAME column.

Dropping a View

Dropping view is simple and below the syntax to drop views:

DROP VIEW  <VIEW_NAME>;

 

All Chapters
Author