Oracle学习笔记0.2


阅读数: 次   

文章导航

Oracle学习代码笔记

函数…

⚡ ⚡ ⚡

常用函数

1.字符函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
--拼接字符串
select 'a' || 'b' from dual;--||拼接
--拼接表中两列
select job||'-'||ename from emp;
select concat('a','b') from dual;--concat拼接
select concat(concat(job,'-'),ename) from emp;

--首字母大写 initcap
select initcap('my name is Teng') from dual;
select initcap(ename) from emp;
--转大写 upper
select upper('my name is Teng') from dual;
--转小写 lower
select lower('MY NAME IS TENG') from dual;

--截取字符串 substr('要截取字符串',从第几位开始(从1开始),截取长度[不写截取到最后])
select substr('my name is teng',7,4) from dual;

--替换字符串 replace('要替换字符串','需要替换掉的字符串','替换后的字符串'[不写表示删除])
select replace('my name is Teng','Teng') from dual;
--去除左侧固定的字符('要操作的字符串','需要去除的内容'[不写代表空格])
select ltrim('       Teng        ') from dual;
--去除右侧固定的字符('要操作的字符串','需要去除的内容'[不写代表空格])
select rtrim('       Teng********','*') from dual;
--去除左右侧固定的字符，中间无法去除
select trim('       Teng        ') from dual;
select trim(both'*' from '       Te n*g********') from dual;
--用trim 去除左侧
select trim(leading'*' from '********Te n*g********') from dual;
--用trim 去除右侧
select trim(trailing'*' from '********Te n*g********') from dual;

--补全字符串 lpad('需要补全的原来字符串',补几位,'补什么')
select lpad('10',5,'0') from dual;--左补全
select rpad('10',5,'0') from dual;--右补全

--计算年龄
select substr('41030619991007444X',7,4) from dual;
--1.计算相差月份，月份/12
select months_between(sysdate,to_date(substr('41030619991007444X',7,8),'yyyy-MM-dd'))/12 from dual;
--2.两个字符串相减
select to_char(sysdate,'yyyy')-substr('41030619991007444X',7,4) from dual;

-- 级联表
create table city(
  id number,
  name varchar(10),
  parentid umber
)
insert into city values(0,'中国',null);
insert into city values(1,'河南省',0);
insert into city values(2,'郑州市',1);
insert into city values(3,'二七区',2);
-- 把单列多行数据拼接一行显示
select wm_concat(name) from city where id in(1,2,3);
-- 字符串替换
select replace(wm_concat(name),',','--') from city where id in(1,2,3);

2.数学函数

1
2
3
4
5
6
7
8
9
10
11
--向上取整 ceil
select ceil(4.1)from dual;
--向下取整 floor
select floor(4.9)from dual;
--按照一定的精度截取数字 trunc(不会四舍五入)
select trunc(3.1415926,4) from dual;

-- 计算性别
-- 取余 mod()
select mod(substr('41030619991007444X',17,1),2) from dual;
select decode(mod(substr('41030619991007444X',17,1),2),'0','女','1','男') from dual;

3.日期函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
--获取系统当前时间 sysdate
select sysdate from dual;
--next_day() 获取指定日期之后的下一个星期几
select sysdate,next_day(sysdate,2) from dual;
--本月最后一天
select last_day(sysdate) from dual;
--add_months() 添加指定月份后的日期
select add_months(sysdate,1) from dual;
--时间差(天数) to_date(字符串转换日期)
select to_date('2020-05-13','yyyy-MM-dd')-sysdate from dual;
--月数时间差 months_between
select months_between(to_date('2020-05-13','yyyy-MM-dd'),sysdate) from dual;

--trunc(截取日期,不显示时分秒)
select trunc(sysdate) from dual;
select to_date('2020-05-13','yyyy-MM-dd')-trunc(sysdate) from dual;
--本月初日期
select trunc(sysdate,'month') from dual;
--本年初日期
select trunc(sysdate,'year') from dual;

--添加月 add_months()
select add_months(sysdate,2) from dual;
select add_months(sysdate,-2) from dual;--负数为减

4.转换函数

