数据操作语句（Data Manipulation Language）

Delete

单表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

多表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*] ...] FROM table_references [WHERE where_definition]

或：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*] ...] USING table_references [WHERE where_definition]

tbl_name中有些行满足where_definition给定的条件。Delete用于删除这些行，并且返回被删除记录的数目。

Insert

insert into tbl_name [(field1,field2,field3…fieldN)] values (value1,value2,value3…valueN)

Insert用于向一个已有的表插入新行。

insert into tbl_name [(field1,field2,field3…fieldN)] select …

例如，

INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;  

Select

select column_name [from table_reference] [where where_definition] [group by {column_name}] [asc| desc ] [having where_definition] [oder by {column_name}] [limit n] [offset n]

Select语句用于从一个或多个表中选择行，并可以加入union和子查询。

其中 group by 和having 常常连在一起用。limit限制查询结果的行数，offset添加偏置，也就是说可以从任意行开始查询。

select … union select …

mysql> select *from books; +---------+-----------------------+-----------+-------+----------+------------+ | book_id |title | author_id | genre | pub_year |isbn | +---------+-----------------------+-----------+-------+----------+------------+ | 1 | The end of the affair | 2 | novel | 1938 | 0099478447 | | 2 | Brighton Rock | 3 | novel | 1938 | 0099478471 | | 3 | The Quiet American | 1 | novel | 1955 | 0099478393| | 6 | The End | 1 | novel | 1939 | 0099478472 | | 7 | Python | 1 | novel | 1970 | 0099478473 | +---------+-----------------------+-----------+-------+----------+------------+  mysql> selectauthor_id,count(*) as total from books group by author_id having total>1; +-----------+-------+ | author_id | total| +-----------+-------+ | 1 | 3 | +-----------+-------+ 1 row in set (0.00 sec)

Join语法

table_references: table_reference [, table_reference] … table_reference: table_factor | join_table table_factor: tbl_name [[AS] alias] [{USE|IGNORE|FORCE} INDEX (key_list)] | ( table_references ) | { OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr } join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition] | table_reference LEFT [OUTER] JOIN table_reference join_condition | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor | table_reference RIGHT [OUTER] JOIN table_reference join_condition | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor join_condition: ON conditional_expr | USING (column_list)

在MySQL中，CROSS JOIN从语法上说与INNER JOIN等同（两者可以互相替换。

cross join 相当与笛卡尔积。

Left join：匹配左侧，即使右侧没有匹配项，也显示。

Right join：匹配右侧，即使左侧没有匹配项，也显示。

 

例子，使用 on expr 将两个表连接起来

mysql> select concat(author_first,author_last),title from authors -> left join books onauthors.author_id=books.author_id; +----------------------------------+-----------------------+ |concat(author_first,author_last) | title | +----------------------------------+-----------------------+ | J.K.Rowling | The end of the affair | |GrahamGreene | TheQuiet American | |GrahamGreene | TheEnd | |GrahamGreene |Python | +----------------------------------+-----------------------+ 4 rows in set(0.01 sec)

例子，使用using（column_list），将两个表连接起来

mysql> select concat(author_first,author_last),title from authors -> left join books using(author_id); +----------------------------------+-----------------------+ |concat(author_first,author_last) | title | +----------------------------------+-----------------------+ | J.K.Rowling | The end of the affair | |GrahamGreene | TheQuiet American | |GrahamGreene | TheEnd | |GrahamGreene |Python | +----------------------------------+-----------------------+ 4 rows in set(0.00 sec)

故意在authors中添加一个，无法匹配的行。

insert into authorvalues (10,”altman”,”wang”,”CN”);

例子，左查询

mysql> selectconcat(author_first," ",author_last) as name,title from authors leftjoin books using( author_id); +---------------+-----------------------+ | name | title | +---------------+-----------------------+ | J.K.Rowling | The end of the affair | | Graham Greene |The Quiet American | | Graham Greene |The End | | Graham Greene |Python | | wang altman | NULL | +---------------+-----------------------+ 5 rows in set(0.00 sec)

Update

Single-table语法：

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

Multiple-table语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行。如果指定了ORDER BY子句，则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值，限制可以被更新的行的数目。

Load

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...)]

在导出文件的过程中，支持配置字段分隔符，包裹符，和转义符。和配置行（记录）结束符，和起始符。

load data infile ‘data.txt’into tabledb2.test fieldsterminated by “str1” lines terminatedby “str2” ignorenumber lines；

每个fields由str1隔开，每行由str2结束，自动忽略前n行。

load data infile 是

select column1,column2,…columnN intooutfile “/tmp/result.txt” fields terminated by “str1” lines terminated by “str2” from test；

的补语。