Oracle第四天 SQL查询

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';

日期是格式敏感：

SQL> select sysdate from dual;

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

SQL> select sysdate from dual;

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

SQL> select last_name from employees where hire_date='23-MAY-06'

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;