1
2
3
4
5
6
7
8
9
10
11
12
13
--to_char
--把数字转换成字符串(如果不够,自动补空格)
select to_char(11223344,'999,999,999') from dual;
--把日期转换成字符串
select to_char(sysdate,'yyyy') from dual;--当前年份
select to_char(sysdate,'MM') from dual;--当前年份
select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from dual;--当前时间

--to_date 将字符串转换为时间
select to_date('2020-02-24 10:54:30','yyyy-MM-dd hh:mi:ss') from dual;

--to_number 将字符串转换为数字(统计,对非数字)
select to_number('2020')-1 from dual;

5.聚合函数

sum(求和)，avg(平均)，max(最大)，min(最小)，count(计数)

1
select count(*),sum(sal),max(sal),min(sal),avg(sal) from emp;

6.其他函数

1
2
3
4
5
6
7
8
--sys_guid 生成32位随机数
select sys_guid() from dual;
--nullif 判断两个值是否一致，一致返回null，否则输出第一个值
select nullif(sal,3000) from emp;
--decode(列名,'值1',输出1,'值2',输出2...,否则输出)
select decode(deptno,20,'河南',30,'河北','北京'),e.* from emp e;
--nvl(不为空显示,为空显示) 判断是否为null
select nvl(comm,0)+sal from emp;

应用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
--成绩表
create table stuno(
  stuno varchar2(20),
  cid number(1),
  score number(3),
  uptime date
)
--查询修改表
select * from stuno for update;
--新建表复制内容
create table score as(select * from stuno);
drop table stuno;
-- 三期考试成绩分数排名
--断号排序(内层查询可以引用外层查询)
select s1.*,(select count(*)+1 from score s2 where s2.cid=3 and s2.stuno!=s1.stuno and s2.score>s1.score)
from score s1 where s1.cid=3;
--连号排序
select s1.*,(select count(distinct(score))+1 from score s2 where s2.cid=3 and s2.stuno!=s1.stuno and s2.score>s1.score) 
from score s1 where s1.cid=3;
-- 求三期考试成绩分数最高的三个学生
select sc.* from(
  select s.*,(
    select count(*)+1 from score s2 
    where s2.cid=s.cid --学期相等
    and s2.stuno=s.stuno --学号相等
    and s2.uptime<s.uptime--考试时间
  ) 第N次考试
  from score s where s.cid =1
)sc where sc.第N次考试=1
order by score desc
-- with t
with t as(
  select sc.* from(
    select s.*,(
      select count(*)+1 from score s2 
      where s2.cid=s.cid --学期相等
      and s2.stuno=s.stuno --学号相等
      and s2.uptime<s.uptime--考试时间
    ) 第N次考试
    from score s where s.cid =1
  )sc where sc.第N次考试=1
  order by score desc)
select * from t where rownum<=3

-- 行转列
--decode
select MAX(decode(c.cid,1,c,cname)) c1,MAX(decode(c.cid,2,cname)) c2,MAX(decode(c.cid,3,c.came)) c3,
MAX(decode(c.cid,4,c.came)) c4,MAX(decode(c.cid,5,c.came)) c5,MAX(decode(c.cid,6,c.came)) c6
from course c;
--case when then
select MAX(case c.cid when 1 then c.cname end) c1,
MAX(case c.cid when 2 then c.cname end) c2,
MAX(case c.cid when 3 then c.cname end) c3,
MAX(case c.cid when 4 then c.cname end) c4,
MAX(case c.cid when 5 then c.cname end) c5,
MAX(case c.cid when 6 then c.cname end) c6
from course c;
--pivot(不灵活，不能有多的列)
select * from course pivot(MAX(cname) for cid in (1,2,3,4,5,6))
--学生三个学期成绩行转列
--pivot需要去除表中多余的列
select * from (select cid,score from score where stuno='S006') s
pivot(MAX(score) for cid in (1 一期,2 二期,3 三期))
--decode和case when then不需要
select MAX(case c.cid when 1 then c.score end) 一期,
MAX(case c.cid when 2 then c.score end) 二期,
MAX(case c.cid when 3 then c.score end) 三期,
MAX(case c.cid when 4 then c.score end) 毕业
from score c where stuno='S006';
赏

感谢支持!

微信

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

正因为没有翅膀，<br>人们才会寻找飞翔的方法。