SQL（oracle） 命令使用积累（持续更新）_oracle查询最新数据

1、connect by递归查询（父子结构）

例：select b_area.*,SYS_CONNECT_BY_PATH(b_area.fareaname, ‘/’) as fpathName from b_area start with b_area.fparentid is null connect by prior fid=fparentid
– start with 语句表示树从什么位置开始进行检索
– connect by语句表示当前数据行和下一行数据之间的关系。
– prior 语句表示那个字段属于前一行（英文解释：优先的；在先的，在前的）
– 如：connect by fparentid = prior fid;
– 表示当前行的fparentid 等于上一行的fid。
PS:结果集中：父在上一行，接着是其子

2、SYS_CONNECT_BY_PATH(b_area.fareaname, ‘/’)

如上例，结果集为
一般该函数用来存储父子结构 往上（靠近父）的路径（层级），有父子关系的fareaname用’/'连接

3、增加或删除一列

alter table tablename drop column columnname;
alter table tabelname add columnname varchar2(8) NULL;
sql命令：链接：https://blog.csdn.net/dazhi_100/article/details/12513945?utm_medium=distribute.pc_relevant.none-task-blog-baidulandingword-3&spm=1001.2101.3001.4242
例：表中已有数据修改字段类型
推荐使用方法1，方法2如果字段不能为空就无法使用—（Oracle的表中已存在数据，再来修改表的字段类型是无法修改的）
方法1

--1.创建备份表
create table test_bak as select * from test;
-- 2.清除原表数据
truncate table test;
-- 3.修改字段类型
alter table test modify test_id number(22);
-- 4.还原数据
insert into test select * from test_bak;
commit;
1
2
3
4
5
6
7
8
9

方法2:只备份需要修改的字段的数据，可以在原表上新增备份字段，也可以新建一张表备份字段数据

--1.新增字段
alter table test add test_id_bak number;
--2.复制数据
update test set test_id_bak = test_id;
commit;
--3.清除字段数据并修改字段类型
update test set test_id = null;
commit;
--4. 修改字段类型（注意clob、blob等类型无法修改为别的类型）
alter table test modify test_id number(22);
-- 5.还原数据
update test set test_id=test_id_bak;
commit;
-- 6.删除临时字段
alter table test drop column test_id_bak;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

4、sql查找日期显示时分秒

例句SQL> select ename,to_char(hiredate,‘yyyy-mm-dd hh24:mi:ss’) from emp;

说明：（1）to_char()函数也可以换成select * from ycl_jtlljc where cjsj >= to_date(‘2020/12/26 15:36:13’,‘yyyy/mm/dd hh24:mi:ss’)
（2）yy:两位数字的年份 yyyy：四位数字的年份。 mm两位数的月份，dd两位数字的天数；
hh24:24小时制； hh12：12小时制； mi分钟； ss秒；

5、to_timestamp函数 给 时间戳字段赋值

– Add comments to the table

comment on table test_gg is ‘测试Date和Timestamp专用’ ;

–Add comments to the columns

comment on column test_gg.tid is ‘主键’;

comment on column test_gg.tname is ‘昵称’;

comment on column test_gg.tbirthday is ‘生日(Timestamp类型)’;

comment on column test_gg.tbirthdate is ‘生日(Date类型)’;

–插入数据

insert into test_gg values(sys_guid(),
‘张三’,
to_timestamp(to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’),‘yyyy-mm-dd hh24:mi:ss’),
sysdate);

6、group by (sql中如何取重复的数据中时间最近的一条)

SELECT
	t1.重复列,
	t1.时间列,
	t1.其余列 
FROM
	表 t1
	INNER JOIN ( SELECT t2.重复列, max( t2.时间列 ) AS 时间列 FROM 表 t2 GROUP BY t2.重复列 ) AS t3
	ON t1.重复列 = t3.重复列 AND t1.时间列 = t3.时间列
	GROUP BY t1.重复列
/**
思路：
1）先把该表进行 group by 分组，并查询出每组最大的时间列，得到一个子表。
2）再将原本的表和子表通过重复列和时间列关联起来；
这样查询出来的数据，都是以原表数据为准的，得到了时间最大的记录的所有字段信息。
3）但是如果最近的时间不止一条记录，那么就会出现重复，所以在外层还需要对原表进行group by去重。

**/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

