It's amazing, how many words one can make about some not that complicated issues like the socalled "fictitious forces" or, as I prefer to call them "inertial froces" in Newtonian physics. To see, what's really going on, one should do the math. Here, I'll concentrate on the special case of a reference frame rotating, relative to an inertial frame. Examples are the motion on Earth, where the rotation is (in some crude approximation) around a fixed axis or the body frame of a rigid body rotating around its center of mass or around another fixed point (spinning free and heavy top).
We look at the most simple case of a single point particle, moving due to some external force (e.g., in the gravitational field of the Earth or a charged particle in an electric field). Let ##\vec{x}## denote the Cartesian righthanded coordinates in an inertial reference frame and ##\vec{x}'## that with respect to the rotating frame. Then there's a time-dependent matrix ##\hat{D}(t) \in \mathrm{SO}(3)## such that
$$\vec{x}=\hat{D} \vec{x}'.$$
The motion in the inertial frame follows Newton's 2nd Law
$$m \ddot{\vec{x}}=\vec{F}(t,\vec{x},\dot{\vec{x}}).$$
Here ##\vec{F}## is a "true force", i.e., caused by some interaction of the particle with some other particles or due to fields.
Now we simply want to know, how and observer in the rotating system describes the motion, i.e., we must calculate the 2nd time derivative in terms of ##\vec{x}'##. Obviously we have
$$\dot{\vec{x}} = \dot{\hat{D}} \vec{x}'+\hat{D} \dot{\vec{x}}'.$$
Multiplying with ##\hat{D}^{-1}## from the left, we find
$$\hat{D}^{-1} \dot{\vec{x}}=\hat{D}^{-1} \dot{\hat{D}} \vec{x}'+\dot{\vec{x}}'.$$
Now since ##\hat{D}^{-1} = \hat{D}^{T}## and thus ##\hat{D} \hat{D}^T=1## and thus through taking the time derivative of this equation
$$\hat{D}^{-1} \dot{\hat{D}} = \hat{D}^{T} \dot{\hat{D}}=-\dot{\hat{D}}^T \hat{D} =-(\hat{D}^t \dot{\hat{D}})^T,$$
we see that ##\hat{D}^{-1} \dot{\hat{D}}## is an antisymmetric matrix and thus we can define an axial vector ##\vec{\omega}'##, the components of the momentaneous angular velocity of the rotation of the reference frames in the rotating frame such that
$$\hat{D}^{-1} \dot{\vec{x}}=\dot{\vec{x}}'+\vec{\omega}' \times \vec{x}'.$$
Thus we have
$$\dot{\vec{x}}=\hat{D} (\dot{\vec{x}}'+\vec{\omega}' \times \vec{x}'.$$
Now we can do the same calculation again to find
$$\ddot{\vec{x}}=\hat{D} [\ddot{\vec{x}}'+2\vec{\omega}' \times \dot{\vec{x}}' + \dot{\vec{\omega}}' \times \dot{\vec{x}}' + \vec{\omega}' \times (\vec{\omega}' \times \vec{x}')]$$
Plugging this into the Equation of motion and multiplying finally again with ##\hat{D}^{-1}## from the left leads to
$$m [\ddot{\vec{x}}'+2\vec{\omega}' \times \dot{\vec{x}}' + \dot{\vec{\omega}}' \times \dot{\vec{x}}' + \vec{\omega}' \times (\vec{\omega}' \times \vec{x}')]=\vec{F}'.$$
To bring this in the form of the usual Newtonian equation of motion, one brings all terms on the left-hand side of the equation to the right-hand side except the first term.
Thus according to the rotating observer the particle feels the external force, expressed in hin coordinates and a bunch of "inertial forces", among them
$$\vec{F}_C'=-2 m \vec{\omega}' \times \dot{\vec{x}}' \qquad \text{(Coriolis force)},$$
$$\vec{F}_Z'=-m \vec{\omega}' \times (\vec{\omega}' \times \vec{x}') \qquad \text{(centrifugal force)}.$$
Then there is a force due to the change of the momentaneous rotation axis, which I don't know whether it has a special name,
$$\vec{F}_A'=-m \dot{\vec{\omega}}' \times \vec{x}'.$$
These forces are "ficititious" in the sense that they are not due to interactions with other particles or fields but just due to the description of the motion in a rotating reference frame, and they can thus be eliminated by doing the opposite transformation back to the inertial frame. That's it. Nothing mysterious.