课堂笔记 - Oarcle - 02 集合 - order by - 单行函数 - 数据分组 - group by - 组函数

order by

作用：用于对查询结果进行排序，可以设置按照某个列字典顺序排列

用法：order by 列名 asc / desc --升序 / 降序(不填写则默认升序)

当存在列（job）排序值相同时，可以设置次要列（ename）排序

从 oracleName 表中查询符合 columnName = 20 的所有（ * ）行按照sal列的升序排列，按照 job 列升序排列（次要）；

select * from oracleName where columnName = 20 order by sal,job;

order by 列名 asc --升序(不填写则默认升序)

select * from oracleName where columnName = 20 order by sal asc;

order by 列名 desc --降序(不填写则默认升序)

select * from oracleName where columnName = 20 order by sal desc;

 

计算字段（列）：不在于表中，通过 + - * / 操作和列进行计算得到的列

 

从emp表中查询ename列的年薪，显示为新的列名 info ;

select (ename ||'的年薪为：'|| sal*12) info from emp; --‘ || ’ 为连接符,用于拼接关联字段

 

 

集合操作：

1.union 并集

2.nuion all 全集

3.intersect 交集

4.minus 差集

 

--集合：每次查询结果可以看作一个符合查询条件集合

select * from emp where deptno = 20;

select * from emp where sal > 2000;

 

-- union 并集

select * from emp where deptno = 20

union

select * from emp where sal > 2000;

-- union all 全集

 

select * from emp where depton = 20

union all

select * from emp where sal >2000;

-- union 和 union all 的区别在于：union all 会重复显示两个集合中相同的部分

 

-- intersect 交集

select * from emp where deptno = 20

intersect

select * from emp where sal > 2000;

 

-- minus 差集 ：需要注意两条sql语句的顺序

select * from emp where deptno = 20

minus

select * from emp where sal < 3000;

-- other

select * from emp where deptno = 20

minus

select * from emp where sal < 3000;

 

集合操作时注意：

1、保证两个sql语句查询后显示的结果列是一致的（select 后面的列名）

2、保证两个sql查询的殊数据类型是一致的

3、保证两个sql查询的列是相同的，否则查询的结果是无意义的

 

单行函数：以单个数值进行操作，返回一个值。

分类：

1、字符函数

concat:

select ename ||'的职位是'|| job from emp;

select concat ( concat(ename,'的职位是'),job) from emp;

 

initcap: 首字母大写

select initcap('li xin') from emp dual; -- dual是一个虚表：临时表内部为空,多用于临时显示

 

lower: 字符串中的字母小写

upper: 字符串中的字母大写

select * lower('laowang') from dual;

select * lower(ename) from emp;

 

length:

select ename, length(ename) from emp;

 

lpad: 左边填充，几个，字符默认是空格

rpad: 右边填充，几个，字符默认是空格

select lpad(ename,10,'*') from emp;

select rpad(ename,10,'*') from emp;

注意：填充个数要需设置合理，否则数据不完整

 

ltrim: 去除字符串左边指定字符，未指定则默认是空格

rtrim: 去除字符串右边指定字符，未指定则默认是空格

select ltrim('abc','a')from dual;

select ririm('abc','c')from dual;

 

replace: 替换字符串

select replace ('','','')test from dual;

 

substr: 截取子字符串

select substr ('',1,1)test from dual;

 

trim: 去除字符串,从左边右边开始，中间那如果左右两边都有间格不能删除

select trim('a' from 'a ba a')from bual;

2、数字函数

abs: 求取绝对值

select abs(-5) from dual;

 

ceil() 向上取整

select ceil(3.1) from dual;

 

floor() 向下取整

select floor(3.9) from dual;

 

round() 四舍五入

select round(4.5),round(4.4) from dual;

 

power(x,y) x的y次幂

select power(2,10) from dual;

 

sqrt()开平方根

select sal() from emp;

3、日期函数

sysdate: 返回系统当前日期，注意：没有括号

select sysdate from dual;

 

add_months(d1,d2) 在d1的基础上，添加d2个月的日期

select add_months(hiredate,12)from emp;

select add_months(sysdate,6) from daul;

 

months_between(d1,d2) 返回d1,d2日期相隔的月份，返回的不是一个整数

select months_between(sysdate,hiredate)/12 from emp;

 

last_day() 返回当前日期的最后一天

select hiredate,last_day(hiredate) from emp;

 

next_day() 返回下一个星期X的日期

select hiredate next_day(hiredate,'星期一') from emp;

select sysdate,netx_day(sysdate,'星期日') from emp;

 

4、转换函数

to_date() 转换日期字符串的格式

select to_date ('1999-12-12 12:12:12','YYYY-MM-DD HH24:MI:SS') from dual;

 

to_char() 转换为特定格式

select to_char(sal,'$9,999.00')from dual;

 

to_number() 字符串转换为数字

select to_number('876') from dual;

 

5、其他函数

nvl(d1,d2) 如果d1列的值为null，则转换为d2，必须类型相同（数字，字符...）

select ename,nvl(comm,0) from emp;

 

sys_guid() --UUID-- 随机生成32位的随机数

select sys_guid() from dual;

 

decode(d1,d2,'匹配') 类似于JAVA中Switch 条件匹配

select decode(sal,800,'屌丝',1200,'白领'，3000，‘小资’，5000，‘高富帅’，‘一般人’) frm emp;

 

case when then else end : 类似于JAVA中的 if - else if - else

selecte ename ,sal,case when sal<1000 then '屌丝'

when sal<2000 then '白领'

when sal<3000 then '小资'

when sal<4000 then '高富帅'

else '王老五' end from emp;

 

group by: 作用对于查询的数据进行分组，并结合组函数处理

select detpno,job from emp group by deptno,job ;

having: 作用用于对分组数据进行过滤

例：求平均薪水在2000以上的部门编号

select deptno from emp group by deptno having avg(sal) > 2000;

-- where 和 having 关键字不冲突!

select avg(sal), deptno from emp

where sal >1500 group by deptno having avg (sal) >2500 order by deptno desc;

组函数：可以用在分组中的函数

-- avg

例：求编号为20部门的平均薪水用svgsal列名显示？

select avg(sal) avgsal from emp where deptno = 20;

 

-- sum

例：求编号为20部门的员工总薪水用sumsal列名显示？

select sum(sal) sumsal from emp where deptno = 20;

 

-- count

例：求编号为20部门的员工个数？

select count(1) from emp where deptno = 20;

 

-- max

例：求编号为20部门的员工总薪水最高的用maxsal列名显示？

select max(sal) from emp where deptno = 20;

例：求hirdedate最大显示这个列

select max(hirdedate) from emp;

 

--min

例：求编号为20部门的员工总薪水最少的用minsal列名显示？

select min(sal) minsal from emp where deptno = 20;

例：求sal列最大并显示这个列

select min(sal) from emp;

 

转载于:https://my.oschina.net/u/4118325/blog/3038417