Things which can be done with MYSQL instead of involving PHP [closed]

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. Closed 12 years ago.

What are the must-know MYSQL functions (开发者_StackOverflow中文版like IF etc..,) which every web developer should know?

---

My favorite:

INSERT ... SELECT

It allows me to bypass heavy PHP code to fetch data from one table, manipulate it and insert into another.

---

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

---

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

• IFNULL and NULLIF

• RLIKE for regular expression matching

• If you work with fulltext indexes:

  ... WHERE MATCH (field) AGAINST ('string')

  (Also take a look at the boolean matching)

• Memory tables

• INSERT DELAYED