OCP培训课程：SQL之限制和排序数据

OCP培训课程：SQL之限制和排序数据 使用where语句限制数据、使用order by语句排序数据、使用替换变量

1、使用where子句限制行

语法：

where子句必须在from子句之后。

例子：只查询部门编号为90的人员信息

SQL> select employee_id,last_name,job_id,department_id from employees where department_id=90;

EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID

----------- ------------------------- ---------- -------------

100 King AD_PRES 90

101 Kochhar AD_VP 90

102 De Haan AD_VP 90

如果要限制字符类型，日期类型，必须要用单引号引起来，而且单引号里面的字符区分大小写，日期要按照指定的格式书写，默认的的格式是DD-MON-RR格式。

例子：查询名字为Whalen的人员信息

SQL> select last_name,job_id,department_id from employees where last_name='Whalen';

LAST_NAME JOB_ID DEPARTMENT_ID

------------------------- ---------- -------------

Whalen AD_ASST 10

如果'Whalen'不加单引号，则会报错

SQL> select last_name,job_id,department_id from employees where last_name=Whalen;

select last_name,job_id,department_id from employees where last_name=Whalen

*

ERROR at line 1:

ORA-00904: "WHALEN": invalid identifier

如果'Whalen'全部小写，则会没有结果

SQL> select last_name,job_id,department_id from employees where last_name='whalen';

no rows selected

例子：查询入职日期为23-MAY-06的人员信息

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

LAST_NAME HIRE_DATE

------------------------- ------------

Feeney 23-MAY-06

如果格式修改为中国习惯的YYYY-MM-DD，则会报错

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

select last_name,hire_date from employees where hire_date='2006-05-23'

*

ERROR at line 1:

ORA-01861: literal does not match format string

这里实际上做了一个隐式转换，将字符串转换成日期类型，格式不一致的话，就报错了，当然也可以用转换函数，我们下一章会讲。

我们刚才看到限制条件的时候，字符类型和日期类型要使用单引号引起来，字符类型要区分大小写，日期类型有严格的格式匹配的。

2、比较条件

在where子句中常用的比较操作符如下表：

例子：查找薪水小于等于3000的人员

SQL> select last_name,salary from employees where salary<=3000;

LAST_NAME SALARY

------------------------- ----------

OConnell 2600

例子：查找薪水在2500到3000之间，包含2500和 3000的人员

SQL> select last_name,salary from employees where salary between 2500 and 3500;

LAST_NAME SALARY

------------------------- ----------

OConnell 2600

例子：查找被管理ID为100、101及102管理者管理的人员信息

SQL> select employee_id,last_name,salary,manager_id from employees where manager_id in (100,101,201);

EMPLOYEE_ID LAST_NAME SALARY MANAGER_ID

----------- ------------------------- ---------- ----------

201 Hartstein 13000 100

Oracle中可以使用like条件进行通配符搜索，Oracle中的通配符有2种：

%：表示0或者多个字符

_：表示一个字符

例子：查找员工的姓中第一个字母为S的员工信息

SQL> select first_name from employees where first_name like 'S%';

FIRST_NAME

--------------------

Sundar

例子：查找员工的名字中第二个字母为o的员工信息

SQL> select last_name from employees where last_name like '_o%';

LAST_NAME

-------------------------

Colmenares

如果要搜索包含%或者_的字段值，则需要使用escape关键字进行转义

例子：查询工作ID中包含_的人员信息

SQL> select employee_id,job_id from employees where job_id like 'SH\_%' escape '\';

EMPLOYEE_ID JOB_ID

----------- ----------

180 SH_CLERK

测试是否为null必须使用is null或者is not null，不能使用等号

例子：查找管理ID为空的人员信息，也就是查找没有人管的人员信息(应该只有国王没有上级吧)

SQL> select last_name,manager_id from employees where manager_id is null;

LAST_NAME MANAGER_ID

------------------------- ----------

King

3、逻辑条件

在where子句中的逻辑操作符如下表：

例子：查找薪水大于等于10000而且工作编号包含MAN的人员信息

SQL> select employee_id,last_name,job_id,salary from employees where salary>=10000 and job_id like '%MAN%';

EMPLOYEE_ID LAST_NAME JOB_ID SALARY

----------- ------------------------- ---------- ----------

201 Hartstein MK_MAN 13000

例子：查找薪水大于等于10000或者工作编号包含MAN的人员信息

SQL> select employee_id,last_name,job_id,salary from employees where salary>=10000 or job_id like '%MAN%';

EMPLOYEE_ID LAST_NAME JOB_ID SALARY

----------- ------------------------- ---------- ----------

201 Hartstein MK_MAN 13000

例子：查找工作编号不是'IT_PROG', 'ST_CLERK', 'SA_REP'这三个的人员信息

SQL> select last_name,job_id from employees where job_id not in('IT_PROG','ST_CLERK','SA_REP');

LAST_NAME JOB_ID

------------------------- ----------

Baer PR_REP

4、运算优先级

Oracle中的运算优先级如下表：

可以使用括号来改变优先级。

例子：查找薪水大于15000而且工作编号为'AD_PRES'的人员信息以及工作编号为'SA_REP'的人员信息

SQL> select last_name,job_id,salary from employees where job_id='SA_REP' or job_id='AD_PRES' and salary>15000;

LAST_NAME JOB_ID SALARY

------------------------- ---------- ----------

King AD_PRES 24000

Tucker SA_REP 10000

例子：查找薪水大于15000而且工作编号为'SA_REP'或'AD_PRES'的人员信息

SQL> select last_name,job_id,salary from employees where (job_id='SA_REP' or job_id='AD_PRES') and salary>15000;

LAST_NAME JOB_ID SALARY

