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 ;

留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理