SQL commands of DML (Data Manipulation Language)

DML (Data Manipulation Language)的SQL commands有:

(1) SELECT ~ retrieve data from the a database

語法:

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' export_options 

        | INTO DUMPFILE 'file_name' 

        | INTO var_name [, var_name]] 

   [FOR UPDATE | LOCK IN SHARE MODE]]

更多參考: http://dev.mysql.com/doc/refman/5.0/en/select.html

例如:
SELECT * FROM mytable
WHERE field1>100 ;

(2) INSERT ~ insert data into a table


語法:

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

更多參考: http://dev.mysql.com/doc/refman/5.5/en/insert.html

例如:
INSERT INTO mytable (field1, field2) VALUES ('John', 100) ;


(3) UPDATE ~ updates existing data within a table


語法:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference 

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

     [WHERE where_condition] 

     [ORDER BY ...] 

     [LIMIT row_count]

更多參考: http://dev.mysql.com/doc/refman/5.0/en/update.html

例如:

UPDATE mytable
SET field1='Mary', field2=80 ;

(4) DELETE ~ deletes all records from a table, the space for the records remain


語法:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name 

     [WHERE where_condition] 

     [ORDER BY ...] 

     [LIMIT row_count]

更多參考: http://dev.mysql.com/doc/refman/5.0/en/delete.html

例如:
DELETE FROM mytbale
WHERE field2<60 ;