【Oracle】第二单元 条件和排序

员工ID为90的

select * from employees e where e.department_id = 90;

员工工资小于4400的

select * from employees e where e.salary < 4400;

between…and在俩个值之间

select * from employees ewhere e.salary between 4400 and 9000;

员工工资在4400到9000之间，包含4400和9000

前面的值小，后面的值大，否则前大后小查询不到数据

部门ID为90、60的员工

select * from employees e where e.department_id in (90,60);

员工姓名g结尾的

select * from employees e where e.last_name like '%g';

员工姓名中含有i的

select * from employees e where e.last_name like '%i%';

员工姓名第二位是o的

select * from employees e where e.last_name like '_o%';

员工ID为空的员工

SELECT * FROM EMPLOYees e where e.department_id is null;

并且 和 and 或者 or

工资一万以上并且工作名称含有MAN字符的员工

select * from employees e where e.salary > 10000 and e.job_id like '%MAN%';

工资一万以上或者工作名称含有MAN字符的员工

select * from employees e where e.salary > 10000 or e.job_id like '%MAN%';

not 不是 不为

员工ID不为空的员工

SELECT * FROM EMPLOYees e where e.department_id is not null;

员工ID不是90、60的员工

SELECT * FROM EMPLOYees e where e.department_id not in (90，60);

escape 转义

select * from t_char where a like '%#%%' escape '#';

escape后的’#'可以指定任意字符

select * from t_char where a like '%z%%' escape 'z';

order by 默认升序asc 降序desc

部门ID不在90的员工并按照日期升序

SELECT * FROM EMPLOYees e where e.department_id not in (90) order by e.hire_date;

部门ID不在90的员工并按照日期降序

SELECT * FROM EMPLOYees e where e.department_id not in (90) order by e.hire_date desc;