I was talking to someone last night, and he asked a question that why should we use Azure Cloud Services and not the Azure Websites as most common things that people do in the cloud is run web sites and web applications. I never heard this question as You can combine these methods as needed to create the right application for your business. I told him that the approach you choose depends on what problems you're trying to solve.
You can create an inexpensive development and test platform using the language, tools and templates (libraries) of your choice on Virtual Machine; or simply extend your web site running on your on-premise datacenter. These apps can use the Azure provided data management options (Table (No SQL concept), Blob or SQL DB, including MySQL). Azure Virtual Machines gives you lots of flexibility, including administrative access, and you certainly can use it to build a very scalable application, but you'll have to handle many aspects of reliability and administration yourself . Windows Azure Web Sites allows creating the web application and offers low-cost web hosting, but don't provide administrative access.
If you need administrative access and control; and want to handle the other management and administration work including load balancing, reliability etc. automatically, then you need cloud services. In cloud Services, your instance would run on WS (Azure VM, separate from VM you create. Web role instances run on IIS.). Windows Azure maintains this VM such as monitoring, patch management etc. Please refer to http://www.windowsazure.com/en-us/ for details on execution models.
In short, Windows Azure Cloud Services is the best choice for creating scalable, reliable applications with low administration costs. And as mentioned earlier, you can use these technologies separately or combine them as needed to create the right foundation for your application.