droptableifexists demo01;createtable demo01(
eno int(10),
ename varchar(20)
);
插入数据
insertinto demo01 values(1,"hello");
增加字段
altertable demo01 add loc varchar(20);
修改字段的值
update demo01 set loc="world";
删除某行记录
delet from demo01 where loc="world";
删除字段
altertable demo01 dropcolumn loc;
查看表结构
desc demo01;
查询
# 查询员工经理
select e.ename as emp,m.ename as mgr from emp e join emp m on e.mgr=m.empno;
# 查询部门中高于平均工资员工
select ename,sal from emp a where sal > (
selectavg(sal) from emp b where a.deptno=b.deptno);
2. HiveQL
数据库相关
create database mydb;
use mydb;
show tables;
创建表
createexternaltable demo01(
eno int,ename string)
row format delimited
fields terminated by','
collection items terminated by'.'
map keys terminated by':'
lines terminated by'\n'
partitioned by (city string)
;
载入数据
load data inpath ‘/demo01.txt’ overwrite into table demo01;
load data local inpath ‘/home/hadoop/demo01.txt’ overwrite into table demo01;
查询wordcount
createexternaltable docs(line string);load data inpath '/word.txt' overwrite intotable docs;
select word,count(1) as num from (
select explode(split(line,'\\s+')) as word from docs) as x
groupby word
orderby num desc
;