准备: 创建一个员工表和一个部门表

dept.txt emp.txt


create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

create external table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';


load data local inpath '/opt/module/datas/dept.txt' overwrite into table default.dept;

load data local inpath '/opt/module/datas/emp.txt' overwrite into table default.emp;

6.1 基本查询

6.1.1 全表查询和特定列查询

select * from emp;
select empno, ename from emp;

6.1.2 列别名

select ename as name, deptno dn from emp;

这里的 name 就是 ename这一列的别名. as 可以省略


6.1.3 算术运算

查出员工的薪水后, 加 100 显示

select sal+100 from emp;

6.1.4 常用函数

  1. 求总行数(count
     select count(*) cnt from emp;
    
  2. 求工资的最大值(max
     select max(sal) max_sal from emp;
    
  3. 求工资的最小值(min
     select min(sal) min_sal from emp;
    
  4. 求工资的总和(sum
     select sum(sal) sum_sal from emp;
    
  5. 求工资的平均值(avg
     select avg(sal) avg_sal from emp;
    

6.1.5 limit 语句

通常的查询语句会把所有的查询到的结果返回.

limit 可以限制显示的行数

select * from emp limit 5;

Copyright © 尚硅谷大数据 2019 all right reserved,powered by Gitbook
该文件最后修订时间: 2018-12-11 22:01:21

results matching ""

    No results matching ""