SQL语句

CREATE DATABASE [IF NOT EXISTS] db_name;

新建数据库 db_test
CREATE DATABASE db_test;

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [database.]table_name
 (column_definition[, column_definition, ...]) 
 [ENGINE = [olap|mysql|broker]]
 [key_desc]   
 [partition_desc]    
 [distribution_desc]    
 [PROPERTIES ("key"="value", ...)]
 [BROKER PROPERTIES ("key"="value", ...)];

col_name col_type [agg_type] [NULL | NOT NULL] [DEFAULT "default_value"]

 | k1  | k2  | v   |
 |-----|-----|-----|
 | 1   | 1   | 10  |
 | 1   | 2   | 20  |
 | 2   | 2   | 30  |

 | k1  | k2  | v   |
 |-----|-----|-----|
 | 1   | 1   | 5   |
 | 2   | 2   | 10  |
 | 3   | 1   | 5   |

 | k1  | k2  | v   |
 |-----|-----|-----|
 | 1   | 1   | 15  |
 | 1   | 2   | 20  |
 | 2   | 2   | 40  |
 | 3   | 1   | 5   |

PROPERTIES (    
 "host" = "mysql_server_host",    
 "port" = "mysql_server_port",   
 "user" = "your_user_name",  
 "password" = "your_password",   
 "database" = "database_name",   
 "table" = "table_name"   
 )

