What are the must-know MYSQL functions (like IF etc..,) which every web developer should know?
My favorite:
It allows me to bypass heavy PHP code to fetch data from one table, manipulate it and insert into another.
My personal favorite is "insert... on duplicate key update..." ;-)
Every string functions: concat, concat_ws, ... now().
GROUP BY functions: sum(), max(), count() + the wonderful group_concat()
JOIN clauses and VIEWs if you you are planning complex requests.
Well, in fact, the more you know MySQL (I mean, everything), the best.
i'd say:
CREATE VIEW .. AS SELECT
when i have querys that i use all the time, but it's not a function =P
Triggers and stored procedures offer much automation that involve only the SQL engine and this will significantly reduce the Web server's load and PHP code involved in data housekeeping.
My favorite:
alter table child add Foreign key (parent_id) references parent(id);
Taking care of data integrity with foreign keys will reduce your code base a lot. Not mentionign MySQL is better at this than PHP.
Some features that come to my mind:
UNION and UNION ALL if your Model involves tables with similar data:
(SELECT ...) UNION ALL (SELECT ...)
RLIKE for regular expression matching
If you work with fulltext indexes:
... WHERE MATCH (field) AGAINST ('string')
(Also take a look at the boolean matching)