-- 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', ',', ''))
;
笔记:常用sql查询
最新推荐文章于 2023-10-25 17:58:10 发布