Learning notes for MySQL view

MySQL view of the study notes, learned to Tarena gold medal lecturer Heshan, Golden Dawn technology company technical Director Chalimoux Course notes synthesis.

View and graphical tools

1. definition of views

A view is a table that is exported from one or more tables and is a virtual existence. A view is like a window (a window of data presentation), through which you can see the data that the system specifically provides (and you can see all of the data in the data table), using the view instead of seeing all the data in the data table, but only the data you want.

In the database, only the definition of the view is stored, and there is no data to hold the view, the data is stored in the original table, the view data is dependent on the data in the original tables, so the data of the original table has changed, then the displayed view of the data will be changed, such as inserting data into the data table, When you view the view, you will find that the same data is inserted in the view.

A view is similar in appearance to a table, but it does not require physical storage, and the view is actually made up of tables in the form of a predefined query.

A view can contain all or part of a table's records, or it can be created by a table or multiple tables, and when we create a view, we actually execute the SELECT statement in the database, and the SELECT statement contains the field names, functions, and operators to display the data to the user.

In the database, the view is used in the same way that the table is used, and we can manipulate the view as if it were a table, or get the data.

In general, we only use views to query data, not to manipulate the data through views.

1.1 View- based views

You can also create a view based on a view that already exists.

1.2 differences in views and tables

The main difference between a view and a table is to see if the physical space is occupied.

1.3 the role of the view

(1) Select useful information, the role of screening

(2) Simple operation, what you see is what you need, what the view sees is information that needs to be understood

(3) Increase the security of data: query or modify the specified data, non-specified data is not touch.

(4) Increase the independence of logic

1.4 features of the view

(1) Simplicity (simplification): can display specific data, without having to set the query conditions repeatedly, simplifying the operation.

(2) Security: The view can only show part of the data table data, for we do not want to let users see all the data, just want users to see some of the data, you can choose to use the view.

