Oracle Learning Chapter II restrictive queries and sorting of data--03

Here, let's move on to the question left in section 2.6: What if the string to be queried contains "_" or "%"? Start today's study.

2.7 How to use escaped (escape) operators

This task can be accomplished with an escape keyword. In order to practice, we must first create a temporary table, and then insert 1 rows of records into the table, which contains wildcard characters. It is possible that you may not understand the SQL statements for example 2-13 and example 2-14. No problem, you just have to follow the input.

Example 2-13

sql> CREATE TABLE dept_temp  2  as  3  SELECT *  4 from  dept;

Example 2-13 results

Example 2-14

Sql> INSERT into dept_temp  2  VALUES (n, ' It_research ', ' Beijing ');

Example 2-14 results

You can now enter the query statement for example 2-15 to display all rows of data whose department name (dname) meaning It_ begins.

Example 2-15

Sql> SELECT *  2 from  dept_temp  3  WHERE dname like ' it\_% ' escape ' \ ';

Example 2-15 results

In the query in example 2-15, you define ' \ ' as Escape (escape), that is, the ' _ ' character after ' \ ' is not a wildcard, but rather his original meaning, the underscore. Therefore, the result of the query statement is: The first two characters are it, the third character is ' _ ', followed by any ego.

It is not necessary to use the ' \ ' character as an escape, and you can use any character you are interested in as an escape (escape) symbol. Many Oracle professionals often use the ' \ ' character as Escape (escape) characters because the character is escaped (escape) in the UNIX operating system and the C language

To verify the above discussion, you can enter a query statement for column 2-16.

Example 2-16

Sql> SELECT *  2 from  dept_temp  3  WHERE dname like ' [Email protected]_% ' escape ' @ ';

Example 2-16 results

In the query statement in Example 2-16, the ' @ ' is defined as an escape (escape) character, but example 2-16 shows the exact same result as example 2-15.

Recommendation: You should not define a character that has special meanings in SQL and Sql*plus as an escape (escape), which makes your SQL statement difficult to understand.

2.8 ORDER BY clause

Do you remember the example at the beginning of Chapter two? If you forget, please go back to the beginning of this chapter and read it again. Now your boss wants you to reprint that payroll list, but sort by the size of the salary from the big to the small. You turn over the SQL manual and find that the ORDER BY clause is used for sorting. So you try to issue the query statement for example 2-17.

Example 2-17

Sql> SELECT empno,ename,sal  2 from  emp  3  WHERE sal >=1500  4  ORDER by Sal;

Example 2-17 results

The display results are ordered, but the order is small to large. Imagine that your company might be a large business, perhaps hundreds of thousands of employees, and in that case the list could be hundreds of or thousands of pages, so the boss would prefer to see a high-paying employee first. So you have once rewritten the query statement for example 2-18.

Example 2-18

Sql> SELECT empno,ename,sal  2 from  emp  3  WHERE sal >=  4 ORDER by  Sal DESC;

Example 2-18 results

Example 2-18 results A list of the official bosses needed. In this query statement, the DESC keyword is used.

Use the ORDER BY clause to rank the results of the query. ASC (ascending order) is sorted in ascending order (default), because ASC is rarely seen in actual SQL statements. You can sort on numeric, date, and character data. By default, the order of numeric and date data is small to large. The order of the character data is in the order of ASCII code, from A to Z. Desc (descending order) is sorted in descending order.

If the ORDER BY clause is not practical in a query statement, the order of the query results is indeterminate, meaning that you have sent two identical query statements. The order of the results may be different.

If the Oder by clause is used, the clause must be the last clause of the SQL statement.

2.9 Using aliases or expressions in order by self-play

Remember the example 1-11 to 1-14 in the first chapter?

Now we'll rewrite example 1-14 in chapter One, like Example 2-19.

Example 2-19

Sql> select Empno as "Employee number", ename name, (500+sal) *12 "annual Salary"  2 from  EMP;

