样例数据输出:
select * from 某表 limit 1; 将查询结果选中然后按制表符,得到竖着的样例数据格式。
星环(hive sql)添加表名注释和字段注释:
alter table sjh_cdhouse.tablename SET TBLPROPERTIES('comment' = '租住信息');
alter table sjh_cdhouse.tablename change column jaa001 jaa001 string comment '公民身份号码';
查看数据库表的创建时间:
SELECT CREATED,LAST_DDL_TIME FROM USER_OBJECTS WHERE OBJECT_NAME='DWM_COORDINATE_RESIDENTIAL' 表名要大写
插入时间列:
varchar格式:update dwm_coordinate set GXSJ = to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
计算实际年龄:
select csrq,TIMESTAMPDIFF(YEAR, csrq, '20220901') age from 表名;
查询表名及字段名:
select t.table_name,f.comments,t.column_name, t.comments from user_col_comments t inner join user_tab_comments f on t.table_name = f.table_name where t.table_name like 'DWD_TEAC_%'
模糊匹配查询:
SELECT T2.列名,T1.列名 FROM 主表 T1, 匹配表 T2 WHERE T1.匹配列 LIKE CONCAT('%',concat(T2.匹配列,'%'));
分列的一种sql写法:
SELECT A,COUNT(A) FROM (select CASEWHEN (xb='2' and AGE='55') OR(xb='1' and AGE='60')OR AGE='65' THEN '2023年退休'WHEN (xb='2' and AGE='54') OR(xb='1' and AGE='59')OR AGE='64' THEN '2024年退休'WHEN (xb='2' and AGE='53') OR(xb='1' and AGE='58')OR AGE='63' THEN '2025年退休' END Afrom (select floor((SYSDATE - to_date(SR,'yyyy-mm-dd'))/365) AS age,xb from 表名 WHERE GZZT=1 OR GZZT=-1 )T)T1
WHERE A IS NOT NULL group by a order by a
查询结果建表:
--执行前 sql drop table ycrx_demp
--执行后 sql create table ycrx_demp as select ''''''''''''''''''''''
查询一列插入表中:
update 表名 a set a.xqmc=''''''''''''''''''''''
查询某数据库下的所有表名及表名注释(hivesql):
selelct table_name,commentsring from system.TABLE_v where database_name = '数据库名';
查询获取某个数据库下的所有表名,表名注释,字段名,字段注释(hivesql):
select database_name,table_name,commentstring,zdm,zdzs from (
select * from system.TABLES_V a left join (select table_name as bm, column_name as zdm,
commentstring as zdzs from system.columns_v) b on a.table_name=b.bm)
where database_name='数据库名字'
关于索引操作:
建索引 create global index global_index_gmsfhm on 数据库名.表名(gmsfhm(100))
删除索引 drop index if exists global_index_gmsfhm on 数据库名.表名
trim(字段名) 去除字段字符串内容前后的空格
基于sql server的时间加法:
DATEADD(HOUR, 8, u_create_date) // 返回当前时间 + 8 小时