In explaining simple linear regression, isn't it a bit misleading for many examples to illustrate a straight line going through some scatterplot? This seems to suggest that linear regression only works if your independent and dependent variables have some sort of straight-line relationship, whereas the "linear" in linear regression really refers to linear in the parameters of the model right?
Well, it's also linear in the predictors.
For example, if you fit a quadratic you might say 'see, not linear!'... but it is! If $x_1 = x$ and $x_2 = x^2$, and you regress on $x_1$ and $x_2$, it's certainly linear in $(1,x_1,x_2)$. It's linear in the predictors you gave it.
If you regress on $x_1 = \sin(\pi x)$ and $x_2 = \cos(\pi x)$... well, it's still linear in $(1,x_1,x_2)$.
and so on.
By judicious choices of your $x$'s you can use it to fit curves, but it's still linear in what you give it.
Even a local polynomial (kernel-type) fit is actually linear in the predictors. You can write the whole thing as one large linear model.
If $E(y) = X\beta$, $X\beta$ is clearly linear in either $X$ (in the columns of X) or $\beta$.
But yes, the linear-in-the-parameters is what the 'linear' in linear regression 'means'.
Is it at least partly misleading that the elementary presentations are always drawing straight line relationships when regression can fit curves? Perhaps, but you pretty much have to start with lines.
You're right that the "linear" in linear regression or linear models actually stands for linear in the parameters. That means that the parameters you are estimating are coefficients.
For what it's worth however, a curvilinear-looking function that is modeled with a polynomial (e.g., $Y=\beta_0+\beta_1X_1+\beta_2X_1^2$) is actually a multiple regression model, even though we plot the model on a 2-dimensional scatterplot and even though we think of $X_1$ and $X_1^2$ as the same underlying variable. When situated in the appropriate space though, it really is a straight line / flat plane.
It can be hard to see it in a 3-dimensional plot as well, because the relationship between $X_1$ and $X^2_1$ is curvilinear. But imagine a perfectly flat plane that slices through a coke can; from the plane's point of view, the line where the plane intersects the can's wall is straight. If you could arrange it such that you were looking at this perfectly edge-on through the plane, you would see that the function was linear.
Update: I have an example of this worked out in my answer here: Why is polynomial regression considered a special case of multiple linear regression?
Well, simple linear regression usually refers to a model with only a single predictor, so the relationship would be linear (if you transform a variable then the relationship is still linear between the transformed variables).
Many curved relationships are modeled using polynomials or splines which moves away from simple linear regression into multiple linear regression.
Though when I teach simple linear regression I also try to do a teaser saying that curved relationships can be modeled as well, but the students have to come back for another statistics course to learn the details (or they should consult with a statistician to fit these models).