PROPERTIES (
 "broker_name" = "broker_name",
 "paths" = "file_path1[,file_path2]",
 "column_separator" = "value_separator",
 "line_delimiter" = "value_delimiter"
）

BROKER PROPERTIES(
 "username" = "name",
 "password" = "password"
)

key_type(k1[,k2 ...])

 PARTITION BY RANGE (k1)   
 (    
 PARTITION partition_name VALUES LESS THAN MAXVALUE|("value1") [("key"="value")],    
 PARTITION partition_name VALUES LESS THAN MAXVALUE|("value2") [("key"="value")],    
 ...   
 )

 DISTRIBUTED BY RANDOM [BUCKETS num]

 DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]

 PROPERTIES (    
 "storage_medium" = "[SSD|HDD]",    
 ["storage_cooldown_time" = "yyyy-MM-dd HH:mm:ss"],    
 ["replication_num" = "3"]    
 )

 PROPERTIES (
 "bloom_filter_columns"="k1,k2,k3"
 ）

SELECT * from tbl WHERE k1 = 12345;

CREATE TABLE example_db.table_random    
(    
k1 TINYINT,    
k2 DECIMAL(10, 2) DEFAULT "10.5",    
v1 CHAR(10) REPLACE,   
v2 INT SUM  
)   
ENGINE=olap
AGGREGATE KEY(k1, k2)   
DISTRIBUTED BY RANDOM BUCKETS 32    
PROPERTIES ("storage_type"="column");

 CREATE TABLE example_db.table_hash    
 (    
 k1 BIGINT,    
 k2 LARGEINT,    
 v1 VARCHAR(2048),   
 v2 SMALLINT DEFAULT "10"   
 )   
 ENGINE=olap
 UNIQUE KEY(k1, k2)   
 DISTRIBUTED BY HASH (k1, k2) BUCKETS 32   
 PROPERTIES(   
 "storage_type"="row"，    
 "storage_medium" = "SSD",    
 "storage_cooldown_time" = "2015-06-04 00:00:00"    
 );

 CREATE TABLE example_db.table_range    
 (    
 k1 DATE,    
 k2 INT,    
 k3 SMALLINT,   
 v1 VARCHAR(2048),   
 v2 DATETIME DEFAULT "2014-02-04 15:36:00"   
 )    
 ENGINE=olap
 DUPLICATE KEY(k1, k2, k3)    
 PARTITION BY RANGE (k1)   
 (    
 PARTITION p1 VALUES LESS THAN ("2014-01-01")   
 ("storage_medium" = "SSD", "storage_cooldown_time" = "2015-06-04 00:00:00"),    
 PARTITION p2 VALUES LESS THAN ("2014-06-01")   
 ("storage_medium" = "SSD", "storage_cooldown_time" = "2015-06-04 00:00:00"),    
 PARTITION p3 VALUES LESS THAN ("2014-12-01")    
 )    
 DISTRIBUTED BY HASH(k2) BUCKETS 32;

 ( { MIN }, {"2014-01-01"} )    
 [ {"2014-01-01"}, {"2014-06-01"} )   
 [ {"2014-06-01"}, {"2014-12-01"} )

 CREATE TABLE example_db.table_mysql   
 (    
 k1 DATE,    
 k2 INT,   
 k3 SMALLINT,    
 k4 VARCHAR(2048),    
 k5 DATETIME    
 )    
 ENGINE=mysql    
 PROPERTIES    
 (   
 "host" = "127.0.0.1",    
 "port" = "8239",    
 "user" = "mysql_user",    
 "password" = "mysql_passwd",    
 "database" = "mysql_db_test",    
 "table" = "mysql_table_test"    
 )

CREATE EXTERNAL TABLE example_db.table_broker 
(
k1 DATE,
k2 INT,
k3 SMALLINT,
k4 VARCHAR(2048),
k5 DATETIME
)
ENGINE=broker
PROPERTIES (
    "broker_name" = "hdfs",
    "path" = "hdfs://hdfs_host:hdfs_port/data1,hdfs://hdfs_host:hdfs_port/data2,hdfs://hdfs_host:hdfs_port/data3%2c4",
    "column_separator" = "|",
    "line_delimiter" = "\n"
)
BROKER PROPERTIES (
    "username" = "hdfs_user",
    "password" = "hdfs_password"
)

CREATE TABLE example_db.example_table
(
k1 TINYINT,
k2 DECIMAL(10, 2) DEFAULT "10.5",
v1 HLL HLL_UNION,
v2 HLL HLL_UNION
)
ENGINE=olap
AGGREGATE KEY(k1, k2)
DISTRIBUTED BY RANDOM BUCKETS 32
PROPERTIES ("storage_type"="column");

DROP DATABASE [IF EXISTS] db_name;

 DROP DATABASE db_test;

 DROP TABLE [IF EXISTS] [db_name.]table_name;

 DROP TABLE my_table;

 DROP TABLE IF EXISTS example_db.my_table;

 ALTER DATABASE db_name SET DATA QUOTA quota;

 ALTER DATABASE example_db SET DATA QUOTA 1073741824;

 ALTER TABLE [database.]table alter_clause1[, alter_clause2, ...];

 ADD PARTITION [IF NOT EXISTS] partition_name VALUES LESS THAN [MAXVALUE|("value1")] ["key"="value"] [DISTRIBUTED BY RANDOM [BUCKETS num] | DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]]

 DROP PARTITION [IF EXISTS] partition_name

 MODIFY PARTITION partition_name SET ("key" = "value", ...)

 ADD ROLLUP rollup_name (column_name1, column_name2, ...) [FROM from_index_name] [PROPERTIES ("key"="value", ...)]

 DROP ROLLUP rollup_name   
 [PROPERTIES ("key"="value", ...)]

 ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]    
 [AFTER column_name|FIRST]    
 [TO index_name]    
 [PROPERTIES ("key"="value", ...)]

 ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_value", ...)   
 [TO index_name]    
 [PROPERTIES ("key"="value", ...)]

 DROP COLUMN column_name [FROM index_name]

 MODIFY COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]    
 [AFTER column_name|FIRST]    
 [FROM index_name]    
 [PROPERTIES ("key"="value", ...)]

 ORDER BY (column_name1, column_name2, ...)    
 [FROM index_name]    
 [PROPERTIES ("key"="value", ...)]

RENAME new_table_name

RENAME ROLLUP old_rollup_name new_rollup_name

