内容来自实验楼:MySQL 基础课程
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] or: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*] ...] USING table_references [WHERE where_definition]当您不想知道被删除的行的数目时,有一个更快的方法,即使用TRUNCATE TABLE。 如果DELETE语句中含有ORDER BY子句,则按照子句中指定的顺序删除行,再结合LIMIT语句会显得非常有优势:DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp_column LIMIT 1;
DO语法:DO expr [, expr] ...DO用于执行表达式,但是不返回任何结果。DO是SELECT expr的简化表达方式。
INSERT语法:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] or: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] or: INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]INSERT...SELECT形式的语句插入从其它表中选出的行。
LOAD DATA INFILE语法:
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,...)]
REPLACE语法:
REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... 或: REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... 或: REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] SELECT ...REPLACE的运行模式与INSERT很相像。只有一点除外,如果表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值,则在新记录被插入之前,旧记录被删除。
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, ... [INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name'] [FROM table_references [WHERE where_definition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_definition] [ORDER BY {col_name | expr | position} [ASC | DESC] , ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [FOR UPDATE | LOCK IN SHARE MODE]]可以在没有表可以参考的情况下指定DUAL为一个虚拟的表名:SELECT 1 + 1 FROM DUAL; (1).JOIN语法:
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 STRAIGHT_JOIN table_factor | table_reference STRAIGHT_JOIN table_factor ON 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)(2).UNION语法:
SELECT ... UNION [ALL | DISTINCT] SELECT ...
UPDATE语法: 单表:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]多表:
UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]