DML of SQL

CLAIMS DML : LOAD,INSERT,SELECT

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

    1.1 Format
       LOAD TABLE table_name FROM expr_list with STRING, STRING;
    1.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’。
    1.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)

    2.1 Format
       INSERT [INTO] table_name [(column_name, …)] VALUES (expr, …), …;
    2.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
    2.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

    3.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_confitions] 
    [ORDER BY order_list [ASC | DESC]]
    [LIMIT [offset,]row_count ];
   
    3.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 suffle 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_confitions	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.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

    3.4 Special Case Description
**Region Table**

regionkey (bigint unsigned)	name(varchar(30))
0	Africa
0	Africa
0	Africa
0	Africa