MySQL views

Why use views, View rules and restrictions.

Views use:

1. Same table can be created in different forms
2. Can hide certain columns of a table
3. Can save time and efforts for queries that are frequently executed
4. Can help in manipulating data

Rules and restriction:

1. The SELECT statement cannot contain a subquery in the FROM clause.
2. The SELECT statement cannot refer to system or user variables.
3. Any table or view referred to in the definition must exist.
4. A temporary view cannot be created.
5. Any tables named in the view definition must exist at definition time.
6. A trigger cannot be associated with a view.

CREATE VIEW Syntax with an example

SQL CREATE VIEW : A view is a virtual table. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

Syntax:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Example:
CREATE VIEW [sample] AS
SELECT employeeID,employeeName
FROM employee
WHERE salary > 10000

ALTER VIEW Syntax with an example

The existing view can be updated as shown below. Below we are adding an additional columns salary.
CREATE VIEW [employee_details] AS
SELECT employeeID,employeename,salary
FROM Employee
WHERE Discontinued=No

DROP VIEW Syntax with an example

The view can be dropped by using the DROP statement.

Syntax:
DROP view view_name
MySQL stored procedures
MySQL stored procedures - What are stored procedures? Explain the use of stored procedures.
MySQL cursor
MySQL cursor - use of cursor, steps involves in using cursors
MySQL triggers
MySQL triggers - What are triggers? What are they used for?, Creating triggers and Dropping Triggers, What are the types of triggers?, How are triggers created in MySQL?, What are the levels of triggers supported by MySQL?
Post your comment

You may like