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