Delete su più tabelle
DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;
Or:
DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id;
IF IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then
IF()
returns expr2; otherwise it returns expr3.IF()
returns a numeric or string value, depending on the context in which it is used.mysql> SELECT IF(1>2,2,3);
-> 3
mysql> SELECT IF(1<2,'yes','no');
-> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
-> 'no'
$sql="SELECT * FROM users WHERE DATEDIFF(CURDATE(), IF('register_date'=NULL,CURDATE(), 'register_date')) ='1'";
14.1.5 LOAD DATA INFILE Syntax
Esempio per caricare in una tabella il contenuto del file mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' -> LINES TERMINATED BY '\n';
14.1.7 SELECT Syntax
Esempio per caricare in unfile il risultato di una select
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;
14.1.4.1 INSERT ... SELECT
With INSERT ... SELECT, you can quickly insert many rows into a table from one or many tables.
INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
14.2.6 CREATE TABLE Syntax
You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:
CREATE TABLE new_tbl SELECT * FROM orig_tbl;