Oracle SQL

如果有一列的值为null，则数学运算结果就是null。

e.g. 

 

select last_name, 12*salary*commission_pct from employees;

 commission_pct为null的行，12*salary*commission_pct结果都是null

 

 

别名：

 

select last_name AS name, commission_pct comm from employees;
select last_name "Name", salary*12 "Annual Salary" from employees;

 

Concatenation:

 

 

select last_name || ' ' || job_id as "Employees" from employees;

 

Literal Character Strings

 

字符、日期、数字

必须用单引号括起来

 

 

 select last_name || ' is a ' || job_id from employees;

 

转义：

 

select department_name || ' Department' || q'['s Manager ID:]' || manager_id as "DD" from departments;

 

重复行：

 

select DISTINCT department_id from employees;

 

 

desc / describe

 

 

 where子句不可以使用别名：

select first_name ||' ' || last_name as "Name", job_id, department_id did
from employees
where department_id=90;

 

UPPER/LOWER

 

字符或日期要用单引号 '19-FEB-96'

 

=,>,>=,<,<=,(<>,!=,^=), BETWEEN ..AND.., IN(set), LIKE, IS NULL

BETWEEN ..AND..       >= .. and <=...

IN            .. OR .. OR ..

 

 

LIKE: % 0个或多个 _一个

ESCAPE:

select * from empoyees where job_id LIKE '%SA\_%' ESCAPE '\';

 

ect * from empoyees where job_id LIKE '%SA|_%' ESCAPE '|';

 

AND, OR, NOT

 

ddsf