Oracle day02 函数
order by关键字
作用:用于对查询结果进行排序
用法:
1.利用asc 、desc对排序列进行升序或降序
2.order by后可以添加多个列(逗号分隔),当一个列的值相同时,在按第二列进行排序,依次类推
集合操作:
1.union 并集
2.union all 全集
3.intersect 交集
4.minus 差集
注意:
1.保证两个sql查询的列数是个数一致的
2.保证两个sql查询的列的数据类型是一致的
3.保证两个sql查询的列是相同的,否则查询的结果是无意义的
group by关键字
作用:对查询结果进行分组处理
用法:
1.分组之后,不能将除分组字段之外的字段放在select后面
2.group by 后面可以跟多个字段,则这多个字段值都相同时,才分为一组
3.分组之后,可以使用组函数对每个组进行数据处理
having 关键字
作用:用于对分组数据进行过滤
用法:
类似于where的用法
sql顺序分为两类:
1.sql的书写顺序
select from where group by having order by [asc/desc]
2.sql的执行顺序
from where group by having select order by [asc/desc]
--Order by 关键字
--作用:用于对查询结果进行排序
Select* from emp where deptno =20 order by sal asc Select * from emp where deptno =20 order by sal desc
--如何决定升序还是降序?
Select* from emp where deptno =20 order by sal asc Select * from emp where deptno =20 order by sal desc
--如何排序的列值相同时,如何处理?
Select*from emp where deptno =20 order by sal desc ,ename desc
|
--创建字段
|
- Union 并集
- Union all 全集
- Intersect 交集
- minus 差集
|
--计算字段:
不在于表中,通过+,-,*,/操作和列进行计算得到的列
--获取员工的年薪
select ename ||sal* 12 info from emp
select (ename ||'的年薪为'||sal* 12) info from emp
|
--集合:每次查询结果可以看做一个集合
Select *from emp where deptno =20; Select* from emp where sal>200;
--union 全集
Select *from emp where deptno =20 union Select* from emp where sal>1100;
--union 和union all的区别在于:union all 会重复显示2个集合相同的部分
--intersect 交集
Select * from emp where deptno =20 Intersect select *from emp where sal >2000;
--minus 差集 (注意2条语句的 顺序)
(集合col1减去 col2 集合中两者都包含的部分)
Select * from emp where deptno =20 Minus select *from emp where sal >2000;
--other
select *from emp where sal >2000 Minus Select * from emp where deptno =20 ;
注意:使用集合语句必须保证查询的列是一致的
--函数
|
--分类
--dual 是一个虚表,为了满足sql句式设置的这么一个表
1. 单行函数
1. 字符函数:
|
(1) --concat 拼接a,b两个字符串
① select concat (ename ,'的职位是') from emp; ② select concat (concat (ename , '的职位是'),job) from emp;
(2) --initcap (将每个单词首字母大写)
① Select initcap('wan yi kun') from dual;
(3) --Lower --upper
Lower将字符串中的字符小写 ,upper将字符串中的字符大写
① Select lower('LAOWANG') from dual; ② select upper('laowang') from dual;
(4) --length 获取字符串的长度
① Select ename ,length(ename) from emp;
(5) --lpad(a,b,c) --rpad(a,b,c)
lpad(a,b,c)将a字符串左边填充至b长度,用c字符填充,
rpad(a,b,c)将a字符串右边填充至b长度,用c字符填充
① Select lpad (ename,10,'*')from emp; ② Select rpad (ename,10,'*')from emp;
--注意:第二个参数要设定合理的值,否则数据就不完整!
③ Select lpad (ename,4)from emp;
(6) --ltrim(a,b)--rtrim(a,b)
--rtrim(a,b)(去除字符串右边指定字符,如果不设定第二个参数,默认去除空格)
① Select ltrim('a abccba a','a') from dual; ② Select ltrim(' abccba ') from dual;
--ltrim(a,b)(去除字符串左边指定字符,如果不设定第二个参数,默认去除空格)
③ Select rtrim('a abccba a','a') from dual; ④ Select rtrim(' abccba ') from dual;
(7) --replace(a,b,c)
--将a中的b字符串替换为c
① Select replace ('he love you','he','i')test from dual;
(8) --substr (a,b,c)
--将a的字符串,从b位置开始截取,截取c个长度
① Select substr('130888888888888888888132',3,8)test from dual;
(9) --trim (a from b)
--将b左右两边的a字符去除掉
① Select trim ('a' from 'a b a')from dual; ② Select trim (' ' from ' b ')from dual;
2. 数字函数
|
(1) --abs 求取绝对值
select abs(-5) from dual;
(2) --ceil 向上取整
Select ceil (3.1) from dual;
(3) --floor 向下取整
Select floor(3.9)from dual;
(4) --round四舍五人
Select floor(3.9)from dual;
(5) --power(x,y) x的y次幂
Select floor(3.9)from dual;
3. 日期函数
(1) --sysdate
--返回系统日期,没有括号
Select sysdate from dual;
(2) --Add_months(d1 ,d2)
--在d1的基础上,添加d2个月后的日期
Select hiredate,add_months(hiredate,12) from emp; Select add_months(sysdate,6) from dual;
(3) --months_between(d1,d2)
--返回d1,d2日期相隔的月份,返回的不是一个整数
Select months_between(sysdate,hiredate)/12 from emp;
(4) --last_day()
--返回当前日期月份的最后一天
Select hiredate,last_day(hiredate) from emp
(5) --next_day()
--返回下一个星期x的日期
Select hiredate ,next_day(hiredate,’星期一’) from emp; Select sysdate ,next_day(sysdate ,’星期日’) from dual;
4. 转换函数
|
(1) .to_date()
--将字符串转化为日期
Select to_date('1999-12-12 12:12:11', 'YYYY-MM-DD HH24:MI:SS') from dual;
(2) To_char()
--将数值,或日期转化为字符串
Select sal,to_char(sal,'$9,999.00')from emp;
(3) --to_number()
--将字符串转化为数字
Select to_number('876' ) from dual;
5. 其他函数
(1) --nvl(x,y)
如果列(x)的值为null,则显示为y,x y 的类型保持一致
Select ename,nvl(comm,0) from emp;
(2) --sys_guid()
生成32位 随机字符串码
Select sys_guid() from dual;
(3) Decode
(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
select ename ,sal,decode(sal,800,'屌丝', 2000,'白领', 3000,'小资', 50000,'高富帅', '一般人') from emp;
(4) case when then else end
类似于java中的if-else if-else
Select ename,sal,case when sal< 1000 then '屌丝' when sal< 2000 then '白领' when sal< 3000 then '小资' when sal< 4000 then '高富帅' when sal< 2000 then '白领'
Else '王宝强' end from emp;
6. 聚组函数
(可以用在分组中的函数)
(1) --avg
求平均值,只能对数字类型进行处理,不处理空字段
① 求20部门的平均薪水为多少?
Select avg(sal) avgsal from emp where deptno=20
(2) --sum
求和只能对数字类型进行处理,不处理空字段
① 求20 部门的员工的总薪水
Select sum(sal) sumsal from emp where deptno=20
(3) --count
对任何类型生效,不处理空字段,不处理空字段
① 求20部门的员工有几个
Select count(*) from emp where deptno=20; Select count(1) from emp where deptno=20;
(4) --max
求最大值,对任何类型生效,不处理空字段
① 求20部门的员工工资最高的是多少
Select max(sal) from emp where deptno=20;
(5) --min
求最小值,对任何类型生效,不处理空字段
① 求20部门的员工工资最高的是多少
Select min(sal) from emp where deptno=20;
7. 数据分组
|
(1) --group by关键字
--作用:用于对查询的数据进行分组,并处理
1)
Select deptno from emp group by deptno
分组之后,不能将除分组字段之外的字段放在select后面
--Group by 后面可以跟多个字段,则这么多个字段都相同,才分为一组
2)
Select deptno,job from emp group by deptno,job
--分组之后,可以使用分组函数对每个组进行数据处理
3) Select deptno,avg(sal) from emp group by deptno 4) Select deptno,count(1) from emp group by deptno
(2) --having 关键字
作用:用于对分组后的数据进行过滤 (类似于where的用法)
例:求平均薪水在2000以上的部门编号
1) Select deptno,avg(sal) from emp group by deptno having avg (sal)>2000 2) Select avg(sal),deptno from emp where sal >1500 group by deptno having avg(sal) >2500
(3) Order by
Select avg(sal),deptno from emp where sal >1500 group by deptno having avg(sal) >2500 order by deptno
8.Sql顺序分为两类
|
① Sql的书写顺序
1) slectrmher oyayngdrdeylase / esc
② Sql的书写顺序
1) from ere grou y haigetrde ylas/escl