Vectors in $\mathbb{R^2}$
As you already know, we write a vector in $\mathbb{R^2}$ as a pair
$\textbf{v}=(v_1, v_2)$
where $v_1$ and $v_2$ are the components of the vector. We define the norm (length) of $\textbf{v}$ as
$\vert\vert \textbf{v} \vert\vert = \sqrt{v_1^2 + v_2^2}$
Now let's try to define orthogonality using the Pythagorean theorem.
Let $\textbf{u}, \textbf{v} \text{ and } \textbf{w}$ be vectors in $\mathbb{R^2}$ such that $\textbf{u} = (u_1, u_2)$ and $\textbf{v}=(v_1, v_2)$ and $\textbf{w}=\textbf{u}+\textbf{v}$. Let's demand that $\textbf{u}, \textbf{v} \text{ and } \textbf{w}$ form a right triangle with $\textbf{w}$ being the hypotenuse, then it is true (Pythagorean theorem) that
$\vert \vert \textbf{w}\vert\vert^2 = \vert\vert \textbf{u}+\textbf{v}\vert\vert^2 = \vert\vert \textbf{u}\vert\vert^2+\vert\vert\textbf{v}\vert\vert^2$
$(u_1 +v_1)^2 + (u_2+v_2)^2 = (u_1^2+u_2^2)+(v_1^2+v_2^2)$
$(u_1^2+2 u_1 v_1 +v_1^2)+(u_2^2+ 2u_2v_2 +v_2^2) = (u_1^2+u_2^2)+(v_1^2+v_2^2)$
After some cancellation we arive at
$\begin{equation}u_1 v_1 + u_2 v_2 = 0\end{equation} ~~~~~~~~(*)$
So, for two vectors to be orthogonal they must satisfy this condition which you may know as the dot product or the inner product.
$\textbf{u} \cdot \textbf{v} = u_1 v_1 + u_2 v_2 $.
There's more to say about this, but let's not.
Functions in $\mathbb{L^2([0, 1])}$
Now you can apply the same ideas to functions. So to say that two functions are orthogonal means that their norms satisfy the Pythagorean theorem.
We define the norm $\mathbb{L^2}$ as
$|| f || = (\int\limits_0^1 | f(t)|^2 dt)^{\frac{1}{2}}$
so the Pythagorean theorem, for real functions $f$ and $g$, is now
$|| f + g || = ||f||^2 +||g||^2$
$\int\limits_0^1 ( f(t)+g(t))^2 dt = \int\limits_0^1 f(t)^2 dt +\int\limits_0^1 g(t)^2 dt$
$\int\limits_0^1 ( f(t)^2 + 2f(t) g(t) +g(t)^2) dt = \int\limits_0^1 f(t)^2 dt +\int\limits_0^1 g(t)^2 dt$
which after some cancellation gives
$\int\limits_0^1 f(t) g(t) dt =0 ~~~~~~~~(**)$
So, for two function to be orthogonal in $L^2([0,1])$ they must satisfy this condition. As we did with vectors in $\mathbb{R^2}$ we will now do with functions in $L^2([0,1])$ and define the inner product for real function $f$ and $g$ in $L^2([0,1])$ as
$(f, g) = \int\limits_0^1 f(t) g(t) dt$
Comments
In conclusion, two vectors are orthogonal if their inner product is zero, or equivalently, when Pythagorean theorem is satisfied. Should you have a mental picture of what it means for two sin function to be orthogonal? I don't know. I don't have it. I guess you could think of it in terms of a total area on the interval $[0, 1]$ of the product of the two functions being zero.