专业只做数据库实训和认证的品牌机构

微信公众号新浪微博
免费咨询电话:400-0909-964
当前位置: 网站首页 > OCP认证 > OCP课程 > OCP培训课程:SQL之限制和排序数据

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

文章来源: 更新时间:2016/6/20 15:31:31

在线老师点击咨询:

最新学讯:近期OCP认证正在报名中,因考试人员较多请尽快报名获取最近考试时间,报名费用请联系在线老师,甲骨文官方认证,报名从速!

我要咨询

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。

本文地址:http://www.cuug.com.cn/ocp/kecheng/12265753138.html 转载请注明!


在线预约 抢先报名 获取课程排期

Oracle培训机构

金牌讲师<>

冉乃纲-老师CUUG金牌讲师
冉老师 CUUG金牌讲师 Oracle及RedHat高级讲师、Unix/Linux 资深专家...[详细了解老师]

免费咨询上课流程 客服在线中

陈卫星-老师CUUG金牌讲师
陈老师 CUUG金牌讲师 精通Oracle管理、备份恢复、性能优化 11年Ora...[详细了解老师]

免费咨询上课流程 客服在线中

选学校如何选择适合自己的学校

CUUG -CHINA UNIX USER GROUP,是国际UNIX组织UNIFORUM的中国代表,是国内悠久的专业UNIX培训机构,被誉为中国UNIX 的摇篮。多年来,以提高教学质量为本,强调素质教育,积极引进、消化国外的新技术,有效的结合中国....[详情]

一站式服务(从入学到就业一帮到底)

入学

学习

就业

实操

食宿
地址:北京市海淀区北清路164号28-38号院
课程咨询:010-59426307 010-59426319 400-0909-964
企业服务:137 1818 8639(陈经理)
部分信息来源于网络,如有错误请联系指正!
版权所有@北京神脑资讯技术有限公司 (CUUG,中国UNIX用户协会) Copyright 2016 ALL Rights Reserved 京ICP备11008061号-1