RENAME PARTITION old_partition_name new_partition_name

 ALTER TABLE example_db.my_table   
 ADD PARTITION p1 VALUES LESS THAN ("2014-01-01");

 ALTER TABLE example_db.my_table   
 ADD PARTITION p1 VALUES LESS THAN ("2015-01-01")    
 DISTRIBUTED BY RANDOM BUCKETS 20;

 ALTER TABLE example_db.my_table   
 DROP PARTITION p1;

 ALTER TABLE example_db.my_table    
 ADD ROLLUP example_rollup_index(k1, k3, v1, v2)    
 PROPERTIES("storage_type"="column");

 ALTER TABLE example_db.my_table    
 ADD ROLLUP example_rollup_index2 (k1, v1)    
 FROM example_rollup_index;

 ALTER TABLE example_db.my_table    
 DROP ROLLUP example_rollup_index2;

 ALTER TABLE example_db.my_table   
 ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1    
 TO example_rollup_index;

 ALTER TABLE example_db.my_table   
 ADD COLUMN new_col INT DEFAULT "0" AFTER col1    
 TO example_rollup_index;

 ALTER TABLE example_db.my_table   
 ADD COLUMN new_col INT DEFAULT "0" AFTER col1    
 TO example_rollup_index;

 ALTER TABLE example_db.my_table   
 ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1    
 TO example_rollup_index;

 ALTER TABLE example_db.my_table    
 ADD COLUMN (col1 INT DEFAULT "1", col2 FLOAT SUM DEFAULT "2.3")    
 TO example_rollup_index;

 ALTER TABLE example_db.my_table    
 DROP COLUMN col2    
 FROM example_rollup_index;

 ALTER TABLE example_db.my_table    
 MODIFY COLUMN col1 BIGINT DEFAULT "1" AFTER col2;

 ALTER TABLE example_db.my_table    
 MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";

 ALTER TABLE example_db.my_table    
 ORDER BY (k3,k1,k2,v2,v1)    
 FROM example_rollup_index;

 ALTER TABLE example_db.my_table    
 ADD COLUMN v2 INT MAX DEFAULT "0" AFTER k2 TO example_rollup_index,   
 ORDER BY (k3,k1,k2,v2,v1) FROM example_rollup_index;

 ALTER TABLE example_db.my_table
 PROPERTIES ("bloom_filter_columns"="k1,k2,k3");

 ALTER TABLE table1 RENAME table2;

 ALTER TABLE example_table RENAME ROLLUP rollup1 rollup2;

 ALTER TABLE example_table RENAME PARTITION p1 p2;

 CANCEL ALTER TABLE COLUMN FROM db_name.table_name

 CANCEL ALTER TABLE ROLLUP FROM db_name.table_name

 CANCEL ALTER TABLE COLUMN    
 FROM example_db.my_table;

 CANCEL ALTER TABLE ROLLUP    
 FROM example_db.my_table;

 LOAD LABEL load_label    
 (    
 data_desc1[, data_desc2, ...]    
 )
 broker    
 [opt_properties];

 [database_name.]your_label

 DATA INFILE    
 (    
 "file_path1 [, file_path2, ...]    
 )   
 [NEGATIVE]   
 INTO TABLE table_name   
 [PARTITION (p1, p2)]   
 [COLUMNS TERMINATED BY "column_separator"]    
 [(column_list)]   
 [SET (k1 = func(k2))]

 WITH BROKER broker_name ("key"="value"[,...])

 [PROPERTIES ("key"="value", ...)]

 LOAD LABEL example_db.label1    
 (    
 DATA INFILE("hdfs://hdfs_host:hdfs_port/user/doris/data/input/file")   
 INTO TABLE my_table   
 )
 WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password")   
 PROPERTIES    
 (   
 "timeout"="3600",    
 "max_filter_ratio"="0.1",    
 );

 LOAD LABEL example_db.label2
 (   
 DATA INFILE("hdfs://hdfs_host:hdfs_port/user/doris/data/input/file1")    
 INTO TABLE my_table_1    
 COLUMNS TERMINATED BY ","    
 (k1, k3, k2, v1, v2),    
 DATA INFILE("hdfs://hdfs_host:hdfs_port/user/doris/data/input/file2")    
 INTO TABLE my_table_2    
 COLUMNS TERMINATED BY "\t"   
 (k1, k2, k3, v2, v1)    
 )    
 WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");

 LOAD LABEL example_db.label3   
 (   
 DATA INFILE("hdfs://hdfs_host:hdfs_port/user/doris/data/input/*")    
 NEGATIVE    
 INTO TABLE my_table    
 COLUMNS TERMINATED BY "\\x01"    
 )
 WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");

 LOAD LABEL example_db.label4   
 (    
 DATA INFILE("hdfs://hdfs_host:hdfs_port/user/doris/data/input/old_file")    
 NEGATIVE    
 INTO TABLE my_table    
 COLUMNS TERMINATED BY "\t"   
 )
 WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");

 LOAD LABEL example_db.label5    
 (   
 DATA INFILE("hdfs://hdfs_host:hdfs_port/user/doris/data/input/file")    
 INTO TABLE my_table    
 PARTITION (p1, p2)    
 COLUMNS TERMINATED BY ","    
 (k1, k3, k2, v1, v2)   
 )
 WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");

 LOAD LABEL example_db.label6
 (
 DATA INFILE("hdfs://hdfs_host:hdfs_port/user/doris/data/input/file")
 INTO TABLE my_table
 PARTITION (p1, p2)
 COLUMNS TERMINATED BY ","
 (tmp_k1, tmp_k2, tmp_k3, v1, v2)
 SET (
   k1 = strftime("%Y-%m-%d %H:%M:%S", tmp_k1)),     
   k2 = time_format("%Y-%m-%d %H:%M:%S", "%Y-%m-%d", tmp_k2)),      
   k3 = alignment_timestamp("day", tmp_k3),     
   k4 = default_value("1"),     
   k5 = md5sum(tmp_k1, tmp_k2, tmp_k3)  
 )
 )
 WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password"); 

 LOAD LABEL example_db.label7
 (
 DATA INFILE("hdfs://hdfs_host:hdfs_port/user/doris/data/input/file")
 INTO TABLE my_table
 PARTITION (p1, p2)
 COLUMNS TERMINATED BY ","
 SET (
   v1 = hll_hash(k1),
   v2 = hll_hash(k2)
 )
 )
 WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password"); 

 LOAD LABEL example_db.label8
 (
 DATA INFILE("hdfs://hdfs_host:hdfs_port/user/doris/data/input/file")
 INTO TABLE `my_table`
 PARTITION (p1, p2)
 COLUMNS TERMINATED BY ","
 (k1, k2, tmp_k3, tmp_k4, v1, v2)
 SET (
   v1 = hll_hash(tmp_k3),
   v2 = hll_hash(tmp_k4)
 )
 )
 WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");

 curl --location-trusted -u user:passwd -T data.file http://fe.host:port/api/{db}/{table}/_load?label=xxx

 curl --location-trusted -u root:root -T testData http://fe.host:port/api/testDb/testTbl/_load?label=123

 curl --location-trusted -u root@test_cluster:root -T testData http://fe.host:port/api/testDb/testTbl/_load?label=123

 curl --location-trusted -u root -T testData http://fe.host:port/api/testDb/testTbl/_load?label=123\$amp;max_filter_ratio=0.2

 curl --location-trusted -u root -T testData http://fe.host:port/api/testDb/testTbl/_load?label=123\$amp;max_filter_ratio=0.2\$amp;columns=k1,k2,k3

 seq 1 10 | awk '{OFS="\\t"}{print $1, $1 * 10}' | curl --location-trusted -u root -T testData http://fe.host:port/api/testDb/testTbl/_load?label=123

 curl --location-trusted -u root -T testData http://host:port/api/testDb/testTbl/_load?label=123\&max_filter_ratio=0.2\&hll=hll_column1,k1:hll_column2,k2
        
 curl --location-trusted -u root -T testData http://host:port/api/testDb/testTbl/_load?label=123\&max_filter_ratio=0.2\&hll=hll_column1,tmp_k4:hll_column2,tmp_k5\&columns=k1,k2,k3,tmp_k4,tmp_k5

 CANCEL LOAD [FROM db_name] WHERE LABEL = "load_label";

 CANCEL LOAD FROM example_db WHERE LABEL = "example_db_test_load_label";

 EXPORT TABLE table_name
 [PARTITION (p1[,p2])]
 TO export_path
 [opt_properties]
 broker;

 [PROPERTIES ("key"="value", ...)]

 WITH BROKER broker_name ("key"="value"[,...])

 EXPORT TABLE testTbl TO "hdfs://hdfs_host:port/a/b/c" WITH BROKER "broker_name" ("username"="xxx", "password"="yyy");

 EXPORT TABLE testTbl PARTITION (p1,p2) TO "hdfs://hdfs_host:port/a/b/c" WITH BROKER "broker_name" ("username"="xxx", "password"="yyy");

 EXPORT TABLE testTbl TO "hdfs://hdfs_host:port/a/b/c" PROPERTIES ("column_separator"=",") WITH BROKER "broker_name" ("username"="xxx", "password"="yyy");

 DELETE FROM table_name PARTITION partition_name WHERE   
 column_name1 op value[ AND column_name2 op value ...];

 DELETE FROM my_table PARTITION p1 WHERE k1 = 3;

 DELETE FROM my_table PARTITION p1 WHERE k1 >= 3 AND k2 = "abc";

 SELECT select_list FROM
     table_or_subquery1 [INNER] JOIN table_or_subquery2 |
     table_or_subquery1 {LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]} JOIN table_or_subquery2 |
     table_or_subquery1 {LEFT | RIGHT} SEMI JOIN table_or_subquery2 |
     table_or_subquery1 {LEFT | RIGHT} ANTI JOIN table_or_subquery2 |
         [ ON col1 = col2 [AND col3 = col4 ...] |
             USING (col1 [, col2 ...]) ]
     [other_join_clause ...]
     [ WHERE where_clauses ]

 SELECT select_list FROM
     table_or_subquery1, table_or_subquery2 [, table_or_subquery3 ...]
     [other_join_clause ...]
 WHERE
     col1 = col2 [AND col3 = col4 ...]

 SELECT select_list FROM
     table_or_subquery1 CROSS JOIN table_or_subquery2
     [other_join_clause ...]
 [ WHERE where_clauses ]

 SELECT lhs.id, rhs.parent, lhs.c1, rhs.c2 FROM tree_data lhs, tree_data rhs WHERE lhs.id = rhs.parent;

 SELECT * FROM t1, t2;
 SELECT * FROM t1 CROSS JOIN t2;

 -- The following 3 forms are all equivalent.
 SELECT t1.id, c1, c2 FROM t1, t2 WHERE t1.id = t2.id;
 SELECT t1.id, c1, c2 FROM t1 JOIN t2 ON t1.id = t2.id;
 SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id = t2.id;

 SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id;
 SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id;
 SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;

 SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id = t2.id;
 SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id > t2.id;

 SELECT t1.c1, t1.c2, t1.c2 FROM t1 LEFT SEMI JOIN t2 ON t1.id = t2.id;

 SELECT t1.c1, t1.c2, t1.c2 FROM t1 LEFT ANTI JOIN t2 ON t1.id = t2.id;

