mysql中2個非常有用的查詢

haohaotmac

                mysql中2個非常有用的查詢
第一個：
INSERT ... SELECT Syntax
用處：數(shù)據(jù)轉(zhuǎn)換（特別是數(shù)據(jù)量大的時候），我測試過。轉(zhuǎn)換100萬數(shù)據(jù)也只是用秒計劃就OK了。
INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)]
    SELECT ...
With INSERT ... SELECT, you can quickly insert many rows into a table from one or many tables.
For example:
INSERT INTO tblTemp2 (fldID)
    SELECT tblTemp1.fldOrder_ID FROM tblTemp1
    WHERE tblTemp1.fldOrder_ID > 100;
The following conditions hold for an INSERT ... SELECT statement:
Prior
to MySQL 4.0.1, INSERT ... SELECT implicitly operates in IGNORE mode.
As of MySQL 4.0.1, specify IGNORE explicitly to ignore records that
would cause duplicate-key violations.
Do not use DELAYED with INSERT ... SELECT.
Prior
to MySQL 4.0.14, the target table of the INSERT statement cannot appear
in the FROM clause of the SELECT part of the query. This limitation is
lifted in 4.0.14.
AUTO_INCREMENT columns work as usual.
To
ensure that the binary log can be used to re-create the original
tables, MySQL will not allow concurrent inserts during INSERT ...
SELECT.
You can use REPLACE instead of INSERT to overwrite old
rows. REPLACE is the counterpart to INSERT IGNORE in the treatment of
new rows that contain unique key values that duplicate old rows: The
new rows are used to replace the old rows rather than being discarded.
2.in的用法。這個好處很多。特別是刪除數(shù)據(jù)的時候可以不用whil循環(huán)就行了
比如語句：
$SQL = sprintf("DELETE FROM ".PRODUCTOPTION_TABLE." WHERE (productOptionID IN ('%s'))",join("','", $_POST['fieldsID']));
        $DB->query($SQL);
就直接把一個產(chǎn)品列表中數(shù)據(jù)全刪除了
               
               
               
               
               

本文來自ChinaUnix博客，如果查看原文請點(diǎn)：http://blog.chinaunix.net/u2/85566/showart_2024913.html