Database design specification: SQL Authoring Specification

SQL Authoring Specification

1. Basic SQL Authoring Specification

(1) Direct use of table names in rule 1:sql, no schema as prefix, and cross schema access to create synonym (Data Warehouse, CRM database not applicable).

(2) Rule 2: You must use bound variables to avoid direct references to constants.

Description: Frequent hard parsing can affect database performance.

(3) Rule 3: Avoid applying a function to an indexed field in a WHERE clause, which results in an index invalidation.

Example:

Wrong wording:
Select employee_id, first_name from employee where TO_CHAR (hire_date, ' yyyymmdd ') = ' 20120601 ';

The correct wording:
Select employee_id, first_name from employee where Hire_date=to_char (' 20120601 ', ' yyyymmdd ');

(4) The rule 4:like is not allowed to use full blur, only the right fuzzy query is allowed.

Note: The full fuzzy query cannot use index, which can cause performance problems.

Example:

Full fuzzy query:
Select employee_id, first_name from employee where name like '%joe% ';

Right blur query:
Select employee_id, first_name from employee where name like '%joe% ';

(5) Rule 5: If you use an Oracle database, use an Oracle outer join instead of the standard ANSI 99 outer JOIN syntax.

Description: ANSI 99 outer JOIN syntax refers to INNER join, left JOIN, right join, full outer join,oracle use (+) to represent outer joins.

Example:

Wrong wording:
Select employee_id, first_name from employee a left join on department b a.department_id = b.department_id;

The correct wording:
Select employee_id, first_name from employee A, Department b where a.department_id = b.department_id (+);

(6) The Rule 6:insert statement must write the field name.

(7) Rule 7: Strict requirements to use the correct type of variable, eliminate implicit type conversion.

When writing SQL, you must determine the data type of each field in the table to prevent implicit type conversions.

Example:

ID is type number

Wrong wording:
Select employee_id, first_name from employee where employee_id = ' 123 '--This causes Oracle to convert IDs first to varchar types, resulting in index invalidation.

The correct wording:
Select employee_id, first_name from employee where employee_id = 123;

Conversely, character data must be enclosed in single quotes.

A field of a time type that must be assigned using To_date (the current time can be represented directly by Sysdate)

Example:

Wrong writing (using date-type variables):
Select Agtphone, Name
From Log_account_info
where Accountno = #accountnot: varchar# and Optime >= #dateBegin:d ate#

The correct wording:
Select Agtphone, Name
From Log_account_info
where Accountno = #accountno: varchar# and Optime >= to_date (#dateBegin: varchar#, ' yyyy-mm-dd hh24:mi:ss ')

(8) Rule 8:mysql, any filtered field values must be enclosed in single quotes.

2. Question of * number in SELECT statement

(1) Rule 1: Try not to use SELECT *.

(2) Rule 2: SELECT * is not allowed when a table is connected.

(3) Rule 3: A single table query that allows the use of select *, but the following are disabled:

The ① table contains CLOB fields.

The ② table contains fields that are larger in length, such as fields above VARCHAR2 (2000), but the SQL does not actually need to take out the value of the field.

(4) In Rule 4:mysql, when the result set does not need to list all the fields of the queried table, and there is an "order BY" statement in the query. Disable the use of SELECT * To remove all fields.

5 in the rule 5:mysql, the "select *" is not allowed in the connection query.

3. Use of NULL

Description: null means "not sure", not "null"

(1) Rule 1: When querying, use is null or is not NULL

(2) Rule 2: When updating, use the equals number.

Example:

Update tablename SET column_name = null;

4. Aggregate Function FAQ

(1) Rule 1: Counting uses count (*) instead of count (1) or count (PK).

Description: Count (column_name) calculates the number of record bars that are NOT NULL

COUNT (DISTINCT column_name) calculates the number of distinct values for which the column is not NULL

The count () function does not return NULL, but the sum () function may return null, and you can use NVL (sum (), 0) to avoid returning null

(2) Rule 2: The field used for sorting, and if there are null values, note the order in which null values are processed. If the null value is in front, the sort statement adds the nulls first keyword, and if the null value is followed, the sort statement adds the nulls last keyword.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/basis/