NoSQL stands for "Not only SQL" and usually means that the database is not a relational database, which have been very popular the last decades.
The reason why NoSQL has been so popular the last few years is mainly because, when a relational database grows out of one server, it is no longer that easy to use. In other words, they don't scale out very well in a distributed system. All of the big sites that you mentioned Google, Yahoo, Facebook and Amazon (I don't know much about Digg) have lots of data and store the data in distributed systems for several reasons. It could be that the data doesn't fit on one server, or there are requirements for high availability.
The properties of a distributed system can be described by the CAP Theorem. Of the three properties you can only have at most two:
- tolerance to network Partitioning
Amazon Dynamo uses Eventual Consistency to come close to get all three properties. The paper Dynamo: Amazon’s Highly Available Key-value Store is worth reading when learning about NoSQL databases and distributed systems. Amazon Dynamo has the A and P properties.
Google take a different approach with BigTable, that has the C and A properties.
Other NoSQL databases
As I wrote in the beginning there are many other kind of NoSQL databases, that are designed for different requirements. E.g. graph databases like Neo4j, document databases like CouchDB and object databases like db4o.
Finally I would like to say that relational databases will remain popular. They are very flexible and maintainable. But they are not always the best choice.