码迷,腾讯三分彩 www.csbesbj.com
腾讯三分彩 > 数据库 > 详细

www.9ph.com:oracle篇 之 排序、限制查询行

时间:2019-04-22 12:02:33      阅读:43      评论:0      收藏:0      [点我收藏+]

腾讯三分彩 www.csbesbj.com 标签:出名字   span   strong   数字   from   取出   null   优先   多个   

第二章:排序、限制查询行

一、order by子句

1.order by排序规则

(1)asc,升序排列,默认取值

(2)desc,降序排列

(3)order byselect命令的最后一个子句

select last_name,salary,dept_id

from s_emp

order by salary;

select last_name,salary,dept_id

from s_emp

order by salary asc;

select last_name,salary,dept_id

from s_emp

order by salary desc;

2.order bynull值处理

(1)升序中,放最后

(2)降序中,放最前

select last_name,title,www.csbesbj.commission_pct

from s_emp

order by www.csbesbj.commission_pct;

3.order by后可跟内容

(1)列名

(2)列的别名

(3)数字:select列表项的位置

select last_name name,salary

from s_emp

order by name;

 

select last_name,salary*12

from s_emp

order by 2;

4.order by后跟多个列

(1)先按第一个列排序,内容相同的,再按照第二个列排...

(2)asc、desc只修饰一个列

select last_name,dept_id,salary

from s_emp

order by dept_id,salary desc;

等价

select last_name,dept_id,salary

from s_emp

order by dept_id asc,salary desc;

 

二、where子句

1.限制取出记录数

(1)where比较中的字符串常量严格区别大小写

select last_name,salary,dept_id

from s_emp

where dept_id=42;

SELECT LAST_NAME,SALARY,DEPT_ID

FROM S_EMP

WHERE DEPT_ID=42;

select last_name,salary,dept_id

from s_emp

where last_name=‘smith‘;

2.where中的操作符

(1)between...and...:闭区间;先跟小值,再跟大值

select last_name,salary

from s_emp

where salary between 940 and 1100;

select last_name,salary

from s_emp

where salary between 1100 and 940;

(2)in:从给定的列表范围内匹配值

select last_name,salary,dept_id

from s_emp

where dept_id in(41,43,45);

(3)like:模糊查询

通配符:

%代表0或多个字符

_代表单一字符

select last_name,salary

from s_emp

where last_name like ‘M%‘;

escape自定义转义字符,可以让统配符失去特殊意义,变成普通字符

查出名字以_a开始的?

select last_name,salary

from s_emp

where last_name like ‘\_a%‘ escape ‘\‘;

(4)is null:空值判断

空值不能用等号进行比较

*select last_name,salary,title,www.csbesbj.commission_pct

from s_emp

where www.csbesbj.commission_pct is null;

(5)多条件查询

and:多个条件必须同时满足

or:多个条件选一个

and的优先级比or

select last_name,salary,dept_id

from s_emp

where salary>=1000

and dept_id=44

or dept_id=42;

等价

select last_name,salary,dept_id

from s_emp

where (salary>=1000

and dept_id=44)

or dept_id=42;

oracle篇 之 排序、限制查询行

标签:出名字   span   strong   数字   from   取出   null   优先   多个   

原文地址:https://www.cnblogs.com/wskb/p/10749174.html

(0)
(0)
   
举报
评论 一句话评论(0
0条  
登录后才能评论!
? 2014 腾讯三分彩 www.csbesbj.com 版权所有 京ICP备13008772号-2
迷上了代码!