View, Map Recognition

View, Map Recognition

I. View

A view is a virtual table in a database. Its content is queried by the query statement. Like a real table, a view contains a series of columns and row data with names. However, a view does not exist in the database as a stored data value set. Its row and column data allows you to define the tables referenced by View queries and dynamically generate them When referencing the view. You can add SQL functions, WHERE statements, and JOIN statements to the view, and submit data, just like a single table.

The view has the following advantages:

• Viewpoint concentration

A view set allows users to focus only on certain data that they are interested in and the specific tasks they are responsible. In this way, the data security is improved by only allowing users to view the data defined in the view, rather than referencing the data in the table.

• Simplified operations

The view greatly simplifies user data operations. When defining a view, if the view itself is a result set of a complex query, you do not have to re-write these complex query statements every time you execute the same query, you only need a simple View query statement. The visible view hides complex join operations between tables.

• Custom Data

The view allows different users to view different or identical result sets in different ways. Therefore, when many users of different levels share the same database, this is extremely important.

• Merge split data

In some cases, because the data volume in the table is too large, horizontal or vertical table segmentation is often performed in the design of the table, but the changes in the table structure have a negative impact on the application. If a view is used, the original structure can be retained so that the external mode remains unchanged. The original application can still use the view to reload data.

• Security

A view can be used as a security mechanism. You can only view and modify the data you can view. Other databases or tables are neither visible nor accessible. If a user wants to access the view result set, he/she must be granted the access permission. The access permission of the table referenced by the view does not affect the settings of the view permission. II. The following example table structure of the instance is as follows:

