Question: I know you can ALTER the column order in MySQL with FIRST and AFTER, but why would you want to bother? Since good queries explicitly name columns when inserting data, is there really any reason to care what order your columns are in in the table?

Answer: 
As is often the case, the biggest factor is the next guy who has to work on the system. I try to have the primary key columns first, the foreign key columns second, and then the rest of the columns in descending order of importance / significance to the system.
In general what happens in SQL Server when you change column order through Management Studio, is that it creates a temp table with the new structure, moves the data to that structure from the old table, drops the old table and renames the new one. As you might imagine, this is a very poor choice for performance if you have a large table. I don't know if My SQL does the same, but it is one reason why many of us avoid reordering columns. Since select \* should never be used in a production system, adding columns at the end is not aproblem for a well-designed system. Order of columns inthe table should in genral not be messed with.