(1) Left Join Left Join is used for multi-tables select. To select columns from different tables, which maybe null, duplicate, relative each other, use Left Join for the case.

The following sql selects a certain record which includes columns in 3 tables:

SELECT * FROM users u left join (questions q, answers a) on ( a.uid = u.uid AND q.qid = a.qid1) where u.sin = ’” . $sin. ”’ and birthdate = ’“. $dob . ”’“;

Here is a more complex example:

SELECT names.codename, s1.score AS “Score1”, s1.comment AS “Comments1”, s2.score AS “Score2”, s2.comment AS “Comments2”, SUM(st.score) AS “Total” FROM students names LEFT JOIN scores s1 ON s1.act_id=1 AND names.id=s1.student_id LEFT JOIN scores s2 ON s2.act_id=2 AND names.id=s2.student_id LEFT JOIN scores st ON names.id=st.student_id WHERE names.codename ” GROUP BY names.codename ORDER BY names.codename;

(2)INSERT … ON DUPLICATE KEY UPDATE

Suppose we have a user_detail table, some user fill up it, some not. The flow like this: . if the record exists, update it, . if not, insert the record.

What is the samplest way to process user_detail table when user insert/update ? MySQL has the answer. Don’t need stored procedure, or function, just the extend ‘INSERT’ syntax. INSERT … ON DUPLICATE KEY UPDATE If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

and:

UPDATE table SET c=c+1 WHERE a=1;

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated.

So, for a certain user_detail record, the following will excute($uid is primary key and unique):

• insert if $uid not exists, or- update if $uid exists

INSERT INTO user_details values(“.$uid.”,“.$qid1.”,’“.$answer1.”’,“.$qid2.”,’“.$answer2.”‘,Now()) on duplicate key update qid1=“.$qid1.”,answer1=’“.$answer1.”‘,qid2=“.$qid2.”,answer2=’“.$answer2.”‘,putdate=Now()

very cool.

(3) import databse/table Except phpMyAdmin, I found the easiest way to import database/table is:

$ mysql -u test -p -D test -h localhost <users.sql

Not mysqldump, or restore etc.