ORDER BY col [ASC | DESC]

mysql> select * from big_table order by tiny_column, short_column desc;

 mysql> select tiny_column, sum(short_column) from small_table group by tiny_column;
 +-------------+---------------------+
 | tiny_column | sum(`short_column`) |
 +-------------+---------------------+
 |           1 |                   2 |
 |           2 |                   1 |
 +-------------+---------------------+
 2 rows in set (0.07 sec)

 mysql> select tiny_column, sum(short_column) from small_table group by tiny_column having sum(short_column) = 1;
 +-------------+---------------------+
 | tiny_column | sum(`short_column`) |
 +-------------+---------------------+
 |           2 |                   1 |
 +-------------+---------------------+
 1 row in set (0.07 sec)

 mysql> select tiny_column, sum(short_column) from small_table group by tiny_column having tiny_column > 1;
 +-------------+---------------------+
 | tiny_column | sum(`short_column`) |
 +-------------+---------------------+
 |           2 |                   1 |
 +-------------+---------------------+
 1 row in set (0.07 sec)

 mysql> select tiny_column from small_table limit 1;
 +-------------+
 | tiny_column |
 +-------------+
 |           1 |
 +-------------+
 1 row in set (0.02 sec)

 mysql> select tiny_column from small_table limit 10000;
 +-------------+
 | tiny_column |
 +-------------+
 |           1 |
 |           2 |
 +-------------+
 2 rows in set (0.01 sec)

 mysql> select varchar_column from big_table order by varchar_column limit 3;
 +----------------+
 | varchar_column |
 +----------------+
 | beijing        |
 | chongqing      |
 | tianjin        |
 +----------------+
 3 rows in set (0.02 sec)

 mysql> select varchar_column from big_table order by varchar_column limit 1 offset 0;
 +----------------+
 | varchar_column |
 +----------------+
 | beijing        |
 +----------------+
 1 row in set (0.01 sec)

 mysql> select varchar_column from big_table order by varchar_column limit 1 offset 1;
 +----------------+
 | varchar_column |
 +----------------+
 | chongqing      |
 +----------------+
 1 row in set (0.01 sec)

 mysql> select varchar_column from big_table order by varchar_column limit 1 offset 2;
 +----------------+
 | varchar_column |
 +----------------+
 | tianjin        |
 +----------------+
 1 row in set (0.02 sec)

