SQL Views

  • A view is nothing more than a SQL statement with a name stored in the database. A view is a preset SQL query that combines the columns of a table.
  • A view can contain all of a table's rows or only a subset of them. Depending on the SQL query used to create the view, a view can be built from a single table or multiple tables.
  • Users can accomplish the following with views, which are a type of virtual table:
  • Structure data in a way that feels natural or intuitive to users or groups of users.
  • Restrict data access so that users can only see and (sometimes) edit what they need.
  • Compile data from several tables into a single summary that may be used to make a report.

Creating Views

To create database views, use the Construct VIEW statement. Views can be generated from a single table, several tables, or another view.

A user must have the appropriate system privileges to build a view, depending on the implementation.

The following is the fundamental syntax for CREATE VIEW:

ADD A VIEW WITH THE NAME view name to your project. AS SELECT column1, column2,..... FROM table name WHERE [condition]; AS SELECT column1, column2,..... FROM table name WHERE [condition]; AS SELECT column1, column2,..... FROM table name WHERE [condition]; AS SELECT column1, column2,..... FROM table name WHERE [condition]; AS SELECT column1, column2,..... FROM table name WHERE [condition]; AS

In the same way that many tables can be included in a typical SQL SELECT query, you can consist of multiple tables in your SELECT statement.

Example

Consider the following CUSTOMERS table records:

Image Not found

The following example explains how to make a view from the CUSTOMERS table. The client name and age from the CUSTOMERS table would be pulled into this view.

CREATE VIEW CUSTOMERS VIEW IN SQL AS SELECT name, age FROM CUSTOMERS; Now you may query CUSTOMERS VIEW as a table. Here's an illustration of what I'm referring to.

SQL > SELECT * FROM CUSTOMERS VIEW yields the following result.

mysql> select cus_name, salary from customers;

Image Not found

A checkbox option is WITH CHECK OPTION.

WITH CHECK OPTION is a CREATE VIEW statement option. The WITH CHECK OPTION assures that all UPDATE and INSERT actions meet the condition set forth in the view definition (s).

If the UPDATE or INSERT commands do not satisfy the requirement, they will fail (s).

The following code block demonstrates how to create the same view CUSTOMERS VIEW using the WITH CHECK OPTION.

TO CREATE A VIEW, SELECT THE NAME AND AGE OF CUSTOMERS. THE OPINION OF THE CUSTOMERS

WHERE age IS NOT NULL WITH CHECK OPTION; Because the view is defined by data with no NULL values in the AGE column, the WITH CHECK OPTION should prevent any NULL values from being inserted in the AGE column in this case.

  • Changing Your Point of View
  • If the conditions given below are met, a view can be adjusted.
  • The SELECT clause cannot contain the keyword DISTINCT.
  • The SELECT clause does not allow summary functions.
  • The SELECT clause does not allow set functions.
  • The SELECT clause does not allow set operators.
  • The SELECT clause may or may not include an ORDER BY clause.
  • The FROM clause cannot contain several tables.
  • The WHERE clause does not allow subqueries.
  • The terms GROUP BY and HAVING are not permitted in the query.
  • Calculated columns may or may not receive an update.
  • All NOT NULL fields from the base table must be included in the view for the INSERT query to work.

You can edit a view if it fits all of the aforementioned conditions. The code block below demonstrates how to update Ramesh's age.

UPDATE CUSTOMERS IN SQL This will update both the underlying table CUSTOMERS and the view itself. set salary =salary+100; Now try querying the basic table with the SELECT statement, and you'll receive the following result.

mysql> update customers set salary =salary+100;

A View's Rows Rows in a View Inserting Rows into a View

Rows of data can be inserted into a display. The INSERT command follows the same set of rules as the UPDATE command.

Because the CUSTOMERS VIEW lacks all of the NOT NULL attributes, we can't insert rows; otherwise, you can insert rows in a view just like you can in a table.

A View's Rows Can Be Removed

The rows of data in a view can be deleted. The UPDATE and INSERT commands use the same logic as the DELETE command.

The code below shows how to delete a record with the AGE value of 22.

mysql> delete from customers where cus_id=321; This will remove a record from the base table CUSTOMERS, and the view will reflect this. Now try querying the basic table with the SELECT statement, and you'll receive the following result.

Points of View Are Disappearing

Wherever there is a view, there must be a way to remove it if it is no longer needed. DROP VIEW view name, DROP VIEW view name, DROP VIEW view name, DROP VIEW view name, DROP VIEW view name, DROP VIEW view name, DROP VIEW view name, DROP VIEW view name, DROP VIEW view name, DROP VIEW view name, DROP VIEW view name, DROP

How to delete the CUSTOMERS VIEW from the CUSTOMERS database is demonstrated in the example below.