How long has it been since I learned SQL systemically last time? It seems to be a long time. Work to write SQL and HAVE a new understanding of SQL, coupled with the recent plan to write SQL optimization related articles, here and systematically comb through the understanding of SQL, is also the MySQL optimization series of articles.
SQL Execution sequence
The basic structure of SQL is as follows:
SELECT DISTINCT
< select_list >
FROM
< left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition >
ORDER BY
< order_by_condition >
Copy the codeSELECT * from (SELECT); SELECT * from (SELECT);
FROM <left_table> ON <join_condition> <join_type> JOIN <right_table> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> ORDER BY <order_by_conditionSELECTThen execute, if usedselectAliases come out)SELECT
DISTINCT <select_list>
LIMIT <limit_number>
Copy the codeI’ve been trying for a while to think of SQL execution as a for loop, where FROM is a traversal, and SELECT is an output statement, but that doesn’t seem to explain why you can have a SELECT inside a SELECT statement, so I’ve patched this up, where a SELECT statement is an output statement, in two rows. However, there is always something wrong with using the for loop of the programming language to understand SQL, so I will return SQL to SQL this time.
- First the connection
Join without on filtering, then the records in each table are taken out in turn and the matching combination is added to the result set and returned to the user. Here is an example of the connection process:
Join table t1 with table T2 to form a new, larger record.
SELECT * FROM t1,t2
Copy the codeThe result set of a join query contains a combination of each record in one table and each record in another table. A result set like this can be called a Cartesian product. Table T1 has 3 records, and Table T2 has 3 records. The result set formed in the end is 9 records as shown in the figure above. The Cartesian product can be very large if we join small tables without any restrictions. For example, if three tables with 100 rows are joined together, the Cartesian product is huge, with 100×100×100= a million rows, so it is especially necessary to add filtering conditions when rejoining. If an inner join is followed by where, then WHERE participates in the inner join.
Left Join, right Join, inner join, left join, left join, right join, inner join. Here we review the semantics of these outer joins:
LEFT JOIN is written as follows: SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON The semantics are to fetch all records of T1 and those that meet the connection conditions of T2, and fill the records that cannot match T1 and T2 with NULL. For the convenience of writing, here we prepare two tables:
CREATE TABLE `score` (
`id` int(11) NOT NULL COMMENT 'Unique identifier'.`coursename` varchar(255) COMMENT 'Course Name'.`score` int(255) NULL DEFAULT NULL COMMENT 'results'.`number` varchar(255) DEFAULT NULL COMMENT 'student id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `student` (
`id` int(11) NOT NULL COMMENT 'Unique identifier'.`name` varchar(255) COMMENT 'name'.`number` varchar(255) COMMENT 'student id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
Copy the codeSELECT * FROM student s1 left join score s2 on s1.number = s2.number
The final result:
Because Wang Wu did not have a matching score in the score list, but it still appeared in the result set, NULL was added.
RIGHT connection standard writing: SELECT * FROM t1 RIGHT/OUTER JOIN t2 ON connection conditions where [ordinary filter conditions], semantic to remove all records of t2 and t1 connection for the qualified record, t2 and t1 cannot match record, fill the NULL. For the convenience of writing.
Example:
SELECT s1.name,s2.coursename ,s2.score,s2.number FROM student s1 right join score s2 on s1.number = s2.number
Copy the code
If we just want to look at two tables matching each other, then we can get an INNER JOIN. This is actually another way of writing the INNER JOIN described above. If two tables do not meet the conditions in on during the JOIN process, they will not appear in the result set.
SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ONConnection conditions] [WHEREGeneral filter condition];Copy the codeThe following three statements are equivalent to each other:
SELECT * FROM t1 join t2;
SELECT * FROM t1 inner joinT2 (this is the most common form)SELECT * FROM t1 CROSS JOIN t2;
SELECT * FROM t1,t2
Copy the codeRoughly speaking, we can think of “join” as glue, because it can join two tables into one.
- Then the group by
We have a new result set, or a new table, from the above connection. Now what if we want to figure out the total score of each person, from which we get group by.
SELECT number.sum(score) FROM score GROUP BY number
Copy the codeThe score will be cut like this:
Sometimes a column is too large, and we want to group each group according to this column. For example, suppose we add a field attribute to the score table, which indicates that this course belongs to science or arts, then group 001 can be divided into two groups, as shown in the figure below:
Our SQL can be regrouped as follows:
SELECT number,sum(score) FROM score GROUP BY number,attribute
Copy the codeIn general, mainstream databases require that only grouped columns be allowed after using group BY. The reason is also very simple. There are several non-grouped columns. If I select the same non-grouped columns as the grouped columns, and they all happen to be the same, do you have a choice problem? The MYSQL designers think you have a point, and they have ONLY_FULL_GROUP_BY mode, In version 5.7.x or later, this function is enabled by default, allowing non-grouped columns to appear after SELECT.
- Another is having
The points of having are the group column and the aggregate function on the group.
Where is executed before HAVING.
- Is the order by again
Each of the above steps results in the virtual table, which is sorted at this step.
- Select and DISTINCT
SELECT columns from a virtual table. SELECT columns from a virtual table. SELECT columns from a virtual table.
To sum up, an SQL execution order is roughly as follows:
Subquery to learn
Several forms of subqueries are summarized below.
- Two-column subquery
As opposed to a single column subquery, we usually say SELECT * FROM Student where name in (‘ c ‘, ‘C’).
But what if we have two columns, and we find the record where the student number is 001 and the grade is 80, we can write this:
SELECT * FROM SCORE WHERE (number,score) in ( SELECT ‘001’, ’80’)
- The EXISTS and the NOT EXISTS
Sometimes the outer layer does not care what the result is in the subquery, but only whether the result set of the subquery is empty. Here we use EXISTS and NOT EXISTS
EXISTS(SELECT … ) The expression is true if the subquery result set is not an empty set
NOT EXISTS The result is true if the subquery result set is empty
- ANY/SOME
ANY is either of the following: column comparison_operator ANY/SOME(subquery) Comparison_operator is the operator, such as greater than or equal.
If only the columns and subqueries match by comparison_operator, the entire expression is valid.
- ALL
ALL, in contrast to ANY, requires ALL matches.
Statements are written in standard order
SELECT DISTINCT FROMThe table name WHEREGROUP BYHAVINGORDER BY
Copy the codeThis is the standard ORDER of SQL syntax. You must write the SQL in this order; otherwise, SQL syntax errors will be reported.
The resources
-
Sentences.finally.an: MySQL architecture overview – > – > parse SQL query execution process order www.cnblogs.com/annsshadow/…
-
How to use MySQL: Learning MySQL nuggets from scratch