-- ------------------------------ Student TABLE -- -------------------------- drop table if exists 'studen'; create table 'studen' ('stu _ no' varchar (255) not null, 'name' varchar (255) not null, 'age' int (11) default null, 'Password' varchar (255) default null, primary key ('stu _ no ')) ENGINE = InnoDB default charset = utf8; -- ------------------------------ student record -- ------------------------ insert into 'student 'values ('2017030', 'zhang san', '20', 'zhang123 '); insert into 'student 'values ('123', 'lily', '21', 'lisi123 '); insert into 'student 'values ('123', 'wang 5 ', '22', 'hang123'); -- revoke student orders TABLE -- ---------------------------- drop table if exists 'Course'; create table 'Course' ('id' int (11) not null AUTO_INCREMENT, 'stu _ no' varchar (255) not null, 'name' varchar (255) not null, 'score 'int (11) not null, primary key ('id '), KEY 'id _ fk '('stu _ no'), CONSTRAINT 'id _ fk' foreign key ('stu _ no ') REFERENCES 'student '('stu _ no') ENGINE = InnoDB AUTO_INCREMENT = 8 default charset = utf8; -- ------------------------------ student achievement record -- -------------------------- insert into 'Course' VALUES ('1', '2017010', 'c ++ ', '70 '); insert into 'Course' VALUES ('2', '123', 'java', '80'); insert into 'Course' VALUES ('3', '123 ', 'lisp ', '90'); insert into 'Course' VALUES ('4', '000000', 'c ++', '75 '); insert into 'Course' VALUES ('5', '123', 'java', '80'); insert into 'Course' VALUES ('6', '123 ', 'lisp ', '91'); insert into 'Course' VALUES ('7', '123', 'c ++ ', '73 ');

The students and student accounts are associated using the stu_no student ID field.

1. View Creation

Syntax:

CREATE [ALGORITHM] = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW name [(attribute list)]
As select statement
[WITH [CASCADED | LOCAL] check option];

ALGORITHM clause:

The ALGORITHM clause is optional and is an extension of standard SQL MySQL. ALGORITHM can have three values: MERGE, TEMPTABLE, or UNDEFINED. Without the ALGORITHM clause, the default ALGORITHM is UNDEFINED (UNDEFINED ).

 

For MERGE, the text of the statements that reference the view is combined with the view definition, so that a part of the view definition replaces the corresponding part of the statement.

 

For TEMPTABLE, the view result is placed in a temporary table and then executed using it.

For UNDEFINED, MySQL selects the algorithm to be used. If possible, it should tend to be MERGE rather than TEMPTABLE, because MERGE is generally more effective, and if a temporary table is used, the view cannot be updated. One reason to explicitly select TEMPTABLE is that the lock on the base table can be released after the temporary table is created and before the statement processing is completed. Compared with the MERGE algorithm, the lock release speed is faster, so that other clients using the view will not be blocked for a long time.

With check option can be used for updatable views. You can specify the with check option clause to prevent inserting or updating rows, unlessSelect_statementThe WHERE clause in is "true ".

 

Query the maximum score of a student.

DROP VIEW IF EXISTS highest_course;CREATE VIEW highest_course ASSELECT s.NAME AS stuname, c.NAME AS course,    max(c.score) AS scoreFROM        course AS c, student AS sWHERE    s.stu_no = c.stu_noGROUP BY    c.stu_no;

Run DESCRIBE highest_course to view the structure of the view, such as whether the field size is empty or not. The execution result is as follows:

　

Run SELECT * FROM highest_course; to query the View data. The result is as follows:

You can also query the maximum score of a subject named John. Execute SELECT * FROM highest_course WHERE stuname = 'John 3'; to query the View content. The result is as follows: 2. View Structure Modification Syntax:

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
Select_statement
[WITH [CASCADED | LOCAL] check option]

Add a studentno field to the original view.

1 -- modify mysql VIEW 2 alter view highest_course AS 3 SELECT s. stu_no AS studentno, s. name as stuname, c. name as course, max (c. score) AS score 4 FROM 5 course AS c, student AS s 6 WHERE 7 s. stu_no = c. stu_no 8 group by 9 c. stu_no; 10 DESCRIBE highest_course;

Modify mysql

The view structure after 10th rows is executed is as follows:

A field studentno has been added.

3. delete a view

DROP VIEW highest_course;

4. View related statements

SELECT * FROM highest_course; -- Query all VIEW content DESCRIBE highest_course; -- query the information of each field in the view show create view highest_course; -- query the creation statement of the VIEW

Iii. Main view points

View definitions are subject to the following restrictions:

• The SELECT statement cannot contain subqueries in the from clause.
• SELECT statements cannot reference system or user variables.
• The SELECT statement cannot reference preprocessing statement parameters.
• During a stored procedure, the definition cannot reference subprogram parameters or local variables.
• The table or view referenced in the definition must exist. However, after creating a view, you can delete the referenced table or view. To CHECK whether view definitions have such problems, you can use the check table statement.
• Temporary tables cannot be referenced in the definition, and temporary views cannot be created.
• The table named in the view definition must already exist.
• You cannot associate the trigger program with the view.
• If order by is used in the view definition, but a new order by is used during the call, the order by defined in the view will be invalid and will be sorted BY the order by at the time of the call.

You can update the View content indirectly by updating table data.

Whether the View content is updated depends on the with check option clause. Some views can be updated, while some views cannot. Also, the updatability of the view may be affected by the value of the system variable updatable_views_with_limit.

What is a view?

A view is an SQL statement stored in a database. It is mainly for two reasons: security reasons. A view can hide some data, such as the Social Insurance Fund table, you can use the view to display only the name and address, but not the social insurance number and wage number. Another reason is that complex queries are easy to understand and use.
View: displays images or documents.

A view is a table exported from one or more tables or views. Its structure and data are based on table queries. Like a table, a view also contains several defined data columns and multiple data rows. However, in essence, these data columns and data rows come from the referenced table.
Therefore, a view is not a basic table that actually exists, but a virtual table. The data of the view is not actually stored in the database in the view structure, but in the table referenced by the view.
Once defined, the view is stored in the database. The corresponding data is not stored in the database as the table. The data displayed in the view is only stored in the basic table. Operations on a view are the same as those on a table. You can query, modify (with certain restrictions), and delete a view.
When you modify the data seen through the view, the data of the corresponding basic table also needs to change. At the same time, if the data of the basic table changes, this change can also be automatically reflected in the view.
View has many advantages, mainly manifested in:
• Centralized Viewpoint
• Simplified operations
• Custom Data
• Merge and split data
• Security

What is a view?

A view is an SQL statement stored in a database. It is mainly for two reasons: security reasons. A view can hide some data, such as the Social Insurance Fund table, you can use the view to display only the name and address, but not the social insurance number and wage number. Another reason is that complex queries are easy to understand and use.
View: displays images or documents.

A view is a table exported from one or more tables or views. Its structure and data are based on table queries. Like a table, a view also contains several defined data columns and multiple data rows. However, in essence, these data columns and data rows come from the referenced table.
Therefore, a view is not a basic table that actually exists, but a virtual table. The data of the view is not actually stored in the database in the view structure, but in the table referenced by the view.
Once defined, the view is stored in the database. The corresponding data is not stored in the database as the table. The data displayed in the view is only stored in the basic table. Operations on a view are the same as those on a table. You can query, modify (with certain restrictions), and delete a view.
When you modify the data seen through the view, the data of the corresponding basic table also needs to change. At the same time, if the data of the basic table changes, this change can also be automatically reflected in the view.
View has many advantages, mainly manifested in:
• Centralized Viewpoint
• Simplified operations
• Custom Data
• Merge and split data
• Security