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

Database view of Mysql

The view in the database is a virtual table. Like a real table, the data containing rows and columns is used to customize the table referenced by the view query and is generated dynamically when the view is referenced. You can isolate users from the data to keep it safe, and query the data quickly.

Post-Mysql5.0 versions support views, and you can use select, insert into, update, delete, and so on. Whether you modify the basic table or the view, it changes accordingly.

The role of the view:

Compared to reading directly from the data table:

1. Simplified: what you see is what you need.

2. Security: users can only query and modify the data they can see through the view. Other data can not be seen or obtained.

3. Logical data independence: views can help users shield the impact of changes in the structure of real tables.

Create a view:

The view contains the results of the select query, so the creation of the view is based on select statements and existing data tables, and the view can be based on one table or multiple tables.

Syntax:

Create [or replace] [algorithm = {undefined | merge | temptable}] view view_name [(column_list)] as select_ status [with [cascaded | local] check option] create # create view replace # replace view algorithm # View algorithm: undefined system chooses its own algorithm; merge indicates that the view statement is merged with the view definition, so that the view definition part replaces the corresponding part of the view definition statement Temptable means to store the view results in a temporary table and use the temporary table to execute the statement. View_name # View name column_list # attribute column select_statment # select statement [with [cascaded | local] check option] # indicates that the permission is within the scope of permission: cascaded means that all conditions are met. Local means that the condition of the view itself is satisfied when the update is made.

Create a view with a single table:

Mysql > create database view;mysql > use view;mysql > create table t (quantity INT,price INT); mysql > insert into t values (3pm 50); mysql > create view view_t as select quantity,price,quantity * price from t * MySQL > select * from view_t

The view created by default is the same as the fields of the basic table, and you can also create the view by specifying the name of the view field.

Mysql > create view view_t2 (qty,price,total) as select quantity,price,quantity * price from tbot MySQL > select * from view_t2

The field names in the # view_t and view_t2 views are different, but the data is the same. Therefore, when using the view, the user may not need to know the mechanism of the table at all, let alone the data in the actual table, thus ensuring the security of the database.

Create a view on multiple tables:

Mysql > create database stud;mysql > use stud;mysql > create table student (id int,name char (20)); mysql > create table stu_info (id int,name char (20), addr char (20)); mysql > show tables;mysql > insert into student values (1m ZHAOYI'), (2Med Zhaoer'), (3Med Zhaoer'), (3MIT ZHANGSHANG'); mysql > insert into student values (1ZHAOYILELLING beijing`), (2MIZHAOERTHAI`), (3MIZHANGZHANGHAI`), (3MIZHANGZHANGZHANGYI`); mysql > select * from stu_info Mysql > select * from student;mysql > create view stu_glass (id,name,glass) as select student.id,student.name,stu_info.addr from student,stu_info where student.id=stu_info.id;mysql > select * from stu_glass

# you can see that the view id is student.id

# name is student.name

# glass is stu_info.addr

View the view:

Mysql > show table status like 'stu_glass'\ G; # all NULL,Comment: VIEW means virtual table mysql > desc stu_glass; # View view basic information mysql > show create view stu_glass\ G; # View view creation information mysql > select * from information_schema.views\ G; # View details

Modify the view:

Mysql > use view;mysql > desc view_t;mysql > alter view view_t as select quantity from t; or: mysql > create or replace view view_t as select * from tsp MySQL > desc view_t

Update the view:

Mysql > alter view view_t as select quantity from tten # modify view and metadata different mysql > select * from view_t;mysql > select * from tten MySQL > update view_t set quantity=5;mysql > select * from view_t;mysql > select * from view_t2;mysql > select * from tten # all updates

Insert data:

Mysql > insert into t values (3 from 5); # insert data mysql > select * from view_t;mysql > select * from view_t2;mysql > select * from tbot # all insert mysql > delete from view_t2 where price=5; # delete price=5 data in view_t2 view; mysql > select * from view_t2;mysql > select * from tten # the operation of deleting price=5 in view_t2 is finally achieved through the related records in the basic table. Note: when the view contains the following, the view update operation will not be performed: 1, the view does not contain the basic table is defined as non-empty columns; 2, in the definition of view select statement in the field list after the use of mathematical expressions; 3, in the definition of view select statement in the field list after the use of aggregate functions; 4, in the definition of view select statement in the use of distinct, union, top, group, having clause.

Delete the view:

Mysql > drop view if exists view_t2;mysql > show create view view_t2; ERROR 1146 (42S02): Table 'view.view_t2' doesn't exist

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.