样例数据输出:
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 fusion_t_ga_rjbxx_baoshanqu;
查询表名及字段名:
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,xbfrom dwd_teac_spzx_sp_ryxx 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 dwm_t_ga_rjbxx_baoshanqu a set a.xqmc=''''''''''''''''''''''