------------------------- ---------- ----------

King AD_PRES 24000

5、使用order by子句进行排序

select语句的结果默认是没有排序的，我们可以使用order by子句对指定字段进行排序，order by子句位于select语句的最后，使用如下2个关键字进行排序

ASC：升序，不使用关键字的话默认为升序

DESC：降序

例子：查找人员信息，按照入职时间升序排序

SQL> select last_name,job_id,department_id,hire_date from employees order by hire_date;

LAST_NAME JOB_ID DEPARTMENT_ID HIRE_DATE

------------------------- ---------- ------------- ------------

De Haan AD_VP 90 13-JAN-01

Mavris HR_REP 40 07-JUN-02

例子：查找人员信息，按照入职时间降序排序

SQL> select last_name,job_id,department_id,hire_date from employees order by hire_date desc;

LAST_NAME JOB_ID DEPARTMENT_ID HIRE_DATE

------------------------- ---------- ------------- ------------

Banda SA_REP 80 21-APR-08

order by后面除了可以指定字段进行排序，还可以使用字段的别名以及字段的位置。

例子：使用字段的别名对人员年薪进行排序

SQL> select employee_id,last_name,salary*12 annsal from employees order by annsal;

EMPLOYEE_ID LAST_NAME ANNSAL

----------- ------------------------- ----------

132 Olson 25200

例子：使用字段的位置对人员年薪进行排序

SQL> select employee_id,last_name,salary*12 annsal from employees order by 3;

EMPLOYEE_ID LAST_NAME ANNSAL

----------- ------------------------- ----------

132 Olson 25200

order by子句后面可以使用多个字段进行排序，但是ASC，DESC关键字只对其前面的一个字段有效，如果字段后面没有加上关键字，默认是ASC升序。

例子：使用部门编号进行升序排列，部门编号相同的再使用薪水进行降序排列来显示人员信息

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

LAST_NAME DEPARTMENT_ID SALARY

------------------------- ------------- ----------

Whalen 10 4400

6、使用替换变量

通过使用替换变量，可以用1条SQL语句执行不同的查询，比如我刚开始需要查询人员编号为100的信息，后来又需要查询人员编号为200的信息，如果使用替换变量，就可以沿用前面的的语句。替换变量有2个符号，一个是单&符号，一个是双&&符号，双&&符号用在语句当中这个变量出现多次的情况。

例子：通过提示输入人员编号查询不同人员的信息

SQL> select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num;

Enter value for employee_num: 100

old 1: select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num

new 1: select employee_id,last_name,salary,department_id from employees where employee_id=100

EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID

----------- ------------------------- ---------- -------------

100 King 24000 90

如果变量的值是字符或者日期，则最好在语句中使用单引号讲变量引起来，就不需要在输入变量的时候输入单引号了。

例子：通过提示输入工作编号查询不同人员的信息

SQL> select last_name,department_id,salary*12 from employees where job_id='&job_title';

Enter value for job_title: IT_PROG

old 1: select last_name,department_id,salary*12 from employees where job_id='&job_title'

new 1: select last_name,department_id,salary*12 from employees where job_id='IT_PROG'

LAST_NAME DEPARTMENT_ID SALARY*12

------------------------- ------------- ----------

Hunold 60 108000

替换变量除了用在条件比较，还可以用于select子句中的字段，where子句中的整个条件，order by子句的排序字段，甚至select关键字后面的所有内容。

例子：通过提示输入需要的字段信息来进行查找、限制和排序

SQL> select employee_id,last_name,job_id,&column_name from employees where &condition order by &order_column;

Enter value for column_name: salary

Enter value for condition: salary>15000

Enter value for order_column: last_name

old 1: select employee_id,last_name,job_id,&column_name from employees where &condition order by &order_column

new 1: select employee_id,last_name,job_id,salary from employees where salary>15000 order by last_name

EMPLOYEE_ID LAST_NAME JOB_ID SALARY

----------- ------------------------- ---------- ----------

102 De Haan AD_VP 17000

如果语句里面的变量需要重复使用，可以使用&&符号。

例子：通过提示输入需要的字段进行选择和排序

SQL> select employee_id,last_name,job_id,&&column_name from employees order by &column_name;

Enter value for column_name: department_id

old 1: select employee_id,last_name,job_id,&&column_name from employees order by &column_name

new 1: select employee_id,last_name,job_id,department_id from employees order by department_id

EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID

----------- ------------------------- ---------- -------------

200 Whalen AD_ASST 10

如果将上面的语句再次执行，会发现不需要输入变量的值了，这是由于刚才输入变量的值时就已经在系统中定义了该变量的值为department_id，如果要更换为其他的值，就需要使用undefine关键字删除该变量，当然也可以使用define预先定义变量的值。

例子：先定义一个变量并赋值，再在select语句中使用，最后删除变量

SQL> define employee_num=200

SQL> select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num;

old 1: select employee_id,last_name,salary,department_id from employees where employee_id=&employee_num

new 1: select employee_id,last_name,salary,department_id from employees where employee_id=200

EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID

----------- ------------------------- ---------- -------------

200 Whalen 4400 10

SQL> undefine employee_num;

前面进行变量替换的时候，会显示替换前和替换后的语句，可以设置sqlplus的verify环境变量进行设置是否显示。

例子：设置进行变量替换的时候不显示替换前和替换后的语句

SQL> set verify off

SQL> select employee_id,last_name,salary from employees where employee_id=&employee_num;

Enter value for employee_num: 200

EMPLOYEE_ID LAST_NAME SALARY

----------- ------------------------- ----------

200 Whalen 4400

SQL> show verify

verify OFF

sqlplus里面环境变量还有很多，可以所有show all进行显示，如果要设置，就使用set。