0005 SQL note Note 01-SELECT statement, select statement example

0005 SQL note Note 01-SELECT statement, select statement example

1. SELECT basic statements:

SELECT field name 1,..., field name n FROM Table Name

2. Search all fields and replace the field name with "*", which leads to inefficiency.

SELECT * FROM table name;

3. Remove duplicate items. "DISTINCT" applies to all fields.

Select distinct field name 1,..., field name n FROM table name;

4. If there are too many search results, you can limit the number of results.

SELECT field name FROM table name where rownum <= n;

5. Sort the Retrieved Data. ASC is the ascending or default sorting method. DESC is the descending order. The order by statement must be the last statement. If it is not sorted, the order of retrieved data cannot be considered to be of any significance.

SELECT field name FROM table name order by field name 1 ASC | DESC, ''', field name n ASC | DESC;

6. WHERE clause. The operators are as follows. Syntax: WHERE field name operator value. Note that Oracle does not support "! <"And"!>"

Operator	Description	Operator	Description
=	Equal
! =	Not equal	<>	Not equal
<	Less	>	Greater
<=	Less than or equal	> =	Greater than or equal
BETWEEN AND	Bilateral closed interval	IS NULL	Null Value

7. Multi-condition Filtering for the WHERE clause -- AND. Multiple Sub-conditions must meet

WHERE subcondition 1 AND · AND subcondition n

8. Multi-condition Filtering for the WHERE clause -- OR, meeting any condition

WHERE subcondition 1 OR · OR subcondition n

9. The WHERE clause-IN operator satisfies any value of IN, which is similar to OR, but has the following advantages: the syntax is clearer and more intuitive; it is easier to manage the order of value; faster than a group of OR statements. Other SELECT statements can be included and WHERE clauses can be dynamically created.

WHERE field name IN (value: 1,..., value: n)

10. not operator: denies the conditions following it. NOT is useful in complex statements.

Where not condition;

11. The priority of AND is higher than OR. You can use "()" to change the priority.

12. Fuzzy match the Like operator, wildcard -- "%", matching 0 ~ N characters; "_", match one character. Note: Oracle uses spaces to complete fields. It is best to use TRIM (), RTRIM (), and LTRIM () to remove spaces.

WHERE field name LIKE 'string with %'

13. SQL must know will be reduced to the "[]" wildcard, But Oracle does not seem to support it, or it is not supported by default. I will change it later.

14. About wildcards: (1) do not overuse wildcards. (2) do not place wildcards at the beginning. It will be very slow. (3) Pay attention to the location of the wildcards and place the wildcards in the wrong place, unexpected data may be returned.