Java programmer cainiao (2) Oracle BASICS (2) Oracle query statements and data sorting

This article is based on a database provided by Oracle. The database contains the employee table EMP. Department info table Dept. Employee payroll summary

The table structure of the three tables is as follows:

I. Basic query statements

1. The simplest statement to query all columns

Select * from EMP;

2. query statements for the specified list

Select empno, ename, Sal from EMP;

Note: by default, Oracle's primary and date data are left aligned. While the digital data is right aligned

3. simple queries with simple arithmetic operators:

Select empno, ename, Sal + 500 from EMP;

Note: Priority of simple operators in Oracle:

1) multiplication and division, and addition and subtraction

2) the same priority is from left to right

3) If brackets are used. Inside brackets

4. Use column aliases in SQL statements

Select empno as "num", ename name, (SAL + 500) * 12 "annual salary" from EMP;

We can see from the preceding SQL statement. You can change the column name to an alias by adding an as or space between the column name and the alias. Double quotation marks must be added to the string alias.

5. Join Operators

Select ename | "the annual salary is" | (SAL + 500) * 12 "annual salary" from EMP;

The connector in Oracle is composed of two vertical bars (|) used to connect one or more columns or strings.

6. deduplicated operator distinct

Select deptno from EMP;

Select distinct deptno from EMP;

We can see from the above. Query the Department numbers of all employees in the employee table. In the first query statement. Distinct removal is not used. The second uses distinct deduplication. All data with the same department number is removed.

Ii. Restricted query and data sorting

1. Where keyword. Followed by restrictions. The condition consists of the column name, string, and comparison budget.

Condition format: expression opera expression

The opera here can be: >,>=,<,<=, <=, <>( or ),! =

In addition to these Oracle, The between and, in, and like restrictions are provided.

Example: Select * from EMP where SAL> = 2000;

2. Use between and for comparison

Select * from EMP where Sal between 2000 and 3000;

Obtain all data that works between 2000 and 3000.

It is worth noting that

1. Between and can also be used for time comparison. The time must be enclosed in single quotes.

2. If you want to query data that is not in a certain range. Simply add not in front of.

3. Use the In comparison operator

Find the persons working for saleman, clerk, and Manager

Select * from EMP where job in

('Salesman', 'cler', 'manager ');

Appendix: If you want to find data that is not in the given element. You can add not directly before in.

4. Use the like Operator

Select * from EMP where job like 'sa % ';

An appeal example is to query information of all employees whose names start with SA.

"%" Indicates 0 or multiple characters

"-" Indicates one character and can only be one character.

Note: One thing to note here is. Strings in the values in the query conditions after where are case sensitive. When I did the experiment just now. Write SA as SA. No result is returned. So pay attention to this.

5. Transfer Character: "\"

6. Order by clause

Select * from EMP where job like 'sa % 'order by Sal;

We can see from the above. The query results are sorted in ascending order of salary. If order by is used in Oracle Data Query. The default sorting is (ASC) ascending. Desc

Note:

1. If order by is not used in the query. The result order is uncertain. That is, two queries. The order may be different.

2. If the order by clause is used. The first is the last clause in the SQL statement.

7. Use aliases or expressions in the order by clause.

Select ename, (500 + Sal) * 12 annualsal from EMP where job like 'sa % 'order by annualsal;

The alias can be used as the sorting column during Oracle sorting. You can also use expressions,

Note:

1. Oracle can also use column numbers as sorting rules. However, this situation is rarely used. Because column numbers are not clearly sorted

2. Oracle can also use multiple columns as the sorting standard, separated by commas.

Select ename, (500 + Sal) * 12 annualsal from EMP where job like 'sa % 'order by annualsal, ename;

Bytes ------------------------------------------------------------------------------------------------------------

The electronic book "Java programmers from stupid birds to cainiao" is officially released. You are welcome to download it.

Http://blog.csdn.net/csh624366188/article/details/7999247