(3 logical Independence: when the real data table structure changes, the view can be used to mask the structural changes of the real table, thus realizing the logical independence of the view.

Views enable applications and database tables to be somewhat independent. If there is no view, the application must be built on the table. With a view, the program can be built on top of the view so that the program is separated from the database table by the view. Views can make programs and data independent in the following ways:

① If the application is built on a database table, when the database table changes, you can establish a view on the table, screen the table changes through the view, and the application can not move.

② If an app is built on a database table, when the app changes, you can create a view on the table, masking the changes to the app through the view, and keep the database table from moving.

③ If the application is built on a view, when the database table changes, the view can be modified on the table, and the table can be masked by the view so that the application does not move.

④ If an app is built on a view, when the app changes, you can modify the view on the table, masking the app's changes through the view, so the database can be fixed.

2. Create a view

CREATE VIEW view name [(column_list)] as SELECT Statement

Cases:

CREATE VIEW Province_view as SELECT * from province;

SELECT * from Province_view;

Description: The created view Chart Province_view is identical to the province table.

2.1 specify the fields that the view displays:

CREATE VIEW Province_view1 (id,name) as SELECT id,pro_name from province;

Mysql> SELECT * from Province_view1;

+-----+------+

| ID | name |

+-----+------+

| 1 | Beijing |

| 2 | Shanghai |

| 3 | Liaoning |

| 4 | Tianjin |

| 5 | Guangdong |

| 6 | Fujian |

| 100 | Jilin |

+-----+------+

7 Rows in Set (0.00 sec)

2.2 to create a view based on two tables:

Use where to connect two tables:

CREATE VIEW v3 (name,score) as SELECT S_name,score from Student,score

WHERE student.s_id=score.s_id

and score.c_id= ' by ';

2.3 algorithm of the View

algorithm=

Undefined:mysql automatically selects the algorithm to use

Merge: Statements that use views are merged with the definition of the view, and some part of the view definition supersedes the corresponding part of the statement

TempTable: Temporary table, the result of the view is stored in a temporary table, and then a temporary table is used to execute the statement

WHIT [cascaded| LOCAL] CHECK OPTION: Indicates that the view is updated with the permission range of the view:

cascaded default value, which indicates that the view and table related conditions are met when the view is updated

LOCAL: Indicates that when the view is updated, a condition that satisfies the view definition can be

Description: Use whit [cascaded| LOCAL] CHECK option to ensure data security

3. Create a full view

CREATE algorithm VIEW View name [(column_list)] As SELECT Statement

with [cascaded| LOCAL] CHECK OPTION

Syntax prompt command:? CREATE VIEW

Name: ' CREATE VIEW '

Description:

Syntax:

CREATE

[OR REPLACE]

[algorithm = {UNDEFINED | MERGE | TempTable}]

[definer = {User | Current_User}]

[SQL SECURITY {definer | INVOKER}]

VIEW view_name [(column_list)]

As Select_statement

[With [cascaded | LOCAL] CHECK OPTION]

Example:

CREATE algorithm=undefined VIEW user_view3 (id,username,age) as SELECT

Id,username,age from Users2 with cascaded CHECK OPTION;

4. View View

To view a view that has been created:

4.1 to see how a view has been created:

DESC

DESCRIBE

SHOW COLUMNS from view name

SHOW TABLE STATUS like

SHOW CREATE VIEW

4.1.1 DESC

mysql> desc USER_VIEW3;

+----------+----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+----------------------+------+-----+---------+-------+

| ID | smallint (5) unsigned |     NO | |       0 | |

| Username | varchar (20) |     NO | |       NULL | |

| Age | tinyint (3) unsigned |     YES | |       NULL | |

+----------+----------------------+------+-----+---------+-------+

3 Rows in Set (0.02 sec)

4.1.2 DESCRIBE

Mysql> DESCRIBE user_view3;

+----------+----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+----------------------+------+-----+---------+-------+

| ID | smallint (5) unsigned |     NO | |       0 | |

| Username | varchar (20) |     NO | |       NULL | |

| Age | tinyint (3) unsigned |     YES | |       NULL | |

+----------+----------------------+------+-----+---------+-------+

3 rows in Set (0.01 sec)

4.1.3 SHOW COLUMNS from View name

Mysql> SHOW COLUMNS from USER_VIEW3;

+----------+----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+----------------------+------+-----+---------+-------+

| ID | smallint (5) unsigned |     NO | |       0 | |

| Username | varchar (20) |     NO | |       NULL | |

| Age | tinyint (3) unsigned |     YES | |       NULL | |

+----------+----------------------+------+-----+---------+-------+

3 Rows in Set (0.02 sec)

4.2 to view the basic information of the view (you can also view the information for the original table):

SHOW TABLE STATUS like ' view name ';

Mysql> SHOW TABLE STATUS like ' Province_view ' \g;

1. Row ***************************

Name:province_view

Engine:null

Version:null

Row_format:null

Rows:null

Avg_row_length:null

Data_length:null

Max_data_length:null

Index_length:null

Data_free:null

Auto_increment:null

Create_time:null

Update_time:null

Check_time:null

Collation:null

Checksum:null

Create_options:null

Comment:view

1 row in Set (0.00 sec)

Description

(1) You can see from Comment:view that it is a view, and if it is a data table, the value of the comment option is empty.

(2) Because the view is a virtual table, the values of many options are null if the show table STATUS is like ' table_name '; Then these options will show the values.

4.3 View creation information for a specified view (commands that specifically view information)

SHOW CREATE VIEW The name of the view;

mysql> SHOW CREATE VIEW user_view3\g;

1. Row ***************************

View:user_view3

Create view:create algorithm=undefined definer= ' root ' @ ' localhost ' SQL SECURITY definer View ' user_view3 ' as select ' Users 2 '. ' ID ' as ' id ', ' users2 '. ' username ' as ' username ', ' users2 '. ' Age ' as ' age ' from ' users2 ' with cascaded CHECK OPTION

Character_set_client:gbk

Collation_connection:gbk_chinese_ci

1 row in Set (0.00 sec)

4.4 where the view data is stored

Mysql> SELECT * from Information_schema.views\g

All views are saved in the information_schema.views.

4.5. Modifying a view:

If the view does not exist, the view is created and if the view is present, the view is modified:

(1)CREATE OR REPLACE view name [(column_list)] as SELECT statement

(2 ) ALTER VIEW view name [(column_list)] as SELECT Statement

4.5.1 CREATE OR REPLACE VIEW View name [(column_list)] As SELECT Statement

(1) Example:

CREATE OR REPLACE VIEW user_view3 (id,username) as SELECT id,username from Users2;

(2) If the input view name does not exist, this MySQL automatically creates the view:

(3 ) To modify the view:

CREATE OR REPLACE alogrithm=temptable VIEW user_view4 (ID) as SELECT ID from

Users2;

(4 ) to modify a view based on two tables, two tables using the Where to connect:

CREATE OR REPLACE VIEW v3 as SELECT s_name,s_sex,score from Student,score

WHERE student.s_id=score.s_id and score.c_id= ' by ';

4.5.2 ALTER

ALTER VIEW view name [(column_list)] as SELECT Statement

ALTER VIEW user_view4 (id,username,age) as SELECT id,username,age from Users2;

To modify a view based on two tables:

ALTER VIEW v3 as SELECT s_name,score from Student,score

WHERE student.s_id=score.s_id

and score.c_id= ' TC ';

5. Update the View

The so-called update view, is actually through the view, the data to insert, modify and delete operations.

5.1 Modifying the data of a view

Note: Modifying the data of the view will directly modify the real data of the data table (that is, the original table).

UPDATE v3 SET score=100 WHERE s_name= ' Ivanini ';

5.2 the principle and 5.1 of inserting and deleting data through views consistent, consistent with the operation syntax of the data table

6. Delete the view:

Deleting a view does not affect the data of the original table, but deleting the view's data will affect the original table.

6.1 DROP VIEW The name of the view;

DROP VIEW The name of the view;

DROP VIEW user_view4;

6.2 DROP VIEW IF EXISTS

No action is taken when deleting a view that no longer exists:

DROP VIEW IF EXISTS The name of the view;

Cases:

DROP VIEW IF EXISTS v1;

6.3 Delete Multiple views

DROP VIEW IF EXISTS v2,v3;

Use of graphical tools

1. graphical tools:

MYSQL Workbench

PHPMyAdmin

Navicat for MySQL: is a client software that needs to be installed by the client software before it can be used.

Mysql-front

2.PHPMyAdmin

phpMyAdmin is a MySQL database management tool that is architected on the site host in Web-base way, and it is using the Web interface to manage the MySQL database.

The database can be fully manipulated by phpMyAdmin.

Learning notes for MySQL view