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

Where features:

1. Used to filter the data

2. Can be compared, logical operation

3. Where needs to be placed behind from.

=

I. comparison operation

The comparison operation includes: >

= select ename,sal,deptno from emp where DEPTNO=10

SQL > select * from emp where sal > 1000

2. Use characters as conditions

SQL > select ename,sal,deptno from emp where ename='SCOTT'

Note: characters after where need to be enclosed in single quotation marks, and characters after where are strictly case-sensitive.

3 、 between... And...: limit data to a certain range

SQL > select * from emp where sal between 1000 and 3000

Note: between is an inclusion relationship.

4. In: query data in the form of enumeration

SQL > select * from emp where ename in ('KING','SCOTT','ALLEN')

5. Like: used for fuzzy matching

%: represents 0 or more characters

_: represents a character

① found emp information with the employee's name beginning with M

SQL > select * from emp where ename like'% M'

② found employee information containing M in the string

SQL > select * from emp where ename like'% M%'

③ found employee information with the second letter M in the name.

SQL > select * from emp where ename like'_ M%'

6. Note: you can escape% or _ using escape

SQL > select * from T11 where name like'% _%'

NAME

-

Aa_a

Aaa

SQL > select * from T11 where name like'%\ _% 'escape'\'

NAME

-

Aa_a

7. Treatment of null

SQL > select * from emp where comm is null

SQL > select * from emp where comm is not null

=

II. Logical operation

1. And requires all expressions to be true before they can be true.

2. Or returns true as long as one of the expressions is true

3. Not inversion

① inquires for people with department number 10 and wages greater than 1500

SQL > select * from emp where sal > 1500 and deptno=10

② inquires people whose department number is 10 or whose salary is greater than 1500.

SQL > select * from emp where sal > 1500 or deptno=10

③ uses not,not to denote

SQL > select * from emp where ename not in ('KING','SCOTT','ALLEN')

=

Third, the priority of the condition in where

1. Arithmetic operation

2. Compare operation

3. Logic operation: not > and > or

① found a job as an administrator or an analyst with a salary of more than 2500

SQL > select * from emp where (job='MANAGER' or job='ANALYST') and sal > 2500

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7566 JONES MANAGER 7839 02-APR-81 2975 20

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7902 FORD ANALYST 7566 03-DEC-81 3000 20

=

IV. Sorting

1. ASC ascending order (default)

2. DESC descending order

SQL > select ename,sal A from emp where comm is null order by A; defaults to ascending order

SQL > select ename,sal A from emp where comm is null order by A desc

3. Order by: you can use numbers

SQL > select * from emp order by 6

4. Multi-column sorting

Sort by deptno in descending order and sal in ascending order.

SQL > select ename,deptno,sal from emp order by deptno desc,sal

SQL > select ename,deptno,sal from emp order by 2 desc 3

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.