query_1 UNION [DISTINCT | ALL] query_2

 mysql> (select tiny_column from small_table) union all (select tiny_column from small_table);
 +-------------+
 | tiny_column |
 +-------------+
 |           1 |
 |           2 |
 |           1 |
 |           2 |
 +-------------+
 4 rows in set (0.10 sec)

 mysql> (select tiny_column from small_table) union (select tiny_column from small_table);    
 +-------------+
 | tiny_column |
 +-------------+
 |           2 |
 |           1 |
 +-------------+
 2 rows in set (0.11 sec)

 mysql> select * from (select tiny_column from small_table union all\
     -> select tiny_column from small_table) as t1 \
     -> order by tiny_column limit 4;
 +-------------+
 | tiny_column |
 +-------------+
 |           1 |
 |           1 |
 |           2 |
 |           2 |
 +-------------+
 4 rows in set (0.11 sec)

 mysql> -- Returns the unique values from one column.
 mysql> select distinct tiny_column from big_table limit 2;
 mysql> -- Returns the unique combinations of values from multiple columns.
 mysql> select distinct tiny_column, int_column from big_table limit 2;

 mysql> -- Counts the unique values from one column.
 mysql> select count(distinct tiny_column) from small_table;
 +-------------------------------+
 | count(DISTINCT `tiny_column`) |
 +-------------------------------+
 |                             2 |
 +-------------------------------+
 1 row in set (0.06 sec)
 mysql> -- Counts the unique combinations of values from multiple columns.
 mysql> select count(distinct tiny_column, int_column) from big_table limit 2;

 mysql> -- Count the unique value from multiple aggregation function separately.
 mysql> select count(distinct tiny_column, int_column), count(distinct varchar_column) from big_table;

