oracle数据库常用sql语句

工作中难免需要写sql查数据，有事会碰到从没碰到过的场景，需要使用某些特定的函数，记录下经常被写的sql，算是对这个项目的一点记忆保留，也是某些函数的应用。文中涉及数据仅用于交流学习，别无他意。

1、INSERT 语句
SYSDATE ：系统日期，
(SELECT MAX(DEPT_ID) FROM TM_DEPARTMENT) + 1 ：对主键id加一
SEQ_BASE.nextval ：id增长方式之一，前提是有一个叫“BASE”的序列

INSERT INTO TM_DEPARTMENT
(DEPT_ID,DEPT_DESC,CREATE_TM, VALID_DT)
VALUES
((SELECT MAX(DEPT_ID) FROM TM_DEPARTMENT) + 1,
‘新竹分部测试’, ‘YVR’,SYSDATE);
2、SELECT 语句
根据运单号bno 查询表中重复数据，平常比较少用having
SELECT t.bno,count(*) as count from tm_pcustom_szx_i t GROUP BY t.bno having COUNT(0)>1;

3、SELECT 语句
–查询当前时间前一天是数据 sysdate - 1
select o.BNO 运单编号,
o.CUS_BATCH 报关批次,
o.GDESC 托寄物内容,
o.DES_ADDR 收件地址
from tm_original_data o
where (o.GDESC like ‘%书%’ or o.GDESC like ‘%書%’ or o.GDESC like ‘%刊%’ or
o.GDESC like ‘%杂%’ or o.GDESC like ‘%读%’ or o.GDESC like ‘%报%’ or
o.GDESC like ‘%漫画%’ or o.GDESC like ‘%册%’)
and to_char(o.EXP_CUSTOMS_DATE, ‘yyyy-MM-dd’) =
to_char(sysdate - 1, ‘yyyy-MM-dd’)；

4、SELECT 语句
–分页查询 先查出50条，之后再取3-50条即截取后48条
select bno,ROWNUM RN from(
select t.bno,ROWNUM RN from tm_pcustom_szx_i t where rownum <= 50) where RN > 2;

–套用下别人总结的公式
SELECT * FROM
(
SELECT temp., ROWNUM RN
FROM (SELECT * FROM 表名) temp
WHERE ROWNUM <=end (pagepagesize)
)
WHERE RN >=start (page-1*pagesize+1)

5、SELECT 语句
–根据关键字查找表，前提是你的表要有注释
select t.table_name, t1.comments, t1.owner
from user_tables t
inner join all_tab_comments t1 on t.table_name = t1.table_name
where comments like ‘%派件%’;

6、SELECT 语句
–查找含有某些字段的表 ，查询条件区分大小写的
SELECT c.TABLE_NAME, c.COLUMN_NAME, c.DATA_LENGTH
FROM user_tables t
INNER JOIN USER_TAB_COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
WHERE c.COLUMN_NAME in (‘BNO’)
AND DATA_LENGTH < 30;

7、SELECT 语句
–使用存储过程 ，text的内容是存储过程中用到的
select distinct name from USER_SOURCE where type = ‘PROCEDURE’ and text like ‘%SYS_B%’;

8、Create 语句
–创建序列
Create sequence SEQ_cmbl_ie_store_status Increment by 1
Start with 1 Maxvalue 999999 Minvalue 1
Nocycle nocache;
–新建索引，索引的名字需要全表唯一
create index 索引名 on 表名（字段名）;
drop index 索引名; --删除索引

9、ALTER 语句
ALTER TABLE TM_CBE_GOODS_RECORD CHANGE MONTHLY_CARD CUSTOMER_ACCT_CODE VARCHAR(50); --修改列名

ALTER TABLE TM_CBE_GOODS_RECORD RENAME COLUMN MONTHLY_CARD TO CUSTOMER_ACCT_CODE; --修改列名

alter table tm_original_data modify GOODS_SPECIFICATION varchar2(3000);–修改字段长度

ALTER TABLE TM_REPORTASSISDATA_QUEUE RENAME TO TM_REPORTASSISDATA_QUEUE_BAK;–更改表名

–修改表注释
comment on table TM_PCUSTOM_PEN_I is ‘马来西亚槟城口岸进口口岸报关资料表’;
–字段注释修改语法：comment on column 表名.字段名 is ‘注释内容’

10、SELECT 语句
NVL函数的格式如下：NVL(expr1,expr2)
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数的值。
to_char、 TO_DATE转换时间格式

