if I manually change the address bar URL from http:// to https:// in a website I visit does it provide security regarding the connection?
Yes, it provides all the standard things TLS provides:
- Authentication (you know you're talking to the expected webserver, not to some attacker impersonating it).
- Confidentiality (your network traffic to and from the server is encrypted; no attacker can know what is sent in each direction).
- Integrity (your network traffic is secure against tampering; you know that the server sees what you sent, and you see what the server sent, without modification en route).
Mind you, there's lots of stuff that HTTPS / TLS doesn't provide. It doesn't fully hide who you're talking to. It doesn't conceal how much data you send or receive. It doesn't give you the ability, after the fact, to prove somebody said something (nobody knows whether you modified it in the meantime, see e.g. fake Twitter screenshots). It doesn't hide your IP address or otherwise make your location unknowable. It doesn't protect you from vulnerabilities in the web application. It doesn't mean that content you receive is safe to open.
Because all the websites that I visited so far which were using the HTTP protocol and I did this worked. I know that major websites redirect from HTTP to HTTPS.
It's a little surprising that this would work on all websites that you tested, without any errors. Most sites that fully support TLS automatically redirect to HTTPS these days, and the rest usually have incomplete configurations (untrusted or expired certificate, certificate issued to a different site such as the hosting provider of your target site, outdated protocol support). I know of a few such examples. Still, it's good news that HTTPS support is so widespread now.
So if I do this and it works, does it simply mean that the website is not redirecting to HTTPS?
Yes. If the page loads correctly and without any warning message (and are using a modern browser), that means your connection is secure and you could do all your browsing for that site via the secure connection. There's even a browser extension to do this for you on a lot of sites where it's known to work: HTTPS Everywhere.
Mind you, as Steffen pointed out, sites that aren't specifically intended to be used with HTTPS may have links (or other forms of navigation) within them that explicitly redirect back to plain HTTP. Also, even if you do all your browsing for that site over HTTPS, any cookies it set on your computer might still be vulnerable to a network-based attacker (somebody on your LAN, or otherwise between you and the server), so be very wary of signing in (or otherwise doing anything unauthenticated) on a site that supports plain HTTP (in any way other than a redirect) at all.