Shulou(Shulou.com)05/31 Report--

This article will explain in detail what the SQL sentence specifications are, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Writing style

1. Statement keywords should be all lowercase.

two。 Use single quotation marks when referencing characters. Such as: update testable set idcol='abcd'.

3. A space should be added before and after the connectors or operators or, in, and, =, +, -, etc. Otherwise, it is easy to lead to the following similar problems. For example, in the statement select a murb from table, ameme b is a variable. When spelling the statement, if a = 6 and b =-3, the statement becomes select 6 Murray 3 from table. -- A comment that is regarded as a SQL, resulting in an error in the statement.

4. Do not use "select * from …" Syntax, you must indicate the field name. Select col1, col2,... From tablea where...

5. The use of "insert into table_name values" is strictly prohibited. Syntax, uniformly use "insert into table_name (col1,col2,...) values (?,...)".

6. When the SQL statement contains a multi-table join, you must add the alias of the table, and use each field with the alias of the table.

Select a.col1, a.col2, b.col3 from tablea a, tableb b where a.col4=b.col5

7. Explicit or implicit type conversions should be avoided. For example, a comparison of numeric and int columns in the where clause.

8. Parentheses must be added before and after the subquery.

Select col1, col2 from tablea where col3 in (select col4 from tableb where col4 > 0)

9. Only one SQL should be executed at a time, and if more than one statement should be executed separately, but must remain in one transaction. It is not possible to execute multiple statements separated by semicolons and so on at a time, so the processing is not clear.

10. If or can be used instead, it is not appropriate to use in statements. The number of elements in an in statement must not exceed 500. if it does, it should be split into multiple SQL statements. It is forbidden to use xx in (',''... (.) Or xx in (',',').

11. The or connection condition must not exceed 500. if it is exceeded, it should be split into multiple statements.

Performance optimization

1. When querying, you should minimize the reading of excess data and reduce the number of records returned by using the where clause.

two。 If there is a not in (in) operation in the statement, try to use not exists (exists) instead. In particular, there is an obvious difference in retrieval speed between the two with large amounts of data.

3. External connections should not be used. The efficiency of external connection is low.

4. Nested queries with more than 3 layers should not be used in a SQL statement. If it exceeds, it should be handled in an application server program such as Java.

5. You must not fetch numbers from 4 or more tables at the same time in a SQL statement. Tables that only make association or filter conditions and do not involve fetching do not participate in table count calculation; if 4 or more tables must be associated, they should be processed in application server programs such as Java.

6. You should try to avoid using order by and group by sort operations, and if you must, try to build them on indexed columns. Because a large number of sorting operations affect system performance.

7. For the comparison of index columns, you should try to avoid using not or! =, which can be divided into several conditions. Because "not" and "! =" do not use indexes. If col1 is an index column, the condition col1! = 0 can be split into col1 > 0 or col2 1000). Instead, you should use the following statement: select name from customer inner join order on customer.customerId = order.customerId where order.money > 100. Or select name from customer where exists (select 1 from order where money > 1000 and customer.customerId = order.customerId)

11. When multi-table association query, the writing method can follow the following principles, which is conducive to the establishment of the index and improve the query efficiency. The format is as follows: select sum (t1.je) from table1 T1, table2 T2, table3 T3 where (equivalence condition of T1 (=) and (non-equivalent condition of T1) and (association condition of T2 and T1) and (equivalence condition of T2) and (non-equivalent condition of T2) and (association condition of T3 and T2) and (equivalence condition of T3) and (non-equivalent condition of T3).

Cross-database support

1. Data format recommendations for VO mapping databases across database Java applications:

1) Integer field: field settings are saved as Integer or Long

2) numeric fields: if you need to use accurate calculations of more than 2 decimal places, read, insert, and update using the BigDecimal type

3) character field: read as String, save as String, insert or update to String 4) time field: read as String, insert or update the time format using middleware unified processing.

two。 String concatenation should use the symbol "| |" instead of "+". "+" is the SQLServer syntax. Oracle and DB2 support "|". When Hibernate is converted to SQLServer, it automatically converts "|" to "+".

3. Wildcards cannot be in the form of'[amurc]%'. It should be written as follows:

Select col1, col2 from table_name where col1 like'[a]%'OR col1 like'[b]%'OR col1 like'[c]%'

4. The intercept string length function should use substr, with a starting position of 1 indicating starting from the beginning. Because an error will be reported if the starting point of substr in db2 is 1Phone0; substring is used in SqlServer database and needs to be converted.

5. You cannot limit the number of records in a query result set through select percent n and select top n.

6. Join and on must match strictly, and join without on is strictly prohibited.

7. Join... It is not appropriate to use or after on, and if you use it, you need to enclose the scope of or in ().

8. Do not use the format of select into. Select into is a SQL Server-specific syntax because Oracle and DB2 do not support it.

9. A null value should be considered different from an empty string (a string of zero length). Although Oracle treats Null as the same as an empty string, DB2 and SQL Server are considered different.

What about the SQL statement specification to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.