Shulou(Shulou.com)06/01 Report--

This article will explain in detail about the mysql view to modify the information in the table, the editor thinks it is very practical, so share it for you to do a reference, I hope you can get something after reading this article.

In mysql, the view is a virtual table, and the actual data comes from the basic table, so updating the data information in the view through insert, modify and delete operations is essentially updating the data information of the basic table referenced by the view; syntax format "ALTER VIEW AS".

Modify view content

The view is a virtual table, and the actual data comes from the basic table, so updating the data in the view through insert, modify and delete operations is essentially updating the data of the basic table referenced by the view.

Note: a change to the view is a change to the basic table, so when you modify it, you should meet the data definition of the basic table.

Basic grammar

You can use the ALTER VIEW statement to modify an existing view.

The syntax format is as follows:

ALTER VIEW AS

The syntax is as follows:

Specifies the name of the view The name must be unique in the database and cannot have the same name as other tables or views.

Specifies the SELECT statement that creates the view, which can be used to query multiple underlying tables or source views

It is important to note that the use of the ALTER VIEW statement requires the user to have CREATE VIEW and DROP permissions for the view, as well as some permissions on each column selected by the SELECT statement.

To modify the definition of the view, in addition to ALTER VIEW, you can also use the DROP VIEW statement to delete the view first, and then use the CREATE VIEW statement to achieve.

Some views are updatable. That is, you can update the contents of the base table using statements such as UPDATE, DELETE, or INSERT. For updatable views, there must be an one-to-one relationship between the rows in the view and the rows in the underlying table.

There are also specific other structures that make the view unupdatable. More specifically, a view is not updatable if it contains any of the following structures:

Aggregate functions SUM (), MIN (), MAX (), COUNT (), and so on.

DISTINCT keyword.

GROUP BY clause.

HAVING clause.

UNION or UNION ALL operator.

A subquery located in the selection list.

A non-updatable view in the FROM clause or contains multiple tables.

A subquery in the WHERE clause that references the table in the FROM clause.

The ALGORITHM option is TEMPTABLE (using temporary tables always makes the view non-updatable).

[example 1] use the ALTER statement to modify the view view_students_info. The input SQL statement and the execution result are shown below.

Mysql > ALTER VIEW view_students_info-> AS SELECT id,name,age-> FROM tb_students_info;Query OK, 0 rows affected (0.07 sec) mysql > DESC view_students_info +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (11) | NO | | 0 | | name | varchar (45) | YES | | NULL | | age | int (11) | YES | | NULL | | +-+-+ 3 rows in set (0.03 sec)

Users can insert, update, and delete data in the table through the view, because the view is a virtual table with no data. When you update a view, you go to the base table to update. If you add or delete records to the view, you are actually adding or deleting records to the base table.

View the data contents of the view view_students_info, as shown below.

Mysql > SELECT * FROM view_students_info +-+ | id | name | age | +-- + | 1 | Dany | 24 | 2 | Green | 23 | 3 | Henry | 23 | 4 | Jane | 22 | 5 | Jim | 24 | 6 | John | 21 | 7 | Lily | 22 | 8 | Susan | 23 | 9 | Thomas | 22 | 10 | Tom | 23 | +-+ 10 rows in set (0.00 sec)

[example 2] use the UPDATE statement to update the view view_students_info. The input SQL statement and the execution result are shown below.

Mysql > UPDATE view_students_info-> SET age=25 WHERE id=1;Query OK, 0 rows affected (0.24 sec) Rows matched: 1 Changed: 0 Warnings: 0mysql > SELECT * FROM view_students_info +-+ | id | name | age | +-- + | 1 | Dany | 25 | 2 | Green | 23 | 3 | Henry | 23 | 4 | Jane | 22 | 5 | Jim | 24 | 6 | John | 21 | 7 | Lily | 22 | 8 | Susan | 23 | 9 | Thomas | 22 | 10 | Tom | 23 | +-+ 10 rows in set (0.00 sec)

View the contents of the base table tb_students_info and view v_students_info, as shown below.

Mysql > SELECT * FROM tb_students_info +-+ | id | name | dept_id | age | sex | height | login_date | + -+-+ | 1 | Dany | 1 | 25 | F | 2015 | 09-10 | 2 | Green | 3 | 23 | F | 2016-10-22 | 3 | Henry | 2 | 23 | M | 2015-05-31 | 4 | Jane | 1 | 22 | F | 2016-12-20 | 5 | | Jim | 1 | 24 | M | 2016 | 01-15 | | 6 | John | 2 | 21 | M | 2015 | 11-11 | | 7 | Lily | 6 | 22 | F | 2016-02-26 | 8 | Susan | 4 | 23 | F | 2015-10-01 | 9 | Thomas | 3 | 22 | M | 2016-06-07 | | | 10 | Tom | 4 | 23 | M | 165th | 2016-08-05 | +-+ 10 rows in set (0.00 sec) mysql > SELECT * FROM v_students_info | +-+ | s_id | s_name | d_id | s_age | s_sex | s_height | s_date | +-- -Dany | 1 | 25 | F | 2015-09-10 | 2 | Green | 3 | 23 | 2016-10-22 | 3 | Henry | 2 | 23 | M | 2015-05-31 | 4 | Jane | | 1 | 22 | F | 2016 | 12-20 | 5 | Jim | 1 | 24 | M | 2016 | 01-15 | 6 | John | 2 | 21 | M | 2015-11 | | 7 | Lily | 6 | 22 | F | 2016-02-26 | 8 | Susan | 4 | 23 | F | 170 | 2015-10- | | 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 | | 10 | Tom | 4 | 23 | M | 165th | 2016-08-05 | +-+ 10 rows in set ( 0.00 sec) this is how the mysql view modifies the information in the table. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.