for example:

select t1.bdid,t1.ztjdbfb,t1.bgrq from t_zbxm_bdztjd t1
 inner join (select bdid,max(bgrq) as latestDate from t_zbxm_bdztjd  group by bdid) t2
 on t1.bdid = t2.bdid and t1.bgrq = t2.latestDate
1
2
3

结果：

7、语句nvl()用法—nvl2

(1)nvl：一个空值转换函数
nvl（表达式1，表达式2）
如果表达式1为空值，nvl返回值为表达式2的值，否则返回表达式1的值。

该函数的目的是把一个空值（null）转换成一个实际的值。

其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。
对数字型： NVL（ comm,0);

对字符型 NVL( TO_CHAR(comm), ‘No Commission’)

对日期型 NVL（hiredate,’ 31-DEC-99’)

select   ename,NVL(TO_char(comm), ename||' is not a salesperson!') AS COMMISSION
from emp
1
2

(2)NVL2(表达式1，表达式2，表达式3）

如果表达式1为空，返回值为表达式3的值。如果表达式1不为空，返回值为表达式2的值

8、decode 函数基本语法

decode（字段|表达式，条件1，结果1，条件2，结果2，...，条件n，结果n，缺省值）；
--缺省值可以省略
1
2

表示如果 字段|表达式 等于 条件1 时，DECODE函数的结果返回 条件1 ,…,如果不等于任何一个条件值，则返回缺省值。
【注意】：decode 函数 ，只能在select 语句用。
例子：（1）DECODE(STATUS,‘01’,0,‘02’,0,1)，status值为01或02时返回0，否则返回1；
（2）DECODE(STATUS, 0, ‘Y’, ‘N’)，当数据中status值为0时，返回’Y’，非0时返回’N’；

//1.使用decode 判断字符串是否一样
select empno,
       decode(empno,
       7369,'smith',
       7499,'allen',
       7521,'ward',
       7566,'jones',
       'unknow') as name
from emp
where rownum<=10;
//2.使用decode 函数比较大小,
//输出两个数中的较小值：
select decode(sign(var1-var2),-1,var 1,var2) from dual
//例子
select decode(sign(100-90),-1,100,90) from dual;
//3.使用decode 函数分段
//设 工资大于等于5000为高薪，大于等于3000且小于5000为中薪，低于3000为低薪 则每个人的工资水平是...?
select e.ename ,e.sal,
       decode(sign(e.sal-5000),
       1, 'high sal',
       0, 'hign sal',
       -1,
          decode(sign(e.sal-3000),
          1, 'mid sal',
          0, 'mid sal',
          -1,'low sal'
             )
          )
        as "工资等级"
from scott.emp e;
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

decode还有其他用法，此处并不完全

9、频数统计–同一字段不同取值范围（sum\count）

mysql> select * from visitor_province_day;
+---------+-----------+-----+
| visitor | province  | day |
+---------+-----------+-----+
|       1 | 陕西省    |   5 |
|       1 | 河北省    |   2 |
|       1 | 浙江省    |   9 |
|      11 | 浙江省    |   7 |
|      11 | 江苏省    |   3 |
|      11 | 湖南省    |   1 |
|      11 | 福建省    |   4 |
|      11 | 陕西省    |   1 |
|      11 | 浙江省    |   5 |
|      11 | 广东省    |   9 |
|      11 | 陕西省    |  11 |
+---------+-----------+-----+

 //1\“SUM”写法
 SELECT SUM(IF(vpd.province = '陕西省', 1, 0)) AS cnt_sx, SUM(IF(vpd.province = '浙江省', 1, 0)) AS cnt_zj
FROM visitor_province_day AS vpd;
+--------+--------+
| cnt_sx | cnt_zj |
+--------+--------+
|      3 |      3 |
+--------+--------+
//“COUNT”写法
SELECT COUNT(vpd.province = '陕西省' OR NULL) AS cnt_sx, COUNT(vpd.province = '浙江省' OR NULL) AS cnt_zj
FROM visitor_province_day AS vpd;
+--------+--------+
| cnt_sx | cnt_zj |
+--------+--------+
|      3 |      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
25
26
27
28
29
30
31
32
33

两种用法中显示样本中两个省份的频次是一样的，都是3次。如果要进行多个匹配，把“=”改成“IN”列表的形式就可以了，如IN(‘陕西省’, ‘浙江省’)。更甚，也可以把等号替换成“LIKE”或者“REGEXP”模糊匹配。

10、not in 与not exists使用区别

使用not in需要注意：
eg：select * from kjgl_expert where fid not in(select kjgl_zjfzb.fzjid from kjgl_zjfzb where ffzid=‘4FEE97729BAC459BB779E4215C9AC4A3’);
一旦子查询结果中存在null，则该语句结果集为空，其他匹配的数据也不会查出；
改为使用：
select * from kjgl_expert e where not exists(select fzjid from kjgl_zjfzb z where ffzid=‘4FEE97729BAC459BB779E4215C9AC4A3’ and e.fid=z.fzjid);
即使子查询结果集有null,也会返回其他符合条件的结果集

//子查询如下
select kjgl_zjfzb.fzjid from kjgl_zjfzb where ffzid='4FEE97729BAC459BB779E4215C9AC4A3'
/*结果为：
FZJID
-------------------------------
BFF240F4C820470080D9BAB82EA8811B
null
1
FCD68962709A42B596065F18EAEC558D
*/
1
2
3
4
5
6
7
8
9
10

源自其他网友：sql中的in与not in,exists与not exists的区别以及性能分析
原文链接：https://blog.csdn.net/baidu_37107022/article/details/77278381
1、in和exists
in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询，一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in；

例如：表A(小表)，表B(大表)

//例如：表A(小表)，表B(大表)
select * from A where cc in(select cc from B)　　//-->效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc)　//　-->效率高，用到了B表上cc列的索引。
//相反的：
select * from B where cc in(select cc from A)　　-->效率高，用到了B表上cc列的索引

select * from B where exists(select cc from A where cc=B.cc)　　-->效率低，用到了A表上cc列的索引。
1
2
3
4
5
6
7
8

2、not in 和not exists

not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的BUG,在此点开头
正如所看到的，not in出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select 语句的执行计划，也会不同，后者使用了hash_aj，所以，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists（它会调用关联子查询）。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。如果子查询字段有非空限制，这时可以使用not in，并且可以通过提示让它用hasg_aj或merge_aj连接。

如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。

11、lag、lead分析函数使用（去掉最高分、最低分）

评分的功能，要求去掉两个最高分，去掉两个最低分，剩下的求平均分。
其他答主：oracle有对应的max，min，last，first，可惜，这回是去掉两个，而不是去掉一个，或许人家国外就没有这样的评分方法，呵呵。
row_number()，感觉麻烦，相比之下，认为这种方式最简单，借用了一下oracle的lag，lead函数。
lag：滞后；lead：领导，头，提前

//eg1
SQL> select aname,ascore from a1;

ANAME          ASCORE

---------- ----------

李四                7

李四                6

李四                6

李四                4

李四                3

李四                2

张三                8

张三                7

张三                6

张三                5

张三                4

ANAME          ASCORE

---------- ----------

张三                3

张三                2

张三                1

李四                8

李四                9

王五                3

王五                4

王五                5

王五                6

王五                7

王五              8.5

ANAME          ASCORE

---------- ----------

王五                9

王五               10
//已选择24行

select aname, avg(ascore)
     from (select lag(ascore, 2, 0) over(partition by aname order by ascore desc) q,
                  lead(ascore, 2, 0) over(partition by aname order by ascore desc) h,
                  aid,
                  aname,
                  ascore
             from a1) t
    where t.q != 0
      and t.h != 0
    group by aname;
  //  结果如下:
  ANAME      AVG(ASCORE)

---------- -----------

张三               4.5

李四              5.75

王五             6.625
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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

另一说法：Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。

在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是LAG和LEAD与left join、rightjoin等自连接相比，效率更高，SQL更简洁。下面我就对这两个函数做一个简单的介绍。

函数语法如下：

lag(exp_str,offset,defval) over(partion by ..order by …)

lead(exp_str,offset,defval) over(partion by ..order by …)
1
2
3

其中exp_str是字段名

 ---没明白说的啥意思，用函数操作看数据知道了该分析函数的用法
 Offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset 为3，则表示我们所要找的数据行就是表中的第2行（即5-3=2）。

 Defval默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag（）函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。
1
2
3
4

————————————————
lag与lead函数转载自
原文链接：https://blog.csdn.net/pelifymeng2/article/details/70313943

12、聚合函数

1. count 返回表或指定列中的行数的聚合函数,COUNT返回BIGINT数据类型

COUNT(*)

COUNT([ALL | DISTINCT [BY(col-list)]] expression [%FOREACH(col-list)] [%AFTERHAVING])
1
2
3

13、添加唯一性约束

案例

alter table mine_ghcg_ghbzba
add constraint  mine_ghcg_ghbzba_u1
unique (szs,SZXZQ,BZNF);

// 另一种写法：
drop index EXP_EXPENSE_ITEM_DESCS_U1;
create unique index EXP_EXPENSE_ITEM_DESCS_U1 on EXP_EXPENSE_ITEM_DESCS (COMPANY_ID,EXPENSE_ITEM_CODE, EXPENSE_TYPE_CODE, EXP_REPORT_TYPE_CODE)

1
2
3
4
5
6
7
8

其他：
使用ALTER TABLE语法创建唯一性约束

// 1）语法
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE (column1, column2, ... , column_n);

// 2）演示样例准备，先创建表
drop table tb_supplier;
drop table tb_products;
 
create table tb_supplier
(
  supplier_id          number not null
 ,supplier_name        varchar2(50)
 ,contact_name         varchar2(50)
);
 
create table tb_products
(
  product_id        number not null,
  product_name      number not null,
  product_type      varchar2(50),
  supplier_id       number
);

3）基于单列的唯一性约束
alter table tb_supplier
add constraint  tb_supplier_u1
unique (supplier_id);

4）基于多列的唯一性约束
alter table tb_products
add constraint  tb_products_u1
unique (product_id,product_name);
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

14、一次插入多条数据-----insert all

案例：

（1）insert all 多条数据插入同一个表，

需要注意的是，在insert all语句里不能直接使用seq_test_insert.nextval，因为即便每个into语句里都加上seq_test_insert.nextval也不会获得多个值。 比单条插入数据高效很多,如果要插入的数据量很大，建议将数据分成多个批次插入，每个批次的数据量适中，以免影响系统性能。

//例1
insert all 
into student(name,age,address) values('lily',23,'北京')
into student(name,age,address) values('Tom',32,'上海')
into student(name,age,address) values('Jim',18,'重庆')
select * from dual;
// 例2
INSERT ALL 
	INTO tb_zgqjtcy (fguid, version, ftb_zgqxx,NHID,NHDM,YHZGX,XM,ZJLX,ZJHM,LXDH,TXDZ,HKSZD,XB,NL,zt) VALUES (sys_guid(),0,null,'value1','value2','value3','value4','value5','value6','value1','value2','value3','value4','value5','value6'）
	INTO tb_zgqjtcy (fguid, version, ftb_zgqxx,NHID,NHDM,YHZGX,XM,ZJLX,ZJHM,LXDH,TXDZ,HKSZD,XB,NL,zt) VALUES (sys_guid(),0,null,'value1','value2','value3','value4','value5','value6','value1','value2','value3','value4','value5','value6'）
select 1 from dual;
1
2
3
4
5
6
7
8
9
10
11

（2）insert all往不同的表里插入数据

insert all into并不表示一个表中插入多条记录，而是表示多表插入各一条记录，而这多表可以是同一个表，就成了单表插入多条记录

//例1
insert all 
into table1(filed1,filed2)values('value1','value2')
into table2(字段1，字段2，字段3) values(值1，值2，值3)
select * from dual;  // select  1 from dual;也可以

//例2  values的值与select中的字段名对应
INSERT ALL
INTO sales (prod_id, cust_ id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date, sales_sun) 
INTO sales (prod_id, cust_ id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (prod_id, cust_ id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (prod_id, cust_ id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+3,  sales_wed)
INTO sales (prod_id, cust_ id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
INTO sales (prod_id, cust_ id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
INTO sales (prod_id, cust_ id, time_id, amount)
VAIUES (product_id, customer_id, weekly_start_date+6, sales_sat)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue,  sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_input_table;
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

(3)insert all与insert first的区别（有无条件插入），自行搜索