SQL基础与进阶

1. 最常用的SQL语法https://www.w3school.com.cn/sql/sql_syntax.asp
   1.1查询语法

SELECT 
COLUMN1,
CASE WHEN COLUMN1='' then '' when COLUMN1='' then '' else '' end,--条件判断
left(COLUMN1,2),--左截断
right(COLUMN1,2),--右截断
substr(COLUMN1,5,6)--中间截断
SUM(COALESCE(VALUE1,0)),--SUM求和&COALESCE依次参考各参数表达式，遇到非null值即停止并返回该值
COUNT(DISTINCT VALUE1)--COUNT计数&DISTINCT去重
FROM TABLE_NAME
JOIN TABLE_NAME1 ON TABLE_NAME.COLUMN1=TABLE_NAME1.COLUMN1--内关联
INNER JOIN TABLE_NAME1 ON TABLE_NAME.COLUMN1=TABLE_NAME1.COLUMN1--内关联
 LEFT JOIN TABLE_NAME1 ON TABLE_NAME.COLUMN1=TABLE_NAME1.COLUMN1--左关联
RIGHT JOIN TABLE_NAME1 ON TABLE_NAME.COLUMN1=TABLE_NAME1.COLUMN1--右关联
WHERE 
TABLE_NAME.COLUMN1 IN ('','','') 
AND COLUMN1 BETWEEN X AND XXX 
OR (TABLE_NAME.COLUMN1 NOT IN ('','','') AND COLUMN1<>'') --用OR的时候，如果后面有多个条件要带括号
GROUP BY   --分组
COLUMN1,
CASE WHEN COLUMN1='' then '' when COLUMN1='' then '' else '' end
left(COLUMN1,2),
right(COLUMN1,2),
ORDER BY  --排序（默认升序）
COLUMN1
desc--降序

2. 数据库运维，表信息查询

select * from  syscat.tables  where   TABNAME = 'XXXX'  
select * from  syscat.tables  where   TABNAME LIKE '%XXXX%'   --表
select * from  syscat.views   where   VIEWNAME = 'XXXX'
select * from  syscat.views  where  VIEWNAME like '%XXXX%'--视图
select * from  syscat.nicknames  where   TABNAME like '%XXXX%'--昵称
select * from  syscat.columns where  TABNAME='XXXX' and  TABSCHEMA='XXXX' --列
select * from  syscat.indexes where    --索引
select * from  tablespaces    --表空间  
select * from  syscat.PROCEDURES where PROCNAME LIKE '%XXXX%'  --存储过程
SELECT * FROM  SYSCAT.TABAUTH WHERE TABNAME='XXXXXX'--表权限

3. 其他

找出表中含有换行符的记录

select
*
from table_name
WHERE XX LIKE ‘%’||chr(10)||’%’

直接导出SQL查询结果

@export on;
@export set FILENAME="D:\test\test.csv";
select * from table_name where 

根据生日和一个时间算当时的年龄

TIMESTAMPDIFF(64,char(to_date(trim(Digits(AA.BIRTHDATE)),'YYYY-MM-DD')-to_date(trim(Digits(CC.DATE))