笔记：常用sql查询

-- oracle查看表格字段
select * from user_tab_columns where 1=1 
and table_name= 'MY_TABLE_NAME'
and column_name like '%_JE%'
;
-- oracle查看字段注释
select * from user_col_comments where 1=1 
and table_name= 'MY_TABLE_NAME'
and column_name like '%E'
;
-- oracle查看表格（owner=xxx）
select OWNER, table_name, tablespace_name from all_tables 
WHERE 1=1 AND OWNER = 'MY_DATABASE'
  AND table_namE LIKE '%TBL_NAME'
ORDER BY table_name
;
-- oracle查看表格字段结构（owner=xxx）
select * from all_tab_columns 
WHERE 1=1 AND OWNER = 'MY_DATABASE'
  AND table_name = 'MY_TABLE_NAME'
ORDER BY table_name, COLUMN_ID
;
-- oracle创建索引 
create index 索引名 on 表名(列名);
-- oracle添加注释
   COMMENT ON COLUMN "MY_TABLE_NAME"."PLAN_YEAR" IS '计划年度';
-- oracle查看字段注释（owner=xxx）
SELECT * FROM all_col_comments
WHERE 1=1 AND OWNER = 'MY_DATABASE'
 and column_name like '%DICT%'
;
-- ORACLE 查询字段结构和注释
select c.table_name, c.column_name as columnName,  
  data_type as dataType, (CASE WHEN data_type LIKE 'NUMBER' THEN c.data_precision ELSE c.char_length END) charLength, c.char_used charUsed, c.data_precision dataPrecision, c.data_scale dataScale, 
  decode(nullable,'Y', 1, 0) as isNullable, c.DATA_DEFAULT as columnDefault, com.comments as columnComment 
from sys.Dba_tab_Columns c, sys.Dba_col_comments com 
where c.owner = 'MY_DATABASE' and c.table_name = 'MY_TABLE_NAME'   
and c.table_name = com.table_name and c.owner = com.owner and c.column_name = com.column_name 
order by c.table_name, column_id
;
-- 或者：
-- ORACLE 查询字段结构和注释
select c.table_name, c.column_name ||
  ' '||data_type || '(' || (CASE WHEN data_type LIKE 'NUMBER' THEN c.data_precision ELSE c.char_length END) || ')' ||
  decode(nullable,'Y', '', ' not null ') ||
--  c.DATA_DEFAULT as columnDefault, 
  (case when com.comments is null then '' else ' comment "'||com.comments||'"' end) || ',' as columnComment,  
  c.char_used charUsed, c.data_precision dataPrecision, c.data_scale dataScale
from all_tab_columns c, all_col_comments com 
where c.owner = 'MY_DATABASE' and c.table_name = 'MY_TABLE_NAME'   
and c.table_name = com.table_name and c.owner = com.owner and c.column_name = com.column_name 
order by c.table_name, column_id
;
-- ORACLE 转mysql（字段结构和注释）
select c.table_name, c.column_name, c.column_name ||
  ' '|| (
      CASE WHEN data_type LIKE 'DATE' THEN 'DATETIME' 
      WHEN data_type LIKE 'NVARCHAR2' THEN 'VARCHAR' 
      WHEN data_type LIKE 'NUMBER' THEN (CASE WHEN c.data_scale > 0 THEN 'DECIMAL' 
        WHEN c.data_precision < 2 THEN 'TINYINT' 
        WHEN c.data_precision < 6 THEN 'SMALLINT' 
        WHEN c.data_precision < 12 THEN 'INT' 
        ELSE 'BIGINT' END) 
    else data_type end
  ) || (
      CASE WHEN data_type LIKE 'DATE' THEN '' 
      WHEN data_type LIKE 'NUMBER' THEN (CASE WHEN c.data_scale > 0 THEN ('(' || to_char(c.data_precision + c.data_scale)||','||to_char(c.data_scale) || ')') 
        WHEN c.data_precision > 11 THEN '(19)' 
        ELSE ('(' || to_char(c.data_precision)||')') END)
      ELSE ('(' || to_char(c.char_length) || ')') END
  ) ||
  decode(nullable,'Y', '', ' not null ') ||
--  c.DATA_DEFAULT as columnDefault, 
  (case when com.comments is null then '' else ' comment "'||com.comments||'"' end) || ',' as columnComment,  
  c.char_used charUsed, c.data_precision dataPrecision, c.data_scale dataScale
from all_tab_columns c, all_col_comments com 
where c.owner = 'MY_DATABASE' and c.table_name in ('MY_TABLE_NAME')   
and c.table_name = com.table_name and c.owner = com.owner and c.column_name = com.column_name  
order by c.table_name, column_id
;
-- ORACLE 查询表格主键
select COLUMN_NAME as columnName FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` 
 where table_schema='MY_DATABASE' and table_name='MY_TABLE_NAME' AND constraint_name='PRIMARY' 
;
-- 或者：
select a.constraint_name,  a.column_name 
from user_cons_columns a, user_constraints b 
where a.constraint_name = b.constraint_name 
and b.constraint_type = 'P' and a.table_name = 'MY_TABLE_NAME'
;

-- MYSQL
-- 查看表格的字段
select * from information_schema.COLUMNS where table_name = '表名' and table_schema = '库名';
-- 查看表格索引
SHOW index FROM 表名;
-- 根据查询结果生成临时表（不会复制主键） --------------------
CREATE TABLE temp_MY_TABLE_NAME 
SELECT * FROM MY_TABLE_NAME WHERE 1=1
;
-- 根据查询结果生成临时表（会复制主键） --------------------
CREATE TABLE temp_MY_TABLE_NAME LIKE MY_TABLE_NAME;   
INSERT INTO temp_MY_TABLE_NAME SELECT * FROM MY_TABLE_NAME;
-- 重命名表格 --------------------
rename table MY_TABLE_NAME to MY_TABLE_NAME_bak; 
-- 重命名索引（v5.7及以上） --------------------
ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name;

-- MYSQL 查看表格字段的结构
select COLUMN_NAME as columnName, COLUMN_TYPE as columnType, DATA_TYPE as dataType, 
  (case when IS_NULLABLE = 'YES' then 1 else 0 end) isNullable, 
  COLUMN_DEFAULT as columnDefault, COLUMN_COMMENT as columnComment 
  from INFORMATION_SCHEMA.Columns where table_schema= 'MY_DATABASE' and table_name= 'MY_TABLE_NAME' 
ORDER BY ordinal_position ASC
;
-- MYSQL 查看数据库的所有表格和注释
select table_name, table_comment, create_time
from INFORMATION_SCHEMA.Tables 
  where table_schema= 'MY_DATABASE'
  ;

-- mysql转时间戳
select unix_timestamp()*1000 当前时间戳, FROM_UNIXTIME(unix_timestamp(), '%Y-%m-%d %H:%i:%s') 当前时间 from dual;

-- oracle转时间戳
select to_char(DATE, 'YYYY-MM-DD') 时间A, to_char(1686416185736/(1000*24*60*60) + to_date('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD') 时间B from dual;

-- mysql分组后取第一个字符串
select type_name, substring_index(group_concat(issue_time order by modify_time desc), ',', 1) issue_time
from my_table 
group by type_name;

-- mysql字段分割行转列
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d', ',', help_topic_id + 1), ',' , -1) AS col 
FROM mysql.help_topic
where help_topic_id <= length('a,b,c,d') - length(replace('a,b,c,d', ',', ''))
;