一 ORACle的排序
排序语句:
SELECT [DISTINCT] * | [列名 [别名],…] FROM 表名 WHERE 条件 ORDER BY 排序的列 [ASC | DESC],…;
select * from emp order by hiredate desc ( desc表示降序 从大到小 asc 表示升序)
select * from emp where where job='MANAGER' order by hiredate desc; ( 按条件查询出结果后排序)
select * from mep order by hiredate desc,sal desc ( 按条件一升序后 相同的条件一在进行条件二排序)
二 Oracle的 伪列
别名
在select语句中 每一个被查询的表都有一个别名 默认就是它的表名 可以给表添加一个别名 添加的别名覆盖默认的别名
select emp.ename from emp t; --错误
select t.ename from emp t;
--如果多表查询时 每一个表都有相同的列名 这时必须要使用 别名
select t.deptno from emp t,dept d;ROWID 是表中每一条记录的唯一标识符,数据库内部使用它来存储行的物理地址
该地址可以唯一地标识数据库中的一行,可以使用 ROWID 伪列快速地定位表中的某一行
无法使用update语句修改
ROWNUM 是SQL查询返回的结果集中每一行的行号【重点】
可以用它来限制查询返回的行数。
ROWNUM是先查到结果集之后再加上去的一个列 。
在oracle中使用 rownum来进行分页
select t.* ,rownum as rn from emp where rownum>=10 and rownum<=20这是错误的
因为行的循环查找 从索引1 开始 第一条记录的索引为1 不满足 循环第二条时 记录索引从1 开始 。。。。所以的行的行号都是1 永远不满足
select * from (select t.*,rownum as rn from emp t) where rn>=10 and rn<=20 这是正确的 (一般使用)
分页另一种写法
select * from (select t.*,rownum as rn from emp t where rownum<=20) where rn>=10 正确的
select * from emp where rownum<=10 这是正确
记录:rownum大于1的记录永远不可能成立 rownum<等于任何值都成立
三 oracle的单行函数
单行函数对于从表中查询到的每一行返回一个值,可以出现在 SELECT子句中、 WHERE 子句中、ORDER BY子句中
单行函数可以大致划分为:
字符函数:接收字符串输入并返回字符串或数值
数字函数:接收数值输入并返回数值
日期函数:对日期进行操作
转换函数:从一种数据类型转换成另一种数据类型
正则表达式函数字符串函数
lower(n) 将字符转换成小写 对应java String.toLowerCase
select lower('AAAA') from dual; --aaaa
select lower(ename) from emp; --列输出结果全部小写
upper(n) 将字符串转换成大写 对应java String.toUpperCase
replace(列名,被替换的字符串,替换的字符串) 对应java String.replace或者 replaceAll
select ename,replace(job,'MANAGER','经理') from emp; --job列中 所有的MANAGER都被替换成了经理
select replace('AAAtestgggg','test','测试') from dual; --AAA测试gggg
instr(列名,被搜索的字符串)
select instr('AAAtestgggg','test') from dual; --4 索引从1开始
substr(列名,开始位置,总长度)
select instr('AAAtestgggg',4,3) from dual; -- java substring 根据 被截取的字符串.substring(开始位置,结束的位置【不包括结束的位置】)
javascript 被截取的字符串.substr(开始位置,长度) 被截取的字符串.substring
oracle substr
concat(参数1,参数2) 等价于 ||
select concat('a','b') from dual; --- ab 等价于 'a'||'b' java "aaa"+"bbb"
length(列名) 获取字符的长度 java String.getLength();
trim(列名) 去空格
ltrim 去左侧的空格
rtrim 去右侧的空格
nvl(列名,值) 当列的值为空时 输入第二参数的值 如果不为空 输出当前列的值 【重要】
select nvl('','bac') from dual -- '' is null 所以输出 bac
nvl2(列名,值1,值2) 当列的值为空时 输出第三个参数的值 如果不为空输出第二个参数的值 【重要】
decode(列名,条件1,值1,条件2,值2......,条件n,值n,默认值) 当列的值等于条件n时输出值n 【重要】
select decode('2','0','男','1','女','人妖') from dual
数字函数
mod(5,2) 取余数 等价于java 5%2
round(n) 对整数位进行四舍五入 满5进1
round(n,p) 对小数位进行四舍五入 比如 round(5.6767,2) 输出为5.68
trunc(n) 截断小数位 只保留整数位
trunc(n,p) 截断小数位意外的位数 比如 trunc(5.666,2) 输出为5.66 就是保留p位小数
日期函数
sysdate 获取系统的当前时间 【重点】
add_months(日期,月数) 将日期加上月数 并返回 比如 (sysdate=2016-3-3)+5个月 =2016-8-3
select sysdate+天数 from dual 将当前日期加上某个数字 表示+天数
months_between(日期1,日期2) 比较日期1和日期2的相差的月份数 结果=日期1-日期2
last_day(日期) 返回日期对应月份的最后一天
round(日期,格式)
YEAR 获取当年的第一天
MONTH 获取当月的第一天
DAY 获取 第一个周末的第一天
next_day(日期,第几天) 获取当前日期下一个星期的第几天
转换函数 【每一个都是重点】
to_date(日期字符串,日期格式) 字符串的格式必须由第二个参数来判断
日期格式 :
年 yyyy,月mm,日dd,小时(hh12小时制,hh24 24小时制) 分钟 mi 秒 ss
举例 :select to_date('2015/03/09','yyyy/mm/dd') from dual
to_char(数字) 将数字转换成char类型
to_char(日期,日期格式) 将日期转换成char类型 日期格式 可以任意 输出的内容 根据日期的格式而定
举例 select to_char(sysdate,'hh24') 输出24小时制的当前时间的hour
to_number(字符串数字) 将字符串的数字转换成number类型
举例 to_number('123')输出数字 123
DECODE函数(oracle特有)
查询出所有员工的姓名和它的领导编号,没有领导的领导编号列值用“无”代替。
Select ename,decode(mgr,null,’无’) from emp;
查询所有的员工姓名和奖金,没有奖金的显示为0:
Select ename,decode(comm,null,0) from emp;
查询出所有员工信息,把它的职位做如下替换:
SALESMAN ? 销售员
MANAGER ?经理
ANALYST ? 分析人员
PRESIDENT ? 总裁
在Oracle9i以上版本建议使用CASE表达式来替代
CASE expre
WHEN condition THEN return_value
WHEN condition2 THEN return_value2
ELSE default_value
END
select ename,decode(job,'CLERK','业务员','SALESMAN','销售员','MANAGER','经理','ANALYST','分析人员','PRESIDENT','总裁') FROM emp;
when ‘CLERK’ then ‘业务员’
when ‘SALESMAN’then’销售员’
when ‘MANAGER’then’经理’
when ‘ANALYST’then’分析人员’
when ‘PRESIDENT’then’总裁’
end
FROM emp;