Shulou(Shulou.com)06/01 Report--

How to write elegant SQL native statements? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Foreword:

In the previous article, when talking about the basic architecture of Mysql, it gave a comprehensive explanation of how sql query statements are executed in MySql architecture. Know the specific execution flow of the sql query statement in the MySql architecture, but in order to write the sql statement better and faster, I think it is very necessary to know the execution order of the clauses in the sql statement. Friends who have read the previous article should know that the execution of the last clause of the sql statement should be completed in the executor, and the storage engine provides a data read and write interface to the executor. Now start our study.

Summary of the complete execution order of each clause in the statement (according to the sequence number)

From (Note: subclauses in from are also included here)

Join

On

Where

Group by (start using aliases in select, which can be used in subsequent statements)

Avg,sum.... Equal aggregate function

Having

Select

Distinct

Order by

Limit

Execution sequence analysis of each clause

All queries are executed from from, and during execution, each step generates a virtual table for the next step, which will be used as input for the next step.

1. From

Form is the beginning of a query statement.

If it is a table, it will be manipulated directly.

If the from is followed by a subquery, the contents of the subquery are executed first, and the result of the subquery is the first virtual table T1. (note: the execution process in the subquery is also in the order described in this article.)

If you need an associated table, use join, see 2Jing 3

2. Join

If from is followed by multiple tables and join associations, a Cartesian product of the first two tables will be performed first, and the first virtual table T1 will be generated (Note: a relatively small table will be selected as the base table)

3. On

ON filtering is performed on the virtual table T1, and only those rows that match are recorded in the virtual table T2. (note that if there is a third table associated with it here, we will use T2 and the third table to produce T3 table by Cartesian product, and continue to repeat the 3. On step to generate T4 table, but the following sequence explanation is not for T3 and T4 here, but just continues from a table association query T2.)

4. Where

WHERE conditional filtering is performed on the virtual table T2. Only matching records are inserted into virtual table T3.

5.group by

The group by clause combines the unique values in into a group to get the virtual table T4. If group by is applied, then all subsequent steps can only operate on the column of T 4 or execute 6. 5. Aggregate functions (count, sum, avg, etc.). (note: the reason is that the final result set after grouping contains only one row in each group. Keep in mind that otherwise there will be a lot of problems here, the following code misunderstanding will be specifically said. )

6. Avg,sum.... Equal aggregate function

The aggregate function only does some processing on the results of the grouping, gets some desired aggregate values, such as summation, statistical quantity, etc., and does not generate a virtual table.

7. Having

Apply the having filter to generate T5. The HAVING clause is used primarily with the GROUP BY clause, and the having filter is the first and only filter that applies to grouped data.

8. Select

Perform the select operation, select the specified column, and insert it into virtual table T6.

9. Distinct

De-duplicate the records in T6. Remove the same row, resulting in a virtual table T7. (note: in fact, if the group by clause is applied, then distinct is redundant, also because when grouping is to group the only values in the column into groups, and only one row of records is returned for each group, then all records will be different. )

10. Order by

Apply the order by clause. Sort T7 by order_by_condition, which returns a cursor instead of a virtual table. Sql is based on the theory of set, the set does not sort its rows in advance, it is just a logical collection of members, and the order of members does not matter. A query that sorts a table can return an object that contains a logical organization of a specific physical order. This object is called a cursor.

Several explanations of oder by

Because the order by return value is a cursor, queries using the order by clause cannot be applied to table expressions.

Order by sorting is costly, and it's best not to specify order by unless you have to sort it.

Two parameters of order by asc (ascending sort) desc (descending sort)

11. Limit

Fetch the record of the specified row, generate the virtual table T9, and return the result.

The parameter after limit can be a limit m or limit m n, representing data from article m to article n.

