hbase语句汇总、sql sever语句汇总、mysql语句汇总

hbase

hbase客户端：dbvis-multi.exe
用db时不会写Hbase语句，有些语句怕自己忘记，记录在下面：

• 查某个字段值取特定值时的记录

select * from "table" where "time"='201709'

• 统计频数

select "aa",count("aa") from "table" group by "aa"

• 查找时间在某个范围的记录

select * from "table" where "time" between '20170101' and '20171220'
select * from "table" where "time" >= '20171101' and "time" <= '20171220'

• 插入一条语句

UPSERT INTO "table" ("pk", "nnnn") VALUES ('aaaa','hh');

• 查找字数大于等于200的记录

select "content","time" from "table" where length("content")>=200

• 字段里含有“哈哈哈”、name在某个范围内的十个记录

select * from "table" where "content" like '%哈哈哈%' and "name" in ('zzz','yyy') limit 10

• 插入一条记录

INSERT INTO table (a,b) VALUES (null, 1);

• 更新一条记录

update "table" set "type" = '7' where "type" = 'h'

• 分组后降序排

select "aa"  from "table" order by "aa" desc

• 表的复制

create table a_copy like a 
insert into a_copy select * from a

• 删除记录

delete from a_copy where class = 'hhh'

• 做实验时需要临时从其他表里导入数据造一张新表，可以这样：

#在xshell里进入hbase主节点的环境
 hbase shell
 #在shell里清空表: truncate “table1”
 truncate "table1"
 #在db里执行插入数据的语句(把table2里的前100行插入table1) 
 upsert into "table1" select * from "table2" limit 100

sql sever

• 删除表

drop TABLE 哈哈哈

mysql

• 查找当月的记录

select * from table where to_char(sysdate,'yyyy-mm')=to_char(hh,'yyyy-mm')

• 查找某一年某一季度的记录

select * from table where to_char(hh,'yyyy')=2016 and to_char(hh,'mm') in (1,2,3)

• 降序排

select * from table where yearNo=2017 order by updateTime desc

spark的dataframe写入mysql时,比如df有3列（q,w,e)，但mysql表有四列（q,w,e,r)，我们只要给mysql的表字段设置默认值就行了，如Null.–我自己的话。

• 查询指定日期的数据

select * from table where TO_DAYS(datetime)=TO_DAYS('2021-01-14')

• 查看sql执行情况

show processlist ;
show  full processlist ;

• 取出表A里前20W数据插入新表B

INSERT INTO B
SELECT * FROM A limit 200000;

• 如果表存在：

insert into tab1 select * from tab2

• 如果表不存在

create table tab1 as select * from tab2

• 用111替代字段B中的空值
  create table A
  select a,b,c,IFNULL(d,‘111’) as d from B;