MySQL Basic operations-view

A view is a virtual table exported by one or more tables in a database, so that the user can only care about the data that is useful to them and the security of the database system without seeing the data in the whole database table. The database only holds the definition of the view, and its data originates from the original table. So the data in the source table changes and the view data changes. Views can simplify repetitive query operations and increase the security of your data.

1. Create a View

Create [algorithm={undefined|merge|temptable}] View name [(property list)] As SELECT statement [with [cascaded|local] Check option];

Algorithm: An optional parameter that represents the algorithm for the view selection. View name: The name of the view to create. SELECT statement: A complete query statement that imports the records that meet the criteria into the view. Cascaded: Represents the condition for all related views and tables to be met when the view is updated. It is best to add the WITH CHECK option parameter +cascaded when using CREATE VIEW, and the update view needs to consider the constraints of its parent view. This method is more rigorous and can guarantee the security of the data.

(1). Query If you have CREATE VIEW permission

SELECT Select_priv,create_view_priv from Mysql.user WHERE user= ' root ';

(2). Create a simple view on table test0

CREATE View Test0_view1 as select Id,name from Test0;

Desc test0_view1;//View View

Show table status like ' view name ' \g; View basic information, whether comment is view or empty, is the most direct difference between views and normal tables.

Show create view name \g; You can view a detailed definition of the view.

2. Create a view on multiple tables

Create Algorithm=merge View Woker_view1 (name,department,sex,age,address) as Select name,department.d_name,sex,2009- Birthday,address from Worker,department where worker.d_id=department.d_id with local check option;

3. Modify the View

(1). Create or replace

Create or replace [algorithm={undefined|merge|temptable}] View name [(View list)] As SELECT statement [with [Cascaded|local]check Opti On

Eg:create or Replace algorithm=temptable view Example_view2 (id,use_id) as select id,use_id from example;

(2) ALTER view

Eg:alter View Example2_view1 (Stu_id,grade) as select Stu_id,grade from Example2;

4. Update view: Insert from the view, update,delete the data in the table, corresponding to the source table data. Use the view as a virtual table of query data instead of updating the data through a view.

(1). Update View Name Set property Name 1 = new value 1, property name 2 = new value 2;

A. Unable to update view condition:

1). The view contains functions such as SUM (), COUNT (), Max (), Min (), and so on.

Eg:create View Worker_view4 (name,sex,total) as select Name,sex,count (name) from worker;

2). The view contains Union,union All,distinct,group by and Havig keywords.

Eg:create View Worker_view5 (name,sex,address) as select Name,sex from worker group by D_ID;

5. Delete a view

Drop view [if exists] View name list [Pestrict|cascade];

If exists: Determines whether the view exists, executes if it exists, does not exist.

MySQL Basic operations-view