###数据排序
语法:
SELECT [DISTINCT]*| 字段 [别名][字段 [别名]]
FROM 表名称 [别名]
[WHERE 条件(S)]
[ORDER BY 字段 [ASC|DESC][字段 [ASC|DESC],…]]
说明:
该子句在所有的SQL语句最后
可以指定多个排序的字段
默认升序
降序需要手工指定
有需要的时候才进行排序
查询所有雇员的信息,按工资排序
select * from emp order by sal;
select * from emp order by sal asc;
降序排列
select * from emp order by sal desc;
查询所有雇员信息,按照工资降序排列,工资相同,则按雇佣日期从早到晚排列
select * from emp order by sal desc,hiredate asc;
###单行函数
分类:
字符函数
数字函数
日期函数
转换函数
通用函数
####字符函数:主要是进行字符串数据的操作
UPPER(字符串|列) 将输入的字符串变为大写返回
LOWER(字符串|列) 将输入的字符串变为小写返回
INITCAP(字符串|列) 开头首字母大写
LENGTH(字符串|列) 求出字符串长度
REPLACE(字符串|列) 进行替换
SUBSTR(字符串|列) 开始点[结束点],字符串截取
oracle提供一个虚拟表dual
转大写
select upper(‘hello’) from dual;
select * from emp where ename=upper(‘&str’);
转小写
将所有雇员姓名按照小写字母返回
select lower(ename) from emp;
将每个雇员姓名的开头首字母大写
select initcap(ename) from emp;
查询出每个雇员姓名的长度
select ename,length(ename) from emp;
查询出雇员姓名长度刚好是5的雇员信息
select ename,length(ename) from emp where length(ename)=5;
使用字符“_”替换雇员姓名中的所有字母“A”
select replace(ename,’A’,’_’) from emp;
字符串截取有两种语法:
1 SUBSTR(字符串|列,开始点),表示从开始点一直截取到结尾
select ename,substr(ename,3) from emp;
2 SUBSTR(字符串|列,开始点,截取多少位),表示从开始点截取多少位
select ename,substr(ename,0,3) from emp;
select ename,substr(ename,1,3) from emp;
要求截取每个雇员名字的后三个字母
通过长度-2确定开始点
select ename,substr(ename,length(ename)-2) from emp;
设置负数,表示从后指定截取位置
select ename,substr(ename,-3) from emp;
####数字函数
ROUND(数字|列[,保留小数的范围]):四舍五入的操作
TRUNC(数字|列[,保留小数的范围]): 舍弃指定位置的内容
MOD(数字1,数字2):取模,取余数
ROUND
select round(903.5) from dual;
select round(903.53567) from dual;
select round(903.53567),round(-903.53567) from dual;
select round(903.53567),round(-903.53567),
round(903.53567,-1) from dual;
select round(903.53567),round(-903.53567),round(903.53567,-1),
round(903.53567,2) from dual;
TRUNC trunc
select trunc(903.53567),trunc(-903.53567),trunc(903.53567,-1),
trunc(903.53567,2) from dual;
MOD
select mod(3,10) from dual;
####日期函数
取得今天的日期,可以使用“SYSDATE”
select sysdate from dual;
日期的计算有以下几种计算:
日期+数字=日期,表示若干天之后的日期
日期-数字=日期,表示若干天之前的日期
日期-日期=数字,表示两个日期之间的天数,但是必须大日期减小日期
求出每个雇员到今天为止的雇佣天数
select ename,hiredate,sysdate-hiredate from emp;
日期除了以上的三个计算公式,还有四个操作函数
LAST_DAY(日期):求出指定日期当月的最后一天
求出本月的最后一天日期
select last_day(sysdate) from dual;
NEXT_DAY(日期,星期数):求出下一个指定星期几的日期
下一个周六是几号
select next_day(sysdate,'sat') from dual;
下一个周日是几号
select next_day(sysdate,'sun') from dual;
ADD_MONTHS(日期,数字):求出若干月之后的日期
四个月后是几号
select add_months(sysdate,4) from dual;
MOUNTHS_BETWEEN(日期1,日期2):求出两个日期之间所经历的月份
求出每个雇员到今天为止的雇佣月份
select ename,hiredate,trunc(months_between(sysdate,hiredate)) from emp;
注意:在所有开发之中,如果是日期的操作,建议使用以上的函数,可以避免闰年的问题。
####转换函数
之前我们学习了字符函数,数字函数和日期函数,而转换函数的主要功能是完成这几种数据之间的互相转换的操作,一共有三种转换函数。
TO_CHAR(字符串|列,格式字符串):将日期或者数字变为字符串显示;
TO_DATE(字符串,格式字符串):将字符串转变为DATE数据显示;
TO_NUMBER(字符串):将字符串变为数字显示.
TO_CHAR()函数
格式:年(yyyy),月(mm),日(dd)
拆分年月日
select to_char(sysdate,’yyyy-mm-dd’),to_char(sysdate,’yyyy’) year,to_char(sysdate,’mm’) month,to_char(sysdate,’dd’) day from dual;
干掉日期中的前导零
select to_char(sysdate,'fmyyyy-mm-dd') day from dual;
在oracle之中,sysdate函数中是包含了时间的,要显示时间需要增加标记
使用sysdate显示时间
select to_char(sysdate,’yyyy-mm-dd hh:mi:ss’) haha from dual;
select to_char(sysdate,’yyyy-mm-dd hh:mi:ss’) from dual;
使用24小时制
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual; MI
使用TO_CHAR函数之后,所有内容为字符串,不再是date型数据,TO_CHAR同样可以用在数字上用来格式化数字
格式化数字22222222222;
select to_char(22222222222,’999,999,999,999,999,999,999’) shuzi from dual;
这时,格式化字符串的数字9表示格式,而不是数字9。
格式化数字为货币记录格式,使用“L”标记表示转换为当前语言环境下的货币符号
select to_char(22222222222,'L999,999,999,999,999,999,999') huobi from dual;
TO_DATE(): (熟悉用法)
使用方法如下;
select to_date(‘1989-09-12’,’yyyy-mm-dd’) from dual;
TO_NUMBER(): (基本不用,会用即可)
使用方法如下;
select to_date(‘1989-09-12’,’yyyy-mm-dd’) from dual;
在oracle中,不使用该函数也可以完成该功能,如下:
select ‘2’+’3’ from dual;
####通用函数
通用函数主要有NVL(),DECODE()两个,这两个函数算是oracle的特色函数。
NVL(): 处理null
要求查询所有雇员的全部年薪
select ename,sal,comm,(sal+comm)*12 from emp;
查询的结果出现问题,解决办法是将comm中的null值变成0,方法如下:
查看转换过程
select ename,sal,comm,nvl(comm,0) from emp;
解决问题
select ename,sal,comm,(sal+nvl(comm,0))*12 from emp;
DECODE():多数值判断
判断的是数值而不是条件,类似编程中的if…else…
说明:这种判断肯定是进行逐行判断,所以这个时候就必须采用DECODE(),而此函数的用法如下:
DECODE(数值|列,判断值1,显示值1,判断值2,显示值2,…)
例如:要求显示全部雇员的职位,但是这些职位要求显示成pinyin。
CLERK: 办事员
SALESMAN: 销售员
MANAGER: 经理
ANALYST: 分析员
PRESIDENT: 总裁
实现:
select empno,ename,job,decode(job,
'CLERK','banshiyuan',
'SALESMAN','xiaoshouyuan',
'MANAGER','jingli',
'ANALYST','fenxiyuan',
'PRESIDENT','zongcai')
from emp;
###总结:
1:以上列出的SQL语句只是最基本的语法,明白SELECT,FROM,WHERE,ORDER BY之间的关系;
2:熟悉SCOTT用户的4张表的内容,包括列的名称,作用和类型;
3:精通以上所有SQL语句和函数的使用。