SQL
1. [Mandatory] Do not use count(column name) or count(constant) instead of count(*). count(*) is the standard syntax for counting the number of rows defined by SQL 92, which has nothing to do with the database. It has nothing to do with NULL and non-NULL.
Note: count(*) will count rows with NULL values, while count(column name) will not count rows with NULL values in this column.
2. [Mandatory] count(distinct col) Calculate the number of unique columns except NULL. Note that count(distinctcol 1, col 2 ) returns 0 if one of the columns is all NULL , even if the other column has a different value.
3. [Mandatory] When the values of a certain column are all NULL, the return result of count(col) is 0, but The return result of sum(col) is NULL, so you need to pay attention to the NPE problem when using sum().
Positive example: You can use the following method to avoid the NPE problem of sum: SELECT IF(ISNULL(SUM(g)),0, SUM(g)) FROM table;
4. [Mandatory] Use ISNULL() to determine whether it is a NULL value. Note: A direct comparison of NULL with any value is NULL.
Note:
1) NULL<>The return result of NULL is NULL, not false.
2) The return result of NULL=NULL is NULL, not true.
3) The return result of NULL<>1 is NULL, not true.
5. [Mandatory] When writing paging query logic in the code, if count is 0, it should be returned directly to avoid executing subsequent paging statements.
#6. [Mandatory] Foreign keys and cascades are not allowed. All foreign key concepts must be solved at the application layer.
Explanation: (Concept explanation) student_id in the student table is the primary key, then student_id in the grades table is the foreign key. If you update the student _ id in the student table and trigger the update of student _ id in the grades table at the same time, it is a cascade update. Foreign keys and cascade updates are suitable for low concurrency on a single machine and are not suitable for distributed and high-concurrency clusters; cascade updates are strongly blocking and have the risk of database update storms; foreign keys affect the insertion speed of the database.
7. [Mandatory] The use of stored procedures is prohibited. Stored procedures are difficult to debug and expand, and have no portability.
8. [Mandatory] When revising data, deleting or modifying records, you must select first to avoid accidental deletion. Only after confirmation can you execute the update statement.
9. [Recommendation] Avoid the in operation if it can be avoided. If it cannot be avoided, you need to carefully evaluate the number of collection elements after the in operation. , controlled within 1,000.
10. [Reference] If there is a need for globalization, all character storage and representation are encoded in utf -8, then the character counting method
Note:
Instructions ：
SELECT LENGTH("Easy Work"); Returns to 12
SELECT CHARACTER _ LENGTH("Easy to Work"); Returns to 4
If you want If you use emoticons, use utfmb 4 for storage. Pay attention to the difference between it and utf-8 encoding.
11. [Reference] TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources, but TRUNCATE has no transactions and does not trigger triggers. It may cause accidents, so it is not recommended to use this statement in development code.
Note: TRUNCATE TABLE is functionally the same as the DELETE statement without a WHERE clause.
