DML of SQL

CLAIMS DML : LOAD,INSERT,SELECT

1. LOAD

(This statement is used to import data from the disk)

1. Format

   LOAD TABLE table_name FROM expr_list with STRING, STRING;
   

2. Explanation

   expr_list：Specify the import data file path, you can enter multiple file paths, separated by commas. eg.“/home/imdb/data/table1”,”/home/imdb/data/table2”.

   The first column delimiter STRING specify only a character, if more than one character, take the first character. eg. ’\t’。 The second column delimiter STRING specify only a character, if more than one character, take the first character. eg. ’\n’。

3. Example

LOAD TABLE a from “/home/imdb/table.csv” with ‘\t’,’\n’;

2. INSERT

(This statement is used to insert one or more data within a particular table)

1. Format

   INSERT [INTO] table_name [(column_name, …)] VALUES (expr, …), …;
   

2. Explanation

   column_name：Specify the insertion of data for the column, if not, the default insert all columns.
   (Expr,…)：Specify the insertion of content, you need to correspond with the specified column.Insert              multiple columns of data can use （expr, …）, …，Each data included by the parentheses, separated by commas
   

3. Example

   insert into test1 values(10,1.11);

   insert into region values(4,'Middle'),(3,'Europe'),(2,'Asia'),(1,'America'),(0,'Africa');

3. SELECT

1. Format

     SELECT [ALL | DISTINCT] select_list [AS other_name]  
     FROM table_name | join_table
     [WHERE where_conditions]
     [GROUP BY group_by_list]
     [HAVING search_conditions] 
     [ORDER BY order_list [ASC | DESC]]
     [LIMIT [offset,]row_count ];
   

2. SELECT Clause Description

Clause	Explanation	notation
ALL | DISTINCT	In the database table may contain duplicate values. Designated "DISTINCT", in the same row on the query results show only one line; specify "ALL", then list all of the line; not specified, the default is "ALL".	Does not support now
select_list	Lists the column name or expression to query, use the "," separated,use "*" present all columns	See more details about expression in 3.3 Expression Support Sheet
AS other_name	Optionally, rename the output field	Aliases can be used in the having and order by
FROM table_name | join_table	Required,specify the table which be read data,support connection between tables	bo not support 'using' in join
WHERE where_conditions	optional, WHERE clause for set a shuffle condition	query results only contain the tuple meet the conditions
GROUP BY group_by_list	for Subtotals	If it is expression, and appear in the select list, then the expression must have aliases, and must refer to the alias in orderby, having
HAVING search_conditions	HAVING clause is similar to the WHERE clause, but the HAVING clause can use aggregate functions (such as SUM, AVG, etc.), the prerequisite is to have a GROUP By clause appears.
ORDER BY order_list [ASC | DESC]	For ascending (ASC) or descending (DESC) display the query results. When you do not specify ASC or DESC, the default is ASC.	order_list only specific properties, does not support digital
[LIMIT [offset,] row_count ]	Forced SELECT statement returns the specified number of records. LIMIT accept one or two digits natural numbers constant parameters, the offset (the default is 0), the maximum number

3.Expression Support Sheet

Expression Type	Parameters required	Example	Remark
expr + expr , expr - expr , expr * expr , expr / expr , expr % expr	Compatible	1+2 , 2-1 , 3*4 , 4/2 , 4%2	do not boolean type data
expr AND expr , expr OR expr	Numeric, logic	1 and 0 , 1 or 0
expr CMP (=,!=,>,<,>=,<=) expr	Compatible	a>b
Expr[NOT] IN const_list	Corresponding parameter type is compatible	a in (2,4,5)	IN=(=ANY)
expr_row [NOT] IN const_row_list	Corresponding parameter type is compatible	(a,b) in ((1,'a'),(2,'b'))
- expr(negative expression)	the type has negative	-a
NOT expr(Negative logic), ! expr(Negative logic)=NOT expr	logic	not a , ! a
SUBSTRING(expr,start_position,length)	start_position,length is Non-negative integer	substring('string',2,4)
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)	remstr is a string which length is 1	trim(' abc '); , trim(BOTH 'a' FROM 'abca');	LEADING Start from the head , TRAILING Start from the tail
UPPER(expr)	String	upper('abc')
expr [NOT]LIKE expr1	String	'abc' like '_b%'	"_" present one char，"%" present any chars
CASE WHEN expr1 then expr2 [WHEN expr1 then expr2 else expr3] END	The return type of expr1 is logical, expr2 is String	case when a%2=0 then 'even' when a%2=1 then 'odd' else 'none' end
CASE expr1 WHEN expr2 THEN expr3 [WHEN expr2 THEN expr3 else expr4] END	"=" is compatible in expr1 and expr2 ,expr3 and expr4 is string	case sex when 1 then ‘male’ else ‘female’ end	it means expr1=expr2，so present expr3
expr BETWEEN expr1 AND expr2	expr1 and expr2 's compare operation is compatible with the expr	a between 2 and 4
DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type)	date : Date type String , expr : non-negative Integer , type : day,month,year	DATE_ADD('2014-9-7',INTERVAL 3 day)	Date constants must be delimited as 2010-10-05

4.Special Case Description

•      Region Table
  

regionkey (bigint unsigned)	name(varchar(30))
0	Africa
1	America
2	Asia
3	Europe
4	Middle

•      National table
  

nationkey (bigint unsigned)	name(varchar(30))	regionkey (bigint unsigned)
0	CHINA	2
1	UNITED STATES	1
2	RUSSIA	3
3	KENYA	0
4	JORDAN	4
5	INDIA	2

(1) aggregation function and group by

if "select_list" exist aggregation function（SUM()，COUNT()，AVG()，MAX()，MIN() and COUNT(*), Then the non-aggregation function attribute columns exist in "select_list" are a subset of the attribute columns in "groupby_list".If a expression in "groupby_list",and it also appear in "select_list",then this expression must use alias in "select_list",and use this alias in "orderby" and "having" refer to this expression. "groupby" expression mathematical operation.

select max(nationkey),regionkey%5 as regionkey from nation group by regionkey;

Result

max(nationkey)	regionkey
3	0
1	1
5	2
2	3
4	4
Explanation: regionkey%5 must declare a alias in select_list like regionkey, in group by use regionkey to refer.

(2)join

join connection can be divided into inner join and outer join.outer join can be divided into left outer join and right outer join and full outer join.After the two tables join, you can use "ON specified condition"to filter.Currently ,CLAIMS support " Ta join Tb [ON condition]" support equal join and corss join。

select nation.name,region.name from nation join region on nation.regionkey = region.regionkey;

Result

nation.name	region.name
CHINA	Asia
UNITED STATES	America
RUSSIA	Europe
KENYA	Africa
JORDAN	Middle
INDIA	Asia