SELECT x FROM t1 WHERE x [NOT] IN (SELECT y FROM t2);
SELECT x FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE y = 1);

SELECT * FROM t1 WHERE x [NOT] IN (SELECT a FROM t2 WHERE t1.y = t2.b);
SELECT * FROM t1 WHERE [NOT] EXISTS (SELECT a FROM t2 WHERE t1.y = t2.b);

1、不相关标量子查询，谓词为=号。例如输出最大工资的人的信息
SELECT name FROM table WHERE salary = (SELECT MAX(salary) FROM table);
2、不相关标量子查询，谓词为>,<等。例如输出比平均工资高的人的信息
SELECT name FROM table WHERE salary > (SELECT AVG(salary) FROM table);
3、相关标量子查询。例如输出各个部门工资最高的信息
SELECT name FROM table a WHERE salary = （SELECT MAX(salary) FROM table b WHERE b.部门= a.部门）;
4、标量子查询作为普通函数的参数
SELECT name FROM table WHERE salary = abs((SELECT MAX(salary) FROM table));

 -- Define one subquery at the outer level, and another at the inner level as part of the
 -- initial stage of the UNION ALL query.
 with t1 as (select 1) (with t2 as (select 2) select * from t2) union all select * from t1;

 SHOW ALTER TABLE [COLUMN | ROLLUP] [FROM db_name];

 SHOW ALTER TABLE COLUMN;

 SHOW ALTER TABLE ROLLUP FROM example_db;

 SHOW DATA [FROM db_name[.table_name]];

 SHOW DATA;

 SHOW DATA FROM example_db.table_name;

 SHOW DATABASES;

 SHOW LOAD    
 [FROM db_name]    
 [   
 WHERE    
 [LABEL [ = "your_label" | LIKE "label_matcher"]]    
 [STATUS = ["PENDING"|"ETL"|"LOADING"|"FINISHED"|"CANCELLED"|]]    
 ]    
 [ORDER BY ...]    
 [LIMIT limit];

 SHOW LOAD;

 SHOW LOAD FROM example_db WHERE LABEL LIKE "2014_01_02" LIMIT 10;

 SHOW LOAD FROM example_db WHERE LABEL = "load_example_db_20140102" ORDER BY LoadStartTime DESC;

 SHOW LOAD FROM example_db WHERE LABEL = "load_example_db_20140102" AND STATE = "loading" ORDER BY LoadStartTime DESC;

 SHOW EXPORT
 [FROM db_name]
 [
 WHERE
 [EXPORT_JOB_ID = your_job_id]
 [STATE = ["PENDING"|"EXPORTING"|"FINISHED"|"CANCELLED"]]
 ]
 [ORDER BY ...]
 [LIMIT limit];

SHOW EXPORT;

SHOW EXPORT FROM example_db ORDER BY StartTime DESC;

SHOW EXPORT FROM example_db WHERE STATE = "exporting" ORDER BY StartTime DESC;

