where和order by

数字条件：

SQL> select salary from employees where employee_id=100;

 

字符串大小写敏感：

SQL> select last_name, salary from employees where last_name='King';

SQL> select table_name, tablespace_name from user_tables where table_name='EMPLOYEES';

lower()

 

日期格式敏感：

select sysdate from dual

SQL> alter session set nls_date_format='RR-Mon-dd';

SQL> select last_name from employees where hire_date='2006-05-23';

SQL> select last_name from employees where hire_date=to_date('2006-05-23', 'yyyy-mm-dd');

 

区间查询：

SQL> select last_name from employees where salary>=3400 and salary<=4000;

SQL> select last_name from employees where salary between 3400 and 4000;

SQL> select last_name from employees where salary between 3000 and 5000 and department_id=50;

 

in：

SQL> select last_name from employees where department_id=30 or department_id=40 or department_id=50;

SQL> select last_name from employees where department_id in (30, 40, 50);

 

通配符：

SQL> select last_name, job_id from employees where job_id like '%\_MAN' escape '\';

 

null作为条件：

SQL> select last_name from employees where commission_pct is null;

SQL> select last_name from employees where commission_pct is not null;

 

and/or/not：

SQL> select last_name, job_id, department_id, salary from employees where job_id='SA_REP' or department_id=50 and salary>=8000;

SQL> select last_name, job_id, department_id, salary from employees where (job_id='SA_REP' or department_id=50) and salary>=8000;

 

排序：

SQL> select last_name, salary from employees order by salary;      列名 默认升序

SQL> select last_name, salary from employees order by salary desc; 降序

SQL> select last_name, salary from employees order by last_name; 字符串左对齐对比ascii码

SQL> select last_name, hire_date from employees order by hire_date; 日期按时间轴排序

SQL> select last_name, salary, commission_pct from employees order by salary desc, commission_pct desc;

SQL> select last_name, salary*12*(1+commission_pct) from employees order by 2;列参数

SQL> select last_name, salary*12*(1+commission_pct) total_salary from employees order by total_salary;起别名