字符和日期
- 字符和日期要包含在单引号中
- 字符大小写敏感，日期格式敏感
- 默认日期格式是DD-MON月-RR
如下：
select last_name,job_id,department_id
from employees where last_name = 'Higgins'
select last_name,hir_date
from employees
where hire_date = '7-6月-1994'
单行函数转化：
select last_name,hir_date
from employees
where
to_char('hire_date,'yyyy-mm-dd') = '1994-06-07'
比较运算
| 操作符 | 含义 |
|---|---|
| = | 等于（不是==） |
| > | 大于 |
| >= | 大于、等于 |
| < | 小于 |
| <= | 小于、等于 |
| <> | 不等于（也可以是!=） |
赋值使用 := 符号
如：
select last_name,hir_date,salary
from employees
where salary >= 4000 and salary < 7000
其它比较运算
| 操作符 | 含义 |
|---|---|
| between......and...... | 在两个之间（包含边界） |
| in(set) | 等于列表中的一个 |
| like | 模糊查询 |
| is null | 空值 |
逻辑运算
| 操作符 | 含义 |
|---|---|
| and | 逻辑并 |
| or | 逻辑或 |
| not | 逻辑否 |
select last_name,hir_date,salary
from employees
where salary between 4000 and 7000
in用法：
select last_name,department_id,salary
from employee
where department_id = 90
or department_id = 80
or department_id = 70
select last_name,department_id,salary
from employee
where department_id in (70,80,90)
注意：范围取值用between....and
有限个值用in
模糊查询like
//员工名字中含有字符a的员工有哪些
select last_name,department_id,salary
from employee
where last_name like '%a%'
//员工名字中第二位含有字符a的员工有哪些
select last_name,department_id,salary
from employee
where last_name like '_a%'
//下划线表示一个字符
//员工名字中含有_（下划线）的员工有哪些
select last_name,department_id,salary
from employee
where last_name like '%\_%' escape '\'
//转义escape
is null用法：
select last_name,department_id,salary,
commission_pct
from employee
where commission_pct is null
//commission_pct不是空的
select last_name,department_id,salary,commission_pct
from employee
where commission_pct is not null
order by子句
- 使用order by 子句排序
- asc(asscend):升序
- desc(descend):降序
- order by 子句在select语句结尾
select last_name,department_id,salary
from employees
order by salary desc,laser_name asc
别名排序
select last_name,department_id,salary,
12*salary annual_sal
from employees
order by annual_sal
