Use of combined query of visual charts and union in mysql

I. Reasons for using the view:

 

1: security. This is generally done: create a view, define the data operated on the view, and then bind the user permission to the view.

 

2: query performance is improved.

 

3: After flexible functions are required, you need to modify the table structure, resulting in a large workload. Therefore, you can use virtual tables to achieve the effect of less modification, which is useful in actual development.

 

4: complex query requirements or sorting can be divided into multiple views to obtain data. Combine the view to obtain the expected result.

 

II. This view is used because two tables are queried in the project and sorted by pages, but the data fields of the two tables are different. If the data fields of the two tables are changed, the SQL statements in other places need to be modified, therefore, a view is created to solve this problem:

 

1: Create a view: directly use phpMyAdmin. Of course, you can also use the SQL statement as follows: enter a table in the database and you will see the new view function: 2: write SQL, obtain and name the corresponding fields and create a proper view: the optional ALGORITHM clause is an extension of standard SQL MySQL. ALGORITHM can have three values: MERGE, TEMPTABLE, or UNDEFINED. Without the ALGORITHM clause, the default ALGORITHM is UNDEFINED (UNDEFINED), which affects the way MySQL processes views. For more information about the specific method, see du Niang.

 

A user is authorized to use a user. No selection is made here, and no selection is made below. Enter the View name directly. The field name is blank. Enter your own SQL statement in.

 

With check option indicates that the view must be updated within the permission range of the attempt (optional)

 

CASCADED: when updating a view, all related views and table conditions must be met.

 

LOCAL: when updating a view, you must satisfy the conditions defined by the view.

 

3: no selection this time. Click "execute" to generate the view, as shown below:

 

View creation syntax:
Create view name as select statement;
4. What are the advantages of using a view?
① Simplified query statement
For example, if there is a commodity table, we often need to check the average price of the commodities in each topic.
Select cat_id, avg (shop_price) from goods gropy by cat_id;
In this case, we can create a view:
Create view avuplice as select cat_id, avg (shop_price) from goods gropy by cat_id;
After the creation, when we want to check the average price of each topic
Select * from avuplice.
② Permission control is supported
The table permission is closed, but the corresponding view permission is open. Only some data columns are open in the view.
For example, in our goods product table, we don't want others to see our sales prices. In this case, we can close the permission to view the product table and create a view.
Create view showGoods as select goods_id, goods_name from goods;
You can do it without the sales price.
③ This can be used in big data table sharding.
For example, if the table's row data exceeds 2 million rows, the speed slows down.
You can split the data of a table into four tables for storage.
News table
Newsid 1, 2, 3, 4...
News1, news2, news3, and news4
There are many ways to distribute data from one table to four tables,
The most common calculation is id modulo.
Id % 4 + 1 = [1, 2, 4]
...
You can also use a view to form one view of the four tables.
Create view news as select * from news1 union select * from news2 union...
5. View modification
Alter view name as select statement;
6. Relationship between views and tables
A view is the query result of a table. Changing the data of a natural table will affect the view result.
7. Will changes to the view affect the table?
① Adding, deleting, and modifying a view will also affect the table;
② However, views cannot always be added, deleted, or modified;
Views and table data can be modified in a one-to-one manner;
For the insert view, note that the view must contain columns without default values in the table.
8. View algorithm (Operation rule)
Algorithm = merge/temptable/undefined
Merge: When a view is referenced, the statements that reference the view are merged with the statements that define the view.
This means that the view is only a rule and a statement rule. When querying a view
For example, the where clause is combined with the statement where clause at the time of creation to analyze and form a select statement.
Give a column:
First, we create a view to query all products with prices greater than 3000.
Create view g2 as select goods_id, goods_name, shop_price from goodswhere shop_price> 3000;
Then, when querying the view, add a where condition <5000
Select * from g2 where shop_price <5000
At this time, it will combine and analyze the two statements to form such a select statement.
Select goods_id, goods_name, shop_price from goods where shop_price> 3000 and shop_price <5000;
 
Temptable: creates a temporary table instantly based on the creation statement, and then queries the View statement to query data from the temporary table.
 
