If database is compromised and downloaded, does it matter if user passwords are hashed?
There are two concepts at play here:


Data at rest
Data in motion


Data at rest

As you correctly noted, if someone gets access to your database and the data itself is not encrypted, then hashing and salting passwords makes no difference.


  
  Encrypt the actual database file itself on the server, so if it were downloaded, they wouldn't be able to have access without knowing the key.
  


You should enable on-disk encryption in some way or form. SQL Server offers Transparent Data Encryption (TDE), where as soon as data leaves memory and is persisted to storage, it is encrypted. This is called "encryption at rest".

This feature may not be available in your RDBMS, so you might rely on the storage vendor's on-disk encryption. Otherwise, you can investigate file-system level encryption.

The important thing is to keep the database administrators away from the encryption keys, and to ensure that database backups are handled with the same security considerations as the database server.

Data in motion

As for your question around applications having access to encrypted data, this is handled differently. I'll return again to SQL Server because that's what I know.


  
  Encrypt as much of the information in the database as possible - but can't encrypt it all otherwise we wouldn't be able to perform quick insight queries on our user base for example.
  


The application uses a certificate to connect to the server, so that only it can see the data it has access to. It's called Always Encrypted, and unless someone has that certificate, the data remains inaccessible.



The database server you end up using should take these two concepts into account. Here's the documentation for SQL Server, but the other more established players should have similar features.