How to modify the information in the table in mysql view-Mysql Tutorial-php.cn

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

##(Recommended tutorial: mysql video tutorial )

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 Updates the data in the base table referenced by the view.

Note: Modification of the view is a modification of the basic table, so when modifying, the data definition of the basic table must be met.

Basic syntax

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

The syntax format is as follows: ALTER VIEW AS Copy after login

The syntax description is as follows:

: Specify the name of the view. The name must be unique in the database and cannot have the same name as another table or view.

: Specify the SELECT statement to create a view, which can be used to query multiple base tables or source views.

It should be noted that the use of the ALTER VIEW statement requires the user to have CREATE VIEW and DROP permissions on the view, as well as certain permissions on each column selected by the SELECT statement.

In addition to modifying the definition of a view through ALTER VIEW, you can also use the DROP VIEW statement to delete the view first, and then use the CREATE VIEW statement.

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

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

Aggregation functions SUM(), MIN(), MAX(), COUNT( ) wait.

DISTINCT keyword.

GROUP BY clause.

HAVING clause.

UNION or UNION ALL operator.

The subquery located in the select list.

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

The subquery in the WHERE clause refers to the table in the FROM clause.

When the ALGORITHM option is TEMPTABLE (using temporary tables will always make the view non-updatable).

[Example 1] Use the ALTER statement to modify the view view_students_info. The input SQL statement and execution results are as follows. 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) Copy after login

Users can insert, update, and delete data in the table through views, because the view is a virtual table with no data. When updating through a view, it goes to the base table for update. If records are added or deleted to the view, records are actually added or deleted to the base table.

View the data content 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) Copy after login

[Example 2] Use the UPDATE statement to update the view view_students_info. The input SQL statement and execution results are as follows. 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: 0 mysql> 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) Copy after login

View the contents of the base table tb_students_info and the 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 | 160 | 2015-09-10 | | 2 | Green | 3 | 23 | F | 158 | 2016-10-22 | | 3 | Henry | 2 | 23 | M | 185 | 2015-05-31 | | 4 | Jane | 1 | 22 | F | 162 | 2016-12-20 | | 5 | Jim | 1 | 24 | M | 175 | 2016-01-15 | | 6 | John | 2 | 21 | M | 172 | 2015-11-11 | | 7 | Lily | 6 | 22 | F | 165 | 2016-02-26 | | 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 | | 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 | | 10 | Tom | 4 | 23 | M | 165 | 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 | +------+--------+------+-------+-------+----------+------------+ | 1 | Dany | 1 | 25 | F | 160 | 2015-09-10 | | 2 | Green | 3 | 23 | F | 158 | 2016-10-22 | | 3 | Henry | 2 | 23 | M | 185 | 2015-05-31 | | 4 | Jane | 1 | 22 | F | 162 | 2016-12-20 | | 5 | Jim | 1 | 24 | M | 175 | 2016-01-15 | | 6 | John | 2 | 21 | M | 172 | 2015-11-11 | | 7 | Lily | 6 | 22 | F | 165 | 2016-02-26 | | 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 | | 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 | | 10 | Tom | 4 | 23 | M | 165 | 2016-08-05 | +------+--------+------+-------+-------+----------+------------+ 10 rows in set (0.00 sec) Copy after login