In my VERY limited experience, school libraries tend to be quiet/silent while public ones are more communal with varied loudness spaces. But a lot of people only experience school libraries.

My hometown library was quiet all the time in the 80s, but it was also basically one really large room and any noise carried really well so they enforced quiet. The state college library was similar, and enforced quiet when I went in the 90s because people were studying.

Most libraries in the movies are also large, open spaces and like everything else they play up the extremes or the writer's experiences. Since movies tend to stick with stereotypes, so don't expect a library scene in a movie without someone being hushed any time soon.

I wish. Getting scolded by an old crone for daring to use your voice in the library never leaves you. Children can be excited or loud sometimes, but libraries exist to make them seen, not heard.