一、order by子句
1.order by排序规则
(1)asc,升序排列,默认取值
(2)desc,降序排列
(3)order by是select命令的最后一个子句
select last_name,salary,dept_id
from s_emp
order by salary;
select last_name,salary,dept_id
from s_emp
order by salary asc;
select last_name,salary,dept_id
from s_emp
order by salary desc;
2.order by中null值处理
(1)升序中,放最后
(2)降序中,放最前
select last_name,title,commission_pct
from s_emp
order by commission_pct;
3.order by后可跟内容
(1)列名
(2)列的别名
(3)数字:select列表项的位置
select last_name name,salary
from s_emp
order by name;
select last_name,salary*12
from s_emp
order by 2;
4.order by后跟多个列
(1)先按第一个列排序,内容相同的,再按照第二个列排...
(2)asc、desc只修饰一个列
select last_name,dept_id,salary
from s_emp
order by dept_id,salary desc;
等价
select last_name,dept_id,salary
from s_emp
order by dept_id asc,salary desc;
二、where子句
1.限制取出记录数
(1)where比较中的字符串常量严格区别大小写
select last_name,salary,dept_id
from s_emp
where dept_id=42;
SELECT LAST_NAME,SALARY,DEPT_ID
FROM S_EMP
WHERE DEPT_ID=42;
select last_name,salary,dept_id
from s_emp
where last_name=‘smith‘;
2.where中的操作符
(1)between...and...:闭区间;先跟小值,再跟大值
select last_name,salary
from s_emp
where salary between 940 and 1100;
select last_name,salary
from s_emp
where salary between 1100 and 940;
(2)in:从给定的列表范围内匹配值
select last_name,salary,dept_id
from s_emp
where dept_id in(41,43,45);
(3)like:模糊查询
通配符:
%代表0或多个字符
_代表单一字符
select last_name,salary
from s_emp
where last_name like ‘M%‘;
escape自定义转义字符,可以让统配符失去特殊意义,变成普通字符
查出名字以_a开始的?
select last_name,salary
from s_emp
where last_name like ‘\_a%‘ escape ‘\‘;
(4)is null:空值判断
空值不能用等号进行比较
*select last_name,salary,title,commission_pct
from s_emp
where commission_pct is null;
(5)多条件查询
and:多个条件必须同时满足
or:多个条件选一个
and的优先级比or高
select last_name,salary,dept_id
from s_emp
where salary>=1000
and dept_id=44
or dept_id=42;
等价
select last_name,salary,dept_id
from s_emp
where (salary>=1000
and dept_id=44)
or dept_id=42;