Mysql Temp Table + view

Learning content:

Basic operations for staging tables and views ...

Scope of use of temporary tables and views ...

1. Temporary tables

Temporary tables: temporary tables, presumably everyone knows the existence of this concept ... But when should we use the temporary table? When there is a large amount of data in a database, we want to get a subset of this data set, then we can use temporary table to save the data we want. Then you can work on the temp table ... There must be a reason for using temporary tables. Using temporary tables will speed up query performance for your database ....

Create temporary table tmp_table  //Create a new temporary table (    name varchar () not NULL,    value integer is not null), temporary table creation is simple, Just add a temporary keyword to complete the creation of the table ... Temporary table when we are disconnected from the database, the system will automatically delete the temporary table and free up its footprint. In addition to this way, we can also manually delete ... drop table tmp_table//Same as our normal delete Table statement ... create temporary table tmp_table SELECT * FROM table_name;//insert our query data directly into the temporary table ... ALTER TABLE tmp_table rename ttmp_table;//use alter to rename the staging table ... rename table tmp_table to Ttmp_table;//rename is not allowed in temporary tables ... Error will occur ...

It is not that we are using the temporary table database query performance will certainly be improved, when our data use a good index, the temporary table may not be fast ... So is it possible to use temporary tables at any time? The use of temporary tables also has the following limitations:

I. Temporary tables can only be used under the MEMORY,MYISAM,MERGE,INNODB storage engine ...

II. TEMP table does not support MySQL cluster.

Iii. in the same query statement, we can only query the temporary table once.

The Iv.show table statement does not enumerate temporary table information:

V. You cannot use Rename to rename a temporary table, but we can use the ALTER TABLE statement instead ...

To give a practical example:

CREATE TABLE User_info (user_id int NOT NULL, user_name varchar () is not null), insert into user_info values (1, ' AA '), (2, ' BB ') ...//Suppose we insert 10,000 data messages ... Then we want to query the data information of id>5000 and id<8000, then we can use a temporary table ... drop procedure if exists query_performance_test; If this stored procedure is present, delete ... delimiter $$//Set $$ symbol for MySQL Terminator instead of semicolon ... create PROCEDURE query_performance_test () BEGIN//  Beginning with begin declare begintime;       DECLARE variable declare endtime;  Set Begintime=curtime ();  Set to current time ... drop temporary if exists userinfo_tmp;            Delete the current temporary table if a temporary table exists ... create temporary table userinfo_tmp//new temporary table (I_userid int NOT NULL,       V_username varchar (+) not null) engine=memory; Insert into Userinfo_tmp (i_userid,v_username) Select User_id,user_name from UserInfo where userid>5000 and userid<  8000;       Put the data we want to query into a temp table ... select * from Userinfo_tmp;       Set Endtime=curtime (); Select endtime-begintime;end//Stored procedure definition complete, end With end ... delimiter; Will endThe symbol is redefined as the default semicolon .... Call Query_profromance_test (); Call stored procedure ....

The final result will output the data in the ID 5000-8000, and will also output the query time information ....

2. View

Views are divided into normal and materialized views, and normal view is a virtual table, which is to reclassify the data from the underlying data tables in the database, making it easier to use and understand. Materialized view is an entity table, in addition to view data for view storage, other similar to normal view, but the query speed is generally faster than the normal view, generally used for large data volume view.

Advantages:

1. Security: Generally when we create a database, there are some important information that we do not want users to see, then we can create a view to set a permission, so that users can only view their own basic data ... More important data users are not able to get ...

2. The performance of the query has improved ...

3. For complex queries, the problem can be decomposed and multiple views will be created to get the data. Combine the views together to get the results you need. For example, when we are querying in multiple tables, we want to use a uniform way of querying, then we create a view to put the data of each table into the view ... Finally we operate on the view, we can get the desired data information ....

CREATE TABLE student (Stuno int, stuname NVARCHAR) CREATE TABLE stuinfo (Stuno int, class NVARCHAR, City NVARCHAR (60 ) insert into student values (1, ' Wanglin '), (2, ' Gaoli '), (3, ' Zhanghai ') inserts into Stuinfo values (1, ' Wuban ', ' Henan '), (2 , ' Liuban ', ' Hebei '), (3, ' Qiban ', ' Shandong ')--Creates a view of the Create views Stu_class (Id,name,glass) as SELECT student. ' Stuno ', Student. ' Stuname ', Stuinfo ' class ' from student, Stuinfo WHERE student. ' Stuno ' =stuinfo. ' Stuno ' SELECT * from stu_class// Display the results of the view ... +----+----------+--------+| ID | NAME |  Glass |+----+----------+--------+| 1 | Wanglin |  Wuban | | 2 | Gaoli |  Liuban | | 3 | Zhanghai | Qiban |+----+----------+--------+describe stu_class;//display basic information about the view ... show table status like ' Stu_class '// Use the Show method to display information about the view .... Show CREATE VIEW Stu_class//Show View Details ... Show view name basic information + code for internal actions in view, etc... modifying views: 1. Use Create or replacemysql> delimiter//mysql> Create or replace view ' Stu_class ' as select ' Student ' . ' Stuno ' as ' id ' from (' Student ' join ' stuinfo ')//join union of two tables... Where (' student '. ' Stuno ' = ' stuinfo '. ' Stuno ')//delimter;desc Stu_class;  DESC is the abbreviation of Descbibe, which is written in the database which allows ... select * from Stu_class; 2. Use alter ... Alter VIEW Stu_class as select Stuno from student; Update views ... UPDATE stu_class SET stuname= ' Xiaofang ' WHERE stuno=2//is simple, nothing too much .... Delete view: Drop view if exists stu_class;

Update considerations:

(1) The view contains columns that are defined as non-empty in the base

(2) A mathematical expression is used in the field list after the SELECT statement of the definition view

(3) Use aggregate function in the field list after defining the view's SELECT statement

(4) Distinct, UNION, TOP, GROUP by, having clauses are used in the SELECT statement that defines the view

So when are we going to use the view?

As we define the stored procedures, we encapsulate the code in the stored procedure in order to speed up the query and complicate the processing of the data, and we can complete our query operation when we call the stored procedure ... Through the code to complete the operation of the data .... To make it easy for us to call the stored procedure every time we use this type of query .... The structure of a stored procedure belongs to a collection ...

The structure of the view is a table, a virtual table, not the actual storage, but we operate the view at the same time, then also represents the operation of the view of the base table ... The view lets us see the data information we want ... Give me the feeling that using the view or because of his security, you can save important information. Users can only manipulate their own part of the information through the view, without permission to manipulate other important information ....

Mysql Temp Table + view