MySQL Learning note (11) Temp table + view

Original: MySQL learning note (11) 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 TableTmp_table//Create a new temporary table (namevarchar(Ten) not NULL, Valueinteger  not NULL,); Creating a temporary table 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 TableTmp_table//same as our normal delete table statement ...Create Temporary TableTmp_tableSelect * 　  fromtable_name;//Insert the data we have queried directly into the temporary table ...Alter Tabletmp_table rename ttmp_table;//use alter to rename a temporary table ... renameTableTmp_table tottmp_table;//Rename is not allowed in the temp table ... 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 TableUser_info (user_id int  not NULL,   user_name varchar( -) not NULL);Insert  intoUser_infoValues(1,'AA'),(2,'BB').......//Let's say we've inserted 10,000 data messages ... Then we want to query the ID> the  andId<8000 of the data, then we can use a temporary table ...Drop procedure if existsQuery_performance_test;//If this stored procedure is present, delete ... delimiter $$//set the $$ symbol for the MySQL terminator, not the semicolon ...Create procedurequery_performance_test ()begin    //begin with beginDeclareBeginTime//declaring VariablesDeclareEndtime; SetBeginTime=Curtime ();//set to current time ...Drop Temporary if existsuserinfo_tmp;//Delete the current temporary table if the temporary table exists ...Create Temporary TableUserinfo_tmp//New temporary table (I_useridint  not NULL, V_usernamevarchar( -) not NULL) Engine=memory; Insert  intoUserinfo_tmp (I_userid,v_username)Select user_id,user_name  fromUserInfowhereUserid> the  andUserid<8000;//put the data we want to query into the temp table ...Select *  fromuserinfo_tmp; SetEndtime=Curtime (); SelectEndtime-BeginTime;End  //The stored procedure definition is complete, end With end ... delimiter;//redefine the end symbol 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 TABLEStudent (StunoINT, StunameNVARCHAR( -))CREATE TABLEStuinfo (StunoINT, classNVARCHAR( -), CityNVARCHAR( -))INSERT  intoStudentVALUES(1,'Wanglin'),(2,'Gaoli'),(3,'Zhanghai')INSERT  intoStuinfoVALUES(1,'Wuban','Henan'),(2,'Liuban','Hebei'),(3,'Qiban','Shandong')--Create a ViewCREATE VIEWStu_class (Id,name,glass) as SELECTstudent. ' Stuno ', student ' stuname ', Stuinfo. ' Class ' fromStudent, StuinfoWHEREStudent. ' Stuno '=stuinfo. ' Stuno 'SELECT *  fromStu_class//Show the results of the view ...+----+----------+--------+|Id|NAME|Glass|+----+----------+--------+|  1 |Wanglin|Wuban||  2 |Gaoli|Liuban||  3 |Zhanghai|Qiban|+----+----------+--------+describe Stu_class;//Show basic information for a view ... showTableStatus like 'Stu_class'//use the Show method to display information about a view .... showCreate ViewStu_class//Show View Details ... Show view name basic information+code for internal operations in the view, etc... To modify a view:1. Using the Createor ReplaceMySQL>Delimiter//MySQL> Create or Replace View' Stu_class ' as Select      -' Student '. ' Stuno ' as' ID ' from(' Student 'Join' Stuinfo ')//join joins two tables in a joint ... - where(' Student '. ' Stuno '=' Stuinfo '. ' Stuno ')//Delimter;descStu_class;// descis the abbreviation of Descbibe, write in the database which all allow ...Select *  fromStu_class; 2. Use the Alter ... Alter ViewStu_class as SelectStuno fromstudent; Update view ... UPDATEStu_classSETStuname='Xiaofang' WHEREStuno=2//very simple, nothing too much .... To delete a view:Drop View if existsStu_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 Learning note (11) Temp table + view