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?
-
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.
-
Tables are in mode and views are in mode.
-
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