Mysql：SQL语句：DML语句

执行存储过程

CALL sp_name([parameter[,...]])

CALL sp_name[()]

 

删除

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

FROM tbl_name

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

 

 

执行表达式计算

DO expr [, expr] ... 和SELECT expr [, expr] ...类似，但不返回select的结果，因此进行表达式的技术时，性能较高

 

 

插入数据

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)]

{VALUES | VALUE} ({expr | DEFAULT},...),(...),...

[ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ]

 

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name

SET col_name={expr | DEFAULT}, ...

[ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ]

 

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)]

SELECT ...

[ ON DUPLICATE KEY UPDATE col_name=expr [, col_name=expr] ... ]

 

插入数据-mysql的扩展，和insert类似，除了：如果表中有何新插入冲突的数据，会先删除旧数据，而后再插入新数据

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)]

{VALUES | VALUE} ({expr | DEFAULT},...),(...),...

 

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name

SET col_name={expr | DEFAULT}, ...

 

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)]

SELECT ...

 

 

 

选择语句select

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT][SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]


select_expr [, select_expr ...]

[

FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position} [ASC | DESC], ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[PROCEDURE procedure_name(argument_list)]

[INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]]

[FOR UPDATE | LOCK IN SHARE MODE]

]

 

联合查询结果

SELECT ...

UNION [ALL | DISTINCT]

SELECT ...

[UNION [ALL | DISTINCT] SELECT ...]

 

 

更新表

UPDATE [LOW_PRIORITY] [IGNORE] table_reference

SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

 

 

 

截断表

TRUNCATE [TABLE] tbl_name

 

 

子查询：使用子查询总是有诸多的限制的，而且往往是可以更好的进行优化的。

在条件中的子查询

子查询的结果集特征	一列	多列
一行	单个标量： {= | > | < | >= | <= |  <> |  != |  <=>} subquery {exists | not exists} subquery	单个矢量：  矢量形式  {= | > | < | >= | <= |<> | != | <=>}  subquery {exists | not exists} subquery
多行	范围标量：  in subquery {= | > | < | >= | <= |  <> |  != |  <=>} {all | any | some } subquery {exists | not exists} subquery	范围矢量：  矢量形式  {= | > | < | >= | <= |<> | != | <=>}  subquery {exists | not exists} subquery

 

相关子查询 ：（内层）子查询中用到 外层查询中的表值

例如：SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

 

作为FROM 表 来源的子查询：

SELECT ... FROM (subquery) [AS] name ...

 

 

装载数据文件中的数据

LOAD DATA

[LOW_PRIORITY | CONCURRENT] [LOCAL]

INFILE 'file_name'

[REPLACE | IGNORE]

INTO TABLE tbl_name

[CHARACTER SET charset_name]

[{FIELDS | COLUMNS} [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,...]