Oracle study Note 3

In the previous query, the result is all records in the table. If you want to only display records that meet certain conditions, you must use conditional query or a restricted query.

Basic Syntax of conditional query:

SELECT * | (specific column names, such as ename and empno)

FROM table (table name)

WHERE conditions

Note: The where clause must be followed by the from clause.

For example, query all information of employees whose salaries are greater than 2000 in the emp table.

SQL> select * from  emp  where sal  >  2000 ; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20 7839 KING       PRESIDENT       1981/11/17    5000.00               10 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20 6 rows selected

In the preceding example, you have used>. You can also use >=, =, <=, <, and other operation symbols in the operation conditions. For the non-equal sign, please note that use! Both = and <> are allowed.

For example, you can query information about employees whose employee ID is not 7566.

SQL> select * from emp where empno != 7566 ;

SQL> select * from emp where empno <> 7566 ; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH      CLERK      7902 1980/12/17     800.00               20 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20 7839 KING       PRESIDENT       1981/11/17    5000.00               10 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20 7900 JAMES      CLERK      7698 1981/12/3      950.00               30 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10 13 rows selected

The query results of the preceding two methods are the same.

Logical operation:

1) AND, logic AND;

2) OR, logic OR;

3) NOT, logical no;

For example, query the information of employees whose department number is 10 and their salary is greater than 2000.

SQL> select * from emp  where deptno = 10 and sal > 2000 ; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10 7839 KING       PRESIDENT       1981/11/17    5000.00               10

Use and to connect two or more conditions at the same time.

For example, query information about employees whose department number is 10 or whose salary is greater than 2000.

SQL> select * from emp  where deptno = 10 or sal > 2000 ; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20 7839 KING       PRESIDENT       1981/11/17    5000.00               10 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10 7 rows selected

There is a relationship between the two conditions. We use logic OR to connect the two conditions.

For example, you can query information about employees who receive bonuses when their salaries exceed 2000. In the preceding query result, comm is null, indicating that the query result is null.

SQL> select * from emp where sal > 1000 and comm is not null; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

The above query shows that the bonus is not empty, so not is used. If you want to query the employee information with the comm empty, the condition is changed to comm is null.

Other comparison operators:

BETWEEN... ON... is BETWEEN two values, including the Boundary

The IN (set) value is one of

LIKE fuzzy query

Is null query NULL Value

Between on syntax format:

Field between small value on queue

For example, query information about employees with salaries ranging from 1500 to 2500.

SQL> select * from emp where sal between 1500 and 2500 ; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

The preceding query results are the same as those of the select * from emp where sal> 1500 and sal <2500; statement.

Syntax format of IN:

In (value 1, value 2, value 3)

For example, query the information of employees numbered 7499,778 2 and 7844

SQL> select  *  from emp where empno in (7499 , 7782 , 7844) ; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

To use LIKE, pay attention to the problem of wildcards. There are two types of wildcards:

% -- Match any number of characters

_ -- Match only one character

For example, query the employee information whose second character is M in the name.

SQL> select * from emp where ename like '_M%'; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH      CLERK      7902 1980/12/17     800.00               20

Translated character: %, _ is used as a matching character. If the name contains an underscore, how can I query it?

At this point, we need to translate;

Syntax format:

SELECT ename

FROM emp

WHERE ename LIKE 'it \ _ % 'escape '\';

Empty identification:

For example, query information about employees who do not have a bonus in the table.

SQL> select * from emp where comm is  null ; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH      CLERK      7902 1980/12/17     800.00               20 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20 7839 KING       PRESIDENT       1981/11/17    5000.00               10 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20 7900 JAMES      CLERK      7698 1981/12/3      950.00               30 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10 10 rows selected

If the where clause contains a character or a date, it must be enclosed in single quotes.

The format is as follows:

SELECT ename, hiredate, sal

FROM emp

WHERE hiredate = '7-January 1, June-1994'

SELECT ename, deptno

FROM emp

WHERE ename = 'whalen ';

The characters are case sensitive and the date format is sensitive. The date format is dd-m-yy.

Sort:

Use the order by clause to sort the query results.

Syntax format:

Select distinct * | (specific column name)

From table name

Where query Conditions

Field 1, Field 2, Field 3 (asc | desc) of order by sorting );

Asc-> indicates ascending, desc-> indicates descending, and the default value is ascending. In addition to sorting by fields in the table, you can also sort by aliases.

For example, query all the information in the emp table and sort the results. First, sort the Department numbers. If the department numbers are the same, sort them in descending order of wages. If the wages are the same, sort by employee number in descending order.

SQL> select * from emp order by deptno ,sal desc  ,empno desc; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7839 KING       PRESIDENT       1981/11/17    5000.00               10 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20 7369 SMITH      CLERK      7902 1980/12/17     800.00               20 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30 7900 JAMES      CLERK      7698 1981/12/3      950.00               30 14 rows selected