Original text transferred from:
Database Design Experience (transfer)
SQL coding specification (Collection) http://hi.baidu.com/rambochow/blog/item/3ebcbb3db3758903bba16743.html
1. Writing format
1 . Writing format
Sample Code:
Stored Procedure SQL document writing format example
Select
C. dealercode,
Round ( Sum (C. submitsubletamountdlr + C. submitpartsamountdlr + C. submitlaboramountdlr) / Count ( * ), 2 ) As AVG ,
Decode ( Null , ' X ' , ' Xx ' , ' CNY ' )
From (
Select
A. dealercode,
A. submitsubletamountdlr,
A. submitpartsamountdlr,
A. submitlaboramountdlr
From Srv_twc_f
Where (To_char (A. origsubmittime, ' Yyyy/mm/dd ' ) > = ' Date range (start) '
And To_char (A. origsubmittime, ' Yyyy/mm/dd ' ) <= ' Date range (end) '
And Nvl (A. deleteflag, ' 0 ' ) <> ' 1 ' )
Union All
Select
B. dealercode,
B. submitsubletamountdlr,
B. submitpartsamountdlr,
B. submitlaboramountdlr
From Srv_twchistory_f B
Where (To_char (B. origsubmittime, ' Yyyy/mm/dd ' ) > = ' Date range (start) '
And To_char (B. origsubmittime, ' Yyyy/mm/dd ' ) <= ' Date range (end) '
And Nvl (B. deleteflag, ' 0 ' ) <> ' 1 ' )
) C
Group By C. dealercode
Order By AVG Desc ;
Example of SQL string writing format in C #
Strsql = " Insert Into Snd_financehistory_tb"
+ "(Dealercode ,"
+ "Requestsequece ,"
+ "Handletime ,"
+ "Jobflag ,"
+ "Frameno ,"
+ "Inmoney ,"
+ "Remainmoney ,"
+ "Deleteflag ,"
+ "Updatecount ,"
+ "Creuser ,"
+ "Credate ,"
+ "Honorcheckno ,"
+ "SEQ )"
+ " Values ( ' "+ Draftinputdetail. dealercode +" ' ,"
+ " ' "+ Draftinputdetail. requestsequece +" ' ,"
+ "Sysdate ,"
+ " ' 07 ' ,"
+ " ' "+ Frameno +" ' ,"
+ Requestmoney + ","
+ Remainmoney + ","
+ " ' 0 ' ,"
+ " 0 ,"
+ " ' "+ Draftstruct. employeecode +" ' ,"
+ "Sysdate ,"
+ " ' "+ Draftinputdetail. honorcheckno +" ' ,"
+ Index + ")";
1 ). Indent
For Stored Procedure files, indentation is 8 spaces
SQL strings in C # cannot be indented, that is, each line of strings cannot start with a space.
2 ). Line feed
1 > . Select / From / Where / Order By / Group By clause must be written in another row
2 > If the select clause contains only one item, it must be written in the same way as the select clause.
3 > If the select clause contains more than one item, each item occupies a single row, and 8 spaces are indented to the right based on the corresponding select clause (C # No indentation)
4 > If the content of the. From clause has only one item, it must be written in the same way as the from clause.
5 > If the content of the. From clause contains more than one item, each item occupies a single row, and 8 spaces are indented to the right based on the corresponding from (C # No indentations)
6 > If there are multiple conditions in the. Where clause, each condition occupies one row and starts with and without indentation.
7 > .( Update ) Each item in the set clause occupies a single row without indentation.
8 > In the. Insert clause, each table field occupies one row without indentation. in values, each field occupies one row without indentation.
9 > . Empty rows are not allowed in the SQL text.
10 > In. C #, single quotes must be in the same line as the SQL clause. The connector (" + ") Must start at the beginning of the line
3 ). Space
1 > . SQL Arithmetic Operators and logical operators must be separated by spaces.
2 > . A space must be followed by a comma.
3 > . There must be a space between the keyword, reserved word, and left parenthesis
2. It is not equal to the unified use of "<> ". Although sqlserver considers "! = "And" <> "are equivalent and both represent non-equal meanings. For unification, "<>" is not always used
3. The table alias must be used for database query.
4. Compatibility with table Field Extensions in SQL. When using select * in C #, it is strictly prohibited to obtain the query result in the form of getstring (1), and the form of getstring ("field name") must be used; when using insert, you must specify the inserted field name. do not specify the field name to insert values directly.
5. Reduce the use of subqueries. In addition to poor readability, subqueries also affect SQL operation efficiency to some extent. Please try to reduce the use of subqueries and replace them with other methods with higher efficiency and better readability.
6. Add indexes appropriately to improve query efficiency. Adding indexes appropriately can greatly improve search speed. For more information, see sqlserver SQL Performance Optimization series.
7. Special requirements for database table operations
7 . Special requirements for database table operations
This project also has the following special requirements for database table operations:
1 ). Replace physical deletion with logical Deletion
Note: Currently, data in the database table is not physically deleted, and only logical deletion is required.
Use the deleteflag field as the deletion flag, deleteflag = ' 1 ' This record is logically deleted. Therefore, you must consider the deleteflag factor when querying data. The standard query condition of deleteflag is nvl (deleteflag, ' 0 ' ) <> ' 1 '
2 ). Added the record Status field.
Each table in the database has the following fields: deleteflag, updatecount, credate, creuser, updatetime, updateuser
Note that the following fields must be taken into account during benchmarking
Deleteflag must be set when a record is inserted. = ' 0 ' , Updatecount = 0 , Credate = Sysdate, creuser = Log on to the user. When querying a record, consider deleteflag. If this record may be updated, you must obtain updatecount for synchronization check. When modifying a record, set updatetime = Sysdate, updateuser = Logon user, updatecount = (Updatecount + 1 ) Mod 1000 ,; When deleting a record, you must set deleteflag = ' 1 '
3 ). History Table
Some tables in the database still have corresponding historical tables, such as srv_twc_f and srv_twchistory_f. When querying data, You must retrieve the corresponding historical tables in addition to the table where the data is located, perform union (or union) on the results of the two All )
8. Analyze SQL Performance Using execution plans
Explain plan is a good tool for analyzing SQL statements. It can analyze statements without executing SQL statements. Through analysis, we can know how sqlserver connects to the table, how to scan the table (index scan or full table scan), and the index name used, from the inside out, the analysis results are interpreted in ascending order. The explain plan analysis results are arranged in indent format. The most internal operations are first interpreted. If the two operations are on the same layer, the operation with the minimum operation number will be executed first. Currently, many third-party tools, such as PLSQL developer and toad, provide an extremely convenient explain Plan tool. PG needs to record the query SQL text added by itself into log, then, analyze in the explain Plan to minimize the number of full table scans,
