select substr(brand,1,6)
2、cast函数
cast(expr AS type), 类型转换函数, 比如将number转成string, 或相反.
select cast(length as int) len
3、max,min,avg函数:length字段是字符串类型
select max(cast(length as int)) len
select min(cast(length as int)) len
select avg(cast(length as int)) len
4、截取数值,四舍五入
select dround() result;
select dround() result;
取整
select dround() result;
5、删除所有小数点以后的数或删除N位小数
select truncate();
select truncate(6,1)
6、返回表达式列表中的最大值:greatest
select greatest(5,16,2) as greatest;
7、返回表达式列表中的最小值: least
select least(5,16,2) as least;
8、like 模糊查询
select count(*) from dw_bill_his where city='北京' and broadcastdate like '2018/03%';
9、字符串截取,substr(str,startindex,length) startindex从1开始
select substr('2018-08-20',1,4) year1;
10、字符串连接 concat(string a,string b…)
11、字符串长度 length(string a)
select length('world') as len;
12、给表增加一列:
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
比如给表dw_bill增加一个float类型的week列
ALTER TABLE dw_bill ADD COLUMNS(week FLOAT);
13、删除一列
ALTER TABLE name DROP [COLUMN] column_name
比如删除dw_bill的week列表
ALTER TABLE dw_bill DROP week;
14、字符串去空格
去左空格: select ltrim(' hello ');
去右空格: select rtrim(' hello ');
去左右空格: select trim(' hello ');
15、查询某个字段为null的记录条数
select count(1) from dw_bill where brand is null;
不为null的记录条数
select count(1) from dw_bill where brand is not null;