2.3 阿里云ODPS常用SQL操作

1.SQL查询语句关键字

SQL语句查询时，可使用的关键字：

%    &    &&    (    )    *    +  
 -    .    /    ;    <    <=    <>  
 =    >    >=    ?    ADD    ALL    ALTER  
 AND  AS    ASC    BETWEEN    BIGINT    BOOLEAN    BY  
 CASE CAST  COLUMN    COMMENT    CREATE    DESC    DISTINCT  
 DISTRIBUTE    DOUBLE    DROP    ELSE    FALSE    FROM    FULL  
 GROUP    IF    IN    INSERT    INTO    IS    JOIN  
 LEFT    LIFECYCLE    LIKE    LIMIT    MAPJOIN    NOT    NULL  
 ON    OR    ORDER    OUTER    OVERWRITE    PARTITION    RENAME  
 REPLACE    RIGHT    RLIKE    SELECT    SORT    STRING    TABLE  
 THEN    TOUCH    TRUE    UNION    VIEW    WHEN    WHERE

2.Select

命令格式如下：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY order_condition]
[DISTRIBUTE BY distribute_condition [SORT BY sort_condition] ]
[LIMIT number]

注意！！！

1. select分区表时禁止全表扫描。
   若表格存在分区时，在查询表格时必须指定分区，如：

select * from tablename where partitionname = "xxxx"

2. 若实在需要对分区表进行全表扫描，可以在对分区表全表扫描的SQL语句前加一个set语句，并和SQL语句一起提交执行。

set odps.sql.allow.fullscan=true;

如果需要整个项目都允许全表扫描，可以通过开关自行打开或关闭（true/false），命令如下：

setproject odps.sql.allow.fullscan=true;

3.子查询

查询的对象是另外一个Select操作，如：

select * from (select shop_name from sale_detail) a;

4.UNION ALL/UNION [DISTINCT]

UNION ALL：将两个或多个Select操作返回的数据集联合成一个数据集，如果结果有重复行时，会返回所有符合条件的行，不进行重复行的去重处理。
UNION [DISTINCT]：其中DISTINCT可忽略。将两个或多个Select操作返回的数据集联合成一个数据集，如果结果有重复行时，将进行重复行的去重处理。
UNION ALL示例如下：

select * from sale_detail where region = 'hangzhou'
        union all
select * from sale_detail where region = 'shanghai';

UNION示例如下：

SELECT * FROM src1 UNION SELECT * FROM src2; 

--执行的效果相当于

SELECT DISTINCT * FROM (SELECT * FROM src1 UNION ALL SELECT * FROM src2) t;

5.JION

left join：左连接，会从左表（shop）中返回所有的记录，即使在右表（sale_detail）中没有匹配的行。

select a.shop_name as ashop, b.shop_name as bshop from shop a
        left outer join sale_detail b on a.shop_name=b.shop_name;
    -- 由于表shop及sale_detail中都有shop_name列，因此需要在select子句中使用别名进行区分。

right outer join：右连接，返回右表中的所有记录，即使在左表中没有记录与它匹配。
示例如下：

select a.shop_name as ashop, b.shop_name as bshop from shop a
        right outer join sale_detail b on a.shop_name=b.shop_name;

full outer join：全连接，返回左右表中的所有记录。
示例如下：

select a.shop_name as ashop, b.shop_name as bshop from shop a
        full outer join sale_detail b on a.shop_name=b.shop_name;

inner join：在表中存在至少一个匹配时，inner join返回行。关键字inner可省略。

select a.shop_name from shop a inner join sale_detail b on a.shop_name=b.shop_name;
select a.shop_name from shop a join sale_detail b on a.shop_name=b.shop_name;

6.更新表中的数据（INSERT OVERWRITE/INTO）

命令格式如下：

INSERT OVERWRITE|INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [(col1,col2 ...)]
select_statement
FROM from_statement;

注意！！！

1. 向某个分区插入数据时，分区列不允许出现在select列表中。
2. partition的值只能是常量，不可以出现表达式。