There is a significant difference between merge and temptalbe:
Merge eventually queries the goods table, while temptable queries the virtual table.
For example, we want to get the most expensive item under each topic.
First, we create a view to find out that the items in each column are sorted by price in descending order.
Create view lmj as select cat_id, goods_id, goods_name, shop_price from goods order by cat_id, shop_price desc;
At this time, when we query this view, can we group the cat_id to get the desired result?
Select * from lmj group by cat_id;
The answer is no, because it combines the statements for creating a view and the statements for querying a view
Select cat_id, goods_id, goods_name, shop_price from goods group by cat_id order by cat_id, shop_price desc;
 
If we specify the Operation rule "temptable" when creating a view
Create algorithm = temptable view lmj as select cat_id, goods_id, goods_name, shop_price from goods order by cat_id, shop_price desc;
Then we can query the view: select * frm lmj group by cat_id; to get the expected result.
It first places the results obtained by the select cat_id, goods_id, goods_name, shop_price from goods order by cat_id, shop_price desc; statement into a temporary table, then we can query the temporary table to obtain the expected result, instead of merging it and then querying it.
Undefined: undefined. It is automatic and can be selected by the system.

After The View is created, you can operate on it. The view is similar to a bridge. You can use SQL to create a table with the content and information you need to query, easy to use.

 

III. union query

 

1: union means to combine the results of two or more queries.

 

Requirement: the number of columns in the two queries must be the same, which is also the reason for the view of appeal and the string sorting must be the same. Otherwise, a union error is prompted,

 

When you appeal to write a view SQL statement, the field names must correspond one to one, and the order cannot be messy, because union takes the column name of the first SQL statement as the standard,

 

If the rows extracted from different statements are identical (the values of each column are the same), union merges the same rows and retains only one row. We can also understand that union removes duplicate rows.

 

If you do not want to remove duplicate rows, you can use union all.

 

The following example illustrates the benefits of joint queries (inline, left, right, and full:

Table T1 structure (username and password)
Userid (int) usernamevarchar (20) passwordvarchar (20)
1 jack jackpwd
2 owen owenpwd

Table T2 structure (username and password)
Userid (int) jifenvarchar (20) dengjivarchar (20)
1 20 3
3 50 6

1. Inline (inner join)
If you want to list user information, points, and levels, you will generally write as follows:

Select * from T1, T3 where T1.userid = T3.userid
(In fact, this result is equivalent to select * from T1 inner join T3 on T1.userid = T3.userid ).

Combine the rows with userid in both tables into one row (that is, inline), but the efficiency of the latter is much higher than that of the former. We recommend that you use the latter method.

SQL statement:
Select * from T1 inner join T2 on T1.userid = T2.userid

Running result
T1.userid username password T2.userid jifen dengji
1 jack jackpwd 1 20 3

 

Second: left outer join)
Show all rows in T1 of the left table, and add the qualified rows in T2 of the right table to T1 of the left table;
If the table T2.

SQL statement:
Select * from T1 left outer join T2 on T1.userid = T2.userid

Running result
T1.userid username password T2.userid jifen dengji
1 jack jackpwd 1 20 3
2 owen owenpwd NULL

 

Third: right outer join ).
Show all rows in the right table T2, and add the qualified rows in the left table T1 to the right table T2;
If the table T1 on the left does not meet the conditions, it does not need to be added to the result table and is NULL.

SQL statement:
Select * from T1 right outer join T2 on T1.userid = T2.userid

Running result
T1.userid username password T2.userid jifen dengji
1 jack jackpwd 1 20 3
NULL 3 50 6

 

Fourth: full outer join)
Display All rows on both sides of the left table T1 and right table T2, that is, combine the left join result table and the right join result table, and then filter out duplicate rows.

SQL statement:
Select * from T1 full outer join T2 on T1.userid = T2.userid
 
Running result
T1.userid username password T2.userid jifen dengji
1 jack jackpwd 1 20 3
2 owen owenpwd NULL
NULL 3 50 6

In summary, the efficiency of joint queries is indeed relatively high. If the four joint methods can be used flexibly, the complex statement structure will also be simple.

In the case of order by and paging limit, you need to use () to enclose the SQL statement.

For example:

(SELECT * FROM view_price) union all (SELECT * FROM view_combo </span> <span class = "s2">) LIMIT 1, 10

Total number of computations:

Select count (*) from (SELECT * FROM view_price) UNION All (SELECT * FROM view_combo) as T

I personally think this method is very useful. When dealing with some troublesome data, although the SQL statement for creating a view will be changed every time during later maintenance, however, when used, the advantage is greater than the disadvantage. You are welcome to criticize and correct the content above!