This SQL statement shows no regularity in the results, and bosses prefer to put high-paying people ahead of the line. Since the yearly salary column is an expression, it should be sorted by alias. You can use the SQL statement in example 2-20 to meet your boss's requirements.

Example 2-20

Sql> select Empno as "Employee number", ename name, (500+sal) *12 "annual Salary"  2 from  emp  3  ORDER by "Annual Salary" DESC;

Example 2-20 results

In addition to using aliases after an ORDER BY clause like example 2-20, you can also follow an ORDER BY clause with an expression. You can enter an SQL statement for example 2-21.

Example 2-21

Sql> select Empno as "Employee number", ename name, (500+sal) *12 "annual Salary"  2 from  emp  3  ORDER by ( 500+sal) *12 DESC;

Example 2-21 results

You get exactly the same result as example 2-20. Just an ORDER BY clause in a 2-21 query statement doesn't look as easy to understand as the previous example.

2.10 Using column numbers in order by self play

In addition, we can use example 2-22 query statements to do the same work, but it seems more puzzling. Here "3" represents the third column, so order by 3 is sorted by the 3rd column.

Example 2-22

Sql> select Empno as "Employee number", ename name, (500+sal) *12 "annual Salary"  2 from  emp  3  ORDER by 3 DESC;

Example 2-22 results

The use of the ORDER BY clause should be as impractical as possible in the SQL statement, because the readability of this usage is too poor. Not at all. The use of order by is not described in the book about Oracle SQL. However, because this usage can reduce the input, especially if the column name or expression after the ORDER BY clause is very long, this usage is still being used. This article describes the purpose of this usage: You can understand this usage when you see it included in the SQL statement, but it is not encouraged to use this method.

2.11 Using multiple columns in order by self-play

You can also sort multiple columns, which can be sorted in ascending or descending order. Let's say that the name, position, and salary of an employee are displayed in such a way that they are sorted first by position from A to Z, then from high to low by salary. This can be achieved by use Case 2-23 query statements.

Example 2-23

Sql> SELECT ename,job,sal  2 from  emp  3  ORDER by Job,sal DESC;

Example 2-23 results

2.12 Using columns that are not in the select list in the ORDER BY clause

You can also sort by columns that are not in the select list. If you can enter the query statement for example 2-24.

Example 2-24

Sql> SELECT ename,job,sal  2 from  emp  3  ORDER by empno;

Example 2-24 results

However, you should avoid using this sort of method whenever possible. Because of the results obtained by this sort of method, others are very difficult to understand. I want to believe that as time goes by, you will not understand it yourself.

2.13 Format of the expanded query statement

In this chapter we extend the basic query statement by adding a WHERE clause and an ORDER BY clause. It has the following format:

SELECT *|{[disctinct] list, ...}

From table name

[WHERE condition]

[ORDER by {column name | alias | expression, ...} [asc| DESC]];

Where conditions are made up of the following parts

Column Name

Text string

an arithmetic expression

Constants

comparison Operators

The ORDER by clause must be placed at the end of the SQL statement.

2.14 What you should know

Before you learn the next chapter, check to see if you have mastered the following:

1, What is the relational database key selection (selection) operation.

2 . How to use the WHERE clause to qualify the selected data row.

3,6 common comparison operators (Operators).

4, How to construct the conditions in the WHERE clause.

5. Between and and in comparison operators (Operators).

6. Use of the NOT operator

7 . String and the representation of the date data in the SQL statement.

8. The like comparison operator (operators) and the wildcard character (wildcard).

9, the application of the meaning character (escape).

10. Simple use of the ORDER by clause.

11 . Use aliases or expressions to sort.

12 . Use column numbers for sorting.

13 . Use multiple columns to sort.

14 . Use columns from the select list no longer sorted.

-----------------------------------------------------

The second chapter of the end of the study, I feel that they have forgotten almost.

Thank CSDN editor almost real-time preservation of the function, let me keep my article, or press the wrong one to delete the building, the page automatically returned to the previous page, I cried heart have.

Oracle Learning Chapter II restrictive queries and sorting of data--03