2018-07-17

作者: b470b9fc7145 | 来源:发表于2018-07-17 15:02 被阅读12次

2018-07-18
2018-07-17
3.1 Types of Learning - Learning
3.2 Types of Learning -Learning
📖2018-07-17 江城笔记17
UltraEdit破解安装
成长不过是一次次的与自己对话（一）
Spring Boot异常处理方案选择
Excel-VBA
2.0天使班班级第1次班会记录

sql笔记1


-- 查询数据库下面所有的表
sel top 10 * FROM DBC.Tables where tablename like 'ARQP_P%';

-- 查询某一个表
sel * from ARQP_data_D.ARQP_P_BGD_QP2_NEW_CUS ;

-- 查询所有的用户有重名的
 sel DOC_NBR from ARQP_data_D.ARQP_P_BGD_QP2_NEW_CUS  group by DOC_NBR having count(DOC_NBR) > 1;
 
 -- 查询所有用户重名的,可以偷懒使用count (1)
  sel DOC_NBR from ARQP_data_D.ARQP_P_BGD_QP2_NEW_CUS  group by DOC_NBR having count(1) > 1;


-- 查询T83 数据

sel top 10 * From DBC.Tables where TableName like '%_DEL';

-- 查询某一个表

-- 查询表和视图
sel top 10 * from DBC.Tables where TableName like '%ARTB_A_STS_QP_F3EACDTAP_DEL%';
-- 只是查询表  tableKind  只是一个表的意思
sel top 10 * From DBC.Tables where TableKind = 'T' and TableName like   '%ARTB_A_STS_QP_F3EACDTAP_DEL%';

select * from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL;   


-- oracle/TD 不能批量插入数据,需要一条一条
insert INTO  ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL values  ('10000','cust_1000',current_date);
insert INTO  ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL values  ('10001','cust_1001',current_date);
insert INTO  ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL values  ('10002','cust_1002',current_date);

show table  ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL;


-- 查询td的一些其他用法

select * from DBC.Tables where TableKind = 'T' and TableName like '%doc%';

-- chaxun shijaincuo 


select current_timestamp;

----
-- 时间加减处理,一定要全部加上'${}'
-- 比较也要加上${}
-- 日期可以进行比较
----

select cast((SELECT cast('${v_dt}' as date format 'YYYY-MM-DD') - INTERVAL '1' DAY) as date format 'YYYY-MM-DD') as v_dt; 
select cast('${v_end_dt}' as date format 'YYYY-MM-DD') as v_end_dt;
select (cast('${v_dt}' as date format 'YYYY-MM-DD') - cast('${v_end_dt}' as date format 'YYYY-MM-DD')) as gap; 
.if gap > 0  then .goto LB_LOOPSTR;

--

sql笔记2

-- 基本数据类型

date 
decimal
numeric
byteint
smallint
integer
real

-- help 

help table ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL;
-- show
show table ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL;


-- 幂预算
select 6**2;

-- 取模
select 9 mod 2;

-- concat
select 'cbw:'||'hello world';


help database dbc;
-- 没有权限去看view
show view dbc.userdb;

select * from dbc.userdb;
-- 查看所有的列
help column ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL.*;


-- 查看sql 命令怎么用
help 'sql insert';

-- 使用between and 
-- 注意不要留下空格
select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT     >=  'cust_1000000652' and a.EACOWNCLT <= 'cust_1000000662' order by a. EACOWNCLT desc;
explain select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT    between  'cust_1000000652' and  'cust_1000000662' order by a. EACOWNCLT desc;

-- 使用like
-- '_' 是用来占位置的
select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT  like 'cust_1000%';
explain select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT  like 'cust_1000%';

-- 使用 _ 
select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT like '_ust%' ;
-- 使用站位字符
select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT like '__st%' ;

-- 不进行转义
select a.* from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL as a where a.EACOWNCLT like 'cust<escpe char>_%' ;


--- 数学运算
-- 取模
sel 9 mod 2;
-- 取幂
sel 8 ** 2;
-- 开方
sel SQRT(9);



-- 系统变量

sel DATe;
sel time;
sel user;
sel DATABASE;

-- 数据抽取操作
-- 抽取月份,在日期基础上+30(比如现在2018-07-09 + 30 = 2018-08-08)
sel EXTRACT(month from date + 30);--基准是天(day)
sel EXTRACT(hour from time);
sel EXTRACT(second from time + 30);-- 基准是秒(second)

-- 减去一个月
sel ADD_MONTHS(date,-1);--2018-07-09--> 2018-06-09
-- 加上一个月
sel ADD_MONTHS(date , 1);--2018-07-09--> 208-08-09

-- 日期减去1天
select cast((SELECT cast('${v_dt}' as date format 'YYYY-MM-DD') - INTERVAL '1' DAY) as date format 'YYYY-MM-DD') as v_dt; 

--coalesce

sel COALESCE(null,1,null,2);--返回1
sel coalesce(null,null,1,2);--返回1

-- union 和 union all  之间的差别
-- uninon 会使用distinct 去重

select * from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL  as a where a.EACOWNCLT   between 'cust_1000000647' and 'cust_1000000650' union   
select * from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL  as a where a.EACOWNCLT   between 'cust_1000000647' and 'cust_1000000655' ;    

-- uninon all 返回所有的结果,不会去重复

select * from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL  as a where a.EACOWNCLT   between 'cust_1000000647' and 'cust_1000000650' 
union   all
select * from ARTB_DATA_D.ARTB_A_STS_QP_F3EACDTAP_DEL  as a where a.EACOWNCLT   between 'cust_1000000647' and 'cust_1000000655' ;

case when then

sel 

CASE 'ds'
         WHEN '1' THEN '男'
         WHEN '2' THEN '女'
ELSE '其他' END

-- 输出:其他

比较字符串


sel '01'='02';-- 在sql 中可能会失效,建议使用<>,如判断每个月01  号

--用来做比较的参数不能使用${v_trx_dt},先定义一个变量,然后变量来比较

/** 语句 12 功能描述：判断是否执行完毕。 **/

select ${v_MAX_TASK_SEQ} - ${v_CUR_TASK_SEQ} as LOOP_CNT  -- 临时定义一个变量
.if LOOP_CNT <> 0  then .goto LB_LOOPSTR;
.if ERRORCODE <> 0 then .quit 12;