mysql各种语句

1.mysql查询数据库内存：use  information_schema；select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='bs_data';

data_length ：存储数据大小
data_length/1024/1024：将字节转换为MB
round(sum(data_length/1024/1024),2)：取两位小数
concat(round(sum(data_length/1024/1024),2),'MB') ：给计算结果追加单位 “MB”
 

2.unix_timestamp()-unix_timestamp(rukutime)>259200   超过入库时间3天以上的条件

3.substring,left,right字符串截取函数

4.根据aqlbdm字段进行分类统计

select count(*) as num,

        case aqlbdm

         when '14' then '侵权'

         when '15' then '侵权'，

         when '16' then '求助'，

         when '17' then '求助'，

         else '其他'

         end as 'type' 

   from 'bsfj_sjd_di' when scbjsj between '2019-06-02' and '2019-07-02'  group by type

5.select ta.cbdw,ta.asjlb,tb.name,ta.asjlb_mc,count(id) total from dwd_frn_case_info ta left join dwd_type_pcs tb on ta.cbdw=tb.bm where tb.name is not null and ta.asjlb  in ('4158001','4157801','4157110') group by ta.cbdw,ta.asjlb_mc

6.load data [low_priority] [local] infile

  如果你指定关键词low_priority，那么MySQL将会等到没有其他人读这个表的时候，才把数据插入

  如果指定local关键词，则表明从客户主机读文件。如果local没指定，文件必须位于服务器上

    load data  low_priority infile "/home/mark/data sql" into table Orders

 参考：https://www.cnblogs.com/weiyiming007/p/8125432.html

 (apache hive中 load data inpath......)

 

创建分桶表：

create table table_name( 
column1 varchar2(20),
column2 varchar2(400),
column3 varchar2(100),
column4 varchar2(400),
column5 varchar2(8),
column6 string,
column7 varchar2(400),
column8 varchar2(20),
column9 varchar2(6),
column10 varchar2(400),
column95 timestamp)
clustered by (column1) into 59 buckets stored as orc tblproperties("transactional"="true");