The difference between Awake and Start
is that Start is only called if the
script instance is enabled. This
allows you to delay any initialization
code, until it is really needed. Awake
is always called before any Start
functions. This allows you to order
initialization of scripts.
LateUpdate is called after all Update
functions have been called. This is
useful to order script execution. For
example a follow camera should always
be implemented in LateUpdate because
it tracks objects that might have
moved inside Update.
Also note that LateUpdate is called after animations are applied - this means you can implement procedural animation in LateUpdate which modifies the pose sampled by the animation system.
FixedUpdate is called every fixed
framerate frame, if the MonoBehaviour
is enabled. FixedUpdate should be used
instead of Update when dealing with
Rigidbody. For example when adding a
force to a rigidbody, you have to
apply the force every fixed frame
inside FixedUpdate instead of every
frame inside Update.
I hope that helps. You can read more about overridable functions on the scripting reference page for MonoBehaviour.
Regarding Awake and Start, I treat Awake as "initialize me" and Start as "initialize my connections to others." You can't rely on the state of other objects during Awake, since they may not have Awoken themselves yet, so it's not safe to call their methods. Once you get to Start, all objects are Awake and can call each other.