Shulou(Shulou.com)06/01 Report--

For the sorting of Chinese characters, it is sorted in the order of the first letter. Using DISTINCT comes with a price; because to remove duplicate values, the result relationships must be sorted, and the same tuples are arranged together, and only by grouping tuples in this way can duplicate values be removed, which is even more time-consuming than the query itself.

Be careful when using the'* 'wildcard, and try to use the single-column query or multi-column query described above when you don't need to query all columns, so as not to take up too much resources.

The ORDERBY clause must be placed at the end of all clauses. The default is ASC (incremental sort); ORDERBY can be followed by

In order to adopt 1, 2, 3. Sort, 1, 2, 3... Represents the order of the columns following the SELECT.

For example:

SELECT column1,column2,column3 FROM dual ORDER BY 1 pr 2; is equivalent to

SELECT column1,column2,column3 FROM dual ORDER BY column1,column2

3.1. when the column required by ORDERBY is not within the range selected by the SELECT statement, it is obviously not feasible to use the column number. At this point, you can mix the column with the serial number to solve this problem.

3.2, the sorting of uppercase and lowercase characters, the general database management system thinks that the two are the same.

The WHERE clause gets the result set returned by the FROM clause and filters the result set using the search criteria defined in the WHERE clause. Each row of the results returned by the FROM clause is checked according to the search conditions, those that meet the conditions are returned, and those that do not match are removed from the results.

If the NULL value participates in any comparison operation, the result will be treated as FALSE, that is, whether it is "NULL 1200" or

"NULL= 1200", the result is that FALSE; is worth the operation to NULL can only be: "IS NULL" or

After any arithmetic operation that the null value participates in, the value of the arithmetic expression is NULL;. If NULL participates in the aggregation operation, all aggregation functions except COUNT (*) ignore the NULL;NULL participating in the AND logic operation, as shown below:

The logical operator "AND" takes precedence over "OR". In order to operate simultaneously, apply parentheses.

For example: the following SQL implementation results are information for all teachers whose salaries in the computer and biology classes are greater than 1000.

SELECT TNAME, DNAME, SAL

FROM TEACHER

WHERE (DNAME = 'computer' OR DNAME = 'biology')

AND SAL > 1000

ORDER BY SAL

If there are no parentheses, the query will show the information of all the teachers whose salary in the computer class is greater than 1000.

Compared with OR, IN has the following advantages:

When there are many conditions to choose, it is easy to use the IN operator, as long as you list it in turn with comma intervals in parentheses.

Using the IN operator, the condition listed after it can be another SELECT statement, a subquery.

LIKE operator

In the SQL language, it is implemented through wildcards. The wildcards provided by the SQL language are "%", "_", "*" and "[]"; only data of types CHAR, VARCHAR, and TEXT can use LIKE operators and wildcards.

You can use "" instead of LIKE when there are no wildcards in the query condition. For example:

SELECT * FROM teacher t WHERE t.dname NOT LIKE 'computer'; equivalent to

SELECT * FROM teacher t WHERE t.dname 'computer'

"%" represents any number of characters; "_" represents one character; "[]" multiple characters match.

Use ESCAPE to define escape characters, telling DBMS that the characters after them are actual values; for example:

The% before LIKE'% M% 'ESCAPE' characters is a wildcard, followed by the actual characters.

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.