Encryption at rest protects data sitting on disk, in a database, or in object storage, so that a stolen drive or a leaked backup file is unreadable. This is often transparent. AWS RDS, EBS volumes, and S3 buckets can encrypt every block with AES-256 with one setting, and the application never sees a difference. The database still decrypts on read, so encryption at rest does not protect against an attacker who already has valid database credentials; it protects against theft of the underlying media.
Encryption in transit protects data while it moves across a network so nobody can read or tamper with it on the wire. TLS 1.3 is the workhorse: it does a handshake to agree on a session key, then encrypts the rest of the connection. The same idea covers service-to-service traffic inside a cluster (mTLS between microservices) and replication traffic between database nodes.
A third category, encryption in use, keeps data encrypted even while being processed, using techniques like homomorphic encryption or hardware enclaves such as Intel SGX. It is far slower and still niche, used mostly for highly sensitive multi-party computation.