View in mysql

Creating a View

A view is essentially a SQL Server object that specifies exactly how a user will see that data
in a database. It is a stored query. Views are useful for enforcing security (that is, granting
use access to views, but not tables) and simplifying the user interface to the database by
creating views for the most frequently used queries.

Use the view

select * from myFirstView
where mark='good'

CREATE VIEW statement would look like this:

USE Music
GO
CREATE VIEW all_bands
AS
SELECT band_title, band_id FROM t_bands

This is a pretty simple example, but a good starting point. To utilize the view, all you
need to do is call it from a SQL statement, like SELECT:

SELECT * FROM all_bands ORDER BY band_title

A view is a virtual table that, in itself, doesn’t contain any data or information.
When building indexes on views, the first index to be created must be a unique clustered index.
Once such an index has been built, additional nonclustered indexes on this view can then be created.
Drop the unique clustered index, then all of the other indexes will automatically be dropped

The view that the index is to build on must only contain tables and cannot contain views. The
tables must all come from one database, and the view must also reside in that database and have
been built with the SCHEMABINDING option.

As was mentioned when discussing the options required to index a view, we didn’t have these two options set to  ON. We therefore have to re-create the view.

CREATE UNIQUE CLUSTERED INDEX ivCustomerOrders
ON CustomerOrders_vw(AccountNumber, SalesOrderID, ProductID);

This brings up the code in a new Query Editor pane. Modify the two SET options and add in a DROP VIEW  statement so that we can re-create the view. Executing the code should be successful.

USE [ApressFinancial]
GO
/****** Object: View [CustomerDetails].[vw_CustFinProducts]
Script Date: 08/07/2008 12:31:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP VIEW CustomerDetails.vw_CustFinProducts
GO
CREATE VIEW [CustomerDetails].[vw_CustFinProducts] WITH SCHEMABINDING
AS
SELECT c.CustomerFirstName + ' ' + c.CustomerLastName AS CustomerName,
c.AccountNumber, fp.ProductName, cp.AmountToCollect,
cp.Frequency, cp.LastCollected
FROM CustomerDetails.Customers c
JOIN CustomerDetails.CustomerProducts cp ON cp.CustomerId = c.CustomerId
JOIN CustomerDetails.FinancialProducts fp ON
fp.ProductId = cp.FinancialProductId
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
USE sample;
GO
DROP VIEW v_count;

How to Encript a View and How to create Index on View

WITH ENCRYPTION goes after the name of the view, but before the AS keyword
WITH ENCRYPTION does not use the OPTION keyword

views, by default, do not exist physically—that is, their content is not stored on the disk.
This information (including the name of the view and the way the rows from the base
tables are to be retrieved) is the only information concerning views that is physically
stored. Thus, views are also called virtual tables.

The SELECT statement in a view cannot include the ORDER BY, INTO, or COMPUTE clauses.

USE sample;
GO
CREATE VIEW v_clerk
AS SELECT emp_no, project_no, enter_date
FROM works_on
WHERE job = 'Clerk';

ALTER VIEW statement.

USE sample;
GO
ALTER VIEW v_without_budget
AS SELECT project_no, project_name
FROM project
WHERE project_no >= 'p3';



USE sample;
GO
CREATE VIEW v_d2
AS SELECT emp_no, emp_lname
FROM employee
WHERE dept_no ='d2';
GO
SELECT emp_lname

FROM v_d2
WHERE emp_lname LIKE 'J%';

INSERT Statement and a View

When a view is used to insert rows, the rows are actually inserted into the underlying base table.

USE sample;
GO
CREATE VIEW v_dept
AS SELECT dept_no, dept_name
FROM department;
GO
INSERT INTO v_dept
VALUES('d4', 'Development');

use MobileManagement
go
Create View mobile_VW
as
select CName, AU
from Company
select CName,AU from mobile_VW