(note: many developers like to use this statement to solve paging problems. For small data, there is no problem with using the LIMIT clause, and when the amount of data is very large, using LIMIT n, m is very inefficient. Because the mechanism of LIMIT is to scan from scratch every time, if you need to start from the 600,000 rows and read three pieces of data, you need to scan to 600000 rows and then read them, and the scanning process is a very inefficient process. Therefore, when dealing with big data, it is very necessary to establish a certain caching mechanism in the application layer.

Develop a section of sqlSELECT `userspk`.`avatar` AS `user _ id`, `a`.`user _ id`, `a`.`user _ record`, MAX (`score`) AS `score`FRom (select * from pkrecord order by score desc) as an INNER JOIN `userspk`AS `userspk`user _ id` = `userspk`.`user _ id`where `a`.status` = 1 AND `a`.`user _ id`! = 'm_6da5d9e0-4629-11e9-b5f7-694ced396953' GROUP BY `user_ `ORDER BY `a`sco`re`DESC LIMIT 9

Query results:

First, let's briefly talk about what I want to inquire about:

Want to query the top 9 user records and their avatars in the competition record table.

Actually think about the execution order of the words and sentences of sql through this sql.

According to the data structure design of the competitive record table, each user may have multiple records under each library every day, so it needs to be grouped, and the query results only want to get the highest record in each group.

Some instructions for this sql:

Some students may think that it is not necessary for the subquery to directly query the competition record table, but can not get the expected results, because each group result after the grouping is not sorted, and the highest score obtained by max must be the highest score under the corresponding grouping, but other records may not be the record corresponding to the highest score. Therefore, the subquery is very necessary, it can sort the original data first, and the item with the highest score is the first record corresponding to the first one.

If you look at the code and compare the execution results with those with subqueries, you can understand a paragraph I said above:

/ / do not use the subquery SELECT `userspk`.`avatar`AS `user_ avatar`, `pkrecord`.`user _ id`, `pkrecord`.`user _ record`, `pkrecord`.`id`, MAX (`score`) AS `score`FROM pkrecordINNER JOIN `userspk`userspk`ON `pkrecord`.`user _ id` = `userspk`.user _ id`Were `pkrecord`.status` = 1 AND `pkrecord`.`user _ id`! = 'm_6da5d9e0-4629-11e9-b5f7-694ced396969' GROUP BY `user_ id`ORDER BY `pkrecord`.status` DESC LIMIT 9

Query result

two。 After the data has been sorted in the subquery, the outer sorting method is in reverse order if the sorting score is the same as that of the subquery, and the outer sorting can be removed, so there is no need to write twice.

When aliases in sql statements are used

In the SQL statement, you can specify aliases for table names and field (column) names

Table name specifies alias

When querying the data of two tables at the same time: before setting the alias:

SELECT article.title,article.content,user.username FROM article, userWHERE article.aid=1 AND article.uid=user.uid

After setting the alias:

SELECT a.title,a.content,u.username FROM article AS a, user AS u where a.aid=1 and a.uid=u.uid

Benefits: using table alias queries can make SQL concise and easier to write and read, especially when SQL is more complex

Query field specifies alias

Query a table and set an alias for the query field directly

SELECT username AS name,email FROM user

Query two tables

Benefits: an obvious effect of field aliases is that you can customize the field names returned by query data; when two tables have the same fields that need to be queried, aliases can be used to perfectly distinguish and avoid conflicts.

SELECT a.title AS atitle,u.username,u.title AS utitle FROM article AS a, user AS u where a.uid=u.uid

When associating a query, when associating the table itself, some classification tables must use aliases.

Aliases can also be used in both group by and having

Aliases can be used in order by sorting

Check the above paragraph sql

Aliases can be used for both delete and update MySQL, especially for multiple table (cascading) deletions.

Delete t _ 1 from t _ 1 from t _ b t _ 2 where t1.id = t2.id

Aliases are required for subquery results

Check the above paragraph sql

Considerations for using aliases

Although the AS keyword that defines field aliases can be omitted, it is recommended that the AS keyword not be omitted when using aliases

Matters needing attention in Writing sql sentences attention in Writing Standards

String type should be in single quotation marks

Each field after the select should be separated by a comma, but do not add a comma to the field connected to the from at last.

Use aliases when creating temporary tables using subqueries, otherwise an error will be reported.

In order to enhance performance attention

Do not use "select * from." Return all columns and retrieve only the required columns, which can avoid unnecessary program modifications caused by subsequent changes in the table structure, and reduce the additional resources consumed

Do not retrieve known columns

Select user_id,name from User where user_id = '10000050'

Use parameterized search criteria such as =, >, > =, 100 * 2 to use the index on the amount column

For example, where substring (Lastname, 1,1) ='F' cannot use the index on the Lastname column, while where Lastname like'F%'or where Lastname > ='F' and Lastname

< 'G'就可以 在有min、max、distinct、order by、group by操作的列上建索引，避免额外的排序开销（索引相关） 小心使用or操作，and操作中任何一个子句可使用索引都会提高查询性能，但是or条件中任何一个不能使用索引，都将导致查询性能下降，如where member_no = 1 or provider_no = 1，在member_no或provider_no任何一个字段上没有索引，都将导致表扫描或聚簇索引扫描（索引相关） Between一般比in/or高效得多，如果能在between和in/or条件中选择，那么始终选择between条件，并用>

= and

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.