What is the idea behind Green's function? What does it do?

Green's function, also called a response function, is a device that would allow you to deal with linear boundary value problems (in the literature there are also Green's functions for the initial value problem, but let me stick to the most classical picture).

@achillehiu gave a good example. Let me elaborate on it. So, start with a system of linear algebraic equations of the form $$ Ax=y. $$ I hope that you have no difficulties in solving this problem. But let me represent the right-hand side as $$ y_1e_1+\ldots+y_ne_n, $$ where $y_i$ are the coordinates of vector $y$ and $e_i$ are the standard unit vectors, i.e., the vectors that have ones at the $i$-th position and zeroes everywhere else.

Now, assume that I consider $n$ problems: $$ Ax=e_i,\quad i=1,\ldots,n $$ with the solutions $x_i$ (these are vectors, not components). The linearity of the problem immediately implies that the solution to my original problem can be written automatically as $$ x=y_1x_1+\ldots+y_nx_n, $$ which has the big name the principle of superposition. That is, having at my disposal $x_1,\ldots,x_n$ I immediately solve any problem with arbitrary $y$.

(I hope you note that I did not discover anything new here since each $x_i$ is just a column of the inverse matrix $A^{-1}$).

The general idea for Green's function is to do something similar for differential equations.

Now, let me consider a boundary value problem with a differential operator: $$ Lu=f,\quad u(0)=u(1)=0. $$ I choose $L=-d^2/dx^2$ to keep things as simple as possible. Ideally I would love to do something similar to what I did with the system of linear algebraic equations, now, however, I live in an infinite dimensional space and things are not that easy.

There are various ways to introduce Green's function. Probably the most uninspiring is to show that my boundary value problem is equivalent to the integral equation $$ u(x)=\int_{0}^{1}G(x;\xi)f(\xi)d\xi $$ and define this $G$ to be Green's function.

Arguably the most natural way to motivate Green's function is to start with an infinite series of auxiliary problems $$ -G''=\delta(x-\xi),\quad x,\xi\in(0,1), $$ $\delta$ is the delta function, and I say that there are infinitely many problems since I have the parameter $\xi$. For each fixed value $\xi$ $G(x,\xi)$ is an analogue of $x_i$ above. The complicated part of this approach is to define what delta-function is (just remark that it is not a function). But let me be a physicist and say that delta function is my model for a unit impulse, such that I can represent my $f$ as a linear combination of the impulses at every point $\xi$ in my interval with the corresponding coefficients, formally (this is the analogue of $y=y_1e_1+\ldots+y_ne_n$): $$ f(x)=\int_0^1f(\xi)\delta(\xi-x)d\xi. $$ Now, let me define the Green function as the solution to $$ -G''=\delta(x-\xi),\quad G(0,\xi)=G(1,\xi)=0, $$ if I can find this $G$ then my physical intuition and similarity with algebraic problem tells me that my solution to the boundary value problem will be expressed as the required integral. It can be proved that indeed if I can find this $G$ then $$ u(x)=\int_0^1G(x,\xi)f(\xi)d\xi $$ as required by the principle of superposition.

Try to look at these two problems and realize the inherent similarity. The only remaining question, of course, is how to find this $G$.

Here is an example of how to find Green's function for the problem I described. Unfortunately, this method will not work for more general differential operator. I will use the fact that $$ \int \delta(x-\xi)d x=\chi(x-\xi),\quad \int \chi(x-\xi)dx=\rho(x-\xi), $$ where $\chi$ is the Heaviside function and $\rho$ is the ramp function.

Integrating twice my equation I find $$ G(x,\xi)=-rho(x-\xi)+Ax+B. $$ Using the first boundary condition I get $B=0$. From the second one $$ A=1-\xi. $$ Therefore $$ G(x;\xi)=-\rho(x-\xi)+(1-\xi)x=\begin{cases}(1-\xi)x,&x\leq\xi,\\ (1-x)\xi,&x\geq \xi. \end{cases} $$ You can check that using the function I found the solution to the problem in the comments can be written as $$ u(x)=-10\int_0^1G(x;\xi)\xi d\xi. $$

I don't have enough reputation to comment, but in response to OP's request for an ODE example, check out this recent Mathematica blog post which uses Green's function to solve a RLC circuit problem and a wave equation.