William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

mysql: 3 tips

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: