The difference between these classifications of fluids is in how they resist the flow versus a Newtonian fluids.

For fluids like water an increase in shear force in linearly proportional to the rate of shear deformation. To put it in the context of a pipe system, the harder a pump pushes (or pulls) a Newtonian fluid, the faster it flows. This is ignoring turbulent friction and other things, but in an idealized situation we can say that doubling the power of the pump would double the flow rate in the pipe.

To give two examples of what a non-newtonian fluid would do there: ketchup is a Bingham plastic, which acts solid under low stresses and then flows if that stress passes a threshold. Imagining the pipe and pump are filled with stationary ketchup and the pump is turned off, we can think about what would happen if we turned a knob that slowly ramped up power to the pump. At first the ketchup wouldn’t move, and the pump would just heat up the ketchup from wasting the work done. Then, turning the know further, it will eventually reach the break point and the fluid will begin to flow like a Newtonian fluid, and linearly proportional too. But if the power is decreased again such that the stress is below that yield stress the fluid will become stationary again.

Next example would be Oobleck, or a shear thickening fluid, which when we construct the same scenario, will flow “easily” at low power through the pump, and a low flow rate. As the power/flow rate/shear stress is increased then the resistance to the flow will increase dramatically and there will be incredible diminishing returns to the increase in flow rate for the increase in pumping power.

In reality there are a lot more asterisks and corollaries, like the fact that I think that oobleck would seize up at an flow rate that would be too slow to be a worthwhile demonstration.

I think that @saccharomyces@mander.xyz provided a great response for the specific case of flow in a pipe.

I just want to add that if you look beyond the restrictions of flow in a pipe, there are many other types of behavior that non-Newtonian fluids exhibit. We measure this in the lab on instruments called rheometers. Basically, we put some liquid in the instrument and then deform it and measure the resistance to that deformation. One of the most common ways to apply that deformation is to do so back and forth in an oscillating manner. Depending on the frequency at which you apply this strain, the solid/liquid-like behavior can change. If you have some background in physics or want to get a decent understanding, I found this paper that, on skimming it, seems to be pretty consistent with the way I was taught this stuff in grad school.

One graph I want to point out is Figure 13 which shows what would be a "typical" viscoelastic polymer solution. An easy way to understand this graph is that as we go from left to right, we are applying strain back and forth quicker and quicker, essentially shaking it faster. When the G' value is higher than the G" value, then the material is behaving more like a solid and conversely, when G" > G', then it is behaving more like a liquid. You can see that the material goes through different phases of behavior as the strain frequency changes. Just for you I went and dug up an old graph from my thesis to show a real-life example of this happening too.

My favorite demonstration of this is to put Oobleck (or something similar) onto a speaker and then change the frequency and see what happens.