MYSQL

Note: All these based on MySQL 5.1.61

1. Find duplicate entries in table

SELECT COUNT(*), column1, column2 FROM tablename
GROUP BY column1, column2
HAVING COUNT(*)>1;

2. Find entries that are null id on the other table

SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.ID) WHERE table2.ID IS NULL

3. Find distinct/unique records

SELECT DISTINCT column1, column2 FROM table ORDER BY column1, column2

4. Joining / combining 2 tables with same structure

SELECT * FROM table1 UNION SELECT * FROM table2
Note: Able to CREATE VIEW using the above syntax to form new VIEW

5. Create & populate table from a VIEW

CREATE TABLE table AS SELECT * FROM view;
INSERT INTO table SELECT * FROM view;

6. Read UTF-8 in MySQL

Put this line before any query:
mysql_query(“SET character_set_results=utf8”, $dbconn); 

7. Create Trigger to update another table in different database

CREATE TRIGGER trigger_name BEFORE INSERT ON table_name
FOR EACH ROW
INSERT INTO db2.table2 (col1,col2,datetime,…) VALUES (NEW.col1,NEW.col2,SYSDATE(),…);

Alternatively, for multiple actions, need to add the following items:

DELIMITER |

CREATE TRIGGER trigger_name BEFORE INSERT ON table_name
FOR EACH ROW

BEGIN
stmt1;
stmt2;
SET NEW.col3=(SELECT col FROM table1 WHERE col2=NEW.col2);
END;
|

DELIMITER ;

Thanks to John Hundley, Jonathan Haddad, MySQL 🙂
Read for further readings on Trigger:

  1.  Forum1
  2. MySQL Expert
  3. With If Else Condition
  4. More Examples
  5. Interesting Complementary
  6. Default Help
  7. Another interesting read
  8. Very detailed help
  9. Similar reference

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