This is the sixth day of my participation in Gwen Challenge

What is a view

View is an important mechanism for relational database system to provide users with multiple perspectives to observe the data in the database.

A view is a virtual table. Unlike a table, a view is created using a SELECT statement (described below). Therefore, when you manipulate a view, you will create a virtual table based on the SELECT statement used to create the view.

What is the difference between a view and a table?

  1. A view is a table that is exported from one or more base tables (or views). Unlike the base table, it is a virtual table, which you can think of as a Windows shortcut. Tables are physical, and you can think of them as files on a computer.

  2. Tables are in mode and views are in mode.

  3. Views can be queried and deleted like basic tables, and views can be defined on a view. However, there are certain restrictions on how views can be added, deleted, or modified.

View definition and deletion

1. Define the view

SQL > CREATE VIEW;

CREATE VIEW< VIEW name >(< column name1>, < the column name2>,...). AS <SELECT statement > [WITH CHECK OPTION];Copy the code

Example: Create a view of computer science students

CREATE VIEW CS_VIEW
AS
	SELECT *
	FROM S
	WHERE sdept='CS';
Copy the code

2. Delete the view

DROP VIEW < VIEW name >;

After a view is deleted, views based on that view are still in the data dictionary, but they are not available and need to be deleted one by one.

Example 2: Delete the CS_VIEW view.

DROP VIEW CS_VIEW
Copy the code

Execution Result:

DROP VIEW CS_VIEW > OK > time:0.016s

Copy the code

Update the view

Updating a view means updating it by INSERT, DELETE, or UPDATE.

!!!!!!!!!!!!!!!!!!

Since a view is actually a virtual table that stores no data, an update to a view is really an update to the base table.

Example 3: Add 1 to the age of the male student in CS_VIEW

UPDATE CS_VIEW
SET sage = sage + 1
WHERE ssex = 'male';
Copy the code

Modify before: Revised:

Fifth, sub-query

1. What is a subquery

Subqueries use the SELECT statement used to define a view directly in the FROM clause.

2. Nested subquery

Select * from student where age > 19;

SELECT
	* 
FROM
	( SELECT * FROM s WHERE sage > 19 ) AS S5
Copy the code

Although nested subqueries can produce results, SQL statements can become difficult to understand and inefficient to execute as the number of nested subqueries increases, so avoid such use.

3. Scalar quantum query

Scalar is a single meaning, so the standard quantum query is a single subquery, that is, what is called a single subquery?

Singleness is the requirement that we execute SQL statements that return only one value, that is, a column of a specific row in the table.

Query student id, name, age, and average age:

SELECT
	sno,
	sname,
	sage,
	( SELECT AVG ( sage ) FROM s ) AS AVG_AGE 
FROM
	S
Copy the code