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,...]