1. 字符集问题
当你新建一个数据库,然后创建数据表,然后插入数据的时候,悲剧了,写入一条语句,报错了:“本地数据库配置错误,sql错误码1366”,好吧,字符集编码出现问题了,这时候可以查询字段的字符集:
show full columns from table_name;
从上图可知,在name字段插入中文时其最大长度超出了latin数据库字段,因此就会报错;
解决方法如下:
(1) 修改数据库的字符集编码
mysql数据库的配置文件my.ini,此文件放在mysql根目录下。在此文件下查找default-character-set属性,并将其值更改为utf8:default-character-set = utf8
(2)修改表格的编码
ALTER TABLE account.t_student convert to character set gbk_chinese_ci collate latin1_swedish_ci;
2. 使用sql查询,满足一些日常需求
2.1 查询每个学校年纪最小的两名学生
select *, row_number() over(partition by school order by age asc) rownum
from t_student
where rownum<3;
HIVE脚本中支持使用row_number over(partition by field1 order by field2 asc) rn根据字段1分组,在分组内根据字段2排序,然后赋予每一行数据一个行编号,通过 row_number = 1 就可以获得分组内的第一行的数字了。
2.2 查询每个学校男女生的人数
select school,
sum(case when sex = 1 then 1 else 0 end ) as maleNum,
sum(case when sex = 0 then 1 else 0 end ) as femaleNum
from demo.t_student
group by school;
select school,
sum(case when sex = 1 then 1 else 0 end ) as maleNum,
sum(case when sex = 0 then 1 else 0 end ) as femaleNum,
sum(case when sex = 1 then 1 else 0 end )/count(distinct id) boyPercentage
from demo.t_student
group by school;