SHOW EXPORT FROM example_db WHERE EXPORT_JOB_ID = job_id;

 SHOW PARTITIONS FROM [db_name.]table_name [PARTITION partition_name];

 SHOW PARTITIONS FROM example_db.table_name;

 SHOW PARTITIONS FROM example_db.table_name PARTITION p1;

 SHOW QUOTA FOR [user]

 SHOW QUOTA FOR system;

 SHOW RESOURCE [LIKE user_name]

 SHOW RESOURCE LIKE "system";

 SHOW TABLES;

 SHOW TABLET [FROM [db_name.]table_name | tablet_id]

 SHOW TABLET FROM example_db.table_name;

 SHOW TABLET 10000;

 CREATE USER user_specification [SUPERUSER]    
 user_specification:    
 'user_name' [IDENTIFIED BY [PASSWORD] 'password']

 CREATE USER 'jack'

 CREATE USER 'jack' IDENTIFIED BY '123456'

 CREATE USER 'jack' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

 SELECT PASSWORD('123456')

 CREATE USER 'jack' SUPERUSER

 DROP USER 'user_name'

DROP USER 'jack'

 ALTER USER user alter_user_clause_list    
 alter_user_clause_list:    
 alter_user_clause [, alter_user_clause] ...    
 alter_user_clause:   
 MODIFY RESOURCE resource value | MODIFY PROPERTY property value   
 resource:    
 CPU_SHARE   
 property:   
 MAX_USER_CONNECTIONS

 ALTER USER jack MODIFY RESOURCE CPU_SHARE 1000

 ALTER USER jack MODIFY PROPERTY MAX_USER_CONNECTIONS 1000

 ALTER QUOTA FOR user_name MODIFY group_name value

 ALTER QUOTA FOR system MODIFY normal 400;

 GRANT privilege_list ON db_name TO 'user_name'    
 privilege_list:   
 privilege [, privilege] ...    
 privilege:    
 READ_ONLY | READ_WRITE | ALL

 GRANT READ_ONLY ON testDb to 'jack';

 GRANT ALL ON testDb to 'jack';

 SET PASSWORD [FOR 'user_name'] = [PASSWORD('plain password')]|['hashed password']

 SET PASSWORD = PASSWORD('123456')    
 SET PASSWORD = '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

 SET PASSWORD FOR 'jack' = PASSWORD('123456')    
 SET PASSWORD FOR 'jack' = '\*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

 1.增加节点
     ALTER SYSTEM ADD BACKEND "host:heartbeat_port"[,"host:heartbeat_port"...];
 2.删除节点
     ALTER SYSTEM DROP BACKEND "host:heartbeat_port"[,"host:heartbeat_port"...];
 3.节点下线
     ALTER SYSTEM DECOMMISSION BACKEND "host:heartbeat_port"[,"host:heartbeat_port"...];
 4.增加Broker
     ALTER SYSTEM ADD BROKER broker_name "host:port"[,"host:port"...];
 5.减少Broker
     ALTER SYSTEM DROP BROKER broker_name "host:port"[,"host:port"...];
 6.删除所有Broker
     ALTER SYSTEM DROP ALL BROKER broker_name

 ALTER SYSTEM ADD BACKEND "host:9850";

 ALTER SYSTEM DROP BACKEND "host1:9850", "host2:9850";

 ALTER SYSTEM DECOMMISSION BACKEND "host1:9850", "host2:9850";

 ALTER SYSTEM ADD BROKER hdfs "host1:9850", "host2:9850";

 CANCEL ALTER SYSTEM DECOMMISSION BACKEND "host:heartbeat_port"[,"host:heartbeat_port"...];

 CANCEL ALTER SYSTEM DECOMMISSION BACKEND "host1:9850", "host2:9850";

 CREATE CLUSTER [IF NOT EXISTS] cluster_name
 PROPERTIES ("key"="value", ...)
 IDENTIFIED BY 'password'

 CREATE CLUSTER test_cluster PROPERTIES("instance_num"="3") IDENTIFIED BY 'test';

 CREATE CLUSTER default_cluster PROPERTIES("instance_num"="3") IDENTIFIED BY 'test';

 ALTER CLUSTER cluster_name PROPERTIES ("key"="value", ...);

 ALTER CLUSTER test_cluster PROPERTIES ("instance_num"="2");

 ALTER CLUSTER test_cluster PROPERTIES ("instance_num"="4");

 DROP CLUSTER [IF EXISTS] cluster_name;