select t.MONTHLY_CARD as CUSTOMER_ACCT_CODE,
t.GOODS_CODE,
t.GOODS_REGISTRATION_NO,
t.UPDATE_BY as MODIFY_EMP,
to_char(t.SUBMIT_TIME, ‘YYYY-MM-DD HH24:MI:SS’) AS LSUBMITTIME,
to_char(nvl(t.CREATE_TIME,Sysdate), ‘yyyy-mm-dd HH:mi:ss’) as CREATE_TIME
from tm_cbe_goods_record t
where t.CUSDEPT like ‘%XMN%’
and t.customs_date BETWEEN
TO_DATE(‘2014-01-01 00:00:00’, ‘yyyy-mm-dd hh24:mi:ss’) AND
TO_DATE(‘2018-09-30 23:59:59’, ‘yyyy-mm-dd hh24:mi:ss’)
and t.EXP_CUSTOMS_DATE BETWEEN TO_DATE(‘2018/11/06’, ‘yyyy/mm/dd’) AND
TO_DATE(‘2018/11/9’, ‘yyyy/mm/dd’);

11、SELECT 语句
–得到每个月的前3条数据，根据报关日期进行分组
SELECT *
FROM (SELECT T.*,
RANK() OVER(PARTITION BY to_char(t.CUSTOMS_DATE, ‘yyyy-mm’) ORDER BY T.ID) RK
FROM tm_pcustom_szx_e T
WHERE t.CUSTOMS_DATE BETWEEN TO_DATE('2014-07-01 ', ‘yyyy-mm-dd’) AND
TO_DATE(‘2015-08-01’, ‘yyyy-mm-dd’))
WHERE RK <= 3;

12、SELECT 语句
minus 不包含…
–两张表相同字段对比，得到左边表存在，右边表不存在的去重数据
select bno from tm_pcustom_szx_e minus select bno from tm_custom_szx_e;

–case when的使用，简单举例
select t.BNO as 运单编号,
t.START_CITY_CODE as 始发城市代码,
t.CFSIGN as 是否转口岸,
case
when trim(t.flightno) is null then
‘N’
ELSE
‘Y’
END 是否发海关
from tm_pcustom_summary1 t;

13、SELECT 语句
–多品名分行
select o.cus_batch 报关批次,
s.strgdesc 托寄物内容,
s.AWEIGHT 实际重量,
s.strquantity 数量单位,
s.SRCCOUNTRY 寄件国别地区代码
from (select t.*,
regexp_substr(t.gdesc, ‘[^,]+’, 1, n) strgdesc,
regexp_substr(t.quantity, ‘[^;]+’, 1, n) strquantity
from tm_pcustom_summary1 t,
(select level n from dual connect by level <= 10)
where regexp_substr(t.gdesc, ‘[^,]+’, 1, n) is not null
and t.cus_dept_code in (‘NGG’, ‘EAN’, ‘PVX’, ‘HGX’, ‘HGS’)
and t.ieflag = ‘I’
and t.GATE_TYPE = ‘F’
and t.customs_date between date ‘2019-01-01’ and date
‘2019-06-01’) s
left join tm_original_data o on s.bno = o.bno order by o.bno desc;

—测试多品名显示多行用例
select t.bno,t.cusvalue,
regexp_substr(t.gdesc, ‘[^,]+’, 1, n) strgdesc,
regexp_substr(t.quantity, ‘[^;]+’, 1, n) strquantity
from tm_pcustom_summary t,
(select level n from dual connect by level <= 10)
where regexp_substr(t.gdesc, ‘[^,]+’, 1, n) is not null;

14、SELECT 语句
–查询出三列 ，常规的查询没什么好说的，主要为了和下面的做对比
SELECT T.DISTRICTDEPTCODE, T.DIVISIONDEPTCODE, T.POINTDEPTCODE
FROM TS_AREA_MANAGE T
WHERE T.DISTRICTDEPTNAME LIKE ‘%东莞区%’;
–查询出一列 ，号拼接，和上面的行数一致，列数减少 || ‘,’ ||
SELECT DISTINCT T.DISTRICTDEPTCODE || ‘,’ || T.DIVISIONDEPTCODE || ‘,’ ||
T.POINTDEPTCODE AS A
FROM TS_AREA_MANAGE T
WHERE T.DISTRICTDEPTNAME LIKE ‘%东莞区%’;

–查询出三列到一列，列数减少的同时行数增多的拼接 UNION
SELECT DISTINCT T.DISTRICTDEPTCODE
FROM TS_AREA_MANAGE T
WHERE T.DISTRICTDEPTNAME LIKE ‘%东莞区%’
UNION
SELECT DISTINCT T.DIVISIONDEPTCODE
FROM TS_AREA_MANAGE T
WHERE T.DISTRICTDEPTNAME LIKE ‘%东莞区%’
UNION
SELECT DISTINCT T.POINTDEPTCODE
FROM TS_AREA_MANAGE T
WHERE T.DISTRICTDEPTNAME LIKE ‘%东莞区%’;