MySQL (7), mysql

MySQL (7), mysql
1 View

• View: View is a virtual table with a structure (with rows and columns) but no results (the data in the structure is not actually stored). The structure Source of the virtual table is not defined by itself, it is generated from the corresponding base table (the data source of the view ).

 

• Sample script:

Create table my_class (id int primary key AUTO_INCREMENT COMMENT 'Primary key', c_name VARCHAR (20) not null comment 'class name', room VARCHAR (20) not null comment 'classroom of the class'); -- INSERT class information INTO my_class VALUES (NULL, 'java001 class', 'a01'); insert into my_class VALUES (NULL, '003 003 ban', 'c15'); insert into my_class VALUES (NULL, 'c005 ban', 'b23 '); -- create table my_student (id int primary key AUTO_INCREMENT COMMENT 'Primary key', name varchar (20) not null comment 'student name', age int not null comment 'student age ', gender VARCHAR (2) not null comment 'student Gender', c_id int comment 'foreign key', height int comment 'height', CONSTRAINT fk_c_id foreign key (c_id) REFERENCES my_class (id); -- INSERT student information insert into my_student VALUES (NULL, 'zhang san', 20, 'mal', 1,180); insert into my_student VALUES (NULL, 'Lee 4', 18, 'female ', 2,170); insert into my_student VALUES (NULL, 'wang 5', 19, 'femal', 2,165); insert into my_student VALUES (NULL, 'zhao liu', 25, 'male', 3,190); insert into my_student VALUES (NULL, 'tian 7', 14, 'female, 1,155 ); insert into my_student VALUES (NULL, 'wang Ba', 19, 'male', 3,160); insert into my_student VALUES (NULL, 'chen jiu', 26, 'male', NULL, 195 );

1.1 create a view

• Basic syntax

Create view name as select statement; -- select statement can be a common query; can be a join query; can be a joint query; can be a subquery

 

• Create a single table view: Only one base table exists.

CREATE VIEW v1 AS SELECT * FROM my_student;

CREATE VIEW v2 AS SELECT * FROM my_class;

• Create a multi-Table view: the base table must have at least two

CREATE VIEW v3 AS SELECT * FROM my_student AS s LEFT OUTER JOIN my_class AS c ON s.c_id = c.id ;

Why? We know that both tables have the id field, and we know that the view is a virtual table with a structure but no results. Since it is a virtual table, how can a table have two identical fields?

CREATE VIEW v3 AS SELECT s.*,c.c_name,c.room FROM my_student AS s LEFT OUTER JOIN my_class AS c ON s.c_id = c.id ;

 

1.2 View

• View view: view the View Structure

 

• A view is a virtual table. All viewing methods of the table apply to the table.
  • Show tables;

• • DESC v1;

 

• • Show create table v1;

 

• • A View has a keyword difference over a table: View. You can use the View keyword to View the statement for creating a View.

 

 

• View creation: the system will create a corresponding structure file: frm file under the database folder corresponding to the view.

 

1.3 Use View

• You can use a view as a table for query purposes.

 

• Example: view the v1, v2, and v3 views

SELECT * FROM v1;

SELECT * FROM v2;

SELECT * FROM v3;

 

• View execution: the essence is to execute the encapsulated select statement.

 

1.4 modify View

• The view itself cannot be modified, but the source of the view can be modified.
• Modifying a view is to modify the source statement (select statement) of the view ).

 

• Basic Syntax:

Alter view name as new select statement;

ALTER VIEW v1 AS SELECT id,NAME,age,gender FROM my_student;

SELECT * FROM v1;

 

1.5 delete a view

• Basic syntax

Drop view name;

CREATE VIEW v4 AS SELECT * FROM my_student;SHOW TABLES;

DROP VIEW v4;SHOW TABLES;

 

1.6 significance of views

• ① The view can save SQL statements: Save a complex query statement using the view, and then operate the view directly.
• ② Data Security: View operations are mainly targeted at queries. If the view structure is processed (Deleted), the basic table data is not affected and the data is relatively secure.
• ③ Views are often used in large projects and are used by multiple systems: Useful data can be provided externally, but key (useless externally) data can be hidden, so that data can be relatively secure.
• ④ The view can provide friendliness to the outside: different views provide different data, as if they were specially designed.
• ⑤ The view can better (easily) control permissions.

 

1.7 view Data Operations

• Views can indeed write data, but they are limited.

 

New data in the 1.7.1 View

• Adding data directly adds data to the view.

 

• Data cannot be added to a multi-Table view.

Insert into v3 VALUES (NULL, 'ha ha ', 50, 'female', 1,180, 'java002 ban', 'c05 ');

• You can insert data to a single table view. However, the fields contained in the view must have all fields in the base table that cannot be empty (or have no default value.

-- ADD student id alter table my_student ADD number VARCHAR (5) not null after id; -- modify student id UPDATE my_student SET number = '001' WHERE id = 1; UPDATE my_student SET number = '002 'WHERE id = 2; UPDATE my_student SET number = '003' WHERE id = 3; UPDATE my_student SET number = '004 'WHERE id = 4; UPDATE my_student SET number = '005 'WHERE id = 5; UPDATE my_student SET number = '006' WHERE id = 6; UPDATE my_student SET number = '007 'WHERE id = 7;

-- INSERT a single table view: the view does not contain all fields that cannot be blank (student ID) insert into v1 VALUES (NULL, 'zhang Sanfeng ', 120, 'mal ');

• Views can insert data.

SELECT * FROM v2;

SELECT * FROM my_class;

Insert into v2 VALUES (NULL, 'c ++ 007 ban', 'd13 ');

SELECT * FROM v2;

SELECT * FROM my_class;

 

Delete data from the 1.7.2 View

• The multi-Table view cannot be deleted. Cause: If a view can query the student and class information, when I want to delete a student, the class is deleted, this is embarrassing because no student has been in this deleted class. Imagine the following scenario: if a student is transferred to another class, the student is first deleted from the original class, and then added to the new class. If the multi-Table view can be deleted, it is not because students cannot transfer their jobs. Otherwise, they must delete their original classes. This is terrible.

SELECT * FROM v3;

DELETE FROM v3 WHERE id = 7;

 

 

• A single table view can be deleted.

DELETE FROM v2 WHERE id = 4;

SELECT * FROM v2;

 

1.7.3 view update data

• Theoretically, data can be updated both in a single table view and in a multi-Table view.

SELECT * FROM v3;

UPDATE v3 SET c_id = 1 WHERE id = 7;

SELECT * FROM v3;

 

• Update restriction: with check option. If a field is restricted when a view is added, the system verifies the update of the View data: make sure that after the update, the data can still be queried by the view; otherwise, the update is not allowed.

-- VIEW: age field restriction update create view v4 as select * FROM my_student WHERE age> 20 with check option; -- indicates that the data source of the VIEW is older than 20 years old: where age> 20 -- with check option: When you decide to update data through the view, you cannot change the obtained data age> 20 to a value smaller than 20.

SELECT * FROM v4;

UPDATE v4 SET age = 18 WHERE id = 4;